一.SQL特性——事務(wù)管理
image.png
COMMIT 提交
將SQL語句的執(zhí)行結(jié)果寫入數(shù)據(jù)庫表中
ROLLBACK 回退
撤銷SQL語句的執(zhí)行結(jié)果
SAVEPOINT 保留點(diǎn)
時(shí)間點(diǎn)的備份，可以對其發(fā)布回退
MySQL 代碼
D:\mysql\mysql80\mysql -uroot -p
Enter password: ***
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE sqllearning
Database changed
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
+--------+---------+---------+------+------------+------------+
6 rows in set (0.01 sec)
mysql> INSERT INTO pet2 VALUES('test1', 'test', 'test', 'f', '20130901', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
7 rows in set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
7 rows in set (0.00 sec)
mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO pet2 VALUES('test2', 'test', 'test', 'f', '20130901', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
| test2 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
8 rows in set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
7 rows in set (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
7 rows in set (0.00 sec)
mysql> SET AUTOCOMMIT = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
7 rows in set (0.00 sec)
mysql> INSERT INTO pet2 VALUES('test3', 'test', 'test', 'f', '20130901',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
| test3 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
8 rows in set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
| test3 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
8 rows in set (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
| test3 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
8 rows in set (0.00 sec)
mysql> DELETE FROM pet2 WHERE name = 'test3';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
7 rows in set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
| test3 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
8 rows in set (0.00 sec)
mysql> SAVEPOINT sp1;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM pet2 WHERE name = 'test3';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
7 rows in set (0.00 sec)
mysql> SAVEPOINT sp2;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO pet2 VALUES('test3','test','test','m','20180101',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
| test3 | test | test | m | 2018-01-01 | NULL |
+--------+---------+---------+------+------------+------------+
8 rows in set (0.00 sec)
mysql> ROLLBACK TO sp1;
ERROR 1305 (42000): SAVEPOINT sp1 does not exist
mysql> INSERT INTO pet2 VALUES('test2', 'test', 'test', 'f', '20130901', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK TO sp1;
ERROR 1305 (42000): SAVEPOINT sp1 does not exist
mysql> ROLLBACK TO sp2;
ERROR 1305 (42000): SAVEPOINT sp2 does not exist
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
| test3 | test | test | m | 2018-01-01 | NULL |
| test2 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
9 rows in set (0.00 sec)
mysql> DELETE FROM pet2 WHERE name = 'test3';
Query OK, 1 row affected (0.00 sec)
mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVEPOINT sp1;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO pet2 VALUES('test3','test','test','m','20190101',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
| test2 | test | test | f | 2013-09-01 | NULL |
| test3 | test | test | m | 2019-01-01 | NULL |
+--------+---------+---------+------+------------+------------+
9 rows in set (0.00 sec)
mysql> ROLLBACK TO sp1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM pet2;
+--------+---------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+---------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Buffy | Har_old | dog | f | 1989-05-13 | 0000-00-00 |
| Chirpy | Gw%en | bird | f | 1998-09-11 | NULL |
| Claws | Gw%%en | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Fluffy | Har_old | cat | f | 1993-02-04 | NULL |
| test1 | test | test | f | 2013-09-01 | NULL |
| test2 | test | test | f | 2013-09-01 | NULL |
+--------+---------+---------+------+------------+------------+
8 rows in set (0.00 sec)
mysql> RELEASE SAVEPOINT sp1;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
二.SQL高級特性 —— 對字段的約束
image.png
image.png
三.SQL高級特性 —— 索引
image.png
四.數(shù)據(jù)庫安全 —— DBA
image.png
五.課后作業(yè)
Q1：什么是事務(wù)？SQL 中為什么要使用事務(wù)管理？
答：成批執(zhí)行的SQL語句叫作事務(wù)。
使用事務(wù)管理系統(tǒng)，可以確保成批執(zhí)行的語句完全執(zhí)行或完全不執(zhí)行，并且可以通過點(diǎn)，控制語句執(zhí)行進(jìn)度。
使用事務(wù)管理，可以確保數(shù)據(jù)庫信息的完整性。
Q2：請總結(jié) SAVAPOINT 的用法。
答：
創(chuàng)建保留點(diǎn)：SAVEPOINT<保留點(diǎn)名>;
回退到保留點(diǎn)：ROLLBACK TO <保留點(diǎn)名>；
釋放保留點(diǎn)：RELEASE SAVEPOINT<保留點(diǎn)名>；
Q3：請寫出以下語句（Navicat 中操作）
1、從 milk_tea 表復(fù)制出 milk_tea2 表。
2、對 milk_tea 表的 in_price 列增加 UNIQUE 屬性
3、開啟一個(gè)事務(wù)管理
4、插入值 prod_id – ‘T1’, prod_name – ‘測試商品 1’，in_price – 88
5、插入值 prod_id – ‘T2’, prod_name – ‘測試商品 2’，in_price – 88
6、回退
7、重復(fù) 5 的操作
CREATE TABLE milk_tea2
AS
SELECT * FROM milk_tea;
SELECT * FROM milk_tea2;
ALTER TABLE milk_tea ADD CONSTRAINT UNIQUE(in_price);
BEGIN;
INSERT INTO milk_tea(prod_id, prod_name, in_price) VALUES('T1', '測試商品', 88);
SELECT * FROM milk_tea;
INSERT INTO milk_tea(prod_id, prod_name, in_price) VALUES('T2', '測試商品2', 88);
ROLLBACK;
SELECT * FROM milk_tea;
