数据库常用函数汇总统计.docx
《数据库常用函数汇总统计.docx》由会员分享，可在线阅读，更多相关《数据库常用函数汇总统计.docx（7页珍藏版）》请在冰豆网上搜索。
实验二（续）：
利用SQL语句查询
三、常用库函数及统计汇总查询
1、求学号为 S1学生的总分和平均分；
selectsum（score）asTotalScore,avg（score）asAveScore
fromsc
wheresno='S1'
2、 求选修 C1号课程的最高分、最低分及之间相差的分数；
selectmax（score）asMaxScore,
min（score）asMinScore,max（score）-min（score）asdiff
fromsc
wherecno='C1'
3、 求选修 C1号课程的学生人数和最高分；
selectcount（distinctsno）,max（score）
fromsc
wherecno='C1'
4、 求计算机系学生的总数；
selectcount（sno）
froms
wheredept=' 计算机 '
5、求学校中共有多少个系；
selectcount（distinctdept）asDeptNum
froms
6、统计有成绩同学的人数；
selectcount（score）
fromsc
7、利用特殊函数 COUNT（*）求计算机系学生的总数；
selectcount（*）
froms
wheredept=' 计算机 '
8、利用特殊函数 COUNT（*）求女学生总数和平均年龄；
selectcount（*）,avg（age）
froms
wheresex=' 女 '
9、利用特殊函数 COUNT（*）求计算机系女教师的总数。
selectcount（*）
fromt
wheredept=' 计算机 'andsex=' 女 '
四、分组查询及排序
1、 查询各个教师的教师号及其任课门数；
selecttno,count（*）asc_num
fromtc
groupbytno
2、按系统计女教师的人数；
selectdept,count（tno）
fromt
wheresex=' 女 '
groupbydept
3、 查询选修两门以上课程的学生的学号和选课门数；
selectsno,count（*）assc_num
fromsc
groupbysno
havingcount（*）>2
4、查询平均成绩大于 70分的课程号和平均成绩；
selectcno,avg（score）
fromsc
groupbycno
havingavg（score）>70
5、查询选修 C1的学生学号和成绩，并按成绩降序排列；
selectsno,score
fromsc
wherecno='01'
orderbyscoredesc
6、查询选修 C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列；
selectsno,cno,score
fromsc
wherecnoin（'C2','C3','C4','C5'）
orderbysno,scoredesc
7、求有三门以上选课成绩及格的学生的学号及其总成绩，查询结果按总成绩降序列出。
selectsno,sum（score）asTotalScore
fromsc
wherescore>=60
groupbysno
havingcount（*）>=3
orderbysum（score）desc
[注意]：
1、 函数 SUM和AVG只能对数值型字段进行计算；
2、COUNT函数对空值不计算，但对零值计算；
3、COUNT（*）用来统计元组的个数，不消除重复行，不允许使用DISTINCT关键字；
4、WHERE子句作用于基本表或视图，从中选择满足条件的元组，HAVING子句作用于组，选择满足条件的组，必须用于GROUPBY子句之后.
在 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 字段进行 notnull 约束；
createtables1
（snochar（10）constraintsno_consnotnull,
snamevarchar（10）,
sexchar
（2）,
ageint,
deptvarchar（30））
2、 创建一个学生表 s2 ，定义 sname 为唯一键；
createtables2
（snochar（10）,
snamechar（10）constraintsname_uniqunique,
sexchar
（2）,
ageint,
deptvarchar（30））
3、 创建一个学生表 s3 ，定义 sname+sex 为唯一键；
createtables3
（snochar（10）,
snamechar（10）,
sexchar
（2）,
ageint,
deptvarchar（30）,
constraints3_uniqunique（sname,sex））
4、 创建一个学生表 s4 ，定义 sno 为主键；
createtables4
（snochar（10）constraintsno_primprimarykey,
snamechar（10）,
sexchar
（2）,
ageint,
deptvarchar（30））
5、 创建一个学生表 s5 ，定义 age 的取值范围为 15~50 ；
createtables5
（snochar（10）,
snamechar（10）,
sexchar
（2）,
ageintconstraintage_chkcheck（agebetween15and50）,
deptvarchar（30））
6、 创建一个学生表 s6 ，使 sex 列的缺省值为“男”；
createtables6
（snochar（10）,
snamechar（10）,
sexchar
（2）default‘ 男 ',
ageint,
deptvarchar（30））
7、创建一个学生表 s7 ，定义 sno 为主键， sname 为唯一键， age 的取值范围为 15~50 ， sex 列的缺省值为“男”， dept 字段进行 notnull 约束；
createtables7
（snochar（10）constraintsno_primprimarykey,
snamechar（10）constraintsname_uniqunique,
sexchar
（2）default‘ 男 ',
ageintconstraintage_chkcheck（agebetween15and50）,
deptvarchar（30）constraintdept_consnotnull）
8、创建一个学生表 s ，定义 sno 为主键，对 sname 字段进行 notnull 约束。
createtables
（snochar（10）constraintsno_primprimarykey,
snamechar（10）constraintsname_consnotnull,
sexchar
（2）,
ageint,
deptvarchar（30））
二、创建教师表
创建一个教师表 t ，定义 tno 为主键，对 tname 字段进行 notnull 约束。
Createtablet
（tnochar（8）constrainttno_primprimarykey,
tnamechar（10）notnull,
sexchar
（2）,
ageint,
profchar（10）,
salnumeric（6）,
commnumeric（6）,
deptvarchar（30））
三、创建课程表
创建一个课程表 c ，定义 cno 为主键，对 cname 字段进行 notnull 约束。
Createtablec
（cnochar（8）constraintcno_primprimarykey,
cnamechar（20）notnull,
ctint）
四、创建选课表
创建一个选课表 sc ，定义 sno+cno 为主键，定义 sno ， cno 为外部键。
Createtablesc
（snochar（10）notnullconstraints_foreforeignkey
referencess（sno）,
cnochar（8）notnullconstraintc_foreforeignkey
referencesc（cno）,
scorenumeric（3）,
constraintsc_primprimarykey（sno,cno））
五、创建授课表
创建一个授课表 tc ，定义 tno+cno 为主键。
Createtabletc
（tnochar（8）,
cnochar（8）,
constrainttc_primprimarykey（tno,cno））
六、创建系别表
创建一个系别表 d ，定义 dept 为主键。
Createtabled
（deptvarchar（30）constraintdept_primprimarykey,
addrvarchar（40））
升级会员 