MySQL—DQL语言

MySQL—DQL语言

进阶1：基础查询

• 语法：
  SELECT 要查询的东西
  【FROM 表名】;

• 1.查询列表可以是：表中的字段、常量值、表达式、函数

• 2.查询的结果是一个虚拟表格

案例：

#选择myemployees数据库
USE myemployees;
#1.查询表中的单个字段
SELECT employee_id FROM employees;
#2.查询表中多个字段
SELECT employee_id,last_name ,email from employees;
#3.查询表中的所有字段
SELECT * from employees;
#4.查询常量
# select 常量值;
# 注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
SELECT 100;
select 'hello';
#5.查询函数
#select 函数名(实参列表);
SELECT VERSION();
#6.查询表达式 
SELECT 100%3;
#7.起别名
#1.便于理解
#2.如果要查询的字段有重名的情况,使用别名区分
select employee_id as  员工编号 from employees;
#案例:查询salary,结果显示 out put
SELECT salary 'out put' from employees;
#8.去重
SELECT DISTINCT last_name from employees;
/*9.+号的作用
案例:查询员工的名和姓,并显示为姓名
发现运算结果是0
因为MySQL中 + 只能作为运算符
select 100+90; 两个操作数都为数值型,做加法运算
select '123+90';其中一方为字符型,试图将字符型数值转换为数值型
		如果转换成功,则继续做加法运算
select 'john'+90; 如果转换失败,则将字符型数值转换成0

select null+0; 只要其中一方为null,则结果肯定为null.
*/
select last_name+first_name  姓名 from employees;
#10.【补充】concat函数 
#字符串拼接
SELECT CONCAT('a','b','c') AS 拼接结果;
SELECT CONCAT(last_name,first_name) 姓名 from employees;
#11.【补充】ifnull函数
#功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值
SELECT IFNULL(commission_pct,0) 奖金率 from employees;

进阶2：条件查询

​ 条件查询：根据条件过滤原始表的数据，查询到想要的数据
​ 语法：
​ select
​ 要查询的字段|表达式|常量值|函数
​ from
​ 表
​ where
​ 条件 ;

分类：
一、条件表达式
	示例：salary>10000
	条件运算符：
	> < >= <= = != <>

二、逻辑表达式
示例：salary>10000 && salary<20000

逻辑运算符：

	and（&&）:两个条件如果同时成立，结果为true，否则为false
	or(||)：两个条件只要有一个成立，结果为true，否则为false
	not(!)：如果条件成立，则not后为false，否则为true

三、模糊查询
示例：last_name like 'a%'

案例：

#一.按条件表达式筛选
#案例1:查询工资>12000的员工信息
SELECT * from employees where salary>12000;
#案例2:查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id from employees where department_id<>90;
#二、按逻辑表达式筛选
#案例1:查询工资在10000到20000之间的员工名、工资及奖金率
SELECT last_name, salary, commission_pct from employees where salary >= 10000 and salary<=20000;
#案例2:查询部门编号不是在90-110之间,或者工资高于15000的员工信息
SELECT * from employees where department_id< 90 OR department_id >100 OR salary >15000;
#三、模糊查询
#案例1:查询员工名中包含字符a的员工信息
SELECT * from employees where last_name LIKE'%a%';
#案例2:查询员工名中第三个字符为b，第五个字符为a的员工名和工资
SELECT last_name,salary from employees where last_name LIKE'__b_a%';
#案例3:查询员工名中第二个字符为_的员工名
SELECT last_name from employees where last_name LIKE '_\_%';
#2.between and
#案例1:查询员工编号在100到120之间的员工信息
SELECT * from employees WHERE employee_id BETWEEN 100 AND 120;
/*注意事项：
1.提高语句简洁度
2.包含临界值
3.两个临界值不能调换顺序
*/
#3.in
/*
含义:判断某字段的值是否属于in列表中的某一项
特点:
 1.使用in提高语句简洁度
 2.in列表的值类型必须一致或兼容
*/
#案例1:查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT last_name, job_id from employees where job_id in('IT_PROG','AD_VP','AD_PRES');
#4.is null
/*
=或<>不能用于判断null值
is null 或 is not null 可以判断null值
*/
#案例1:查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct from employees where commission_pct is NULL;
#安全等于<=>

#案例1:查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct<=>NULL;
#案例2:查询工资为12000的员工信息
SELECT * from employees where salary<=>12000;
#is null PK <=>
#	      普通类型的数值	null值		可读性
# is null	×		  √		  √
# <=>		√		  √		  ×

进阶3：排序查询

语法：
select
要查询的东西
from
表
where
条件

order by 排序的字段|表达式|函数|别名 【asc|desc】

特点：

• 1.asc代表的是升序，desc代表降序，不写默认为升序
• 2.order by子句中可以支持单个字段、多个字段、表达式、函数、别名
• 3.order by子句一般是放在查询语句的最后面,limit子句除外

案例：

#案例1:查询员工信息,要求工资从高到低排序
SELECT * from employees ORDER BY salary DESC;

#案例2:查询部门编号是>=90，按入职时间的先后进行排序
SELECT * from employees WHERE department_id>=90 ORDER BY hiredate;

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

#案例4:按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT *,salary*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC;

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

#案例6:查询员工信息,要求按工资升序排序，再按员工编号降序排序【按多个字段排序】
select * from employees order BY salary, employee_id desc;

进阶4：常见函数

字符函数

函数名称	作用
concat	拼接（如果是非字符串，进行数值运算； 如果非数值，按0计算）
substr	截取子串，也可以指定位置
upper	转换成大写
lower	转换成小写
trim	去前后指定的空格和字符
ltrim	去左边空格
rtrim	去右边空格
replace	替换
lpad	左填充
rpad	右填充
instr	返回子串第一次出现的索引
length	获取字节个数（中文utf-8默认3个字节；gb2312默认2个字节）

#1.length 获取参数值的字节值
SELECT LENGTH(last_name) 字节值 from employees;
#2.concat 拼接字符串
SELECT CONCAT(last_name,first_name) 姓名 from employees;
#3.upper:变大写、lower：变小写
SELECT upper('abc');
SELECT LOWER('ABC');
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 from employees;
#4.substr、substring
#注意:索引从1开始
#截取从指定所有处后面的所以字符
SELECT SUBSTR('abcdefg',2);
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('abcdefg',2,3);
#案例:姓名中首字符大写,其他字符小写，然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2))) 姓名 from employees;
#5.instr:获取子串第一次出现的索引,找不到返回0
SELECT INSTR('aabbccddee','b') location;
#6.trim:去前后空格
SELECT TRIM('   abc   ');
#7.lpad:用指定的字符实现左填充指定长度
SELECT LPAD('abc',5,'z');
#8.rpad:用指定的字符实现右填充指定长度
SELECT RPAD('abc',5,'d');
#9.replace:替换
SELECT REPLACE('aabbccdd','a','zz');

数学函数

函数名称	作用
round	四舍五入
rand	随机数
floor	向下取整
ceil	向上取整
mod	取余
truncate	截断

#1.round:四舍五入
SELECT ROUND(1.45);
SELECT ROUND(1.567,2);
#2.ceil:向上取整,返回>=该参数的最小整数
SELECT CEIL(1.005);
SELECT CEIL(-1.002);
#3.floor:向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99);
#4.truncate:截断，保留几位小数
SELECT TRUNCATE(1.65,1);
#5.mod:取余
SELECT MOD(10,3);
SELECT MOD(-10,-3);
SELECT MOD(10,-3);
#6.rand:获取随机数，返回0-1之间的小数，如果带参数，则返回值为固定值
SELECT RAND();
select RAND(2)；

日期函数

函数名称	作用
now	返回当前系统时间+日期
year	返回年份
month	返回月份
monthname	返回英文月份
day	返回天
dateiff	返回两个日期相差天数
str_to_date	将字符串按照指定格式转换成日期
date_format	将日期格式转换为字符串
curdate	返回当前日期
curtime	返回当前时间

#1.now:返回当前系统时间+日期
SELECT NOW();
#2.year:返回年
SELECT YEAR(NOW());
SELECT YEAR(hiredate) FROM employees;
SELECT YEAR('2012-02-03');
#3.month:返回月
#MONTHNAME:以英文形式返回月
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT MONTH('2012-02-03');
SELECT MONTHNAME('2012-02-03');
#4.day:返回日
#DATEDIFF:返回两个日期相差的天数
SELECT DAY(NOW());
SELECT DAY('2012-02-03');
SELECT DATEDIFF('2020/07/30','2020/07/21');
#5.str_to_date:将字符通过指定格式转换成日期
SELECT STR_TO_DATE('2020-5-13','%Y-%c-%d');
#6.date_format:将日期转换成字符
SELECT DATE_FORMAT('2020/6/6','%Y年%m月%d日');
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');
#7.curdate:返回当前日期
SELECT CURDATE();
#8.curtime:返回当前时间
SELECT CURTIME();

流程控制函数

if 处理双分支
case语句 处理多分支
情况1：处理等值判断
情况2：处理条件判断

#1.if函数: if else效果
SELECT IF(2>1,'true','false');

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'太可怜了，奖金为0','太开心了，有奖金!') 备注 FROM employees;
#2.case函数
#使用一:switch case 的效果
/*
case 要判断的变量或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

#案例:查询员工的工资,要求:

部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资

*/
SELECT department_id 部门号,salary 工资,
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;
#3.case函数的使用二:类似于多重if
/*
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

*/

#案例:查询员工的工资的情况
/*
如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示c级别
否则，显示D级别
*/
SELECT employee_id 工号, salary 工资,
CASE
WHEN salary>20000 then 'A'
WHEN salary>15000 then 'B'
WHEN salary>10000 then 'C'
ELSE 'D' 
END AS 工资级别
from employees;

其他函数

函数名称	作用
version	返回当前数据库服务器的版本
database	返回当使用的数据库
user	返回当前用户
password	返回字符串的密码形式
md5	返回字符的md5加密形式

#version 当前数据库服务器的版本
SELECT VERSION();
#database 当前打开的数据库
SELECT DATABASE();
#user当前用户
SELECT USER();
#password('字符')：返回该字符的密码形式
SELECT PASSWORD('aaa');
#md5('字符'):返回该字符的md5加密形式
SELECT MD5('bbb');

分组函数

函数名称	作用
sum	求和
max	最大值
min	最小值
avg	平均值
count	计数

分组函数特点：
1、以上五个分组函数都忽略null值，除了count()
2、sum和avg一般用于处理数值型；max、min、count可以处理任何数据类型
3、都可以搭配distinct使用，用于统计去重后的结果
4、count的参数可以支持：字段、常量值，一般放1，例count(1)

建议使用 count(*)

#1.简单使用
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) 最高工资,MIN(salary) 最低工资,COUNT(salary) 有工资的人数
FROM employees;

#2.参数支持哪些数据类型
SELECT SUM(last_name),AVG(last_name) FROM employees;
SELECT SUM(hiredate),AVG(hiredate) FROM employees;

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

SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;

#3.是否忽略null
SELECT SUM(commission_pct),AVG(commission_pct) FROM employees;

SELECT commission_pct FROM employees;

SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,AVG(commission_pct)/107 FROM employees;

SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;

SELECT COUNT(commission_pct) 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(字段)
*/
#6.和分组函数一同查询的字段有限制

SELECT AVG(salary),employee_id FROM employees;

进阶5：分组查询

语法：
​ select 查询的字段，分组函数
​ from 表
​ group by 分组的字段

特点：

• 可以按单个字段分组，也可以按多个字段分组，没有先后顺序
• 和分组函数一同查询的字段最好是分组后的字段
• 分组筛选
  针对的表位置关键字
  分组前筛选：原始表group by的前面—where
  分组后筛选：分组后的结果集group by的后面—having
• 可以按多个字段分组，字段之间用逗号隔开
• 可以支持排序，但是要放在分组后面
• having后可以支持别名

#引入:查询每个部门的平均工资
SELECT department_id 部门号, AVG(salary) 平均工资 from employees GROUP BY department_id;

#案例1:查询每个工种的最高工资
SELECT job_id 工种, MAX(salary)最高工资 FROM employees GROUP BY job_id;

#案例2:查询每个地方的部门个数
SELECT location_id 地方编号, COUNT(*) 部门个数 from departments GROUP BY 地方编号;

#添加筛选条件
#案例1:查询邮箱中包含a字符的，每个部门的平均工资
SELECT department_id 部门号, AVG(salary) 平均工资 FROM employees where email LIKE'%a%' GROUP BY 部门号;

#案例2:查询有奖金的每个领导手下员工的最高工资
SELECT manager_id 领导工号, employee_id 工号, MAX(salary) 最高工资 FROM employees where commission_pct IS NOT NULL GROUP BY 领导工号;

#添加复杂的筛选条件
#案例1:查询哪个部门的员工个数>2
#1.查询每个部门的员工个数
SELECT department_id 部门号, COUNT(*)员工人数 FROM employees GROUP BY 部门号;

#2.根据1的结果进行筛选，查询哪个部门的员工个数大于2
SELECT department_id 部门号, COUNT(*)员工人数 FROM employees GROUP BY 部门号 HAVING 员工人数>2;

#案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资 
#1.查询每个工种有奖金的员工的最高工资
SELECT job_id 工种, employee_id 工号, MAX(salary) 最高工资 from employees WHERE commission_pct IS NOT NULL GROUP BY 工种;

#2.根据结果继续筛选，最高工资>12000 
SELECT job_id 工种, employee_id 工号, MAX(salary) 最高工资 from employees WHERE commission_pct IS NOT NULL GROUP BY 工种 HAVING 最高工资>12000;

#按表达式或函数分组

#案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5

#1.查询每个名字长度的员工个数 
SELECT LENGTH(last_name) 名字长度, COUNT(*) 人数 FROM employees GROUP BY 名字长度;

#2.基于上面结果，筛选员工个数>5
SELECT LENGTH(last_name) 名字长度, COUNT(*) 人数 FROM employees GROUP BY 名字长度 HAVING 人数>5;

#按多个字段查询
#案例:查询每个部门每个工种的员工的平均工资
SELECT department_id 部门号, job_id 工种, employee_id 工号, AVG(salary) 平均工资 from employees GROUP BY 部门号,工种;

#添加排序
#案例:查询每个部门每个工种的员工的平均工资,按平均工资的高低查询

SELECT department_id 部门号, job_id 工种, employee_id 工号, AVG(salary) 平均工资 from employees GROUP BY 部门号,工种 ORDER BY 平均工资 DESC;

进阶6：多表连接查询

笛卡尔乘积：如果连接条件省略或无效则会出现
解决办法：添加上连接条件

• 按年代分类

  • sql92标准:仅仅支持内连接

  • sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

• 按功能分类

  • 内连接： 等值连接 非等值连接 自连接
  • 外连接： 左外连接 右外连接 全外连接
  • 交叉连接

sql92案例：查询员工名和直接上级的名称

#1、等值连接
/*
1.多表等值连接的结果为多表的交集部分
2.n表连接，至少需要n-1个连接条件
3.多表的顺序没有要求
4.一般需要为表起别名
5.可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/
#案例1：查询女神名和对应的男神名
SELECT NAME, boyname FROM boys,beauty WHERE boys.id = beauty.id;

#案例2：查询员工名和对应的部门名
SELECT last_name, department_name FROM employees,departments where employees.department_id = departments.department_id;

#2、为表起别名
/*
1.提高语句的简洁度
2.区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#查询员工名、工种号、工种名
SELECT e.last_name 员工名, e.job_id 工种, j.job_title 工种名称 from employees e, jobs j where e.job_id = j.job_id;

#3、两个表的顺序是否可以调换
#查询员工名、工种号、工种名
SELECT e.last_name 员工名, e.job_id 工种, j.job_title 工种名称 from jobs j, employees e where e.job_id = j.job_id;

#4、可以加筛选
#案例：查询有奖金的员工名、部门名
SELECT e.last_name 姓名, d.department_name 部门名, e.commission_pct 奖金率 from employees e, departments d where e.department_id = d.department_id AND e.commission_pct IS NOT NULL;

#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT d.department_name 部门名, l.city 城市名 from departments d, locations l where d.location_id = l.location_id AND l.city LIKE'_o%';

#5、可以加分组
#案例1：查询每个城市的部门个数
SELECT city 城市名, count(department_id) 部门个数 from departments d, locations l where d.location_id = l.location_id GROUP BY 城市名;

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

#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title 工种名, COUNT(*) 员工人数 FROM employees e, jobs j where e.job_id = j.job_id GROUP BY 工种名 ORDER BY 员工人数 DESC;

#7、可以实现三表连接？
#案例：查询员工名、部门名和所在的城市
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;

#3、自连接
#案例：查询员工名和上级的名称
SELECT e.employee_id, e.last_name, m.employee_id, m.last_name from employees e, employees m where e.manager_id = m.employee_id;

进阶7：子查询

含义：

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询

特点：

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空
② 多行子查询
结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替

#一、where或having后面
/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
*/

#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) 最低工资 from employees GROUP BY department_id;

#③ 在②基础上筛选，满足min(salary)>①
SELECT department_id 部门编号, MIN(salary) 最低工资 from employees GROUP BY department_id HAVING 最低工资 > (SELECT MIN(salary) from employees WHERE department_id = 50);

#非法使用标量子查询

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  salary
	FROM employees
	WHERE department_id = 250
);

#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：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工   的员工号、姓名、job_id 以及salary
SELECT last_name 姓名, employee_id 工号, job_id 工种, salary 工资 from employees WHERE salary < ALL(
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 MIN(salary)  from employees where job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';

#3、行子查询（结果集一行多列或多行多列）

#案例：查询员工编号最小并且工资最高的员工信息
SELECT * from employees WHERE employee_id = (SELECT MIN(employee_id) from employees) and salary = (SELECT MAX(salary) from employees );
#或
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 from departments d INNER JOIN employees e ON d.department_id = e.department_id WHERE employee_id = 102
) 部门名;

#三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/

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

#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*, g.grade_level from(
SELECT AVG(salary) ag FROM employees GROUP BY department_id
) ag_dep
INNER JOIN 
job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

#四、exists后面（相关子查询）
/*
语法：
exists(完整的查询语句)
结果：
1或0
*/

#案例1：查询有员工的部门名
#in
SELECT department_name from departments d WHERE d.department_id in(SELECT department_id from employees);
#exists
SELECT department_name 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);

进阶8：分页查询

应用场景：实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句

语法：

select 字段|表达式,…
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;

特点：

1.起始条目索引从0开始

2.limit子句放在查询语句的最后

3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page

#案例1：查询前五条员工信息
SELECT * FROM  employees LIMIT 0,5;
SELECT * FROM  employees LIMIT 5;

#案例2：查询第11条——第25条
SELECT * FROM employees LIMIT 10,15;

#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10 ;

进阶9：联合查询

引入：
union 联合、合并

语法：

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
…
select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点：

1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致

#引入的案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

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

#案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname,csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id,tName,tGender FROM t_ua WHERE tGender='male';