一、MySQL 操作集

• 一、MySQL 操作集
• 二、MySQL 数据类型
• 三、MySQL 函数
• 四、MySQL Explain
• 五、MySQL 索引优化一
• 六、MySQL 索引优化二
• 七、SQL 优化
• 八、MySQL 锁与事务
• 九、MySQL 索引数据结构
• 十、MySQL 外键

---

一、用户操作

• MySQL 数据库，最高权限管理者是 root 用户。
  包括 select、update、delete、grant 等权限操作。

---

1. 查看用户

-- 查看所有用户。
SELECT User,Host FROM mysql.user;

SELECT DISTINCT User FROM mysql.user;

-- 查看当前登录用户。
SELECT USER();

---

2. 创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
-- username：创建的用户名。
-- host：指定用户在那台主机上可以登陆（`localhost`为仅限于本机，`%`通配符为任意远程主机）。
-- password：指定用户密码（密码可以为空，为不需要密码）。

-- 先刷新一下权限表。
flush privileges;

-- 创建`test`用户，并指定`本主机`可访问。
CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';

-- 创建`test`用户，并指定`192.168.1.10开头`可访问。
CREATE USER 'test'@'192.168.1.10_' IDENTIFIED BY 'test';
-- 创建`test`用户，并指定`192.168.1.开头`可访问。
CREATE USER 'test'@'192.168.1.%' IDENTIFIED BY 'test';

-- 创建`test`用户，并指定`任何主机`可访问。
CREATE USER 'test'@'%' IDENTIFIED BY 'test';

-- 密码可以为空。
CREATE USER 'test'@'%' IDENTIFIED BY '';

-- 不设置密码。
CREATE USER 'test'@'%';

---

3. 删除用户

DROP USER 'username'@'host';

DROP USER 'test'@'%';

---

4. 修改用户

RENAME USER '用户名'@'IP地址' TO '新用户名'@'IP地址';

---

5. 用户授权

GRANT '权限' ON 'db_name'.'table_name' TO 'username'@'host';
-- 权限：权限符（多个`,`号分割）。
-- db_name：库名（`*`通配符任意数据库）。
-- table_name：表名（`*`通配符任意表）。

-- 授权`test`用户，对`test.user`表，有`查、增、改`权限。
GRANT SELECT,INSERT,UPDATE ON `test.user` TO 'test'@'localhost';

-- `ALL`所有的权限，除了`grant`权限（`grant`命令是`root`用户才有的）。
GRANT ALL ON `test.user` TO 'test@localhost';

-- 授权`test`用户，对`test`数据库所有表，有`增、删、改、查`权限。
GRANT ALL ON test.* TO 'test'@'%';

-- 授权`test`用户，对`所有数据库`，有`增、删、改、查`权限。
GRANT ALL ON *.* TO 'test'@'%';

---

5.1 授权 GRANT 权限

• 注意：以上用户授权命令，不能给其它用户再授权。如果想让该用户可以授权，用以下命令。

GRANT '权限' ON 'db_name'.'table_name' TO 'username'@'host' WITH GRANT OPTION;

GRANT ALL ON *.* TO 'test'@'%' WITH GRANT OPTION;

---

5.2 查看用户权限

SHOW GRANTS FOR 'test'@'%'; 

---

5.2 撤销用户权限

REVOKE '权限' ON 'db_name'.'table_name' FROM 'username'@'host';

-- 授权1。
GRANT SELECT ON *.* TO 'test'@'%';
-- 撤权1。
REVOKE SELECT ON *.* FROM 'test'@'%';

-- 撤权2。
REVOKE INSERT ON *.* FROM 'test'@'%';

-- 撤权3。
REVOKE ALL ON *.* FROM 'test'@'%';

• 注意：下面两种撤销都无效。

-- 授权1。
GRANT SELECT ON `test.user` TO 'test'@'%';
-- 撤权1。
REVOKE SELECT ON *.* FROM 'test'@'%';

-- 授权2。
GRANT SELECT ON *.* TO 'test'@'%';
-- 撤权2。
REVOKE SELECT ON `test.user` FROM 'test'@'%';

---

6. 设置密码

---

6.1 登录状态设置

SET PASSWORD FOR 'username'@'host' = PASSWORD('新密码');

-- 设置指定用户的密码。
mysql>SET PASSWORD FOR 'test'@'%' = PASSWORD("123456");

-- 设置当前登录用户的密码。
mysql>SET PASSWORD = PASSWORD("123456");

---

-- 修改表方式设置新密码。
UPDATE mysql.user SET password=PASSWORD('新密码') WHERE User='root';

mysql>UPDATE mysql.user SET password=PASSWORD('123456') WHERE User='test' AND Host='localhost';
-- 刷新权限表。
mysql>FLUSH PRIVILEGES;

---

6.2 未登录状态设置

mysqladmin -u'用户名' -p'旧密码' password '新密码'

mysqladmin -u test -p test -password 123456

---

6.3 忘记密码

# 1、关闭正在运行的`MySQL`服务。

# 2、打开`DOS`窗口，切换目录。
`cd mysql\bin`

# 3、输入命令回车。
`mysqld --skip-grant-tables`
# `--skip-grant-tables`是启动`MySQL`服务的时候，跳过权限表认证。
# 3.1、my.cnf文件加入配置。
`vim /etc/my.cnf` > `skip-grant-tables`
# 3.2、重启`MySQL`服务。
`systemctl restart mysqld` 或 `service mysqld restart`

# 4、再开一个`DOS`窗口（因为刚才那个`DOS`窗口已经不能动了）切换目录。  
`cd mysql\bin`

# 5、输入`mysql`回车，如果成功将出现`MySQL`提示符`>`。
  
# 6、连接权限数据库。
`show databases;` 
`use mysql;`

# 7、改密码。
`update user set password=password("123456") where user="root";`。

# 8、刷新权限。
`flush privileges;`

# 9、退出。
`quit`

# 9.1、修改`MySQL`配置文件。
# 注释掉`skip-grant-tables`。
# 9.2、并重启`MySQL`服务。
`systemctl restart mysqld`。

# 10、注销`MySQL`重启再进入，使用用户名`root`和刚才设置的新密码`123456`重新登录。

# 11、测试不带密码登录`MySQL`，发现还是能够登陆上。
# 但显示数据库时只能看到两个数据库了，说明重启之后跳过密码验证已经被取消了（是因为数据库里存在无须口令的账户）。

---

二、数据库操作

---

1. 创建库

-- 创建数据库。
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] '库名'
[[DEFAULT] CHARACTER SET[=]'字符集名']
[[DEFAULT] COLLATE[=]'校对规则名'];

-- 创建`test`库。
CREATE DATABASE `test` 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

---

-- 使用数据库。
USE '库名';

-- 查看正在使用的库。
SELECT DATABASE(); 

---

-- 修改`test`库。
ALTER DATABASE `test`
DEFAULT CHARACTER SET gb2312
DEFAULT COLLATE gb2312_chinese_ci;

---

2. 查看库

-- 查看所有库。
SHOW {DATABASES|SCHEMAS}
[LIKE '%模糊匹配%'|WHERE 'expr'];

SHOW DATABASES LIKE '%test%';

-- 查看数据库（非系统表）。
SHOW TABLES;

---

3. 删除库

DROP {DATABASE|SCHEMA} [IF EXISTS] '库名';

DROP DATABASE `test`;

---

三、数据表操作

---

1. 创建表

CREATE [TEMPORARY] TABLE '表名' (
	'主键名' '数据类型' ['约束'] ['自增'],    
    '字段1'  '数据类型' ['约束'],
    ...
    [, '表级完整性约束条件']
) [ENGINT='引擎类型'];
-- TEMPORARY：临时表。
-- AUTO_INCREMENT：设置自增属性，自动编号。
-- DEFAULT：指定默认值。
-- PRIMARY KEY('主键字段')：指定主键。

CREATE TABLE IF NOT EXISTS `customers`
(
    id      INT       NOT NULL AUTO_INCREMENT,
    name    CHAR(50)  NOT NULL,
    sex     CHAR(1)   NOT NULL DEFAULT '0',
    address CHAR(100) NULL,
    contact CHAR(50)  NULL,
    PRIMARY KEY (id)
);

• 数值型：int、double、float(n)
• 字符型：char(n) 定长字符串、varchar(n) 变长字符串
• 位串型：bit(n)、bit varying(n)
• 时间型：date、time

---

-- 查看创建表语句。
SHOW CREATE TABLE '表名';

-- 查看本库所有表
SHOW TABLES;

---

2. 查看表

SHOW [FULL] COLUMNS {FROM|IN} '表名' [{FROM|IN} '库名'] [LIKE 'pattern'|WHERE 'expr'];

SHOW COLUMNS FROM `customers`;

---

{DESCRIBE|DESC} '表名' ['列名'|wild];

DESC `customers` name;

-- 查看指定表结构。
DESC '表名';

---

3. 修改表

• ALTER TABLE

1. 重命名 表名。
2. 新增、修改、删除 字段和字段类型。
3. 创建、修改、删除 索引和外键。
4. 修改 表注释、引擎类型。
5. 创建 表触发器、存储过程。

---

3.1 修改表名

RENAME TABLE '原表名' [TO] '新表名';

ALTER TABLE '原表名' RENAME TO '新表名';

-- 写法1。
RENAME TABLE `customers` TO `customers_bk`;

-- 写法2。
ALTER TABLE `customers` RENAME TO `customers_bk`;

---

3.2 修改表字符集

-- 修改表字符集（gbk、utf8）。
ALTER TABLE '表名' CHARACTER SET '字符集';

---

3.3 添加列

-- ADD [COLUMN] 新增列。
ALTER TABLE '表名' ADD '列名' '数据类型(长度)' ['约束'];

ALTER TABLE '表名' ADD COLUMN '列名' '数据类型(长度)' ['约束'];

-- 添加一个列。
ALTER TABLE `customers`
-- `sex`列后新增`city`列
ADD COLUMN `city` CHAR(50) NOT NULL DEFAULT 'ShangHai' AFTER `sex`;

-- 添加多个列。
ALTER TABLE '表名'
	ADD COLUMN '列名1' '数据类型(长度)' ['约束'] COMMENT '注释1',
    ADD COLUMN '列名2' '数据类型(长度)' ['约束'] COMMENT '注释2';

---

3.4 修改列

-- CHANGE [COLUMN] 修改列。
ALTER TABLE '表名' CHANGE '原列名' '新列名' '数据类型(长度)' ['约束'];  

-- 修改列名。
ALTER TABLE `customers`
-- `sex`列重命名为`sex_bk`
CHANGE COLUMN `sex` `sex_bk` CHAR(1) NULL DEFAULT 'M';

---

-- MODIFY [COLUMN] 只修改指定列的数据类型，不涉及列名。
ALTER TABLE '表名' MODIFY '列名' '数据类型(长度)' ['约束'];

-- 修改列属性。
ALTER TABLE `customers`
-- `name`列修改类型
MODIFY COLUMN `name` CHAR(20) FIRST;

---

3.5 删除列

-- DROP [COLUMN] 删除列。
ALTER TABLE '表名' DROP '列名';

ALTER TABLE '表名' DROP COLUMN '列名';

ALTER TABLE `customers` DROP COLUMN `contact`;

---

4. 删除表

DROP [TEMPORARY] TABLE [IF EXISTS] '表名'[',表名2'...] [RESTRICT|CASCADE]; 

DROP TABLE `test`;

DROP TABLE IF EXISTS `student`;

---

四、索引操作

• 索引分类：

1. 单列索引。
2. 组合索引。

---

• 索引类型：

1. 普通索引：index、key
2. 唯一索引：unique
3. 主键索引：primary key

---

1. 查看索引

SHOW {INDEX|INDEXS|KEYS} {FROM|IN} '表名'
[{FROM|IN} '库名'] [WHERE 'expr'];

SHOW INDEX FROM '表名';

SHOW KEYS FROM '表名';

SHOW INDEX FROM `dept`;

1. Table：
   表的名称。
2. Non_unique：
   如果索引不能包括重复词，则为0。
   如果可以，则为1。
3. Key_name：
   索引的名称。
4. Seq_in_index：
   索引中的列序列号，从1开始。
5. Column_name：
   列名称。
6. Collation：
   列以什么方式存储在索引中。
   在 MySQL 中，有值A（升序）或NULL（无分类）。
7. Cardinality：
   索引中唯一值的数目的估计值。通过运行 ANALYZE TABLE 或 myisamchk -a 可以更新。
   基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。
   基数越大，当进行联合时，MySQL 使用该索引的机 会就越大。
8. Sub_part：
   如果列只是被部分地编入索引，则为被编入索引的字符的数目。
   如果整列被编入索引，则为NULL。
9. Packed：
   指示关键字如何被压缩。
   如果没有被压缩，则为NULL。
10. Null：
    如果列含有NULL，则含有YES。
    如果没有，则该列含有NO。
11. Index_type：
    用过的索引方法（BTREE、FULLTEXT、HASH、RTREE）。
12. Comment：
    说明。
13. Index_comment：
    索引说明。

---

1.2 查看索引大小

SELECT
	DATA_LENGTH / (1024 * 1024 * 1024) AS 'DATA_SIZE(GB)', 
   	INDEX_LENGTH / (1024 * 1024 * 1024) AS 'INDEX_SIZE(GB)', 
   	TABLE_SCHEMA, 
   	TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_NAME = 'test_1';

---

2. 创建索引

CREATE [UNIQUE] INDEX '索引名' 
ON '表名'('索引描述');

-- UNIQUE：唯一索引。

-- `customers`表`name`列前三个字符，创建一个升序索引。
CREATE INDEX `index_customers` 
ON `test`.`customers`(name(3) ASC);

-- 组合索引。
CREATE INDEX `index_cust`
ON `test`.`customers`(cust_name,cust_id);

---

-- 创建表，同时创建`主键索引`。
[CONSTRAINT['symbol']] PRIMARY KEY('index_col_name'),

-- 创建表，同时创建`唯一索引`。
[CONSTRAINT['symbol']] UNIQUE [INDEX|KEY] ['索引名']('index_col_name', ...),

-- 创建表，同时创建`外键索引`。
[CONSTRAINT['symbol']] FOREIGN KEY ['索引名']('index_col_name', ...),


-- 修改表，同时添加`主键索引`。
ADD [CONSTRAINT['symbol']] PRIMARY KEY('index_col_name', ...),

-- 修改表，同时添加`普通索引`。
ADD {INDEX|KEY} ['索引名']('index_col_name', ...),

-- 修改表，同时添加`唯一索引`。
ADD [CONSTRAINT['symbol']] UNIQUE [INDEX|KEY] ['索引名']('index_col_name', ...),

-- 修改表，同时添加`外键索引`。
ADD [CONSTRAINT['symbol']] FOREIGN KEY ('index_col_name', ...),

CREATE TABLE `seller` (
	`seller_id` int not null auto_increment,
	`seller_name` char(50) not null,
	`seller_address` char(50) null,
	`seller_contact` char(50) null,
	`product_type` int(5) null,
	`sales` int null,
	primary key(seller_id,product_type),
	index index_seller(sales)
);

-- `seller`表`姓名`列，添加`普通索引`。
ALTER TABLE `test`.`seller` 
ADD INDEX `index_seller_name`('seller_name');

---

3. 删除索引

-- DROP INDEX 删除各种类型索引。
DROP INDEX '索引名';
DROP INDEX '索引名' ON '表名';

-- 删除主键（表中只有一个主键，可以不加主键名）。
DROP PRIMARY KEY;

-- 删除外键。
DROP FOREIGN KEY;

DROP INDEX `index_cust` ON `test`.`customers`;

-- 删除`customers`表，的`主键索引`和`普通索引`。
ALTER TABLE `customers`
DROP PRIMARY KEY,
DROP INDEX `index_customers`;

---

五、单表操作

---

1. 新增

• INSERT ... VALUES 语句，插入 单行 或 多行 数据。

1. 数据是字符型，必须使用 单引号 或 双引号（如：“张三”）。
2. AUTO_INCREMENT 列，无需赋值 或 0，自动生成。

-- 插入指定列的值。
INSERT INTO '表名' ('列名1', '列名1', ..., '列名n') 
VALUES ({'值1'|DEFAULT}, '值1', ..., '值n');

-- 插入所有列的值。
INSERT INTO '表名' VALUES ('值1', '值2', ...);

INSERT INTO `customers` VALUES (0, '张三', 'F', '北京', '朝阳');

INSERT INTO `customers` VALUES (0, '李四', DEFAULT, '上海', NULL);

---

• INSERT ... SET 语句。

INSERT [INTO] '表名' 
SET '列名1'={'expr'|DEFAULT}, ..., '列名n'='值n';

INSERT INTO `customers`
SET cus_name = '李四', cus_address = '西安', cus_sex = DEFAULT;

---

• INSERT ... SELECT 语句。

INSERT [INTO] '表名' [('列名1', ..., '列名n')]
SELECT ...

• 注意：两个表的 字段类型 和 个数 要匹配。

INSERT INTO `customers` (cus_name, cus_sex)
SELECT name, sex FROM `customers_bk`;

-- REPLACE INTO 是 SQL 语句中用于向表中插入数据的语句。
-- 它与 INSERT INTO 语句类似，但有一个关键的区别：REPLACE INTO 会尝试更新现有记录，如果根据主键或唯一索引找到匹配的记录，则更新该记录；否则，它会插入新记录。
REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

---

2. 修改

UPDATE '表名'
SET '列名1' = {'expr'|DEFAULT}, ..., '列名n' = {'expr'|DEFAULT}
[WHERE '条件']
[ORDER BY '排序字段']
[LIMIT '行数']; 

-- 修改记录。
UPDATE '表名' SET '列名1'='值1', '列名2'='值2' [WHERE '条件'];

UPDATE `customers`
SET `cus_address` = '深圳'
WHERE `cus_name` = '张三';

---

3. 删除

DELETE FROM '表名'
[WHERE '条件']
[ORDER BY '排序字段']
[LIMIT '行数']; 

-- 删除记录。
DELETE FROM '表名' [WHERE '条件'];

DELETE FROM `customers`
WHERE cus_name = '李四';

---

TRUNCATE TABLE '表名';

---

4. 单表查询

• SQL 查询语句顺序：
  SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT;

SELECT [ALL|DISTINCT] '*|列名' 
FROM '表名' 
[WHERE '条件']
[GROUP BY '分组字段']
[HAVING '组条件']
[ORDER BY '排序字段' [ASC|DESC]]
[LIMIT '行数'];

-- `*`通配符。 

子句	说明	是否必须
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚合时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

SELECT cus_name AS '姓名', cus_address AS '地址'
FROM `customers`;

---

4.1 聚合函数

函数名	说明
COUNT	求组中项数，返回INT类型整数
MAX	求最大值
MIN	求最小值
SUM	返回表达式中所有值的和
AVG	求组中值的平均值
STD或STDDEV	返回给定表达式中所有值的标准值
VARIANCE	返回给定表达式中所有值的方差
GROUP_CONCAT	返回由属于一组的列值连接组合而成的结果
BIT_AND	逻辑与
BIR_OR	逻辑或
BIT_XOR	逻辑异或

---

4.2 条件过滤

比较运算符	说明
=	等于
<> 或 !=	不等于
<	小于
>	大于
<=	小于等于
>=	大于等于
<=>	不会返回 UNKNOWN

• WHERE：

1. 条件关键字：
   >、<、=、>=、<=、<>。
2. LIKE 中可以使用占位符：
   _：下划线匹配一个字符。
   %：可以匹配任意多个字符。
3. [NOT] IN ('子查询') 后跟着一组值。
4. AND、OR、NOT。
5. BETWEEN ... AND。
6. IS NULL、IS NOT NULL。
7. expression {=|<|<=|!=|>=...} {ALL|SOME|ANY} ('子查询')。

-- like 
like '张％';
like '张_';
like '%明';
like '%明%'; 
	
-- in
id in (1, 2, 3);

---

• 子查询分类：

1. 标量子查询：返回单一值的标量，最简单的形式。
2. 列子查询：返回的结果集时 N行一列。
3. 行子查询：返回的结果集是 一行N列。
4. 表子查询：返回的结果集是 N行N列。

'expression' {=|<|<=|!=|>=...} {ALL|SOME|ANY} ('子查询')
-- ALL：满足所有。
-- SOME、ANY：满足任意一个。

SELECT 's1' FROM 'table1' WHERE 's1' > ANY(SELECT s2 FROM 'table2');
SELECT 's1' FROM 'table1' WHERE 's1' > SOME(SELECT s2 FROM 'table2');

-- EXISTS('子查询') 

SELECT * 
FROM 'table1'
WHERE 'city' = '上海'
AND EXISTS(
	SELECT *
	FROM 'table2'
	WHERE 'table1.cus_id' = 'table2.cus_id'
);

---

4.3 分组

GROUP BY '列名' [ASC|DESC] [WITH ROLLUP]
-- WITH ROLLUP：分组的汇总行。

-- 分组
GROUP BY '列名...';

-- 结果再过滤
HAVING '条件';

-- 对数据进行排序，`ASC`升序默认，`DESC`降序
ORDER BY '列名' [ASC|DESC];

---

4.4 案例

-- 将成绩`+10`分进行显示
select name, english+10, chinese+10, math+10 from exam;

-- 显示这个人的名称和对应总成绩的分数
select name, english+chinese+math from exam;

-- 使用`as`起别名`[AS]`可以省略
select name, english+chinese+math as sum from exam;

-- 查询学生的信息，按照英语成绩降序排序，如果英语成绩相同，按照语文降序
select * from exam order by english desc, chinese desc;

---

六、多表操作

---

1. 多表创建

• 一对多关系 的建表原则：
  在多的一方创建一个字段，这个字段作为 外键，指向一的一方的 主键。

---

• 多对多关系 的建表原则：
  创建 中间表，表中两个字段分别作为 外键，指向 多对多双方 的各自的 主键。

---

• 一对一关系 的建表原则：

1. 唯一外键对应，假设 一对一 的双方是 一对多 的关系，在多的一方创建 外键 指向一的一方的主键，需要在外键上添加 unique 唯一约束；
2. 主键对应，将一对一的双方的主键建立映射。
3. 交叉连接。

---

2. 交叉连接

• 交叉连接后返回的查询结果集的记录行数，为两张表记录行数的乘积。

-- 笛卡尔积。
SELECT * FROM '表1' CROSS JOIN '表2';

-- [CROSS JOIN] 可以省略。
SELECT * FROM 'A','B';

---

3. 内连接（INNER JOIN）

• INNER 可以省略。

-- 显式内连接
SELECT * FROM 'A' INNER JOIN 'B' ON '条件';

-- 隐式内连接
SELECT * FROM A,B WHERE '条件';

SELECT * FROM `customer` c INNER JOIN `orders` o ON c.cid = o.cid;

SELECT * FROM `customer` c, `orders` o WHERE c.cid = o.cid;

---

4. 外连接（OUTER JOIN）

• OUTER 可以省略。

-- 左外连接 LEFT [OUTER] JOIN
SELECT * FROM A LEFT OUTER JOIN B ON '条件';

-- 右外连接 RIGHT [OUTER] JOIN
SELECT * FROM A RIGHT OUTER JOIN B ON '条件';  

SELECT * FROM `customer` c LEFT OUTER JOIN orders o ON c.cid = o.cid;

SELECT * FROM `customer` c RIGHT OUTER JOIN orders o ON c.cid = o.cid;

-- 多表查询的子查询，一个SQL语句查询的过程中需要依赖另一个查询语句
SELECT * FROM customer c, orders o 
WHERE c.cid = o.cid 
AND c.cid IN (SELECT cid FROM orders WHERE addr LIKE '海淀%');  

---

七、导入导出

---

1. 导出数据

• 按库导出数据：
  /usr/local/mysql/bin/mysqldump

-- 按库导出`表结构 + 数据`。
mysqldump -h'ip' -P'port' -u'用户名' -p'密码' '库名' > '库名.sql'

-- 指定库。
mysqldump -u'root' -p'密码' --databases 'db_name' > '/root/db_name.sql'
-- 所有库。
mysqldump -u'root' -p'密码' --all-databases > '/root/db_name.sql'

---

• 按表导出数据：
  mysqldump -u'用户名' -p'密码' '库名' 'table1' 'table2' 'table3' > '表名.sql'

-- 按表导出`user表`。
mysqldump -u'root' -p'密码' '库名' '表名' > '表名.sql'

-- 导出`指定表`。
mysqldump -u'root' -p'密码' '库名' --tables 'table1' 'table_2' 'table_3' > '表名.sql'

-- 导出`忽略指定表`。
mysqldump -u'root' -p'密码' --databases '库名' --default-character-set=utf8 
	--ignore-table='db_name.table_1' 
	--ignore-table='db_name.table_2' 
	--ignore-table='db_name.table_3' > 'db.sql'

---

1. -d：表结构。
2. -t：数据。

-- 按库导出`表结构`。
mysqldump -u'root' -p'密码' -d '库名' > '库名.sql'

-- 按库导出`数据`。
mysqldump -u'root' -p'密码' -t '库名' > '库名.sql'

---

• --add-drop--database：
  CREATE DATABASE 语句前添加 DROP DATABASE 语句（默认：开启）。
• --add-drop-table：
  CREATE TABLE 语句前添加 DROP TABLE 语句（默认：开启）。
• --skip-add-drop-table：
  不加 DROP TABLE 语句。

mysqldump -u'root' -p'密码' -d --add-drop-table '库名' > '库名.sql'

# 不加`DROP TABLE语句`。
mysqldump -u'root' -p'密码' -d --skip-add-drop-table '库名' > '库名.sql'

---

2. 导入数据

• 未登录导入：

# 1、先创建一个新的数据库
create database 'db2';

# 2、将`db1.sql数据库文件`导入到`db2数据库`中。
mysqldump -u'root' -p'密码' '库名2' < '库名.sql'

mysqldump -u'root' -p'密码' '库名2' '表名' < '表名.sql'

mysqldump -u'root' -p'密码' -d '库名2' < '库名.sql'

mysql -u'root' -p'密码' '库名' < '库名.sql'

---

• 登录后导入：

# 1、登录。
mysql -h'192.168.0.1' -P'3306' -u'root' -p'123456'

-- 切换`db1库`。
mysql>use db1;

-- 执行`user.sql文件`。
mysql>source /root/user.sql

---

• 导入到远程数据库：

mysqldump -u'root' -p'密码' 'database_name' \
	| mysql -h'192.168.1.2' 'database_name'

---

八、其他

---

1. MySQL——数据库表信息

-- 查看数据库表基本信息
show table status like 'employees';

select *
from information_schema.TABLES
where information_schema.TABLES.TABLE_SCHEMA = 'learn_mysql'
  and information_schema.TABLES.TABLE_NAME = 'employees';

---

2. MySQL——数据库大小

-- 查看`MySQL`数据库大小（单位字节，除以`1024 * 1024`为`MB`）
SELECT sum(DATA_LENGTH) + sum(INDEX_LENGTH)
FROM information_schema.TABLES
where TABLE_SCHEMA = 'learn_mysql';

---

3. MySQL——表的最后修改时间

-- 查看`MySQL`表的最后修改时间
select TABLE_NAME, UPDATE_TIME
from information_schema.TABLES
where TABLE_SCHEMA = 'learn_mysql'
order by UPDATE_TIME desc
limit 1;

select TABLE_NAME, UPDATE_TIME
from information_schema.TABLES
where TABLE_SCHEMA = 'learn_mysql'
  and information_schema.TABLES.TABLE_NAME = 'employees';

---

4. MySQL——时区和时间

-- 查看数据库时间
select now(); 

SELECT CURRENT_TIMESTAMP;

---

-- 查看数据库时区（默认`SYSTEM`、`UTC`）
show variables like "%time_zone%";

• UTC：协调世界时。
• SYSTEM：MySQL 使用 SYSTEM 的时区（SYSTEM 是 UTC 时区）。
  

---

-- 修改`MySQL`全局时区为北京时间（即东8区）
set global time_zone = '+8:00';

-- 修改当前会话时区
set time_zone = '+8:00';
set global time_zone = '+8:00';

-- 立即生效
flush privileges; 

• 东 8 区：
  

---

5. MySQL——最大 SQL 长度

---

• mysql允许最大sql语句长度配置
• 由一个抓瞎的SQL引申出去

• MySQL 对每个客户端的连接：都会分配 连接Buffer 和 结果集Buffer

• 连接Buffer：
  接收 客户端 发送的 SQL语句，控制语句大小，实现 慢SQL 相关优化。

1. 初始分配大小是 net_buffer_length（默认：16k）。
2. 可以动态增长，最大到 max_allow_packet（默认：4M）。
3. max_allow_packet 是 MySQL 控制 网络包大小的参数。
4. 这个参数是会话只读的，言外之意就是只能全局修改，新建连接才生效。

---

-- 连接`buffer`大小（默认: 16384 = 16k）
show global variables like 'net_buffer_length';

-- `SQL`最大大小（默认: 4194304 = 4m）
show global variables like 'max_allowed_packet';

---

5.1 问题分析

-- `in_list`元素最多个数
select * from 'table' where 'conditions' and `id` in ('in_list');

---

5.2 测试脚本

-- 修改`max_allowed_packet`为128K
set global max_allowed_packet = 1024*128;

---

• 测试 test.py：

#!/bin/env python
#coding:utf-8

import pymysql

def get_in_list(i):
    in_list = ''
    for i in range(1,i):
        in_list = str(i) + ',' + in_list

    in_list = in_list + str(i+1)
    return in_list

def exec_mysql(sql):
    conn = pymysql.connect(host='127.0.0.1', user='root',
                           password='123456', database='learn_mysql', charset='utf8')
    cursor = conn.cursor()
    cursor.execute(sql)
    results = cursor.fetchall()
    if results:
        print("get results")
    conn.close()

if __name__ == "__main__":
    in_list = get_in_list(100)
    sql = "select * from qs_user where id in ({})".format(in_list)
    # 打印出`sql`占用的字节数
    print(len(in_list.encode()))
    exec_mysql(sql)

---

• 当 i = 4200 时：

1. SQL 大小为 19892 个字节（大于 net_buffer_length）。
2. 能正常返回数据，结果如下。

[root@qs python_scripts]# python test.py 19892get results

---

• 当 i = 23690 时：

1. SQL 大小为 131033 个字节（小于 max_allowed_packet）。
2. 能正常返回数据，结果如下。

[root@qs python_scripts]# python test.py 131033get results

---

• 当 i = 23691 时：

1. SQL 大小为 131039 个字节（小于 max_allowed_packet）。
2. 抛出异常。

[root@qs python_scripts]# python test.py 
131039
Traceback (most recent call last):
  File "test.py", line 29, in <module>
    exec_mysql(sql)
  File "test.py", line 19, in exec_mysql
    cursor.execute(sql)
  File "/usr/lib64/python2.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib64/python2.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/lib64/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib64/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1153, u"Got a packet bigger than 'max_allowed_packet' bytes")

---

• MySQL 错误日志如下：

2020-03-10T09:07:46.992043Z 32 [Note] Aborted connection 32 to db: 'sbtest' user: 'root' host: '127.0.0.1' (Got a packet bigger than 'max_allowed_packet' bytes)

---

5.3 测试结果

• 为什么 SQL 字节数 131039 < 131072。
  小于 max_allowed_packet MySQL 还是报错了呢？

1. 因为这里还需要加上 MySQL 的包头大小，这个包头的大小是多少呢？
   还是用 i = 23690 的例子，在 MySQL 服务器上抓包。
   
2. 可以看到 MySQL 接收到的包大小是 131070 字节。
   那么这个 包头大小 就为 37 字节了（未深入研究，留到以后研究）。

• 上述测试中 i = 23691 的例子，SQL 大小为 131039 字节，加上包头的大小 37 字节。

1. 总大小为 131076 字节，大于 max_allowed_packet 的 131072。
2. 所以 MySQL 报错，符合逻辑。

---

5.4 测试结论

• 虽然 MySQL 没有限制 子查询 内元素个数。

1. 但还是不建议元素太多，会影响执行计划。
2. 同时也会影响 SQL解析的效率 和 内存占用。

• 适当调大 net_buffer_length。

1. 最好能够一次性 缓存SQL，无需再分配内存。
2. 且这是一个全局性参数。

• 适当调大 max_allowed_packet。

1. 但是也不要分配过大。
2. 这是一个会话级变量，生产建议为 32M 最佳。

• 学会利用 抓包，解决网络相关的问题。

---

6. MySQL——最大连接

-- `MySQL实例`的最大连接数（上限值是16384）。
SHOW VARIABLES LIKE '%max_connections%'; 
set global max_connections = 1000;

---

-- `数据库用户`的最大连接数。
SHOW VARIABLES LIKE '%max_user_connections%';

• MySQL 会为每个连接提供缓冲区，意味着消耗更多的内存。

1. 如果连接数设置太高硬件吃不消，太低又不能充分利用硬件。
2. 一般要求两者比值超过 10%。计算方法如下：
   max_used_connections / max_connections * 100% = 3 / 100 * 100% ≈ 3%

---

• my.cnf 配置文件，修改最大连接数：

[mysqld]
max_connections = 100
max_used_connections = 20

---

-- 当前连接数 
SHOW STATUS LIKE '%Threads_connected%'; 
SHOW STATUS LIKE 'Threads%'; 
SHOW STATUS LIKE '%Connection%';  

-- 进程列表
SHOW PROCESSLIST;  
SHOW FULL PROCESSLIST;

-- 查询非`Sleep`状态的链接，按消耗时间倒序展示。
SELECT id, db, user, host, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- 查询执行时间超过`2`分钟的线程，然后拼接成`kill`语句。
SELECT concat('kill ', id, ';')
FROM information_schema.processlist
WHERE command != 'Sleep'
AND time > 2*60
ORDER BY time DESC;

-- 半小时
set global wait_timeout=1800;

---

7. MySQL——缓存机制

• mysql 缓存机制

-- 是否开启缓存（默认: OFF关闭、ON打开、DEMAND只有明确写了`SQL_CACHE`的查询才会写入缓存）
show variables like "%query_cache_type%";
-- 缓存使用的总内存空间大小（单位字节，默认：1048576 = 1m）
-- 必须是`1024`的整数倍，否则`MySQL`实际分配大小可能跟这个数值不同（这个应该跟文件系统的`blcok`大小有关）
show variables like "%query_cache_size%";
-- 分配内存块时的最小单位大小（默认: 4096 = 4k）
show variables like "%query_cache_min_res_unit%";
-- `MySQL`能够缓存的最大结果，如果超出，则增加`Qcache_not_cached`的值，并删除查询结果
show variables like "%query_cache_limit%";
-- 如果某个数据表被锁住，是否仍然从缓存中返回数据（默认: OFF关闭，表示仍然可以返回）
show variables like "%query_cache_wlock_invalidate%";

-- 缓存池中空闲块的个数
SHOW STATUS LIKE '%Qcache_free_blocks%';
-- 缓存中空闲内存量
SHOW STATUS LIKE '%Qcache_free_memory%';
-- 缓存命中次数
SHOW STATUS LIKE '%Qcache_hits%';
-- 缓存写入次数
SHOW STATUS LIKE '%Qcache_inserts%';
-- 因内存不足删除缓存次数
SHOW STATUS LIKE '%Qcache_lowmen_prunes%';
-- 查询未被缓存次数（例如: 查询结果超出缓存块大小，查询中包含可变函数等）
SHOW STATUS LIKE '%Qcache_not_cached%';
-- 当前缓存中缓存的`SQL`数量
SHOW STATUS LIKE '%Qcache_queries_in_cache%';
-- 缓存总`block`数
SHOW STATUS LIKE '%Qcache_total_blocks%';

---

九、异常

---

1. ERROR 1105 (HY000): unsupported yet: subquery in select list with aggregation

show variables like "%mode%";

• 错误：
  

• 标准：
  

set global sql_mode=NO_ENGINE_SUBSTITUTION;

---