第四天MySQL 连接查询（内连接、外连接、交叉连接）（sql99版本下） 子查询： select 后面（标量子查询） from 后面（表子查询） where 或having后面（标量子查询、列子查询、行子查询） exists后面（相关子查询）（表子查询）
2020 5/7
（sql99）
语法：
SELECT 查询列表
FROM 表1 别名 [连接类型]
JION 表2 别名
ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 筛选条件]
[ORDER BY 排序列表]
分类：
内连接：inner
外连接：
左外：left [outer]
右外：right[outer]
全外：full[outer]
交叉连接：cross
#############（一）、内连接########################################
语法：
SELECT 查询列表
FROM 表1 别名
INEER JOIN 表2 别名
ON 连接条件;(后可外加其他如where、group by等子句)
特点：
① 可添加排序、分组、筛选
② inner可以省略
③ 筛选条件放在where 后面，连接条件放在on后面，提高分离性，便于阅读（对比与sql92,它的筛选条件和连接条件都是放在where后面）
分类：
###等值连接
#案例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
e.last_name LIKE '%e%';
#案例3：查询那个部门个数大于3的部门名和员工个数，并按个数降序【排序】
SELECT
department_name,
COUNT(*)
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;
#案例4：查询部门个数大于3的城市名和部门个数【分组+筛选】
SELECT
city,
COUNT(*)
FROM
locations l
INNER JOIN departments d ON l.location_id = d.location_id
GROUP BY
city
HAVING
COUNT(*) > 3;
#案例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 j.job_id = e.job_id
ORDER BY
department_name DESC;
###非等值连接
#查询员工的工资级别
SELECT
*
FROM
job_grades;
SELECT
grade_level,
last_name,
salary
FROM
employees e
JOIN job_grades j ON e.salary BETWEEN lowest_sal
AND highest_sal;
#查询工资级别的个数大于20，并且按工资级别降序【排序+分组+筛选】
SELECT
COUNT(*) 个数,
grade_level
FROM
employees e
JOIN job_grades j ON e.salary BETWEEN lowest_sal
AND highest_sal
GROUP BY
grade_level
HAVING
COUNT(*) > 20
ORDER BY
grade_level DESC;
###自连接
#案例：查询员工的名字中包含'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%';
###（二）、外连接#########################################
特点：
- 外连接的查询结果为主表中的所有记录
a) 如果从表中有和它匹配的，则显示为匹配的值
b) 如果从表中没有和它匹配的，则线束null
c) 外连接查询结果=内连接查询结果+主表中有而从表中没有的记录
- 左外连接，left join左边的是主表（右外连接，right join右边的为主表）
- 左外和右外交换两个表的顺序，可以实现同样的效果
- 全外连接=内连接查询结果+1表中有而表2中没有的记录+表2有而表1没有的记录
引入：
#查询没有男朋友的女神名
USE girls;
SELECT
*
FROM
beauty;
SELECT
NAME,
bo.id
FROM
beauty bea
LEFT JOIN boys bo ON bea.boyfriend_id = bo.id
WHERE
bo.id IS NULL;
###左（右）外连接
#查询哪个部门没有员工
#左外
SELECT
department_name,
employee_id
FROM
departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE
employee_id IS NULL;
-----------------------------------------------------------
#右外
SELECT
department_name,
employee_id,
FROM
employees e
RIGHT JOIN departments d ON d.department_id = e.department_id
WHERE
employee_id IS NULL;
###全外连接
#查询女神表和男朋友表的全外连接
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 PK sql99）
功能：sql99支持的较多
可读性：sql99实现连接查询的条件和筛选选条件的分离，可读性较高
十八、子查询
含义：出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句 ，称为主查询或外查询
分类：
按子查询出现的位置：
select 后面（仅仅支持标量子查询）
from 后面（支持表子查询）
where 或having后面（标量子查询、列子查询、行子查询）-》重要
exists后面（相关子查询）（表子查询）
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果一般为多行多列）
###where或having 后面
1. 标量子查询（单行子查询）
2. 列子查询（多行子查询）
3. 行子查询（多行多列）
特点:
① 子查询放在小括号内
② 子查询一般放在条件的右侧
③ 标量子查询，一般搭配着单行操作符使用（> < <>
④ 列子查询，一般搭配着多行操作符使用（in 、 any|some 、 all）
⑤ 子查询的执行优先于主查询的执行，主查询的结果用到了子查询的结果
###标量子查询
#案例1：谁的工资比Abel高？
SELECT *
FROM employees
WHERE salary > (
SELECT salary FROM employees WHERE last_name = 'Abel' #先查出Abel的工资
);
#案例2：返回job_id与141号相同，salary比143号员工多的员工、姓名、job_id和工资
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 last_name,job_id,salary
FROM employees
WHERE salary =(
SELECT MIN(salary) FROM employees
);
#案例4：查询最低工资大于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：返回location_id是1400或1700的部门中的所有员工姓名
SELECT
last_name,
department_id
FROM
employees e
WHERE
department_id IN (
SELECT
department_id
FROM
departments
WHERE
location_id IN (1400, 1700)
);
#案例2：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员 工号、姓名、job_id 以及salary
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees e
WHERE
salary < ANY (
SELECT DISTINCT
salary
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
-------------------两种方式等价------------------------
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees e
WHERE
salary < (
SELECT
MAX(DISTINCT salary)
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees e
WHERE
salary < ALL (
SELECT DISTINCT
salary
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
-------------------两种方式等价------------------------
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees e
WHERE
salary < (
SELECT
MIN(DISTINCT salary)
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
注意：因为列子查询中的ALL和ANY|SOME通能能被MAX或MIN分组查询后的标量子查询替换，因此用的较少
#行子查询（结果集一行多列或多行多列）
#案例：查询员工编号最小并且工资最高的员工信息
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 后面
注意：仅仅支持标量子查询
#案例：查询每个部门的员工个数
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 e.department_id = d.department_id
WHERE
e.employee_id = 102
);
###from后面
注意：将子查询结果充当一个表，要求必须取别名
#案例：查询每个部门的平均工资的工资等级
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后面（相关子查询）
语法:exists(完整的查询语句)
结果：1或0
SELECT EXISTS(SELECT employee_id FROM employees);->1
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary = 30000);->0
存在结果就返回1，不存在就返回0
#案例：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id = e.department_id
);
