第06章_多表查询

-- 多表查询
-- 需要有链接条件
SELECT employee_id ,department_name
FROM employees,departments
-- 链接条件
WHERE employees.`department_id`=departments.`department_id`;


-- 4：如果查询语句中有出现了多个表中都存在的字段，必须指明字段所在的表
SELECT employees.`employee_id`,departments.`department_name`,employees.`department_id`
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`
-- 建议从优化角度讲，多表查询都带上所在的表


-- 5：可以给表起别名，在 select和where中使用表的别名
SELECT emp.`employee_id`,dept.`department_name`,emp.`department_id`
FROM employees emp,departments dept
WHERE emp.`department_id`=dept.`department_id`
  
  
  
  -- 6：联系 查询员工的employess_id,last_name,departmrnt_name,city
  -- 至少需要n-1个条件
SELECT employee_id,last_name,department_name,city
FROM employees,departments,locations 
WHERE employees.`department_id`=departments.`department_id`
AND departments.`location_id`=locations.`location_id`;



-- 7：多表查询的分类
-- 角度1：等值连接 vs    非等值连接
-- 角度2    自连接    vs 非自连接
-- 角度3 ：内连接 vs    外连接

-- 7.1 等值连接 vs 非等值连接
#非等值链接的例子
SELECT *
FROM JOB_GRADES;

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

-- 7.2 自连接    vs 非自连接
SELECT * FROM employees	;
-- 自连接的例子
# 联系查询员工id，员工姓名，及其管理者的id 和姓名
SELECT emp.`employee_id`,emp.`last_name`,mgr.`employee_id`,mgr.`last_name`
FROM employees emp,employees mgr
WHERE emp.`manager_id`=mgr.`employee_id`

-- 7.3 内连接 vs    外连接
-- 内连接的数据
SELECT emp.`employee_id`,dept.`department_name`,emp.`department_id`
FROM employees emp,departments dept
WHERE emp.`department_id`=dept.`department_id` #需要使用左外连接
# 外连接： 合并具有同一列的两个以上的表的行，结果集中了除了一个表与另一个表匹配的行之外，
-- 还查询到了左表和右表中不匹配的行
 
 #外连接的分类：左外连结，右外连接，全连接
 
-- 练习：查询所有员工的last_name,department_name信息（外连接）
# SQL92 语法实现内连接：见上
#sql92语法实现外连接：使用+
 SELECT emp.`employee_id`,dept.`department_name`,emp.`department_id`
FROM employees emp,departments dept
WHERE emp.`department_id`=dept.`department_id(+)`;

#SQL999语法中使用 JOIN..ON 的方式实现多表的查询。这种方式也能解决外连接的问题。MYSQL支持这种方式
#SQL99语法如何实现多表的查询
#sql99实现内连接：
SELECT last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id`=d.`department_name`;

#sql99实现外连接：
# 练习： 查询所有的员工的last_name,department_name信息

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

#满外连接
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id`=d.`department_id`;

# 8：union和union all的操作
-- union 去重 union all 不去重

# 9：九种join的显示

# 中图 内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id`=d.`department_id`;

# 左上图：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`

# 右上图
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`

# 左中图
SELECT employee_id,department_name,d.`department_id`
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE D.`department_id` IS NULL;
#右中图
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_id`IS NULL;

# 左下图：满外连结
方式一：坐上图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_id`IS NULL;
# 方式二 左中图和右上图
SELECT employee_id,department_name,d.`department_id`
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE D.`department_id` IS NULL;
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`


# 右下图：左中图union all 右中图