/*
使用统计函数查询
*/
/*
一、计数函数：
格式：count(*｜字段名)
作用：用来统计表中记录个数
注意：用"*",则表示统计所有记录数，空值也在统计范围内；用"字段名"表示统计指定字段的记录个数，空值不在统计范围内
*/
--1.进入rj1903数据库，统计customer表中的记录条数,使用别名
use rj1903
go
select COUNT(*) as 人数 from customer
-- 或者：
select COUNT(cname) as 人数 from customer
--2.统计customer表中的有email的个数,使用别名
select * from customer
select COUNT(email) '有email人数' from customer---说明email为null则不在统计范围内
二、求和函数
格式：sum(表达式|字段名)
作用：计算表达式或指定字段值的总和
三、求平均值、最大值、最小值函数
格式：avg(字段名)，max(字段名),min(字段名)
*/
--1.创建一个进货信息表
use Goods
go
create table 进货信息表(
商品编号 char(8) not null,
商品名称 varchar(20) not null,
进货数量 int not null,
进货金额 int not null
)
--2.给进货信息表插入若干条记录,查看记录
insert into 进货信息表 values('11110001','电脑',20,80000)
insert into 进货信息表 values('11110002','手机',50,50000)
insert into 进货信息表 values('11110003','键盘',50,5000)
insert into 进货信息表 values('11110004','鼠标',60,3000)
insert into 进货信息表 values('11110005','U盘',80,2400)
insert into 进货信息表 values('11110006','Pad',10,10000)
select * from 进货信息表
--3.统计进货信息表记录总数、进货金额总和，进货金额最大值，最小值,进货金额平均值
select COUNT(*) '记录总数',SUM(进货金额) '进货金额总和',MAX(进货金额) '进货金额最大值',MIN(进货金额) '进货金额最小值',
AVG(进货金额) '进货金额平均值' from 进货信息表
--5.在student数据库中新建一个cj表（成绩表）(stu_xh,stu_name,yw,sx,yy)（即：学号，姓名，语文，数学，英语）
use Student
go
create table cj(
stu_xh char(4) primary key,
stu_name varchar(20) not null,
yw int,
sx int,
yy int
)
--6.向cj表插入7条记录
insert into cj values('0001','张三',89,96,84)
insert into cj values('0002','李四',87,85,89)
insert into cj values('0003','王五',96,65,78)
insert into cj values('0004','赵六',92,67,91)
insert into cj values('0005','钱小七',66,62,97)
insert into cj values('0006','周八',92,80,77)
insert into cj values('0007','杨九',95,62,81)
insert into cj values('0008','杨九',95,62,81)
select * from cj
--7.统计出cj表中姓名不同的人数
select count(stu_name) as 姓名人数 from cj
select count(distinct stu_name) as 不同姓名人数 from cj
--8.找出cj表中总分最高的学生分数信息: stu_id,stu_name,yw,sx,yy,zf（总分）(嵌套查询)
select stu_id,stu_name,yw,sx,yy,yw+sx+yy '总分' from cj where yw+sx+yy = (select MAX(yw+sx+yy) from cj)
select stu_id,stu_name,yw,sx,yy,yw+sx+yy '总分' from cj where yw+sx+yy = MAX(yw+sx+yy)
--9.查询出cj表中语数外平均分
select AVG(yw) '语文平均分',AVG(sx) '数学平均分',AVG(yy)'英语平均分' from cj
--10.查询出cj表各学科的最高分
select max(yw) '语文最高分',max(sx) '数学最高分',max(yy)'英语最高分' from cj
/*
四、使用排序查询
order by 用法：
格式： order by 字段名1[,字段名2，...] [asc|desc]
作用： 主要是按字段名1来排序，若字段名1的值相同，则这些记录再按字段名2来排序,以此类推,默认为升序，即asc ,可以省略
注意： 如果有where 条件表达式，则order by 在其之后，若没有where 子句，则order by 就在表名之后
*/
--1.查询出"进货信息表"中进货数量最多的前3条记录
use Goods
go
select top 3 * from 进货信息表 order by 进货数量 desc
--2.在Student数据库中对cj表按照yw的降序排列显示
use Student
go
select * from cj order by yw desc
--3.对cj表按照yw的降序排列,若yw有相同记录，则这些记录再按sx降序排列显示，并非所有sx降序排列
select * from cj order by yw desc,sx desc
--4.查询出cj表中学号，姓名，总分，按总分降序排列
select * ,yw+sx+yy 'zf' from cj order by zf desc
--5.查询出cj表中学号，姓名 ，总分，按总分降序排列，只显示总分前5名的记录
select top 5 * ,yw+sx+yy 'zf' from cj order by zf desc
/*
五、分类汇总查询
格式：group by 字段名列表 [having 条件表达式]
作用：按指定字段分组查询，若加上having 条件表达式,则表示对分组后查询的结果再进行筛选
注意：
1.group by 语句出现在from 表名或where子句后面
2.若加上having 条件表达式,前面必须要有group by 字段名,紧跟group by 后面
*/
--1.在Student数据库中创建一个学生信息表：stuinfo
use Student
go
create table stuinfo(-
stu_id char(4) primary key,
stu_name varchar(20) not null,
sex char(2)default('男') check(sex in('男','女')),
zy varchar(20),
birthday datetime
)
select * from stuinfo
--2,向表stu_info添加记录,查看结果
insert into stuinfo(stu_id,stu_name,sex,zy,birthday) values('0001','张三','男','软件开发','1999-06-08')
insert into stuinfo(stu_id,stu_name,sex,zy,birthday) values('0002','李四','女','广告设计','1997-11-28')
insert into stuinfo(stu_id,stu_name,sex,zy,birthday) values('0003','王五','男','电子商务','1998-06-05')
insert into stuinfo values('0004','赵六','女','环境艺术','1997-06-25')
insert into stuinfo values('0005','钱七','男','软件开发','1998-05-03')
insert into stuinfo values('0006','周八','女','网络工程','1999-02-12')
insert into stuinfo values('0007','杨九','男','软件开发','1999-08-20')
select * from stuinfo
update stuinfo set zy='软件开发' where zy is null
--3.查询出stuinfo表中男女生各自人数(使用分组查询)
select sex as 性别,COUNT(*) as 人数 from stuinfo group by sex
--4.查询出stuinfo表中每个专业的人数
select zy as 专业,COUNT(*) as 人数 from stuinfo group by zy
--5.统计stuinfo男女生的平均年龄
select GETDATE()--查询当前系统日期时间
select YEAR(GETDATE())--查询当前系统日期的年份
select month(GETDATE())--查询当前系统日期的月份
select day(GETDATE())--查询当前系统日期中当月几号
select sex as 性别, AVG(YEAR(getdate())-year(birthday))as 平均年龄 from stuinfo group by sex
--6.统计出stuinfo表中的各专业人数,只显示专业人数2人(包含2)以上的
select zy as 专业,COUNT(*) as 人数 from stuinfo group by zy having COUNT(*) >=2
--7.统计出stuinfo表中软件开发和网络工程专业的各自人数(两种方法：where和group by ...having...)
select zy as 专业,COUNT(*) as 人数 from stuinfo where zy='软件开发' or zy='网络工程' group by zy
select zy '专业',count(*) '人数' from stuinfo where zy in('软件开发' , '网络工程') group by zy
select zy as 专业,COUNT(*) as 人数 from stuinfo group by zy having zy='软件开发' or zy='网络工程'
/*
六 分组小计查询
compute ... by...用法
格式：order by 字段名 compute 聚合函数列表 by 字段名
作用： 按某个字段进行多个统计
注意：
1.compute ...by ...前面必须有order by ,且两个by 后面字段名必须相同
2.compute ...by ...中by后面的字段必须是分组的字段
*/
--1.在Goods库中对"进货信息表"按进货日期分类，统计出进货数量与进货金额
use Goods
go
select * from 进货信息表 order by 进货日期 compute sum(进货数量),sum(进货金额) by 进货日期
--select * from 进货信息表 order by 进货日期 compute sum(进货数量),max(进货金额) by 进货日期
--select * from 进货信息表 order by 进货日期 compute sum(进货数量),avg(进货金额) by 进货日期