四、mysql连接查询

参考url:https://www.bilibili.com/video/BV12b411K7Zu

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

笛卡尔乘积现象：表１有m行，表2有n行，结果等于m*n行

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

分类：

　　按年代分类：

　　　　Sql92标准：仅仅支持内连接

　　　　Sql99标准【推荐】：支持内连接＋外链接(左外和右外)＋交叉连接

　　　　语法：

　　　　　　SELECT 查询列表

　　　　　　FROM 表１　别名　【连接类型】

　　　　　　JOIN 　表2 别名

　　　　　　ON   连接条件

　　　　　　【WHERE 筛选条件】

　　　　　　【group by 分组】

　　　　　　【having 筛选条件】

　　　　　　【order by 排序列表】

　　按功能分类：

　　　　内连接：inner

　　　　　　等值连接

　　　　　　非等值连接

　　　　　　自连接

　　　　外连接：

　　　　　　左外连接left [outer]

　　　　　　右外连接right[outer]

　　　　　　全外连接full[outer]

　　　　交叉连接cross

1、 等值连接

　　(1) 多表等值连接的结果为多表的交集部分

　　(2) N表连接，至少需要n-1个连接条件

　　(3) 多表的顺序没有要求

　　(4) 一般需要为表起别名

　　(5) 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

　　案例１：查询女孩和对应的男孩名

　　　　　　SELECT name,boyname

　　　　　　FROM boys,beauty

　　　　　　WHERE beauty.boyfriend_id=boys.id;

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

　　　　　　SELECT last_name,department_name

　　　　　　FROM employees,departments

　　　　　　WHERE employees.department_id = departments.department_id;

　　为表起别名：提高语句的简洁度，区分多个重名字段避免歧义

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

　　案例：查询员工名、工种号，工种民

　　　　　　SELECT 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.employees_id,e.last_name,m.employees,m.last_name

　　　　　　FROM employees e,employees m

　　　　　　WHERE e.manager_id=m.employee_id;

4、 内连接

　　语法：

　　　　SELECT 查询列表

　　　　FROM 表１　别名

　　　　Inner join 表２　别名

　　　　On 连接条件;

　　特点：

　　　　1) 添加排序、分组、筛选

　　　　2) INNER可以省略

　　　　3) 筛选条件放在WHERE后面，连接条件放在ON后面，提高分离性，便于阅读

　　　　4) INNER JOIN连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

　　(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％’;

　　(2) 非等值连接

　　　　　　# 查询员工的工资级别

　　　　　　SELECT salary,grade_level

　　　　　　FROM employees e

　　　　　　INNER JOIN job_grades g

　　　　　　ON e.salary BETWEEN g.lowest_sal AND g.highest_sal；

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

　　　　　　SELECT COUNT(*),grade_level

　　　　　　FROM employees e

　　　　　　INNER JOIN job_grades g

　　　　　　ON e.salary BETWEEN g.lowest_sal AND g.highest_sal

　　　　　　GROUP BY grade_levels

　　　　　　HAVING COUNT(*)>20

　　　　　　ORDER BY grade_level DESC;

　　(3) 自连接

　　　　　　SELECT e.last_name,m.last_name

　　　　　　FROM employees e

　　　　　　INNER JOIN employees m

　　　　　　ON e.manager_id=m.employee_id;

5、 外连接

　　应用场景：查询在一个表中有，另一个表中没有的记录

　　特点：

　　　　(1) 外连接的查询结果为主表中的所有记录

　　　　　　如果从表中有和它匹配的，则显示匹配的值

　　　　　　如果从表中没有和它匹配的，则显示为NULL

　　　　　　外连接查询结果＝内连接结果＋主表中有而从表没有的记录

　　　　(2) 左外连接，left join左边的是主表

　　　　　　右外连接，right join右边的是主表

　　　　(3) 左外和右外交换两个表的顺序，可以实现同样的效果

　　　　(4) 全外连接＝内连接的结果＋表１中有但表２没有的＋表２中有但表１没有的

　　　　＃查询男朋友不再男生表的女生名

　　　　　　SELECT b.name,bo.*

　　　　　　FROM beauty b

　　　　　　LEFT OUTER JOIN boys bo

　　　　　　ON b.boyfriend_id=bo.id;

　　　　6) 全外连接

　　　　7) 交叉连接

　　　　＃实现笛卡尔乘积

　　　　　　SELECT b.*,bo.*

　　　　　　FROM beauty b

　　　　　　CROSS JOIN boys bo;