SQL基础知识复习

数据类型

 

关系模型中一个很重要的概念是域。每一个属性来自一个域，它的取值必须是域中的值。在SQL中域的概念用数据类型来实现。

 

数据类型	含义
CHAR(n)	长度为n的定长字符串
VARCHAR(n)	最大长度为n的变长字符串
INT	长整数（也可写作INTEGER）
SMALLINT	短整数
NUMERIC(p,d)	定点数，由p位数字（不包括符号、小数点）组成，小数后面有d位数字
REAL	取决于机器精度的浮点数
Double Precision	取决于机器精度的双精度浮点数
FLOAT(n)	浮点数，精度至少为n位数字
DATE	日期，包含年、月、日，格式为YYYY-MM-DD
TIME	时间，包含一日的时、分、秒，格式为HH:MM:SS

 

 

 

-----------------------------------------------------------------------

 

 

 

基本表的定义、删除与修改

 

一、定义基本表

 

CREATE TABLE <表名>（ <列名> <数据类型>  [列级完整性约束条件]

 

                  [,  <列名> <数据类型>  [列级完整性约束条件]]

 

                  …

 

                  [,  <表级完整性约束条件>]

 

）

 

 

 

建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统地数据字典中，当用户操作表中的数据时由RDBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

 

 

 

CREATE TABLE course（

 

    Cno    char(4)        PRIMARY KEY,

 

    Cname char(40)   UNIQUE,

 

    Cpno   char(4) ,

 

    Foreign KEY Cpno REFERENCES  course（Cno）,

 

    /* Cpno 是外键，被参照表是course，被参照列是Cno */

 

）

 

 

 

本例说明参照表和被参照表可以是同一个表

 

 

 

CREATE TABLE SC（

 

    Cno    char(4)        ,

 

    Cname char(40)   ,

 

    Cpno   char(4) ,

 

    PRIMARY KEY   （Cpno ,Cno）,

 

    /*主键由两列组成，必须作为表级完整性进行定义*/

 

）

 

二、修改基本表

 

ALTER   TABLE <表名>

 

[ADD   <新列名>   <数据类型>  [完整性约束]]

 

[DROP  <完整性约束名>]

 

[DROP  COLUMN  <列名>]

 

[ALTER  COLUMN    <列名>  <数据类型>];

 

不论基本表中是否已有数据，新增加的列一律为空值。

 

 

 

 

 

三、删除基本表

 

DROP TABLE    <表名>  [RESTRICT|CASCADE]

 

若选择RESTRICT，则该表的删除是有限制条件的。欲删除的基本表不能被其它的表的约束所引用（如CHECK,FOREIGN KEY等约束），不能有视图，不能有触发器，不能有存储过程或函数。如果存在这些依赖该表的对象，则此表不能被删除。

 

若选择CASCADE：则该表的删除没有限制条件。再删除基本表的同时，相关的依赖对象，例如视图，都将一起删除。

 

 

 

-----------------------------------------------------------------------

 

 

 

索引的建立与删除

 

在RDBMS中索引一般采用B+树、HASH索引来实现。B+树索引具有动态平衡的优点。HASH索引具有查找速度快的特点。索引是关系数据库的内部实现技术，属于内模式的范畴。

 

用户使用CREATE INDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引。至于某一个索引是采用B+树，还是HASH索引则由具体的RDBMS来决定。

 

一、建立索引

 

CREATE   [UNIQUE][CLUSTER]  INDEX  <索引名> 

 

ON  <表名> (<列名>  [ <次序> ]  [,  <列名>  [ <次序>]]…)

 

其中<表名>是要建立索引的基本表的名字。索引可以建立在该表的一列或者多列上，各列名之间用逗号分隔。每个 <列名> 后面还可以用 <次序> 指定索引值的排列次序，可选ASC（升序）或DESC（降序），缺省值是ASC。

 

UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。

 

CLUSTER表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。

 

用户可以在最经常查询的列上建立聚簇索引以提高查询效率。显然在一个基本表上最多只能建立一个聚簇索引。建立聚簇索引后，更新该索引列上的数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。

 

 

 

二、删除索引

 

DROP INDEX <索引名>

 

 

 

-----------------------------------------------------------------------

 

 

 

数据查询

 

SELECT [ALL | DISTINCT]<目标列表达式>[,<目标列表达式>]…

 

FROM <表名或视图名>[,表名或视图名]…

 

[WHERE <条件表达式>]

 

[GROUP BY <列名1> [ HAVING <条件表达式>]]

 

[ORDER BY <列名2> [ASC | DESC]]

 

 

 

整个SELECT语句的含义是，根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。

 

如果有GROUP BY子句，则将结果按<列名 1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY 子句带HAVING短语，则只有满足指定条件的组才予以输出。

 

如果有ORDER BY 子句，则结果表还要按<列名2>的值升序或降序排序。

 

 

 

SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式。如下：

 

SELECT Sname ,2004-Sage FROM Student;

 

<目标列表达式>不仅可以是算术表达式，还可以是字符串常量、函数等。如下：

 

SELECT Sname,’Year of Birth:’,2004-Sage,LOWER(Sdept) FROM Student;

 

还可以指定别名来改变查询结果的列标题。如下：

 

SELECT Sname NAME,2004-Sage BIRTHDAY FROM Student;

 

 

 

如果想去掉结果表中的重复行，必须指定DISTINCT关键词：

 

SELECT DISTINCT Sno FROM SC;

 

如果没有指定 DISTINCT,则缺省为ALL,即保留结果表中取值重复的。

 

 

 

查询满足条件的元组

 

WHERE子句常用的查询条件

 

查询条件	谓词
比较	=，>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符
确定范围	BETWEEN AND,NOT BETWEEN AND
确定集合	IN,NOT IN
字符匹配	LIKE,NOT LIKE
空值	IS NULL,IS NOT NULL
多重条件（逻辑运算）	AND,OR,NOT

 

 

 

字符匹配

 

[ NOT ]LIKE ‘<匹配串>’[ESCAPE ‘<换码字符>’]

 

<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。其中：

 

%（百分号）代表任意长度（长度可以为0）的字符串。例如a%b表示以a开头，以b结尾的任意长度的字符串。

 

_(下横线)代表任意单个字符。注意：一个汉字占两个字符的位置。所以要用两个下横线匹配。

 

如果LIKE后面的匹配串中不含通配符，则可以用=（等于）运算符取代LIKE谓词，用!=或<>(不等于)运算符取代NOT LIKE。

 

如果要查询的字符串本身就含有通配符%或_，这时就要使用ESCAPE’<换码字符>’短语，对通配符进行转义。例：

 

SELECT Cno,Ccredit

 

FROM Course

 

WHERE Cname LIKE ‘DB\_Design’ ESCAPE ’\’;

 

ESCAPE ’\’表示’\’为换码字符。这样匹配串中紧跟在’\’后面的字符’_’不再具有通配符的含义，转义为普通’_’字符。

 

 

 

涉及到空值的查询

 

SELECT Sno,Cno

 

FROM SC

 

WHERE Grade IS NULL;

 

注意：这里的IS不能用等号（=）代替。

 

 

 

ORDER BY子句

 

用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，缺省值为升序。

 

对于空值，若按升序排列，含空值的元组将在最后显示。若按降序排列，空值的元组将最先显示。

 

 

 

聚集函数（aggregate functions）

 

为了进一步方便用户，增强检索功能，SQL提供了许多聚集函数，主要有：

 

COUNT ( [ DISTINCT | ALL ]  * )         统计元组个数

 

COUNT ( [ DISTINCT | ALL ]  <列名>) 统计一列中值的个数

 

SUM ( [ DISTINCT | ALL ]  <列名>  ) 计算一列值的总和（此列必须是数值型）

 

AVG ( [ DISTINCT | ALL ]  <列名>  ) 计算一列值的平均值（此列必须是数值型）

 

MAX ( [ DISTINCT | ALL ]  <列名>  )   求一列值中的最大值

 

MIN ( [ DISTINCT | ALL ]  <列名>  ) 求一列值中的最小值

 

如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复键。默认为ALL，不取消重复值。例：

 

SELECT COUNT ( DISTINCT Sno)

 

FROM SC;

 

在聚集函数遇到空值时，除COUNT(*)外，都跳过空值而只处理非空值。

 

注意：WHERE子句中是不能用聚集函数作为条件表达式的。

 

 

 

GROUP BY子句

 

GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。对查询结果分组的目的是细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组，即每一组都有一个函数值。例：

 

SELECT Cno,COUNT(Sno)

 

FROM SC

 

GROUP BY Cno;

 

如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。例：

 

SELECT Sno

 

FROM SC

 

GROUP BY Sno

 

HAVING COUNT(*)>3;

 

这里先用GROUP BY 子句按Sno进行分组，再用聚集函数COUNT对每一组计数。HAVING短语给出了选择组的条件，只有满足条件（即元组个数>3，表示此学生选修的课超过3门）的组才会被选出来。

 

WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

 

 

 

-----------------------------------------------------------------------

 

 

 

连接查询

 

连接查询是关系数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等

 

 

 

一、等值与非等值连接查询

 

连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：

 

[<表名1>.]<列名1>  <比较运算符>  [<表名2>.]<列名2>

 

其中比较运算符主要有：=、>、<、>=、<=、!=（或<>）等。

 

此外连接谓词还可以使用下面的形式：

 

[<表名1>.]<列名1>  BETWEEN  [<表名2>.]<列名2> AND [<表名2>.]<列名3>

 

当连接运算符为=号时，称为等值连接。使用其它运算符称为非等值连接。

 

连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。

 

若在等值连接中把目标列中重复的属性列去掉则为自然连接。

 

 

 

二、自身连接

 

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。例：

 

SELECT FIRST.Cno,SECOND.Cpno

 

FROM Course FIRST ,Course SECOND

 

WHERE FIRST.Cpno=SECOND.Cno;

 

其中为Course表取两个别名，一个是FIRST,一个是SECOND。

 

 

 

三、外连接

 

在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。如果想要把舍弃的元组也包含在结果关系中，而在其它属性上填空值（NULL），这时就需要使用外连接。

 

左外连接列出左边关系中所有的元组，右外连接列出右边关系中所有的元组。例：
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

 

FROM  Student LEFT JOIN SC ON (Student.Sno=SC.Sno)

 

 

 

四、复合条件查询

 

WHERE子句中可以有多个连接条件，称为复合条件查询。例：

 

SELECT Student.Sno,Sname

 

FROM Student,SC

 

WHERE Student.Sno=SC.Sno AND

 

       SC.Cno=’2’   AND  SC.Grade>80;

 

 

 

-----------------------------------------------------------------------

 

 

 

嵌套查询

 

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询（nested query）。

 

SQL语言允许多层嵌套查询。嵌套查询使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。以层层嵌套的方式来构造程序正是SQL(Structured Query Language)中“结构化”的含义所在。

 

注意：子查询的SELECT 语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。

 

 

 

1.带有IN谓词的子查询

 

在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。例：查询与“刘晨”在同一个系学习的学生

 

SELECT Sno,Sname,Sdept

 

FROM Student

 

WHERE Sdept IN

 

              (SELECT Sdept

 

FROM Student

 

WHERE Sname=”刘晨”);

 

本例中，子查询的查询条件不依赖与父查询，称为不相关子查询。

 

如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询（Correlated Subquery）,整个查询语句称为相关嵌套查询（Correlated nested query）。例：找出每个学生超过他选修课程平均成绩的课程号

 

SELECT Sno,Cno

 

FROM SC x

 

WHERE Grade >=  ( SELECT AVG(Grade)

 

                  FROM SC y

 

                  WHERE y.Sno=x.Sno );

 

X是表SC的别名，又称为元组变量，可以用来表示SC的一个元祖。内层查询是求一个学生所有选修课平均成绩的，至于是哪一个学生的平均成绩要看参数x.Sno的值，而该值是与父查询相关，因此这类查询称为相关子查询。

 

内层查询由于与外层查询有关，因此必须反复求值。

 

 

 

2.带有ANY(SOME)或ALL谓词的子查询

 

子查询返回单值时可以用比较运算符，但返回多值时要用ANY（有的系统用SOME）或ALL谓词修饰符。而使用ANY或ALL谓词时必须同时使用比较运算符。其语义是：

 

 

 

>ANY       大于子查询结果中的某个值

 

>ALL       大于子查询结果中的所有值

 

<ANY       小于子查询结果中的某个值

 

<ALL       小于子查询结果中的所有值

 

>=ANY      大于等于子查询结果中的某个值

 

>=ALL      大于等于子查询结果中的所有值

 

<=ANY      大于等于子查询结果中的某个值

 

<=ALL      小于等于子查询结果中的所有值

 

=ANY       等于子查询结果中的某个值

 

=ALL       等于子查询结果中的所有值（通常没有实际意义）

 

!=（或<>）ANY  不等于子查询结果中的某个值

 

!= (或<>）ALL  不等于子查询结果中的任何一个值

 

 

 

事实上，用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高。ANY,ALL与聚集函数的对应关系如下表：

 

	=	<>或!=	<	<=	>	>=
ANY	IN	--	<MAX	<=MAX	>MIN	>=MIN
ALL	--	NOT IN	<MIN	<=MIN	>MAX	>=MAX

 

 

 

3.带有EXISTS谓词的子查询

 

EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值”true”或逻辑假值”false”。

 

与EXISTS谓词相对应的是NOT EXISTS谓词。使用存在量词NOT EXISTS 后，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值。例：查询没有选修1号课程的学生姓名

 

SELECT Sname

 

FROM Student

 

WHERE NOT EXISTS

 

           ( SELECT *

 

             FROM SC

 

WHERE Sno=Student.Sno  AND Cno=’1’);

 

由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS 的子查询只返回真值或假值，给出列名并无实际意义。

 

 

 

-----------------------------------------------------------------------

 

 

 

集合查询

 

SELECT 语句的查询结果是元组的集合，所以多个SELECT 语句的结果可进行集合操作。集合操作主要包括并操作UNION，交操作INTERSECT,差操作EXCEPT。

 

注意：参加集合操作的个查询结果的列数必须相同；对应项的数据类型也必须相同。例：查询计算机科学系的学生及年龄不大于19岁的学生。

 

SELECT *

 

FROM Student

 

WHERE Sdept=’CS’

 

UNION

 

SELECT *

 

FROM Student

 

WHERE Sage<=19;

 

使用UNION将多个查询结果合并起来时，系统会自动去掉重复元组。如果要保留重复元组则用UNION ALL操作符。

 

 

 

-----------------------------------------------------------------------

 

 

 

数据更新

 

插入数据

 

SQL的数据插入语句INSERT通常有两种形式。一种是插入一个元组，另一种是插入子查询结果。后者可以一次插入多个元组。

 

1.插入元组

 

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

 

VALUES ( <常量1 > [, <常量2> ]… );

 

例：

 

INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage)

 

VALUES(‘2005213’,’陈东’,’男’,’IS’,18);

 

 

 

2.插入子查询结果

 

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

 

子查询;

 

 

 

例：

 

INSERT INTO Dept_age(Sdept,Avg_age)

 

SELECT Sdept ,AVG(Sage)

 

FROM Student

 

GROUP BY Sdept;

 

 

 

修改数据

 

UPDATE <表名>

 

SET <列名>= <表达式>[, <列名>= <表达式>]…

 

[WHERE <条件>];

 

 

 

如果省略WHERE子句，则表示要修改表中的所有元组。子查询也可以嵌套在UPDATE语句中，用以构造修改的条件。例：

 

UPDATE SC

 

SET Grade=0

 

WHERE ‘SC’=

 

           (SELECT Sdept

 

           FROM Student

 

           WHERE Student.Sno=SC.Sno);

 

 

 

删除数据

 

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

 

 

 

-----------------------------------------------------------------------

 

 

 

视图

 

建立视图

 

CREATE VIEW  <视图名>  [ ( <列名> [, <列名> ] … )]

 

AS <子查询>

 

[WITH CHECK OPTION];

 

其中，子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY 子句和DISTINCT短语

 

WITH CHECK OPTION 表示对视图进行UPDATE,INSERT,DELETE操作时要保证更新，插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

 

组成视图的属性列名或者全部指定或者全部省略，没有第三种选择。如果省略了视图的各个属性列名，则隐含该视图由子查询中SELECT子句中目标列中的诸字段组成。但在下面三种情况下必须明确指定组成视图的所有列名：

 

1. 某个目标列不是单纯的属性名，而是聚集函数或列表达式；

 

2. 多表连接时选出了几个同名列作为视图的字段；

 

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

 

例：建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。

 

CREATE VIEW IS_Student

 

AS

 

SELECT Sno,Sname,Sage

 

FROM Student

 

WHERE Sdept=’IS’

 

WITH CHECK OPTION;

 

由于定义时带上了WITH CHECK OPTION子句，以后对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept=’IS’的条件。

 

 

 

删除视图

 

DROP VIEW <视图名> [CASCADE]

 

如果该视图上还导出了其它视图，则使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除。

 

 

 

查询视图

 

RDBMS执行对视图的查询时，首先进行有效性检查。检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解（View Resolution）

 

 

 

更新视图

 

由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。像查询视图那样，对视图的更新操作也是通过视图的消解，转换为对基本表的更新操作。

 

 

 

-----------------------------------------------------------------------

 

 

 

授权（Authorization）与回收

 

一、GRANT

 

GRANT <权限> [, <权限> ]…

 

ON <对象类型> <对象名> [, <对象类型> <对象名>]…

 

TO <用户> [, <用户> ]…

 

[WITH GRANT OPTION];

 

含义为：将对指定操作对象的指定操作权限授予指定的用户。发出该GRANT语句的可以是DBA，也可以是该数据库对象创建者（即属主Owner），也可以是已经拥有该权限的用户。接受权限的用户可以是一个或多个具体用户，也可以是PUBLIC,即全体用户。

 

如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其它的用户。如果没有指定WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，不能传播该权限。

 

注意：不允许循环授权，即被授权者不能把权限再授回给授权者或其祖先。

 

例：把查询Student表的权限授给用户U1

 

GRANT SELECT

 

ON TABLE Student

 

TO U1;

 

 

 

关系数据库系统中主要的存取权限如下表：

 

对象类型	对象	操作类型
数据库	模式	CREATE SCHEMA
基本表	CREATE TABLE,ALTER TABLE
模式	视图	CREATE VIEW
索引	CREATE INDEX
数据	基本表和视图	SELECT,INSERT,UPDATE,DELETE,REFERENCES, ALL PRIVILEGES
数据	属性列	SELECT,INSERT,UPDATE,REFERENCES, ALL PRIVILEGES

 

 

 

二、REVOKE

 

REVOKE <权限>[, <权限>]…

 

ON <对象类型> <对象名>[, <对象类型> <对象名>]…

 

FROM <用户>[, <用户>]… [CASCADE|RESTRICT];

 

 

 

三、创建数据库模式的权限

 

GRANT和REVOKE语句向用户授予或收回对数据的操作权限。对数据模式的授权则有DBA在创建用户时实现。

 

CREATE USER 语句一般格式如下：

 

CREATE USER <username>

 

[WITH] [DBA|RESOURCE|CONNECT]

 

说明：1.只有系统的超级用户才有权创建一个新的数据库用户。

 

      2.新创建的数据库用户有三种权限：DBA、RESOURCE和CONNECT。缺省值为CONNECT。

 

      3.拥有CONNECT权限的用户不能创建新用户，不能创建模式，不能创建基本表；只能登录数据库。然后由DBA或其他用户授予他应有的权限，根据授得的权限情况他可以对数据库对象进行权限范围内的操作。

 

 

 

权限和可执行的操作对照表如下：

 

拥有的权限	可否执行的操作
CREATE USER	CREATE SCHEMA	CREATE TABLE	登录数据库执行数据查询和操纵
DBA	可以	可以	可以	可以
RESOURCE	不可以	不可以	可以	可以
CONNECT	不可以	不可以	不可以	可以，但必须拥有相应权限

 

 

 

-----------------------------------------------------------------------

 

 

 

数据库角色

 

 

 

一、角色的创建

 

CREATE ROLE <角色名>

 

 

 

二、给角色授权

 

GRANT <权限> [,<权限>]…

 

ON <对象类型> <对象名>

 

TO <角色>[, <角色> ]…

 

 

 

三、将一个角色授予其他的角色或用户

 

GRANT <角色1> [, <角色2> ]…

 

TO <角色3> [ , <用户1> ]…

 

[WITH ADMIN OPTION]

 

 

 

如果指定了WITH ADMIN OPTION子句，则获得某种权限的角色或用户还可以把这种权限再授其它的角色。

 

 

 

四、角色权限的收回

 

REVOKE <权限> [,<权限>]…

 

ON <对象类型> <对象名>

 

FROM <角色>[, <角色> ]…

 

 

 

-----------------------------------------------------------------------

 

 

 

用户定义完整性

 

 

 

一、属性上的约束条件的定义

 

CREATE TABLE Student

 

（Sno CHAR(9) PRIMARY KEY,

 

Sname  CHAR(8)  NOT NULL,

 

Ssex CHAR(2)  CHECK (Ssex IN (‘男’,’女’)),

 

）

 

 

 

二、元组上的约束条件的定义

 

CREATE TABLE Student

 

(Sno CHAR(9) ,

 

Sname  CHAR(8)  NOT NULL,

 

Ssex CHAR(2),

 

Sage SMALLIINT,

 

Sdept CHAR(20),

 

PRIMARY KEY (Sno),

 

CHECK ( Ssex=’女’ OR Sname NOT LIKE ‘Ms.%’)

 

)

 

 

 

完整性约束命名子句

 

CONSTRAINT  <完整性约束条件名>  [PRIMARY KEY 短语 | FOREIGN KEY短语 | CHECK 短语]

 

例：

 

CREATE TABLE Student

 

(Sno NUMERIC(6) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),

 

 Sname CHAR(20) CONSTRAINT C2 NOT NULL,

 

 CONSTRAINT StudentKeys  PRIMARY KEY(Sno)

 

)

 

 

 

修改表中的完整性限制

 

ALTER TABLE Student

 

  DROP CONSTRAINT C1;

 

ALTER TABLE Student

 

  ADD CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999);

 

 

 

-----------------------------------------------------------------------

 

 

 

触发器

 

定义触发器

 

CREATE TRIGGER <触发器名>

 

{ BEFORE | AFTER } <触发事件> ON <表名>

 

FOR EACH { ROW | STATEMENT }

 

[WHEN <触发条件>]

 

<触发动作体>

 

 

 

说明：

 

1.表的拥有者即创建表的用户才可在表上创建触发器，并且一个表上只能创建一定数量的触发器。

 

2.触发器名

 

触发器名可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须是唯一的；并且触发器名和<表名>必须在同一模式下。

 

3.表名。

 

当这个表的数据发生变化时，将激活定义在该表上相应的<触发事件>的触发器，因此，该表也称为触发器的目标表。

 

4.触发事件

 

触发事件可以是INSERT,DELETE或UPDATE，也可以是这几个事件的组合，如INSERT OR DELETE 等。UPDATE 后面还可以有OF<触发列，…>，即进一步指明修改哪些列时触发器激活。

 

5.触发器类型

 

触发器按照所触发动作的间隔尺寸可以分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT）

 

6．触发条件

 

触发器被激活时，只有当触发条件为真时触发动作体才执行；否则触发动作体不执行。如果省略WHEN 触发条件，则触发动作体在触发器激活后立即执行。

 

7.触发动作体

 

  触发动作体既可以是一个匿名PL/SQL过程块，也可以是对已创建存储过程的调用。如果是行级触发器，在两种情况下，用户都可以在过程体中使用NEW和OLD引用UPDATE/INSERT事件之后的新值和UPDATE/DELETE事件之前的旧值。如果是语句级触发器，则不能在触发动作体中使用NEW 或OLD进行引用。

 

例：定义一个BEFORE 行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

 

CREATE TRIGGER Insert_Or_Update_Sal

 

  BEFORE INSERT OR UPDATE ON Teacher

 

  FOR EACH ROW

 

  AS BEGIN

 

     IF (new.Job = ’教授’ ) AND (new.Sal < 4000 ) THEN

 

         new.Sal=4000;

 

      END IF;

 

END;

 

 

 

删除触发器

 

DROP TRIGGER <触发器名> ON <表名>;