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

《数据库原理》实验指导书《数据库原理》上机实验上机学时：8学时一、上机性质、目的及任务：通过上机实践，配合课堂教学内容加深对数据库的基本概念和基本技术的理解，掌握数据库系统设计的基本思想和基本步骤，熟悉关系数据库的标准语言SQL，并对典型的关系数据库管理系统有较深入的了解，使学生的动手能力有较大的提高。

二、面向专业：计算机类各专业三、实验指导书：见下页四、实验内容：实验一SQL数据定义 (3)实验二SQL简单查询 (6)实验三SQL 复杂查询 (7)实验四SQL 数据更新 (8)实验一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 字符（文本）型8 主键sname 字符（文本）型 4 不为空ssex 字符（文本）型 1sage 整数（数值）型sdept 字符型10表2 课程信息表：course列名数据类型长度完整性约束cno 字符（文本）型 2 主键cname 字符（文本）型30credit 整数（数值）型cpno 字符（文本）型 3表3 学生选课表：sc列名数据类型长度完整性约束sno 字符（文本）型8 主属性，外键cno 字符（文本）型 2 主属性，外键grade 整数（数值）型是提示：在不使用SQL语句创建表的情况下，可通过ACCESS中的关系（菜单—工具—关系）和SQL SERVER 2005中的数据库关系图（数据库节点展开—数据库关系图）实现外键的创建。

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

2、输入表中的记录分别在student表、course表和sc表中输入如下表中的记录：sno sname ssex sage sdept95001 李勇男20 CS95002 刘晨女19 IS95003 王敏女18 MA95004 张立男19 IS95005 刘云女18 CScno cname credit pcno1 数据库 4 52 数学 63 信息系统 3 14 操作系统 4 65 数据结构 4 76 数据处理 37 PASCAL语言 4 6sno cno grade95001 1 9295001 2 8595001 3 8895002 2 9095002 3 8095003 2 8595004 1 5895004 2 85观察输入时有无提示错误，如果有如何修改，体会参照完整性的作用，弄清楚先输入那些表中记录，为什么？3、对表中的记录进行浏览、修改、删除操作。

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

5、在SQL SERVER 2005中新建查询，建立到服务器的连接6、用SQL语言CREATE TABLE语句创建实验一中学生表student、课程表course和选课表sc及其相应约束，具体约束如下：表1 学生信息表：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语言ALTER语句修改表结构;1）STUDENT表中增加一个字段入学时间scome，2）删除STUDENT表中sdept字段；3）删除创建的SC表中CNO字段和COURSE表CNO字段之间的外键约束；4）重建3）中删除的约束9、重新定义一个简单表，然后用SQL语言DROP语句删除该表结构;10、用SQL语言CREA TE INDEX语句定义表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）利用字符转换函数实现字符联接。

Select sname + ‘年龄为’+cast(sage as char(2))+’岁’From student（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,UPDA TE,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分的学生学号、姓名和性别，把值送往另一个已经存在的基本表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)default'男',check(ssex in('男','女')),sage int default('是'),sdept char(10)default('否'),)create table course1(cno char(2)primary key,cname char(30),------创建表course1 credit int,cpno char(3),)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 student1add scome intalter table student1drop column sdeptselect namefrom sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_idwhere f.parent_object_id=object_id('sc1')--------查找出约束名，然后再用drop语句删除约束alter table sc1drop constraint FK__sc1__cno__740F363E--------在括号中补充用上面语句查找出的约束名alter table sc1add constraint[pk_sc1]primary key (cno)alter table sc1add constraint[fk_sc1_course1]foreign key (cno) references course1(cno)alter table sc1drop constraint[fk_sc1_course1]create unique index student1_index on student1(sname desc) 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-----3where student.sdept='IS'select distinct student.sno-----4from student,scwhere sc.sno=student.snoselect distinct student.sno----5from student,scwhere sc.sno=student.sno and grade<60select ssex,sage,sdept-----------6from studentwhere sno not in(select snofrom studentwhere sdept='IS')/* 这是exists的用法select ssex,sage,sdeptfrom student as ywhere not exists(select *where Y.sdept='CS')*/select sno,sname,sage,sdept-------7from studentwhere sage between 18 and 20;select*---------------8from studentwhere sname like'刘%'select*-----------------9from studentwhere sname like'[刘李]%'select sname----------------10from studentwhere sname like'刘_ _'--中文占用两个字符，所以这里用两个_select sname-------------------11from studentwhere 2013-sage>1983-- select year(sage) 这是取单个人的出生年份的用法，调用year函数select year(getdate())-s.sage+1from student as s-------12where sdept='CS'Select sname+'年龄为'+cast(sage as char(2))+'岁'From student---------13select*from student---------------------14order 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='7'select max(grade)from sc-------------------18where cno='6'select sdept,COUNT(sno)from student-------------------19group by sdeptselect COUNT(student.sno),A VG(grade)from student,sc----------20where student.sno=sc.snogroup by cno/* -----------------20第二种解法select cname,COUNT(student.sno),A VG(grade)from student,sc,coursewhere student.sno=sc.sno and o=ogroup by o,cname*/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,scwhere 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.snofrom student,sc-----4 导出表的使用where sc.sno=student.sno and cno='2'group by student.sno)as result(sno)where student.sno=sc.sno and cno='3'group 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.sno from student,sc as ywhere cno='3'and exists(select*from student,sc as zwhere z.sno=y.sno and 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 snamefrom studentwhere sage<any(select sagefrom student----------7where sdept='is')and sdept<>'is'select snamefrom studentwhere sage<all(select sage------8from studentwhere sdept='is')and sdept<>'is'select snamefrom student------------9where sno in(select snofrom scgroup by snohaving COUNT(*)=7)select*--------------10from studentwhere sdept='is'and ssex='男'select distinct student.sname-------------11 from student,scwhere o='1'except(select student.snamefrom student,scwhere o='2')select cnofrom sc---------------------12 where sc.sno not in(select snofrom studentwhere student.sname='李丽')select A VG(sage)--------------------------13 from student,scwhere student.sno=sc.sno and cno='3'select cno,A VG(grade)from sc-------------------14group by cnoselect cno,COUNT(sno)from sc------------------------15group by cnoorder by cno,COUNT(sno)descselect snamefrom student-------------------16where sno>(select snofrom studentwhere sname='刘晨')and sage<(select sagefrom studentwhere sname='刘晨')select x.sname----------------16法二from student x,student ywhere x.sno>y.sno and x.sage<y.sage and y.sname='刘晨'select distinct x.sname,x.sagefrom student x,student y------17where 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')--------2update student1---------------3set sage='20'where sdept='IS'update sc1--------------------4set grade='0'where sno in(select snofrom student1where sdept='MA')update sc-----------------5set grade=grade+5where sno in(select student.snofrom student,scwhere ssex='女'group by student.sno,gradehaving grade<A VG(grade))update scset grade=grade*1.05where cno='2'and grade<75 ----------------6 update scset grade=grade*1.04where cno='2'and grade>75delete from student1-----------7where sno='95030'delete from sc1where grade is null--------------8delete from sc1where sno in(select snofrom student1-------------------9where sname='张娜')delete from sc1----------10 where grade<60delete from sc1where sno in(select snofrom student1where sdept='MA')delete from course1where cno not in(select cnofrom sc1,course1where o=o)create table stu(sno char(8),sname char(8),ssex char(4)) insert into stu(sno,sname,ssex)select sno,sname,ssexfrom studentwhere sno in(select snofrom scwhere sno not in(select snofrom scwhere grade is null)group by snohaving min(grade)>=80)create table sdeptgrade(sdept char(4),avggrade char(2)) insert into sdeptgrade(sdept,avggrade)select sdept,A VG(grade)from sc,studentgroup by sdept。