MySQL/MariaDB基础性知识及DDL操作详解

前言

MySQL/MariaDB是一个开放源码的小型关联式数据库管理系统，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL/MariaDB作为网站数据库。

基础架构

MySQL核心组件

连接池：认证、线程重用、连接数限制、内存检查、缓存

SQL接口：DDL, DML, 关系型数据库的基本抽象

parser: 查询转换、对象权限检查

优化器：访问路径，性能相关的统计数据

caches和buffers：与存储引擎自身相关的I/O性能提升工具

存储引擎：MyISAM、InnoDB(变种：XtraDB)、Memory、Merge、Federated、CSV、Archive、Blackholl、Aria、SphinxSE、TokuDB

内部结构

程序类别及命令选项

服务器端程序：启动并监听于套接字上；mysqld, mysqld_safe, mysqld_multi

客户端程序：可通过mysql协议连入服务器并发出请求的；mysql, mysqlbinlog, mysqladmin, mysqldump等

工具程序：运行于服务器进程所在的主机，实现一些管理或维护操作，myisamchk

客户端程序通用选项

1
2
3
4
5
6
7
8
9
10
11


-u,--user #指定登陆用户

-h,--host #指定登陆主机

-p,--password #指定登陆密码

--protocol={tcp|socket|memory|pipe} #指定协议

-P,--port #指定端口，默认监听端口：tcp/3306

--socket #指定本地连接的sock文件，相当于--protocol socket

--compress #数据传输采用压缩格式

-D,--database #指定连接后默认使用的数据库

-H,--html #指定产生html输出

-X,--xml #指定产生xml输出

--safe-updates #拒绝使用无where子句的update或delete命令

客户端程序命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

交互式模式：
#客户端命令

   mysql> help #列出所有命令

   mysql> \? #相当于help

   mysql> \c #取消命令执行

   mysql> \g #发送命令至服务器端

   mysql> \G #发送命令至服务器端，垂直显示结果

   mysql> \q #退出

   mysql> \! #运行shell命令

   mysql> \s #显示服务器端状态信息

   mysql> \. /path/to/somefile.sql #批量执行sql

   mysql> \u #将指定的库设为默认库
#服务器端命令

   mysql> help KEYWORD 获取关键字的帮助信息
批模式：

mysql < /path/from/somefile.sql

管理工具mysqladmin

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22


格式：mysqladmin [options] command [arg] [command [arg]] ...
#常用命令
create DB_Name：创建数据库
drop DB_Name：删除数据库
debug：打开调试日志并记录于error log中
status：显示简要状态信息

        --sleep #间隔秒数

        --count #显示的次数
extend-status: 显示mysqld的所有服务器状态变量
flush-privileges: 刷新授权表，相当于reload命令
flush-hosts: 清除dns缓存及被拒绝的客户端列表缓存
flush-logs: 滚动日志, 二进制日志和中继日志
flush-status: 重置各状态变量
flush-tables: 关闭当前打开的所有的表文件句柄；
flush-treads: 重置线程缓存；
password: 设置密码

ping: 测试服务器是否在线
processlist: 显示当前服务器上的所有线程
refresh: 相当于执行flush-hosts和flush-logs

shutdown: 关闭服务器进程 ；
start-slave, stop-slave: 启动、关闭从服务器线程；
variables: 显示服务器变量

功能特性补充

命令行编辑功能

1
2
3
4
5

Ctrl+a: 快速移动光标至行首
Ctrl+e: 快速移动光标至行尾
Ctrl+w: 删除光标之前的单词
Ctrl+u: 删除行首至光标处的所有内容
Ctrl+y: 粘贴使用Ctrl+w或Ctrl+u删除的内容

提示符

1
2
3
4
5
6


mysql> #等待输入命令

->   #续行

‘>  #还需补全后半部单引号

“>  #还需补全后半部双引号

`>   #还需补全后半部反引号

/*>  #注释，不执行，需以*/结束注释

SQL语言的组成部分

DDL：数据定义语言

DCL：数据控制语言，如授权

DML：数据操作语言

完整性定义语言：DDL功能性约束（主键、外键、惟一键、条件、非空、事务）

视图定义：虚表，存储下来的select语句

事务控制

数据字典

数据字典：系统编目（system catalog）

保存了数据库服务器上的元数据

元数据

1
2
3
4
5
6
7
8
9
10
11

关系的名称
每个关系中各字段的名称
各字段的类型和长度
约束
每个关系上的视图的名字及视图的定义
授权的用户名字
用户的授权和帐户信息
统计类数据

    每个关系中字段数；

    每个关系中行数；

    每个关系的存储方法；

保存元数据的数据库

1
2
3

infomation_schema
mysql
performance_schema

数据类型及属性修饰符

数据类型的功用

①存储的值类型

②占据的最大存储空间

③定长、变长

④如何被索引和排序

⑤是否能够被索引

数据类型
字符型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CHAR、VARCARH、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
 
BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOG、LONGBLOB
 
修饰符：
NULL：允许为空
NOT NULL：不允许为空

DEFAULT 'string'：默认值，不适用于TEXT

CHARACTER SET 'set'：设定字符集

    SHOW CHARACTER SET;查看可用字符集

COLLATION 'collation'：设定字符集排序规则

    SHOW COLLATION;查看可用排序规则
DEFAULT：不用于BLOB类型
字符有通配符：

    %: 匹配任意长度的任意字符

    _: 匹配任意单个字符；

整型

1
2
3
4
5
6
7
8
9

TINYINT、SMALLINT、MEDIUMINT 、INT、BININT
 
修饰符：
UNSIGNED：无符号
NULL
NOT NULL
DEFAULT
AUTO_INCREMENT: 自动增长
#特殊要求：非空，且必须是主键或惟一键

浮点型

1
2
3
4
5
6
7

FLOAT、DOUBLE
 
修饰符：
NOT NULL
NULL
DEFAULT
UNSIGNED

布尔型

1

没有专用布尔型，其是TINYINT(1)的别名

日期时间型

1
2
3
4
5
6

DATE、TIME、DATETIME、TIMESTAMP、YEAR(2)、YEAR(4)
 
修饰符：
NULL
NOT NULL
DEFAULT VALUE

内建类型

1
2
3
4
5
6
7
8
9

ENUM：枚举，表示仅能从给出的选项选择其中一个

      ENUM('string1','string2')
SET：集合, 表示能使用给出的元素组合成字符串

      SET('a','b','c')

       
修饰符：
NULL
NOT NULL

DEFAULT ''

SQL模式及服务器变量

sql模式：用来限定mysqld的工作特性

TRADITIONAL：传统模式

STRICT_TRANS_TABLES：对支持事务的表使用严格模式

STRICT_ALL_TABLES：对所有表使用严格模式

服务器变量的类型

全局：对所有会话都生效

所有的会话在建立时都从全局继承，但继承完成后每个会话独立维护自己会话级变量

修改全局的服务器变量仅对之后建立的会话生效

要求有管理权限

会话：仅对当前会话有效

修改即刻生效

不要求管理权限

修改方式

动态修改: 会话级别，立即生效；全局级别，新建立的会话有效，重启服务会失效

静态修改：要修改配置文件，或修改传递给mysqld的选项的值，重启后有效

注：并非所有的服务器变量都支持动态修改

查看服务器变量

1
2
3
4
5
6
7

mysql> SHOW {GLOBAL|SESSION} VARIABLES [LIKE clause];
 
mysql> SELECT @@{GLOBAL|SESSION}.VARIABLE_NAME;
 

mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='';
 

mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='';

修改服务器变量

1
2
3
4
5

动态修改变量的值：

mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE'
让设置永久有效的方式：
[mysqld]

sql_mode = 'STRICT_ALL_TABLES'

语句书写大小写说明

①SQL关键字及函数名不区字符大小写

②数据库、表、索引及视图的名称是否区分大小写取决于低层的OS及FS

③存储过程、存储函数及事件调度器不区分字符大小写，但触发器区分

④表别名不区分大小写

⑤字段中字符数据，类型为binary、blog、varbinary时区分大小写，其它的不区分

DDL操作

数据库操作

1
2
3
4
5
6

创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
修改数据库
ALTER {DATABASE | SCHEMA} [IF EXISTS] db_name

表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

创建表
CREATE TABLE [IF NOT EXISTS] tb_name
(col1_def,col2_def,PRIMARY KEY(col_name,...),UNIQUE (col1,...),INDEX (col1,...))
[table_option]
table_option:

    ENGINE [=] engine_name

    COMMENT [=] 'string'

    ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

    TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name   #复制表数据

    [(create_definition,...)]

    [table_options]

    select_statement

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name   #复制表结构

    { LIKE old_tbl_name | (LIKE old_tbl_name) }
删除表
DROP [TEMPORARY] TABLE [IF EXISTS]  tbl_name [, tbl_name] ...

[RESTRICT | CASCADE]  #cascade会将有依赖关联的表一并删除
修改表
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name

    [alter_specification [, alter_specification] ...]
alter_specification:
#插入新字段
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
#删除字段
DROP [COLUMN] col_name
#修改字段属性
ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} 
#修改字段名
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
#修改字段类型及属性等
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
修改表名
ALTER TABLE tb_name RENAME TO new_tb_name;
RENAME TABLE old_name TO new_name;
#指定排序字段
ORDER BY col_name [, col_name] ...
#转换字符集及排序规则
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

表结构之索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

索引：特殊的数据结构，用于快速找到数据记录（行）

      注：键可用作索引；并非所有索引都是键

索引类型：B-Tree (B+ Tree) (左前缀), hash (key-value), R-Tree, FullText
索引的优点：
大大减少服务器需要扫描的数据量
索引可以帮助服务器尽量避免进行排序及使用临时表

索引可以将随机I/O转换为顺序I/O
索引可以创建在一个字段，也可是多个字段上：简单索引、组合索引

    例子：索引(姓名,性别)

    where name like 'tom%'

    where gender = 'female'

    where familyname = 'tom' and gender = 'female'
B-Tree索引的适用场景:
全值匹配：比较操作符 =, <=>

左前缀匹配：LIKE 'tom%'
列前缀匹配
匹配范围值：
组合索引类型中，精确匹配前一列，并范围匹配后一列
只访问索引的查询：覆盖索引，从索引就可以直接得到最终结果
哈希索引适用场景：
哈希索引只包含哈希值和行指针；不是按照索引值顺序存储，无法用于排序；不支持部分索引匹配查找
哈希索引只支持等值比较查询，包含=, IN(), <=>
聚簇索引：索引和数据一起存放；

    数据文件：索引顺序文件
非聚簇索引：索引和数据分开存放，而数据记录未必顺序存放；但索引数据一般顺序存放；

    数据文件：堆文件
添加索引
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name ADD {INDEX|KEY} [index_name]
[index_type] 
(index_col_name,...) 
[index_option] ...
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option] ...
删除索引
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name DROP {INDEX|KEY} index_name
DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name
查看索引
SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]

[WHERE expr]
索引创建的基本法则：基于搜索键来创建索引
SELECT的WHERE子句的查询条件中的字段
索引的使用策略：
1、要使用独立的列：索引列不是表达式的一部分；
SELECT Name FROM students WHERE Age + 2 > 32;
2、前缀索引

索引选择性：单独的索引项与数据表中的记录的总数的比值；取值范围：1/n-1;
books, book
3、多列索引
查询条件多用AND，则建议使用多列索引；
查询条件多用OR，建议使用多个简单索引；
4、合适的次序：将选择性高的列写在最左侧；
(Name, Gender)
5、聚簇索引：
好处：数据访问更快；
缺点：更新聚簇索引列的代价很高；
6、覆盖索引

SELECT Name FROM students WHERE Name LIKE'tom%';
7、避免冗余索引
name, (name,gender)

The end

MySQL/MariaDB的知识就先说到这里了，后续文章还会继续讲解MySQL/MariaDB的相关知识，有兴趣可以继续关注。以上仅为个人学习整理，如有错漏，大神勿喷~~~

本文转自北城书生 51CTO博客，原文链接:http://blog.51cto.com/scholar/1642286

MySQL/MariaDB基础性知识及DDL操作详解

相关推荐