Mysql查询（五）:连接查询（DQL语言）

一、含义

• 当查询中涉及到了多个表的字段，需要使用多表连接
• 语法：select 字段1，字段2 from 表1，表2,…;
• 笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
• 如何解决：添加有效的连接条件

二、分类

按年代分类：

sql92：

	等值
	非等值
	自连接
	也支持一部分外连接（用于oracle、sqlserver，mysql不支持）

sql99【推荐使用】

	内连接
		等值
		非等值
		自连接
	外连接
		左外
		右外
		全外（mysql不支持）
	交叉连接

SQL92语法

1、等值连接
语法：
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点：
① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分

2、非等值连接
语法：
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
3、自连接
语法：
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

四、SQL99语法

1、内连接
语法：
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点：
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件

分类：
等值连接

#案例1.查询员工名、部门名
#1、等值连接
#案例1.查询员工名、部门名
SELECT first_name,department_name 
FROM departments as a 
JOIN employees as b 
on  a.department_id=b.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  as d
JOIN locations as l
WHERE d.location_id=l.location_id
GROUP BY city
HAVING count(*)>3

#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT department_name,count(*) 
FROM departments as d 
join employees as e
on d.department_id=e.department_id
GROUP BY e.department_id
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;

非等值连接

#查询员工的工资级别
SELECT salary,grade_level 
FROM employees as e
join job_grades as j
on e.salary BETWEEN j.lowest_sal AND j.highest_sal;

 #查询工资级别的个数>20的个数，并且按工资级别降序
SELECT salary,grade_level ,count(*)
FROM employees as e
join job_grades as j
on e.salary BETWEEN j.lowest_sal AND j.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%' AND m.last_name LIKE '%k%'

2、外连接
语法：
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点：
①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
②left join 左边的就是主表，right join 右边的就是主表
full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行

 #右外连接
 SELECT b.*,bo.*
 FROM boys bo
 RIGHT OUTER JOIN beauty b
 ON b.`boyfriend_id` = bo.`id`
 WHERE bo.`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;

3、交叉连接
语法：
select 查询列表
from 表1 别名
cross join 表2 别名;

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

特点：
类似于笛卡尔乘积

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