文章
文章用户商铺快讯网址导航

小知识：MySQL多表联查的实现思路

多表联查场景

一对一

用户与用户信息表：当用户的信息数据过多时，我们可以将其分成两个表分别对应用户基本信息和用户的详情信息。

?
1
2
3
4
5
6
7
8
9
10
11
create table user(
`id` int auto_increment primary key comment用户id,
`name` varchar(10),
`age` varchar(10),
`gender` char,
`tel` varchar(30),
`school` varchar(20),
`addr` varchar(20),
`degree` varchar(10),
`university` varchar(10)
);

当用户信息过多时，就可以使用外键进行关联。在任意一方加入外键，关联另一方主键，并且设置外键为唯一的UNIQUE如下实现：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table user(
`id`     int auto_increment primary key,
`name`   varchar(10),
`age`    varchar(10),
`gender` char
);
create table user_info(
`id`         int auto_increment primary key ,
`tel`        varchar(30),
`school`     varchar(20),
`addr`       varchar(20),
`degree`     varchar(10),
`university` varchar(10),
`user_id` int unique,
constraint fk_user_info foreign key (user_id) references user(id)
);

多对一

部门与员工：一个员工对应一个部门，一个部门对应多个员工

员工指向多的一方，部门指向一的一方。此时应该在员工表中创建外键，指向部门表中的主键

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 员工表
create table emp
(
`emp_id`      int auto_increment primary key,
`emp_name`    varchar(20) not null,
`emp_gender`  char,
`emp_tel`     varchar(30),
`emp_dept_id` int,
constraint fk_emp_dept foreign key (emp_dept_id) references dept (dept_id)
);
# 部门表
create table dept
(
`dept_id` int auto_increment primary key,
`dept_name` varchar(20) not null
);

查询方法：

?
1
2
3
4
5
6
— 正常单表查
select * from emp;
— 全查 笛卡尔积
select * from emp,dept;
— 联查 消除无效的笛卡尔积
select * from emp,dept where emp_dept_id = dept.dept_id;

多对多

学生与课程：一个学生可以选修多门课程，一门课程可以被多个学生选择

此时我们应该在学生表与课程表之间建立中间表。中间表包含两个外键，分别对应学生表和课程表的主键

首先我们准备好数据，学生表+课程表+中间信息表

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 学生表
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,小瘪三,2001),(NULL,小瘪四,2002),(NULL,小瘪五,2003),(NULL,小瘪六,2004);
# 课程表
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,
student_id INT NOT NULL COMMENT 学生ID,
course_id INT NOT NULL COMMENT 课程ID,
CONSTRAINT fk_course_id FOREIGN KEY (course_id) REFERENCES course (id),
CONSTRAINT fk_student_id FOREIGN KEY (student_id) 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);

我们切换IDEA打开可以清晰的看到三个表之间的关系，中间信息表中的两个外键：student_id与course_id将学生表和课程表关联了起来

%小知识：MySQL多表联查的实现思路-猿站网-插图

内连接查询

查询A集合与B集合的交集

?
1
2
3
4
— 方式一
select [字段列表] from 表1,表2 where 条件…;
— 方式二
select [字段列表] from 表1 inner join 表2 on 条件…;

示例：查询所有员工与其所属部门

?
1
2
3
4
— 方式一
select emp_name,dept_name from emp inner join dept d on emp.emp_dept_id = d.dept_id;
— 方式二
select emp_name,dept_name from emp,dept where emp.emp_dept_id = dept.dept_id;

外连接

右外连接：查询右表所有数据以及两表交集部分数据

?
1
select 字段列表 from 表1 right outer join 表2 on 条件…;

左外连接：查询左表所有数据以及两表交集部分数据

?
1
select 字段列表 from 表1 left outer join 表2 on 条件…;

示例：

?
1
2
3
4
— 2. 查询员工全部信息及其对应的部门信息 左外连接
select  e.*,dept_name from emp e left join dept d on d.dept_id = e.emp_dept_id;
— 3. 查询全部部门和其对应的所有员工信息 右外连接
select d.*,e.emp_name from emp e right join dept d on e.emp_dept_id = d.dept_id;

自连接

当前表与自身的连接查询，自连接必须使用别名

格式：

?
1
select 字段列表 from 表A 别名 join 表A 别名 on 条件…;

在员工表中，所有的普通员工、管理者都是员工。查询每个员工归属哪个管理者管理就需要用到自连接

?
1
select a.name,b.name from emp a join emp b on a.emp_id = b.manager_id;

联合查询

关键字 union [all] 将两条sql语句查询的结果拼接起来

?
1
2
3
4
— 查询年龄大于50的员工  和薪资小于10000的员工
select * from emp where emp.emp_age > 50
union all
select * from emp where emp.emp_salary < 10000;

加上all表示不会去重，不加all表示去重复（即同时满足两条sql语句的只出现一次即可）

多张表的列数必须保持一致，字段类型也需要一致

子查询

子查询是指在SQL语句中嵌套select语句进行嵌套查询

?
1
select * from t1 where column1 =(select column1 from t2)；

子查询的外部语句可以是insert、uodate、delete、select的任何一个

标量子查询示例：查询‘ 开发部 ’的全部员工信息

首先你可以利用正常的两条sql去查询：

?
1
2
3
4
— 查询“开发部”的所有员工
— 1. 两条语句查询
select dept_id from dept where dept_name = 开发部;
select emp_name from emp where emp_dept_id = 1;

或者你可以使用内连接方式进行联查：

?
1
2
— 内连接
select e.emp_name,d.dept_name from emp e inner join dept d on e.emp_dept_id = d.dept_id where dept_name = 开发部;

或者使用子查询：

?
1
2
— 使用子查询
select * from emp where emp_dept_id = (select dept_id from dept where dept_name = 开发部);

到此这篇关于MySQL多表联查的实现思路的文章就介绍到这了,更多相关MySQL多表联查内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/yuqu1028/article/details/128879346

声明： 猿站网有关资源均来自网络搜集与网友提供，任何涉及商业盈利目的的均不得使用，否则产生的一切后果将由您自己承担！ 本平台资源仅供个人学习交流、测试使用 所有内容请在下载后24小时内删除，制止非法恶意传播，不对任何下载或转载者造成的危害负任何法律责任！也请大家支持、购置正版！ 。本站一律禁止以任何方式发布或转载任何违法的相关信息访客发现请向站长举报，会员发帖仅代表会员个人观点，并不代表本站赞同其观点和对其真实性负责。本网站的资源部分来源于网络，如有侵权烦请发送邮件至：2697268773@qq.com进行处理。
建站知识

小知识：将java项目打包成exe可执行文件的完整步骤

2023-2-27 18:34:33

建站知识

小知识：什么是web服务器（WEB网站服务器和应用服务器的区别与联系）

2023-2-27 23:57:13

猜你喜欢
0 条回复 A文章作者 M管理员
    暂无讨论，说说你的看法吧
❯
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索

  • 扫码打开当前页

返回顶部