#### 第14章：
#### MySQL
#### 14.1 MySQL 介绍
MySQL 是一个小型关系数据库管理系统。MySQL8已出，有兴趣的可以自己学习。
##### MySQL 的优势
* 速度：运行速度快。
* 价格：MySQL对多数人来说是免费的。
* 容易使用：与其他大型数据库的设置和管理相比，其复杂度较低，易于学习。
* 可移植性：能够工作在众多不同的系统平台上，例如windows，linux，unix，mac os等。
* 丰富的接口：提供了用于C/C++、Java、Perl、PHP、Python、Ruby和Tcl等语言的API。
* 支持查询语言：MySQL可以利用标准SQL语法和支持ODBC的应用程序。
* 安全性和连接性：十分灵活和安全的权限和密码系统，允许基于主机的验证。连接到服务器时，所有的密码传输均采用加密形式，从而保证了密码安全。并且由于MySQL是网络化的，因此可以在因特网上的任何地方访问，提高数据共享效率。
##### MySQL 5.7 的性功能
1. 支持JSON
JSON是一种存储信息的格式，可以很好的替代XML。从MySQL5.7.8版本开始，MySQL将支持JSON，而在此版本之前，只能通过string之类的通用形式来存储JSON文件，这样做的缺陷很明显，就是必须要自行确认和解析数据、忍受更新数据困难和在执行插入时较慢的速度。
2. 性能和可扩展性
改进InnoDB的可扩展性和`临时表`的性能，从而实现更快的网络和大数据加载等操作。
3. 改进复制以提高可用性的性能
改进复制包括多源复制、多从线程增强、在线GTIDs和增强的半同步复制。
4. 性能模式提供更好的视角
增加了许多新的监控功能，以减少空间和过载，使用新的SYS模式显著提高易用性。
5. 安全
以安全为第一宗旨，提供了很多新的功能，从而保证数据库的安全。
6. 优化
重写了大部分解析器、优化器和成本模型，这提高了可维护性、可扩展性和性能。
7. GIS
MySQL5.7全新的功能，包括InnoDB空间索引，使用Boots.Geometry，同时提高完整性和标准符合性。
#### 14.2 MySQL数据库基本操作
##### 显示所有数据库
MySQL安装完成之后，将会在其data目录下(配置的数据存放目录)创建几个必须的数据库，可以使用`SHOW DATABASES;`语句来查看对当前所有存在的数据库。
~~~
mysql> SHOW DATABASES;
+----------------------+
|Database |
+----------------------+
|information_schema |
|musql |
|performance_schema |
|sakila |
|test |
|world |
+---------------------+
6 rows in set (0.04 sec)
~~~
查看到有6个数据库，其中mysql库是必须的，可以利用test库做测试工作。
##### 创建数据库
语法
~~~
CTEATE DATABASE database_name
~~~
例如:
~~~
mysql>CREATE DATABASE test_db;
~~~
~~~
mysql>SHOW databases;
+----------------------+
|Database |
+----------------------+
|information_schema |
|musql |
|performance_schema |
|sakila |
|test |
|test_db | //这里有了test_db库
|world |
+---------------------+
7 rows in set (0.05 sec)
~~~
##### 删除数据库
删除数据库是将已经存在的数据库从磁盘空间上清除，数据库中的数据也将一同被删除。
语法:
~~~
DROP DATABASE database_name;
~~~
例如:
~~~
mysql>DROP DATABASE test_db;
~~~
~~~
mysql>SHOW CREATE DATABASE test_db\G
ERROR 1049 (42000):Unkonwn database 'test_db' //表示test_db库已不存在，删除成功
ERROR:
No query specified
~~~
#### 14.3 数据库存储引擎
数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的数据库引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎可以获得特定的功能。
MySQL5.7支持的存储引擎有：InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOlE等。(对于普通的线性生产数据的存储，尽量使用InnoDB)。
##### InnoDB 存储引擎
InnoDB 是事务性数据库的首选引擎，支持事务安全表(ACID)，支持行锁和外键。是默认的存储引擎，主要特性有:
* InnoDB给MySQL提供了具有`提交`、`回滚`和`崩溃恢复能力`的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表与其他MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。
* InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎不能匹敌的。
* InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引存在一个逻辑表空间中，表空间可以包含数个文件(或原始磁盘分区)。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
* InnoDB支持外键完整性约束(FOREIGN KEY)
存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显性地在表定义时指定主键，InnoDB会为每一行生成一个6B的ROWID，并以此作为主键。
* InnoDB被用在众多需要高性能的大型数据库站点上。
InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及名为ib\_logfile0和ib\_logfile1的5MB大小的日志文件(二进制事务日志文件)。
##### MyISAM存储引擎
MyISAM基于ISAM存储引擎，并对其进行扩展。MyISAM拥有较高的插入、查询速度，但不支持事务。主要特性有：
* 大文件(达63位文件长度)在支持大文件的文件系统和操作系统上被支持。
* 当把删除、更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一个块来自动完成。
* 每个MyISAM表最大索引数是64，可以通过重新编译来改变。每个最大索引列是16个。
* 最大的键长度是1000B，这也可以通过编译来改变。对于键长度超过250B的情况，一个超过1024B的键将用上。
* BLOB和TEXT列可以被索引。
* NULL值被允许在索引的列中。这个值占每个键的0~1个字节。
* 所有数字键值以高字节优先被存储以允许一个更高的索引压缩。
* 每表一个AUTO\_INCREMENT列的内部处理。INSERT和UPDATE操作自动更新这一列。这使得AUTO\_INCREMENT列更快(至少10%)。在序列顶的值被删除之后就不能再利用。
* 可以把数据文件和索引文件放在不同的目录。
* 每个字符列可以有不同的字符集。
* 有VARCHAR的表可以固定或动态记录长度。
* VARCHAR和CHAR列可以多达64KB。
MyISAM引擎创建数据库，将产生3个文件。文件的名字以表的名字开始，扩展名指出文件类型：frm文件存储表定义，数据文件的扩展名为,MYD(MYData)，索引文件的扩展名是.MYI（MYIndex）。
##### MEMORY存储引擎
MEMORY存储引擎表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。主要特性：
* MEMORY表的每个表都可以有多达32个索引，每个索引16列，以及500B最大键长度。
* MEMORY存储引擎执行HASH和BTREE索引。
* 可以在一个MEMORY表中有非唯一键。
* MEMORY不支持BLOB或TEXT列。
* MEMORY支持AUTO\_INCREMENT列和对可包含NULL值的列的索引。
* MEMORY表在所有客户端之间共享（就像其他任何非TEMPORARY表）。
* MEMORY表内容被存在内存中，内存是MEMORY表和服务器在查询处理时的空闲中创建的内部表共享。
* 当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）。
| 功能 | MyISAM | Memory | InnoDB | Archive |
| --- | --- | --- | --- | --- |
| 存储限制 | 256TB | RAM | 64TB | None |
| 支持事务 | No | No | Yes | No |
| 支持全文索引 | Yes | No | No | No |
| 支持数索引 | Yes | Yes | Yes | No |
| 支持哈希索引 | No | Yes | No | No |
| 支持数据缓存 | No | N/A | Yes | No |
| 支持外键 | No | No | Yes | No |
存储引擎比较
如果要提供提交、回滚和崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是个很好的选择。(建议新手使用InnoDB)。
#### 14.4 数据表的基本操作
数据表属于数据库，在操作数据表之前应当选择数据库。使用"USE "指定需要操作的数据库。
##### 创建数据表
语法：
~~~
CREATE TABLE 表名
(
字段名1，数据类型[列级别约束条件] [默认值],
字段名2，数据类型[列级别约束条件] [默认值],
字段名3，数据类型[列级别约束条件] [默认值],
......
[表级别约束条件]
);
~~~
例如创建一个员工表：
~~~
CREATE TABLE tb_empl
(
id INT(11), //员工编号
name VARCHAR(25), //员工名称
deptId INT(11), //所在部门编号
salary FLOAT //工资
);
~~~
使用"SHOW TABLES" 查看所有数据表：
~~~
mysql>SHOW TABLES;
+-----------------------------+
|Tables_in_ test_db |
+-----------------------------+
|tb_empl |
+-----------------------------+
1 row in set (0.00 sec)
~~~
##### 主键约束
主键是一列或者多列的组合，可以唯一标识表中的一行记录。要求不能为空，且主键列的数据唯一。可以结合外键定义多个表之间的关系，并可以加快数据库查询的速度。
1. 在定义列的时候可以指定主键，语法：
~~~
CREATE TABLE tb_empl
(
id INT(11) PRIMARY KEY, // 主键
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
~~~
1. 在定义完所有列之后指定主键，语法：
~~~
CREATE TABLE tb_empl
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id) // 指定主键
);
~~~
多列(多字段)联合主键
~~~
CREATE TABLE tb_empl
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(name,deptId) // 指定组合主键
);
~~~
##### 外键约束(生产环境视情况使用)
外键用来在两个表的数据之间建立关联，它可以是一列或多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以是空值，如果不是空值，则每一个外键必须对应另一个表的中主键的某个值。
外键：首先它是表中的一个字段，它可以不是本表的主键，但对应另一个表的主键。外键主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保证数据的`一致性`、`完整性`。
~~~
CREATE TABLE tb_emp5
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);
~~~
创建tb\_emp5表，添加名为fk的外键约束，外键为deptId依赖主表tb\_dept1的主键id。
##### 使用非空约束
非空约束指定字段不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库会报错。
~~~
CREATE TABLE tb_emp6
(
id INT(11) PRIMARY KEY,
name VARCHAR(25) NOT NULL, // NOT NULL 非空约束
deptId INT(11),
salary FLOAT,
);
~~~
##### 唯一约束
唯一约束要求该列唯一，允许为空，但也只能出现一个空值。唯一约束可以保证一列或者几列不出现重复值。
1. 在定义列后直接指定唯一约束
~~~
CREATE TABLE tb_emp6
(
id INT(11) PRIMARY KEY,
name VARCHAR(25) UNIQUE, // 唯一约束
location VARCHAR(50)
);
~~~
2. 在定义完所有列后指定唯一约束
~~~
CREATE TABLE tb_emp6
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
location VARCHAR(50),
CONSTRAINT STH UNIQUE(name) //指定唯一约束
);
~~~
##### 默认约束
默认约束指定某列的默认值。
~~~
CREATE TABLE tb_emp7
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11) DEFAULT 1111, //指定默认值为1111
location VARCHAR(50),
);
~~~
之后向tb\_emp7表插入数据时，新插入记录如果没有指定部门编号，则默认都为1111。
##### 设置表的属性自动增加
在每次插入新记录的时候，系统自动生成字段自动增加的主键值。使用AUTO\_INCREMENT关键字来实现。
~~~
CREATE TABLE tb_emp8
(
id INT(11) PRIMARY KEY AUTO_INCREMENT, //设置id为主键并插入时自动自增
name VARCHAR(25),
deptId INT(11) DEFAULT 1111,
location VARCHAR(50),
);
~~~
##### 查看数据表结构
基本查看语句`DESCRIBE/DESC`用于查看表结构 ：
~~~
mysql>DESCRIBE tb_dept1;
+----------+-----------+-----------+----------+--------+----------+
|Field |Type |Null |Key |Default |Extra |
+----------+-----------+-----------+----------+--------+----------+
|id |int(11) |No |PRI |NULL | |
|name |varchar(22)|No | |NULL | |
|location |varchar(50)|YES | |NULL | |
+----------+-----------+-----------+----------+--------+----------+
~~~
* NULL：表示该列是否可以存储NULL值。
* Key：表示该列是否有索引。PRI表示该列是表主键一部分；UNI表示该列有UNIQUE索引的一部分；MUL表示在列中某个值允许出现多次。
* Default：表示该列是否有默认值，如果有的话值是多少。
* Extra：表示可以获取的与给定列有关的附加信息，例如AUTO\_INCREMENT等。
详细查看语句`SHOW CREATE TABLE 表名\G`用来显示创建表时的CREATE TABLE语句。
##### 修改表名
语法：
~~~
ALTER TABLE <旧表名> RENAME [TO] <新表名>
~~~
~~~
ALTER TABLE tb_dept3 RENAME tb_deptment3;
~~~
将tb\_dept3表名修改为tb\_deptment3
##### 修改字段的数据类型
语法：
~~~
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
~~~
~~~
ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);
~~~
将tb\_dept1表的name字段改为VARCHAR数据类型。
##### 修改字段名
语法:
~~~
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <数据类型>
~~~
~~~
ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
~~~
将tb\_dept1表中location字段改为loc字段并指定数据类型为VARCHAR;
##### 添加字段
语法：
~~~
ALTER TABLE <表名> ADD <字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在字段名]
~~~
FIRST将新添加的字段设置为表的第一个字段，AFTER将新添加的字段指定到已存在的字段之后。
1. 添加无完整性约束条件的字段
~~~
ALTER TBALE tb_dept1 ADD managerId INT(10);
~~~
1. 添加有完整性约束条件的字段
~~~
ALTER TBALE tb_dept1 ADD column1 VARCHAR(12) NOT NULL;
~~~
1. 在表的第一列添加字段
~~~
ALTER TBALE tb_dept1 ADD column2 int (12) FIRST;
~~~
1. 在表的指定列后添加一个字段
~~~
ALTER TBALE tb_dept1 ADD column3 int (11) AFTER name;
~~~
##### 删除字段
语法：
~~~
ALTER TABLE <表名> DROP <字段名>
~~~
~~~
ALTER TABLE tb_dept1 DROP column2;
~~~
##### 修改字段的排列位置
~~~
ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER<字段2>
~~~
~~~
ALTER TABLE tbdept1 MODIFY column1 VARCHAR(12) FIRST;
~~~
##### 更改表的存储引擎
语法:
~~~
ALTER TABLE <表名> ENGINE=<更改后的引擎名>
~~~
~~~
ALTER TABLE tb_deptment3 ENGINE=MyISAM;
~~~
##### 删除外键约束
语法：
~~~
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
~~~
~~~
ALTER TABLE tb_emp9 DROP FOREGIN KEY fk;
~~~
删除名为fk的外键。
### 删除数据表
语法:
~~~
DROP TABLE [IF EXISITS] 表1，表2，...表n；
~~~
~~~
DROP TABLE IF EXISTS tb_dept2;
~~~
#### 14.5 数据类型
MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。
##### 整数类型
数值型数据主要用来存储数字，MySQL提供了多种数值数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，其所需要的存储空间也会越大。整数类型可以添加自增约束条件AUTO\_INCREMENT。
| 类型名称 | 说明 | 存储需求 |
| --- | --- | --- |
| TINYINT | 很小的整数 | 1个字节 |
| SMALLINT | 小的整数 | 2个字节 |
| MEDIUMINT | 中等大小的整数 | 3个字节 |
| INT（INTEGER） | 普通大小的整数 | 4个字节 |
| BIGINT | 大整数 | 8个字节 |
MySQL中的整数类型数据类型
| 数据类型 | 有符号 | 无符号 |
| --- | --- | --- |
| TINYINT | \-128~127 | 0~255 |
| SMALLINT | \-32768~32767 | 0~65535 |
| MEDIUMINT | \-8388608~8388607 | 0~16777215 |
| INT（INTEGER） | \-2147483648~2147483647 | 0~4294967295 |
| BIGINT | \-9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
不同整数类型的取值范围
##### 浮点类型和定点数类型
MySQL使用浮点数和定点数来表示小数。
| 类型名称 | 说明 | 存储需求 |
| --- | --- | --- |
| FLOAT | 单精度浮点数 | 4个字节 |
| DOUBLE | 双精度浮点 | 8个字节 |
| DECIMAL(M,D) ，DEC | 压缩的"严格"定点数 | M+2个字节 |
MySQL中的小数类型
DECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以串存放的。DECIMAL可能的最大值范围与DOUBLE一样，但其有效取值范围由M和D的值决定。
FLOAT类型的取值范围:
有符号：-3.4020823466E+38 ~ -1.175494351E-38
无符号：0和1.175494351E-38 ~ 3.402823466E+38
DOUBLE类型的取值范围：
有符号：-1.7976931348623157E+308 ~ -2.2250738585072014E-308
无符号：0和2.225738585072014E-308 ~ 1.7976931348623157E+308
##### 日期和时间类型
| 类型名称 | 日期格式 | 日期范围 | 存储需求 |
| --- | --- | --- | --- |
| YEAR | YYYY | 1901~2155 | 1个字节 |
| TIME | HH:MM:SS | \-838:59:59 ~ 838:59:59 | 3个字节 |
| DATE | YYYY:MM:DD | 1000-01-01 ~ 9999-12-31 | 3个字节 |
| DATETIME | YYYY:MM:DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 8个字节 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC | 4个字节 |
日期和时间类型可以进行时间转化，如YEAR插入时候是01会识别为1901。
##### 文本字符串类型
字符串类型用来存储字符串数据，除了可以存储字符串之外，还可以存储其他数据，如图片和声音的二进制数据。
| 类型名称 | 说明 | 存储需求 |
| --- | --- | --- |
| CHAR(M) | 固定长度非二进制字符串 | M字节，1<=M<=255 |
| VARCHAR(M) | 变长非二进制字符串 | L+1字节,在此L<=M 和1<=M<=65535 |
| TINYTEXT | 非常小的非二进制字符串 | L+1字节，在此L<2^8 |
| TEXT | 小的非二进制字符串 | L+2字节，在此L<2^16 |
| MEDIUMTEXT | 中等大小的非二进制字符串 | L+3字节，在此L<2^24 |
| LONGTEXT | 大的非二进制字符串 | L+4字节，在此L<2^32 |
| ENUM | 枚举类型，只能有一个枚举字符串值 | 1或2个字节，取决于枚举值的数目(最大值65535) |
| SET | 一个设置，字符串对象可以有零个或多个SET成员 | 1，2，3，4或8个字节，取决于集合成员的数量(最多64个成员) |
CHAR类型和VARCHAR类型
CHAR(M)为固定长字符串，在定义时指定字符串列长，M的范围是0-255。
VARCHAR(M)是长度可变的字符串,M表示最大列长度，范围是0-65535。`实际占用空间是字符串实际长度+1`。
| 插入值 | CHAR(4) | 存储需求 | VARCHAR(4) | 存储需求 |
| --- | --- | --- | --- | --- |
| "" | " " | 4个字节 | "" | 1个字节 |
| "ab" | "ab " | 4个字节 | "ab" | 3个字节 |
| "abc" | "abc" | 4个字节 | "abc" | 4个字节 |
| "abcd" | "abcd" | 4个字节 | "abcd" | 5个字节 |
| "abcdef" | "abcd" | 4个字节 | "abcd" | 5个字节 |
CHAR(4)和VARCHAR(4)的区别
ENUM类型与SET类型
ENUM类型与SET类型的主要区别是，ENUM类型只能在其值中选择一个，而SET类型可以在其值中选择多个。
ENUM类型语法格式
~~~
字段名 ENUM("值1","值2","值3",..."值n")
~~~
SET类型语法格式
~~~
SET("值1","值2","值3",..."值n")
~~~
##### 二进制字符串类型
| 类型名称 | 说明 | 存储需求 |
| --- | --- | --- |
| BIT(M) | 位字段类型 | d大约(M+7)/8个字节 |
| BITARY(M) | 固定长度二进制字符串 | M个字节 |
| VARBINARY(M) | 可变长度二进制字符串 | M+1个字节 |
| TINYBLOB(M) | 非常小的BLOB | L+1 字节，在此L<2^8 |
| MEDIUMBLOB(M) | 小BLOB | L+2 字节，在此L<2^16 |
| MEDIUMBLOB(M) | 中等大小的BLOB | L+3 字节，在此L<2^24 |
| LONGBLOB(M) | 非常大的BLOB | L+4 字节，在此L<2^32 |
MySQL中的二进制字符串类型
#### 14.6 运算符
运算符经常用存在SELECT的查询条件上。
##### 算数运算符
| 运算符 | 作用 |
| --- | --- |
| + | 加法 |
| \- | 减法 |
| \* | 乘法 |
| / | 除法 |
| % | 求余 |
~~~
mysql> SELECT num, num+10,num-3+5;
+-------+---------+--------+
|num |num+10 |nun-3+5 |
+-------+---------+--------+
|64 |74 |66 |
+-------+---------+--------+
1 row in set (0.00 sec)
~~~
##### 比较运算符
比较运算符的结果总是1、0或者NULL。比较结果正确返回1，错误返回0。
| 运算符 | 作用 |
| --- | --- |
| \= | 等于 |
| | 安全等于 |
| <>(!=) | 不等于 |
| <= | 小于等于 |
| \>= | 大于等于 |
| \> | 大于 |
| IS NULL | 是否为NULL |
| IS NOT NULL | 是否不为NULL |
| LEAST | 取最小值 |
| GREATEST | 取最大值 |
| BETWEEN AND | 是否在两值之间 |
| ISNULL | 与IS NULL 作用相同 |
| IN | 是否是IN列中的一个值 |
| NOT IN | 是否不是IN列中的一个值 |
| LIKE | 通配符匹配 |
| REGEXP | 正则表达式匹配 |
~~~
mysql> SELECT 'good'<='god',1<=2;
+-------------+-----------+
|'good'<='god'|1<=2 |
+-------------+-----------+
|0 |1 |
+-------------+-----------+
1 row in set (0.00 sec)
~~~
##### 逻辑运算符
| 运算符 | 作用 |
| --- | --- |
| NOT 或 ！ | 逻辑非 |
| AND 或 && | 逻辑与 |
| OR 或 || | 逻辑或 |
| XOR | 逻辑异或(找不同) |
~~~
mysql> SELECT 1 AND -1,1 AND 0;
+---------+----------+
|1 AND -1 |1 AND 0 |
+---------+----------+
|1 |0 |
+---------+----------+
1 row in set (0.00 sec )
~~~
运算符AND或&&操作所有操作数均为非零值、并且不为NULL时，计算结果为1；当一个或者多个操作数为0时，所得结果为0，其余情况返回值为NULL。
##### 位运算符
| 运算符 | 作用 |
| --- | --- |
| | | 位或 |
| & | 位与 |
| ^ | 位异或 |
| << | 位左移 |
| \>> | 位右移 |
| ~ | 位取反，反转所有比特 |
~~~
mysql> SELECT 10 | 15, 9 | 4 | 2;
+------------+-------------+
|10 | 15 | 9 | 4 | 2 |
+------------+-------------+
|15 | 15 |
+------------+-------------+
1 row in set (0.00 sec)
~~~
10的二进制为1010，15的二进制为1111，位或计算后为1111，就是15。
9的二进制为1001，4的二进制为0100，2的二进制为0010，位或计算后为1111，就是15。
#### 14.7 MySQL函数
MySQL提供了众多功能强大、方便易用的函数。使用这些函数，可以极大地提高用户对数据库的管理效率。
生产环境里由于效率问题不推荐使用函数。
数学函数，例如：
ABS(X)返回绝对值
~~~
mysql>SELECT ABS(2),ABS(-3.3),ABS(-33);
+----------+---------+----------+
|ABS(2) |ABS(-3.3)|ABS(-33) |
+----------+---------+----------+
|2 |3.3 |33 |
+----------+---------+----------+
1row in set (0.01 sec)
~~~
#### 14.8 表数据的操作
MySQL数据表最重要的操作就是对数据的增、删、改、查。
##### 查询数据
MySQL从数据库中查询数据的基本语句为SELECT语句。语法:
~~~
SELECT [*|字段列表(逗号分隔)] FROM <表1>，<表2>...
WHERE [<表达式>
<GROUP BY>
<HAVING>
<ORDER BY>
<LIMIT>
]
~~~
含义如下：
* \[\*|字段列表(逗号分隔)\] 包含星号通配符选择字段列表，表示查询的字段，其中字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段不要加逗号。
* FROM ，...表示查询数据的来源，可以是一个或者多个表。
* WHERE子句是可选项，如果选择该选项，将限定查询行必须满足的条件。
* 该句子告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。
* 该句子告诉MySQL按照什么样的顺序显示查询出来的数据，可以进行的排序有：升序(ASC)，降序(DESC)。
* 该句子告诉MySQL每次查询出来的数据条数。
##### 查询所有字段数据：
~~~
SELECT * FROM 表名;
~~~
使用 \* 通配符将返回所有列
~~~
mysql> SELECT * FROM fruits;
+-------+--------+----------+
|f_id |s_id | f_name |
+-------+--------+----------+
|a1 |101 |apple |
|a2 |103 |apricot |
|b1 |101 |blackberry|
+-------+--------+----------+
~~~
##### 查询指定字段
~~~
mysql> SELECT f_id FROM fruits;
+-------+
|f_id |
+-------+
|a1 |
|a2 |
|b1 |
+-------+
~~~
##### 查询多个字段
~~~
mysql> SELECT f_id,s_id FROM fruits;
+-------+--------+
|f_id |s_id |
+-------+--------+
|a1 |101 |
|a2 |103 |
|b1 |101 |
+-------+--------+
~~~
##### 查询指定记录
数据库中包含大量的数据，根据特殊要求，可能只需要查询表中的指定数据，即对数据进行过滤。语法:
~~~
SELECT 字段名1，字段名2，...字段名n FROM 表名 WHERE 查询条件
~~~
| 操作符 | 说明 |
| --- | --- |
| \= | 相等 |
| <>，!= | 不相等 |
| < | 小于 |
| <= | 小于或者等于 |
| \> | 大于 |
| \>= | 大于或者等于 |
| BETWEEN | 位于两者之间 |
例子：
~~~
SELECT f_name,s_id FROM fruits WHERE s_id = 101 AND f_id = a1;
~~~
##### 带IN关键字查询
IN操作符用来查询满足指定范围内的条件的记录。例子：
~~~
mysql>SELECT s_id,f_id FROM fruits WHERE s_id IN (101,102) ORDER BY s_id;
~~~
##### 带BETWEEN AND的范围查找
BETWEEN AND 用来查询某个范围内的值。需要操作两个参数，即开始值和结束值。例子：
~~~
SELECT s_id,f_id FROM fruits WHERE s_id BETWEEN 101 AND 103;
~~~
##### 带LIKE的字符匹配查询
1. 百分号通配符'%'匹配任意长度的字符，甚至包括零字符。
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id like "10%";
+-------+--------+
|f_id |s_id |
+-------+--------+
|a1 |101 |
|a2 |103 |
|b1 |101 |
+-------+--------+
~~~
匹配右边。
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id like "%3%";
+-------+--------+
|f_id |s_id |
+-------+--------+
|a2 |103 |
+-------+--------+
~~~
左右都匹配。
1. 下划线通配符'\_'，一次只能匹配一个字符。
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id like "__3";
~~~
查询sid以3结尾一共有三个字符的数据。
##### 查询空值
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id IS NULL;
~~~
查询s\_id为NULL的数据。
##### AND 多条件查询
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id = 103 AND f_id != a2;
~~~
查询s\_id为103 ，f\_id 不为a2的数据。
##### OR 多条件查询
~~~
mysql>SELECT f_id,s_id FROM fruits WHERE s_id = 103 AND s_id = 101;
~~~
查询s\_id为103或者s\_id为101的数据。
##### 查询去除重复值
使用`DISTINCT`关键字只是MySQL消除重复的记录值。
~~~
mysql> SELECT DISTINCT 字段名 FROM 表名;
~~~
##### 对查询结果排序
SELECT语句中通过`ORDER BY` 子句对查询结果排序。默认升序。
~~~
mysql> SELECT s_id FORM fruits ORDER BY s_id;
~~~
##### 多列排序
有时候需要先按s\_id排序，再按f\_id排序，就需要多列排序。
~~~
mysql> SELECT s_id,f_id FORM fruits ORDER BY s_id,f_id;
~~~
##### 指定升降序
ORDER BY 子句默认为升序。升序使用ASC关键字，降序使用DESC关键字。
~~~
mysql> SELECT s_id,f_id FORM fruits ORDER BY s_id DESC; //指定降序
~~~
##### 分组查询
分组查询是对查询数据按照某个或多个字段进行分组。使用 `GROUP BY` 关键字。
GROUP BY 通常和集合函数一起使用：例如MAX()、MIN()、COUNT()、SUM()、AVG()。
~~~
mysql> SELECT s_id,COUNT(*) as Tatal FROM fruits GROUP BY s_id;
+----------+-----------+
| s_id |Tatal |
+----------+-----------+
|101 |3 |
|102 |2 |
|103 |3 |
|104 |3 |
+----------+-----------+
~~~
##### HAVING 过滤分组
GOURP BY 可以和HAVING一起限定显示记录所需满足的条件，只有满足记录才会被显示。
~~~
mysql> SELECT s_id,COUNT(*) as Tatal FROM fruits GROUP BY s_id HAVING COUNT(*) > 2;
+----------+-----------+
| s_id |Tatal |
+----------+-----------+
|101 |3 |
|103 |3 |
|104 |3 |
+----------+-----------+
~~~
##### 在GROUP BY 子句中使用 WITH ROLLUP
使用`WITH ROLLUP`关键字之后，在所有查询出来的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
~~~
mysql> SELECT s_id,COUNT(*) as Tatal FROM fruits GROUP BY s_id WITH ROLLUP;
+----------+-----------+
| s_id |Tatal |
+----------+-----------+
|101 |3 |
|102 |2 |
|103 |3 |
|104 |3 |
|NULL |11 |
+----------+-----------+
~~~
##### 多字段分组
使用`GROUP BY` 可以对多个字段进行分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组，以此类推。
~~~
mysql> SELECT * FROM fruits group by s_id ,f_name;
+--------+--------+-----------+--------+
|f_id |s_id |f_name |f_price |
+--------+--------+-----------+--------+
|a1 |101 |apple |5.20 |
|b1 |101 |blackberry |10.20 |
|c0 |101 |banana |3.20 |
|t1 |102 |grape |5.30 |
|t2 |102 |orange |11.20 |
............
~~~
##### GROUP BY和ORDER BY 一起使用
~~~
mysql> SELECT * FROM fruits group by s_id ,f_name ORDER BY f_price;
~~~
##### LIMIT 限制查询结果的数量
SELECT 返回所有匹配的行，可能是表中所有行。当只需要其中一行或前几行时，使用`LIMIT`关键字。
语法：
~~~
LIMIT [位置偏移量], 行数
~~~
~~~
mysql> SELECT * FROM fuits LIMIT 4, 3;
~~~
查询第5行开始的3条记录。
##### 连接查询
连接是关系型数据库模型的主要特点。连接查询是关系数据库中最重要的查询，主要包括内连接、外连接等。通过连接运算符可以实现多个表查询。
##### 内连接
使用`INNER JOIN`关键字，查询一个或者多个表中的数据，只有满足条件的记录才能出现再结果关系中。
~~~
mysql> SELECT suppliers.s_id,s_name,f_name,f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;
~~~
查询suppliers的s\_id,fruits的s\_id、f\_name、f\_price，条件是suppliers的s\_id=fruits的s\_id。
这时候的`ON`等同于其他句子里`WHERE`的作用。
两张表都是一张表需要连接的情况属于特殊的内连接:
~~~
mysql>SELECT f1.f_id,f1.f_name FROM fruits as f1,fruits as f2 where f1.s_id = f2.s_id AND f2.f_id = 'a1';
~~~
`as`是为表名取别名。
##### 外连接查询
外连接将查询多个表中相关联的行，返回查询集合不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个连接表(全外连接)中所有数据行。
* LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
* RIGHT JOIN（右连接）：返回包括右表的所有记录和左边中连接字段相等的记录。
左连接例子：
创建orders表
~~~
CREATE TABLE orders
(
o_num int NOT NULL AUTO_INCREMENT,
o_date datetime NOT NULL,
c_id int NOT NULL,
PRIMARY KEY (o_num)
)
~~~
插入记录
~~~
INSERT INTO orders(o_num,o_date,c_id) VALUES
(30001,"2008-07-01",10001),
(30002,"2008-09-01",10003),
(30003,"2008-09-21",10004),
(30004,"2008-10-03",10005),
(30005,"2008-09-11",10001),
~~~
左连接
~~~
mysql>SELECT customers.c_id,orders.o_num FROM customers LEFT JOIN orders ON customers.c_id = orders.c_id;
+---------+------------+
|c_id |o_num |
+---------+------------+
|10001 |30001 |
|10001 |30005 |
|10002 |NULL |
|10003 |30002 |
|10004 |30003 |
+---------+------------+
~~~
显示了5条记录，在customers.c\_id = orders.c\_id的条件下，customers一共有五条记录全部显示出来了，其中10002这条记录，将左表customers的记录展示了出来，右表orders没有匹配上。所以这条记录查询的右表orders的o\_num用空值NULL展示。
右连接与左连接相反，有表显示全部行，左表没有匹配上的控制NULL替代。
##### 子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。
##### 带ANY、SOME关键字的子查询
带有`任何一个`的意义。
~~~
mysql> SELECT * FROM tb11 WHERE num1 > ANY(SELECT num2 FROM tb12);
~~~
查询出tb11表中num1大于任何一个tb12表中num2的记录。
`SOME`关键字和`ANY`关键字是同义词。
##### 带ALL关键字的子查询
带有`每一个/所有`的意义
~~~
mysql> SELECT * FROM tb11 WHERE num1 > ALL(SELECT num2 FROM tb12);
~~~
查询出tb11表中num1大于每一个tb12表中num2的所有记录。
##### 带EXITST关键字的子查询
用于判断子查询是否至少返回一行，如果至少返回一行，EXITST结果为true。则外层查询语句将进行查询。
~~~
mysql> SELECT * FROM tb11 WHERE EXITST (SELECT num2 FROM tb12);
~~~
如果SELECT num2 FROM tb12有结果，则外层SELECT \* FROM tb11 语句将进行查询。
`NO EXITST` 与`EXITST`使用方法相同，结果相反。
##### 带有IN关键字的子查询
IN关键字进行查询时，内查询仅仅返回一个数据列，为外层查询提供比较操作。
~~~
mysql> SELECT c_id FROM orders WHERE o_num IN (SELECT O_num FROM orderitems WHERE f_id = 'c0');
~~~
查找orders的o\_num字段，要求等于orderitems中f\_id = 'c0' 记录的o\_num字段。
##### 合并查询结果
UNION关键字用于合并数据类型和列数相同的查询。默认删除重复记录，带ALL时不删除重复记录也不排序。
语法:
~~~
SELECT column ,.... FROM table1
UNION [ALL]
SELECT column ,.... FORM table2
~~~
##### 为表和字段取别名
表别名语法：
~~~
表名 [as] 别名
~~~
字段别名语法：
~~~
列名 [as] 别名
~~~
##### 正则表达式匹配
正则表达式匹配结果使用REGEXP关键字，其无法使用索引提高效率。有兴趣的同学可以自己练习使用REGEXP匹配查询结果。
##### 插入数据
使用INSERT语句插入数据,要求指定表名称和插入到新记录中的值(有默认值的可以不用)。
语法：
~~~
INSERT INTO table_name (column_list) VALUES (values_list)
~~~
table\_name为表名，column\_list为需要插入的列(逗号分隔)，values\_list为需要插入的数据(逗号分隔)。
column\_list与values\_list需要数量相等并一一对应。
~~~
mysql> INSERT INTO person (id,name) VALUES (1,"张三");
Query OK, 1 row affected (0.00 sec)
~~~
一次插入多条。
~~~
mysql> INSERT INTO person (id,name) VALUES (1,"张三"),(2,"李四"),(3,"赵财神")....;
Query OK, 1 row affected (0.05 sec)
~~~
将查询的结果插入到表中。
~~~
mysql>INSERT INTO table_name1 (column_list1) SELECT (column_list2) FROM table_name2 WHERE(condition)
~~~
##### 更新数据
对表中原有数据进行更新操作使用`UPDATE`关键字。
语法：
~~~
UPDATE table_name SET field1=new-value1 [ WHERE <condition> ]
~~~
例子：
~~~
mysql> UPDATE person set age = 15 , name = 'LiXiao' WHERE id = 11;
~~~
##### 删除数据
从数据表中删除数据使用DELETE语句，允许WHERE子句指定删除条件。
语法：
~~~
DELETE FROM table_name [ WHERE <condition> ]
~~~
例子：
~~~
mysql>DELETE FROM person WHERE id = 111;
~~~
#### 14.9 索引
索引用于快速查找出在某个列中有特定值的行。不使用索引，MySQL必须从第1条记录开始读完整个表，直接找出相关的行。表越大，查询数据花费时间越多。如果要查询的列有一个索引，MySQL能快速到达某个位置去搜寻数据文件，而不必查看所有数据。索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。
##### 索引的含义和特点
索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用记录。使用索引用于快速找出某个或多个列中有一特定值的行，对相关列使用索引是提高查询操作速度的最佳途径。
索引的有点：
1. 通过创建唯一索引，可以保证数据表中每一行数据的唯一性。
2. 可以大大加快数据的查询速度，这也是创建索引的最主要的原因。
3. 在实现数据的参考完整性方面，可以加快表与表之间的连接。
4. 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。
索引的不利：
1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
2. 索引需要占磁盘空间，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果有大量索引，索引文件可能逼数据文件尺寸更快达到最大文件尺寸。
3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
##### 索引的分类
##### 1.普通索引和唯一索引
普通索引是MySQL中的基本索引型，允许在定义索引的列中插入重复值和空值。
唯一索引，索引的值必须唯一，但允许有空值。如果是组合索引，则组合索引的多列值必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
##### 2.单列索引和组合索引
单列索引就是一个索引只包含单个列，一个表可以有多个单列索引。
组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时（最左前缀原则），索引才会被使用。
##### 3\. 全文索引
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和控制。可以在TEXT、CHAR、VARCHAR类型上建立全文索引，只有MyISAM引擎可以使用。
##### 4\. 空间索引
空间索引是对空间数据类型的字段建立的索引，有4种：GEOMETRY、POINT、LINESTRING、POLYGON。有兴趣的同学可以自己学习。
##### 索引的设计原则
索引设计不合理或者缺少索引会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。
1. 索引并非越多越好，一个表中如有大量索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新。
2. 避免对经常更新的表进行过多的索引，并且索引的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
3. 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还短，索引可能不会产生优化效果。
4. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如性别只有”男，女“两个不同值，就无须建立索引。这会严重降低效率。
5. 当唯一性是某种数据本身的特征，指定唯一索引。
6. 在频繁进行排序或分组的列上建立索引，如果排序待排序的列有多个，可以在这些列上建立组合索引。
##### 创建普通索引
~~~
mysql> CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication) //建立普通索引
)
~~~
`EXPLAIN`语句查看索引是否正在使用。
~~~
mysql> explain select * from book where year_publication = 1990 \g;
*** 1. row ***
id: 1
select_type:SIMPLE
table:book
type:ref
possible_keys:year_publication
key:year_publication
key_len:1
ref:const
rows:1
Extra:
1 row in set (0.05 sec)
~~~
EXPLAIN语句各行解释
* select\_type：指定所使用的SELECT查询类型，有SIMPLE、PRIMARY、UNION、SUBQUERY等。
* table：指定数据库读取的数据表名称。
* type：指定了本数据表与其他数据表之间的关联关系，有system、const、eq\_ref、ref、range、index和ALL。
* possible\_keys：给出MySQL在搜索数据记录时可选的各个索引。
* key：MySQL实际选择的索引。
* key\_len：给出索引按字节计算的长度，key\_len越小，越快。
* ref：给出了关联关系中另一个数据表里的数据列的名字。
* extra：提供了与操作相关的信息。
##### 创建唯一索引
使用`UNIQUE`语句
~~~
mysql> CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
UNIQUE INDEX UniIdx(bookid) //建立唯一索引
)
~~~
##### 建立单列索引
~~~
mysql> CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX SingleIdx(bookname) //建立单列索引
)
~~~
##### 建立组合索引
~~~
mysql> CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX MultiIdx(bookid,bookname) //建立组合索引，由两个字段组成
)
~~~
最左前缀原则：WHERE子句查询时候从左到右查询才能命中索引。例如索引MultiIdx使用最左前缀的命中的情况：
~~~
mysql>SELECT * FROM book WHERE bookid = 1 AND bookname = '语文教材';
mysql>SELECT * FROM book WHERE bookid = 1;
~~~
索引MultiIdx未使用最左前缀的命中的情况：
~~~
mysql>SELECT * FROM book WHERE bookname = '语文教材' AND bookid = 1;
mysql>SELECT * FROM book WHERE bookname = '语文教材';
~~~
##### 全文索引
全文索引需要MyISAM引擎
~~~
mysql> CREATE TABLE t4
(
id INT NOT NULL,
name VARCHAR(255) NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
)ENGINE=MyISAM;
~~~
##### 空间索引
有兴趣的同学可以自行查阅资料学习。
##### 在已经存在的表上创建索引
1. 使用ALTER TABLE 语句创建
~~~
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (column[length]) [ASC|DESC]
~~~
~~~
mysql>ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));
~~~
1. 使用CREATE TABLE 语句创建
~~~
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column[length]) [ASC|DESC]
~~~
~~~
mysql>CREATE INDEX bkNameIdx ON book(bookname);
~~~
##### 删除索引
1. 使用ALTER TABLE 语句删除
~~~
ALTER TABLE table_name DROP INDEX inex_name;
~~~
~~~
mysql>ALTER TABLE book DROP INDEX UniqidIdx;
~~~
1. 使用DROP INDEX 语句删除
~~~
DROP INDEX index_name ON table_name;
~~~
~~~
mysql> DROP INDEX bkAuAndInfoidx On book;
~~~
##### 查看指定表中创建的索引
使用`SHOW INDEX` 语句
~~~
mysql> SHOW INDEX FROM book \G
*** 1. Row ***
Table: book
Non_unique:1
Key_name:year_publication
Seq_in_index:1
Column_name:year_publication
cardinality:0
Sub_part:NULL
Packed:NULL
NULL:
Index_type:BTREE
Comment:
Index_comment:
~~~
参数含义：
* Table：表示创建索引的表。
* Non\_unique：1待表是非唯一索引，0代表是唯一索引。
* Key\_name：表示索引的名称。
* Seq\_in\_index：表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引定义中的顺序号。
* Column\_name：表示定义索引的列字段。
* Sub\_part：表示索引的长度。
* Null：表示该字段是否能为空值。
* Index\_type：索引类型。
#### 14.10 存储过程及触发器
##### 存储过程
存储过程就是一条或者多条SQL语句的集合。有兴趣的同学可以自己学习。
~~~
CREATE PROCEDURE Proc()
BEGIN
SELECT * FROM fruits；
...
END;
~~~
##### 触发器
MySQL触发器和存储过程一样，是嵌到MySQL的一段程序。有兴趣的同学可以自行学习。
触发器是个特殊的存储过程，不同的是，执行存储过程需要使用CALL语句调用，而触发器不需要，只要当一个预定义的事件发生，就会被MySQL自动调用。用于满足复杂的业务规则或要求。比如：可以根据客户当前的账户状态，控制是否允许插入新订单。
#### 14.11 用户管理
MySQL是一个多用户数据库，具有强大的访问控制系统，可以为不同的用户指定允许的权限。
##### 权限表
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库中。存储账户权限信息表主要有：user、db、host、tables\_priv、columns\_priv和procs\_priv。
user表是全局级的，其中字段主要分类:
1. 用户列：用户记录和校对。
2. 权限列：决定用户权限。
3. 安全列：用户权限的更多配置。
4. 资源控制列：限制用户使用的资源。
db表和host表，存储了用户对某个数据库的操作权限，字段分类：
1. 用户列：表示某个主机用户对某个库的操作权限。
2. 权限列：决定用户权限。
#### 14.12 MySQL日志
MySQL日志记录了MySQL数据库日常操作和错误信息。日志分类：
1. 错误日志：记录MySQL服务的启动、运行、停止MySQL服务时出现的问题。
2. 查询日志：记录建立的客户端连接和执行的语句。
3. 二进制日志：记录所有更改数据的语句，可以用于数据复制。
4. 慢查询日志：记录所有执行时间超过long\_query\_time的所有查询或不使用索引的查询。
##### 二进制日志配置
在my.ini中
~~~
[mysqld]
log-bin [=path/[filename]] //配置二进制日志地址、名称
expire_logs_days = 10 //配置过期自动删除文件时间，单位日
max_binlog_size = 100M //配置单个二进制日志大小，超过会发生滚动新建另一个二进制日志
~~~
##### 错误日志
在my.ini中
~~~
[mysqld]
log-error=[path/filename]
//如果没有指定错误日志路径，会默认在数据库目录里，文件名叫hostname.err
~~~
##### 通用查询日志
在my.ini中
~~~
[mysqld]
log=[path/filename]
//如果没有指定错误日志路径，会默认在数据库目录里，文件名叫hostname.log
~~~
##### 慢查询日志
在my.ini或者my.cnf中
~~~
[mysqld]
log-slow-queries=[path/filename]
//如果没有指定错误日志路径，会默认在数据库目录里，文件名叫hostname-slow.log
long_query_time=n //指定慢日志记录时间
~~~
慢日志经常用来优化数据查询性能。
#### 14.13 MySQL体系结构与存储引擎
MySQL体系结构可以分为两层，`MySQL Server层`和`存储引擎层`。MySQL Server层包括`连接层`和`SQL`层。
应用程序通过接口来连接MySQL。最先连接处理的是**连接层**，连接层包括`通信协议`、`线程处理`、`用户名密码认证`三个部分。通信协议负责检测客户端版本是否兼容MySQL服务端。线程处理是指每一个连接请求都会分配一个对应的线程，相当于一条SQL对应一个线程，一个线程对应一个逻辑CPU，并会在多个逻辑CPU之间切换(多个控制流之间切换)。用户名密码认证验证账号和密码。
**SQL层**包括权限判断、查询缓存、解析器、预处理、查询优化器、缓存、执行计划。
权限判断可以验证用户对某个库、表、行的权限。缓存查询通过`query cache`进行操作，如果在query cache中，则直接返回结果给客户端(生产环境中建议关闭)。查询解析器针对SQL语句进行解析，判断语法是否正确。预处理器对解析器无法解析的语义进行处理。优化器对SQL进行改写和相应的优化，并生成最优的执行计划。然后调用程序的API接口通过引擎层访问数据。
**存储引擎层**是MySQL数据库区别于其他数据库最核心的一点。
##### Query Cache 详解
生产中建议关闭Query Cache，因为它只能缓存静态数据，一旦数据发生变化，经常读写，Query Cache就没有必要。
关闭的方法：
将query\_cache\_type设置成off。
##### InnoDB体系结构
InnoDB体系结构实际上是由内存结构、线程、磁盘文件这三层组成。
这里的线程就是一条数据从内存到磁盘的过程。
:-: 
InnoDB体系结构
##### InnoDB 存储结构
InnoDB逻辑存储单元主要分为表空间、段、区和页。
层级关系为table-->segment-->extent(64个page，1MB)->page。
:-: 
InnoDB存储结构
1. 表空间
InnoDB存储引擎表中所有数据都是存储在表空间中。表空间中的`系统表空间`以ibdata1来命名，在安装数据库初始化数据时系统会创建一个ibdata1的表空间文件，它会存储所有数据的信息以及回滚段(undo)的信息。MySQL5.6之后，undo表空间可以通过参数单独设置存储位置了，可从ibdata1中独立出来。Innodb\_data\_file\_path负责定义系统表空间的路径、初始化、自动扩展策略。数据库默认的自动扩展大小是64MB。
数据库默认的ibdata1的大小是10MB，这里建议不要使用10MB的默认大小，在遇到高并发事务时，会受到不小的影响。建议把ibdata1的初始值调整为1GB。
除了系统表空间，还有`独立表空间`，设置参数innodb\_file\_per\_table=1即可。目前MySQL默认使用的都是独立表空间文件，就是每个表就有自己的表空间文件，而不用存储在ibdata1中。独立表空间存储对应表的B+树数据、索引和插入缓冲等信息，其余信息还是存储在默认表空间。
独立表空间的每个表都有自己的表空间，并且可以实现表空间的转移，回收表空间也很方便。不好的地方在于每个表文件都有.frm和.ibd文件两个文件描述符，如果单表增长过快就很容易出现性能问题。
`共享表空间`的数据和文件放在一起方便管理。但是共享表空间无法在线回收空间，共享表空间想要回收，需要将InnoDB表中的数据备份、删除原表，然后再把数据导回到与原表结构一样的新表中。统计分析、日志类系统不适合用共享表空间。
综合考虑，独立表空间效率、性能比共享表空间高一些。默认使用独立表空间。
`临时表空间`是把临时表数据从系统表空间抽离出来形成自己的独立表空间，默认大小为12MB。
`通用表空间`是多个表放在同一个表空间中，可以根据活跃度划分表，存放在不同的磁盘上，减少metadata的存储开销。生产上很少使用。
1. 段
表空间由段组成，也可以把一个表理解为多个段。通常有数据段、回滚段、索引段等。每个段由N个区和32个零散页组成，段空间扩展是以区为单位进行扩展的。
2. 区
区是由连续的页组成的，是物理上连续分配的一段空间，每个区的大小固定是1MB。
3. 页
InnoDB的最小物理存储分配单位是page，由数据回滚页等。一般情况下，一个区由64个连续页组成，页默认大小是16KB。一个page页会默认预留1/16的空间用于更新数据。一个页最少可以存两行数据。虚拟最小行和虚拟最大行用来限定记录的范围，以此来保证B+tree节点是双向链表结构。
:-: 
1页的结构
`整体页的结构`有记录页头信息的、记录状态信息和首个记录位置的、虚拟行记录限定记录边界的、存储实际行数据信息的、空闲空间、存放记录的相对位置的、保证页完整写入磁盘的。
1. 行
页里记录着行记录的信息，InnoDB存储引擎是面向行的。也就是数据是按照行记录的。
##### 内存结构
MySQL内存结构分为`SGA(系统全局区)`和`PGA(程序缓存区)`。可以通过数据库内存参数分配。
系统全局区(SGA)设置参数:
1. innodb\_buffer\_pool
用途：用来缓存innoDB表的数据、索引、插入缓冲、数据字典等信息。
2. innodb\_log\_buffer
用途：事务在内存的缓冲，即redo log buffer的大小。
3. Query Cache
用途：高速查询缓存，生产建议关闭。
4. key\_buffer\_size
用途：只用于MyISAM存储引擎表，缓存MyISAM存储。
5. innodb\_additional\_mem\_pool\_size
用途：用来保存数据字典信息和其他内部数据结构的内存池的大小。MySQL5.7.4后被移除了。
程序缓存区(PGA)设置参数：
1. sort\_buffer\_size
用途：主要用于SQL语句在内存中的临时排序。
2. join\_buffer\_size
用途：表连接使用，用于BKA(一种join连接优化)。
3. read\_buffer\_size
用途：表顺序扫描的缓存，只能应用于MyISAM表存储引擎。
4. read\_rnd\_buffer\_size
用途：MySQL随机读缓冲区大小，用于做mrr(一种对主键索引集合排序的优化)。
特殊：
1. tmp\_table\_size
用途：SQL语句在排序或者分组时没有用到索引，就会使用临时表空间。
2. max\_heap\_table\_size
用途：管理heap、memory引擎表。
##### Buffer 状态及其链表结构
page是InnoDB磁盘I/O的最小单位，数据是存放在page中，对应到内存就是一个个buffer。
buffer分三个状态：
* free buffer：空闲的buffer
* clean buffer：内存和磁盘数据一致的buffer
* dirty buffer：内存中新写入的信息还未刷进磁盘的buffer
因此由三个不同的buffer集合形成了三条双向链表：
* free list：空闲的buffer链表
* lru list：内存与磁盘一致最近用到的buffer链表
* flush list：将内存和磁盘数据不一致的buffer形成链表方便线程将其刷到磁盘。
##### 各大线程及其作用
InnoDB存储引擎属于多线程模型，后台有多种线程，负责处理不同的任务。
`master线程`是主线程，优先级最高。内部有主循环loop、后台循环background loop、刷新循环flush loop、暂停循环suspend loop。
主循环loop每1s操作：
1. 日志缓冲刷新到磁盘，即使这个事务还没有提交。
2. 刷新脏页到磁盘。
3. 执行合并插入缓冲的操作。
4. 产生checkpoint（检查点，一种刷脏数据的机制）。
5. 清除无用的table cache。
6. 如果没有用户活动就切换到后台循环
主循环loop每10s操作：
1. 日志缓冲刷新到磁盘，即使事务还没有提交。
2. 执行合并插入缓冲的操作。
3. 刷新脏页到磁盘。
4. 删除无用的undo页。
5. 产生checkpoint。
`read thread` 线程是数据库读线程。
`write thread` 线程是数据库写线程。
`redo log thread` 线程负责把日志缓冲刷新到redo log文件中。
`change buffer thread` 线程负责把插入缓冲中的内容刷新到磁盘。
##### 内存刷新机制
MySQL讲究`日志先行`，就是一条DML语句(对表操作的语句)进入数据库后，都会先写日志，再写数据文件。
1. redo log
redo log 是重做日志文件。用于记录事务操作的变化，记录的是数据修改后的值，不管事务是否提交都会记录下来。用来保证数据完整性。默认情况下至少有两个redo log文件，在磁盘上用ib\_logfile(0~N)命名。
redo log 刷到磁盘的条件：
* 通过innodb\_flush\_log\_at\_trx\_commit参数来控制将redo log buffer中的数据写入redo log文件。
* master thread：每秒进行刷新。
* redo log buffer：超过一半时会进行刷新。
2. binlog
DML语句即会写redo log文件，也会写binlog二进制文件。用于备份恢复和主从复制。由sysc\_binlog参数决定，事务提交后，MySQL会让Filesystem自行决定什么时候同步，或者等cache满了之后才同步到磁盘。
总结脏页的刷新条件：
1. 重做日志ib\_logfile文件写满后，会执行checkpoint触发脏页刷新。
2. 通过innodb\_max\_dirty\_pages\_pct参数控制。指的在buffer pool中脏页所占百分比达到设置之后进行刷新。
3. 通过innodb\_adaptive\_flushing参数控制。该参数影响每秒刷新脏页的数目。
##### InnoDB三大特性
1. 插入缓冲
插入缓冲的作用是把普通索引上的DML操作从随机I/O变成顺序I/O，提高I/O效率。
2. 两次写
两次写保证写入的安全性，防止在MySQL实例发生宕机时，InnoDB发生数据页部分页写的问题。
3. 自适应哈希
InnoDB存储引擎有一个机制，可以监控索引的搜索，如果InnoDB注意到查询可以通过建立哈希索引得到优化，就会自动完成这件事。
#### 14.14 事务的隔离级别
InnoDB有4中隔离级别，默认是可重复读。
1. 读未提交。在其中一个事务里读到其他事务未提交的变化。这种读取称为脏读。
2. 读已提交。在其中一个事务里读到其他事务已经提交的变化。这种读取也称为不可重复读，允许发生幻读现象。
3. 可重复读。在其中一个事务结束前，可以反复读取事务刚开始看到的数据，并一致不会发生变化，避免了脏读、不可重复读、幻读。
4. 串行。在每个读的数据行上都加表级共享锁，每次写数据都加表级排它锁。这样会造成并发能力降低，但是保证了数据完整性。不建议使用在生产环境中。
脏读：其中一个事务读到其他事务还未提交的数据。
不可重复读：在其中一个事务中读取到其他事务针对旧数据的修改记录。
幻读：在其中一个事务中，读到了其他事务新增的数据。
##### InnoDB的锁类型
1. 读锁（共享锁）
一个事务获取一个数据行的读锁，其他事务可以获得该行对应的读锁，不能获得写锁。
2. 写锁（排他锁）
一个事务获取了一个数据行的写锁，其他事务不能再获取该行得其他锁。
3. MDL锁
一个会话开启了事务后自动开启一个MDL锁 (表级别)，其他会话不能进行DDL操作(库级操作)。
4. 意向锁
意向锁是表级锁，分为`意向共享锁`和`意向排他锁`。意向共享锁是在给一个数据行加共享锁之前获取的；意向排他锁是在给一个数据行加排他锁之前获取的。
##### InnoDB行锁种类
1. 单行记录的锁
用于在单行操作时候加锁。
2. 间隙锁(Gap lock)
为了避免幻读，加入了间隙锁，锁定记录数据的范围，不包含记录本身。即不允许在此范围内插入任何数据，常用于where指定范围的语句。
3. Next-key locks
是记录锁和间隙锁的组合，当InnoDB扫描锁记录时，会先对选中的索引记录加上记录锁，再对索引记录两边的间隙上加上间隙锁。
4. 锁等待和死锁
锁等待是指一个事务过程中产生的锁，其他事务需要等待上一个事务释放锁后才能使用该资源。一直不释放超过锁等待时间会报锁超时错误。
死锁是指两个或两个以上的事务在执行过程中争抢资源造成的一种相互等待的现象，例如：
A会话执行update tt set name ='aaa' where score=60
B会话执行update tt set name ='a' where score=70
A会话执行update tt set name ='bb' where score=70
B会话执行update tt set name ='aa' where score=60
InnoDB存储引擎可以自动检测死锁，并自动回滚该事务。