一、多表查询一多表关系在项目开发中进行数据库结构设计时会根据业务需求和业务模块之间的关系设计表结构由于业务之间的练习表结构之间也会存在联系基本分为3种1.一对多多对一案例部门和员工的关系关系一个部门对应多个员工一个员工对应一个部门实现在多的一方建立外键指向一的一方的主键2.多对多案例学生和课程的关系关系一个学生可以选择多个课程一门课程也可以供多个学生选择实现建立第三张中间表中间表至少包括两个外键分别关联两方主键练习-- 学生表 create table student( id int auto_increment primary key comment 主键id, name varchar(20) comment 姓名, no varchar(10) comment 学号 )comment 学生表; insert into student values (null, 张三, 2026061701),(null 李四, 2026061702); -- 课程表 create table course( id int auto_increment primary key comment 主键id, name varchar(10) comment 课程名称 )comment 课程表; insert into course values(null, 语文), (null, 数学); -- 中间表 create table studnet_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) reference course (id), constraint fk_studentid foreign key (studentd) reference student (id) )comment 学生课程中间表; insert into student_course values (null,1,1),(null,1,2),(null,2,1),(null,2,2);3.一对一案例用户和用户详情的关系关系一对一的关系多用于单表拆分将一张表的基础字段放在一张表种其他详情字段放在另一张表中用来提升操作效率。实现在任意一方加入外键关联另外一方的主键并设置外键是唯一的UNIQUE。练习-- 用户基本信息 create table tb_user( id int auto_increment primary key comment 主键id, name varchar(10) comment 姓名, age int comment 年龄, gender char(1) comnment 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 专业, userid int unique comment 用户id, constraint fk_userid foreign key (userid) references tb_user(id) )comment 用户教育信息表;二多表查询概述1.概述在多表查询时需要消除无效的笛卡尔积笛卡尔积集合A和集合B所有的组合情况select * from employ, dept with employ.id dept.id;不满足连接条件的数据将不会查询到2.多表查询分类1连接查询内连接查询A、B交集部分数据外连接左外连接查询左表所有数据以及两张表交集部分数据右外连接查询右表所有数据以及两张表交集部分数据自连接当前表与自身的连接查询自连接必须使用表别名2子查询在一个查询语句中嵌套另一个查询语句三内连接内连接查询的是两张表交集部分的数据隐式内连接和显式内连接的区别内连接查询语法1.隐式内连接SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;2.显式内连接SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;内连接查询的时两张表交集的部分练习查询每个员工的姓名和关联部门的名称隐式内连接-- 员工表employ部门表dept -- 连接条件employ.dept_id dept.id -- 隐式内连接 select employ.name, dept.name from employ, dept where employ.dept_id dept.id; -- 使用别名 select e.name, d.name from employ e,dept d where e.dept_id d.id;显式内连接-- 显式内连接 ...INNER JOIN...ON...(INNER可省略 select e.name, d.name from employ e inner join dept d on e.dept d.id;注意使用别名后不能再用表名限定字段拓展Oracle表连接四外连接1.左外连接SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;相当于查询表1的所有数据包含表1和表2交集部分的数据2.右外连接SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;相当于查询表2的所有数据包含表1和表2交集部分的数据练习-- 左外连接 -- 查询employ表的所有数据和对应的部门信息 select e.*, d.name from employ e left outer join dept d on e.dept_idd.dept_id; -- 右外连接 -- 查询dept表所有数据和对应的员工表的信息 select e.*, d.* from employ e right outer join dept d on e.dept_idd.dept_id;五自连接SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;自连接查询可以是内连接查询也可以是外连接查询。练习查询员工的领导员工信息中有所属领导id-- 查询员工及其领导的名字内连接 select a.name 员工, b.name领导 from employ a, employ b where a.manageridb.id; -- 查询所有员工及其领导的名字如果员工没有领导也需要查询出来外连接 select a.name 员工, b.name领导 from employ a left join employ b on a.manageridb.id;六联合查询把多次查询的结果合并起来形成一个新的查询结果集关键字union (all)union all直接合并多次查询的结果union合并后去重使用要求查询语句的列的数量要保持一致字段类型也要保持一致SELECT 字段列表 FROM 表A... UNION [ALL] SELECT 字段列表 FROM 表B...练习-- 将薪资低于5000的员工和年龄大于50岁的员工全部查询出来 -- 将两次查询的结果合并在一起可能会出现重复内容 select * from employ where salary 5000 union all select * from employ where age 50; -- 去重去掉all select * from employ where salary 5000 union select * from employ where age 50;七子查询SQL语句中嵌套SELECT语句成为嵌套查询又称子查询。SELECT * FROM t1 WHERE column1 SELECT column1 FROM t2);子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个根据子查询结果不同分为标量子查询子查询结果为单个值列子查询子查询结果为一列行子查询子查询结果为一行表子查询子查询结果为多行多列根据子查询位置分为WHERE之后FROM之后SELECT之后1.标量子查询1概念子查询返回的结果是单个值数字、字符串、日期等最简单的形式这种子查询称为标量子查询。2常用操作符 3练习-- 查询销售部所有员工信息 -- 拆解 select * from dept where name 销售部; select * from employ where dept_id 4; --合并 select * from employ where dept_id (select * from dept where name 销售部); --查询在“张东白”入职后的员工信息 --拆解 select entrydate from employ where name 张东白; select * from employ where entrydate 2009-07-01; --合并 select * from employ where entrydate (select entrydate from employ where name 张东白);2.列子查询1概念子查询返回的结果事一列可以是多行这种子查询成为列子查询2常用的操作符IN NOT IN ANY SOME ALL操作符描述IN在指定的集合范围之内多选一NOT IN不在指定的集合范围之内ANY子查询返回列表中有任意一个满足条件即可SOME与ANY等同ALL子查询返回列表的所有值都必须满足3练习--1.查询“销售部”和“市场部”所有员工信息 select * from employ where dept_id in (select id from dept where name 销售部 or name 市场部); --2.查询比财务部所有人工资都高的员工的信息 select * from employ where salary all ( select salary from employ where dept_id (select id from dept where name 财务部)); --3.查询比研发部任意一个人工资高的员工的信息 select * from employ where salary any ( select salary from employ where dept_id (select id from dept where name 研发部)); --any也可以用some3.行子查询1概念子查询返回的结果是一行可以是多列这种子查询称为行子查询。2常用操作符 IN NOT IN3练习--1.查询与“张一”的薪资及直属领导相同的员工的信息 --拆分 select salary, managerid from employ where name 张一; select * from employ where (salary, managerid) (12500, 1); --合并 select * from employ where (salary, managerid) (select salary, managerid from employ where name 张一);4.表子查询1概念子查询返回的结果是多行多列这种子查询称为表子查询。2常用操作符IN3练习--1.查询与李一、李二职位和薪资都相同的员工的信息 select * from employ where (job, salary) in (select job, salary from employ where name 李一 or name 李二); --2.查询入职日期在2010年之后的员工信息及部门信息 select * from (select * from employ where entrydate 2010-01-01) e left join dept d on e.dept_id d.id; 
长河君 · 2026/10/24 2:01:38 · 阅读 1.2k MySQL学习笔记（part 3：多表查询）
一、多表查询一多表关系在项目开发中进行数据库结构设计时会根据业务需求和业务模块之间的关系设计表结构由于业务之间的练习表结构之间也会存在联系基本分为3种1.一对多多对一案例部门和员工的关系关系一个部门对应多个员工一个员工对应一个部门实现在多的一方建立外键指向一的一方的主键2.多对多案例学生和课程的关系关系一个学生可以选择多个课程一门课程也可以供多个学生选择实现建立第三张中间表中间表至少包括两个外键分别关联两方主键练习-- 学生表 create table student( id int auto_increment primary key comment 主键id, name varchar(20) comment 姓名, no varchar(10) comment 学号 )comment 学生表; insert into student values (null, 张三, 2026061701),(null 李四, 2026061702); -- 课程表 create table course( id int auto_increment primary key comment 主键id, name varchar(10) comment 课程名称 )comment 课程表; insert into course values(null, 语文), (null, 数学); -- 中间表 create table studnet_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) reference course (id), constraint fk_studentid foreign key (studentd) reference student (id) )comment 学生课程中间表; insert into student_course values (null,1,1),(null,1,2),(null,2,1),(null,2,2);3.一对一案例用户和用户详情的关系关系一对一的关系多用于单表拆分将一张表的基础字段放在一张表种其他详情字段放在另一张表中用来提升操作效率。实现在任意一方加入外键关联另外一方的主键并设置外键是唯一的UNIQUE。练习-- 用户基本信息 create table tb_user( id int auto_increment primary key comment 主键id, name varchar(10) comment 姓名, age int comment 年龄, gender char(1) comnment 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 专业, userid int unique comment 用户id, constraint fk_userid foreign key (userid) references tb_user(id) )comment 用户教育信息表;二多表查询概述1.概述在多表查询时需要消除无效的笛卡尔积笛卡尔积集合A和集合B所有的组合情况select * from employ, dept with employ.id dept.id;不满足连接条件的数据将不会查询到2.多表查询分类1连接查询内连接查询A、B交集部分数据外连接左外连接查询左表所有数据以及两张表交集部分数据右外连接查询右表所有数据以及两张表交集部分数据自连接当前表与自身的连接查询自连接必须使用表别名2子查询在一个查询语句中嵌套另一个查询语句三内连接内连接查询的是两张表交集部分的数据隐式内连接和显式内连接的区别内连接查询语法1.隐式内连接SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;2.显式内连接SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;内连接查询的时两张表交集的部分练习查询每个员工的姓名和关联部门的名称隐式内连接-- 员工表employ部门表dept -- 连接条件employ.dept_id dept.id -- 隐式内连接 select employ.name, dept.name from employ, dept where employ.dept_id dept.id; -- 使用别名 select e.name, d.name from employ e,dept d where e.dept_id d.id;显式内连接-- 显式内连接 ...INNER JOIN...ON...(INNER可省略 select e.name, d.name from employ e inner join dept d on e.dept d.id;注意使用别名后不能再用表名限定字段拓展Oracle表连接四外连接1.左外连接SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;相当于查询表1的所有数据包含表1和表2交集部分的数据2.右外连接SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;相当于查询表2的所有数据包含表1和表2交集部分的数据练习-- 左外连接 -- 查询employ表的所有数据和对应的部门信息 select e.*, d.name from employ e left outer join dept d on e.dept_idd.dept_id; -- 右外连接 -- 查询dept表所有数据和对应的员工表的信息 select e.*, d.* from employ e right outer join dept d on e.dept_idd.dept_id;五自连接SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;自连接查询可以是内连接查询也可以是外连接查询。练习查询员工的领导员工信息中有所属领导id-- 查询员工及其领导的名字内连接 select a.name 员工, b.name领导 from employ a, employ b where a.manageridb.id; -- 查询所有员工及其领导的名字如果员工没有领导也需要查询出来外连接 select a.name 员工, b.name领导 from employ a left join employ b on a.manageridb.id;六联合查询把多次查询的结果合并起来形成一个新的查询结果集关键字union (all)union all直接合并多次查询的结果union合并后去重使用要求查询语句的列的数量要保持一致字段类型也要保持一致SELECT 字段列表 FROM 表A... UNION [ALL] SELECT 字段列表 FROM 表B...练习-- 将薪资低于5000的员工和年龄大于50岁的员工全部查询出来 -- 将两次查询的结果合并在一起可能会出现重复内容 select * from employ where salary 5000 union all select * from employ where age 50; -- 去重去掉all select * from employ where salary 5000 union select * from employ where age 50;七子查询SQL语句中嵌套SELECT语句成为嵌套查询又称子查询。SELECT * FROM t1 WHERE column1 SELECT column1 FROM t2);子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个根据子查询结果不同分为标量子查询子查询结果为单个值列子查询子查询结果为一列行子查询子查询结果为一行表子查询子查询结果为多行多列根据子查询位置分为WHERE之后FROM之后SELECT之后1.标量子查询1概念子查询返回的结果是单个值数字、字符串、日期等最简单的形式这种子查询称为标量子查询。2常用操作符 3练习-- 查询销售部所有员工信息 -- 拆解 select * from dept where name 销售部; select * from employ where dept_id 4; --合并 select * from employ where dept_id (select * from dept where name 销售部); --查询在“张东白”入职后的员工信息 --拆解 select entrydate from employ where name 张东白; select * from employ where entrydate 2009-07-01; --合并 select * from employ where entrydate (select entrydate from employ where name 张东白);2.列子查询1概念子查询返回的结果事一列可以是多行这种子查询成为列子查询2常用的操作符IN NOT IN ANY SOME ALL操作符描述IN在指定的集合范围之内多选一NOT IN不在指定的集合范围之内ANY子查询返回列表中有任意一个满足条件即可SOME与ANY等同ALL子查询返回列表的所有值都必须满足3练习--1.查询“销售部”和“市场部”所有员工信息 select * from employ where dept_id in (select id from dept where name 销售部 or name 市场部); --2.查询比财务部所有人工资都高的员工的信息 select * from employ where salary all ( select salary from employ where dept_id (select id from dept where name 财务部)); --3.查询比研发部任意一个人工资高的员工的信息 select * from employ where salary any ( select salary from employ where dept_id (select id from dept where name 研发部)); --any也可以用some3.行子查询1概念子查询返回的结果是一行可以是多列这种子查询称为行子查询。2常用操作符 IN NOT IN3练习--1.查询与“张一”的薪资及直属领导相同的员工的信息 --拆分 select salary, managerid from employ where name 张一; select * from employ where (salary, managerid) (12500, 1); --合并 select * from employ where (salary, managerid) (select salary, managerid from employ where name 张一);4.表子查询1概念子查询返回的结果是多行多列这种子查询称为表子查询。2常用操作符IN3练习--1.查询与李一、李二职位和薪资都相同的员工的信息 select * from employ where (job, salary) in (select job, salary from employ where name 李一 or name 李二); --2.查询入职日期在2010年之后的员工信息及部门信息 select * from (select * from employ where entrydate 2010-01-01) e left join dept d on e.dept_id d.id;