关系数据库标准语言SQL

数据定义

一个实例可以建立多个数据库，一个数据库可以建立多个模式，一个模式通常包括多个表、视图、索引等数据库对象。

注：这里的模式就是我们常说的数据库database；而数据库指的是常说的目录。

模式的定义与删除

schema/ˈskiːmə/ 模式
authorization /ˌɔːθərəˈzeɪʃ(ə)n/ 授权书

定义模式
create schema <模式名> authorization <用户名>;
create schema <模式名> authorization <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]

删除模式
drop schema <模式名><cascade|restrict>;
cascade 级联：删除模式的同时，删除模式中所有数据库对象
restrict 限制：如果该模式中已经定义了下属数据库对象，则拒绝删除语句的执行
1
2
3
4
5
6
7
8

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

定义基本表

create table <表名> (<列名><数据类型>[列级完整性约束条件]
					,<列名><数据类型>[列级完整性约束条件]
					...);
列级完整性约束条件:
	primary key:主码
	not null：不为空
	...
数据类型(参见数据库系统概论83页)
	char(n)
	varchar(n)
	clob
	blob
	int
	smallint
	bigint
	numeric(p,d)|decimal(p,d)|dec(p,d) : p位数字，其中小数d位
	real: 单精度浮点
	double precision: 双精度
	float(n):可选精度浮点数
	boolean
	date
	time
	timestamp: 时间戳
	interval: 时间间隔
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

模式与表？？？

show seach_path;显示当前搜索路径
set serach_path to "S-T"，public;设置搜索路径
1
2

修改基本表

alter table <表名>
[add [column] <新列名><数据类型>[完整性约束]] #添加新的列
[add <表级完整性约束>]						#添加新的表级完整性约束
[drop [column] <列名> [cascade|restrict]]	#删除列
[drop constraint <完整性约束名> [restrict|cascade]] #删除指定的完整性约束
[alter column <列名><数据类型>];				#修改原有的列定义（列名，数据类型）
1
2
3
4
5
6

删除基本表

drop table <表名> [restrict|cascade];
restrict:欲删除的基本表不能被其他表的约束所引用，不能有视图、触发器、储存过程或函数
cascade:相关依赖对象都被删除
1
2
3

不同数据库在遵循SQL标准的基础上具体实现细节和处理策略会不同

索引的建立和删除

当表数据量较大时，查询操作会比较耗时。建立索引是加快查询速度的有效手段。
常见的索引包括：顺序文件索引、B+树索引、散列索引、位图索引。

用户不必也不能显示地选择索引。

建立索引

create [unique][cluster] index<索引名> 
	on <表名>(<列名>[<次序>],<列名>[<次序>],...);

次序：desc(降序),asc(升序)
unique：表名此索引的每一个索引只对应唯一的数据记录
clustar：表示要建立的索引为聚簇索引
1
2
3
4
5
6

修改索引

重命名

alter index <旧索引名> rename to <新索引名>
1

删除索引

drop index <索引名>
1

视图

视图创建

create view <视图名> [(<列名>,<列名>,...)]
as <子查询>
[with check option];
1
2
3

with check option 表示对视图进行update，insert，delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件。

如果省略视图各属性列名，则由子查询中select子句目标列中的诸字段组成。

create view BT_S(Sno,Gavg)
AS
SELECT Sno,AVG(garde)
FROM SC
GROUP BY Sno;
1
2
3
4
5

删除视图

Drop view <视图名> [cascade];
1

查询视图

将视图作为表使用即可

更新视图

视图一般不用做更新

数据查询

一般格式

select [all|distinct] <目标列表达式> [,<目标列表达式>]...
from <表名或视图名>[,<表名或视图名>] | (<select语句>[as]<别名>)
[where <条件表达式>]
[group by <列名1> [having<条件表达式>]]
[order by <列名2> [asc|desc]];
1
2
3
4
5

group by：该属性列值相等的元组为一个组。
若group by子句带有having：只有满足指定条件的组才予以输出。

通过指定别名来改变查询结果的列标题

select Sname name,'Year of Birth' birth,2014-sage birthday,
from student;
1
2

Sname 列按别名name显示查找出的列标题

其他操作

消除重复行

select distinct 表名 from ...
1

如果没有distinct，则默认为all

条件语句

=,<,>,<=,>=,!=,<>,!<,!>,not + 上述比较符

between and , not between and
in , not in
like, not like
is null,is not null
and, or , not

like字符匹配

[not] like '<匹配串>' [escape '<换码字符>']
1

匹配串可含有通配符
% ：任意长度的字符串
_ ：任意单个字符

如_阳%;
若字符串本身含有%，_，则需要转义
如'DB \_Design' escape '\';，escape ‘\’表示“\”为换码字符

空值非空查询

is null
is not null
1
2

多重条件查询

and or in

排序输出

ASC 、 DESC

order by Grade DESC
1

聚集函数

count(*)						#统计元组个数
count([distinct|all] <列名>) 	#统计一列中值的个数
sum([distinct|all] <列名>)
avg([distinct|all] <列名>)
max([distinct|all] <列名>)
min([distinct|all] <列名>)
1
2
3
4
5
6

distinct表示计算时取消指定列的重复值

当聚集函数遇到空值时，除count(*)外，都跳过空值。

使用：

select count(*) from student;
select count(distinct Sno) from sc;
select avg(grade) from sc where cno = '1';
1
2
3

聚集函数只能用于select, group by , having.

group by

分组后，聚集函数将作用于每一个组，即每一个组都有一个函数值。

select Cno,count(sno)
from sc
group by Cno;

select sno
from sc
group by sno
having count(*)>3;

1
2
3
4
5
6
7
8
9

数据库安全性

SQL中使用GRANT和REVOKE语句向用户授权或收回对数据的操作权限。

授权

GRANT <权限>,<权限>,...
ON <对象类型><对象名>,[<对象类型><对象名>]...
TO <用户>,<用户>,...
[WITH GRANT OPTION];
1
2
3
4

权限包括

创建权限：
	CREATE SCHEMA
	CREATE TABLE,ALTER TABLE
	CREATE VIEW
	CREATE INDEX
数据权限：
	SELECT,INSERT,UPDATE,DELETE,
	REFERENCES,
	ALL PRIVILEGES   #全部操作权限
1
2
3
4
5
6
7
8
9

使用案例：

GRANT UPDATE(Sno),SELECT           #Sno为列名
ON TABLE SC
TO User4                          #授予所有人用 PUBLIC
WITH GRANT OPTION;
1
2
3
4

收回权限

REVOKE <权限>,...
ON  <对象类型><对象名>,[<对象类型><对象名>]...
FROM <用户>,<用户>...
[CASCADE|RESTRICT];
1
2
3
4

CASCADE为连锁删除
RESTRICT为约束删除，即没有对本列的任何引用时才能删除。

数据库对象的授权

CREATE USER <username> [WITH][DBA|RESOURCE|CONNECT];
1

三种权限：

CONNECT(默认)：不能创建模式、新用户、基本表，只能登陆数据库，由数据库管理员或其他用户授予他应有的权力。

RESOURCE:能创建基本表和视图，成为所创建对象的属主，但不能创建模式，不能创建新用户。数据库的属主可以授权该对象。

DBA：超级用户，拥有所有数据库对象的存储权限。
1
2
3
4
5

数据库角色

角色是权限的集合

角色的创建

create role <角色名>
1

给角色授权

grant <权限>,<权限>...
ON <对象类型>对象名
TO <角色>,<角色>...
1
2
3

将角色授予其他角色或用户

GRANT <角色1>,<角色2>...
TO <角色3>,<用户1>...
[WITH ADMIN OPTION];
1
2
3

[WITH ADMIN OPTION]可将这个权限授予其他人。

角色权限收回

REVOKE <权限>,<权限>...
ON <对象类型><对象名>
FROM <角色>,<角色>...
1
2
3

审计

把用户对数据库的所有操作自动记录下来，放入审计日志。

设计审计功能:对修改表结构或修改表数据的操作进行审计。

audit alter,update
on SC;
1
2

取消审计

NOAUDIT ALTER,UPDATE
ON SC;
1
2

数据库完整性

实体完整性

主码值是否唯一
主码各属性是否为空

主码定义

create table SC 
(Sno CHAR(9) NOT NULL,
 Cno CHAR(4) NOT NULL,
 Grade SMALLINT,
 PRIMARY KEY (Sno,Cno)    /*多个属性构成的码，只能在表级定义主码*/
);
1
2
3
4
5
6

参照完整性

用Foreign Key定义外码，需要保证参照表的值必须在被参照表中可以找到。
当发生不一致时，系统可以采取以下策略加以处理：
（1）拒绝（NO ACTION）(默认操作)
（2）级联（Cascade）:删除或修改参照表中所有导致不一致的元组
（3）设置为空值

create table <表名>
(	Sno char(9),
	Cno char(4),
	Grade SmallInt,
	Primary Key(Sno,Cno),/*实体完整性*/
	Foreign Key(Sno) References <被参照表1> /*参照完整性*/
		On Delete Cascade /*删除被参照表元组时，级联删除参照表元组*/
		On Update Cascade /*更新被参照表元组时，级联更新参照表元组*/
	Foreign Key(Cno) References <被参照表2>
		On Delete No Action /*默认，当造成不一致时，拒绝删除*/
		On Update Cascade
);
1
2
3
4
5
6
7
8
9
10
11
12

用户定义的完整性

属性上的约束

不满足条件则拒绝执行
有：
（1）不允许取空值-----NOT NULL
（2）列值唯一-----------UNIQUE
（3）用CHECK短语指定列值应该满足的条件

create table <表名>
(	Sno Char(9) Unique NOT NULL, 
	Cno Char(4) NOT NULL,
	Ssex Char(2) Check(Ssex IN ('男','女'))/*性别只允许男女*/
);
1
2
3
4
5

元组上的约束

可以用check语句定义元组上的约束条件

create table <表名>
(	Sno Char(9) Unique NOT NULL, 
	Cno Char(4) NOT NULL,
	Ssex Char(2) Check(Ssex IN ('男','女')),

	Primary Key(Sno),
	Check (Ssex='女' AND Sno Like 'Ms.%') /*元组约束*/
);
1
2
3
4
5
6
7
8

完整性约束命名子句（Constraint 约束）

命名子句

Constraint <完整性约束条件名> <完整性约束条件>
1

create table <表名>
(	Sno Numeric(6)
		Constraint C1 Check(Sno Between 90000 AND 99999),
	Sname Char(20)
		Constraint C2 Not NULL,
	Sage Numeric(3)
		Constraint C3 Check (Sage <30),
	Ssex Char(2)
		Constraint C4 Check (Ssex In('男','女'),
		Constraint StudentKey Primary Key(Sno)
);
1
2
3
4
5
6
7
8
9
10
11

修改子句

删除约束

Alter Table <表名>
	Drop Constraint C4;
1
2

添加新的约束

Alter Table <表名>
	Add constraint C1 check (Sno Between 900000 AND 999999);
1
2

域中的完整性限制 DOMAIN（类似于角色）

创建域

create Domain GenderDomain char(2)
	Constraint GD check (Value In('男','女'));

这样在定义Ssex时使用
Ssex GenderDomin 即可达到相同效果。
1
2
3
4
5

删除域限制条件

Alter Domain GenderDomin
	Drop Constraint GD;
1
2

增加性别限制条件

Alter Domain GenderDomin
	Add constraint GDD Check (Value In ('1','0'));
1
2

断言

使用Create Assertion来指定更具一般性的约束。
任何断言不为真值都会被拒绝

断言格式

Create Assertion <断言名> <Check 子句>
1

限制每一门课程最多60名学生选秀

create Assertion <断言名>
	Check(60 >= ALL(Select count(*)
					From SC
					Group by cno )
		  );
1
2
3
4
5

删除断言

Drop Assertion <断言名>;
1

触发器(Trigger)

只有表的拥有者，才可以在表上创建触发器.
触发器会影响系统性能，需要谨慎使用。

格式

Create Trigger <触发器名>
{Before|After} <触发事件> ON <表名>
Referencing new|old Row AS <变量>
For Each{Row|Statement}
[When <触发条件>] <触发动作体>
1
2
3
4
5

<触发时间>:可以是Insert OR Delete,Update of <触发列，...>等事件组合
{Before|After} 是触发的时机
行级触发器：For Each Row
语句级触发器：For Each Statement

举例：

（1）行级触发器，每行的修改都会执行

Create Trigger <触发器名称>
After Update OF <列名> ON <表名>
Referencing    
	OldRow AS <变量1>,   /*行级触发器*/
	NewRow AS <变量2>
For Each Row            /*行级触发器*/
When (条件)     /*可在条件和操作中使用变量1、2*/
	<操作>
1
2
3
4
5
6
7
8

（2）语句级触发器，触发事件Insert语句执行完成后才执行一次的动作。
省略when，表示无条件执行。

Create Trigger <触发器名称>
After Insert ON <表名>
Referencing
	NewTable AS <关系名>  /*语句级触发器*/
For Each Statement             /*语句级触发器*/
	<操作>
1
2
3
4
5
6

（3）触发动作体是一个PL/SQL过程块

Create Trigger <触发器名称>
Before Insert OR Update ON <表名>
Referencing
	New row AS <变量名>  
For Each Row
Begin
	IF (条件1) AND (条件2)
		Than 操作(newtuple.Sal:=4000);
	END IF;
END;
1
2
3
4
5
6
7
8
9
10

激活触发器

一个数据表上多个触发器，执行顺序是：
先Before触发器，执行SQL语句，After触发器。
同类触发器，谁先创建，谁先执行。

删除触发器

Drop Trigger <触发器名> ON <表名>;
1