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

《网络数据库技术》
—— 省级精品课程
http://172.16.38.204/index.asp
学习情境4
学生成绩数据库的信息查询(1)
主讲：余建军
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
目标：

掌握单表SELECT语句
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务1 查询丁晓婕同学的出生年月
SELECT sname, sbirthday from S
where sname='丁晓婕'
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识1] 基本select语句
数据检索：查找出所需要的数据
语句格式：
select <检索内容>
from <表>
where <检索条件>
group by <分类字段>
having <检索条件>
order by <排序字段>
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务2： 查询10网络1班所有学生信息
select * from s where classno='31012331‘
select * from s
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务3： 计算身份证为
610104198902160542学生的出生年月和
年龄
（2）declare @sbirthday as datetime
/*输出出生年月 select --print*/
（1）select convert(datetime,substring('610104198902160542',7,8))
/*print convert(datetime,substring('610104198902160542',7,8))*/
/* select --set*/
（2）select @sbirthday = convert(datetime,substring('610104198902160542',7,4))
（2）select year(getdate())-year(@sbirthday)+1
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识2]操纵列(1)
1.计算表达式:
<表达式>[,…n]
实例 香港回归多少年、天,15个月是哪天。 注意：与PRINT的区别
2.输出指定列： <字段>[,…n]
实例 查询学号、班级、姓名
3.输出所有列:
*
实例 查询学生的所有信息
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务4：检索学号、姓名、班级和专业
（1）select year(getdate())-year(@sbirthday)+1 as '年龄‘
select year(getdate())-year(@sbirthday)+1 '年龄‘
（2）select sname 姓名,'is',datediff(year,sbirthday,getdate()) as 年龄 from s
任务5：查询学校所有的班级
[选修了12312050课程的班级号]
（1）select classname from class
substring(sno,1,8)from sc where cno='12312050‘
（3）select distinct substring(sno,1,8)from sc where
cno='12312050'
（2）select
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务拓展：
select sno,sname ,case when classno=‘31012331’ then
‘10网络1’ when classno=‘31012332’ then ‘10网络2’
end as 班级
from s
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识2]操纵列（2）
1.设置列标题： <表达式> [AS] <别名>|<别名>=<表达式>
2.消除重复记录：
DISTINCT
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务6：查1990/1/1-1991/12/出生学生信息
select sname,sbirthday from s where ssex='女' and
year(sbirthday) between 1990 and 1991
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识3]操纵行（1）
1.普通查询:
where <逻辑表达式>
注意：AND与OR的区别
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务7：查姓李的学生，以及第二个字为
李的学生
select * from s where sname like ‘李%’
select * from s where sname like '_李%'
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识3]操纵行（2）
运算符: like
1.模糊查询
匹配符
%(任意个字符)
_ (任意一个字符)
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务8：查姓所有学生/每个学生选修课程的数量、总
分、最高分、最低分
select
sno,count(*),max(score),min(score),avg(score),sum(score)
From sc ------取消sno才ok
select
sno,count(*),max(score),min(score),avg(score),sum(score)
From sc group by sno
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识4.1]分类汇总
概念：工资表
单位
计算机
计算机
计算机
电气
电气
机械
机械
机械
姓名 工资
张三 1200
李四 1100
王五 1300
赵六 1200
杨七 1100
马八 1400
孙九 1300
袁十 1200
分类 ：分类字段
求和 ：汇总
3600
2300 实例 学生总成绩
3900 实例 课程平均成绩
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识4.2]聚合函数
聚合函数：
COUNT(DISTINCT <列>|*)（列惟一值个数或记录总数）
MAX([DISTINCT] <列>)（列最大值或列惟一值最大值）
MIN([DISTINCT] <列>)（列最小值或列惟一值最小值）
SUM([DISTINCT] <列>)（列算术和或列惟一值算术和）
AVG([DISTINCT] <列>)（列平均值或列惟一值平均值）
实例 查询所有学生选修课程数量、总分及最高、最低分。
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识4.3]分类
语句格式 ：
GROUP BY <列1>[,…n]
注意
SELECT列只能是分类字段或聚合函数
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务9：查姓平均分及格的学生选修课程的数量、总
分、最高分、最低分
select
sno,count(*),max(score),min(score),avg(score),sum(score)
From sc group by sno having avg(score)>=60
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识4.4]分类后过滤
语句格式 ：
HAVING <逻辑表达式>
where: group前过滤,不汇总。如:成绩不为空
having: group后,汇总后过滤。如:平均成绩及格
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
任务10：把课程12312050的选修成绩按高到低输出
select * from sc where cno=‘12312050’ order by score desc
任务11：查姓每个学生选修课程的数量、总分、最高
分、最低分，并按平均分、综分排序
select sno,count(*) 选修门数 ,max(score) 最高分,min(score
最低分 ,avg(score) 平均分,sum(score) 总分
From sc group by sno order by avg(score) desc,max(score)
desc
衢州职业技术学院
http://172.16.38.204/index.asp
数据库技术
[相关知识5]排序
语句格式 ：
ORDER BY <列1>[DESC][,…n]
衢州职业技术学院