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

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

《数据库原理》上机实验

上机学时：8学时

1

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

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

二、 面向专业：

计算机类各专业

三、 实验指导书：

见下页

四、 实验内容：

实验一SQL数据定义....................................................................................................... 3 实验二 SQL简单查询................................................................................................. 6 实验三 SQL 复杂查询 ................................................................................................ 7 实验四 SQL 数据更新 ................................................................................................... 8

2

实验一SQL数据定义

一、实验目的

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

(2)熟练掌握建立数据库和表，向数据库输入数据、修改数据和删除数据的操作。 (3) 熟悉SQL数据定义语言（DDL）

二、实验内容

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

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

三、实验步骤：

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

1、创建用于学生管理的数据库，数据库名为XSGL，包含学生信息，课程信息和选课信息。 数据库XSGL包含下列3 个表： (l） student：学生基本信息。 (2）course：课程信息表。

(3）sc：学生选课表。

各表的结构分别如表1、表2和表3 所示。 表1 学生信息表：student 列名 sno sname ssex 数据类型 长度 完整性约束 主键 不为空 完整性约束 主键 完整性约束 主属性，外键 主属性，外键 字符（文本）型 8 字符（文本）型 4 字符（文本）型 1 整数（数值）型 sage 字符型 sdept 10 表2 课程信息表：course 列名 cno cname credit 数据类型 长度 字符（文本）型 2 字符（文本）型 30 整数（数值）型 字符（文本）型 3 cpno 表3 学生选课表：sc 列名 sno cno 数据类型 长度 字符（文本）型 8 字符（文本）型 2 整数（数值）型 是 grade 提示：在不使用SQL语句创建表的情况下，可通过ACCESS中的关系（菜单—工具—关系）

3

和SQL SERVER 2005中的数据库关系图（数据库节点展开—数据库关系图）实现外键的创建。外键字段和参照字段之间的数据类型以及长度要保持一致。 2、输入表中的记录

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

sno 95001 95002 95003 95004 95005 sname 李勇 刘晨 王敏 张立 刘云 ssex 男 女 女 男 女 sage 20 19 18 19 18 sdept CS IS MA IS CS cno 1 2 3 4 5 6 7 sno 95001 95001 95001 95002 95002 95003 95004 95004 cname 数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL语言 cno 1 2 3 2 3 2 1 2 credit 4 6 3 4 4 3 4 grade 92 85 88 90 80 85 58 85 pcno 5 1 6 7 6 观察输入时有无提示错误，如果有如何修改，体会参照完整性的作用，弄清楚先输入那些表中记录，为什么？

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

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

4

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

6、 用SQL语言CREATE TABLE语句创建实验一中学生表student、课程表

course和选课表sc及其相应约束，

具体约束如下：

表1 学生信息表：student 列名 sno sname ssex sage sdept 数据类型 长度 完整性约束 主键 不为空 默认值为’男’ 取值为’男’或’女’ 是 否 完整性约束 主键 完整性约束 主属性，外键 主属性，外键 取值在0-100之间 字符（文本）型 8 字符（文本）型 4 字符（文本）型 2 整数（数值）型 字符型 10 长度 表2 课程信息表：course 列名 数据类型 cno cname credit cpno 列名 sno cno grade 字符（文本）型 2 字符（文本）型 30 整数（数值）型 字符（文本）型 3 数据类型 长度 表3 学生选课表：sc 字符（文本）型 10 字符（文本）型 30 整数（数值）型 是 7、向创建的表中输入数据，测试所创建的完整性约束是否起作用 8、用SQL语言ALTER语句修改表结构;

1）STUDENT表中增加一个字段入学时间scome， 2）删除STUDENT表中sdept字段；

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

9、重新定义一个简单表，然后用SQL语言DROP语句删除该表结构; 10、用SQL语言CREATE INDEX语句定义表STUDENT的SNAME字段的降序唯一索引;

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

5

实验二 SQL简单查询

目的：

掌握简单数据查询操作。

内容：

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

1）创建学生表student、课程表course和选课表SC,并输入数据（注意数据的完整性。）;（可以使用实验一中已经建立的表和数据） 2) 对各表中的数据进行不同条件的查询；

包括的运算：投影、选择、比较运算符、逻辑运算符、字符匹配运算符、匹配列

表范围、算术运算符、内部函数、排序、分组、分组函数使用

（1） 查询全体学生的学号和姓名 （2） 查询全体学生的详细记录

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

（4） 查询所有选修过课程的学生学号（不重复） （5） 查询考试不及格的学生学号（不重复）

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

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

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

（10） 查询姓刘且名字为两个字的学生情况 （11） 查询1983年以后出生的学生姓名。

（12） 利用内部函数 year()查找软件学院学生的出生年份 （13） 利用字符转换函数实现字符联接。

Select sname + ‘年龄为’+cast(sage as char(2))+’岁’ From student

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

生按年龄降序排列。 （15） 查询学生总人数。

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

（17） 查询选修了7号课程的学生总人数和平均成绩 （18） 查询选修6号课程学生的最好成绩 （19） 查询每个系的系名及学生人数。 （20） 查找每门课的选修人数及平均成绩 （21） 查找没有先修课的课程情况

要求：

1、将上述任务中完整的SQL语句调试并使之运行正确； 2、写出实验报告；

6

实验三 SQL 复杂查询

目的：

掌握复杂数据查询操作。

内容：

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

步骤：

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

2) 对各表中的数据进行不同条件的连接查询和嵌套查询； ? 查询每个学生及其选课情况； ? 查询每门课的间接先修课 ? 将STUDENT,SC进行右连接

? 查询既选修了2号课程又选修了3号课程的学生姓名、学号； ? 查询和刘晨同一年龄的学生

? 选修了课程名为“数据库”的学生姓名和年龄 ? 查询其他系比IS系任一学生年龄小的学生名单

? 查询其他系中比IS系所有学生年龄都小的学生名单 ? 查询选修了全部课程的学生姓名

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

? 查询选修课程1的学生集合和选修2号课程学生集合的差集 ? 查询李丽同学不学的课程的课程号 ? 查询选修了3号课程的学生平均年龄 ? 求每门课程学生的平均成绩

? 统计每门课程的学生选修人数（超过3人的才统计）。要求输出课程号和

选修人数，结果按人数降序排列，若人数相同，按课程号升序排列 ? 查询学号比刘晨大，而年龄比他小的学生姓名。 ? 求年龄大于所有女同学年龄的男同学姓名和年龄

要求：

1、将上述任务中完整的SQL语句调试并使之运行正确； 2、写出实验报告；

7

实验四 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、写出实验报告；

8

附录（总版）： 语句清单

--sql 查询实验一代码

create table student1 (sno char(8) primary key,

sname char(4) not null, ssex char(2) default'男', check(ssex in('男','女')), sage int default('是'), sdept char(10) default('否'), )

create table course1 (cno char(2) primary key,

cname char(30), credit int, cpno char(3), )

-------创建表student1 ------创建表course1 9

create table sc1

(sno char(8) foreign key references student1(sno), 创建表sc1 cno char(2) foreign key references course1(cno), grade int default('是') check(grade between 0 and 100), )

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

alter table student1 add scome int alter table student1 drop column sdept

select name

from sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id

where f.parent_object_id=object_id('sc1') --------查找出约束名，然后再用drop语句删除约束

alter table sc1 drop constraint FK__sc1__cno__740F363E --------在括号中补充用上面语句查找出的约束名

alter table sc1 add constraint [pk_sc1] primary key (cno)

alter table sc1 add constraint [fk_sc1_course1] foreign key (cno) references course1(cno)

alter table sc1 drop constraint [fk_sc1_course1]

10

create unique index student1_index on student1(sname desc) drop index student1_index on student1

create table teacher (tno char(4) primary key, tname char(8) not null, title char(5) , )

drop table teacher

--sql查询实验二的代码

SELECT sno,sname from student

select * ------------2 from student

select sname,sage,sdept -----3

--1 11

from student

where student.sdept='IS'

select distinct student.sno -----4 from student,sc

where sc.sno=student.sno

select distinct student.sno ----5 from student,sc

where sc.sno=student.sno and grade<60

select ssex,sage,sdept -----------6 from student where sno not in (select sno from student where sdept='IS')

/* 这是exists的用法 select ssex,sage,sdept from student as y where not exists(select *

12

from student where Y.sdept='CS') */

select sno,sname,sage,sdept -------7 from student

where sage between 18 and 20;

select * ---------------8 from student

where sname like'刘%'

select * -----------------9 from student

where sname like'[刘李]%'

select sname ----------------10 from student

where sname like'刘_ _'--中文占用两个字符，所以这里用两个

select sname -------------------11 from student

_ 13

where 2013-sage>1983

-- select year(sage) 这是取单个人的出生年份的用法，调用year函数

select year(getdate())-s.sage+1

from student as s -------12 where sdept='CS'

Select sname + '年龄为'+cast(sage as char(2))+'岁' From student ---------13 select *

from student ---------------------14 order by sdept,sage desc

select COUNT(sno)----------------15 from student

select COUNT(distinct sno) from sc --------------16 /*

14

SELECT COUNT(*)

FROM student ----------16题解法二

WHERE EXISTS( SELECT * FROM SC WHERE sc.sno = student.sno ) */

select COUNT(student.sno),avg(sc.grade) from student,sc ------------17 where student.sno=sc.sno and cno='7'

select max(grade)

from sc -------------------18 where cno='6'

select sdept,COUNT(sno) from student -------------------19 group by sdept

select COUNT(student.sno),AVG(grade) from student,sc ----------20 where student.sno=sc.sno group by cno

15

/* -----------------20第二种解法 select cname,COUNT(student.sno),AVG(grade) from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno group by sc.cno,cname */

select * ------为什么这里查询不到结果 from course -------------21 where cpno is null

--sql 查询实验三代码

--------1

select student.*,sc.cno,sc.grade ----左外连接(显性连接) from student left join sc on student.sno=sc.sno /*

select student.*,sc.cno,sc.grade

16

from student inner join sc ----内连接法（显性连接） on student.sno=sc.sno */ /*

select student.*,sc.cno,sc.grade ----------隐性连接 from student,sc

where student.sno=sc.sno */

select first.cname,first.cpno,second.cno from course first,course second ----------2 where first.cpno=second.cno

select student.*,sc.* ---------------3 from student right join sc on student.sno=sc.sno

select student.sno,sname from student,sc,(select student.sno

from student,sc -----4 导出表的使用 where sc.sno=student.sno and cno='2' group by student.sno)as result(sno)

17

where student.sno=sc.sno and cno='3' group by student.sno,student.sname

select student.sno,sname

from student,sc as x --------4法二

where student.sno=x.sno and cno='2' and exists(select student.sno from student,sc as y

where cno='3' and exists(select * from student,sc as z

where z.sno=y.sno and z.sno=x.sno))

select student.sno,sname

from student,sc as x,sc as y ------4法三

where student.sno=x.sno and x.sno=y.sno and x.cno='2' and y.cno='3' select *

from student as x,student y --------5 where x.sname='刘晨' and x.sage=y.sage

select sname,sage

18

from student,sc,course -----------6 where

student.sno=sc.sno

and

sc.cno=course.cno

course.cname='数据库'

select sname from student

where sage

from student ----------7 where sdept='is' ) and sdept< >'is' select sname from student

where sage'is'

select sname

from student ------------9 where sno in(select sno from sc

and

19

group by sno

having COUNT(*)=7)

select * --------------10 from student

where sdept='is' and ssex='男'

select distinct student.sname -------------11 from student,sc

where sc.cno='1' except(select student.sname from student,sc where sc.cno='2') select cno

from sc ---------------------12 where sc.sno not in(select sno from student

where student.sname='李丽')

select AVG(sage) --------------------------13 from student,sc

where student.sno=sc.sno and cno='3'

20

select cno,AVG(grade)

from sc -------------------14 group by cno

select cno,COUNT(sno)

from sc ------------------------15 group by cno

order by cno,COUNT(sno) desc

select sname

from student -------------------16 where sno>(select sno from student

where sname='刘晨')and sage<(select sage from student where sname='刘晨')

select x.sname ----------------16法二 from student x,student y

where x.sno>y.sno and x.sage

21

select distinct x.sname,x.sage

from student x,student y ------17

where x.ssex='男' and x.sage>y.sage and y.ssex='女'

-----实验四sql 的数据更新

insert into student(sno,sname,sage)values('95030','李莉','18') -----1

insert into sc(sno,cno)values('95030','1') --------2

update student1 ---------------3 set sage='20' where sdept='IS'

update sc1 --------------------4 set grade='0'

where sno in(select sno from student1 where sdept='MA')

update sc -----------------5

22

set grade=grade+5

where sno in(select student.sno from student,sc where ssex='女'

group by student.sno,grade

having grade

update sc

set grade=grade*1.05

where cno='2' and grade<75 ----------------6 update sc

set grade=grade*1.04 where cno='2' and grade>75

delete from student1 -----------7 where sno='95030'

delete from sc1

where grade is null--------------8

delete from sc1 where sno in(select sno

23

from student1 -------------------9 where sname='张娜')

delete from sc1 ----------10 where grade<60

delete from sc1 where sno in(select sno from student1 where sdept='MA')

delete from course1 where cno not in(select cno from sc1,course1

where sc1.cno=course1.cno)

create table stu(sno char(8),sname char(8),ssex char(4)) insert into stu(sno,sname,ssex) select sno,sname,ssex from student

where sno in(select sno from sc

24

where sno not in(select sno from sc

where grade is null) group by sno

having min(grade)>=80)

create table sdeptgrade(sdept char(4),avggrade char(2)) insert into sdeptgrade(sdept,avggrade) select sdept,AVG(grade) from sc,student group by sdept

25

本文来源：https://www.bwwdw.com/article/bm06.html