SQL管理事务处理、使用游标、高级SQL特征

管理事务处理

事务处理

使用事务处理（transaction processing），通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。
事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。
1、事务（transaction）指一组SQL语句；
2、回退（rollback）指撤销指定SQL语句的过程；
3、提交（commit）指将未存储的SQL语句结果写入数据库表；
4、保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。
事务处理用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT语句（回退SELETE语句也没有必要），也不能回退CREATE或DROP操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。
参考：https://blog.csdn.net/hfdgjjg/article/details/87925535

控制事务处理

管理事务的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

START TRANSACTION;
……

使用ROLLBACK回退（撤销）SQL语句

BEGIN;
SET FOREIGN_KEY_CHECKS = 0;  -- 临时设置外键失效
DELETE FROM Orders;   -- 执行删除操作
SET FOREIGN_KEY_CHECKS = 1;  -- 操作结束后恢复外键
ROLLBACK;

使用COMMIT提交事务

BEGIN;
INSERT INTO Orders VALUE(
	'20010',
    current_time(),
    '1000000002');
COMMIT;

使用保留点

使用简单的ROLLBACK和COMMIT语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。
要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。

BEGIN;
INSERT INTO Customers(cust_id, cust_name)
VALUES('1000000010', 'Toys Emporium');
SAVEPOINT test;
INSERT INTO Customers(cust_id)
VALUES(strings);
COMMIT;

第一条插入语句没有问题，可以插入，之后定义了一个保留点，再进行错误的插入时，第一条的插入操作不受影响，仍然是成功插入，第二条插入失败。

保留点越多越好

在SQL代码中设置的保留点越多，就越能灵活地进行回退。

使用游标

游标

SQL检索操作返回一组称为结果集（SELECT SET）的行，这组返回的行都是与SQL语句相匹配的行（零行或多行）。简单地使用SELECT语句，没有办法得到第一行、下一行或前10行。但这是关系DBMS功能的组成部分。
有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。
游标（cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
游标的选项和特征如下：
a、能够标记游标为只读，使数据能读取，但不能更新和删除。
b、能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）。
c、能标记某些列为可编辑的，某些列为不可编辑的。
d、规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问。
e、指示DBMS对检索出的数据（而不是指表中活动数据）进行复制，使数据在游标打开和访问期间不变化。
游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

使用游标

a、在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句和游标选项。
b、一旦声明，就必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
c、对于填有数据的游标，根据需要取出（检索）各行。
d、在结束游标使用时，必须关闭游标，可能的话，释放游标。
声明游标后，可根据需要频繁地打开和关闭游标。在游标打开时，可根据需要频繁地执行取操作。

创建游标

（期间出现了建表错误，解决参考：https://bbs.csdn.net/topics/392270715）
游标参考：
1、https://www.cnblogs.com/progor/p/8875100.html
2、https://blog.csdn.net/qq_36213352/article/details/83059805
3、https://blog.csdn.net/wuzuyu365/article/details/79232468

首先，建立一个新表：

create table `tysql`.`new_table` (
	cust_id 		CHAR(10),  -- 以逗号分隔开
	cust_name 		CHAR(50),
	cust_address 	CHAR(50),
	cust_city 		CHAR(50),
	cust_state 		CHAR(5),
	cust_zip 		CHAR(10),
	cust_country 	CHAR(50),
	cust_contact 	CHAR(50),
	cust_email 		CHAR(255)
)

然后，游标使用方法如下：

DELIMITER //
# 创建存储过程
CREATE PROCEDURE test() 
BEGIN
	DECLARE 	cust_id 		CHAR(10);  -- 创建接收游标数据的变量
	DECLARE 	cust_name   	CHAR(50);
	DECLARE 	cust_address 	CHAR(50);
	DECLARE 	cust_city 		CHAR(50);
	DECLARE 	cust_state 		CHAR(5);
	DECLARE 	cust_zip 		CHAR(10);
	DECLARE 	cust_country 	CHAR(50);
	DECLARE 	cust_contact 	CHAR(50);
	DECLARE 	cust_email 		CHAR(255);
	DECLARE flag int default 0;  -- 创建结束标志变量flag
	DECLARE CustCursor CURSOR FOR SELECT Customers.* FROM Customers WHERE Customers.cust_email IS NULL;  
	# 创建游标，尤其要注意的是，因为建立了一个与Customers表结构完全相同的表new_table，所以，查询及过滤操作时必须要加上“Customers.”
	DECLARE continue handler for not found set flag = 1;  -- 结束标志变量flag初始为真
	OPEN CustCursor;  -- 打开游标
	read_loop:loop  -- 开始循环游标里的数据
	FETCH CustCursor INTO cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email;  
	# 根据游标当前指向的一条数据
	if flag=1 then  -- 当无法fetch会触发handler continue
		leave read_loop;  -- 跳出游标循环
	end if;
	INSERT INTO new_table VALUES(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email);  
	# 这里是为了显示获取结果
	END LOOP;  -- 结束游标循环
	CLOSE CustCursor;  -- 关闭游标
END;
CALL test();  -- 调用存储过程
select * from new_table;  -- 显示表的内容

高级SQL特征

约束

约束是管理如何插入或处理数据库数据的规则。
DBMS通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的，用CREATE TABLE或ALTER TABLE语句。

主键

主键是一种特殊的约束。
一种定义主键的方法是创建它。如下所示：

CREATE TABLE Vendors(
	cust_id 		CHAR(10) 		NOT NULL PRIMARY KEY,
	cust_name   	CHAR(50) 		NOT NULL,
	cust_address 	CHAR(50) 		NULL,
	cust_city 		CHAR(50) 		NULL,
	cust_state 		CHAR(5) 		NULL,
	cust_zip 		CHAR(10) 		NULL,
	cust_country 	CHAR(50) 		NULL
);

还可以使用CONSTRAINT语法定义相同的列为主键。（定义主键方法二选一）

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

外键

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。
定义外间的方法：

CREATE TABLE Orders
(
	order_num 	INTEGER 		NOT NULL PRIMARY KEY,
	order_date	DATETIME 		NOT NULL,
	cust_id		CHAR(10) 		NOT NULL REFERENCES Customers(cust_id)
);

其中，表定义使用了REFERENCES关键字，它表示cust_id中的任何值都必须是Customers表的cust_id中的值。
相同的工作也可以在ALTER TABLE语句中用CONSTRAINT语法来完成：

ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

外键除帮助保证引用完整性外，还有助于防止意外删除。

唯一约束

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。
a、表可包含多个唯一约束，但每个表只允许一个主键。
b、唯一约束列可包含NULL值。
c、唯一约束列可修改或更新。
d、唯一约束列的值可重复使用。
e、与主键不一样，唯一约束不能用来定义外键。
employees表是一个使用约束的例子。每个雇员都有唯一的社会安全号，但我们并不想用它作主键，因为它太长（而且我们也不想使该信息容易利用）。因此，每个雇员除了其社会安全号外还有唯一的雇员ID（主键）。
雇员ID是主键，可以确定它是唯一的。你可能还想使DBMS保证每个社会安全号也是唯一的（保证输入错误不会导致使用他人号码）。可以通过在社会安全号列上定义UNIQUE约束做到。
唯一约束的语法类似于其他约束的语法。唯一约束既可以用UNIQUE关键字在表定义中定义，也可以用单独的CONSTRAINT定义。

检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点：
a、检查最小或最大值。例如，防止0个物品的订单（即使0是合法的数）。
b、指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
c、只允许特定的值。例如，在性别字段中只允许M或F。
换句话说，数据类型限制了列中可保存的数据的类型。检查约束在数据类型内又做了进一步的限制，这些限制极其重要，可以确保插入数据库的数据正是你想要的数据。不需要依赖于客户端应用程序或用户来保证正确获取它，DBMS本身将会拒绝任何无效的数据。
下面的例子保证了所有物品的数量大于0：

CREATE TABLE OrderItems
(
	order_num 	INTEGER 	NOT NULL,
	order_item 	INTEGER 	NOT NULL,
	prod_id 	CHAR(10) 	NOT NULL,
	quantity 	INTEGER 	NOT NULL CHECK (quantity > 0),
	item_price 	MONEY 		NOT NULL
);

利用这个约束，任何插入（或更新）的行都会被检查，保证quantity大于0。
检查名为gender的列只包含M或F，可编写如下的ALTER TABLE语句：

ADD CONSTRAINT CHECK (gender LIKE '[MF]')

索引

在开始创建索引前，以下几点需要注意：
a、索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。
b、索引数据可能要占用大量的存储空间。
c、并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值得数据（如姓或名），能通过索引得到那么多的好处。
d、索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
e、可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。
MySQL索引参考：
1、https://blog.csdn.net/justry_deng/article/details/81458470
2、https://www.runoob.com/mysql/mysql-index.html
检查索引：索引的效率随表数据的增加或改变而变化。许多数据库管理员发现，过去创建的某个理想的索引经过几个月的数据处理后可能变得不再理想了。最好定期检查索引，并根据需要对索引进行调整。

触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT、UPFATE和DELETE操作（或组合）相关联。
与存储过程不一样（存储过程只是简单的存储SQL语句），触发器与单个的表相关联。与Orders表上的INSERT操作相关联的触发器只在Orders表中插入行时执行。类似地，Customers表上的INSERT和UPDATE操作的触发器只在表上出现这些操作时执行。
触发器内的代码具有以下数据的访问权：
a、INSERT操作中的所有新数据
b、UPDATE操作中的所有新数据和旧数据
c、DELETE中删除的数据
触发器的用途：
a、保证数据一致。例如，在INSERT或UPDATE操作中将所有州名转换为大写
b、基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表
c、进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入
d、计算计算列的值或更新时间戳
触发器参考：
1、https://blog.csdn.net/babycan5/article/details/82789099
2、https://blog.csdn.net/u012964753/article/details/81175631

注：MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错

进入数据库：mysql -hlocalhost -uroot -p
显示所有数据库：show databases;
使用某个数据库：use databases;
设置触发器例子：https://jingyan.baidu.com/article/375c8e19d0e16925f3a22972.html
注：只是简单练习，所以例子中可能导致的乱码问题，直接不使用中文字符即可。

一个触发器的简单小例子：

DELIMITER //
CREATE TRIGGER customer_state
AFTER UPDATE ON t_book FOR EACH ROW
BEGIN
UPDATE Customers SET cust_state = Upper(cust_state);
END //
DELIMITER ;
UPDATE t_book set bname='hhh' where id=1;

数据库安全

一般来说，需要保护的操作有：
1、对数据库管理功能（创建表、更改或删除已存在的表等）的访问；
2、对特定数据库或表的访问；
3、访问的类型（只读、对特定列的访问等）；
4、仅通过视图或存储过程对表进行访问；
5、创建多层次的安全措施，从而允许多种基于登录的访问和控制；
6、限制管理用户账号的能力。

小结

约束是实施引用完整性的重要部分。
索引可改善数据检索的功能。
触发器可以用来执行运行前后的处理。
安全选项可用来管理数据访问。