1、数据库完整性
1.1 什么是数据库完整性
数据库完整性是指数据库中数据的正确性和相容性。
1.2 完整性约束条件的作用对象
1.3 定义与实现完整性约束
1.3.1 实体完整性
在MySQL中，实体完整性是通过主键约束和候选键约束实现的。
1.3.2 参照完整性
REFERENCES tbl_name(index_col_name,…)
[ON DELETE reference_option]
[ON UPDATE reference_option]
tbl_name： 指定外键所参照的表名
col_name：指定被参照的列名
on delete | on update：指定参照动作相关的SQL语句
reference_option：指定参照完整性约束的实现策略，（RESTRICT-限制策略（默认的） | CASCADE-级联策略 | SET NULL-置空策略 | NO ACTION-不采取实施策略）
1.3.2.1 练习
1.3.3 用户定义完整性
1.3.3.1 非空约束
NOT NULL
1.3.3.2 约 束
CHECK(expr)
1.4 命名完整性约束
CONSTRAINT[symbol]
symbol：指定的约束名字
用途：只能给基于表的完整性约束指定名字，
无法给基于列的完整性约束指定名字。
1.5 更新完整性约束
使用ALTER TABLE语句更新与列或表有关的各种约束。
1、完整性约束不能直接被修改。（先删除，再增加）
2、使用ALTER TABLE语句，可以独立地删除完整性约束，而不会删除表本身。（DROP TABLE语句删除一个表，则表中所有的完整性约束都会被自动删除）
2、触发器
2.1 什么是触发器
触发器是用户定义在关系表上的一类由事件驱动的数据对象，也是一种保证数据完整性的方法。
2.2 创建触发器
使用CREATE TRIGGER语句创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body
•trigger_name：指定触发器的名称
•trigger_time：指定触发器被触发的时刻
•trigger_event ：指定触发事件
•tbl_name：指定与触发器相关联的表名
•FOR EACH ROW ：指定对于受触发事件影响的每一行都要激活触发器的动作
•trigger_body：指定触发器动作主体
练习
•在数据库mysql_test的表customers中创建一个触发器customers_insert_trigger，用于每次向表customers插入一行数据时，将用户变量str的值设置为one customer added!
•mysql> CREATE TRIGGER mysql_test.customers_insert_trigger AFTER INSERT
-> ON mysql_test.customers FOR EACH ROW SET @str='one customer added!'
Query OK, 0 rows affected(0.13 sec)
mysql> INSERT INTO mysql_test.customers
-> VALUES(NULL,’aa’,’F’,’浙江省’,’滨江区’);
Query OK, 1 row affected(0.13 sec)
mysql> SELECT @str;
+-----------------------------------+
¦ @str ¦
+-----------------------------------+
¦ one customer added! ¦
+-----------------------------------+
1 row in set (0.00 sec)
2.3 删除触发器
（1）DROP TRIGGER [IF EXISTS][schema_name.]trigger_name
IF EXISTS：用于避免在没有触发器的 情况下删除触发器
schema_name：指定触发器所在的数据库的名称
trigger_name：指定要删除的触发器名称
（2）删除数据库mysql_test中的触发器customers_insert_trigger
DROP TRIGGER IF EXISTS mysql_test.customers_insert_trigger;
2.4 使用触发器
2.4.1 INSERT触发器
在INSERT触发器代码内，可引用一个名为NEW（不区分大小写）的虚拟表，来访问被插入的行。
在BEFORE INSERT触发器中，NEW中的值可以被更新。
练习
在数据库mysql_test的表customers中重新创建触发器customers_insert_trigger，用于每次向表customers插入一行数据时，将用户变量str的值设置为新插入客户的id号。
mysql> CREATE TRIGGER mysql_test.customers_insert_trigger AFTER INSERT
-> ON mysql_test.customers FOR EACH ROW SET @str=NEW.cust_id;
Query OK, 0 rows affected(0.34 sec)
mysql> INSERT INTO mysql_test.customers
-> VALUES(NULL,'syxk','F','浙江省','滨江区');
Query OK, 1 row affected(0.11 sec)
mysql> SELECT @str;
+--------------+
¦ @str ¦
+--------------+
¦ 910 ¦
+--------------+
1 row in set (0.00 sec)
2.4.2 DELETE触发器
在DELETE触发器代码内，可引用一个名为OLD（不区分大小写）的虚拟表，来访问被删除的行。
OLD中的值全部是只读的，不能被更新。
2.4.3 UPDATE触发器
在UPDATE触发器代码内，可引用一个名为OLD（不区分大小写）的虚拟表，来访问UPDATE语句执行前的值，也可以引用一个名为NEW（不区分大小写）的虚拟表来访问更新后的值。
2.4.3.1 练习
在数据库mysql_test的表customers中创建一个触发器customers_update_trigger，用于每次更新表customers时，将该表中cust_address列的值设置为cust_contact列的值。
mysql> CREATE TRIGGER mysql_test.customers_update_trigger BEFORE UPDATE ON mysql_test.customers FOR EACH ROW SET NEW.cust_address=OLD.cust_contact;
mysql> UPDATE mysql_test.customers SET cust_address=‘武汉市’ WHERE cust_name=‘syxk’;
mysql> SELECT cust_address FROM mysql_test.customers WHERE cust_name=‘syxk’;
+------------------------+
¦ cust_address ¦
+------------------------+
¦ 滨江区 ¦
+------------------------+
1 row in set (0.00 sec)
3、安全性与访问控制
数据库的安全性是指保护数据库以防止不合法的使用而造成数据泄露、更改或破坏，所以安全性对于任何一个DBMS来说都是至关重要的。
3.1 用户账号管理
身份验证
数据库用户权限确认
3.1.1 使用SELECT USER查看当前登录的用户
select user from mysql.user;
3.1.2 使用CREATE USER语句创建MySQL账户
• CREATE USER user[IDENTIFIED BY [PASSWORD]’password’]
• user：指定创建用户账户。格式：‘user_name’@'hostname'
• IDENTIFIED BY：可选项，指定用户账号对应的口令
• PASSWORD：可选项，指定散列口令
• password：指定用户账号的口令
3.1.2.1 练习
• 在MySQL服务器中添加两个新的用户，其用户名分别为zhangsan和lisi，他们的主机名均为localhost，用户zhangsan的口令为123，用户lisi的口令为对明文456使用PASSWORD()函数加密返回的散列值。
3.1.3 使用DROP USER语句删除用户账号
DROP USER user [,user]…
3.1.3.1 练习
• 删除前面例子中的lisi用户。DROP USER lisi@localhost;
3.1.4 使用RENAME USER语句修改用户账号
RENAME USER old_user TO new_user [,old_user TO new_user] …
• old_user：指定系统中已经存在的用户账号
• new_user：指定新的用户账号
3.1.4.1 练习
• 将前面例子中用户zhangsan的名字修改成wangwu。RENAME USER ‘zhangsan’@’localhost’ TO ‘wangwu’@’localhost’;
3.1.5 使用SET PASSWORD语句修改用户登录口令
SET PASSWORD [FOR user]=
{
PASSWORD(‘new_password’)
|’encrypted password’
}
• for user：可选项，指定欲修改口令的用户
• PASSWORD(‘new_password’)：使用函数PASSWORD()设置新口令new_password
• encrypted password：表示已被函数PASSWORD()加密的口令值
3.1.5.1 练习
• 使用SET PASSWORD语句修改用户登录口令
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('pass');
3.2 账户权限管理
3.2.1 使用GRANT语句为用户授权
GRANT
priv_type [(column_list)]
[,priv_type [(column_list)]] …
ON [object_type] priv_level
TO user_specification [,user_specification] …
[WITH GRANT OPTION]
• priv_type：用于指定权限的名称
• column_list：用于指定权限要授予给表中哪些具体的列
• object_type：用于指定权限授予的对象类型
• priv_level：用于指定权限授予的级别
• TO：用于设定用户的口令，以及指定 被授予权限的用户user
• user_specification：user[IDENTIFIED BY [PASSWORD]’password’]
• WITH ：可选项，用于实现权限的转移或限制
3.2.1.1 练习
• 授予用户zhangsan在数据库mysql_test的表customers上拥有对列cust_id和列cust_name的SELECT权限。
• mysql> GRANT SELECT(cust_id,cust_name)
-> ON mysql_test.customers
-> TO’zhangsan’@’localhost’;
Query OK,0 rows affected(0.02 sec)
• 授予当前系统中一个不存在的用户liming和用户huang，要求创建这两个用户，并设置对应的系统登录口令，同时授予他们在数据库mysql_test的表customers上拥有SELECT和UPDATE的权限。
• mysql> GRANT SELECT,UPDATE
-> ON mysql_test.customers
-> TO 'liming'@'localhost' IDENTIFIED BY '123',
-> 'huang'@'localhost' IDENTIFIED BY '789';
Query OK, 0 rows affected(0.01 sec)
• 授予系统中已存在的wangwu可以在数据库mysql_test中执行所有数据库操作的权限。
• mysql> GRANT ALL
-> ON mysql_test.*
-> TO ‘wangwu’@’localhost’;
Query OK, 0 rows affected(0.00 sec)
• 授予系统中已存在的wangwu拥有创建用户的权限
• mysql> GRANT CREATE USER
-> ON .
-> TO ‘wangwu’@’localhost’;
Query OK, 0 rows affected(0.00 sec)
3.2.2 权限的转移
• 授予当前系统中不存在的用户 zhou 在数据库mysql_test的表customers上拥有SELECT和UPDATE的权限，并允许其可以将自身的这个权限授予给其他用户。
• mysql> GRANT SELECT,UPDATE
-> ON mysql_test.customers
-> TO ‘zhou’@’localhost’ IDENTIFIED BY ‘123’
-> WITH GRANT OPTION;
Query OK, 0 rows affected(0.00 sec)
3.2.3 使用REVOKE语句撤销用户权限
REVOKE
priv_type [(column_list)]
[,priv_type [(column_list)]] …
ON [object_type] priv_level
FROM user [,user] …
3.2.3.1 练习
• 回收系统中已存在用户zhou在数据库mysql_test的表customers上的SELECT权限。
• mysql> REVOKE SELECT
->ON mysql_test.customers
->FROM ‘zhou’@’localhost’;
Query OK, 0 rows affected(0.00 sec)
4、事务与并发控制
4.1 事务的概念
所谓事务是用户定义的一个数据操作序列，这些操作可作为一个完整的工作单元，要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务中的操作一般是对数据的更新操作，包括增、删、改。以BEGIN TRANSACTION语句开始，以COMMIT（提交）语句或ROLLBACK（回滚）语句结束
4.2 事务的特征
4.2.1 ACID
4.2.1.1 原子性(Atomicity)
• 事务是不可分割的最小工作单位，所包含的这些操作所一个整体。
4.2.1.2 一致性(Consistency)
• 事务必须满足数据库的完整性约束，且事务执行完毕后将数据库由一个一致性转变到另一个一致性状态。
4.2.1.3 隔离性(Isolation)
• 事务所彼此独立的、隔离的，即一个事务的执行不能被其他事务所干扰。
4.2.1.4 持续性(Durability)
• 也称为永久性，是指一个事务一旦提交，它对数据库中数据的改变就应该所永久性的，且接下来的其他操作或故障对其执行结果无影响。
4.2.2 练习
依据事务的ACID特征，分析并编写银行数据库系统中的转账事务T：从账户A转账S金额资金到账户B。
首先，考虑到此事务应包含如下操作：
• read(A);
A=A-S;
write(A);
read(B);
B=B+S;
write(B);
• BEGIN TRANSACTION
read(A);
A=A-S;
write(A);
If(A<0)ROLLBACK;
else read(B);
B=B+S
write(B);
COMMIT;}
4.3 并发操作问题
4.3.1 丢失更新
事务T1,T2同时读入同一数据并加以修改，T2的提交结果会破坏T1提交的结果
4.3.2 不可重复读
事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果
4.3.3 读“脏”数据
事务T1修改数据后撤销，使得T2读取的数据与数据库中不一致
4.4 封锁
封锁是最常用的并发控制技术
基本思想：需要时，事务通过向系统请求对它所希望的数据对象加锁，以确保它不被非预期改变
4.4.1 锁
一个锁实质上就是允许或阻止一个事务对一个数据对象的存取特权。
基本的封锁类型：
1、排他锁（X锁），用于写操作
2、共享锁（S锁），用于读操作
4.4.2 封锁的工作原理
(1).若事务T对数据D加了X锁，则所有别的事务对数据D的锁请求都必须等待直到事务T释放锁。
(2).若事务T对数据D加了S锁，则别的事务还可对数据D请求S锁，而对数据D的X锁请求必须等待直到事务T释放锁。
(3).事务执行数据库操作时都要先请求相应的锁，即对读请求S锁，对更新请求X锁。这个过程一般是由DBMS在执行操作时自动隐含地进行。
(4).事务一直占有获得的锁直到结束时释放。
4.4.3 封锁的粒度
我们通常以粒度来描述封锁的数据单元的大小
DBMS可以决定不同粒度的锁粒度越细，并发性就越大，软件复杂性和系统开销也就越大。
4.4.4 封锁的级别
封锁的级别又称为一致性级别或隔离度
0级封锁：不重写其他非0级封锁事务的未提交的更新数据。（实用价值低）
1级封锁：不允许重写未提交的更新数据。防止了丢失更新的发生
2级封锁：既不重写也不读未提交的更新数据（防止了读脏数据）
3级封锁：不读未提交的更新数据，不写任何（包括读操作）未提交数据。
4.4.5 死锁和活锁
活锁——先来先服务
死锁——预防
• 一次性锁请求
• 锁请求排序
• 序列化处理
• 资源剥夺
4.4.6 可串行性
一组事务的一个调度就是它们的基本操作的一种排序。
在数据库系统中，可串行性就是并发执行的正确性准则，即当且当一组事务的并发执行调度是可串行化的，才认为它们是正确的。
4.4.7 两段封锁法(2PL)
1.发展（Growing）或加锁阶段
2.收缩（Shrinking）或释放锁阶段
5 备份与恢复
5.1 数据库备份与恢复的概念
1.计算机硬件故障
2.计算机软件故障
3.病毒
4.人为误操作
5.自然灾害
6.盗窃
数据备份是指通过导出数据或者复制表文件的方式来制作数据库的复本；数据库恢复则是当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。
数据库的恢复是以备份为基础的，它是与备份相对应的系统维护和管理操作。
5.2 备份数据的方法
5.2.1 使用SELECT INTO…OUTFILE语句备份数据
SELECT * INTO OUTFILE ‘file_name’ export_options
| INTO DUMPFILE ‘file_name’
• OUTFILE：导出语句关键字
• TERMINATED BY：指定字段值之间的符号
• ENCLOSED BY ：指定包裹文件中字符值的符号
• ESCAPED BY ：指定转义字符
• TERMINATED BY ：指定一个数据行结束的标志
• DUMPFILE：导出的备份文件里面所有的数据行都会彼此紧挨着放置
• file_name：指定数据备份文件的名称
5.3 恢复数据的方法
5.3.1 使用LOAD DATA…INFILE语句恢复数据
LOAD DATA INFILE ‘file_name.txt’
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY ‘string’]
[[OPTIONALLY]ENCLOSED BY ‘char’]
[ESCAPED BY ‘char’]
]
[LINES
[STARTING BY ‘string’]
[TERMINATED BY ‘string’]
]
• file_name：指定待导入的数据库备份文件名
• tbl_name：指定需要导入数据的表名
• FIELDS：判断字段之间和数据行之间的符号
• STARTING BY：指定一个前缀
• TERMINATED BY：指定一行结束的标志
5.3.2 练习
备份数据库mysql_test中表customers的全部数据到c盘的BACKUP目录下一个名为backupfile.txt的文件中，要求字段值如果是字符则用双引号标注，字段值之间用逗号隔开，每行以问号为结束标志。然后，将备份后的数据导入到一个和customers表结构相同的空表customers_copy中
SELECT * FROM mysql_test.customers
INTO OUTFILE ‘C:/BACKUP/backupfile.txt’
FIELDS TERMINATED BY ’,’
OPTIONALLY ENCLOSED BY “”
LINES TERMINATED BY ‘?’;
LOAD DATA INFILE ‘C:/BACKUP/backupfile.txt’
INTO TABLE mysql_test.customers_copy
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY “”
LINES TERMINATED BY ’?’;