数据库实验指导Word下载.docx

数据库实验指导Word下载.docx

《数据库实验指导Word下载.docx》由会员分享，可在线阅读，更多相关《数据库实验指导Word下载.docx（69页珍藏版）》请在冰点文库上搜索。

3．“SSMS”中和查询编辑器中数据表中数据的插入。

六、验证性实验

（一）在“SSMS”中创建数据库、数据表和进行数据插入

1．创建数据库

（1）使用“SSMS”建立数据库的步骤

1）单击“开始”菜单下“所有程序”→“MicrosoftSQLServer2005”→“SQLServerManagementStudio”，如图3-1所示。

图3-1SQLServer2005启动

2）在连接到服务器窗口中选择服务器名（点击服务器名称右面的下拉列表→选择“浏览更多”→展开“数据库引擎”→选择下面出现的本机号，单击“确定”），单击”连接”按钮,如图3-2所示.。

3）进入到“MicrosoftSQLServerManagementStudio（SSMS）”,如图3-3所示.。

图3-2SQLServer2005连接服务器界面图3-3“SSMS”界面

4）选中“数据库”文件夹，单击鼠标右键，在弹出菜单上选择“新建数据库”。

随后在数据库属性对话框的常规页面中，输入数据库名“学生选课”，如图3-4所示。

新建数据库属性对话框有3个页面：

常规、选项和文件组。

常规选项分别用来义数据库名、数据文件属性和日志文件属性。

选项是对数据库中游标\状态等信息的设置。

5）单击“确定”按钮，关闭对话框。

在“SSMS”窗口中出现“学生选课”数据库标志，如图3-5所示，表明已经成功创建数据库。

图3-4新建数据库界面图3-5数据库建立成功界面

6）在“SSMS”中，右键单击“学生选课”数据库，在弹出菜单中选择“属性”，将弹出一个对话框，显示“学生选课”数据库的属性,如图3-6所示。

2．创建数据库表

（1）打开刚才创建的“学生选课”数据库，并在“SSMS”窗口的右边窗口中用鼠标右键点击“表”对象，选择“新建表”命令，打开表编辑器窗口，如图3-7所示。

图3-6数据库属性窗口图3-7表编辑器界面

（2）根据表3-1所示的表结构，添加新列。

表3-1学生表

列名

数据类型

长度

是否允许为空

学号

Varchar

10

N

姓名

20

性别

Char

Y

出生日期

Smalldatetime

4

Y

专业名

所在系

联系电话

（3）点击工具栏上的“保存”按钮，在弹出的“选择名称”对话框中输入表名“学生-

表”，然后单击“确定”按钮，完成新表的创建。

（4）同理，根据表3-2建课程表，根据表3-3建选课表

表3-2课程表

课程号

6

N

课程名

学时

Tinyint

1

学分

Tinyint

表3-3选课表

3

成绩

3．使用“SSMS”向数据库表输入数据、修改数据、删除数据。

（1）打开“SSMS”，选中服务器，展开数据库文件夹，进一步展开“学生选课”数据库。

单击表文件夹，找到“学生表”。

（2）用鼠标右键单击“学生表”，选择“打开表”――“返回全部行，（SQL2008中选择“编辑前100行”）就会出现表更新对话框，如图3-8所示。

图3-8记录输入界面

（3）参考表3-4的内容，为学生表输入数据。

（二）在查询编辑器中创建数据库、数据表和进行数据插入

1．在“SSMS”中，点击工具栏中的第一个按钮“新建查询”,如图3-9所示。

图3-9查询编辑器的打开

打开查询编辑器的窗口，如图3-10所示。

图3-10查询编辑器

2．在查询编辑器的右窗口中输入T-SQL语句：

createdatabasestudent

选中该语句并执行（单击工具栏的“执行”按钮），出现

命令已成功

表示数据库成功建立。

3．用T-SQL语句创建数据表STUD：

USESTUDENT

CREATETABLESTUD（SNOVARCHAR（10）NOTNULL,SNAMEVARCHAR（20）NOTNULL,SEXCHAR

（2）,BIRTHDAYDATETIME）

选中上述语句并执行，出现

命令已成功完成

表示数据表已经建立好。

可以在“对象浏览器”中的STUDENT数据库上右击鼠标，并单击“刷新”，然后展开“用户表”，可以看到STUD数据表。

4．向STUD数据表中插入数据记录。

INSERTINTOSTUDVALUES（’200512’,’Josephine’,’F’,’1980-12-20’）

所影响的行数为1行

表示数据已经插入到数据表中。

七、设计性实验（注意：

设计性实验的题目用T-SQL命令完成）

1．实验要求

（1）创建一个XSDA数据库，该数据库的主文件逻辑名称为xsda_data，物理文件名为D:

\SQL\xsda.mdf，初始大小为20MB，无上限，增长速度为20%；

数据库的日志文件逻辑名称为xsda_log，物理文件名为D:

\SQL\xsda.ldf，初始大小为3MB，最大尺寸为10MB，增长速度为1MB。

（2）在XSDA数据库中创建三个数据表：

S（SNOVARCHAR（10）NOTNULL,SNAMEVARCHAR（20）,DEPAVARCHAR（20）,AGEINT,SEXCHAR（4））

C（CNOVARCHAR（10）NOTNULL,CNAMEVARCHAR（20）,cpnovarchar（10）,creditint）

SC（SNOVARCHAR（10）NOTNULL,CNAMEVARCHAR（10）NOTNULL,GRADEDECIMAL）

（3）在3个表中输入如下数据。

（参考以下表中内容，每个表至少输入4-6个记录）

表3-4学生表

系部

年龄

200512

李勇

计算机系

20

男

200518

刘晨

19

女

200018

王敏

数学系

18

200511

杨扬

物理系

200510

张立

信息系

200513

200514

王点点

200012

欧阳雨

200515

刘依依

23

表3-5课程表

前修课程

c01

数据库原理

c03

c02

信息系统

C01

数据结构

null

c04

DB_设计

co1

表3-6成绩表

70

55

80

95

45

78

……

（4）修改S表结构，增加一个入校时间，列名为RXTIME，日期型。

（5）删除SC表中成绩不及格的记录。

（6）把S表中学号为“200512”的学生的年龄改为19。

2．思考题

（1）SQLServer2005/2008提供了哪些基本数据类型？

（2）数据库创建后怎样修改数据库操纵文件和数据库日志文件分配的空间大小？

（3）用数据的导入、导出实现SQL数据库之间；

SQL与EXCEL之间的数据传递。

实验D002:

单表查询实验

单表查询

1．掌握数据的基本检索方法。

2．掌握数据查询的Groupby和Orderby子句的使用。

3．掌握聚集函数的使用方法。

数据检索的语句格式：

SELECT[ALL|DISTINCT]<

目标列表达式列表>

FROM<

表名或视图名列表>

[WHERE<

条件表达式>

]

[GROUPBY<

列名1>

[HAVING<

]]

[ORDERBY<

列名2>

[ASC|DESC]]

其中，SELECT后的目标列表达式可以是列名、表达式或函数。

GROUPBY子句：

对查询结果按指定列的值分组，该属性列值相等的元组为一个组。

通常会在每组中作用集函数。

HAVING短语：

筛选出只有满足指定条件的组

ORDERBY子句：

对查询结果表按指定列值的升序或降序排序

注意：

T-SQL语句中关键字不区分大小写

1．对数据表进行简单检索。

2．对数据表进行排序检索。

3．在检索中使用聚集函数。

先选择要操作的数据库，用T-SQL命令：

或在工具栏的当前数据库中选择STUDENT，如图3-11所示。

工作数据库

图3-11查询编辑器界面

（首先把精品课程网站-→上机实验数据库脚本中的代码复制到新建查询窗口执行（注意，只执行一次。

为避免重复，执行后把这段代码删除。

）

以下查询要求在逐个语句执行，执行后将执行结果记录下来：

1．条件查询全部数据

SELECT*FROMS

2．在SELECT关键字后指明要检索的列名

（1）查询S表的学生学号和姓名

SELECTSNO，SNAMEFROMS

（2）查询S表中的系部名

SELECTDEPAFROMS

（3）查询S表中不重复的系部名

SELECTDISTINCTDEPAFROMS

3．改变列标题的检索

（1）使用空格形式：

列名新标题

SELECTSNO学号,SNAME姓名FROMS

（2）使用“AS”形式，列名AS新标题

SELECTSNOAS学号,SNAMEAS姓名FROMS

3．有条件选择的查询

（1）在S表检索“信息系”的学生信息

WHEREDEPA=’信息系’

（2）在S表中检索姓“王”的学生信息

WHEREsnamelike‘王%’

（3）在SC表检索’C01’选修课成绩为空的选课记录

SELECTSNO，CNOFROMSC

WHEREcno=’C01’andgradeisnull

（4）检索年龄为21，18，22的学生学号、姓名

WHEREagein（21,18,22）

AgeIN{21,18,22}表示某条记录的AGE字段值是否是集合{21,18,22}中的元素，如是，则选择。

它等价于下面语句：

WHEREage=18orage=21orage=22

4．使用聚集函数

（1）查询选课表中最高分、平均分、最低分

SELECTMAX（GRADE）,AVG（GRADE）,MIN（GRADE）

FROMSC

（2）查询“C01”课程的最高分、平均分和最小成绩。

WHERECNO=‘C01’

5．对检索结果进行排序

SELECT*FROMSC

WHEREGRADEISNULL

ORDERBYSNO，CNODESC

6．进行分组统计：

（1）查询各学生的选课数

SELECTSNO，COUNT（*）

GROUPBYSNO

（2）使用HAVING字句：

“选课表”中查询选修了3门以上课程的学生学号。

SELECTSNO，COUNT（*）FROMSC

HAVINGCOUNT（*）>

=3

（3）“选课表”中按学号分组汇总学生的平均分，并按平均分的降序排列。

SELECTSNO学号,AVG（GRADE）平均分FROMSC

ORDERBY平均分DESC

（4）析下面两个SELECT语句执行结果，说明有什么不同？

SELECTSNOFROMSC

ORDERBYSNO

GO

七、设计性实验

（1）查询计算机系学生的学号和姓名。

（2）查询选修了课程的学生学号。

（3）查询选修“C01”课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。

（4）查询每门课程的平均分。

（5）查询学校开设的课程总数。

（6）查询选修两门及两门以上课程的学生学号。

（7）查询年龄在20-22之间的男生的学号、姓名、系部。

（8）查询选修“C01”课程的学生人数。

2．实验报告要求

（1）写出验证性实验的执行结果。

（2）用T-SQL完成设计性实验并写出执行结果。

（3）完成以下思考题。

3．思考题

（1）使用ORDERBY子句后SELECT后的数据列有什么限制吗？

使用GROUPBY呢？

（2）执行selectmax（grade）fromsc,写出执行结果；

执行selectsno,max（grade）fromscgroupbysno,写出执行结构。

思考：

使用GROUPBY<

分组条件>

子句后，语句中的统计函数的运行结果有什么不同?

实验D003：

多表查询实验

多表查询

1．掌握多表连接查询方法。

2．掌握IN子查询的嵌套查询。

3．了解EXISTS嵌套查询方法。

1．ANSI连接语法：

SELECT表1.列名表,表2.列名2,表……

FROM表1[join_type]JOIN表2ON连接条件

[WHERE条件]

其中JOIN_TYPE：

使用INNERJOIN关键字,结果集仅包含满足条件的行.

使用CROSSJOIN关键字,结果集包含两个表中所有行的组合.

使用OUTERJOIN关键字,结果集既包含那些满足条件的行,还包含那些其中某个表的全部行。

或者SQLSERVER连接语法：

FROM表1,表2，……

WHERE连接条件

2．IN嵌套子查询

SELECT<

FROM表名

WHERE列名IN

（SELECT字句）

3．EXISTS嵌套子查询

–带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

●若内层查询结果非空，则返回真值

●若内层查询结果为空，则返回假值。

由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义

所以，EXISTS子查询中一般是相关自查询，即子查询脱离父查询后不能单独执行。

如果EXISTS子查询中是不相关子查询，会有什么结果？

1．多表等值连接查询；

2．外连接查询；

3．IN子查询嵌套；

4．EXISTS嵌套查询。

1．多表的连接查询（相当于做笛卡儿乘积）

SELECT*FROMC，SC

2．表的等值连接查询

（1）查询各学生的选课信息（包括学号、课程名、成绩）

因为学号和成绩在SC表中，而课程名在C表中，因此需要多表查询。

SELECTSNO，CNAME，GRADE

FROMCINNERJOINSCONC.CNO=SC.CNO

（2）查询学生的选课记录，显示学生的学号、姓名、课程号、成绩。

SELECTS.SNO，SNAME，CNO，GRADE

FROMSINNERJOINSCONS.SNO=SC.SNO

（3）查询学生的选课记录，显示学生的学号、姓名、课程号、课程名、成绩

SELECTS.SNO，SNAME，SC.CNO，CNAME，GRADE

FROMSINNERJOINSCONS.SNO=SC.SNOINNERJOINCONSC.CNO=C.CNO

3．左外连接查询：

当希望左表（第一张表）中所有记录全部显示出来时，需要用左外连接操作。

INSERTINTOS（SNO,SNAME）VALUES（’20000’,’ZXX’）

FROMS

LEFTOUTERJOINSCONS.SNO=SC.SNO

观察与下面等值连接的执行结果有何不同？

4．表自身的连接

（1）查询与‘李勇’同系的学生学号

将学生表S与S本身进行等值连接（系部相等），因为S与S做连接操作时不能区分，所以，对表取一个别名。

然后将第二张表S中名字为‘李勇’的记录选择出就可。

SELECTS1.SNO

FROMSASS1INNERJOINSASS2ONS1.DEPA=S2.DEPA

WHERES2.SNAME=’李勇’

（2）显示每个学生的非最高分成绩（学生自己的选课成绩中，不是最高分的选课记录显示出来）

SELECTSNO,CNO,GRADEFROMSCASSC1

WHEREGRADE<

（SELECTMAX（GRADE）FROMSCASSC2

WHERESC2.SNO=SC1.SNO）

5．IN嵌套子查询

（1）不相关的IN子查询：

子查询可以单独执行，与被嵌套的查询无关。

如，查询与‘李勇’同系的学生学号

可以先查询出‘李勇’所在的系，然后再到S表中查询与上述结果相同的记录。

SELECTSNO

FROMS

WHEREDEPAIN

（SELECTDEPAFROMSWHERESNAME=‘李勇’）

查询‘数据库原理’课程的选课人数

SELECTCOUNT（*）FROMSC

WHERECNOIN

（SELECTCNOFROMCWHERECNAME=‘数据库原理’）

（2）相关的子查询：

子查询中要用到父查询表的信息，子查询不能独立执行。

如，查询选修课程号为“C01”课程且成绩至少高于选修课程号为“C02”的同学的Cno、Sno和GRADE。

在子查询中，因为要查找该同学‘C02’课程的成绩，所以，需要父查询表中该学生的学号信息。

SELECTCNO，SNO，GRADE

FROMSCASSC1

WHERECNO=’C01’ANDGRADE>

（

SELECTGRADEFROMSCASSC2

WHERESC2.SNO=SC1.SNOANDSC2.CNO=’C02’）

6．EXISTS嵌套子查询

（1）执行以下语句，观察显示的两个查询结果

SELECTSNO，SNAME

WHEREEXISTS

（SELECT*FROMSCWHERECNO=‘C03’）

GO

（2）执行以下语句，观察显示的两个查询结果

INSERTINTOCVALUES（‘C06’，‘数据库安全’，NULL，3）

（SELECT*FROMSCWHERECNO=‘C06’）

（3）查询‘数据库原理’课程的选课人数

SELECTCOUNT（*）

（SELECT*FROMC

WHEREC.CNO=SC.CNOANDCNAME=’数据库原理’）

（1）查询“计算机系”学生所学课程的成绩表。

（2）查询成绩比该课程平均成绩低的同学的成绩表。

（提示：

用相关子查询。

（3）查询选修“C01”课程的学生学号、课程名、成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。

（4）查询选修两门及两门以上课程的学生学号及姓名。

（5）查询年龄在20-2