关于SQL结构化查询相关知识

一、SQL的组成
四大功能：数据查询、数据定义DDL、数据操纵DML和数据控制DCL（data query,data definition,Data manipulation, data control）

1.1 数据定义语言：DDL
主要用于对数据库及数据库中的各种对象进行创建删除、修改等操作。
数据库对象主要有表、默认约束、规则、视图、触发器、存储过程等。
包括SQL语句有：
1.CREATE：用于创建数据库或数据库对象。
2.ALTER：用于对数据库或数据库对象进行修改。
3.DROP：用于删除数据库或数据库对象。

1.2 数据操纵语言：DML
主要用于操纵数据库中各种对象，检索和修改数据。
包括SQL语句主要有：
1.SELECT：用于从表或视图中检索数据，使用最为频繁的语句之一。
2.INSERT：用于将数据插入到表或视图中。
3.UPDATE：用于修改表或视图中的数据，其既可修改表或视图中一行数据，也可同时修改多行或全部数据。
4.DELETE: 用于删除数据表中的数据。

1.3 数据控制语言：DCL
主要用于安全管理。主要SQL语句如下：
1.GRANT：用于授权，把语句许可或对象许可的权限授予其他用户和角色。
2.REVOKE：用于收回权限。

1.4 嵌入式和动态SQL规则：规定了SQL语句在高级程序设计语言中使用的规范方法，以便适应较为复杂的应用。
1.5 SQL调用和会话规则：调用包括SQL例程和调用规则，以便提高SQL的灵活性、有效性、共享性以及使SQL具有更多的高级语言的特征。

二、MySQL中的SQL
MySQL中一个关系对应一个基本表，一个或多个基本表对应一个存储文件，一个表可以有若干索引，索引也存放在存储文件中。
MySQL在SQL标准的基础上增加了部分扩展的语言要素：包括常量、变量、运算符、表达式、函数、流程控制语句和注解等。
1.常量:是指在程序运行过程中值不变的量，也称为字面值或标量值。
常量的使用格式取决于值的数据类型，可分为字符串常量、数值常量、十六进制常量、时间日期常量、位字段值、布尔值和NULL值。
 1.1 字符串常量是指用单引号或双引号括起来的字符序列，分为ASCII字符串常量和Unicode字符串常量。
 1.2 数据值常量可以分为整数常量和浮点数常量。整数常量是不带小数点的十进制数；浮点数常量则是使用小数点的数值常量。
 1.3 一个十六进制值通常指定为一个字符串常量，每对十六进制数字被转换为一个字符，其最前面有一个大写字母X或小写字母x。
 1.4 日期和时间常量是用单引号将表示日期时间的字符串括起来构成的。
 1.5 使用b‘value’格式书写位字段值。Value是一个用0或1书写的二进制值。
 1.6 布尔值的两个值：TRUE（数字值1）和FALSE（数字值0）。
 1.7 NULL值通常用于表示“没有值”“无数据”等意义，与数字类型的“0”或字符串类型的空字符串是完全不同的。
 
2.变量:在MySQL中，变量分为用户变量和系统变量。用户变量前常添加一个符号“@”，大多数系统变量前，添加两个“@”符号。

3.运算符:MySQL几类编程语言中常用的运算符：
算术运算符：+（加）、—（减）、*（乘）、/（除）和%（求模）
位运算符有：&（位与）、|（位或）、^（位异或）、~（位取反）、>>（位右移）、<<（位左移）。
比较运算符：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）、!=（不等于）、<=>（相等或都等于空）。
逻辑运算符：NOT或！（逻辑非）、AND或&&（逻辑与）、OR或||（逻辑或）、XOR（逻辑异或）

4.表达式:是常量、变量、列名、复杂计算、运算符和函数的组合。表达式可分为字符表达式、数值型表达式和日期表达式。

5.内置函数
MySQL包含了100多个函数，基本分类如下：
数学函数，例如ABS()函数、SORT()函数；
聚合函数，例如COUNT()函数；
字符串函数，例如ASCII()函数、CHAR()函数；
日期和时间函数，例如NOW()函数、YEAR()函数；
加密函数，例如ENCODE()函数、ENCRYPT()函数;
控制流程函数，例如IF()函数、IFNULL()函数；
格式化函数，例如FORMAT()函数；
类型转换函数，例如CAST()函数；
系统信息函数，例如USER()函数、VERSION()函数。

三、数据定义语言：DDL
包含数据库以及数据表（关系）的创建、选择、修改、删除、查看等操作。
1. 数据库定义
1.1 创建数据库：
        CREATE DATABASE IF NOT EXISTS DBNAME_Test  -- 是否存在相同名称的数据库
        DEFAULT CHARACTER SET= 'utf8';             -- 设置字符集
1.2 选择数据库：USE DBNAME_Test;
1.3 删除数据库：DROP DATABASE IF EXISTS DBNAME_Test;  -- 如果删除的数据库存在
1.4 修改数据库：ALTER DATABASE DBNAME_Test alter_specification  -- 定义修改数据库的详细描述
1.5 查看数据库：SHOW DATABASES;

2. 数据库表的定义
2.1 创建数据表：
    use dbname_test;
      CREATE [TEMPORARY] TABLE TABNAME_Test   -- TEMPORARY:创建临时表
        (
           id INT NOT NULL AUTO_INCREMENT,   -- 主键递增
             tname CHAR(20) NOT NULL,
             tsex CHAR(1) NOT NULL,
             tage INT DEFAULT 0,
           theight DOUBLE DEFAULT 0,
             tweight DOUBLE DEFAULT 0,
             tbirth DATE DEFAULT '1970-01-01',
             taddress VARCHAR(50),
             ttel int(11),
             tid INT NOT NULL, -- REFERENCES teacher(tid)  添加外键1 
             PRIMARY KEY(id),
             CONSTRAINT `FK1C81D1738DA76` FOREIGN KEY (tid) REFERENCES teacher(tid) -- 添加外键2
        );
        
2.2 修改数据表：
        2.2.1添加新的列
                ALTER TABLE tabname_test
                ADD COLUMN theights INT DEFAULT 0,
                ADD COLUMN tweights INT DEFAULT 0,
                ADD COLUMN tbirth DATE DEFAULT '1970-01-01',
                ADD COLUMN tid INT NOT NULL,
              ADD CONSTRAINT `FK1C81D1738DA76` FOREIGN KEY(tid) REFERENCES teacher(tid);
    2.2.2卸载或删除列
                ALTER TABLE tabname_test
                DROP COLUMN theight,
                DROP COLUMN tweight,
                DROP COLUMN tbirth,
                DROP FOREIGN KEY FK1C81D1738DA76,  -- 解除外键
                DROP COLUMN tid;                   -- 删除外键
        2.2.3更改列的数据类型以及名称
                ALTER TABLE tabname_test
                CHANGE COLUMN theights theight DOUBLE DEFAULT 1,
                CHANGE COLUMN tweights tweight DOUBLE DEFAULT 1;
        2.2.4只更改列的默认值    
                ALTER TABLE tabname_test
                ALTER COLUMN theight  0,
                ALTER COLUMN tweight  0; 
        2.2.5只改变列的数据类型
                ALTER TABLE tabname_test
                MODIFY COLUMN theight DOUBLE,
                MODIFY COLUMN tweight DOUBLE;            
      2.2.6 重命名表：ALTER TABLE studnet RENAME TO tabname_test;
    
2.3 重命名数据表：RENAME TABLE student TO tabname_test,T2 TO NT2....
2.4 删除表：DROP [TEMPORARY] TABLE IF EXISTS T1,t2... [RESTRICT | CASCADE]
2.5 查看表: SHOW [FULL] TABLES [{FROM | IN} dbname_test]
2.6 查看表结构：SHOW [FULL] COLUMNS {FROM | IN} tabname_test [{FROM | IN} dbname_test]
                -- SHOW COLUMNS FROM tabname_test FROM dbname_test | show full columns from dbname_test.tabname_test;
                          DESCRIBE tabname_test; DESC tabname_test;

3 索引
索引是DBMS根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，因而索引实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。
索引存在的弊端如下：
1.索引是以文件的形式存储的，DBMS会将一个表的所有索引保存在同一个索引文件中，索引文件需要占用磁盘空间。
2.索引在提高查询速度的同时，却会降低更新表的速度。
索引在逻辑上通常包含以下几类：
1.普通索引(INDEX)：最基本的索引类型，没有任何限制。
2.唯一性索引(UNIQUE)：索引列中的所有值都只能出现一次，必须是唯一的。 
3.主键索引(PRIMARY KEY)：一种唯一性索引，不允许出现相同的值。

3.1 创建索引
   3.1.1 CREATE UNIQUE INDEX index_t1 ON tabname_test(id,tname);
     3.1.2 CREATE TABLE teacher
           (
                     tid int NOT NULL AUTO_INCREMENT,
                         tname CHAR(20) NOT NULL,
                         age int DEFAULT 0,
                         sex CHAR(1),
                         PRIMARY KEY(tid),
                         UNIQUE INDEX index_t2(tid,tname)  -- 创建表的同时创建索引
                 );
     3.1.2 ALTER TABLE tabname_test ADD UNIQUE INDEX index_t1(id,tname);
                  
3.2 索引的删除：
   3.2.1 DROP INDEX index_t1 ON tabname_test;
     3.2.2 ALTER TABLE tabname_test DROP INDEX index_t1;
 
3.3 索引的查看：SHOW INDEX FROM tabname_test;
                              SHOW INDEXES FROM tabname_test;
                                SHOW KEYS FROM tabname_test;

4.视图
外模式对应到数据库中的概念就是视图。
视图是数据库中的一个对象，它是数据库管理系统提供给用户的以多种角度观察数据库中数据的一种重要机制。
视图仍不同于数据库中真实存在的基本表，它们存在以下区别：
1.视图不是数据库中真实的表，而是一张虚拟表。
2.视图的内容是由存储在数据库中进行查询操作的SQL语句来定义的，它的列数据与行数据均来自于定义视图的查询所引用的真实表，并且这些数据是在引用视图时动态生成的。
3.视图不是以数据集的形式存储在数据库中，它所对应的数据实际上是存储在视图所引用的真实表中。
4.视图是用来查看存储在别处的数据的一种虚拟表，而其自身并不存储数据。
使用视图有如下优点；
1.集中分散数据；
2.简化查询语句；
3.重用SQL语句：保护数据安全；共享所需数据；更改数据格式。

4.1 创建视图
  CREATE VIEW view_showv1 AS 
    SELECT 
        a.id as Sid,
        a.tname as Sname,
        b.tid as Tid,
        b.tname AS Tname
  FROM tabname_test a 
    INNER JOIN teacher b
    ON a.tid=b.tid
    WITH[CASCADED|LOCAL] CHECK OPTION]
    
4.2 删除视图 DROP VIEW[IF EXISTS] view_name[,view_name]… [RESTRICT|CASCADE]
4.3 修改视图 ALTER VIEW view_name[(column_list)] AS 查询语句 [WITH[CASCADED|LOCAL]CHECK OPTION]
4.4 查看视图定义 SHOW CREATE VIEW view_showv1;

5 存储过程
存储过程是一组为了完成某项特定功能的SQL语句集，其实质上就是一段存储在数据库中的代码，它可以由声明式的SQL语句
（如CREATE、UPDATE和SELECT等语句）和过程式SQL语句（如IF…THEN…ELSE控制结构语句）组成。
使用存储过程通常具有以下一些好处：
（1） 可增强SQL语言的功能和灵活性
（2） 良好的封装性
（3） 高性能
（4） 可减少网络流量
（5） 存储过程可作为一种安全机制来确保数据库的安全性和数据的完整性

5.1 创建存储过程 
DELIMITER命令将MySQL语句的结束标志临时修改为其他符号，从而使得MySQL服务器可以完整地处理存储过程体中所有的SQL语句，而后可通过DELIMITER命令再将MySQL语句的结束标志改回为MySQL的默认结束标志，即分号（；）。
DELIMITER命令的使用语法格式是： DELIMITER $$

在MySQL中，是使用CREATE PROCEDURE语句来创建存储过程，其常用的语法格式是：
CREATE PROCEDURE sp_name（[proc_parameter[，…]]）
Routine_body
其中，语法项“proc_parameter”的语法格式是：[IN|OUT|INOUT]param_name type
在此语法格式中：
（1） 语法项 “sp_name”用于 指定存储过程的名称,且默认在当前数据库中创建。
（2） 语法项 “proc_parameter” 用于指定存储过程的参数列表。
（3） 语法项 “routine_body” 表示存储过程的主体部分，也称为存储过程体。

5.2 存储过程体
    5.2.1 局部变量:在存储过程体中可以声明局部变量，用来存储存储过程体中的临时结果。
    在MySQL中,可以使用 DECLARE 语句来声明局部变量，并且同时还可以对该局部变量赋予一个初始值，
    其使用的语法格式是：DECLARE var_name[，…]type[DEFAULT value]

    语法项“var_name”用于指定局部变量的名称；
    语法项“type”用于声明局部变量的数据类型；
    DEFAULT 子句用于为局部变量指定一个默认值，若没有指定，则默认为 NULL。

    需要注意的事项如下：
    （1） 局部变量只能在存储过程体的BEGIN…END语句块中声明。
    （2） 局部变量必须在存储过程体的开头处声明。
    （3） 局部变量的作用范围仅限于声明它的BEGIN…END语句块，其他语句块中的语句不可以使用它。
    （4） 局部变量不同于用户变量，两者的区别是：局部变量声明时，在其前面没有使用@符号，并且它只能被声明它的 BEGIN…END 语句块中的语句所使用；
                而用户变量在声明时，会在其名称前面使用@符号，同时已声明的用户变量存在于整个会话之中。

    5.2.2 SET 语句
    在MySQL中,可以使用SET语句为局部变量赋值，其使用的语法格式是：
    SET var_name=expr[，var_name=expr] …

    5.2.3 SELECT…INTO 语句
    在MySQL中，可以使用SELECT…INTO语句把选定列的值直接存储到局部变量中，其使用的语法格式是：
    SELECT col_name[,…] INTO var_name[,…] table_expr
    在此语法格式中：语法项“col_name”用于指定列名；语法项“var_name”用于指定
    要赋值的变量名；语法项“table_expr”表示 SELECT 语句中的 FROM 子句及后面的语法部分。
    此外，需要注意的是：存储过程体中的 SELECT…INTO 语句返回的结果集只能有一行数据。

    5.2.4 流程控制语句
    在 MySQL 中，可以在存储过程体中，使用条件判断语句和循环语句这样两类用于控制语句流程的过程式 SQL 语句。
    （1）条件判断语句
            常用的条件判断语句有 IF…THEN…ELSE 语句和 CASE 语句。它们的使用语法及方式类似于高级程序设计语言。
    （2）循环语句
            常用的循环语句有 WHILE 语句、REPEAT语句和 LOOP 语句。它们的使用语法及方式同样类似于高级程序设计语言。
            此外，循环语句中还可以使用ITERATE 语句，但它只能出现在循环语句的 LOOP、REPEAT 和 WHILE 子句中，用于表示退出当前循环，且重新开始一个循环。

    5.2.5 游标
    游标是一个被 SELECT 语句检索出来的结果集。在MySQL中，使用游标的具体步骤如下：
        （1） 声明游标
                DECLARE cursor_name CURSOR FOR select_statement
                其中，语法项“cursor_name”用于指定要创建的游标的名称，其命名规则与表名相同；
                语法项“select_statement”用于指定一个 SELECT 语句，其会返回一行或多行的数据，且需注意此处的 SELECT 语句不能有 INTO 子句。
        （2） 打开游标
                OPEN cursor_name
                其中，语法项“cursor_name”用于指定要打开的游标。
        （3） 读取数据
                FETCH cursor_name INTO var_name[,var_name]…
                其中，语法项“cursor_name”用于指定已打开的游标；语法项“var_name”用于指定存放数据的变量名。
        （4） 关闭游标
                CLOSE cursor_name
                其中，语法项“cursor_name”用于要关闭的游标。在使用游标过程中，需要注意以下几点：
                （1） 游标只能用于存储过程或存储函数中，不能单独在查询操作中使用。
                （2） 在存储过程或存储函数中可以定义多个游标，但是在一个 BEGIN…END 语句块中每一个游标的名字必须是唯一的。
                （3） 游标不是一条 SELECT 语句，是被 SELECT 语句检索出来的结果集。

5.3 调用存储过程 
CALL sp_name[parameter[,…]]
CALL sp_name[()]
在此语法格式中：
（1） 语法项“sp_name”用于指定被调用的存储过程的名称。如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。
（2） 语法项“parameter”用于指定调用存储过程所要使用的参数。调用语句中参数的个数必须等于存储过程的参数个数。
（3） 当调用没有参数的存储过程时，使用 CALL sp_name()语句与使用 CALL sp_name 语句是相同的。

5.4 删除存储过程 
DROP PROCEDURE[IF EXISTS] sp_name
其中，语法项“sp_name”用于指定要删除的存储过程的名称。

5.5 DEMO:
DELIMITER $$
CREATE PROCEDURE proc_test(IN id INT,IN tna CHAR(20),IN age INT,IN sex CHAR(1),OUT res CHAR(15))
BEGIN
         DECLARE TID INT DEFAULT 0;
         DECLARE TName CHAR(20);
         DECLARE TAge INT DEFAULT 0;
         DECLARE TSex CHAR(15);
         SET TID=id;
         SET TName=tna;
         SET TAge=age;
         SET TSex=sex;
         INSERT INTO teacher VALUES(Tid,TName,TAge,TSex);
         SET res="OK";
END $$

CALL proc_test(3,'张三',20,'男',@str);
SELECT @str;

6 存储函数
存储函数和存储过程的区别：
（1） 存储函数不能拥有输出参数，这是因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。
（2） 可以直接对存储函数进行调用，且不需要使用 CALL 语句；而对存储过程的调用，需要使用 CALL 语句。
（3） 存储函数中必须包含一条 RETURN 语句，而这条特殊的 SQL 语句不允许包含于存储过程中。

6.1 创建存储函数
CREATE FUNCTION sp_name([func_parameter[，…]])
RETURNS type
routine_body
其中，语法项“func_parameter”的语法格式是：param_name type
在此语法格式中：
（1） 语法项“sp_name”用于指定存储函数的名称，需注意，存储函数不能与存储过程具有相同的名字。
（2） 语法项“func_parameter”用于指定存储函数的参数，这里的参数只有名称和类型，不能指定关键字“IN”“OUT”和“INOUT”。
（3） RETURNS子句用于声明存储函数返回值的数据类型，其中 type 用于指定返回值的数据类型。
（4） 语法项“routine_body”用于指定存储函数的主体部分，也称为存储函数体。所有在存储过程中使用的 SQL 语句在存储函数中同样也适用，
      包括前面所介绍的局部变量、SET语句,流程控制语句、游标等。但是,存储函数体中还必须包含一个RETURN value语句，其中value用于指定存储函数的返回值。

6.2 调用存储函数
成功创建存储函数后，就可以如同调用系统内置函数一样，使用关键字
SELECT 对其进行调用，其使用的语法格式是： SELECT sp_name([func_parameter[，…]])

6.3 删除存储函数 
DROP FUNCTION[IF EXISTS] sp_name
其中，语法项“sp_name”指定要删除的存储函数的名称。

6.4 DEMO:
DELIMITER $$
CREATE FUNCTION fun_add(t1 int,t2 int)
RETURNS INT
BEGIN
   DECLARE inta int DEFAULT 0;
   DECLARE intb int DEFAULT 0;     
   SET inta=t1;SET intb=t2;
     RETURN t1+t2;
END $$

SELECT fun_add(5,30);

7 数据库完整性
数据库完整性是指数据库中数据的正确性和相容性。数据完整性约束是为了防止数据库中存在不符合语义的数据，
为了维护数据的完整性，DBMS 必须提供一种机制来检查数据库中的数据，以判断其是否满足语义规定的条件。
这些加在数据库之上的语义约束条件就是数据完整性约束，而 DBMS 检查数据是否满足完整性约束条件的机制就称为完整性检查。

7.1 完整性约束条件的作用对象 
完整性检查是围绕完整性约束条件进行的，因而完整性约束条件是完整性控制机制的核心。完整性约束条件的作用对象可以是列、元组和表。
1、 列级约束主要指对列的类型、取值范围、精度等的约束，具体包括如下内容:
    （1） 对数据类型的约束，其包括数据类型、长度、精度等。
    （2） 对数据格式的约束。
    （3） 对取值范围或取值集合的约束。
    （4） 对空值的约束。
2、 元组约束指元组中各个字段之间的相互约束，例如某个活动的开始日期小于结束日期。
3、 表级约束指若干元组之间、关系之间的联系的约束。

7.2 定义与实现完整性约束
关系模型的完整性规则是对关系的某种约束条件，关系模型中可以有三类完整性约束，分别是实体完整性、参照完整性和用户定义的完整性。
1、实体完整性
（1）主键约束
主键可以是表中的某一列，也可以是表中多个列所构成的一个组合。其中，由多个列组合而成的主键也称为复合主键。在 MySQL 中，主键列必须遵守如下一些规则。
        i 每一个表只能定义一个主键。
        ii 主键的值，也称为键值，必须能够唯一标志表中的每一行记录，且不能为NULL。
        iii 复合主键不能包含不必要的多余列。
        iv 一个列名在复合主键的列表中只能出现一次。
主键约束可以在 CREATE TABLE 或 ALTER TABLE 语句中使用关键字“PRIMARY KEY”来实现，其方式有两种。
        i 一种是作为列的完整性约束，此时只需在表中某个列的属性定义后加上关键字“PRIMARY KEY”即可。
        ii 一种是作为表的完整性约束，需要在表中所有列的属性定义后添加一条 PRIMARY KEY（index_col_name，…）格式的句子。
        
（2）候选键约束
 候选键可以是表中的某一列，也可以是表中多个列所构成的一个组合。任何时候，候选键的值必须是唯一的，且不能为 NULL。
 候选键可以在 CREATE TABLE 或 ALTER TABLE 语句中使用关键字“UNIQUE”来定义，其实现方法与主键约束相似，同样可作为列或表（关系）的完整性约束两种方式。
    MySQL 中候选键与主键之间存在以下几点区别。
    i 一个表中只能创建一个主键，但可以定义若干个候选键。
    ii 定义主键约束时，系统会自动产生 PRIMARY KEY 索引，而定义候选键约束时，系统自动产生 UNIQUE 索引。

2、参照完整性
 在MySQL中，参照完整性是通过在创建表（CREATE TABLE）或更新表（ALTER TABLE）的同时定义一个外键声明来实现的。其中，外键声明有下列两种方式。
（1）在表中某个列的属性定义后直接加上“reference_definition”语法项。
（2）在表中所有列的属性定义后添加“FOREIGN KEY（index_col_name，…）
     reference_definition”子句的语法项。
        “reference_definition”语法项的定义是：
        REFERENCES tbl_name（index_col_name，…）
        [ON DELETE reference_option]
        [ON UPDATE reference_option]
        
        reference_option 的语法格式是：
        RESTRICT|CASCADE|SET NULL|NO ACTION
        
        相关语法说明如下：
      a. 语法项“tbl_name”指定外键所参照的表名。这个表称为被参照表（或父表），而外键所在的表称作参照表（或子表）。
        b. 语法项“col_name”指定被参照表的列名。
        c. 关键字“ON DELETE”或“ON UPDATE”指定参照动作相关的 SQL 语句。
        d. 语法项“reference_option”指定参照完整性约束的实现策略。
        
        在指定外键时，需要遵守下列规则。
        a. 被参照表必须已经用一条 CREATE TABLE 语句创建了，或者必须是当前正在创建的表。如若是后一种情形，则被参照表与参照表是同一个表，这样的表称为          自参照表，这种结构称为自参照完整性。
        b. 必须为被参照表定义主键。
        c. 必须在被参照表的表名后面指定列名或列名的组合。这个列或列组合必须是这个被参照表的主键或候选键。
        d. 尽管主键是不能够包含空值的，但允许在外键出现一个空值。这意味着，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
        e. 外键中的列的数目必须和被参照表的主键中的列的数目相同。
        f. 外键中的列的数据类型必须和被参照表的主键中的对应列的数据类型相同。

3、用户定义的完整性
 MySQL支持几种用户自定义完整性约束，分别是非空约束、CHECK 约束和触发器。
 （1）非空约束
  在 MySQL 中，非空约束可以通过在 CRETE TABLE 或 ALTER TABLE 语句中的某个列定义后面，加上关键字“NOT NULL”作为限定词，来约束该列的取值不能为空。
 （2）CHECK 约束
  与非空约束一样，CHECK约束也是在创建表或更新表的同时，根据用户的实际完整性要求来定义的。语法格式是：CHECK（expr）。其中，语法项“expr”是一个SQL表达式，用于指定需要检查的限定条件。
  (3) 触发器见第8部分

7.3 命名完整性约束
命名完整性约束使用的语法格式是：
CONSTRAINT[symbol]
其中，语法项“symbol”是指定的约束名字，这个名字是在完整性约束前面被定义，其在数据库里必须是唯一的。倘若没有明确给出约束的名字，则 MySQL自动创建一个约束名字。

7.4 更新完整性约束
当对各种约束进行命名后，就可以使用ALTER TABLE语句来更新与列或表有关的各种约束。需要注意以下两点：
1、完整性约束不能直接被修改。若要修改某个约束，实际上是用 ALTER TABLE 语句先删除该约束，然后再增加一个与该约束同名的新约束。
2、使用 ALTER TABLE 语句，可以独立地删除完整性约束，而不会删除表本身。若使用 DROP TABLE 语句删除一个表，则表中所有的完整性约束都会自动被删除。

8 触发器
触发器是用户定义在关系表上的一类由事件驱动的数据库对象，也是一种保证数据完整性的方法。触发器一旦定义，无须用户调用，任何对表的修改操作均为数据库服务器自动激活相应的触发器。触发器与表的关系十分密切，其主要作用是实现主键和外键不能保证的复杂的参照完整性和数据的一致性，从而有效地保护表中的数据。

8.1 创建触发器
  常用的语法格式是：
    CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body
    （1）语法项“trigger_name”用于指定触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。
    （2）语法项“trigger_time”用于指定触发器被触发的时刻，它有两个选项，即关键字“BEFORE”和关键字“AFTER”，用于表示触发器是在激活它的语句之前或之后触发。
                 如果希望验证新数据是否满足使用的限制，则使用BEFORE选项；
                 如果希望在激活触发器的语句执行之后完成几个或更多的改变，通常使用AFTER选项。
    （3）语法项“trigger_event”用于指定触发事件，即指定激活触发器的语句的种类，其可以是下述值之一：
                 关键字“INSERT”，表示将新的数据行插入到表时激活触发器；
                 关键字“UPDATE”，表示更改表中某一行数据时激活触发器；
                 关键字“DELETE”，表示从表中删除某一行数据时激活触发器。
    （4）语法项“tbl_name”用于指定与触发器相关联的表名，必须引用永久性表，不能将触发器与临时表或视图关联起来，且同一个表不能拥有两个具有相同触发时刻和事件的触发器。
    （5） 关键字“FOR EACH ROW”用于指定对于受触发事件影响的每一行都要激活触发器的动作。
    （6） 语法项“trigger_body”用于指定触发器动作主体，即包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用BEGIN…END复合语句结构。

8.2 删除触发器
  使用的语法格式是：DROP TRIGGER[IF EXISTS][schema_name.]trigger_name
        关键字“IF EXISTS”用于避免在没有触发器的情况下删除触发器；
        语法项“schema_name”用于指定触发器所在的数据库的名称，若没有指定，则为当前默认数据库；
        语法项“trigger_name”指定要删除的触发器名称。

8.3 使用触发器
    1、INSERT触发器可在INSERT语句执行之前或之后执行。使用该触发器时，需要注意以下几点：
    （1）在INSERT触发器代码内，可引用一个名为NEW（不区分大小写）的虚拟表，来访问被插入的行。
    （2）在BEFORE INSERT触发器中，NEW中的值也可以被更新，即允许更改被插入的值。
    （3）对于AUTO_INCREMENT列，NEW在INSERT执行之前包含的是0值，在INSERT执行之后将包含新的自动生成值。
    
  2、DELETE触发器可在 DELETE 语句执行之前或之后执行。使用该触发器时，需要注意以下几点：
    （1）在DELETE触发器代码内，可以引用一个名为OLD（不区分大小写）的虚拟表，来访问被删除的行。
    （2）OLD中的值全部都是只读的，不能被更新。
    
    3、UPDATE触发器可在 UPDATE 语句执行之前或之后执行。使用该触发器时，需要注意以下几点：
    （1）在UPDATE触发器代码内，可以引用一个名为OLD（不区分大小写）的虚拟表访问以前（UPDATE 语句执行前）的值，也可以引用一个名为NEW（不区分大小写）的虚拟表访问新更新的值。
    （2）在BEFORE UPDATE触发器中，NEW中的值可能也被更新，即允许更改将要用于UPDATE语句中的值（只要具有对应的操作权限）。
    （3）OLD 中的值全部是只读的，不能被更新。
    （4）当触发器涉及对触发表自身的更新操作时，只能使用BEFORE UPDATE触发器，而AFTER UPDATE触发器将不被允许。

四、安全性与访问控制 
1. 用户账号管理
     MySQL 的用户账号及相关信息都存储在一个名为 mysql 的 MySQL 数据库中，这个数据库里有一个名为 user 的数据表，包含了所有用户账号，并且它用一个名为 user 的列存储用户的登录名。可以使用下面的 SQL 语句查看 MySQL 数据库的使用者账号。
     
1.1 创建用户账号
常用的语法格式是：CREATE USER user[IDENTIFIED BY [PASSWORD]‘password’]

语法格式介绍如下：
（1） 语法项“user”指定创建用户账号，其格式为‘user_name‘@’host name’。其中，user_name 表示用户名，host_name 表示主机名，即用户连接 MySQL时所在主机的名字。如果在创建的过程中，只给出了账户中的用户名，而没指定主机名，则主机名会默认为是“%”，其表示一组主机。
（2） 语法项“IDENTIFIED BY子句”是可选项，用于指定用户账号对应的口令，若该用户账号无口令，则可省略此子句。
（3） 关键字“PASSWORD”是可选项，用于指定散列口令，即若使用明文设置口令时，而忽略PASSWORD关键字；如果不想以明文设置口令，且知道PASSWORD()函数返回给密码的散列值，则可以在此口令设置语句中指定此散列值，但需要加上关键字PASSWORD。
（4） 语法项“password”指定用户账号的口令，其在IDENTIFIED BY关键字或PASSWORD关键字之后。设定的口令值可以是只有字母和数字组成的明文，也可以是通过PASSWORD()
函数得到散列值。

在 CREATE USER语句的使用中，需要注意以下几点。
（1）要使用CREATE USER语句，必须拥有MySQL中mysql数据库的INSERT权限或全局CREATE USER权限。
（2）使用CREATE USER语句创建一个用户账户后，会在系统自身的mysql数据库的user表中添加一条新记录。如果创建的账户已经存在，则语句执行会出现错误。
（3）如果两个用户具有相同的用户和不同的主机名，MySQL会将他们视为不同的用户，并允许为这两个用户分配不同的权限集合。
（4）如果在CREATE USER语句的使用中，没有为用户指定口令，那么 MySQL 允许该用户可以不使用口令登录系统，然而从安全的角度而言，不推荐这种做法。
（5）新创建的用户拥有的权限很少。他们可以登录到 MySQL，只允许进行不需要权限的操作，比如使用SHOW语句查询所有存储引擎和字符集的列表等，不能使用 USE 语句来让其他用户已经创建了的任何数据库成为当前数据库，因而无法访问那些数据库的表。

1.2 删除用户
为了删除一个或多个用户账号以及相关的权限，可以使用 DROP USER 语句，其使用的语法格式是：DORP USER user[，user]…
在 DROP USER 语句的使用中，需要注意以下几点：
（1）DROP USER语句可用于删除一个或多个MySQL账户，并消除其权限。
（2）要使用DROP USER语句，必须拥有 MySQL中mysql数据库的DELETE权限或全局CREATE USER权限。
（3）在DROP USER语句的使用中，如果没有明确地给出账户的主机名，则该主机名会默认为是%。
（4）用户的删除不会影响到他们之前所创建的表、索引或其他数据库对象，这是因为 MySQL 并没有记录是谁创建了这些对象。

1.3 修改用户账号
可以使用 RENAME USER 语句修改一个或多个已经存在的 MySQL 用户账号，其使用的语法格式是：RENAME USER old_user TO new_user[,old_user TO new_user]…
在RENMAE USER语句的使用中，需要注意以下几点。
（1）RENAME USER语句对于原有MySQL账户进行重命名。
（2）要使用RENAME USER语句，必须拥有 MySQL 中的 mysql 数据库的 UPDATE权限或全局 CREATE USER 权限。
（3）倘若系统中旧账户不存在或者新账户已存在，则语句执行会出现错误。

1.4 修改用户口令
可以使用 SET PASSWORD 语句修改一个用户的登录口令，其使用的语法格式是：
        SET PASSWORD[FOR user]=
        {
        PASSWORD(‘new_password’)
        |’encrypted password’
        }
 在 SET PASSWORD 语句的使用中，需要注意以下几点。
（1）若不加上FOR子句，表示修改当前用户的口令；若加上FOR子句，表示修改账户为user的用户口令其中user的格式必须以’user_name’@’host_name’的格式给定，user_name 为账户的用户名，host_name 为账户所在的主机名。该账户必须在系统中存在，否则语句执行会出现错误。
（2）只能使用选项 PASSWORD(‘new_password’)和’encrypted password’中的一项，且必须使用其中的某一项。

2、 账户权限管理
2.1 权限的授予
    新建的 MySQL 用于必须被授权，可以使用 GRANT 语句来实现，常用的语法格式是：
            GRANT
            Pri_type[(column_list)]
            [,pri_type[(column_list)]]…
            ON[object_type]priv_level
            TO user_specification[,user_specification]…
            [WITH GRANT OPTION]
    在此语法中：
    （1）语法项“pri_type”用于指定权限的名称。
    （2）可选语法“column_list”用于指定权限要授予给表中哪些具体的列。
    （3）语法项“ON 子句”用于指定权限授予的对象和级别。
    （4）可选项“object_type”用于指定权限授予的对象类型，包括表、函数和存储过程，分别用关键字“TABLE”“FUNCTION”和“PROCEDURE”标识。
    （5）语法项“priv_level”:用于指定权限的级别，其可以授予的权限有这样几个：列权限、表权限、数据库权限和用户权限。相应地，
                在 GRANT 语句中可用于指定权限级别的值有这样几类格式：
                        “*”表示当前数据库中的所有表；
                        “*.*”表示所有数据库中的所有表；
                        “db_name*”表示某个数据库中的所有表，db_name 指定数据库名；
                        “db_name.tbl_name”表示某个数据库中的某个表或视图，db_name 指定数据库名， tbl_name 指定表名或视图名；
                        “tbl_name” 表示某个表或视图，tbl_name指定表名或视图名；
                        “db_name.routine_name”表示某个数据库中的某个存储过程或函数，routine_name 指定存储过程名或函数名。
            
    （6）语法项“TO子句”用来设定用户的口令，以及指定被授予权限的用户 user。
                若在TO子句中给系统中存在的用户指定口令，则新密码会将原密码覆盖；
                如果权限被授予给一个不存在的用户，MySQL会自行执行一条CREATE USER语句来创建这个用户，但同时必须为该用户指定口令。
                由此可见，GRANT语句亦可以用于创建用户账号。
    （7）语法项“user_specification”是 TO 子句中的具体描述部分，其常用的语法格式是：user[IDENTIFIED BY[PASSWORD]’password’]
    （8）语法项“WITH 子句”为可选项，用于实现权限的转移或限制。
    
 关于 GRANT 语句中语法项“priv_type”的使用，需要注意以下几点:
    （1）授予表权限时，语法项“priv_type”可以指定为以下值。
             SELECT：表示授予用户可以使用 SELECT 语句访问特定表的权限。
             INSERT：表示授予用户使用 INSERT 语句向一个特定表中添加数据行的权限。
             DELETE: 表示授予用户可以使用 DELETE 语句向一个特定表中删除数据行的权限。
             UPDATE: 表示授予用户可以使用 UPDATE 语句修改特定数据表中值的权限。
             REFERENCES: 表示授予用户可以创建一个外键来参照特定数据表的权限。
             CREATE：表示授予用户可以使用特定的名字创建一个数据表的权限。
                 ALTER：表示授予用户可以使用 ALTER TABLE 语句修改数据表的权限。
             INDEX:表示授予用户可以在表上定义索引的权限。
             DROP：表示授予用户可以删除数据表的权限。
             ALL 或 ALL PRIVILEGES：表示所有的权限名。
  （2）授予权限时，语法项“priv_type”的值只能指定为SELECT、INSERT和UPDATE，同时权限的后面需要加上列名列表 column_list。
  （3）授予数据库权限时，语法项“priv_type”可以指定为以下值。
             SELECT：表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
             INSERT：表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
             DELETE: 表示授予用户可以使用 DELETE 语句向删除特定数据库中所有表的数据行的权限。
             UPDATE: 表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
             REFERENCES: 表示授予用户可以创建指向特定的数据库中的表外键的权限。
             CREATE：表示授予用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
             ALTER：表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
             INDEX:表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
             DROP：表示授予用户可以删除特定数据库中所有表和视图的权限。
             CREATE TEMPORARY TABLES:表示授予用户可以在特定数据库中创建临时表的权限。
             CREATE VIEW：表示授予用户可以在特定数据库中创建新的视图的权限。
             SHOW VIEW：表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
             CREATE ROUTINE：表示授予用户可以为特定的数据库创建存储过程和存储函数等权限。
             ALTER ROUTINE:表示授予用户可以更新和删除数据库中已有的存储过程和存储函数等权限。
             EXECUTE ROUTINE:表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
             LOOK TABLES:表示授予用户可以锁定特定数据库的已有数据表的权限。
             ALL 或 ALL PRIVILEGES：表示所有的权限名。
    （4）最有效率的权限是用户权限。授予用户权限时，语法项“priv_type”除了可以指定为授予数据库权限时的所有值之外，还可以是下面这些值。
             CREATE USER：表示授予用户可以创建和删除新用户的权限。
             SHOW DATABASES：表示授予用户可以使用 SHOW DATABASES 语句查看所有已有的数据库的定义的权限。

2.2 权限的转移可以通过 GRANT 语句中使用 WITH 子句来实现。
        如果将 WITH 子句指定为关键字“WITH GRANT OPTION”，则表示TO子句中所指定的所有用户都具有把自己所拥有的权限授予给其他用户的权利，而无论那些其他用户是否拥有该权限。

2.3 用户的撤销
    当需要回收某些特定的权限时，可使用的语法格式是：
        REVOKE
        priv_type[(column_list)]
        [,priv_type[(column_list)]]…
        ON[object_type]priv_level
        FROM user[,user]…
  当需要收回特定用户的所有权限时，可使用的语法格式是：REVOKE ALL PRIVILEGES,GRANT OPTION FROM user[,user]…

五、事务与并发控制 
模块一 事务的概念
1、所谓事务是用户定义的一个数据操作序列，这些操作可作为一个完整地工作单元，要么全部执行，要么全部不执行，是一个不可分割的工作单位。
2、事务与程序的不同：程序是静止的，事务是动态的，是程序的执行而不是程序本身；同一程序的多个独立执行可以同时进行，每一步执行则是一个不同的事务。
3、在 SQL 中，用户显示定义事务的语句一般有三条：
    BEGIN TRANSACTION、COMMIT 和 ROLLBACK，且事务通常是以BEGIN TRANSACTION语句开始，以COMMIT语句或ROLLBACK语句结束。
4、 事务中的操作一般是对数据的更新操作，包括增、删、改。

模块二 事务的特征
1、 原子性：事务的原子性保证事务包含的一组更新操作是原子不可分的，即事务是不可分割的最小工作单位，所包含的这些操作是一个整体。
2、 一致性：一致性要求事务必须满足数据库的完整性约束，且事务执行完毕后将数据库由一个一致性状态转变到另一个一致性状态。其中，数据库的一致性状态是一种以一致性规则为基础的逻辑属性。
3、 隔离性：隔离性要求事务是彼此独立的、隔离的，即一个事务的执行不能被其他事务所干扰，一个事务对数据库变更的结果必须在它 COMMIT 后，另一个事务才能存取。
4、 持续性：持续性也称为永久性，是指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，且接下来的其他操作或故障不应该对其执行结果又任何影响。

模块三 并发操作问题
事务是并发控制的基础单位，保证事务的 ACID 特征是事务处理的重要任务，而事务的 ACID 特征可能遭到破坏的原因之一是多个事务对数据库的并发操作造成的。
为了保证事务的隔离性和一致性，DBMS 需要对并发操作进行正确调度。
其中，完整性检验可以保证一个事务单独执行时，若输入的数据库状态是正确的，则其输出的数据库状态也是正确的。
但当多个事务交错执行时，可能出现不一致问题，这也称为并发操作问题，典型的有如下三种：丢失更新、不可重复读和读“脏”数据。
1、丢失更新
        设有两个事务T1和T2,当它们同时读入同一数据并加以修改时，事务T2的提交结果会破坏事务T1提交的结果，由此导致事务T1的修改被丢失。这就是一种由于对数据的并发操作而带来的数据不一致。
2、不可重复读包括三种情况：
（1）事务T1读取某一数据后，事务 T2 对其做了修改，当事务T1再次读该数据时，得到与前一次不同的值。
（2）事务T1按一定条件从数据库中读取了某些数据记录后，事务T2删除了其中部分记录，当事务 T1 再次按相同条件读取数据时，发现某些记录神秘地消失了。
（3）事务T1按一定条件从数据库中读取某些数据记录后，事务T2插入了一些记录，当事务 T1 再次按相同条件读取数据时，发现多了一些记录。
3、读“脏”数据
设有两个事务T1和T2，读“脏”数据是指:事务T1修改某一数据，并将其写回磁盘，事务T2读取同一数据后，事务T1由于某种原因被撤销，这时事务T1已修改过的数据恢复原值，事务T2读到的数据就与数据库中的数据不一致，则事务T2读到的数据就为“脏”数据，即不正确的数据。
解决并发操作所带来的数据不一致性问题的方法有封锁、时间戳、乐观控制法和多版本并发控制等。

模块四 封锁
封锁是最常用的并发控制技术，它的基本思想是：需要时，事务通过向系统请求对它所希望的数据对象加锁，以确保它不被非预期改变。
1、锁
    一个锁实际上就是允许或组织一个事务对一个数据对象的存取特权。基本的封锁类型有两种：排他锁和共享锁。
2、用封锁进行并发控制工作原理是：
（1）若事务T对数据D加了X锁，则所有别的事务对数据D的锁请求都必须等待直到事务T释放锁。
（2）若事务T对数据D加了S锁，则别的事务还可对数据D请求S锁，而对数据D的X锁请求必须等待直到事务T释放锁。
（3）事务执行数据库操作时都要先请求相应的锁，即对读请求S锁，对更新（插入、删除、修改）请求X锁。这个过程一般是由DBMS在执行操作时自动隐含地进行。
（4）事务一直占有获得的锁直到结束（COMMIT 或 ROLLBACK）时释放。因此，利用封锁机制可以解决并发操作所带来的三个不一致问题。
3、封锁的粒度
    以粒度来描述封锁的数据单元的大小。DBMS 可以决定不同粒度的锁。
    由最底层的数据元素到最高层的整个数据库，粒度越细，并发性就越大，但软件复杂性和系统开销也就越大。
4、 封锁的级别
    封锁的级别又称为一致性级别或隔离度。由各种锁的类型与其封锁期限组合可形成不同的封锁级别：
    （1）0 级封锁
    （2）1 级封锁
    （3）2 级封锁
    （4）3 级封锁
5、活锁与死锁
    在并发事务处理过程中，由于锁会使一事务处于等待状态而调度其他事务处理，因而该事务可能会因优先级低而永远等待下去，这种现象称为“活锁”。
    活锁问题的解决与调度算法有关，一种最简单的办法是“先来先服务”。
    两个以上事务循环等待被同组中另一事务锁住的数据单元的情形，称为“死锁”。
    DBMS 需要提供死锁预防、死锁检测和死锁发生后的处理技术与方法。预防死锁的办法在操作系统中已普遍讨论，其中主要有如下几种：
    （1）一次性锁请求
    （2）锁请求排序
    （3）序列化处理
    （4）资源剥夺
6 可串行性
一组事务的一个调度就是它们的基本操作的一种排序。若在一个调度中，对于任意两个事务T1和T2，要么T1的所有操作都在T2所有操作之前，
要么反之，则该调度室串行的，因而是正确的。
通常，在数据库系统中，可串行性就是并发执行的正确性准则，即当且仅当一组事务的并发执行调度是可串行化，才认为它们是正确的。

7、两段封锁法
采用两段封锁法是一种最简单而有效的保障封锁其调度是可串行性的方法。两段锁协议规定在任何一个事务中，所有加锁操作都必须在所有释放锁操作之前。其中，事务划分成如下两个阶段。
（1）发展（Growing）或加锁阶段
（2）收缩（Shrinking）或释放锁阶段
定理：遵循两段锁协议的事务的任何并发调度都是可串行化的。