函数与分组查询数据
一、系统函数
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
WHENSEX='男' THEN 1
ELSENULL
END)AS 男生人数,
COUNT(CASE
WHENSEX='女' THEN 1
ELSENULL
END)AS 女生人数
FROM STU_INFO
GROUP BY DEPART
6.使用HAVING子句设置分组查询条件
SELECT DEPART,COUNT(*) AS 人数
FROM STU_INFO
GROUP BY DEPART
HAVING DEPART IN('外语系')