-- 1. 查询 教研部 和 咨询部 的部门ID select id from dept where name 教研部 or name 咨询部; -- 结果: 3,2 -- 2. 根据上面查询出来的部门ID, 查询员工信息 select * from emp where dept_id in(3,2); -- 3. 合并SQL为一条SQL语句 select * from emp where dept_id in (select id from dept where name 教研部 or name 咨询部);一多表关系项目开发中在进行数据库表结构设计时会根据业务需求及业务模块之间的关系分析并设计表结构由于业务之间相互关联所以各个表结构之间也存在着各种联系基本上分为三种一对多(多对一)多对多一对一一对多场景部门与员工的关系一个部门下有多个员工。由于一个部门下会关联多个员工。 而一个员工是归属于某一个部门的 。那么此时我们就需要在emp表中增加一个字段dept_id来标识这个员工属于哪一个部门dept_id关联的是dept的id。 如下所示上述的emp员工表的dept_id字段关联的是dept部门表的id。部门表是一的一方也称为父表员工表是多的一方称之为子表。一对多的表关系在数据库层面实现方式在数据库表中多的一方添加字段来关联一的一方的主键 。一对一一对一关系表在实际开发中应用起来比较简单通常是用来做单表的拆分也就是将一张大表拆分成两张小表将大表中的一些基础字段放在一张表当中将其他的字段放在另外一张表当中以此来提高数据的操作效率。一对一的应用场景 用户表(基本信息身份信息)基本信息用户的ID、姓名、性别、手机号、学历身份信息民族、生日、身份证号、身份证签发机关身份证的有效期(开始时间、结束时间)如果在业务系统当中对用户的基本信息查询频率特别的高但是对于用户的身份信息查询频率很低此时出于提高查询效率的考虑我就可以将这张大表拆分成两张小表第一张表存放的是用户的基本信息而第二张表存放的就是用户的身份信息。他们两者之间一对一的关系一个用户只能对应一个身份证而一个身份证也只能关联一个用户。通过外键来体现一对一之间的关系只需要在任意一方来添加一个外键就可以了。多对多多对多的关系在开发中属于也比较常见的。比如学生和老师的关系一个学生可以有多个授课老师一个授课老师也可以有多个学生。在比如学生和课程的关系一个学生可以选修多门课程一个课程也可以供多个学生选修。案例学生与课程的关系关系一个学生可以选修多门课程一门课程也可以供多个学生选择实现关系建立第三张中间表中间表至少包含两个外键分别关联两方主键多对多 需要建立一张中间表中间表中有两个外键字段分别关联两方的主键。二多表查询1.介绍多表查询查询时从多张表中获取所需数据单表查询的SQL语句select 字段列表 from 表名;那么要执行多表查询只需要使用逗号分隔多张表即可如 select 字段列表 from 表1, 表2;查询用户表和部门表中的数据select * from emp , dept;此时我们看到查询结果中包含了大量的结果集总共180条记录而这其实就是员工表所有的记录(30行)与部门表所有记录(6行)的所有组合情况这种现象称之为笛卡尔积。笛卡尔积笛卡尔乘积是指在数学中两个集合(A集合和B集合)的所有组合情况。在多表查询时需要消除无效的笛卡尔积只保留表关联部分的数据。在SQL语句中去除无效的笛卡尔积只需要给多表查询加上连接查询的条件即可。select * from emp , dept where emp.dept_id dept.id ;2.分类多表查询可以分为连接查询内连接相当于查询A、B交集部分数据外连接左外连接查询左表所有数据(包括两张表交集部分数据)右外连接查询右表所有数据(包括两张表交集部分数据)子查询3.内连接内连接查询查询两表或多表中交集部分数据。内连接从语法上可以分为隐式内连接显式内连接隐式内连接语法select 字段列表 from 表1 , 表2 where 条件 ... ;显式内连接语法select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;案例1查询所有员工的ID姓名及所属的部门名称隐式内连接实现select emp.id, emp.name, dept.name from emp , dept where emp.dept_id dept.id;显式内连接实现select emp.id, emp.name, dept.name from emp inner join dept on emp.dept_id dept.id;在多表联查时我们指定字段时需要在字段名前面加上表名来指定具体是哪一张的字段。 如emp.dept_id给表起别名简化书写select 字段列表 from 表1 as 别名1 , 表2 as 别名2 where 条件 ... ; select 字段列表 from 表1 别名1 , 表2 别名2 where 条件 ... ; -- as 可以省略4.外连接外连接分为两种左外连接 和 右外连接。左外连接语法select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;左外连接相当于查询表1(左表)的所有数据当然也包含表1和表2交集部分的数据。右外连接语法select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;右外连接相当于查询表2(右表)的所有数据当然也包含表1和表2交集部分的数据。案例1查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)-- 左外连接以left join关键字左边的表为主表查询主表中所有数据以及和主表匹配的右边表中的数据 select e.name , d.name from emp as e left join dept as d on e.dept_id d.id ;案例2查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)-- 右外连接以right join关键字右边的表为主表查询主表中所有数据以及和主表匹配的左边表中的数据 select e.name , d.name from emp as e right join dept as d on e.dept_id d.id;注意事项左外连接和右外连接是可以相互替换的只需要调整连接查询时SQL语句中表的先后顺序就可以了。而我们在日常开发使用时更偏向于左外连接。5.子查询5.1.介绍SQL语句中嵌套select语句称为嵌套查询又称子查询。SELECT * FROM t1 WHERE column1 ( SELECT column1 FROM t2 ... );子查询外部的语句可以是insert / update / delete / select 的任何一个最常见的是 select。根据子查询结果的不同分为标量子查询子查询结果为单个值 [一行一列]列子查询子查询结果为一列但可以是多行行子查询子查询结果为一行但可以是多列表子查询子查询结果为多行多列[相当于子查询结果是一张表]子查询可以书写的位置where之后from之后select之后子查询的要点是先对需求做拆分明确具体的步骤然后再逐条编写SQL语句。 最终将多条SQL语句合并为一条。5.2.标量子查询子查询返回的结果是单个值(数字、字符串、日期等)最简单的形式这种子查询称为标量子查询。常用的操作符 案例1查询 最早入职 的员工信息-- 1. 查询最早的入职时间 select min(entry_date) from emp; -- 结果: 2000-01-01 -- 2. 查询入职时间 最早入职时间的员工信息 select * from emp where entry_date 2000-01-01; -- 3. 合并为一条SQL select * from emp where entry_date (select min(entry_date) from emp);5.3.列子查询子查询返回的结果是一列(可以是多行)这种子查询称为列子查询。常用的操作符操作符描述in在指定的集合范围之内多选一not in不在指定的集合范围之内案例1查询 教研部 和 咨询部 的所有员工信息-- 1. 查询 教研部 和 咨询部 的部门ID select id from dept where name 教研部 or name 咨询部; -- 结果: 3,2 -- 2. 根据上面查询出来的部门ID, 查询员工信息 select * from emp where dept_id in(3,2); -- 3. 合并SQL为一条SQL语句 select * from emp where dept_id in (select id from dept where name 教研部 or name 咨询部);5.4.行子查询子查询返回的结果是一行(可以是多列)这种子查询称为行子查询。常用的操作符 、 、IN 、NOT IN案例1查询与 李忠 的薪资 及 职位都相同的员工信息-- 1. 查询 李忠 的薪资和职位 select salary , job from emp where name 李忠; -- 结果: 5000, 5 -- 2. 根据上述查询到的薪资和职位 , 查询对应员工的信息 select * from emp where (salary, job) (5000,5); -- 3. 将两条SQL合并为一条SQL select * from emp where (salary, job) (select salary , job from emp where name 李忠);5.5表子查询子查询返回的结果是多行多列常作为临时表这种子查询称为表子查询。案例获取每个部门中薪资最高的员工信息-- a. 获取每个部门的最高薪资 select dept_id, max(salary) from emp group by dept_id; -- b. 查询每个部门中薪资最高的员工信息 select * from emp e , (select dept_id, max(salary) max_sal from emp group by dept_id) a where e.dept_id a.dept_id and e.salary a.max_sal;