1.简单查询
2.条件查询
3.表达式和函数
4.分组查询
5.简单查询语句中各子句的顺序
6.子查询
7.连接查询
8.组合查询 UNION
- 包含或去除重复的行
- 组合查询中的ORDER BY和LIMIT子句
9.数据的插入、删除和更新

1.简单查询

去重

SELECT DISTINCT 列名 FROM 表名;

mysql> SELECT department FROM student_info;
+-----------------+
| department      |
+-----------------+
| 计算机学院      |
| 计算机学院      |
| 计算机学院      |
| 计算机学院      |
| 航天学院        |
| 航天学院        |
+-----------------+
6 rows in set (0.00 sec)
mysql> SELECT DISTINCT department FROM student_info;
+-----------------+
| department      |
+-----------------+
| 计算机学院      |
| 航天学院        |
+-----------------+
2 rows in set (0.00 sec)

同时针对多列去重

SELECT DISTINCT 列名1, 列名2, ... 列名n  FROM 表名;

mysql> SELECT department, major FROM student_info;
+-----------------+--------------------------+
| department      | major                    |
+-----------------+--------------------------+
| 计算机学院      | 计算机科学与工程         |
| 计算机学院      | 计算机科学与工程         |
| 计算机学院      | 软件工程                 |
| 计算机学院      | 软件工程                 |
| 航天学院        | 飞行器设计               |
| 航天学院        | 电子信息                 |
+-----------------+--------------------------+
6 rows in set (0.00 sec)
mysql> SELECT DISTINCT department, major FROM student_info;
+-----------------+--------------------------+
| department      | major                    |
+-----------------+--------------------------+
| 计算机学院      | 计算机科学与工程         |
| 计算机学院      | 软件工程                 |
| 航天学院        | 飞行器设计               |
| 航天学院        | 电子信息                 |
+-----------------+--------------------------+
4 rows in set (0.00 sec)
mysql>

限制查询结果条数

  - 开始行指的是我们想从第几行数据开始查询(从0开始计数)，限制条数是结果集中最多包含多少条记录。
  - LIMIT后边也可以只有一个参数，那这个参数就代表着限制行数。也就是说我们可以不指定开始行，默认的开始行就是第0行

LIMIT 开始行, 限制条数;

mysql> SELECT number, name, id_number, major FROM student_info LIMIT 0, 2;
+----------+-----------+--------------------+--------------------------+
| number   | name      | id_number          | major                    |
+----------+-----------+--------------------+--------------------------+
| 20180101 | 杜子腾    | 158177199901044792 | 计算机科学与工程         |
| 20180102 | 杜琦燕    | 151008199801178529 | 计算机科学与工程         |
+----------+-----------+--------------------+--------------------------+
2 rows in set (0.00 sec)
mysql>

  - 如果指定的开始行大于结果中的行数，那查询结果就什么都没有：

mysql> SELECT number, name, id_number, major FROM student_info LIMIT 6, 2;
Empty set (0.00 sec)
mysql>

  - 如果查询的结果条数不超过限制条数，那就可以全部显式出来

对查询结果排序

  - MySQL默认会按照这些数据底层存储的顺序来给我们返回数据，默认不能确定排序顺序

ORDER BY 列1 ASC|DESC, 列2 ASC|DESC ...

mysql> SELECT * FROM student_score ORDER BY score DESC;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180102 | 母猪的产后护理              |   100 |
| 20180102 | 论萨达姆的战争准备          |    98 |
| 20180101 | 论萨达姆的战争准备          |    88 |
| 20180101 | 母猪的产后护理              |    78 |
| 20180103 | 论萨达姆的战争准备          |    61 |
| 20180103 | 母猪的产后护理              |    59 |
| 20180104 | 母猪的产后护理              |    55 |
| 20180104 | 论萨达姆的战争准备          |    46 |
+----------+-----------------------------+-------+
8 rows in set (0.00 sec)
mysql>

  - 不指定排序方向，则默认使用的是ASC，也就是从小到大的升序规则
  - ORDER BY语句和LIMIT语句结合使用，不过 ORDER BY 语句必须放在 LIMIT 语句前边，比如这样：

mysql> SELECT * FROM student_score ORDER BY score LIMIT 1;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180104 | 论萨达姆的战争准备          |    46 |
+----------+-----------------------------+-------+
1 row in set (0.00 sec)
mysql>

2.条件查询

简单的比较操作符

操作符	示例	描述
=	a = b	a等于b
<>或者!=	a <> b	a不等于b
<	a < b	a小于b
<=	a <= b	a小于或等于b
>	a > b	a大于b
>=	a >= b	a大于或等于b
BETWEEN	a BETWEEN b AND c	满足 b <= a <= c
NOT BETWEEN	a NOT BETWEEN b AND c	不满足 b <= a <= c

匹配枚举出的元素

操作符	示例	描述
IN	a IN (b1, b2, …)	a是b1, b2, … 中的某一个
NOT IN	a NOT IN (b1, b2, …)	a不是b1, b2, … 中的任意一个

匹配NULL值

  - NULL代表没有值，意味着你并不知道该列应该填入什么数据，在判断某一列是否为NULL的时候并不能单纯的使用=操作符，而是需要专业判断值是否是NULL的操作符：

操作符	示例	描述
IS NULL	a IS NULL	a的值是NULL
IS NOT NULL	a IS NOT NULL	a的值不是NULL

多个搜索条件的查询

AND操作符: 且

mysql> SELECT * FROM student_score WHERE subject = '母猪的产后护理' AND score > 75;
+----------+-----------------------+-------+
| number   | subject               | score |
+----------+-----------------------+-------+
| 20180101 | 母猪的产后护理        |    78 |
| 20180102 | 母猪的产后护理        |   100 |
+----------+-----------------------+-------+
2 rows in set (0.00 sec)
mysql>

  - 其中的subject = '母猪的产后护理'和score > 75是两个搜索条件，我们使用AND操作符把这两个搜索条件连接起来表示只有当两个条件都满足的记录才能被加入到结果集。

OR操作符: 或

AND OR 注意事项

     - AND操作符的优先级高于OR操作符，也就是说在判断某条记录是否符合条件时会先检测AND操作符两边的搜索条件

通配符模糊查询

操作符	示例	描述
LIKE	a LIKE b	a匹配b
NOT LIKE	a NOT LIKE b	a不匹配b

  - %：代表任意多个字符
  - _：代表任意单个字符

LIKE或者NOT LIKE操作符只用于字符串匹配。另外，通配符不能代表NULL，如果需要匹配NULL的话，需要使用IS NULL或者IS NOT NULL。

转义通配符

     - 如果匹配字符串中需要普通字符'%'或者'_'的话，需要在它们前边加一个反斜杠\来和通配符区分开来
     - '\%'代表普通字符'%'
     - '\_'代表普通字符'_'

mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '范\_';
Empty set (0.00 sec)
mysql>

3.表达式和函数

操作数

     1. 常数
        1. 常数很好理解，我们平时用到的数字、字符串、时间值什么的都可以被称为常数，它是一个确定的值，比如数字1，字符串'abc'，时间值2019-08-16 17:10:43啥的。
     2. 列名
        1. 针对某个具体的表，它的列名可以被当作表达式的一部分，比如对于student_info表来说，number、name都可以作为操作数。
     3. 函数调用
        1. MySQL中有函数的概念，比方说获取当前时间的函数NOW，而在函数后边加个小括号就算是一个函数调用，比如NOW()。
     4. 标量子查询或者行子查询
        1. <br />
     5. 其他表达式
        1. 一个表达式也可以作为一个操作数与另一个操作数来形成一个更复杂的表达式，比方说（假设col是一个列名）：
           1. (col - 5) / 3
           1. (1 + 1) * 2 + col * 3

操作符

     1. 算术操作符

：

操作符	示例	描述
+	a + b	加法
-	a - b	减法
*	a * b	乘法
/	a / b	除法
DIV	a DIV b	除法，取商的整数部分
%	a % b	取余
-	-a	负号

DIV和/都表示除法操作符，但是DIV只会取商的整数部分，/会保留商的小数部分。比如表达式 2 DIV 3的结果是0，而2 / 3的结果是0.6667

     2. [比较操作符](#WlNE4)

由比较操作符连接而成的表达式也称为布尔表达式，表示真或者假，也可以称为TRUE或者FALSE。比如1 > 3就代表FALSE，3 != 2就代表TRUE

     3. 逻辑操作符

操作符	示例	描述
AND	a AND b	只有a和b同时为真，表达式才为真
OR	a OR b	只要a或b有任意一个为真，表达式就为真
XOR	a XOR b	a和b有且只有一个为真，表达式为真

逻辑操作符是用来将多个布尔表达式连接起来

表达式

  - 把这些操作数和操作符相互组合起来就可以组成一个表达式
  - 放在查询列表中
     - 我们前边都是将列名放在查询列表中的(*号代表所有的列名～)。列名只是表达式中超级简单的一种，我们可以将任意一个表达式作为查询列表的一部分来处理，比方说我们可以在查询student_score表时把score字段的数据都加100，就像这样：

mysql> SELECT  number, subject, score + 100 FROM student_score;
+----------+-----------------------------+-------------+
| number   | subject                     | score + 100 |
+----------+-----------------------------+-------------+
| 20180101 | 母猪的产后护理              |         178 |
| 20180101 | 论萨达姆的战争准备          |         188 |
| 20180102 | 母猪的产后护理              |         200 |
| 20180102 | 论萨达姆的战争准备          |         198 |
| 20180103 | 母猪的产后护理              |         159 |
| 20180103 | 论萨达姆的战争准备          |         161 |
| 20180104 | 母猪的产后护理              |         155 |
| 20180104 | 论萨达姆的战争准备          |         146 |
+----------+-----------------------------+-------------+
8 rows in set (0.00 sec)
mysql>

     - 其中的number、subject、score + 100都是表达式，结果集中的列的名称也将默认使用这些表达式的名称

函数

  - MySQL为我们提供了很多所谓的函数, 例: NOW函数用来获取当前的日期和时间。
  - 如果我们想使用这些函数，可以在函数名后加一个小括号()就好，表示调用一下这个函数，简称函数调用。比方说NOW()就代表调用NOW函数来获取当前日期和时间。针对某些包含参数的函数，我们也可以在小括号()里将参数填入，比方说UPPER('abc')表示将字符串'abc'转换为大写格式。

文本处理函数

名称	调用示例	示例结果	描述
LEFT	LEFT(‘abc123’, 3)	abc	给定字符串从左边取指定长度的子串
RIGHT	RIGHT(‘abc123’, 3)	123	给定字符串从右边取指定长度的子串
LENGTH	LENGTH(‘abc’)	3	给定字符串的长度
LOWER	LOWER(‘ABC’)	abc	给定字符串的小写格式
UPPER	UPPER(‘abc’)	ABC	给定字符串的大写格式
LTRIM	LTRIM(‘ abc’)	abc	给定字符串左边空格去除后的格式
RTRIM	RTRIM(‘abc ‘)	abc	给定字符串右边空格去除后的格式
SUBSTRING	SUBSTRING(‘abc123’, 2, 3)	bc1	给定字符串从指定位置截取指定长度的子串
CONCAT	CONCAT(‘abc’, ‘123’, ‘xyz’)	abc123xyz	将给定的各个字符串拼接成一个新字符串

  - 函数调用也算是一种表达式的操作数，它可以和其他操作数用操作符连接起来组成一个表达式来作为查询列表的一部分或者放到搜索条件中. 举例 CONCAT():

mysql> SELECT CONCAT('学号为', number, '的学生在《', subject, '》课程的成绩是：', score) AS 成绩描述 FROM student_score;
+---------------------------------------------------------------------------------------+
| 成绩描述                                                                              |
+---------------------------------------------------------------------------------------+
| 学号为20180101的学生在《母猪的产后护理》课程的成绩是：78                              |
| 学号为20180101的学生在《论萨达姆的战争准备》课程的成绩是：88                          |
| 学号为20180102的学生在《母猪的产后护理》课程的成绩是：100                             |
| 学号为20180102的学生在《论萨达姆的战争准备》课程的成绩是：98                          |
| 学号为20180103的学生在《母猪的产后护理》课程的成绩是：59                              |
| 学号为20180103的学生在《论萨达姆的战争准备》课程的成绩是：61                          |
| 学号为20180104的学生在《母猪的产后护理》课程的成绩是：55                              |
| 学号为20180104的学生在《论萨达姆的战争准备》课程的成绩是：46                          |
+---------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql>

日期和时间处理函数

名称	调用示例	示例结果	描述
NOW	NOW()	2019-08-16 17:10:43	返回当前日期和时间
CURDATE	CURDATE()	2019-08-16	返回当前日期
CURTIME	CURTIME()	17:10:43	返回当前时间
DATE	DATE(‘2019-08-16 17:10:43’)	2019-08-16	将给定日期和时间值的日期提取出来
DATE_ADD	DATE_ADD(‘2019-08-16 17:10:43’, INTERVAL 2 DAY)	2019-08-18 17:10:43	将给定的日期和时间值添加指定的时间间隔
DATE_SUB	DATE_SUB(‘2019-08-16 17:10:43’, INTERVAL 2 DAY)	2019-08-14 17:10:43	将给定的日期和时间值减去指定的时间间隔
DATEDIFF	DATEDIFF(‘2019-08-16’, ‘2019-08-17’);	-1	返回两个日期之间的天数（负数代表前一个参数代表的日期比较小）
DATE_FORMAT	DATE_FORMAT(NOW(),’%m-%d-%Y’)	08-16-2019	用给定的格式显示日期和时间

数值处理函数

名称	调用示例	示例结果	描述
ABS	ABS(-1)	1	取绝对值
Pi	PI()	3.141593	返回圆周率
COS	COS(PI())	-1	返回一个角度的余弦
EXP	EXP(1)	2.718281828459045	返回e的指定次方
MOD	MOD(5,2)	1	返回除法的余数
RAND	RAND()	0.7537623539136372	返回一个随机数
SIN	SIN(PI()/2)	1	返回一个角度的正弦
SQRT	SQRT(9)	3	返回一个数的平方根
TAN	TAN(0)	0	返回一个角度的正切

聚集函数(统计函数)

  - 如果将上边介绍的那些函数以函数调用的形式放在查询列表中，那么会为表中符合WHERE条件的每一条记录调用一次该函数

函数名	描述
COUNT	返回某列的行数. ( count(*): 返回所有条目数; count(列名): 返回所有列非NULL的条目数 )
MAX	返回某列的最大值
MIN	返回某列的最小值
SUM	返回某列值之和
AVG	返回某列的平均值

聚集函数中DISTINCT的使用

  - 默认情况下，上边介绍的聚集函数将计算指定列的所有非NULL数据，如果我们指定的列中有重复数据的话，可以选择使用DISTINCT来过滤掉这些重复数据。
  - 比方说我们想查看一下student_info表中存储了多少个专业的学生信息，就可以这么写：

mysql> SELECT COUNT(DISTINCT major) FROM student_info;
+-----------------------+
| COUNT(DISTINCT major) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.01 sec)
mysql>

组合聚集函数

  - 这些聚集函数也可以集中在一个查询中使用

mysql> SELECT COUNT(*) AS 成绩记录总数, MAX(score) AS 最高成绩, MIN(score) AS 最低成绩, AVG(score) AS 平均成绩 FROM student_score;
+--------------------+--------------+--------------+--------------+
| 成绩记录总数       | 最高成绩     | 最低成绩     | 平均成绩     |
+--------------------+--------------+--------------+--------------+
|                  8 |          100 |           46 |      73.1250 |
+--------------------+--------------+--------------+--------------+
1 row in set (0.00 sec)
mysql>

隐式类型转换

  - 只要某个值的类型与上下文要求的类型不符，MySQL就会根据上下文环境中需要的类型对该值进行类型转换，由于这些类型转换都是MySQL自动完成的，所以也可以被称为隐式类型转换
     1. 把操作数类型转换为适合操作符计算的相应类型。
     1. 将函数参数转换为该函数期望的类型
     1. 存储数据时，把某个值转换为某个列需要的类型(单向的 数字 --> 字符串)
  - MySQL会尽量把值转换为表达式中需要的类型，而不是产生错误。

4.分组查询

分组查询: GROUP BY

  - 针对某个列，将该列的值的相同记录分到一个组中

mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject;
+-----------------------------+------------+
| subject                     | AVG(score) |
+-----------------------------+------------+
| 母猪的产后护理              |    73.0000 |
| 论萨达姆的战争准备          |    73.2500 |
+-----------------------------+------------+
2 rows in set (0.01 sec)
mysql>

  - 对于查询结果中设定某列, 其: 既非聚合函数, 又不是分组列. 则分组后 组的该列数据是不能确定取哪个组成员的值的. 就会出现报错:

mysql> SELECT number, subject, AVG(score) FROM student_score GROUP BY subject;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xiaohaizi.student_score.number' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>

带有WHERE子句的分组查询

  - 这个过程可以分成两个步骤理解： 先 WHERE 后 GROUP BY
     1. 将记录进行过滤后分组。
     1. 分别对各个分组进行数据统计, 产生最终结果

分组后的过滤条件 HAVING

  - 流程: WHERE => GROUP BY => HAVING

SELECT subject, AVG(score) FROM student_score GROUP BY subject having subject = '母猪的产后护理';

嵌套分组

  - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12524106/1632580051133-ae15c9fb-a848-4606-9d61-1b63f6849af8.png#clientId=uc6b33205-bc18-4&from=paste&height=215&id=ub839d829&margin=%5Bobject%20Object%5D&name=image.png&originHeight=429&originWidth=733&originalType=binary&ratio=1&size=174877&status=done&style=none&taskId=u4f74b1ca-7e84-45b6-bc8e-91541323ff6&width=366.5)
  - 现在有了2个大分组，4个小分组，我们把这种对大的分组下继续分组的的情形叫做嵌套分组，如果你乐意，你可以继续把小分组划分成更小的分组。我们只需要在GROUP BY子句中把各个分组列依次写上，用逗号,分隔开就好了。比如这样：

mysql> SELECT department, major, COUNT(*) FROM student_info GROUP BY department, major;
+-----------------+--------------------------+----------+
| department      | major                    | COUNT(*) |
+-----------------+--------------------------+----------+
| 航天学院        | 电子信息                 |        1 |
| 航天学院        | 飞行器设计               |        1 |
| 计算机学院      | 计算机科学与工程         |        2 |
| 计算机学院      | 软件工程                 |        2 |
+-----------------+--------------------------+----------+
4 rows in set (0.00 sec)
mysql>

  - 可以看到，在嵌套分组中，聚集函数将作用在最后一个分组列上，在这个例子中就是major列。

使用分组注意事项

     1. 如果分组列中含有NULL值，那么NULL也会作为一个独立的分组存在。
     1. 如果存在多个分组列，也就是嵌套分组，聚集函数将作用在最后的那个分组列上。
     1. 如果查询语句中存在WHERE子句和ORDER BY子句，那么GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
     1. 非分组列不能单独出现在检索列表中(可以被放到聚集函数中)。
     1. GROUP BY子句后也可以跟随表达式(但不能是聚集函数)。
     1. WHERE子句和HAVING子句的区别。WHERE子句在分组前进行过滤，作用于每一条记录，WHERE子句过滤掉的记录将不包括在分组中。而HAVING子句在数据分组后进行过滤，作用于整个分组。

5.简单查询语句中各子句的顺序

SELECT [DISTINCT] 查询列表
[FROM 表名]
[WHERE 布尔表达式]
[GROUP BY 分组列表 ]
[HAVING 分组过滤条件]
[ORDER BY 排序列表]
[LIMIT 开始行, 限制条数]

  - 其中中括号[]中的内容表示可以省略，我们在书写查询语句的时候各个子句必须严格遵守这个顺序，不然会报错的！

6.子查询

标量子查询

  - 第二条查询语句的搜索条件用到了第一条查询语句的查询结果。为了书写简便，我们可以把这两条语句合并到一条语句中. 
  - 我们把第二条查询语句用小括号()扩起来作为一个操作数放到了第一条的搜索条件处，这样就起到了合并两条查询语句的作用。小括号中的查询语句也被称为子查询或者内层查询，使用内层查询的结果作为搜索条件的操作数的查询称为外层查询。如果你在一个查询语句中需要用到更多的表的话，那么在一个子查询中可以继续嵌套另一个子查询，在执行查询语句时，将按照从内到外的顺序依次执行这些查询。

mysql> SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = '杜琦燕');
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180102 | 母猪的产后护理              |   100 |
| 20180102 | 论萨达姆的战争准备          |    98 |
+----------+-----------------------------+-------+
2 rows in set (0.01 sec)
mysql>

  - 在这个例子中的子查询的结果只有一个值(也就是'杜琦燕'的学号)，这种子查询称之为标量子查询。正因为标量子查询单纯的代表一个值，所以它可以作为表达式的操作数来参与运算，它除了用在外层查询的搜索条件中以外，也可以被放到查询列表处，比如这样：

mysql> SELECT (SELECT number FROM student_info WHERE name = '杜琦燕') AS 学号;
+----------+
| 学号     |
+----------+
| 20180102 |
+----------+
1 row in set (0.00 sec)
mysql>

列子查询

  - 很显然第一条查询语句的结果集中并不是一个单独的值，而是一个列（本例中第一条查询语句的结果集中该列包含2个值，分别是：20180101和20180102），所以它对应的子查询也被称之为列子查询。因为列子查询得到的结果是多个值，相当于一个列表。我们前边的章节中说过，IN和NOT IN操作符正好是用来匹配列表的，上边使用的例子是使用IN操作符和子查询的结果组成表达式来作为外层查询的搜索条件的。

mysql> SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180101 | 母猪的产后护理              |    78 |
| 20180101 | 论萨达姆的战争准备          |    88 |
| 20180102 | 母猪的产后护理              |   100 |
| 20180102 | 论萨达姆的战争准备          |    98 |
+----------+-----------------------------+-------+
4 rows in set (0.00 sec)
mysql>

行子查询

  - 只要子查询的结果集中最多只包含一条记录，而且这条记录中有超过一个列的数据（如果该条记录只包含一个列的话，该子查询就成了标量子查询），那么这个子查询就可以被称之为行子查询

mysql> SELECT * FROM student_score WHERE (number, subject) = (SELECT number, '母猪的产后护理' FROM student_info LIMIT 1);
+----------+-----------------------+-------+
| number   | subject               | score |
+----------+-----------------------+-------+
| 20180104 | 母猪的产后护理        |    55 |
+----------+-----------------------+-------+
1 row in set (0.01 sec)
mysql>

在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用LIMIT 1子句来限制记录数量。

表子查询

  - 如果子查询结果集中包含多行多列，那么这个子查询也可以被称之为表子查询

mysql> SELECT * FROM student_score WHERE (number, subject) IN (SELECT number, '母猪的产后护理' FROM student_info WHERE major = '计算机科学与工程');
+----------+-----------------------+-------+
| number   | subject               | score |
+----------+-----------------------+-------+
| 20180101 | 母猪的产后护理        |    78 |
| 20180102 | 母猪的产后护理        |   100 |
+----------+-----------------------+-------+
2 rows in set (0.00 sec)
mysql>

EXISTS和NOT EXISTS子查询

  - 有时候外层查询并不关心子查询中的结果是什么，而只关心子查询的结果集是不是为空集，这时可以用到这两个操作符：

操作符	示例	描述
EXISTS	EXISTS (SELECT …)	当子查询结果集存在(不是空集)时表达式为真
NOT EXISTS	NOT EXISTS (SELECT …)	当子查询结果集不存在(空集)时表达式为真

mysql> SELECT * FROM student_score WHERE EXISTS (SELECT * FROM student_info WHERE number = 20180108);
Empty set (0.00 sec)
mysql>

  - 其中子查询的意思是在student_info表中查找学号为20180108的学生信息，很显然并没有学号为20180108的学生，所以子查询的结果集是一个空集，于是EXISTS表达式的结果为FALSE，所以外层查询也就不查了，直接返回了一个Empty set，表示没有结果。

7.连接查询

简介

  - 连接的本质就是把各个表中的记录都取出来依次匹配的组合加入结果集并返回给用户。
  - ![](https://cdn.nlark.com/yuque/0/2021/webp/12524106/1632733789853-1b450cfd-0f0f-49e4-9c80-d436ac995603.webp#clientId=uf2253c52-e6ea-4&from=paste&id=u7f35215a&margin=%5Bobject%20Object%5D&originHeight=431&originWidth=1077&originalType=url&ratio=1&status=done&style=none&taskId=u2654e492-33ce-4a17-b834-1aef37924e5)

内连接

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM t1, t2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
|    3 | c    |    2 | b    |
|    1 | a    |    3 | c    |
|    2 | b    |    3 | c    |
|    3 | c    |    3 | c    |
|    1 | a    |    4 | d    |
|    2 | b    |    4 | d    |
|    3 | c    |    4 | d    |
+------+------+------+------+
9 rows in set (0.00 sec)

  - 查询列表处的*代表从FROM语句后列出的表中选取每个列，上边的查询语句其实和下边这几种写法都是等价的：

SELECT t1.m1, t1.n1, t2.m2, t2.n2 FROM t1, t2;
SELECT m1, n1, m2, n2 FROM t1, t2;
SELECT t1.*, t2.* FROM t1, t2;

  - 内连接的等价写法:

SELECT * FROM t1, t2;
SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;

连接过程

  - 如果我们乐意，我们可以连接任意数量张表，但是如果没有任何限制条件的话，这些表连接起来产生的笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接起来产生的笛卡尔积就有100×100×100=1000000行数据！所以在连接的时候过滤掉特定记录组合是有必要的
  - 对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接。

外连接

  - 在MySQL中，根据选取驱动表的不同，外连接细分为2种：
     - 左外连接: LEFT JOIN
        - 选取左侧的表为驱动表。

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

     - 右外连接: RIGHT JOIN
        - 选取右侧的表为驱动表。

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

连接查询中两种过滤条件

  - WHERE
     - WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。
  - ON
     - 对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。
     - ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。
     - 一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。

多表连接

  - 类比双表

自连接

  - 上边说的都是多个不同的表之间的连接，其实同一个表也可以进行连接。比方说我们可以对两个t1表来生成笛卡尔积

mysql> SELECT * FROM t1, t1;
ERROR 1066 (42000): Not unique table/alias: 't1'
mysql>

  - 报错: 因为设计MySQL不允许FROM子句中出现相同的表名。我们这里需要的是两张一模一样的t1表进行连接，为了把两个一样的表区分一下，需要为表定义别名。比如这样：

mysql> SELECT * FROM t1 AS table1, t1 AS table2;
+------+------+------+------+
| m1   | n1   | m1   | n1   |
+------+------+------+------+
|    1 | a    |    1 | a    |
|    2 | b    |    1 | a    |
|    3 | c    |    1 | a    |
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
|    3 | c    |    2 | b    |
|    1 | a    |    3 | c    |
|    2 | b    |    3 | c    |
|    3 | c    |    3 | c    |
+------+------+------+------+
9 rows in set (0.00 sec)
mysql>

  - 这里相当于我们为t1表定义了两个副本，一个是table1，另一个是table2，其连接过程可认为两个不同的表

表别名

  - 我们也可以为表来定义别名，格式与定义列的别名一致，都是用空白字符或者AS隔开，这个在表名特别长的情况下可以让语句表达更清晰一些

8.组合查询 UNION

  - 把两个查询语句的结果集合并到一个大的结果集中

mysql> SELECT m1, n1 FROM t1 WHERE m1 < 2;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)
mysql> SELECT m2, n2 FROM t2 WHERE m2 > 2;
+------+------+
| m2   | n2   |
+------+------+
|    3 | c    |
|    4 | d    |
+------+------+
2 rows in set (0.00 sec)

  - 第一个查询是从t1表中查询m1, n1这两个列的数据，第二个查询是从t2表中查询m2, n2这两个列的数据。我们可以使用UNION直接将这两个查询语句拼接到一起：

mysql> SELECT m1, n1 FROM t1 WHERE m1 < 2 UNION SELECT m2, n2 FROM t2 WHERE m2 > 2;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.01 sec)
mysql>

  - 第一个查询的查询列表处的m1和第二个查询的查询列表的m2对应，第一个查询的查询列表处的n1和第二个查询的查询列表的n2对应，m1/m2虽然类型不同，但MySQL会帮助我们自动进行必要的类型转换。
  - 这几个查询语句的结果集都可以被合并到一个大的结果集中，但是这个大的结果集总是要有展示一下列名的吧，所以就规定组合查询的结果集中显示的列名将以第一个查询中的列名为准，上边的例子就采用了第一个查询中的m1, n1作为结果集的列名。

包含或去除重复的行

  - UNION来合并多个查询的记录会默认过滤掉重复的记录。由于t1表和t2表都有(2, b)、(3, c)这两条记录，所以合并后的结果集就把他俩去重了:

mysql> SELECT m1, n1 FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)
mysql> SELECT m2, n2 FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)
mysql> SELECT m1, n1 FROM t1 UNION SELECT m2, n2 FROM t2;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)
mysql>

  - 如果我们想要保留重复记录，可以使用UNION ALL来连接多个查询：

mysql> SELECT m1, n1 FROM t1 UNION ALL SELECT m2, n2 FROM t2;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
6 rows in set (0.00 sec)
mysql>

组合查询中的ORDER BY和LIMIT子句

  - 组合查询会把各个查询的结果汇总到一块，如果我们相对最终的结果集进行排序或者只保留几行的话，可以在组合查询的语句末尾加上ORDER BY和LIMIT子句

mysql> SELECT m1, n1 FROM t1 UNION SELECT m2, n2 FROM t2 ORDER BY m1 DESC LIMIT 2;
+------+------+
| m1   | n1   |
+------+------+
|    4 | d    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)
#加上括号()区分查询块
mysql> (SELECT m1, n1 FROM t1) UNION (SELECT m2, n2 FROM t2) ORDER BY m1 DESC LIMIT 2;
+------+------+
| m1   | n1   |
+------+------+
|    4 | d    |
|    3 | c    |
+------+------+
2 rows in set (0.01 sec)
mysql>

  - 由于最后的结果集展示的列名是第一个查询中给定的列名，所以ORDER BY子句中指定的排序列也必须是第一个查询中给定的列名（别名也可以）
  - 对各小查询部分进行 group by / limit:

mysql> (SELECT m1, n1 FROM t1 ORDER BY m1 DESC LIMIT 1) UNION (SELECT m2, n2 FROM t2 ORDER BY m2 DESC LIMIT 1);
+------+------+
| m1   | n1   |
+------+------+
|    3 | c    |
|    4 | d    |
+------+------+
2 rows in set (0.00 sec)
mysql>

  - 最终结果集中的(3, 'c')其实就是查询(SELECT m1, n1 FROM t1 ORDER BY m1 DESC LIMIT 1)的结果，(4, 'd')其实就是查询(SELECT m2, n2 FROM t2 ORDER BY m2 DESC LIMIT 1)的结果。

9.数据的插入、删除和更新

插入完整的记录

  - 在插入完整的一条记录时，需要我们指定要插入表的名称和该条记录中全部列的具体数据，完整的语法是这样：

INSERT INTO 表名 VALUES(列1的值，列2的值, ..., 列n的值);

  - VALUES语句中必须给出表中所有列的值，缺一个都不行，如果我们不知道向某个列填什么值，可以使用填入NULL（前提是该列没有声明NOT NULL属性）

插入记录的一部分

  - 在插入记录的时候，某些列的值可以被省略，但是这个列必须满足下边列出的某个条件之一：
     - 该列允许存储NULL值
     - 该列有DEFAULT属性，给出了默认值

INSERT INTO 表名 (列1,列3,列5,...) VALUES(列1的值，列3的值,列5的值, ...);

  - INSERT语句中指定的列顺序可以改变，但是一定要和VALUES列表中的值一一对应起来。

批量插入记录

  - 直接在VALUES后多加几组值，每组值用小括号()扩起来，各个组之间用逗号分隔

mysql> INSERT INTO first_table(first_column, second_column) VALUES(7, 'ggg'), (8, 'hhh');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql>

将某个查询的结果集插入表中

INSERT INTO 目标表名(目标列1, 目标列2) SELECT 查找列1, 查找列2 FROM 查找的表名 ;

  - 在将某个查询的结果集插入到表中时需要注意，INSERT语句指定的列要和查询列表中的表达式一一对应

INSERT IGNORE

  - 于一些是主键或者具有UNIQUE约束的列或者列组合来说，它们不允许重复值的出现，比如我们把first_table的first_column列添加一个UNIQUE约束.  测试中: 因为first_column列有了UNIQUE约束，所以如果待插入记录的first_column列值与已有的值重复的话就会报错

mysql> ALTER TABLE first_table MODIFY COLUMN first_column INT UNIQUE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>

  - 对于那些是主键或者具有UNIQUE约束的列或者列组合来说，如果表中已存在的记录中没有与待插入记录在这些列或者列组合上重复的值，那么就把待插入记录插到表中，否则忽略此次插入操作

mysql> INSERT IGNORE INTO first_table(first_column, second_column) VALUES(1, '哇哈哈') ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>

  - 我们只是简单的在INSERT后边加了个IGNORE单词便不再报错了！对于批量插入的情况，INSERT IGNORE同样适用

INSERT ON DUPLICATE KEY UPDATE

  - 对于主键或者有唯一性约束的列或列组合来说，新插入的记录如果和表中已存在的记录重复的话，我们可以选择的策略不仅仅是忽略该条记录的插入，也可以选择更新这条重复的旧记录: 对于那些是主键或者具有UNIQUE约束的列或者列组合来说，如果表中已存在的记录中没有与待插入记录在这些列或者列组合上重复的值，那么就把待插入记录插到表中，否则按照规定去更新那条重复的记录中某些列的值

INSERT (通用的插入语句) ON DUPLICATE KEY UPDATE ()对于重复值的默认更新处理的操作)

mysql> INSERT INTO first_table (first_column, second_column) VALUES(1, '哇哈哈') ON DUPLICATE KEY UPDATE second_column = '雪碧';
Query OK, 2 rows affected (0.00 sec)
mysql>

  - 这个语句的意思就是，对于要插入的数据(1, '哇哈哈')来说，如果first_table表中已经存在first_column的列值为1的记录（因为first_column列具有UNIQUE约束），那么就把该记录的second_column列更新为'雪碧'

删除(行)数据

  - 普通的 where筛选 删除行数据

DELETE FROM 表名 [WHERE 表达式];

  - 以使用LIMIT子句来限制想要删除掉的记录数量，使用ORDER BY子句来指定符合条件的记录的删除顺序

DELETE FROM 表名 ORDER BY 排序根据的列名 DESC LIMIT 1;

更新数据

  - 我们在UPDATE单词后边指定要更新的表，然后把你想更新的列的名称和该列更新后的值写到SET单词后边，如果想更新多个列的话，它们之间用逗号,分隔开。如果我们不指定WHERE子句，那么表中所有的记录都会被更新，否则的话只有符合WHERE子句中的条件的记录才可以被更新。

UPDATE 表名 SET 列1=值1, 列2=值2, ...,  列n=值n [WHERE 布尔表达式];

2.常用SQL语句

1.简单查询

去重

同时针对多列去重

限制查询结果条数

对查询结果排序

2.条件查询

简单的比较操作符

匹配枚举出的元素

匹配NULL值

多个搜索条件的查询

AND操作符: 且

OR操作符: 或

AND OR 注意事项

通配符模糊查询

转义通配符

3.表达式和函数

操作数

操作符

表达式

函数

文本处理函数

日期和时间处理函数

数值处理函数

聚集函数(统计函数)

聚集函数中DISTINCT的使用

组合聚集函数

隐式类型转换

4.分组查询

分组查询: GROUP BY

带有WHERE子句的分组查询

分组后的过滤条件 HAVING

嵌套分组

使用分组注意事项

5.简单查询语句中各子句的顺序

6.子查询

标量子查询

列子查询

行子查询

表子查询

EXISTS和NOT EXISTS子查询

7.连接查询

简介

内连接

连接过程

外连接

连接查询中两种过滤条件

多表连接

自连接

表别名

8.组合查询 UNION

包含或去除重复的行

组合查询中的ORDER BY和LIMIT子句

9.数据的插入、删除和更新

插入完整的记录

插入记录的一部分

批量插入记录

将某个查询的结果集插入表中

INSERT IGNORE

INSERT ON DUPLICATE KEY UPDATE

删除(行)数据

更新数据