MySQL（六）多表关系、多表查询及分类详细讲解（包含笔记和练习）

8.多表查询多表查询概述指从多张表中查询数据8.1 多表关系多表关系在项目开发中在进行数据库表结构设计时会根据业务需求及模块之间的关系分析并设计表结构由于业务之间相互关联所以各个表结构之间也存在着各种联系基本上分为三种一对多多对一多对多一对一8.1.1 一对多多对一案例部门与员工 关系一个部门对应多个员工一个员工对应一个部门 实现在多的一方建立外键指向一的一方的主键8.1.2 多对多案例学生与课程 关系一个学生可以选多门课程一门课程也可以供多个学生选修 实现建立第三张中间表中间表至少包含两个外键分别关联两方主键-- 多对多关系示例学生表 create table student( id int auto_increment primary key comment 主键ID, name varchar(10) comment 姓名, no varchar(10) comment 学号 ) comment 学生表; insert into student values (null, 黛绮丝, 2000100101), (null, 谢逊, 2000100102), (null, 殷天正, 2000100103), (null, 韦一笑, 2000100104); -- 课程表 create table course( id int auto_increment primary key comment 主键ID, name varchar(10) comment 课程名称 ) comment 课程表; insert into course values (null, Java), (null, PHP), (null, MySQL), (null, Hadoop); -- 学生课程中间表实现多对多关系 create table student_course( id int auto_increment comment 主键 primary key, studentid int not null comment 学生ID, courseid int not null comment 课程ID, constraint fk_courseid foreign key (courseid) references course (id), constraint fk_studentid foreign key (studentid) references student (id) ) comment 学生课程中间表; -- 插入学生选课数据 insert into student_course values (null,1,1), (null,1,2), (null,1,3), (null,2,2), (null,2,3), (null,3,4);8.1.3 一对一案例用户与用户详情 关系一对一关系多用于单表拆分将一张表的基础字段放在一张表中其他详情字段放在另一张表中以提升操作效率 实现在任意一方加入外键关联另外一方的主键并且设置外键为唯一的UNIQUE-- 用户基本信息表 create table tb_user( id int auto_increment primary key comment 主键ID, name varchar(10) comment 姓名, age int comment 年龄, gender char(1) comment 1: 男 , 2: 女, phone char(11) comment 手机号 ) comment 用户基本信息表; -- 用户教育信息表一对一关系示例 create table tb_user_edu( id int auto_increment primary key comment 主键ID, degree varchar(20) comment 学历, major varchar(50) comment 专业, primaryschool varchar(50) comment 小学, middleschool varchar(50) comment 中学, university varchar(50) comment 大学, userid int unique comment 用户ID,-- 设置外键为唯一 constraint fk_userid foreign key (userid) references tb_user(id) ) comment 用户教育信息表;8.2 多表查询及分类select * from 表1, 表2;笛卡尔积笛卡尔乘积是指在数学中两个集合A集合和B集合的所有组合情况。再多表查询时需要消除无效的笛卡尔积表1和表2的笛卡尔积等于表1的每一行和表2的所有行合并和的新的表假设表1有m行表2有n行则笛卡尔积有mn行多个表的笛卡尔积就是所有的可能集合构成的表笛卡尔积的行数是各个表行数的乘积一般笛卡尔积是没有意义的我们会加上WHERE条件筛选多表查询分类连接查询内连接相当于查询A、B交集部分数据外连接左外连接查询左表所有数据以及两张表交集部分数据右外连接查询右表所有数据以及两张表交集部分数据自连接当前表与自身的连接查询自连接必须使用表别名子查询8.3 内连接查询内连接查询的是两张表交集的部分内连接分两种隐式内连接与显示内连接隐式内连接SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;-- select 字段列表 from 表1, 表2 where 条件 ...;显式内连接SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;-- select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;注意如果已经为表起了别名执行顺序之后的语句只能通过表的别名访问表不能通过表名访问表否则会报错隐式内连接和显式内连接仅仅是代码上的区别功能上完全一致-- ---------------------------内连接 -- 1、隐式内连接 查询每一个员工的姓名及关联的部门的名称 -- 表结构emp,dept -- select 字段列表 from 表1, 表2 where 条件 ...; -- 连接条件emp.dept_id dept.id; select emp.name,dept.name from emp, dept where emp.dept_id dept.id; -- 起别名 select e.name , d.name from emp as e , dept as d where e.dept_id d.id; -- 2、显示内连接 -- select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...; select e.name, d.name from emp as e inner join dept as d on e.dept_id d.id;8.4 外连接查询外连接分为左外连接和右外连接。左外连接查询左表所有数据以及两张表交集部分数据右外连接查询右表所有数据以及两张表交集部分数据左外连接查询左表所有数据以及两张表交集部分数据SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;-- select 字段列表 from 表1 left [outer] join 表2 on 条件 ...;这里left相当于查询表1左表所有数据 包含 表1和表2交集部分的数据相当于查询表1的所有数据包含表1和表2交集部分数据右外连接查询右表所有数据以及两张表交集部分数据SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;-- select 字段列表 from 表1 right [outer] join 表2 on 条件 ...;这里right相当于查询表2右表的所有数据 包含 表1和表2交集部分的数据注左外连接可以查询到左边表的null字段右外连接可以查询到右边表的null字段-- -----------------------------外连接 -- 1、左外连接 查询emp表中的所有数据和对应的部门信息 select e.*, d.name from emp as e left outer join dept as d on e.dept_id d.id; -- 2、右外连接 查询dept表的所有数据和相应的员工信息 select e.*,d.* from emp as e right outer join dept as d on e.dept_id d.id;8.5 自连接查询自连接查询可以是内连接查询也可以是外连接查询注意当前表与自身的连接查询自连接必须使用表别名SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;-- select 字段列表 from 表A 别名A join 表A 表名B on 条件 ...;-- ---------------------------自连接 -- 查询emp表中员工及其直属领导注自连接查询中必须起别名 -- 表结构emp.a(员工表),emp.b(领导表) select a.name, b.name from emp as a , emp as b where a.managerid b.id; -- 查询所有员工 emp 及其领导的名字 emp如果员工没有领导也需要查询出来所以这里还需要外连接 -- 表结构emp.a(员工表),emp.b(领导表) select a.name as 员工, b.name as 领导 from emp as a left join emp as b on a.managerid b.id;8.6 联合查询-union, union all对于union查询就是把多次查询的结果合并形成一个新的查询集SELECT 字段列表 FROM 表A ... UNION [ALL] SELECT 字段列表 FROM 表B ...-- select 字段 from 表A ... -- union [all] -- select 字段列表 from 表B ...注意UNION ALL 会将全部的数据直接合并在一起UNION 会对合并之后的数据去重对于联合查询的多张表的列数必须保持一致字段类型也需要保持一致。联合查询比使用or效率高不会使索引失效-- ---------------------------联合查询 -- 1、查询薪资低于5000 的员工和 年龄大于50岁的员工全部查询出来 select * from emp where salary 5000 union all select * from emp where age 50;8.7 子查询概念 SQL语句中嵌套SELECT语句称为嵌套查询又称子查询。SELECT * FROM t1 WHERE 列名 ( SELECT 列名 FROM t2);-- select * from t1 where 列名 (select 别名 from t2);注意子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个1根据子查询根据结果不同分为标量子查询子查询结果为单个值列子查询子查询结果为一列行子查询子查询结果为一行表子查询子查询结果为多行多列2根据子查询位置分为WHERE之后、FROM之后、SELECT之后8.7.1 子查询分类1.根据子查询结果可以分为标量子查询子查询结果为单个值列子查询子查询结果为一列行子查询子查询结果为一行表子查询子查询结果为多行多列2.根据子查询位置可分为WHERE 之后FROM 之后SELECT 之后8.7.2 标量子查询标量子查询子查询返回的结果是单个值数字、字符串、日期等最简单的形式这种子查询称为标量子查询。常用操作符 -- 标量子查询 -- 1、查询’销售部的所有成员信息 -- a. 查询‘销售部’部门ID select id from dept where name 销售部;-- 这里查询到的是一个数据所以称为标量查询 -- b. 根据销售部部门ID查询员工信息 select * from emp where dept_id (select id from dept where name 销售部); -- 2、查询在“方东白”入职之后的员工信息 -- a. 查询方东白的入职日期 select entrydate from emp where name 方东白;-- 这里查询到的是一个数据所以称为标量查询 -- b. 查询指定入职日期之后入职的员工信息 select * from emp where entrydate (select entrydate from emp where name 方东白);8.7.3 列子查询返回的结果是一列可以是多行这种子查询称为列子查询常用操作符操作符描述IN在指定的集合范围内多选一NOT IN不在指定的集合范围内ANY子查询返回列表中有任意一个满足即可SOME与ANY等同使用SOME的地方都可以使用ANYALL子查询返回列表的所有值都必须满足-- 列子查询 -- 1、查询“销售部”和“市场部”的所有员工信息 -- a、查询“销售部”和“市场部”的部门ID select id from dept where name 销售部 or name 市场部;-- 这里查询到的是一列多行所以称为列子查询 -- b、根据部门ID查询员工信息 select * from emp where dept_id in (select id from dept where name 销售部 or name 市场部); -- 2、查询比财务部所有人工资都高的员工信息 -- a. 查询财务部人员工资 select id from dept where name 财务部; -- 这里查询到的是一列多行所以称为列子查询 select salary from emp where dept_id (select id from dept where name 财务部); -- b 查询比财务部所有人工资都高的员工信息 select * from emp where salary all(select salary from emp where dept_id (select id from dept where name 财务部)); -- 3、查询比研发部其中任意一人工资高的员工信息 -- a。 查询研发部所有人工资 select salary from emp where dept_id (select id from dept where name 研发部);-- 这里查询到的是一列多行所以称为列子查询 -- b、比研发部其中任意一人工资都高的员工信息 select * from emp where salary any (select salary from emp where dept_id (select id from dept where name 研发部)); select * from emp where salary some (select salary from emp where dept_id (select id from dept where name 研发部));8.7.4 行子查询概念子查询返回的结果是一行可以是多列这种子查询称为行子查询常用操作符 IN NOT IN-- 行子查询 -- 1、查询与“张无忌” 的薪资及其直属领导相同的员工信息 -- a、查询“张无忌”的薪资及其直属领导 select salary, managerid from emp where name 张无忌; -- 子查询返回的结果是一行多列所以称为行子查询 -- b. 查询与“张无忌”的薪资及其直属领导相同的员工信息 select * from emp where (salary, managerid) (select salary, managerid from emp where name 张无忌);8.7.5 表子查询概念:子查询返回的结果是多行多列这种子查询称为表子查询。常用操作符IN示例-- 表子查询 -- 1、查询与“鹿杖客”“宋远桥” 的职位和薪资相同的员工信息 -- a、查询“鹿杖客”“宋远桥”的职位和薪资 select job,salary from emp where name 鹿杖客 or name 宋远桥; -- 子查询返回的结果是多行多列所以称为表子查询 -- b、查询与“鹿杖客””宋远桥“的职位和薪资相同的员工信息 select * from emp where (job,salary) in (select job,salary from emp where name 鹿杖客 or name 宋远桥); -- 2、查询入职日期是“2006-01-01”之后入职的员工信息及其部门信息 -- a、查询入职日期是“2006-01-01“之后的员工信息 select * from emp where entrydate 2006-01-01;-- 子查询返回的结果是多行多列所以称为表子查询 -- b、查询这部分员工对应的部门信息 select e.*,d.* from (select * from emp where entrydate 2006-01-01) as e left join dept as d on e.dept_id d.id;8.8 多表查询综合练习-- 1、查询员工的姓名、年龄、职位、部门信息隐式内连接 -- 表emp、dept select e.name,e.age,e.job,d.name from emp e, dept d where e.dept_id d.id; -- 2、查询年龄小于30的员工的姓名、年龄、职位、部门信息显示内连接 -- 表emp,dept -- 连接条件emp.dept_id dept.id select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id d.id where e.age 30; -- 3、查询拥有员工的部门ID、部门名称(隐式内连接 select distinct d.id, d.name from emp e, dept d where e.dept_id d.id; -- 4、查询所有所有年龄大于40岁的员工及其归属的部门名称如果员工没有分配部门也需要展示出来 -- 表emp ,dpt select e.*, d.name from emp e left outer join dept d on e.dept_id d.id where e.age 40; -- 5、查询所有员工的工资等级 -- 表emp,salgrade -- 连接条件emp.salary salgrade.losal and emp.salary salgrade.hisal select e.*,s.grade,s.losal,s.hisal from emp e , salgrade s where e.salary s.losal and e.salary s.hisal; select e.*, s.grade ,s.losal, s.hisal from emp e, salgrade s where e.salary between s.losal and s.hisal; -- 6、查询“研发部”所有员工的信息及工资等级 -- 表empsalgradedept -- 连接条件e.salary between s.losal and s.hisal , emp.dept_id dept.id; -- 查询条件dept.name 研发部’ select e.*, s.grade from emp e, dept d, salgrade s where e.dept_id d.id and (e.salary between s.losal and s.hisal) and d.name 研发部; -- 7、查询“研发部”员工的平均薪资 -- 表emp, dept select avg(e.salary) from emp e, dept d where e.dept_id d.id and d.name 研发部; -- 8、查询薪资比“灭绝”高的员工信息子查询 -- a. 查询“灭绝”的薪资(标量子查询) select salary from emp where name 灭绝; -- b. 查询比它薪资高的员工 select * from emp where salary (select salary from emp where name 灭绝); -- 9、查询比平均薪资高的员工信息 -- a. 查询员工的平均薪资 select avg(salary) from emp; -- b。查询比平均薪资高的员工信息 select * from emp where salary (select avg(salary) from emp); -- 10、查询低于本部门平均薪资的员工信息 -- a。查询指定部门的平均薪资 select avg(e1.salary) from emp e1 where e1.dept_id 1; -- b、查询低于本部门平均薪资的员工信息 select * from emp e2 where e2.salary (select avg(e1.salary) from emp e1 where e1.dept_id e2.dept_id); -- 11、查询所有的部门信息并统计部门的员工人数 -- a、查询所有部门信息 select id,name from dept; -- b、统计部门员工数 select count(*) from emp where dept_id 1; -- c、 select d.id , d.name , (select count(*) from emp e where e.dept_id d.id) 人数 from dept d; -- 12、查询所有学生的选课情况展示出学生名称学号课程名称 -- 表Student courseStudent_course; -- 连接条件Student.id student_course.studentid , course.id student_course.courseid select s.name, s.no , c.name from student s, student_course sc ,course c where s.id sc.studentid and c.id sc.courseid;