数据库原理复习3

数据库原理复习3

关系数据库标准语言SQL

学生-课程数据库：
S表：

sno	sname	ssex	sdep	sage
801	张三	女	01	19

C表：

cno	cname	credit	cpno
C1	数据库	3.5	C2

SC表：

sno	cno	grade
801	C4	92

数据的定义

数据库的定义：
CREATE DATEBASE <数据库名>
数据库的删除：
DROP DATABASE <数据库名>
数据库的修改：
ALTER DATABASE<数据库名>

基本表的定义：
CREATE TABLE <表名>（
<列名> <数据类型> [完整性约束]
…
）
常用数据类型：char、varchar、text、nchar、nvarchar、ntext
完整性约束：Primary key，Unique、Not null、Foreign key、default value、check约束
例：
建立一个“学生”表student，由学号Sno、Sname、Ssex，Sdept五个属性构成。其中学号不为空，值唯一，并且姓名取值也是唯一的。
CREATE TABLE Student
(
Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(20) UNIQUE,
Ssex CHAR(1),
Sage INT,
Sdept CHAR(15),
PRIMARY KEY(SNO)
)
注： PRIMARY KEY(SNO)与Sno CHAR(5) NOT NULL UNIQUE等价
例2：建立一个“学生选课表”SC，由Sno，Cno，Grade组成，其中（Sno，Cno）为主码。
CREATE TABLE SC
(
SNO CHAR(5),
CNO CHAR(3),
GRADE INT,
PRIMARY KEY (SNO,CNO),
FOREIGN KEY (SNO) REFERENCES S(SNO),
FOREIGN KEY (CNO) REFERIENCES C(CNO)
)
基本表的修改：
ALTER TABLE <表名>
[ADD <新列名><数据类型> [完整性约束]]
[DROP<完整性约束><列名>]
[ALTER COLUMN<列名><数据类型>]；

基本表的删除：
DROP TABLE <表名 >

索引的定义：
CREATE [UNIQUE] [CLUSTER/NONCLUSTERED] INDEX <索引名>
ON <表名>（<列名>，[<次序>] …）
注：次序：ASC->升序 DESC->降序(默认是升序)
例：
CREATE UNIQUE INDEX STUSNO ON S(SNO);
CREATE UNIQUE INDEX STUCNO ON C(CNO);
CREATE UNIQUE INDEX ON SC(SNO ASC,CNO DESC);

在Student表的sname列上建立一个聚簇索引，而且student表中的记录将按照sname值的升序存放
CREATE CLUSTER INDEX STUSNAME ON STUDENT(SNAME);

索引的删除
DROP INDEX <索引名>

数据查询

单表查询

1.选中表中若干列

SELECT SNO,SNAME FROM STUDENT;
目标列为表达式的话 SELECT SNAME,2000-SAGE FROM S;

2.选择表中的若干元组

消除取值重复的行：DISTINCT
运算符：

IN谓词（多个OR的缩写）
字符匹配：
LIKE ‘<匹配串>’[ESCAPE’<换码字符>’]
通配符：%——任意字符串（长度可为0）
_——任意单字符（一个汉字需要两个）
select * from s where sname like ‘刘%’

order by ——对查询结果排序(asc为升序desc为降序，默认为asc)
select sno,grade from sc where cno = ‘3’ order by grade desc;

集合函数

group by 对查询结果分组
·值相等的为一组，常与集函数一起使用
·having条件只能作用组
·where作用于表和视图
例：求各个课程号及相应的选课人数
SELECT cno,count(sno)from sc group by cno;
例：查询学号在95001到95100之间选修了2门以上的课程的学生学号
select sno from sc
where sno between 95001 and 95100
group by sno having count(*)>2

等值连接与自然连接：

前一个等值连接会有两个sno：student.sno和SC.sno
自身连接：
例：查询每门课的间接选修课：
select a.cno,b.cpno from C a,C b Where a.cpno = b.cno
或者
selcet a.cno,b.pcno from c as a INNER JOIN c as b on a.cpno = b.cno
外连接
查询每个学生及其选课情况：
select s.sno,sname,ssex,sage,sdept,cno,grade
from s LEFT OUT JOIN sc (左外连接)
on s.sno = sc.sno

复合条件连接
where语句后加多个条件

嵌套查询

查询块：select-from-where
例：
注意：子查询的select不能使用order by 子句

有些嵌套查询可以用连接查询实现，有些不可以


（连接查询一般较快）采用不相关子查询效率要优于连接查询
注意：子查询必须跟在比较符之后
in -》返回多值或单值 = -》返回单值

带有any/all 谓词的子查询

例：查询其他系中比信息系某一学生的年龄小的学生姓名和年龄
select sname,sage from s
where sage<ANY(
select sage from s
where sdep = ‘IS’
)
AND sdept <> ‘IS’;
或者：
select sname,sage from s
where sage<(
select MAX(sage) from s
where sdep = ‘IS’
)
AND sdept <> ‘IS’;

带有EXISTS谓词的子查询
·带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值true（内层非空）和逻辑假值false（内层查询为空）
·由EXISTS引出的子查询，其目标列表达式通常都用*
例：查询所有选修了1号课程的学生姓名。
select sname from s
where EXISTS
(
select * from sc where sno = s.sno AND cno=‘1’
)
NOT EXISTS谓词：
例：查询所有没有选修1号课程的学生姓名。
select sname from s
where NOT EXISTS
(
select * from sc where sno = s.sno AND cno=‘1’
)

替换举例：

用EXISTS表示ALL
例如：查询选修了全部课程的学生姓名。
（不存在一门课，是该学生没有选的）
select sname from s
where NOT EXISTS
(
select * from C
where NOT EXISTS
(
select * from SC
where sno = s.sno AND cno = c.cno
)
)

或者：
select sno
from sc
group by sno
having count(cno) =
(select count(cno ) from c）

集合查询

并（UNION）
交（INTERSECT）
差（EXCEPT）


查询综合应用中的注意问题
1）集函数只能用在select字句和having短语之中，绝对不能出现在where字句之中

集函数没有复合功能，不能写成MAX（AVG（GRADE））
2）查询的输出只能输出最外层查询所用的表，如果输出属性涉及多个表，需要进行连接
3）ORDER BY 的使用

数据更新

插入数据（给表插入数据）

插入单个元组：insert into SC(sno,cno)values(‘9500’,‘1’)
插入子查询结果：
insert into Deptage(Sdept,Avgage)
select sdept,AVG(sage)
from S
Group by sdept

修改数据（修改表中元组）

修改单个元组
update S set sage = 22 where sno = ‘95001’
修改多个元组：
update S set age = age +1;
带子查询修改语句：
将信息系学生成绩置0
update sc set grade = 0
where sno in
(
select sno from s where sdept = ‘IS’
)

删除数据（从表中删除元组）

删除单个元组的值
delete from S where sno = ‘95001’
删除多个元组的值
delect from sc where cno = ‘2’
带子查询的删除语句:
删除计算机系所有学生的选课记录
delete from SC
where sno in
(
select sno from s where s.sdept = ‘CS’
)

视图

视图是从基本表中导出的虚拟表
create view<视图名>[(列名)]
as <子查询>< with check option>
with check option :透过视图进行增删改操作时，不得破坏视图中定义的谓词条件

例：建立信息系学生视图，并要求通过该视图进行的更新操作只涉及信息系学生
create view IS_Student
as
select sno,sname,sage
from S
where sdept = ‘IS’
with check option
从单个基本表中只是去掉了基本表的某些行和某些列，但是保留了码，这类视图被称为行列子集视图
例：建立信息系选修了1号课程的学生视图（基于多个基本表的视图）
create view IS_S1(sno,sname,grade)
as
select S.sno,sname,grade
from S,sc
where sdept = ‘IS’ AND S.sno = SC.sno AND SC.cno = ‘1’
例：定义一个反应学生出生年份的视图（带表达式的视图）.
create view BT_S(sno,sname,sbirth)
as
select sno,sname,2000-sbirth
from s

视图删除
drop view <视图名>
注意：该语句从数据字典中删除指定的视图定义
但是由该视图导出的其他视图定义仍然在数据字典中，但已经不能使用
所以删除基本表时，由该基本表导出的所有视图定义都必须显式删除

视图查询：

例：在信息系学生视图中找出年龄小于20岁的学生
create view IS_Student
as
select sno,sname,sage
from S
where sdept = ‘IS’;

select sno,sage
from IS_Student
where sage < 20;
视图消解：
select sno,sage
from S
where sdept = ‘IS’ AND sage < 20

视图更新：
注意with check option 的作用
修改：update IS_Student set sname = ‘张三’ where sno = ‘95001’
插入： insert into IS_Student(sno,sname,sage) values(‘95001’，‘张三’，‘20’)
删除：delete from IS_Student where sno = ‘95001’
一些视图是不可以更新的，因为这些视图的更新不能唯一有意义的转换成对基本表的更新，即视图的更新最终还是通过修改基本表实现的。