数据库常用函数汇总统计

实验二（续）：利用SQL语句查询三、常用库函数及统计汇总查询1、求学号为 S1学生的总分和平均分；select sum(score) as TotalScore,avg(score)as AveScorefrom scwhere sno='S1'2、求选修 C1号课程的最高分、最低分及之间相差的分数；select max(score)as MaxScore,min(score)as MinScore, max(score)- min(score)as difffrom scwhere cno='C1'3、求选修 C1号课程的学生人数和最高分；select count(distinct sno),max(score)from scwhere cno='C 1'4、求计算机系学生的总数；select count(sno)from swhere dept=' 计算机 '5、求学校中共有多少个系；select count(distinct dept) as DeptNumfrom s6、统计有成绩同学的人数；select count(score)from sc7、利用特殊函数 COUNT（*）求计算机系学生的总数；select count(*)from swhere dept=' 计算机 '8、利用特殊函数 COUNT（*）求女学生总数和平均年龄；select count(*),avg(age)from swhere sex=' 女 '9、利用特殊函数 COUNT（*）求计算机系女教师的总数。

select count(*)from twhere dept=' 计算机 'and sex=' 女 '四、分组查询及排序1、查询各个教师的教师号及其任课门数；select tno,count(*)as c_numfrom tcgroup by tno2、按系统计女教师的人数；select dept,count(tno)from twhere sex=' 女 'group by dept3、查询选修两门以上课程的学生的学号和选课门数；select sno,count(*)as sc_numfrom scgroup by snohaving count(*)>24、查询平均成绩大于 70分的课程号和平均成绩；select cno,avg(score)from scgroup by cnohaving avg(score)>705、查询选修 C1的学生学号和成绩，并按成绩降序排列；select sno,scorefrom scwhere cno='01'order by score desc6、查询选修 C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列；select sno,cno,scorefrom scwhere cno in('C2','C3','C4','C5')order by sno ,score desc7、求有三门以上选课成绩及格的学生的学号及其总成绩，查询结果按总成绩降序列出。

select sno,sum(score)as TotalScorefrom scwhere score>=60group by snohaving count(*)>=3order 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 s4sname 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 keyreferences s(sno),cno char(8) not null constraint c_fore foreign keyreferences 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))。