模式

定义模式

CREATE SCHEMA 模式名 AUTHORIZATION 用户名

eg:为用户ZHANG定义一个S-T模式：

CREATE SCHEMA "S-T" AUTHORIZATION ZHANG

eg:为用户ZHANG定义一个未命名的模式：

CREATE SCHEMA AUTHORIZATION ZHANG

未定义模式名称的时候，默认模式为用户名

删除模式

DROP SCHEAM 模式名

普通：用于删除空的模式，如果模式下面有对象（表、视图）等，拒绝删除

DROP SCHEAM 模式名 CASCADE

级联：把模式里面的对象一并删除

DROP SCHEAM 模式名 RESTRICT

限制：会把对象转移到公共模式保留并删除模式，但如果对象和模式有联系，则拒绝删除模式

基本表

定义基本元素

数据类型

列级完整性约束条件

表级完整性约束条件

特殊完整性约束条件——断言

相较于列级的CHECK语句，断言能指定更一般的约束

创建断言

CREATE ASSERTION 断言名 CHECK字句;

eg.限制A课程最多60人选修：

CREATE ASSERTION ASSE CHECK(60>=
                            					     SELECT COUNT(*) 
                                                     FROM TAB1,TAB2 
                                                     WHERE TAB1.NUM=TAB2.NUM AND TAB2.CLASS="A");  -- 此处具体操作原理往后看

删除断言

DROP ASSERTION 断言名;

特殊完整性约束条件——触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程

表的拥有者才能在表上创建触发器

触发器只能定义在基本表上，不能定义在视图上

定义触发器

CREATE TRIGGER 触发器名

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

REFERENCING NEW|OLD ROW AS 变量

FOR EACH {ROW|STATEMENT}

[WHEN 触发条件]触发动作体

触发事件：INSERT DELETE UPDATE 或者他们的组合

	 还可以UPDATE OF<触发列1,...>，即进一步指明修改哪些列时激活触发器

BEFORE/AFTER：触发时机，表示在触发事件的操作执行之前激活触发器

ROW/STATEMENT：指明是行级/语句级触发器，行级有多少行就触发多少次，语句级只触发一次

触发条件：只有触发条件为真时才执行动作体，省略WHEN则立即执行动作体

触发动作体：行级可以使用NEW/OLD引用事件之后的新值和事件之前的旧值，语句级不行

eg.对TAB1的Grade属性修改时，若增加了10%，则将操作记录在TAB2(Name,OldGrade,NewGrade)中

CERATE TRIGGER TAB1_T
AFTER UPDATE OF Grade ON TAB1
REFERENCING
	OLD row AS OldTuple,
	NEW row AS NewTuple
FOR EACH ROW
WHEN(NewTuple.Grade>=1.1*OldTuple.Grade)
	INSERT INTO TAB2(Name,OldGrade,NewGrade)
	VALUES(OldTuple.Name,OldTuple.Grade,NewTuple.Grade);

如果触发器内有多个语句，要定义新的定界符（不常用的），并使用BEGIN和END包裹：

-- 1. 将定界符从 ; 改为 //
DELIMITER //

-- 2. 定义包含多条语句的触发器
CREATE TRIGGER TAB1_T
AFTER UPDATE OF Grade ON TAB1
REFERENCING
    OLD row AS OldTuple,
    NEW row AS NewTuple
FOR EACH ROW
BEGIN
    -- 检查条件
    IF (NewTuple.Grade >= 1.1 * OldTuple.Grade) THEN
        -- 这是块内的第一条语句，用 ; 结尾
        INSERT INTO TAB2(Name, OldGrade, NewGrade)
        VALUES(OldTuple.Name, OldTuple.Grade, NewTuple.Grade);
    END IF; -- IF语句也需要用 ; 结尾
-- 3. 使用新的定界符 // 来结束整个 CREATE TRIGGER 命令
END //

-- 4. 将定界符改回默认的 ;
DELIMITER ;

激活触发器

多个触发器执行顺序：

执行BEFORE触发器 -> 激活触发器的SQL语句 -> 执行AFTER触发器

删除触发器

DROP TRIGGER 触发器名 ON 表名;

定义基本表

CREATE TABLE 表名 (列名1 数据类型 列级完整性约束条件, 列名n 数据类型 列级完整性约束条件, 表级完整性约束条件1, 表级完整性约束条件n );

eg:

CREATE TABLE TAB1
(Ch VARCHAR(10),
 Nu NUMBER(10),
 Grade INT NOT NULL,
 PRIMARY KEY(Ch,Nu),     -- 多个主码
 FOREIGN KEY(Ch) REFERENCES TAB2(Ch)
);

在模式中定义表

一个模式包含很多基本表，有三种方式在模式里面定义基本表

创建表的时候指出模式

CREATE TABLE 模式名.表名();

创建模式时直接定义表

CREATE SCHEMA 模式名 AUTHORIZATION 用户名 CREATE TABLE 表名();

事先设置所属的模式

SET SCHEMA ‘my_schema’

eg.

SET SCHEMA 'my_schema';

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

my_table 将被创建在 my_schema 模式下

修改基本表

同样的，修改表时想要指定模式里面的表，就使用“模式名.表名”的方式指定表名

增加新的属性列

ALTER TABLE 表名 ADD 新列名 数据类型 完整性约束条件;

eg.向TAB1里增加时间列Time：

ALTER TABLE TAB1 ADD Time DATE;

当然，还能在ADD后面加上修饰词COLUMN，这样会更容易理解是在添加列，对功能没有影响：

ALTER TABLE TAB1 COLUMN Time DATE;
                                    ~~~~~~

增加列级完整性约束条件

ALTER TABLE SC ADD 列级完整性约束条件;

eg.向TAB1里增加Grade列必须取唯一值的条件：（Grade必须要已经存在）

ALTER TABLE TAB1 ADD UNIQUE(Grade);

增加表级完整性约束条件

ALTER TABLE SC ADD 表级完整性约束条件;

eg.向TAB1里增加Ch为外码的条件，参照表是TAB2：

ALTER TABLE TAB1 ADD FOREIGN KEY(Ch) REFERENCES TAB2(Ch);

删除列

ALTER TABLE 表名 DROP CASCADE;

级联：引用了该列的其他对象（例如视图）一起删除

ALTER TABLE 表名 DROP RESTRICT;

限制：若该列被其他对象引用，则拒绝删除

删除指定的完整性约束条件

ALTER TABLE 表名 DROP CONSTRAINT 完整性约束名 CASCADE/RESTRICT;

修改列

ALTER TABLE 表名 ALTER COLUMN 列名 数据类型;

eg.把Grade由INT型修改成字符型：

ALTER TABLE 表名 ALTER COLUMN 列名 数据类型；

删除基本表

DROP TABLE 表名 CASCADE;

级联：把表相关的依赖对象（比如视图）一并删除

DROP TABLE 表名 RESTRICT;

限制：如果此表被其他表依赖（外码）或者有视图等，拒绝被删除

索引

建立索引

建立唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名1 次序,列名n 次序);

唯一索引 是关于数据值是否唯一的，它强制数据的唯一性，并帮助快速查找特定值

eg.为表TAB1按照学号升序和时间降序建立唯一的索引：

CREATE UNIQUE INDEX NewIndex ON TAB1(Nu ASC,Time DESC);

ASC：asceding，升序 DESC：descending，降序

建立聚簇索引

CREATE CLUSTER INDEX 索引名 ON 表名(列名1 次序,列名n 次序);

聚簇索引 是关于数据如何物理存储的，它把数据和索引紧密地绑定在一起，让查找和范围查询非常快

重命名索引

ALTER INDEX 旧索引名 RENAME TO 新索引名;

eg.把TAB1表的NewIndex索引重命名为OldIndex：

ALTER INDEX NewIndex RENAME TO OldIndex;

删除索引

DROP INDEX 索引名;

查询数据–SELECT语句

一般格式

SELECT ALL/DISTINCT 目标列表达式

FROM 表名/视图名

WHERE 条件表达式

GROUP BY 列名 HAVING 条件表达式

ORDER BY 列名 次序;

SELECT 目标列表达式

查询指定的列

SELECT 列名1，列名n

eg.查询TAB1的Ch列和Nu列：

SELECT Ch,Nu
FROM TAB1;

查询全部的列

SELECT *

eg.查询TAB1的全部记录：

SELECT *
FROM TAB1;

查询计算后的值

SELECT 表达式

表达式可以是算术表达式（+、-…)，字符串常量，函数等等

eg.查询TAB1里面所有Grade减去2的值：

SELECT Grade-2
FROM TAB1;

改变查询结果的列标题

SELECT 列名 别名

eg.查询TAB1里面的Ch列和Nu列，在结果里面使用Chinese和Num显示

SELECT Ch Chinese,Nu Number
FROM TAB1;

去除查询结果的重复行

SELECT DISTINCT 列名

如果没有使用DISTINCT，默认为ALL

聚集函数

聚集函数只处理非空值

聚集函数只能用于SELECT语句和CROUP BY中的HAVING子句

统计元组个数

COUNT(*)

某个元组的一个或者部分取空值的时候，不影响统计结果

eg.查询TAB1里面的总数：

SELECT COUNT(*)
FROM TAB1;

统计某一列值的个数

COUNT(DISTINCT/ALL 列名)

指定DISTINCT会去重，NULL不计入总数

计算某一列值的平均数（该列必须为数值型）

AVG(DISTINCT/ALL 列名)

eg.查询TAB1里面的Grade平均值：

SELECT AVG(ALL Grade)
FROM TAB1;

计算某一列值的总和（该列必须为数值型）

SUM(DISTINCT/ALL 列名)

计算某一列值的最大值/最小值

MAX/MIN(DISTINCT/ALL 列名)

WHERE 条件表达式

比较大小

WHERE 列名 运算式

常用运算符：= > < >= <= !=/<> !> !«/mark>

eg.查询TAB1里面所有三年级（Grade=3）的学生的姓名：

SELECT Ch
FROM TAB1
WHERE Grade=3;

eg.查询TAB1里面所有学号2300开头学生（Nu>23000）的学生的年级：

SELECT Grade
FROM TAB1
WHERE Nu>23000;

确定范围

WHERE 列名 BETWEEN 最小值 AND 最大值

WHERE 列名 NOT BETWEEN 最小值 AND 最大值

eg.查询TAB1里面年级在1到3之间的学生的姓名：

SELECT Ch
FROM TAB1
WHERE Grade BETWEEN 1 AND 3;

eg.查询学号不在23000到24000之间的学生的姓名：

SELECT Ch
FROM TAB1
WHERE NOT Nu BETWEEN 23000 AND 24000;

确定集合

WHERE 列名 IN (‘列值1’,‘列值n’)

WHERE 列名 NOT IN (‘列值1’,‘列值n’)

eg.查询学号不是23001也不是23002的学生年级：

SELECT Grade
FROM TAB1
WHERE Nu NOT IN ('23001','23002');

相当于多重条件查询的=语句

字符匹配

百分号 % 表示任意长度的字符串(类似正则.*)，比如a%b就是以a开头，b结尾的任意长度字符串

下划线 _ 表示单个字符，比如a_ _ _b（不用空格，这里方便看）是以a开头b结尾的长度为5的字符串

在ASCII码表里，一个汉字长度为2，需要两个下划线

反斜杠 \ 表示转义，跟在 % 和 _ 前面（\% 、\_）让他们变成普通字符而非通配符

使用 ESCAPE ‘符号’ 设置转义字符，但一般使用反斜杠

根据环境决定要不要加上ESCAPE（有的数据库默认由\转义）

WHERE 列名 LIKE ‘字符串’ ESCAPE ‘\’

WHERE 列名 NOT LIKE ‘字符串’ ESCAPE ‘\’

eg.查询TAB1中姓名满足a%i_e的学生的年级：

SELECT Grade
FROM TAB1
WHERE Ch LIKE 'a%i_e';

空值查询

WHERE 列名 IS NULL

WHERE 列名 IS NOT NULL

eg.查询TAB1中缺少学号的学生的年级：

SELECT Grade
FROM TAB1
WHERE Nu IS NULL;

多重条件查询

WHERE 条件表达式1 AND 条件表达式2

WHERE 条件表达式1 OR 条件表达式2

可以把AND和OR组合使用，其中AND优先级大于OR

GROUP BY 列名 HAVING 条件表达式

用于将查询结果按某一列或多列的值分组，值相等的为一组 目的是细化聚集函数的作用对象，分组后聚集函数将作用于每一个组，每一组都有一个函数值

GROUP BY 列名

eg.求TAB1表里面各个年级和对应的人数：

SELECT Grade,COUNT(Ch)
FROM TAB1
GROUP BY Grade;

以Grade分组，在每一组中求取Ch的数量

GROUP BY 列名 HAVING 筛选条件

HAVING用于从组中选择满足条件的组 WHERE用于从基本表或视图中选择满足条件的元组（注意：WHERE子句不可以接聚集函数）

eg.求TAB1表里面各个年级和对应的人数：

SELECT Grade,COUNT(Ch)
FROM .TAB1
GROUP BY Grade
HAVING GRADE>=2;

以Grade分组，在每一组中求取Ch的数量

ORDER BY 次序

ORDER BY 列名1 列名n ASC

ORDER BY 列名1 列名n DESC

如果不设置，默认升序（ASC）

eg.查询TAB1中学生的年级，按照降序排列：

SELECT Grade
FROM TAB1
ORDER BY GRADE DESC;

连接查询

两表连接查询

WHERE 表名1.列名1 比较运算符 表名2.列名2

当列名在参与连接的各表中唯一时，可省去表名前缀

eg.查询TAB1和TAB2中所有数据，并在一个查询结果里面展示

SELECT STUDY.TAB1.*,STUDY.TAB2.*
FROM STUDY.TAB1,STUDY.TAB2
WHERE TAB1.Nu=TAB2.Nu;

若想获得自然连接，则列举全部属性列，并去掉一个相同的属性列即可。可以将上述SELECT语句改写如下：

SELECT Ch,Grade,Cla,Hom,STUDY.TAB2.Nu  -- 去掉了其中一个Nu
FROM STUDY.TAB1,STUDY.TAB2
WHERE TAB1.Nu=TAB2.Nu;

eg.在TAB1和TAB2里查询选了英语课，并且是三年级的学生的学号:

SELECT STUDY.TAB1.Nu 
FROM STUDY.TAB1,STUDY."tab2" 
WHERE CLA='ENGLISH' AND GRADE=3;

单表连接查询

通过取两个别名，对同一个表进行自连接

eg.查询cla和cla2相同的学生学号：

SELECT FIRST.*,SECOND.*
FROM STUDY.TAB2 FIRST,STUDY.tab2 SECOND 
WHERE FIRST.CLA=SECOND.CLA2;

外连接查询

左外连接保留左表的所有记录，并尽可能地匹配右表中的记录 右外连接保留右表的所有记录，并尽可能地匹配左表中的记录

将悬浮元组保留在结果关系中，没有属性值的位置填上NULL

SELECT 列名 FROM 表名1 LEFT OUTER JOIN 表名2 ON(连接条件)

SELECT 列名 FROM 表名1 RIGHT OUTER JOIN 表名2 ON(连接条件)

eg.以TAB1为主体，列出每个学生选课cla的结果

SELECT STUDY.TAB1.Ch,CLA
FROM STUDY.TAB1 LEFT OUTER JOIN STUDY.TAB2 ON(STUDY.TAB1.Nu=STUDY.TAB2.Nu);

此时会保留TAB1里面的所有记录，匹配对应的TAB2记录

多表连接查询

WHERE 表名1.列名1 = 表名2.列名2 AND 表名2.列名2 = 表名3.列名3

多表连接一般是先进行两个表的连接操作，再将其连接结果与第三个表执行连接

嵌套查询

查询块：SELECT-FROM-WHERE 嵌套查询：将一个查询块嵌套在另一个查询块的WHERE子句或者HAVING子句 上层的查询块称为外层查询/父查询；下层的查询块称为内层查询/子查询 子查询的SELECT语句中不能使用ORDERBY子句，ORDERBY子句只能对最终查询结果排序

集合判断IN子查询

WHERE 列名 IN (子查询)

eg.查询alice的年级：

SELECT GRADE
FROM STUDY.TAB1
WHERE CH='alice';

查询结果为alice在三年级，再查找三年级的其他学生：

SELECT CH,NU
FROM STUDY.TAB1
WHERE GRADE=3;

上面两个查询结合为嵌套查询：

SELECT CH,NU
FROM STUDY.TAB1
WHERE GRADE IN
	(SELECT GRADE
     FROM STUDY.TAB1
     WHERE CH='alice'
    );

本例的子查询条件不依赖于父查询，这类子查询称为不相关子查询

比较运算符子查询

WHERE 列名 比较运算符 (子查询)

当明确知道子查询结果是单个值而不是集合的时候使用

eg．在SC表中，找出每个学生（Sno）超过他自己选修课程平均成绩（Grade）的课程号（Cno)

SELECT Sno, Cno
FROM SC x    -- x是表 SC 的别名，又称为元组变量，可以用来表示 SC 的一个元组
WHERE Grade >=
	(SELECT AVG(Grade)
	FROM SC y
	WHERE y.Sno=x.Sno);

这里必须加上WHERE y.Sno=x.Sno这个条件，此时内外对应的sno才会相同，否则求的不是单个学生的平均成绩，而是所有学生的平均成绩

本例的子查询条件依赖于父查询，这类子查询称为相关子查询，整个查询称为相关嵌套查询

ANY/ALL子查询

WHERE 列名 比较运算符 ANY/ALL (子查询)

eg.查询TAB1里面三年级学生学号大于23000的：

SELECT Ch
FROM STUDY.TAB1
WHERE Nu>=ANY
	(SELECT Nu
	 FROM STUDY."tab1" 
	 WHERE Grade=3
	);

EXISTS子查询

EXISTS代表存在量词，对应的为NOT EXISTS

EXISTS谓词的子查询不返回数据，只返回逻辑’true’和’false’

eg1.在SC表中查询至少选修了1号学生选修的全部课程（Cno）的学生的学号（Sno)

查询学号为 x 的学生，对所有的课程 y，只要 1 号学生选修了课程 y，则 x 也选修了 y。

令 p 表示"学生 1 号选修了课程 y"

令 q 表示"学生 x 选修了课程 y"

则上述查询可以表示为(∀y)p→q

通过等价转换，可得(∀y)p→q ≡ ¬(∃y(¬(p→q))) ≡ ¬(∃y(¬(¬p∨q))) ≡ ¬∃y(p∧¬q)

最终用 SQL 实现的表达式 ¬∃y(p∧¬q)，语义：不存在这样的课程 y，学生 1 号选修了 y，而学生 x 没有选修

SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
    (SELECT *                            -- 由EXISTS引I出的子查询，其目标列表达式通常都用*
     FROM SC SCY
     WHERE SCY.Sno='1' AND NOT EXISTS
         (SELECT *
          FROM SC SCZ
          WHERE SCZ.Sno = SCX.Sno AND SCZ.Cno = SCY.Cno)   -- 保证内外指向相同的学生
    );

eg2.基于 SC 表，查询选修了全部课程（Course 表）的学生姓名（Student 表）

令 p 表示"课程 x 被学生 y 选修了"，则有(∀x)p ≡ ¬(∃x(¬p))，语义：查询没有任何课程是其不选修的学生 y

SELECT Sname
FROM Student
WHERE NOT EXISTS
    (SELECT *
     FROM Course
     WHERE NOT EXISTS
         (SELECT *
          FROM SC
          WHERE Sno=Student.Sno AND Cno=Course.Cno)
    );

集合查询

多个SELECT语句的结果可以进行集合的并（UNION)、交（INTERSECT)、差（EXCEPT） 参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同

UNION并操作（满足前者或满足后者）

UNION合并查询结果时，系统会自动去掉重复元组，若需保留，则采用UNIONALL

eg.在TAB2中查询学号大于等于23003的学生和选择了MATH科目的学生

SELECT *
FROM STUDY.TAB2
WHERE Nu>=23003
UNION
SELECT *
FROM STUDY.TAB2
WHERE CLA='MATH' OR CLA2='MATH';

INTERSECT交操作（前后都满足）

eg.在TAB2中查询选了MATH又选了history的学生

SELECT *
FROM STUDY.TAB2
WHERE CLA='history' OR CLA2='history'
INTERSECT
SELECT *
FROM STUDY.TAB2
WHERE CLA='MATH' OR CLA2='MATH';

EXCEPT差操作（满足前者，不满足后者）

eg.在TAB2中查询学号大于23002的学生和选修了MATH的学生的差集

SELECT *
FROM STUDY.TAB2
WHERE Nu>23002
EXCEPT
SELECT *
FROM STUDY.TAB2
WHERE CLA='MATH' OR CLA2='MATH';

基于派生表的查询

子查询出现在FROM子句时，子查询将生成临时的派生表，成为主查询的查询对象

FROM (子查询) AS 别名 (属性列名1,属性列名2)

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其默认属性 AS可以省略，但必须为派生表关系指定一个别名

eg1.找出每个学生超过他自己选修课程平均成绩的课程号

SELECT Sno, Cno
FROM SC, 
	(SELECT Sno, Avg(Grade) 
     FROM SC 
     GROUP BY Sno
    ) 
AS Avg_sc(avg_sno, avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno AND SC.Grade> =Avg_sc.avg_grade;

eg2.查询所有选修了1号课程的学生姓名

SELECT Sname
FROM Student, 
	(SELECT Sno 
     FROM SC 
     WHERE Cno='1'
    )
AS SC1
WHERE Student.Sno=SC1.Sno;

插入数据–INSERT语句

插入元组

一般格式

INSERT

INTO 表名(列名1,列名n)

VALUES(常量1,常量n); //字符串常量要用单引号’‘括起来

假设现在有TAB1表，有C1到C4四列，其中C4列是字符串常量：

情况1：明确给出新增元组要在哪些属性上赋值（插入数据包含全部属性列）

INSERT
INTO TAB1(C1,C2,C3,C4)
VALUES(1,2,3,'4');

情况2：明确给出新增元组要在哪些属性上赋值（插入数据只包含部分属性列）

INSERT
INTO TAB1(C1,C2,C3)
VALUES(1,2,3)

这种情况下C4列会被赋值NULL，如果C4有约束条件NOT NULL则会报错

情况3：仅指出要在TAB1表上插入元组（插入数据包含全部属性列）

INSERT
INTO TAB1
VALUES(1,2,3'4');

这种情况表示要在全部属性列上赋值，插入数据顺序必须和列的顺序对应

情况4：仅指出要在TAB1表上插入元组（插入数据只包含部分属性列）

INSERT
INTO TAB1
VALUES(1,2,3,NULL);

这种情况必须明确未赋值的属性列为NULL

插入子查询结果

一般格式

INSERT

INTO TAB1(属性列1,属性列2)

子查询;

eg.假设现有TAB1表（如上），并按C1列分组求C2列的平均值，并存入TAB2表（其中TAB2表的C1列存放 C1，avg_C2列存放C2列的均值)

INSERT
INTO TAB2 (C1, avg_C2)
SELECT C1, AVG(C2)
FROMTAB1
GROUP BY C1;

修改数据–UPDATE语句

一般格式

UPDATE 表名

==SET 列名1=表达式1,列名n=表达式n

WHERE 条件;==

WHERE语句若省略，则表示修改表中所有元组

情况1：修改某一个元组的值

UPDATE TAB1
SET C4='0'
WHERE C1=1;

情况2：修改多个元组的值

UPDATE TAB1
SET C3=C3+1;

情况3：带子查询的修改语句

UPDATE TAB1
SET C4='0'
WHERE C1 IN
	(SELECT C1
     FROM TAB2
     WHERE avg_C2=2
    );

删除语句–DELETE语句

一般格式

DELETE

FROM 表名

WHERE 条件;

WHERE语句若省略，则表示删除表中所有元组

情况1：删除某一个元组的值

DELETE 
FROM TAB1
WHERE C1=1;

情况2：修改多个元组的值

DELETE
FROM TAB1

情况3：带子查询的修改语句

DELETE 
FROM TAB1
WHERE C1 IN
	(SELECT C1
     FROM TAB2
     WHERE avg_C2=2
    );

VIEW 视图

建立视图

一般格式

CREATE VIEW 视图名 (列名1,列名n)

AS 子查询

WITH CHECK OPTION;

若省略视图名后的列名，则该视图由子查询中SELECT的目标列字段组成

若添加WITH句，则表示对视图进行增删改时要满足子查询中的条件表达式

在以下情况中必须明确指定组成视图的列名： 1.某个目标列不是单纯的列名，而是聚集函数或列表达式 2.多表连接时选出了几个同名列作为视图的字段 3.需要在视图中为某个列启用新的更合适的名字

行列子集视图：由单个基本表导出，仅去掉了基本表的某些行和某些列，但保留了主码

若某些视图是建立在另一个表的全部属性列上的（视图与基本表的各列是一一对应的）那么当修改基本表的结构时，基本表和视图的映像关系会被破坏。这种情况最好在修改基本表后删除该视图，然后重建该视图

情况1：建立完全视图

eg1.建立C1为1时TAB1的视图

CREATE VIEW V_TAB1
AS
SELECT C1,C2,C3,C4
FROM TAB1
WHERE C1=1;

情况2：建立带有增删改条件的视图

eg2.建立C4为4时TAB1的视图，并且以后每次增删改时都要满足C4=4

CREATE VIEW V_TAB2
AS
SELECT C1,C2,C3,C4
FROM TAB1
WHERE C4='4'
WITH CHECK OPTION;

情况3：由视图新建视图

eg3.建立在一个或多个已定义的视图上

CREATE VIEW V_TAB3
AS
SELECT C1,C2,C3
FROM V_TAB1
WHERE C2=2;

情况4：带有派生数据的视图

减少冗余数据，定义基本表时一般只存放基本数据。当需要使用计算得出的派生数据时，可以设置在视图 中的派生属性列上，也称为虚拟列。带虚拟列的视图也称为带表达式的视图

eg4.建立有派生数据的视图

CREATE VIEW V_TAB4(C1,new_C2)
AS
SELECT C1,10+C2
FROM TAB1;

情况5：带有聚集函数和GROUP BY的分组视图

eg5.建立包含聚集函数的分组视图

CREATE VIEW V_TAB5(C1,avg_C2)
AS
SELECT C1,AVG(C2)
FROM TAB1;
GROUP BY C1;

删除视图

DROP VIEW 视图名 CASCADE;

如果使用了CASCADE级联删除语句，则将把该视图导出的所有视图一并删除

查询和更新视图

视图定义后，对视图进行查询和更新的语句和语法与基本表相同 视图的查询与更新最终都会转换为对基本表的查询和更新，这一过程也被称为视图消解 一般来说，行列子集视图的查询和更新都可以顺利转换，其他则不一定

空值

判断属性为空值

这部分和WHERE语句里面空值判断一样啦

属性 IS NULL;

属性 IS NOT NULL;

eg.查找TAB1中名字为空的学生：

SELECT *
FROM STUDY.TAB1
WHERE Ch IS NULL;

空值的运算

算数运算

空值与另一个值的算术运算结果为空值

SELECT 5 + NULL; -- 结果为 NULL
SELECT 10 / NULL; -- 结果为 NULL

比较运算

空值与另一个值的比较运算结果为 UNKNOWN

SELECT 5 = NULL; -- 结果为 UNKNOWN
SELECT 5 <> NULL; -- 结果为 UNKNOWN
SELECT 5 > NULL; -- 结果为 UNKNOWN

在查询语句中的处理

在查询语句中，只有使WHERE和HAVING子句的选择条件为TRUE的元组才会被选出作为输出结果（即不包括UNKNOWN的情况)

eg1:

SELECT * 
FROM employees 
WHERE salary > 50000 AND commission IS NOT NULL;

这个查询会返回所有工资大于 50000 且佣金不为 NULL 的员工记录

eg2:

SELECT * 
FROM employees 
WHERE commission <> 0; 

查询过程中commission可能为NULL，这部分运算后产生UNKNOWN，对应元组会被忽略

要让所有都能被输出，最好做如下改动：

SELECT *
FROM employees
WHERE commission <> 0 OR commission IS NULL;

数据库安全

授权

授予用户权限

GRANT 权限 ON 对象类型 对象名 TO 用户名 [WITH GRANT OPTION];

权限：查询权限 SELECT，全部操作权限 ALL PRIVILEGES

对象类型：TABLE/VIEW

对象名：表和视图的名称

用户名：可以指定用户，也可以全体用户PUBLIC

如果没有WITH GRANT OPTION语句，那么用户不能传播这个权限

SQL不允许循环授权，被授权者不能把权限传递给授权者或其祖先

eg.假设我们有一个名为employees的表，现在想让用户user_A只能查询这张表

GRANT SELECT ON TABLE employees TO user_A;

如果要让A还能把权限授权给别人：

GRANT SELECT ON TABLE employees TO user_A WITH GRANT OPTION;

收回用户权限

REVOKE 权限 ON 对象类型 对象名 FROM 用户名 [CASCADE/RESTRICT];

CASCADE：级联回收，把用户传播出去的权限一并收回

RESTRICT：受限回收，如果用户传播过该权限，回收会失败（默认行为）

eg.收回user_A的权限

REVOKE SELECT ON TABLE employees FROM user_A;

创建数据库模式的权限

对创建数据库模式一类的数据库对象的授权在数据库管理员创建用户的时候实现

CREATE USER 用户名 [WITH DBA|RESOURCE|CONNECT];

DBA：可以创建新用户、模式、表、视图等，还可以把这些权限授予其他用户

RESOURCE：可以创建表、视图，但是不能创建新的模式和用户

CONNECT：只能登录数据库，或者被授予权限后操作

数据库角色

角色是权限的集合，可以为一组相同权限的用户创建同一个角色，使用角色管理权限，简化授权过程

创建角色

CREATE ROLE 角色名;

给角色添加角色/用户

GRANT 角色 TO 某角色/某用户 [WITH ADMIN OPTION];

给角色授权

GRANT 权限 ON 对象类型 对象名 TO 角色；

收回角色权限

REVOKE 权限 ON 对象类型 对象名 FROM 角色;