讲义8：分组查询

“`
–上课内容: 第四章 用语句查询表中的数据(分组查询)

– 4.2.1 准备工作：创建表并插入数据

– 4.2.1.1 创建表
create table stu_info
(
t_number char(8),
t_name varchar(10),
t_gender char(2),
t_birthday datetime
)

create table course
(
c_number char(6),
c_name varchar(20),
c_credit int,
c_hour int,
c_teacher varchar(10)
)

create table exam
(
t_number char(8),
c_number char(6),
t_grade decimal(5,2)
)

– 4.2.1.2.为表设置约束

–将要设置为主键的t_number字段设为非空
alter table stu_info alter column t_number char(8) not null
–把t_number字段设为主键
alter table stu_info add constraint stu_pr primary key clustered(t_number) –clustered是什么意思

–将要设置为主键的c_number字段设为非空
alter table course alter column c_number char(6) not null
–把c_number字段设为主键
alter table course add constraint course_pr primary key clustered(c_number)

– 4.2.1.3.插入数据

–向stu_info表插入数据
insert into stu_info values(‘20040301’,’张华’,’女’,’19840113’)
insert into stu_info values(‘20040302’,’王立’,’男’,’19830624’)
insert into stu_info values(‘20040303’,’蒋超’,’男’,’19841115’)
insert into stu_info values(‘20040304’,’王浩雨’,’男’,’19851020’)
insert into stu_info values(‘20040305’,’张静’,’女’,’19840418’)
insert into stu_info values(‘20050301’,’李华’,’女’,’19830113’)
insert into stu_info values(‘20050302’,’张立’,’男’,’19840624’)
insert into stu_info values(‘20050303’,’黄超’,’男’,’19851125’)
insert into stu_info values(‘20050304’,’汪雨’,’男’,’19861020’)
insert into stu_info values(‘20050305’,’王静’,’女’,’19850418’)

–向course表插入数据
insert into course values(‘100101’,’高等数学’,2,60,’赵金’)
insert into course values(‘100102’,’大学英语’,3,80,’王维’)
insert into course values(‘100103’,’大学物理’,2,60,’李华’)
insert into course values(‘100104’,’大学英语’,4,80,’刘杰’)
insert into course values(‘100105’,’大学英语’,NULL,80,’刘杰’)

–向exam表插入数据
insert into exam values(‘20040301’,’100101’,79)
insert into exam values(‘20040301’,’100102’,88)
insert into exam values(‘20040302’,’100101’,90)
insert into exam values(‘20040302’,’100103’,75)
insert into exam values(‘20040303’,’100101’,79)
insert into exam values(‘20040303’,’100102’,75)
insert into exam values(‘20040303’,’100103’,95)
insert into exam values(‘20040304’,’100102’,43)
insert into exam values(‘20040304’,’100103’,68)
insert into exam values(‘20040305’,’100101’,64)
insert into exam values(‘20040305’,’100102’,87)
insert into exam values(‘20040305’,’100103’,92)

– 4.2.1.4.查看输入是否有误，有错误就修改掉
select * from stu_info
select * from course
select * from exam

– 4.2.2 查询操作

– 别名（3种方式）:
– 例：在pubs数据库titles表中，显示价格上涨1%的价格
select price,price*1.01 as price_new from titles
select price,price*1.01 price_new from titles
select price,price_new=price*1.01 from titles

– 别名的形式
– 表达式 AS 别名
– 表达式 别名
– 别名=表达式

– 别名的规范写法：
select price,price*1.01 as 新价格 from titles
–显然 新价格 这是一个字符串
select price,price*1.01 as ‘新价格’ from titles
select price,price*1.01 as 新 价 格 from titles
–同上
select price,price*1.01 as ‘新 价 格’ from titles

– 4.2.2.1 聚合函数
– 描 述

AVG(expr) –列值的平均值。该列只能包含数字数据。

SUM(expr) –列值的合计。该列只能包含数字数据。

COUNT(expr), COUNT() –列值的计数（如果将列名指定为 expr）或是表或组中所有行的计数（如果指定 ）。
–COUNT(expr) 忽略空值，但 COUNT(*) 在计数中包含空值。

MAX(expr) –列中最大的值（文本数据类型中按字母顺序排在最后的值）。忽略空值。

MIN(expr) –列中最小的值（文本数据类型中按字母顺序排在最前的值）。忽略空值。

– 4.2.2.1.1 AVG()函数

– 使用AVG 函数进行计算
– 例：查询在pubs数据库中titles表，business类书的平均预付款
SELECT AVG(advance) as ‘平均预付款’ FROM titles WHERE type = ‘business’

– 与 GROUP BY 子句一起使用AVG 函数进行计算
– 例：查询在pubs数据库中titles表，每一类书的平均预付款
SELECT type, AVG(advance) as ‘平均预付款’ FROM titles GROUP BY type ORDER BY AVG(advance)

– 4.2.2.1.2 SUM()函数

– 使用 SUM 函数进行计算
– 例：查询在pubs数据库中titles表，business类书的销售总额
SELECT SUM(ytd_sales) as ‘销售总额’ FROM titles WHERE type = ‘business’

– 与 GROUP BY 子句一起使用 SUM 函数进行计算
– 例：查询在pubs数据库中titles表，每一类书的销售总额
SELECT type, SUM(ytd_sales) as ‘销售总额’ FROM titles GROUP BY type ORDER BY SUM(ytd_sales)

– 4.2.2.1.3 COUNT()函数

– 使用 COUNT(*) 函数进行计算

-- 例：查询在pubs数据库中titles表，类型为'psychology'的书的数量 SELECT COUNT(*) FROM titles where type='psychology'

– 使用 COUNT(字段名) 函数进行计算
– 例：查询在pubs数据库中titles表，按 *、type、 notes字段分别计数书的数量
SELECT COUNT(*) FROM titles
SELECT COUNT(type) FROM titles
SELECT COUNT(notes) FROM titles

– 4.2.2.1.4 MAX()函数
– 例：查询在pubs数据库中titles表，销售额最大的书
SELECT MAX(ytd_sales) as ‘销售额最大’ FROM titles

-- 例：查询在pubs数据库中titles表，每一类书销售额最大的书 SELECT type,MAX(ytd_sales)  as '销售额最大' FROM titles group by [type] ORDER BY MAX(ytd_sales) 

– 4.2.2.1.5 MIN()函数
– 例：查询在pubs数据库中titles表，销售额最小的书
SELECT MIN(ytd_sales) as ‘销售额最小’ FROM titles

-- 例：查询在pubs数据库中titles表，每一类书销售额最小的书 SELECT type,MIN(ytd_sales) as '销售额最小' FROM titles group by type ORDER BY MIN(ytd_sales) 

–4.2.2.2 GROUP BY 子句指定用来放置输出行的组

–注意：
– 选择列表中任一非聚合表达式内的所有列都应该包含在 GROUP BY 列表中
– 例：查询exam表中每种c_number 平均分的记录
select c_number,avg(t_grade) as ‘平均分’ from exam group by c_number
select c_number,t_number,avg(t_grade) as ‘平均分’ from exam group by c_number,t_number
select c_number,avg(t_grade) as ‘平均分’ from exam group by c_number

-- 使用 GROUP BY 的select 语句仍可使用 HAVING 子句对统计后的查询结果进行筛选 -- 例：查询exam表中每种c_number 平均分小于80分的记录select c_number,avg(t_grade) as '平均分' from exam  group by c_number having avg(t_grade)<80-- 使用 GROUP BY 的select 语句仍可使用where 子句指定条件，但这是条件是分组前对原来记录进行筛选，就是说满足条件的记录才能参加分组 -- 例：查询exam表中每种c_number 为100101 的平均分select c_number,avg(t_grade) as '平均分' from exam WHERE C_NUMBER='100101' group by c_number -- 使用 GROUP BY 的select 语句仍可使用 ORDER BY 子句对分组后的记录进行排序 -- 例：查询exam表中每种c_number 平均分的记录,按课程号降序排序  select c_number,avg(t_grade) as '平均分' from exam group by c_number order by avg(t_grade) desc-- 使用CUBE分组查询exam表中每种c_number 平均分  select c_number,avg(t_grade) as '平均分' from exam group by c_number with cube-- 使用ROLLUP分组查询exam表中每种c_number 平均分  select c_number,avg(t_grade) as '平均分' from exam group by c_number with rollup

–4.2.2.4 COMPUTE 子句用于生成统计结果，且放在新的结果集中
– （例：统计exam 表中所有课程的学时平均分）
select * from course compute avg(c_hour)
– 我们可以观察一下，算平均分的时候，把NULL算为0

–with cube 和 with rollup 的区别

create table title1(tid int,tname varchar(20),pub_id int,price1 float,price2 float)
insert into title1 values(1,’aa1’,1,20,30)
insert into title1 values(2,’aa2’,1,120,130)
insert into title1 values(3,’aa3’,2,200,300)
insert into title1 values(4,’aa4’,3,210,320)
insert into title1 values(5,’aa5’,3,240,360)
insert into title1 values(6,’aa6’,1,202,307)
insert into title1 values(7,’aa1’,1,20,30)
insert into title1 values(8,’aa1’,1,120,130)
insert into title1 values(9,’aa3’,2,200,300)
insert into title1 values(10,’aa4’,3,210,320)
insert into title1 values(11,’aa6’,3,240,360)
insert into title1 values(12,’aa6’,1,202,307)

select * from title1

select pub_id,tname,sum(price1) p1,sum(price2) p2 from title1 group by pub_id,tname with cube
select pub_id,tname,sum(price1) p1,sum(price2) p2 from title1 group by pub_id,tname with rollup

– 复习：

– 聚合函数：AVG() SUM() MAX() MIN() COUNT(*)等

– 这些聚合函数都可以单独使用在SELECT 子句中，来汇总所有记录

– 例：在exam表中，我们要统计所有考试的平均分
select avg(t_grade) as 平均分 from exam

– 多数情况下是与GROUP BY 子句一起来做分组汇总

– GROUP BY 子句

– 可以只根据某字段进行分组，取出某个表里某个字段的无重复的值

– 例：在exam表中，我们要统计有多少个学生参加了考试
select * from exam
select t_number from exam group by t_number

– 例：在exam表中，我们要统计有多少门课程考试
select c_number from exam group by c_number

– 也可以根据某字段进行分组计算需要的值

– 例：在exam表中，我们要统计学生考试的平均分
select * from exam
select t_number,avg(t_grade) as 学生平均分 from exam group by t_number

– 例：在exam表中，我们要统计课程考试的平均分
select c_number,avg(t_grade) as 学生平均分 from exam group by c_number

– 还可以加条件、排序等。详见 4.2.2.2
“`fen