MySQL基础篇（一）

用户8768390618843
365 阅读44分钟

MySQL5.7中 命令行操作sql乱码问题

问题解决 步骤1：查看编码命令 步骤2：修改mysql的数据目录下的my.ini配置文件

...

default-character-set=utf8 #默认字符集

[mysqld] # 大概在76行左右，在其下添加

...

character-set-server=utf8

collation-server=utf8_general_ci

导入表的问题

导入数据时外键约束问题

数据导入指令：

source d:\xxx.sql

通过FOREIGN_KEY_CHECKS解决，用法如下：

set FOREIGN_KEY_CHECKS=0;  #在导入前设置为不检查外键约束
set FOREIGN_KEY_CHECKS=1;  #在导入后恢复检查外键约束

第三章_最基本的SELECT语句

1. SQL语言的规则和规范

1) 基本规则

  • SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
  • 每条命令以 ; 或 \g 或 \G 结束
  • 关键字不能被缩写也不能分行
  • 关于标点符号
    • 必须保证所有的()、单引号、双引号是成对结束的
    • 必须使用英文状态下的半角输入方式
    • 字符串型和日期时间类型的数据可以使用单引号（' '）表示
    • 列的别名，尽量使用双引号（" "），而且不建议省略as

2) SQL大小写规范（建议遵守）

  • MySQL 在 Windows 环境下是大小写不敏感的
  • MySQL 在 Linux 环境下是大小写敏感的
    • 数据库名、表名、表的别名、变量名是严格区分大小写的
    • 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
  • 推荐采用统一的书写规范：
    • 数据库名、表名、表别名、字段名、字段别名等都小写
    • SQL 关键字、函数名、绑定变量等都大写

3) 注释

单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

4) 命名规则

  • 数据库、表名不得超过30个字符，变量名限制为29个
  • 必须只能包含 A–Z, a–z, 0–9, _共63个字符
  • 数据库名、表名、字段名等对象名中间不要包含空格 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；
  • 同一个表中，字段不能重名 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使 用`（着重号）引起来
  • 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据 类型在一个表里是整数，那在另一个表里可就别变成字符型了

2. 基本的SELECT语句

1) SELECT ... FROM

  • 语法
SELECT 标识选择哪些列
FROM 标识从哪个表中选择

2) 列的别名

  • 重命名一个列
  • 便于计算
  • 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特 殊的字符并区分大小写。
  • AS 可以省略
  • 建议别名简短，见名知意
  • 举例：
SELECT last_name AS name, commission_pct comm
FROM employees;

3) 去除重复行

DISTINCT关键字

SELECT DISTINCT department_id FROM employees;

4) 空值参与运算

空值：null ( 不等同于0, ’ ‘, ’null‘ )

实际问题的解决方案：引入IFNULL

SELECT employee_id, salary "月工资", salary * (1 + IFNULL(commission_pct, 0)) * 12 "年工资" FROM employees;

这里你一定要注意，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长 度是空。而且，在 MySQL 里面，空值是占用空间的。

5) 着重号 ``

必须保证你的字段没有和保留字、数据库系统或常见方法冲突。

如果坚持使用，在SQL语句中使用 ` ` 引起来。

SELECT * FROM `order`;

6) 查询常数

SELECT '小张科技' as "公司名", employee_id, last_name FROM employees;

3. 显示表结构

显示表中字段的详细信息

DESCRIBE employees;
或
DESC employees;

mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

其中，各个字段的含义分别解释如下：

  • Field：表示字段名称。
  • Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。
  • Null：表示该列是否可以存储NULL值。
  • Key：表示该列是否已编制索引。
  • PRI表示该列是表主键的一部分；
  • UNI表示该列是UNIQUE索引的一 部分；
  • MUL表示在列中某个给定值允许出现多次。
  • Default：表示该列是否有默认值，如果有，那么值是多少。
  • Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

4. 过滤数据

  • 语法：
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件

使用WHERE 子句，将不满足条件的行过滤掉。WHERE子句紧随 FROM子句。

  • 举例：
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;

第四章_运算符

DUAL 伪表

1. 算术运算符

SELECT 100 + 0, 100 + 50 * 30, 100 - 35.5 FROM DUAL;
  • 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
  • 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
  • 在Java中， + 的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中 + 只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（注：MySQL 中字符串拼接要使用字符串函数CONCAT()实现）
  • 在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。

2. 比较运算符

1) 等号运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果 为假则返回0，其他情况则返回NULL。

比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

mysql> SELECT 1 = 1, 1 = '1', 1 = 0, 'a' = 'a', (5 + 3) = (2 + 6), '' = NULL , NULL = NULL;
+-------+---------+-------+-----------+-------------------+-----------+-------------+
| 1 = 1 | 1 = '1' | 1 = 0 | 'a' = 'a' | (5 + 3) = (2 + 6) | '' = NULL | NULL = NULL |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
|   1   |    1    |   0   |     1     |          1        |    NULL   |     NULL    |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
1 row in set (0.00 sec)

mysql> SELECT 1 = 2, 0 = 'abc', 1 = 'abc' FROM DUAL;
+-------+-----------+-----------+
| 1 = 2 | 0 = 'abc' | 1 = 'abc' |
+-------+-----------+-----------+
|   0   |     1     |     0     |
+-------+-----------+-----------+
1 row in set, 2 warnings (0.00 sec)
  • 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的 是每个字符串中字符的ANSI编码是否相等。
  • 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
  • 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
  • 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
ysql> SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL,NULL <=> NULL FROM dual;
+-----------+---------+-------------+---------------------+-------------+---------------+
| 1 <=> '1' | 1 <=> 0 | 'a' <=> 'a' | (5 + 3) <=> (2 + 6) | '' <=> NULL | NULL <=> NULL |
+-----------+---------+-------------+---------------------+-------------+---------------+
|     1     |    0    |      1      |           1         |      0      |       1       |
+-----------+---------+-------------+---------------------+-------------+---------------+
1 row in set (0.00 sec)

可以看到，使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，其他返回结果与等于运算符相同。

2) 不等于运算符

不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等， 如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL， 或两边都为NULL，则结果为NULL。 SQL语句示例如下：

mysql> SELECT 1 <> 1, 1 != 2, 'a' != 'b', (3+4) <> (2+6), 'a' != NULL, NULL <> NULL;
+--------+--------+------------+----------------+-------------+--------------+
| 1 <> 1 | 1 != 2 | 'a' != 'b' | (3+4) <> (2+6) | 'a' != NULL | NULL <> NULL |
+--------+--------+------------+----------------+-------------+--------------+
|    0   |    1   |      1     |        1       |     NULL    |      NULL    |
+--------+--------+------------+----------------+-------------+--------------+
1 row in set (0.00 sec)

此外，还有非符号类型的运算符：

image-20220531154418141.png

3) 空运算符

空运算符 (IS NULL 或者 ISNULL) 判断一个值是否为NULL，如果为NULL则返回1，否则返回0。

mysql> SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;
+--------------+--------------+-------------+-----------+
| NULL IS NULL | ISNULL(NULL) | ISNULL('a') | 1 IS NULL |
+--------------+--------------+-------------+-----------+
|      1       |       1      |      0      |     0     |
+--------------+--------------+-------------+-----------+
1 row in set (0.00 sec)

4) 非空运算符

非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。

5) 最小值运算符

语法格式为：LEAST(值1，值2，...，值n)。其中，“值n”表示参数列表中有n个值。在有 两个或多个参数的情况下，返回最小值。

mysql> SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
+---------------+--------------------+-----------------+
| LEAST (1,0,2) | LEAST('b','a','c') | LEAST(1,NULL,2) |
+---------------+--------------------+-----------------+
|       0       |          a         |        NULL     |
+---------------+--------------------+-----------------+
1 row in set (0.00 sec)

由结果可以看到，当参数是整数或者浮点数时，LEAST将返回其中最小的值；当参数为字符串时，返回字 母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

6) 最大值运算符

语法格式为：GREATEST(值1，值2，...，值n)。其中，n表示参数列表中有n个值。当有 两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。

mysql> SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
+-----------------+-----------------------+--------------------+
| GREATEST(1,0,2) | GREATEST('b','a','c') | GREATEST(1,NULL,2) |
+-----------------+-----------------------+--------------------+
|         2       |             c         |         NULL       |
+-----------------+-----------------------+--------------------+
1 row in set (0.00 sec)

由结果可以看到，当参数中是整数或者浮点数时，GREATEST将返回其中最大的值；当参数为字符串时， 返回字母表中顺序最靠后的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

7) BETWEEN AND运算符

BETWEEN运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN A AND B，此时，当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。

8) IN运算符

IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给 定的值为NULL，或者IN列表中存在NULL，则结果为NULL。

mysql> SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
+----------------------+------------+-------------------+--------------------+
| 'a' IN ('a','b','c') | 1 IN (2,3) | NULL IN ('a','b') | 'a' IN ('a', NULL) |
+----------------------+------------+-------------------+--------------------+
|            1         |      0     |         NULL      |          1         |
+----------------------+------------+-------------------+--------------------+

9) NOT IN运算符

NOT IN运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一 个值，则返回1，否则返回0。

10) LIKE运算符

LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回 0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。

“%”：匹配0个或多个字符。
“_”：只能匹配一个字符。

11) ESCAPE

回避特殊符号的：使用转义符。例如：将[%]转为[%]、[]转为[]，然后再加上[ESCAPE‘$’]即可。

SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT\_%‘;

如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。

SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT$_%‘ escape ‘$‘;

12) REGEXP运算符

REGEXP运算符用来匹配字符串，语法格式为： expr REGEXP 匹配条件 。

（1）‘^’匹配以该字符后面的字符开头的字符串。

（2）‘$’匹配以该字符前面的字符结尾的字符串。

（3）‘.’匹配任何一个单字符。

（4）“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一 个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

（5）‘’匹配零个或多个在它前面的字符。例如，“x”匹配任何数量的‘x’字符，“[0-9]”匹配任何数量的数字， 而“”匹配任何数量的任何字符。

3. 逻辑运算符

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

MySQL中支持4种逻辑运算符如下：

image-20220531195405333.png

4. 位运算

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算， 最后将计算结果从二进制变回十进制数。

MySQL支持的位运算符如下：

image-20220531195442995.png

5. 运算符的优先级

数字编号越大，优先级越高，优先级高的运算符先进行计算。

image-20220531195522668.png

扩展：使用正则表达式查询

  • 数据库名、表名、表别名、字段名、字段别名等都小写

第五章_排序与分页

1. 排序规则

  • 使用 ORDER BY 子句排序

    • ASC（ascend）: 升序
    • DESC（descend）:降序

  • ORDER BY 子句在SELECT语句的结尾。

1) 单列排序

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date;

2) 多列排序

  • 可以使用不在SELECT列表中的列排序。
  • 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第 一列数据中所有值都是唯一的，将不再对第二列进行排序。

2. 分页

  • 格式：
LIMIT [位置偏移量,] 行数
  • 举例：
--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录：
SELECT * FROM 表名 LIMIT 20,10;

MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。

  • 分页显式公式：（当前页数-1）* 每页条数，每页条数
SELECT * FROM table
LIMIT(PageNo - 1) * PageSize, PageSize;

  • 注意：LIMIT 子句必须放在整个SELECT语句的最后！

  • 使用LIMIT的好处

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

第六章_多表查询

1. 多表查询分类讲解

1) 自连接

题目：查询employees表，返回 <员工 works for 老板>

SELECT CONCAT(worker.last_name , ' works for ', manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id;

2) 内连接与外连接

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

SQL92语法

SELECT emp.employee_id, dep.department_name
FROM employee emp, department dep
WHERE emp.`department_id` = dep.`department_id`;

SQL99语法

SELECT emp.employee_id, dep.department_name
FROM employee emp JOIN department dep
ON emp.`department_id` = dep.`department_id`;
  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
  • 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。

LEFT OUTER JOIN

SELECT last_name, department_name
FROM employees emp LEFT OUTER JOIN department dep
ON emp.`department_id` = dep.`department_id`;
  • 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

RIGHT OUTER JOIN

SELECT last_name, department_name
FROM employees emp RIGHT OUTER JOIN department dep
ON emp.`department_id` = dep.`department_id`;

2. UNION的使用

合并查询结果

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并 时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：

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

UNION操作符

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符

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

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

举例：查询部门编号>90或邮箱包含a的员工信息

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

#方式2
SELECT * FROM employees 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';

3.七种SQL JOINS的实现

image-20220531224324213.png

# 中图：内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左上图：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右上图：右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左中图：
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

# 右中图：
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;


# 左下图：满外连接
# 方式1：左上图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;


# 方式2：左中图 UNION ALL 右上图
SELECT employee_id,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,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右下图：左中图  UNION ALL 右中图
SELECT employee_id,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,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

4. SQL99语法的新特性

1) 自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把 自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值 连接 。

在SQL92标准中：

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`;

在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

2) USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配 合JOIN一起使用。比如：

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

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下 面的 SQL 查询结果是相同的：

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

5. 小结

表连接的约束条件可以有三种方式：WHERE, ON, USING

  • WHERE：适用于所有关联查询
  • ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起 写，但分开写可读性更好。
  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字 段值相等

我们要控制连接表的数量 。

多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下 降得很严重，因此不要连接不必要的表。

在许多 DBMS 中，也都会有最大连接表的限制。

# 习题巩固
# 注意：当两个表外连接之后，组成主表和从表，主表的连接字段是不为空的，从表的连接字段可能为空，因此从表的关键字段用来判断是否为空。

# 1.查询哪些部门没有员工
# 方式一
SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id` IS NULL;

# 方式二
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
		SELECT *
    	FROM employees e
    	WHERE e.`department_id` = d.`department_id`
);

# 2.查询哪个城市没有部门
SELECT l.location_id, l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;

# 3.查询部门名为 Sales 或 IT 的员工信息
SELECT e.employee_id, e.last_name, e.department_id
FROM employees e JOIN department d
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` IN ('Sales', 'IT');

第七章_单行函数

1. 数值函数

1) 基本函数

函数用法
ABS(x)返回x的绝对值
SIGN(X)单元格
PI()返回圆周率的值
CEIL(x)，CEILING(x)返回大于或等于某个值的最小整数
FLOOR(x)返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…)返回列表中的最小值
GREATEST(e1,e2,e3…)返回列表中的最大值
MOD(x,y)返回X除以Y后的余数
RAND()返回0~1的随机值
RAND(x)返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机 数
ROUND(x)返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y)返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y)返回数字x截断为y位小数的结果
SQRT(x)返回x的平方根。当X的值为负数时，返回NULL

2) 角度与弧度互换函数

函数用法
RADIANS(x)将角度转化为弧度，其中，参数x为角度值
DEGREES(x)将弧度转化为角度，其中，参数x为弧度值

3) 三角函数

函数用法
SIN(x)将角度转化为弧度，其中，参数x为角度值
ASIN(x)将弧度转化为角度，其中，参数x为弧度值
COS(x)返回x的余弦值，其中，参数x为弧度值
ACOS(x)返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x)返回x的正切值，其中，参数x为弧度值
ATAN(x)返回x的反正切值，即返回正切值为x的值
ATAN2(m,n)返回两个参数的反正切值
COT(x)返回x的余切值，其中，X为弧度值

4) 指数与对数函数

函数用法
POW(x,y)，POWER(X,Y)返回x的y次方
EXP(X)返回e的X次方，其中e是一个常数，2.718281828459045
LN(X)，LOG(X)返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG10(X)返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG2(X)返回以2为底的X的对数，当X <= 0 时，返回NULL

5) 进制间的转换

函数用法
BIN(x)返回x的二进制编码
HEX(x)返回x的十六进制编码
OCT(x)返回x的八进制编码
CONV(x,f1,f2)返回f1进制数变成f2进制数

2. 字符串函数

函数用法
ASCII(S)返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s)返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s)返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,......,sn)连接s1,s2,......,sn为一个字符串
CONCAT_WS(x, s1,s2,......,sn)同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x
INSERT(str, idx, len, replacestr)将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str, a, b)用字符串b替换字符串str中所有出现的字符串a
UPPER(s) 或 UCASE(s)将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)将字符串s的所有字母转成小写字母
LEFT(str,n)返回字符串str最左边的n个字符
RIGHT(str,n)返回字符串str最右边的n个字符
LPAD(str, len, pad)用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)去掉字符串s左侧的空格
RTRIM(s)去掉字符串s右侧的空格
TRIM(s)去掉字符串s开始与结尾的空格
TRIM(s1 FROM s)去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s)去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s)去掉字符串s结尾处的s1
REPEAT(str, n)返回str重复n次的结果
SPACE(n)返回n个空格
STRCMP(s1,s2)比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同
LOCATE(substr,str)返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn)返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn)返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(s)返回s反转后的字符串
NULLIF(value1,value2)比较两个字符串，如果value1与value2相等，则返回NULL，否则返回 value1

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

3. 日期和时间函数

1) 获取日期、时间

函数用法
CURDATE() ，CURRENT_DATE()返回当前日期，只包含年、 月、日
CURTIME() ， CURRENT_TIME()返回当前时间，只包含时、 分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()返回当前系统日期和时间
UTC_DATE()返回UTC（世界标准时间） 日期
UTC_TIME()返回UTC（世界标准时间） 时间

2) 日期与时间戳的转换

函数用法
UNIX_TIMESTAMP()以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884
UNIX_TIMESTAMP(date)将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp)将UNIX时间戳的时间转换为普通格式的时间

3) 获取月份、星期、星期数、天数等函数

函数用法
YEAR(date) / MONTH(date) / DAY(date)返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time)返回具体的时间值
FROM_UNIXTIME(timestamp)将UNIX时间戳的时间转换为普通格式的时间
MONTHNAME(date)返回月份：January，...
DAYNAME(date)返回星期几：MONDAY，TUESDAY.....SUNDAY
WEEKDAY(date)返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date)返回日期对应的季度，范围为1～4
WEEK(date) ， WEEKOFYEAR(date)返回一年中的第几周
DAYOFYEAR(date)返回日期是一年中的第几天
DAYOFMONTH(date)返回日期位于所在月份的第几天
DAYOFWEEK(date)返回周几，注意：周日是1，周一是2，。。。周六是 7

4) 日期的操作函数

函数用法
EXTRACT(type FROM date)返回指定日期中特定的部分，type指定返回的值

EXTRACT(type FROM date)函数中type的取值与含义：

image-20220601162705975.png

5) 时间和秒钟转换的函数

函数用法
TIME_TO_SEC(time)将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟 *60+秒
SEC_TO_TIME(seconds)将 seconds 描述转化为包含小时、分钟和秒的时间

6) 计算日期和时间的函数

函数用法
DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type)返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type)， SUBDATE(date,INTERVAL expr type)返回与date相差INTERVAL时间间隔的日期

上述函数中type的取值：

image-20220601165055639.png

函数用法
ADDTIME(time1,time2)返回time1加上time2的时间。当time2为一个数字时，代表的是 秒 ，可以为负数
SUBTIME(time1,time2)返回time1减去time2后的时间。当time2为一个数字时，代表的 是 秒 ，可以为负数
DATEDIFF(date1,date2)返回date1 - date2的日期间隔天数
TIMEDIFF(time1, time2)返回time1 - time2的时间间隔
FROM_DAYS(N)返回从0000年1月1日起，N天以后的日期
TO_DAYS(date)返回日期date距离0000年1月1日的天数
LAST_DAY(date)返回date所在月份的最后一天的日期
MAKEDATE(year,n)针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second)将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n)返回time加上n后的时间

7) 日期的格式化与解析

函数用法
DATE_FORMAT(date,fmt)按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)返回日期字符串的显示格式
STR_TO_DATE(str, fmt)按照字符串fmt对str进行解析，解析为一个日期

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

格式符说明格式符说明
%Y4位数字表示年份%y表示两位数字表示年份
%M月名表示月份（January,....）%m两位数字表示月份 （01,02,03。。。）
%b缩写的月名（Jan.，Feb.，....）%c数字表示月份（1,2,3,...）
%D英文后缀表示月中的天数 （1st,2nd,3rd,...）%d两位数字表示月中的天数(01,02...)
%e数字形式表示月中的天数 （1,2,3,4,5.....）
%H两位数字表示小数，24小时制 （01,02..）%h 和%I两位数字表示小时，12小时制 （01,02..）
%k数字形式的小时，24小时制(1,2,3)%l数字形式表示小时，12小时制 （1,2,3,4....）
%i两位数字表示分钟（00,01,02）%S 和%s两位数字表示秒(00,01,02...)
%W一周中的星期名称（Sunday...）%a一周中的星期缩写（Sun.， Mon.,Tues.，..）
%w以数字表示周中的天数 (0=Sunday,1=Monday....)
%j以3位数字表示年中的天数(001,002...)%U以数字表示年中的第几周， （1,2,3。。）其中Sunday为周中第一 天
%u以数字表示年中的第几周， （1,2,3。。）其中Monday为周中第一 天
%T24小时制%r12小时制
%pAM或PM%%表示%

4. 流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。 MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

函数用法
IF(value,value1,value2)如果value的值为TRUE，返回value1， 否则返回value2
IFNULL(value1, value2)如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END相当于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END相当于Java的switch...case...

5. 加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。

函数用法
PASSWORD(str)返回字符串str的加密版本，41位长的字符串。加密结果不可逆 ，常用于用户的密码加密
MD5(str)返回字符串str的md5加密后的值，也是一种加密方式。若参数为 NULL，则会返回NULL
SHA(str)从原明文密码str计算并返回加密后的密码字符串，当参数为 NULL时，返回NULL。 SHA加密算法比MD5更加安全 。
ENCODE(value,password_seed)返回使用password_seed作为加密密码加密value
DECODE(value,password_seed)返回使用password_seed作为加密密码解密value

6. MySQL信息函数

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

函数用法
VERSION()返回当前MySQL的版本号
CONNECTION_ID()返回当前MySQL服务器的连接数
DATABASE()，SCHEMA()返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER()返回当前连接MySQL的用户名，返回结果格式为 “主机名@用户名”
CHARSET(value)返回字符串value自变量的字符集
COLLATION(value)返回字符串value的比较规则

MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视 的。

函数用法
FORMAT(value,n)返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留 到小数点后n位
CONV(value,from,to)将value的值进行不同进制之间的转换
INET_ATON(ipvalue)将以点分隔的IP地址转化为一个数字
INET_NTOA(value)将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr)将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费 的时间
CONVERT(value USING char_code)将value所使用的字符编码修改为char_code

第八章_聚合函数

1. 聚合函数介绍

  • 什么是聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。

  • 聚合函数类型
    • AVG()
    • SUM()
    • MAX()
    • MIN()
    • COUNT()

1) AVG和SUM函数

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

2) MIN和MAX函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

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(列名)替换count(*)?

不要使用 count(列名)来替代 count() ， count() 是 SQL92 定义的标准统计行数的语法，跟数 据库无关，跟 NULL 和非 NULL 无关。 说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

2. GROUP BY

1) 基本使用

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

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

结论1：SELECT中出现的非组函数的字段必须声明在GROUP BY中。

​ 反之，GROUP BY中声明的字段可以不出现在SELECT中。

结论2：GROUP BY声明在FROM后面、WHERE后面、ORDER BY前面、LIMIT前面。

2) 使用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

1) 基本使用

过滤分组：HAVING子句

  1. 行已经被分组。
  2. 使用了聚合函数。
  3. 满足HAVING 子句中条件的分组将被显示。
  4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

要求

  • 如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。
  • 当过滤条件中没有聚合函数时，则次过滤条件声明在WHERE中或HAVING中都可以。但是，建议声明在WHERE中的执行效率高。
  • HAVING必须声明在GROUP BY 的后面
  • 开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。

2) WHERE和HAVING的对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件； HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为， 在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之 后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成 的。另外，WHERE排除的记录不再包括在分组中。

区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接 后筛选。

这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一 个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要 先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用 的资源就比较多，执行效率也较低。

小结如下：

关键字用法缺点
WHERE先筛选数据再关联，执行效率高不能使用分组中的计算函数进行筛选
HAVING可以使用分组中的计算函数在最后的结果集中进行筛选，执行效率较低

开发中的选择：

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组 统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发 挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很 大的差别。

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：分页

需要记住 SELECT 查询时的两个顺序：

1. 关键字的顺序是不能颠倒的：

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

1. SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

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 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步 骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

2) SQL的执行原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

  1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
  2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
  3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟 表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

  • 当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得 到是我们的原始数据。

  • 然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的 基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。

  • 当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段 。

  • 首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2 。

  • 当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到 虚拟表 vt6 。

  • 最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段 ，得到最终的结果，对应的是虚拟表 vt7 。

  • 当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的 关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

第九章_子查询

1. 基本使用

  • 子查询的基本语法结构：

  • 子查询（内查询）在主查询之前一次执行完成。

  • 子查询的结果被主查询（外查询）使用 。

  • 注意事项

    • 子查询要包含在括号内
    • 将子查询放在比较条件的右侧
    • 单行操作符对应单行子查询，多行操作符对应多行子查询

2. 子查询的分类

分类方式1：

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

  • 单行子查询

image-20220603135507360.png

  • 多行子查询

image-20220603135544144.png

分类方式2：

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

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

3. 单行子查询

1) 单行比较操作符

操作符含义
=equal to
greater than
>=greater than or equal to
<less than
<=less than or equal to
<>not equal to

2) 代码示例

  • 题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name, job_id, salary
FROM eployees
WHERE job_id = (
	SELECT job_id
	FROM eployees
    WHERE employee_id = 141
)
AND salary > (
	SELECT salary
	FROM eployees
    WHERE employee_id = 143
);
  • 题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id， manager_id，department_id
# 实现方式一：不成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
        (SELECT manager_id
        FROM employees
        WHERE employee_id IN (174,141))
AND department_id IN
        (SELECT department_id
        FROM employees
        WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);

# 实现方式二：成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
        (SELECT manager_id, department_id
        FROM employees
        WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);
  • 题目：查询最低工资大于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);

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) 子查询中的空值问题

SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');

子查询不返回任何行

5) 非法使用子查询

SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

多行子查询使用单行比较符

4. 多行子查询

  • 也称为集合比较子查询
  • 内查询返回多行
  • 使用多行比较操作符

1) 多行比较操作符

操作符含义
IN等于列表中的任意一个
ANY需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME实际上是ANY的别名，作用相同，一般常使用ANY

2) 代码示例

  • 题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG' 
AND salary < ANY(
	SELECT salary
    FROM emplyees
    WHERE job_id = 'IT_PROG'
);
  • 题目：查询平均工资最低的部门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) 空值问题

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

5. 相关子查询

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

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

image-20220603154919387.png

image-20220603155013864.png

说明：子查询中使用主查询中的列

1) 代码示例

  • 题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
# 方式一：使用相关子查询
SELECT last_name, salary, department
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
    	FROM employees e2
    	WHERE 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_salary
WHERE e.department_id = t_dept_avg_salary.department_id
AND e.salary > t_dept_avg_salary.avg_sal;

在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
);

2) EXISTS 与 NOT EXISTS 关键字

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

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

# 方式一：EXISTS
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
ON e1.employee_id = e2.manager_id;

# 方式三：IN
SELECT employee_id, last_name, job_id, department_id
WHERE employee_id IN (
        SELECT DISTINCT manager_id
        FROM employees
);

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

# 方式一：
SELECT d.department_id, d.department_name
FROM departments e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

# 方式二：
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
	SELECT *
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
);

3) 相关更新

UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

使用相关子查询依据一个表中的数据更新另一个表的数据。

题目：在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) 相关删除

DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

使用相关子查询依据一个表中的数据删除另一个表的数据。

题目：删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees e
WHERE employee_id in(
    SELECT employee_id
    FROM emp_history
    WHERE employee_id = e.employee_id
);

6. 思考题

问题：谁的工资比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 的处理过 程中，对于自连接的处理速度要比子查询快得多。 可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表 进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

7. 课后练习

  1. 查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE department_id = (
	SELECT department_id
    FROM employees
    WHERE last_name = 'Zlotkey'
);
  1. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
    FROM employee
);
  1. 选择工资大于所有JOB_ID = 'SA_MAN' 的员工的工资的员工的last_name, job_id, salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary > ALL (
	SELECT salary
    FROM employees
    WHERE job_id = 'SA_MAN'
);
  1. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id, last_name
FROM eployees
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
);
  1. 查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN (
	SELECT department_id
    FROM departments
    WHERE location_id = 1700
);
  1. 查询管理者是King的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE manage_id IN (
	SELECT employee_id
    FROM employees
    WHERE last_name = 'King'
);
  1. 查询工资最低的员工信息 (last_name, salary)
SELECT last_name, salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
    FROM employees
);
  1. 查询平均工资最低的部门信息
# 方式一
SELECT *
FROM departments
WHERE department_id = (
	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 *
FROM departments
WHERE department_id = (
	SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) <= ALL (
        SELECT AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
    )
);

# 方式三: LIMIT
SELECT *
FROM departments
WHERE department_id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) = (
    	SELECT AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
        ORDER BY avg_sal ASC
        LIMIT 1
    )
);

# 方式四
SELECT d.*
FROM departments d, (
	SELECT department_id, AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
    ORDER BY avg_sal ASC
    LIMIT 0,1
) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.`department_id`;
  1. 查询平均工资最低的部门信息和该部门的平均工资 (相关子查询)
SELECT d.*, (SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d, (
	SELECT department_id, AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
    ORDER BY avg_sal ASC
    LIMIT 0,1
) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.`department_id`;
  1. 查询平均工资最高的job信息
SELECT *
FROM jobs
WHERE job_id = (
	SELECT job_id
    FROM employees
    GROUP BY job_id
    HAVING AVG(salary) = (
    	SELECT MAX(avg_sal)
        FROM (
        	SELECT AVG(salary) avg_sal
            FROM employees
            GROUP BY job_id
        ) t_job_avg_sal
    )
);
  1. 查询平均工资高于公司平均工资的部门有哪些？
SELECT depatment_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
	SELECT AVG(salary)
    FROM eployees
);
  1. 查询出公司中所有manager的详细信息
# 方式1：自连接
SELECT DISTINCT *
FROM employees emp, employees manager
WHERE emp.`manager_id` = manager.`employee_id`;

SELECT DISTINCT *
FROM employees emp JOIN employees manager
ON emp.`manager_id` = manager.`employee_id`; 

# 方式2：子查询
SELECT *
FROM employees
WHERE employee_id IN (
	SELECT manager_id
    FROM employees
);

# 方式3：EXISTS
SELECT *
FROM employees manager
WHERE EXISTS (
	SELECT *
    FROM employees emp
    WHERE manager.`employee_id` = emp.`manager_id`
);
  1. 各个部门中，最高工资中最低的那个部门的最低工资是多少？
# 方式一：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
	GROUP BY department_id
	HAVING MAX(salary) = (
    	SELECT MIN(max_sal)
        FROM (
        	SELECT MAX(salary) max_sal
            FROM employees
            GROUP BY department_id
        ) t_dept_max_sal
    ) 
);

# 方式二：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
	GROUP BY department_id
	HAVING MAX(salary) <= ALL (
        SELECT MAX(salary)
        FROM employees
        GROUP BY department_id
    ) 
);

# 方式三：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
	GROUP BY department_id
	HAVING MAX(salary) = (
        SELECT MAX(salary) max_sal
        FROM employees
        GROUP BY department_id
        ORDER BY max_sal ASC
        LIMIT 0,1
    ) 
);

# 方式四：
FROM employees e, (
	SELECT department_id, MAX(salary) max_sal
    FROM employees
    GROUP BY department_id
    ORDER BY max_sal ASC
    LIMIT 0,1
) t_dept_max_sal
WHERE e.`department_id` = t_dept_max_sal.`department_id`;
  1. 查询平均工资最高的部门的manager的详细信息：last_name, department_id, email, salary
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
	SELECT DISTINCT manager_id
    FROM employees
    WHERE department_id = (
    	SELECT department_id
        FROM employees
        GROUP BY department_id
        HAVING AVG(salary) = (
        	SELECT MAX(avg_sal)
            FROM (
            	SELECT AVG(salary) avg_sal
                FROM employees
                GROUP BY department_id
            ) t_dept_avg_sal
        )
    )
);

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
    SELECT DISTINCT manager_id
    FROM employees e, (
        SELECT department_id, AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
        ORDER BY avg_sal DESC
        LIMIT 0,1
    ) t_dept_avg_sal
    WHERE e.`department_id` = t_dept_avg_sal.`department_id`
);
  1. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
SELECT department_id
FROM departments
WHERE department_id NOT IN (
	SELECT DISTINCT department_id
    FROM employees
    WHERE job_id = `ST_CLERK`
);

SELECT department_id
FROM department d
WHERE NOT EXISTS (
	SELECT *
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
    AND e.`job_id` = 'ST_CLERK'
);
  1. 选择所有没有管理者的员工的last_name
SELECT last_name
FROM employees emp
WHERE NOT EXISTS (
	SELECT *
    FROM employees mgr
    WHERE emp.`manager_id` = mgr.`employee_id`
);
  1. 查询员工号、姓名、雇用时间、工资，其中员工的管理者为 ‘De Haan'
SELECT employee_id, last_name, hire_date, salary
FROM employee
WHERE manager_id IN (
	SELECT manager_id
    FROM employee
    WHERE last_name = 'De Haan'
);
  1. 查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资（相关子查询）
SELECT department_id, last_name, salary
FROM employees e1
WHERE salary > (
	SELECT AVG(salary)
    FROM employees e2
    WHERE e2.`department_id` = e1.`department_id`
);

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`;
  1. 查询每个部门下的部门人数大于5的部门名称（相关子查询）
SELECT department_name
FROM departments d
WHERE 5 < (
	SELECT COUNT(*)
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
);
  1. 查询每个国家下的部门个数大于2的国家编号（相关子查询）
SELECT country_id
FROM locations l
WHERE 2 < (
	SELECT COUNT(*)
    FROM department d
    WHERE l.`location_id` = d.`location_id`
);

第十章_创建和管理表

1. 基础知识

1) 标识符命名规则

  • 数据库名、表名不得超过30个字符，变量名限制为29个
  • 必须只能包含 A–Z, a–z, 0–9, _共63个字符
  • 数据库名、表名、字段名等对象名中间不要包含空格
  • 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
  • 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使 用`（着重号）引起来
  • 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据 类型在一个表里是整数，那在另一个表里可就别变成字符型了

2) MySQL中的数据类型

类型数据变量
整数类型TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型FLOAT、DOUBLE
定点数类型DECIMAL
位类型BIT
日期时间类型YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型ENUM
集合类型SET
二进制字符串类型BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型JSON对象、JSON数组
空间数据类型单值：GEOMETRY、POINT、LINESTRING、POLYGON； 集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

其中，常用的几类类型介绍如下：

数据类型描述
INT从-2^31到2^31-1的整型数据。存储大小为 4个字节
CHAR(size)FLOAT、DOUBLE
VARCHAR(size)DECIMAL
FLOAT(M,D)BIT
DOUBLE(M,D)YEAR、TIME、DATE、DATETIME、TIMESTAMP
DECIMAL(M,D)CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
DATEENUM
BLOBSET
TEXTBINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

2. 创建和管理数据库

1) 创建数据库

  • 方式1：创建数据库

    CREATE DATABASE 数据库名;

  • 方式2：创建数据库并指定字符集

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

  • 方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）

    CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

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

2) 使用数据库

  • 查看当前所有的数据库

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

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

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

  • 查看指定库下所有的表

    SHOW TABLES FROM 数据库名

  • 查看数据库的创建信息

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

  • 使用/切换数据库

    USE 数据库名;

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

3) 修改数据库

  • 更改数据库字符集

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

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

    DROP DATABASE 数据库名;

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

    DROP DATABASE IF EXISTS 数据库名;

3. 创建表

1) 创建方式1

  • 语法格式：
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);

加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表； 如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

2) 创建方式2

  • 使用 AS subquery 选项，将创建表和插入数据结合起来
CREATE TABLE 表名
	[(column, column, ...)]
AS subquery;
  • 指定的列和子查询中的列要一一对应
  • 通过列名和默认值定义列
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

3) 查看数据表结构

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

语法格式如下：

SHOW CREATE TABLE 表名\G

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

4. 修改表

修改表指的是修改数据库中已经存在的数据表的结构。

使用 ALTER TABLE 语句可以实现：

  • 向已有的表中添加列
  • 修改现有表中的列
  • 删除现有表中的列
  • 重命名现有表中的列

1) 追加一个列

语法格式如下：

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

举例：

ALTER TABLE dept80
ADD job_id varchar(15);

2) 修改一个列

  • 可以修改列的数据类型，长度、默认值和位置
  • 修改字段数据类型、长度、默认值、位置的语法格式如下：
ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
  • 举例：
ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;
  • 对默认值的修改只影响今后对表的修改
  • 此外，还可以通过此种方式修改列的约束。

3) 重命名一个列

使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下：

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

举例：

ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);

4) 删除一个列

删除表中某个字段的语法格式如下：

ALTER TABLE 表名 DROP 【COLUMN】字段名

5) 更改表名

  • 方式一：使用RENAME
RENAME TABLE emp
TO myemp;
  • 方式二：
ALTER table dept
RENAME [TO] detail_dept; -- [TO]可以省略
  • 必须是对象的拥有者

5. 删除表

  • 在MySQL中，当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
  • 数据和结构都被删除
  • 所有正在运行的相关事务被提交
  • 所有相关索引被删除
  • 语法格式：
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

IF EXISTS 的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存 在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

举例：

DROP TABLE dept80;
  • DROP TABLE 语句不能回滚

6. 清空表

  • TRUNCATE TABLE语句：
    • 删除表中所有的数据
    • 释放表的存储空间
  • 举例：
TRUNCATE TABLE detail_dept;
  • TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

阿里开发规范： 【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无 事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。 说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

7. 内容扩展

拓展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错误日志中。

avatar
文章
阅读
粉丝