数据库实验报告模版

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

姓 名： 班 级： 学 号：

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

2012年12月

1

目录

一．实验需用表格 ................................................... 3 二．实验内容 ....................................................... 4 2.1 SQL数据定义 ............................................... 4 2.2实验二 SQL数据查询 ......................................... 6 2.3实验三 连接、嵌套和集合查询 ................................ 10 2.4实验四 SQL的数据更新 ...................................... 15 2.5实验五 视图的定义和维护 ................................... 17 2.6实验六 触发器和存储过程 .................................... 22 三.实验感想 ....................................................... 26

2

一．实验需用表格

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

学生表：Student

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

课程表：Course

Cno（课程号） Cname（课程名） Cpno（先修课） Ccredit（学分） 1 数据库 5 4 2 数学 2 3 信息系统 1 4 4 操作系统 6 3 5 数据结构 7 4 6 数据处理 2 7 PASCAL 6 4

学生选课表：SC

Sno（学号） Cno（课程号） Grade（成绩） 200215121 1 92 200215121 2 85 200215121 3 88 200215122 2 90 200215122 3 80 3

二．实验内容

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

【题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

4

);

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

5

2.2实验二 SQL数据查询

一、实验目的和要求

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

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

二、实验内容及步骤 1 无条件查询

【题2-01】 查询全体学生的详细记录。这是一个无条件的选择查询，其命令为： SELECT * /*这里的“*”等价于ALL*/ FROM Student;

其结果为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)。其命令为：

6

SELECT Sname, Sage FROM Student WHERE Sage<20;

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

SELECT Sname, Sage FROM Student

WHERE Sage?=18 AND Sage<=22; 或

SELECT Sname, Sage FROM Student

WHERE Sage BETWEEN 18 AND 22;

【题2-10】 查询年龄不在18-20岁之间的学生姓名(Sname)及年龄(Sage)。其命令为： SELECT Sname, Sage FROM Student

WHERE Sage NOT BETWEEN 18 AND 20;

【例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 Students

WHERE Sname LIKE '刘____';

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

7

FROM Students

WHERE Sname NOT LIKE '刘%';

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

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

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

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

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

SELECT Sno, Cno FROM Reports

WHERE Grade IS NULL;

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

WHERE Grade IS NOT NULL;

【题20】查询计算机系年龄在20岁以下的学生姓名。

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)

8

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 SC.Cno=Course.Cno;

5 查询结果分组

例31 求各个课程号(Cno)及相应的选课人数。其命令为： SELECT Cno , COUNT(Sno) CntSno FROM SC GROUP BY Cno;

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

HAVING COUNT(Cno)>3

9

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 A.Cno, A.Cname, B.Cpno FROM Course A, Course B WHERE A.Cpno =B.Cno; 3 外连接

【例36】把例33中的等值连接改为左连接。该左连接操作在SQL Server 2000中的命令格式为： 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

10

SC.Sno=Student.Sno; 4 复合条件连接 【例37】

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

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

WHERE Student.Sno= SC.Sno AND SC.Cno=Course.Cno;

（二）嵌套查询

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

11

(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 R.Cno=C.Cno AND C.Cname='数据结构'; 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 FROM Student WHERE Sdept<>'CS'

AND Sage<= (SELECT MIN(Sage)

FROM Student WHERE Sdept='CS'); 4 带谓词EXISTS的嵌套查询

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

12

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 FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS

(SELECT * FROM SC WHERE Sno=Student.Sno

AND Cno=Course.Cno));

（三）集合查询

【例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;

13

【例50】 查询计算机系的学生与年龄不大于20岁的学生的差集。 本查询的等价说法是，查询数学系中年龄大于20岁的学生。 SELECT * FROM Student

WHERE Sdept='CS' AND Sage>20;

14

2.4实验四 SQL的数据更新

一、实验目的和要求

掌握SQL数据插入、修改和删除语句的一般格式和使用方法。

二、实验内容及步骤 1．插入数据

【题4-01】 将学生陈冬的信息（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。

INSERT

INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES(‘200215128’,’陈冬’,’男’,’IS’,18); 或 INSERT

INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES(‘200215128’,’陈冬’,’男’,18,’IS’); 或INSERT INTO Student

VALUES(‘200215128’,’陈冬’,’男’,18,’IS’);

【题4-02】 插入一条选课记录（’200215128’,’1’） INSERT

INTO SC(Sno,Cno) VALUES(‘200215128’,’1’);

【题4-03】 设数据库中已有一个关系History_Student，其关系模式与Student完全一样，试将关系Student中的所有元组插入到关系History_Student中去，其SQL命令为：

INSERT

INTO History_Student SELECT * FROM Student;

【例4-04】对每一个系，求学生的平均年龄，并把结果存入表文件Dept_age中。Dept_age表文件属性为：Sdept（系名）、Avg_age（平均年龄）。

INSERT

INTO Dept_age(Sdept,Avg_age) SELECT Sdept,AVG(Sage) FROM Student

15

GROUP BY Sdept;

2．修改数据

【题4-05】 将学号为“200215121”的学生年龄改为22岁。即要修改满足条件的一个元组的属性值。 UPDATE Student SET Sage=22

WHERE Sno=’200215121’;

【题4-06】 将所有学生的年龄增加1岁。即要修改多个元组的值。 UPDATE Student SET Sage=Sage+1;

【题4-07】将计算机科学系所有学生的成绩置零。

由于学生所在系的信息在Student表中，而学习成绩在SC表中，因此，可以将SELECT子查询作为WHERE子句的条件表达式。故该更新要求的SQL命令为：

UPDATE SC SET Grade=0 WHERE ‘CS’=

(SELECT Sdept FROM Student

WHERE Student.Sno=SC.Sno);

3．删除数据

【题4-08】删除学号为200215128的学生记录。 DELETE FROM Student

WHERE Sno=’200215128’;

【题4-09】删除所有学生的选课记录。 DELETE FROM SC;

这条DELETE语句将删除SC的所有元组，使SC成为空表。 【题4-10】删除计算机科学系所有学生的选课记录。 DELETE FROM SC WHERE ‘CS’=

(SELECT Sdept FROM Student

WHERE Student.Sno=SC.Sno);

16

2.5实验五 视图的定义和维护

一、实验目的和要求

1．掌握SQL视图建立、修改和删除; 2．掌握SQL视图查询。

二、实验内容

方法一：利用SQL语言实现视图的建立、删除、查询、更新 （一）定义视图 1 建立视图

【题5-01】建立数学系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有数学系（MA）的学生，视图的属性名为Sno，Sname，Sage，Sdept。

CREATE VIEW C_Student

AS

SELECT Sno, Sname, Sage, Sdept FROM Student WHERE Sdept=’MA’ WITH CHECK OPTION

【题5-02】建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图。 本视图由三个基本表的连接操作导出，其SQL语句如下： CREATE VIEW Student_CR

AS

SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course

WHERE Student.Sno= SC.Sno AND SC.Cno=Course.Cno

【题5-03】定义一个反映学生出生年份的视图。 CREATE VIEW Student_birth(Sno, Sname, Sbirth)

AS SELECT Sno, Sname, 2007-Sage FROM Student

2 删除视图

【题5-04】删除视图Student_birth。 DROP VIEW Student_birth;

（二）查询视图

【题5-05】在数学系的学生视图C_Student中找出年龄(Sage)小于20岁的学生姓名(Sname)和年龄(Sage)。

SELECT Sname, Sage FROM C_Student WHERE Sage<20;

17

说明：本例转换后的查询语句为： SELECT Sname, Sage FROM Student

WHERE Sdept=’MA’ AND Sage<20;

【题5-06】在Student_CR视图中查询成绩在85分以上的学生学号(Sno)、姓名(Sname)和课程名称(Cname)。

SELECT Sno, Sname, Cname FROM Student_CR WHERE Grade>85;

（三）更新视图

【题5-07】将数学系(MA)学生视图C_Student中学号为200215123的学生姓名改为“黄海”。 UPDATE C_Student SET Sname='黄海' WHERE Sno='200215123';

说明：DBMS自动转换为对基本表的更新语句如下： UPDATE Student SET Sname='黄海'

WHERE Sno='200215123' AND Sdept='数学';

【题5-08】向数学系学生视图C_Student中插入一个新的学生记录，其中学号为“200215124”，姓名为“王海”，年龄为20岁。

INSERT

INTO C_Student

VALUES ('200215124', '王海', 20, ’MA’);

【题5-09】删除数学系学生视图C_Student中学号为“200215124”的记录。 DELETE

FROM C_Student

WHERE Sno=’200215124’

方法二：利用企业管理器和视图创建向导实现视图的建立、删除、查询、更新

视图是根据子模式建立的虚拟表。一个视图可以由一个表构造，也可以由多个表构造。利用企业管理器和视图创建向导进行创建，查看和修改视图就如同对表的操作一样，非常容易。

1．创建视图

下面利用了SQL Server中提供的视图创建向导，来实现建立计算机科学系（CS）选修了1号课程的学生视图。

（1）打开企业管理器窗口，确认服务器，打开数据库文件夹，选中新视图所在的数据库（此例为xskc）。 （2）选择菜单：[工具]|[向导]

（3）在如图5-1所示的向导选择对话框中，单击数据库左边的“+”号，使之展开。选择“创建视图向导”项，单击“确定”按键。

18

图5-1 向导选择对话框

（4）进入创建视图向导后，首先出现的是欢迎使用创建视图向导对话框，其中简单介绍了该向导的功能。单击“下一步”后，就会出现选择数据库对话框。

（5）在选择数据库对话框中，选择视图所属的数据库。本例的数据库为“xskc”。单击“下一步”按键，则进入选择表对话框。

（6）在选择表对话框中，列出了指定数据库中所有用户定义的表。用户可以从中选择构造视图所需的一个表或者多个表，被选中的表成为构造视图的参考表。选择构造视图参考表的方法是：用鼠标单击表后的“包含在视图中”列，使复选框为选中状态。在本例的数据库中选中学生表（Student）和选课表(SC)。

单击“下一步”按键，则进入选择对话框。

（7）列选择对话框中以表格形式列出了创建视图参考表的全部属性，每个属性占表的一行，创建视图参考属性可以在表格中选出。选择视图参考属性的方法是用鼠标单击属性名后边的“选择列”列，使复选框为选中状态。本例中Student.Sno、Student.Sname、SC.Grade属性要被选中。

单击“下一步”按键，进入创建视图的定义限制对话框，如图5-2所示。

19

图5-2 定义限制对话框

（8）在定义限制对话框中，输入表的连接和元组选择条件。本例输入: WHERE Sdept=’CS’ and Student.Sno=SC.Sno and SC.Cno=’1’。

单击“下一步”按键，出现视图名对话框。

（9）在输入视图名对话框中输入所建视图的标识名。本例的视图名为“CSSC-View”。单击“下一步”按键，则出现视图创建完成对话框。

在完成对话框中给出了根据前面对话框输入的内容译成的SQL语句。您可以认真阅读该SQL语句，如果发现与要求有不符合之处可以直接进行修改。

确认无误后单击“完成”按钮。随后出现一个通知用户已成功创建视图的信息框，单击“确认”按钮后，整个创建视图工作就完成了。

2．查看和修改视图

视图创建好后，就可以利用它进行查询信息了。如果发现视图的结构不能很好地满足要求，还可以在企业管理器对它进行修改。

在企业管理器中修改视图结构需要首先选择服务器，数据库，并使数据库展开，然后用鼠标右击要修改的视图，在弹出的菜单上选择“设计视图”项，则弹出一个视图设计对话框，如图5-3所示。当对其修改完毕后关闭窗口，新的视图结构就会取代原先的结构。

20

图5-3

21

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

一、实验目的

1.通过实验使学生加深对数据完整性的理解，学会创建和使用触发器。 2.熟练创建存储过程，体会存储过程的作用。

二、实验内容

1．触发器

①在学生表Student中定义一个触发器tri1，保证新添加的学生的年龄（Sage）在16～25岁之间。 ②在成绩表SC中定义一个触发器tri2，当修改一个记录时，确保此记录的成绩(Grade)在0～100分之间。 ③在学生表Student中创建一个触发器tri2，当删除学生表Student中学生时，自动删除成绩表SC中相应学生的选课记录。

④在学生表Student中创建一个触发器tri4，当修改学生表Student中学生的学号时，自动修改成绩表SC中的相应学号值。并验证该触发器是否可以正常工作。

⑤在学生表Student中创建一个触发器tri5，当删除学生表Student中学生时，自动删除成绩表SC中相应学生的选课记录；当修改学生表Student中学生的学号时，自动修改成绩表SC中的相应学号值。并验证该触发器是否可以正常工作。 2．存储过程

①利用存储过程查找Student表中所有人的记录 ②利用存储过程查找Student表中某学号的记录

③利用存储过程查找Student表中某学号、某门课程的记录 ④计算出某学生的平均分

⑤创建一个带返回值的存储过程，返回某人的平均成绩

⑥创建一个存储过程pro6将课程中某门课程的学分修改为指定的值，其中某门课程的课程号、指定的值通过参数传递。并多次调用执行该存储过程来修改不同的课程的学分值。

三、具体实现

1．触发器

①在学生表Student中定义一个触发器tri1，保证新添加的学生的年龄（Sage）在16～25岁之间。 【创建触发器】

create trigger tri1 on student for insert as

if(select Sage from inserted)>25 or (select Sage from inserted)<16 begin

print ‘年龄在16-25岁之间’ rollback end

【调用触发器】insert into Student values('200231001','王涛','男',30,'IS')

②在成绩表SC中定义一个触发器tri2，当修改一个记录时，确保此记录的成绩(Grade)在0～100分之间。 create trigger tri2 on SC

22

for update as

if not exists(select * from inserted where Grade between 0 and 100) print ‘成绩应在0-100分之间’ rollback

注：就是插入语句后，如果inserted表（插入或更新影响行构成的表）内的值不在0-100之间，就撤销插入。 【调用触发器】 update SC set Grade=120 where Sno='200215122'

③在学生表Student中创建一个触发器tri3，当删除学生表Student中学生时，自动删除成绩表SC中相应学生的选课记录。 【创建触发器】

create trigger tri3 on student for delete as

delete SC where Sno in(select Sno from deleted) delete Student where Sno in(select Sno from deleted) 【调用触发器】

delete Student where Sno=’200215122’

【注意】实现级联删除时，因为Student表和SC表的主键、外键关系，应先允许“级联删除相关记录”。

23

2．存储过程

①利用存储过程查找Student表中所有人的记录 【创建存储过程】

create procedure pro1 as

select * from Student

【调用存储过程】exec pro1

②利用存储过程查找Student表中某学号的记录 【创建存储过程】 create procedure pro2 @xh char(9) as

select * from student where Sno=@xh 【调用存储过程】exec pro2 200215121

③利用存储过程查找Student表中某学号、某门课程的记录 【创建存储过程】 create procedure pro3 @xh char(9),@kch char(4) as

select * from SC

where Sno=@xh and Cno=@kch 【调用存储过程】 exec pro3 200215121,1

④创建一个存储过程pro4，将课程中某门课程的学分修改为指定的值，其中某门课程的课程号、指定的值通过参数传递。并多次调用执行该存储过程来修改不同的课程的学分值。 【创建存储过程】 create procedure pro4 @kch char(4),@xf int as

update course set Ccredit=@xf where Cno=@kch 【调用存储过程】 exec pro4 1,10 exec pro4 2,20

⑤创建一个带返回值的存储过程，返回某人的平均成绩 【创建存储过程】 create procedure pro5 @xh char(9)

24

as

declare @pjf int select @pjf=avg(grade) from SC where Sno=@xh return @pjf 【调用存储过程】 declare @pjcj int exec @pjcj=pro5 200215121

print ‘pjcj=’+cast(@pjcj as char(10))

⑥计算出某学生的平均分 【创建存储过程】 create procedure pro6 @xh char(9),@pjf int output as

select @pjf=avg(grade) from SC where Sno=@xh 【调用存储过程】 declare @picj int

exec pro6 200215121,@pjcj output select @pjcj

25

四、在企业管理器中创建触发器

（1）在企业管理器中，由服务器开始逐步扩展到触发器所属表的数据库（本例为学生选课数据库），打开表文件夹，在表窗口中用鼠标右击触发器所属的表（本例为选课表）。

（2）在弹出菜单上选择“所有任务”“管理触发器”项，则弹出如图所示的触发器属性对话框。

图6-1 触发器属性对话框

如果要新建触发器，在文本[T]的文本框中输入创建触发器的SQL语句（见实验内容），单击“检查语法”按钮进行语法的检查，检查无误后，单击确定按钮。

（3）如果要修改触发器，则在名字弹出项中选择要修改的触发器名（本例为SC_inserted）,然后在文本[T]的文本框中对已有的内容进行修改，检查无误后单击“确定”按钮。

（4）如果要删除触发器，则在名字弹出项中选择要删除的触发器名（本例为SC_inserted），并检查文本[T]的文本框中的内容，看是否选得正确，最后单击“删除”按钮。

三.实验感想

26

四、在企业管理器中创建触发器

（1）在企业管理器中，由服务器开始逐步扩展到触发器所属表的数据库（本例为学生选课数据库），打开表文件夹，在表窗口中用鼠标右击触发器所属的表（本例为选课表）。

（2）在弹出菜单上选择“所有任务”“管理触发器”项，则弹出如图所示的触发器属性对话框。

图6-1 触发器属性对话框

如果要新建触发器，在文本[T]的文本框中输入创建触发器的SQL语句（见实验内容），单击“检查语法”按钮进行语法的检查，检查无误后，单击确定按钮。

（3）如果要修改触发器，则在名字弹出项中选择要修改的触发器名（本例为SC_inserted）,然后在文本[T]的文本框中对已有的内容进行修改，检查无误后单击“确定”按钮。

（4）如果要删除触发器，则在名字弹出项中选择要删除的触发器名（本例为SC_inserted），并检查文本[T]的文本框中的内容，看是否选得正确，最后单击“删除”按钮。

三.实验感想

26

本文来源：https://www.bwwdw.com/article/ic0w.html