[大数据]MySQL 基础篇

MySQL 基础篇

1. 数据库概述

• 关系型数据库典型的数据结构就是 数据表，这些数据表的组成都是结构化的（Structured）
• 将数据放到表中，表再放到库中；
• 一个数据库中可以有多个表，每个表都有一个唯一的标识名；

1.1. 表、记录、字段

• E-R(entity-relationship，实体-联系)模型中有三个主要概念是：实体集、属性、联系集
• 一个实体集（class）对应数据库中的一个表（table）；一个实体（instance）则对应于数据表中的一行（row），也成为一条记录（record）；一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）；

ORM 思想（Object Relational Mapping）
表 <---> 类
一条记录 <---> 实例对象（实体）
列 <---> 类中的一个字段（field）

1.2. 表的关联关系

• 表与表之间的数据记录之间有四种关系（relationship）：一对一关联、一对多关联、多对多关联、自我引用；

1.2.1. 一对一关联（one-to-one）

不常用，一对一可以创建成一张表（当然有时候字段太多也可以拆分下）；

• 外键唯一：主表的主键与从表的外键（唯一），形成主外键关系，外键唯一；
• 外键是主键：主表的主键和从表的主键，形成主外键关系；

1.2.2. 一对多关联（ont-to-many）

常见实例场景：客户表和订单表、分类表和商品表、部门表和员工表

一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键；

1.2.3. 多对多关联（mant-to-many）

多对多关系中必须有第三个表，通常称为 联接表，它将多对多关系划分为两个一对多关系，将这两个表的主键都插入到第三个表中；

• 举个栗子：学生-课程

  • 学生表：学号、姓名、班级
  • 课程表：课程号、授课老师、简介
  • 选课表：一个学生可以选多门课，一门课可以被多个学生选择

• 再举个栗子：产品-订单

  《订单表》和《产品表》有一种多对多的关系，这种关系是通过《订单明细表》建立两个一对多关系来定义的。一个订单可以有多个产品，每个产品可以出现在多个订单中；

  • 产品表
  • 订单表
  • 订单明细表

2. SQL 之 SELECT

2.1. 基本规则

• 为了可读性，SQL 各子句分行写，必要时使用缩进

• 每条命令以 ; 或 \g 或 \G 结束

• 列的别名，尽量使用双引号 ""，不建议省略 as

• 字符串型和日期类型的数据使用单引号 '' 表示

• MySQL 在 Windows 下大小写不敏感，而在 Linux 下大小写敏感；

  • 数据库名、表名、表的别名、变量名是严格区分大小写的
  • 关键字、函数名、列明（或字段名）、列的别名（字段的别名）时忽略大小写的

• 大小写规范：

  1. 数据库名、表名、表别名、字段名、字段别名等都小写
  2. SQL 关键字、函数名、绑定变量等都大写

• 注释

  单行注释：# 注释文字
  单行注释：-- 注释文字  --（--后面必须有一个空格）
  多行注释：/* 注释文字 */
  

• 命名规则

  1. 数据库、表名在 30 个字符以内，变量名限制为 29 个
  2. 必须只能包含 A-Z、a-z、0-8、_ 共 63 个字符
  3. 同一个 MySQL 中，数据库不能同名；同一个数据库中，表不能同名；同一个表中，字段不能重名；
  4. 字段中不能和保留字、数据库系统或常用方法冲突，冲突则需要用 ` 着重号引起来
  5. 保持字段名和类型的一致性

• ?导入现有的数据表、表的数据：

  1. source 文件的全路径名：source d:\\book.sql
  2. 使用图形化界面工具导入

2.2. 基本语法

1. 伪表 DUAL： 一个不存在的表，它的字段和记录都是无意义的，用来方便操作；

   SELECT 1 + 1
   # 上面的 SQL 会被自动补为
   SELECT 1 + 1
   FROM DUAL
   

2. 列的别名 alias： 简称 as，当列的别名中有空格时，需要使用双引号 "" 引起来；

   SELECT employee_id emp_id, last_name AS lname, department_id "部门 id"
   FROM employees;
   

3. 去除重复行 DISTINCT

   SELECT DISTINCT department_id
   FROM employees
   

4. 空值 null： 空值不等同于 0，空值参与运算，结果也一定为空；

5. 查询常数： 在查询结果中添加一列或多列

   SELECT '尚硅谷', 123, employee_id, last_name
   FROM employees
   

6. 显示表结构 DESCRIBE

   DESCRIBE employees
   # 简写为 DESC
   DESC employees
   

7. 过滤数据 WHERE

   SELECT *
   FROM employees
   # 过滤条件
   WHERE department_id = 90
   

2.3. 运算符

只要 NULL 值参与运算，结果就为 NULL；有特殊情况 <=> 安全等于号，运算符主要需要注意的就是 NULL 值参与运算是结果是什么；

2.3.1. 算术运算符

算术运算符主要用于数学运算，可以连接运算符前后的两个数值或表达式，对数值或表达式进行加减乘除或取模运算；

SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 - 0, 100 + 35.5, 100 - 35.5
FROM DUAL;
# 100 100 100 150 150 135.5 64.5

SELECT 100 + '1' # 在 SQL 中，+ 没有连接的作用
FROM DUAL;
# 101

SELECT 100 + '1' # 此时将 'a' 看做 0 处理
FROM DUAL;
# 100

SELECT 100 + NULL # NULL 参与运算结果为 NULL
FROM DUAL;
# 100

SELECT 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2, 100 + 2 * 5 / 2, 100 / 3, 100 DIV 0
FROM DUAL;
# 100 100.0 100.0000 50.0000 105.0000 33.3333 NULL

SELECT 12 % 3, 12 % 5, 12 MOD -5, -12 % 5, -12 % -5
FROM DUAL
# 0 2 2 -2 -2

2.3.2. 比较运算符

1. 等于 =

   SELECT 1 = 2,1 != 2, 1 = '1', 1 = 'a', 0 = 'a', 'a' = 'a', 'ab' = 'ab', 'a' = 'b'
   FROM DUAL # 两边都是字符串则以 ASCII 码比较
   # 0 1 1 0 1 1 1 0
   

2. 安全等于 <=>：与 = 的 唯一区别 是： <=> 可以用来对 NULL 进行判断；

   SELECT 1 <=> NULL, NULL <=> NULL # 两边都是 NUll 则为 1，一边是 NULL则为 0
   FROM DUAL;
   # 0 1
   

比较运算符之一些关键字；

# % 代表不确定个数的字符（0 个，1 个，多个）
# _ 代表一个不确定的字符
# \_ 代表 _
# \\ 代表 \
SELECT last_name
FROM employees
WHERE last_name LIKE "%a%";

# 把转义字符设置为 $
SELECT last_name
FROM employees
WHERE last_name LIKE "_$_a%" ESCAPE '$';

REGEXP 运算符，正则；语法格式为：expr REGEXP 匹配条件 如果 expr 满足匹配条件，返回 1；不满足则返回 0；若 expr 或 匹配条件任意一个为 NULL，则结果为 NULL；

(1) '^' 匹配以紧跟着的字符开头的字符串
(2) '$' 匹配以紧跟着的字符结尾的字符串
(3) '.' 匹配任意一个单字符
(4) '[...]' 匹配方括号内任意一个字符，[0-9] 代表任意数字
(5) '*' 拓展零个或多个他前面的字符

SELECT 'xbai-hang' REGEXP '^x', 'xbai-hang' REGEXP 'g$', 'xbai-hang' REGEXP 'bai'
FROM DUAL;
# 1 1 1 

2.3.3. 逻辑运算符

逻辑运算主要用来判断表达式的真假，在 MySQL 中，逻辑运算符的返回结果为 1、0、NULL；

OR 和 AND 可以一起使用，AND 的优先级高于 OR；

2.3.4. 位运算符

将操作数转换成二进制，然后进行位运算，最后将结果转换为十进制；

2.3.5. 运算符优先级

2.4. 排序

如果没有排序操作，默认查询返回的数据是以添加数据的顺序显示的；

2.4.1. 排序规则

• 使用 ORDER BY 子句排序；
  • ASC（ascend）：升序，ORDER BY 子句的默认值
  • DESC（descend）：降序
• ORDER BY 子句在 SELECT 语句的结尾；

2.4.2. 单列排序

# 默认升序，不写 ASC 也是升序
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC;

# WHERE 要在 FROM 之后、ORDER BY 之前
SELECT employee_id AS emp_id, salary
FROM employees
WHERE department_id IN (50, 60, 70)
-- WHERE emp_id IN (50, 60, 70) # 错误的，WHERE 子句中不能用别名
ORDER BY emp_id DESC;

2.4.3. 多列排序

当第一列列值相同时，对第二列进行排序；如下例中 部门 id 降序排序，相同部门时按 薪资高低升序排序；

# 先按照 department_id 降序排序，再按照 salary 升序排序
SELECT employee_id, department_id, salary
FROM employees
ORDER BY department_id DESC, salary ASC;

2.5. 分页

WHERE、ORDER BY、LIMIT 声明顺序（非执行顺序）：先 WHERE，后 ORDER BY，最后 LIMIT；

2.5.1. 使用规则

• LIMIT 位置偏移量, 条目数，偏移量为 0 时可以省略；
  • LIMIT 0, 条目数 等价于 LIMIT 条目数
• MySQL 8.0 新特性：LIMIT ... OFFSET ...
  • LIMIT 条目数 OFFSET 偏移量

SELECT employee_id, last_name
FROM employees
LIMIT 0, 20; -- 每页 20 条记录，显示第 1 页

SELECT employee_id, last_name
FROM employees
LIMIT 20, 20; -- 每页 20 条记录，显示第 2 页

# 需求：每页显示 pageSize 条记录，此时显示第 pageNo 页
# 公式：LIMIT (pageNo - 1) * pageSize, pageSize;
SELECT employee_id, last_name
FROM employees
LIMIT 20, 20;

• 分页显示公式： (当前页数 - 1) * 每页条数，每页条数

  SELECT * FROM table
  LIMIT (pageNo - 1) * pageSize, pageSize;
  

• LIMIT 子句必须放在整个 SELECT 语句的最后

• 使用 LIMIT的好处：

  约束返回结果的数量可以 减少数据表的网络传输量，也可以 提升查询效率；当直到返回结果只有 1 条，就可以使用 LIMIT 1 ；这样做的好处是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回；

2.6. ?多表查询

多表查询：也称为关联查询，指两个或更多个表一起完成查询操作；

前提条件：一起查询的表之间有关联关系（一对多，一对一），它们之间有关联字段，这个关联字段可能建立了外键，也可能没有建立外键；

比如：员工表和部门表，两个表依靠 部门编号 进行关联；

【Alibaba 开发手册 强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能。

• 表中有相同列时，在列名之前加上表名前缀
• 给表起别名后，SELECT、WHERE 中使用表名则必须用表的别名
• 从 sql 优化的角度，建议多表查询时，每个字段前都指明其所在的表

等值连接与非等值连接

• 等值连接： 即 WHERE 语句的条件是等值判断；

  SELECT e.employee_id, d.department_name, e.department_id AS department_id
  FROM employees AS e, departments AS d
  WHERE e.department_id = d.departmen_id
  

• 非等值连接

  SELECT e.last_name, e.salary, j.grade_level
  FROM employees e, job_grades j
  WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
  

自连接与非自连接

涉及多表查询的都是非自连接；自连接仅涉及一张表（自我引用，逻辑层面视为多张表）

SELECT emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name
FROM  employees AS emp, employees AS mgr
WHERE emp.manager_id = mgr.employee_id;

内连接与外连接

• 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行；

  SELECT 字段列表
  FROM A 表 [INNER] JOIN B 表
  ON 关联条件
  WHERE 等其子句
  
  SELECT e.last_name, d.department_name
  FROM employees e JOIN departments d
  ON e.department_id = d.department_id;
  

• 外连接：两个表在连接过程中除了返回满足连接条件的行以外 还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接，没有匹配的行时，结果表中相应的列为空（NULL）；

• 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表

  SELECT 字段列表
  FROM A 表 LEFT | RIGHT [OUTER] JOIN B 表
  ON 关联条件
  WHERE 等其子句
  
  SELECT last_name, department_name
  FROM employees e LEFT JOIN  departments d
  ON e.department_id = d.department_id
  
  SELECT last_name, department_name
  FROM employees e RIGHT JOIN  departments d
  ON e.department_id = d.department_id
  

• 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表

2.7. UNION 的使用

UNION 用于合并查询结果，将多条 SELECT 语句的结果组合成单个结果集；合并时，两个表对应的列数和数据类型必须相同，并相互对应；

各个 SELECT 语句之间使用 UNION 或 UNION ALL 关键字隔离；

语法格式：

SELECT colum, ... FROM table1
UNION [ALL]
SELECT colum, ... FROM table2

• UNION UNION 操作符返回两个查询的结果集的并集，并去除重复记录
• UNION ALL UNION ALL 操作符返回两个查询结果的并集，对于两个结果集的重复部分，不去重

注意：执行 UNION ALL 语句时所需要的资源比 UNION 语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用 UNION ALL 语句，以提高数据查询的效 率。

2.8. SQL99 新特性

自然（NATURAL）连接

自然连接 NATURAL JOIN 自动查询两张连接表中的 所有相同字段 ，然后进行 等值连接

SELECT employee_id, last_name, department_name
FROM employees e JOIN  departments d
ON e.department_id = d.department_id
AND e.manager_id = d.manager_id;

上面的查询语句可以写为如下语句：

# 改写为自然连接
SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN  departments d

USING 连接

USING 连接制定了具体的相同的字段名称，在 USING 的括号 () 中填入要指定的同名字段；

SELECT employee_id, last_name, department_name
FROM employees e JOIN  departments d
ON e.department_id = d.department_id

上面的查询语句可以写为如下语句：

SELECT employee_id, last_name, department_name
FROM employees e JOIN  departments d
USING(department_id)

3. 单行函数

在 MySQL 中，函数分为内置函数与自定义函数；内置函数从实现的功能角度可以分为： 数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取 MySQL 信息函数、聚合函数等；

3.1. 单行函数的理解

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套
• 参数可以是一列或一个值

3.2. 数值函数

3.2.1. 基本函数

SELECT ABS(-123), ABS(32), SIGN(-23), SIGN(43), PI(), CEIL(32.32), CEILING(-43.23), FLOOR(32.32), FLOOR(-43.32), MOD(12,5)
FROM DUAL;
-- 123 32 -1 1 3.141593 33 -43 32 -44 2

SELECT ROUND(123.556), ROUND(123.556,0), ROUND(123.456,1), ROUND(123.456,-1)
FROM DUAL;
-- 124	124	123.5	120

# 第二个参数为负数时，从小数点向前查 n 位

SELECT TRUNCATE(123.456,0),TRUNCATE(123.456,1),TRUNCATE(123.456,-1)
FROM DUAL;
-- 123	123.4	120

3.2.2. 角度弧度互换函数

SELECT RADIANS(30), RADIANS(60), RADIANS(90), DEGREES(2*PI()), DEGREES(RADIANS(90))
FROM DUAL;
-- 0.5235987755982988	1.0471975511965976	1.5707963267948966	360	90

3.2.3. 三角函数

ATAN2(M, N) 函数返回两个参数的反正切值。 与 ATAN(X) 函数相比，ATAN2(M, N) 需要两个参数，例如有两个点 point(x1, y1) 和 point(x2, y2)，使用 ATAN(X) 函数计算反正切值为 ATAN((y2 - y1) / (x2 - x1))，使用 ATAN2(M, N) 计算反正切值则为 ATAN2(y2 - y1, x2 - x1)。由使用方式可以看出，当 x2 - x1 等于 0 时，ATAN(X) 函数会报错，而 ATAN2(M, N) 函数则仍然可以计算。

SELECT SIN(RADIANS(30)), DEGREES(ASIN(1)), TAN(RADIANS(45)), DEGREES(ATAN(1)), DEGREES(ATAN2(1,1))
FROM DUAL;
-- 0.49999999999999994	90	0.9999999999999999	45	45

3.2.4. 指数和对数

3.2.5. 进制间的转换

SELECT BIN(10), HEX(10), OCT(10), CONV(10, 2, 8)
FROM DUAL;
-- 1010	A	12	2

3.3. 字符串函数

注意：MySQL中，字符串的位置是从1开始的；

字符串去重

3.4. 日期与时间函数

3.4.1. 获取日期、时间

3.4.2. 日期与时间戳的转换

3.4.3. 获取月份、星期、星期数、天数等函数

3.4.4. 日期操作函数

Type 取值：

1. MICROSECOND：毫秒
2. SECOND：秒
3. MINUTER：分钟
4. HOUR：小时
5. DAY：天
6. WEEK：一年中的第几个星期
7. MONTH：一年中的第几个月
8. QUARTER：一年中的第几个季度
9. YEAR：年份
10. SECOND_MICROSECOND：秒和毫秒值
11. MINUTE_MICROSECOND：分钟和毫秒值
12. MINUTE_SECOND：分钟和秒值
13. HOUR_MICROSECOND：小时和毫秒值
14. HOUR_SECOND：小时和秒值
15. HOUR_MINUTE：小时和分钟值
16. DAY_MICROSECOND：天和毫秒值
17. DAY_SECOND：天和秒值
18. DAY_MINUTE：天和分钟值
19. DAY_HOUR：天和小时
20. YEAR_MONTH：年和月

SELECT EXTRACT(MINUTE FROM NOW()), EXTRACT(WEEK FROM NOW()), EXTRACT(QUARTER FROM NOW()), EXTRACT(MINUTE_SECOND FROM NOW())
FROM DUAL;
-- 43	22	2	4353

3.4.5. 时间和秒钟的转换函数

3.4.6. ? 计算日期和时间的函数

Type 取值：

1. SECOND：秒
2. MINUTER：分钟
3. HOUR：小时
4. DAY：日
5. MONTH：月
6. YEAR：年
7. MINUTE_SECOND：分钟和秒
8. HOUR_SECOND：小时和秒
9. HOUR_MINUTE：小时和分钟
10. DAY_SECOND：日和秒
11. DAY_MINUTE：日和分钟
12. DAY_HOUR：日和小时
13. YEAR_MONTH：年和月

举例：查询 7 天内的新增用户数有多少？

SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

3.4.7. 日期的格式化与解析

上述 非GET_FORMAT 函数中fmt参数常用的格式符：

3.5. 流程控制函数

流程控制函数可以根据不同的条件，执行不同的处理流程；

SELECT IF(1 > 0, '正确','错误');
-- 正确

SELECT IFNULL(null,'Hello Word');
-- Hello Word

SELECT CASE
	WHEN 1 > 0
	THEN '1 > 0'
	WHEN 2 > 0
	THEN '2 > 0'
	ELSE '3 > 0'
END;
-- 1 > 0

SELECT CASE 1
	WHEN 1 THEN '我是 1'
	WHEN 0 THEN '我是 0'
	ELSE '你是谁'
END;
-- 我是 1

3.6. 加密与解密函数

加密与解密函数用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取；

3.7. MySQL 信息函数

MySQL 内置了一些可以查询 MySQL 信息的函数，这些函数主要用于帮助数据库开发或运维人员更好的对数据库进行维护工作；

SELECT VERSION(),  CONNECTION_ID(), DATABASE(), SCHEMA(), USER(),
CURRENT_USER(), CHARSET('xbai-hang'), COLLATION('xbai-hang');

-- 8.0.22	1127	atguigudb	atguigudb	root@localhost	root@localhost	utf8mb4	utf8mb4_0900_ai_ci

3.8. 其他函数

FORMAT(value, n) 函数与 ROUND 一样是四舍五入，当 n ≤ 0 时，保留至整数位；

IP 地址转换：

以 192.168.1.100 为例，计算方式为：

• 192 * (256 ^ 3) + 168 * (256 ^ 2) + 1 * (256 ^ 1) + 100 * (256 ^ 0 = 1)

SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);
-- 10000	22B8	NULL

SELECT INET_ATON('192.168.1.100');
-- 3232235876

SELECT INET_NTOA('3232235876');
-- 192.168.1.100

SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
-- utf8mb4	utf8

4. 聚合函数

4.1. 聚合函数介绍

聚合（分组）函数是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个的值；常用的聚合函数有如下五个；

前四个字段用法很简单，COUNT() 函数的作用如下：

• 计算指定字段（一般使用 COUNT(1) 或 COUNT(*)）在查询结果中出现的个数；
  • 指定具体字段是不计算 NULL 值，可能会出现条数记录偏差；

4.2. GROUP BY（分组）

1. 基本使用

可以使用 GROUP BY子句将表中的数据分成若干组

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

明确：WHERE一定放在FROM后面

在 SELECT 列表中所有未包含在组函数中的列都 必须 包含在 GROUP BY子句中

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;

2. GROUP BY 中使用 WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量；

注意： 当使用ROLLUP时，不能同时使用 ORDER BY 子句进行结果排序，即 ROLLUP 和 ORDER BY 是互相排斥的；

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

4.3. HAVING

1. 基本使用

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

• 如果过滤条件中使用了聚合函数，则必须使用 HAVING 来替换 WHERE，否则会报错；
• HAVING 必须声明在 GROUP BY 的后面

2. WHERE 与 HAVING 对比

4.4. SELECT 执行过程

1. 查询的结构

#方式1： 
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,... 

#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#其中： 
#（1）from：从哪些表中筛选 
#（2）on：关联多表查询时，去除笛卡尔积 
#（3）where：从表中筛选的条件
#（4）group by：分组依据 
#（5）having：在统计结果中再次筛选 
#（6）order by：排序 
#（7）limit：分页

2. SELECT 执行顺序

1. 关键字顺序不能颠倒

   SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
   

2. SELECT 语句的执行顺序

   SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
   FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
   WHERE height > 1.80 # 顺序 2
   GROUP BY player.team_id # 顺序 3
   HAVING num > 2 # 顺序 4 
   ORDER BY num DESC # 顺序 6
   LIMIT 2 # 顺序 7
   

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入；这些步骤隐对用户是透明的；

5. 子查询

• 子查询是指一个查询语句嵌套在另一个查询语句内部的查询；
• 子查询的使用增强了 SQL 的查询能力，很多时候查询需要从结果集中获取数据，或者需要从一个表中先计算得出一个数据结果，然后与这个数据结果进行比较；

5.1. 基本使用

# 需求：谁的工资比 Abel 的高
SELECT last_name, salary
FROM employees
WHERE salary > (
				SELECT salary
				FROM employees
				WHERE last_name = 'Abel'
				);		

• 子查询（内查询）在主查询之前一次执行完成。
• 子查询的结果被主查询（外查询）使用 。
• 注意事项
  • 子查询要包含在括号内
  • 将子查询放在比较条件的右侧
  • 单行操作符对应单行子查询，多行操作符对应多行子查询

子查询的分类

分类方式1：

• 按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询

分类方式2：

• 按内查询是否被执行多次，将子查询划分为相关(或关联)子查询、不相关(或非关联)子查询

4.2. 单行子查询

单行子查询可以使用的比较操作符有：=、>、>=、<、<=、<>；

4.2.1. 代码示例

题目：查询工资大于 149 号员工工资的员工信息

SELECT employee_id, last_name, salary 
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
  	WHERE employee_id = 149
);

题目：查询 job_id 与 141 号员工相同，salary 比 143 号员工多的员工姓名，job_id和工资

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
	)
AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 143
	);

4.2.2. HAVING 中的子查询

题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
	);

4.2.3. CASE 中的子查询

题目：显示员工的 employee_id, last_name和location。其中，若员工 department_id 与 location_id 为 1800 的 department_id 相同，则location 为 ‘Canada’，其余则为 ‘USA’

SELECT employee_id, last_name, (
    CASE department_id 
    WHEN (
        SELECT department_id
        FROM departments
        WHERE location_id = 1800
    ) THEN 'Canada' ELSE 'USA' END
) location
FROM employees;

4.2.4. 子查询中的空值问题

子查询不返回任何行

SELECT last_name, job_id
FROM employees
WHERE job_id = ( # 内查询结果为空
		SELECT job_id
		FROM employees
		WHERE last_name = 'Haas' # 没有这个人
);

4.2.5. 非法使用子查询

mysql> SELECT employee_id, last_name
    -> FROM employees
    -> WHERE salary = ( # ERROR: 多行子查询使用单行比较符
    ->     SELECT MIN(salary)
    ->     FROM employees
    ->     GROUP BY department_id
    -> );
ERROR 1242 (21000): Subquery returns more than 1 row

4.3. 多行子查询

多行子查询可以使用的比较操作符有：IN、ANY、ALL、SOME

4.3.1. 代码示例

题目：查询其他 job_id 中比 job_id 为 ‘IT_PROG’ 部门所有工资低的员工的员工号

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
	);

题目：查询平均工资最低的部门 id

# 方法一：limit
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1;

# 方法二：单行子查询
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
				SELECT MIN(avg_sal)
				FROM (
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						) t_dept_avg_sal
			);

# 方法三：多行子查询
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
			);

MySQL中聚合函数是不能嵌套使用的；

4.3.2. 空值问题

SELECT last_name
FROM employees
WHERE employee_id NOT IN (
					SELECT manager_id
					FROM employees
  				# WHERE manager_id IS NOT NULL
					);

4.4. 相关子查询

如果 子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询；

4.4.1. 代码示例

题目：查询员工中工资大于本部门平均工资的员工的 last_name, salary, department_id

# 使用相关子查询
SELECT last_name, salary, department_id
FROM employees e1
WHERE salary > (
	SELECT AVG(salary)
	FROM employees e2
	WHERE e2.department_id = e1.department_id
	);
	
# 在 FROM 中声明子查询
SELECT e.last_name, e.salary, e.department_id
FROM employees e, (
		SELECT department_id, AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal

题目：查询员工的 id、salary，按照 department_name 排序

SELECT employee_id, salary
FROM employees e
ORDER BY (
		SELECT department_name
		FROm departments d
		WHERE e.department_id = d.department_id
		) ASC;

结论：在SELECT中，除了GROUP BY 和 LIMIT 之外，其他位置都可以声明子查询！

4.4.2. EXISTS 与 NOT EXISTS 关键字

• 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
• 如果在子查询中不存在满足条件的行：
  • 条件返回 FALSE
  • 继续在子查询中查找
• 如果在子查询中存在满足条件的行：
  • 不在子查询中继续查找
  • 条件返回 TRUE
• NOT EXISTS 关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

题目：查询公司管理者的 employee_id，last_name，job_id，department_id 信息

# 自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2 
WHERE e1.employee_id = e2.manager_id;

# 相关子查询 --- EXISTS 关键字
SELECT employee_id, last_name, job_id, department_id
FROM employees e
WHERE EXISTS (
		SELECT *
		FROM employees e2
		WHERE e.employee_id = e2.manager_id
		);

题目：查询 departments 表中，不存在于 employees 表中的部门的 department_id 和 department_name

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
		SELECT *
		FROM employees e
		WHERE d.department_id = e.department_id
		);

一般情况建议使用自连接，因为许多 DBMS 对于自连接的处理速度要比子查询快得多；

6. SQL 之 DDL、DML、DCL

• DDL（Data Definition Language、数据定义语言）： 这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构；
  • 主要的关键字：CREATE、DROP、ALTER
• DML（Data Manipulation Language、数据操作语言）： 用于添加、删除、更新和查询数据库记录，并检查数据完成性；
  • 主要的关键字：INSERT、DELETE、UPDATE、SELECT
  • SELECT 是 SQL 语言的基础，最为重要
• DCL（Data Control Language、数据控制语言）： 用于定义数据库、表、字段、用户的访问权限和安全级别；
  • 主要的关键字：GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT

因为查询语句使用的非常频繁，所以也有把查询语言单独分为一类： DQL（Data Select Language，数据查询语言）

还有将 COMMIT、ROLLBACK 称为 TCL（Transaction Control Language，事务控制语言）

6.1. 创建和管理数据库

6.1.1. 创建数据库

• 创建数据库

  CREATE DATABASE 数据库名;
  

• 创建数据库并指定字符集

  CREATE DATABASE 数据库名 CHARACTER SET 字符集;
  

• 判断数据库是否存在，不存在则创建数据库

  CREATE DATABASE IF NOT EXISTS 数据库名;
  

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的

6.1.2. 使用数据库

• 查看当前所有的数据库

  SHOW DATABASES; # 有一个S，代表多个数据库
  

• 查看当前正在使用的数据库

  SELECT DATABASE(); # 使用的一个 mysql 中的全局函数
  

• 查看指定库下所有的表

  SHOW TABLES FROM 数据库名;
  

• 查看数据库的创建信息

  SHOW CREATE DATABASE 数据库名;
  或者： 
  SHOW CREATE DATABASE 数据库名\G
  

• 使用/切换数据库

  USE 数据库名;
  

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

6.1.3. 修改数据库

• 更改数据库字符集

  ALTER DATABASE 数据库名 CHARACTER SET 字符集; # 比如：gbk、utf8等
  

6.1.4. 删除数据库

• 方式1：删除指定的数据库

  DROP DATABASE 数据库名;
  

• 方式2：删除指定的数据库（推荐）

  DROP DATABASE IF EXISTS 数据库名;
  

6.2. 创建和管理表

6.2.2. 创建表

创建方式一

• 必须具备：

  • CREATE TABLE权限
  • 存储空间

• 语法格式：

  CREATE TABLE [IF NOT EXISTS] 表名( 
      字段1 数据类型 [约束条件] [默认值], 
      字段2 数据类型 [约束条件] [默认值], 
      字段3 数据类型 [约束条件] [默认值], 
      ··· ···
      [表约束条件] 
  );
  

创建方式二

• 使用 AS 选项，将创建表和插入数据结合起来

  CREATE TABLE table [(column, column...)]
  AS subquery;
  

• 指定的列和子查询中的列要一一对应

• 通过列名和默认值定义列

  CREATE TABLE emp1 AS SELECT * FROM employees; 
  
  CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1 = 2; -- 创建的 emp2 是空表
  

查看数据表结构

在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用 DESCRIBE/DESC 语句查看数据表结构，也支持使用 SHOW CREATE TABLE 语句查看数据表结构。

语法格式如下：

SHOW CREATE TABLE 表名

使用SHOW CREATE TABLE 语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

6.2.3. 修改表

追加一个列

语法格式如下：

ALTER TABLE 表名
ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];

修改一个列

• 修改字段数据类型、长度、默认值、位置的语法格式如下：

ALTER TABLE 表名
MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值][FIRST|AFTER 字段名 2];

• 对默认值的修改只影响今后对表的修改

重命名一个列

• 语法格式如下：

ALTER TABLE 表名
CHANGE [column] 列名 新列名 新数据类型;

删除一个列

• 语法格式如下：

ALTER TABLE 表名 
DROP [COLUMN] 字段名;

6.2.4. 重命名表

• 方式一：使用RENAME（推荐）

RENAME TABLE emp TO myemp;

• 方式二：

ALTER table dept
RENAME [TO] detail_dept; -- [TO]可以省略

6.2.5. 删除表

• 在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。

• 数据和结构都被删除

• 所有正在运行的相关事务被提交

• 所有相关索引被删除

• 语法格式：

  DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
  

• DROP TABLE 语句不能回滚

6.2.6. 清空表

• TRUNCATE TABLE 语句：

  • 删除表中所有的数据
  • 释放表的存储空间

• 举例：

  TRUNCATE TABLE detail_dept;
  

• TRUNCATE 语句 不能回滚 ，而使用 DELETE 语句删除数据，可以回滚

• COMMIT：提交数据。一旦执行 COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。

• ROLLBACK：回滚数据。一旦执行 ROLLBACK，则可以实现数据的回滚。回滚到最近的一次 COMMIT之后。

SET autocommit = FALSE;

DELETE FROM emp2;
#TRUNCATE TABLE emp2; 

SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;

DDL 和 DML 的说明

• DDL 的操作一旦执行，就不可回滚。指令 SET autocommit = FALSE 对 DDL 操作失效。(因为在执行完 DDL 操作之后，一定会执行一次 COMMIT。而此 COMMIT 操作不受 SET autocommit = FALSE 影响的。)

• DML 的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行 DML 之前，执行了 SET autocommit = FALSE，则执行的 DML 操作就可以实现回滚。

阿里开发规范：

【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

6.3. 内容拓展

6.3.1. 阿里巴巴《Java 开发手册》之 MySQL 字段命名

• 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

  • 正例：aliyun_admin，rdc_config，level3_name
  • 反例：AliyunAdmin，rdcConfig，level_3_name

• 【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。

• 【强制】表必备三字段：id, gmt_create, gmt_modified。

  • 说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新

• 【推荐】表的命名最好是遵循 “业务名称_表的作用”。

  • 正例：alipay_task 、 force_project、 trade_config

• 【推荐】库名与应用名称尽量一致。

• 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

6.3.2. 如何理解清空表、删除表等操作需谨慎？！

表删除 操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有任何的确认信息提示，因此执行删除操作时应当慎重。在删除表前，最好对表中的数据进行 备份 ，这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。

同样的，在使用 ALTER TABLE 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的 备份 ，因为数据库的改变是 无法撤销 的，如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。

6…3.3. MySQL8新特性—DDL的原子化

在MySQL 8.0版本中，InnoDB 表的 DDL 支持事务完整性，即 DDL操作要么成功要么回滚。DDL 操作回滚日志写入到 data dictionary 数据字典表 mysql.innodb_ddl_log（该表是隐藏的表，通过 show tables 无法看到）中，用于回滚操作。通过设置参数，可将 DDL 操作日志打印输出到 MySQL 错误日志中。

6.4. 表数据处理

6.4.1. 插入数据

方式1：VALUES 的方式添加

使用这种语法一次只能向表中插入一条数据；

1. 为表的所有字段按默认顺序插入数据

   INSERT INTO 表名 
   VALUES (value1,value2,....);
   

2. 为表的指定字段插入数据（推荐）

   在 INSERT 语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值

   INSERT INTO 表名(column1 [, column2, …, columnn]) 
   VALUES (value1 [,value2, ··· , valuen]);
   

3. 同时插入多条记录（推荐）

   INSERT INTO table_name[(column1 [, column2, …, columnn])]
   VALUES 
   (value1 [,value2, …, valuen]), 
   (value1 [,value2, …, valuen]), 
   ···
   (value1 [,value2, …, valuen]);
   

方式2：将查询结果插入到表中

基本语法格式如下：

INSERT INTO 目标表名 (tar_column1 [, tar_column2, …, tar_columnn]) 
SELECT (src_column1 [, src_column2, …, src_columnn]) 
FROM 源表名
[WHERE condition]

• 在 INSERT 语句中加入子查询
• 不必书写 VALUES 子句
• 子查询中的值列表应与 INSERT 子句中的列名对应

INSERT INTO emp2 
SELECT * 
FROM employees 
WHERE department_id = 90;

6.4.2. 更新数据

• 使用 UPDATE 语句更新数据。语法如下：

  UPDATE table_name 
  SET column1=value1, column2=value2,···, column=valuen 
  [WHERE condition]
  

• 可以一次更新多条数据。

• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

• 使用 WHERE 子句指定需要更新的数据。

• 如果省略 WHERE 子句，则表中的所有数据都将被更新。

6.4.3. 删除数据

• 使用 DELETE 语句从表中删除数据

  DELETE FROM table_name
  [WHERE <condition>];
  

• 使用 WHERE 子句删除指定的记录。

• 如果省略 WHERE 子句，则表中的全部数据将被删除

6.4.4. 计算列

计算列：计算列是 MySQL8 中的新特性，某一列的值是通过别的列计算得来的。

例如，a 列值为 1、b 列值为 2，c 列不需要手动插入，定义 a + b 的结果为 c 的值，那么 c 就是计算列，是通过别的列计算得来的；

CREATE TABLE tb1( 
    id INT, 
    a INT, 
    b INT, 
    c INT GENERATED ALWAYS AS (a + b) VIRTUAL 
);

插入演示数据，语句如下：

INSERT INTO tb1(a, b)
VALUES (100, 200);

查询数据表tb1中的数据，结果如下：

mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
| NULL |  100 |  200 |  300 |
+------+------+------+------+

更新数据中的数据，语句如下：

mysql> UPDATE tb1 SET a = 500;
mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
| NULL |  500 |  200 |  700 |
+------+------+------+------+

7. MySQL 数据类型

7.1. MySQL中的数据类型

常见 数据类型的属性 ，如下：

7.2. 整数类型

7.2.1. 类型介绍

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。

7.2.2. 可选属性

int 类型默认显示宽度为 int(11)，无符号 int 类型默认显示宽度为 int(10)。因为负号占了一个数字位。

整数类型的可选属性有三个：

1. M: 表示显示宽度，取值范围是(0, 255)。当数据宽度小于指定位数的时候在数字前面需要用字符填满宽度。

   需配合 ZEROFILL 属性使用，否则指定显示宽度无效。

   显示宽度与类型可以存储的值范围无关。

2. UNSIGNED: 无符号类型（非负）；

3. ZEROFILL: 用 0 左填充,（如果某列是 ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性）

7.2.3. 适用场景

TINYINT：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。

SMALLINT：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。

MEDIUMINT：用于较大整数的计算，比如车站每日的客流量等。

INT、INTEGER：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。

BIGINT：只有当处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

7.3. 浮点类型

7.3.1. 类型介绍

浮点数和定点数类型的特点是可以处理小数

• FLOAT 表示单精度浮点数；
• DOUBLE 表示双精度浮点数；

• REAL 默认是 DOUBLE，把 SQL 模式设定为启用 REAL_AS_FLOAT，此时 REAL 就是 FLOAT；

  SET sql_mode = "REAL_AS_FLOAT";
  

7.3.2. 数据精度说明

在编程中，如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以要避免用 = 来判断两个数是否相等；

对于浮点类型，在 MySQL 中单精度值使用4个字节，双精度值使用8个字节。

FLOAT(M, D)、DOUBLE(M, D) 中 D 是小数位，M - D 是整数位；

• 如果存储时，整数部分超出了范围，MySQL就会报错，不允许存这样的值
• 如果存储时，小数点部分若超出范围，就分以下情况：
  • 若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。例如在 FLOAT(5, 2) 列内插入 999.009，近似结果是 999.01。
  • 若四舍五入后，整数部分超出范围，则MySQL报错，并拒绝处理。如 FLOAT(5, 2) 列内插入 999.995 和 -999.995 都会报错。

MySQL 存储浮点数的格式为：符号(S)、尾数(M)和阶码(E)。无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。因此， 所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。

7.4. 定点数类型

7.4.1. 类型介绍

• MySQL中的定点数类型只有 DECIMAL 一种类型。

使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M 被称为精度，D 被称为标度。0<= M <=65， 0 <= D <= 30，D < M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。

• DECIMAL(M, D)的最大取值范围与 DOUBLE 类型一样，但是有效的数据范围是由 M 和 D 决定的。

• 定点数在 MySQL 内部是以 字符串 的形式进行存储，这就决定了它一定是精准的。

• 当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

• 浮点数 vs 定点数

  • 浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）
  • 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景 （比如涉及金额计算的场景）

7.4.2. 开发中经验

“由于 DECIMAL 数据类型的精准性，在我们的项目中，除了极少数（比如商品编号）用到整数类型外，其他的数值都用的是 DECIMAL，原因就是这个项目所处的零售行业，要求精准，一分钱也不能差。 ” ——来自某项目经理

7.5. 位类型：BIT

BIT类型中存储的是二进制值，类似 010110。

BIT 类型，如果没有指定(M)，默认是 1 位。这个 1 位，表示只能存 1 位的二进制值。这里(M)是表示二进制的位数，位数最小值为 1，最大值为 64。

使用 b + 0 查询数据时，可以直接查询出存储的十进制数据的值。

7.6. 日期与时间类型

MySQL 有多种表示日期和时间的数据类型，不同的版本可能有所差异，MySQL8.0 版本支持的日期和时间类型主要有：YEAR类型、TIME类型、DATE类型、DATETIME 类型和 TIMESTAMP 类型。

• YEAR 类型通常用来表示年
• DATE 类型通常用来表示年、月、日
• TIME 类型通常用来表示时、分、秒
• DATETIME 类型通常用来表示年、月、日、时、分、秒
• TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

7.1. TIMESTAMP类型

TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是YYYY-MM-DD HH:MM:SS，需要4个字节的存储空间。但是 TIMESTAMP 只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间。

• 存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

TIMESTAMP和DATETIME的区别：

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离 1970-1-1 0:0:00 毫秒的毫秒值。
• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
• TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而 DATETIME 则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

7.2. 开发中经验

用得最多的日期时间类型，就是DATETIME。虽然 MySQL 也支持 YEAR（年）、 TIME（时间）、DATE（日期），以及 TIMESTAMP 类型，但是在实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，很不容易记，而且查询的时候，SQL 语句也会更加复杂。

此外，一般存注册时间、商品发布时间等，不建议使用 DATETIME 存储，而是使用时间戳，因为DATETIME 虽然直观，但不便于计算。

7.7. 文本字符串类型

7.7.1. CHAR 与 VARCHAR

M 代表的都是字符个数（MySQL 5.0 以上），字符占用字节数 len 如下：

• ASCII码：
  一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间。一个二进制数字序列，在计算机中作为一个数字单元，一般为 8 位二进制数，换算为十进制。最小值0，最大值255。如一个ASCII码就是一个字节。
• UTF-8 编码：
  一个英文字符等于一个字节，一个中文（含繁体）等于三个字节。
• Unicode 编码： 一个英文等于两个字节，一个中文（含繁体）等于两个字节。

CHAR 类型：

• CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
• 如果保存时，数据的实际长度比 CHAR 类型声明的长度小，则会在 右侧填充 空格以达到指定的长度。当MySQL检索 CHAR 类型的数据时，CHAR 类型的字段会去除尾部的空格。
• 定义 CHAR 类型字段时，声明的字段长度即为 CHAR 类型字段所占的存储空间的字节数。

VARCHAR 类型：

• VARCHAR(M) 定义时，必须指定 长度M，否则报错。
• MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。
• 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

7.7.2. TEXT

由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。

开发中经验：

TEXT 文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR， VARCHAR 来代替。还有 TEXT 类型不用加默认值，加了也没用。而且 text 和 blob 类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以 频繁使用的表不建议包含 TEXT 类型字段，建议单独分出去，单独用一个表。

7.8. ENUM类型

ENUM 类型也称为枚举类型，取值范围需要在定义字段时进行指定。ENUM 只允许从成员中选取单个值，不能一次选取多个值。

• 当 ENUM 类型包含 1～255 个成员时，需要 1 个字节的存储空间；
• 当 ENUM 类型包含 256～65535 个成员时，需要 2 个字节的存储空间。
• ENUM 类型的成员个数的上限为 65535 个。

CREATE TABLE test_enum( 
    season ENUM('春','夏','秋','冬','unknow')
);

INSERT INTO test_enum 
VALUES('春'), ('秋');

# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum 
VALUES('1'),(3);

# 当ENUM 类型的字段没有声明为NOT NULL时，插入 NULL 也是有效的
INSERT INTO test_enum
VALUES(NULL);

7.9. SET类型

SET 表示一个字符串对象，可以包含 0 个或多个成员，但成员个数的上限为 64 。设置字段值时，可以取取值范围内的 0 个或多个值。

当SET类型包含的成员个数不同时，其所占用的存储空间也是不同的，具体如下：

SET 类型在存储数据时成员个数越多，其占用的存储空间越大。注意：SET 类型在选取成员时，可以一次选择多个成员，这一点与 ENUM 类型不同。

CREATE TABLE test_set(
    s SET ('A', 'B', 'C')
);

INSERT INTO test_set (s)
VALUES ('A'), ('A,B');

#插入重复的SET类型成员时，MySQL会自动删除重复的成员 
INSERT INTO test_set (s) 
VALUES ('A,B,C,A');

7.10. 二进制字符串类型

MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据。

7.10.1. BINARY 与 VARBINARY

BINARY 和 VARBINARY 存储的是二进制字符串

1. BINARY(M) 固定长度的二进制字符串，M 表示最多能存储的字节数，取值范围是0~255个。如果未指定(M)，表示只能存储1个字节。例如BINARY (8)，表示最多能存储 8 个字节，若字段值不足(M)个字节，则 右填充 \0 补齐指定长度
2. VARBINARY(M) 可变长度的二进制字符串，M 表示最多能存储的字节数，总字节数不能超过行的字节长度限制 65535，另外还要考虑额外字节开销，VARBINARY 类型的数据除了存储数据本身外，还需要 1 或 2 个字节来存储数据的字节数。VARBINARY类型必须指定(M)，否则报错。

7.10.2. BLOB 类型

BLOB是一个 二进制大对象，可以容纳可变数量的数据。

需要注意的是，在实际工作中，往往不会在 MySQL 数据库中使用 BLOB 类型存储大对象数据，通常会将图片、音频和视频文件存储到资源服务器上，并将图片、音频和视频的访问路径存储到 MySQL 中。

7.11. JSON 类型

JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

CREATE TABLE test_json(
    js json 
);

INSERT INTO test_json (js) 
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');

SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province' AS province, js -> '$.address.city' AS city 
FROM test_json;

7.12. 空间类型

MySQL 的空间数据类型（Spatial Data Type）对应于OpenGIS 类，包括单值类型：GEOMETRY、POINT、 LINESTRING、POLYGON 以及集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION 。

7.13. 选择建议

在定义数据类型时，如果确定是整数，就用INT； 如果是小数，一定用定点数类型DECIMAL(M,D)； 如果是日期与时间，就用DATETIME。

这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

关于字符串的选择，建议参考如下阿里巴巴的《Java开发手册》规范：

阿里巴巴《Java开发手册》之MySQL数据库：

• 任何字段如果为非负数，必须是 UNSIGNED

• 【强制】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。

  • 说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并分开存储。

• 【强制】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。

  【强制】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

8. 约束

8.1. 约束（constraint）概述

8.1.1. 为什么需要约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

为了保证数据的完整性，SQL规范以约束的方式对 表数据进行额外的条件限制。从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
• 域完整性（Domain Integrity）：例如：年龄范围 0 - 120，性别范围 男/女
• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的 5 倍。

8.1.2. 什么是约束

约束是表级的强制规定。

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

8.1.3. 约束的分类

• 根据约束数据列的限制，约束可分为：

  • 单列约束：每个约束只约束一列
  • 多列约束：每个约束可约束多列数据

• 根据约束的作用范围，约束可分为：

  • 列级约束：只能作用在一个列上，跟在列的定义后面
  • 表级约束：可以作用在多个列上，不与列一起，而是单独定义

• 根据约束起的作用，约束可分为：

  • NOT NULL 非空约束，规定某个字段不能为空
  • UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
  • PRIMARY KEY 主键(非空且唯一)约束
  • FOREIGN KEY 外键约束
  • CHECK 检查约束
  • DEFAULT 默认值约束

• 查看某个表已有的约束

#information_schema 数据库名（系统库） 
#table_constraints 表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints 
WHERE table_name = '表名称';

8.2. 非空约束

非空约束用于限定某个字段/某列的值 不允许为空 ，其关键字是 NOT NULL 作用域表对象的列上，只能限定某个列单独限定非空，不能组合非空；

8.2.1. 添加非空约束

建表时

CREATE TABLE 表名称( 
    字段名 数据类型, 
    字段名 数据类型 NOT NULL, 
    字段名 数据类型 NOT NULL 
);

建表后

ALTER TABLE 表名称 
MODIFY 字段名 数据类型 NOT NULL;

8.2.2. 删除非空约束

方式一

ALTER TABLE 表名称 
MODIFY 字段名 数据类型 NULL; 

方式二

ALTER TABLE 表名称 
MODIFY 字段名 数据类型;

8.3. 唯一性约束

唯一性约束用来限制某个字段/某列的 值不能重复 ，其关键字是 UNIQUE；

8.3.1. 特点

• 同一个表可以有多个唯一约束。
• 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
• 唯一性约束允许列值为空。
• 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
• MySQL会给唯一约束的列上默认创建一个唯一索引。

8.3.2. 添加（复合）唯一约束

建表时

# 某一个列
CREATE TABLE 表名称( 
  字段名 数据类型, 
  字段名 数据类型 UNIQUE [KEY], 
  字段名 数据类型 
);
# 多个列组合
CREATE TABLE 表名称( 
  字段名 数据类型, 
  字段名 数据类型, 
  字段名 数据类型, 
  
  [CONSTRAINT 约束名] UNIQUE [KEY](字段列表)  
);

建表后指定唯一键约束

字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的；

#方式1： 
ALTER TABLE 表名称
# 多个字段之间用逗号隔开
ADD [CONSTRAINT 约束名] UNIQUE [KEY](字段列表);

#方式2： 
ALTER TABLE 表名称 
MODIFY 字段名 字段类型 UNIQUE [KEY];

可以向声明为 unique 的字段上添加 null 值。而且可以多次添加 null

3.5 删除唯一约束

• 添加唯一性约束的列上也会自动创建唯一索引。
• 删除唯一约束只能通过删除唯一索引的方式删除。
• 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
• 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和(字段列表)中排在第一个的列名相同。也可以自定义唯一性约束名。

ALTER TABLE 表名称
DROP INDEX 索引名;

注意：可以通过 show index from 表名称; 查看表的索引

8.4. PRIMARY KEY 约束

主键约束用来唯一标识表中的一行记录，其关键词是 PRIMARY KEY；

8.4.1. 特点

• 主键约束相当于唯一约束 + 非空约束的组合，主键约束列不允许重复，也不允许出现空值
• 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
• 主键约束对应着表中的一列或者多列（复合主键）
• 如果是多列组合的复合主键约束，那么这些列 都不允许 为空值，并且组合的值不允许重复。
• MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
• 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
• 需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

8.4.2. 添加（复合）主键约束

建表时指定主键约束

CREATE TABLE 表名称( 
  字段名 数据类型 PRIMARY KEY, # 列级模式 
  字段名 数据类型, 
  字段名 数据类型 
);
CREATE TABLE 表名称( 
  字段名 数据类型, 
  字段名 数据类型, 
  字段名 数据类型, 
  [CONSTRAINT 约束名] PRIMARY KEY(字段列表) # 表级模式
);

建表后增加主键约束

ALTER TABLE 表名称 
ADD PRIMARY KEY(字段列表);

8.4.3. 删除主键约束

ALTER TABLE 表名称 
DROP PRIMARY KEY;

说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

在实际开发中，不会去删除表中的主键约束！

8.5. 自增列

自增列 AUTO_INCREMENT 的作用是使某个字段的值自增

8.5.1. 特点

1. 一个表最多只能有一个自增长列
2. 当需要产生唯一标识符或顺序值时，可设置自增长
3. 自增长列约束的列必须是键列 （主键列，唯一键列）
4. 自增约束的列的数据类型必须是 整数类型
5. 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

8.5.2. 指定自增约束

建表时

CREATE TABLE 表名称( 
    字段名 数据类型 PRIMARY KEY AUTO_INCREMENT, 
    字段名 数据类型 UNIQUE KEY NOT NULL, 
    字段名 数据类型 UNIQUE KEY, 
    字段名 数据类型 NOT NULL DEFAULT 默认值
);

CREATE TABLE表名称( 
    字段名 数据类型 DEFAULT 默认值, 
    字段名 数据类型 UNIQUE KEY AUTO_INCREMENT, 
    字段名 数据类型 NOT NULL DEFAULT 默认值, 
    PRIMARY KEY(字段名) 
);

建表后

ALTER TABLE 表名称
MODIFY 字段名 数据类型 AUTO_INCREMENT;

8.5.3. 删除自增约束

# ALTER TABLE 表名称 MODIFY 字段名 数据类型 AUTO_INCREMENT; # 给这个字段增加自增约束 
ALTER TABLE 表名称 MODIFY 字段名 数据类型; # 去掉 AUTO_INCREMENT 相当于删除

8.5.4. MySQL 8.0新特性—自增变量的持久化

在 MySQL 8.0之前，自增主键 AUTO_INCREMENT 的值如果大于 max(primary key) + 1，在MySQL重启后，会重置 AUTO_INCREMENT = max(primary key) + 1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。

在 MySQL 5.7 系统中，对于自增主键的分配规则，是由 InnoDB 数据字典内部一个 计数器 来决定的，而该计数器只在 内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

MySQL 8.0 将自增主键的计数器持久化到 重做日志 中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB 会根据重做日志中的信息来初始化计数器的内存值。

8.6. 外键约束

外键约束用于限定某个表的某个字段的引用完整性，其关键字为：FOREIGN KEY

8.6.1. 特点

1. 从表的外键列，必须引用/参考主表的主键或唯一约束的列，因为被依赖/被参考的值必须是唯一的
2. 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名，也可以指定外键约束名。
3. 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
4. 删表时，先删从表（或先删除外键约束），再删除主表
5. 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
6. 在“从表”中指定外键约束，并且一个表可以建立多个外键约束
7. 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。
8. 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）
9. 删除外键约束后，必须 手动 删除对应的索引

8.6.2. 添加外键约束

建表时

create table 主表名称( 
    字段1 数据类型 primary key, 
    字段2 数据类型 
);
create table 从表名称( 
    字段1 数据类型 primary key, 
    字段2 数据类型, 
    [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段) 
);
# (从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
# (从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

建表后

ALTER TABLE 从表名 
ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用 字段) [on update xx][on delete xx];

总结：约束关系是针对双方的

• 添加了外键约束后，主表的修改和删除数据受约束
• 添加了外键约束后，从表的添加和修改数据受约束
• 在从表上建立外键，要求主表必须存在
• 删除主表时，要求从表先删除，或将从表中外键引用该主表的关系先删除

8.6.3. 约束等级

• Cascade 方式：在父表上 update/delete 记录时，同步 update/delete 掉子表的匹配记录
• Set null 方式：在父表上 update/delete 记录时，将子表上匹配记录的列设为 null，但是要注意子表的外键列不能为 not null
• No action 方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行 update/delete 操作
• Restrict 方式：默认值，同 no action， 都是立即检查外键约束
• Set default 方式：父表有变更时，子表将外键列设置成一个默认的值，但 Innodb 不能识别

对于外键约束，最好是采用：ON UPDATE CASCADE ON DELETE RESTRICT的方式。

create table emp( 
    eid int primary key, #员工编号 
    ename varchar(5), #员工姓名 
    deptid int, #员工所在的部门
    foreign key (deptid) references dept(did) ON UPDATE CASCADE ON DELETE RESTRICT # 把修改操作设置为级联修改等级，把删除操作设置为Restrict等级 
);

8.6.4. 删除外键约束

# (1) 第一步先查看约束名和删除外键约束 
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称'; # 查看某个表的约束名 

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名; 

#（2）第二步查看索引名和删除索引。（注意，只能手动删除） 
SHOW INDEX FROM 表名称; # 查看某个表的索引名 

ALTER TABLE 从表名 DROP INDEX 索引名;

8.6.5. 开发场景

问题1：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？

答：不是的

问题2：建和不建外键约束有什么区别？

答：建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。

不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的引用完整性，只能依靠程序员的自觉，或者是在Java程序中进行限定。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。

问题3：那么建和不建外键约束和查询有没有关系？

答：没有

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以， MySQL 允许不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。

8.6.6. 阿里开发规范

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的 插入速度。

8.7. Check 约束

Check 约束用于检查某个字段的值是否符合要求，一般指的是值的范围，其关键字为 CHECK；

MySQL5.7 可以使用 check 约束，但 check 约束对数据验证没有任何作用；添加数据时，没有任何错误或警告；

MySQL 8.0中可以使用 check 约束。

CREATE TABLE temp( 
    id INT AUTO_INCREMENT, 
    NAME VARCHAR(20), 
    age INT CHECK(age > 20), 
    gender char CHECK ('男' OR '女'),
    PRIMARY KEY(id) 
);

8.8 DEFAULT约束

给某个字段/某列指定默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值，其关键字为 DEFAULT

8.8.1. 添加默认值约束

建表时

create table 表名称(
    字段名 数据类型 default 默认值 , 
    字段名 数据类型 not null default 默认值, 
    字段名 数据类型 not null default 默认值, 
    primary key(字段名),
    unique key(字段名) 
);
# 说明：默认值约束一般不在唯一键和主键列上加

建表后

alter table 表名称 
modify 字段名 数据类型 default 默认值; 
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了 
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了 
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

8.8.2. 删除默认值约束

alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束 
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束

8.9. 面试

面试1、为什么建表时，加 not null default '' 或 default 0

答：不想让表中出现 null 值。

面试2、为什么不想要 null 的值

答：

1. 不好比较。null 是一种特殊值，比较时只能用专门的 is null 和 is not null 来比较。碰到运算符，通常返回 null。
2. 效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ‘’ 或 default 0

面试3、带 AUTO_INCREMENT 约束的字段值是从 1 开始的吗？

在 MySQL 中，默认 AUTO_INCREMENT 的初始值是1，每新增一条记录，字段值自动加 1。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定 id 值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。

面试4、并不是每个表都可以任意选择存储引擎？

外键约束（FOREIGN KEY）不能跨引擎使用。

MySQL 支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。

开发者涨薪指南

48位大咖的思考法则、工作方式、逻辑体系