SQL 函数与分组查询数据

函数与分组查询数据

一、系统函数

1.1聚合函数

1.SUM函数

SUM函数主要用于求某一组值的和。

例如：

SELECT SUM(YEAR) AS SUM

FROM STU_INFO

2.AVG函数

AVG函数主要用于求某一组值的平均值。

例如：

SELECT AVG(YEAR) AS AVG

FROM STU_INFO

3.MIN函数

MIN函数主要用于求某一组值的最小值。

例如：

SELECT MIN(YEAR) AS MIN

FROM STU_INFO

4.MAX函数

MAX函数主要用于求某一组值的最大值。

例如：

SELECT MAX(YEAR) AS MAX

FROM STU_INFO

5.COUNT函数

COUNT函数主要用于求某一组值的个数。

例如：

SELECT COUNT(SNAME) AS COUNT

FROM STU_INFO

二、类型转换函数

1.CONVERT()函数

CONVERT()函数的语法格式为：

CONVERT(datatype[length],expression,[style])

Datatype:表示要转换的数据类型，如果要转换成CHAR、VARCHAR、BINARY、VARBINARY数据类型，还要设置数据类型的长度。

Expression:表达式，要进行数据类型转换的列名或值。

Style:用于日期格式的设置。如果要将日期类型转换为字符类型数据，则还可以使用style参数设置日期显示格式。

2.CAST()函数

CAST()函数也是数据类型转换函数，与CONVERT()函数相比，CAST()函数使用比较方便，但如果要对日期类型进行转换却没有CONVERT()函数使用方便。所以一般数据类型转换，推荐使用CAST()函数，对于日期类型转换要使用CONVERT()函数转换。

CAST()函数的语法格式：

CAST(expression AS datatype[length])

Expression:表达式

Datatype:为数据类型，如果是CHAR、   VARCHAR、NUMERIC等数据类型，则可以选择length参数设置长度。

三、日期类型

1.GETDATE()函数

GETDATE()函数用于获取当前系统时间。

2.DATEADD()函数

用于在指定日期上增加年、月、日和时间等，其返回值为日期型数据。

格式如下：
DATEADD(datepart,number,date)

Datepart参数规定在日期的哪个部分（如年份、月份等）增加（减）数值。

如:DATEADD(Year,5,GETDATE())是在当前时间的年上增加了5年，并返回5年后的日期。

3.DATEDIFF()函数

用于获取两个日期间的差，并返回数值数据。

格式：

DATEDIFF(datepart,date1,date2)

Datepart参数的说明同上，date1和date2时日期或日期格式的字符串。

 

4.DATENAME()函数

用于获取日期的一部分，并以字符串型是返回。

格式：

DATENAME(datepart,date)

Datepart参数的说明同上，date是日期或日期格式的字符串。

5.DATEPART()函数

用于获取日期的一部分，并以整数值返回。

四、数学函数

常用的数学函数：

函数	参数	说明
ABS	(numeric_表达式)	绝对值
ASIN	（float_表达式）	返回以弧度表示的角度
ATAN	（float_表达式）	返回以弧度表示的角度
COS	（float_表达式）	返回余弦值
SIN	（float_表达式）	返回正弦值
TAN	（float_表达式）	返回正切值
POWER	(numeric_表达式，y)	返回数字表达式的y次方

 

五、字符函数

常用字符函数：

函数	参数	说明
CHAR	(char_表达式)	返回ASCII码为指定整数的字符
LEFT	(char_表达式，integer_表达式)	返回字符串左面的指定个数的字符
LOWER	(char_表达式)	将字符串表达式中所有的大写字母全部转换为小写字母
RTRIM	（char_表达式）	删除字符串右边所有的空格
STR	（float_expression [,length[,decimal]]）	由数字数据转换来的字符数据
STUFF	(char_表达式，start,length,char_表达式)	删除指定长度的字符并在指定的起始点插入另一组字符
UPPER	（char_表达式）	将字符串表达式中所有的小写字母全部转换为大写字母

 

六、其他几个系统函数

1.文本和图像函数

函数	说明
PATINDEX	返回指定表达式中某模式第一次出现的位置
TEXTPTR	返回对应于varbinary格式的text、ntext列的文本指针值
TEXTVALID	检查特定文本指针是否有效的text、ntext函数

 

2.配置函数

可以返回有关配置设置的信息。

如：@@DATEFIRST、@@DBTS等

3.游标函数

可以返回有关游标状态的信息。

4.元数据函数

可以返回数据库或数据库对象的属性信息。

由于较多，就不列举了。

5.安全函数

返回有关用户和角色的信息。

6.常用的系统函数

七、分组查询

1.将表内容按列分组

GROUP BY子句用来对数据分组。

例子：

SELECT SEX

FROM STU_INFO

GROUP BY SEX

2.聚合函数与分组配合使用

        SELECT SEX,COUNT(YEAR)AS NUM

FROM STU_INFO

GROUP BY SEX

3.查询数据的直方图

SELECT SEX,REPLICATE('*',COUNT(*)*3) AS NUM

FROM STU_INFO

GROUP BY SEX

4.排序分组结果

SELECT SEX,COUNT(YEAR)AS NUM

FROM STU_INFO

GROUP BY SEX

ORDER BY NUM DESC

5.反转查询结果

        SELECT DEPART,

 COUNT(CASE

        WHEN SEX='男' THEN 1

        ELSE NULL

       END) AS 男生人数,

 COUNT(CASE

        WHEN SEX='女' THEN 1

        ELSE NULL

       END) AS 女生人数

FROM STU_INFO

GROUP BY DEPART

6.使用HAVING子句设置分组查询条件

SELECT DEPART,COUNT(*) AS 人数

FROM STU_INFO

GROUP BY DEPART

HAVING DEPART IN('外语系')