MySQL多表操作

火眼猊

已于 2022-06-02 10:59:43 修改

阅读量837

点赞数 1

分类专栏：数据库文章标签： mysql 数据库 sql

于 2022-05-31 15:17:12 首次发布

本文链接：https://blog.csdn.net/JAX_fire/article/details/125020585

版权

数据库专栏收录该内容

19 篇文章 0 订阅

订阅专栏

多表关系

概念：在实际开发中，一个项目通常需要多张表才能完成。
多表关系可以概括为：一对一、一对多/多对一，多对多

一对一关系

一个学生只有一张身份证，一张身份证只能对应一个学生
在任一表中添加唯一外键，指向另一方主键，确保一对一关系
一般一对一关系很少见，遇到一对一关系的表最好是合并表

一对多/多对一

一个部门有多个员工，一个员工只能对应一个部门
再多的一方建立外键，指向一的一方的主键

多对多关系

一个学生可以选择很多门课程，一个课程也可以被很多学生选择
多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键

外键约束

概念：MySQL外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

特点
定义一个外键时，需要遵循下列规则：

主表必须已经存在于数据库中，或者是当前正在创建的表
必须为主表定义主键
逐渐不能包含控制，但允许在外键中出现空值。也就是说，主子要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的
在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键
外键中列的数目必须和主表的主键中列的数目相同
外键中列的数据类型必须和主表主键中对应的数据类型相同

创建外键约束

方法一：
[constraint<外键名>] foreign key 字段名 [,字段名2，……] references <主表名> 主键列1[,主键列2……]

例如：

-- 创建部门表，主表
CREATE TABLE IF NOT EXISTS dept(
deptno VARCHAR(20) PRIMARY KEY,
name VARCHAR(20)
);

-- 创建员工表，从表，并创建dept_id外键约束
CREATE TABLE IF NOT EXISTS emp(
eid VARCHAR(20) PRIMARY KEY,
ename VARCHAR(20),
age INT,
dept_id VARCHAR(20),
CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept(deptno)
);

方法二：
alter table <数据表名> add constraint <外键名> foreign <列名> references <主表名>(<列名>)；

例如：

-- 创建部门表，主表
CREATE TABLE IF NOT EXISTS dept2(
deptno VARCHAR(20) PRIMARY KEY,
name VARCHAR(20)
);

-- 创建员工表，从表，并创建dept_id外键约束
CREATE TABLE IF NOT EXISTS emp2(
eid VARCHAR(20) PRIMARY KEY,
ename VARCHAR(20),
age INT,
dept_id VARCHAR(20)
); 

ALTER TABLE emp2 add CONSTRAINT emp2_fk FOREIGN KEY(dept_id) REFERENCES dept2(deptno);

数据插入

-- 外键约束操作
-- 1.添加主表数据
-- 注：必须先给主表添加数据
INSERT INTO dept VALUES('1001','研发部');
INSERT INTO dept VALUES ('1002', '销售部');
INSERT INTO dept VALUES ('1003', '财务部');
INSERT INTO dept VALUES ('1004', '人事部');

-- 2.添加从表数据
-- 注：给从表添加数据时，外键列的值必须依赖主表的主键列
INSERT INTO emp VALUES('1','乔峰',20,'1001');
INSERT INTO emp VALUES('2', '段誉', 21, '1001');
INSERT INTO emp VALUES('3', '虚竹', 23, '1001');
INSERT INTO emp VALUES('4', '阿紫', 18, '1002');
INSERT INTO emp VALUES('5', '扫地僧', 35, '1002');
INSERT INTO emp VALUES('6', '李秋水', 33, '1003');
INSERT INTO emp VALUES('7', '鸠摩智', 50, '1003');

数据删除

-- 3.删除数据
-- 注：
-- 1.主表的数据被从表依赖时，不能删除
-- 2.从表的数据可以随便删除

DELETE FROM dept WHERE deptno=1004;
DELETE FROM emp	 WHERE eid='7';

删除外键约束

概念：当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会接触主表与从表的关联关系。

方法：
alter table <表名>drop foreign key<外键约束名>;
例如：

ALTER TABLE emp2 drop FOREIGN KEY emp2_fk;

多对多关系

在多对多关系中，A表的一行对应B的多行，B的一行对应A的多行，我们姚新增加一个中间表，来建立多对多的关系。

-- 创建学生表student(左侧主表)
CREATE TABLE IF NOT EXISTS	student(
sid INT PRIMARY KEY auto_increment,
name VARCHAR(20),
age INT,
gender VARCHAR(20)
);

-- 创建课程表course（右侧主表）
CREATE TABLE course(
cid INT PRIMARY KEY auto_increment,
cidname VARCHAR(20)
);

-- 创建中间表student_course/score(从表)
CREATE TABLE score(
sid INT,
cid INT,
score DOUBLE
);

-- 建立外键约束（两次）
ALTER TABLE score ADD FOREIGN KEY(sid) REFERENCES student(sid);
ALTER TABLE score ADD FOREIGN KEY(cid) REFERENCES course(cid);

-- 给学生表添加数据
INSERT INTO student VALUES(1,'小龙女',18,'女'),(2,'阿紫',19,'女'),(3,'周芷若',20,'男');
-- 给课程表添加数据
INSERT INTO course VALUES(1,'语文'),(2,'数学'),(3,'英语');
-- 给中间表添加数据
INSERT INTO score VALUES(1,1,78),(1,2,75),(2,1,88),(2,3,90),(3,2,80),(3,3,65);

注：修改与删除时，中间从表可以随便删除与修改，但是两边的主表受从表依赖的数据不能删除或者修改

多表联合查询

概念：多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候，需要显示的数据来自多张表。
多表查询有以下分类：

交叉连接查询（产生笛卡尔积）
语法：select * from A,B;
内连接查询（使用的关键字inner join – inner可以省略）
隐式内连接：select * from A,B where 条件;
显示内连接：select * from A inner join B on 条件;
外连接查询（使用的关键字outer join --outer可以省略）
左外连接：left outer join
select * from A left outer join B on 条件;
右外连接：right outer join
select * from A right outer join B on 条件;
满外连接：full outer join
select * from A full outer join B on 条件;
子查询
select的嵌套
表自关联
将一张表当成多张表来用
准备查询数据
接下来准备多表查询的数据，注意，外键约束对多表查询并无影响。

-- 创建部门表
CREATE TABLE IF NOT EXISTS dept3(
deptno varchar(20) PRIMARY KEY,
name VARCHAR(20)
);

-- 创建员工表
CREATE TABLE IF NOT EXISTS emp3(
eid VARCHAR(20) PRIMARY KEY,
ename VARCHAR(20),
age INT,
dept_id VARCHAR(20)
);

-- 给dept3添加数据
INSERT INTO dept3 VALUES('1001','研发部');
INSERT INTO dept3 VALUES ('1002', '销售部');
INSERT INTO dept3 VALUES ('1003', '财务部');
INSERT INTO dept3 VALUES ('1004', '人事部');

-- 给emp3添加数据
INSERT INTO emp3 VALUES('1','乔峰',20,'1001');
INSERT INTO emp3 VALUES('2', '段誉', 21, '1001');
INSERT INTO emp3 VALUES('3', '虚竹', 23, '1001');
INSERT INTO emp3 VALUES('4', '阿紫', 18, '1002');
INSERT INTO emp3 VALUES('5', '扫地僧', 35, '1002');
INSERT INTO emp3 VALUES('6', '李秋水', 33, '1003');
INSERT INTO emp3 VALUES('7', '鸠摩智', 50, '1003');
INSERT INTO emp3 VALUES('8','天山童姥',60,'1003');
INSERT INTO emp3 VALUES('9','慕容博',58,'1003');

交叉连接查询

交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
笛卡尔积可以理解为一张表的每一行去和另一张表的任意一行进行匹配
假如A表有m行数据，B表有n行数据，则返回m*n行数据
笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选
方法：
select * from 表1，表2，表3……;
例如：

SELECT * FROM dept3,emp3;

内连接查询

内连接查询求多张表的交集
方法：
隐式——selsect * from A,B where 条件;
显示——selsect * from A inner join B on 条件;
例如：

-- 创建部门表
CREATE TABLE IF NOT EXISTS dept3(
deptno varchar(20) PRIMARY KEY,
name VARCHAR(20)
);

-- 创建员工表
CREATE TABLE IF NOT EXISTS emp3(
eid VARCHAR(20) PRIMARY KEY,
ename VARCHAR(20),
age INT,
dept_id VARCHAR(20)
);

-- 给dept3添加数据
INSERT INTO dept3 VALUES('1001','研发部');
INSERT INTO dept3 VALUES ('1002', '销售部');
INSERT INTO dept3 VALUES ('1003', '财务部');
INSERT INTO dept3 VALUES ('1004', '人事部');

-- 给emp3添加数据
INSERT INTO emp3 VALUES('1','乔峰',20,'1001');
INSERT INTO emp3 VALUES('2', '段誉', 21, '1001');
INSERT INTO emp3 VALUES('3', '虚竹', 23, '1001');
INSERT INTO emp3 VALUES('4', '阿紫', 18, '1002');
INSERT INTO emp3 VALUES('5', '扫地僧', 35, '1002');
INSERT INTO emp3 VALUES('6', '李秋水', 33, '1003');
INSERT INTO emp3 VALUES('7', '鸠摩智', 50, '1003');
INSERT INTO emp3 VALUES('8','天山童姥',60,'1003');
INSERT INTO emp3 VALUES('9','慕容博',58,'1003');

SELECT * FROM dept3,emp3;



-- 查询每个部门的所属员工
-- 隐式内连接
SELECT * FROM dept3,emp3 WHERE deptno = dept_id;
SELECT * FROM dept3 a,emp3 b WHERE a.deptno =b.dept_id;
-- 显示内连接
SELECT * FROM dept3 INNER JOIN emp3 ON deptno = dept_id;
SELECT * FROM dept3 a INNER JOIN emp3 b ON a.deptno =b.dept_id;

-- 查询研发部门的所属员工
-- 隐式内连接
SELECT * FROM dept3 a,emp3 b WHERE a.deptno =b.dept_id AND name ='研发部';
-- 显示内连接
SELECT * FROM dept3 a JOIN emp3 b ON a.deptno = b.dept_id AND name = '研发部';

-- 查询研发部和销售部的所属员工
SELECT * FROM dept3 a JOIN emp3 b on a.deptno = b.dept_id and (name= '研发部' or name = '销售部');
SELECT * FROM dept3 a JOIN emp3 b on a.deptno = b.dept_id and name in ('研发部','销售部');

-- 查询每个部门的员工数，并升序排序
SELECT 
a.name,a.deptno,COUNT(*) '员工数' 
FROM 
dept3 a JOIN emp3 b on a.deptno = b.dept_id GROUP BY a.deptno ORDER BY COUNT(*) ;

-- 查询人数大于等于3的部门，并按照人数降序排序
SELECT
a.deptno,a.name,COUNT(*) as total_cnt 
FROM 
dept3 a JOIN emp3 b on a.deptno = b.dept_id 
GROUP BY
a.deptno,a.name HAVING total_cnt >=3 ORDER BY total_cnt DESC;

外连接查询

外连接分为左外连接（left outer join）、右外连接（right outer join），满外连接（full outer join）。
注：oracle里面有fulll join，可是在MySQL中full join 支持的不好，我们可以使用union来达到目的。
方法：
左外连接：left outer join
select * from A left outer join B on 条件;
右外连接：right outer join
select * from A right outer join B on 条件;
满外连接：full outer join
select * from A full outer join B on 条件;

例如：

-- 外连接查询
SELECT * FROM dept3 a LEFT OUTER JOIN emp3 b ON a.deptno = b.dept_id;
SELECT * FROM dept3 a LEFT JOIN emp3 b ON a.deptno = b.dept_id;
-- SELECT * FROM A LEFT JOIN B on 条件1 RIGHT JOIN C on 条件2 RIGHT JOIN D on 条件3; 

-- 查询那些部门有员工，哪些部门没有员工
SELECT * FROM dept3 a RIGHT OUTER JOIN emp3 b ON a.deptno = b.dept_id;
SELECT * FROM dept3 a RIGHT JOIN emp3 b ON a.deptno = b.dept_id;
-- SELECT * FROM A RIGHT JOIN B on 条件1 RIGHT JOIN C on 条件2 RIGHT JOIN D on 条件3; 

-- 实现满外连接
-- 使用union关键字实现左外连接和右外连接的并集
-- union 将两个长训结果上下拼接，并去重
SELECT * FROM dept3 a LEFT OUTER JOIN emp3 b ON a.deptno = b.dept_id
UNION
SELECT * FROM dept3 a RIGHT JOIN emp3 b ON a.deptno = b.dept_id;
-- union all是将两个长训结果上下拼接，不去重
SELECT * FROM dept3 a LEFT OUTER JOIN emp3 b ON a.deptno = b.dept_id
UNION ALL
SELECT * FROM dept3 a RIGHT JOIN emp3 b ON a.deptno = b.dept_id;

子查询

概念：子查询就是值的一个完整的查询语句中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套查询。

特点
子查询可以返回的数据类型一共分为四种：

单行单列：返回的是一个具体列的内容，可以理解为一个单值数据
单行多列：返回一行数据中多个列的呢日用
多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围
多行多列：查询返回的结果是一张临时表

基本子查询

-- 子查询就是值的在一个完整的查询语句之中，嵌套若干个不通功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询
-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字、员工年龄
-- 步骤：
-- 1.查询最大年龄
SELECT MAX(age) FROM emp3;
-- 2.让每个员工的年龄和最大年龄进行比较，相等则满足条件
SELECT * FROM emp3 WHERE age = (SELECT MAX(age) FROM emp3);

-- 查询研发部和销售部的员工信息，包含员工号、员工名字
-- 方式一:关联查询
SELECT * FROM dept3 a JOIN emp3 b ON a.deptno = b.dept_id AND ( name in('研发部','销售部'));
-- 方式二：子查询
-- 步骤：
-- 1.先查询研发部和销售部的部门号
SELECT deptno FROM dept3 WHERE name in('研发部','销售部');
-- 2.查询那个员工的部门号是1001或1002
SELECT * FROM emp3 WHERE dept_id in (SELECT deptno FROM dept3 WHERE name in('研发部','销售部'));


-- 查询研发部20岁以下的员工信息，包括员工号、员工名字，部门名字
-- 方式一：关联查询
SELECT * FROM dept3 a JOIN emp3 b ON a.deptno = b.dept_id AND (name = '研发部'AND age <20);
-- 方式二：子查询
-- 步骤：
-- 1.在部门表中查询研发部信息
SELECT * FROM dept3 WHERE name = '研发部';
-- 2.在员工表中查询年龄小于20岁的员工信息
SELECT * FROM emp3 WHERE age < 20;
-- 3.将以上两个查询的结果进行关联查询
SELECT * 
FROM
(SELECT * FROM dept3 WHERE name = '研发部') t1 
JOIN
(SELECT * FROM emp3 WHERE age < 20) t2 ON t1.deptno = t2.dept_id;

子查询关键字

在子查询中，有一些常用的逻辑关键字，这些关键字可以给我们提供能丰富的查询功能，主要关键字如下：

ALL关键字
ANY关键字
SOME关键字
IN关键字
EXISTS关键字

ALL关键字
方法：
select …… from…… where c > all （查询语句）
等价于
select ……from …… where c > result1 and c > result2 and c > result3

特点：

ALL：与子查询返回的所有制比较为TRUE 则返回true
ALL可以与=、>、>=、<、<=、<>结合来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的所有数据
ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以退出其他的比较运算符情况

例如：

-- 1.查询年龄大于‘1003’部门的所有年龄的员工信息
SELECT * FROM emp3 WHERE age > all (SELECT age FROM emp3 WHERE dept_id = '1003');

-- 2.查询不属于任何一个部门的员工信息
SELECT * FROM emp3 WHERE dept_id <> all(SELECT deptno FROM dept3 );

ANY和SOME

方法：
select …… from…… where c > any（查询语句）
等价于
select ……from …… where c > result1 or c > result2 or c > result3

特点：

ANY：与子查询返回的任何字比较为true 则返回ture
ANY可以与=、>、>=、<、<=、<>结合来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据结合
表示指定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以退出比其他的比较运算符的情况
SOME和ANY的作用一样，SOME可以理解为ANY的别名

例如：

-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
SELECT * FROM emp3 WHERE age > ANY(SELECT age FROM emp3 WHERE dept_id='1003') and dept_id <>'1003';
SELECT * FROM emp3 WHERE age > SOME(SELECT age FROM emp3 WHERE dept_id='1003') and dept_id <>'1003';

IN关键字

方法：
select …… from…… where c in（查询语句）
等价于
select ……from …… where c = result1 or c = result2 or c = result3

特点：

IN关键字，用于判断某个记录的值，是否在指定的集合中
在IN关键字前面加上not可以将条件反过来


-- 查询研发部和销售部的员工信息，包含员工号、员工名字
SELECT eid,ename FROM emp3 WHERE dept_id in (SELECT  deptno FROM dept3 WHERE name in ('研发部','销售部'));

EXISTS关键字

方法：
select……from……where exists (查询语句)

特点：

该子查询如果“有数据结果”（至少返回一行数据），则该EXISTS（）的结果为“true”，外层查询执行
该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS（）的结果为“false”，外层查询不执行
EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where 条件成立
注意，EXISTS关键字比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字

例如：

-- 查询公司是否有大于等于60岁的员工，有则输出
SELECT * FROM emp3 a WHERE EXISTS(SELECT * FROM emp3 WHERE a.age >= 60);
 
 SELECT * FROM emp3 a WHERE eid in(SELECT eid FROM emp3 WHERE a.age >= 60);
-- 查询有所属部门的员工信息
SELECT * FROM emp3 a WHERE EXISTS(SELECT * FROM dept3 b  WHERE a.dept_id=b.deptno);

SELECT * FROM emp3 a WHERE dept_id in(SELECT deptno FROM dept3 b WHERE a.dept_id=b.deptno);

自关联查询

概念：MySQL有事在信息查询是需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。注意自关联时必须给表起别名。

方法一：
select 字段列表 from 表1 a, 表1 b where 条件;
方法二：
select 字段列表 from 表1 a [left] join 表1 b on 条件;

例如：

-- 创建表，并建立自关联约束
CREATE TABLE t_sanguo(
eid INT PRIMARY KEY,
ename VARCHAR(20),
manager_id INT,
FOREIGN KEY(manager_id) REFERENCES t_sanguo(eid)
);

INSERT INTO t_sanguo VALUES(1,'刘协',NULL);
INSERT INTO t_sanguo VALUES(2,'刘备',1);
INSERT INTO t_sanguo VALUES(3, '关羽', 2);
INSERT INTO t_sanguo VALUES(4, '张飞', 2);
INSERT INTO t_sanguo VALUES(5, '曹操', 1);
INSERT INTO t_sanguo VALUES(6, '许褚', 5);
INSERT INTO t_sanguo VALUES(7, '典韦', 5);
INSERT INTO t_sanguo VALUES(8, '孙权', 1);
INSERT INTO t_sanguo VALUES(9, '周瑜', 8);
INSERT INTO t_sanguo VALUES(10, '鲁肃', 8);

--  进行关联查询
-- 1.查询每个三国人物及他的上级信息
SELECT * FROM t_sanguo a,t_sanguo b WHERE a.manager_id = b.eid;
SELECT a.ename, b.ename FROM t_sanguo a,t_sanguo b WHERE a.manager_id = b.eid;

-- 2. 查询所有任务及上级
SELECT a.ename, b.ename FROM t_sanguo a  LEFT JOIN t_sanguo b on a.manager_id = b.eid;

-- 3. 查询所有任务、上级，上上级 比如：张飞 刘备  刘协

SELECT a.ename, b.ename FROM t_sanguo a LEFT JOIN t_sanguo b on a.manager_id = b.eid LEFT JOIN t_sanguo c on b.manager_id = c.eid;

多表操作练习题

MySQL多表操作练习题

火眼猊

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL多表操作

多表关系概念：在实际开发中，一个项目通常需要多张表才能完成。多表关系可以概括为：一对一、一对多/多对一，多对多一对一关系一个学生只有一张身份证，一张身份证只能对应一个学生在任一表中添加唯一外键，指向另一方主键，确保一对一关系一般一对一关系很少见，遇到一对一关系的表最好是合并表一对多/多对一一个部门有多个员工，一个员工只能对应一个部门再多的一方建立外键，指向一的一方的主键多对多关系一个学生可以选择很多门课程，一个课程也可以被很多学生选择多对多关系实现需要借助第三张中间表。中间
复制链接

扫一扫