MySQL基础（三）

连接查询

含义：多表查询
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
按年份分类
sql192标准：仅仅支持内连接
sql199标准【推荐】：支持内连接 + 外连接（左外和右外）+交叉连接

按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接

一、sql192标准
1、等值连接
案例1:查询员工名和对应的部门名

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

SELECT e.last_name, d.department_id
FROM employees AS e, departments AS d  //为表取别名 
WHERE e.last_name = d.department_id;

添加分组，条件，排序
案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name, d.manager_id, MIN(salary)
FROM employees AS e, departments AS d  //为表取别名 
WHERE e.department_id = d.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id
ORDER BY department_name DESC;

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

2、非等值连接(在等值连接的条件中是非等的，其他的都一样)
案例1:查询员工的工资和工资级别

SELECT salary, grad_level
FROM job_grades AS j, employess AS e
WHERE salary BETWEEN j.lowest_sal AND j.highest_sal
AND jgrade_level = "A";

3、自连接（对一张表查多遍）
案例：查询员工名和上级的名称

SELECT e.last_name, e.employees_id,m.last_name, m.employees_id,
FROM  employess AS e, employess AS m
WHERE e.manager_id = m.employees_id;

二、sql199语法
语法：
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 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】；
分类：
等值连接
非等值连接
自连接
1）、等值连接

特点：
1、添加排序、分组、筛选
2、inner可以省略
3、筛选条件放在where后面，连接条件放在鸥鸟的后买呢，提高分离性，便于阅读
4、inner join连接和sql192语法中的等值连接效果一样的，都是查询多表的交集

案例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 COUNT（*）, city
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
HAVING COUNT（*）>3;

案例4:查询员工名、部门名、工种名，并按部门名降序（三表查询）

SELECT last_name, job_title，department_name
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
GROUP BY department_name;

2）、非等值连接
比较简单，参照1和sql192语法
3）、自连接
比较简单，参照1和sql192语法

二、外连接

应用场景：用于查询一个表中有，另一个表中没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

案例1:哪个部门没有员工
左外：

SELECT d.*, e.employee_id
FROM departments d
LEFT JOIN employees e 
ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

交叉连接：笛卡尔乘积

总结：

子查询

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

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

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

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

特点：
1、子查询放在小括号内
2、子查询一般放在条件的右测
3、标量子查询，一帮搭配单行操作符使用

<    =     <=     >=     <>

列子查询，一般搭配之多行操作符使用
IN、ANY/SOME、ALL
4、子查询的执行优先于主查询执行，主查询的条件用到子查询的结果

1）、标量子查询
案例1、谁的工资比Abel高？
（1）、查询Abel的工资

SELECT salary
FROM employees e
WHERE last_name = “Abel”;

（2）、谁的工资比（1）查询的结果高

SELECT last_name
FROM employees e
WHERE salary >（
	SELECT salary
	FROM employees e
	WHERE last_name = “Abel”
  ）;

案例2、返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
1）、查询141号员工的job_id

SELECT job_id
FROM employees
WHERE employee_id = 141;

2）、查询143员工工资

SELECT salary
FROM employees
WHERE employee_id = 143;

3）、查询员工工资 、工姓名和job_id， 要求job_id与141号员工相同且salary比143号员工多

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
1）、查询公司的最低工资

SELECT MIN（salary）
FROM employees;

2）、查询last_name, job_id , salary，且salary= （1）

SELECT last_name, job_id , salary
FROM employees
WHERE salary = （
	SELECT MIN（salary）
	FROM employees
）;

2）、列子查询（多行子查询）

案例1:返回location_id是1400或1700的部门中的所有员工姓名
1）、查询location_id是1400和1700的部门

SELECT department_id
FROM departments
WHERE location_id IN( 1400, 1700) ;

2）、查询(1)中的员工姓名，要求部门号是（1）列表中的某一个

SELECT last_name
FROM employees
WHERE department_id IN( 
	SELECT department_id
	FROM departments
	WHERE location_id IN( 1400, 1700)
) ;

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

完整的查询语句的结果为非空，则结果为1，反之为0