（MySQL学习笔记）3、DQL数据查询

1、基础查询

使用SELECT语句从表中查询一个或多个数据列

1.1 检索单个字段、多个字段、全部字段

字段==列

检索单个字段：

SELECT 字段名
FROM 表名；

检索多个字段：

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

检索全部字段：

SELECT * 
FROM 表名

注解：

• 若没有明确排序查询结果，则返回的数据顺序无特殊意义，返回的数据可能是数据被添加到表中的顺序，也可能不是。
• 在处理SQL语句时，所有的空格都会被忽略。

1.2 检索字段中不同的行（去重）

使用关键字：DISTINCT

SELECT DISTINCT 字段名1，字段名2...
FROM 表名;

注：不能部分使用DISTINCT

# 使用DISTINCT去重
SELECT DISTINCT job_id,manager_id
FROM employees;
#结果会去除job_id，manager_id都相同的行

1.3 使用LIMIT限制结果

SELECT job_id,manager_id
FROM employees
LIMIT 5;#限制返回结果从行0开始不超过5行

SELECT job_id,manager_id
FROM employees
LIMIT 5,2;#限制返回结果时从行2开始的5行
#等价于
SELECT DISTINCT job_id,manager_id
FROM employees
LIMIT 2 OFFSET 5;

检索出来的第一行为行0，并非行1

LIMIT 1，1：从行1开始取1行 结果是第二行

注意：LIMIT 5,2 与 LIMIT 2 OFFSET 5相同都表示从行5开始取2行

1.4 使用完全限定名（表名、列名）

#使用完全限定名
SELECT employees.job_id
FROM myemployees.employees
# 此时 等价于
SELECT job_id
FROM employees

2、排序：ORDER BY

2.1 通过已选择列排序

SELECT first_name,last_name
FROM employees
ORDER BY first_name;

通过ORDER BY子句取一个或多个列的名字，据此对检索的数据进行排序

2.2 通过非选择列进行排序：

SELECT first_name,last_nameFROM employeesORDER BY salary;

2.3 按多个列排序

SELECT first_name,last_nameFROM employeesORDER BY salary,last_name;

2.4 指定排序方向

升序：ASC(默认方式) 降序：DESC

SELECT first_name,last_name,salaryFROM employeesORDER BY salary DESC,last_name;

2.5 ORDER BY子句的位置

ORDER BY子句应保证在FROM子句后，而LIMIT子句须在ORDER BY子句后

SELECT first_name,last_name,salaryFROM employeesORDER BY salary DESC,last_nameLIMIT 10;

3、条件查询：WHERE

3.1 单一条件查询

3.1.1 检查单个值 ：= 、>、<、>=

SELECT first_name,last_name,salaryFROM employeesWHERE salary >= 12000ORDER BY salary DESC,last_nameLIMIT 10;

3.1.2 不匹配检查：!= 或<>

SELECT * FROM employeesWHERE job_id <> 'SA_MAN'# 等价WHERE job_id != 'SA_MAN'ORDER BY last_name;

注：字符串需要用单引号‘’ 引起来

3.1.3 范围值检查：BETWEEN AND

SELECT *FROM employeesWHERE salary BETWEEN 5000 AND 10000ORDER BY salary;

注：BETWEEN a AND b 包括a、b 即 包含等于号

3.1.4 空值（null）检查 ：IS NULL

SELECT *FROM employeesWHERE commission_pct is NULLORDER BY last_name;# SELECT *FROM employeesWHERE commission_pct is NOT NULLORDER BY last_name;

3.2 组合条件查询

操作符：用来联结或改变WHERE子句中的子句的关键字

3.2.1 AND操作符

SELECT *FROM employeesWHERE commission_pct  IS NULL AND job_id = 'SH_CLERK' AND salary <= 3000 ORDER BY last_name;#等价于SELECT *FROM employeesWHERE commission_pct  IS NULL && job_id = 'SH_CLERK' && salary <= 3000 ORDER BY last_name;

3.2.2 OR操作符

SELECT *FROM employeesWHERE  job_id = 'SH_CLERK' OR salary <= 3000 ORDER BY last_name;#等价于SELECT *FROM employeesWHERE  job_id = 'SH_CLERK' || salary <= 3000 ORDER BY last_name;

3.2.3 AND与OR计算次序

默认计算次序：AND > OR

圆括号（）：通常在包含AND和OR的语句中使用圆括号() 进行明确的分组操作，以此来消除歧义

3.2.4 IN操作符

IN操作符：用来指定条件范围，范围中的每个条件都可以进行匹配

SELECT *FROM employeesWHERE manager_id IN (121,122)ORDER BY manager_id,salary;

注：IN操作符完成与OR操作符完全相同的功能

使用IN操作符的优点：

• 在使用长的合法选项清单时，IN操作符的语法更清楚、直观
• IN操作符执行速度更快
• 最大优点：可以包含其他SELECT语句，使得能够更加动态的建立WHERE语句（使用自子查询相关语句）

3.2.5 NOT操作符

NOT操作符：：WHERE子句中使用，作用为否定它之后所跟的任何条件。

通常搭配 ：NOT IN 、NOT BETWEEN

SELECT *FROM employeesWHERE manager_id NOT IN (121,122)ORDER BY manager_id,salary;SELECT *FROM employeesWHERE manager_id NOT BETWEEN 121 AND 123ORDER BY manager_id,salary;

4、模糊查询(通配符过滤)

4.1 使用通配符过滤（WHERE子句中）

通配符（wildcard）：用来匹配 值的一部分 的特殊字符

是WHERE子句中有特殊含义的字符

4.1.1 LIKE操作符

搜索模式：由字面值、通配符或两者组合而成的搜索条件

LIKE操作符是与通配符进行搭配使用的，

LIKE指示MySQL，后面跟的搜索模式是利用通配符匹配的，而不是直接相等匹配进行比较

4.1.2 百分号（%）通配符

%：表示任何字符出现的任意次数（包括0次）

SELECT first_name,last_nameFROM employeesWHERE last_name LIKE '%ar%'ORDER BY last_name;

注：%告诉MySQL接受ar之前和之后的任意长度的字符

%通配符不能匹配NULL 即使使用LIKE ‘%’ 也无法匹配

4.1.3 下划线（_)通配符

_：下划线通配符只能匹配单个字符

SELECT first_name,last_nameFROM employeesWHERE last_name LIKE '_ab%'ORDER BY last_name;

4.1.4 通配符使用技巧

通配符搜索的处理一般比其他搜索所花费的时间更长

使用通配符要记住：

• 不要过度使用
• 除非绝对需要，否则不要将通配符放在搜索模式的开始，这样会花费极多的时间

4.2 使用正则表达式（WHERE子句中）

通配符过滤数据只适用于基本的过滤，正则表达式是为复杂搜索指定搜索模式的强大方式，

正则表达式(Regexp):用来匹配文本的特殊的串(字符集合)

4.2.1 MySQL正则表达式简介

4.2.1.1 正则表达式功能

• 提供了强大而灵活的匹配模式，可以帮助我们为数据库系统实现强大的搜索实用程序。
• REGEXP是执行正则表达式模式匹配时使用的运算符
• 支持许多元字符，这些元字符在执行模式匹配时可以提供更大的灵活性和控制。
• 反斜杠用作转义字符。如果使用了双反斜杠，则仅在模式匹配中考虑。
• 不区分大小写。

4.2.1.2 正则模式表

模式 描述（具体匹配什么）
^ 匹配字符串的开始位置。
$ 匹配字符串的结束位置。
. 匹配除 “\n” 之外的任何单个字符。
[…] 匹配所包含的任意一个字符。
[^…] 匹配未包含的任意字符。
? 匹配它前面的零个或一个子表达式（字符串）。
* 匹配它前面的零个或多个子表达式（字符串）。
+ 匹配它前面的一个或多个子表达式（字符串）。
[abc] 匹配方括号之间列出的任何字符。
[^abc] 匹配方括号之间未列出的任何字符。
[A-Z] 匹配任何大写字母。
[a-z] 匹配任何小写字母。
[0-9] 匹配从0到9的任何数字。
[[:<:]] 匹配单词的开头。
[[:>:]] 匹配单词的结尾。
[:class:] 匹配字符类，即[：alpha：]匹配字母，[：space：]匹配空格，[：punct：]匹配标点符号，[：upper：] 匹配上层字母。
p1|p2|p3 匹配任何模式，p1或 p2或p3
{n} 匹配n前面元素的子表达式
{m,n} 匹配m到前面元素的n个子表达式

4.2.2 基本字符匹配

SELECT first_name,last_nameFROM employeesWHERE last_name REGEXP 'ab'ORDER BY last_name;#等价于SELECT first_name,last_nameFROM employeesWHERE last_name LIKE '%ab%'ORDER BY last_name;

说明: 搜索last_name字段中所有包含字串‘ab’的数据

SELECT first_name,last_nameFROM employeesWHERE last_name REGEXP '.ab'ORDER BY last_name;#等价于SELECT first_name,last_nameFROM employeesWHERE last_name LIKE '%_ab%'ORDER BY last_name;

.(点)：正则表达式中表示 匹配匹配除 “\n” 之外的任何单个字符。，与通配符_ 功能相同

4.2.3 OR匹配

SELECT first_name, last_nameFROM employeesWHERE last_name REGEXP 'ab|ob'ORDER BY last_name;#等价于SELECT first_name, last_nameFROM employeesWHERE last_name REGEXP '[ao]b'ORDER BY last_name#等价于SELECT first_name, last_nameFROM employeesbWHERE last_name LIKE '%ab%' OR last_name LIKE '%ob%'ORDER BY last_name;

说明：表示匹配last_name中包含子串‘ab’或‘ob’的数据

|：为正则表达式的OR操作符

[…] 匹配所包含的任意一个字符

[^…] 匹配未包含的任意字符。

4.2.4 范围匹配

[A-Z] 匹配任何大写字母。
[a-z] 匹配任何小写字母。
[0-9] 匹配从0到9的任何数字。
[:alnum:] 匹配任意字母和数字，同[a-zA-Z0-9]
[:alpha:]:匹配任意字符，同[a-zA-Z]
[:digit:]:匹配任意数字，同[0-9]
[:lower:]:匹配任意小写字母，同[a-z]
[:upper:]:匹配任意大写字母，同[A-Z]a
[:space:]:匹配任意大写字母，同[\f\n\r\t\v]

4.2.5 定位符

模式 描述（具体匹配什么）
^ 匹配字符串的开始位置。
$ 匹配字符串的结束位置。
[[:<:]] 匹配单词的开头。
[[:>:]] 匹配单词的结尾。

5、使用别名、算术计算

5.1 使用别名

AS关键字：创建别名（AS可省略）

SELECT     employee_id,    last_name,    salary,    salary * 12 * (1 + ifnull(commission_pct,0)) AS 年薪FROM    employees

ifnull(commission_pct,0)：判断commission_pct是否为null，若为null则更改为0

#省略ASSELECT 100 % 98 结果;#别名中含空格或关键字时，用双引号引起来SELECT 100 % 98 "out put";

6、SQL内置函数

6.1 文本处理函数

常用文本处理函数

函数	说明
Length()	返回串的长度
Locate()	找出串的一个字串
Replace()	替换指定字符
Lower()	将串转换为小写
Upper()	将串转换为大写
Trim()	去前后空格,或其他(重载方法)
LTrim()	去掉串左边的空格
RTrim()	去掉串右边的空格
concat()	拼接字符串
substr()	截取子串
instr()	返回字串第一次出现的索引
LPad()	左填充（指定字符填充指定总长度）
RPad()	右填充（指定字符填充指定总长度）

6.1.1 length()

#length:获取参数值的字节数SELECT length('beautiful') AS 串长;#9SELECT length('周星驰666') AS 串长;#12/*	注：utf-8编码：一个汉字：3个字节		gbk编码：一个汉字：2个字节*/

6.1.2 concat()

#将 姓与名 进行连接SELECT employee_id,concat(last_name,'_',first_name) AS nameFROM employees;

6.1.3 substr() 、instr()

SELECT substr('阿珍爱上了阿强',6);#阿强SELECT substr('阿珍爱上了阿强',1,2);#阿珍#sql语言中,索引从1开始

SELECT instr('阿珍爱上了阿强','阿强');#6#instr():返回字串第一次出现的索引，若找不到，返回0

6.1.4 trim()、ltrim()、rtrim()

SELECT trim('         马化腾   周润发        ')  out_put;#马化腾   周润发#SELECT length(trim('         马化腾   周润发        ')) out_put;#21#SELECT length(ltrim('         马化腾   周润发        ')) out_put1;SELECT length(rtrim('         马化腾   周润发        ')) out_put2;#SELECT trim('a' FROM 'aaaaaa马化腾aaa周润发aaaaa') out_put;#马化腾aaa周润发

6.1.5 lpad()、rpad()

SELECT lpad('马化腾',10,'*') AS out_out;#*******马化腾SELECT rpad('马化腾',10,'*$') AS out_out;#马化腾*$*$*$*

6.1.6 replace()

SELECT replace('功夫再高,也怕菜刀、菜刀菜刀','菜刀','大炮') AS out_put;#功夫再高,也怕大炮、大炮大炮

6.1.7 locate()

SELECT locate('无敌','无敌是多么寂寞') AS out_put;#1#找到返回1，找不到返回0

6.2 聚集函数

聚集函数：运行在行组上，计算和返回单个值的函数，5种

​ 理解：仅进行数据的汇总，而不用实际进行检索，重点是要汇总信息，不需要返回实际表数据

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列的总和

以上聚合函数都忽略null值

6.2.1 avg()

#求工资平均值，小数点后保留两位SELECT round(avg(salary),2)FROM employees;

avg()：仅使用于单个列，且忽略列值为null的行

6.2.2 count()

#第一种SELECT count(*) FROM employees;#第二种：忽略null值SELECT count(commission_pct) FROM employees;#与DISTINCT搭配SELECT     COUNT(DISTINCT salary), COUNT(salary)FROM    employees;

6.2.3 max()、min()、sum()

SELECT max(salary)FROM employees;SELECT min(salary)FROM employees;SELECT sum(salary)FROM employees;

6.3 数学函数

函数	说明
Round()	四舍五入
Ceil()	向上取整
Floor()	向下取整
Truncate()	截断

6.3.1 round()

SELECT round('1.66');#2SELECT round('1.567',2);#1.57

6.3.2 ceil()、floor()

SELECT ceil(1.002);#2SELECT ceil(-1.002);#-1#SELECT floor(1.002);#1SELECT floor(-1.002);#-2

6.3.3 truncate()

SELECT truncate(1.88,1);#1.8SELECT truncate(-1.88,1);#-1.8

6.3.4 mod()

SELECT mod(10,3);#等价于SELECT 10%3;

6.4 日期函数

日期和时间在sql中采用相应的数据类型和特定的格式存储

函数	说明
Now()	返回当前日期和时间
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间中的日期
Time()	返回日期时间中的时间
DayOfWeek()	对应一个日期，返回对应是星期几
DateDiff()	计算两个日期之差
Date_Format()	返回一个格式化的日期或时间串

6.4.1 now()、curdate()、curtime()

SELECT now();#2021-05-26 11:54:25SELECT curdate();#2021-05-26SELECT curtime();#11:54:26

6.4.2 date()、time()

SELECT date('1998-1-1 10:10:10') AS out_put;#1998-01-01SELECT time('1998-1-1 10:10:10') AS out_put;#10:10:10SELECT date(hiredate)FROM employees;

6.4.3 datediff()

SELECT datediff(curdate(),'1998-1-1') AS out_put;#默认返回天数

6.5 数值处理函数

数值处理函数 仅用来处理数值数据

函数	说明
Abs()	返回一个数的绝对值
Exp()	返回一个数的指数
Mod()	取模
Pi()	返回圆周率
Rand()	返回一个随机数
Sqrt()	返回一个数的平方根

6.6 其他函数

函数	说明
version()	返回当前数据库版本
database()	返回当前数据库名
user()	返回当前用户名

6.7 流程控制函数

函数	说明
if()	类似于三元运算符，实现双分支
case end	实现多分支

6.7.1 if()

SELECT IF(10 > 5, '是', '否');#是SELECT last_name,commission_pct,if(commission_pct IS NULL,'没奖金','有奖金') AS 备注FROM employees;

6.7.2 case end

SELECT salary 原始工资,department_id,#用法一CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2ELSE salaryEND AS 新工资FROM employees;#用法二SELECT     last_name,    salary,    CASE        WHEN salary > 30000 THEN 'A'        WHEN salary > 20000 THEN 'B'        WHEN salary > 10000 THEN 'C'        ELSE 'D'    END AS 工资等级FROM    employees;

7、分组查询 GROUP BY

7.1 数据分组

#查询每个部门的员工个数SELECT     department_id, COUNT(*)FROM    employeesGROUP BY department_id;#查询每个部门的平均工资（保留两位）SELECT     last_name, ROUND(AVG(salary), 2) 平均工资FROM    employeesGROUP BY department_id;#查询每个工种的最高工资SELECT	job_id,MAX(salary) 最高工资FROM	employeesGROUP BY job_idORDER BY 最高工资;#查看邮箱中包含a字符的每个部门的平均工资SELECT     email, department_id, ROUND(AVG(salary),2) AS 平均工资FROM    employeesWHERE    email LIKE '%a%'GROUP BY department_idORDER BY 平均工资;#查询每个领导手下有奖金的员工的最高工资SELECT     manager_id, MAX(salary)FROM    employeesWHERE    commission_pct IS NOT NULLGROUP BY manager_id;

7.2 过滤分组 HAVING

WHERE VS HAVING

• where 用于分组前筛选，用于过滤行

• having用于分组后筛选，用于过滤分组

  注意：where过滤的行不包含在分组中

7.2.1 按单字段过滤分组

#查询员工个数>2的部门编号SELECT     department_id, COUNT(*)FROM    employeesGROUP BY department_idHAVING COUNT(*) > 2;#查询每个工种中 有奖金且奖金>12000的员工 的工种编号和最高工资SELECT     job_id, MAX(salary)FROM    employeesWHERE    commission_pct IS NOT NULLGROUP BY job_idHAVING MAX(salary) > 12000;/*	筛选条件	①是否有奖金 可直接在employees表中查询，在分组之前查询筛选	②最高工资>12000 必须在分组后才能进行筛选*/#查询领导编号>102的每个领导手下的员工（且工资>5000）个数及领导编号、最低工资SELECT     manager_id, COUNT(*), MIN(salary)FROM    employeesWHERE    manager_id > 102GROUP BY manager_idHAVING MIN(salary) > 5000;

7.2.2 按多字段过滤分组

#查询同一部门(部门不能为null）同一工种的平均工资(保留两位，且>10000））,并降序排列SELECT     department_id, job_id, ROUND(AVG(salary), 2) AS avg_salaryFROM    employeesWHERE    department_id IS NOT NULLGROUP BY department_id , job_idHAVING avg_salary > 10000ORDER BY avg_salary DESC;

8、联结查询(多表查询)

8.1 笛卡尔乘积现象

现象: 表1 n行,表2 m行 查询结果 m*n 行

#出现笛卡尔乘积现象(sql92)SELECT     beauty.name, boys.boyNameFROM    beauty,boys;#解决:添加有效的联结条件SELECT     beauty.name, boys.boyNameFROM    beauty,    boysWHERE    beauty.boyfriend_id = boys.id;        

8.2 内联结(sql92)

应用场景: 查询出的结果 是两个表的交集部分

8.2.1 等值联结(sql92)

#查询员工名和对应的部门名SELECT     last_name, department_nameFROM    departments,    employeesWHERE    departments.department_id = employees.department_id;#起别名#查询员工名,工种号,工种名   SELECT     e.last_name, e.job_id, j.job_titleFROM    employees e, # 建议为表起别名    jobs jWHERE    e.job_id = j.job_id;#加筛选#查询有奖金的员工名,部门名SELECT     last_name, department_name,commission_pctFROM    employees e,    departments dWHERE    e.department_id = d.department_id        AND commission_pct IS NOT NULL;

注:若为表 起了别名,则查询字段就不可以用原来的表名来限定

#加分组#查询每个城市的部门个数SELECT     city, COUNT(*) 个数FROM    locations l,    departments dWHERE    l.location_id = d.location_idGROUP BY city;#加排序#查询每个工种的工种名和员工个数，最低工资，并按最低工资升序SELECT     job_title, COUNT(*), MIN(salary)FROM    employees e,    jobs jWHERE    e.job_id = j.job_idGROUP BY job_titleORDER BY MIN(salary);

8.2.2 非等值联结(sql92)

#查询员工的姓名、工资和工资级别SELECT     last_name, salary, grade_levelFROM    employees e,    job_grades jWHERE    salary BETWEEN lowest_sal AND highest_salORDER BY grade_level,salary;#查询员工的姓名、工资和工资级别,只查看工资级别为A的SELECT     last_name, salary, grade_levelFROM    employees e,    job_grades jWHERE    salary BETWEEN lowest_sal AND highest_sal        AND grade_level = 'A'ORDER BY salary;

8.2.3 自联结(sql92)

对同一张表 使用两次别名

#查询员工名,及其领导的名称SELECT     e.last_name, e.manager_id, m.employee_id, m.last_nameFROM    employees e,    employees mWHERE    e.manager_id = m.employee_id;

8.3 内联结(sql99) INNER

基本语法

select from 表1 [联结类型] join 表2on 联结条件where

8.3.1 等值联结(sql99)

#查询员工名和对应的部门名SELECT     last_name, department_nameFROM    employees e        INNER JOIN    departments d ON e.department_id = d.department_id;#加筛选#查询有奖金的员工名,部门名SELECT     last_name, department_name, commission_pctFROM    employees e        INNER JOIN    departments d ON e.department_id = d.department_idWHERE    commission_pct IS NOT NULL;

8.3.2 非等值联结(sql99)

#查询员工的姓名、工资和工资级别,只查看工资级别为A的SELECT last_name,salary,grade_levelFROM employees eINNER JOIN job_grades jgON salary BETWEEN lowest_sal AND highest_salWHERE grade_level ='A'ORDER BY last_name;

8.3.3 自联结(sql99)

#查询员工名,及其领导的名称SELECT     e.last_name 员工, e.manager_id, m.employee_id, m.last_name 上司FROM    employees e        INNER JOIN    employees m ON e.manager_id = m.employee_idORDER BY 上司;

8.4 外联结 OUTER

查询一个表中有,另一个表中没有的记录

特点:

• ​ 外联结的查询结果为主表中的所有记录
  • 若从表中有和其匹配的记录,则显示匹配的记录
  • 若从表中没有和其匹配的记录,则显示null
• 左外联结 :LEFT JOIN关键字左边的表为 主表
• 右外连接:RIGHT JOIN关键字右边的表为主表
• 左外联结与右外联结 交换俩表的位置,可实现同样的效果

OUTER关键字可省略

8.4.1 左外联结 LEFT OUTER

#查询男朋友不在男神表中的 女神名SELECT     nameFROM    beauty b        LEFT OUTER JOIN    boys bo ON b.boyfriend_id = bo.idWHERE    bo.id IS NULL;    #查询那个部门没有员工SELECT     d.department_id, d.department_nameFROM    departments d        LEFT OUTER JOIN    employees e ON e.department_id = d.department_idWHERE    e.department_id IS NULL;

8.4.2 右外联结 RIGHT OUTER

SELECT     b.nameFROM    boys bo        RIGHT OUTER JOIN    beauty b ON b.boyfriend_id = bo.idWHERE    bo.id IS NULL;

8.4.3 全外联结 FULL OUTER(MySQL不支持)

与内联结结果为两个表的交集不同,全外联结的结果为两个表的并集

相当于 内联结+左外+右外

8.5 交叉连接 CROSS

使用sql99语法标准来实现的笛卡尔乘积

9、子查询

9.1 定义与特点

定义：出现在其他sql语句中的select语句，又称 内查询

包含子查询的外部语句 称之为 外查询或主查询

特点：子查询允许把一个查询嵌套在另一个查询当中

9.2 分类

9.2.1 按结果集的行列数分类

• 标量子查询：结果集只有 一行一列
• 列子查询：结果集为 多行一列
• 行子查询：结果集为 一行多列
• 表子查询：结果集为 多行多列

9.2.2 按子查询出现的位置

• select后
  • 仅支持标量子查询
• from后
  • 支持 表子查询
• where或having后
  • 支持 标量子查询、列子查询、行子查询（极少使用）
• exists后（相关子查询）
  • 支持 表子查询

9.3 where或having后

9.3.1 特点

1. 子查询均放在小括号中
2. 子查询一般放在条件的右侧
3. 标量子查询一般搭配单行操作符（> < = >= …)使用
4. 列子查询一般搭配多行操作符（IN ANY/SOME ALL)
5. 子查询的执行一般都优先于主查询

9.3.2 标量子查询（单行子查询）

举例：

#谁的工资比 Abel 高SELECT     last_name, salaryFROM    employeesWHERE    salary > (SELECT             salary        FROM            employees        WHERE            last_name = 'Abel')ORDER BY salary;#返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资SELECT     last_name, job_id, salaryFROM    employeesWHERE    job_id = (SELECT             job_id        FROM            employees        WHERE            employee_id = 141)        AND salary > (SELECT             salary        FROM            employees        WHERE            employee_id = 143);#返回公司工资最少的员工的last_name,job_id和salarySELECT     last_name, job_id, salaryFROM    employeesWHERE    salary = (SELECT             MIN(salary)        FROM            employees);#加分组#查询最低工资大于50号部门最低工资的部门id和其最低工资SELECT     employee_id, MIN(salary)FROM    employeesGROUP BY employee_idHAVING MIN(salary) > (SELECT         MIN(salary)    FROM        employees    GROUP BY department_id    HAVING department_id = 50)ORDER BY MIN(salary);

9.3.3 列子查询（多行子查询）

9.3.3.1 特点

• 返回多行一列
• 使用多行比较操作符

9.3.3.2 多行比较操作符

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

9.3.3.3 举例

#返回location_id 为1400 或 1700 的部门中的所有员工姓名#方式一 内联结SELECT     last_name, location_idFROM    employees e        INNER JOIN    departments d ON e.department_id = d.department_idWHERE    location_id IN (1400 , 1700);#方式二： 列子查询SELECT     last_nameFROM    employeesWHERE    department_id IN (SELECT DISTINCT            department_id        FROM            departments        WHERE            location_id IN (1400 , 1700));

9.3.4 行子查询（很少使用）

#查询员工编号最小且工资最高的员工信息#方式一SELECT     *FROM    employeesWHERE    employee_id = (SELECT             MIN(employee_id)        FROM            employees)        AND salary = (SELECT             MAX(salary)        FROM            employees)#方式二SELECT     *FROM    employeesWHERE    (employee_id , salary) = (SELECT             MIN(employee_id), MAX(salary)        FROM            employees)

9.4 select后

举例

#查询每个部门的员工个数SELECT     d.*,    (SELECT             COUNT(*)        FROM            employees e        WHERE            d.department_id = e.department_id) 员工个数FROM    departments d	

9.5 from后

将子查询结果充当一张表，要求子表必须起别名

举例

#查询每个部门的平均工资的工资等级SELECT     avg_dep.*, j.grade_levelFROM    (SELECT         department_id, ROUND(AVG(salary), 2) ag    FROM        employees e    GROUP BY department_id) avg_dep        INNER JOIN    job_grades j ON avg_dep.ag BETWEEN j.lowest_sal AND j.highest_salORDER BY grade_level;

9.6 exists后（相关子查询）

特点

1.只关心子查询的结果是否存在,存在返回1,不存在返回0

2.相关子查询的结果是布尔类型

#简单举例SELECT EXISTS(SELECT department_id FROM employees) 结果;SELECT EXISTS(SELECT department_id FROM employees WHERE salary =300000) 结果;#查询有员工的部门名 SELECT     department_nameFROM    departments dWHERE    EXISTS( SELECT             last_name        FROM            employees e        WHERE            e.department_id = d.department_id);

10、联合查询 UNION

10.1 举例

使用union关键字将多条查询语句的结果合并成一个结果

#查询部门编号>90或邮箱包含'ab'的员工信息SELECT *FROM employeesWHERE department_id > 90 OR email LIKE '%ab%';#使用unionSELECT     *FROM    employeesWHERE    department_id > 90 UNION SELECT     *FROM    employeesWHERE    email LIKE '%ab%'

10.2 UNION规则

1. UNION必须有两条或两条以上的SELECT语句组成2. UNION中的每个查询必须包含相同的列、表达式或聚集函数3. 列数据类型必须兼容

10.3 特点

优点：极大简化复杂WHERE子句

1. UNION会从查询结果集中自动去除重复的行，UNION ALL 不去重
2. 对组合查询结果进行排序时，只能使用一条ORDER BY子句，且必须出现在最后一条SELECT子句后

注：本文章根据尚硅谷李玉婷老师的b站课程整理所得