实例8(分组查询).sql

经典的sql资料

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

-- 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)


--将要设置为主键的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 查询操作

  -- 别名:
   -- 例：在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 type

  -- 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 type

  -- 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 

  -- 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 


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

   --注意：
    -- 选择列表中任一非聚合表达式内的所有列都应该包含在 GROUP BY 列表中
     -- 例：查询exam表中每种c_number 平均分的记录
    select c_number,t_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 c_number 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







--练习：

-- 1. 查询 test 数据库中 exam表中所有学生考试成绩的平均分大于80分的记录，并且按成绩降序排序

-- 2. 使用CUBE分组查询 test 数据库中 exam表中所有学生考试成绩的平均分记录

-- 3. 查询 test 数据库中 exam表中所有课程考试成绩的平均分大于80分的记录，并且按成绩降序排序

-- 4. 使用ROLLUP分组查询 test 数据库中 exam表中所有课程考试成绩的平均分记录

-- 5. 查询 test 数据库中 用COMPUTE计算exam表中所有课程考试成绩的平均分

-- 6. 查询test数据库中，每门课程的最高分、最低分、平均分

-- 7. 查询test数据库中，每位学生的最高分、最低分、平均分

-- 8. 查询 Pubs 数据库中 Titles 数据表中每种类型的书的数量

-- 9. 查询 Pubs 数据库中 publishers数据表中某国家某州的出版商的数量

-- 10. 查询northwind数据库中orderdetails表中,销售额前3名的产品

-- 11. 查询northwind数据库中orderdetails表中,销售额前3名的订单

-- 12. 查询northwind数据库中,销售额>60000的产品编号

-- 13. 查询northwind数据库中,1997年每月的订单数，按月升序排序

-- 14. 查询northwind数据库中,每年的订单数，按年升序排序

-- 15. 查询northwind数据库中,每年每月的订单数，按年、月升序排序



-- 复习：

-- 聚合函数：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


--练习参考答案：

-- 1. 查询 test 数据库中 exam表中所有学生考试成绩的平均分大于80分的记录，并且按成绩降序排序
select t_number,avg(t_grade) as '平均分' from exam group by t_number having avg(t_grade) >80 order by avg(t_grade) desc

-- 2. 使用CUBE分组查询 test 数据库中 exam表中所有学生考试成绩的平均分记录
select t_number,avg(t_grade) as '平均分' from exam group by t_number with cube

-- 3. 查询 test 数据库中 exam表中所有课程考试成绩的平均分大于80分的记录，并且按成绩降序排序
select c_number,avg(t_grade) as '平均分' from exam group by c_number having avg(t_grade) >80 order by avg(t_grade) desc

-- 4.  使用ROLLUP分组查询 test 数据库中 exam表中所有课程考试成绩的平均分记录
select c_number,avg(t_grade) as '平均分' from exam group by c_number with rollup

-- 5. 查询 test 数据库中 用COMPUTE计算exam表中所有课程考试成绩的平均分
select c_number,t_grade  from exam  compute avg(t_grade)

-- 6. 查询test数据库中，每门课程的最高分、最低分、平均分
select c_number,max(t_grade) as '最高分',min(t_grade) as '最低分',avg(t_grade) as '平均分' from exam group by c_number

-- 7. 查询test数据库中，每位学生的最高分、最低分、平均分
select t_number,max(t_grade) as '最高分',min(t_grade) as '最低分',avg(t_grade) as '平均分' from exam group by t_number

-- 8. 查询 Pubs 数据库中 Titles 数据表中每种类型的书的数量
select type,count(*) as '计数' from titles group by type
select * from titles

-- 9. 查询 Pubs 数据库中 publishers数据表中某国家某州的出版商的数量
select state,country,count(*) from publishers group by country,state

-- 10. 查询northwind数据库中orderdetails表中,销售额前3名的产品
select top 3 productid,sum(unitprice*quantity) as '销售额' from [order details] group by productid order by sum(unitprice*quantity) desc 

-- 11. 查询northwind数据库中,销售额前3名的订单
select top 3 orderid,sum(unitprice*quantity) as '销售额' from [order details] group by orderid order by sum(unitprice*quantity) desc 

-- 12. 查询northwind数据库中,销售额>60000的产品编号
select productid,sum(unitprice*quantity) as '销售额' from [order details]  group by productid having sum(unitprice*quantity)>60000

-- 13. 查询northwind数据库中,1997年每月的订单数，按月升序排序
select  month(orderdate) as '月份',count(*) as '订单数' from orders where year(orderdate)='1997' group by month(orderdate) order by month(orderdate)

-- 14. 查询northwind数据库中,每年的订单数，按年升序排序
select  year(orderdate) as '年',count(*) as '订单数' from orders  group by year(orderdate) order by year(orderdate)

-- 15. 查询northwind数据库中,每年每月的订单数，按年、月升序排序
select  year(orderdate) as '年',month(orderdate) as '月份',count(*) as '订单数' from orders  group by year(orderdate),month(orderdate) order by year(orderdate),month(orderdate)