MySQL DQL语言——连接查询

连接查询

beauty 表

boys 表

departments 表

employee 表

jobs 表

locations表

含义:又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行 表2有n行 结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
按年代分类
sql92
sql99【推荐】:支持内连接+外连接（左外连接和右外连接）+交叉连接

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

一、sql92标准
(一) 、等值连接
1、等值连接
案例1：查询女神名和对应的男神

SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id=boys.id;

案例2:查询员工名和对应的部门名

SELECT  last_name,department_name FROM employees,departments WHERE employees.`department_id`=departments.`department_id`;

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,e.department_id,e.commission_pct FROM employees  e,departments ep WHERE e.`department_id`=ep.`department_id` AND e.`commission_pct` IS NOT NULL ;

案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city FROM departments d,locations l WHERE d.`location_id`=l.`location_id` AND city LIKE '%_a%'   ;

5.可不可以加分组?
案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数,city FROM locations l,departments d WHERE l.`location_id`=d.`location_id`  GROUP BY city;

案例2：查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资

SELECT department_name 部门名,d.manager_id  部门领导编号,MIN(e.salary) FROM departments d,employees e WHERE commission_pct IS NOT NULL AND d.`manager_id`=e.`manager_id` GROUP BY department_name,d.manager_id;

6.可以加排序
案例：查询每个工种的工种名和员工的个数,并且按员工的个数降序

SELECT job_title,COUNT(*) 个数 FROM jobs j,employees e WHERE j.`job_id`=e.`job_id` GROUP BY job_title ORDER BY COUNT(*) 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`;

（二）、非等值连接
案例:查询员工的工资和工资级别

 SELECT salary,grade_level FROM employees e,job_grades j WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

（三）、自连接
案例：查询 员工名和上级的名称

 SELECT e.last_name,e.manager_id,m.last_name,m.manager_id  FROM employees e ,employees m WHERE e.`employee_id`=m.`manager_id` ;
 

二、sql99标准

语法：
select 查询列表
from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【WHERE 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：
内连接（☆）：inner
外连接
左外（☆）：left 【outer】
右外（☆） right 【outer】
全外:full 【outer】
交叉连接:cross 【join】

内连接
完整语法:
select 查询请求
from 表1 别名
inner join 表2 别名
on 连接条件;
分类：
等值连接
非等值连接
自连接

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

(一)、等值连接
1.查询员工名、部门名（调换位置）

 SELECT  last_name,department_name FROM employees e INNER JOIN departments d ON 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 departments d INNER JOIN locations l ON d.`location_id`=l.`location_id` GROUP BY city HAVING COUNT(*)>3;

4.查询哪个部门的部门员工个数>3的部门名和员工个数，并且按个数降序（排序）

SELECT department_name,COUNT(*) 员工个数 FROM departments d INNER JOIN employees e ON d.`manager_id`=e.`manager_id` GROUP BY department_name HAVING COUNT(*) >3 ORDER BY COUNT(*) ASC	;  

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 ;

(二)、非等值连接
1.查询员工的工资级别

SELECT salary,grade_level
FROM employees e 
JOIN job_grades j ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

2.查询工资级别的个数>20的个数，并且按工资级别排序

SELECT COUNT(*),grade_level
FROM employees e 
JOIN job_grades j ON e.`salary` 
BETWEEN j.`lowest_sal` AND j.`highest_sal`   
GROUP BY grade_level HAVING COUNT(*)>20 ORDER  BY grade_level DESC;

(三)、自连接
1.查询员工的名字、上级的名字

SELECT e.last_name,m.last_name 
FROM employees e JOIN employees m ON e.`manager_id`=m.`manager_id` 

2.查询姓名中包含字符k的员工的名字、上级的名字

SELECT e.last_name,m.last_name 
FROM employees e JOIN employees m 
ON e.`manager_id`=m.`manager_id`WHERE e.last_name LIKE '%k%';

(四)、外连接

应用场景：用于查询一个表中有，另一个表没有的记录 特点：
1.外连接的查询结果为主表的所有记录 如果从表中国有和它匹配的，则显示匹配的值 如果从表中没有和它匹配的，则显示null 外连接查询结果=内连接结果+主表中有而从表没有的记录

2.左外连接，left join左边的是主表 右外连接，right join右边的是主表

3.左外和右外交换两个表的顺序，可以实现同样的效果

1.查询男朋友不在男神表的女神名

SELECT m.name,b.* 
FROM beauty m 
LEFT OUTER JOIN boys b 
ON m.`boyfriend_id`=b.`id`  WHERE  b.`id` IS NULL;

2.查询哪个部门没有员工
左外

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

右外

SELECT d.*,e.`employee_id`  FROM employees e 
RIGHT OUTER JOIN departments d ON d.`manager_id`=e.`manager_id` WHERE e.`employee_id` IS NULL ; 

交叉连接

SELECT m.*,b.* 
FROM beauty m
CROSS JOIN  boys b;