MySQL多表查询

已于 2023-04-07 22:31:03 修改
阅读量817 收藏 2
点赞数
分类专栏： MySQL学习 文章标签： mysql 数据库
于 2023-04-05 10:39:21 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_62142545/article/details/129344823
版权

多表查询

多表关系

  • 概述：
    项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
    1、一对多（多对一）
    2、多对多
    3、一对一
  • 一对多（多对一）
    案例：部门与员工的关系
    关系：一个部门对应的员工
    实现：在多的一方建立外键，指向一的一方主键
  • 多对多
    案例：学生与课程的关系
    关系：一个学生可以选修多门课程，一门课程也可以提供多个学生选择
    实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
-- 多对多 ----------
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,'黛绮丝','20010101'),(null,'谢孙','20010102'),
                           (null,'应正天',20010103),(null,'伟一行',20010104);

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);
  • 一对一
    案例：用户与用户详情的关系
    关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其它详情字段放在另一张表中，以提升操作效率
    实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询概述

  • 概述：指从多张表中查询数据
  • 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）
    数据准备
-- 数据准备
create table emp2
(
    id          int primary key auto_increment comment '主键',
    name        varchar(10) comment '姓名',
    age         tinyint unsigned comment '年龄',
    job         varchar(10) comment '工作',
    salary      int comment '薪水',
    entrydate   date comment '入职时间',
    managerid   int comment '员工号',
    dept_id     int comment '外键'
) comment '员工表';

create table dept(
    id int primary key auto_increment comment '主键',
    name varchar(10) comment '名称'
)comment '部门表';

-- 添加外键
alter table emp2 add foreign key (dept_id) references dept(id);
-- alter table 从表 add foreign key(外键字段) references 主表(主键字段)
-- 添加数据
insert into emp2 (id, name, age,job,salary, entrydate,managerid,dept_id)
values
       (1,'金庸',66,'总裁',20000,'2000-01-01',null,5),
       (2,'张无忌',20,'项目经理',10000,'2001-02-01',1,1),
       (3,'杨逍',33,'开发',2000,'2003-05-01',2,1),
       (4,'韦一笑',46,'开发',2000,'2010-11-11',2,1),
       (5,'常遇春',43,'开发',2300,'2011-11-14',3,1),
       (6,'小昭',19,'开发',2000,'2012-10-15',2,1),
       (7,'灭绝',60,'总监',2500,'2013-09-16',1,3),
       (8,'周芷若',19,'出纳',2600,'2014-08-17',7,3),
       (9,'丁敏君',23,'职员',2050,'2015-06-18',7,3),
       (10,'赵敏',20,'职员',2090,'2016-05-19',1,2),
       (11,'鱼梁洲',56,'销售总监',3000,'2017-03-18',10,2),
       (12,'鹿杖客',19,'销售',5000,'2004-03-17',10,2),
       (13,'鹤壁问',19,'销售',6000,'2005-04-19',10,2),
       (14,'东方白',23,'销售',7000,'2006-05-22',1,4),
       (15,'张三丰',41,'市场部总监',8000,'2007-09-25',14,4),
       (16,'宋远桥',42,'程序员鼓励师',9000,'2008-12-27',14,4),
       (17,'程友良',43,null,10000,'2009-12-01',1,null);

insert into dept(id,name)values
(1,'研发部'),
(2,'市场部'),
(3,'财务部'),
(4,'销售部'),
(5,'总经部'),
(6,'人事部');

多表查询

-- 多表查询 -- 笛卡尔积 共102行（17 * 6）
select * from emp2,dept;
select * from emp2,dept where emp2.dept_id = dept.id;
  • 多表查询分类
  1. 连接查询
    内连接：相当于查询A、B交集部分数据
    外连接：
    左外连接：查询左表所有数据，以及两张表交集部分数据
    右外连接：查询右表所有数据，以及两张表交集部分数据
    自连接：当前表与自身的连接查询，自连接必须使用表别名
  2. 子查询

多表查询—内连接

内连接查询语法

  • 隐式内连接

SELECT 字段列表 FROM 表1，表2 WHERE 条件…;

  • 显示内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件…;

内连接查询的是两张表交集的的部分

-- 内连接演示
-- 1、查询每一个员工的姓名，及关联的部门名称（隐式内连接实现）
-- 表结构：emp,dept
-- 连接条件：emp.dept_id = dept.id
select emp2.name,dept.name from emp2,dept where emp2.dept_id = dept.id;
-- 当表名很长时，为表取别名，简化SQL语句的编写
select e.name,d.name from emp2 e,dept d where e.dept_id = d.id;

-- 1、查询每一个员工的姓名，及关联的部门名称（显式内连接实现）
-- 表结构：emp,dept
-- 连接条件：emp.dept_id = dept.id
select e.name,d.name from emp2 e inner join dept d on e.dept_id = d.id

多表查询—外连接

外连接查询语法：

  • 左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件…;

相当于查询表1（左表）的所有数据包含表1和表2交集的部分数据

  • 右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件…;

相当于查询表2（右表）的所有数据包含表1和表2交集的部分数据
在这里插入图片描述

-- 外连接演示
-- 1、查询emp表的所有数据，和相对应的部门信息（左外连接）
-- 表结构
-- 连接条件: emp.dept.id = dept.id
select e.*,d.name from emp2 e left outer join dept d on e.dept_id = d.id;
select e.*,d.name from emp2 e left join dept d on e.dept_id = d.id;
-- 1、查询dept表的所有数据，和相对应的员工信息（右外连接）
select d.*,e.* from emp2 e right outer join dept d on d.id = e.dept_id;
-- 将左外改成右外
select d.*,e.* from dept d left outer join emp2 e on d.id = e.dept_id;

多表查询—自连接

自连接查询语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件…;

自连接查询，可以是内连接查询，也可以是外连接查询。

-- 自连接
-- 1.查询员工及其所属领导的名字
-- 表结构：emp
select a.name,b.name from emp2 a,emp2 b where a.managerid = b.id;
-- 2.查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来
-- 表结构：emp a, emp b
select a.name '员工', b.name '领导' from emp2 a left join emp2 b on a.managerid = b.id;

多表查询—联合查询union

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A…
UNION[ALL]
SELECT 字段列表 FROM 表B…;

1. 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
2. union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

-- union all , union
-- 1.将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来
select * from emp2 where salary < 5000
union all
select * from emp2 where age > 50;
-- 去重后：
select * from emp2 where salary < 5000
union all
select * from emp2 where age > 50;

多表查询—子查询

  • 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 EHERE column1 = (SELECT column1 FROM t2);

  • 根据子查询的结果不同，分为：
  1. 标量子查询（子查询结果为单个值）
  2. 列子查询（子查询结果为一列）
  3. 行子查询（子查询结果为一行）
  4. 表子查询（子查询结果为多行多列）
  • 根据子查询的位置，分为：WHERE之后、FROM之后、SELECT之后。

子查询—标量子查询

  • 标量子查询
    子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询
-- 标量子查询
-- 1.查询“销售部”的所有员工信息
-- a.查询“销售部”部门ID
select id from dept where name = '销售部';
-- b.根据销售部部门ID，查询员工信息
select * from emp2 where dept_id = (select id from dept where name = '销售部');

-- 2.查询在“东方白”入职之后的员工信息
-- a.查询东方白的入职日期
select entrydate from emp2 where name = '东方白';
-- b.查询指定入职日期之后入职的员工信息
select * from emp2 where
entrydate > (select entrydate from emp2 where name = '东方白');

子查询—列子查询

  • 列子查询
    子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
    常用的操作符：IN、NOT IN、ANY、SOME、ALL
    在这里插入图片描述
-- 列子查询
-- 1.查询“销售部”和“市场部”的所有员工信息
-- a.查询“销售部”和“市场部”的部门ID
select id from dept where name = '销售部' or name = '市场部';
-- b.根据部门ID，查询员工信息
select * from emp2 where dept_id in
(select id from dept where name = '销售部' or name = '市场部');

-- 2.查询比财务部所有人工资都高的员工信息
-- a.查询所有财务部人员工资
select id from dept where name = '财务部';

select salary from emp2 where dept_id = (select id from dept where name = '财务部');
-- b.比财务部所有人工资都高的员工信息
select * from emp2 where salary >
all(select salary from emp2 where dept_id =
(select id from dept where name = '财务部'));

-- 3.查询比研发部其中任意一人工资高的员工信息
-- a.查询研发部所有人工资
select salary from emp2 where dept_id
= (select id from dept where name = '研发部');
-- b.比研发部其中任意一人工资高的员工信息(比最小值高)，any可以换成some
select * from emp2 where salary >
any (select salary from emp2 where dept_id =
(select id from dept where name = '研发部'));

子查询—行子查询

  • 行子查询
    子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
    常用的操作符：=、<>、IN、NOT IN
-- 行子查询
-- 1、查询与“张无忌”的薪资及直属领导相同的员工信息;
-- a.查询“张无忌“的薪资及直属领导
select salary,managerid from emp2 where name = '张无忌';
-- b.查询与”张无忌“的薪资及直属领导相同的员工信息
select * from emp2 where (salary,managerid)
= (select salary,managerid from emp2 where name = '张无忌');

子查询—表子查询

  • 表子查询
    子查询返回的结果是多行多列，这种子查询称为表子查询。
-- 表子查询
-- 1、查询“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
-- a.查询“鹿杖客”，“宋远桥”的职位和薪资
select job,salary from emp2 where name = '鹿杖客' or name = '宋远桥';
-- b.查询“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
select * from emp2 where (job,salary) in
(select job,salary from emp2 where name = '鹿杖客' or name = '宋远桥');

-- 2、查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
-- a.查询入职日期是“2006-01-01”之后的员工信息
select * from emp2 where entrydate > '2006-01-01';
-- b.查询这部分员工，对应的部门信息
select e.*,d.* from (select * from emp2 where entrydate > '2006-01-01')
    e left join dept d on e.dept_id = d.id;

多表查询练习

数据准备：

create table salgrade(
    grade int comment '等级',
    losal int comment '该等级下的最低薪资',
    hisal int comment '该等级下的最高薪资'
)comment '薪资等级表';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);
  • 根据需求，完成SQL语句的编写
  1. 查询员工的姓名、年龄、职位、部门信息。
  2. 查询年龄小于30岁的员工姓名、 年龄、职位、部门信息。
  3. 查询拥有员工的部门ID、部门名称。
  4. 查询所有年龄大于40岁 的员工,及其归属的部门名称;如果员工没有分配部门，也需要展示出来。
  5. 查询所有 员工的工资等级。
  6. 查询"研发部"所有员工的信息及工资等级。
  7. 查询"研发部"员工的平均工资。
  8. 查询工资比"灭绝"高的员工信息。
  9. 查询 比平均薪资高的员工信息。
  10. 查询低于本部门平均工资的员工信息。
  11. 查询所有的部门信息,并统计部门的员工人数。
  12. 查询所有学生的选课情况，展示出学生名称,学号,课程名称
-- 1. 查询员工的姓名、年龄、职位、部门信息。(隐式内连接)
select e.name, e.age, e.job, d.name
from emp2 e,
     dept d
where e.dept_id = d.id;

-- 2. 查询年龄小于30岁的员工姓名、 年龄、职位、部门信息。(显式内连接)
-- 显示内连接方式:
select e.name, e.age, e.job, d.name
from emp2 e
         inner join dept d
                    on e.dept_id = d.id
where e.age < 30;
-- 第二种查询方式
select e.name, age, job, d.name
from emp2 e,
     dept d
where e.dept_id = d.id
  and e.age < 30;

-- 3. 查询拥有员工的部门ID、部门名称。（要去重）
select distinct d.id, d.name
from emp2 e,
     dept d
where e.dept_id = d.id;

-- 4. 查询所有年龄大于40岁 的员工,及其归属的部门名称;如果员工没有分配部门，也需要展示出来。(左外连接)
select e.*, d.name
from emp2 e
         left join dept d on
    d.id = e.dept_id
where e.age > 40;

-- 5. 查询所有 员工的工资等级。
select e.*, s.grade, s.losal, s.hisal
from emp2 e,
     salgrade s
where e.salary >= s.losal
  and e.salary <= s.hisal;

select e.*, s.grade, s.losal, s.hisal
from emp2 e,
     salgrade s
where e.salary between s.losal and s.hisal;

-- 6. 查询"研发部"所有员工的信息及工资等级。
select *
from emp2 e,
     dept d,
     salgrade s
where e.dept_id = d.id
  and (e.salary between s.losal and s.hisal)
  and d.name = '研发部';

-- 7. 查询"研发部"员工的平均工资。
select avg(salary)
from emp2 e,
     dept d
where e.dept_id = d.id
  and d.name = '研发部';

-- 8. 查询工资比"灭绝"高的员工信息。
select *
from emp2
where salary > (select salary from emp2 where name = '灭绝');

-- 9. 查询 比平均薪资高的员工信息。
select *
from emp2
where salary > (select avg(salary) from emp2);

-- 10. 查询低于本部门平均工资的员工信息。
-- a.查询指定部门平均薪资
select avg(e1.salary) from emp2 e1 where e1.dept_id = 1;-- 部门1平均薪资
select avg(e1.salary) from emp2 e1 where e1.dept_id = 2;-- 部门2平均薪资
-- b.查询低于本部门平均工资的员工信息
select *
from emp2 e2
where e2.salary < (select avg(e1.salary) from emp2 e1 where e1.dept_id = e2.dept_id);

-- 11. 查询所有的部门信息,并统计部门的员工人数。
select d.id, d.name, (select count(*) from emp2 e where e.dept_id = d.id) '人数'
from dept d;
select count(*) from emp2 where dept_id = 1;

-- 12. 查询所有学生的选课情况，展示出学生名称,学号,课程名称
select s.name, s.no, c.name
from student s,
     student_course sc,
     course c
where s.id = sc.id
  and sc.id = c.id;

总结

1、多表关系

  • 一对多:在多的一方设置外键，关联一的一方的主键
    多对多:建立中间表，中间表包含两个外键，关联两张表的主键
  • 一对一:用于表结构拆分，在其中任何一方设置外键(UNIQUE)，关联另一方的主键
    2、多表查询
  • 内连接
    隐式: SELECT … FROM表A,表B WHERE条件…
    显式: SELECT … FROM表A INNER JOIN表B ON条件…
  • 外连接
    左外: SELECT … FROM表A LEFT JOIN表B ON条件…
    右外: SELECT … FROM表A RIGHT JOIN表B ON条件…
  • 自连接
    SELECT … FROM表A别名1 ,表A别名2 WHERE条件…
  • 子查询
    标量子查询、列子查询、行子查询、表子查询
匿名459
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL 多表查询实现分析
09-11
在一个数据库中，可能存在多个表，这些表都是相互关联的。
MySQL多表查询实例详解【链接查询、子查询等】
09-08
主要介绍了MySQL多表查询,结合实例形式详细分析了mysql多表查询中的链接查询、子查询等相关操作技巧与使用注意事项,需要的朋友可以参考下
MYSQL多表查询
最新发布
weixin_42894555的博客
04-13 1105
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
MySQL多表查询详解
03-08
文档讲述了MySQL中的多表查询和一些其他的知识点，这是以后经常用到的
MySQL多表查询的方法（含例子）
2301_76297780的博客
12-31 683
我们查两张及以上表的时候，普通的查询语法(select * from 表名）不能发挥作用。下面我演示两张表和三张表的查询方法。
mysql多表查询
zcffight的博客
10-04 2272
mysql的多表查询
MySQL --- 多表查询 & 驱动表
weixin_53622554的博客
05-12 8809
指从多张表中查询数据，就是从多张表当中进行查询单表查询的SQL语句：select 字段列表 from 表名;那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;-- 多表查询:直接在from之后跟上多张表就可以了,多张表之间使用逗号分隔可以看到，在多表查询的结果当中，每个员工都展示了5次，将每个员工分别和五个部门进行了一次匹配， 也就是将17个员工和5个部门挨个组合了一次，就是17 * 5 = 85。
MySQL多表查询 （超详细）
weixin_55772633的博客
08-05 6039
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）（开发中一般取消外键的设定，即在多的一方建立字段指向一的一方的主键即可）（开发中一般取消外键的设定，即在任意一方加入字段关联另外一方的主键即可）（开发中一般取消外键的设定，即在中间表建立两个字段指向两方的主键即可）关系：一个部门对应多个员工，一个员工对应一个部门。实现：在多的一方建立外键，指向一的一方的主键。
MySQL中的多表查询详解
qq_57492774的博客
07-09 4289
在实际的项目开发中，在进行数据表结构设计的时候，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间很多都是相关联的，所以各个表结构之间也存在着一定的关系。id INT PRIMARY KEY COMMENT '部门编号',name VARCHAR(20) UNIQUE COMMENT '部门名称'
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
MySQL多表查询详解上
09-08
主要介绍了MySQL多表查询详解上，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
解析Mysql多表查询的实现
09-10
本篇文章是对Mysql多表查询的实现进行了详细的分析介绍，需要的朋友参考下
25.3 MySQL 多表查询
11-05
配套资源
mysql多表查询准备
10-01
mysql
MySQL：多表查询（全面详解）
weixin_52533007的博客
06-16 7437
本博主将用CSDN记录软件开发求学之路上亲身所得与所学的心得与知识，有兴趣的小伙伴可以关注博主！也许一个人独行，可以走的很快，但是一群人结伴而行，才能走的更远！
【⑥MySQL多表查询】：让你的数据检索更高效
热爱编程的小K的博客
06-18 3800
【MySQL多表查询】：让你的数据检索更高效
【MySQL基础】多表查询
Chovy_pyc的博客
06-22 1903
MySQL基础入门

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值