数据定义
一个实例可以建立多个数据库,一个数据库可以建立多个模式,一个模式通常包括多个表、视图、索引等数据库对象。
注:这里的模式就是我们常说的数据库database;而数据库指的是常说的目录。
模式的定义与删除
schema/ˈskiːmə/ 模式
authorization /ˌɔːθərəˈzeɪʃ(ə)n/ 授权书
定义模式
create schema <模式名> authorization <用户名>;
create schema <模式名> authorization <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]
删除模式
drop schema <模式名><cascade|restrict>;
cascade 级联:删除模式的同时,删除模式中所有数据库对象
restrict 限制:如果该模式中已经定义了下属数据库对象,则拒绝删除语句的执行
基本表的定义、删除、修改
定义基本表
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: 时间间隔
模式与表???
show seach_path;显示当前搜索路径
set serach_path to "S-T",public;设置搜索路径
修改基本表
alter table <表名>
[add [column] <新列名><数据类型>[完整性约束]] #添加新的列
[add <表级完整性约束>] #添加新的表级完整性约束
[drop [column] <列名> [cascade|restrict]] #删除列
[drop constraint <完整性约束名> [restrict|cascade]] #删除指定的完整性约束
[alter column <列名><数据类型>]; #修改原有的列定义(列名,数据类型)
删除基本表
drop table <表名> [restrict|cascade];
restrict:欲删除的基本表不能被其他表的约束所引用,不能有视图、触发器、储存过程或函数
cascade:相关依赖对象都被删除
不同数据库在遵循SQL标准的基础上具体实现细节和处理策略会不同
索引的建立和删除
当表数据量较大时,查询操作会比较耗时。建立索引是加快查询速度的有效手段。
常见的索引包括:顺序文件索引、B+树索引、散列索引、位图索引。
用户不必也不能显示地选择索引。
建立索引
create [unique][cluster] index<索引名>
on <表名>(<列名>[<次序>],<列名>[<次序>],...);
次序:desc(降序),asc(升序)
unique:表名此索引的每一个索引只对应唯一的数据记录
clustar:表示要建立的索引为聚簇索引
修改索引
重命名
alter index <旧索引名> rename to <新索引名>
删除索引
drop index <索引名>
视图
视图创建
create view <视图名> [(<列名>,<列名>,...)]
as <子查询>
[with check option];
with check option
表示对视图进行update
,insert
,delete
操作时要保证更新、插入或删除的行满足视图定义中的谓词条件。
如果省略视图各属性列名,则由子查询中select子句目标列中的诸字段组成。
create view BT_S(Sno,Gavg)
AS
SELECT Sno,AVG(garde)
FROM SC
GROUP BY Sno;
删除视图
Drop view <视图名> [cascade];
查询视图
将视图作为表使用即可
更新视图
视图一般不用做更新
数据查询
一般格式
select [all|distinct] <目标列表达式> [,<目标列表达式>]...
from <表名或视图名>[,<表名或视图名>] | (<select语句>[as]<别名>)
[where <条件表达式>]
[group by <列名1> [having<条件表达式>]]
[order by <列名2> [asc|desc]];
group by:该属性列值相等的元组为一个组。
若group by子句带有having:只有满足指定条件的组才予以输出。
通过指定别名来改变查询结果的列标题
select Sname name,'Year of Birth' birth,2014-sage birthday,
from student;
Sname 列按别名name显示查找出的列标题
其他操作
消除重复行
select distinct 表名 from ...
如果没有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 '<换码字符>']
匹配串可含有通配符
% :任意长度的字符串
_ :任意单个字符
如_阳%
;
若字符串本身含有%,_,则需要转义
如'DB \_Design' escape '\';
,escape ‘\’表示“\”为换码字符
空值非空查询
is null
is not null
多重条件查询
and or in
排序输出
ASC 、 DESC
order by Grade DESC
聚集函数
count(*) #统计元组个数
count([distinct|all] <列名>) #统计一列中值的个数
sum([distinct|all] <列名>)
avg([distinct|all] <列名>)
max([distinct|all] <列名>)
min([distinct|all] <列名>)
distinct表示计算时取消指定列的重复值
当聚集函数遇到空值时,除count(*)外,都跳过空值。
使用:
select count(*) from student;
select count(distinct Sno) from sc;
select avg(grade) from sc where cno = '1';
聚集函数只能用于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;
数据库安全性
SQL中使用GRANT和REVOKE语句向用户授权或收回对数据的操作权限。
授权
GRANT <权限>,<权限>,...
ON <对象类型><对象名>,[<对象类型><对象名>]...
TO <用户>,<用户>,...
[WITH GRANT OPTION];
权限包括
创建权限:
CREATE SCHEMA
CREATE TABLE,ALTER TABLE
CREATE VIEW
CREATE INDEX
数据权限:
SELECT,INSERT,UPDATE,DELETE,
REFERENCES,
ALL PRIVILEGES #全部操作权限
使用案例:
GRANT UPDATE(Sno),SELECT #Sno为列名
ON TABLE SC
TO User4 #授予所有人用 PUBLIC
WITH GRANT OPTION;
收回权限
REVOKE <权限>,...
ON <对象类型><对象名>,[<对象类型><对象名>]...
FROM <用户>,<用户>...
[CASCADE|RESTRICT];
CASCADE
为连锁删除
RESTRICT
为约束删除,即没有对本列的任何引用时才能删除。
数据库对象的授权
CREATE USER <username> [WITH][DBA|RESOURCE|CONNECT];
三种权限:
CONNECT(默认):不能创建模式、新用户、基本表,只能登陆数据库,由数据库管理员或其他用户授予他应有的权力。
RESOURCE:能创建基本表和视图,成为所创建对象的属主,但不能创建模式,不能创建新用户。数据库的属主可以授权该对象。
DBA:超级用户,拥有所有数据库对象的存储权限。
数据库角色
角色是权限的集合
角色的创建
create role <角色名>
给角色授权
grant <权限>,<权限>...
ON <对象类型>对象名
TO <角色>,<角色>...
将角色授予其他角色或用户
GRANT <角色1>,<角色2>...
TO <角色3>,<用户1>...
[WITH ADMIN OPTION];
[WITH ADMIN OPTION]
可将这个权限授予其他人。
角色权限收回
REVOKE <权限>,<权限>...
ON <对象类型><对象名>
FROM <角色>,<角色>...
审计
把用户对数据库的所有操作自动记录下来,放入审计日志。
设计审计功能:对修改表结构或修改表数据的操作进行审计。
audit alter,update
on SC;
取消审计
NOAUDIT ALTER,UPDATE
ON SC;
数据库完整性
实体完整性
主码值是否唯一
主码各属性是否为空
主码定义
create table SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY (Sno,Cno) /*多个属性构成的码,只能在表级定义主码*/
);
参照完整性
用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)不允许取空值-----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 ('男','女'))/*性别只允许男女*/
);
元组上的约束
可以用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.%') /*元组约束*/
);
完整性约束命名子句(Constraint 约束)
命名子句
Constraint <完整性约束条件名> <完整性约束条件>
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)
);
修改子句
删除约束
Alter Table <表名>
Drop Constraint C4;
添加新的约束
Alter Table <表名>
Add constraint C1 check (Sno Between 900000 AND 999999);
域中的完整性限制 DOMAIN(类似于角色)
创建域
create Domain GenderDomain char(2)
Constraint GD check (Value In('男','女'));
这样在定义Ssex时使用
Ssex GenderDomin 即可达到相同效果。
删除域限制条件
Alter Domain GenderDomin
Drop Constraint GD;
增加性别限制条件
Alter Domain GenderDomin
Add constraint GDD Check (Value In ('1','0'));
断言
使用Create Assertion
来指定更具一般性的约束。
任何断言不为真值都会被拒绝
断言格式
Create Assertion <断言名> <Check 子句>
限制每一门课程最多60名学生选秀
create Assertion <断言名>
Check(60 >= ALL(Select count(*)
From SC
Group by cno )
);
删除断言
Drop Assertion <断言名>;
触发器(Trigger)
只有表的拥有者,才可以在表上创建触发器.
触发器会影响系统性能,需要谨慎使用。
格式
Create Trigger <触发器名>
{Before|After} <触发事件> ON <表名>
Referencing new|old Row AS <变量>
For Each{Row|Statement}
[When <触发条件>] <触发动作体>
<触发时间>:可以是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*/
<操作>
(2)语句级触发器,触发事件Insert语句执行完成后才执行一次的动作。
省略when,表示无条件执行。
Create Trigger <触发器名称>
After Insert ON <表名>
Referencing
NewTable AS <关系名> /*语句级触发器*/
For Each Statement /*语句级触发器*/
<操作>
(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;
激活触发器
一个数据表上多个触发器,执行顺序是:
先Before触发器,执行SQL语句,After触发器。
同类触发器,谁先创建,谁先执行。
删除触发器
Drop Trigger <触发器名> ON <表名>;