系统整理Mysql学习笔记——基础命令DQL

2023-08-13 13:56:04

前言

程序开发中，数据库接触的最多，尤其是mysql数据库，平时一直在使用，但是一直对这个数据库概念很模糊，现在系统的整理一下mysql数据库的内容

一、数据库基础命令

1 查看当前所有数据库

show databases;

2 打开指定数据库

use 库名

3 查看当前库的所有表

show tables [from 库名];

后面参数可以指定库名，查看指定库的所有表

4 查看表结构

desc 表名

5 创建表

create table 表名{
	列名 列类型 是否可以为空 是否为主键 默认值 外键
	`t_tm` datetime DEFAULT NULL COMMENT '报案时间'
}

6 必要的语法规范

  1. 不区分大小写，但建议关键字大写，表名、列名小写；
  2. 每条命令最好以分号结尾；
  3. 注释：单行注释：#，多行注释/**/

二、数据库DQL

DQL，即Data Query Language，数据库查询语言

1 查询语句

select 查询列表 from 表名；

查询列表：表中字段、常量值、表达式、函数
查询结果：虚拟表格

1.1 查询常量值

SELECT 1;

1.2 查询表达式

SELECT 1*120*2;

1.3 查询函数

SELECT VERSION();

这个函数有mysql函数，也有自定义函数的

1.4 去重

SELECT DISTINCT `name`, `sex`  FROM `test1`;

其实就是加了一个DISTINCT 关键字，当出现完全一样的记录时，只显示一条，这个“完全一样”指的是一条记录的每一个字段都一样

1.5 +运算符

+运算符只做算数运算

  1. 两个数字相加，算数运行，如1+1
  2. 一个字符和一个数字相加，则尝试将字符转变为数字，然后进行相加，如‘1‘+1，实际为1+1
  3. 一个字符和一个数字相加，若没办法转变，则字符默认为0，如‘qq’+1，实际为0+1
  4. null和任何相加最后为null，如null+1

1.6 concat

字符串拼接

SELECT CONCAT('1','a','b')

2 where条件

2.1 条件运算符

< 
> 
= 
!= 
<> 
>= 
<= 
like 
between and
in
is null
<=> # 安全等于，不常用； <=> null 等同于 is null

like涉及到匹配的通配符分为_、%
%：百分号通配符: 表示任何字符出现任意次数 (可以是0次).
_：下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符.

2.2 逻辑运算符

and
or
not

3. 排序

SELECT * FROM `test1` ORDER BY [字段|表达式|别名|函数] [ASC|DESC],..;

对account字段进行排序，默认ASC，ASC：从小到大，正序
DESC：从大到小，倒序

3.1 根据多个列表进行排序

SELECT * FROM `test1` ORDER BY `sex` DESC, `account` ASC;

这种情况下，以第一个为主要排序条件，也就是先排第一个，然后第二个，然后第三个…

4 常见函数

函数可以简单的分为单行函数、分组函数
注：函数参数可以是字段、常量、表达式，以下为了简单，大多数使用的是常量

4.1 单行函数

4.1.1 字符函数
4.1.1.1 length

length 获取参数值的字节个数

SELECT LENGTH('是我qq123') AS `length`

我的mysql字符编码是utf8，因此汉字三个字节，字母、数字一个字节
可以使用这个命令查看数据库的字节编码，当然这个也可以在my.ini配置文件中配置
SHOW VARIABLES LIKE '%char%';

4.1.1.2 concat

concat 拼接字符串

SELECT CONCAT('是我qq123','_','我知道是你，点赞啊') AS `concat`;
4.1.1.3 upper、lower

upper 将参数变成大写

SELECT UPPER('abc_D') AS `upper`;

lower 将参数变成小写

SELECT LOWER('ABC_d') AS `lower`;
4.1.1.4 substr、substring

substr、substring: 截取字符串，一个字符代表1，起始位置是1

SELECT SUBSTR('是否帮助到你了呢？那么三连吧',12) AS `substring`;
SELECT SUBSTR('是否帮助到你了呢？那么三连吧' FROM 12 FOR 2) AS `substring`;
SELECT SUBSTR('是否帮助到你了呢？那么三连吧' ,12,2) AS `substring`;
4.1.1.5 instr

instr ：返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('是否帮助到你了呢？那么三连吧','三连') AS `instr`;
4.1.1.6 trim

trim：清空字符串两端的指定字符，默认清除空格

SELECT TRIM('    是否帮助到  你了呢？那么三连吧     ') AS `trim`;
SELECT TRIM('a' FROM 'aaa   是否帮助到你了呢？那么三连吧    aa') AS `trim`;
4.1.1.7 lpad

lpad：用指定的字符实现左填充指定长度，现有字符小于指定长度，则在左进行填充指定字符；现有字符大于指定长度，则在右边开始截取

SELECT LPAD('是否帮助到你了呢？那么三连吧',2,'*'); # 截取右边字符
SELECT LPAD('是否帮助到你了呢？那么三连吧',22,'*'); # 在左边填充‘*‘
4.1.1.8 rpad

rpad：用指定的字符实现右填充指定长度，现有字符小于指定长度，则在右边进行填充指定字符；现有字符大于指定长度，则在左边开始截取

SELECT RPAD('是否帮助到你了呢？那么三连吧',2,'*'); # 截取左边字符
SELECT RPAD('是否帮助到你了呢？那么三连吧',22,'*'); # 在右边填充‘*‘
4.1.1.9 replace

replace：替换

SELECT REPLACE('是否帮助到你了呢？那么三连吧','三连','二连') AS `replace`;
4.1.2 数字函数
4.1.2.1 round

round：四舍五入函数，可以简单的理解为，不区分正负数，只对绝对值进行四舍五入。

SELECT ROUND(1.49) as `round`; # 1
SELECT ROUND(-1.49) as `round`; # -1
SELECT ROUND(1.5) as `round`; # 2
SELECT ROUND(-1.5) as `round`; # -2
4.1.2.2 ceil

ceil：天花板（直译），向上取整，总是返回>=该参数的最小整数

SELECT CEIL(-1.02) AS `ceil`; # -1
SELECT CEIL(1.02) AS `ceil`; # 2
4.1.2.3 floor

floor：地板（直译），向下取整，返回<=该参数的最大整数

SELECT FLOOR(-9.9) AS `floor`; # -10
SELECT FLOOR(9.9) AS `floor`; # 9
4.1.2.4 truncate

truncate：截断（直译），截断小数位，不关心小数位大小，不关心正负数，对参数的绝对值的小数进行指定位数的截断

SELECT TRUNCATE(-1.69999,1) AS `truncate`; # -1.6
SELECT TRUNCATE(1.69999,2) AS `truncate`; # 1.69
4.1.2.5 mod

mod：取余，数学取余运算，取余的结果正负根据第一个参数的正负来决定。对应的的公式是a-a/b*b

SELECT MOD(5,3) AS `mod`; # 2 
SELECT MOD(-5,3) AS `mod`; # -2
SELECT MOD(5,-3) AS `mod`; # 2 
SELECT MOD(-5,-3) AS `mod`; # -2
4.1.3 日期函数
4.1.3.1 now()

now：获取当前时间和日期

SELECT NOW() AS `now`;
4.1.3.2 CURDATE()

CURDATE：返回当前系统日期，不包含时间

SELECT CURDATE() AS `curdate`;
4.1.3.3 CURTIME()

CURTIME：返回当前时间，不包含日期

SELECT CURTIME() AS `curtime`;
4.1.3.4 YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND()

获取时间的年、月、日、时、分、秒

SELECT YEAR(NOW()) AS `year`;
SELECT MONTH(NOW()) AS `month`;
SELECT MONTHNAME(NOW()) as `monthName`;
SELECT DAY(NOW()) AS `day`;
SELECT HOUR(NOW()) AS `hour`;
SELECT MINUTE(NOW()) AS `minute`;
SELECT SECOND(NOW()) AS `second`;
4.1.3.5 str_to_date、date_format

STR_TO_DATE(str,format)：将字符转为日期型，str:字符串；format：格式
DATE_FORMAT(date,format)：将日期型转为字符，date:日期；format：格式

SELECT STR_TO_DATE('1996-09-07','%Y-%m-%d') AS `STR_TO_DATE`;
SELECT STR_TO_DATE('07-9-1996','%d-%c-%Y') AS `STR_TO_DATE`;

SELECT DATE_FORMAT(NOW(),'%Y年%c月%d日') AS `date`;
SELECT DATE_FORMAT(NOW(),'%c月%d日%Y年') AS `date`;
格式符 功能
%Y 四位的年份
%y 两位的年份
%m 月份（01,02）
%c 月份（1，2）
%d 日（01,02）
%H 小时（24小时制）
%h 小时（12小时制）
%i 分钟（00,01）
%s 秒
4.1.4 其他函数
SELECT DATABASE() AS `database`; # 查看所在数据库
SELECT VERSION() AS `version`; # 查看版本号
SELECT USER() AS `user`; # 当前用户
4.1.4 流程选择函数
4.1.4.1 if

IF(expr1,expr2,expr3)：流程控制函数，类似于java中的三目运算符，expr1：表达式；expr2：表达式为true时的选择；expr3：表达式为false时的选择。

SELECT IF(10>5,'哈哈','呵呵') AS `IF流程判断函数`;
4.1.4.2 case

CASE case_value WHEN when_value THEN statement_list ELSE statement_list END ;

  1. case_value：可选参数，表达式、变量、常量，类似于java中switch
  2. when_value：必填参数：分支case_value的值，若case_value不存在，则该值应该为boolean来确定是否选择该分支
  3. statement_list：结果集
SELECT CASE 2*8+3
	WHEN 16 THEN
		100
	WHEN 17 THEN
		200
	ELSE
		0
END AS `case`;

SELECT CASE 
	WHEN 5>6 THEN
		2
	WHEN 5>2 THEN
		3
	ELSE
		0
END AS `case`;

4.2 分组函数

分组函数也称之为聚合函数、统计函数，用于统计所用。

4.2.1 sum、avg、min、max、count

SUM([DISTINCT] expr)：对一组数据进行求和，对null字段忽略，对字符字段认为是0，对于时间字段认为是毫秒数。因此可以认为只可以对数值型进行操作。
AVG([DISTINCT] expr)：对一组数据求平均值，对null字段忽略，对字符字段认为是0，对于时间字段认为是毫秒数。因此可以认为只可以对数值型进行操作。
MIN([DISTINCT] expr)：对一组数据取最小值，可以对字符、时间进行操作，字符一般是ASCII、时间是毫秒，对null字段忽略。
MAX([DISTINCT] expr)：对一组数据取最大值，可以对字符、时间进行操作，字符一般是ASCII、时间是毫秒，对null字段忽略。
COUNT(DISTINCT expr,[expr...])：统计一组数据的个数，忽略null值，COUNT(*)和COUNT(1)在innodb中要比COUNT(字段)性能更好一些。

SELECT 
SUM(`account`) AS `sum`,
AVG(`account`) AS `avg`, 
MIN(`account`) AS `min`,
MAX(`account`) AS `max`, 
COUNT(*) AS `count` 
FROM `test1`;

上面每个函数都有一个可选字段DISTINCT，这个是排除一组数据中完全重复数据所用。
注： 和分组函数一起查询的一般为分组字段，也就是order by 后的字段，否则没有意义。

5 分组查询

5.1 简单应用

select 分组函数, 列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组列表【可多个】
【having 筛选条件】
【order by 子句】

类似于上面，order by 一般处于查询语句的后面，如果没有limit，那么它就是最后一个。

SELECT parent_id AS id, SUM(order_num) AS `sum` 
FROM `sys_menu` 
GROUP BY parent_id;

SELECT parent_id AS id, SUM(order_num) AS `sum` 
FROM `sys_menu` 
GROUP BY parent_id
ORDER BY id ASC;


SELECT parent_id AS id, SUM(order_num) AS `sum` 
FROM `sys_menu` 
WHERE `order_num` > 1
GROUP BY parent_id
HAVING `sum` > 10
ORDER BY id ASC;

SELECT parent_id AS id, SUM(order_num) AS `sum` , menu_type AS type
FROM `sys_menu` 
GROUP BY parent_id, menu_type
ORDER BY id ASC;

注意： 查询列表必须特殊要求是分组函数和group by 后出现的字段
以上分组查询中的筛选条件分为两类

类别 数据源 位置 关键字
分组前筛选 原始表 group by子句前面 where
分组后筛选 分组后的结果集 group by子句后面 having

以上在一些特殊的筛选时需要根据分组后的结果集进行筛选，那么就只能选择having，比如某个部门的平均工资大于某个临界值的。
group by 子句支持单个字段分组、多个字段分组（多个字段分组没有顺序要求）、表达式、分组函数

6 连接查询

链表查询，也称之为多表查询，就是在关键字from后面对多个表进行查询，在这里就不得不提到一个现象了，那就是笛卡尔乘积现象。这个是什么意思呢，就好比如有两个表，表1有m行，表2有n表，那么结果是m*n行，就是表1中的每天条数据分别和表二中的所有数据相对应。
发生的原因就是因为没有有效的连接条件，这么这里就需要添加一个有效的连接条件去保障两个表数据的对应关系了。

sql发展分有两个版本，根据年代来区分分别是92标准和99标准，这个有什么差别呢，主要的差别就是在于支持哪些的连接。
连接查询可以分为：

  1. 内连接：等值连接、非等值连接、自连接
  2. 外链接：左外连接、右外连接、全外链接（mysql 不支持）
  3. 交叉连接

刚刚说的区别就是92年的仅仅支持内连接，而且语法比较依赖于where，99年的当然都是支持的。

6.1 92年语法

	SELECT #字段、函数、表达式、常量
	FROM # 表1,表2
	WHERE # 连接条件
6.1.1 等值连接
SELECT  one.id AS `oneId`, two.id AS `twoId`, two.class AS `class`
FROM test1 AS `one`, test2 AS `two`
WHERE one.id = two.test1Id;

连接查询就是上面这个样子，这样的查询应该都是写过的，这里只需要特外注意别名，当使用别名后，那么查询字段不能使用原来的表名去表示了。
不过上面的只是两个表联查，原则上讲是可以N个表查询，那么所对应的连接条件就是N-1个连接条件。

6.1.2 非等值连接
SELECT  one.id AS `oneId`, two.id AS `twoId`, two.class AS `class`
FROM test1 AS `one`, test2 AS `two`
WHERE one.id != two.test1Id;

非等值连接，顾名思义，就是连接条件不是等于，除了等于以外，任何连接条件都可以算是非等值连接，比如<、>、!=、<>等

6.1.3 自连接
SELECT  one.id AS `oneId`,one.`name` AS `oneName`,one.address AS `oneAddr`, two.id AS `twoId`,two.cardnumber AS `cardnumber`, two.`name` AS `twoName`,two.address AS `twoAddr`
FROM test1 AS `one`, test1 AS `two`
WHERE one.id = two.cardnumber;

自连接，其实这几个连接的意思基本相同，不过是连接条件的不同而进行了细分，这个自连接就是对同一个表的连接，这块我在理解的时候也有一点疑惑，一个表为什么还要进行连接？当然是需要的，比如一个业务场景，在一个权限表中，是分为多级权限的，那么就需要用自连接来关联多个权限了，比如父级权限和子级权限的关联查询。
就像上面一样，这里因为cardnumber字段并不是unique的，所以可能会出现多个相同的和id相对应，那么查询出来的结果呢？其实就上面的笛卡尔乘积的现象了。

6.2 99年语法

SELECT  查询列表
FROM 表1 别名 
[连接类型] JOIN 表2 别名 
ON 连接条件
...
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 筛选条件]
[ORDER BY 排序列表]

这个如果只是内连接的话，那么和92年版本的是没有区别的，只是语法上更加的简洁了，因为他将连接条件和where筛选条件区分开了。
下面是连接类型的选择

  • 内连接：inner(可省略）
  • 交叉连接：cross

外链接

  • 左外连接：left [outer]
  • 右外连接：right [outer]
  • 全外连接：full [outer](mysq不支持）
6.2.1 内连接
6.2.1.1 等值连接
SELECT  one.id AS `oneId`, two.id AS `twoId`, two.class AS `class`
FROM test1 AS `one` 
INNER JOIN test2 AS `two`
ON one.id = two.test1Id;

和上面等值查询一致

6.2.1.2 非等值连接
SELECT  one.id AS `oneId`, two.id AS `twoId`, two.class AS `class`
FROM test1 AS `one` 
INNER JOIN test2 AS `two`
ON one.id > two.test1Id;
6.2.1.3 自连接
SELECT  one.id AS `oneId`,one.`name` AS `oneName`,one.address AS `oneAddr`, two.id AS `twoId`,two.cardnumber AS `cardnumber`, two.`name` AS `twoName`,two.address AS `twoAddr`
FROM test1 AS `one`
INNER JOIN test1 AS `two`
ON one.id = two.cardnumber;
6.2.2 外连接
6.2.2.1 左外连接
SELECT  one.id AS `oneId`,one.`name` AS `oneName`,one.address AS `oneAddr`, two.id AS `twoId`,two.test1Id AS `test1Id`, two.`class` AS `class` 
FROM test1 AS `one`
LEFT JOIN test2 AS `two`
ON one.id = two.test1Id;
6.2.2.2 右外连接
SELECT  one.id AS `oneId`,one.`name` AS `oneName`,one.address AS `oneAddr`, two.id AS `twoId`,two.test1Id AS `test1Id`, two.`class` AS `class` 
FROM test1 AS `one`
RIGHT JOIN test2 AS `two`
ON one.id = two.test1Id;

这里全外连接就不介绍了
左外和右外其实都是一样的，不过是方向不同，这个也可以通过调整表的顺序来进行调整，这个是在调整什么呢？
这个差点忘了讲了，就是在外连接查询中， 是有一个主表的概念的，也就是多个表中是有主次划分的，这个就和内连接不同了，内连接就是符合条件的数据都查询出来。那么外连接呢，是查询主表中的所有记录，如果从表中有和它相匹配的记录，则显示出现，否则将显示null。也就是说不管是什么情况下，主表的记录时绝对显示出来的。
外连接的查询结果=内连接结果+主表中有而从表中没有的记录。
左外连接左边的表是主表，右外连接是右边的表是主表。
注： 全外连接可以根据上面外连接的理论来理解，就是左连接和右连接相加去掉重复部分的结果。在理解一下，就是对于全外来说两个表都是主表。全外连接=内连接+表1中有而表2中没有的记录+表2中有而表1中没有的记录

6.2.3 交叉连接

交叉连接是没有连接条件的，其实就是普通的查询，会在多表查询中造成笛卡尔乘积的效果。这，其实运行一次就知道了。

SELECT *
FROM test1 AS `one`
CROSS JOIN test2 AS `two`;

它是等于

SELECT *
FROM test1 AS `one`, test2 AS `two`;

这样应该就很清楚了。

7 子查询

关于子查询这里，原以为自己懂了，现在才发现自己肤浅了，可以通过子查询也完成非常复杂的DQL语句。
子查询的定义：出现在其他语句中的select语句，称为子查询或内查询。
这个其他语句并不是只是指select查询语句，还包括insert、update、delete
按照结果集的不同可以划分为：

  • 标量子查询：结果集中只有一行一列
  • 列子查询：结果集中只有一列多行
  • 行子查询：结果集中有多列多行
  • 表子查询：包括上面任何一种情况，一般为多列多行

按照子查询出现的位置

SELECT 后面：
仅仅支持标量子查询
FROM 后面：
支持表子查询
WHERE 或 HAVING 后面：
标量子查询、列子查询、行子查询

根据这两种划分，其实不难理解，select关键字后面是查询字段，这个位置可以放字段、函数、表达式、常量，但无论是什么都不能是多个值，都应该是一列一行的一个值才可以，所以这里只能放标量子查询；
from关键字后面根据语法来说是一个表，本质上来讲是一个多行多列的结果集，所以这里可以放表子查询；
where和having关键字后面是一个筛选条件，那么它的选择就很多了，所以原则上讲可以看做是支持所有的子查询，这里的行子查询其实和表子查询差别不大，这个格外区分也是因为放置的位置不同罢了。

子查询的特点

  1. 子查询必须放在小括号中
  2. 标量子查询需要搭配单行操作符使用，如=、<、>、<=、>=、<>
  3. 列子查询，一般搭配着多行操作符使用，如：in、not in、 any、some、all

7.1 标量子查询

下面根据标量子查询在不同位置的使用来写一下对应的例子吧，这里只是语法的介绍，所以会尽可能的简单。

7.1.2 Select 后面
SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`, 
(			
	SELECT `class` FROM `grade` WHERE grade.test1Id = stu.id
) AS `class`
FROM `student` AS stu

可以看到子查询SELECT class FROM grade WHERE grade.test1Id = stu.id它的返回值只可能是一条或者是null，这就是标量子查询了，当然这有个前提，就是grade表的test1Id字段必须是unique，否则就会报下面这个错了

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`, 
(			
			SELECT `class` FROM `grade` WHERE grade.test1Id = stu.id
) AS `class`
FROM `student` AS stu
> Subquery returns more than 1 row
> 时间: 0s

假设grade表的test1Id字段是unique，那么使用外连接查询和上面的查询结果其实是一样的。

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`, gra.class AS `class`
FROM `student` AS stu
LEFT JOIN `grade` AS gra
ON stu.id = gra.test1Id

当然如果test1Id，这个字段并不是唯一的，那么子查询会报错，而外连接查询并不会，但是这里结果就存在差异了。
其实这里我是这样理解的，在select关键字后面进行子查询，其实也是在查询多个表中的字段并通过一个结果集来返回，那么它和连接查询本质上是一样的，不过在细分上，连接查询有了多种方式。

7.1.3 Where 后面

where后面也就是作为一个筛选条件了，那么标量子查询这里，可以将它看做是一个表达式、函数，甚至是一个常量，因为子查询就是返回一个值而已，这里和上面还是有些不一样的，这里其实涉及到了sql的执行顺序，可以看一下10DQL总结的内容

从这里不难看出来，标量子查询在不同的位置，会导致子查询执行的时间是不同的，相对而言在WHERE后面的子查询肯定是需要做WHERE判断前得出结果的，这就好像是一个函数一样。

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`
FROM `student` AS stu
WHERE stu.id = (			
			SELECT grade.test1Id FROM `grade` WHERE grade.class = '小学'
)

在我这个业务场景下，其实也可以使用外连接查询来实现，但是这里必须具备一个条件，那就是必须两个表之间具备连接条件才可以

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`
FROM `student` AS stu
LEFT JOIN `grade` AS gra
ON stu.id = gra.test1Id
WHERE gra.class = '小学'

7.2 列子查询

列子查询只可以在WHERE语句后面，列子查询的结果集是单列多行，也就类似于java中的数组。
这里先介绍一下多行比较操作符吧

操作符 含义
IN/NOT IN 等于列表中任意一个
ANY/SOME 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较
7.2.1 IN/NOT

这个用的比较多

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`
FROM `student` AS stu
WHERE stu.id IN (			
			SELECT grade.test1Id FROM `grade`
);

SELECT grade.test1Id FROM grade这个子查询会有多行记录并且只有一列，这就是列子查询了，这里的操作符IN的意思就是student表中的stu.id等于列子查询结果集中任何一个都是符合筛选条件的。

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`
FROM `student` AS stu
WHERE stu.id NOT IN (			
			SELECT grade.test1Id FROM `grade`
);

NOT IN不等于子查询结果集中任何一个，也就是全部不等于的意思。

7.2.2 ANY/SOME

ANY和SOME的含义是一样的，都是指任意一个的意思，结果集中任意一个值。
这个和上面要区分开，上面的只可以做等于和不等于筛选，而这个就不一样了，它相当于在子查询结果集中每一个值都和筛选条件做逻辑判断，只要有任何一个符合条件，那么可以认为这个筛选条件为true。那么的区别就在于逻辑判断上，IN/NOT IN是只可以做=和!=，但是ANY/SOME可以做所有的逻辑判断。这里我认为IN相当于= ANY()。
但是其实除了=和!=两个逻辑判断以外，其他的逻辑判断都可以通过其他的方式来替代，所以这两个操作符在目前的工作中用的不是很多，比如大于，那么其实就是大于子查询中最小的值，就是满足大于子查询中任意一个值了。

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`
FROM `student` AS stu
WHERE stu.id =  ANY(			
			SELECT grade.test1Id FROM `grade`
);
SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`
FROM `student` AS stu
WHERE stu.id =  SOME(			
			SELECT grade.test1Id FROM `grade`
);
7.2.3 ALL

ALL是和子查询返回的所有值进行一个比较，只要有一个不符合条件，那么即这个筛选条件不符合条件。
NOT IN相当于<> ALL()，因为这里NOT IN的意思就相当于不符合子查询中任何一个值，也就是全部都不符合，那么就是<> ALL()

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`
FROM `student` AS stu
WHERE stu.id <>  ALL(			
			SELECT grade.test1Id FROM `grade`
);

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`
FROM `student` AS stu
WHERE stu.id NOT IN(			
			SELECT grade.test1Id FROM `grade`
);

7.3 行子查询

行子查询，对应的是一行多列，或者多行多列，这个可以看出来和上面两种最明显的区别莫过于是多列了，也就是说这个子查询会返回多个列的查询结果，至于是否是多行，其实不是很重要，因为单行就使用单行操作符，多行就使用多行操作符就可以了。

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`
FROM `student` AS stu
WHERE (stu.id,stu.cardnumber) IN (			
			SELECT grade.studentId,grade.id FROM `grade`
);

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`
FROM `student` AS stu
WHERE (stu.id,stu.cardnumber) <>  ALL(			
			SELECT grade.studentId,grade.id FROM `grade`
);

这个详细说一下吧
我的student表的数据是这样的
系统整理Mysql学习笔记——基础命令DQL
grade表数据如下
系统整理Mysql学习笔记——基础命令DQL

这两个表其实很简单，那么看一下上面的第一个SQL，将这个sql翻译一下，就是查询grade表中中存在的学生信息，以grade.id=student.cardnumber，grade.studentId=student.id来作为判断grade和student的关联。这里其实有点多此一举，正常情况下，只需要studentId和student.id相对应就可以了，主键映射。但是这里是介绍语法而已，不必在意。
可以看出来这个好像是一个与逻辑，也就是and，也就是说单单一个字段是不能关联两个表了，需要两个字段同时对应才可以表明两个表的关系，那么上面的查询是不是感觉可以换成是下面这个

SELECT stu.id AS studentId, stu.`name` AS `name`, stu.sex AS `sex`, stu.address AS `address`
FROM `student` AS stu
WHERE stu.id IN (			
			SELECT grade.studentId FROM `grade`
) AND stu.cardnumber 
IN (
			SELECT grade.id FROM `grade`
);

我也是这样想的，但是这样是错的，这里有一点很重要，如果是单行多列的行子查询，下面这个sql是没有问题的，是可以替换掉的，但是如果是多行多列，就像上面写的一样，那么是替换不了的。为什么呢？原因就出在题意上，只有grade.id=student.cardnumber，grade.studentId=student.id，才可以证明grade表中的记录和学生对应上，那么下面的这个查询，IN的含义是子查询中任意一个值相当就符合条件。那么假如我将studen表的数据变一下
系统整理Mysql学习笔记——基础命令DQL
可以看到，我将小李的cardnumber改成了1，将小明的cardnumber改成2，那么对于下面的SQL来说是依然符合条件的，这是因为 stu.id IN ( SELECT grade.studentId FROMgrade )就相当于 stu.id IN ( 1,3 )，stu.cardnumber IN ( SELECT grade.id FROM grade )就相当于stu.cardnumber IN ( 1,2 )，无论student表中的cardnumber字段如何变化，只要依然是1，2就可以了。
那么行子查询在多行多列的情况下就不同了， 这个直接拿一条数据去模拟吧，比如student第一条数据id=1，cardnumber=2，那么他会同时对比grade中有没有一条数据同时满足studentId=1,id=2的数据，而不是上面的分开满足并构成一个与逻辑。
注：一行多列的行子查询这两种查询方式是可以互换的

7.4 表子查询

表子查询，我个人认为和行子查询很像，他就是查出来一个多行多列的查询结果。不过它的位置是在FROM关键字后面，作为一个虚拟表
应用场景呢，这个我建的表有点太简单了，无法满足这个业务场景，举个例子
查询部门平均工资的工资等级
关于上面这个问题，假设我们有两个表，一个是工资等级表，就好比如阿里的岗位P5、P6、P7一样，等级越高对应的工资肯定越高；另外一个是个人工资表，在这个表中有个字段你是部门ID，部门ID相同的肯定是一个部门的。
基于这两张表得出上面的结果，那么第一步肯定需要拿到部门平均工资，然后才能拿到对应的工资等级。
关于平均工资不是说直接查询就可以查询到的，因为他不是某个字段，他需要进行分组来拿到，那么它就需要作为子查询了。

SELECT ag_dep.*, g.`grade_level`
FROM (
		SELECT AVG(salary) ag, department_id
		FROM employees
		GROUP BY department_id
) ag_dep
LEFT JOIN job_grades g
ON ag_dep BETWEEN lowest_sal AND highest_sal;

这里有一点需要注意，那就是表子查询必须是有别名的。

7.5 exists

这个关键字主要的含义是判断子查询中是否有记录，只要有一条记录，那么都是返回1，否则返回0。
这里的1可以看做是true，这里的0可以看做是false

SELECT EXISTS(SELECT grade.id FROM `grade`); # 1 
SELECT EXISTS(SELECT grade.id FROM `grade` WHERE id=1000); # 0

8 分页查询

所谓的分页查询也就是LIMIT [offset,] size关键字的运用，它是对结果集的一个直接截取，截取范围取决于定义的范围，如LIMIT 1,3，就是从第二条记录开始，数量是三的结果集；LIMIT 0,3这个当然是第一条记录开始，数量是三的结果集，也就是第一、二、三条记录，它和LIMIT 3是等同的。省略offset，默认的起始位置就是第一条。
当然这里我感觉mysql 的起始位置是1，那么它的offset参数是0，其实是从下一条记录开始算起。另外截取的话，肯定是根据已有的结果集的，这个结果集中的顺序可以通过ORDER BY去限定，也可以使用默认的顺序，默认应该是根据主键正序的。
因为实际项目中做得都是分页查询，所以分页的话，肯定是有两个参数，分别是页数和每页的数量，肯定不是一个起始位置一个数量的，这里只需要一个公式就可以了。假定页数是P，每页的数量是N。那么对应的分页查询是LIMIT (P-1)*N N

SELECT * FROM `student` LIMIT 3;

注：如果有类似于WHERE、ORDER BY等，LIMIT是始终处于最后的。

9 联合查询

联合查询的关键字是UNION，他的应用场景是指要查询的结果来自于多个表，但是这些表之间是没有直接的连接关系的，但是查询的结果集字段一致。
它的特点

  1. 要求多条查询语句的查询列表一致；
  2. 要求多条查询语句的查询列表顺序最好一致，否则也是不会报错的，但是没有意义；
  3. union关键字默认是去重的，也就是一般使用DISTINCT关键字后的效果，如果不需要去重效果的话，可以使用UNION ALL去重重复项。
SELECT stu.id id, stu.`name` `name`, stu.sex sex
FROM `student` stu 
UNION
SELECT gra.id, gra.studentId, gra.class
FROM `grade` gra;

这里的字段有些不对应啊，但是不要在意这个逻辑关系，只要关注语法就好了。
查询结果：
系统整理Mysql学习笔记——基础命令DQL

10 DQL总结

DQL基本语法

SELECT 查询列表             # 七
FROM 表                       # 一
[ JOIN TYPE JOIN 表2  		# 二
ON 连接条件                   # 三
WHERE 筛选条件               # 四
GROUP BY 分组条件            # 五
HAVING 分组后的筛选条件       # 六
ORDER BY 排列字段            # 八
LIMIT offset,size;          # 九
UNION ALL SELECT ...            # 十
]

这应该包括了DQL全部的语法了，当然一些细节是没有的。在每个语句的后面标注了顺序，这个顺序其实代表了SELECT语句的查询过程，每执行一个语句可以理解都会生成一个虚拟表，比如FROM执行后，会查询出表中所有的记录，下面对这个过程根据我的理解做个解释：

  1. FROM：查询出表中所有的记录
  2. JOIN TYPE JOIN：查询出多个表的笛卡尔乘积的结果集（这里没有连接条件会报错，但是这里只是一个流程）
  3. on：添加连接条件后，其实就是再做一个筛选，将符合条件的数据留下，这里其实是在做一个结果集的减法，上面两个都是结果集的加法
  4. WHERE：筛选，对当前结果集做进一步的减法
  5. GROUP BY：对一些字段进行分组，其实就是合并，将多条合并成一条，这里也可以看成是一个减法
  6. HAVING：筛选结果集
  7. SELECT：对需要显示的列名做一个限制
  8. ORDER BY：对结果集的顺序进行排列
  9. LIMIT：这个就是一个直接的限制结果集数量了，也是在做一个减法
  10. UNION：将多个查询结果集合并到一个结果集中返回，这个是个加法。

三 总结

首先感谢看到的这里的朋友，这篇文章写的并不完美，有些例子并不恰当，当然就像文中多次提到的那样，这篇文章的主旨是对DQL语法的一个总结概括，对于这点应该还是可以的。后续有时间第三次学习mysql的话，会进行更加系统的理解。
非常抱歉的是，这篇文章应该是不适合初学sql的同学，只适合经常使用sql但是偶尔忘了命令的朋友。
初学mysql的朋友，推荐一个教学视频吧，我也是跟着这个视频进行整理这些命令的，要不然有些命令工作中真的很少用到。

上一篇：mysql分组排序


下一篇：JAVA今日2021.8.5