连接表达式

基本分类

  • 连接成分

    • 包括两个输入关系、连接条件、连接类型
  • 连接条件

    • 决定两个关系中哪些元组相互匹配
  • 连接类型

    • 决定如何处理与连接条件不匹配的元组

连接条件

on条件允许在参与有连接的关系的关系上设置通用谓词,该谓词的写法与where子句谓词类似。

1
select * from s join sc on s.sno = sc.sno

等价于

1
select * from s,sc where s.sno = sc.sno
  • 在表名后面加外连接操作符(*)或(+)指定非主体表

  • 非主体表有一“万能”的虚行,该行全部由空值组成。虚行可以和主体表中所有不满足连接条件的元组进行连接

  • 由于虚行各列全部是空值,因此与虚行连接的结果中,来自非主体表的属性值全部是空值

如:

查询每个学生及其选修课程的情况包括没有选修课程的学生----用外连接操作

1
2
3
SELECT  s.sno,sname,gender,age,dno,cno,score
FROM s,sc
WHERE s.sno = sc.sno(*);

连接类型

  • 内连接(inner join

    • 舍弃不匹配的元组
  • 左外连接(left outer join

    • 内连接+左边失配的元组(缺少的右边关系属性用null)
  • 右外连接(right outer join

    • 内连接+右边失配的元组(缺少的左边关系属性用null)
  • 全外连接(full outer join

    • 内连接 + 左边失配的元组(缺少的右边关系属性用null)+ 右边失配的元组(缺少的左边关系属性用null)

(nature 代表自然连接)

视图

定义视图

1
create view view_name [(列名,列名 …)] as  (查询表达式)

视图的属性名缺省为子查询结果中的属性名,也可以显式指明,在下列情况下,必须指明视图的所有列名

  • 某个目标列是聚集函数或者目标列表达式

  • 多表连接时,选出了几个同名列作为视图的列名

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

  • 目标列是*

1
2
3
4
create  view  DEPTSAL( dno, low, high, agerage, total )
as ( select dno, min(sal), max(sal), avg(sal), sum(sal)
from T
group by dno );

注意:

  • 视图不会要求分配存储空间,视图中也不会包含实际的数据。
  • 视图只保存视图定义,视图中的数据是从基表中获取。
  • 视图中的数据在视图被引用时动态的生成。

删除视图

1
drop  view  view_name

视图作用

  • 定义视图能够简化用户的操作

    • 基于多张表连接形成的视图
    • 基于复杂嵌套查询的视图
    • 包含导出属性的视图
  • 视图机制能使不同用户以不同方式看待同一数据集,适应数据库共享的需要

  • 视图在一定程度上提高了数据的逻辑独立性

    • 由于对视图的更新是有条件的,因此应用程序中修改数据的语句可能仍会因基本表结构的改变而改变。
  • 视图能够对数据提供安全保护,对不同用户定义不同视图,使每个用户只能看到他有权看到的数据

更新视图

由于视图中的数据是从基表中获取,所以对视图的更新会转化为对基本表的更新。

  • 允许对行列子集视图进行更新

    • 从单个基本表导出

      只是去掉了基本表的某些行和某些列

      保留了主码,为行列子集视图

  • 对其他类型视图的更新不同系统有不同限制

    • select子句中的目标列不能包含聚集函数
    • select子句中不能使用unique或distinct关键字
    • 不能包括group by子句
    • 不能包括经算术表达式计算出来的列

忠告:使用视图作为查询对象,尽量减少视图更新

物化视图

物化视图(MATERIALIZED VIEW)即实体化视图,它确实存放有物理数据。物化视图包含定义视图的查询时所选择的基表中的行。对物化视图的查询就是直接从该视图中取出数据,不需要从基本表中获得数据

  • 使用物化视图的目的是为了提高查询性能,是以空间换时间的一种有效手段,更少的物理读/写,更少的cpu时间,更快的响应速度;

  • 物化视图对应用透明;

  • 物化视图需要占用存储空间;

  • 当基表发生变化时,物化视图也应当刷新(所以可能导致数据不一致)。

  • 规模较大的报表适合使用物化视图来提高查询性能。

事务简介

一个事务由查询和更新的语句的序列组成。一个SQL语句开始执行隐含一个事务的开始。以下列语句之一表示结束一个事务:

  • commit [work]:提交当前事务,即将该事务所做的更新在数据库中永久保存。

  • rollback [work]:回滚当前事务,即撤销该事务中所有SQL对数据库的更新,数据库恢复到执行该事务的第一条语句之前的状态。

DDL和DCL与事务无关,而且如果执行了drop就无法回滚

完整性约束2.0

NOT NULL

UNIQUE

unique ( A1, A2, ..., Am)

Unique声明指明下列属性A1, A2, ... ,Am 形成了一个候选码,候选码的属性可以为空(主码的属性不能为空)

如:

1
2
3
4
5
Create table s
(sno char(2) primary key,
pid char(18) not null,
sname varchar(8),
unique(pid));

CHECK

check (P),P是一个谓词。如:

1
2
3
4
5
6
7
8
CREATE   TABLE   S
( sno CHAR4),
sname CHAR8NOT NULL,
age SMALLINT,
gender CHAR1),
constraint pk_s PRIMARY KEY (sno),
CHECK (gender=0OR gender=1’)
)

全局约束

全局约束涉及多个属性间的或多个关系间的联系,如:

1
2
3
4
5
6
7
CREATE TABLE SC
(sno CHAR(4),
cno CHAR(4),
score SMALLINT,
PRIMARY KEY (sno, cno),
CHECK(sno IN (SELECT sno FROM S)),
CHECK(cno IN (SELECT cno FROM C)))

如果S中删除元组,不会触发CHECK子句,只有对SC表的更新和插入才会触发

外码约束(参照完整性)

关系R中的一个属性组,它不是R的码,但它与另一个关系S的码相对应,则称这个属性组为R的外部码,如S关系中的dno属性。如果关系R2的外部码Fk与关系R1的主码Pk相对应,则R2中的每一个元组的Fk值或者等于R1 中某个元组的Pk 值,或者为空值

意义:如果关系R2的某个元组t2参照了关系R1的某个元组t1,则t1必须存在

例如关系Sdno上的取值有两种可能

  • 空值,表示该学生尚未分到任何系中;
  • 非空值,则必须是D关系中某个元组的dno值,表示该学生不可能分到一个不存在的系中

定义形式

SC表中,定义

1
FOREIGN KEY (sno)  REFERENCES S(sno)

snoSC中是外码,在S中是主码,snoSC中取值或为空或为S中某个sno

作为主码的关系称为基本(参照)关系,作为外码的关系称为依赖关系

删除基本关系元组

  • RESTRICT方式(默认)

    • 只有当依赖关系中没有一个外码值与要删除的基本关系的主码值相对应时,才可以删除该元组,否则系统拒绝此删除操作
  • CASCADE方式

    • 将依赖关系中所有外码值与基本关系中要删除的主码值所对应的元组一起删除
  • SET NULL方式

    • 删除基本关系中元组时,将依赖关系中与基本关系中被删主码值相对应的外码值置为空值

如:

1
FOREIGN KEY (sno) REFERENCES S(sno) [ON DELETE [RESTRICT |CASCADE | SET NULL] ]

修改基本关系主码

  • RESTRICT方式(默认)

    • 只有当依赖关系中没有一个外码值与要修改的基本关系的主码值相对应时,才可以修改该元组主码,否则系统拒绝此次修改
  • CASCADE方式

    • 将依赖关系中所有与基本关系中要修改的主码值所对应的外码值一起修改为新值
  • SET NULL方式:

    • 修改基本关系中元组主码时,将依赖关系中与基本关系中被修改主码值相对应的外码值置为空值

如:

1
FOREIGN KEY (sno) REFERENCES S(sno) [ON UPDATE [CASCADE | SET NULL] ]

对约束的命名、撤消和添加

命名

1
CONSTRAINT  约束名 <约束条件>

示例

1
2
3
4
sno CHAR(4) CONSTRAINT S_PK PRIAMRY KEY

age SAMLLINT CONSTRAINT AGE_VAL
CHECK(age >= 15 AND age <= 25)

关系上约束的撤消与添加

撤消用alter …drop…

添加用alter …add…

示例

1
2
alter table S drop constraint S_PK
alter table SC add constraint SC_CHECK check(sno in select sno from S)

授权

  • 权限的转授和回收

    • 允许用户把已获得的权限转授给其他用户,也可以把已授给其他用户的权限再回收上来
  • 权限图

    • 结点是用户,根结点是DBA,有向边UiUjU_i→U_j,表示用户UiU_i把某权限授给用户UjU_j
    • 一个用户拥有权限的充分必要条件是在权限图中有一条从根结点到该用户结点的路径

授权命令

1
grant 表级权限 on {表名 | 视图名} to {用户 [,用户]… | public}  [with grant option]

表级权限包括:select, update, insert, delete, index, alter, drop, resource以及它们的总和all,其中对select , update可指定列名

with grant option表示获得权限的用户可以把权限再授予其它用户


示例

1
2
3
4
grant  select , insert  on  S  to  Liming  with  grant  option;
grant all on S to public;
grant UPDATE(sno), SELECT ON S to U4;
grant ALL PRIVILIGES to public;

把对表SCINSERT权限授予U5用户,并允许他再将此权限授予其他用户

1
GRANT INSERT ON  SC  TO U5  WITH GRANT OPTION

角色

角色:为了指明一类人应有的授权,提出了角色概念。在数据库中建立角色集,并授予每个角色一定的权限,然后将角色分配给用户。

1
2
3
4
create role teller;
grant select on account to teller;
grant insert,delete on customer to teller;
grant teller to john;
  • 数据库中可能只有一个用户标识符来对应应用程序的所有用户

  • 应用程序的授权由应用程序本身完成,SQL授权机制被忽略

    • 功能权限
    • 数据权限

回收权限

1
revoke 表级权限 on {表名 | 视图名} from {用户 [,用户]… | public}

收回权限时,若该用户已将权限授予其它用户,则也一并收回。授权路径的起点一定是DBA

示例

1
revoke  insert  on  S  from  Liming;

数据库级别权限

支持多库的数据库系统中授权对象可以是数据库

1
grant 数据库级权限 to {用户 [,用户]… | public}

数据库级权限包括:

connect:允许用户在database语句中指定数据库

resourceconnect权限+建表、删除表及索引权利

dbaresource权限 + 授予或撤消其他用户的connect、resource、dba权限

不允许dba撤消自己的dba权限

1
2
3
4
5
6
7
8
9
10
11
create  or replace view test6_02 as
select a.s sid,name, sum_score
from
(select sid s,name
from pub.student
where class = '2009' and dname = '软件学院' ) a,
(select a.sid s,sum(score) sum_score
from pub.student a,pub.student_course b
where class = '2009' and dname = '软件学院' and a.sid = b.sid
group by a.sid) b
where a.s=b.s(+)
1
2
3
4
5
6
7
8
9
10
create view test6_06 as
select distinct sid, name
from pub.student S
where not exists
((select cid
from pub.course)
minus
(select cid
from pub.student_course T
where S.sid = T.sid and score>=60))