1 基础查询

1.1 语法

select 查询列表 from 表明;

1.2 特点

1.查询列表中的: 表中的字段,常量值,表达式,函数
2.查询结果是一个虚拟的表格

2 查询分类

2.1 查询表中的单个字段

SELECT last_name FROM employees;

2.2 查询表中的多个字段

SELECT last_name,salary,email FROM employees;

2.3查询表中的所有字段

SELECT
employee_id,
first_name,
…
FROM
employees;

SELECT * FROM employees;

2.4 查询常量值

SELECT 100;
SELECT ‘john’;

2.5 查询表达式

SELECT 100%98;

2.6 查询函数

SELECT VERSION();

2.7 起别名

便于理解
有重名的话,可以使用别名区分开

  • 方式一:

SELECT 100%98 AS result;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

  • 方式二

SELECT last_name 姓,first_name 名 FROM employees;
#案列: 查询salary,显示结果为out put
SELECT salary AS “out put” FROM employees;

2.8去重

案例: 查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;

2.9 +号的作用

  • mysql中的+号,仅仅只有一个功能:运算符
  • select 100+90 ;两个数都是数值型,则做加法运算
  • select ‘123’+90:其中一个是字符型,试图将字符型转换为数值型,如果转换成功,则继续做加法运算
  • select ‘john’+90 如果转换失败,则将字符型数值转换成0
  • select null+10 只要其中一方为null,则结果为null

2.10显示结构

DESC departments;

3 条件查询

3.1 语法

select 查询列表
from 表名
where 筛选条件

3.2 分类

3.2.1 按条件表达式筛选

条件运算符: > < = != <> >= <=
#案例1: 查询工资大于12000的员工信息
SELECT * FROM employees WHERE salary > 12000;
#案例2: 查询部门编号不等于90号的员工名和部门编号
SELECT last_name, department_id
FROM employees
WHERE department_id <> 90;

3.2.2 按逻辑表达式筛选

逻辑运算符:&& || ! and or not
#案例1: 查询工资在10000到20000之间的员工名,工资以及奖金

  1. SELECT last_name,salary,commission_pct
  2. FROM employees
  3. WHERE salary >= 10000 AND salary <= 20000;

案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE
NOT(department_id>=90 AND department_id<=110) OR salary>15000;

3.2.3 模糊查询

关键词: like between and in is null

like

特点:
1.一般和通配符搭配使用
通配符:
%任意多个字符
_任意单个字符

between and

  1. 提供语句的简洁度
  2. 包含临界值
  3. 不能颠倒

  • 案例1: 查询员工名中包含a字符的员工信息

    SELECT  *
FROM employees
WHERE last_name LIKE '%a%';

  • 案例2: 查询员工名中第三个字符为e,第五个字符为a的员工名和工资

    SELECT
  first_name,
  salary
FROM
  employees
WHERE
  first_name LIKE '__e_a%';

案例3: 查询员工名中第二个字符为_的员工名//转义,\转义或者escape转义

SELECT
    last_name
FROM
    employees
WHERE
    #last_name like '_\_%';
    last_name LIKE '_$_%' ESCAPE '$';

between and

案例1: 查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE
#employee_id>=100 and employee_id<=120;
emplpyee_id BETWEEN 100 AND 120;

in

含义:判断某字段的值是否属于in列表中的某一项
特点:
1.使用in提高语句的简洁度
2.in列表的值类型必须一致或者兼容
2.不支持通配符
is null/is not null
#案例: 查询员工的工种编号是 IT_PROG,AD_VP,AD_PRES中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
#job_id = ‘IT_PROG’
#or job_id=’AD_VP’
#or job_id=’AD_PRES’;
job_id IN (‘IT_PROG’,’AD_VP’,’AD_PRES’);

is null

案例1: 查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE #commission_pct is null;
commission_pct IS NOT NULL;

安全等于<=>

既可以判断NULL值,又可以判断数值，
与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。
mysql> select 2<=>3;
+———-+
| 2<=>3 |
+———-+
| 0 |
+———-+

mysql> select null=null;
+—————-+
| null=null |
+—————-+
| NULL |
+—————-+

    <br />mysql> select null<=>null;<br />+-------------+<br />| null<=>null |<br />+-------------+<br />|           1 |<br />+-------------+

案例1: 查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;

案例2: 查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE
salary <=> 12000;

4 排序查询

4.1 语法

select 查询列表
from 表名
where 筛选条件
order by 排序列表 [asc|desc]
asc表示升序,desc表示降序,默认是asc
可以支持单个字段,多个字段,表达式,函数,别名

#案例: 查询员工信息,工资从高到低排序
SELECT
    *
FROM
    employees
#order by salary desc;
ORDER BY salary ASC;

#案例 查询部门编号>=90的员工信息,按入职时间的先后进行排序
SELECT *
FROM
    employees
WHERE
    department_id >= 90
ORDER BY hiredate;

#案例: 按年薪高低排序显示员工信息和年薪按表达式排序
SELECT
    *,salary*12*(IFNULL(commission_pct,0)+1) 年薪
FROM
    employees
ORDER BY
    salary*12*(IFNULL(commission_pct,0)+1) DESC;

#按别名
SELECT
    *,salary*12*(IFNULL(commission_pct,0)+1) 年薪
FROM
    employees
ORDER BY
    salary*12*(IFNULL(commission_pct,0)+1);

#案例: 按姓名的长度排序显示员工的姓名和工资
SELECT
    LENGTH(last_name) 字节长度,
    last_name,
    salary
FROM
    employees
ORDER BY
    LENGTH(last_name) DESC;

#案例: 查询员工信息,要求先按工资排序,再按员工编号排序
SELECT *
FROM employees
ORDER BY salary,employee_id;

5 常见函数

5.1 概念

类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名,隐藏了实现细节,提高代码的重用性

5.2 格式

select 函数名(实参列表)

5.3 分类:

单行函数concat,length,ifnull等

1,字符函数
2.数学函数
3.日期函数
4.其他函数

分组函数:统计函数,聚合函数

6 字符函数

1.length 获取参数值得字节个数

SELECT LENGTH(‘张三丰’);
SHOW VARIABLES LIKE ‘%char%’;

2.concat 拼接字符串

SELECT CONCAT(lastname,’‘,first_name) 姓名 FROM employees;

3.upper,lower

SELECT UPPER(‘john’);
SELECT UPPER(‘joHn’);
#案例 将姓大写,名小写,然后拼接
SELECT CONCAT(UPPER(lastname),’‘,LOWER(first_name)) 姓名
FROM employees;

4.substr,substring 索引从1开始

截取从制度所引出后面的所有字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,7) output;
#截取从指定索引出指定字符长度的字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,1,3) out_put;
#案例: 姓名中首字符大写,其他字符小写.然后用拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(lastname,1,1)),
LOWER(SUBSTR(last_name,2)),’‘,
UPPER(SUBSTR(first_name,1,1)),
LOWER(SUBSTR(first_name,2))) 姓名
FROM employees;

5.instr

返回子串第一次出现的索引,如果找不到就返回0
SELECT INSTR(‘杨不悔爱上了殷六侠’,’殷六侠’) out_put;

6.trim

SELECT LENGTH(TRIM(‘ 张翠山 ‘)) out_put;
SELECT TRIM(‘a’ FROM ‘aaaaaa张aaaa翠山aaaaaa’) out_put;

7.lpad 左填充,

用指定的字符实现左填充指定的长度,从右边截断
SELECT LPAD(‘殷素素’,10,’*’) out_put;

8.rpad 右填充

用指定的字符实现从右边填充指定的成双
SELECT RPAD(‘殷素素’,2,’ab’) out_put;

9.replace 全部替换

SELECT REPLACE(‘张无忌爱上了周芷若’,’周芷若’,’赵敏’) out_put;

数学函数

1.round 四舍五入

SELECT ROUND(1.67);
SELECT ROUND(1.567,2);

2.ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(1.01);

3.floor 向下取整,返回<=该参数的最大整数

SELECT FLOOR(-9.99);

4.truncate 截断

SELECT TRUNCATE(1.6999,1);

5.mod取余

SELECT MOD(10,-3);

日期函数

1.now 当前时间

SELECT NOW();

2.curdate 当前日期

SELECT CURDATE();

3.curtime 返回时间,不包含日期

SELECT CURTIME();

4.获取指定部分,年,月,日,时,分,秒

SELECT YEAR(NOW()) 年;
SELECT YEAR(‘2020_2-2’) 年;
SELECT DISTINCT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

5.str_to_date

将日期格式字符转换为指定格式的日期
SELECT STRTO_DATE(‘2020-2-2’,’%Y-%c-%d’) out_put;
#查询入职日期为1992-4-3的员工日期
SELECT FROM employees WHERE hiredate = ‘1992-4-3’;
SELECT _ FROM employees WHERE hiredate = STR_TO_DATE(‘4-3 1992’,’%c-%d %Y’);

6.date_format 将日期转换为字符

SELECT DATE_FORMAT(NOW(),’%Y年%m月%d日’);
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,’%m月/%d日 %y年’) 日期
FROM employees WHERE commission_pct IS NOT NULL;

其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER( );

流程控制函数

1.if函数

SELECT IF(10>5,10,5);
SELECT last_name,commission_pct,IF(commission_pct IS NULL,0,commission_pct) FROM employees;

2.case

/case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句;
…
else 要显示的值n或语句n;
end/
/使用2
case
when 条件1 when 值或语句
…
else 值或语句
end/

/*案例: 查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1,2倍
部门号=50,显示的工资为2.3倍
其他不变
*/
SELECT salary 原始工资, department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

#案例: 查询员工的工资,如果工资>20000显示A级别.如果工资>15000,显示B级别
#如果公共资源>10000,显示c级别,否则显示d级别
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) 'Dream Salary'  
FROM employees
WHERE salary = 24000;

分组函数

功能

用作统计使用,又称为聚合函数,统计函数或组函数

分类

sum 求和,avg 平均数,max 最大值,min 最小值,count 计数
可以搭配distinct去查查询

  • 简单使用

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高 FROM employees;

  • 参数支持哪些类型

/*
sum,avg一般用于处理数值型
max,min,count可以处理任何类型

  • 分组函数忽略null值

*/
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;

  • 4.和distinct搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

  • 5.count函数的详细介绍

SELECT COUNT(salary) FROM employees;
SELECT COUNT() FROM employees;
SELECT COUNT(1) FROM employees;
效率：
MYISAM存储引擎下 ，COUNT()的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些

分组查询

语法

select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

特点

1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
针对的表 位置 连接的关键字
分组前筛选 原始表 group by前 where

分组后筛选 group by后的结果集 group by后 having
问题1：分组函数做筛选能不能放在where后面
答：不能
问题2：where——group by——having
一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用

分类举例

引入：查询每个部门的员工个数
SELECT COUNT(*) FROM employees WHERE department_id=90;

1.简单的分组

案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
#案例2：查询每个位置的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

2、可以实现分组前的筛选

案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE ‘%a%’
GROUP BY department_id;
#案例2：查询有奖金的每个领导手下员工的平均工资
SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

3、分组后筛选

案例：查询哪个部门的员工个数>5
#①查询每个部门的员工个数
SELECT COUNT(),department_id
FROM employees
GROUP BY department_id;
#② 筛选刚才①结果
SELECT COUNT(),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
manager_id>102
SELECT manager_id,MIN(salary)
FROM employees
GROUP BY manager_id
HAVING MIN(salary)>5000;

4.添加排序

案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;

5.按多个字段分组

案例：查询每个工种每个部门的最低工资,并按最低工资降序
SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

连接查询

含义

又称多表查询,用于查询多个标

笛卡尔乘积现象

表1有m行,表2有n行,查询显示m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类

按年代分

sql92标准:仅仅支持内连接
sql99标准 :支持内连接+外连接(左外,右外)+交叉连接

按功能分

内连接

等值连接
非等值连接
自连接

外连接

左外连接
右外连接
全外连接

sql92标准

1.等值连接

  • 多表等值连接的结果为多表的交集部分
  • n表连接，至少需要n-1个连接条件
  • 多表的顺序没有要求
  • 一般需要为表起别名
  • 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

示例

案例1: 查询员工名对应的部门名
SELECT last_name,department_name
FROM departments,employees
WHERE departments.department_id = employees.department_id;

案例2: 查询员工名,工种号,工种名 为表起别名
SELECT e.last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.job_id = j.job_id;

加筛选

案例1: 查询有奖金的员工名,部门名
SELECT last_name,department_name,commission_pct
FROM departments d,employees e
WHERE d.department_id = e.department_id
AND e.commission_pct IS NOT NULL;

案例2: 查城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id AND city LIKE “_o%”;

加分组

案例1: 查询每个城市的部门个数
SELECT COUNT(),city
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY city
ORDER BY COUNT();

案例2: 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,e.manager_id,MIN(salary)
FROM departments d, employees e
WHERE d.department_id=e.department_id AND commission_pct IS NOT NULL;

加排序

案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT jobtitle,COUNT()
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY jobtitle
ORDER BY COUNT() DESC;

三表连接

案例: 查询员工名.部门名和所在城市
SELECT last_name,department_name,city
FROM employees e, departments d, locations l
WHERE e.department_id= d.department_id AND d.location_id= l.location_id;

2.非等值连接

案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level=’A’;

3.自连接

案例: 查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id manager,m.last_name
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;

sql99语法

语法

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名    
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类

内连接（★）：inner
等值
非等值
自连接
外连接
左外(★):left 【outer】
右外(★)：right 【outer】
全外：full【outer】
交叉连接：cross

内连接

语法

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

特点

①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

1、等值连接

案例1.查询员工名、部门名
SELECT last_name,department_name
FROM departments d
JOIN employees e
ON e.department_id = d.department_id;

案例2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id= j.job_id
WHERE e.last_name LIKE ‘%e%’;

案例3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
#①查询每个城市的部门个数
#②在①结果上筛选满足条件的
SELECT city,COUNT() 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id=l.location_id
GROUP BY city
HAVING COUNT()>3;

案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
#①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name

② 在①结果上筛选员工个数>3的记录，并排序
SELECT COUNT() 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name
HAVING COUNT()>3
ORDER BY COUNT(*) DESC;

5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;

非等值连接

案例1: 查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

案例2: 查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(),grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT()>20
ORDER BY grade_level DESC;

自连接

查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id= m.employee_id;

查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id= m.employee_id
WHERE e.last_name LIKE ‘%k%’;

外连接

应用场景

用于查询一个表中有，另一个表没有的记录
左外(★):left 【outer】
右外(★)：right 【outer】
全外：full【outer】

特点

1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

引入：查询男朋友 不在男神表的
SELECT * FROM beauty; 女神表（boyfriend为男朋友id，关联男神表）
SELECT * FROM boys; 男神表

左外连接
SELECT b. , bo.
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.boyfriend_id = bo.id
WHERE b.id IS NULL;

案例1：查询哪个部门没有员工
#左外
SELECT d.,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;


#右外
SELECT d._,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

全外
USE girls;
SELECT b.,bo.
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;

交叉连接
SELECT b.,bo.
FROM beauty b
CROSS JOIN boys bo;

#sql92和 sql99pk

/功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高/

子查询

含义

出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类

按出现位置

  • select后面: 仅仅支持标量子查询
  • from后面: 支持表子查询
  • where或having后面*(重点)
  • exists后面(相关子查询) :表字查询

按结果集的行列数不同

  • 标量子查询(结果集只有一行一列)
  • 列子查询(结果集只有多行一列)
  • 行子查询(结果集有一行多列)
  • 表字查询(结果集一般为多行多列

where或having后面

1,标量子查询
2.列子查询
3.行子查询

特点

1.子查询放在小括号
2.子查询一般放在条件的右侧
3.标量子查询一般搭配单行操作符使用 < > >= <= <> =
4.列子查询，一般搭配着多行操作符使用in、any/some、all
5.子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1.标量子查询

#案例1：谁的工资比 Abel 高?

#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'

#②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(

    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'

);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141

#②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143

#③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
    SELECT job_id
    FROM employees
    WHERE employee_id = 141
) AND salary>(
    SELECT salary
    FROM employees
    WHERE employee_id = 143

);

#案例3：返回公司工资最少的员工的last_name,job_id和salary

#①查询公司的 最低工资
SELECT MIN(salary)
FROM employees

#②查询last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

#①查询50号部门的最低工资
SELECT  MIN(salary)
FROM employees
WHERE department_id = 50

#②查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

#③ 在②基础上筛选，满足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT  MIN(salary)
    FROM employees
    WHERE department_id = 50
);

2.列子查询

#案例1：返回location_id是1400或1700的部门中的所有员工姓名

#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

#②查询员工姓名，要求部门号是①列表中的某一个

SELECT last_name
FROM employees
WHERE department_id  <>ALL(
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN(1400,1700)
);

#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

#①查询job_id为‘IT_PROG’部门任一工资

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
    SELECT MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

3.行子查询

#案例：查询员工编号最小并且工资最高的员工信息

SELECT * 
FROM employees
WHERE (employee_id,salary)=(
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees

#②查询最高工资
SELECT MAX(salary)
FROM employees

#③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
    SELECT MIN(employee_id)
    FROM employees
)AND salary=(
    SELECT MAX(salary)
    FROM employees
);

select后面

/*
仅仅支持标量子查询
*/

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

 #案例2：查询员工号=102的部门名
SELECT (
    SELECT department_name,e.department_id
    FROM departments d
    INNER JOIN employees e
    ON d.department_id=e.department_id
    WHERE e.employee_id=102
) 部门名;

from后面

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

#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id


SELECT * FROM job_grades;


#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

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

exists后面(相关子查询)

结果是1或者0#案例1：查询有员工的部门名

in
SELECT department_name
FROM departments d
WHERE d.department_id IN(
SELECT department_id
FROM employees
)

exists
SELECT departmentname
FROM departments d
WHERE EXISTS(
SELECT FROM employees e
WHERE d.department_id=e.department_id
);


#案例2：查询没有女朋友的男神信息
#in
SELECT bo.
FROM boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty
)
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id=b.boyfriend_id
);

联合查询

union 联合 : 将多条查询语句结果合并成一个结果
语法
查询语句1
union
查询语句2
union
…
特点

  1. 要求多条查询语句的查询列数是一致的
  2. 要求多条查询语句的查询的每一列的类型和顺序最好一致
  3. union关键字默认是去重的,不去重用union all
1. #引入的案例: 查询部门编号>90或邮箱包含a的员工信息
SELECT *
FROM employees
WHERE department_id>90
OR email LIKE '%a%';

SELECT *
FROM employees
WHERE department_id>90
UNION
SELECT *
FROM employees
WHERE email LIKE '%a%';