DQL 7_连接查询

7_连接查询

/*
含义：

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。

笛卡尔乘积现象：

表1 有m行，表2 有n行，结果=m*n行

发生原因：

没有有效的连接条件

如何避免：

添加有效的连接条件

分类：

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

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

*/

一、sql92标准

1.等值连接

/*
特点：

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

*/

案例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`;

/*

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

*/

案例3：查询员工名、工种号、工种名 (为表起别名，两个表顺序可以交换)

SELECT LAST_NAME,E.JOB_ID,JOB_TITLE #job_ID在两个表都有 因此 加入限定 E.JOB_ID 表明 员工中的job_ID
FROM EMPLOYEES AS E,JOBS AS J
WHERE E.`job_id`=J.`job_id`;

案例4：查询有奖金的员工名和部门名 (加筛选)

SELECT LAST_NAME,DEPARTMENT_NAME
FROM employees AS E,departments AS D
WHERE E.`department_id`=D.`department_id`
AND commission_pct IS NOT NULL;

案例5：查询城市名中第二个字符为o的部门名和城市名 （加筛选）

SELECT DEPARTMENT_NAME,CITY 
FROM departments AS D,LOCATIONS AS L
WHERE D.`location_id`=L.`location_id`
AND CITY LIKE '_o%';

案例6：查询每个城市的部门个数（加上分组）

SELECT CITY,COUNT(*)
FROM LOCATIONS AS L,departments AS D
WHERE L.`location_id`=D.`location_id`
GROUP BY city;

案例7：查询有奖金的每个部门的 部门名和部门领导编号，和该部门最低工资（加上分组）

SELECT DEPARTMENT_NAME,D.manager_id,MIN(salary)
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; #group by 加上D.manager_id 是不确定 部门名和领导编号是不是一一对应

案例8：查询每个工种的工种名和员工个数，并且按员工个数降序 (加排序)

SELECT JOB_TITLE,COUNT(*)
FROM JOBS AS J,EMPLOYEES AS E
WHERE J.`job_id`=E.`job_id`
GROUP BY JOB_TITLE
ORDER BY COUNT(*) DESC;

案例9：查询员工名、部门名和所在城市（三表连接）

SELECT LAST_NAME,D.DEPARTMENT_NAME,CITY
FROM EMPLOYEES AS E,departments AS D,LOCATIONS AS L
WHERE E.`department_id`=D.`department_id`
AND L.`location_id`=D.`location_id`
AND CITY LIKE 's%'
ORDER BY D.DEPARTMENT_NAME DESC;

2.非等值连接

案例10 查询员工的工资和工资级别

SELECT SALARY,grade_level
FROM EMPLOYEES,job_grades
WHERE SALARY BETWEEN lowest_sal AND highest_sal;

3.自连接 （把 一张表 当作 多个表 使用）

案例11 查询 员工名和上级名称

/*
思路：

先找last-name，再找last-name对应的 manager_ID ，
manager_ID 对应其上级id 其值 也是employee——id 
，因此 把employee表当作领导表 

查询思路：

员工表 和 领导表 是 一张表
员工表 last—name -》员工表中的manageer-ID（领导表中的employee-id）-》（领导表）last——name

*/

SELECT 员工.`last_name` AS 员工名 ,员工.`manager_id` as 其领导ID,领导.`last_name`AS 领导名
FROM employees AS 员工, employees AS 领导
where 员工.`manager_id`=领导.`employee_id`;

4.练习

练1：显示员工表的最大工资，平均工资

SELECT MAX(SALARY),AVG(SALARY)
FROM employees;

练2：查询员工表的employee——id job-id last——name 按department-id降序，salary升序

SELECT EMPLOYEE_ID,JOB_ID,LAST_NAME
FROM employees
ORDER BY DEPARTMENT_ID DESC,SALARY ASC;

练3：查询员工表中job_ID中包含a和e，并且a在e的前面

SELECT JOB_ID
FROM EMPLOYEES
WHERE JOB_ID LIKE '%a%e%';

练4：显示所有员工的姓名，部门号和部门名称

SELECT LAST_NAME,D.DEPARTMENT_ID,DEPARTMENT_NAME
FROM employees AS E,DEPARTMENTS AS D
WHERE E.`department_id`=D.`department_id`;

练5:查询90号部门员工的job-id和90号部门的location-id

SELECT E.JOB_ID,D.LOCATION_ID,D.DEPARTMENT_ID
FROM employees AS E,departments AS D
WHERE D.DEPARTMENT_ID=E.`department_id`
AND D.`department_id`=90;

练6：选择所有有奖金的员工的 last-name，department-name。location-id，city

SELECT LAST_NAME,DEPARTMENT_NAME,L.LOCATION_iD,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`
AND commission_pct IS NOT NULL;

练7：选择city在Toronto工作的员工的 last——name，job-id，department-id，department-name

SELECT LAST_NAME,E.JOB_ID,E.DEPARTMENT_ID,DEPARTMENT_NAME
FROM EMPLOYEES AS E,departments AS D,LOCATIONS AS L
WHERE  E.`department_id`=D.`department_id`
AND D.`location_id`=L.`location_id`
AND CITY='Toronto';

练8：查询每个工种、每个部门的部门名，工种名，和最低工资

SELECT D.DEPARTMENT_ID,JOB_TITLE,MIN(SALARY)
FROM DEPARTMENTS AS D,JOBS AS J,EMPLOYEES AS E
WHERE E.`job_id`=J.`job_id`
AND D.`department_id`=E.`department_id`
GROUP BY E.JOB_ID,DEPARTMENT_iD;

练9:查询 每个国家下的 部门个数大于2的 国家编号

SEL0ECT COUNTRY_ID,COUNT(*)
FROM LOCATIONS AS L,departments AS D
WHERE D.`location_id`=L.`location_id`
GROUP BY country_id
HAVING COUNT(*)>2;

练10：选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

/*

employees  Emp#  manager  Mgr#
kochhar    101    king      100

*/

SELECT 员工.LAST_NAME AS employees,员工.EMPLOYEE_ID AS"Emp#",管理者.LAST_NAME AS manager,管理者.EMPLOYEE_ID  AS "Mgr#"
FROM employees AS 员工,employees as 管理者
where 员工.`manager_id`=管理者.`employee_id`
AND 员工.last_NAME='kochhar';

二、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 连接条件;

分类：

等值
非等值
自连接

特点：

1.添加排序，分组，筛选
2.inner可以省略
3.筛选条件放在where后面，连接条件放在on后面

*/

1.等值连接

案例1：查询员工名，工种名

SELECT LAST_NAME,JOB_TITLE
FROM employees AS E
INNER JOIN JOBS AS J
ON E.`job_id`=J.`job_id`;

案例2：查询名字中包含e的员工名和工种名(添加筛选)

SELECT LAST_NAME,JOB_TITLE
FROM employees AS E
INNER JOIN JOBS AS J
ON E.`job_id`=J.`job_id`
WHERE E.LAST_NAME LIKE '%e%';

案例3：查询部门个数>3的城市名和部门个数，（添加分组+筛选）

SELECT CITY,COUNT(*) AS 个数
FROM departments AS D
INNER JOIN LOCATIONS AS L
ON D.`location_id`=L.`location_id`
GROUP BY L.`city`
HAVING 个数>3;

案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按照个数降序（添加排序）

SELECT DEPARTMENT_NAME,COUNT(*)AS 个数
FROM EMPLOYEES AS E
INNER JOIN DEPARTMENTS AS D
ON E.`department_id`=D.`department_id`
GROUP BY DEPARTMENT_NAME
having 个数>3
ORDER BY 个数 DESC;

案例5：查询员工名，部门名，工种名 并按部门名降序（三表连接）

SELECT LAST_NAME,DEPARTMENT_NAME,JOB_TITLE
FROM EMPLOYEES AS E
INNER JOIN departments AS 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;

2.非等值连接

案例6：查询员工工资级别

SELECT salary,grade_level
FROM employees AS E
INNER JOIN job_grades AS J
ON E.`salary` between J.`lowest_sal` AND J.`highest_sal`
ORDER BY J.`grade_level` DESC;

案例7：查询工资级别的个数>20的个数，并且按工资级别降序

SELECT count(*) AS 个数,grade_level
FROM employees AS E
INNER JOIN job_grades AS J
ON E.`salary` BETWEEN J.`lowest_sal` AND J.`highest_sal`
GROUP BY J.`grade_level`
having 个数>20
ORDER BY J.`grade_level` DESC;

3.自连接

案例8：查询姓名中包含字符k的员工名字和其对应的上级的名字

SELECT EE.LAST_NAME,ER.LAST_NAME
FROM employees AS EE
INNER JOIN employees AS ER
ON EE.`manager_id`=ER.`employee_id`
WHERE EE.`last_name` LIKE '%k%';
 

2.外连接

/*
应用场景：

用于查询一个表中有，另一个表中没有的记录。

特点：

1.外连接的查询结果 为 主表中的所有记录
  如果从表中有和主表匹配的，则显示匹配的值
  如果从表中没有和主表匹配的，则显示null值
  外连接查询结果=内连接结果+主表中有 而 从表中没有的记录
  
2.左外连接，left join 左边的是主表
  右外连接，right join 右边的是主表
  
3.左外和右外交换两个表顺序，可以实现同样的效果
 
4.全外连接=内连接结果+表1中有但表2中没有的+表2中有但表1没有的 （左外连接+右外连接）

*/

案例9：查询 男朋友 不在 男生表 的女生名 （左外连接）

SELECT BE.NAME,BO.*
FROM beauty BE
LEFT OUTER JOIN boys BO
ON BE.`boyfriend_id`=BO.`id`
WHERE BO.`id` IS NULL; #最好选从表里的主键列

案例10：查询哪个部门没有员工（左外）

SELECT D.*,E.EMPLOYEE_ID
FROM departments AS D
LEFT OUTER JOIN employees AS E
ON D.`department_id`=E.`department_id`
WHERE E.`employee_id` IS NULL;

案例11：查询哪个部门没有员工（右外）

SELECT D.*,E.EMPLOYEE_ID
FROM employees AS E
right OUTER JOIN departments AS D
ON D.`department_id`=E.`department_id`
WHERE E.`employee_id` IS NULL;

案例12：全外 这个实现不了 （不分 主、从表）

SELECT BE.*,BO.*
FROM beauty AS BE
FULL OUTER JOIN BOYS AS BO
ON BE.`boyfriend_id`=BO.ID;

3.交叉连接 (两表笛卡尔乘积)

SELECT BE.*,BO.*
FROM beauty AS BE
CROSS JOIN BOYS AS BO;

4.练习

案例13：查询编号>3的女生的男朋友信息，如果有则列出详细，如果没有，用null填充

/*

由题意得，如果有男朋友信息，就输出，没有，就用null 由此可知 女生为主表（全输出）

*/

SELECT BE.ID,BE.NAME,BO.*
FROM beauty AS BE
LEFT OUTER JOIN boys AS BO
ON BE.`boyfriend_id`=BO.ID
WHERE BE.ID>3;

案例14:查询哪个城市没有部门

SELECT CITY,D.*
FROM LOCATIONS AS L
LEFT OUTER JOIN departments AS D
ON L.`location_id`=D.`location_id`
WHERE D.`department_id` IS NULL;

案例15：查询部门名为SAL或IT的员工信息

/*

部门名为SAL或IT 有可能没有员工 所以用外连接
部门一定有，所以部门 作 主表
有两行null值的原因是 部门名一样，但部门编号不一样

*/

SELECT E.*,D.DEPARTMENT_NAME ,D.`department_id` 
FROM departments AS D
left outer JOIN employees AS E
ON E.`department_id`=D.`department_id`
WHERE D.`department_name` in ('SAL','IT');

左外连接 内连接 右外连接