Mysql查询总结（DQL）

1.基础查询（使用myemployees.sql）

1.1进阶一 基础查询

# select 查询列表 from 表名
USE myemployees;

#1.查询表中的单个字段
SELECT last_name FROM employees;

#2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;

#3.查询表中所有字段（双击左侧）
#方式一
SELECT 
  `department_id`,
  `department_name`,
  `manager_id`,
  `location_id` 
 FROM 
  employees;
#方式二
SELECT * 
FROM employees ;

#4.查询常量值
SELECT 100;
SELECT 'john';
#（不区分字符和字符串）

#5.查询表达式
SELECT 100%98;

#6.查询函数
SELECT VERSION();

#7.起别名
/*
①便于理解
②如果要查询的字段有重名的情况，使用别名可以区分开来
*/
#方式一:用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 
FROM employees;

#方式二:空格
SELECT last_name 姓，first_name 名 
FROM employees;
#案例：查询salary，显示结果为output
SELECT salary AS "out put" 
FROM employees;


#8.去重
#案例：查询员工表涉及到的所有部门编号
SELECT DISTINCT department_id 
FROM employees;


#9. +号的作用
/*
java中的+号：
①运算符：两个操作数都为数值型
②连接符：只要有一个操作数为字符串

mysql中的+号：
仅仅只有一个功能：运算符

select 100+90；两个操作数都为数值型，则做加法运算
select '123'+90; 其中一方为字符型，会试图将字符型数值转换成数值型
		                  如果转换成功则继续加法运算
select 'john'+90; 如果转换失败，则字符型数组值转换成0；
select null+10;只要其中一方为null，则结果为null
*/

#案例：查询员工名和姓连接成一个字段，并显示为 姓名
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

#判断结果是否为null，为null显示为0
SELECT IFNULL(commission_pct,0)AS 奖金率,commission_pct
FROM employees

1.2进阶二 条件查询

/*
语法：
select 查询列表
from 表名
where 筛选条件；
分类：
一、按照条件表达式筛选
条件运算符：< > = != <> >= <=
二、按照逻辑表达式筛选
逻辑运算符：用于连接条件表达式
&& || ！，推荐使用 and or not
三、模糊查询
like
between and
in
isnull
*/

#一、按照条件表达式筛选
#案例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

#案例二：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT *
FROM employees
WHERE department_id NOT BETWEEN 90 AND 110 OR salary>15000

三、模糊查询
/*
like
特点：
①一般和通配符搭配使用
通配符：
% 任意多个字符，包含0个字符
_ 任意单个字符
between and
特点：
①可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
in
含义：判断某字段的值是否属于in列表中的某一项
特点：
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③不支持通配符
isnull
特点：
①=或<>不能用于判断null值，is null或 is not null 可以判断null值
*/

#1.like
#案例1：查询员工名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%';   #字符要用单引号引起来

#案例2：查询员工名中第三个字符为n，第5个字符为l的员工名和工资
SELECT last_name,salary
FROM employees
WHERE last_name LIKE '__n_l%';

#案例3：查询员工名中第二个字符为_的员工名
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_%';#使用转义字符\

SELECT last_name
FROM employees
WHERE last_name LIKE '_$_%'ESCAPE'$';#随意指定转义字符，使用escape关键字，推荐使用这种

#2.between and
#案例1：查询员工编号在100到120之间的员工信息
SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 120

#3.in
#案例：查询员工的工种编号是 IT_PROT、AD_VP、AD_PRES中的一个的员工名和工种编号
SELECT last_name,job_id
FROM employees
WHERE job_id='IT_PROT' OR job_id='AD_VP' OR job_id='AD_PRES';

SELECT last_name,job_id
FROM employees
WHERE job_id in ('IT_PROT','AD_VP','AD_PRES');

#4.is null
#案例1：查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct
FROM employees
where commission_pct is NULL;#这里不能用=号代替

#补充知识：安全等于 <=>，可以判断null值和普通类型的值
SELECT last_name,commission_pct
FROM employees
where commission_pct <=> null;

SELECT *
FROM employees
where salary <=> 12000;

#is null pk <=>
is null：仅仅可以判断null值，可读性较高，建议使用
<=>：既可以判断null值，又可以判断普通的数值，可读性较低

1.3进阶三 排序查询

/*
语法：
select 查询列表
from 表
[where 筛选条件]
order by 排序列表[ asc|desc ]
特点：
①asc代表的是升序，desc代表的是降序，如果不写，默认是升序
②order by子句中可以支持单个字段、多个字段、表达式、函数、别名
③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*12*(1+IFNULL(commission_pct,0))AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

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

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

#案例6：查询员工信息，要求先按工资升序，工资一样再按员工编号降序【按多个字段排序】
SELECT *
FROM employees
ORDER BY salary ASC,department_id DESC;

1.4进阶四 常见函数

/*
功能：类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：①隐藏了实现细节
②提高代码的重用性
调用：select 函数名（实参列表）
【from 表】
特点：①叫什么（函数名）
②干什么（函数功能）
分类：
①单行函数
如：concat、length、ifnull等
②分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
常见函数总结：
字符函数：length、concat、sustr、instr、trim、upper、lower、lpad、rpad、replace
数学函数：round、ceil、floor、truncate、mod
日期函数：now、curdate、curtime、year、month、monthname、day、hour、minute、second、str_to_date、date_format
其他函数：version、database、user
控制函数：if、case
*/

#一、字符函数
#1.length  获取参数值的字节个数 
SELECT LENGTH('join')
SELECT LENGTH('张三丰hahah')#一个字母占一个字节、一个汉字占三个字节（字符集为utf9）
show VARIABLES LIKE'%char%' #查看字符集

2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) as 姓名
from employees

3.upper、lower
SELECT UPPER('join')
SELECT LOWER('join')
#示例：将姓变大写、名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name))AS 姓名
FROM employees

4.substr、substring 截取
注意：索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元',7)AS out_put# 结果为陆展元----截取从索引7开始后面所有的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3)AS out_put#结果为李莫愁----截取从1开始长度为3的字符

#案例：姓名中首字符大写，其他字符小写，然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS out_put
FROM employees

5.instr
返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put

6.trim
去掉前后空格
SELECT LENGTH(TRIM('     张翠山   '))AS out_put
SELECT TRIM('a'  FROM 'aaaaa张aaaa翠山aaaaa')as out_put#去掉前后的a，中间的a去不掉

7.lpad
用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',10,'*')AS out_put

8.rpad
用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'@')AS out_put

9.replace 替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏')AS out_put

#二、数学函数
#round 四舍五入
SELECT ROUND(1.65);------->2
SELECT ROUND(-1.653,2);------>1.65，小数点后保留两位

#ceil 向上取整，返回>=该参数的最小整数
SELECT CEIL(1.52);------>2
SELECT CEIL(-1.02);------->1

#floor 向下取整，返回<=该参数的最大整数
SELECT floor(-9.99);-------> -10

#truncate 截断
SELECT TRUNCATE(1.65,1);-------->1.6，小数点后截断一位

#mod 取余
SELECT MOD(10,3);------->1，相当于10%3

三、日期函数
#now 返回当前系统日期+时间
SELECT NOW();

#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

#curtime 返回当前时间，不包含日期
SELECT CURTIME();

#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) AS 年;
SELECT MONTH(NOW())AS 月;
SELECT day(NOW())AS 日;
……

#str_to_date：通过指定的格式将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y')------>1999-09-13

#查询入职日期为1992-4-3号的员工信息
SELECT *
FROM employees
WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format：将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日')----->20年06月10日

#查询有奖金的员工名和入职日期（xx月/xx日/xx年）
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年')AS 入职日期
from employees
where commission_pct is not NULL;

#四、其他函数
SELECT  version(); 查询版本号
SELECT  database();查询当前的库
SELECT  user();查询当前的用户

五、流程控制函数
#1.if函数：if else的效果
SELECT IF(10>5,'大','小')------->大
SELECT last_name,commission_pct,IF(commission_pct is NULL,'没奖金','有奖金')AS 备注
FROM employees

#2.case函数的使用一：switch case的效果
/*
java中
switch(变量或表达式){
case 常量1：语句1；break；
……
default：语句n；break；
}

mysql中
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 salary AS 原始工资,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

#case函数的使用二：类似于 多重if
/*
java中：
if（条件1）{
                  语句1；
}else if（条件2）{
                  语句2；
}
……
else{
            语句n；
}

mysql中:
case 
when 条件1 then 要显示的值1或语句1；
when 条件2 then 要显示的值2或语句2；
……
else 要显示的值n或语句n；
end
*/

#案例：查询员工的工资情况
如果工资＞20000，显示A级别
如果工资＞15000，显示B级别
如果工资＞10000，显示C级别
否则，显示D级别
SELECT salary AS 原始工资,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>1000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees

六、分组函数
/*
功能：用作统计使用，又成为聚合函数或统计函数或组函数
分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
特点：
①sum、avg一般用于处理数值型，max、min、count可以处理任何类型的数值
②以上分组函数都忽略null值
③可以和distinct搭配实现去重的运算
④count函数的单独介绍
一般使用count(*)用作统计函数
⑤和分组函数一同查询的字段要求是group by 后的字段
*/
#1.简单的使用
SELECT SUM(salary) FROM employees
SELECT AVG(salary) FROM employees
SELECT MIN(salary) FROM employees
SELECT MAX(salary) FROM employees
SELECT COUNT(salary) FROM employees

#2.参数支持哪些类型
SELECT SUM(last_name),AVG(last_name)
FROM employees;--------->结果都为0，这样写是不对的

SELECT MAX(last_name),MIN(last_name)
FROM employees;------------>last_name可排序，支持

SELECT COUNT(commission_pct)
FROM employees;------------>计算不为null的个数

#3.是否忽略null值
SELECT AVG(commission_pct),sum(commission_pct)/35,sum(commission_pct)/107
FROM employees;----->avg结果与除以35的结果相同，说明忽略null值了

SELECT max(commission_pct),min(commission_pct)
FROM employees;-------->max和min结果都不为null，说明忽略null值了

#4.和distinct搭配，去重
SELECT SUM(DISTINCT salary),SUM(salary)
FROM employees;

SELECT count(DISTINCT salary),count(salary)
FROM employees;

#5.count函数的详细介绍
SELECT COUNT(*)
FROM employees;-------->统计行数

SELECT COUNT(1)
FROM employees;

SELECT COUNT(salary)
FROM employees;

效率：
MYISAM存储引擎下，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1）的效率差不多，比COUNT(字段)要高

#6.两个日期之间差的天数，使用datediff()函数
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate))AS Diffrence
FROM employees

1.5进阶五 分组查询

/*
语法:
select 分组函数，列（要求出现在group by的后面）
from 表名
【where 筛选条件】
group by 分组的列表
【order by 子句】

注意：查询列表必须特殊，要求是分组函数和group by后出现的字段

特点：
①分组查询中的筛选条件分为两类（数据源不一样）：
分组前筛选：数据源为原始表，位置放在group by子句前面，使用where关键字
分组后筛选：数据源为分组后的结果集，位置放在group by子句后面，使用having关键字
②分组函数做条件肯定是放在having子句中
③能用分组前筛选的，优先考虑分组前筛选
④group by子句支持单个字段分组，也支持多个字段分组，多个字段之间用逗号隔开没有顺序要求，表达式或函数（用的较少）
⑤也可以添加排序（排序放在整个分组查询的最后，用order by子句）
*/

#引入：查询每个部分的平均工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id

#简单的分组查询
#案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id

#案例2：查询每个位置上的部门个数
SELECT COUNT(department_id),location_id
FROM departments
GROUP BY location_id

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

#案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct is not NULL
GROUP BY manager_id

#添加分组前的筛选条件
#案例1：查询哪个部门的员工个数>2
#①查询每个部门的员工个数
SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id
#②根据①的结果进行筛选，查询哪个部门的员工个数>2
SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id)>2

#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和其最高工资
#①查询每个工种有奖金的员工的最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct is not NULL #根据原始表就可以筛选
GROUP BY job_id
#②根据①的结果进行筛选，最高工资>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的领导编号是哪个，以及其最低工资
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000

#按表达式或函数进行分组
#案例1：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name) AS len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5

#按多个字段分组
#案例1：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id

#添加排序
#案例1：查询每个部门每个工种的员工的平均工资，并且按平局工资的高低显示出来
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC

1.6进阶六 连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
按年代分类：
sql92标准：仅仅支持内连接
sql99标准：支持内连接+外连接（左外和右外）+交叉连接【推荐使用】
按功能分类：
内连接（等值连接、非等值连接、自连接）
外连接（左外连接、右外连接、全外连接）
交叉连接

一、sql92标准
特点：
①多表连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表连接的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

1.等值连接

#案例1：查询女神名和对应的男神名
SELECT name,boyName
FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;

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

①为表起别名
好处:
提高语句的简洁度
区分多个重名的字段

**注意：**如果为表起了别名，则查询的字段就不用使用原来的表名去限定

#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.job_id=j.job_id;

②两个表的顺序是否可以调换，可以随意调换

#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM jobs AS j,employees AS e
WHERE e.job_id=j.job_id;

③可以加筛选？可以

#案例1：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees AS e,departments AS d
WHERE e.department_id=d.department_id AND commission_pct is not NULL

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

④可以加分组？可以

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

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,min(salary),commission_pct
FROM departments AS d,employees AS e
WHERE d.department_id=e.department_id  AND commission_pct is not NULL
GROUP BY department_name,d.manager_id

⑤可以加排序？可以

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

⑥是否可以实现三表连接？可以

#案例：查询员工名、部门名、所在的城市
SELECT last_name,department_name,city
FROM employees AS e,departments AS d,locations AS l
WHERE e.department_id=d.department_id AND d.location_id=l.location_id

2.非等值连接

#案例：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees AS e,job_grades AS g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal

3.自连接

#案例：查询员工名和上级的名称
SELECT e.last_name,m.last_name
FROM employees AS e,employees AS 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

1.内连接
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件；

分类:
等值连接
非等值连接
自连接

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

1.1 等值连接

#案例1：查询员工名、部门名
SELECT last_name,department_name
FROM employees e
inner JOIN departments d
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 last_name LIKE '%e%'

#案例3：查询部门个数＞3的城市名和部门个数（分组+筛选）
SELECT city,count(*)
FROM locations l
INNER JOIN departments d
on l.location_id=d.location_id
GROUP BY city
HAVING count(*)>3

#案例4：查询哪个部门的部门员工个数>3的部门名和员工个数，并按照个数降序（添加排序）
SELECT department_name,count(*)AS 员工个数
from departments AS d
INNER JOIN employees AS e
on d.department_id=e.department_id
GROUP BY d.department_name
HAVING count(*)>3
ORDER BY COUNT(*)DESC

#案例5：查询员工名、部门名、工种名、并按部门名降序（三表连接）
SELECT last_name,department_name,job_title
FROM employees AS e
INNER JOIN departments d on e.department_id=d.department_id
INNER JOIN jobs AS j on e.job_id=j.job_id
ORDER BY department_name DESC

1.2 非等值连接

#案例1：查询员工的工资级别
SELECT salary,grade_level
FROM employees e
inner JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal and g.highest_sal

#案例2：查询每个工资级别的个数＞2的个数，并且按工资级别降序
SELECT grade_level,COUNT(*)
FROM employees e
inner 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

1.3 自连接

#案例：查询姓名中包含字符k的员工名和上级领导名
SELECT e.last_name AS 员工名,m.last_name AS 上级名
FROM employees e
INNER JOIN employees m
on e.manager_id=m.employee_id
WHERE e.last_name LIKE '%k%'

2.外连接
应用场景：用于查询一个表中有，另一个表没有的记录

特点：
①外连接的查询结果为主表中的所有记录，
如果从表中有和他匹配的，则显示匹配的值
如果表中没有和他匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
②左外连接：left join左边的是主表
右外连接：right join右边的是主表
③左外和右外交换两个表的顺序，可以实现同样的效果
④全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有的
引入：
案例：查询男朋友不在男生表的女神名

#左外连接
SELECT b.name
FROM beauty AS b
LEFT OUTER JOIN boys AS bo
on b.boyfriend_id=bo.id
WHERE bo.id is NULL

#右外连接
SELECT b.name
FROM boys AS bo
RIGHT OUTER JOIN beauty AS b
on b.boyfriend_id=bo.id
WHERE bo.id is NULL

#案例：查询哪个部门没有员工
SELECT d.department_id
FROM departments AS d
LEFT OUTER JOIN employees AS e
on d.department_id=e.department_id 
WHERE e.employee_id is null

#全外连接
mysql不支持

3.交叉连接
笛卡尔乘积，b一共12行数据，bo一共4行数据，最终查询结果为48行数据

SELECT b.*,bo.*
FROM beauty b
cross join boys bo

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

1.7进阶七 子查询

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

分类：
按子查询出现的位置进行分类：
select后面----仅支持标量子查询
from后面----支持表子查询
where或having后面----支持标量子查询（单行）、列子查询（多行）、行子查询（★）
exists后面（相关子查询）-----支持表子查询

按结果集的行列数不同进行分类：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

1.where或having后面
1.1 标量子查询（单行子查询）★
1.2 列子查询（多行子查询）★
1.3 行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用 < > >= <= = <>
④子查询的执行优先于著查询执行，主查询的条件用到了子查询的结果

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

一、放在where或having后面
1.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=1并且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
②查询部门id和其最低工资，要求最低工资大于①
SELECT department_id,min(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
					SELECT min(salary)
					FROM employees
					WHERE department_id=50
)

1.2 列子查询（多行子查询）
特点：
①返回多行
②使用多行比较操作符
in/not in 等于列表中的任意一个
any/some 和子查询返回的某一个值比较 ，可读性不高，可用其他方法替代，min()
all 和子查询返回的所有值比较，可替换

#案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id in(
    SELECT department_id
    FROM departments
    WHERE location_id in(1400,1700)
)
该题也可以不同子查询，使用内连接：
SELECT DISTINCT last_name
FROM employees AS e
INNER JOIN departments AS d
ON e.department_id=d.department_id 
where location_id in(1400,1700)

#案例2：返回其他工种中比job_id为'IT_PROG‘部门任一工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees 
WHERE salary<ANY(
    SELECT salary
    FROM employees
    WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG'
或
SELECT employee_id,last_name,job_id,salary
FROM employees 
WHERE salary<(
    SELECT max(salary)
    FROM employees
    WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG'


#案例3:#案例2：返回其他工种中比job_id为'IT_PROG‘部门所有工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees 
WHERE salary<ALL(
    SELECT salary
    FROM employees
    WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG'

in可以换成=any
not in可以换成<>all 

1.3 行子查询（结果集一行多列或多行多列）–使用较少

案例1:查询员工编号最小并且工资最高的员工信息
普通写法：
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后面
总结：
仅支持标量子查询（结果集只能一行一列）

#案例1：查询每个部门的员工个数
左连接查询法：
SELECT COUNT(employee_id),d.department_id
FROM departments AS d
LEFT  JOIN employees AS e 
ON e.department_id=d.department_id
GROUP BY d.department_id

放在select后查询法：
SELECT d.*,(
			SELECT COUNT(*)
			FROM employees AS e
			WHERE e.department_id=d.department_id
)AS 员工个数
FROM departments AS d

#案例2：查询员工号=102的部门名
内连接方法：
SELECT department_name
FROM departments AS d
INNER JOIN employees AS e
ON d.department_id=e.department_id
WHERE e.employee_id=102

放在select后面方法：
SELECT (
SELECT department_name
FROM departments AS d
INNER JOIN employees e
ON d.department_id=e.department_id 
WHERE e.employee_id=102
) AS 部门名
强行嵌套，不好使！！！

三、放在from后面
from后面跟表，即相当于将查询结果当表来使用,要求必须起别名，结果几行几列视情况而定

#案例1：查询每个部门的平均工资的工资等级
①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
②连接①的结果集和job_grades表，筛选条件为平均工资在between lowest_sal and highest_sal
SELECT ag_dep.*,g.grade_level
FROM(
		SELECT AVG(salary) AS ag,department_id
		FROM employees
		GROUP BY department_id
)AS ag_dep
INNER JOIN job_grades AS g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal

三、放在exists后面（相关子查询）
初认识：
SELECT EXISTS(
SELECT employee_id
FROM employees)
结果为1，即exists只关心有没有，有为1，没有为0

语法：
exists（完成的查询语句）
结果：1或0

#案例1：查询有员工的部门名
SELECT department_name
FROM departments AS d
WHERE EXISTS(
				SELECT *
        FROM employees AS e
				WHERE d.department_id=e.department_id
)
#用in
SELECT department_name
FROM departments AS d
WHERE d.department_id in(
											SELECT department_id
											FROM employees
)

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

1.8进阶八 分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序后的字段】#可选
limit offset，size；
★offset 代表要显示条目的起始索引（起始索引从0开始）
size 代表要显示的条目个数
特点： 放在查询语句的最后
公式：要显示的页数page，每页的条目数size
select 查询列表
from 表
limit （page-1)*size，size

案例1：查询前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

1.9进阶九 联合查询

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

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

• 要求多条查询语句的查询列数是一致的
• 要求多条查询语句的查询的每一列的类型和顺序最好一致
• union关键字默认去重，如果使用union all可以包含重复项

引入案例：查询部门编号＞90或者邮箱包含a的员工信息

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