四、关系数据库标准语言SQL_2

四、关系数据库标准语言SQL_2

主要内容

• 4.1 SQL概述
  • SQL简介
• 4.2 SOL的系统结构
• 4.3 SQL数据定义
  • SQL的数据定义
• 4.5 SQL数据查询
• 4.6 SQL数据更新
• 4.7 SQL中的视图
• 4.8 SQL的数据控制
• 4.9 嵌入式SQL
• 小结

4.6 数据更新

主要内容

• 数据更新
  • 插入
  • 修改
  • 删除

1.插入数据

• 两种插入数据方式

  • 插入单个元组
  • 插入子查询结果

• 插入单个元组

  • 语句格式

    INSERT INTO <表名>[(<属性列1>[,<属性列2>]……)]
    	VALUES(<常量1>[,<常量2>]……);
    

  • 功能：将新元组插入指定表中。

  • INTO子句

    • 指定要插入数据的表名及属性列
    • 属性列的顺序可以与表定义中的顺序不一致
    • 没有指定属性列：表示要插入的是一条完整的元组，且属性列的属性值与表定义中的顺序一致
    • 指定部分属性列：插入的元组在其余属性列上取空值或者是默认值

  • VALUES子句

    • 提供的值必须与INTO子句匹配：个数、顺序和值的类型

  【例】在学生表中插入一个学生元组，其学号为101215，姓名为李斌，男，19岁，是计算机系的学生。

  INSERT INTO Student
      VALUES ('101215','李斌',19,'男','计算机系');
  

  等价于

  INSERT INTO Student(Sno,Sname,Sdept,Sage,Ssex)
  	VALUES('101215','李斌','计算机系',19,'男');
  

  【例】学号为101253的学生选修了C2号课，将其插入选课表中。

  INSERT INTO SC(Sno,Cno)
  	VALUES('101253','C2');
  

  等价于

  INSERT INTO SC
  	VALUES('101253','C2',NULL);
  

  注意：如果表的定义里面说明某列的约束为NOT NULL，则插入时不能取空值，同时保证101253 和 C2是Student,Course里面有的值，因为SC里面的这两个值是外键，依赖于Student,Course的主键。

• 将子查询结果插入指定表中

  INSERT INTO <表名>[(<列名1>[,<列名2>,……])]
  	<SELECT语句>;-- 子查询
  

  • INTO子句
    • 指定要插入数据的表名及属性列
    • 属性列的顺序可于表定义中的顺序不一致
      • 没有指定属性列，则表示插入一条完整的元组
      • 指定部分属性列，则在其余属性列上取空值
    • 子查询
      • SELECT子句目标列必须与INTO子句匹配：个数、顺序和值的类型

  【例】计算计算机系每个学生的平均成绩，并保存在CS-AVG表中

  1. 生成学生的平均成绩表CS-AVG
  2. 在CS-AVG中插入计算机系学生的平均成绩

  CREATE TABLE CS_AVG
  	(Sno CHAR(6) NOT NULL,
      Grade DECIMAL(4,1),
      CONSTRAINT CS_AVG_FK FOREIGN KEY (Sno) REFERENCES Student(Sno));
      
  INSERT INTO CS_AVG (Sno,Grade)
  	(SELECT Sno,AVG(Grade)
       FROM SC
       WHERE Sno IN
       (SELECT Sno FROM Student
        WHERE Sdept = '计算机系')
      GROUP BY Sno);
  

• DBMS在执行插入语句时会检查所有插入元组是否破坏表上已定义的完整性规则

  • 实体完整性
  • 参照完整性
  • 用户定义的完整性
    • 对于有NOT NULL约束的属性列是否提供了非空值
    • 对于有UNIQUE约束的属性列是否提供了重复值
    • 对于有值域约束的属性列所提供的属性值是否在值域范围内

2.修改数据

• 语句格式

  UPDATE <表名>
  	SET 列名1 = <表达式1>[,<表达式2>]……
  	[WHERE<条件表达式>]
  

• 功能：修改指定表中满足WHERE子句条件的元组的指定列的内容

• SET子句，指定修改方式

  • 要修改的列
  • 修改后的值

• WHERE子句，指定要修改的元组

  • 缺省（默认）表示要修改表中的所有元组

• DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则

  • 实体完整性
  • 主码（主键）不允许修改
  • 用户定义的完整性约束
    • NOT NULL约束
    • UNIQUE约束
    • 值域约束

• 修改一个或多个元组的值

  【例】将数据库课的学分修改为4。

  UPDATE Course SET Ccredit = 4 WHERE Cname = '数据库';
  

  【例】将所有学生的年龄增加1岁。

  UPDATE Student SET Sage = Sage+1;
  

  【例】将所有选修了数据库课的学生的成绩清空。

  UPDATE SC SET Grade = NULL
  	WHERE Cno IN 
  	(SELECT Cno 
      FROM Course
      WHERE Cname = '数据库');
  

  【例】将计算机系全体学生的成绩置零。

  UPDATE SC SET Grade = 0
  	WHERE Sno IN
  	(SELECT Sno 
       FROM Student
      WHERE Sdept = '计算机系');
  

  等价于

  UPDATE SC 
  	SET Grade = 0
  		WHERE '计算机系' = 
  		(SELECT Sdept 
          	FROM Student
          		WHERE Student.Sno = SC.Sno);
  

  下面适用于SQL Server，不适用于MySQL

  UPDATE SC SET Grade = 0
  	FROM SC,Student
  	WHERE SC.Sno = Student.Sno AND Sdept = '计算机系';
  

  • 正确的MySQL写法

    UPDATE SC
    	JOIN Student ON SC.Sno = Student.Sno
    	SET SC.Grade = 0
    	WHERE Student.Sdept = '计算机系';
    

    说明：

    • UPDATE……JOIN……ON……是MySQL支持的更新多表的语法。
    • 我们可以像使用SELECT一样把两个表连接起来，再更新目标表的字段。

3.删除数据

这个与ALTER …… DROP……相比，它更侧重于行

• 语句格式

  DELETE FROM <表名>
  	[WHERE <条件>];
  

• 功能

  • 删除指定表中满足WHERE子句条件的元组

• WHERE子句

  • 指定要删除的元组
  • 缺省（默认）表示要修改表中的所有元组

• DBMS在执行删除语句时

  • 检查所删元组是否破坏表上已定义的完整性规则（参照完整性）；如果破坏，则不允许删除，除非级联删除。

• 删除一个或多个元组

  【例】删除学号为201225的学生记录。

  DELETE FROM Student WHERE Sno = '201225';
  

  【例】删除学号为200705的学生揭记录（该学生在SC 、CS_AVG也出现，因此需要级联删除）

  SHOW CREATE TABLE SC;-- 查看SC的创建SQL语句
  
  ALTER TABLE SC DROP FOREIGN KEY SC_FK1;-- 找到约束名字，然后删掉没有级联删除的外键约束
  -- 添加有级联约束的外键约束
  ALTER TABLE SC
      ADD CONSTRAINT SC_FK1
      FOREIGN KEY (Sno) REFERENCES Student(Sno)
      ON DELETE CASCADE;-- CASCADE是串联的意思
      
  ALTER TABLE CS_AVG DROP FOREIGN KEY CS_AVG_FK;
  ALTER TABLE CS_AVG
      ADD CONSTRAINT CS_AVG_FK
      FOREIGN KEY (Sno) REFERENCES Student(Sno)
      ON DELETE CASCADE;
      
  DELETE FROM Student WHERE Sno = '200705';
  

  【例】删除所有的学生选课记录

  DELETE FROM SC;
  

  • 带子查询的删除

    【例】删除所有选修数据库课学生的选课信息

    DELETE FROM SC
    	WHERE Cno IN
    	(SELECT Cno FROM Course
        	WHERE Cname = '数据库');
    

截断表TRUNCATE TABLE

• 语句格式

  TRUNCATE TABLE <表名>
  

• 功能

  • 删除表中所有行（与不带WHERE子句的DELETE语句相同），但不记录单个行删除操作
  • 比 DELETE速度快，使用的系统和事物日志资源少
    • DELETE语句每次删除一行，就在事务日志中为所删除的每行记录一项。
    • TRUNCATE TABL通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
  • 操作不能回滚（后续在事务这一章节会提到），但DELETE可以回滚

更新数据与数据一致性

• DBMS在执行插入、删除、更新语句时必须保证数据库的一致性
  • 数据库不一致性的形成原因可能如下
    • 当某同学退学时，首先删除成绩表（子表）中数据，然后删除学籍表（主表）中的数据，但如果执行完第一步后，计算机发生故障，则第二步永远不会执行，就会形成数据库的不一致性。
    • 问题的解决：必须有事务的概念（后面在事务章节会提到）和原子性保障
  • 完整性检查和保证
    • 系统自动在删除主表元组时删除子表对应的元组
    • 系统检查子表中是否有相应的元组，如果存在，禁止删除动作（如果没有开启级联操作——ON DELETE CASCADE）

4.7 SQL中的视图

主要内容

• 视图的定义
• 创建视图
• 删除视图
• 视图查询
• 视图更新
• 视图更新的限制
• 视图的优点

视图（View）

• 视图
  • 相对于基本表而言；对应外模式
  • 是从一个或几个基本表（或视图）导出的虚表
  • 视图的定义是递归的，可以定义基于该视图的新视图
  • DBMS只存放视图的定义，不存放视图的数据，不会出现数据冗余
  • 基表的数据发生变化，从视图中查询出的数据也改变
  • 视图实际上提供了一种观察数据的逻辑窗口，用户可以不同的角度观察数据库
  • 对视图的操作意味着对基本表进行相对应的操作；但对视图的更新（插入数据、删除、修改）有一些限制

创建视图

• 语句格式

  CREATE VIEW <视图名> [(<列名1>[,<列名2>]……)]
  	AS <SELECT 语句>
  	[WITH CHECK OPTION];
  

• DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图进行操作时才按照视图定义生成数据，供用户使用。

  数据字典是数据库系统用来存储数据库本身结构信息的一套系统表或元数据。简单来说，它是**”数据库的数据库“**

  说明：

  假设运行下面这个执行语句

  CREATE VIEW my_view AS
  SELECT * FROM Student WHERE Sdept = '计算机系';
  

  1. 创建视图不会立刻执行 SELECT 查询。当我们执行CREATE VIEW语句时，数据库并不会马上去查出“计算机系的学生”并存起来

  2. 数据库只会把这个定义保存下来。视图的定义（比如上面这条SELECT语句）会被存进数据字典，就像是一张“SQL配方”

  3. 只有在我们使用视图时，才真正去执行SELECT查询。

     比如我们后面执行：

     SELECT * FROM my_view;
     

     这时数据库才会真正执行视图中的SELECT子句，去实时查出“计算机系的学生”，再返回结果给你。

  4. 举个类比：

     • 视图就像是菜单上的一道“定制菜”——只是配方
     • 我们点菜（访问视图）时，厨房（数据库）才根据这个配方去做菜（执行SQL查询）。

• SELECT语句表示子查询，视图的属性列和数据都是由该子查询决定的。

• 选项[(<>[,<>])……]用来定义视图的列名。

• 组成视图的属性列名可以全部省略或全部指定（就是说，**创建视图时，可以选择是否为视图的每一列手动命名。**有时候可以省略，有时候必须指定。）

  • 该情况可以省略：组成视图的属性列名由SELECT查询结果的目标列名组成。就是说，当你写视图时，只要 SELECT 子句本身的结果列有名字，系统就可以自动用这些列名

  • 以下情况必须明确指定视图的所有列名：

    1. 目标列中包含聚集函数或表达式

    2. 视图中包含出现在多个表中的相同列名（比如我们连接两个表，它们可能都有id字段，系统会不知道哪个id是哪个，必须我们来指定清楚视图中每列的名字）

       CREATE VIEW v3 (aid, bid) AS
       SELECT A.id, B.id FROM A JOIN B;
       

    3. 需要在视图中为某个列启用新的更合适的名字

• WITH CHECK OPTION选项的作用

  • 通过视图插入、删除或修改元组时，检查元组是否满足视图定义中的条件（即子查询中的条件表达式），如果不满足将拒绝执行这些操作。
  • 如果视图定义中含有约束条件，建议选择WITH CHECK OPTION选项，以约束更新的数据。

• 建立与基本表结构相同的视图

  【例】建立年龄小于23岁的学生视图，并要求数据更新时进行检查。

  CREATE VIEW Sage_23
  	AS SELECT * FROM Student
  	WHERE Sage < 23
  	WITH CHECK OPTION;
  

  • 当通过视图更新学生时，系统将检查所更新的学生年龄是否小于23岁，不满足条件时系统将拒绝执行更新操作

• 建立带表达式的视图

  【例】按系建立学生平均年龄的视图。

  CREATE VIEW D_Sage(Sdept,Average)
  	AS SELECT Sdept,AVG(Sage)
  		FROM Student
  		GROUP BY Sdept;
  

  • 因在SELECT目标表中有聚集函数AVG，视图定义中必须含有列名选项。
  • 视图的列名与SELECT后的列名相对应，即使有与基本表相同的列名也不能省略。

• 建立基于多个表的视图

  【例】建立计算机系选修了2号课的学生姓名和成绩的视图。

  CREATE VIEW CS_SC(Sno,Sname,Grade)
  	AS SELECT Student.Sno,Student.Sname,Grade
  		FROM Student INNER JOIN SC 
  		ON Student.Sno = SC.Sno
  		WHERE Sdept = '计算机系' AND Cno = '2';
  

• 建立基于视图的视图

  【例】建立计算机系选修了2号课，且成绩在90分以上的学生视图。

  CREATE VIEW CS_90
  	AS SELECT Sno,Sname,Grade
  		FROM CS_SC
  		WHERE Grade >= 90;
  

• 一类不易扩充的视图

  以SELECT *方式创建的视图可扩充性差，应尽可能避免。

  【例】将Student中所有女生定义为一个视图。

  CREATE VIEW F_Std(num,name,age,sex,dept)
  	AS SELECT *
  		FROM Student
  		WHERE Ssex = '女';
  

  说明：

  • SELECT * 的实际列顺序和含义依赖于当前 Student 表的结构。
  • 如果原表结构发生改变（例如列顺序调整、新增列），视图就可能：
    • 绑定错误列；
    • 返回不完整或错误数据；
    • 报错或变得不可用。

  这就是所谓的“扩充性差”或“可维护性差”

  下面是非SELECT *方式

  CREATE VIEW F_Std(num,name,age,sex,dept)
  	AS SELECT Sno,Sname,Sage,Ssex,Sdept
  		FROM Student;
  		WHERE Ssex = '女'；
  

  说明：

  • 显式列出所需字段，与表结构解耦；
  • 哪怕 Student 表将来增加了新字段，这个视图仍然安全稳定；
  • 可读性强，维护者能清楚知道视图中每一列来自哪里。

删除视图

• DROP VIEW <视图名>
  

  • 该语句从数据字典中删除指定的视图定义
  • 删除基本表时，由该基本表导出的所有视图定义都必须显示删除。

• 【例】删除学生视图CS_90

  DROP VIEW CS_90;
  

视图查询

• 从用户（数据库使用者，也就是写SQL的人，比如你与我、开发者、数据分析师、学生等）角度：查询视图与查询基本表相同

• DBMS实现视图查询的方法

  • 实体化查询（View Materialization）
    1. 有效性检查：检查所查询的视图是否存在
    2. 执行视图定义，将视图临时实体化，生成临时表
    3. 查询视图转化为查询临时表
    4. 查询完毕删除被实体化的视图（临时表）
  • 视图消解法（View Resolution）
    1. 进行有效性检查，检查查询的表、视图等是否存在；如果存在，则从数据字典中取出视图的定义；
    2. 把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询；
    3. 执行修正后的查询。

• 【例】查询计算机系年龄小于23岁的学生。

  SELECT * 
  	FROM Sage_23 -- 是前文的一张视图
  	WHERE Sdept = '计算机系';
  

  • 视图消解法：系统首先从数据字典中取出视图定义，把查询语句与视图定义中的子查询合并在一起，然后在相关基本表上执行查询。转换后的查询语句为：

    SELECT *
    	FROM Student
    	WHERE Sdept = '计算机系' AND Sage < 23;
    

• 【例】查询专业系，要求学生平均年年龄小于21岁。

  下面是创建视图时的SQL语句

  CREATE VIEW D_Sage(Sdept,Average)
  	AS SELECT Sdept,AVG(Sage)
  		FROM Student
  		GROUP BY Sdept;
  

  下面是查询专业系，要求学生平均年年龄小于21岁的语句

  SELECT Sdept
  	FROM D_Sage
  	WHERE Average < 21;
  

  那么DBMS如果采用视图消解法，会自动将它转化为对基本表的查询

  SELECT Sdept
  	FROM Student
  	GROUP BY Sdept
  	HAVING AVG(Sage) < 21;
  

• 视图消解法的局限性

  • 有些情况下，视图消解法不能生成正确查询。
  • 采用视图消解法的DBMS会限制这类查询。
  • 对于简单视图，视图消解总是能正常进行。

• 实体化视图

  • 无限制

视图更新

• 对视图的数据插入、删除、修改最终转换为对基本表的操作来进行（视图和基本表之间的更新是同步的）

• 用户角度：更新视图与更新基本表相同

• 指定WITH CHECK OPTION子句后，DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新

• 有些视图是不可更新的：因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新。

• 【例】通过视图Sage_23插入学生刘敏的信息（‘20041’,‘刘晨曦’,20,‘男’,‘数学’）

  先给出视图的定义

  CREATE VIEW Sage_23
  	AS SELECT Sno,Sname,Sage,Ssex,Sdept
  	FROM Student
  	WHERE Sage < 23
  	WITH CHECK OPTION;
  

  下面给出SQL执行语句

  INSERT INTO Sage_23
  	VALUES ('20041','刘晨曦',20,'男','数学');
  

  以上插入转换成如下语句执行：

  INSERT INTO Studnet
  	VALUES ('20041','刘晨曦',20,'男','数学');
  

• 【例】通过视图Sage_23修改学生王茵的年龄为21岁

  UPDATE Sage_23
  	SET Sage = 21
  	WHERE Sname = '王茵';
  

  该修改转换为学生表的修改：

  UPDATE Student
  	SET Sage = 21
  	WHERE Sname = '王茵';
  

  • 因修改后学生年龄小于23岁，该操作可直接对Student修改。

视图更新的限制

• 【例】通过视图D_Sage插入计算机系学生的平均年龄（‘计算机’，21）；

  INSERT INTO D_Sage
  	VALUES ('计算机系'，21)
  

  以上插入语句无法执行，因为视图D_Sage是不可更新的视图（non-updatable view）

  下面请看该视图的创建语句

  CREATE VIEW D_Sage(Sdept,Average)
  	AS SELECT Sdept,AVG(Grade)
  		FROM Student
  		GROUP BY Sdept;
  

  为什么D_Sage是不可更新的？

  • 因为它违反了视图可更新的基本规则——它使用了聚合函数AVG(Sage)和GROUP BY。

  • MySQL 中以下几种视图都是“不可更新视图”：

    特征	是否可更新
    使用聚合函数（如 AVG, SUM, COUNT）	不可更新
    使用 GROUP BY 或 HAVING	不可更新
    使用 DISTINCT	不可更新
    联合多个表（JOIN）且不满足主键唯一性条件	不可更新
    子查询、窗口函数等复杂结构	不可更新

• 【例】通过视图CS_SC删除学生刘明亮的信息

  DELETE FROM CS_SC
  	WHERE Sname = '刘明亮';
  

  以上语句无法执行，因为不能转化为对基本表的操作，删除操作的语义不明确。

  CREATE VIEW CS_SC(Sno,Sname,Grade)
  	AS SELECT Student,Sno,Sname,Grade
  		FROM Student INNER JOIN SC 
  			ON Student.Sno = SC.Sno
  		WHERE Sdept = '计算机系'
  

  以上语句无法执行的原因：

  1. 原因1：视图基于多表JOIN

     • CS_SC同时包含了Student表的Sname字段和SC表的Grade字段

     • 执行DELETE时，数据库无法判断我们到底想：

       • 删除Student表中学号为某人的记录？
       • 还是删除SC表中该学生的选课成绩？
       • 还是两个一起删？

       数据库认为这种操作“语义不明确”，因此拒绝执行。

  2. 原因2：视图中没有主键或唯一行对应

     • 我们通过连接产生的视图中，每一行可能来自多个原始表的组合
     • 数据库无法将视图中的一行唯一映射回某一张表的一行
     • 所以不能执行删除

• 视图更新是一个比较复杂的问题，在实际商品化系统中对视图的更新都有限制。

• 有的视图是不可更新的，但也有一些视图是可更新的而实际系统没有实现

  • 不可更新的视图：理论上证明不可更新的视图
  • 不允许更新的视图：实际的数据库系统不支持更新，但其本身从理论上可以更新的视图

• 仅在一个表上取其行列值且其列中包含了候选键，这样所形成的视图都是可更新的，这类视图称为“行列子集视图”。

• 除行列子集视图外的视图的更新都会受到限制。

• DB2（Data Base 2 是IBM公司推出的一款关系型数据库管理系统）对视图更新的限制：

  • 由两个以上基本表导出的视图不允许更新。

  • 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。

  • 若视图的字段来自聚集函数或含有GROUP BY子句、含有DISTINCT短语，则此视图不允许更新

  • 若视图定义中由嵌套查询，并且内层查询的FROM子句涉及的表也是导出该视图的基本表，则此视图不允许更新。

    例如

    CREATE VIEW V_Test AS
    SELECT Sno, Sname
    FROM Student
    WHERE Sage > (
        SELECT AVG(Sage)
        FROM Student
    );
    

    • 外层查询用了Student
    • 内层查询（子查询）也用了Student
    • 这会造成逻辑冲突：我们要更新哪一个Student表？内层的？外层的？会影响平均数吗？逻辑不明确。

  • 一个不允许更新的视图上定义的视图也不允许更新。

视图的作用

• 视图提供了数据的逻辑独立性

  • 数据库的逻辑结构发生变化时，由于数据库的数据不变，可以通过对视图的重新定义，使用户的外模式保持不变，从而使查询视图数据的应用程序不必修改。
  • 视图在一定程度上保证了数据的逻辑独立性
  • 视图只能在一定程度上提供了数据的逻辑独立性
    • 视图更新是有条件的，视图的重新定义可能会影响到数据的更新，此时修改数据的应用程序可能也需要做相应的修改

  【例】学生关系Student(Sno,Sname,Ssex,Sage,Sdept)，垂直分成两个基本表：SX(Sno,Sname,Sage)，SY(Sno,Ssex,Sdept)

  CREATE VIEW Student(Sno,Sname,Ssex,Sage,Sdept)
  	AS 
  	SELECT SX,Sno,SX.Sname,SY.Ssex,SX,Sage,SY.Sdept
  	FROM SX,SY
  	WHERE SX.Sno = SY.Sno;
  

  使用户外模式保持不变，用户应用程序通过视图仍然能够查找数据。

• 简化了用户视图

  • 视图使用户把注意力集中在自己所关系的数据上，简化了用户的数据结构；
  • 定义视图能够简化用户的操作；适当的利用视图可以更清晰的表达查询
    • 基于多张表连接形成的视图
    • 基于复杂嵌套查询的视图
    • 含导出属性的视图

• 视图使用户以不同角度看待相同的数据

  • 视图机制能使不同用户以不同方式看待同一数据，适应数据库共享的的需要。

• 视图提供了安全保护功能

  • 对不同用户定义不同视图，使每个用户只能只看到他有权看到的数据，实现对机密数据的保护。
  • 可以通过WITH CHECK OPTION对关键数据定义操作限制，比如操作时间的限制。

  【例】通过视图限定1号课程的选课记录只能在工作时间进行。

  CREATE VIEW V_SC AS
  	SELECT Sno,Cno,Grade
  		FROM SC
  		WHERE Cno = '1'
  		AND TO_CHAR(SYSDATE,'HH24')
  			BETWEEN 9 AND 17	-- 只允许9~17点
  		AND TO_CHAR(SYSDATE,'D')
  			BETWEEN 2 AND 6	-- 只允许周一到周五
  	WITH CHECK OPTION;
  

  注意：

  • 这是Oricle的语法（MySQL不支持TO_CHAR(SYSDATE,'HH24')，但可以用HOUR(CURRENT_TIME)等代替）
  • WITH CHECK OPTION 是保证你 不能插入不符合视图条件 的数据
  • 如果你绕过视图，直接操作 SC 表，是不受这个限制的

4.8 SQL的数据控制

SQL的数据控制

• SQL的数据控制功能包括

  • 数据的安全性——第6章
  • 数据的完整性——第7章
  • 并发控制——第9章
  • 数据恢复——第8章

• SQL对数据的安全保护采取了许多措施

  • 视图机制
  • 权限机制：通过权限控制用户对数据的操作，只有拥有了相应的权限，才能实现相应的操作
  • ……

• SQL中的权限

  在SQL中，用户是否有权限对数据库中的内容执行操作（比如查询、插入、修改、删除），是由严格控制的。

• 不同的数据对象有不同的操作权限

  • 数据库
    • CREATE TABLE
  • 表
    • SELECT、INSERT、DELETE、UPDATE
    • ALTER、INDEX
  • 属性列
    • SELECT、INSERT、DELETE、UPDATE
  • 视图
    • SELECT、INSERT、DELETE、UPDATE

• SQL权限由谁定义？

  DBA和表的建立者（即表的主人）

• SQL权限如何定义？

  SQL 语句：

  • 授权语句GRANT
  • 回收语句REVOKE

• 表或视图的建立者（Owner）拥有所创建表或视图上的所有权限，包括授予权。

授权

• 授权：是指有授予权的用户将自己所拥有的权限授予其他用户。

• 语句格式：

  GRANT <权限1>[,<权限2>]……
  	[ON <对象类型> <对象名>]
  	TO <用户1>[,<用户2>……|PUBLITC]
  	[WITH GRANT OPTION];
  

  MySQL版：

  GRANT SELECT, DELETE
  ON 数据库名.表名
  TO 'User2'@'主机'
  WITH GRANT OPTION;
  

• 功能：把指定对象的某些权限授予指定的用户。

• WITH GRANT OPTION短语表示被授权的用户还可以把获得的权限再授予给其他用户。

• PUBLIC短语表示把权限授予数据库的所有用户。（MySQL不支持该短语）

用户

这里介绍MySQL里面，用户和数据库、表的关系

1. 用户和数据库/表是独立的

   • 用户属于 MySQL服务器（是负责管理个人所有数据库和用户账户的那台“数据库软件后台”）
   • 表属于 某个数据库
   • 授权关系决定用户能不能访问表

   用户是“钥匙持有者”，数据库是“房间”，表是“房间里的柜子”

2. 创建数据库的用户是谁？

   • MySQL本事不记录“谁创建了数据库”（不像Oracle一样）

   • 在 MySQL 中，只有当你以某个特定用户身份（即 '用户名'@'主机'）登录，并且该用户被授予了相应范围的 CREATE 权限时，才能创建数据库或表。

     1. “你”是谁？

        • “你”指向的是：当前连接 MySQL的用户账户，例如

          'root'@'localhost'
          'student'@'192.168.1.100'
          'user1'@'%'
          

        • 在MySQL中，用户是由用户名+来源主机组成的唯一标识，这意味着：

          • 'user1'@'localhost'和'user1'@'%'是两个不同的账户
          • 权限必须精确授予给'用户名@'主机'，否则不生效

     2. “被授予权限”指的是什么？

        • MySQL使用GRANT命令用户分配权限
        • CREATE是一种权限类型，代表我们可以：
          • 创建数据库（需要全局CREATE权限）
          • 创建表（需要数据库级CREATE权限）

     3. “相应范围的权限”是什么意思？

        • MySQL的权限可以按照作用范围分为三级：

          级别	示例	可用于
          全局级	ON *.*	作用于所有数据库中的所有对象
          数据库级	ON dbname.*	作用于某个数据库内的所有表
          表级	ON dbname.table	只作用于某张表（对于 CREATE TABLE 不适用）

          注意：CREATE DATABASE必须是全局级；CREATE TABLE至少是数据库级。

     4. “主机”是什么意思

        • 指的是我们连接MySQL服务器时使用的主机地址，比如：

          • localhost（本机）

          • 127.0.0.1（本机IP），IP：在网络中每一台设备的“唯一编号”，就像“你家的门牌号”。

          • 远程IP（如192.168.1.5），远程IP是指：你连接的 MySQL 服务器不在你这台电脑上，而在 局域网中的另一台电脑 或互联网上的某台服务器。

          • 通配符%（代表任何地址），意思是允许 user1 从任何主机（任何 IP 地址）连接 MySQL。

          • 举例：

            • 'user1'@'localhost' → 只能从本机登录

              'user1'@'192.168.1.10' → 只能从 IP 为 192.168.1.10 的主机登录

              'user1'@'%' → 可以从任意 IP 远程连接

3. 怎么选择/创建用户？

   创建用户：

   CREATE USER 'user2'@'localhost' IDENTIFIED BY '123456';
   

   授权权限：

   GRANT ALL PRIVILEGES ON mydb.* TO 'user2'@'localhost';
   -- 或精细一点：
   GRANT SELECT, INSERT ON mydb.Student TO 'user2'@'localhost';
   

4. 如何查看现有用户？

   SELECT user, host FROM mysql.user;
   

5. 查看某用户权限：

   SHOW GRANTS FOR <用户名>
   

6. 查看当前登陆用户：

   SELECT USER();-- 当前连接标识，显示连接数据库时使用的用户名和主机地址（user@host）
   SELECT CURRENT_USER();-- 当前会话权限身份，显示数据库当前识别你是谁（即你的有效权限是哪个用户拥有的权限）
   

授权的例子

【例】授予用户User2在表Student上的查询权和删除权，同时使User2拥有将所得权限授予其他用户的权力。

GRANT SELECT, DELETE
ON TABLE Student
TO User2
WITH GRANT OPTION;

下面展示MySQL语法

SELECT user, host FROM mysql.user; -- 查找当前的所有用户
CREATE USER 'User2'@'localhost' IDENTIFIED BY '123456';-- 创建一个新用户,密码为'123456'
GRANT SELECT,DELETE
    ON sst.Student
    TO 'User2'@'localhost'
    WITH GRANT OPTION;

【例】授予用户User4对表SC的列Grade的修改权

GRANT UPDATE(Grade)
ON TABLE SC
TO User4;

下面展示MySQL语法

CREATE USER 'User4'@'localhost' IDENTIFIED BY '123456';
GRANT UPDATE(Grade)
	ON sst.SC
	TO 'User4'@'localhost';

【例】把对表SC的查询权限授予所有用户

GRANT SELECT 
	ON sst.SC
	TO PUBLIC;

但MySQL不支持PUBLIC，所以如果要实现，需要列出全部用户

【例】把对Student表和Course表的全部权限授予用户U2和U3

GRANT ALL PRIVILEGES
ON sst.Student TO 'U2'@'localhost', 'U3'@'localhost';

GRANT ALL PRIVILEGES
ON sst.Course TO 'U2'@'localhost', 'U3'@'localhost';

MySQL不可以把两个表写在一块

GRANT ALL PRIVILEGES
ON sst.Student,sst.Course
TO 'U2'@'localhost', 'U3'@'localhost';

这个会报错。

【例】DBA把在数据库sst中建立表的权限授予用户U4

GRANT CREATE TABLE
ON DATABASE dbsc
TO U4;

下面展示MySQL语法

GRANT CREATE 
ON sst.* 
TO 'U4'@'localhost';

权限回收

• 具有授予权的用户可以通过回收语句将所授予的权限回收

• 回收语句格式为

  REVOKE <权限1>[,<权限2>]……
  	[ON <对象类型> <对象名>]
  	FROM <用户1>[,<用户2>……|PUBLIC]
  	[RESRICT|CASCADE]
  

  • CASCADE选项表示回收权限时要引起级联操作，要把转授出去的权限一起回收。
  • RESTRICT选项表示，只有当用户没有将拥有的权限转授给其他用户时才能回收该用户的权限，否则系统将拒绝执行。

  下面展示MySQL语法

  REVOKE <权限1>[,<权限2>]……
  	ON 数据库名.表名
  	FROM '用户名'@'主机';
  

  同时 MySQL不支持**RESTRICT | CASCADE** 子句：MySQL默认行为类似于RESTRICT，如果用户已经将该权限转授给他人，撤销操作会失败，除非先撤回那些转授

【例】回收用户User2对学生表Student的查询权和删除权。

REVOKE SELECT,DELETE
	ON TABLE Student
	FROM User2
	CASCADE;

下面展示MySQL语法

REVOKE SELECT,DELETE
	ON sst.Student
	FROM 'User2'@'localhost';

【例】把用户U4修改学生学号的权限收回。

REVOKE UPDATE(Sno)
	ON TABLE Student
	FROM U4;

下面展示MySQL语法

REVOKE UPDATE(Sno)
	ON sst.Student
	FROM 'U4'@'localhost';

SQL授权机制小结

• DBA拥有对数据库所有对象的所有权限，并可以根据应用的需要将不同的权限授予不同的用户。
• 用户对自己建立的基本表和视图拥有全部的操作权限，并且可以用GRANT语句把其中某些权限授予其他用户。
• 被授权的用户如果有“继续授权”的许可，还可以把获得的权限再授予其他用户。
• 所有授予出去的权力在必要时有都可以用REVOKE语句收回