MySQL数据库之多表查询

MySQL数据库之多表查询

引用

CSDN

1.

https://blog.csdn.net/new9232/article/details/146136544

MySQL多表查询是数据库操作中的重要技能，本文将详细介绍多表关系、内连接、外连接、自连接、联合查询和子查询等知识点，并通过具体的SQL语句示例帮助读者掌握这些技能。

1 多表关系

在项目开发中，数据库表结构的设计需要根据业务需求及业务模块之间的关系进行分析。表结构之间的关系主要分为三种：一对多（多对一）、多对多和一对一。

一对多（多对一）

案例：部门与员工的关系。

• 关系：一个部门对应多个员工，一个员工对应一个部门。
• 实现：在多的一方建立外键，指向一的一方的主键。

语法示例

# 创建部门表
create table department(id int primary key, name varchar(30));
# 插入数据
insert into department(id, name) values(1, '研发部'),(2, '市场部'),(3, '财务部'),(4, '销售部');
# 创建员工表，并关联部门表
create table employee(id int primary key, name varchar(30), age int, dept_id int, constraint fk_dept_id foreign key(dept_id) references department(id));
# 插入数据
insert into employee(id, name, age, dept_id) values(1001, 'Jack', 22, 1),(1002, 'Lucy', 24, 1),(1003, 'Mary', 24, 2),(1004, 'Tom', 26, 2),(1005, 'Mark', 26, 3);

多对多

案例：学生与课程的关系。

• 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择。
• 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

语法示例

# 先创建学生表
create table student(id int primary key, name varchar(30), age int);
# 插入数据
insert into student(id, name, age) values(1001, 'Jack', 22),(1002, 'Lucy', 24),(1003, 'Mary', 24),(1004, 'Tom', 26),(1005, 'Mark', 26);
# 创建课程表
create table course(id int primary key, name varchar(30));
# 插入数据
insert into course(id, name) values(1, 'C++'),(2, 'Java'),(3, 'Python'),(4, 'Go'),(5, 'JS');
# 创建中间表
create table student_course(id int primary key, student_id int, course_id int, constraint fk_student_id foreign key(student_id) references student(id), constraint fk_course_id foreign key(course_id) references course(id));
# 插入数据
insert into student_course(id, student_id, course_id) values(1, 1001, 1),(2, 1002, 1),(3, 1003, 1),(4, 1003, 2),(5, 1004, 2),(6, 1005, 1),(7, 1005, 3);

一对一

案例：用户与用户详情的关系

• 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以便提升操作效率。
• 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的。

语法示例

# 创建用户基本信息表
create table tb_user(id int primary key, name varchar(30), age int);
# 插入数据
insert into tb_user(id, name, age) values(1001, 'Jack', 22),(1002, 'Lucy', 24),(1003, 'Mary', 24),(1004, 'Tom', 26),(1005, 'Mark', 26);
# 创建用户教育信息表，并关联用户基本信息表，并且外键是唯一约束
create table tb_user_edu(id int primary key, degree varchar(20), middleschool varchar(30), university varchar(30), language varchar(10), userid int unique, constraint fk_userid foreign key(userid) references tb_user(id));
# 插入数据
insert into tb_user_edu(id, degree, middleschool, university, language, userid) values(1001, '本科', '北师大附中', '清华大学', 'C++', 1001),(1002, '本科', '衡水中学', '北京大学', 'Java', 1002),(1003, '本科', '人大附中', '浙江大学', 'Python', 1003),(1004, '硕士', '西工大附中', '复旦大学', 'Go', 1004),(1005, '硕士', '成都七中', '交通大学', 'JS', 1005);

2 多表查询概述

多表查询是指从多张表中查询数据。在多表查询时，需要消除无效的笛卡尔积。

笛卡尔积

指在数学中，集合A和集合B的所有组合情况。在多表查询时，需要消除无效的笛卡尔积。

多表查询分类

分为连接查询和子查询，连接查询又分为内连接、外连接和自连接。

3 内连接

内连接查询两张表交集部分数据。

3.1 隐式内连接

select 字段列表 from 表1,表2 where 条件;

执行SQL查询

select employee.name, department.name from employee, department where employee.dept_id = department.id;

也可以给表起个别名简化书写

select e.name, d.name from employee e, department d where e.dept_id = d.id;

3.2 显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件;

执行SQL查询

select e.name, d.name from employee e inner join department d on e.dept_id = d.id;

4 外连接

4.1 左外连接

查询左表的所有数据，以及两种表交集部分数据。

select 字段列表 from 表1 left [outer] join 表2 on 条件;

执行SQL查询

select e.*, d.name from employee e left outer join department d on e.dept_id = d.id;

4.2 右外连接

查询右表的所有数据，以及两种表交集部分数据。

select 字段列表 from 表1 right [outer] join 表2 on 条件;

执行SQL查询

select d.*, e.* from employee e right outer join department d on e.dept_id = d.id;

5 自连接

当前表与自身的连接查询，自连接必须使用表别名。自连接可以是内连接，也可以是外连接。

select 字段列表 from 表A 别名A join 表A 别名B on 条件;

执行SQL查询

select e1.name, e2.name from employee e1, employee e2 where e1.manager_id = e2.id;

再比如查询所有员工极其领导名字，执行SQL查询（属于左外连接）

select e1.name, e2.name from employee e1 left outer join employee e2 on e1.manager_id = e2.id;

6 联合查询

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

select 字段列表 from 表A ... union [all] select 字段列表 from 表B ...;

执行SQL查询

select id,name from student where id > 1002 union all select id,name from course where name = 'C++';

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

• union all 会将全部的数据直接合并在一起，
• union 会对合并之后的数据去重。

7 子查询

SQL语句中嵌套 select 语句，称为嵌套查询，又称子查询。

select * from t1 where column1 = (select column1 from t2);

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

7.1 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），这种子查询称为标量子查询。

常用的操作符：= 、!=、>、>=、<、<=。

执行SQL查询

select * from employee where dept_id = (select id from department where name = '研发部');

7.2 列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：in、not in、any、some、all。

• in：在指定的集合范围之内，多选一
• not in：不在指定的集合范围之内
• any：子查询返回列表中，有任意一个满足即可
• some：与 any 等同，使用 some 的地方都可以使用 any
• all：子查询返回列表的所有值都必须满足

执行SQL查询

select * from employee where dept_id in (select id from department where name = '研发部' or name = '市场部');

查询比市场部所有员工年龄都大的员工信息。

select * from employee where age > all (select age from employee where dept_id = (select id from department where name = '市场部'));

查询比市场部任意一个员工年龄都大的员工的信息

select * from employee where age > any (select age from employee where dept_id = (select id from department where name = '市场部'));

7.3 行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：=、!=、in、not in。

执行SQL查询

select * from employee where (age,dept_id) = (select age,dept_id from employee where name = 'Lucy');

7.4 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用操作符：in

执行SQL查询

select * from employee where (age, dept_id) in (select age,dept_id from employee where name = 'Tom' or name = 'Lucy');