参考文献：

SQLServer内置函数官方文档

SQL内置函数

聚合函数: 返回汇总值。

转型函数: 将一种数据类型转换为另外一种。

日期函数: 处理日期和时间。

数学函数: 执行算术运算。

字符串函数: 对字符串、二进制数据或表达式执行操作。

系统函数: 从数据库返回在SQLSERVER中的值、对象或设置的特殊信息。

文本和图像函数: 对文本和图像数据执行操作。

下面仅列出常用的聚合函数、日期函数、数学函数和字符串函数：

1、聚合函数

AVG(表达式) 返回表达式中所有的平均值。仅用于数字列并自动忽略NULL值。

COUNT(表达式) 返回表达式中非NULL值的数量。可用于数字和字符列。

COUNT(*) 返回表中的行数(包括有NULL值的列)。

MAX(表达式) 返回表达式中的最大值,忽略NULL值。可用于数字、字符和日期时间列。

MIN(表达式) 返回表达式中的最小值,忽略NULL值。可用于数字、字符和日期时间列。

SUM(表达式) 返回表达式中所有的总和,忽略NULL值。仅用于数字列。

2、日期函数

GETDATE() 当前的系统日期。

举例：select GETDATE()  --结果：2019-05-07 18:34:27.343（SQL执行时的系统当前时间）

DATEADD(日期部分,number,date)  

返回带有指定数字(number)的日期(date),该数字添加到指定的日期部分(datepart)

举例：select DATEADD(dd, 5, getdate())  --在系统当前时间的基础上增加5天时间，第1个参数dd表示天数

DATEDIFF(日期部分,date1,date2) 返回两个日期中指定的日期部分之间的差值。

举例：select DATEDIFF(mm, '2010-1-1', '2010-3-1 00:00:00')  --结果：2，第1个参数mm表示月份

DATENAME(日期部分,date) 返回日期中日期部分的字符串形式。

举例：select DATENAME(dw,GETDATE()) --结果：星期二

注：DATENAME 和 DATEPART 的区别，返回的值类型不同，一个是VARCHAR一个是INT，另外就是星期会用本地语言来表示。

第1个参数说明如下：（SQL中日期的表示方法及有效范围）

DATEPART(日期部分,date) 返回日期中指定的日期部分的整数形式。

举例：select DATEPART(dw,GETDATE()) --结果(返回今天是一周中的第几天)：3

YEAR(date) 返回指定日期的年份数值

举例：select YEAR(GETDATE())  --结果：2019

MONTH(date) 返回指定日期的月份数值

DAY(date) 返回指定日期的天数值

3、数学函数

ABS(num_expr) 返回数值表达式的绝对值。

ACOS(float_expr)   返回角(以弧度表示),它的余弦值近似于指定的浮点表达式。

ASIN(float_expr)   返回角(以弧度表示),它的正弦值近似于指定的浮点表达式。

ATAN(float_expr)   返回角(以弧度表示),它的正切值近似于指定的浮点表达式。

ATN2(float_expr1, float_expr2)   返回角(以弧度表示),它的正切值在两个近似的浮点表达式之间。

CEILING(num_expr)   返回大于或等于数值表达式的最小整数。

COS(float_expr)   返回以浮点表达式表示的近似于指定角度(以弧度表示)的余弦三角函数的值。

COT(float_expr)   返回以浮点表达式表示的近似于指定角度(以弧度表示)的余切三角函数的值。

DEGREES(num_expr)  返回数值表达式表示的弧度值对应的度值。

EXP(float_expr)   根据指定的近似浮点表达式,返回指数值。

FLOOR(num_expr)   返回小于或等于数值表达式的最大整数。

LOG(float_expr)   根据指定的近似浮点表达式,返回自然对数值。

LOG10(float_expr)   根据指定的近似浮点表达式,返回以为底的对数。

PI()   返回常量值.141592653589793

POWER(num_expr,y)   返回幂为y的数值表达式的值。

RADIANS(num_expr)   返回数值表达式表示的度值对应的弧度值。

RAND([seed])   随机返回的到之间的近似浮点值,可以对seed指定为整数表达式(可选)。

ROUND(num_expr,length)   对数值表达式截取指定的整数长度,返回四舍五入后的值。

SIGN(num_expr)   对正数执行+1操作,对负数和零执行-1操作。

SIN(float_expr)   返回以浮点表达式表示的近似于指定角度(以弧度表示)的正弦三角函数的值。

SQUARE(float_expr)   返回浮点表达式的平均值。

SQRT(float_expr)   返回指定的近似浮点表达式的平方根。

TAN(float_expr)   返回以浮点表达式表示的近似于指定角度(以弧度表示)的正切三角函数的值。

4、字符串函数

可用于binary 和varbinary数据类型列,但主要用于char和varchar数据类型。

Expr1+expr2 返回两个表达式的组合形式的字符串。

CHARINDEX('pattern',char_expr) 返回字符表达式中指定模式的起始位置。

举例：SELECT CHARINDEX('bicycle','Reflectors are vital safety components of your bicycle.'); //返回48

LEFT ( char_expr , int_expr)   返回从字符表达式最左端起根据指定的字符个数得到的字符。

举例：SELECT LEFT('abcdefg',2);    //返回ab

LEN(char_expr)   返回字符表达式的长度。

举例：SELECT LEN('abcdefg');    //返回7

LOWER(char_expr)   将字符表达式全部转换为小写。

LTRIM(char_expr)   返回删除掉前面空格的字符表达式。

举例：SELECT LTRIM('     Five spaces are at the beginning of this string.')

          //返回Five spaces are at the beginning of this string.

PATINDEX('%pattern%',expr)   返回表达式中模式第一次出现的起始位置。

举例：SELECT PATINDEX('%en_ure%', 'Please ensure the door is locked!');     //返回8

REPLACE ( string_expression , string_pattern , string_replacement )    替换字符串中的内容。

举例：SELECT REPLACE('abcdefghicde','cde','xxx');    //返回abxxxfghixxx

REPLICATE(char_expr,int_expr)   返回重复指定次数的字符表达式产生的字符串。

举例：SELECT REPLICATE('0', 4);  //返回0000

REVERSE(char_expr)   反转字符表达式。

举例：SELECT REVERSE('ken');   //返回nek

RIGHT(char_expr,int_expr)   返回从字符表达式最右端起根据指定的字符个数得到的字符。

举例：SELECT RIGHT('abcdefg', 2);   //返回fg

RTRIM(char_expr)   返回删除掉其后空格的字符表达式。

举例：SELECT RTRIM('Removes trailing spaces.   ');   //返回Removes trailing spaces.

SPACE(int_expr)   返回包含指定空格数的字符串。

举例：

SELECT RTRIM(LastName) + ',' + SPACE(2) +  LTRIM(FirstName)  

FROM Person ORDER BY LastName, FirstName;  

STR(float_expr[,length[,decimal]])   返回浮点表达式的字符串表示法。

举例：SELECT STR(123.45, 6, 1);  //返回123.5 ，第2个参数表示总长度，第3个参数表示小数位数

STUFF(char_expr1,start,length,char_expr2)   使用字符表达式替换字符表达式的一部分字符,从指定的位置开始替换指定的长度。

举例：SELECT STUFF('abcdef', 2, 3, 'ijklmn');    //返回aijklmnef

SUBSTRING(char_expr,start,length)   返回从字符表达式的指定位置开始,截取指定长度得到的字符集。

举例：SELECT SUBSTRING('abcdef', 2, 3);  //返回bcd,索引从1开始计数

UPPER(char_expr)   将字符表达式全部转换为大写。

                                            分组查询

转载自：https://zhuanlan.zhihu.com/p/626153850

GROUP BY: 以某个或者某些字段将数据进行分组成若干个小集合

在不使用group by 关键字的时候使用 聚合函数

是将整个表看成一个大组。 所以返回的是一行数据。

当我们需要将整个表分成过个小组的时候 ， 就需要使用group by 关键字了 来告诉数据库， 这个大集合，你要靠什么来进行分组。

#需求：查询各个部门的平均工资，最高工资
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id

#需求：查询各个job_id的平均工资
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;

结论： 以某个列进行分组， 其返回的结果集中该列一定具有唯一性

多列分组

先按照A列分组，让后按照B列进行分组。 A+B 在分组后的结果集中保持着唯一性。

分组后查询列问题——以A列分组，但是要查询显示B 列

因为 在group by 中以 A列 department 进行分组的，分组后 一个department 只有一行数据，但是同一个department中B列Jod有多个， 那么这一列应该显示谁了？ 无法自洽。 所以在分组中 以A列进行分组但是要展示B类是错误的。

#结论：SELECT中出现的非组函数的字段必须声明在GROUP BY 中 (比如上面的job_id就是非组函数字段)。反之，GROUP BY中声明的字段可以不出现在SELECT中。

Having ：对分组后结果进行过滤

HAVING的使用 (作用：用来过滤数据的， 仅仅过滤的是分组后的数据)

1. #要求1：如果过滤条件中使用了聚合函数 ，则必须使用HAVING来替换WHERE。否则，报错。

2. #要求2：HAVING 必须声明在 GROUP BY 的后面。

练习：查询各个部门中最高工资比10000高的部门信息
#正确的写法：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

                                   HAVING子句

转载自：https://zhuanlan.zhihu.com/p/531246390?utm_id=0

HAVING 也是条件筛选语句，放在GROUP BY的后面。

基本语法：

SELECT  <字段名> 
FROM  <表名>  
GROUP BY <字段名>
HAVING  <筛选条件>;

1、HAVING与WHERE的区别

HAVING 与我们前面学的 WHERE 都是条件筛选语句，他俩不仅作用差不多，写法也相通。

WHERE语句后面的比较运算符、IN、BETWEEN、LIKE等，在HAVING中也可以使用。

这俩的本质区别是：

WHERE是在GROUP BY分组之前进行条件筛选，后面不可以跟聚合函数。

HAVING是在GROUP BY分组之后进行条件筛选，后面可以直接跟聚合函数。

实例：在Students表中，找出学生编号Sid小于8的记录，并查找每个班主任带的男女学生数量，最后输出数量大于2的记录。

示例结果：

实例解析：我们要输出数量大于2的记录，筛选条件就是COUNT(*)>2，WHERE后面不能跟聚合函数，所以这个筛选条件要放在HAVING的后面。

SELECT Tid,Ssex,COUNT(*)
FROM Students
WHERE Sid<8
GROUP BY Tid,Ssex
HAVING COUNT(*) > 2

这里有的同学可能也有疑问了，既然HAVING也能做条件筛选，那我们可以将上面SQL语句的WHERE去掉，筛选条件都合并到HAVING中也行吧，就像下面这样：

SELECT Tid,Ssex,COUNT(*)
FROM Students
GROUP BY Tid,Ssex
HAVING Sid<8 AND COUNT(*) > 2

大家可以运行一下这个语句，会报错 Unknown column 'Sid' in 'having clause’，我们不能这样写。

因为HAVING后面的字段如果是表中现有的列，则这个列必须出现在SELECT后面。

比如我们将HAVING后面的Sid改成Tid，语句就正确了，因为Tid，也在SELECT的后面。

HAVING Tid<8 AND COUNT(*) > 2

2、字段总结

HAVING 字段、SELECT字段，再加上前面讲的GROUP BY字段，他们后面列之间的关系，大家可能会觉得有点乱，我们在这里整体总结一下，理顺一下思路。

最终可以总结为下面的SQL语句，其中A、B、C都是表中现有的列。

SELECT A, C,COUNT(A)
GROUP BY A,B,C
HAVING A>1

GROUP BY后面如果是A,B,C：

SELECT后面跟的列，只要包含在A,B,C中就可以了。可以是A,B,C，也可以是A,B，也可以是A,C，也可以是A，但不能是A,D。

SELECT后面如果是A,C：

HAVING后面跟的列，只要包含在A,C中就可以了，可以是A,C，也可以是A，也可以是C，但不能是B。

也就是GROUP BY中没有出现的列，SELECT中就不能出现，SELECT中没有出现的列，HAVING中就不能出现。

有点绕，大家可以根据上面的实例感受一下~

之所以是这样，其中跟语句的执行顺序有关系，我们后面课程会详细讲解语句的执行顺序。

作业：结合Students表和Teachers表，查找每个班主任带的男女学生数量，选出每组学生数量大于2的记录。

示例结果：

作业解析：根据示例答案，第一列是班主任姓名，Students表中只有教师编号Tid，所以我们需要JOIN连接Teachers表，获取到班主任姓名。

还要查找每个班主任带的男女学生数量，通过GROUP BY对班主任Tname，学生性别Ssex分组，再通过COUNT(*)计算数量就可以了。

最后选出每组学生数量大于2的记录，筛选条件就是COUNT(*)>2，WHERE后面不能跟聚合函数，所以这个筛选条件要放在HAVING的后面。

SELECT
  t.Tname AS "老师姓名",
  s.Ssex AS "学生性别",
  COUNT(*) AS "数量"
FROM Teachers AS t
JOIN Students AS s
ON t.Tid = s.Tid
GROUP BY t.Tname,s.Ssex
HAVING COUNT(*)>2;