20141031--SQL分组，数学函数，聚合函数

/*  通过代码操作:创建一个数据库,里面有一个学生信息表,
    内容包括:学号,姓名,性别,体重,年龄,语数外三门课分数,班级    插入20条数据
    执行以下查询操作:
    1.查姓王的同学的信息
    2.分别查每门课程最高分,最低分
    3.查男同学的名字,只查一列
    4.查每个班每门课程分别最高分最低分*/

create database data01
go
use data01
go
create table xueshengxinxi--注意列之间用,隔开
(
[No.] int,
name varchar(10),
sex varchar(10),
age int,
[weight] decimal(10,2),
Chinese int,
Math int,
English int
)
insert into xueshengxinxi values(1,'张蕾蕾','女',20,50,95,90,97)
insert into xueshengxinxi values(2,'李鑫','男',21,65,90,90,88)
insert into xueshengxinxi values(3,'刘莉莉','女',21,55,90,80,100)
insert into xueshengxinxi values(4,'张峰','男',20,63,85,85,80)
insert into xueshengxinxi values(5,'王乐','男',20,66,80,90,73)
insert into xueshengxinxi values(6,'王馨茹','女',19,47,99,70,96)
insert into xueshengxinxi values(7,'赵矿一','男',20,70,70,60,55)
insert into xueshengxinxi values(8,'程依依','女',19,45,87,89,86)
insert into xueshengxinxi values(9,'程依儿','女',19,45,90,85,87)
insert into xueshengxinxi values(10,'孙厚','男',21,76,75,67,63)
insert into xueshengxinxi values(11,'朱磊','男',20,67,70,78,71)
insert into xueshengxinxi values(12,'王谦','男',22,71,80,81,74)
insert into xueshengxinxi values(13,'孙丽娜','女',21,49,90,71,96)
insert into xueshengxinxi values(14,'张乐乐','女',22,51,81,72,84)
insert into xueshengxinxi values(15,'李忠','男',21,61,79,81,75)
insert into xueshengxinxi values(16,'李艳艳','女',20,48,70,60,98)
insert into xueshengxinxi values(17,'王萌萌','女',22,50,90,63,70)
insert into xueshengxinxi values(18,'刘星','男',22,66,70,81,66)
insert into xueshengxinxi values(19,'邹子冰','女',21,44,70,70,80)
insert into xueshengxinxi values(20,'富国庆','男',22,70,80,50,70)

select *from xueshengxinxi
--查某姓的同学
select *from xueshengxinxi where name like '刘%'
--分别查每门课程最高分,最低分
select top 1 *from xueshengxinxi order by Chinese desc--Chinese最高分
select top 1 *from xueshengxinxi order by Math desc--Math最高分
select top 1 *from xueshengxinxi order by English desc--English最高分
select top 1 *from xueshengxinxi order by Chinese asc--Chinese最低分
select top 1 *from xueshengxinxi order by Math --Math最低分
select top 1 *from xueshengxinxi order by English --English最低分
--    3.查男同学的名字,只查一列
select name from xueshengxinxi where sex='男'
--    4.查每个班每门课程分别最高分最低分*/
--把班级忘了,
alter table xueshengxinxi add Class varchar(10)--添加班级一列
update xueshengxinxi set Class='12级1班' where [No.] between 1 and 7
update xueshengxinxi set Class='12级2班' where [No.] between 8 and 14
update xueshengxinxi set Class='12级3班' where [No.] between 15 and 20
--查每个班每门课程分别最高分最低分
select top 1 *from xueshengxinxi where Class='12级1班' order by English
select top 1 *from xueshengxinxi where Class='12级1班' order by Chinese
select top 1 *from xueshengxinxi where Class='12级1班' order by Math
select top 1 *from xueshengxinxi where Class='12级1班' order by English desc
select top 1 *from xueshengxinxi where Class='12级1班' order by Chinese desc
select top 1 *from xueshengxinxi where Class='12级1班' order by Math desc

select top 1 *from xueshengxinxi where Class='12级2班' order by English
select top 1 *from xueshengxinxi where Class='12级2班' order by Chinese
select top 1 *from xueshengxinxi where Class='12级2班' order by Math
select top 1 *from xueshengxinxi where Class='12级2班' order by English desc
select top 1 *from xueshengxinxi where Class='12级2班' order by Chinese desc
select top 1 *from xueshengxinxi where Class='12级2班' order by Math desc

select top 1 *from xueshengxinxi where Class='12级3班' order by English
select top 1 *from xueshengxinxi where Class='12级3班' order by Chinese
select top 1 *from xueshengxinxi where Class='12级3班' order by Math
select top 1 *from xueshengxinxi where Class='12级3班' order by English desc
select top 1 *from xueshengxinxi where Class='12级3班' order by Chinese desc
select top 1 *from xueshengxinxi where Class='12级3班' order by Math desc

---------------------聚合函数-------------------------
--只针对一列操作，只返回一个值，可以组合使用
select *from xueshengxinxi
--求平均值 AVG 只能对数字类型进行操作。返回一个值，根据列的数据类型返回
select avg(age) as 年龄 from xueshengxinxi 
select AVG([weight]) as 体重 from xueshengxinxi 
select AVG(Chinese) as 语文 from xueshengxinxi where Class='12级1班'
-- 个数 COUNT() 括号内加字段 返回非空值的数量
select COUNT(*) as 总人数 from xueshengxinxi--显示所有个数
select COUNT(*) from xueshengxinxi where name like '王%'--按条件显示个数
select COUNT(distinct Class) from xueshengxinxi--去重显示个数。(distinct)
--最大值 MAX 最小值 MIN
select MAX(English) from xueshengxinxi where Class='12级1班'
select MIN(Math) from xueshengxinxi
--和 SUM
select SUM(Math) from xueshengxinxi
--这些聚合函数只返回一个值，可以组合使用
--组合使用聚合函数 , 使用,隔开
select AVG(age)平均年龄, COUNT(*) as 人数 from xueshengxinxi--不用as可直接在括号后跟上要显示的列名
select MAX(English) as 英语最高分, MAX(Math) as 数学最高分, MAX(Chinese) as 语文最高分 from xueshengxinxi

--------分组--------
--group by 只针对一列分组，先分完组，再进行操作（必须有聚合函数）
select Class from xueshengxinxi group by Class
select Class,AVG(Math) from xueshengxinxi group by Class
select age from xueshengxinxi where age>=20 group by age--可以添加条件
--可以显示人数用COUNT
select age,COUNT(*) from xueshengxinxi where age between 18 and 20 group by age
--习题按照课程的分数段查询每个班有多少人
select Class,COUNT(*) from xueshengxinxi where Chinese>=80 group by Class
select Class,COUNT(*) from xueshengxinxi where Chinese>=60 and English>=60 and Math>=60 group by Class
--group by 特殊的having 后面加聚合函数的选择条件
--group by 先看group by 之前的再看之后的
--先from之前，在有where的时候先算where
select Class,COUNT(*) from xueshengxinxi where Chinese>=80 group by Class having COUNT(*)>5
select Class,COUNT(*),AVG(Math) from xueshengxinxi group by Class having AVG(Math)>70
--每个班的数学平均分
select Class,COUNT(*),AVG(Math) from xueshengxinxi where Math>75 group by Class order by count(*) 
--每个班的语，数，外三门课的最高分
select Class,MAX(Chinese) as 语文,MAX(Math) as 数学,MAX(English) as 外语 from xueshengxinxi group by Class

------------------------------数学函数---------------------------
--print 是在消息框中显示  select 是在结果中显示
--取绝对值， ABS 在要取绝对值的那一列前 ABS(列名)
alter table xueshengxinxi add test int--添加test一列
alter table xueshengxinxi add test2 decimal(18,2)
select *from xueshengxinxi
select ABS(-10)
print abs(-15)
select [No.],name,abs(test) from xueshengxinxi--显示某一列的绝对值
select *from xueshengxinxi where ABS(test)>50--绝对值大于50的

--取整数 上限CEILING   下限FLOOR 前后都可使用，
select *from xueshengxinxi where CEILING(test2)>6--上限大于6的
select FLOOR(test2) from xueshengxinxi --取下限

--POWER 次方  POWER(列名,x次方)
select POWER(age,2),POWER(age,3) from xueshengxinxi--查询age列的平方 3次方
--SQRT 平方根
select SQRT(age) from xueshengxinxi--age列的平方根
--ROUND  四舍五入  ROUND(列名,x位小数)
select test2,ROUND(test2,0) from xueshengxinxi-- test2列的四舍五入（小数点后0个）