数据库概论五

¶其他概念

¶视图

¶视图的特点

• 虚表,从基本表或视图导出的表
• 视图是定义,不存放数据
• 当基本表数据改变时,从视图查询的数据也会改变

¶定义视图

CREATE VIEW <视图名> [(<列名>[,<列名>...])] AS 子查询 [WITH CHECK OPTION]

• WITH CHECK OPTION
  对视图进行操作时,要确保满足定义时的条件(即子查询中的条件表达式)
• 视图中的列
  • 省略时则由子查询字段构成

    #创建信息系学生的视图
    CREATE VIEW cs_student as select * from student where dept='is';
    #带上with check option
    CREATE VIEW cs_student as select * from student where dept='is' with check option;

• with check option:
  • insert 操作时,若添加了with check option 插入数据不满足创建view时条件则拒绝插入(MYSQL)
    insert into x_student (name, age, sex, dept, entrance) values ('刘五狗',23,'男','is','2015-09-01');
    只有当dept为’is’才能插入
  • update操作时,不能改变视图条件
• 基于多个表的视图

# 建立信息系选修了1号课程的学生的视图（包括 学号、姓名、成绩）。 
create view IS_S1 AS select student.id,name,grade from student,sc where dept='is' and student.id=sid and cid=1;

• 基于视图的视图

#建立信息系选修了1号课程且成绩在90分以上的 学生的视图
create view is_s2 as select * from is_s1 where grade>90;

• 带有表达式

#定义一个反映学生出生年份的视图
create view bt_s as select name,2019-age as birthday from student;

• 带有分组

#将学生的学号及平均成绩定义为一个视图 
create view s_g as select name,avg(grade) from sc group by sid;

¶删除视图

drop view 视图名 [cascade]

¶查询视图

用户角度来看和操作表相同

#在信息系学生的视图中找出年龄小于20岁的学生
select * from is_student where age <20;
#查询选修了一号课程的信息系学生
select * from is_student,sc where is_student.id=sc.sid and where cid=1;

¶视图更新

• 有些视图无法更新
  • 若视图是由两个以上基本表导出的，则此视图不允许更新,即连接表无法更新
  • 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT 和UPDATE操作，但允许执行DELETE操作。
  • 若视图列来自聚集函数则无法更新,如上面例子的S_G 平均成绩表,就无法更新平均成绩
  • 若视图定义中含有GROUP BY子句，则此视图不允许更新。
  • 若视图定义中含有DISTINCT短语，则此视图不允许更新
  • 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是 导出该视图的基本表，则此视图不允许更新。

¶视图的作用

• 视图能够简化用户的操作:当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作
  • 基于多张表连接形成的视图
  • 基于复杂嵌套查询的视图
  • 含导出属性的视图
• 视图使用户能以多种角度看待同一数据
• 视图对重构数据库提供了一定程度的逻辑独立性

例：学生关系Student(Sno,Sname,Ssex,Sage,Sdept)
“垂直”地分成两个基本表：
SX(Sno,Sname,Sage)
SY(Sno,Ssex,Sdept)
通过建立一个视图Student： 
CREATE VIEW  Student(Sno,Sname,Ssex,Sage,Sdept) AS  SELECT  SX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.Sdept FROM  SX,SY WHERE  SX.Sno=SY.Sno; 
使用户的外模式保持不变，用户的应用程序通过视图仍然能够 查找数据

• 视图能够对机密数据提供安全保护
  • 对不同用户定义不同视图，使每个用户只能看到他有权看 到的数据
• 适当的利用视图可以更清晰的表达查询

¶完整性约束

• 定义机制
• 检查机制
• 违约处理

¶实体完整性

• 实体完整性定义
  由primary key定义,作用于单列|多列(表级)

#列级主码
在列级定义主码
CREATE TABLE Student
( Sno CHAR(9) PRIMARY KEY,
 Sname  CHAR(20) NOT NULL,
 Ssex  CHAR(2),
 Sag SMALLINT,
 Sdept  CHAR(20)); 
#表级主码
CREATE TABLE Student
( Sno CHAR(9) ,
 Sname  CHAR(20) NOT NULL,
 Ssex  CHAR(2),
 Sag SMALLINT,
 Sdept  CHAR(20)
 PRIMARY KEY (Sno)
); 

• 违约处理
  • 检查主码是否唯一,若不唯一则拒绝
  • 检查是否为null,是null则拒绝
• 数据库一般会主动创建索引,避免全表查询主码

¶参照完整性

• 定义
  • 定义了参照的情况,只能选择空值,或者被参照列的值
  • 在CREATE TABLE中用FOREIGN KEY短语定义哪些 列为外码
  • 用REFERENCES短语指明这些外码参照哪些表的主码

#列级外键
CREATE TABLE student(
id int primary key,auto_increment,
name varchar(20) not null,
sex char(2) not null,
dept int foreign key referneces dept(id));
#表级外键
CREATE TABLE student(
id int primary key,auto_increment,
name varchar(20) not null,
sex char(2) not null,
dept int, 
foreign key referneces dept(id));

• 检查机制
  • 当参照组中添加元组
  • 修改参照列中的元组或修改被参照表中的参照列
  • 删除

• 违约处理
  • 拒绝（NO ACTION）执行,不允许该操作执行。该策略一般设置为默认策略
  • 级联（CASCADE）操作,当删除或修改被参照表（Student）的一个元组造成了与参照 表（SC）的不一致，则删除或修改参照表中的所有造成不一 致的元组
  • 设置为空值（SET-NULL）,当删除或修改被参照表的一个元组时造成了不一致，则将参照 表中的所有造成不一致的元组的对应属性设置为空值。

 
#设置参照完整性违约处理
CREATE TABLE SC (
sno char(9) not null,
cno char(9) not null,
grade smallint,
primary key (sno,cno),
foreign key (sno) references student(id) on delete cascade on update cascade,
foreign key (cno) references course(id) on delete no action,onupdate cascade);

¶用户定义完整性

• 定义
  • 列值非空not null
  • 列值唯一unique
  • 检查是否满足条件check

 
#检查单列满足条件
create table student(
sname vchar(20)
sex char(2) check (sex in ('男','女'),
CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
)

mysql似乎不能使用check约束,上例子中可以通过定义数据类型为enum(‘男’,‘女’),当取值为一个范围时只能通过触发器

• 检查机制
  当插入和更新时检查,若不满足则拒绝操作

¶断言

MYSQL不支持check和断言

¶定义

CREATE ASSERTION<断言名><CHECK 子句>

 
#限制数据库课程最多60人选修
CREATE ASSERTION asse_sc_db_num CHECK (60>= selelct count(*) from course,sc where sc.cid=course.id and course.name='数据库');
#限制每一门课程最多60名学生选修
CREATE ASSERTION ASSE_SC_CNUM1   CHECK(60 >= ALL (SELECT count(*) FROM  SC GROUP by cno) );