数据库原理和应用实验指导书.docx

1、数据库原理和应用实验指导书4新建表(1)使用企业管理器建立student、course和SC三个表，其结构为：Student(学生)列名描述数据类型允许空值说明Sno学号Varchar(20)No主键Sname姓名Varchar(50)NoAge年龄IntYESSex性别char(2)YESDept所在系Varchar(50)YESCourse(课程)列描述数据类型允许空值说明cno课程号Varchar(20)No主键cname课程名Varchar(50)Nocredit学分FloatYESpcno先行课Varchar(20)YESdescribe课程描述varchar(100)YESSC(选

2、课)列描述数据类型允许空值说明sno学号Varchar(20)No主键（同时都是外键）cno课程号Varchar(20)Nograde成绩FloatYES在SQL Server 2000 的数据库中，文件夹是按数据库对象的类型建立的，文件夹名是该数据库对象名。当在企业管理器中选择服务器和数据库文件夹，并打开己定义好的学生选课数据库后。会发现它自动设置了关系图、表、视图、存储过程、用户、角色、规则、默认等文件夹。要建立“student”表，先选中学生选课数据库中的表文件夹，单击鼠标右键，在弹出的菜单中选择“新建表”，随后的输入表结构对话框。输入表结构对话框是一张表，它的列属性有列名、数据类型、长

3、度和是否允许空4 项。用户把新建表的结构填入对话框的表中，表中的每一行定义新建表（Course）的一列，每一列定义新建表的一个列属性。当光标移到表中的某一行时，下面的列描述就会对应当前行显示输入项，用户可在其中对关系的属性进行进一步说明。列描述包括数据的精度、小数位数、默认值、是否标识等项。 (2)使用T-SQL语句建立student,course,SC三个表Create Table student(sno varchar(20) primary key ,Sname varchar(50) not null,Age int,Sex char(2),Dept varchar(50); Crea

4、te table course(cno varchar(20) primary key,Cname varchar(50) not null,Credit float not null,Pcno varchar(20) not null,Describe varchar(100) not null);Create table SC(sno varchar(20),Cno varchar(20),Grade float,primary key (sno,cno),foreign key (sno) REFERENCES student(sno),foreign key (cno) REFEREN

5、CES course(cno);5定义表的完整性约束和索引表的约束包括码（主键）约束、外键约束（关联或关系约束）、惟一性约束、Check （检查）约束4 种。这些约束可以在表属性对话框中定义。(l）定义索引和键选择“索引键”页面，其界面如图10 所示。l）查看、修改或删除索引时，先要在“选定的索引”下拉列表框中选择索引名，其索引内容就显示在表中。需要时，可以直接在表中修改索引内容。如改变索引列名、改变排序方法等。对于不需要的索引可以单击“删除”按钮，直接删除此索引。2）新建一个索引时，单击“新建”按钮，并在下面的表中输入索引名、索引列名及排列顺序；3）设置UNIQUE 复选框，确定是否为惟一索

6、引约束。设置CLUSTERED 复选框，确定是否为群集索引（CLUSTERED ）。(2）定义表间关联选择表页面，其界面如图12 所示。l）查看、修改或删除表关联时，先要在“选定的关系”下拉列表框中选择关联名（即关系名），其关联内容就显示在表中。需要时，可以直接在表中修改关联内容，例如改变主键、改变外键键等。对于不需要的关联可以单击“删除”按钮，直接删除此关联。2）新建一个关联时，单击“新建”按钮，选择库中的关联表（参照表）后，在表中输入关联名、主键和外键。3）设置“创建中检查现存数据”复选框，确定新建关联时是否对数据进行检查，要求符合外键约束；设置“对复制强制关系”复选框。确定在进行数据复制

7、时是否要符合外键约束：设置“对INSERT 和UPDATE 强制关系”复选框，确认在对数据插入和更新时，是否符合外键约束：设置“级联更新相关的字段”复选框和“级联删除相关的记录”复选框，确认被参照关系的主键位被修改时，是否也将参照表中的对应的外键值修改，而被参照关系的上码值被删除时，是否也将参照表中对应外键的记录删除。（3）定义CHECK 约束选择CHECK 约束页面。其界面如图13 所示。 图12 表属性对话框中的表关联页面 图13 表属性对话框中的约束页面1）查看、修改或删除CHECK 约束时，先要在“选定的约束”下拉列表框中选择约束名，其约束内容就显示在约束表达式框中。需要时，可以直接在

8、框中修改约束表达式。对于不需要的CHECK 约束可以按“删除”按钮，直接删除此约束。2）新建一个CHECK 约束时，单击“新建”按钮，并在表中输入约束名和约束表达式。3）设置“创建中检查现存数据”，确认在创建约束时是否对表中数据进行检查，要求符合约束要求；设置“对复制强制约束”复合框，确认对数据复制时是否要求符合约束条件；设置“对INSERT和UPDATE强制约束”，确认在进行数据插入和数据修改时，是否要求符合约束条件。6修改表结构(1)使用企业管理器修改表结构当需要对建好的表修改结构时，首先要在企业管理器中找到该表，用鼠标右击该表名，就会弹出一个菜单，如图14 所示。随后，在弹出的菜单中选择

9、“设计表”项，企业管理器会调出如图9 所示的建表对话框，用户可对原有内容进行修改。图14在弹出菜单中选择“设计表”项(2)使用T-SQL语句修改表结构可以使用Alter语句增加、删除或修改字段信息。例如为学生表中增年龄字段增加约束，限制年龄至少要15岁：ALTER TABLE student ADD CONSTRAINT AGE CHECK(AGE 15)例如在学生表中增加班集字段为字符型，长度为50：ALTER TABLE student ADD Class varchar(50) NULL例如修改学生表中的班集字段的长度为20：Alter Table student ALTER COLUM

10、N Class varchar(20)例如删除学生表中的班集字段：ALTER TABLE student DROP COLUMN Class 四、注意事项1建表中如果出现错误，应采用相应的修改结构或删除结构的方法。2注意数据库的主键、外键和数据约束的定义。五、思考题1 数据库中一般不允许更改主键数据。如果需要更改主键数据时，怎样处理？2 为什么不能随意删除被参照表中的主键。实验三 数据库的建立和维护实验本实验需要2 学时。一、实验目的要求学生熟练掌握使用Transact-SQL和SQL Server 企业管理器向数据库输入数据、修改数据和删除数据的操作。二、实验内容 （1）通过企业管理器，在数

11、据库的student、course和SC三个表中各输入10 条记录。要求记录不仅满足数据约束要求，还要有表间关联的记录。（2）通过企业管理器实现对学生选课数据库的student、course和SC三 个表中数据的插入、删除和修改操作。（3）通过企业管理器实现对学生选课库的数据增加、数据删除和数据修改操作。要求学生、课程和选课表中各有10 条以上的记录。snosnameAgesexdept001101张林18男计算机系001102程明18男计算机系001103王艳19女计算机系001104严平平20男计算机系001201王敏19女数学系001202王林 20女数学系001203李计21男数学系0

12、01204孙祥欣17男数学系001205孙研18男数学系001206孙小18男数学系cnocnameCreditpcnodescribe1310101计算机基础5可自学1310102C+程序设计41310101可自学1310206离散数学4可自学1310208数据结构41310102可自学1310209操作系统41310101可自学1310210微机原理51310101可自学1310212数据库原理41310102可自学1310301计算机网络31310102可自学1310302软件工程31310102可自学1310211图形学31310101可自学snocnoGrade00120313101

13、0188001203131020676001103131010162001103131010270001104131010191001104131010284001104131020695001201131010278001201131020678001201131010191三、实验步骤(1)使用企业管理器更新数据在Microsoft SQL server2000的企业管理器中，对表进行数据增、删、改操作非常简便。其操作方法是：1）选中服务器，展开数据库文件夹，进一步展开指定的数据库。单击表文件夹，找到需要更新数据的基本表。2）用鼠标右击要更新数据的表，会出现与表相关操作的弹出菜单，如图15

14、所示。在弹出菜单中选择“打开表”“返回所有行”。图15基本表的弹出菜单在该对话框中，数据以表格形式组织，每个字段就是表中的一列，每条记录是表中的一行。原有的记录已经在表格中，通过移动右边的滑块可查阅所有的记录。3）需要数据插入时，就在最后一条记录后输入一条记录。当鼠标点击其他行时，输入的记录会自动保存在表中。4）需要修改记录时，直接对表中已有记录的数据进行改动，用新值替换原有值。5）需要删除记录时，先用鼠标单击要删除行的左边灰色方块，使该记录成为当前行，然后按（Delete）键。为了防止误操作，SQL server 2000 将弹出一个警告框，要求用户确认删除操作，单击“确认”按钮即可删除记录

15、，也可通过先选中一行或多行记录，然后再按( Delete )键的方法一次删除多条记录。(2)使用T-SQL语句更新数据请给每个学生选修三门课，在期未时给每门课一个成绩。如张林同学选修了计算机基础这门课，期未的考试成绩为95分，SQL语句如下：Insert into SC(sno,cno) values (001101,1310101)Update SC set grade=95 where sno=001101 and cno=1310101四、注意事项1输入数据时要注意数据类型、主键和数据约束的限制。2数据更改和数据删除时要注意外键约束。五、思考题1数据库中一般不允许更改主键数据。如果需要更

16、改主键数据时，怎样处理？2为什么不能随意删除被参照表中的主键。实验四 数据库的简单查询和连接查询实验一、实验目的使学生掌握SQL Server 查询分析器的使用方法，加深对Transact-SQL语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据联结查询的操作方法。二、实验内容1简单查询操作该实验包括投影、选择条件表达、数据排序、使用临时表等。2连接查询操作该实验包括等值连接、自然连接、求笛卡儿积、一般连接、外连接、内连接、左连接、右连接和自连接等。三、实验步骤（1）简单查询实验1）用Transact-SQL语句表示下列操作，在学生选课库中实现其数据查询操作： 求数学系学生的学号和

17、姓名。 求选修了课程的学生学号。 求选修课程号为1310101的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。 求选修课程号为1310101的成绩在80-90 分之间的学生学号和成绩，并将成绩乘以系数0.8 输出。 求数学系或计算机系姓张的学生的信息。 求缺少了成绩的学生的学号和课程号。 （2）连接查询实验用Transact-SQL语句表示，并在学生选课库中实现下列数据连接查询操作： 查询每个学生的情况以及他（她）所选修的课程。 求学生的学号、姓名、选修的课程名及成绩。 求选修离散数学 课程且成绩为90 分以上的学生学号、姓名及成绩。 查询每一门课的间接先行课（

18、即先行课的先行课）。四、注意事项1查询结果的几种处理方式。2内连接、左外部连接和右外部连接的含义及表达方法。3输入SQL 语句时应注意，语句中均使用西文操作符号。五、思考题1如何提高数据查询和连接速度。2对于常用的查询形式或查询结果，怎样处理好？实验五 数据库的嵌套查询实验一、实验目的使学生进一步掌握SQL Server 查询分析器的使用方法，加深Transact-SQL 语言的嵌套查询语句的理解。二、实验内容在SQL Server 查询分析器中使用IN 、比较符、ANY 或ALL 和EXISTS 操作符进行嵌套查询操作。三、实验步骤用Transact-SQL语句表示，在学生选课库中实现其数据

19、嵌套查询操作。（l）求选修了离散数学的学生学号和姓名。SELECT S#,SNAMEFROM SWHERE S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE CNAME=离散数学（2）求1310101课程的成绩高于张林的学生学号和成绩。（3）求其他系中年龄小于计算机系年龄最大者的学生。（4）求其他系中比计算机系学生年龄都小的学生。（5）求选修了1310206 课程的学生姓名。SELECT SNAMEFROM SWHERE S# IN (SELECT S# FROM SC WHERE C#=1310206（6）求没有选修13

20、10206 课程的学生姓名。SELECT SNAMEFROM SWHERE S# NOT IN (SELECT S# FROM SC WHERE C#=1310206（7）查询选修了全部课程的学生的姓名。答案： select sname From student Where not exists (select * From course Where not exists (select * From sc Where sno=student.sno and cno=o)（8）求至少选修了学号为“001103”的学生所选修的全部课程的学生学号和姓名。答案： select sno,sname F

21、rom student Where sno in (select scx.sno From sc scx Where not exists (select * From sc scy Where scy.sno=001103 and not exists (select * From sc scz Where scz.sno=scx.sno and o=o)四、注意事项1输入SQL 语句时应注意，语句中均使用西文操作符号。2语句的层次嵌套关系和括号的配对使用问题。五、思考题试用多种形式表示实验中的查询语句，并进行比较。实验六 数据库的组合查询和统计查询实验一、实验目的使学生熟练掌握SQL Se

22、rver查询分析器的使用方法，加深对Transact-SQL语言的查询语句的理解。熟练掌握数据查询中的分组、统计、计算和组合的操作方法。二、实验内容1分组查询实验。该实验包括分组条件表达、选择组条件的表达方法。2使用函数查询的实验。该实验包括统计函数和分组统计函数的使用方法。3组合查询实验。4计算和分组计算查询的实验。三、实验步骤在学生课程数据库中实现其查询操作（1）查找选修“计算机基础”课程的学生成绩比此课程的平均成绩大的学生学号，成绩。（2）求选修计算机基础课程的学生的平均成绩。（3）列出各系学生的总人数，并按人数进行降序排列。（4）统计各系各门课程的平均成绩。（5）查询选修计算机基础和离

23、散数学的学生学号和平均成绩。四、注意事项1子句WHERE （条件） 表示元组筛选条件，子句HAVING （条件）表示组选择条件。2组合查询的子句间不能有语句结束符。3子句HAVING （条件）必须和CROUP BY （分组字段） 子句配合使用。五、思考题1组合查询语句是否可以用其他语句代替，有什么不同？2使用GROUPB BY（分组条件）子句后，语句中的统计函数的运行结果有什么不同？实验七 数据库的视图和图表的定义及使用实验本实验需要2 学时。一、实验目的使学生掌握SQL Server 中的视图创建向导和图表创建向导的使用方法，加深对视图和SQL server图表作用的理解。二、实验内容1创建

24、、查看、修改和删除视图。2创建、编辑和删除数据库图表。三、实验步骤1创建视图根据学生选课数据库中已经建立了student、course和SC三 个表，结构如下：student（sno,sname,age,sex,dept）； course（cno,cname,credit,pcno,describe）； SC（sno,cno,grade）。如果要在上述3 个表的基础上建立一个视图，取名为SC_VIEW ；其操作用SQL 语句表示为：CREATE VIEW SC_VIEW AS SELECT STUDENT.* , COURSE.*, SC.GRADEFROM STUDENT, COURSE,

25、SCWHERE STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO下面利用SQL Server中提供的视图创建向导，来创建SC_VIEW 视图。1）打开企业管理器窗口，确认服务器，打开数据库文件夹，选中新视图所在的数据库。2）选择菜单“工具”“向导”，如图18 所示。3）在如图 19 所示的向导选择对话框中，单击数据库左边的“+ ”号，使之展开。选择“创建视图向导”项，单击“确定”按钮。4）进入创建视图向导后，首先出现的是欢迎进入创建视图向导对话框，其中简单介绍了该向导的功能，如图20 所示。单击“下一步”按钮后，就会出现如图21 所示的选择“数据库名称”对话框。

26、 图18 选择向导工具选项图19 在向导对话框中选择视图向导 图20 欢迎进入创建视图向导对话框图 21 选择数据库名称对话框5）在“选择数据库名称”对话框中，选择视图所属的数据库。本例的数据库为“学生选课”；单击“下一步”按钮，则进入如图 22 所示的选择表对话框。6）在选择表对话框中，列出了指定数据库中所有用户定义的表和视图。用户可以从中选择构造视图所需的一个表或多个表（或视图），被选中的表成为构造视图的参考表。选择构造视图参考表的方法是：用鼠标单击表名后的“包含在视图中”列，使义选框为选中状态。本例的数据库中的3 个表都应当被选中。单击“下一步”按钮，则进入选择列对话框，如图23 所示。

27、7）列选择对话框中以表格形式列出了创建视图参考表的全部属性，每个属性占表的一行。创建视图的参考属性可以在表格中选出。选择视图参考属性的方法是用鼠标单击属性名后边的“选择列”，使其复选框为选中状态。单击“下一步”按钮，进入创建视图的定义限制对话框，如图24 所示。8）在定义限制对话框中，输入表的连接和元组选择条件。本例应输入“WHERE STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO”。 图22 创建视图向导的选择表对话框 图23 创建视图向导的选择列对话框 图24 创建视图的定义限制对话框 图 25 定义视图名称对话框单击“下一步”按钮后，出现视图名对话框，如

28、图25 所示。9）在输入视图名对话框中输入所建视图的标识名。本例的视图名为“SC_VIEW ”。单击“下一步”按钮，则出现如图26 所示的视图创建完成对话框。图26 创建视图向导的完成对话框在完成对话框中给出了根据前面对话框输入的内容译成的SQL 语句。读者可以认真阅读该SQL 语句，如果发现与要求有不符合之处则可以直接进行修改。确认无误后单击“完成”按钮。随后出现一个通知用户已成功创建视图的信息框，单击“确认”按钮后，整个创建视图工作就完成了。2查看和修改视图视图创建好后，就可以利用它进行查询信息了。如果发现视图的结构不能很好地满足要求，还可以在企业管理器对它进行修改。（1）操作步骤l）在企

29、业管理器中，选择服务器、数据库，并使数据库展开，然后用鼠标右击要修改结构的视图，会弹出视图功能菜单，如图27 所示。图27 视图的功能弹菜单2）在弹出的菜单上 选择“设计视图”项，则弹出一个视图设计对话框，如图28 所示。图中列出了视图结构的详细内容，当对其修改完毕后关闭窗口，新的视图结构就会取代原先的结构。图28 视图设计对话框图28 中的视图设计对话框分为4个区域：图表区、表格区、SQL 语言区和结果区，各区域的作用如下：（2）视图设计对话中各区域的作用l）图表区。图表区域与数据库图表很相似，它图形化地显示了视图中的表以及表之间的关联。在图表区中，可以添加或去掉视图所基于的表，也可以添加或去掉视图所包含的表列。2）表格区。表格区用表格显示视图所有的表列。在表格区中，可以添加或去掉视图所包含的表列，设定排序和分组，也可以通过修改某些列的取值规则来限制结果集的范围。3 )SQL语言区。SQL语言区用Transact-SQL语句表示视图结构，可