五邑大学《数据库原理》实验指导书.docx

1、五邑大学数据库原理实验指导书数据库原理实验指导书V2.0执笔： 日期：实验一 数据库、表、索引的定义一、实验目的：通过本实验加深对SQL Server 2008环境下数据定义语言的理解，能够熟练地建立数据库，并通过SQL语句来创建和修改基本表、创建和取消索引。二、实验内容：使用SQL Server 2008软件，练习创建和修改数据库、表、索引。三、实验要求：建立一个新的SQL Server设备管理数据库，使用SQL的数据定义功能建立数据库中的各个基本表（共3个），并按要求对有关表进行修改。 每次操作后通过企业管理器查看有关对象的变化。四、实验学时：2学时五、实验步骤：1创建数据库1) 使用企业

2、管理器创建数据库运行Microsoft SQL Server Management Studio并建立于数据库的连接后，进入如图1界面：在“数据库”上右击，进入图2所示界面。在图3中进行相关设置，主要包括：1 输入数据库名称（根据学生本人的学号取名），所有者为默认。2 在“数据库文件”页中输入文件逻辑名称。3 若要更改数据文件的物理存储位置，在“路径”中修改，（注意确定文件目录已经存在、并允许读写）。4 在“初始大小”项上输入希望的大小。5 可在“自动增长”中修改默认设置。【说明一】：数据文件和日志文件的作用数据文件又包括：主数据文件和辅助数据文件。主数据文件的扩展名是.mdf，每个数据库只能

3、包含一个主数据文件。辅助数据文件的扩展名是.ndf。辅助数据文件可以同主数据文件存放在相同的位置，也可以存放在不同的地方。日志文件记录页的分配和释放以及对数据库数据的修改操作。日志文件的扩展名为.ldf，它包含用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。【说明二】：数据库文件的属性在定义数据库的数据文件和日志文件时，可以指定如下属性： 文件名及其位置。每个数据库的数据文件和日志文件都具有一个逻辑名称以及文件的物理存放位置。 文件大小。可以指定每个数据文件和日志文件的大小，以MB为单位。 增长方式。如果需要的话，可以指定文件是否自动增长，该选项的默认配置为自动增长。 最大大小。指

4、定文件增长的最大大小。默认是大小无限制。2) 使用Transact-SQL语句创建数据库1 语法结构 CREATE DATABASE 数据库名 ON , n LOG ON , n := ( NAME = 逻辑文件名, FILENAME = 操作系统下的物理路径和文件名 , SIZE = 文件初始大小 , MAXSIZE = 文件最大大小 | UNLIMITED , FILEGROWTH = 增量值 ) , n2 有关说明 ON关键字表示数据库是根据后面的参数来创建的； n是一个占位符，表明可为新数据库指定多个文件； LOG ON 子句用于指定该数据库的事务日志文件； NAME用于指定数据库文件

5、的逻辑文件名； FILENAME用于指定数据库文件的存放位置及在磁盘上的文件名； SIZE用于指定数据库文件的初始大小，可以加上MB或KB，默认为MB； MAXSIZE用于指定数据库文件的最大大小，可以加上MB或KB，默认为MB。省略此项表示最大大小无限制； FILEGROWTH用于指定数据库文件的增加量，可以加上MB或KB或%，默认为MB。省略此项表示不自动增长。3 实验内容创建一个数据库，数据库名称为你的学号，例如：“AP0406150”，此数据库包含一个数据文件和一个事务日志文件。数据文件只有主数据文件，其逻辑文件名为“AP0406150_data”，其物理文件名为“AP0406150.

6、mdf”，存放位置在默认目录下，其初始大小为10MB，最大大小为30MB，自动增长时的递增量为5MB。事务日志文件的逻辑文件名为“AP0406150_log”，物理文件名为“AP0406150.ldf”，也存放在默认目录下，初始大小为3MB，最大大小为12MB，自动增长时的递增量为2MB。【注意】： 上机时要根据实际情况确定存放位置，由于计算中心的大多数机器的C、D驱动器有保护卡，建议在驱动器E中建立自己的目录，有关文件将存放在该目录中。4 语句格式CREATE DATABASE AP0406150ON ( NAME = AP0406150_data, FILENAME = E:AP04061

7、50AP0406150.mdf , SIZE = 10, MAXSIZE = 30, FILEGROWTH = 5 )LOG ON( NAME = AP0406150_log, FILENAME = E: AP0406150AP0406150.ldf , SIZE = 3, MAXSIZE = 12, FILEGROWTH = 2 )2创建基本表点击屏幕左上角的“新建查询”，选择操作数据库，在查询界面中输入以下数据定义语句后，按“执行”按钮，即建立三个基本表： 学生表：Student（Sno，Sname，Ssex，Sage，Sdept）其中：属性Sno，Sname，Ssex，Sage和Sdep

8、t分别表示学号、姓名、性别、年龄和所在系，Sno为主码，加下划线表示。CREATE TABLE Student（Sno CHAR(9) PRIMARY KEY，Sname CHAR(20) UNIQUE，Ssex CHAR(2)，Sage CHAR(3),Sdept CHAR(20)； 课程表：Course（Cno，Cname，Cpno，Ccredit）其中：属性Cno，Cname，Cpno和Ccredit分别表示课程号、课程名、先修课号和学分，Cno为主码，加下划线表示。CREATE TABLE Course（Cno CHAR(4) PRIMARY KEY，Cname CHAR(40) ，C

9、pno CHAR(4)，Ccredit SMALLINT，FOREIGN KEY (Cpno) REFERENCES Course(Cno)； 学生选课表：SC（Sno，Cno，Grade）其中：属性Sno，Cno和Grade分别表示学号、课程号和成绩，主码为属性组（Sno，Cno），加下划线表示。CREATE TABLE SC（Sno CHAR(9)，Cno CHAR(4) ，Grade SMALLINT，PRIMARY KEY (Sno，Cno)，FOREIGN KEY (Sno) REFERENCES Student(Sno)，FOREIGN KEY (Cno) REFERENCES C

10、ourse(Cno)；3修改基本表 在Student表中增加“入学时间”列，数据类型为日期型ALTER TABLE StudentADD S_entrance DATETIME； 将年龄的数据类型由字符型改为整型，因SQL SERVER2000没有MODIFY功能，只能先删除再添加，即：ALTER TABLE Student /先删除DROP COLUMN Sage;ALTER TABLE Student /再添加ADD Sage INT; 删除Student表中的“入学时间”列ALTER TABLE StudentDROP COLUMN S_entrance； 在Student表中增加一个完

11、整性约束定义，使年龄的取值只能在15到40之间ALTER TABLE StudentADD CHECK(Sage BETWEEN 15 AND 40);4建立索引为学生-课程数据库中的Student、Course、SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno) ;CREATE UNIQUE INDEX Coucno ON Course(Cno) ;CREATE UNIQUE INDEX SCno ON SC(Sno A

12、SC,Cno DESC) ;5取消索引取消按步骤4建立的索引。DROP INDEX Student.Stusno; / DROP INDEX 表名.索引名DROP INDEX Course.Coucno ;DROP INDEX SC.SCno ;实验二 数据更新语言的使用一、实验目的：通过本实验熟悉数据库的数据更新操作，加深SQL语言中对数据库进行数据插入、更新、删除操作的理解。二、实验内容：使用SQL Server2008软件，练习SQL语言中的插入、更新、删除命令。三、实验要求：使用数据插入语句INSERT INTO向实验一所建立的基本表中输入若干数据，然后再通过UPDATE或DELETE

13、语句对已经插入的数据进行更新或删除，每执行一次操作都将通过查询命令SELECT语句查看数据变化。 四、实验学时：2学时五、实验步骤：1插入内容 学生表：StudentSnoSnameSsexSageSdept200215121 李勇 男 19CS200215122 刘晨 男 20CS 200215123王敏 女 20MA 200215125张立 男 22IS 课程表：CourseCnoCnameCpnoCcredit1 数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言 64学生选课表：SCSnoCnoGrade200215121192200215121

14、2852002151213882002151222902002151223802插入数据使用insert语句将给定数据分别插入上述基本表中：INSERT INTO Student VALUES(200215121,李勇,男 ,19,CS); INSERT INTO Student VALUES(200215122,刘晨,男 ,20,CS); INSERT INTO Student VALUES(200215123,王敏,女 ,20,MA); INSERT INTO Student VALUES(200215124,王敏乙,女 ,20,MA); INSERT INTO Student VALUE

15、S(200215125,张立,男 ,22,IS); 【注意】：用SELECT命令观察结果。INSERT INTO Course(Cno,Cname,Ccredit)VALUES(2,数学,2); INSERT INTO Course(Cno,Cname,Ccredit)VALUES(6,数据处理,2); INSERT INTO Course VALUES(4,操作系统,6 ,3); INSERT INTO Course VALUES(7,PASCAL语言,6 ,4); INSERT INTO Course VALUES(5,数据结构,7 ,4); INSERT INTO Course VALU

16、ES(1,数据库,5 ,4); INSERT INTO Course VALUES(3,信息系统,1 ,4); 先按课程号顺序向Course表中插入记录，再按上述顺序向Course表插入记录，体会两者的不同，并用SELECT命令观察结果。INSERT INTO SC(Sno,Cno) VALUES(200215121,1);INSERT INTO SC(Sno,Cno,Grade) VALUES(200215121,2,85);INSERT INTO SC VALUES(200215121,3,88);INSERT INTO SC VALUES(200215122,2,90);INSERT I

17、NTO SC VALUES(200215122,3,80);3修改数据给学号为200215121学生录入课程号为1（数据库）的成绩。UPDATE SC SET Grade = 92WHERE Sno=200215121 and Cno = 1;4删除数据删除学号为200215124的学生记录。DELETE FROM Student WHERE Sno=200215124;实验三 数据查询语言的使用一、实验目的：通过本实验加深对SQL语句中数据查询语言的理解，能够使用SQL语句对数据库进行单表查询、连接查询、嵌套查询、集合查询和统计查询。二、实验内容：使用SQL Server 2008软件，练习

18、SQL语言中的查询命令。三、实验要求：结合实验二中有关数据，按要求完成给定的查询操作。四、实验学时：4学时五、实验步骤：1 单表查询 查询全体学生的学号与姓名 SELECT Sno，Sname FROM Student； 查询全体学生的详细记录 SELECT Sno，Sname，Ssex, Sage, Sdept FROM Student；SELECT * FROM Student； 查询全体学生的姓名及其出生年份 SELECT Sname，2006 - Sage FROM Student； 将学生按年龄的升序排序。 SELECT * FROM Student ORDER BY Sage 查询

19、全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept, Sage DESC 查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept=CS AND Sage90; 查询每个学生的学号、姓名、选修的课程及成绩。SELECT Student.Sno, Sname，Cname,GradeFROM Student,SC,CourseWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno; 3 嵌套查询

20、 查询与“刘晨”在同一个系学习的学生。 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname = 刘晨)； 查询选修了“数据库”课程的学生的学号、姓名。 SELECT Sno, Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname = 数据库) )；4 集合查询 查询选修了课程1或者选修了课程2的学生。SELECT S

21、no FROM SCWHERE Cno =1UNIONSELECT Sno FROM SCWHERE Cno =2；5 统计查询 统计学生总人数。 SELECT COUNT（*） FROM Student； 统计选修了课程的学生的人数。 SELECT COUNT （DISTINCT Sno） FROM SC； 计算200215121号学生的考试成绩之和。 SELECT SUM(Grade) FROM SC WHERE Sno = 200215121； 计算1号课程学生的考试平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno=1； 查询选修了1号课程的学生的最高分

22、和最低分。 SELECT MAX(Grade) , MIN(Grade) FROM SC WHERE Cno=1； 查询修了3门以上课程的学生的学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3； 统计每门课程的选课人数，列出课程号和人数。 SELECT Cno as 课程号, COUNT(Sno) as 选课人数 FROM SC GROUP BY Cno；实验四 视图的定义与使用一、实验目的：通过本实验熟悉SQL支持的有关视图操作，能够熟练使用SQL语句来创建需要的视图、对视图进行查询和取消视图等。二、实验内容：使用SQL Server

23、 2008软件，练习通过SQL语言创建视图、对视图进行查询和取消视图的操作。三、实验要求：结合实验二中有关数据，按要求完成有关操作。四、实验学时：2学时五、实验步骤：1建立视图 建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept = IS 建立信息系学生的视图，并要求进行插入、修改操作时还需保证该视图中只有信息系学生。 CREATE VIEW IS_Student1 AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept =

24、IS WITH CHECK OPTION 建立信息系选修了c01号课程的学生的视图。 CREATE VIEW V_IS_S1(Sno, Sname, Grade) AS SELECT Student.Sno, Sname, Sage FROM Student，SC WHERE Student.Sno = SC.Sno AND Sdept = IS AND SC.Cno = c01 建立信息系选修了c01号课程且成绩在90分以上的学生的视图。 CREATE VIEW V_IS_S2 AS SELECT Sno, Sname, Grade FROM V_IS_S1 WHERE Grade = 90

25、 定义一个反映学生出生年份的视图。 CREATE VIEW BT_S(Sno, Sname, Sbirth) AS SELECT Sno, Sname, 2006-Sage FROM Student 定义一个存放每个学生的学号及平均成绩的视图。 CREATE VIEW S_G(Sno, AverageGrade) AS SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno 2查询视图（根据视图进行查询） 根据视图IS_Student，查询信息系学生的信息。 SELECT * FROM IS_Student； 根据视图V_IS_S2，查询信息系选修了c01号课程

26、且成绩在90分以上的学生。SELECT * FROM V_IS_S2； 在信息系学生的视图中找出年龄小于20岁的学生。 SELECT Sno,Sage FROM IS_Student WHERE Sage 20；3更新视图 将信息系学生视图IS_Student中学号为200215122的学生姓名改为刘辰。 UPDATE IS_Student SET Sname = 刘辰 WHERE Sno = 200215122；4删除视图 删除IS_Student视图。DROP VIEW IS_Student；实验五 数据控制语言的定义与使用一、实验目的：通过本实验熟悉SQL的数据控制功能，能够熟练通过企业

27、管理器或使用SQL语句来向用户授予和收回权限。二、实验内容：（1） 使用GRANT语句对用户授权：对单个用户或多个用户授权、使用保留字PUBLIC对所有用户授权。有关操作对象包括数据库、视图、基本表等。（2） 使用WITH GRANT OPTION子句授予用户传播该权限的权利。（3） 使用REVOKE子句收回授权。【注意】：在SQL SERVER 2008中，权限分为：（1） 对象权限：是指用户对数据库中的表、视图等对象的操作权，用来设置是否允许查询、增加、删除和修改数据等。（2） 语句权限：用来设置是否允许执行CREATE TABLE、CREATE VIEW等与创建数据库对象有关的操作。（3） 隐含权限：是指由SQL SERVER预定义的服务器角色、数据库角色、数据库拥有者和数据库对象拥有者所具有的权限。三、实验要求：结合所建立的数据库和表，按要求完成向用户授予和收回权限的操作，在完成授权或收回权限操作后，再按该用户登录数据库，并进行有关操作，观察操作权限的变化。四、实验学时：2学时五、实验步骤：1 建立用户在所建立的数据库中建立五个用户：USER1、USER2、USER3、USER4、USER