sql数据库系统原理上机实验综合版.docx

sql数据库系统原理上机实验综合版.docx

《sql数据库系统原理上机实验综合版.docx》由会员分享，可在线阅读，更多相关《sql数据库系统原理上机实验综合版.docx（22页珍藏版）》请在冰豆网上搜索。

sql数据库系统原理上机实验综合版

《数据库原理》实验指导书

《数据库原理》上机实验

上机学时：

8学时

一、上机性质、目的及任务：

通过上机实践，配合课堂教学内容加深对数据库的基本概念和基本技术的理解，掌握数据库系统设计的基本思想和基本步骤，熟悉关系数据库的标准语言SQL，并对典型的关系数据库管理系统有较深入的了解，使学生的动手能力有较大的提高。

二、面向专业：

计算机类各专业

三、实验指导书：

见下页

四、实验内容：

实验一SQL数据定义3

实验二SQL简单查询6

实验三SQL复杂查询8

实验四SQL数据更新9

实验一SQL数据定义

一、实验目的

（1）认识几种常见的数据库管理系统，熟悉它们的使用界面;

（2）熟练掌握建立数据库和表，向数据库输入数据、修改数据和删除数据的操作。

（3）熟悉SQL数据定义语言（DDL）

二、实验内容

（1）分别在Access和SQLSERVER2005中建立数据库并设计各表，输入多条实际数据，并实现数据的增、删、改操作。

（2）用SQL语言进行基本表结构的定义、修改、删除，索引的建立和删除

三、实验步骤：

分别在ACCESS数据库管理系统和SQLSERVR2005环境下利用图形操作界面（非SQL语句）实现以下操作：

1、创建用于学生管理的数据库，数据库名为XSGL，包含学生信息，课程信息和选课信息。

数据库XSGL包含下列3个表：

（l）student：

学生基本信息。

（2）course：

课程信息表。

（3）sc：

学生选课表。

各表的结构分别如表1、表2和表3所示。

表1学生信息表：

student

列名

数据类型

长度

完整性约束

sno

字符（文本）型

8

主键

sname

字符（文本）型

4

不为空

ssex

字符（文本）型

1

sage

整数（数值）型

sdept

字符型

10

表2课程信息表：

course

列名

数据类型

长度

完整性约束

cno

字符（文本）型

2

主键

cname

字符（文本）型

30

credit

整数（数值）型

cpno

字符（文本）型

3

表3学生选课表：

sc

列名

数据类型

长度

完整性约束

sno

字符（文本）型

8

主属性，外键

cno

字符（文本）型

2

主属性，外键

grade

整数（数值）型

是

提示：

在不使用SQL语句创建表的情况下，可通过ACCESS中的关系（菜单—工具—关系）和SQLSERVER2005中的数据库关系图（数据库节点展开—数据库关系图）实现外键的创建。

外键字段和参照字段之间的数据类型以及长度要保持一致。

2、输入表中的记录

分别在student表、course表和sc表中输入如下表中的记录：

sno

sname

ssex

sage

sdept

95001

李勇

男

20

CS

95002

刘晨

女

19

IS

95003

王敏

女

18

MA

95004

张立

男

19

IS

95005

刘云

女

18

CS

cno

cname

credit

pcno

1

数据库

4

5

2

数学

6

3

信息系统

3

1

4

操作系统

4

6

5

数据结构

4

7

6

数据处理

3

7

PASCAL语言

4

6

sno

cno

grade

95001

1

92

95001

2

85

95001

3

88

95002

2

90

95002

3

80

95003

2

85

95004

1

58

95004

2

85

观察输入时有无提示错误，如果有如何修改，体会参照完整性的作用，弄清楚先输入那些表中记录，为什么？

3、对表中的记录进行浏览、修改、删除操作。

4、利用“分离数据库”和“附加数据库”操作对SQLServer中创建的数据库做备份和还原操作。

5、在SQLSERVER2005中新建查询，建立到服务器的连接

6、用SQL语言CREATETABLE语句创建实验一中学生表student、课程表course和选课表sc及其相应约束，

具体约束如下：

表1学生信息表：

student

列名

数据类型

长度

完整性约束

sno

字符（文本）型

8

主键

sname

字符（文本）型

4

不为空

ssex

字符（文本）型

2

默认值为’男’

取值为’男’或’女’

sage

整数（数值）型

是

sdept

字符型

10

否

表2课程信息表：

course

列名

数据类型

长度

完整性约束

cno

字符（文本）型

2

主键

cname

字符（文本）型

30

credit

整数（数值）型

cpno

字符（文本）型

3

表3学生选课表：

sc

列名

数据类型

长度

完整性约束

sno

字符（文本）型

10

主属性，外键

cno

字符（文本）型

30

主属性，外键

grade

整数（数值）型

是

取值在0-100之间

7、向创建的表中输入数据，测试所创建的完整性约束是否起作用

8、用SQL语言ALTER语句修改表结构;

1）STUDENT表中增加一个字段入学时间scome，

2）删除STUDENT表中sdept字段；

3）删除创建的SC表中CNO字段和COURSE表CNO字段之间的外键约束；

4）重建3）中删除的约束

9、重新定义一个简单表，然后用SQL语言DROP语句删除该表结构;

10、用SQL语言CREATEINDEX语句定义表STUDENT的SNAME字段的降序唯一索引;

11、用SQL语言DROP语句删除索引;

实验二SQL简单查询

目的：

掌握简单数据查询操作。

内容：

使用各种查询条件完成指定的查询操作

步骤：

1）创建学生表student、课程表course和选课表SC,并输入数据（注意数据的完整性。

）;（可以使用实验一中已经建立的表和数据）

2）对各表中的数据进行不同条件的查询；

包括的运算：

投影、选择、比较运算符、逻辑运算符、字符匹配运算符、匹配列表范围、算术运算符、内部函数、排序、分组、分组函数使用

（1）查询全体学生的学号和姓名

（2）查询全体学生的详细记录

（3）查询软件学院的学生姓名、年龄、系别

（4）查询所有选修过课程的学生学号（不重复）

（5）查询考试不及格的学生学号（不重复）

（6）查询不是软件学院的学生性别、年龄、系别

（7）查询年龄18－20岁的学生学号、姓名、系别、年龄；

（8）查询姓刘的学生情况

（9）查询姓刘或姓李的学生情况

（10）查询姓刘且名字为两个字的学生情况

（11）查询1983年以后出生的学生姓名。

（12）利用内部函数year（）查找软件学院学生的出生年份

（13）利用字符转换函数实现字符联接。

Selectsname+‘年龄为’+cast（sageaschar

（2））+’岁’

Fromstudent

（14）查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。

（15）查询学生总人数。

（16）查询选修了课程的学生人数。

（17）查询选修了7号课程的学生总人数和平均成绩

（18）查询选修6号课程学生的最好成绩

（19）查询每个系的系名及学生人数。

（20）查找每门课的选修人数及平均成绩

（21）查找没有先修课的课程情况

要求：

1、将上述任务中完整的SQL语句调试并使之运行正确；

2、写出实验报告；

实验三SQL复杂查询

目的：

掌握复杂数据查询操作。

内容：

掌握各种连接查询、嵌套查询的使用

步骤：

1）实验一中的数据为基础

2）对各表中的数据进行不同条件的连接查询和嵌套查询；

●查询每个学生及其选课情况；

●查询每门课的间接先修课

●将STUDENT,SC进行右连接

●查询既选修了2号课程又选修了3号课程的学生姓名、学号；

●查询和刘晨同一年龄的学生

●选修了课程名为“数据库”的学生姓名和年龄

●查询其他系比IS系任一学生年龄小的学生名单

●查询其他系中比IS系所有学生年龄都小的学生名单

●查询选修了全部课程的学生姓名

●查询计算机系学生及其性别是男的学生

●查询选修课程1的学生集合和选修2号课程学生集合的差集

●查询李丽同学不学的课程的课程号

●查询选修了3号课程的学生平均年龄

●求每门课程学生的平均成绩

●统计每门课程的学生选修人数（超过3人的才统计）。

要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列

●查询学号比刘晨大，而年龄比他小的学生姓名。

●求年龄大于所有女同学年龄的男同学姓名和年龄

要求：

1、将上述任务中完整的SQL语句调试并使之运行正确；

2、写出实验报告；

实验四SQL数据更新

目的：

掌握SQL的常用数据更新操作，熟练应用INSERT,UPDATE,DELETE语句。

内容：

1）应用INSERT,UPDATE,DELETE语句进行更新操作；

a）插入如下学生记录（学号：

95030，姓名：

李莉，年龄：

18）

b）插入如下选课记录（95030，1）

c）计算机系学生年龄改成20

d）把数学系所有学生成绩改成0

e）把低于总平均成绩的女同学成绩提高5分

f）修改2号课程的成绩，若成绩小于75分提高5％，成绩大于75时提高

4％（两个语句实现，注意顺序）

g）删除95030学生信息

h）删除SC表中无成绩的记录

i）删除张娜的选课记录

j）删除不及格的学生选课记录

k）删除数学系所有学生选课记录

l）删除所有未被选修的课程

m）查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个已经存在的基本表STU（SNO,SNAME,SSEX）中

n）建立一个sdeptgrade表，包含（sdept,avggrade）字段，对每一个系，求学生的成绩，并把结果存入sdeptgrade

2）熟练掌握INSERT,UPDATE,DELETE语句并能综合应用；

要求：

1、将上述任务中完整的SQL语句调试并使之运行正确；

2、写出实验报告；

附录（总版）：

语句清单

--sql查询实验一代码

createtablestudent1

（snochar（8）primarykey,

snamechar（4）notnull,-------创建表student1

ssexchar

（2）default'男',

check（ssexin（'男','女'））,

sageintdefault（'是'）,

sdeptchar（10）default（'否'）,

）

createtablecourse1

（cnochar

（2）primarykey,

cnamechar（30）,------创建表course1

creditint,

cpnochar（3）,

）

createtablesc1

（snochar（8）foreignkeyreferencesstudent1（sno）,创建表sc1

cnochar

（2）foreignkeyreferencescourse1（cno）,

gradeintdefault（'是'）check（gradebetween0and100）,

）

----------------------第题

altertablestudent1addscomeint

altertablestudent1dropcolumnsdept

selectname

fromsys.foreign_key_columnsfjoinsys.objectsoonf.constraint_object_id=o.object_id

wheref.parent_object_id=object_id（'sc1'）--------查找出约束名，然后再用drop语句删除约束

altertablesc1dropconstraintFK__sc1__cno__740F363E--------在括号中补充用上面语句查找出的约束名

altertablesc1addconstraint[pk_sc1]primarykey（cno）

altertablesc1addconstraint[fk_sc1_course1]foreignkey（cno）referencescourse1（cno）

altertablesc1dropconstraint[fk_sc1_course1]

createuniqueindexstudent1_indexonstudent1（snamedesc）

dropindexstudent1_indexonstudent1

createtableteacher

（tnochar（4）primarykey,

tnamechar（8）notnull,

titlechar（5）,

）

droptableteacher

--sql查询实验二的代码

SELECTsno,sname--1

fromstudent

select*------------2

fromstudent

selectsname,sage,sdept-----3

fromstudent

wherestudent.sdept='IS'

selectdistinctstudent.sno-----4

fromstudent,sc

wheresc.sno=student.sno

selectdistinctstudent.sno----5

fromstudent,sc

wheresc.sno=student.snoandgrade<60

selectssex,sage,sdept-----------6

fromstudent

wheresnonotin

（selectsno

fromstudent

wheresdept='IS'）

/*这是exists的用法

selectssex,sage,sdept

fromstudentasy

wherenotexists（select*

fromstudent

whereY.sdept='CS'）

*/

selectsno,sname,sage,sdept-------7

fromstudent

wheresagebetween18and20;

select*---------------8

fromstudent

wheresnamelike'刘%'

select*-----------------9

fromstudent

wheresnamelike'[刘李]%'

selectsname----------------10

fromstudent

wheresnamelike'刘__'--中文占用两个字符，所以这里用两个_

selectsname-------------------11

fromstudent

where2013-sage>1983

--selectyear（sage）这是取单个人的出生年份的用法，调用year函数

selectyear（getdate（））-s.sage+1

fromstudentass-------12

wheresdept='CS'

Selectsname+'年龄为'+cast（sageaschar

（2））+'岁'

Fromstudent---------13

select*

fromstudent---------------------14

orderbysdept,sagedesc

selectCOUNT（sno）----------------15

fromstudent

selectCOUNT（distinctsno）

fromsc--------------16

/*

SELECTCOUNT（*）

FROMstudent----------16题解法二

WHEREEXISTS（SELECT*FROMSCWHEREsc.sno=student.sno）

*/

selectCOUNT（student.sno）,avg（sc.grade）

fromstudent,sc------------17

wherestudent.sno=sc.snoandcno='7'

selectmax（grade）

fromsc-------------------18

wherecno='6'

selectsdept,COUNT（sno）

fromstudent-------------------19

groupbysdept

selectCOUNT（student.sno）,AVG（grade）

fromstudent,sc----------20

wherestudent.sno=sc.sno

groupbycno

/*-----------------20第二种解法

selectcname,COUNT（student.sno）,AVG（grade）

fromstudent,sc,course

wherestudent.sno=sc.snoando=o

groupbyo,cname

*/

select*------为什么这里查询不到结果

fromcourse-------------21

wherecpnoisnull

--sql查询实验三代码

--------1

selectstudent.*,o,sc.grade----左外连接（显性连接）

fromstudentleftjoinsconstudent.sno=sc.sno

/*

selectstudent.*,o,sc.grade

fromstudentinnerjoinsc----内连接法（显性连接）

onstudent.sno=sc.sno

*/

/*

selectstudent.*,o,sc.grade----------隐性连接

fromstudent,sc

wherestudent.sno=sc.sno

*/

selectame,first.cpno,o

fromcoursefirst,coursesecond----------2

wherefirst.cpno=o

selectstudent.*,sc.*---------------3

fromstudentrightjoinsconstudent.sno=sc.sno

selectstudent.sno,sname

fromstudent,sc,（selectstudent.sno

fromstudent,sc-----4导出表的使用

wheresc.sno=student.snoandcno='2'

groupbystudent.sno）asresult（sno）

wherestudent.sno=sc.snoandcno='3'

groupbystudent.sno,student.sname

selectstudent.sno,sname

fromstudent,scasx--------4法二

wherestudent.sno=x.snoandcno='2'andexists（selectstudent.sno

fromstudent,scasy

wherecno='3'andexists（select*

fromstudent,scasz

wherez.sno=y.snoandz.sno=x.sno））

selectstudent.sno,sname

fromstudent,scasx,scasy------4法三

wherestudent.sno=x.snoandx.sno=y.snoando='2'ando='3'

select*

fromstudentasx,studenty--------5

wherex.sname='刘晨'andx.sage=y.sage

selectsname,sage

fromstudent,sc,course-----------6

wherestudent.sno=sc.snoando=oandame='数据库'

selectsname

fromstudent

wheresage

fromstudent----------7

wheresdept='is'

）andsdept<>'is'

selectsname

fromstudent

wheresage

fromstudent

wheresdept='is'）

andsdept<>'is'

selectsname

fromstudent------------9

wheresnoin（selectsno

fromsc

groupbysno

havingCOUNT（*）=7）

select*--------------10

fromstudent

wheresdept='is'andssex='男'

selectdistinctstudent.sname-------------11

fromstudent,sc

whereo='1'except（selectstudent.sname

fromstudent,sc

whereo='2'）

selectcno

fromsc--