MySQL多表查询详解下

吾爱主题阅读：176 2024-04-05 16:22:07 评论：0

　　好好吃饭，好好休息，听着很简单，实际落实缺失不那么容易。

继续回顾mysql的多表查询之1999语法

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 #二，sql1999语法 语法: select 查询列表 from 表1 别名【连接类型】 join 表2 别名 on 链接条件 【 where 筛选条件】 【 group by 分组】 【 having 筛选条件】 【 order by 排序列表 asc | desc 】 分类(连接类型): 内连接(): inner 外联结 左外(): left 【 outer 】 右外(): right 【 outer 】 全外: full 【 outer 】 交叉连接: cross

===============================================================================

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 一、内连接 语法: select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件 【 where 筛选条件】 【 group by 分组】 【 having 筛选分组】 【 order by 排序列表 asc | desc 】 分类: 等值 非等值 自连接 特点: ①添加排序,分组,筛选 ② inner 可以省略 ③筛选条件放在 where 后面,连接条件放在 on 后面,提高分离性,便于阅 读。 ④ inner join 连接和sql1992语法中的等值连接效果是一样的,都是查 询交集部分。

1 2 3 4 5 6 7 8 9 10 #1.等值连接 #案例1.查询员工名,部门名。交换连接条件不影响结果。 select last_name as 员工名,department_name as 部门名 from employees e inner join departments d on e.department_id=d.department_id; select last_name as 员工名,department_name as 部门名 from employees d inner join departments e on e.department_id=d.department_id;

1 2 3 4 5 6 #案例2.查询名字中包含e的员工和工种名(筛选) select last_name as 员工名,job_title as 工种名 from employees e inner join jobs j on e.job_id=j.job_id where e.last_name like '%e%' ;

1 2 3 4 5 6 7 8 #案例3.查询部门个数>3的城市名和部门个数。(分组+筛选) 分步:先把每个城市的部门个数查出来，在筛选满足条件的。 select city as 城市, count (*) as 个数 from locations l inner join departments d on l.location_id=d.location_id group by city having count (*) > 3;

1 2 3 4 5 6 7 8 9 10 11 12 #案例4.查询哪个部门员工个数>3的部门名和员工个数，并按个数降序排序。 分步: 1.查询每个部门的员工个数, 2.在上面的结果上筛选出员工数大于3的记录， 3.把员工数排序 select count (*) as 员工个数,d.department_name as 部门名 from employees e inner join departments d on e.department_id=d.department_id group by d.department_name having count (*) > 3 order by 员工个数 desc ;

1 2 3 4 5 6 7 8 9 #案例5.查询员工名,部门名,工种名,并按部门名降序。三表连接注意条件。 select last_name as 员工名,department_name as 部门名, job_title as 工种名 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 d.department_name desc ;

1 2 3 4 5 6 #2.非等值连接。范围(间接) #案例1.查询员工的工资级别。 select salary as 月薪,grade_level as 工资等级 from employees e inner join job_grades g on e.salary between g.lowest_sal and g.highest_sal;

1 2 3 4 5 6 7 8 #案例2.查询每个工资的级别的个数>20,并且按降序排列。 select j.grade_level as 等级, count (*) 个数 from employees e inner join job_grades j on e.salary between j.lowest_sal and j.highest_sal group by j.grade_level having count (*) > 20 order by 个数 desc ;

1 2 3 4 5 6 #3.自连接 #案例1.查询员工的名字，上级的名字。 select e.last_name as 员工名,m.last_name as 上级名 from employees e inner join employees m on e.manager_id=m.employee_id;

1 2 3 4 5 6 #案例2.查询员工的名字，上级的名字,名字中包含字符k。 select e.last_name as 员工名,m.last_name as 上级名 from employees e inner join employees m on e.manager_id=m.employee_id where e.last_name like '%k%' ;

1 2 3 4 5 6 7 8 9 10 11 12 #二,外连接 应用场景:用于查询一个表中有,另一个表没有的记录。 特点: 1.外连接的查询结果为主表中的所有记录 如果从表中有和它匹配的，则显示匹配的值 如果从表中没有和它匹配的，则显示 null 外连接查询结果=内连接结果+主表中有而从表没有的记录 2.左外连接, left join 左边的是主表 右外连接, right join 右边的是主表 3.左外和右外交换两个表的顺序，可以实现同样的效果。 4.全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表一没有的(mysql不支持)

1 2 3 引入: select * from beauty; select * from boys;

1 2 3 4 5 #引入:查询男朋友不在男神表的女神名 select b. name ,bo.* from beauty b left outer join boys bo on b.boyfriend_id=bo.id;

1 2 3 4 5 6 7 #左外连接 select b. name ,bo.* from beauty b left outer join boys bo on b.boyfriend_id=bo.id where bo.id is null ; #选从表中的主键列

1 2 3 4 5 6 #右外连接，得到同样的结果。 select b. name ,bo.* from boys bo right outer join beauty b on b.boyfriend_id=bo.id where bo.id is null ;

1 2 3 4 5 6 #把beauty表中的id为10行的boyfriend_id从4改到6.才能查询出如下结果。 select b.*,bo.* from boys bo left outer join beauty b on b.boyfriend_id=bo.id where b.id is null ;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 #案例1.哪个部门没有员工。 #左外写法 select d.*,e.employee_id from departments d left outer join employees e on d.department_id=e.department_id where e.department_id is null ; ============================== #右外写法 select d.*,e.employee_id from employees e right outer join departments d on d.department_id=e.department_id where e.employee_id is null ;

1 2 3 4 5 6 7 #全外连接查出的结果有三部分组成(举例，mysql不支持)语法结构演示 先把两张表的交集查出来，在把缺失的填充查出来，在把其他无关联的查出来 语法结构示例 select b.*,bo.* from beauty b full outer join boys bo on b.boyfriend_id=bo.id

1 2 3 4 5 #交叉连接，使用1999的语法标准实现的 笛卡尔乘积 select b.*,bo.* from beauty b cross join boys bo;

1 2 3 #sql1992语法与 sql 1999语法相比 功能:sql1999支持的较多 可读性:sql1999实现连接条件和筛选条件的分离，可读性提高

总结:

1 2 3 4 select <select_list> from a inner join b on a. key =b. key ;

1 2 3 4 select <select_list> from a left join b on a. key =b. key ;

1 2 3 4 select <select_list> from a right join b on a. key =b. key ;

1 2 3 4 5 select <select_list> from a left join b on a. key =b. key where b. key is null ;

1 2 3 4 5 select <select_list> from a right join b on a. key =b. key where a. key is null ;

1 2 3 4 select <select_list> from a full join b on a. key =b. key ;

1 2 3 4 5 6 select <select_list> from a full join b on a. key =b. key where a. key is null or b. key is null ;