《数据库系统原理》课程实验姓名：班级：学号：佛山科学技术学院计算机系2012年12月目录一．实验需用表格 (3)二．实验内容 (4)2.1 SQL数据定义 (4)2.2实验二 SQL数据查询 (6)2.3实验三连接、嵌套和集合查询 (10)2.4实验四 SQL的数据更新 (15)2.5实验五视图的定义和维护 (17)2.6实验六触发器和存储过程 (22)三.实验感想 (26)一．实验需用表格学生-课程数据库xskc中用到的三个表文件如下：学生表：Student课程表：Course学生选课表：SC二．实验内容2.1 SQL数据定义一、实验目的和要求1．掌握利用SQL查询分析器和企业管理器进行数据库及基本表的定义、删除与修改；2．掌握索引的建立与删除的方法。

二、实验内容与步骤（一）建立数据库通过企业管理器或查询分析器建立学生-课程数据库xskc。

create database xskcon( 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);【题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;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, SdeptFROM 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) SdeptFROM Student;【题2-05】查询选修了课程的学生学号。

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

其命令为：SELECT Sno, SnameFROM StudentWHERE Sdept='MA';【题2-07】查询考试成绩有不及格的学生的学号。

SELECT DISTINCT SnoFROM SCWHERE Grade<60;【题2-08】查询所有年龄在20岁以下的学生姓名(Sname)及年龄(Sage)。

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

其命令为：SELECT Sname, SageFROM StudentWHERE Sage =18 AND Sage<=22;或SELECT Sname, SageFROM StudentWHERE Sage BETWEEN 18 AND 22;【题2-10】查询年龄不在18-20岁之间的学生姓名(Sname)及年龄(Sage)。

其命令为：SELECT Sname, SageFROM StudentWHERE Sage NOT BETWEEN 18 AND 20;【例11】查询计算机系、数学系和信息系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。

其命令为：SELECT Sno, Sname, SsexFROM StudentWHERE Sdept IN ('CS', 'MA', 'IS');等价于：SELECT Sno, Sname, SsexFROM StudentWHERE Sdept='CS' OR Sdept='MA' OR Sdept='IS';【例12】查询既不是信息系（IS）、数学系（MA）、也不是计算机系（CS）的学生的姓名(Sname)和性别(Ssex)。

其命令为：SELECT Sname, SsexFROM StudentWHERE Sdept NOT IN ('IS', 'MA', 'CS');【例13】查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。

其命令为：SELECT Sname, Sno, SsexFROM StudentWHERE Sname LIKE '刘%';【例14】查询姓“刘”且全名为4个汉字的学生的姓名(Sname)和所在系(Sdept)。

其命令为：SELECT Sname, SdeptFROM StudentsWHERE Sname LIKE '刘____';【例15】查询所有不姓刘的学生姓名(Sname)和年龄(Sage)。