Transcript 学习情境4:学生成绩数据库的信息查询

《网络数据库技术》
—— 省级精品课程
http://172.16.38.204/index.asp
学习情境4
学生成绩数据库的信息查询（2-含复杂数据更新)
主讲：余建军
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
目标：





掌握联接查询
掌握子查询
掌握集合运算
掌握生成新表查询
了解嵌入式SQL语句
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务1 查询所有学生的学号、姓名、课程编
号和成绩
select * from s,sc
[相关知识1] 交叉联接(无限制联接) ——笛卡尔积
如何去除多余的数据？
select * from s,sc where s.sno=sc.sno
select sno,sname,cno,score from s,sc where s.sno=sc.sno
错啦？
select s.sno,sname,cno,score from s,sc where s.sno=sc.sno
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识2]内联接
格
式
SQL-92： FROM <表1> INNER JOIN <表2>
ON <条件>
T-SQL: FROM <表1>,<表2>
WHERE <条件>
拓展任务： 查询所有学生的学号、姓名、课程编号、
课程名、成绩
select s.sno,sname,c.cno,cname,score from s,sc,c
where s.sno=sc.sno and sc.cno=c.cno
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识3] 联接查询
交叉联接： 无条件联接
内联接： 有条件联接
联接
外联接： 保留舍弃记录
自联接： 与自身联接
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务2：查询所有同时选修了课程编号为12312050和
12312051的学生的学号
select sno from sc where cno='12312050'
select sno from sc where cno='12312051' and cno='12312050'
select sno from sc where cno='12312051' or cno='12312050'
select * from sc ,sc
select * from sc a,sc b
select * from sc b,sc a where a.sno=b.sno
select * from sc b,sc a where a.sno=b.sno and
a.cno='12312050' and b.cno='12312051'
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识4]自联接
概念：表与其自身联接
设置表别名： FROM <表名> [AS] <别名>
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务3：查询选修网络数据库技术课程的学号、
成绩
使用联接技术
select sno,score from sc,c where sc.cno=c.cno and c.cname='网络数据
库技术'
select sno,score from sc where sc.cno=(select cno
from c where cname='网络数据库技术')
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识5]子查询（1）
子查询：select语句的嵌套
主查询（外查询），子查询（内层查询）
不相关子查询 ： 子查询条件不依赖于主查询
先执行子查询再执行主查询
子查询
单列单值子查询：＝
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[任务4]查询选修网络数据库技术和C语言程序设计课程
的学号、课程编号、成绩
select sno,score from sc where sc.cno
in (select cno from c where cname='网络数据库技术'
or cname='C语言程序设计')
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识5]子查询（2）
单列单值子查询：＝
子查询
单列多值子查询：IN、ANY、SOME、ALL
子查询作派生表：FROM <查询> AS <派生表名>
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务5：查询所有学生的学号、姓名，所选课程的数
量、总成绩、平均成绩
select sno,count(*) ,sum(score), avg(score) from sc group by sno
select sno,count(*) num ,sum(score) sum ,avg(score)
avg from sc group by sno
select s.sno,sname ,num,sum,avg
from s,(select sno,count(*) num ,sum(score)
sum ,avg(score) avg
from sc group by sno) as t
where s.sno=t.sno
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务6：查询所有学生的学号、姓名、总成绩
select sno,sname ,(select sum(score) from sc where
sc.sno=s.sno)
from s
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识5]子查询（3）
执行：首先执行主查询得到第一条记录，再根据主
查询第一条记录的值执行子查询，依此类推 。
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务7：查询没有选修任何课程的学生的学号、姓名
select sno,sname from s where not exists
(select * from sc where sc.sno=s.sno )
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识5]子查询（4）
子查询用作存在性测试：[NOT] EXISTS
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务8：查询教师保存和提交的学生成
绩
Insert into sc_temp
values('3101233101','12312052',98)
select * from sc
union
select * from sc_temp
[相关知识6]并：UNION
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务9：把每个学生选修课程的数量、最高分、
最低分，平均分存放在表 sc_total
select sno,count(*) total ,max(score) max,min(score)
min ,avg(score) avg into sc_total
From sc group by sno
select * from sc_total
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务10：将每门成绩都>80的学生的学号,姓名,班级名
保存在表S80(sno,sname,classname),该表已经存在
create table S80( sno char(10) primary key,
sname char(8),classname char(20))
insert into s80
select distinct s.sno,sname,classname
from s,sc,class
where sc.sno=s.sno and s.classno=class.classno
and not exists(select * from sc where score<=80
and sc.sno=s.sno)
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务11：将选修网络数据库技术课程且成绩不及格的
学生的网络数据库技术课程成绩清0
update sc set score=0 where cno=(select cno from c
where cname='网络数据库技术' and score<60)
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务12：把低于平均成绩的女同学的成绩提高
5%。
update sc set score=score*1.05
where score<(select avg(score) from sc)
and
sno in (select sno from s where ssex='女')
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识7]生成新表
格式：SELECT <表达式列表> INTO <新表名>
本地临时表：#<表名>
临时表
新表
全局临时表：##<表名>
永久表
注意
本地临时表在当前会话中可见，创建该表用户断开连接时系统自动删除
全局临时表对任何用户均可见，当所有用户断开连接时由系统自动删除
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[知识拓展]嵌入式SQL
交互式T-SQL：在SQL Server下使用
T-SQL
嵌入式T-SQL：在宿主语言程序中使用
宿主语句：宿主语言
执行
T-SQL： SQL Server
规则
T-SQL语句加标志以示区别
共享变量：宿主语言中定义，T-SQL加“:”
衢州职业技术学院