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

1、字符（文本）型8主键sname4不为空ssex1sage整数（数值）型sdept字符型10表2 课程信息表：coursecno2cname30creditcpno3表3 学生选课表：sc主属性，外键grade是提示：在不使用SQL语句创建表的情况下，可通过ACCESS中的关系（菜单工具关系）和SQL SERVER 2005中的数据库关系图（数据库节点展开数据库关系图）实现外键的创建。外键字段和参照字段之间的数据类型以及长度要保持一致。2、输入表中的记录分别在student表、course表和sc表中输入如下表中的记录：95001李勇男20CS95002刘晨女19IS95003王敏18MA950

2、04张立95005刘云pcno数据库5数学6信息系统操作系统数据结构7数据处理PASCAL语言928588908058 观察输入时有无提示错误，如果有如何修改，体会参照完整性的作用，弄清楚先输入那些表中记录，为什么？3、对表中的记录进行浏览、修改、删除操作。4、利用“分离数据库”和 “附加数据库”操作对SQL Server中创建的数据库做备份和还原操作。5、 在SQL SERVER 2005中新建查询，建立到服务器的连接6、 用SQL语言CREATE TABLE语句创建实验一中学生表student、课程表course和选课表sc及其相应约束， 具体约束如下：默认值为男取值为男或女否取值在0-1

3、00之间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语句删除索引;实验二 SQL简单查询目的：掌握简单数据查询操作。内容：使用各种查询条件完成指定的查询

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

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

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

7、其他系中比IS系所有学生年龄都小的学生名单 查询选修了全部课程的学生姓名 查询计算机系学生及其性别是男的学生 查询选修课程1的学生集合和选修2号课程学生集合的差集 查询李丽同学不学的课程的课程号 查询选修了3号课程的学生平均年龄 求每门课程学生的平均成绩 统计每门课程的学生选修人数（超过3人的才统计）。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列 查询学号比刘晨大，而年龄比他小的学生姓名。 求年龄大于所有女同学年龄的男同学姓名和年龄实验四 SQL 数据更新 掌握SQL的常用数据更新操作，熟练应用INSERT,UPDATE,DELETE语句。1） 应用INSERT,

8、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分的学生学号、姓名和性别，把值送往另一个

9、已经存在的基本表STU（SNO,SNAME,SSEX）中n） 建立一个sdeptgrade 表，包含（sdept,avggrade）字段，对每一个系，求学生的成绩，并把结果存入sdeptgrade2）熟练掌握INSERT,UPDATE,DELETE语句并能综合应用；附录（总版）：语句清单-sql 查询实验一代码create table student1 （sno char（8） primary key,sname char（4） not null, -创建表student1ssex char（2） default男,check（ssex in（,女）,sage int default（是）,s

10、dept 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 int default（） check（grade between 0 and 100）,-第题alter

11、 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 drop constraint FK_sc1_cno_740F363E -在括号中补充用上面语句查找出的约束名

12、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 desc）drop index student1_index on student1create table te

13、acher（tno char（4） primary key,tname char（8） not null,title char（5） ,drop table teacher -sql查询实验二的代码SELECT sno,sname -1from studentselect * -2select sname,sage,sdept -3where student.sdept=ISselect distinct student.sno -4from student,scwhere sc.sno=student.snoselect distinct student.sno -5where sc.sno

14、=student.sno and grade1983- select year（sage） 这是取单个人的出生年份的用法，调用year函数select year（getdate（）-s.sage+1from student as s -12Select sname + 年龄为+cast（sage as char（2）+岁From student -13select *from student -14 order by sdept,sage descselect COUNT（sno）-15select COUNT（distinct sno）from sc -16/*SELECT COUNT（*）

15、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=7select max（grade）from sc -18where cno=6select sdept,COUNT（sno）from student -19group by sdeptselect COUNT（student.sno）,AVG（grade）f

16、rom 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,cnameselect * -为什么这里查询不到结果from course -21where cpno is null-sql 查询实验三代码 -1select student.*,o,sc.grade -左外连接（显性连接） from student

17、left join sc on student.sno=sc.sno/* select student.*,o,sc.gradefrom student inner join sc -内连接法（显性连接）on student.sno=sc.snoselect student.*,o,sc.grade -隐性连接select ame,first.cpno,ofrom course first,course second -2where first.cpno=oselect student.*,sc.* -3from student right join sc on student.sno=sc.

18、snoselect student.sno,snamefrom student,sc,（select student.sno from student,sc -4 导出表的使用 where sc.sno=student.sno and cno=2 group by student.sno）as result（sno）3group by student.sno,student.snamefrom student,sc as x -4法二where student.sno=x.sno and cno= and exists（select student.snofrom student,sc as

19、y and exists（select *from student,sc as zwhere z.sno=y.sno and z.sno=x.sno）from student,sc as x,sc as y -4法三where student.sno=x.sno and x.sno=y.sno and o= and o=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

20、and ame=数据库select snamewhere sageany（select sage from student -7 where sdept=is ） and sdeptall（select sage -8 from student and sdeptfrom student -9where sno in（select sno from sc group by sno having COUNT（*）=7） select * -10 and ssex=select distinct student.sname -11where o=1 except（select student.sname from student,sc where o=select cnofrom sc -12where sc.sno not in（select sno from st