sql 查询（内外连接） 多表查询

#05排序和分页 
#练习 1，查询员工的姓名和部门号年薪，按照年薪降序 姓名升序进行排列

SELECT last_name,department_id,salary*12 annual_salary
FROM employees
 ORDER BY salary desc ,last_name ASC;

#2.选择工资不在8000到17000的员工和姓名和工资，按照工资降序，显示21到40的位置

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC 
LIMIT 20,20;

#3.查寻邮箱中包含e的员工信息，并先按照员工的邮箱的字节数降序（邮箱的长短，一个字符两个字节），在按照部门号升序

SELECT employee_id,last_name,department_id,email
FROM employees
WHERE email LIKE '%e%' 
ORDER BY LENGTH(emaill) DESC,department_id;

---

# 06章多表查询
#多表查询，也称为关联查询，指两个表或者更多个表一起完成查询操作
#前提条件：这些一起查询的表之间是有关系的（一对一 一对多），他们之间一定是有
#关联字段，这个关联字段可能设立了外键，也可能没有设立外键，比如员工表和部门表
#这两个表依靠部门编号进行关联
#学习中常见的几个表

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;

#上面的sql使用了三张表进行工作城市查询 比较繁琐

#2.多表的查询如何实现 出现笛卡尔积的错误
#错误的实现方式：每个员工斗鱼每个部门匹配了一遍
#错误的原因：缺少了多表的链接条件
#笛卡尔积的理解（或者交叉链接）
#笛卡尔积是一个数学运算，假设我们有两个集合x和y    ，那么x和y的笛卡尔积就是x和
#y的所有可能的组合，也就是第一个对象来自于x第二个对象来自于y的所有的可能。
#组合的个数即为两个集合中元素个数的乘积

SELECT employee_id,department_name
FROM employees,departments; #查询出2899条记录

SELECT *FROM employees;#107条记录 
SELECT 2889/107 FROM DUAL; 


SELECT*FROM departments;#27条记录

#3.多表查询的正确方式 需要有链接条件

SELECT employee_id,department_name
FROM employees,departments
#两个表的链接条件
WHERE employees.`department_id`=departments.department_id;

#4.如果查询语句中出现了多个表中都存在的字段，则必须指明此字段存在的表。
SELECT employee_id,department_name,employees.department_id
FROM employees ,departments
WHERE employees.department_id=departments.department_id;

#建议从sql优化的角度，建议多表查询时，每个字段前都指明所在的表。
#5.可以给表起别名，在select和where中使用表的别名 格式与给列起别名相同
#如果给表起了别名 一旦在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  结论：查询员工的 employee_id,last_name,department_name ,city
#如果有n个表实现多表的查询，则需要至少n-1个链接条件
SELECT employee_id,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;

/*
提出问题1---》解决问题
归纳式：由总到分

*/

---

#7.多表查询的分类
/*
角度1：等值连接和非等值连接
角度2：自连接 和非自连接
角度3：内连接 外连接
*/
#71.等值连接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;
WHERE e.salary>=j.lowest_sal && e.salary<=j.lowest_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内连接和外连接
#内连接：合并具有同一列的两个以上的表的行数，**结果集中不包含一个表与另一个表不匹配的**
SELECT employee_id,department_name
from employees e,departments d
WHERE e.department_id=d.department_id;#只有106条记录

#外连接：合并具有同一列的两个以上的表的行数，结果集中除了包含一个表与另一个表匹配的行之外
#还查询到了 左表或者右表中不匹配的行。
#外连接的分类： 左外连接 和 右外连接 ，满外连接
#左外连接 ：两个表在连接的过程中除了返回满足连接条件的行以外还返回左表中
#不满足条件的行这种连接称为左外连接

#右外连接： 两个表在连接的过程中除了返回满足条件的行以外还返回了右表中
#不满足条件的行，这种情况称为右外连接。

#查询所有的员工的last_name ,department_name

SELECT employee_id,department_name
FROM employees e,departments d
WHERE  e.department_id=d.department_id(+);#需要使用左外连接

#SQL92语法实现内连接：见上 略
#SQL92语法实现外连接：使用+ ---- mysql不支持sql92外连接中的写法
#SQL99语法中使用 JOIN...ON的方式实现多表的查询。这种方式也能解决外连接的问题。mysql是支持这种方式的
#SQL99语法如何实现多表的查询。

#SQL99语法实现内连接

SELECT last_name,department_name
from employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

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

#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 RIGHT OUTER JOIN departments d
ON e.department_id =d.department_id;

#满外连接： mysql不支持full outer join的方式
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id=d.department;

#8.UNION 和 UNIOall 的使用
#UNION 回执行去重的操作
#UNIONall 不会执行驱虫的操作
#结论：如果明确的直到合并数据后的结果不存在重复数据，或者不需要去除重复的数据
#尽量使用union all语句，可以提高数据查询的效率。

#9 7种join的实现：

 

#中图：内连接
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
FROM employees e LEFT JOIN departments d
ON d.department_id =d.department_id
WHERE d.department_id is NULL;

#中图：内连接
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
FROM employees e LEFT JOIN departments d
ON d.department_id =d.department_id
WHERE d.department_id is NULL;

#方式2：左中图 UNIONall 右上图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE 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;

#右下图 左中图 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 e.department_id is NULL;