MySQL-day02-多表查询

一.笛卡尔积错误

错误例子:

select employee_id,department_id

from employees,departments  一共会有107*27个记录,会有不存在的信息

from employees cross join departments

错误原因: 缺少了连接条件

解决:  案例：查询员工的姓名及其部门名称

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

在表中有相同列时，在列名之前加上表名前缀

建议:从SQL优化角度,建议多表查询,每个字段前都指明所在的表

可以给表起别名,在select和where中使用(起了别名就要都使用别名否则报错)

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

二.多表查询

练习: 查询员工的employee_id,last_name,department_name,city

SELECT  e.employee_id,e.last_name,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;

三个表有两次连接,b个表至少需要n-1个条件

2.1 等值连接 vs 非等值连接

以上都是等值连接

非等值连接例子:

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

2.2 自连接 vs 非 自连接

2.3 内连接 vs 外连接

1.内连接

返回的是满足条件的行

2.外连接

除了返回满足条件的行,还返回不满足条件的(左,右)

练习 : 查询所有员工的last_name,department_name信息 

select last_name,department_name

from employees e,departments d

where e.department_id = d.department.id(+);   //SQL92语法

2.3.1 SQL 99 实现 

1.内连接  join ...on...

SELECT e.last_name,d.department_id
FROM employees  e (inner)JOIN departments d
ON e.department_id = d.department_id;

SELECT e.last_name,d.department_id,l.location_id
FROM employees  e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON l.location_id=d.location_id;

2.外连接 left join on /  right joinon

练习 : 查询所有员工的last_name,department_name信息 

左外:SELECT e.last_name,d.department_name
FROM employees e LEFT (OUTER) JOIN departments d
ON e.department_id=d.department_id;

右外: SELECT e.last_name,d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id;

满外连接 full join on mysql不支持

2.3.2 UNION操作符 

UNION 操作符返回两个查询的结果集的并集， 去除重复记录。

UNION ALL 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行 UNION ALL 语句时所需要的资源比 UNION 语句少。如果明确知道合并数据后的结果数据 不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL 语句，以提高数据查询的效 率。

 

2.4  七种join实现

#中间 内连接
SELECT e.last_name,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
#2左上 左外连接
SELECT e.last_name,d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
#3右上 右外连接 
SELECT e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;
#4 左中  A中不含B
SELECT e.last_name,d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
# 5 右中 b中不含a
SELECT e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
#6 左下 满外连接(两种方式)
SELECT e.last_name,d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL 
SELECT e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
#7 左中 union all 右中
SELECT e.last_name,d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL 
SELECT e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
 

2.5 自然连接,using (了解)

1.自然连接

你自动查询两张连接表中 所有相同的字段 ，然后进行 等值 连接 。

92:

SELECT employee_id,last_name,department_name

FROM employees e JOIN departments d

ON e. `department_id` = d. `department_id`

AND e. `manager_id` = d. `manager_id` ;

SELECT employee_id,last_name,department_name

FROM employees e NATURAL JOIN departments d;

2.using

SELECT employee_id,last_name,department_name

FROM employees e JOIN departments d

USING (department_id);

​​​​​​2.6练习

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

SELECT e.last_name,d.department_id,d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

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

SELECT e.job_id,l.location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON l.location_id = d.location_id
WHERE d.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 LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON l.location_id = d.location_id
WHERE 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
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON l.location_id = d.location_id
WHERE l.city = 'Toronto'  ;

# 5. 查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为 ’Executive’

SELECT e.last_name,e.job_id,d.department_id,d.department_name,e.salary,d.location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON l.location_id = d.location_id
WHERE d.department_name = 'Executive' ;

# 6. 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号

SELECT Emp.last_name,Emp.employee_id,Mgr.last_name "manager",Mgr.employee_id "m_id"
FROM employees Emp LEFT JOIN employees Mgr
ON Emp.manager_id = Mgr.employee_id;

//所有员工的管理者

# 7. 查询哪些部门没有员工(子查询也行)

SELECT d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS  NULL;

//员工的部门为空

# 8. 查询哪个城市没有部门

SELECT l.city
FROM departments d RIGHT JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_id IS NULL;

# 9. 查询部门名为 Sales 或 IT 的员工信息

SELECT d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name='IT' OR d.department_name='Sales';
WHERE d.department_name IN ('IT','Sales');
#总结:
#on 后面是连接条件,where是过滤条件
#1找表 2. 什么连接 3. 连接条件 4.过滤条件
# 左外,右外会有null