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

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

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

字符（文本）型

8

主键

sname

4

不为空

ssex

1

sage

整数（数值）型

sdept

字符型

10

表2课程信息表：

course

cno

2

cname

30

credit

cpno

3

表3学生选课表：

sc

主属性，外键

grade

是

提示：

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

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

2、输入表中的记录

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

95001

李勇

男

20

CS

95002

刘晨

女

19

IS

95003

王敏

18

MA

95004

张立

95005

刘云

pcno

数据库

5

数学

6

信息系统

操作系统

数据结构

7

数据处理

PASCAL语言

92

85

88

90

80

58

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

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

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

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

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

具体约束如下：

默认值为’男’

取值为’男’或’女’

否

取值在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人的才统计）。

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

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

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

实验四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语句并能综合应用；

附录（总版）：

语句清单

--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

selectsname,sage,sdept-----3

wherestudent.sdept='

IS'

selectdistinctstudent.sno-----4

fromstudent,sc

wheresc.sno=student.sno

selectdistinctstudent.sno----5

wheresc.sno=student.snoandgrade<

60

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

wheresnonotin

（selectsno

wheresdept='

/*这是exists的用法

selectssex,sage,sdept

fromstudentasy

wherenotexists（select*

fromstudent

whereY.sdept='

CS'

*/

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

wheresagebetween18and20;

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

wheresnamelike'

刘%'

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

[刘李]%'

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

刘__'

--中文占用两个字符，所以这里用两个_

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

where2013-sage>

1983

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

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

fromstudentass-------12

Selectsname+'

年龄为'

+cast（sageaschar

（2））+'

岁'

Fromstudent---------13

select*

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

orderbysdept,sagedesc

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

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----------隐性连接

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）

3'

groupbystudent.sno,student.sname

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

wherestudent.sno=x.snoandcno='

andexists（selectstudent.sno

fromstudent,scasy

andexists（select*

fromstudent,scasz

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

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

wherestudent.sno=x.snoandx.sno=y.snoando='

ando='

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

wherex.sname='

刘晨'

andx.sage=y.sage

selectsname,sage

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

wherestudent.sno=sc.snoando=oandame='

数据库'

selectsname

wheresage<

any（selectsage

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

wheresdept='

is'

）andsdept<

>

'

all（selectsage------8

fromstudent

andsdept<

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

wheresnoin（selectsno

fromsc

groupbysno

havingCOUNT（*）=7）

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

andssex='

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

whereo='

1'

except（selectstudent.sname

fromstudent,sc

whereo='

selectcno

fromsc---------------------12

wheresc.snonotin（selectsno

fromst