mysql联表查询_mysql级联查询-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_44482877/article/details/130166204

文章展示了如何通过外键约束在MySQL中建立员工表和部门表之间的关系，强调了外键对于保持数据一致性和完整性的关键作用。通过级联删除和级联修改，确保了删除或更新主表时，副表相应记录的同步操作，防止了数据不一致的情况发生。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建一张表，其中包括id、员工的名字、员工部门、创建时间

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table employee(
    -> id int primary key auto_increment,
    -> emp_name varchar(32) not null,
    -> dept_name varchar(32) not null,
    -> create_time timestamp default current_timestamp
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into employee (emp_name, dept_name) values("老邢", "教学部");
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee (emp_name, dept_name) values("骚磊", "教学部");
Query OK, 1 row affected (0.02 sec)

mysql> insert into employee (emp_name, dept_name) values("二贝", "品保部");
Query OK, 1 row affected (0.02 sec)

mysql> insert into employee (emp_name, dept_name) values("二佳", "品保部");
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee (emp_name, dept_name) values("吉首", "技术部");
Query OK, 1 row affected (0.01 sec)

mysql> select * from employee;
+----+----------+-----------+---------------------+
| id | emp_name | dept_name | create_time         |
+----+----------+-----------+---------------------+
|  1 | 老邢     | 教学部    | 2023-04-15 09:58:48 |
|  2 | 骚磊     | 教学部    | 2023-04-15 09:58:54 |
|  3 | 二贝     | 品保部    | 2023-04-15 09:59:00 |
|  4 | 二佳     | 品保部    | 2023-04-15 09:59:05 |
|  5 | 吉首     | 技术部    | 2023-04-15 09:59:11 |
+----+----------+-----------+---------------------+
5 rows in set (0.00 sec)

这种设计表是有问题的，部门的数据会出现冗余，这样是不好的，可以拆开成两个表，一个员工表一个部门表

两个表

mysql> create table dept(
    -> dept_id int primary key auto_increment,
    -> dept_name varchar(32)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table emp (
    -> emp_id int primary key auto_increment,
    -> emp_name varchar(32) ,
    -> dept_id int,
    -> reg_time timestamp default current_timestamp
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into  dept(dept_name) values("教学部");
Query OK, 1 row affected (0.02 sec)

mysql> insert into  dept(dept_name) values("品保部");
Query OK, 1 row affected (0.01 sec)

mysql> insert into  dept(dept_name) values("技术部");
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp (emp_name, dept_id) values ("老邢", 1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into emp (emp_name, dept_id) values ("骚磊", 1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into emp (emp_name, dept_id) values ("二贝", 2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp (emp_name, dept_id) values ("二佳", 2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp (emp_name, dept_id) values ("吉首", 3);
Query OK, 1 row affected (0.02 sec)

mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 教学部    |
|       2 | 品保部    |
|       3 | 技术部    |
+---------+-----------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      1 | 老邢     |       1 | 2023-04-15 10:04:36 |
|      2 | 骚磊     |       1 | 2023-04-15 10:04:49 |
|      3 | 二贝     |       2 | 2023-04-15 10:05:03 |
|      4 | 二佳     |       2 | 2023-04-15 10:05:19 |
|      5 | 吉首     |       3 | 2023-04-15 10:05:31 |
+--------+----------+---------+---------------------+
5 rows in set (0.00 sec)

这时有一个要求：插入一条员工数据，写入一个dept_id=8的数据，能否插入成功呢？

mysql> insert into emp (emp_name, dept_id) values("果冻", 8);
Query OK, 1 row affected (0.02 sec)

mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      1 | 老邢     |       1 | 2023-04-15 10:04:36 |
|      2 | 骚磊     |       1 | 2023-04-15 10:04:49 |
|      3 | 二贝     |       2 | 2023-04-15 10:05:03 |
|      4 | 二佳     |       2 | 2023-04-15 10:05:19 |
|      5 | 吉首     |       3 | 2023-04-15 10:05:31 |
|      6 | 果冻     |       8 | 2023-04-15 10:07:47 |
+--------+----------+---------+---------------------+
6 rows in set (0.00 sec)

可以看到数据插入成功了，这是不符合开发场景的

再来一个需求：删除一个部门：如果我将一个部门删除技术部，真实的需求的是删除一个部门，部门下面所对应员工也要全部删除

mysql> delete from dept where dept_id = 3;
Query OK, 1 row affected (0.02 sec)

mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      1 | 老邢     |       1 | 2023-04-15 10:04:36 |
|      2 | 骚磊     |       1 | 2023-04-15 10:04:49 |
|      3 | 二贝     |       2 | 2023-04-15 10:05:03 |
|      4 | 二佳     |       2 | 2023-04-15 10:05:19 |
|      5 | 吉首     |       3 | 2023-04-15 10:05:31 |
|      6 | 果冻     |       8 | 2023-04-15 10:07:47 |
+--------+----------+---------+---------------------+
6 rows in set (0.00 sec)

添加员工的时候，随便可以添加，尽管这个部门不存在，删除一个部门。部门下面的员工也还在。这些都是实际开发中不合理的，造成不合理的原因，这两张表是没有关系的！！！
如何让两张表产生关系呢？使用外键

两个表

	constraint  约束的意思
    fk_emp_dept  外键的名字 随意起
    foreign key(dept_id)  外键 使用本表中的一个字段作为外键去和别的表产生关系
    references 关联 引用的意思，去关联主表里面的某一个字段（外键所在的表叫副表）

mysql> drop table emp;
Query OK, 0 rows affected (0.02 sec)

mysql> create table emp (
    -> emp_id int primary key auto_increment,
    -> emp_name varchar(32) ,
    -> dept_id int,
    -> reg_time timestamp default current_timestamp,
    -> constraint fk_emp_dept foreign key(dept_id)  references dept(dept_id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 教学部    |
|       2 | 品保部    |
+---------+-----------+
2 rows in set (0.00 sec)

#插入数据
mysql> insert into emp (emp_name, dept_id) values("老邢", 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp (emp_name, dept_id) values("骚磊", 1);
Query OK, 1 row affected (0.01 sec)

#插入了一个不存在的部门的时候 结果报错
mysql> insert into emp (emp_name, dept_id) values("果冻", 4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))

#删除数据  删除教学部 (需要先在emp表中先把dept_id = 1的员工删除完)
mysql> delete from dept where dept_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))

#修改数据(需要先将emp表中的dept_id全部改为5)
mysql> update dept set dept_id  = 5 where dept_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))

#加上外键约束以后，增删改有的时候会报错
#主表（部门表）副表（员工表）
#增加的时候：先看主表然后增加副表
#删除的时候：先删除副表再删除主表
#修改的时候: 先修改副表再修改主表
#这样才能不报错
#太麻烦了，还得记这些
#级联操作！！！级联修改和级联删除

mysql> drop table emp;
Query OK, 0 rows affected (0.02 sec)

mysql> create table emp (
    -> emp_id int primary key auto_increment,
    -> emp_name varchar(32) ,
    -> dept_id int,
    -> reg_time timestamp default current_timestamp,
    -> constraint fk_emp_dept foreign key(dept_id)  references dept(dept_id)
    #加上两个状态
    -> on delete cascade
    -> on update cascade
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into emp (emp_name, dept_id) values("老邢", 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp (emp_name, dept_id) values ("骚磊", 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp (emp_name, dept_id) values ("二贝", 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp (emp_name, dept_id) values ("二佳", 2);
Query OK, 1 row affected (0.02 sec)

mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      1 | 老邢     |       1 | 2023-04-15 10:26:14 |
|      2 | 骚磊     |       1 | 2023-04-15 10:26:33 |
|      3 | 二贝     |       2 | 2023-04-15 10:26:41 |
|      4 | 二佳     |       2 | 2023-04-15 10:26:52 |
+--------+----------+---------+---------------------+
4 rows in set (0.00 sec)

#修改
mysql> update dept set dept_id = 3 where dept_id  =2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      1 | 老邢     |       1 | 2023-04-15 10:26:14 |
|      2 | 骚磊     |       1 | 2023-04-15 10:26:33 |
|      3 | 二贝     |       3 | 2023-04-15 10:26:41 |
|      4 | 二佳     |       3 | 2023-04-15 10:26:52 |
+--------+----------+---------+---------------------+
4 rows in set (0.00 sec)

真实开发的时候，特别是外键约束的表的时候，要加上级联删除和级联修改

增删改和外键约束有关查询和外键约束没有关系

mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time            |
+--------+----------+---------+---------------------+
|      1 | 老邢     |       1 | 2023-04-15 10:26:14 |
|      2 | 骚磊     |       1 | 2023-04-15 10:26:33 |
|      3 | 二贝     |       3 | 2023-04-15 10:26:41 |
|      4 | 二佳     |       3 | 2023-04-15 10:26:52 |
+--------+----------+---------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 教学部    |
|       3 | 品保部    |
+---------+-----------+
2 rows in set (0.00 sec)

联表查询查看二贝属于哪个部门

select emp.emp_name, dept.dept_name # 查什么
from emp, dept #从哪查
where emp.dept_id = dept.dept_id and emp.emp_name = “erbei” #查询的条件

mysql> select emp.emp_name, dept.dept_name
    -> from emp, dept
    -> where emp.dept_id = dept.dept_id and emp.emp_name = "二贝";
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| 二贝     | 品保部    |
+----------+-----------+
1 row in set (0.01 sec)

#对表起表名
select e.emp_name, d.dept_name
from emp e, dept d
where e.dept_id = d.dept_id and e.emp_name = "二贝";

mysql> select e.emp_name as "员工名字", d.dept_name  as "部门名字"
    -> from emp e, dept d
    -> where e.dept_id = d.dept_id and e.emp_name = "二贝";
+--------------+--------------+
| 员工名字     | 部门名字     |
+--------------+--------------+
| 二贝         | 品保部       |
+--------------+--------------+
1 row in set (0.00 sec)

找出教学部有有哪些人

select d.dept_name, e.emp_name
from dept d, emp e
where d.dept_id = e.dept_id and d.dept_name= “教学部”;

mysql> select d.dept_name, e.emp_name
    -> from dept d, emp e
    -> where d.dept_id = e.dept_id  and d.dept_name= "教学部";
+-----------+----------+
| dept_name | emp_name |
+-----------+----------+
| 教学部    | 老邢     |
| 教学部    | 骚磊     |
+-----------+----------+
2 rows in set (0.00 sec)