一：常用函数
?? 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(*)：返回表中的所用行数
? 四：SQLSERVER中日期类型的使用 datetime?? ? ?? --日期字段使用要注意的地方 ?? ??? --substring,字符串的截取 ??? --LEN(内容):返回字符串的长度 ???? select substring('420104197911260016',7,4)use testcreate 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','软件开发',75insert into student select '李四',18,'武汉','200801','网络',95insert into student select '王五',22,'宜昌','200801','软件开发',55insert into student select '赵六',23,'宜昌','200801','网络',90insert into student select '小明',18,'孝感','200801','软件开发',90insert into student select '小王',19,'钟祥','200801','网络',100insert into student select '小红',21,'武汉','200801','软件开发',99insert into student select '小黄',20,'宜昌','200801','网络',87--平均分select sum(mark)/count(*) from student select avg(mark) from student--1.查询班上有多少位同学select count(*) '总数' from studentselect count(*) '总数' from student where address='武汉'--2.请求出班上所有同学的JAVA成绩的总分select sum(mark) '总成绩' from studentselect sum(mark) '总成绩' from student where address='武汉'--3.请求出班上JAVA成绩的平均分select avg(mark) '平均分' from studentselect avg(mark) '平均分' from student where address='宜昌'--4.请求出JAVA成绩最高分select max(mark) '最高分' from studentselect max(mark) '最高分' from student where address='宜昌'--5.请求出JAVA成绩最低分select min(mark) '最低分' from studentselect 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 specialityselect address,count(address) from student group by address select address,count(address) as '地址总数' from student where age<20 group by address having count(address)>=2select 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
create table datetimetable( id int identity primary key, ctime datetime)--日期类型的插入方式--1.使用字符串的形式插入，常用格式两种2010-03-02,2010/03/02insert 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(内容,开始下标,要截取的长度),下标从1开始,
??? select substring(replace(replace(replace(replace(convert(varchar(30),ctime,21),'-',''),' ',''),':',''),'.',''),1,4)??? from datetimetable
??? select len(replace(replace(replace(replace(convert(varchar(30),ctime,21),'-',''),' ',''),':',''),'.',''))??? from datetimetable
?