MYSQL-----多表查询详解,配有练习讲解
在我之前的博客中,讲解过单表查询,也就是DQL语句
在项目开发时,由于业务之间相互关联,所以各个表之间也存在联系,基本上分为三种:
1.一对多
2.多对多
3.一对一
在一对多或者多对一时:
案例:部门与员工表之间的关系,一个部门对应多个员工,一个员工对应一个部门,要实现在多的一方建立外键,指向一的一方的主键,也就是说,一的一方要作为主线,作为父表,作为多的一方的外键。
多对多时:
案例:学生与课程之间的关系,一个学生可以选修多门课程,一门课程也可以供多名学生选择。
此时,我们需要建立一个中间表,至少包含两个外键,分别关联两方主键,以下是代码示例:
create table student(
id tinyint auto_increment primary key ,
name varchar(10) ,
no varchar(10)
);
insert into student value (null,‘张文超’,‘2022403797’),(null,‘丁含斌’,‘2022405030’),(null,‘龚’,‘2022404989’),(null,‘test’,‘123456’);
create table course(
id int auto_increment primary key ,
name varchar(10)
);
insert into course value (null,‘java’),(null,‘C’),(null,‘mysql’),(null,‘dosbox’);
create table student_course(
id tinyint auto_increment primary key ,
studentid tinyint not null ,
courseid int not null ,
constraint fk_course_id foreign key (courseid)references course(id),
constraint fk_student_id foreign key (studentid)references student(id)
);
insert into student_course values (null,1,2),(null,1,3),(null,2,1),(null,2,2),(null,3,4);
我创建了一个课程表,一个学生表,并且创建了一个中间表,值得注意的是:在主表中的数据是什么类型,外键就应该是什么类型。
constraint fk_course_id foreign key (courseid)references course(id),
创建外键的过程语句,很多人不懂,包括我刚开始也不是特别懂,这里再做一次解释:就是将表中的某一个字段作为另一个表的外键,constraint后面接着的是创建完这个外键以后,给外键的命名,foreign key 后面接的是要将哪一个字段作为外键约束,references后面接的是主表的字段,这样解释应该会清晰很多
一对一的关系:
案例:用户和用户详情之间的关系,用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中。
实现:在任意一方中加入外键,关联另一方的主键,并且设置外键为唯一约束。
那到底什么是多表查询呢:
之前咱们演示的是单表查询,就是从多张表中查询数据:
select * from newy,dept;
比如说这个命令,就是查看两张表的数据,查看的结果一共有20条,newy的记录数*dept的记录数,就是最终的记录,这不是我们想要的结果,这种称为笛卡尔积。所以在查询时,我们需要消除不必要的笛卡尔积。要想消除笛卡尔积,只需要在后面加上约束条件:
select * from newy,dept where newy.dept_id=dept.id;
这样就可以查看到他们的关联数据。
多表查询的分类:
1.连接查询
内连接:相当于查询A,B交集部分数据
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
自连接:当前表与自身的连接查询,自连接必须使用表别名。
子查询:
我们再仔细看看每个连接方式:
内连接:
语法:1.隐式内连接:select * from 表一,表二 where 条件…;
显式内连接:select * from 表一inner join 表二 on 约束条件。
根据需求,我们看一下他们的区别
需求1:查询每一个员工的姓名,以及他们关联部门的名称:
select newy.name,dept.name from newy,dept where newy.dept_id=dept.id;
需求二:使用显式内连接:
select n.name,d.name from newy n inner join dept d on n.dept_id = d.id;
效果是一样的。
外连接:
左外连接:语法:select 字段列表 from 表一 left join 表二 on 条件
查询的是表一的所有数据以及两张表的交集部分。
右外连接:左外连接:语法:select 字段列表 from 表一 right join 表二 on 条件
查询的是表二的所有数据以及两张表的交集部分。
通过一个需求,来加深巩固:
1.查询newy的所有数据,并且查看对应的部门信息:
select n.*,d.name from newy n left join dept d on n.dept_id = d.id;
自连接:
所谓自连接,就是查看自己表中的一些结构,语法:
select from 表一别名1,表一别名二,where约束条件。
2.selectfrom 表A别名A join 表A别名B on 约束条件。
接下来登场的是联合查询:
语法:select 字段列表from 表A union[ALL]select 字段列表 表B;
其实实际运用时,就是将两个条件查询的sql语句使用union[all]联合起来
当然了,这样会有重复的记录,并不是我们想要的结果,所以我们可以将all删掉,这样,就可以去重。
这样查询很方便,但是是有使用条件的,就是需要有相同的列数才可以使用。
最后登场的是 子查询:
根据子查询结果不同,分为:
标量子查询(子查询结果为单个的值)
列子查询(子查询结果为一列)
行子查询(子查询结果为一行)
表子查询(子查询结果为多列多行)
根据子查询的位置:where之后的子查询,from之后的子查询,select之后。
1.标量子查询:
直接上需求:查询销售部的所有员工信息:
这里我们可以进行拆分,首先查看销售部的部门ID,再根据ID查看员工表的信息:
select * from dept where name=‘销售部’;
select * from emp where dept_id=(select * from dept where name=‘销售部’);
2.查询在方方之后入职的员工信息:
同样的,我们分为两步:
1.查询方方的入职时间信息
2.查询员工表中入职时间大于方方的
select * from emp where entrydate>(select entry from emp where name=‘方方’😉
2.列子查询:
子查询结果为一列:
这里还要介绍两个关键字:ANY,ALL,any的意思是,在返回列表中,有一个满足即可,all的意思是,返回列表的所有值都要满足。
根据需求来学习:
1.查询’销售部’和’市场部’的所有员工信息。
分为几步:
1.查询销售部和市场部的部门id
2.根据id查询员工信息
就是一列多行
select from emp where dept_id in(select id from dept where name=‘销售部’or’市场部’);
2.查询比财务部所有人工资都高的员工信息:
select * from emp where salary>all(selsct salary from emp where dept_id=(select id from dept where name=‘财务部’);
行子查询:
子查询的结果是一行,也可以是多列,这种查询称为行子查询。
根据需求来了解:
查询与张无忌薪资及直属领导相同的员工信息:
我们还是会分为两个步骤:
1.查询张无忌的薪资,以及直属领导:
select salary,managerid from emp where name=‘张无忌’;
2.查询相同的员工信息
select * from emp where salary,managerid=(select salary,managerid from emp where name=‘张无忌’);
表子查询:返回的结果是多列多行的,像一张表,这种查询称为子查询,
把查询结果作为一张表,再和其他表进行联查操作。
还是通过需求来快速了解:
1.查询与’鹿仗客’和’宋远桥’的职位和薪资相同的员工信息:
还是先拆解:
1.查询’鹿仗客’和’宋远桥’的职位和薪资:
select job,salary from emp where name=‘鹿仗客’or’宋远桥’;
2.查询与’鹿仗客’和’宋远桥’的职位和薪资相同的员工信息:
select * from emp where (job,salary) in(select job,salary from emp where name=‘鹿仗客’or’宋远桥’);
我们来解读一下这一条语句:
查询的是job和salary两个信息,这两个信息满足in后面的任意一个条件就可以,无论是’鹿仗客’还是’宋远桥’都无所谓。
原文地址:https://blog.csdn.net/ZZZ_zzz555/article/details/137235161
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!