数据库上机实验(共12页).doc
精选优质文档-倾情为你奉上学号 姓名 张宏源 班级 物联网1班 上机实验四SELECT语句基本格式的使用一、实习目的：掌握SELECT的基本使用格式，能使用SQL Server对表作简单查询。二、实习准备：1. 复习第三章3.4节中SELECT语句的基本使用格式。2. 复习SQL中五种库函数：AVG、SUM、MAX、MIN、COUNT；3. 完成习题三第12题中的各项操作的SQL语句。三、实习内容：1. 验证习题三第12题中的各项操作的SQL语句。 找出所有被学生选修了的课程号；Select DistinctCnoFrom GradeOrder by Cno 找出01311班女学生的个人信息；Select *From Studentwhere Ssex='女' and Clno=01311 找出01311班、01312班的学生姓名、性别、出生年份；Select Sname,Ssex,2014-Sage as birthyearFrom Studentwhere Clno='01311'or Clno='01312' 找出所有姓李的学生的个人信息；Select *From Studentwhere Sname like '李%' 找出学生李勇所在班级的学生人数；Select number FROM Classwhere clno=(select Clno from Student where Sname='李勇' ) 找出课程名为操作系统的平均成绩、最高分、最低分；Select AVG(Gmark),MAX(Gmark),MIN(Gmark) FROM Gradewhere Cno=(Select Cno from Course where Cname='操作系统' ) 找出选修了课程的学生人数；Select COUNT(Sno) as renshuFROM Grade 找出选修了课程操作系统的学生人数。Select COUNT(Sno) as renshuFROM Gradewhere Cno=(Select Cno from Coursewhere Cname='操作系统' )2. 试一下以下语句是否正确：SELECT eno，basepay，serviceFROM salaryWHERE basepay<AVG（basepay）答：不对，聚合函数不应该出现在where语句中，除非该聚合位于HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用四、上机实验收获（感想）：更加熟悉了SQL基础语言的运用，实际操作后弥补了理论想象的不足五、思考题：什么情况下需要使用关系的别名？别名的作用范围是什么？1. 简化书写2. 子查询3. 为了把先后查询的同一关系区分开来学号 姓名 班级 上机时间 上机实验五SELECT语句高级格式和完整格式的使用一、实习目的：掌握SELECT语句的嵌套使用方法，能使用SQL Server2000对表作复杂查询。二、实习准备：1. 复习第三章3.4节中SELECT语句的高级格式和完整格式的使用。2. 了解库函数在分组查询中的使用规则；3. 完成习题三第13、14题中的各项操作的SQL语句。三、实习内容：完成以下各项操作的SQL语句： 找出与李勇在同一个班级的学生信息；Select *FROM Studentwhere Clno=(Select Clno from studentwhere Sname='李勇' ) 找出选修了课程操作系统的学生学号和姓名；Select Sno,Snamefrom studentwhere sno in( Select distinct Sno from Gradewhere Cno =(Select Cnofrom Coursewhere Cname='操作系统') 找出年龄介于学生李勇和25岁之间的学生信息；（已知李勇年龄小于25岁）Select *from studentwhere Sage in(Select Sagefrom Student)and Sage<25 找出所有没有选修1号课程的学生姓名Select distinct s.snamefrom Student s LEFT JOIN Grade gon s.sno=g.snowhere CNO != 1 查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列；Select Sno,Gmarkfrom gradewhere Cno=3Order by Gmark DESC 求每个课程号及相应的选课人数；Select Cno,count(cno) as 人数from gradegroup by cnoorder by cno查询选修了3门以上课程的学生学号。Select snofrom Gradegroup by snohaving COUNT(sno)>3四、上机实验收获（感想）：五、思考题：1 用UNION或UNION ALL将两个SELECT命令结合为一个时，结果有何不同？。UNION 命令只会选取不同的值，UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值2 当既能用连接词查询又能用嵌套查询时，应该选择哪种查询较好？为什么？选用连接词查询。 因为连接词查询,效率比嵌套查询高3 库函数能否直接使用在：SELECT选取目标、HAVING子句、WHERE子句、GROUP BY列名中？库函数可以使用在SELECT选取目标、HAVING字句、WHERE子句，不能使用在GROUP BY列名学号 姓名 班级 上机时间 上机实验六SQL的存储操作一、实习目的：掌握用交互式SQL语句对已建基本表进行存储操作：修改、删除、插入，加深对数据的完整性的理解。二、实习准备：1. 复习数据的完整性，在进行数据的修改、删除、插入时，要注意保持数据的一致性。2. 复习第三章3.5节UPDATE、DELETE、INSERT语句与子查询的结合使用；3. 完成习题三15题中的各项操作的SQL语句。三、实习内容：1、完成以下各项操作的SQL语句：将01311班的全体学生的成绩置零update Grade set Gmark=0where Sno in(select Sno from Studentwhere Clno='01311')删除01311班全体学生的选课记录； Delete from Gradewhere sno in(select snofrom Studentwhere clno='01311')学生李勇已退学，从数据库中删除有关他的记录；delete from Gradewhere Sno in(select Sno from Studentwhere Sname='李勇')update Class set Number=Number-1where Clno in(select Clno from Studentwhere Sname='李勇')update Class set Monitor=casewhere Clno in(select Clno from Studentwhere Sname='李勇')delete from Studentwhere Sname='李勇'对每个班，求学生的平均年龄，并把结果存入数据库；alter table Class add pingjunnianlin smallint nullupdate Class set pingjunnianlin = (select AVG(Sage) from Student where Clno='00311')where Clno='00311'update Class set pingjunnianlin = (select AVG(Sage) from Student where Clno='00312')where Clno='00312'update Class set pingjunnianlin = (select AVG(Sage) from Student where Clno='01311')where Clno='01311'2、把所有工程师的基本工资（Basepay）增加100，试一试以下的UPDATE语句对不对：UPDATE salarySET basepay=basepay+100WHERE eno in (SELECT eno FROM employeeWHERE title=工程师)对的四、上机实验收获（感想）：五、思考题：DROP命令和DELETE命令的本质区别是什么？DROP是删除表DELETE是删除表中的记录学号 姓名 班级 上机时间 上机实验七视图的建立及操作一、实习目的：掌握创建、删除和查询视图的方法，验证可更新视图和不可更新视图。二、实习准备：1. 复习第三章3.6节视图；2. 完成习题三第16题中的各项操作的SQL语句。3. 了解可更新视图和不可更新视图。三、实习内容：1. 验证习题三第16题中各项操作的SQL语句； 建立01312班选修了1号课程的学生视图Stu_01312_1；create view Stu_01312_1AS select *From student,Gradewhere clno='01312' and sno='1' 建立01312班选修了1号课程并且成绩不及格的学生视图Stu_01312_2；create view Stu_00312_2 as select * from Student where Sno in (select Sno from Grade where Grade.Cno='1'and Grade.Gmark<='60') and Clno='00312' 建立视图Stu_year，由学生学号、姓名、出生年份组成。create view Stu_year as select sno as '学号',sname as '姓名','出生年份'=year(getdate()-sage from Student 查询1990年以后出生的学生姓名Create view v_day As select Sname from Student where Sage<=year(getdate()-1990 查询01312班选修了1号课程并且成绩不及格的学生的学号、姓名、出生年份。Create view v_avg As select Student.Sno,Sname, year(getdate()-sage as '出生年份' from Student inner join Grade on student.sno=grade.sno where Grade.Cno='1' and Student.Clno='00312' and gmark<602. 建立一视图Class_grade，用来反映每个班的所有选修课的平均成绩。并对其进行更新操作。create view Class_gradeasselect Student.clno as clno,AVG(Grade.gmark) as Gmark_avgfrom Student full join Grade on student.sno=grade.sno Group by Student.clno四、上机实验收获（感想）：学号 姓名 班级 上机时间 上机实验八*完整性约束的实现一、实习目的：掌握SQL中实现数据完整性的方法，加深理解关系数据模型的三类完整性约束。二、实习准备：1. 复习第4章“完整性约束SQL定义”；2. 完成习题四第10题中四个表结构的SQL定义。3. 了解SQL Server中实体完整性、参照完整性和用户自定义完整性的实现手段。三、实习内容：验证习题四第10题四个表结构的SQL定义。create table course ( cno char(1) primary key, cname varchar(20) not null, credit smallint check（credit>=1 and credit<=6） ) clno char(5) primary key, speciality varchar(20) not null, inyear char(4) not null, number integer check(number>1 and number<100), monitor char(7) ) create table student3 ( sno char(7) primary key, sname varchar(20) not null, ssex char(2) not null default('男'), sage smallint check(sage>14 and sage<65), clno char(5) not null references class(clno) on delete cascade on update cascade ) alter table classadd constraint fk_monitor foreign key (monitor) references student(sno) on delete no action sno char(7) not null references student(sno) on delete cascade on update cascade, cno char(1) not null references course(cno) on delete cascade on update cascade, gmark decimal(4,1) check(gmark>0 and gmark <100), primary key (sno,cno) ) 四、上机实验收获（感想）：五、思考题：SQL Server中提供了哪些方法实现实体完整性、参照完整性和用户自定义完整性。：not null， unique 和 primary key：foreign key 的级联操作策略（、置空）用户定义：专心-专注-专业
