多表查询

#
/**
select .....
from .....数据库
where 限制条件
order by 排序 desc 降序 asc升序
limit 切片 初始值 偏移量 偏移量 offset 初始值 初始值一般位为要求的-1
*/
#熟悉的常见的表
DESC employees;
DESC departments;
DESC locations;
#需求查询名字为abel的名字所在的城市
SELECT *
FROM employees
WHERE last_name = 'Abel';
SELECT *
FROM departments
WHERE department_id = 80;

SELECT *
FROM locations
WHERE location_id = 2500;
#多表查询如何实现？
#错误的实现方式 出现了笛卡尔积错误
SELECT employee_id,last_name
FROM employees,departments;#查询出2889条记录
#错误的原因：缺少了多表连接的条件
#下面为正确的使用方法：
SELECT employee_id,last_name
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

SELECT employees.employee_id,department_name,department_id
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;
#sql优化：建议在多表查询的时候，在每个需要查询的字段前面到加上各自的表

#标注完所用到的表以后发现sql语句冗长，失去了可读性
#解决方法：给表起别名 别名以后只能使用别名（select where中）
SELECT emp.employee_id,emp.last_name
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.`department_id`;

#6.如果有n个表需要连接查询，则需要至少n-1个连接条件
#练习：查询员工的 employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_id,l.city
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;
#7.多表查询的分类
/**
角度一：等值连接 vs 非等值连接
角度二：自连接 vs 非自连接
角度三： 内连接 vs 外连接
*/
#7.1 等值连接 vs 非等值连接
#等值连接
SELECT *
FROM job_grades;
#非等值连接
SELECT last_name,salary,grade_level
FROM employees e,job_grades j
WHERE e.`salary`BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#7.2 自连接 vs 非自连接
#练习： 查询员工姓名及其管理者的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`;

#内连接 vs 外连接
#内连接，合并具有同一列的两个以上的表的行，结果集中不包含一个表与另外一个表不匹配的行
SELECT employee_id,department_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`;

#外连接 左外连接、右外连接、全外连接
# 左外连接
#查询所有的员工的last——name，deapartment_id 信息 (左外连接)
SELECT employee_id,department_id
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;
# 所有的字眼 就是外连接
/*
SELECT e.employee_id,e.department_id
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`(+);
#WHERE e.`department_id` = d.`department_id`;
#需要使用左外连接
mysql不支持sql92语法 WHERE e.`department_id` = d.`department_id`(+);
*/
#sql99语法 使用 join on 的方式实现多表查询，这种方式也能解决外连接的问题，并且mysql支持这种语法

#sql99 语法 如何实现多表查询的：
SELECT e.last_name,d.department_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
#多表查询
SELECT e.last_name,d.department_id,city
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations j
ON d.`location_id` = j.`location_id`;

#SQL99 语法实现外连接：
#内连接 inner 连接词
SELECT e.last_name,d.department_name
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;
#左外连接 outer 可以省略
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 JOIN departments d
ON e.`department_id` = d.`department_id`;
#满外连接 MySql 不支持 full outer join 来表示满外连接
/*SELECT e.last_name,d.department_name
FROM employees e full outer JOIN departments d
ON e.`department_id` = d.`department_id`;
output: error
*/
#8. union union all 的区别 一个效率高 一个效率低 union all 需要去重，效率低
# 中图：内连接
SELECT e.employee_id,d.department_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
#左上图
SELECT employee_id,department_id
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图
SELECT e.employee_id,d.department_id
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中图
SELECT e.employee_id,d.department_id
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;
#右中图
SELECT e.employee_id,d.department_id
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
#左下图
#方式一：满外连接 左上图 union all 有中图
SELECT e.employee_id,d.department_id
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT e.employee_id,d.department_id
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
#方拾贰 左中图 右上图
SELECT e.employee_id,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 e.employee_id,d.department_id
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
# SQL99 语法新特性
#sql92语法
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`;
#SQL99 自然连接 会自动把属性相等的属性进行连接
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
#using的使用
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);