复试数据库系统概论（2）

上一篇：复试数据库系统概论（1）

一、sql的特点

1、综合统一

SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、插入数据建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求。

2.高度非过程化

只需提出“做什么”，而不必指明“怎么做”。3.面向集合的操作方式
4.以同一种语法结构提供两种使用方法
SQL语言既是自含式语言，又是嵌入式语言。

支持SQL的关系数据库管理系统同样支持关系数据库三级模式结构。
外模式对应于视图和部分基本表;
模式对应于基本表;
内模式对应于存储文件。

二、数据准备

用到的3个关系
s(sno,sname,ssex,sbirth,college)
c(cno,cname,cpno,ccredit)
sc(sno,cno,grade)

三、数据定义

1、创建基本表

1.1、格式

create table<表名>
(<列名><数据类型>[<列级完整性约束条件>][,<列名><数据类型[<列级完整性约束条件>]]
[，表级完整性约束条件>]);

1.2、常用数据类型

char：固定长度的字符型,最大存放2000个字符。
varchar2：可变长度的字符型，最大存放4000个字符。
number：所有的数值型,格式为number(p，s),其中p表示总位数，s表示小数的位数,p最大为38。
date：存放日期时间类型，用7个字节分别描述年、月、日、时、分、秒。

1.3、常用完整性约束

实体完整性约束:PRIMARY KEY
参照完整性约束:FOREIGN KEY
用户自定义的完整性约束：
check约束
唯—性约束:unique
非空值约束:not null

1.4、例子

（1）建立一个“学生”表s，它由学号sno，姓名sname，性别ssex，出生日期sbirth，所在学院college五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

create table s
	(sno varchar2(10) primary key,
	sname varchar2(40) unique,
	ssex varchar2(10),
	sbirth date, 
	college varchar2(40));

（2）建立“学生选课”表sc，它由学号sno，课程号cno，修课成绩grade组成，其中(sno, cno)为主码,成绩grade取值在0-100之间。

create table sc(
	sno varchar2(10),
	cno varchar2(10),
	grade number,
	primary key (sno, cno),
	foreign key (sno) references s(sno),
	foreign key (cno) references c(cno));

2、修改基本表

2.1、格式

alter table<表名>
[ add<新列名><数据类型>[完整性约束]]
[ add<表级完整性约束>]
[ drop column<列名>]
[ drop constraint<完整性约束名>]
[ modify<列名><数据类型>];

2.2例

（1）向s表增加“入学时间”列，其数据类型为日期型。

alter table s add rxsj date;

（2）删除学生姓名必须取唯一值的约束。

alter table s
drop constraint sys_c0014884;

3、删除基本表

3.1、格式

drop table<表名>;

3.2例

删除学生选课表sc。

drop table sc;

4、索引

4.1、什么是索引？

·在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。
·它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
·建立索引是加快查询速度的有效手段。

4.2、谁建立索引？

(1)DBA或表的属主根据需要建立。
(2）有些DBMS自动建立以下列上的索引。-Primary key Unique

4.3、维护索引

DBMS自动完成索引的维护。

4.4、使用索引

DBMS自动选择是否使用索引以及使用哪些索引。

4.5、建立索引的考虑

·索引为性能所带来的好处是有代价的，因为索引在数据库中会占用一定的存储空间。
·在对数据进行插入、更改和删除操作时，需要对索引进行维护,这需要花费额外的时间。
·在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源方面的代价。

4.6、建立索引

·创建索引的方法可以分为直接方法和间接方法。
·直接创建索引的方法就是使用create index语句直接创建。
·间接创建索引就是通过创建其他对象而附加创建了索引，例如在表中定义主键约束或唯一约束时，同时也创建了索引。

4.6.1格式

create [unique | bitmap] index<索引名>
on<表名>(<列名[<次序>][,列名>[次序>]]…);

4.6.2例

在c表中的cname属性列上建立索引。

create index (ind_cname)on c(cname);

（2）将c表的ind_cname索引的名称进行修改。

alter index ind cname rename to index_cname;

（3）删除c表的ind_cname索引。

drop index ind_cname;

四、数据查询

1、分类

单表查询、连接查询、嵌套查询、集合查询

2、格式

select [all]distinct]<目标列表达式[,<目标列表达式]…
from<表名或视图名式,<表名或视图名>]…
[ where <条件表达式>]
[ group by <列名1> [ having <条件表达式>]
[ order by<列名2> [ asc]desc ] ];

3、单表查询

3.1、查询指定列

查询所有学生的姓名和所在学院。

select sname,college
from s;

3.2、查询全部列

查询所有的课程信息。

select*
from c;

3.3、查询经过计算的列

查询所有学生的姓名和年龄。

select sname,2019-to_char(birth, 'yyyy')from s;
#起别名
select sname,2019-to_char(birth, 'yyyy') sage from s;

3.4、消除取值重复的行(distinct)

在学生表中查询所有的学院信息。

select distinct college
from s;

3.5、查询满足条件的元组

查询满足指定条件的元组可以通过where子句实现。

（1）比较=,>,K，=,=,!=,; not+上述比较运算符。
查询课程成绩在9o分以上的选课记录。

select*
from sc
where grade>90;

(2）确定范围 Between and，not between and。
查询课程成绩在60到100分之间的选课记录。

select *
from sc
where grade between 60 and 100;

(3）确定集合 in，not in。
查询课程成绩是60,70,80,90,100分的选课记录。

select*
from sc
where grade in (60,70,80,90,100);

(4)字符匹配 谓词like可以用来进行字符串的匹配。
格式如下:
where<属性列>[not] like<匹配串>[escape<换码字符]
功能:查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串,也可以含有通配符%和_。

通配符%(百分号)代表任意长度（长度可以为0)的字符串。
例: a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串。
通配符_(下划线)代表任意单个字符。
例: a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。

例:查询所有姓“李”的学生信息。

select *
from s
where sname like李%';

例:查询课程名以‘DB_”开头，且倒数第3个字符为i的课程的详细情况。

select *from c
where cname like‘DB\%i__'escape'\' ;
#escape'\' 意为：指定\后的_不是通配符

(s）涉及空值的查询 使用谓词is null或is not nullo“is null”不能用“=null”代替。
例:某些学生选修课程后没有参加考试，所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

select sno,cno 
from sc
where grade is null;

(6）多重条件查询
·用逻辑运算符and和or来联结多个查询条件。
and的优先级高于or
可以用括号改变优先级
·可用来实现多种其他谓词。
[not] in
[not] between … and …

例:查询课程成绩在60到100分之间的选课记录

select*
from sc
where grade >=60 and grade<=100;

3.5、对查询结果排序

使用order by子句
可以按一个或多个属性列排序。
升序: asc，降序:desc，缺省值为升序。
当排序列含空值null时
asc:排序列为空值的元组最后显示。
desc:排序列为空值的元组最先显示。

例:查询选修了003号课程的学生的学号及其成绩,查询结果按分数降序排列。

select sno,grade
from sc
where cno=003'
order by grade desc;

3.6、使用集函数

计数
count([distinct|all]*)
count([distinct|all]<列名>)
计算总和
sum([distinct|all]<列名>)
计算平均值
avg([distinct|all]<列名>)
求最大值
max([distinct|all]<列名>)
求最小值
min([distinctlall]<列名>)

例:计算001号课程的学生平均成绩。

select avg(grade)
from sc
where cno='001";

3.7、对查询结果分组

使用group by子句将查询结果表按某一列或多列值分组,值相等的为一组。

对查询结果分组的目的是为了细化集函数的作用对象。
未对查询结果分组，集函数将作用于整个查询结果。对查询结果分组后，集函数将分别作用于每个组。

例:查询各个课程号及相应的选课人数。

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

如果分组后还要按一定的条件对这些组进行筛选，最终只输出满足条件的组，则可以使用having短语指定筛选条件。

例:查询选修了3门以上课程的学生学号。

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

having短语与where子句的区别:作用对象不同。

• where子句作用于基表或视图，从中选择满足条件的元组。
• having短语作用于组,从中选择满足条件的
  组。

4、连接查询

4.1、概念

若一个查询同时涉及两个以上的表，则称之为连接查询。
连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词。

4.2、连接条件的一般格式

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
比运算符:=，>，<，>=，<=，

4.3、等值与非等值连接查询

当连接运算符为“-”时，称为等值连接。使用其它运算符称为非等值连接。
例:查询姓名为陈旭的学生选修的所有课程的课程号与成绩。

select cno, grade
from s, sc
where s.sno=sc.sno and sname=‘陈旭’;

4.4、自身连接

·一个表与其自己进行连接，称为表的自身连接。
需要给表起别名以示区别。
由于所有属性名都是同名属性，因此必须使用表别名前缀。

例:查询选修了001号课程和003号课程的学生学号。

select a.sno
from sc a, sc b
where a.sno=b.sno and
a.cno='001' andb.cno=‘003";

4.5、外连接

·悬浮元组:两个关系在做自然连接时被舍弃的元组称为悬浮元组。

外连接:如果把悬浮元组也保存在自然连接的结果关系中，而在其它属性上填空值，那么这种连接就叫做外连接(outer join)。
左外连:如果只保留左边关系R中的悬浮元组就叫做左外连(left outer join)。
右外连:如果只保留右边关系S中的悬浮元组就叫做右外连(right outer join)。

例:查询每个学生及其选修课程的情况,包括没有选修课程的学生。

select s.sno, sname, ssex, sbirth, college, cno, grade
from s left outer join sc on(s.sno=sc.sno);

4.6复合条件连接

where子句中含多个连接条件时，称为复合条件连接。

例:查询选修002号课程且成绩在90分以上的所有学生的学号、姓名。

select s.sno, sname
from s, sc
where s.sno=sc.sno and cno=‘002' and grade>90;

5、嵌套查询

5.1、介绍

SQL语言允许多层嵌套查询。
子查询的限制:不能使用ORDER BY子句。
层层嵌套方式反映了SQL语言的结构化。
有些嵌套查询可以用连接查询替代。

5.2分类

不相关子查询：子查询的查询条件不依赖于父查询。
相关子查询：子查询的查询条件依赖于父查询。

5.3、引出子查询的谓词

（1）带有IN谓词的子查询。
（2）带有比较运算符的子查询。
（3）带有ANY或ALL谓词的子查询。
（4）带有EXISTS谓词的子查询。
前三种多为不相关子查询，第四种多为相关子查询

5.4、带有IN谓词的子查询

例：查询与“董顺”在同一个学院学习的学生。

#将第一步查询嵌入到第二步查询的条件中
select sno, sname, college
from s
where college in
	(select college
	from s
	where sname=‘董顺');

例：查询选修了课程名为“c语言”的学生学号和姓名。

select sno, sname   #最后在S关系中取出Sno和Sname
from s
where sno in
	(select sno        #然后在SC关系中找出选修了“001”号课程的学生学号。
	from sc
	where cno in         
		(select cno        #首先在C关系中找出“℃语言’的课程号，结果为“0011”号。
		from c
		where cname=‘C语言’));

#连接查询方法
select sno, sname
from s, sc, c
where s.sno=sc.sno and
sc.cno=c.cno andc.cname=‘C语言;

5.5、带有比较运算符的子查询

当能确切知道内层查询返回单值（一行值，只有一行元组）时，引出子查询的谓词可以用比较运算符。
例：查询与“董顺”在同一个学院学习的学生。

select sno, sname, college
from s
where colleg  =
	(select college
	from s
	where sname=‘董顺');

例：查询选修了课程名为“c语言”的学生学号和姓名。

select sno, sname   
from s
where sno in
	(select sno        
	from sc
	where cno =         
		(select cno       
		from c
		where cname=‘C语言’));

5.6、带有ANY或ALL谓词的子查询

需要配合使用比较运算符

>ANY：大于子查询结果中的任意一个值
>ALL：大于子查询结果中的所有值

<ANY：小于子查询结果中的任意一个值
<ALL：小于子查询结果中的所有值

=ANY：等于子查询结果中的任意一个值
=ALL：等于子查询结果中的所有值
<>ANY：不等于子查询结果中的某一个值
<>ALL：不等于子查询结果中的任何一个值

例：查询成绩比选修了006号课程中任意一个成绩低的
选课信息。

select *
from sc
where grade<any
	(select grade
	from sc
	where cno=‘006’);

注意：ANY和ALL谓词有时可以用集函数实现

5.7、带有EXISTS谓词的子查询

EXISTS谓词
带有EXISTS谓词的查询中，子查询不返回任何数据，只产生逻辑真值“True”或逻辑假值“False”。
·若内层查询结果非空，则返回真值。
·若内层查询结果为空，则返回假值。

由EXISTS引出的子查询，其目标列表达式通常都用“*”，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

例查询选修了001号课程的学生姓名。

select sname
from s
where exists
	(select*
	from sc
	where sc.sno=s.sno and cno=‘001');

6、集合查询

6.1种类

（1）并操作(UNION)
（2）交操作(INTERSECT)
（3）差操作(MINUS)

6.2、并操作

语句格式
<查询块>UNION<查询块>
参加UNION操作的各结果表的列数必须相同;对应项的数据类型也必须相同。

例:查询选修了001号或者003号课程的学生学号。

select sno
from sc
where cno=001’
union
select sno
from sc
where cno=‘003';

6.3、交操作

6.3.1 语句格式

<查询块>INTERSECT<查询块>
参加INTERSECT操作的各结果表的列数必须相同;对应项的数据类型也必须相同。

6.3.2 例:查询选修了001号和003号课程的学生学号。

select sno
from sc
where cno='001’
intersect
select sno
from sc
where cno=‘003’;

6.4、差操作

6.4.1 语句格式

<查询块>MINUS<查询块>
参加MINUS操作的各结果表的列数必须相同;对应项的数据类型也必须相同。

6.4.2 例:查询选修了003号课程与成绩低于60分的学生选课信息的差集。

select *
from sc
where cno=003
minus
select *
from sc
where grade<60;

五、数据更新

1、分类

（1）插入数据
（2）修改数据
（3）删除数据
注意：
修改数据与修改基本表的区别：update与alter
删除数据与删除基本表的区别：delete与drop

2、插入数据

2.1、两种方式

插入单个元组
插入子查询结果

3、插入单个元组

3.1、语句格式

insert
into<表名>[(<属性列1[,<属性列2>.…)]]
values (<常量1>[,<常量2>…])

说明：
into子句:指定要插入数据的表名及属性列名。
·全部指定属性列名:属性列的顺序可与表定义中的顺序不一致。
·没有指定任何属性列:表示要插入的是一条完整的元组，且插入数据顺序与表定义中属性列的顺序一致。
·指定部分属性列:插入的元组在其余属性列上取空值。

values子句:提供的值必须与into子句匹配。
·值的个数
·值的类型

3.2、例:将下面新的学生记录插入到S表中:(04050，陈冬，男，信息，1997/10/01)

insert
into s
values ('04050',‘陈冬',男', to_date(1997/10/01", 'yyyy/mm/dd'),'信息');

#方式二
insert
into s(sno,college,sname,ssex,sbirth)
values (04050∵信息∵陈冬∵男',to_date( 1997/10/01', 'yyyy/mm/dd'));

3.3、例:插入一条选课记录(‘040207 , ‘001’)。

insert
into sc(sno,cno)
values (04020',"001');

4、插入子查询结果

例:对每一个学院，求学生的平均年龄，并把结果存入数据库。
第一步:建表

create table college_avg_age
(college varchar2(40),
avgage number(4));

第二步:插入数据

insert
into college_avg_age(college,avgage)
select college,avg(2019-to_char(sbirth, 'yyyy'))
from s
group by college;

5、修改数据

5.1、语句格式

update<表名>
set<列名>-<表达式[,<列名>-<表达式>]…
[where<条件>;

5.2、例:将信息学院所有学生的年龄增加1岁。

update s
set sbirth=sbirth-365
where college=‘信息';

5.3例:将信息学院全体学生的成绩置100。

update sc     #update后只能跟一个表名
set grade=100
where sno in
	(select sno
	from s
	where college=‘信息');

6、删除数据

6.1、语句格式

delete
from<表名>
[where <条件>];

6.2、例:删除信息学院所有学生的选课记录。

delete     #delete也只能跟一个表名
from sc
where sno in
	(select sno
	from s
	where college=‘信息');

六、视图

·让所有用户都看到整个逻辑模型是不合适的。出于安全考虑，可能需要向用户隐藏特定的数据。
·除了安全考虑,我们还可能希望创建一个比逻辑模型更符合特定用户直觉的个人化的关系集合。

1、概念

视图是从一个或几个基本表(或视图)导出的表,它与基本表不同,是一个虚表。
数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。
基表中的数据发生变化，从视图中查询出的数据也随之改变。

2、定义视图

2.1、创建视图

2.1.1格式

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

2.1.2例

create view zb_sas
select sno, sname, birth, college
from s
where college=‘植保’
with check option;

2.2、删除视图

drop view<视图名;

该语句从数据字典中删除指定的视图定义。
·视图删除后，由该视图导出的其他视图定义仍在数据字典中,但已不能使用，必须使用drop view语句删除。
删除基表后，由该基表导出的所有视图定义都必须使用drop view语句删除。

2.3、视图的举例

2.3.1行列子集视图

若一个视图是从单个基本表导出,只是去掉了基本表的某些行和某些列，但保留了主码，则称这类视图为行列子集视图。

例：建立植保学院学生的视图，视图中包括学号、姓名、出生日期和学院属性。

create view zb_s(学号,姓名,出生日期,学院)
as
select sno,sname,birth,college
from s
where college=植保’;

2.3.2with check option的视图

例：建立信息学院学生的视图，并要求透过该视图进行的
更新操作只涉及信息学院学生。

create view xx_s
as
select sno,sname,birth,college
from s
where college=‘信息’
with check option;    #作用：更新操作只能对信息学院学生

2.3.3基于多个基本表的视图

例：建立水建学院选修了001号课程的学生视图。

create view sj_sc(sno, sname, grade)
as
select s.sno,sname,grade
from s, sc
where college=‘水建'and s.sno=sc. sno and sc.cno=001';

2.3.4基于视图的视图

例：建立水建学院选修了001号课程且成绩在90分以上的学生的视图。

create view sj_sc2as
select sno, sname, grade
from sj_sc    #sj_sc视图
where grade>90;

2.3.5带表达式的视图

例:定义一个反映学生年龄的视图。

create view s_age(sno, sname, sage)
as
select sno, sname,2019-to_char(birth,'yyyy')
from s;

2.3.6建立分组视图

例：将学生的学号及他的平均成绩定义为一个视图。

create view sc_avg_grade(sno, gavg)
as
select sno, avg(grade)
from sc
group by sno;

2.3.7删除视图

例：将视图xx_s删除。

drop view xx_s;

3、查询视图

3.1、介绍

视图定义以后，用户就可以像对基本表一样对视图进行查询。

DBMS内部执行对视图的查询时采用的的方法:视图消解。
视图消解是指将用户执行的对视图的查询,在DBMS内部转换成对导出视图的基本表的查询。
DBMS先进行有效性检查，检查查询的表、视图等是否存在。
如果存在,则从数据字典中取出视图的定义，把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询。
DBMS最终执行转换后的查询。

3.2、例:在植保学院学生的视图中找出1994年1月1号以后出生的学生。

select sno, birth
from zb_s
where birth>to_date( 1994/01/017,'yyyy/mm/dd');

#执行过程
#首先找出视图定义语句
zb_s视图的定义:
create view zb_s
as
select sno,sname,birth,college
from s
where college=‘植保;

#视图消解法转换后的查询语句
select sno, birth
from s
where college=‘植保’and birth>to_date(‘1994/01/01','yyyy/mm/dd');

4、更新视图

4.1、介绍

更新视图是指通过视图来插入(insert)、删除(delete)和修改(update)数据。
由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换成对基本表的更新。
像查询视图那样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。

4.2、例:将植保学院学生视图zb_s中学号04003的学生姓名改为“刘辰”。

update zb_s
set sname=‘刘辰’
where sno="04003';

#过程
#首先找到视图定义语句
zb_s视图的定义:
create view zb_s
as
select sno,sname,birth,college
from s
where college=‘植保';

#视图消解法转换后的更新语句:
update s
set sname=‘刘辰’
where sno=‘04003' and college=‘植保’;

注意：
在关系数据库中，并不是所有的视图都是可更新的,因为对有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。

4.3、更新视图的限制

一般地,行列子集视图是可更新的。
对其他类型视图的更新不同数据库管理系统软件有不同限制。
在Oracle中可以通过数据字典user_updatable_columns进行查看。

5、视图的作用

（1）视图能够简化用户的操作。
当数据不是直接来自基本表时，定义视图能够简化用户的操作。
·基于多张表连接形成的视图。
·基于复杂嵌套查询的视图。
·含导出属性的视图

（2）视图对重构数据库提供了一定程度的逻辑独立性。
（3）视图能够对机密数据提供安全保护。
（4）适当利用视图可以更清晰地表达查询。

6、总结

下一篇：复试数据库系统概论（3）