数据库实验4-查询.doc

实验四 表的查询操作(4学时)【实验目的】了解SQL语言的使用，进一步理解关系运算，巩固数据库的基础知识。【实验要求】掌握利用Select语句进行各种查询操作：单表查询、多表连接及查询、嵌套查询、集合查询等。【实验内容】在实验三创建并插入数据的表（Student， Course，SC，Teacher，TC）的基础上，完成以下操作。1.对实验步骤中所给示例进行验证。2.参考所给示例，完成下列各种查询操作。（1）将教师罗莉的名字改为罗莉莉。update Teacher set Tname=罗莉莉where Tname=罗莉（2）将两个同学（数据自己临时设置，用后即删除）的两门课程的成绩以运行sql程序文件的形式插入score表中。该题用以验证、理解和掌握关系模型的完整性规则；Insert into SC(Sno,Cno,Grade)values(04563002, C003,80)Insert into SC(Sno,Cno,Grade)values(04563003, C004,87)Delete from SC where Sno=04563002 and Cno= C003and Grade=80Delete from SC where Sno=04563003 and Cno= C004and Grade=87（3）求每门课的平均成绩，并把结果存入average表（自行设计并创建）；Create table average ( Cno varchar(8) primary key, Average float(2); Insert into average( Cno , average ) select Cno ,avg(Grade) from sc group by Cno;（4）将学生“马丽”的年龄改为24；Update Student set 2014-year(Sbirth) 年龄 where Sname= 马丽（5）将所有学生的zipcode属性列值填补上；Update Student set Szipcode=221300（6）将average表中的所有课程的平均成绩置零；Update average set Average=0 （7）删除average表中的课程号为c007的平均成绩记录；Delete from average where Cno=c007（8）删除所有average表中平均成绩记录；Delete from average（9）建立一个临时学生信息表（tstudent），删除该表中的学号含101的所有学生记录。createtabletstudent(Snochar(8)primarykey,Snamevarchar(8)unique);DeletefromtstudentwhereSnolike001011%;（10）查询全体学生的学号与姓名；select Sno,Sname from Student（11）查询全体学生的学号、姓名、所属系；select Sno,Sname,Sdept from Student（12）查询全体学生的详细记录；select * from Student（13）查询全体学生的姓名及其年龄；select 2014-year(Sbirth) 年龄 ,Sname from Student（14）查询全体学生的姓名、出生年份；select Sname,Sbirth from Student（15）查询所有修过课的学生的学号；select Sno from SC（16）查询“计算机系”班全体学生名单；select * from Student where Sdept=计算机系（17）查询查询所有年龄在23岁以下的学生姓名及其年龄；select Sname,select Sname,2014-year(Sbirth) 年龄 from Student where 2014-year(Sbirth)=23（18）查询考试成绩有不及格的学生的学号；select Sno from SC where Grade60（19）查询年龄在20至22岁之间的学生姓名、系和年龄；select Sname,Sdept, 2014-year(Sbirth) 年龄 from Student where 2014-year(Sbirth) between 20 and 22（20）查询年龄不在20至22岁之间的学生姓名、系和年龄；select Sname,Sdept, 2014-year(Sbirth) 年龄 from Student where 2014-year(Sbirth) not between 20 and 22（21）查询“计算机系”和“电商系”的学生的姓名；select Sname from Student where Sdept in(计算机系,电商系)（22）查询既不是“计11”也不是“计61”班的学生的姓名和班级信息；select Sname,Sclass from Student where Sclass not in (计11,计61)（23）查询学号为“04262002”的学生的详细情况；Select * from Student where Sno=04262002（24）查询学号以“04262”打头的学生信息；Select * from Student where Sno like04262%（25）查询所有姓“张”学生的学号、姓名、性别、年龄；Select Sno,Sname,Ssex,2014-year(Sbirth) 年龄 from Student where Sname like张%（26）查询名字中第二个字有“海”字的学生的学号、姓名、性别、年龄；Select Sno,Sname,Ssex,2014-year(Sbirth) 年龄 from Student where Sname like_海%（27）查询所有不姓“刘”学生的姓名；Select Sname from Student where Sname not like 刘%（28）查询课程号以“C”开头的最后两个字母为“05”的课程号和课程名；Select Cno,Cname from Course where Cno like C%05（29）某些学生选修某门课程后没有参加考试，所以有选修课记录，但没有考试成绩，试查找缺少考试成绩的学生和相应的课程号；select Sno,Cno from SC where Grade is null（30）查找全部有成绩记录的学生学号、课程号；select Sno,Cno from SC where Grade is not null（31）查找“计算机系”年龄在22岁以下的学生学号、姓名；select Sname,Sno from Student where Sdept=计算机系 and 2014-year(Sbirth)2)（40）自然连接student和score表；Select Student.Sno,Sname, Ssex, Sdept, Sclass, Sremark, Saddress, Szipcode, Sphone, Semail, Sbirth,Cno,Grade from Student,SC where Student.Sno=SC.Sno（41）使用自身连接查询每一门课程的间接先行课（即先行课的先行课）Select first.Cno,second.Cpno from Course first,Course second where first.Cpno=second.Cno（42）使用复合条件连接查询选修“c001”号课程且成绩在90分以上的所有同学；Select Student.Sno,Sname from Student,SC where Student.Sno=SC.Sno And SC.Cno=C001and SC.Grade90（43）使用复合条件连接查询每个学生选修的课程名及其成绩；Select Student.Sno,Sname,Cname,Grade from Student,Course,SC where Student.Sno=SC.Sno and Course.Cno=SC.Cno（44）查询选修了全部课程的学生；select Sname fromStudentwhere not exists(select * from Course where not exists(select * from SC where Sno=Student.Sno and Cno=Course.Cno)（45）查询所有选修了C001号课程的学生学号、姓名；Select Sno,Sname from Student where Sno in (select Sno from SC where Cno=C001)（46）查询选修了课程C001或c007的学生学号、姓名；Select Sno,Sname from Student where Sno in (select Sno from SC where Cno=C001 or Cno=C007 )（47）查询“计算机系”的学生及年龄不大于23岁的学生；select *from Student where Sdept=计算机系 and 2014-year(Sbirth)23（48）查询既选修了课程C001又选修了课程c007的所有学生学号、姓名；Select Sno,Sname from Student where Sno in (select Sno from SC where Cno=C001 and Cno=C002)（49）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄；select Student.Sno,Ssex,Sname from Student,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Course.Cname=数据库原理（50）查询其他班中比“计算机系”所有学生年龄都小的学生名单；select Sname, 2014-year(Sbirth) 年龄from Student where 2014-year(Sbirth) all(select 2014-year(Sbirth) 年龄 from Student where Sdept=计算机系) and Sdept计算机系（51）查询与“夏天”在同一个系学习的学生学号、姓名、性别、年龄；select Sno,Sname,Ssex, 2014-year(Sbirth) 年龄from Student where Sdept in (select Sdept from Student where Sname=夏天)（52）建立“计算机系”学生的视图1；create view IS_Student as select Sno,Sname,2014-year(Sbirth) 年龄from Student where Sdept =计算机系（53）建立“计算机系”学生的视图2，并要求进行修改与插入时，仍须保证该视图只有“计算机系”班学生；create view IS_Student as select Sno,Sname,2014-year(Sbirth) 年龄 from Student where Sdept=IS with check option（54）建立“计算机系”选修了“C001”课程的学生的视图，定义视图名为“v_cs_C001_student1”；create view v_cs_C001_student1 (Sno,Sname,Grade)as select Student.Sname,Sno,Grade from Student,SC where Sdept=IS and Student.Sno=SC.Sno and SC.Cno=C001（55）建立“计算机系”班选修了“C001”课程且成绩在90分以上的学生的视图，定义视图名为“cs_c001_student2”；create view v_cs_C001_student2 as select Sname,Sno,Grade from v_cs_C001_student1 where Grade=90（56）定义一个反映学生年龄的视图，定义视图名为“v_birth_student”；Create view v_birth_student (Sno,Sname,Sbirth) as select Sno,Sname,2014-year(Sbirth) 年龄 from Student（57）将学生表中所有女生记录定义为一个视图，视图名为“v_female_student”；Create view v_female_student(f_sno,name,sex,age,dept) as select * from Student where Ssex=女（58）将学生的学号及其平均成绩定义为一个视图，视图名为“v_average_student”；Create view v_average_student(Sno,Gavg) as select Sno,AVG(Grade) from SC group by Sno（59）在“计算机系”学生视图中找出年龄小于22岁的学生；select Sno,2014-year(Sbirth) 年龄from IS_Student where Sdept=计算机系 and 2014-year(Sbirth)2800 order by tsalary ASC4条件查询 使用关系运算符：从teacher表中查询出工作量大于288的教师资料，语句如下：select * from teacherWHERE tamount288 order by tamount DESC 使用BETWEENAND谓词：从teacher表中查询出工作量界于144和288之间的教师资料，语句如下：select * from teacher WHERE tamount between 144 and 288 使用IN谓词：从teacher表中查询出职称为“教授”或“副教授”的教师的教工号、教师姓名、职称及家庭住址，语句如下：select tno,tname,tposition, taddress from teacherWHERE tposition in (教授,副教授) 使用LIKE谓词：从teacher表中分别检索出姓王的教师的资料，或者姓名的第2个字是莉或轩的教师的资料，语句如下：select * from teacher WHERE tname like 王%select * from teacher WHERE tname like _莉，轩%二、多表查询数据库各表中存放着不同的数据，用户经常需要用多个表中的数据来组合提炼出所需要的信息，如果一个查询需要对多个表进行操作，就称为关联查询，关联查询的结果集或结果表称为表之间的连接。关联查询实际上是通过各各表之间共同列的关联来查询数据的，它是关系数据库查询最基本的特征。1SQL 2000兼容2种连接形式：用于FROM子句的ANSI连接语法和用于WHERE子句的SQL SERVER连接语法形式。从student、course和SC三张表中检索学生的学号、姓名、学习课程号、学习课程名及课程成绩，语句如下：select student.sno, sname, cno, gradefrom student inner join SC on student.sno=SC.snoselect student.sname,sc.gradefrom student,scWHERE student.sno=sc.sno select student.sno,student.sname,o,ame,sc.gradefrom student,sc,course WHERE student.sno=sc.sno and o=o使用UNION子句进行查询使用UNION子句可以将一个或者多个表的某些数据类型相同的列显示在同一列上。如从teacher表中列出教工号、姓名并从student表中列出学号及学生姓名，语句及查询结果如下：select sno AS 学号或工号, sname AS 姓名 from student union select tno, tname from teacher3用GROUP子句进行查询如果要在数据检索时对表中数据按照一定条件进行分组汇总或求平均值，就要在SELECT语句中与GROUP BY子句一起使用集合函数。使用GROUP BY子句进行数据检索可得到数据分类的汇总统计、平均值或其他统计信息。 使用不带HAVING的GROUP BY子句。使用不带HAVING 的GROUP BY子句汇总出SC表中的学生的学号及总成绩，语句如下：select 学号=sno,总成绩=SUM(Grade) from SCgroup by Sno 使用带HAVING 的GROUP BY子句。使用带HAVING 的GROUP BY子句汇总出SC表中总分大于450分的学生的学号及总成绩，语句如下：select 学号=sno,总成绩=SUM(Grade) from SCgroup by Sno Having SUM(Grade)160 4用COMPUTE和COMPUTE BY子句进行查询使用COMPUTE和COMPUTE BY 既能浏览数据又能看到统计的结果。 用COMPUTE子句汇总出SC表中每个学生的学号及总成绩，语句如下：select 学号=sno,成绩=Grade from S