数据库常用函数汇总统计

实验二（续）：利用SQL语句查询

三、常用库函数及统计汇总查询 1、求学号为 S1学生的总分和平均分；

select sum(score) as TotalScore,avg(score)as AveScore from sc where sno='S1'

2、 求选修 C1号课程的最高分、最低分及之间相差的分数； select max(score)as MaxScore,

min(score)as MinScore, max(score)- min(score)as diff from sc where cno='C1'

3、 求选修 C1号课程的学生人数和最高分； select count(distinct sno),max(score) from sc where cno='C 1'

4、 求计算机系学生的总数； select count(sno) from s

where dept=' 计算机 ' 5、求学校中共有多少个系； select count(distinct dept) as DeptNum from s

6、统计有成绩同学的人数； select count(score) from sc

7、利用特殊函数 COUNT（*）求计算机系学生的总数； select count(*) from s

where dept=' 计算机 '

8、利用特殊函数 COUNT（*）求女学生总数和平均年龄； select count(*),avg(age) from s where sex=' 女 '

9、利用特殊函数 COUNT（*）求计算机系女教师的总数。 select count(*) from t

where dept=' 计算机 'and sex=' 女 ' 四、分组查询及排序

1、 查询各个教师的教师号及其任课门数； select tno,count(*)as c_num from tc group by tno

2、按系统计女教师的人数； select dept,count(tno) from t where sex=' 女 ' group by dept

3、 查询选修两门以上课程的学生的学号和选课门数； select sno,count(*)as sc_num from sc group by sno having count(*)>2

4、查询平均成绩大于 70分的课程号和平均成绩； select cno,avg(score) from sc group by cno having avg(score)>70

5、查询选修 C1的学生学号和成绩，并按成绩降序排列； select sno,score

from sc where cno='01' order by score desc

6、查询选修 C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列； select sno,cno,score from sc

where cno in('C2','C3','C4','C5') order by sno ,score desc

7、求有三门以上选课成绩及格的学生的学号及其总成绩，查询结果按总成绩降序列出。 select sno,sum(score)as TotalScore from sc where score>=60 group by sno having count(*)>=3 order by sum(score) desc [注意]：

1、 函数 SUM和AVG只能对数值型字段进行计算； 2、COUNT函数对空值不计算，但对零值计算；

3、COUNT（*）用来统计元组的个数，不消除重复行，不允许使用DISTINCT关键字； 4、WHERE子句作用于基本表或视图，从中选择满足条件的元组，HAVING子句作用于组，选择满足条件的组，必须用于GROUP BY子句之后.

在 D 盘建立以自己的班级 _ 姓名 _ 学号为名称的文件夹，用来保存数据库以及相关的 SQL 语句。

先创建教学数据库 student 中包含六个基本表： 学生情况基本表 s (sno,sname,sex,age,dept)

教师情况基本表 t (tno,tname,sex,age,prof,sal,comm,dept) 课程基本表 c (cno,cname,ct) 选课基本表 sc (sno,cno,score)

授课基本表 tc (tno,cno) 系别基本表 d (dept,addr) [步骤]：

用 SQL语句完成如下操作： 一、创建学生表

1、 创建一个学生表 s1 ，对 sno 字段进行 not null 约束； create table s1

(sno char(10) constraint sno_cons not null, sname varchar(10), sex char(2), age int,

dept varchar(30))

2、 创建一个学生表 s2 ，定义 sname 为唯一键； create table s2 (sno char(10),

sname char(10) constraint sname_uniq unique, sex char(2), age int,

dept varchar(30))

3、 创建一个学生表 s3 ，定义 sname+sex 为唯一键； create table s3 (sno char(10), sname char(10), sex char(2), age int, dept varchar(30),

constraint s3_uniq unique(sname,sex)) 4、 创建一个学生表 s4 ，定义 sno 为主键； create table s4