数据库常用函数汇总统计

实验二（续）：利用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和A VG只能对数值型字段进行计算；

2、COUNT函数对空值不计算，但对零值计算；

3、COUNT（*）用来统计元组的个数，不消除重复行，不允许使用DISTINCT关键字；

4、WHERE子句作用于基本表或视图，从中选择满足条件的元组，HA VING子句作用于组，选择满足条件的组，必须用于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

sname char(10),

sex char(2),

age int,

dept varchar(30))

5、创建一个学生表 s5 ，定义 age 的取值范围为 15~50 ；

create table s5

(sno char(10),

sname char(10),

sex char(2),

age int constraint age_chk check(age between 15 and 50),

dept varchar(30))

6、创建一个学生表 s6 ，使 sex 列的缺省值为“男”；

create table s6

(sno char(10),

sname char(10),

sex char(2) default …男 ',

age int,

dept varchar(30))

7、创建一个学生表 s7 ，定义 sno 为主键， sname 为唯一键， age 的取值范围为 15~50 ， sex 列的缺省值为“男”， dept 字段进行 not null 约束；

create table s7

(sno char(10) constraint sno_prim primary key,

sname char(10) constraint sname_uniq unique,

sex char(2) default …男 ',

age int constraint age_chk check(age between 15 and 50),

dept varchar(30) constraint dept_cons not null)

8、创建一个学生表 s ，定义 sno 为主键，对 sname 字段进行 not null 约束。

create table s

(sno char(10) constraint sno_prim primary key,

sex char(2),

age int,

dept varchar(30))

二、创建教师表

创建一个教师表 t ，定义 tno 为主键，对 tname 字段进行 not null 约束。Create table t

(tno char(8) constraint tno_prim primary key,

tname char(10) not null,

sex char(2),

age int,

prof char(10),

sal numeric(6),

comm numeric(6),

dept varchar(30))

三、创建课程表

创建一个课程表 c ，定义 cno 为主键，对 cname 字段进行 not null 约束。Create table c

(cno char(8) constraint cno_prim primary key,

cname char(20) not null,

ct int)

四、创建选课表

创建一个选课表 sc ，定义 sno+cno 为主键，定义 sno ， cno 为外部键。Create table sc

(sno char(10) not null constraint s_fore foreign key

references s(sno),

cno char(8) not null constraint c_fore foreign key

references c(cno),

score numeric(3),

constraint sc_prim primary key(sno,cno))

五、创建授课表

创建一个授课表 tc ，定义 tno+cno 为主键。Create table tc

(tno char(8),

cno char(8),

constraint tc_prim primary key(tno,cno))

六、创建系别表

创建一个系别表 d ，定义 dept 为主键。

Create table d

(dept varchar(30) constraint dept_prim primary key, addr varchar(40))