MySQL—事务、多表查询-优快云博客

本文详细介绍了事务控制语言（TCL）的概念及其在数据库操作中的应用，包括手动提交、自动提交、插入数据、查询数据、多表查询、外键约束、数据修改等关键操作，并展示了如何在实际场景中实现数据的正确管理和维护。

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

1. 事务控制语言（Transaction Control Language，TCL）

事务是访问并可能更新数据库中各种数据项的一个程序执行单元（unit），在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。事务应该具有4个属性：原子性（atomicity）、一致性（consistency ）、隔离性（isolation）、持续性（durabbility），这4个属性通常称为ACID特性。

默认事务的提交方式

set autocommit=false：设置事务提交方式为手动提交

set autocommit=true：设置事务提交方式为自动提交

事务就是对数据库的多不操作，要么一起成功，要么一起失败。

commit：手动提交事务

rollback：回滚事务

savepoint poin2：保存还原点

rollbock to point2：回滚到point2还原点

设定事务提交方式为手动提交：

mysql> set autocommit=false;
Query OK, 0 rows affected (0.00 sec)

向表中插入数据：

mysql> insert into emp
    -> (name,gender,hiredate,salary,job,comm)
    -> values
    -> ("小强","m","2016-2-05",12000,"项目经理",700);
Query OK, 1 row affected (0.00 sec)

查询：

mysql> select * from emp;
+----+--------+--------+------------+----------+--------------+---------+
| id | name   | gender | hiredate   | salary   | job          | comm    |
+----+--------+--------+------------+----------+--------------+---------+
|  1 | 张三   | m      | 2016-02-12 |  5000.00 | 老师         |  200.00 |
|  2 | 李四   | m      | 2016-02-12 |  8000.00 | 设计师       |  500.00 |
|  4 | 王五   | f      | 2016-01-25 |  1000.00 | 总监         | 1000.00 |
|  5 | 小强   | m      | 2016-02-05 | 12000.00 | 项目经理     |  700.00 |
+----+--------+--------+------------+----------+--------------+---------+
4 rows in set (0.00 sec)

发现事务并没有提交，但是小强这个员工的信息已经在数据库中了，其实并没有在，只是存在于内存中。如果这是打开另一个mysql客户端去查询会发现并没有小强这个员工的信息。

多表查询

在mydb下再建一个dept表，查询其信息：

mysql> select * from dept;
+----+-----------+--------------------+
| id | name      | descr              |
+----+-----------+--------------------+
|  1 | 开发部    | 负责平台开发       |
|  2 | 教学部    | 负责授课           |
+----+-----------+--------------------+
2 rows in set (0.00 sec)

查看员工表

mysql> select * from emp;
+----+--------+--------+------------+----------+--------------+---------+
| id | name   | gender | hiredate   | salary   | job          | comm    |
+----+--------+--------+------------+----------+--------------+---------+
|  1 | 张三   | m      | 2016-02-12 |  5000.00 | 老师         |  200.00 |
|  2 | 李四   | m      | 2016-02-12 |  8000.00 | 设计师       |  500.00 |
|  4 | 王五   | f      | 2016-01-25 |  1000.00 | 总监         | 1000.00 |
|  5 | 小强   | m      | 2016-02-05 | 12000.00 | 项目经理     |  700.00 |
+----+--------+--------+------------+----------+--------------+---------+
4 rows in set (0.00 sec)

如果想要让这两个表有关系，比如说每个员工都有所属的部门，所以可以在emp表中添加一个dep_id列（属性），并将这个dep_id设为外键，表示这个员工所属哪个部门。

添加dep_id列：

mysql> alter table emp add column dep_id int;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(100) | YES  |     | NULL    |                |
| gender   | char(1)      | YES  |     | NULL    |                |
| hiredate | date         | YES  |     | NULL    |                |
| salary   | float(10,2)  | YES  |     | NULL    |                |
| job      | varchar(100) | YES  |     | NULL    |                |
| comm     | float(7,2)   | YES  |     | NULL    |                |
| dep_id   | int(11)      | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

将dep_id设为外键：

语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);（其中FK_ID是外键的名称）

mysql> alter table emp add foreign key(dep_id) references dept(id);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

查看emp表：

mysql> select * from emp;
+----+--------+--------+------------+----------+--------------+---------+--------+
| id | name   | gender | hiredate   | salary   | job          | comm    | dep_id |
+----+--------+--------+------------+----------+--------------+---------+--------+
|  1 | 张三   | m      | 2016-02-12 |  5000.00 | 老师         |  200.00 |   NULL |
|  2 | 李四   | m      | 2016-02-12 |  8000.00 | 设计师       |  500.00 |   NULL |
|  4 | 王五   | f      | 2016-01-25 |  1000.00 | 总监         | 1000.00 |   NULL |
|  5 | 小强   | m      | 2016-02-05 | 12000.00 | 项目经理     |  700.00 |   NULL |
+----+--------+--------+------------+----------+--------------+---------+--------+
4 rows in set (0.00 sec)

修改emp表中的dep_id数据：

mysql> update emp set dep_id=2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update emp set dep_id=1 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update emp set dep_id=1 where id=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update emp set dep_id=1 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看表中数据：

mysql> select * from emp;
+----+--------+--------+------------+----------+--------------+---------+--------+
| id | name   | gender | hiredate   | salary   | job          | comm    | dep_id |
+----+--------+--------+------------+----------+--------------+---------+--------+
|  1 | 张三   | m      | 2016-02-12 |  5000.00 | 老师         |  200.00 |      2 |
|  2 | 李四   | m      | 2016-02-12 |  8000.00 | 设计师       |  500.00 |      1 |
|  4 | 王五   | f      | 2016-01-25 |  1000.00 | 总监         | 1000.00 |      1 |
|  5 | 小强   | m      | 2016-02-05 | 12000.00 | 项目经理     |  700.00 |      1 |
+----+--------+--------+------------+----------+--------------+---------+--------+
4 rows in set (0.00 sec)

查询出工资最高的员工：

mysql> select name,salary from emp
    -> where salary=
    -> (select max(salary) from emp);
+--------+----------+
| name   | salary   |
+--------+----------+
| 小强   | 12000.00 |
+--------+----------+
1 row in set (0.00 sec)

查询出每个部门的平均月薪并按照工资降序进行排序：

mysql> select dep_id,avg(salary) from emp group by dep_id order by avg(salary) desc;
+--------+-------------+
| dep_id | avg(salary) |
+--------+-------------+
|      1 | 7000.000000 |
|      2 | 5000.000000 |
+--------+-------------+
2 rows in set (0.00 sec)

查询月薪比平均薪水高的员工：

mysql> select name,salary from emp
    -> where salary>
    -> (select avg(salary) from emp);
+--------+----------+
| name   | salary   |
+--------+----------+
| 李四   |  8000.00 |
| 小强   | 12000.00 |
+--------+----------+
2 rows in set (0.00 sec)