MySQL

一、简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的关系型数据库管理系统应用软件之一。

二、SQL语言

SQL（Structured Query Language）结构化查询语言，用于存取数据、更新、查询和管理关系型数据库系统的程序设计语言。对于数据库的操作，需要进入MySQL环境下进行指令输入，并在一句指令的末尾使用 ; 结束。

2.1 基本命令

查看MySQL中的所有数据库：

SHOW DATABASES;

MySQL中自带的几个数据库：

创建自定义数据库：

CREATE DATABASE mydb1;  # 创建一个名为 mydb1 的数据库
CREATE DATABASE mydb2 CHARACTER SET utf8;  # 创建数据库时设置编码格式为utf8
CREATE DATABASE IF NO EXISTS mydb3;  # 如果mydb3数据库不存在则创建，如果存在则不创建

查看创建数据库时的信息：

即查看该数据库是用什么命令创建的，编码格式是什么

SHOW CREATE DATABASE mydb1;

修改数据库：

ALTER DATABASE mydb1 CHARACTER SET utf8;  # 修改mydb1数据库的编码格式为utf8

删除数据库：

DROP DATABASE mydb1;  # 删除mydb1数据库

使用数据库：

USE mydb2;  # 选择操作mydb2数据库

查看当前所使用的数据库：

SELECT database();

三、数据查询

3.1 数据库表的基本结构

关系结构数据库是以表格（Table）进行数据存储的，表格由“行”和“列”组成。

执行查询语句返回的结果集是一张虚拟表。

3.2 基本查询

语法：SELECT 列名 FROM 表名（当要查询多个列时使用逗号将多个列名隔开）

3.2.1 查询部分列

# 查询员工表中所有员工的编号、名字、邮箱
SELECT employee_id, first_name, email FROM t_employees;

3.2.2 查询所有列

# 查询员工表中所有员工的所有信息(所有列)
SELECT * FROM t_employees;

注意：生产环境下，优先使用列名查询，*的方式需要转换成全列名，效率低、可读性差。

3.2.3 对列中的数据进行运算

# 查询员工表中所有员工的编号、名字、年薪
SELECT employee_id, first_name, salary*12 FROM t_employees;

注意：在MySQL的运算中仅可以使用加减乘除；%在MySQL中是占位符，而非模运算符。

3.2.4 列的别名

# 查询员工表中所有员工的编号、名字、年薪(这时第三列的列名就是salary*12, 可读性差, 需要起一个别名)
SELECT employee_id, first_name, salary*12 FROM t_employees;

# 查询员工表中所有员工的编号、名字、年薪
SELECT employee_id AS '编号', first_name AS '名字', salary*12 AS '年薪' FROM t_employees;

3.2.5 查询结果去重

# 加上DISTINCT用于显示去重后的查询结果
SELECT DISTINCT manager_id FROM t_employees;

3.3 排序查询（ORDER BY）

语法：SELECT 列名 FROM 表名 ORDER BY 排序列 [排序规则]

排序规则（排序规则可以不写，不写默认升序）：

• ASC：升序
• DESC：降序

3.3.1 依据单列排序

# 查询员工的编号、名字、薪资，按照工资高低进行升序排序
SELECT employee_id, first_name, salary FROM t_employees ORDER BY salary ASC;

# 查询员工的编号、名字、薪资，按照工资高低进行降排序
SELECT employee_id, first_name, salary FROM t_employees ORDER BY salary DESC;

3.3.2 依据多列排序

当第一个排序列的值相同时，可以依据第二个排序列的值来排序

# 查询员工的编号、名字、薪资，按照工资高低进行降序排序，当薪资相同时按照编号进行升序排序
SELECT employee_id, first_name, salary FROM t_employees 
ORDER BY salary DESC, employee_id ASC;

3.4 条件查询

语法：SELECT 列名 FROM 表名 WHERE 条件 （在查询结果中筛选符合条件的结果，条件为布尔表达式）

3.4.1 等值判断（=）

# 查询薪资是10000的员工信息（编号、名字、薪资）
SELECT employee_id, first_name, salary FROM t_employees
WHERE salary = 10000;

注意：与Java中的等值判断（==）不同，MySQL中等值判断使用的是（=）

3.4.2 逻辑判断（AND、OR、NOT）

# 查询薪资是10000并且提成是0.3的员工信息
SELECT employee_id, first_name, salary FROM t_employees
WHERE salary = 10000 AND commission_pct = 0.3;

# 查询薪资是10000或者提成是0.3的员工信息
SELECT employee_id, first_name, salary FROM t_employees
WHERE salary = 10000 OR commission_pct = 0.3;

# 查询薪资不是10000的员工信息
SELECT employee_id, first_name, salary FROM t_employees
WHERE NOT salary = 10000;

3.4.3 不等值判断（>、<、>=、<=、!=、<>）

# 查询员工的薪资在6000~10000之间的员工信息
SELECT employee_id, first_name, salary FROM t_employees
WHERE salary >= 6000 AND salary <= 10000;

3.4.4 区间判断（BETWEEN 值1 AND 值2）

# 查询员工的薪资在6000~10000之间的员工信息
SELECT employee_id, first_name, salary FROM t_employees
WHERE salary BETWEEN 6000 AND 10000;  # 区间判断查询的是一个闭区间

注意：在区间判断语法中，小值在前、大值在后，反之则得不到正确结果

3.4.5 NULL值判断

语法：列名 IS NULL 或 列名 IS NOT NULL

# 查询没有提成的员工信息
SELECT employee_id, first_name, salary FROM t_employees
WHERE commission_pct IS NULL;

注意：不能用 WHERE commission_pct = NULL

3.4.6 枚举查询（IN(值1、值2、值3)）

# 查询部门编号为70、80、90的员工信息
SELECT employee_id, first_name, salary FROM t_employees
WHERE department_id IN (70, 80, 90);

注意：枚举查询的效率比较低，可以用多个OR来代替（连续值可以用BETWEEN）

3.4.7 模糊查询（LIKE）

占位符：

• _ ：单个任意字符，比如：name LIKE '张_'，即 “张”后面固定跟一个字符，可以查出“张三”、“张四”...
• % ：任意长度的字符，比如：name LIKE '张%'，即“张”后面可以跟任意长度的字符，可以查出“张”（即后面跟了长度为0的字符）、“张三”、“张三三”...

注意：模糊查询只能和LIKE关键字结合使用

# 查询名字以'L'开头的员工信息
SELECT employee_id, first_name, salary FROM t_employees
WHERE first_name LIKE 'L%';

#查询名字以'L'开头且长度为4的员工信息
SELECT employee_id, first_name, salary FROM t_employees
WHERE first_name LIKE 'L____';

3.4.8 分支结构查询

语法：

CASE

    WHEN 条件1 THEN 结果1

    WHEN 条件2 THEN 结果2

    WHEN 条件3 THEN 结果3

    ELSE 结果

END

注意：通过CASE END进行条件判断，每条数据对应生成一个值（类似Java中的switch或 if ... else if ... else）

# 查询员工信息（在编号、姓名、薪资的基础上新增了一列薪资级别，值为THEN后面的值）
# CASE ... END 这个结构查询的结果会新增一列， 列名就是CASE ... END 整个语句
# 所以END后必须起别名，否则可读性差
SELECT employee_id, first_name, salary,
CASE 
    WHEN salary >= 10000 THEN 'A'
    WHEN salary >= 8000 THEN 'B'
    WHEN salary >= 6000 THEN 'C'
    ELSE THEN 'D'
END AS 'level'
FROM t_employees;

3.5 时间查询

语法：SELECT 时间函数([参数列表])

执行时间函数查询，返回的结果是一张一行一列的虚拟表。

3.6 字符串查询

语法：SELECT 字符串函数([参数列表])

注意：MySQL中字符串的下标是从1开始的

# 拼接内容
SELECT CONCAT('My', 'SQL');

# 字符串替换
SELECT INSERT('这是一个数据库', 3, 2, 'MySQL'); # 结果为：'这是MySQL数据库'

# 指定内容替换为小写
SELECT LOWER('MySQL'); # 结果为：'mysql'

# 指定内容替换为大写
SELECT UPPER('MySQL'); # 结果为：'MYSQL'

# 指定内容截取
SELECT SUBSTRING('JavaMySQLOracle', 5, 5);

3.7 聚合函数

对多条数据进行统计，返回统计后的一行结果

语法：SELECT 聚合函数(列名) FROM 表名

# 求所有员工薪资总和
SELECT SUM(salary) FROM t_employees;

# 求所有员工薪资平均值
SELECT AVG(salary) FROM t_employees;

# 求所有员工薪资中的最大值
SELECT MAX(salary) FROM t_employees;

# 求所有员工薪资中的最小值
SELECT MIN(salary) FROM t_employees;

# 求员工总数(因为COUNT在统计时不会统计NULL值的行, 所以统计总数最好使用id列来统计)
SELECT COUNT(employee_id) FROM t_employees;

3.8 分组查询（GROUP BY）

语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据（分组依据即列名）

注意：分组是在WHERE条件之后生效

# 查询各部门的总人数
# 思路：
# 1. 按照部门id进行分组（分组依据是department_id）
# 2. 再针对各部门的人数进行统计（COUNT）
SELECT department_id, COUNT(employee_id)
FROM t_employees
GROUP BY department_id;

# 执行步骤是：先对部门id进行分组，比如所有id为1的是一个组，所有id为2的是一个组...
#           然后再对每个组内的员工id列求总行数

# 查询各部门的平均薪资
# 思路：
# 1. 按照部门id进行分组
# 2. 再针对各部门进行平均薪资统计（AVG）
SELECT department_id, AVG(salary)
FROM t_employees
GROUP BY department_id;

# 执行步骤是：先对部门id进行分组，比如所有id为1的是一个组，所有id为2的是一个组...
#           然后再对每个组内的薪资列进行求平均

# 查询各部门、各岗位的总人数
# 思路：
# 1. 按照部门id进行分组
# 2. 按照岗位名称进行分组（分组依据是job_id）
# 3. 针对每个部门中的各个岗位进行人数统计
SELECT department_id, job_id, COUNT(employee_id)
FROM t_employees
GROUP BY department_id, job_id;

# 执行步骤是：先对部门id进行分组，然后再对job_id进行分组，这样就由两个列共同组成一种分组情况，
#           比如部门id为1和job_id为1的是一个组，部门id为1和job_id为2的是一个组...
#           然后再对每个组内的员工id列求总行数

# 分组查询的常见问题：
# 查询各部门id、总人数、员工姓名
SELECT department_id, COUNT(employee_id), first_name
FROM t_employees
GROUP BY department_id;

# 出现问题：比如id为1的部门下面有6个人，但是first_name中只会显示一个人的名字
# 所以，在分组查询中，SELECT显示的列只能是分组依据列或者是聚合函数列，不能出现别的列

3.9 分组过滤查询（HAVING）

语法：SELECT 列名 FROM 表名 WHERE条件 GROUP BY 分组依据列 HAVING 过滤规则

过滤规则定义对分组后的数据进行过滤。

# 统计60、70、80号部门的最高薪资
# 思路：
# 1. 按照部门id进行分组
# 2. 对分组后的数据进行过滤，过滤出部门id是60、70、80的部门组
# 3. 再针对各部门进行薪资最大值统计
SELECT department_id, MAX(salary)
FROM t_employees
GROUP BY department_id
HAVING department_id IN (60, 70, 80);

# 执行步骤是：先对部门id进行分组，然后对分组的结果进行一个过滤，筛选出id是60、70、80，
#           然后在筛选出来的每个组内的薪资进行求最大值

3.10 限定查询（LIMIT）

语法：SELECT 列名 FROM 表名 LIMIT 起始行, 查询条数

注意：起始行数是从0开始计数

# 查询表中前5名员工的所有信息
SELECT * FROM t_employees LIMIT 0, 5;

# 查询表中从第3条数据开始，查询10条
SELECT * FROM t_employees LIMIT 3, 10;

LIMIT的经典应用：分页查询

# 第一页从0开始，显示10条
SELECT * FROM t_employees LIMIT 0, 10;

# 第一页从10开始，显示10条
SELECT * FROM t_employees LIMIT 10, 10;

# 第一页从20开始，显示10条
SELECT * FROM t_employees LIMIT 20, 10;

3.11 基础查询总结

3.11.1 SQL语句编写顺序

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据列 HAVING 过滤条件 ORDER BY 排序列 [排序规则] LIMIT 起始行, 查询条数

3.11.2 SQL语句执行顺序

1. FROM：指定数据来源表
2. WHERE：对查询数据做一次过滤
3. GROUP BY：分组
4. HAVING：对分组的结果进行第二次过滤
5. SELECT：查询各字段的值
6. ORDER BY：排序
7. LIMIT：限定查询结果的起始行和查询条数

3.12 子查询

3.12.1 子查询结果作为外层查询的条件判断

语法：SELECT 列名 FROM 表名 WHERE 条件（条件为子查询结果）

# 查询工资大于Bruce的员工信息
# 思路：
# 1. 先查询到Bruce的工资
SELECT salary FROM t_employees WHERE first_name = 'Bruce'; # 查询结果是6000
# 2. 查询工资大于6000的员工信息
SELECT * FROM t_employees WHERE salary > 6000;

# 将两条语句进行整合，即把第1条语句的查询结果作为第2条语句的查询条件
SELECT * FROM t_employees 
WHERE salary > (SELECT salary FROM t_employees WHERE first_name = 'Bruce');

注意：只有子查询得到一行一列的结果才能作为外部查询的等值或不等值判断条件

补充：如果要将多行一列的结果作为等值或不等值判断条件则要使用ANY或ALL关键字：

• ALL：表示需要大于子查询多行一列的所有结果
• ANY：表示只需要大于子查询多行一列的任意一行的结果即可

# 查询工资高于60号部门的所有员工信息
# 思路：
# 1. 先查询到部门id为60的所有员工的工资
SELECT salary FROM t_employees WHERE department_id = 60;
# 2. 查询工资高于60号部门所有员工信息（高于所有）
SELECT * FROM t_employees 
WHERE salary > ALL(SELECT salary FROM t_employees WHERE department_id = 60);
# 3. 查询工资高于60号部门所有员工信息（高于任意一个）
SELECT * FROM t_employees 
WHERE salary > ANY(SELECT salary FROM t_employees WHERE department_id = 60);

3.12.2 子查询结果作为外层枚举查询条件

语法：SELECT 列名 FROM 表名 WHERE 列名 IN (子查询结果)

# 查询与名为King的员工同一部门的员工信息
# 思路：
# 1. 先查询到名为King的员工所在的部门编号
SELECT department_id FROM t_employees WHERE first_name = 'King'; # 查询结果是80、90
# 2. 再查询部门id是80、90的员工信息
SELECT * FROM t_employees WHERE department_id IN (80, 90);

# 将两条语句整合，即把第1条语句的查询结果作为第2条语句的枚举条件
SELECT * FROM t_employees 
WHERE department_id 
IN (SELECT department_id FROM t_employees WHERE first_name = 'King');

注意：只有子查询得到多行一列的结果才能作为外部枚举查询的条件

3.12.3 子查询结果作为外层查询的数据来源表

语法：SELECT 列名 FROM (子查询结果集) AS 临时表名 WHERE 条件

# 查询员工表中工资排名前5的员工信息
# 思路：
# 1. 先对所有鱼啊弄个的工薪资进行降序排序
SELECT * FROM t_employees 
ORDER BY salary DESC;
# 2. 再查询临时表中前5行员工信息
SELECT * FROM (临时表)
LIMIT 0, 5;

# 将两条语句整合，即把第1条语句的查询结果作为第2条语句查询的数据来源表（临时表）
SELECT * FROM (SELECT * FROM t_employees ORDER BY salary DESC) AS t_temp
LIMIT 0, 5;

注意：子查询的多行多列结果是一张临时的虚拟表，虚拟表默认是没有名字的，需要为其赋一个临时表名，否则会报错

3.13 合并查询（UNION）

即把两个查询结果进行合并（第2条查询语句的结果会拼接到第1条查询语句的结果后面），所以两个查询的列数必须相同（最好列名也相同，因为最终显示的结果的列名是第1条查询语句指定的列名）

语法：

• SELECT 列名 FROM 表名1 UNION SELECT 列名 FROM 表名2
• SELECT 列名 FROM 表名1 UNION ALL SELECT 列名 FROM 表名2

UNION：去除重复结果

# 合并两张表的查询结果，对于重复的结果只保留第1条查询语句得到的结果
SELECT * FROM t1 UNION SELECT * t2;

UNION ALL：保留重复结果

# 合并两张表的查询结果，对于重复的结果全都进行保留
SELECT * FROM t1 UNION ALL SELECT * t2;

3.14 表连接查询

语法：SELECT 列名 FROM 表名1 连接方式 表名2 ON 连接条件

注意：表连接时一定要跟上连接条件，如果没有连接条件，则会产生笛卡尔积的连接结果，即表1的第1行与表2所有行拼接、表1的第2行与表2的所有行拼接 ...

3.14.1 内连接查询（INNER JOIN ON）

# 查询所有员工的基本信息和岗位信息
# 员工信息表中有job_id，根据job_id可以到岗位信息表中去查找对应的岗位信息
SELECT * FROM t_employees 
INNER JOIN t_jobs ON t_employees.job_id = t_jobs.job_id;

# 因为在两张表中都叫job_id，所以在指定连接条件时需要指定是那张表的job_id
# 内连接查询时不会保留连接条件为NULL的查询结果
# 比如t_employees表中有某一行数据的job_id为NULL，则这行数据不会被显示到连接表中
# 而不是根据NULL值去t_job表中匹配数据

3.14.2 三表连接查询

# 查询呢所有员工工号、名字、部门名称、部门所在国家id
SELECT * FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.department_id = d.department_id
INNER JOIN t_location AS l
ON d.location_id = l.location_id;

3.14.3 左外连接查询（LEFT JOIN ON）

以左表为主表，右表去向左表匹配，如果匹配到就显示完整结果；如果左表中有数据但是右表中没有右表的数据就显示NULL；如果左表中没有的数据右表中有，则该行不显示

# 查询所有员工信息，以及所对应的部门名称（没有部门的员工也在查询结果集中，部门名称以NULL填充）
SELECT e.employee_id, e.first_name, e.salary, d.department_name 
FROM t_employees AS e
LEFT JOIN t_department AS d
ON e.depatment_id = d.department_id;

# 即左表的所有行都显示，右表以department_id为条件去向左表匹配，
# 如果左表中有一行数据的department_id为NULL或者department_id=100在右表中不存在id为100的部门，
# 因为左表是主表，所以该行数据照常显示，department_name要从右表得到，但是右表没有对应的数据时给NULL值

3.14.4 右外连接查询（RIGHT JOIN ON）

以右表为主表，左表去向右表匹配，如果匹配到就显示完整的结果；如果右表中有数据，但是左表中没有左表的数据就显示NULL；如果右表中没有的数据左表中有，则该行不显示（与左外连接相反）

# 查询所有部门信息，以及此部门中所有员工信息（没有员工的部门也在查询结果集中，员工信息以NULL填充）
SELECT e.employee_id, e.first_name, e.salary, d.department_name 
FROM t_employees AS e
RIGHT JOIN t_department AS d
ON e.depatment_id = d.department_id;

# 即右表的所有行都显示，左表以department_id为条件去向右表匹配，
# 如果左表中有一行数据的department_id=100在右表中不存id为100的部门，
# 因为右表是主表，所以该条数据不显示

四、DML操作

4.1 新增（INSERT）

语法：INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...)

列名与值按照顺序一一对应（数据类型也要对），没有指定的列名则该条数据的这个列值为NULL

# 添加一条员工信息
INSERT INTO t_employees
(employee_id, first_name, email, job_id, salary)
VALUES
(194, 'zhangsan', 'xxx@qq.com', 10, 10000);

4.2 修改（UPDATE）

语法：UPDATE 表名 SET 列名1=新值1, 列名2=新值2, ... WHERE 条件

注意：在修改时，绝大多数情况都要加上WHERE条件的限制，否则会修改该列所有行的值

# 修改编号为130的员工工资为10000, job_id为10
UPDATE t_employees SET salary = 10000, job_id = 10 WHERE employee_id = 130;

4.3 删除（DELETE）

语法：DELETE FROM 表名 WHERE 条件

注意：在删除时，绝大多数情况都要加上WHERE条件的限制，否则会删除整张表的数据

# 删除编号为130的员工
DELETE FROM t_employees WHERE employee_id = 130;

4.4 清空整表数据（TRUNCATE）

语法：TRUNCATE TABLE 表名

与DELETE不加WHERE条件不同，TRUNCATE是把原表销毁，再按照原表的格式创建一张新的空表；而DELETE不加WHERE条件是一行一行删除原表的每一行数据。（TRUNCATE是直接对表进行操作，DELETE是对数据进行操作）

# 清空t1整张表
TRUNCATE TABLE t1;

五、数据表操作

5.1 MySQL的数据类型

MySQL支持多种数据类型，大致可以分成三类：数值、日期/时间和字符串（字符）类型，对于我们约束数据的类型有很大的帮助。

5.1.1 数值类型

5.1.2 日期类型

5.1.3 字符串类型

5.2 数据表的创建（CREATE）

语法：

CREATE TABLE 表名 (
    列名 数据类型 [约束],
    列名 数据类型 [约束],
    ...
    列名 数据类型 [约束]
) [charset=utf8];  # 可根据需要指定表的字符编码集

例：

CREATE TABLE t_subject (
    subject_id INT,
    subject_name VARCHAR(20),
    subject_hours INT
) charset=utf8;

5.3 数据表的修改（ALTER）

语法：ALTER TABLE 表名 操作

5.3.1 向现有表中添加列

语法：ALTER TABLE 表名 ADD 列名 数据类型 [约束]

# 在subject表中添加grade_id列
ALTER TABLE t_subject ADD grade_id INT;

5.3.2 修改表中的列

语法：ALTER TABLE 表名 MODIFY 列名 数据类型 [约束]

# 修改subject表中subject_name的字符长度
ALTER TABLE t_subject MODIFY subject_name VARCHAR(10);

5.3.3 删除表中的列

语法：ALTER TABLE 表名 DROP 列名

（一次只能删除一列）

# 在subject表中删除grade_id列
ALTER TABLE t_subject DROP grade_id;

5.3.4 修改列名

语法：ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [约束]

# 修改subject表中subject_name列为class_name
ALTER TABLE t_subject CHANGE subject_name class_name VARCHAR(10);

5.3.5 修改表名

语法：ALTER TABLE 旧表名 RENAME 新表名

# 修改t_subject表为t_sub
ALTER TABLE t_subject RENAME t_sub;

5.4 数据表的删除（DROP）

语法：DROP TABLE 表名

例：

# 删除t_sub表
DROP TABLE t_sub;

六、约束

6.1 实体完整性约束

表中的一行数据代表一个实体（entity），实体完整性约束的作用即是标识每一行数据不重复，实体唯一。

6.1.1 主键约束

关键字：PRIMARY KEY

作用：唯一，标识表中该列的每一行数据的值不可重复，且不能为NULL值（主键一般用于数值，比如id）。

# 为表中适用主键的列添加主键约束
CREATE TABLE t_subject (
    subject_id INT PRIMARY KEY, # 课程编号标识每一个课程的编号唯一，且不能为NULL
    subject_name VARCHAR(20),
    subject_hours INT
) charset=utf8;

# 插入数据
INSERT INTO t_subject(subject_id, subject_name, subject_hours)
VALUES (1, '语文', 40);
INSERT INTO t_subject(subject_id, subject_name, subject_hours)
VALUES (1, '数学', 50); # 会报错，主键1已存在

6.1.2 唯一约束

关键字：UNIQUE

作用：唯一，标识表中该列的每一行数据的值不可重复，但是可以为NULL值。

# 为表中不允许重复的列添加唯一约束
CREATE TABLE t_subject (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(20) UNIQUE, #课程名称唯一
    subject_hours INT
) charset=utf8;

# 插入数据
INSERT INTO t_subject(subject_id, subject_name, subject_hours)
VALUES (1, '语文', 40);
INSERT INTO t_subject(subject_id, subject_name, subject_hours)
VALUES (2, '语文', 50); # 会报错，课程名称已存在

6.1.3 自动增长列

关键字：AUTO_INCREMENT

作用：标识表中该列的每一行数据是自动增长的（从1开始）（在插入数据时不用指定自动增长列的值），不能单独使用，一般和主键约束结合使用。

# 为表中主键列添加自动增长
CREATE TABLE t_subject (
    subject_id INT PRIMARY KEY AUTO_INCREMENT, # 为课程标号添加自动增长
    subject_name VARCHAR(20) UNIQUE,
    subject_hours INT
) charset=utf8;

# 插入数据
INSERT INTO t_subject(subject_name, subject_hours)
VALUES ('语文', 40); # 不用指定课程标号的值，会从1开始自动增长
INSERT INTO t_subject(subject_name, subject_hours)
VALUES ('数学', 50);

6.2 域完整性约束

限制列的单元格的数据正确性

6.2.1 非空约束

关键字：NOT NULL

作用：标识表中该列的每一行数据不能为NULL值

# 为表中课时设置非空约束
CREATE TABLE t_subject (
    subject_id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(20) UNIQUE,
    subject_hours INT NOT NULL # 限制课时不能为NULL值
) charset=utf8;

# 插入数据
INSERT INTO t_subject(subject_name, subject_hours)
VALUES ('语文'); # 会报错，插入一条数据时课时必须指定值，因为有非空约束

6.2.2 默认值约束

关键字：DEFAULT 默认值

作用：为列赋予默认值，当新增数据不指定值时，输入DEFAULT，以指定的默认值进行填充

# 若课时没有指定值则以默认值填充
CREATE TABLE t_subject (
    subject_id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(20) UNIQUE,
    subject_hours INT NOT NULL DEFAULT 40
) charset=utf8;

# 插入数据
INSERT INTO t_subject(subject_name, subject_hours)
VALUES ('语文', DEFAULT); # 给该条数据的课时赋默认值

6.2.3 引用完整性约束

（又称外键约束）

语法：CONSTRAINT 引用名（或叫外键名） FOREIGN KEY (列名) REFERENCES 被引用表名(列名)

详解：FOREIGN KEY 引用外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值（一般被引用的列是被引用表的主键）

注意：被引用表为主表，删除表时不能先删主表

# 创建专业表
CREATE TABLE t_speciality (
    id INT PRIMARY KEY AUTO_INCREMENT,
    special_name VARCHAR(20) UNIQUE NOT NULL
)charset=utf8;

# 创建课程表（课程表的special_id引用专业表的id）
CREATE TABLE t_subject (
    subject_id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(20) UNIQUE,
    subject_hours INT NOT NULL DEFAULT 40,
    special_id UNIQUE NOT NULL,
    # 引用专业表中的id作为外键，新增课程信息时，约束课程所属的专业
    CONSTRAINT fk_subject_speciality REFERENCES t_speciality (id)
)charset=utf8;

# 专业表中新增数据
INSERT INTO t_speciality (special_name) VALUES('语文');
INSERT INTO t_speciality (special_name) VALUES('数学');

# 课程表中新增数据
INSERT INTO t_subject(subject_id, subject_name, special_id)
VALUES ('语文', 40, 1);
INSERT INTO t_subject(subject_id, subject_name, special_id)
VALUES ('数学', 40, 3);
# 会报错， 插入时会检查special_id的值在被引用表（专业表）的id列中是否存在，存在才能插入

七、事务

7.1 模拟转账

生活当中转账是转账方账户扣钱，收款方账户加钱，我们用数据库操作来模拟现实转账。

# 数据库模拟转账
# 账户1转给账户2 1000元

# 账户1减1000元
UPDATE account SET money = money-1000 WHERE id=1;

# 账户1减钱后由于某些原因（断电、断网、异常、出错等）下面的语句不能执行
# 即账户1已经减钱成功了，但是账户2并没有加钱，这明显是不行的

# 账户2加1000元
UPDATE account SET money = money+1000 WHERE id=2;

7.2 事务的概念

事务是一个原子操作，是一个最小执行单元。可以由一条或多条SQL语句组成。在同一个事务当中，所有的SQL语句都执行成功时，则整个事务成功，有一个SQL语句执行失败，则整个事务都执行失败。

7.3 事务的边界

• 开始：连接到数据库，执行一条DML语句。上一个事务结束后，又输入了一条DML语句，即事务的开始。

• 结束：

  1. 提交：

     a. 显示提交：COMMIT；

     b. 隐式提交：一条创建、删除的语句，正常退出（客户端退出连接）；

  2. 回滚：

     a. 显示回滚：ROLLBACK；

     b. 隐式回滚：非正常退出（断电、宕机），执行了创建、删除语句失败，会为这个无效语句执行回滚

7.4 事务的原理

数据库会为每一个客户端都维护一个空间独立的缓存区（回滚段），一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句都正常结束（COMMIT），才会将回滚段中的数据同步到数据库；否则无论哪种原因失败，整个事务将回滚（ROLLBACK）。

7.5 事务的特性

• 原子性（Atomicity）

  表示一个事务内的所有操作是一个整体，要么都成功，要么都失败

• 一致性（Consistency）

  表示一个事务内有一个操作失败时，事务内所有修改过的数据都必须回滚到修改前的状态

• 隔离性（Isolation）

  事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据（比如一个事务1中一共有10条SQL语句，该事务已经执行了5条 ，但是在事务1的外面，有一个事务2，事务2是看不到这5条语句执行后的结果的，只能看到10条SQL语句都成功提交的结果或者都失败回滚的结果）

• 持久性（Durability）

  事务完成后，它对系统的影响是永久性的

7.6 事务应用（完善转账操作）

# 账户1给账户2转账
# 1. 开启事务
START TRANSACTION;

# 2. 事务内操作SQL语句
# 账户1减1000元
UPDATE account SET money = money-1000 WHERE id=1;
# 账户2加1000元
UPDATE account SET money = money+1000 WHERE id=2;

# 3. 如果事务内的语句都成功了，执行COMMIT提交
COMMIT; # 提交后表的内容就会被改变

# 4. 如果事务内有一条语句失败，执行ROLLBACK回滚
ROLLBACK; # 回滚后，表的内容会回到事务开始前的内容

注意：开启事务后，执行的语句均属于当前事务，成功再执行COMMIT，失败要执行ROLLBACK

八、权限管理

即数据库管理员可以创建用户、给哪些用户授权/撤销哪些数据库的哪些表的使用权限、删除用户

8.1 创建用户

语法：CREATE USER 用户名 IDENTIFIED BY 密码

# 创建一个zhangsan用户
CREATE USER 'zhangsan' IDENTIFIED BY '123';

8.2 授权

语法：GRANT ALL ON 数据库名.表名 TO 用户名

# 将companyDB数据库下的所有表的权限都开放给zhangsan用户
GRANT ALL ON companyDB.* TO 'zhangsan';

8.3 撤销权限

语法：REVOKE ALL ON 数据库名.表名 FROM 用户名

注意：撤销权限后，用户要重新连接客户端才会生效（即管理员撤销某用户的权限后，该用户只要不退出连接就还能继续操作）

# 撤销zhangsan用户对companyDB数据库下的所有表的权限
REVOKE ALL ON companyDB.* FROM 'zhangsan';

8.4 删除用户

语法：DROP USER 用户名

# 删除zhangsan用户
DROP USER 'zhangsan';

九、视图

9.1 视图的概念

视图，即虚拟表，从一个表或多个表中查询出来的结果集，作用和真实的表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT、UPDATE、DELETE修改记录，视图可以使用户操作方便，并保障数据库系统的安全。

9.2 视图的特点

优点：

• 简单化，数据所见即所得
• 安全性，用户只能查询或修改他们所能见得到的数据
• 逻辑独立性，可以屏蔽真实表结构变化带来的影响

缺点：

• 性能相对较差，简单的查询也会变得稍显复杂
• 修改不方便，特别是复杂的聚合视图基本无法修改

9.3 视图的创建

语法：CREATE VIEW 视图名 AS 查询数据源表语句

## 创建视图
# 创建t_empInfo的视图，其视图的数据源是从t_employees表中查询到员工编号、员工姓名、工资
CREATE VIEW t_empInfo
AS
SELECT employee_id, first_name, salary FROM t_employees;

## 使用视图
# 查询视图t_empInfo中编号为3的员工信息
SELECT * FROM t_empInfo WHERE employee_id='3';

9.4 视图的修改

方式一：CREATE OR REPLACE VIEW 视图名 AS 查询数据源表语句

（如果视图存在则进行修改，不存在则创建视图，用于不知道视图是否存在的场景）

# 修改t_empInfo视图的数据来源
CREATE OR REPLACE VIEW t_empInfo
AS
SELECT employee_id, first_name, salary, email, department_id FROM t_employees;

方式二：ALTER VIEW 视图名 AS 查询数据源表语句

（只会对视图进行修改，用于已知某视图存在的场景）

# 修改t_empInfo视图的数据来源
ALTER VIEW t_empInfo
AS
SELECT employee_id, first_name, salary, email, department_id FROM t_employees;

9.5 视图的删除

语法：DROP VIEW 视图名

（删除视图不会影响数据源表）

# 删除t_empInfo视图
DROP VIEW t_empInfo;

9.6 视图的注意事项

1. 视图不会独立存储数据，数据源表发生变化，则视图中也会跟着改变。
2. 视图没有优化任何的查询性能，只是让我们的SQL语句简单化了。
3. 如果视图包含一下结构中的一种，则视图不可以被修改：
   • 聚合函数的结果
   • DISTINCT 去重之后的结果
   • GROUP BY 分组之后的结果
   • HAVING 筛选过滤后的结果
   • UNION、UNION ALL 联合后的结果