第五章 SQL函数的运用

一：常用函数

   COUNT, SUM, AVG, MAX, MIN,REPLACE, SUBSTR

 

二：SQL中的子句
   ORDER BY   排序
   GROUP BY 分组
   HVING

 

三：聚合函数和GROUP BY子句
   GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的聚合值。如果聚合函数没有使用 GROUP BY 子句，则只为 SELECT 语句报告一个聚合值。

   常用的聚合函数：

       MIN：求一列中的最小值
       MAX：求一列中的最大值
       SUM：按列计算值的总和
       AVG：按列计算平均值
       COUNT ：按列值计个数

       COUNT(*)：返回表中的所用行数

use test
create table student(
	id int identity primary key,
	stdName nvarchar(10),
	age int,
	address nvarchar(20),
	class nvarchar(10),
	speciality nvarchar(10),
	mark numeric(4,1),
)
insert into student select '张三',20,'咸宁','200801','软件开发',75
insert into student select '李四',18,'武汉','200801','网络',95
insert into student select '王五',22,'宜昌','200801','软件开发',55
insert into student select '赵六',23,'宜昌','200801','网络',90
insert into student select '小明',18,'孝感','200801','软件开发',90
insert into student select '小王',19,'钟祥','200801','网络',100
insert into student select '小红',21,'武汉','200801','软件开发',99
insert into student select '小黄',20,'宜昌','200801','网络',87

--平均分
select sum(mark)/count(*) from student 
select avg(mark) from student

--1.查询班上有多少位同学
select count(*) '总数' from student
select count(*) '总数' from student where address='武汉'

--2.请求出班上所有同学的JAVA成绩的总分
select sum(mark) '总成绩' from student
select sum(mark) '总成绩' from student where address='武汉'

--3.请求出班上JAVA成绩的平均分
select avg(mark) '平均分' from student
select avg(mark) '平均分' from student where address='宜昌'

--4.请求出JAVA成绩最高分
select max(mark) '最高分' from student
select max(mark) '最高分' from student where address='宜昌'

--5.请求出JAVA成绩最低分
select min(mark) '最低分' from student
select MIN(mark) '最低分' from student where address='武汉'

--order by 排序
select * from student order by mark	asc		-- 升序,默认
select * from student order by mark desc	--降序

--带where条件的排序
select * from student where addredd='宜昌' order by mark desc
--两个条件,先满足第一个条件,其次再满足第二个条件
select * from student where age<20 order by mark desc

--group by分组汇总函数,就是把某一列中，相同的字段进行分组汇总, 
--group by不能单独使用,必须与其他汇总函数一起使用.如MIN,MAX,SUM,AVG,COUNT,COUNT(*)
select speciality as '专业', count(*) as '人员总数' FROM student group by speciality
select address,count(address) from student group by address 
select address,count(address) as '地址总数' from student where age<20 group by address having count(address)>=2
select address,count(address) as addressCount from student where age<20 group by address order by addressCount desc

--distinct删除重复项
select count(distinct speciality) as speciality from student

--having 分类汇总后的条件
select address,count(address) as COUNTSSTU from student where age<20 group by address having count(address)>=2

 

四：SQLSERVER中日期类型的使用 datetime  

 

create table datetimetable(
	id int identity primary key,
	ctime datetime
)

--日期类型的插入方式
--1.使用字符串的形式插入，常用格式两种2010-03-02,2010/03/02
insert into datetimetable values('2010-03-02')
insert into datetimetable values('2010/03/02')
insert into datetimetable values('2010-03-02 21:25:50')
insert into datetimetable values('2010/03/02 21:25:50')

--2.使用内置函数的形式插入
insert into datetimetable select getdate()

   --日期字段使用要注意的地方
   --1.插入值的时候,格式无所谓,在数据库中保存的格式永远都是2009-03-02 00:00:00.000
   --yyyy-MM-dd HH:mm:ss:SSS,我们要做的事情就是处理取出时间的格式
   --取出日期的处理方式,需要借助于日期格式处理函数
   --CONVERT(返回类型,要格式的日期,格式参数)

  
   --字符串工具replace,substring,len的使用
   --replace的使用,请将日期格式改成yyyyMMddHHmmss 20090302142636
   --replace(内容,要转的字符,转换成什么内容)
   select replace(replace(replace(replace(convert(varchar(30),ctime,21),'-',''),' ',''),':',''),'.','')   from datetimetable

    --substring,字符串的截取
    --substring(内容,开始下标,要截取的长度),下标从1开始,
    select substring(replace(replace(replace(replace(convert(varchar(30),ctime,21),'-',''),' ',''),':',''),'.',''),1,4)    from datetimetable

    --LEN(内容):返回字符串的长度
    select len(replace(replace(replace(replace(convert(varchar(30),ctime,21),'-',''),' ',''),':',''),'.',''))    from datetimetable

     select substring('420104197911260016',7,4)