MySQL连接查询

MySQL连接查询

1、含义：又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免发生：添加有效的连接条件

分类：
（1）按年代分类：92版落后了，使用99

（2）sql92标准：1992年的推出的sql语法：
只支持内连接
（也支持一部分外连接用于oracle、sqlserver但是不稳定很少使用）
MySQL不支持外连接

（3）sql99标准：1999年推出的sql标准【推荐】：
支持内连接
外连接（左、右、全外）
交叉连接

按功能分类
内连接：理解成“俩张表中同时符合某种条件的数据记录的组合”
内连接与交叉连接区别在于它有更多的限制条件
内连接例子：
SELECT name,boyName
FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;

等值连接
非等值连接
自连接

外连接：
左外连接
右外连接
全外连接（MySQL不支持）
交叉连接：没有任何限制条件的连接方式，称为交叉连接，交叉连接得到的结果跟线性代数中的迪卡尔承继一样。crooss join,可以省略。

交叉连接例子：
SELECT name,boyName
FROM beauty,boys

---

2、sql92标准

（1）等值连接
拿着第一章表的每一行去匹配第二章的每一行，匹配的时候挨个匹配，然后根据条件筛。

特点：
　　1.用等号连接，最终显示的结果就是两个表的交集部分（多表等值连接的结果为多表的交集部分）
　　2.n表连接，至少要有n-1个条件
　　3.多表的顺序没有要求（from后面的顺序）
　　4.一般需要为表起别名
　　5.连接可以搭配前面介绍的所有子句使用（排序、分组、筛选）

#1.用等号连接

#案例1：查询女神名和对应的男神名
SELECT name,boyName
FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;

#案例2：查询员工名和对应的部门名–#连接条件找两个表中一样的字段
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

#2.为表起别名
1.提高语句的简洁度
2.区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

#查询员工名、工种号、工种名

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

#3.两个表的顺序可以调换
可以调换，因为是用一张表匹配另一张表，相同的值才会筛选出来

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

#4.可以加筛选
#案例：查询有奖金的员工名、部门名

SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id=d.department_id #等值连接
AND e.commission_pct IS NOT NULL;#筛选条件

#查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
AND city LIKE ‘_o%’;

#5.可以加分组
#案例：查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY city;
#案例：查询出有奖金的每个部门的部门名、部门领导编号、该部门最低工资

复制代码
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;
查询两个列不能确定两个列是否一一对应，分组的时候把两个列都加上

#6.可以加排序

#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT()
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT() DESC;

#7.可以实现三表连接

#案例：查询员工名、部门名、所在的城市

SELECT 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
AND city LIKE ‘s%’
ORDER BY department_name DESC;

（2）非等值连接

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

SELECT salary,grade_level
FROM employees e,job_grades g
WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal
– WHERE e.salary>=g.lowest_sal AND e.salary<=g.highest_sal
AND g.grade_level=‘A’;

（3）自连接

相当于等值连接
等值连接涉及到多个表，连接条件为等于

自连接涉及到的表就是它自己
　　一张表筛选多次，把得到的数据通过条件再组成一张表，为了区分要起别名

就是一个表中的数据需要找两遍才能找到想要的数据，
　　一张表中第一遍筛选的数据，是同一张表中第二次筛选的条件
一张表多次使用

#案例：查询员工名和领导名

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

---

#sql 99语法推荐使用

语法：

SELECT 查询列表
FROM 表1 别名
【连接类型】JOIN 表2 别名
ON 连接条件
【WHERE 筛选条件】
【GROUP BY 分组】
【HAVING 筛选条件】(用到分组的时候再使用，没有用到分组直接用where)
【ORDER BY 排序列表】

连接类型的分类：

内连接：inner
外连接
左外:left 【outer】
右外:right 【outer】
全外:full 【outer】
交叉连接:cross

#1.内连接

语法：
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件；

分类：
等值连接
非等值连接
自连接

特点：
1.添加排序、分组、筛选
2.inner可以省略
3.筛选条件放在where后，连接条件放在on后面，提高分离性，便于阅读
4.inner join 连接和SQL92语法中的等值连接效果是一样的，都是查询多表的交集

（1）#1.等值连接
#案例1：查询员工名、部门名

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;
’和92的原理一样都是拿着一张表去匹配另一张表’

#案例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 locations l
ON d.location_id=l.location_id
GROUP BY city
HAVING COUNT()>3;

#案例4：查询那个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
#1.查询每个部门的员工个数

SELECT COUNT(*),department_id
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_id;

#2.在1的基础上筛选员工的个数>3的记录，并排序

SELECT COUNT(),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name
HAVING COUNT()>3
ORDER BY COUNT(*) DESC;

#案例5：查询员工名、部门名、工种名、并按部门名降序（添加三表连接）

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;

（2）#2.非等值连接

#查询员工的工资级别

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

#查询每个工资级别的个数>20的个数，并且按工资级别降序，

SELECT COUNT(),grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT()>20
ORDER BY grade_level DESC;

（3）#3自连接

#查询员工的名字、上级的名字

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

#查询员工的名字、上级的名字

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id=m.manager_id
WHERE e.last_name LIKE ‘k%’;

（4）#内连接：

就是查询两个表的交集

SELECT g.name,b.boyName
FROM beauty g,boys b
WHERE g.boyfriend_id=b.id;

（5）#2.外连接
应用场景：用于查询一个表中有，另一个表没有的记录

左右外连接特点：

1.分主从表
2.外连接的查询结果为主表中的所有记录
　　（
　　　　从表中有和它匹配的，则显示匹配的值
　　　　从表中没有和它匹配的，则显示null
　　　　外连接查询结果=内连接结果+（主表中有，从表中没有）的部分
　　）
3.左外连接：left 左边的是主表
　　右外连接：right 右边的是主表
4.左外和右外，交换两个表的位置可以实现同样的效果
全外连接的特点：
　　全外连接结果=内连接结果+（主表中有，从表中没有）的部分+（从表有，主表没有）的部分
交叉连接的特点：
　　笛卡尔乘积

#案例1：查询哪个部门没有员工
#左外

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

#右外

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

#全外连接

组成部分：
1.交集部分（内连接）
2.主表有从表没有的部分
3.从表有主表没有

USE girls;
SELECT b.,bo.
FROM beauty b
FULL JOIN boys.bo
ON b.boyfriend_id=bo.id;

#交叉连接：笛卡尔乘积

#SQL99
SELECT b.,bo.
FROM beauty b
CROSS JOIN boys bo;

#SQL92
SELECT b.,bo.
FROM beauty b,boys bo