《数据库系统概论》实习指导书

1、中国地质大学计算机学院数据库系统概论实习指导书编制:张夏林2010年11月数据库系统概论实习一、实习目的与任务通过实习教学，使学生进一步加深理解数据库系统的基本理论，学会数据库设计方法、dbms的使用，数据库系统的管理和维护，熟悉数据库技术的应用。二、实习室要求装有win2000及sql server 2000的计算机。三、主要教材及参考书教材：数据库系统概论（第四版） 王珊，萨师煊 高等教育出版社 参考书目： sql server 图解教程 北京希望电子出版社；sql server 2000简明教程，清华大学出版社。四、考核形式 1、平时实习完成情况(实习报告质量) 20% 2、每人独立开发

2、一套数据库应用系统 80%五、实习项目与要求序号实习项目名称时数目的及要求实习类型1sql server 2000基本操作4了解sql server2000安装过程，掌握软件的组成，启动，运行。在图形界面下建立数据库及基本数据表，及其表的定义、删除与修改操作。认识2sql 语言综合实习4掌握使用sql语言创建删除数据库，建立基本数据表，建立索引，更新数据；掌握sql的数据各种查询；掌握连接、嵌套和集合查询综合3数据库应用系统4提高学生数据库系统的综合设计和开发应用能力，解决实际问题设计实习一实习名称： sql server 2000基本操作实习主要内容及方法： 数据表的定义、删除及修改实习要求

3、：（1） 了解并掌握sql企业管理器的使用;（2） 掌握在图形界面方式下的数据创建，基本表的定义、删除与修改。（3） 掌握sqlserver的基本功能和使用。实习目的：了解sql server2000安装过程，掌握软件的组成，启动，运行。在图形界面下建立数据库及基本数据表，及其表的定义、删除与修改操作。为后继学习作准备。实习设备：装有sqlserver2000的电脑实习步骤：(1)启动sql server企业管理器；(2)新建数据库 studydb；(3) 新建数据表，修改，删除；(4) 数据录入：建议的数据表样式表3.3 关系studentssnosnamessexsagesdepts01s

4、02s03s04s05s06王建平刘华范林军李伟黄河长江男女女男男男211918191820自动化自动化计算机数学数学数学表3.4 关系coursescnocnamepre_cnocreditsc01c02c03c04c05c06c07英语数据结构数据库db_设计c+网络原理操作系统c05c02c03c07c054223333表3.5 关系reportssnocnogrades01s01s02s02s02s03s03s04c01c03c01c02c03c01c02c039284909482729075操作参考资料：sql server 2000看图教程(pdf) 中 sql01,03sql s

5、erver 2000简明教程_03,04实习二实习名称： sql综合应用实习主要内容及方法： sql语言的综合应用 首先验证性试验教材中所有sql 语句的实例，然后选择性试验本指导书中的示例。实习要求：（1） 了解并掌握sql查询分析器及企业管理器的使用。（2） 掌握用sql语言对基本表的定义、删除与修改。教材82页数据表的定义。（3） 掌握sql数据插入、修改和删除语句的一般格式及使用方法。教材82页数据表数据的插入。（4） 掌握建立索引的二种方法，即在基本表创建过程中建立和用命令方式建立。（5） 掌握sql 查询语句的一般格式;掌握无条件、有条件查询及查询结果排序与分组。（6） 掌握连接、

6、嵌套和集合查询语句的一般格式及使用方法。（7） 掌握sql视图建立、修改和删除。（8） 边实习边写实习报告，将实习过程和实习中采用的语句，运行结果，出现的问题及解决办法都写到实习报告中。实习目的：熟练掌握sql语句的使用方法，为后继学习作准备。实习设备：装有sqlserver2000的电脑实习步骤：（1） 启动sql查询分析器；（2） 编写sql语句完成实习要求的各项内容；（3） 编写实习报告。建议的基本表样式表3.3 关系studentssnosnamessexsagesdepts01s02s03s04s05s06王建平刘华范林军李伟黄河长江男女女男男男211918191820自动化自动化计

7、算机数学数学数学表3.4 关系coursescnocnamepre_cnocreditsc01c02c03c04c05c06c07英语数据结构数据库db_设计c+网络原理操作系统c05c02c03c07c054223333表3.5 关系reportssnocnogrades01s01s02s02s02s03s03s04c01c03c01c02c03c01c02c039284909482729075用sql语言对基本表的定义、删除与修改。1 定义基本表例3.1 建立表3.3所示的学生表students，每个属性名的意义为sno-学号、sname-姓名、ssex-性别、sage-年龄、sdept-

8、所在系。这里要求sno和sname不能为空值，且取值唯一。create table students /*列级完整性约束条件*/ (sno char(5) not null, /* sno不能为空值*/ sname char(20) not null, /*sname不能为空值*/ ssex char(2), sage int, sdept char(15), constraint un_sno unique(sno), /* sno取值唯一的约束*/ constraint un_sname unique(sname); /* sname取值唯一的约束*/说明：在microsoft sql s

9、erver 2000的查询分析器(query analyzer)中使用单条sql语句，其末尾不需要分号“；”作为命令结尾标记。通常，sql server 2000对大多数末尾带有分号的sql命令都能顺利执行，但对少数的sql命令，末尾若带分号，则sql server 2000会给出错误信息提示。 比如，若在例3.59的sql命令末尾加上一个分号“；”，sql server 2000就会出现“incorrect syntax near ;”的提示，虽然sql server 2000实际上已经执行了该命令。例3.1-1 建立表3.4所示的课程表courses，其属性名意义分别为cno-课程号, c

10、name-课程名, pre_cno-先修课程号, credits-学分。create table courses (cno char(5) not null, /* cno不能为空值*/ cname char(20) not null, /*cname不能为空值*/ pre_cno char(5), credits int, constraint un_cno unique(cno); /*cno取值唯一的约束*/例3.1-2 建立表3.5所示的成绩表reports。其中的属性名意义分别为sno-学号，cno-课程号和grade-考试成绩。 create table reports ( sno

11、 char(5) not null, /* sno不能为空值*/ cno char(5) not null, /* cno不能为空值*/ grade int, constraint sno_cno unique(sno,cno); /*sno+cno取值唯一的约束*/2 修改基本表例3.2 向基本表students中增加“入学时间”属性列，其属性名为sentrancedate，数据类型为datetime型。alter table students add sentrancedate datetime; 例3.3 将sage(年龄)的数据类型改为smallint型。alter table stu

12、dents alter column sage smallint;例3.4 删除sname(姓名)必须取唯一值的约束。alter table students drop constraint un_sname;注意：sql server 2000 增加了删除属性的命令。比如，删除属性列sentrancedate的命令为：alter table students drop column sentrancedate;说明： 为了保证后面例子能够顺利运行，请大家一定将属性列sentrancedate从students表中删除。 为了调试sql语句方便，这里没有在表reports中增加参照完整性约束，

13、甚至没有定义主键。等本章学完后，第7章的实习系统就是把这些约束全部加上了。3 删除基本表例3.5 删除students表。drop table students;说明：此表删除后，请立即用例3.1将其建立起来，以便后面的例子使用。4 向表中添加元组例3.6 将一个学生元组(s01，王建平，男，21，计算机)添加到基本表students中。insertinto studentsvalues (s01,王建平,男,21,自动化);说明： 请读者用这个命令将其余5个学生的元组也添加到基本表students中。 向courses表插入元组(c01,英语,4)的命令为：insertinto course

14、svalues (c01,英语,4);请大家将其余6门课程的信息插入courses表中。例3.7 将学习成绩的元组(s01, c01)添加到基本表reports中。insertinto reports(sno, cno)values (s01,c01);说明：请大家用这个命令将其余7个选课元组也添加到基本表reports中.建立索引1 建立索引例3.8 为学生选课数据库中的students，courses，reports三个表建立索引。其中students表按sno(学号)升序建唯一索引，courses表按cno(课程号)升序建唯一索引，reports表按sno(学号)升序和cno(课程号)号

15、降序建唯一索引。其语句为：create unique index stu_sno on students(sno);create unique index cou_cno on courses(cno);create unique index rep_scno on reports(sno asc, cno desc);例3.9 在基本表students的sname(姓名)和sno(学号)列上建立一个聚簇索引，而且students中的物理记录将按照sname值和sno值的升序存放。其语句为：create clustered index stu_sname_sno on students(sna

16、me, sno);2 删除索引例3.10 删除基本表reports上的rep_scno索引。drop index reports.rep_scno;sql查询1 无条件查询例3.11 查询全体学生的详细记录。这是一个无条件的选择查询，其命令为：select * /*这里的“*”等价于all*/from students;其结果为表3.3中的全部数据。例3.12 查询全体学生的姓名(sname)、学号(sno)、所在系(sdept)。这是一个无条件的投影查询，其命令为：select sname, sno, sdeptfrom students;例3.13 查询全体学生的姓名(sname)、出生年

17、份及学号(sno)。由于select子句的不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。其命令为：select sno, sname, 2001-sagefrom students;例3.14 查询全体学生的姓名、出生年份和学号，要求用小写字母表示学号中的字母。其命令为：select sname, birth: title, 1996-sage birthyear, lower(sno) lsnofrom students;例3.15 查询选修了课程的学生学号。其命令为：select distinct snofrom reports;2 条件查询例3.16 查询数学系全体学生

18、的学号(sno)和姓名 (sname)。其命令为：select sno, snamefrom studentswhere sdept=数学;例3.17 查询所有年龄在1822岁(包括18岁和22岁)之间的学生姓名(sname)及年龄(sage)。其命令为：select sname, sagefrom studentswhere sage=18 and sage连接、嵌套和集合查询一、连接查询1 不同表之间的连接查询例3.37 查询每个学生及其选修课程的情况。本查询实际上是涉及students与reports两个表的连接操作。这两个表之间的联系是通过公共属性sno实现的，因此，其操作命令为：se

19、lect students.*, reports.*from students, reportswhere students.sno = reports.sno； 说明：若在以上等值连接中把目标列中重复的属性列去掉则为自然连接，其命令为select students.sno, sname, ssex, sage, sdept, cno, gradefrom students, reportswhere students.sno= reports.sno;例3.38 查询每个学生的学号(sno)、姓名(sname)、选修的课程名(cname)及成绩(grade)。本查询涉及到三个表的连接操作，完

20、成该查询的sql语句如下：select students.sno, sname, cname, gradefrom students, reports, courseswhere students.sno= reports.sno and reports.cno=courses.cno;2 自身连接例3.39 查询每一门课的间接先修课(即先修课的先修课)。在courses表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。这就需要要将courses表与其自身连接。为方便连接运算，这里为courses表取

21、两个别名分别为a，b。则完成该查询的sql语句为：select a.cno, a.cname, b.pre_cnofrom courses a, courses bwhere a.pre_cno =b.cno;3 外连接例3.40把例3.37中的等值连接改为左连接。该左连接操作在sql server 2000中的命令格式为：select students.sno, sname, ssex, sdept, cno, gradefrom studentsleft join reports onstudents.sno= reports.sno;说明：以上左连接操作也可以用如下的右连接操作代替，其结

22、果完全一样。select students.sno, sname, ssex, sdept, cno, gradefrom reportsright join students onreports.sno=students.sno;二、嵌套查询1 带谓词in的嵌套查询例3.41 查询选修了编号为“c02”的课程的学生姓名(sname)和所在系(sdept)。select sname, sdeptfrom studentswhere sno in (select sno from reports where cno=c02);例3.42 查询与“李伟”在同一个系学习的学生学号(sno)、姓名(s

23、name)和系名(sdept)。该查询可构造嵌套查询实现，其sql语句如下：select sno, sname, sdeptfrom studentswhere sdept in (select sdept from students where sname=李伟);说明：本例中的查询也可以用自身连接来完成，其sql语句如下：select a.sno , a.sname , a.sdeptfrom students a , students bwhere a.sdept=b.sdept and b.sname=李伟;例3.43 查询选修了课程名为“数据结构”的学生学号(sno)和姓名(snam

24、e)。本查询涉及学号、姓名和课程名(cname)三个属性。学号和姓名存放在students表中，课程名的存放在courses表中，但students与courses两个表之间没有公共属性，必须通过reports表建立它们之间的联系。所以本查询实际上涉及三个关系的连接操作。select sno, sname /* 最后在studenst关系中 */from students /* 取出sno和sname */where sno in (select sno /* 然后在sc关系中找出 */ from reports /*选修了3号课程的学生学号*/ where cno in (select cn

25、o /* 首先在courses关系中 */ from courses /*找出“数据结构”的课程号，*/ where cname = 数据结构)； /*结果为c02号 */说明：本查询同样可以用连接查询实现：select s.sno, snamefrom students s, reports r, courses cwhere s.sno=r.sno and r.cno=c.cno and c.cname=数据结构;2 带有比较运算符的嵌套查询例3.44 将例3.42改为带有比较运算符的嵌套查询。由于一个学生只可能在一个系学习，因此子查询的结果是一个值，因此可以用=代替in，其sql语句如下

26、：select sno , sname, sdeptfrom studentswhere sdept = (select sdept from students where sname=李伟);3 带谓词any或all的嵌套查询例3.45 查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(sname)和年龄(sage)。其查询命令为select sname, sagefrom studentswhere sdept自动化 and sage=all (select sage from students where sdept= 自动化);说明：本查询也可以用集函数来实现。其sql语句如下：

27、select sname, sagefrom studentswhere sdept自动化 and sage= (select min(sage) from students where sdept=自动化);4 带谓词exists的嵌套查询例3.46 查询所有选修了编号为“c01”课程的学生姓名(sname)和所在系(sdept)。本查询的sql语句是：select sname, sdeptfrom studentswhere exists (select * from reports where sno=students.sno and cno=c01);例3.47 将例3.42改为带谓词

28、exists的查询，其sql语句如下select sno, sname, sdeptfrom students awhere exists (select * from students b where b.sdept=a.sdept and b.sname=李伟);例3.48 查询选修了所有课程的学生姓名(sname)和所在系。由于没有全称量词，可将题目的意思转换成等价的用存在量词的形式：查询这样的学生，没有一门课程是他不选修的。其sql语句为：select sname, sdeptfrom studentswhere not exists (select * from courses wh

29、ere not exists (select * from reports where sno=students.sno and cno=courses.cno);三、集合查询例3.49 查询计算机科学系的学生或年龄不大于20岁的学生信息。select *from studentswhere sdept=计算机unionselect *from studentswhere sage=20;例3.50 查询数学系的学生且年龄不大于20岁的学生的交集，这实际上就是查询数学系中年龄不大于20岁的学生。select *from studentswhere sdept=数学 and sage20;sql

30、的数据更新1 插入数据例3.52 设数据库中已有一个关系history_student，其关系模式与students完全一样，试将关系students中的所有元组插入到关系history_student中去，其sql命令为：insertinto history_studentselect *from students;2 修改数据例3.53 将学号为“s03”的学生年龄改为22岁，即要修改满足条件的一个元组的属性值。update studentsset sage=22where sno=s03;例3.54 将所有学生的年龄增加1岁。即要修改多个元组的值。update studentsset s

31、age=1+sage;例3.55 将数学系所有学生的成绩置零。由于学生所在系的信息在students表中，而学习成绩在reports表中，因此，可以将select子查询作为where子句的条件表达式。故该更新要求的sql命令为：update reportsset grade=0where 数学=(select sdeptfrom studentswhere students.sno=reports.sno);3 删除数据例3.56 删除学号为“s04”的学生选修的课号为“c02”的记录。deletefrom reportswhere sno=s04 and cno=c02;例3.57 删除所有

32、学生的选课记录。deletefrom reports;这条delete语句将删除reports的所有元组，使reports成为空表。例3.58 删除数学系所有学生的选课记录。deletefrom reports where 数学=(select sdeptfrom studentswhere students.sno=reports.sno); sql视图一 定义视图1 建立视图例3.59 建立数学系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为sno，sname，sage，sdept。create view c_studentasselect sno,

33、sname, sage, sdept from studentswhere sdept=数学with check option例3.60 建立学生的学号(sno)、姓名(sname)、选修课程名(cname)及成绩(grade)的视图。本视图由三个基本表的连接操作导出，其sql语句如下：create view student_crasselect students.sno, sname, cname, gradefrom students, reports, courseswhere students.sno= reports.sno and reports.cno=courses.cno例3

34、.61 定义一个反映学生出生年份的视图。create view student_birth(sno, sname, sbirth)as select sno, sname, 1996-sagefrom students2 删除视图例3.62 删除视图student_cr。drop view student_cr;二 查询视图例3.63 在数学系的学生视图c_student中找出年龄(sage)小于20岁的学生姓名(sname)和年龄(sage)。select sname, sagefrom c_studentwhere sage20;说明：本例转换后的查询语句为：select sname, s

35、age from studentswhere sdept=数学 and sage85; 三 更新视图例3.65 将数学系学生视图c_student中学号为s05的学生姓名改为“黄海”。update c_studentset sname=黄海where sno=s05;说明：dbms自动转换为对基本表的更新语句如下：update studentsset sname=黄海where sno=s05 and sdept=数学;例3.66 向数学系学生视图c_student中插入一个新的学生记录，其中学号为“s09”，姓名为“王海”，年龄为20岁。insertinto c_studentvalues

36、(s09, 王海, 20, 数学);例3.67 删除数学系学生视图c_student中学号为“s09”的记录。deletefrom c_studentwhere sno=s09参考实习 在powerbuilder中的嵌入式sql语句说明：本节的例子必须在powerbuilder环境中才能运行，不懂powerbuilder的初学者没有必要对其做上机实习。以下这些材料对制作多媒体教案有用。1 检索单行数据的select语句例3.76 查询学号为“s01”的学生选修课程号“c01”课程的考试成绩。string xno,kno /powerbuilder 声明两个字符串变量，分别存放学号和课程号int

37、 cgrage / powerbuilder 声明一个整型变量，用于存放学习成绩select sno,cno,grade /本行开始的4行为一条完整的嵌入式sql语句into :xno, :kno, :cgrage /sql中引用powerbuilder的主变量前要加冒号 from reports where sno=s01 and cno=c01; /powerbuilder使用sql时以分号结束以上程序段将select取得的一条记录赋值给主变量xno, kno和cgrage。2 insert语句例3.77 向表reports中添加一条记录（s12,c05,87）。insert into r

38、eports(sno,cno,grade) values (s12,c05,87)；或者string xno,kno /powerbuilder 声明两个字符串变量，分别存放学号和课程号int cgrage / powerbuilder 声明一个整型变量，用于存放学习成绩xno=s12 /将欲插入的值赋给主变量xno, kno和cgragekno=c05cgrage=87insert into reports(sno,cno,grade) /将主变量xno, kno和cgrage的值插入表中values (:xno, :kno, :cgrage)；3 update语句例3.78 将表“repo

39、rts”中所有成绩低于90的学生加5分。update reports set grade=grade+5 where grade90；4 delete语句例3.79 删除“reports”表中所有选“c03”课程的学生信息。delete from reports where cno=c03；5 游标的使用例3.80 查询关系reports中的grade信息，如果grade的值小于85，则将其在原来基础上增加5，否则将该记录删除。这个更新操作可以通过使用游标的以下程序实现。int cgrade /声明一个整型变量，作为sql的主变量declare report_cur cursor for /声

40、明游标report_curselect grade /并将查询结果数据集相联系 from reports;open report_cur; /打开游标report_curfetch report_cur into :cgrade; /读取游标的第一条记录，并存入主变量do while sqlca.sqlcode = 0 /循环结构开始，若读取成功则进入循环 if cgrade85 then /若读出的该记录成绩(cgrade)低于85分 update reports /则该记录的成绩(grade)增加85分 set grade = grade+5 where current of report

41、_cur; /这是current型update语句else /否则删除该记录 delete from reports where current of report_cur;end if fetch report_cur into :cgrade; /读取游标的下一条记录loop /循环结构结束符close report_cur; /关闭游标report_cur二 在delphi中使用sql语句说明：本节的例子必须在delphi环境中才能运行，不懂delphi的初学者没有必要对其做上机实习，第7章有一个完整的应用系统例子供上机实习之用。以下这些材料对制作多媒体教案有用。本节后面每个例子都需要的

42、-步语句定义如下，在后面的例子中不再给出。myquery: =tquery.create(application); /声明并创建一个tquery类的控件myquerymyquery.databasename :=student; / myquery控件要访问的数据库别名为student 1检索单行数据的select语句例3.81 查询学号为“s01”的学生选修课程号“c01”课程的考试成绩，请与例3.76比较。varxno:ansistring; kno:ansistring; /delphi 声明两个字符串变量，分别存放学号和课程号cgrage:integer /delphi 声明一个整型变量，用于存放学习成绩myquery.sql.add(select sno,cno,grade /指定myquery控件使用的sql语句from reports where sno=s01 and cno=c01);myquery.open; /执行myquery控件中的sql语句xno:=myquery.fieldb