一篇文章解决Mysql8

基于尚硅谷的Mysql8.0视频，修修改改。提取了一些精炼的内容。

首先需要在数据库内引入一张表。链接地址如下。

链接：https://pan.baidu.com/s/1DD83on3J1a2INI7vrqPe4A 
提取码：68jy

会进行持续更新。。

---

1. Mysql目录结构

Mysql的目录结构	说明
bin目录	所有Mysql的可执行文件，比如mysql.exe
MySQLInstanceConfig.exe	数据库的配置向导，在安装时出现的内容
data目录	系统数据库所在的目录
my.ini文件	MySQL的主要配置文件
C:\ProgramData\MySQL\MySQL Server8.0\data\	用户创建的数据库所在的目录

---

2. 基本的SELECT语句

2.1 SQL分类

Sql语言在功能上主要分为以下三大类：

• DDL（Data Definition Languages，数据定义语言），这些语句定义了不同的数据库，表，视图，索引等数据库对象，还可以用来创建，删除，修改数据库和数据表的结构。
  • 主要的语句关键字包括 CREATE， DROP， ALTER等。
• DML（Data Manipulation Languages，数据库操作语言），用于 增，删，改，查。并检查数据库完整性。
  • 主要的关键字包括：Insert，Delete，Update，Select等
  • Select是Sql语言的基础，最为重要。
• DCL（Data Control Languages，数据库控制语言），用于定义数据库，表，字段，用户的访问权限和安全级别。
  • 主要的语句关键字包括：Grant，REVOKE，COMMIT，ROLLBACK，SAVEPOINT等。

因为查询语句使用的非常频繁，所以很多人把查询语句拎出来单独作为：DQL（数据库查询语言）

还有单独将COMMIT，ROLLBACK，取出来称为TCL（事务控制语言）

2.2 SQL语言的规范

2.2.1 基本规则

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

2.2.2 SQL大小写规范

1. Mysql在Window环境下是大小写不敏感的
2. Mysql在Linux环境下是大小写敏感的
3. 数据库类名，表名，表的别名，变量名是严格区分大小写的
4. 关键字，函数名，列名，字段名，列的别名，字段的别名，是忽略大小写的
5. 推荐采用统一的规范
   1. 数据库名，表名，表别名，字段名，字段别名等都小写
   2. SQL关键字，函数名，绑定变量等都大写

2.3 最基本的SELECT ... FROM结构

2.3.1 最基本的SELECT语句

SELECT 1;   // 1

SELECT 1 + 1;  // 2

这种语句只有一个SELECT，没有后面的语句结构，也是一样可以进行查询出来的，查询出来的内容就是结果。列名就是 SELECT后面跟的内容。其实他的表结构就类似于以下

SELECT 1 + 1 FROM DUAL;

DUAL: 伪表。

也就是说，SELECT语句的最基本的构成是以下内容

SELECT * FROM table;

SELECT 字段1, 字段2... FROM 表名

查询一张完整的表，在上面提供过数据库，查询库内的employees表。

-- 使用数据库
USE atguigudb;

-- 查询所有的员工表下的信息

SELECT * from employees;

如果只想查询特定的列，则可以把*改为想要查的列名 

SELECT email FROM employees;

如果有多个列，则可以使用 逗号隔开。

SELECT email, employee_id FROM employees;

2.3.2 列的别名的定义

1. 重命名一个列
2. 便于计算
3. 紧跟列名，也可以在列名和别名中间加上关键字AS，别名使用双引号，以便在别名中包含空格或者特殊的字符并且区分大小写。
4. AS可以省略
5. 建议别名见名知意。

以下举例说明。

-- 原有的sql
SELECT email, employee_id FROM employees;

-- 使用别名后的sql
SELECT email AS em, employee_id emp_id FROM employees;

2.3.3 去除重复行 DISTINCT

查询所有的数据，可能会出现重复的内容，就可以使用取出重复行，比如有以下一个需求。

我需要查询所有的department_id，但是我不希望看见重复的。就可以使用这个关键字

DISTINCT 

SELECT DISTINCT department_id FROM employees;

2.3.4 空值参与运算 IFNULL()

在MySQL查询中，常常遇到某些元组的字段为NULL，其参与的运算就全都为NULL。如何让字段为NULL的元组能按照我们指定的值参与运算呢？本篇博客将教你IFNULL() 函数的使用。

给定一张员工表 employees ，现在的需求是根据表中的 salary 和 commission_pct (即绩效奖金比例，乘工资即为奖金) ，计算每个员工的年总工资。

IFNULL 函数表示：如果字段 commission_pct 不是 (NULL) ，就按 commission_pct 本来的值计算；而如果 commission_pct 为 (NULL) ，就按 0 计算。如下代码所示：

SELECT employee_id, last_name, salary, commission_pct, salary * (1 + IFNULL(commission_pct, 0) * 12) AS "annual salary"
FROM employees; 

有了 IFNULL 函数，在 commission_pct 为 (NULL) 时，就可以按照我们指定的值进行计算。

【结论】

只要在运算中，带有 (NULL) 的元组，在没有其他措施的干预下，其运算结果必为 (NULL) 。
运用 IFNULL 函数，在碰到字段为 (NULL) 的元组时能够按照我们指定的值参与运算。从而保证我们计算的结果不会出现 (NULL) 。

2.3.5 着重号

必须保证你的字段没有和保留字、数据库系统或常见方法冲突。如果坚持使用，在SQL语句中使用 ` ` 引起来。

SELECT * from `order`;

2.3.6 查询常数

用处：我想查询员工表里的employee_id和last_name并且要带上公司名称。我就可以用查询常数来实现。

SELECT 'xxxx有限公司' AS "公司名", employee_id, last_name FROM employees;

2.4 显示表结构 DESC

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等。

2.5 过滤数据 WHERE

以下格式来机械能过滤数据，也就是筛选数据，使用关键字WHERE

SELECT 字段1,字段2 FROM 表名 WHERE 过滤条件;

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

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;

3. 运算符

这一章节主要是运算，用到了开始提到的一个概念，伪表 DUAL

3.1 算数运算符

SELECT 10 + 0, 30 + 50 * 10, 100 - 99.5 FROM DUAL;

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

3.2 比较运算符

3.2.1 等号运算符

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

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

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

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会按照字符串进行比较，其比较的 是每个字符串中字符的ANSI编码是否相等。
• 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
• 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
• 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

  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，其他返回结果与等于运算符相同

 在MySQL中，字符串和数字类型的比较规则可能会引起一些问题。下面列举几个需要注意的事项：
1）如果字符串中包含非数字字符，那么MySQL将无法将其转换成数字类型，比较结果可能会出现异常。
2）当一个字符串被转换成数字类型时，如果其开头是0，那么MySQL会将其视为八进制数进行转换。因此，如果字符串中包含八进制数，可能会导致比较结果出现异常。
3）当进行字符串和数字类型的比较时，MySQL会先将字符串类型的操作数转换成数字类型，这可能会导致一些不必要的性能损失。

总结
当进行比较时，需要注意字符串中是否存在非数字字符和八进制数，以及字符串和数字类型的转换可能会导致性能损失。

3.2.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)

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

3.2.3 空运算符

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

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)

3.2.4 非空运算符

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

3.2.5 最小值运算符

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

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。

3.2.6 最大值运算符

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

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。

3.2.7 BETWEEN AND运算符

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

3.2.8 IN运算符

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

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         |
+----------------------+------------+-------------------+--------------------+

3.2.9 NOT IN运算符

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

3.2.10 LIKE 运算符

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

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

3.2.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 ‘$‘;

3.2.12 REGEXP运算符

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

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

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

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

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

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

3.3. 逻辑运算符

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

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

3.4. 位运算 

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

MySQL支持的位运算符如下：

3.5 运算符的优先级

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

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

4. 排序与分页

4.1 排序规则

1. 使用 ORDER BY 子句排序
   1. ASC（ascend）升序排序
   2. DESC（descend）降序排序
2. ORDER BY 的子句在 SELECT语句的结尾

4.1.1 单列排序

-- 如果查询employees表，根据id进行升序或者降序排序，应该如下

-- 降序排序
SELECT * FROM employees ORDER BY employee_id DESC; 


-- 升序排序 
SELECT * FROM employees ORDER BY employee_id ASC;

4.1.2 多列排序

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

就是排序字段可以使用当前表的所有字段，并不是只能使用select中所查找的字段

4.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;

LIMIT 后面跟的第一个数字是从哪个位置开始，他有点类似于下标的概念。limit索引从0开始，后面跟的数字是步长，就是从前面哪个下标开始，向后查询几条。如果是 10，5。则是从下标为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 不需 要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

 

5.多表查询

5.1 多表查询分类

5.1.1 自链接

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