第1章_常见问题
1. 重置root用户密码
2. Navicat17调出sql输入窗口
快捷键方式
Ctrl+q：调出sql输入窗口
Ctrl+r：执行sql语句
非快捷键方式
3. 数据库的导入
3.1 指令导入（通用）
○ 打开cmd命令提示符
○ 登录MySQL（mysql -uroot -h127.0.0.1 -P3306 -p123456）
○ mysql> source D:\atiguigudb.sql
○ 回车，导入成功
3.2 非指令导入（例：Navicat）
第2章_基本的SELECT语句
1. SQL语言的分类
● DDL（Data Definition Language 数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建，删除，修改数据库和数据表的结构。
○主要的语句关键字包括`CREATE`、`DROP`、`ALTER`等。
● DML（Data Mainpulation Language 数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据的完整性。
○主要的语句关键字包括`INSERT`、`DELETE`、`UPDATE`、`SELECT`等。
○SELECT是SQL语言的基础，最为重要
● DCL（Data Control Language 数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
○主要的语句关键字包括`GRANT`、`REVOKE`、`COMMIT`、`ROLLBACK`、`SAVEPOINT`等。
2. SQL语言的规则和规范
2.1 基本规则
● SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进。
● 每条命令以 ; 或 \g 或 \G 结束
● 关键字不能被缩写也不能分行
● 关于标点符号
○ 必须保证所有的()、单引号、双引号是成对结束的
○ 必须使用英文状态下的半角输入方式
○ 字符串型和日期时间类型的数据可以使用单引号（' '）表示
○ 列的别名，尽量使用双引号（" "），而且不建议省略as
2.2 SQL大小写规范
● MySQL在Windows环境下是大小写不敏感的。
● MySQL在Linux环境下是大小写敏感的。
○ 数据库名、表名、表的别名、变量名是严格区分大小写的
○ 关键字、函数名、列名（字段名）、列的别名（字段的别名）是忽略大小写的。
● 推荐采用统一的书写规范：
○ 数据库名、表名、表别名、字段名、字段别名等都小写
○ SQL关键字、函数名、绑定变量等都大写
2.3 注释
单行注释：#文本
单行注释：-- 文本（--后面必须包括一个空格）
多行注释：/* 文本 */
2.4 命名规则
3. 基本的SELECT语句
3.1 SELECT ...
#没有任何子句
SELECT 1;
SELECT 8/2;3.2 SELECT ... FROM ...
#语法
#选择指定字段（列）
SELECT 字段
FROM 表名;
#选择全部字段（列）
SELECT *
FROM 表名;
例：
SELECT department_id, locaiton_id
FROM department;
SELECT *
FROM department;3.3 列的别名
#列的别名
#1. 列名与别名之间是空格
SELECT department_id dep_id, location_id loc_id
FROM department;
#2. 列名与别名之间是AS (alias的缩写)
SELECT department_id AS dep_id, location_id AS loc_id
FROM department;
#3. 别名用" "包裹
SELECT department_id "dep_id", location_id "loc_id"
FROM department;
效果展示：
3.4 去除重复行
默认情况下，当我们查询某个字段时，结果集会显示全部行，包括重复行，如图所示。
当我们想要去除重复行时，可以使用关键字DISTINCT，结果集会自动帮我们删除重复行，如图所示。
针对于：
SELECT DISTINCT salary,department_id
FROM employees;注意点：
●DISTINCT需要放在所有列名的前面，如果写成SELECT salary,DISTINCT department_id FROM employees； 则会报错
●DISTINCT实际上是对所写的所有列名进行联合去重
3.5 空值NULL参与运算
●所有运算符或列值遇到NULL值，运算结果都为NULL
SELECT employee_id,salary,commission_pct,salary * 12 * (1 + commission_pct) "annual_sal"
FROM employees;
3.6 着重符
#当表名与关键字相同时，需要使用着重符``
#例如：当我们要查询表order中的所有内容
#正确的
SELECT *
FROM `order`;
#错误的
SELECT *
FROM ORDER;3.7 常量
当要查询的列中包含常量时，系统会默认把该常量放到所有行中。
4. 显示表结构
使用DISCRIBE或DESC命令，表示表结构。（伪表：DUAL）
DESCRIBE employees;
#或
DESC employees;
5. 过滤数据
5.1 使用WHERE过滤数据
#语法
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件;
#举例
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;
第3章_运算符
1. 算术运算符
SELECT 100 + 1 , 100 + 1.0 , 100 - 1 , 100 - 1.0 ,
100 * 2 , 100 * 2.0 , 100 / 2 , 100 DIV 2 ,
100 % 3 , 100 MOD 3
FROM DUAL;
#结果分别为：101,101.0,99,99.0,200,200.0,50.0000,50,1,1
SELECT 100 + NULL,100 + '1',100 + 'a'
FROM DUAL;
#结果分别为：NULL,101,100
/*
需要注意的是：
1. DIV 和 / 本质上都是除法运算，但结果表示不同，DIV会强制取整，而/会强制取小数
2. NULL参与的任何运算，结果都为NULL
3. +只表示数值相加，当加号左右两侧有字符串时，'1'会转换为整数1进行操作，'a'会转换为整数0进行操作。
*/2. 比较运算符
●比较结果为真则返回1
●比较结果为假则返回0
●其他情况则返回NULL
● 安全等于运算符与等于运算符的作用是相似的，唯一的区别是'<=>'可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。
3. 逻辑运算符
● OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。
4. 位运算符
5. 运算符的优先级
拓展：正则表达式（了解）
#代码演示
#1. 查询以特定字符或字符串开头的记录 字符‘^’匹配以特定字符或者字符串开头的文本。
#在fruits表中，查询f_name字段以字母‘b’开头的记录，SQL语句如下
SELECT * FROM fruits WHERE f_name REGEXP '^b';
#2. 查询以特定字符或字符串结尾的记录 字符‘$’匹配以特定字符或者字符串结尾的文本
#在fruits表中，查询f_name字段以字母‘y’结尾的记录，SQL语句如下
SELECT * FROM fruits WHERE f_name REGEXP 'y$';
#3. 用符号"."来替代字符串中的任意一个字符 字符‘.’匹配任意一个字符。
#在fruits表中，查询f_name字段值包含字母‘a’与‘g’且两个字母之间只有一个字母的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
#4. 使用"*"和"+"来匹配多个字符 星号‘*’匹配前面的字符任意多次，包括0次。加号‘+’匹配前面的字符至
#少一次。
#在fruits表中，查询f_name字段值以字母‘b’开头且‘b’后面出现字母‘a’的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
#在fruits表中，查询f_name字段值以字母‘b’开头且‘b’后面出现字母‘a’至少一次的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
第4章_排序与分页
1. 排序
1.1 排序规则
● 使用ORDER BY 子句排序
○ ASC（ascend）：升序
○DESC（descend）：降序
● ORDER BY子句在SELECT语句的结尾
1.2 单列排序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;
1.3 多列排序
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
● 可以使用不在SELECT列表中的列排序
● 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
2. 分页
2.1 实现规则
● 分页原理：将数据库中的结果集，一段一段显示出来需要的条件
● MySQL中使用 ' LIMIT ' 实现分页
● 格式：
LIMIT [ 位置偏移量, ] 行数
● 举例
#前十条记录
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
#第11至20条记录
SELECT * FROM 表名 LIMIT 10,10;
#第21至30条记录
SELECT * FROM 表名 LIMIT 20,10;● 在MySQL8.0中可以使用" LIMIT 3 OFFSET 4 "，等价于" LIMIT 4,3 "
● 分页显示公式：（当前页数-1） * 每页条数，每页条数
SELECT * FROM table
LIMIT (PageNO - 1) * PageSize , PageSize;● LIMIT子句必须放在整个SELECT语句的最后！
第5章_多表查询
1. 一个案例引发的多表连接（了解）
1.1 案例说明
1.2 笛卡尔积（交叉连接）的理解
1.3 案例分析与问题解决
2. 多表查询
2.1 等值连接vs非等值连接
等值连接：两个表中相同的字段所对应的结果集相同。
SELECT employees.empolyee_id, employees,last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM emoployees,departments
WHERE employees.department_id = departments.department_id;● 注意：
○ 多个表中有相同列时，必须在列名之前加上表名前缀。
○ 在不同表中具有相同列名的列可以用表名加以区分。
○ 连接n个表，至少需要n-1个连接条件。比如连接三个表，至少需要两个连接条件。
● 建议：
○ 使用别名可以简化查询。
○ 列名前使用表名前缀可以提高查询效率。
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;阿里开发规范：
【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。
说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。
正例：
SELECT t1.name
FROM table_first AS t1,table_second AS t2
WHERE t1.id = t2.id;反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现1052异常：Column 'name' in field list is ambiguous
2.2 自连接vs非自连接
当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。
#查询employees表，返回“XXX works for Xxx”
SELECT CONCAT(worker.last_name, ' works for ', manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id;2.3 内连接vs外连接
● 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。
● 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接成为左（或右）外连接。没有匹配的行事，结果表中相应的列为空（NULL）。
● 左外连接，左边的表叫主表，右边的表叫从表。
● 右外连接，右边的表叫主表，左边的表叫从表。
● 一般都采用左外连接，左右连接可以转换。
3. SQL99语法实现多表查询
3.1 基本语法
● 使用JOIN...ON子句创建连接的语法结构：
SELECT table1.column, table2.column, table3.column
FROM table1 JOIN table2
ON table1 和 table2 的连接条件
JOIN table3
ON table2 和 table3 的连接条件● 语法说明：
○ 可以使用 ON 子句指定额外的连接条件。
○ 这个连接条件是与其它条件分开的。
○ ON 子句使语句具有更高的易读性。
○ 关键字 JOIN、 INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接。
3.2 内连接（INNER JOIN）的实现
● 语法：
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;● 举例：
SELECT e.employee_id,e.last_name,e.department_id
d.department_id,d.location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
3.3 外连接（OUTER JOIN）的实现
3.3.1 左外连接（LEFT OUTER JOIN）
● 语法：
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其它子句;● 举例：
SELECT e.last_name,e._department_id,d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
3.3.2 右外连接（RIGHT OUTER JOIN）
● 语法：
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 其它子句;●举例：
SELECT e.last_name,e.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;
3.3.3 满外连接（FULL OUTER JOIN）
● 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
● SQL99是支持满外连接的。使用 FULL JOIN 或 FULL OUTER JOIN 来实现。
● 需要注意的是，MySQL不支持 FULL JOIN ，但是可以用 LEFT JOIN UNION RIGHT JOIN 代替。
4. UNION的使用
● 作用：合并查询结果
利用 UNION 关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集，合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用 UNION 或 UNION ALL 关键字分隔。
● 语法：
SELECT column, ... FROM table1
UNION [ALL]
SELECT column, ... FROM table2;UNION 操作符（返回两个查询结果集的并集，去除重复记录）
UNION ALL操作符（返回两个查询结果集的并集，对于两个结果集中重复的部分，不去重）
注意：执行 UNION ALL 语句所需要的资源比 UNION 语句少。若果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用 UNION ALL 语句，以提高数据查询的效率。
● 举例：查询部门编号>90或邮箱包含a的员工信息
#方式1
SELECT *
FROM employees
WHERE email LIKE '%a%' OR department_id > 90;
#方式2
SELECT *
FROM emoployees
WHERE email LIKE '%a%'
UNION
SELECT *
FROM employees
WHERE department_id > 90;● 举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息
SELECT id,cname
FROM t_chinamale
WHERE csex = '男'
UNION ALL
SELECT id,tname FROM t_usmale
WHERE tGender = 'male';5. 七种 SQL JOINS 的实现
5.1 代码实现
#中图：内连接 A ∩ B
SELECT e.employee_id,e.last_name,d.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 LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中图：A -（A ∩ B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
#右中图：B -（A ∩ B）
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
#左下图：满外连接
#左中图 + 右上图 A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右下图
#左中图 + 右中图 A ∪B -（A ∩ B） 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL5.2 语法格式小结
● 左中图：
#实现A - A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;● 右中图：
#实现B - A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句● 左下图：
#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;● 右下图：
#实现A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B）
#使用左外的 (A - A∩B) union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句6. SQL99语法新特性
6.1 自然连接（ NATURAL JOIN ）
● 作用：自动查询两张连接表中所有相同的字段，然后进行等值连接。
在SQL92标准中：
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;在SQL99中你可以写成：
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e NATURAL JOIN departments d;6.2 USING连接
SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。例：
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e JOIN departments d
USING (department_id);7. 小结
表连接的约束条件可以有三种方式：WHERE , ON , USING
● WHERE：适用于所有关联查询
● ON：只能和 JOIN 一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
● USING：只能和 JOIN 一起使用，并且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等。
注意：
我们要控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让SQL查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。
阿里巴巴《Java开发手册》：
【强制】超过三个表禁止 JOIN 。需要 JOIN 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。
说明：即使双表 JOIN 也要注意表索引、SQL性能。
第6章_单行函数
1. 函数
两种SQL函数
单行函数：
● 操作数据对象
● 接受参数返回一个结果
● 只对一行进行变换
● 每行返回一个结果
● 可以嵌套
● 参数可以是一列或一个值
2. 数值函数
2.1 基本函数
举例：
SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CWILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;
2.2 角度与弧度互换函数
2.3 三角函数
2.4 指数与对数
2.5 进制间的转换
3. 字符串函数
注意：MySQL中，字符串的位置是从1开始的。
4. 日期与时间函数
4.1 获取日期、时间
4.2 日期与时间戳的转换
4.3 获取月份、星期、星期数、天数等函数
4.4 日期的操作函数
4.5 时间和秒钟转换的函数
4.6 计算日期和时间的函数
4.7 日期的格式化与解析
5. 流程控制函数
SELECT IF( 1 > 0,'正确','错误')
-> 正确
SELECT IFNULL(null,'Hello World')
-> Hello World
SELECT CASE WHEN 1 > 0 THEN '1 > 0'
WHEN 2 > 0 THEN '2 > 0'
ELSE '3 > 0' END
-> 1 > 06. 加密与解密函数
7. MySQL信息函数
8. 其他函数
第7章_聚合函数
1. 聚合函数介绍
● 含义：聚合函数作用于一组数据，并对一组数据返回一个值。
● 类型：
○ AVG()
○ SUM()
○ MAX()
○ MIN()
○ COUNT()
● 语法：
1.1 AVG和SUM函数
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';1.2 MIN和MAX函数
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;1.3 COUNT函数
● COUNT(*) 返回表中记录总数，适用于任意数据类型
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;● COUNT(expr) 返回 expr 不为空的记录总数
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;● COUNT(*),COUNT(1),COUNT(列名)谁好呢？
其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb引擎的表引用COUNT(*),COUNT(1)直接读取行数，复杂度O(n)，因为Innodb真的要去数一遍。但好于具体的COUNT(列名)。
● COUNT(*)会统计值为NULL的行，而COUNT(列名)不会统计此列为NULL值的行。
2. GROUP BY
在SELECT列表中所有未包含在组函数中的列都应该包含在GRUOP BY子句中。
2.1 基本使用
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;2.2 GROUP BY中使用WITH ROLLUP
使用 `WITH ROLLUP`关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;注意：
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY时互相排斥的。
3. HAVING
过滤分组：HAVING子句
1. 行已经被分组
2. 使用了聚合函数
3. 满足 HAVING 子句中条件的分组将被显示
4. HAVING 不能单独使用，必须要跟 GRUOP BY 一起使用
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;WHERE 与 HAVING 的对比
区别1：WHERE可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GRUOP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。
4. SELECT的执行过程
4.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：分页4.2 SELECT执行顺序
4.2.1 关键字的顺序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...4.2.2 SELECT语句的执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT第8章_子查询
概念：子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。
1. 需求分析与问题解决
1.1 实际问题
解决方式：
#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
#方式二：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`
#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);1.2 子查询的基本使用
● 语法：
● 子查询 在 主查询之前一次执行完毕
● 子查询的结果被主查询使用
● 注意事项：
○ 子查询要包含在括号内
○ 将子查询放在比较条件的右侧
○ 单行操作符对应单行子查询，多行操作符对应多行子查询。
1.3 子查询分类
分类方式1：
按内查询的结果返回一条还是多条记录，将子查询分为 `单行子查询` `多行子查询`
分类方式2：
按内查询是否被执行多次，将子查询划分为 `相关(或关联)子查询` 和 `不相关(或非关联)子查询`
2. 单行子查询
2.1 单行比较操作符
2.2 代码示例
题目：返回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);题目：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);2.3 HAVING 中的子查询
● 首先执行子查询
● 向主查询中的 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);2.4 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;2.5 子查询中的空值问题
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');子查询不返回任何行
2.6 非法使用子查询
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);错误原因：多行子查询使用单行比较符
3. 多行子查询
● 也称为集合比较子查询
● 内查询返回多行
● 使用多行比较操作符
3.1 多行比较操作符
3.2 代码示例
题目：查询平均工资最低的部门id
#方式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
) dept_avg_sal
);
#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
);3.3 空值问题
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
);no rows selected
4. 相关子查询
含义：如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为`关联子查询`
4.1 相关子查询执行流程
4.2 代码示例
题目：查询员工中工资大于本部门平均工资的员工的last_name，salary和其department_id
方式一：相关子查询
方式二：在FROM中使用子查询
SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP
BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary在 ORDER BY 中使用子查询：
题目：查询员工的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`
);题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同
id的员工的employee_id,last_name和其job_id
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (
SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);4.3 EXISTS 与 NOT EXISTS关键字
● 关联子查询通常也会和 EXISTS 操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
● 如果在子查询中不存在满足条件的行：
○ 条件返回 FALSE
○ 继续在子查询中查找
● 如果在子查询中存在满足条件的行：
○ 条件返回 TRUE
○ 不在子查询中继续查找
● NOT EXISTS 关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。
题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
方式一：
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *
FROM employees e2
WHERE e2.manager_id =
e1.employee_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;
方式三：
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);题目：查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id = d.department_id);4.4 相关更新
题目：在employees中增加一个department_name字段，数据为员工对应的部门名称
# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2）
UPDATE employees e
SET department_name = (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);4.5 相关删除
题目：删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in
(SELECT employee_id
FROM emp_history
WHERE employee_id = e.employee_id);5. 思考题
问题：谁的工资比Abel的高？
解答：
#方式1：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`
#方式2：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);问题：以上两种方式有好坏之分吗？
解答：自连接方式好 （在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多）
第9章_创建和管理表
1. 基础知识
1.1 一条数据存储的过程
1.2 标识符命名规则
1.3 MySQL中的数据类型
2. 创建和管理数据库
2.1 创建数据库
#方式1：创建数据库
CREATE DATABASE 数据库名;
#方式2：创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
#方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）
CREATE DATABASE IF NOT EXISTS 数据库名;注意：DATABASE 不能改名。一些可视化工具可以改名，它是新建库，把所有表复制到新库，再删旧库完成的。
2.2 使用数据库
#查看当前所有的数据库
SHOW DATABASE;
#查看当前正在使用的数据库
SELECT DATABASE();
#查看指定库下所有的表
SHOW TABLES FROM 数据库名;
#查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G
#使用/切换数据库
USE 数据库名;注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上"数据库名"。
2.3 修改数据库
#更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集;2.4 删除数据库
#方式1：删除指定的数据库
DROP DATABASE 数据库名;
#方式2：删除指定的数据库（推荐）
DROP DATABASE IF EXISTS 数据库名;3. 创建表
3.1 创建方式1
● 必备条件：
○ CREATE TABLE 权限
○ 存储空间
● 语句格式：
● 必须指定：
○ 表名
○ 列名（字段名），数据类型，长度
● 可选指定：
○ 约束条件
○ 默认值
● 创建表举例1：
-- 创建表
CREATE TABLE emp (
-- int类型
emp_id INT,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);● 创建表举例2：
CREATE TABLE dept(
-- int类型，自增
deptno INT(2) AUTO_INCREMENT,
dname VARCHAR(14),
loc VARCHAR(13),
-- 主键
PRIMARY KEY (deptno)
);3.2 创建方式2
● 使用 AS subquery 选型，将创建表和插入数据结合起来
● 指定的列和子查询中的列要一一对应
● 通过列名和默认值定义列
CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;
DESCRIBE dept80;
3.3 查看数据表结构
在MySQL中创建好数据表之后，可以查看数据库的结构。MySQL支持使用 `DESCRIBE/DESC` 语句查看数据表结构，也支持使用 `SHOW CREATE TABLE` 语句查看数据表结构。
语法格式：
SHOW CREATE TABLE 表名\G使用 SHOW CREATE TABLE 语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。
4. 修改表
使用 ALTER TABLE 语句可以实现：
● 向已有的表中添加列
● 修改现有表中的列
● 删除现有表中的列
● 重命名现有表中的列
4.1 追加一个列
● 语法：
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;● 举例：
ALTER TABLE dept80
ADD job_id varchar(15);4.2 修改一个列
● 可以修改列的数据类型，长度、默认值和位置
● 修改字段数据类型、长度、默认值、位置的语法格式如下：
ALTER TABLE 表名
MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】 【FIRST|AFTER 字段名2】;● 举例：
ALTER TABLE dept80
MODIFY last_name VARCHAR(30);
ALTER TABLE dept80
MODIFY salary DOUBLE(9,2) DEFAULT 1000; 4.3 重命名一个列
● 语法：
ALTER TABLE 表名 CHANGE 【COLUMN】 列名 新列名 新数据类型;● 举例：
ALTER TABLE dept80
CHANGE department_name dept_name VARCHAR(15);4.4 删除一个列
● 语法：
ALTER TABLE 表名 DROP 【COLUMN】 字段名;● 举例：
ALTER TABLE dept80
DROP COLUMN job_id;5. 重命名表
● 语法：
#方式一：
RENAME TABLE 表名1
TO 表名2;
#方式二：
ALTER TABLE 表名1
RENAME [TO] 表名2;● 举例：
#方式一：
RENAME TABLE dept1
TO dept2;
#方式二：
ALTER TABLE dept1
RENAME [TO] dept2; -- [TO]可以省略6. 删除表
● 在MySQL中，当一张数据表`没有与其他任何数据表形成关联关系`时，可以将当前数据表直接删除。
● 数据和结构都被删除
● 所有正在运行的相关事务被提交
● 所有相关索引被删除
● 语法：
DROP TABLE [IF EXISTS] 数据表1 [,数据表2,数据表3,···];
● 举例：
DROP TABLE dept80;● DROP TABLE 语句不能 `回滚`
7. 清空表
● TRUNCATE TABLE 语句：
○ 删除表中的所有数据
○ 释放表的存储空间
● 举例：
TRUNCATE TABLE detail_dept;● TRUNCATE 语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
● 对比：
SET autocommit = FALSE;
DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;阿里开发规范：
【参考】 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事物且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE语句相同。
8. 内容拓展
拓展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
● 【推荐】库名与应用名称尽量一致。
● 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
○ 正例：无符号值可以避免误存负数，且扩大了表示范围。
拓展2：如何理解清空表、删除表等操作需要谨慎？！
`表删除`操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有任何的确认信 息提示，因此执行删除操时应当慎重。在删除表前，最好对表中的数据进行 `备份`，这样当操作失误时可 以对数据进行恢复，以免造成无法挽回的后果。
同样的，在使用 `ALTER TABLE` 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进 行完整的`备份`，因为数据库的改变是 `无法撤销` 的，如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。
拓展3：MySQL8新特性——DDL的原子化
在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即 `DDL操作要么成功要么回滚` 。DDL操作回滚日志 写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到） 中，用于回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。
分别在MySQL 5.7版本和MySQL 8.0版本中创建数据库和数据表，结果如下：
CREATE DATABASE mytest;
USE mytest;
CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(255)
);
SHOW TABLES;（1）在MySQL 5.7版本中，测试步骤如下： 删除数据表book1和数据表book2，结果如下：
mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'再次查询数据库中的数据表名称，结果如下：
mysql> SHOW TABLES;
Empty set (0.00 sec)从结果可以看出，虽然删除操作时报错了，但是仍然删除了数据表book1。
（2）在MySQL 8.0版本中，测试步骤如下： 删除数据表book1和数据表book2，结果如下：
mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'再次查询数据库中的数据表名称，结果如下：
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| book1 |
+------------------+
1 row in set (0.00 sec)从结果可以看出，数据表book1并没有被删除。
第10章_数据处理之增删改
1. 插入数据
使用这种语法一次只能向表中插入`一条`数据。
● 语法：
#为表的所有字段按默认顺序插入数据
INSERT INTO 表名
VALUES (value1,value2,···);#为表的指定字段插入数据
INSERT INTO 表名 (字段1,字段2,···)
VALUES (value1,value2,···);#同时插入多条数据
INSERT INTO 表名
VALUES (value1,value2,···),
(value1,value2,···),
···
(value1,value2,···);
INSERT INTO 表名 (字段1,字段2,···)
VALUES (value1,value2,···),
(value1,value2,···),
···
(value1,value2,···);#将查询结果插入到表中
INSERT INTO 目标表名
(字段1,字段2,···)
SELECT
(字段1,字段2,···)
FROM 源表名
[WHERE condition];● 举例：
INSERT INTO departments
VALUES (70,'Pub',100,1700);INSERT INTO departments (department_id,department_name)
VALUES (80,'IT');INSERT INTO emp(emp_id,emp_name)
VALUES (1001,'shkstart'),
(1002,'atguigu'),
(1003,'Tom');INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';2. 更新数据
● 语法：
UPDATE 表名
SET column1=value1,column2=value2,···
[WHERE condition];● 可以一次更新`多条`数据
● 如果需要回滚数据，需要保证在DML前，进行设置： `SET AUTOCOMMIT = FALSE`
● 举例：
#使用 WHERE 子句指定需要更新的数据
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
#如果省略 WHERE 子句，则表中的所有数据都将被更新
UPDATE copy_emp
SET department_id = 110;3. 删除数据
● 语法：
DELETE FROM 表名
[WHERE condition];● 举例：
#使用 WHERE 子句删除指定的记录
DELETE FROM departments
WHERE department_name = 'Finance';
#如果省略 WHERE 子句，则表中的全部数据将被删除
DELETE FROM copy_emp;
4. MySQL8新特性：计算列
在MySQL8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE 为例进行举例。
● 语法：
CREATE TABLE 表名(
字段1 数据类型,
字段2 数据类型,
字段3 数据类型,
字段4 数据类型 GENERATED ALWAYS AS (字段1 + 字段2) VIRTUAL
);第11章_数据类型精讲
1. MySQL中的数据类型
常见数据类型的属性，如下：
2. 整数类型
2.1 类型介绍
2.2 适用场景
`TINYINT`：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
`SMALLINT`：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
`MEDIUMINT`：用于较大整数的计算，比如车站的每日的客流量等。
`INT、INTEGER`：取值范围足够大，一般情况下不用考虑超限问题，用的最多。比如商品编号。
`BIGINT`：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。
2.3 如何选择
在评估用哪种整数类型的时候，你需要考虑 存储空间 和 可靠性 的平衡问题：一方面，用占用字节数少的整数类型可以节省存储空间；另一方面，要是为了节省存储空间， 使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起 系统错误，影响可靠性。
举个例子，商品编号采用的数据类型是 INT。原因就在于，客户门店中流通的商品种类较多，而且，每天都有旧商品下架，新商品上架，这样不断迭代，日积月累。
如果使用 SMALLINT 类型，虽然占用字节数比 INT 类型的整数少，但是却不能保证数据不会超出范围 65535。相反，使用 INT，就能确保有足够大的取值范围，不用担心数据超出范围影响可靠性的问题。
你要注意的是，在实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，我建议你首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间。
3. 浮点型
3.1 类型介绍
● FLOAT 表示单精度浮点数
● DOUBLE 表示双精度浮点数
● REAL 默认就是 DOUBLE。如果你把SQL模式设定为启用 `REAL_AS_FLOAT`，那么，MySQL就认为 REAL 是 FLOAT。如果要启用 `REAL_AS_FLOAT`，可以通过以下 SQL 语句实现：
SET sql_mode = “REAL_AS_FLOAT”;问题1：FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？ FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。
问题2：为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于 有符号数取值范围大于等于零的部分呢？
MySQL 存储浮点数的格式为： 符号(S) 、 尾数(M) 和 阶码(E) 。因此，无论有没有符号，MySQL 的浮 点数都会存储表示符号的部分。因此， 所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。
3.2 精度精度说明
#举例：
CREATE TABLE test_double1(
f1 FLOAT,
f2 FLOAT(5,2),
f3 DOUBLE,
f4 DOUBLE(5,2)
);
DESC test_double1;
INSERT INTO test_double1
VALUES(123.456,123.456,123.4567,123.45);
#Out of range value for column 'f2' at row 1
INSERT INTO test_double1
VALUES(123.456,1234.456,123.4567,123.45);
SELECT * FROM test_double1;
3.3 精度误差说明
4. 定点数类型
4.1 类型介绍
● DECIMAL(M,D)的最大取值范围与DOUBLE类型一样。
● 浮点数 vs 定点数
○ 浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精
准，适用 于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、
分子建模、流体动 力学等）
○ 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景
（比如涉 及金额计算的场景）
4.2 开发中经验
5. 位类型：BIT
6. 日期与时间类型
7. 文本字符串类型
7.1 CHAR与VARCAHR类型
7.2 TEXT类型
由于实际存储的长度不确定，MySQL不允许 TEXT 类型的字段做主键。
8. ENUM类型
ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM 类型只允许从成员中选取单个值，不能一次选取多个值。
CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);
INSERT INTO test_enum
VALUES('春'),('秋');
# 忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');
# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES('1'),(3);
# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');
# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);9. SET类型
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');
#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
INSERT INTO test_set (s) VALUES ('A,B,C,D');
SELECT *
FROM test_set;
CREATE TABLE temp_mul(
gender ENUM('男','女'),
hobby SET('吃饭','睡觉','打豆豆','写代码')
);
INSERT INTO temp_mul VALUES('男','睡觉,打豆豆'); #成功
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('男,女','睡觉,写代码'); #失败
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('妖','睡觉,写代码');#失败
INSERT INTO temp_mul VALUES('男','睡觉,写代码,吃饭'); #成功
10. 二进制字符串类型
CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
# f3 VARBINARY,
f4 VARBINARY(10)
);
INSERT INTO test_binary1(f1,f2)
VALUES('a','a');
INSERT INTO test_binary1(f1,f2)
VALUES('尚','尚');#失败
INSERT INTO test_binary1(f2,f4)
VALUES('ab','ab');
SELECT LENGTH(f2),LENGTH(f4)
FROM test_binary1;
+------------+------------+
| LENGTH(f2) | LENGTH(f4) |
+------------+------------+
| 3 | NULL |
| 3 | 2 |
+------------+------------+
2 rows in set (0.00 sec)
11. JSON类型
JSON（JavaScript Object Notation）是一种轻量级的 `数据交换格式`。JSON 可以将JavaScript 对象中表示一组数据转换为字符串，然后就可以在网络或程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。
CREATE TABLE test_json(
js json
);
INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing",
"city":"beijing"}}');
12. 空间类型
MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东 西，可以是一个实体，例如一座山；可以是空间，例如一座办公楼；也可以是一个可定义的位置，例如 一个十字路口等等。MySQL中使用 Geometry（几何） 来表示所有地理特征。Geometry指一个点或点的 集合，代表世界上任何具有位置的事物。
MySQL的空间数据类型（Spatial Data Type）对应于OpenGIS类，包括单值类型：GEOMETRY、POINT、 LINESTRING、POLYGON以及集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION 。
● Geometry是所有空间集合类型的基类，其他类型如POINT、LINESTRING、POLYGON都是Geometry的 子类。
○ Point，顾名思义就是点，有一个坐标值。例如POINT(121.213342 31.234532)，POINT(30 10)， 坐标值支持DECIMAL类型，经度（longitude）在前，维度（latitude）在后，用空格分隔。
○LineString，线，由一系列点连接而成。如果线从头至尾没有交叉，那就是简单的 （simple）；如果起点和终点重叠，那就是封闭的（closed）。例如LINESTRING(30 10,10 30,40 40)，点与点之间用逗号分隔，一个点中的经纬度用空格分隔，与POINT格式一致。
○ Polygon，多边形。可以是一个实心平面形，即没有内部边界，也可以有空洞，类似纽扣。最 简单的就是只有一个外边界的情况，例如POLYGON((0 0,10 0,10 10, 0 10))。
下面展示几种常见的几何图形元素：
● MultiPoint、MultiLineString、MultiPolygon、GeometryCollection 这4种类型都是集合类，是多个 Point、LineString或Polygon组合而成。
下面展示的是多个同类或异类几何图形元素的组合：
13. 小结及选择建议
在定义数据类型时，如果确定是 `整数`，就用 `INT`；如果是 `小数`，一定用定点数类型 `DECIMAL（M,D）`；如果是日期与时间，就用 `DATETIME`。
第12章_约束
1. 约束概述
1.1 为什么需要约束
数据完整性是指数据的精确性和可靠性。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
为了保证数据的完整性，SQL规范以约束的方式对 表数据进行额外的条件限制。
● 实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录
● 域完整性（Domain Integrity） ：例如：年龄范围0-120，性别范围“男/女”
● 引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门
● 用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等，本部门 经理的工资不得高于本部门职工的平均工资的5倍。
1.2 什么是约束
约束是表级的强制规定。
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。
1.3 约束的分类
注意：MySQL 不支持 CHECK 约束，但可以使用CHECK约束，而没有任何效果
2. 非空约束
2.1 作用
限定某个字段/某列的值不允许为空
2.2 关键字
NOT NULL
2.3 特点
2.4 添加非空约束
（1）建表时
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);
举例：
CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL,
sex CHAR NULL
);
CREATE TABLE student(
sid int,
sname varchar(20) not null,
tel char(11) ,
cardid char(18) not null
);
（2）建表后
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;
举例：
ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;2.5 删除非空约束
alter table 表名称 modify 字段名 数据类型 NULL;
#去掉not null，相当于修改某个非注解字段，该字段允许为空
或
alter table 表名称 modify 字段名 数据类型;
#去掉not null，相当于修改某个非注解字段，该字段允许为空
举例：
ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;
ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;3. 唯一性约束
3.1 作用
用来限制某个字段/某列的值不能重复
3.2 关键字
UNIQUE
3.3 特点
3.4 添加唯一约束
（1）建表时
create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);
举例：
create table student(
sid int,
sname varchar(20),
tel char(11) unique,
cardid char(18) unique key
);
CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
-- 使用表级约束语法
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);
（2）建表后指定唯一键约束
#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
#方式1：
alter table 表名称 add unique key(字段列表);
#方式2：
alter table 表名称 modify 字段名 字段类型 unique;
举例：
ALTER TABLE USER
ADD UNIQUE(NAME,PASSWORD);
ALTER TABLE USER
MODIFY NAME VARCHAR(20) UNIQUE;3.5 复合唯一约束
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多
个字段的组合是唯一的
);3.6 删除唯一约束
#通过删除唯一索引的方式删除
ALTER TABLE USER
DROP INDEX uk_name_pwd;4. PRIMARY KEY 约束
4.1 作用
用来唯一标识表中的一行记录
4.2 关键字
primary key
4.3 特点
4.4 添加主键约束
（1）建表时指定主键约束
create table 表名称(
字段名 数据类型 primary key, #列级模式
字段名 数据类型,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) #表级模式
);
举例：
create table temp(
id int primary key,
name varchar(20)
);
列级约束
CREATE TABLE emp4(
id INT PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20)
);
表级约束
CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);
（2）建表后增加主键约束
#字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);
ALTER TABLE student ADD PRIMARY KEY (sid);
ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);4.5 复合主键
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
primary key(字段名1,字段名2) #表示字段1和字段2的组合是唯一的，也可以有更多个字段
);4.6 删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。
5. 自增列：AUTO_INCREMENT
5.1 作用
某个字段的值自增
5.2 关键字
auto_increment
5.3 特点和要求
5.4 添加自增约束
（1）建表时
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(字段名)
);
（2）建表后
alter table 表名称 modify 字段名 数据类型 auto_increment;5.5 删除自增约束
#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除
5.6 MySQL 8.0新特性——自增变量的持久化
在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重 置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发 现的问题。 下面通过案例来对比不同的版本中自增变量是否持久化。 在MySQL 5.7版本中，测试步骤如 下： 创建的数据表中包含自增主键的id字段，语句如下：
6. FOREIGN KEY 约束
6.1 作用
限定某个表的某个字段的引用完整性
6.2 关键字
foreign key
6.3 主表和从表/父表和子表
主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表
6.4 特点
（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列 为什么？因为被依赖/被参考的值必须是唯一的
（2）在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。
（3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
（4）删表时，先删从表（或先删除外键约束），再删除主表
（5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖 该记录的数据，然后才可以删除主表的数据
（6）在“从表”中指定外键约束，并且一个表可以建立多个外键约束
（7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类 型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。 例如：都是表示部门编号，都是int类型。
（8）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束 名。（根据外键查询效率很高）
（9）删除外键约束后，必须 手动 删除对应的索引
6.5 添加外键约束
（1）建表时
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
举例：
create table dept( #主表
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(#从表
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) #在从表中指定外键约束
#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);
说明：
（1）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。
（2）删除表时，先删除从表emp，再删除主表dept
（2）建表后
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用
字段) [on update xx][on delete xx];
举例：
ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);6.6 演示问题
（3）成功，两个表字段名一样
总结：约束关系是针对双方的
● 添加了外键约束后，主表的修改和删除数据受约束
● 添加了外键约束后，从表的添加和修改数据受约束
● 在从表上建立外键，要求主表必须存在
● 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除
6.7 约束等级
● Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
● Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子 表的外键列不能为not null
● No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
● Restrict方式 ：同no action， 都是立即检查外键约束
● Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置 成一个默认的值，但Innodb不能识别
如果没有指定等级，就相当于Restrict方式。
对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
6.8 删除外键约束
(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个
表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;6.9 开发场景
问题1：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否 一定要建外键约束？
答：不是的
问题2：建和不建外键约束有什么区别？
答：建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限 制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。
不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的 性，只能依 靠程序员的自觉，或者是 引用完整 在Java程序中进行限定。例如：在员工表中，可以添加一个员工的 信息，它的部门指定为一个完全不存在的部门。
问题3：那么建和不建外键约束和查询有没有关系？
答：没有
在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适 合。比如大型网站的中央数据库，可能会 许你不使用系统自带的外键约束，在 因为外键约束的系统开销而变得非常慢。所以， MySQL 允 应用层面 完成检查数据一致性的逻辑。也就是说，即使你不 用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。
6.10 阿里开发规范
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学 生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于 单机低并发，不适合 分布式、 高并发集群；级联更新是强阻塞，存在数据库 更新风暴的风险；外键影响数据库的 插入速度。
7. CHECK 约束
7.1 作用
检查某个字段的值是否符合xx要求，一般指的是值的范围
7.2 关键字
check
7.3 说明：MySQL 5.7 不支持
MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告
但是MySQL 8.0中可以使用check约束了。
8. DEFAULT 约束
8.1 作用
给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默 认值。
8.2 关键字
default
8.3 添加默认值
（1）建表时
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 not null default 默认值,
字段名 数据类型 not null default 默认值,
primary key(字段名),
unique key(字段名)
);
说明：默认值约束一般不在唯一键和主键列上加
create table employee(
eid int primary key,
ename varchar(20) not null,
gender char default '男',
tel char(11) not null default '' #默认是空字符串
);
（2）建表后
alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;8.4 删除默认值
alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束
alter table employee modify gender char; #删除gender字段默认值约束，如果有非空约束，也一并删除
alter table employee modify tel char(11) not null;#删除tel字段默认值约束，保留非空约束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支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来 保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不 能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。
第13章_视图
1. 常见的数据库对象
2. 视图概述
2.1 为什么使用视图
视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查 询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的 价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他 人的查询视图中则不提供这个字段。
2.2 视图的理解
● 视图是一种 虚拟表，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。
● 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
● 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和 修改操作时，数据表中的数据会相应地发生变化，反之亦然。
● 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为 存储起来的 SELECT 语句
○ 在数据库中，视图不会保存数据，数据真正保存在数据表中。当对视图中的数据进行增加、删 除和修改操作时，数据表中的数据会相应地发生变化；反之亦然。
● 视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视 图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我 们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。
3. 创建视图
#精简版
CREATE VIEW 视图名称
AS 查询语句
#在 CREATE VIEW 语句中嵌入子查询
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]3.1 创建单表视图
举例：
CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM
employees
WHERE department_id = 80;
查询视图：
SELECT *
FROM salvu80;
举例：
CREATE VIEW emp_year_salary (ename,year_salary)
AS
SELECT ename,salary*12*(1+IFNULL(commission_pct,0))
FROM t_employee;
CREATE VIEW salvu50
AS
SELECT employee_id ID_NUMBER, last_name NAME,salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;说明1：实际上就是我们在 SQL 查询语句的基础上封装了视图 VIEW，这样就会基于 SQL 语句的结果集形成一张虚拟表。
说明2：在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。
3.2 创建多表联合视图
举例：
CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
CREATE VIEW emp_dept
AS
SELECT ename,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did;
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
3.3 基于视图创建视图
4. 查看视图
5. 更新视图的数据
5.1 一般情况
MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。
5.2 不可更新的视图
6. 修改、删除视图
6.1 修改视图
6.2 删除视图
7. 总结
7.1 视图优点
1. 操作简单
将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间 的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。
2. 减少数据冗余
视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。
3. 数据安全
MySQL将用户对数据的 访问限制 在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有 隔离性。视图相当于在用户和实际的数据表之加了一层虚拟表。
同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。
4. 适应灵活多变的需求
当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。
5. 能够分解复杂的查询逻辑
数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。
7.2 视图不足
如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。
实际项目中，如果视图过多，会导致数据库维护成本的问题。
所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视 图，使系统整体达到最优。
第14章_存储过程与函数
MySQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的SQL逻辑封装在一起，应用程 序无须关注存储过程和函数内部复杂的SQL逻辑，而只需要简单地调用存储过程和函数即可。
1. 存储过程概述
1.1 理解
含义：存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过的封装。 预先编译的 SQL 语句
执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用 存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。
好处： 1、简化操作，提高了sql语句的重用性，减少了开发程序员的压力
2、减少操作过程中的失误，提高效率
3、减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
4、减少了 SQL 语句暴露在 网上的风险，也提高了数据查询的安全性
和视图、函数的对比：
它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是 虚拟表， 通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以 直接操作底层数据表，相比于面向集 合的操作方式，能够实现一些更复杂的数据处理。
一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于 函数，存储过程是 没有返回值 的。
1.2 分类
● 没有参数（无参数无返回）
● 仅仅带 IN 类型（有参数无返回）
● 仅仅带 OUT 类型（无参数有返 回）
● 既带 IN 又带 OUT（有参数有返回）
● 带 INOUT（有参数有返回）
注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。
2. 创建存储过程
2.1 语法分析
CREATE PROCUDURE 存储过程名（IN|OUT|INOUT 参数名 参数类型）
[characteristics···]
BEGIN
存储过程体
END 
2.2 代码举例
举例1：创建存储过程select_all_data()，查看 emps 表的所有数据
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM emps;
END
举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资
CREATE PROCEDURE avg_employee_salary()
BEGIN
SELECT AVG(salary) FROM emps;
END
举例3：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms FROM emps;
END
举例4：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
SELECT salary FROM emps WHERE ename = empname;
END
举例5：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
BEGIN
SELECT salary INTO empsalary FROM emps WHERE ename = empname;
END
举例6：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名
CREATE PROCEDURE show_mgr_name(INOUT empname VARCAHR(20))
BEGIN
SELECT ename INTO empname FROM emps
WHERE eid = (SELECT MID FROM emps WHERE ename=empname);
END3. 调用存储过程
3.1 调用格式
3.2 代码举例
举例1：
CREATE PROCEDURE CountProc(IN sid INT,OUT num INT)
BEGIN
SELECT COUNT(*) INTO num FROM fruits
WHERE s_id = sid;
END
CALL CountProc (101, @num);
举例2：
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
DECLARE i INT;
DECLARE sum INT;
SET i = 1;
SET sum = 0;
WHILE i <= n DO
SET sum = sum + i;
SET i = i +1;
END WHILE;
SELECT sum;
END
CALL add_num(50);
3.3 如何调试
4. 存储函数的使用
4.1 语法分析
● 语法：
CREATE FUNCTION 函数名（参数名 参数类型,···）
RETURNS 返回值类型
[characteristics]
BEGIN
函数体 #函数体中必有 RETURN 语句
END注意：RETURNS 和 RETURN
4.2 调用存储函数
4.3 代码举例
举例1：
创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为
字符串型。
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;
调用：
SELECT email_by_name();
举例2：
创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型
为字符串型。
DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //
DELIMITER ;
调用：
SET @emp_id = 102;
SELECT email_by_id(102);注意：
若在创建存储函数中报错 `you might want to use the less safe log_bin_trust_function_creators variable`，有两种处理方法：
● 方式1：：加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
● 方式2：SET GLOBAL log_bin_trust_function_creators = 1;
4.4 对比存储过程和存储函数
5. 存储过程和函数的查看、修改、删除
5.1 查看
5.2 修改
5.3 删除
6. 关于存储过程使用的争议
6.1 优点
1、存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译， 这就提升了 SQL 的执行效率。
2、可以减少开发工作量。将代码 封装 成模块，实际上是编程的核心思想之一，这样可以把复杂的问题 拆解成不同的模块，然后模块之间可以 重复使用，在减少开发工作量的同时，还能保证代码的结构清 晰。
3、存储过程的安全性强。我们在设定存储过程的时候可以 设置对用户的使用权限，这样就和视图一样具 有较强的安全性。
4、可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减 少了网络传输量。
5、良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接 多次数据库才能完成的操作，现在变成了一次存储过程，只需要 连接一次即可。
6.2 缺点
1、可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
2、调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
3、存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
4、它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护， 增加数据库的压力，显然就不适用了。
第15章_变量、流程控制与游标
1. 变量
在 MySQL 数据库中，变量分为 系统变量 以及 用户自定义变量
1.1 系统变量
1.1.1 系统变量分类
1.1.2 查看系统变量
● 查看所有或部分系统变量
#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
或
SHOW VARIABLES;
#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';
● 查看指定系统变量
#查看指定的系统变量的值
SELECT @@global.变量名;
#查看指定的会话变量的值
SELECT @@session.变量名;
#或者
SELECT @@变量名;
● 修改系统变量的值
方式1：修改MySQL配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值
#为某个系统变量赋值
#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;
#为某个会话变量赋值
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;作为 MySQL 编码规范，MySQL 中的系统变量以 两个“@” 开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在， 则标记全局系统变量。
1.2 用户变量
1.2.1 用户变量分类
1.2.2 会话用户变量
● 举例：
1.2.3 局部变量
1.2.4 对比会话用户变量与局部变量
2. 定义条件与处理程序
2.1 案例分析
案例分析：创建一个名称为“UpdateDataNoCondition”的存储过程。代码如下：
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END //
DELIMITER ;
调用存储过程：
CALL UpdateDataNoCondition(); # ERROR 1048 (23000): Column 'email' cannot be null
SELECT @x; #1
可以看到，此时@x变量的值为1。结合创建存储过程的SQL语句代码可以得出：在存储过程中未定义条件
和处理程序，且当存储过程中执行的SQL语句报错时，MySQL数据库会抛出错误，并退出当前SQL逻辑，
不再向下继续执行。2.2 定义条件
2.3 定义处理程序
2.4 案例解决
3. 流程控制
3.1 IF
● 语法：
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]
···
[ELSE 操作N]
END IF● 举例：
IF val IS NULL
THEN SELECT 'val is null';
ELSE SELECT 'val is not null';
END IF;3.2 CASE
● 语法：
方式一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1（如果是语句，需要加分号）
WHEN 值2 THEN 结果2或语句2（如果是语句，需要加分号）
···
ELSE 结果n或语句n（如果是语句，需要加分号）
END [case] (如果是放在 begin end 里面需要加上 case)
方式二：类似于多重if
CASE
WHEN 值1 THEN 结果1或语句1（如果是语句，需要加分号）
WHEN 值2 THEN 结果2或语句2（如果是语句，需要加分号）
···
ELSE 结果n或语句n（如果是语句，需要加分号）
END [case] (如果是放在 begin end 里面需要加上 case)● 举例：
使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。
CASE val
WHEN 1 THEN SELECT 'val is 1';
WHEN 2 THEN SELECT 'val is 2';
ELSE SELECT 'val is not 1 or 2';
END CASE;
使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0。
CASE
WHEN val IS NULL THEN SELECT 'val is null';
WHEN val < 0 THEN SELECT 'val is less than 0';
WHEN val > 0 THEN SELECT 'val is greater than 0';
ELSE SELECT 'val is 0';
END CASE;3.3 LOOP
● 语法：
[loop_label:] LOOP
循环执行语句
END LOOP [loop_label]
#loop_label表示LOOP语句的标注名称，可以省略● 举例：
使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id + 1;
IF id >= 10 THEN LEAVE add_loop;
END IF;
END LOOP3.4 WHILE
● 语法：
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];● 举例：
WHILE语句示例，i值小于10时，将重复执行循环过程，代码如下：
DELIMITER //
CREATE PROCEDURE test_while()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
SET i = i + 1;
END WHILE;
SELECT i;
END //
DELIMITER ;
#调用
CALL test_while();3.5 REPEAT
● 语法：
[repeat_label:] REPEAT
循环体语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label];● 举例：
DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SET i = i + 1;
UNTIL i >= 10;
END REPEAT;
SELECT i;
END //
DELIMITER ;3.6 LEAVE
● 语法：
LEAVE 标记名● 举例：
举例1：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN...END加标记名，并在BEGIN...END中使用IF语句判断num参数的值。
如果num<=0，则使用LEAVE语句退出BEGIN...END；
如果num=1，则查询“employees”表的平均薪资；
如果num=2，则查询“employees”表的最低薪资；
如果num>2，则查询“employees”表的最高薪资。
IF语句结束后查询“employees”表的总人数。
DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_label: BEGIN
IF num <= 0
THEN LEAVE begin_label;
ELSEIF num = 1
THEN SELECT AVG(salary) FROM employees;
ELSEIF num = 2
THEN SELECT MIN(salary) FROM employees;
ELSE
SELECT MAX(salary) FROM employees;
END IF;
SELECT COUNT(*) FROM employees;
END //
DELIMITER ;3.7 ITERATE
● 语法：
ITERATE 标记名● 举例：
举例： 定义局部变量num，初始值为0。循环结构中执行num + 1操作。
如果num < 10，则继续执行循环；
如果num > 15，则退出循环结构
DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
my_loop:LOOP
SET num = num + 1;
IF num < 10
THEN ITERATE my_loop;
ELSEIF num > 15
THEN LEAVE my_loop;
END IF;
END LOOP my_loop;
END //
DELIMITER ;4. 游标
4.1 什么是游标（或光标）
4.2 使用游标步骤
4.3 举例
4.4 小结
补充：MySQL 8.0 的新特性——全局变量的持久化
第16章_触发器
1. 触发器概述
2. 触发器的创建
● 语法：
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;
● 表名：表示触发器监控的对象。
● BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
● INSERT|UPDATE|DELETE ：表示触发的事件。
○ INSERT 表示插入记录时触发；
○ UPDATE 表示更新记录时触发；
○ DELETE 表示删除记录时触发。● 举例：
#创建触发器：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向
test_trigger_log数据表中插入before_insert的日志信息。
DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log(t_log)
VALUES('before_insert');
END //
DELIMITER;
#向test_trigger数据表中插入数据
INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');3. 查看、删除触发器
3.1 查看
3.2 删除
4. 触发器的优缺点
4.1 优点
4.2 缺点
4.3 注意点
283
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
