数据库表的查询操作实践演练（实验三）
程序员文章站
2022-07-11 23:51:59
继前两次的实验，本次实验以熟练掌握利用select语句进行各种查询操作：单表查询、多表连接及查询、嵌套查询、集合查询等，巩固数据库查询操作。
下面就跟着小编一起练习吧！...
继前两次的实验，本次实验以熟练掌握利用select语句进行各种查询操作：单表查询、多表连接及查询、嵌套查询、集合查询等，巩固数据库查询操作。
下面就跟着小编一起练习吧！
在实验一创建并插入数据的表（student， course，sc，teacher，tc）的基础上，完成以下操作。
（1）将教师‘罗莉'的名字改为‘罗莉莉'。
复制代码 代码如下:
update teacher set tname='罗莉莉' where tname='罗莉'
（2）将两个同学（数据自己临时设置，用后即删除）的两门课程的成绩以运行sql程序文件的形式插入score表中。该题用以验证、理解和掌握关系模型的完整性规则；
插入：
复制代码 代码如下:
insert into score(sno,cno,grade) values ('04261006','c003','64')
insert into score(sno,cno,grade) values('04261007','c004','79')
insert into score(sno,cno,grade) values('04261007','c004','79')
查询：
复制代码 代码如下:
select sno 学号,cno 课程号,grade 分数from score where sno=04261006 or sno=04261007;
删除：
复制代码 代码如下:
delete from score where sno=04261006 or sno=04261007;
（3）求每门课的平均成绩，并把结果存入average表（自行设计并创建）；
复制代码 代码如下:
create table average
(
cno char(8),
avscore numeric(5,2),
constraint a1 primary key (cno),
constraint a2 foreign key (cno) references course(cno),
)
insert into average(cno,avscore)
select distinct cno ,avg(grade) from score group by cno
(
cno char(8),
avscore numeric(5,2),
constraint a1 primary key (cno),
constraint a2 foreign key (cno) references course(cno),
)
insert into average(cno,avscore)
select distinct cno ,avg(grade) from score group by cno
（4）将学生“马丽”的年龄改为24；
复制代码 代码如下:
update student set 2014-year(sbirth) 年龄 where sname=' 马丽'
（5）将所有学生的szipcode属性列值填补上；
复制代码 代码如下:
update student set szipcode='221000'
（6）将average表中的所有课程的平均成绩置零；
复制代码 代码如下:
update average set avscore='0'
（7）删除average表中的课程号为‘c007'的平均成绩记录；
复制代码 代码如下:
delete from average where cno='c007'
（8）删除所有average表中平均成绩记录；
复制代码 代码如下:
delete from average;
（9）建立一个临时学生信息表（tstudent），删除该表中的学号含‘101'的所有学生记录。
复制代码 代码如下:
create table tstudent ( sno char(8) primary key, sname varchar(8) unique );
delete from tstudent where sno like '001011%';
delete from tstudent where sno like '001011%';
（10）查询全体学生的学号与姓名；
复制代码 代码如下:
select sno 学号,sname 姓名from student
（11）查询全体学生的学号、姓名、所属系；
复制代码 代码如下:
select sno 学号,sname 姓名,sdept 系from student
（12）查询全体学生的详细记录；
复制代码 代码如下:
select * from student
（13）查询全体学生的姓名及其年龄；
复制代码 代码如下:
select sname 姓名,2014-year(sbirth) 年龄from student
（14）查询全体学生的姓名、出生年份；
复制代码 代码如下:
select sname 姓名,year(sbirth) 出生年份from student
（15）查询所有修过课的学生的学号；
复制代码 代码如下:
select distinct sno from score
select distinct student.sno from student,score where student.sno=score.sno and score.grade>0 ;
select distinct student.sno from student,score where student.sno=score.sno and score.grade>0 ;
（16）查询“计算机系”班全体学生名单；
复制代码 代码如下:
select sno,sname from student where sdept='计算机系'
（17）查询查询所有年龄在23岁以下的学生姓名及其年龄；
复制代码 代码如下:
select sname 姓名,2014-year(sbirth) 年龄from student where 2014-year(sbirth)<23;
（18）查询考试成绩有不及格的学生的学号；
复制代码 代码如下:
select distinct sno from score where grade<60;
（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='计算机系' or sclass='电商系'
（22）查询既不是“计11”也不是“计61”班的学生的姓名和班级信息；
复制代码 代码如下:
select sname,sclass from student where sclass not in('计','计');
（23）查询学号为“04262002”的学生的详细情况；
[code]select student.sno,sname,ssex,2014-year(sbirth),sclass,grade from student,score where student.sno=score.sno and student.sno='04262002';
（23）查询学号为“04262002”的学生的详细情况；
[code]select student.sno,sname,ssex,2014-year(sbirth),sclass,grade from student,score where student.sno=score.sno and student.sno='04262002';
（24）查询学号以“04262”打头的学生信息；
复制代码 代码如下:
select * from student where sno like '04262%'
（25）查询所有姓“张”学生的学号、姓名、性别、年龄；
复制代码 代码如下:
select sno 学号,sname 姓名,ssex 性别,2011-year(sbirth) 年龄from student where sname like'王%'
（26）查询名字中第二个字有“海”字的学生的学号、姓名、性别、年龄；
复制代码 代码如下:
select sno 学号,sname 姓名,ssex 性别,2011-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 student.sno,sname,cno from student,score where student.sno=score.sno and grade is null;
（30）查找全部有成绩记录的学生学号、课程号；
复制代码 代码如下:
select sno, cno from score where grade is not null;
（31）查找“计算机系”年龄在22岁以下的学生学号、姓名；
复制代码 代码如下:
select sno ,sname from student where sdept='计算机系' and 2014-year(sbirth)<22
（32）查找选修了“c001”号课程的学生学号及其成绩，查询结果按分数降序排序；
复制代码 代码如下:
select student.sno,grade from student,score where student.sno=score.sno and cno='c001' order by grade desc;
（33）查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列；
复制代码 代码如下:
select * from student order by sdept asc,2014-year(sbirth) desc;
（34）查询学生总人数；
复制代码 代码如下:
select count(*) 人数from student;
（35）查询选修了课程的学生人数；
复制代码 代码如下:
select count(distinct sno)人数from score;
（36）在所有课程中查询最高分的学生学号和成绩；
复制代码 代码如下:
select sno,grade from score where grade =(select max(grade)from score )
复制代码 代码如下:
select distinct a.* from score a where a.sno in (select top 1 score.sno from score where score.cno = a.cno order by grade desc)
（37）查询学习“c001”课程的学生最高分数；
复制代码 代码如下:
select max(grade)最高分数from score where cno='c001'
（38）计算各个课程号与相应的选课人数；
复制代码 代码如下:
select count(sno) 选课人数from score group by cno;
（39）查询“计算机系”选修了两门课程以上的学生学号、姓名；
复制代码 代码如下:
select student.sno,sname from student where student.sno in
(select student.sno from student,score where
sdept='计算机系'and student.sno=score.sno group by student.sno having count(cno)>=2);
(select student.sno from student,score where
sdept='计算机系'and student.sno=score.sno group by student.sno having count(cno)>=2);
（40）自然连接student和score表；
复制代码 代码如下:
select student.*,score.grade from student ,score where student.sno=score.sno;
（41）使用自身连接查询每一门课程的间接先行课（即先行课的先行课）
复制代码 代码如下:
select a.cno,b.cpno from course a,course b where a.cpno=b.cno;
（42）使用复合条件连接查询选修“c001”号课程且成绩在90分以上的所有同学；
复制代码 代码如下:
select sname,grade from student,score where student.sno=score.sno and cno='c001' and grade>=90;
（43）使用复合条件连接查询每个学生选修的课程名及其成绩；
复制代码 代码如下:
select student.sno,sname,cname,grade from course,score,student where course.cno=score.cno and student.sno=score.sno;
（44）查询选修了全部课程的学生；
复制代码 代码如下:
select sname from student where not exists (select * from course where not exists(select * from score where sno=student.sno and cno=course.cno))
（45）查询所有选修了c001号课程的学生学号、姓名；
复制代码 代码如下:
select student.sno,sname from student,score where student.sno=score.sno and cno='c001';
（46）查询选修了课程c001或c007的学生学号、姓名；
[code]select student.sno,sname,cno from student,score where student.sno=score.sno and cno in ('c001','c007');
[/code]（46）查询选修了课程c001或c007的学生学号、姓名；
[code]select student.sno,sname,cno from student,score where student.sno=score.sno and cno in ('c001','c007');
（47）查询“计算机系”的学生及年龄不大于23岁的学生；
复制代码 代码如下:
select sno ,sname,2014-year(sbirth) age ,sclass from student where sdept='计算机系' or 2014-year(sbirth)<=23;
（48）查询既选修了课程c001又选修了课程c007的所有学生学号、姓名；
复制代码 代码如下:
select student.sno,sname from student,score where student.sno=score.sno and cno='c001' and student.sno in (select student.sno from student,score where student.sno=score.sno and cno='c007')
（49）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄；
复制代码 代码如下:
select student.sno ,sname,ssex,cname,2011-year(sbirth) age from student,score,course where student.sno=score.sno and score.cno=course.cno and cname='数据库原理';
（50）查询其他班中比“计算机系”所有学生年龄都小的学生名单；
复制代码 代码如下:
select sno,sname ,2014-year(sbirth) age from student where 2014-year(sbirth)<(select min(2014-year(sbirth)) from student where sclass='计61')and sclass !='计61';
（51）查询与“夏天”在同一个系学习的学生学号、姓名、性别、年龄；
复制代码 代码如下:
select sno,sname,ssex,2014-year(sbirth) age from student where sdept=(select sdept from student where sname='夏天') and sname!='夏天'
（52）建立“计算机系”学生的视图1；
复制代码 代码如下:
create view view_student
as select sno,sname,ssex,sbirth,sclass from student where sclass='13z网络'
as select sno,sname,ssex,sbirth,sclass from student where sclass='13z网络'
（53）建立“计算机系”学生的视图2，并要求进行修改与插入时，仍须保证该视图只有“计算机系”班学生；
复制代码 代码如下:
create view view_student2
as select sno,sname,ssex,sbirth,sclass from student where sclass='13z网络' with check option;
as select sno,sname,ssex,sbirth,sclass from student where sclass='13z网络' with check option;
（54）建立“计算机系”选修了“c001”课程的学生的视图，定义视图名为“v_cs_c001_student1”；
复制代码 代码如下:
create view v_cs_c001_student1
as select student.sno,sname,ssex,sbirth,sclass from student ,score where
student.sno=score.sno and sclass='13z网络' and cno='c001';
as select student.sno,sname,ssex,sbirth,sclass from student ,score where
student.sno=score.sno and sclass='13z网络' and cno='c001';
（55）建立“计算机系”班选修了“c001”课程且成绩在90分以上的学生的视图，定义视图名为“cs_c001_student2”；
复制代码 代码如下:
create view cs_c001_student2
as
select student.sno,sname ,ssex,sbirth,sclass,cno from student,score where
student.sno=score.sno and cno='c001' and sclass='13z网络'and student.sno in (select student.sno from student,score where student.sno=score.sno and grade>90)
as
select student.sno,sname ,ssex,sbirth,sclass,cno from student,score where
student.sno=score.sno and cno='c001' and sclass='13z网络'and student.sno in (select student.sno from student,score where student.sno=score.sno and grade>90)
（56）定义一个反映学生年龄的视图，定义视图名为“v_birth_student”；
复制代码 代码如下:
create view v_birth_student
as
select sno,sname,2014-year(sbirth) age from student
as
select sno,sname,2014-year(sbirth) age from student
（57）将学生表中所有女生记录定义为一个视图，视图名为“v_female_student”；
复制代码 代码如下:
create view v_female_student
as
select * from student where ssex='女';
as
select * from student where ssex='女';
（58）将学生的学号及其平均成绩定义为一个视图，视图名为“v_average_student”；
复制代码 代码如下:
create view v_average_student
as
select sno,avg(grade) avscore from score group by sno;
as
select sno,avg(grade) avscore from score group by sno;
（59）在“计算机系”学生视图中找出年龄小于22岁的学生；
复制代码 代码如下:
select * from view_student where 2014-year(sbirth)<=22;
（60）利用视图查询“计算机系”选修了“c001”课程的学生；
复制代码 代码如下:
select * from v_cs_c001_student1;
（61）通过（52）中的“计算机系”视图修改某个学生的名字；
复制代码 代码如下:
update view_student set sname='王某某'where sno=04261001;
（62）通过（53）中的“计算机系”视图，插入一个新学生记录。
复制代码 代码如下:
insert into view_student2(sno,sname,ssex,sbirth,sclass) values ('04262004','张某某','男','1987/11/09','计');
（63）通过（53）中的“计算机系”视图，删除一个学生记录。
复制代码 代码如下:
delete from view_student2 where sno='04262004'and sname='张某某';
实验课结束了，相信通过本节课的实践操作，小伙伴们都对数据库表的操作有了更进一步的了解。
以上就是查询数据库表的基本操作，几乎涵盖了各种查询操作所遇到的情况，值得大家亲自操作一下，相信对大家的学习有所帮助。
下一篇： 数据库设计三大范式简析