《数据库系统原理》课程实验

《数据库系统原理》课程实验

姓名：

班级：

学号：

佛山科学技术学院计算机系

2012年12月

目录

一、实验所需表格 (3)

二、实验内容 (3)

2.1实验一SQL数据定义 (3)

2.2实验二SQL数据查询 (7)

2.3实验三连接、嵌套和集合查询 (14)

2.4实验四SQL的数据更新 (21)

2.5实验五视图的建立和维护 (24)

2.6实验六触发器和存储过程 (330)

三、实验感想 (37)

参考资料

1．王珊萨师煊.《数据库系统概论》（第四版）.高等教育出版社，2006

2．李存斌.数据库应用技术---SQL Server2000简明教程.中国水利水电出版社，2001

一、实验所需表格

学生-课程数据库xskc中用到的三个表文件如下：

学生表：Student

Sno（学号）Sname（姓名）Ssex（性别）Sage（年龄）Sdept（所在系）200215121李勇男20CS（计算机科学系）200215122刘晨女19CS（计算机科学系）200215123欧阳原野女18MA（数学系）200215125刘阳光男19IS（信息系）

课程表：Course

Cno（课程号）Cname（课程名）Cpno（先修课）Ccredit（学分）1数据库54

2数学2

3信息系统14

4操作系统63

5数据结构74

6数据处理2

7PASCAL64

学生选课表：SC

Sno（学号）Cno（课程号）Grade（成绩）

200215121192

200215121285

200215121388

200215122290

200215122380

二、实验内容

2.1实验一SQL数据定义

一、实验目的和要求

1．掌握利用SQL查询分析器和企业管理器进行数据库及基本表的定义、删除与修改；

2．掌握索引的建立与删除的方法。

二、实验内容及结果

（一）建立数据库

通过查询分析器或企业管理器建立学生-课程数据库xskc。

create database xskc

on

(name=xskc_data,

filename='e:\sjksy\xskc_data.mdf')

log on

(name=xskc_log,

filename='e:\sjksy\xskc_log.ldf')

注：先在E：盘上建立一个文件夹（例如：E:\sjksy），数据库文件保存到自建的文件夹中。

（二）基本表的定义、修改与删除

1．定义基本表

利用查询分析器或企业管理器创建基本表，并输入数据。

【题1-01】建立一个学生表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。要求“学号”为主键，“姓名”不能为空,“性别”默认值为“男”。

CREATE TABLE Student

(Sno CHAR(9)PRIMARY KEY,

Sname CHAR(20)NOT NULL,

Ssex CHAR(2)DEFAULT‘男’,

Sage INT,

Sdept CHAR(20)

）;

说明：在Microsoft SQL Server2000的查询分析器(Query Analyzer)中使用单条SQL语句，其末尾不需要分号“；”作为命令结尾标记。通常，SQL Server 2000对大多数末尾带有分号的SQL命令都能顺利执行，但对少数的SQL命令，末尾若带分号，则SQL Server2000会给出错误信息提示。比如，若在实验五的例1的SQL命令末尾加上一个分号“；”，SQL Server2000就会出现“Incorrect syntax near';'”的提示，虽然SQL Server2000实际上已经执行了该命令。

【题1-02】建立课程表Course，它由课程号Cno、课程名Cname、先修课Cpno、Ccredit学分四个属性组成。要求“课程号”为主键，“课程名”属性不能为空。

CREATE TABLE Course

(Cno CHAR(4)PRIMARY KEY,

Cname CHAR(40)NOT NULL,

Cpno CHAR(4),

Ccredit INT

);

【题1-03】建立学生选修课表SC，包含学号Sno、课程号Cno、成绩Grade 三个字段。要求建立主键及与student、kc表联接的外键，并创建检查约束(Grade>=0and Grade<=100)。

CREATE TABLE SC

(Sno CHAR(9),

Cno CHAR(4),

Grade INT,

PRIMARY KEY（Sno，Cno），

FOREIGN KEY(Sno)REFERENCES Student(Sno),

FOREIGN KEY(Cno)REFERENCES Course(Cno)

);

2修改基本表

利用查询分析器修改基本表。

【题1-04】向基本表Student中增加“入学时间”属性列，其属性名为S_entrance，数据类型为日期型。

ALTER TABLE Student ADD S_entrance DATETIME;

【题1-05】将Student表中Sage(年龄)的数据类型改为SMALLINT型。

ALTER TABLE Student ALTER COLUMN Sage SMALLINT;

【题1-06】增加课程名称必须取唯一值的约束条件。

ALTER TABLE Course ADD UNIQUE(Cname);

【题1-07】将Student表的Sdept列允许空值的属性更改为不允许为空。

ALTER TABLE Student ALTER COLUMN Sdept CHAR(20)NOT NULL;

【题1-08】删除Student表中的S_entrance列。

ALTER TABLE Student DROP COLUMN S_entrance;

3删除基本表

【题1-09】删除Student表。

DROP TABLE Student;

说明：此表删除后，请立即将其建立起来，以便后面的例子使用。

（三）索引的建立和删除

1建立索引

【题1-10】在基本表Student的Sname(姓名)列上建立一个聚簇索引，而且Student中的物理记录将按照Sname值的升序存放。其语句为：CREATE CLUSTERED INDEX Stu_Sname ON Student(Sname);

【题1-11】分别为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按Sno(学号)升序建唯一索引，Course表按Cno(课程号)升序建唯一索引，SC表按Sno(学号)升序和Cno(课程号)号降序建唯一索引。其语句为：

CREATE UNIQUE INDEX Stu_Sno ON Student(Sno);

CREATE UNIQUE INDEX Cou_Cno ON Course(Cno);

CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

2删除索引

【题1-12】删除Student表的Stu_Sname索引。

DROP INDEX Student.Stu_Sname;

2.2实验二SQL数据查询

一、实验目的和要求

1．掌握SQL Server查询分析器的使用方法，加深对SQL查询语句的理解。2．熟练掌握查询语句的一般格式。

3．熟练掌握数据查询中的排序、分组、统计、计算和集合的操作方法。

二、实验内容及结果

1无条件查询

【题2-01】查询全体学生的详细记录。这是一个无条件的选择查询，其命令为：

SELECT*/*这里的“*”等价于ALL*/

FROM Student;

【题2-02】查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。这是一个无条件的投影查询，其命令为：

SELECT Sname,Sno,Sdept

FROM Student;

【题2-03】查询全体学生的学号(Sno)、姓名(Sname)及出生年份。由于SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。其命令为：

SELECT Sno,Sname,2012-Sage as‘2012-Sage’

FROM Student;

【题2-04】查询全体学生的学号、姓名、出生年份和所在系，要求用小写字母表示所有系名。其命令为：

SELECT Sno,Sname,2012-Sage'Year of Birth',LOWER(Sdept)Sdept FROM Student;

【题2-05】查询选修了课程的学生学号。其命令为：

SELECT DISTINCT Sno

FROM SC;

2条件查询

【题2-06】查询数学系（MA）全体学生的学号(Sno)和姓名(Sname)。其命令为：

SELECT Sno,Sname

FROM Student

WHERE Sdept='MA';

【题2-07】查询考试成绩有不及格的学生的学号。

SELECT DISTINCT Sno

FROM SC

WHERE Grade<60;

【题2-08】查询所有年龄在20岁以下的学生姓名(Sname)及年龄(Sage)。其命令为：

SELECT Sname,Sage

FROM Student

WHERE Sage<20;

【题2-09】查询所有年龄在18~20岁(包括18岁和20岁)之间的学生姓名(Sname)及年龄(Sage)。其命令为：

SELECT Sname,Sage

FROM Student

WHERE Sage =18AND Sage<=22;

或

SELECT Sname,Sage

FROM Student

WHERE Sage BETWEEN18AND22;

【题2-10】查询年龄不在18-20岁之间的学生姓名(Sname)及年龄(Sage)。

其命令为：

SELECT Sname,Sage

FROM Student

WHERE Sage NOT BETWEEN18AND20;

【例11】查询计算机系、数学系和信息系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。其命令为：

SELECT Sno,Sname,Ssex

FROM Student

WHERE Sdept IN('CS','MA','IS');

等价于：SELECT Sno,Sname,Ssex

FROM Student

WHERE Sdept='CS'OR Sdept='MA'OR Sdept='IS';

【例12】查询既不是信息系（IS）、数学系（MA）、也不是计算机系（CS）的学生的姓名(Sname)和性别(Ssex)。其命令为：

SELECT Sname,Ssex

FROM Student

WHERE Sdept NOT IN('IS','MA','CS');

【例13】查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。其命令为：

SELECT Sname,Sno,Ssex

FROM Student

WHERE Sname LIKE'刘%';

【例14】查询姓“刘”且全名为4个汉字的学生的姓名(Sname)和所在系(Sdept)。其命令为：

SELECT Sname,Sdept

FROM Student

WHERE Sname LIKE'刘____';

【例15】查询所有不姓刘的学生姓名(Sname)和年龄(Sage)。

SELECT Sname,Sage

FROM Student

WHERE Sname NOT LIKE'刘%';

【例16】查询课程名为“DB_设计”的课程号(Cno)和学分(Ccredit)。其命令为：

SELECT Cno,Ccredit

FROM Course

WHERE Cname LIKE'DB\_设计'ESCAPE'\';

【例17】查询以"DB_"开头，且倒数第2个汉字字符为“设”的课程的详细情况。其命令为：

SELECT*

FROM Course

WHERE Cname LIKE'DB\_%设__'ESCAPE'\';

【例18】假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。其命令为：

SELECT Sno,Cno

FROM SC

WHERE Grade IS NULL;

【例19】查询所有有成绩的学生学号(Sno)和课程号(Cno)。其命令为：SELECT Sno,Cno

FROM SC

WHERE Grade IS NOT NULL;

3查询结果排序

【例24】查询选修了3号课程的学生的学号(Sno)和成绩(Grade)，并按成绩降序排列。其命令为：

SELECT Sno,Grade

FROM SC

WHERE Cno='3'

ORDER BY Grade DESC;

【例25】查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。其命令为：

SELECT*

FROM Student

ORDER BY Sdept,Sage DESC;

4集函数的使用

【例26】查询学生总人数。其命令为：

SELECT COUNT(*)

FROM Student;

【例27】查询选修了课程的学生人数。其命令为：

SELECT COUNT(DISTINCT Sno)

FROM SC;

【例28】计算选修2号课程的学生平均成绩。其命令为：

SELECT AVG(Grade)

FROM SC

WHERE Cno='2';

【例29】查询选修2号课程的学生最高分数。其命令为：

SELECT MAX(Grade)

FROM SC

WHERE Cno='2';

【例30】查询学生200215122选修课程的总学分数。其命令为：SELECT SUM(Ccredit)

FROM SC,Course

WHERE Sno='200215122'AND https://www.360docs.net/doc/8c11777161.html,o=https://www.360docs.net/doc/8c11777161.html,o;

5查询结果分组

【例31】求各个课程号(Cno)及相应的选课人数。其命令为：

SELECT Cno,COUNT(Sno)CntSno

FROM SC

GROUP BY Cno;

【例32】查询选修了3门或3门以上课程的学生学号(Sno)。其命令为：SELECT Sno

FROM SC

GROUP BY Sno

HAVING COUNT(Cno)>3

2.3实验三连接、嵌套和集合查询

一、实验目的和要求

1．掌握SQL Server查询分析器的使用方法，加深对SQL查询语句的理解。2．熟练掌握查询语句的一般格式。

3．熟练掌握连接、嵌套和集合查询的使用。

二、实验内容及结果

（一）连接查询

1不同表之间的连接查询

【题3-01】查询每个学生及其选修课程的情况。

本查询实际上是涉及Student与SC两个表的连接操作。这两个表之间的联系是通过公共属性Sno实现的，因此，其操作命令为：

SELECT Student.*,SC.*

FROM Student,SC

WHERE Student.Sno=SC.Sno；

说明：若在以上等值连接中把目标列中重复的属性列去掉则为自然连接，其命令为

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

FROM Student,SC

WHERE Student.Sno=SC.Sno;

2自身连接

【例35】查`询每一门课的间接先修课(即先修课的先修课)。

在Course表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。这就需要要将Course表与其自身连接。为方便连接运算，这里为Course表取两个别名分别为A，B。则完成该查询的SQL语句为：SELECT https://www.360docs.net/doc/8c11777161.html,o,https://www.360docs.net/doc/8c11777161.html,ame,B.Cpno

FROM Course A,Course B

WHERE A.Cpno=https://www.360docs.net/doc/8c11777161.html,o;

3外连接

【例36】把例33中的等值连接改为左连接。该左连接操作在SQL Server2000中的命令格式为：

SELECT Student.Sno,Sname,Ssex,Sdept,Cno,Grade

FROM Student

LEFT JOIN SC ON

Student.Sno=SC.Sno;

说明：以上左连接操作也可以用如下的右连接操作代替，其结果完全一样。

SELECT Student.Sno,Sname,Ssex,Sdept,Cno,Grade

FROM SC

RIGHT JOIN Student ON

SC.Sno=Student.Sno;

4复合条件连接

【例38】查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。

本查询涉及到三个表的连接操作，完成该查询的SQL语句如下：SELECT Student.Sno,Sname,Cname,Grade

FROM Student,SC,Course

WHERE Student.Sno=SC.Sno AND https://www.360docs.net/doc/8c11777161.html,o=https://www.360docs.net/doc/8c11777161.html,o;

（二）嵌套查询

1带谓词IN的嵌套查询

【例39】查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。

该查询可构造嵌套查询实现，其SQL语句如下：

SELECT Sno,Sname,Sdept

FROM Student

WHERE Sdept IN

(SELECT Sdept

FROM Student

WHERE Sname=’李伟’);

说明：本例中的查询也可以用自身连接来完成，其SQL语句如下：

SELECT A.Sno,A.Sname,A.Sdept

FROM Student A,Student B

WHERE A.Sdept=B.Sdept AND B.Sname=’李伟’;

【例40】查询选修了编号为“2”的课程的学生姓名(Sname)和所在系(Sdept)。

SELECT Sname,Sdept

FROM Students

WHERE Sno IN

(SELECT Sno

FROM SC

WHERE Cno='2');

【例41】查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。

本查询涉及学号、姓名和课程名(Cname)三个属性。学号和姓名存放在Student表中，课程名的存放在Course表中，但Student与Course两个表之间没有公共属性，必须通过SC表建立它们之间的联系。所以本查询实际上涉及三个关系的连接操作。

SELECT Sno,Sname/*③最后在Studen关系中*/ FROM Student/*取出Sno和Sname*/ WHERE Sno IN

(SELECT Sno/*②然后在SC关系中找出*/

FROM SC/*选修了3号课程的学生学号*/

WHERE Cno IN

(SELECT Cno/*①首先在Course关系中*/

FROM Course/*找出“数据结构”的课程号*/

WHERE Cname=‘数据结构’))；/*结果为5号*/说明：本查询同样可以用连接查询实现：

SELECT S.Sno,Sname

FROM Student S,SC R,Course C

WHERE S.Sno=R.Sno AND https://www.360docs.net/doc/8c11777161.html,o=https://www.360docs.net/doc/8c11777161.html,o AND https://www.360docs.net/doc/8c11777161.html,ame='数据结构';

2带有比较运算符的嵌套查询

【例42】将例39改为带有比较运算符的嵌套查询。由于一个学生只可能在一个系学习，因此子查询的结果是一个值，因此可以用=代替IN，其SQL语句如下：

SELECT Sno,Sname,Sdept

FROM Student

WHERE Sdept=

(SELECT Sdept

FROM Student

WHERE Sname=’李伟’);

3带谓词ANY或ALL的嵌套查询

【例43】查询计算机系（CS）的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。其查询命令为

SELECT Sname,Sage

FROM Student

WHERE Sdept<>'CS'

AND Sage<=ALL(SELECT Sage

FROM Student

WHERE Sdept='CS');

说明：本查询也可以用集函数来实现。其SQL语句如下：

SELECT Sname,Sage

WHERE Sdept<>'CS'

AND Sage<=(SELECT MIN(Sage)

FROM Student

WHERE Sdept='CS');

4带谓词EXISTS的嵌套查询

【例44】查询所有选修了编号为“1”课程的学生姓名(Sname)和所在系(Sdept)。

本查询的SQL语句是：

SELECT Sname,Sdept

FROM Student

WHERE EXISTS

(SELECT*

FROM SC

WHERE Sno=Student.Sno AND Cno='1');

【例45】将例39改为带谓词EXISTS的查询，其SQL语句如下

SELECT Sno,Sname,Sdept

FROM Student A

WHERE EXISTS

(SELECT*

FROM Students B

WHERE B.Sdept=A.Sdept AND B.Sname=’李伟’);

【例46】查询选修了所有课程的学生姓名(Sname)和所在系。

由于没有全称量词，可将题目的意思转换成等价的用存在量词的形式：查询这样的学生，没有一门课程是他不选修的。其SQL语句为：

SELECT Sname,Sdept

WHERE NOT EXISTS

(SELECT*

FROM Course

WHERE NOT EXISTS

(SELECT*FROM SC WHERE Sno=Student.Sno

AND Cno=https://www.360docs.net/doc/8c11777161.html,o));

（三）集合查询

【例48】查询计算机系（CS）的学生或年龄不大于20岁的学生信息。

SELECT*

FROM Student

WHERE Sdept='CS'

UNION

SELECT*

FROM Student

WHERE Sage<=20;

【例49】查询计算机系（CS）的学生且年龄不大于20岁的学生的交集，这实际上就是查询数学系中年龄不大于20岁的学生。

SELECT*

FROM Student

WHERE Sdept='CS'AND Sage<=20;

【例50】查询计算机系的学生与年龄不大于20岁的学生的差集。