8、连接查询 (sql192标准)---学习笔记

8、连接查询 (sql192标准)

• 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
• 笛卡尔乘积现象：表1 有m行 ，表2有n行，结果 = m*n 行

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

• 分类：

按年代分类：
	sql192标准：仅仅支持内连接
	sql199标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
	
	按功能分类：
		内连接
			等值连接
			非等值连接
			自连接
		外连接
			左外连接
			右外连接
			全外连接
		交叉连接

8.1、sql192标准

8.1.1、等值连接

• 总结：

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

8.1.2、案例1：查询女神名和对应的男神名

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

8.1.2、案例2：查询员工名和对应的部门名

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

8.2.1、为表起别名

1、提高语句的简洁度
2、区分多个重名的字段

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

8.2.2、查询员工名、工种号，工种名

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

8.3、两个表的顺序是否可以调换（可以）

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

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

8.4、可以加筛选吗（可以）

8.4.1、案例1：查询有奖金的员工名、部门名

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

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

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

8.5、可以加分组吗（可以）

8.5.1、案例1：查询每个城市的部门个数

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

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

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

8.6、可以加排序

8.6.1、案例1：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT(*) 员工个数
FROM jobs j ,employees e
WHERE j.`job_id` = e.`job_id`
GROUP BY j.job_title
ORDER BY COUNT(*) DESC;

8.7、可以实现三表连接吗（可以）

8.7.1、案例1：查询员工名、部门名和所在的城市,和城市首字母为 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%'

8.8、非等值连接

• 要用到的数据表

CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);

8.8.1、案例1：查询员工的工资和工资级别

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

8.9、自连接

8.9.1、案例1：查询员工名和上级的名称

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

8.10、练习题

#1.  显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name , d.department_id, d.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 e.last_name , d.department_name , l.location_id , l.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 e.last_name, e.job_id , d.department_id ,d.department_name,l.city
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 j.job_id , d.department_name , j.job_title,MIN(salary)
FROM jobs j, departments d , employees e
WHERE e.`department_id` = d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY j.job_id , d.department_name;


#6.查询每个国家下的部门个数大于 2 的国家编号
SELECT COUNT(*) 个数 , l.country_id
FROM departments d , locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY l.country_id
HAVING COUNT(*) > 2;

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

-- employees  Emp#  manager Mgr#
-- kochhar 101 king 100
SELECT e.last_name employees, e.employee_id "Emp#", m.last_name manager , m.employee_id "Mgr#"
FROM employees e , employees m
WHERE m.`employee_id` = e.`manager_id`;