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

1、sql数据库系统原理上机实验综合版数据库原理实验指导书 数据库原理上机实验上机学时：8学时一、 上机性质、目的及任务：通过上机实践，配合课堂教学内容加深对数据库的基本概念和基本技术的理解，掌握数据库系统设计的基本思想和基本步骤，熟悉关系数据库的标准语言SQL，并对典型的关系数据库管理系统有较深入的了解，使学生的动手能力有较大的提高。二、 面向专业：计算机类各专业三、 实验指导书： 见下页四、 实验内容：实验一SQL数据定义 3实验二 SQL简单查询 6实验三 SQL 复杂查询 8实验四 SQL 数据更新 9实验一SQL数据定义一、实验目的(1)认识几种常见的数据库管理系统，熟悉它们的使用界面;

2、(2)熟练掌握建立数据库和表，向数据库输入数据、修改数据和删除数据的操作。(3) 熟悉SQL数据定义语言（DDL）二、实验内容(1)分别在Access 和SQL SERVER2005 中建立数据库并设计各表，输入多条实际数据，并实现数据的增、删、改操作。(2)用SQL语言进行基本表结构的定义、修改、删除，索引的建立和删除三、实验步骤：分别在ACCESS数据库管理系统和SQL SERVR 2005环境下利用图形操作界面（非SQL语句）实现以下操作：1、创建用于学生管理的数据库，数据库名为XSGL，包含学生信息，课程信息和选课信息。 数据库XSGL包含下列3 个表：(l） student：学生基本

3、信息。(2）course：课程信息表。(3）sc：学生选课表。各表的结构分别如表1、表2和表3 所示。表1 学生信息表：student列名数据类型长度完整性约束sno字符（文本）型8主键sname字符（文本）型4不为空ssex字符（文本）型1sage整数（数值）型sdept字符型10表2 课程信息表：course列名数据类型长度完整性约束cno字符（文本）型2主键cname字符（文本）型30credit整数（数值）型cpno字符（文本）型3表3 学生选课表：sc列名数据类型长度完整性约束sno字符（文本）型8主属性，外键cno字符（文本）型2主属性，外键grade整数（数值）型是提示：在不使用

4、SQL语句创建表的情况下，可通过ACCESS中的关系（菜单工具关系）和SQL SERVER 2005中的数据库关系图（数据库节点展开数据库关系图）实现外键的创建。外键字段和参照字段之间的数据类型以及长度要保持一致。2、输入表中的记录分别在student表、course表和sc表中输入如下表中的记录：snosnamessexsagesdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19IS95005刘云女18CScnocnamecreditpcno1数据库452数学63信息系统314操作系统465数据结构476数据处理37PASCAL语言46sno

5、cnograde9500119295001285950013889500229095002380950032859500415895004285 观察输入时有无提示错误，如果有如何修改，体会参照完整性的作用，弄清楚先输入那些表中记录，为什么？3、对表中的记录进行浏览、修改、删除操作。4、利用“分离数据库”和 “附加数据库”操作对SQL Server中创建的数据库做备份和还原操作。 5、 在SQL SERVER 2005中新建查询，建立到服务器的连接6、 用SQL语言CREATE TABLE语句创建实验一中学生表student、课程表course和选课表sc及其相应约束， 具体约束如下：表1 学

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

7、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语句删除索引;实验二 SQL简单查询目的：掌握简单数据查询操作。内容：使用各种查询条件完成指定的查询操作步骤：1）创建学生表student、课程表course和选课表SC,并输入数据

8、（注意数据的完整性。）;（可以使用实验一中已经建立的表和数据）2) 对各表中的数据进行不同条件的查询；包括的运算：投影、选择、比较运算符、逻辑运算符、字符匹配运算符、匹配列表范围、算术运算符、内部函数、排序、分组、分组函数使用（1） 查询全体学生的学号和姓名（2） 查询全体学生的详细记录（3） 查询软件学院的学生姓名、年龄、系别（4） 查询所有选修过课程的学生学号（不重复）（5） 查询考试不及格的学生学号（不重复）（6） 查询不是软件学院的学生性别、年龄、系别（7） 查询年龄1820岁的学生学号、姓名、系别、年龄；（8） 查询姓刘的学生情况（9） 查询姓刘或姓李的学生情况（10） 查询姓刘且名

9、字为两个字的学生情况（11） 查询1983年以后出生的学生姓名。（12） 利用内部函数 year()查找软件学院学生的出生年份（13） 利用字符转换函数实现字符联接。 Select sname + 年龄为+cast(sage as char(2)+岁 From student（14） 查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。（15） 查询学生总人数。（16） 查询选修了课程的学生人数。（17） 查询选修了7号课程的学生总人数和平均成绩（18） 查询选修6号课程学生的最好成绩（19） 查询每个系的系名及学生人数。（20） 查找每门课的选修人数及平均成绩（21）

10、 查找没有先修课的课程情况要求：1、将上述任务中完整的SQL语句调试并使之运行正确；2、写出实验报告；实验三 SQL 复杂查询目的：掌握复杂数据查询操作。内容：掌握各种连接查询、嵌套查询的使用步骤：1）实验一中的数据为基础2) 对各表中的数据进行不同条件的连接查询和嵌套查询； 查询每个学生及其选课情况； 查询每门课的间接先修课 将STUDENT,SC进行右连接 查询既选修了2号课程又选修了3号课程的学生姓名、学号； 查询和刘晨同一年龄的学生 选修了课程名为“数据库”的学生姓名和年龄 查询其他系比IS系任一学生年龄小的学生名单 查询其他系中比IS系所有学生年龄都小的学生名单 查询选修了全部课程的

11、学生姓名 查询计算机系学生及其性别是男的学生 查询选修课程1的学生集合和选修2号课程学生集合的差集 查询李丽同学不学的课程的课程号 查询选修了3号课程的学生平均年龄 求每门课程学生的平均成绩 统计每门课程的学生选修人数（超过3人的才统计）。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列 查询学号比刘晨大，而年龄比他小的学生姓名。 求年龄大于所有女同学年龄的男同学姓名和年龄要求：1、将上述任务中完整的SQL语句调试并使之运行正确；2、写出实验报告；实验四 SQL 数据更新 目的：掌握SQL的常用数据更新操作，熟练应用INSERT,UPDATE,DELETE语句。内容：

12、1） 应用INSERT,UPDATE,DELETE语句进行更新操作；a) 插入如下学生记录（学号：95030，姓名：李莉，年龄：18）b) 插入如下选课记录（95030，1）c) 计算机系学生年龄改成20d) 把数学系所有学生成绩改成0e) 把低于总平均成绩的女同学成绩提高5分f) 修改2号课程的成绩，若成绩小于75分提高5，成绩大于75时提高4（两个语句实现，注意顺序）g) 删除95030学生信息h) 删除SC表中无成绩的记录i) 删除张娜的选课记录j) 删除不及格的学生选课记录k) 删除数学系所有学生选课记录l) 删除所有未被选修的课程m) 查询每一门课程成绩都大于等于80分的学生学号、姓

13、名和性别，把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中n) 建立一个sdeptgrade 表，包含（sdept,avggrade）字段，对每一个系，求学生的成绩，并把结果存入sdeptgrade2）熟练掌握INSERT,UPDATE,DELETE语句并能综合应用；要求：1、将上述任务中完整的SQL语句调试并使之运行正确；2、写出实验报告；附录（总版）：语句清单-sql 查询实验一代码create table student1 (sno char(8) primary key,sname char(4) not null, -创建表student1ssex char(2

14、) default男,check(ssex in(男,女),sage int default(是),sdept char(10) default(否),)create table course1(cno char(2) primary key,cname char(30), -创建表course1credit int,cpno char(3),)create table sc1(sno char(8) foreign key references student1(sno), 创建表sc1cno char(2) foreign key references course1(cno),grade

15、 int default(是) check(grade between 0 and 100),)-第题alter table student1 add scome intalter table student1 drop column sdeptselect 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

16、 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 create unique index student1_index on student1(sname d

17、esc)drop index student1_index on student1create table teacher(tno char(4) primary key,tname char(8) not null,title char(5) ,)drop table teacher -sql查询实验二的代码SELECT sno,sname -1from studentselect * -2from studentselect sname,sage,sdept -3from studentwhere student.sdept=ISselect distinct student.sno -4

18、from student,scwhere sc.sno=student.snoselect distinct student.sno -5from student,scwhere sc.sno=student.sno and grade1983- select year(sage) 这是取单个人的出生年份的用法，调用year函数select year(getdate()-s.sage+1from student as s -12where sdept=CSSelect sname + 年龄为+cast(sage as char(2)+岁From student -13select *from

19、student -14 order by sdept,sage descselect COUNT(sno)-15from studentselect COUNT(distinct sno)from sc -16/*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 -17where student.sno=sc.sno and cno=7selec

20、t max(grade)from sc -18where cno=6select sdept,COUNT(sno)from student -19group by sdeptselect COUNT(student.sno),AVG(grade)from student,sc -20where student.sno=sc.snogroup by cno/* -20第二种解法select cname,COUNT(student.sno),AVG(grade)from student,sc,course where student.sno=sc.sno and o=ogroup by o,cna

21、me*/select * -为什么这里查询不到结果from course -21where cpno is null-sql 查询实验三代码 -1select student.*,o,sc.grade -左外连接(显性连接) from student left join sc on student.sno=sc.sno/* select student.*,o,sc.gradefrom student inner join sc -内连接法（显性连接）on student.sno=sc.sno*/* select student.*,o,sc.grade -隐性连接from student,s

22、cwhere student.sno=sc.sno*/select ame,first.cpno,ofrom course first,course second -2where first.cpno=oselect student.*,sc.* -3from student right join sc on student.sno=sc.snoselect student.sno,snamefrom student,sc,(select student.sno from student,sc -4 导出表的使用 where sc.sno=student.sno and cno=2 group

23、 by student.sno)as result(sno)where student.sno=sc.sno and cno=3group by student.sno,student.snameselect student.sno,snamefrom student,sc as x -4法二where student.sno=x.sno and cno=2 and exists(select student.snofrom student,sc as ywhere cno=3 and exists(select *from student,sc as zwhere z.sno=y.sno a

24、nd z.sno=x.sno)select student.sno,snamefrom student,sc as x,sc as y -4法三where student.sno=x.sno and x.sno=y.sno and o=2 and o=3 select *from student as x,student y -5where x.sname=刘晨 and x.sage=y.sageselect sname,sagefrom student,sc,course -6where student.sno=sc.sno and o=o and ame=数据库select snamefr

25、om studentwhere sageany(select sage from student -7 where sdept=is ) and sdeptis select snamefrom studentwhere sageall(select sage -8 from student where sdept=is) and sdeptisselect snamefrom student -9where sno in(select sno from sc group by sno having COUNT(*)=7) select * -10from studentwhere sdept=is and ssex=男select distinct student.sname -11from student,scwhere o=1 except(select student.sname from student,sc where o=2) select cnofrom sc -