Mysql基础学习（三）连接查询

连接查询

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

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

按功能分类

	内连接：
			等值连接
			非等值连接
			自连接
	外连接：
			左外连接
			右外连接
			全外连接
	交叉连接

Sql 192 标准

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

SELECT name,boyname 
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;

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

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

案例3：为表格使用别名

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

案例4： 查询每个城市的部门个数

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

总结：
① 多表等值连接的结果为多表的交集部分
②n 表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④ 可以搭配前面介绍的所有查询

非等值连接

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

SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

自连接

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

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

SELECT s.name,g.name,g.socre
FROM student s,grade g,result r
WHERE s.id = r.studentNo AND s.gradeId = g.id

SQL99标准

1. 等值连接
   案例1：查询员工名，部门名

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

案例2：查询名字中包含e的员工名和工钟名（添加筛选）

SELECT last_name, job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE e.last_name LIKE '%e%';

案例3: 查询部门个数>3的城市名和部门个数 (分组和筛选)

SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN location l
ON d.location_id = l.location_id
GROUP BY city
HAVING COUNT(*) > 3;

案例4：三表连接， 查询员工名，部门名，工种名，并按照部门名降序

SELECT last_name, department_name, job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;

总结：
① 添加排序，分组，筛选
②inner 可以省略
③ 筛选条件放在 where 后面，连接条件放在 on 后面，提高分离性，便于阅读
④ inner join 连接，和 sql 192 语法中的等值连接是一样。 都是查询交集部分

2. 非等值连接
   案例1 ： 查询员工的工资级别

SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.salay BETWEEN g.lowest_sal AND g.highest_sal;

3. 自连接
   案例1： 查询员工的名字，以及上级的名字

SELECT e.last_name, m.last_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id

4. 外连接
   应用场景： 用于查询一个表中存在，另一个表中没有的记录
   特点：
   • 如果从表中有和他匹配的，则显示匹配的值
   • 如果从表中没有和他匹配的。则显示null
   • 外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录
     左外连接： left join 左边的是主表
     右外连接： right join 右边的是主表。
     全外连接 = 内连接的结果 + 表1 中有，但表2 没有的 + 表2 中有但 表1 中没有的

案例1： 查询女生的男朋友们。没有的显示为nullh
左外连接 LEFT OUTER JOIN

SELECT b.name , bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;

案例2： 查询哪个部门中没有员工

SELECT d.*, e.employee_id
FROM deparments d
LEFT OUTER JOIN employees e 
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

右外查询。 两表位置交换就可以

SELECT d.*, e.employee_id
FROM employees e 
LEFT OUTER JOIN deparments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

5. 交叉连接 等于 笛卡尔乘积查询

SELECT b.*, bo.*
FROM beauty b
CROSS JOIN boys bo;

John总结连接

回顾

SELECT 查询列表
FROM TABLE
WHERE 查询条件 ...
ORDER BY 排序列表 [ASC|DESC]

ASC 升序  默认
DESC 降序
ORDER BY 一般放在查询语句的最后。除了 LIMIT 除外