数据库实验报告(SQL)

四川师范大学计算机学院 实 验 报 告 册 院系名称： 计算机科学学院 课程名称： 数据库原理概论 实验学期 2013 年至 2014 年 第 二 学期 专业班级： 网络工程3班 姓名： 学号： 指导教师： 俞晓 实验最终成绩： 数据库原理实验大纲 课程总学时：64 学分：2 实验学时：32 实验个数： 7 实验学分： 1 课程性质：（专业必修） 适用专业： 计算机软件工程，计算机科学技术，网络工 程，电子商务 教材及参考书：数据库系统概论 大纲执笔人： 俞晓 大纲审定人： 1、 实验课的性质与任务 数据库技术是计算机科学技术中发展最快的领域之一，也是应用 最广的技术之一，它已成为计算机信息系统与应用系统的核心技术 和重要基础。 本实验课程主要是在SQL SERVER 下实现SQL语言的基本操 作：建立、修改、删除表格等数据库对象，插入、修改、删除表格 中的数据，对数据做多种查询。并熟悉使用SQL SERVER的系统工 具创建数据库，用户等。最后用VB连接SQL SERVER做一个简单 的数据库系统。该课程注重教学体系的严密性、教学内容的实用 性、知识体系的扩展性。通过练习，提高学生的动手能力，使学生 能较熟练的在SQL SERVER数据库管理系统中实现数据管理和维 护。 二、实验课程目的与要求 1实验目的：本课程的目的和任务是使学生全面地了解和掌握数 据库的原理，SQL SERVER系统的基本操作和应用技术，以适应社 会对数据库技术应用的大量需求。 2实验要求： 1）实验安排在上机时间进行。 2）实验以前，学生应对实验设计的相关知识点和相关设计技术有 所了解 3）每个实验要求写出实验报告（实验报告包括：实验内容、目 的、实现方法、调试结果） 三、实验项目及内容提要 网络操作系统实验课程 （课程编号1107343002） 序 号 实 验 项 目 编 号 实验名 称 学 时 必 做 选 做 学 分 数 实验类型内容提要 基 本 操 作 验 证 综 合 设 计 1 熟悉 SQL SERVER 的环境 2 必 做 基 本 操 作 在 WINDOWS 操作系统下 熟悉SQL SERVER的常 用工具。 2 建立表 格，并 插入若 干记录 2 必 做 基 本 操 作 在查询分析器 中使用Create Table语句建立 Student，SC， Course三张表 格，并用Insert 语句向这三张 表格里添加至 少10条记录。 3 修改表 格结 构，修 改和删 除表格 中的数 4 必 做 基 本 操 作 用ALTER语 句修改表结 构：添加 列，修改列 定义，删除 列。使用 UPDATE和 DELETE语句修 据改和删除 Student,sc, course表格中 的数据。 4 查询 （多表 查询， 嵌套查 询，分 组查 询） 12 必 做 基 本 操 作 实现单表和多 表的普通查询 和嵌套查询。 包括返回单值 的子查询和返 回多值的子查 询。使用5个聚 合函数以及 GROUP BY子句 和HAVING子句 实现分组查询 5 为表格 建立约 束，修 改约束 和查询 约束 4 必 做 基 本 操 作 为 Student,sc, course表建立 主键约束，唯 一性约束，检 查约束，缺省 约束，外键约 束。 6 Sql Server数 据库安 全管理 4 必 做 基 本 操 作 建立用户，为 用户赋权限， 收回权限，建 立角色，给用 户赋角色 7 使用 ADO技 术连接 数据库 4 必 做 综 合 在高级语言中 通过ADO连接 SQL SERVER 数据库，做一 些简单应用 四、实验内容安排： 实验1 熟悉SQL SERVER的环境 （ 操作性实验 2学时） 实验目的 了解SQL Server数据库的各个工具软件 实验内容 使用SQL Server的相关工具，其中包括查询分析器和企业管理器，在 查询分析器里写简单的SQL语句，在企业管理器中建立表格等。 工具及准备 SQL SERVER数据库 实验2 建立表格，并插入若干记录 （ 操作性实验 2学时） 实验目的 学会使用Create Table语句和Insert语句 实验内容 在SQL SERVER的查询分析器中使用Create Table语句建立Student， SC，Course三张表格（包括主键），并用Insert语句向这三张表格里 添加至少10条记录。 一、建表 建立学生表 Create Table Student ( sno char(10) primary key , sname varchar(20) not null, sage smallint, ssex char(2), sdept varchar(20) 建立课程表 Create Table Course ( cno char(10), primary key (cno) , cname varchar(20) , cpno char(10), credit smallint ) 建立选课表 Create Table SC (sno char(10), cno char(10), grade smallint, primary key (sno, cno) ) 2、 插入数据 插入学生数据 insert into student values(001,张 四,20,男,CS); insert into student values(002,刘 五,19,女,IS); insert into student values(003,邓小 庆,21,女,MA); insert into student values(004,彭大 琛,22,男,IS); insert into student values(005,张 三,19,男,CS); insert into student values(006,李 四,21,女,IS); insert into student values(007,雷 锋,20,男,MA); insert into student values(008,刘 健,18,女,CS); insert into student values(009,陈 东,23,女,MA); insert into student values(010,代 大,19,男,IS); insert into student values(011,都敏 俊,20,女,CS); insert into student values(012,余 小,20,女,MA); insert into student values(013,余 雨,20,女,MA); 插入课程数据 insert into course values(1001,数据库,5,4) insert into course values(1002,数学, ,2) insert into course values(1003,信息系统, ,4) insert into course values(1004,操作系统,6,3) insert into course values(1005,数据结构,7,4) insert into course values(1006,数据处理, ,2) insert into course values(1007,p语言,6,4) insert into course values(1008,C语言, ,4) 插入选课数据 insert into sc values(001,1001,92) insert into sc values(001,1002,90) insert into sc values(001,1003,95) insert into sc values(001,1004,56) insert into sc values(002,1001,93) insert into sc values(002,1002,78) insert into sc values(002,1005,82) insert into sc values(002,1006,65) insert into sc values(002,1007,71) insert into sc values(003,1006,88) insert into sc values(003,1008,66) insert into sc values(004,1001,59) insert into sc values(004,1003,74) insert into sc values(004,1005,66) insert into sc values(004,1008,66) insert into sc values(005,1002,70) insert into sc values(005,1005,87) insert into sc values(005,1006,64) insert into sc values(005,1007,78) insert into sc values(005,1008,80) insert into sc values(006,1002,90) insert into sc values(006,1004,67) insert into sc values(006,1005,89) insert into sc values(006,1008,70) insert into sc values(007,1003,76) insert into sc values(007,1004,46) 工具及准备 SQL SERVER数据库 实验3 修改表格结构，修改和删除表格中的数据 （ 操作性实验 4学时） 实验目的 用ALTER语句修改表结构：添加列，修改列定义，删除 列。使用UPDATE和DELETE语句修改和删除Student,sc, course表格中 的数据。 实验内容 添加，修改和删除列：为STUDENT表添加一列，观察添加的列对应 于已经有的数据行的数据是多少？为其修改数据；修改STUDENT表的 列定义，删除才添加的多余的列。 使用UPDATE和DELETE语句修改和删除Student, sc, course表格中的 数据（注意表格中的主键），并对表格做一些普通查询：单表查询，要 求使用LIKE，BETWEEN.AND等比较条件。 一、 1、添加一列，并观察 代码：alter table student add sarea varchar(50); 2、修改列定义： 代码：alter table student alter column sarea varchar(100); 3、删除才添加的多余的列： 代码：alter table student drop column sarea; 二、UPDATE update student set classno=1 where sno = 001; update student set classno=2 where sno = 002; update student set classno=1 where sno = 003; update student set classno=3 where sno = 004; update student set classno=2 where sno = 005; update student set classno=1 where sno = 006; update student set classno=3 where sno = 007; update student set classno=2 where sno = 008; update student set classno=3 where sno = 009; update student set classno=2 where sno = 010; update student set classno=1 where sno = 011; update student set classno=T+classno where sno=001; update student set classno=T+classno where sno=003; update student set classno=rtrim(classno) + T where sno =002; update student set classno=2 where sno = 002; update student set classno= substring(classno,2,10) where sno = 003; update student set classno= substring(classno,1,len(classno) -1) where sno = 002; 三、DELETE delete from student where sno = 001; delete from student where classno is null delete from sc where grade 90 -查询和数据库相同学分的课程 select cname from course where credit in (select credit from course where cname = 数据库) and cname 数据库 -查询选修了数据库课程的学生的学号。 select sno from sc where cno in (select cno from course where cname = 数据库) -查询成绩最高的学生的姓名 select sname from student where sno in(select sno from sc where grade =all(select grade from sc) select *from sc select * from student -查询CS系成绩最高的学生的学号、姓名以及成绩。 select student.sno,sname,grade from student,sc where sdept = cs and grade = all (select grade from sc) and student.sno=sc.sno -查询数据库课程成绩最高的学生的姓名。 select sname from student where sno in (select sno from sc where cno in (select cno from course where cname = 数据 库) and grade = all(select grade from sc where cno in (select cno from course where cname = 数据 库) -查询每门课程的平均成绩 select avg(grade)from sc group by cno -查询每个系学生的最高成绩 select sdept ,MAX(grade) from SC ,Student where sc.sno = student.sno group by sdept -查询平均分在以上的课程 select cno,AVG(grade ) from SC group by cno having AVG (grade)75 select AVG(grade) from SC where cno = 1004 select * from sc -查询女同学中平均成绩最高的学生所在的系。 select sdept from student where sno in(select student.sno from sc,student where student.sno=sc.sno and ssex=女 group by student.sno having avg(grade)=all (select avg(grade) savg from sc,student where student.sno=sc.sno and ssex=女 group by student.sno) select *from SC,Student where ssex=女 order by grade desc -查询选课人数最多的课程 select cno , count(cno) ccno from SC group by cno having COUNT (cno )= all (select COUNT(cno) from SC group by cno ) select *from Course -检索所有学生中年龄最大的学生的姓名及年龄。 select sname,sage from Student where sage =all (select sage from Student) -求每一个学生的最高分和最低分。 select sno,MAX(grade) mgrade,MIN(grade) mingrade from SC group by sno -查询CS系所有男同学考C05课程的成绩，列出这些学生的学号，姓 名，成绩，并按成绩降序排列。 select student.sno,sname,grade from Student,SC where sdept = CS and Student.sno=SC.sno and ssex=男 and cno = 1005 order By grade desc select sname,grade from SC,Student where sc.sno=Student.sno and ssex = 男 and sdept=cs and cno=1005 Select sname from student where sage some (select sage from student where ssex=男) and ssex=女 Select cno ,avg(grade) as avg from sc group by cno -检索选修了“C语言”课程的学生的姓名（可用子查询IN或 Exists） select sname from Student where sno in(select sno from SC where cno in(select cno from Course where cname = C语言) -5检索选修了课程号为C01或C02课程，且成绩高于或等于分的学生 的姓名，课程名和成绩 select sname,cname,grade from Student,SC,Course where Student.sno=sc.sno and SC.cno=Co and o in(1001,1002) and grade =70 -有问题检索所有学生的姓名、所选课程的课程名和成绩以及课程号， 并且按成绩的降序和课程号的升序进行排列（使用外连接将没有选课的 同学列出来）。 select sname,cno,grade from Student,SC where Student left join sc on student.sno = sc.sno and sc.sno not in(select sno from SC) order by cno desc, grade asc -列出没有选课的学生姓名 select sname from Student where sno not in (select sno from SC) select * from SC where sno =(select sno from Student where sname = 代大) -8. 列出平均分最高的学生所在系的所有学生的姓名 select sname from Student where sno in (select sno from Student where sdept in (select sdept from Student where sno in (select sno from SC group by sno having AVG(grade)=all(select AVG(grade)from SC group by sno ) ) 工具/准备工作 SQL SERVER数据库 实验5 为表格建立约束，修改约束和查询约束 （ 操作性实验 4学时） 实验目的 使用ALTER语句和CREATE语句建立、修改、删除和查询约 束 实验内容 为Student,sc, course表建立和删除主键约束，唯一性约 束，检查约束，缺省约束，外键约束。 暂时关闭某个约束。 使用系统存储过程， sp_help, sp_helpconstraint等对约束进行查询 和管理 -1、为Student表的Sage（小于），Ssex（M或F，缺省为M）添加 约束。 alter table student add constraint stu_sage check(sage30); alter table student add constraint stu_ssex check(ssex in (m,f); alter table student add constraint sex default m for ssex; select * from student; -2、为SC表的sno（外码），cno（外码），grade（到分）添加约 束。 alter table sc add constraint sc_sno foreign key(sno) references student(sno), foreign key(cno) references course(cno); alter table sc add constraint sc_garde check(grade between 0 and 100); -3、为Course表的Cname（唯一)，Credit（到）添加约束。 alter table course add constraint c_cname unique cname; alter table course add constraint c_credit check(credit between 1 and 5); -删除约束 alter table student drop stu_sage; 工具/准备工作 SQL SERVER数据库 实验6 Sql Server数据