第八节：连接查询——SQL92

一、连接查询

　　1、连接查询是什么？

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

　　2、笛卡尔积

　　　　笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接

　　　　笛卡尔积：当表1有 m 条数据，表2有 n 行数据，连接查询结果就为 m*n 行。

　　　　但是这时候并不是我们想要的数据，就需要在笛卡尔积上面进行筛选，即通过添加有效的连接条件，来获取数据。

　　3、分类

　　　　（1）按年代分类：

　　　　　　　　SQL92 标准：仅仅支持内连接；（对其他连接支持不好）

　　　　　　　　SQL99 标准【推荐使用】：支持内连接 + 外连接（左外和右外）+ 交叉连接

　　　　（2）按功能分类：

　　　　　　　　内联接：

　　　　　　　　　　① 等值连接

　　　　　　　　　　② 非等值连接

　　　　　　　　　　③ 自连接

　　　　　　　　外连接：

　　　　　　　　　　① 左外连接

　　　　　　　　　　② 右外连接

　　　　　　　　　　③ 全外连接

　　　　　　　　交叉连接

 

二、SQL92 标准

　　SQL92 标准对内连接支持的较好，所以在这里只学习 SQL92 标准的内连接。

　　1、等值连接

　　　　（1）语法格式：

select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

　　

　　　　（2）特点：

　　　　　　① 一般为表起别名；
　　　　　　②多表的顺序可以调换；
　　　　　　③n表连接至少需要n-1个连接条件；
　　　　　　④等值连接的结果是多表的交集部分；

 

　　2、非等值连接

　　　　（1）语法格式

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

　　

　　　　（2）特点

　　　　　　① 一般为表起别名；
　　　　　　②多表的顺序可以调换；
　　　　　　③n表连接至少需要n-1个连接条件；
　　　　　　④等值连接的结果是多表的交集部分；

 

　　3、自连接

　　　　（1）语法格式：

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

　　　　　　注意：这里的别名1和别名2都是同一张表的不同别名。

　　　　（2）特点：

　　　　　　① 一般为表起别名；
　　　　　　②多表的顺序可以调换；
　　　　　　③n表连接至少需要n-1个连接条件；
　　　　　　④等值连接的结果是多表的交集部分；

　　

三、案例

　　1、等值连接

　　　　（1）简单连接查询

　　　　　　① 查询员工名和对应的部门名

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

　　

　　　　（2）为表起别名

　　　　　　好处：

　　　　　　　　① 提高语句的简洁度；

　　　　　　　　② 区分多个重名的字段；

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

SELECT 
  last_name,
  e.job_id,
  job_title 
FROM
  employees e,
  jobs j 
WHERE e.`job_id` = j.`job_id` ;

　　　　

　　　　（3）两个表的顺序可以调换

　　　　　　① 查询员工名、工种号、工种名

SELECT 
  last_name,
  e.job_id,
  job_title 
FROM
  jobs j,
  employees e 
WHERE j.`job_id` = e.`job_id` ;

　　

　　　　（4）添加筛选条件

　　　　　　① 查询有奖金的员工名、部门名

SELECT 
  last_name,
  department_name,
  commission_pct 
FROM
  employees e,
  departments d 
WHERE e.`department_id` = d.`department_id` 
  AND e.`commission_pct` IS NOT NULL ;

　　

　　　　　　② 查询城市名中第二个字符为 o 的对应的部门名和城市名

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

　　

　　　　（5）添加分组

　　　　　　① 查询每个城市的部门个数

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

　　

　　　　　　② 查询出有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT 
  department_name,
  d.manager_id,
  MIN(salary) 最低工资 
FROM
  employees e,
  departments d 
WHERE e.`department_id` = d.`department_id` 
  AND commission_pct IS NOT NULL 
GROUP BY d.`department_name`,
  d.`department_id` ;

　　

　　　　（6）添加排序

　　　　　　① 查询每个工种的工种名和员工的个数，并且按员工个数降序

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

　　

　　　　　　② 查询员工名，部门名和所在城市，并且城市以 's' 开头，按照部门名降序；

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` 
  AND city LIKE 's%' 
ORDER BY department_name DESC ;

　　

　　2、非等值连接

　　　　（1）查询员工的工资和工资级别

　　　　　　方式一：

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

　　

　　　　　　方式二：

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

　　

　　3、自连接

　　　　（1）查询员工名以及他的上级的名称

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

　　

四、练习

　　1、显示所有员工的姓名，部门号和部门名称。

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

　　

　　2、查询90号部门员工的job_id和90号部门的location_id

SELECT 
  job_id,
  location_id 
FROM
  employees e,
  departments d 
WHERE e.`department_id` = d.`department_id` 
  AND e.`department_id` = 90 ;

　　

　　3、选择所有有奖金的员工的 last_name, department_name, location_id和city

SELECT 
  last_name,
  department_name,
  l.location_id,
  city 
FROM
  employees e,
  departments d,
  locations l 
WHERE e.department_id = d.department_id 
  AND d.location_id = l.location_id 
  AND e.commission_pct IS NOT NULL ;

　　

　　4、选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name;

SELECT 
  last_name,
  job_id,
  d.department_id,
  department_name 
FROM
  employees e,
  departments d,
  locations l 
WHERE e.department_id = d.department_id 
  AND d.location_id = l.location_id 
  AND city = 'Toronto' ;

　　

　　5、查询每个工种、每个部门的部门名、工种名和最低工资

SELECT 
  department_name,
  job_title,
  MIN(salary) 最低工资 
FROM
  employees e,
  departments d,
  jobs j 
WHERE e.`department_id` = d.`department_id` 
  AND e.`job_id` = j.`job_id` 
GROUP BY department_name,
  job_title ;

　　

　　6、查询每个国家下的部门个数大于2的国家编号

SELECT 
  country_id,
  COUNT(*) 部门个数 
FROM
  departments d,
  locations l 
WHERE d.`location_id` = l.`location_id` 
GROUP BY country_id 
HAVING 部门个数 > 2 ;

　　

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

　　

 

 

 

SELECT 
  e.last_name employees,
  e.employee_id "Emp#",
  m.last_name manager,
  m.employee_id "Mgr#" 
FROM
  employees e,
  employees m 
WHERE e.manager_id = m.employee_id 
  AND e.last_name = 'kochhar' ;