＜SQL＞《SQL命令（含例句）精心整理版（2）》

跳转《SQL命令（含例句）精心整理版（1）

8 函数

8.1 文本处理函数

函数	说明
LEFT()	（或使用子字符串函数） 返回字符串左边的字符
LENGTH()	（也使用DATALENGTH()或LEN()） 返回字符串长度
LOWER()	（Access使用LCASE()） 将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RIGHT()	（或使用子字符串函数） 返回字符串右边的字符
RTRIM()	去掉字符串右边的空格
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()	（Access使用UCASE()） 将字符串转换成大写

8.2 数值处理函数

函数	说明
ABS()	返回一个数的绝对值
COS()	返回一个角度的余弦
EXP()	返回一个数的指数值
PI()	返回圆周率
SIN()	返回一个角度的正弦
SQRT()	返回一个数的平方根
TAN()	返回一个角度的正切

8.3 时间处理函数

函数	说明
now()	当前日期日期
curdate()	当前日期
curtime（）	当前时间
时间差	DATE_ADD() # 给日期添加指定的时间间隔 DATE_SUB() # 给日期减去指定的时间间隔 例如：DATE_SUB(curdate(),INTERVAL 7 DAY) 当前日期减七天
时间差函数 timestampdiff	语法： timestampdiff（unit，begin，end） begin和end可以为DATE或DATETIME类型，并且可允许参数为混合类型。

8.3.1 时间戳转化为自定义格式from_unixtime

函数	说明
格式	from_unixtime(timestamp(需要转化的时间戳),‘%Y-%m-%d %h:%i:%s’)
%Y 年	4位，2023
%y	年，2位，23
%M	月名
%m	月，数值(00-12)
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%H:%i:%s	00:00:00

8.3.2 转化时间格式CONVERT()

函数	说明
格式	CONVERT(expr,type) expr: 要转换的值 type: 要转换为的数据类型
DATE	将expr转换成’YYYY-MM-DD’格式
DATETIME	将expr转换成’YYYY-MM-DD HH：MM：SS’格式
TIME	将expr转换成’HH：MM：SS’格式
CHAR	将expr转换成CHAR(固定长度的字符串)格式
SIGNED	将expr转换成INT(有符号的整数)格式
UNSIGNED	将expr转换成INT(无符号的整数)格式
DECIMAL	将expr转换成FLOAT(浮点数)格式
BINARY	将expr转换成二进制格式

8.3.3 获取系统时间

函数	说明
DBMS	函数/变量
Access	NOW()
DB2	CURRENT_DATE
MySQL	CURRENT_DATE()
Oracle	SYSDATE
PostgreSQL	CURRENT_DATE
SQL Server	GETDATE()
SQLite	Date(‘now’)

8.4 聚集函数（aggregate function）

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUN()	返回某列值之和
举例：组合聚合函数	SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM Products

COALESCE

按顺序对其参数求值并返回第一个非NULL值。仅当所有参数均为NULL时结果才为NULL。
为消息中可能不存在的字段提供缺省值。
表达式：COALESCE(Body.Salary,0) ,如果消息中有SALARY字段,将返回它的值，如果该字段不存在，将返回0.

9 子查询

9.1 子查询

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否

9.2 GROUP BY & ORDER BY

例1：SELECT vend_id,count() AS num_prods FROM Products GROUP BY vend_id
例2：SELECT cust_id,count() AS orders FROM Orders GROUP BY cust_id HAVING COUNT() >=2

9.3 分组排序 （row_number()over()）

（1）对查询结果进行排序（无分组）

SELECT empno,WORKDEPT,SALARY,Row_Number() OVER (partition by workdept ORDER BY salary desc) rank FROM employee

empno	WORKDEPT	SALARY	Row_Number()
000010	A00	152750	1
000110	A00	66500	2
000120	A00	49250	3
200010	A00	46500	4
200120	A00	39250	5
000020	B01	94250	1
000030	C01	98250	1
000130	C11	73800	2

（2）对查询结果进行排序（无分组）

SELECT empno,WORKDEPT,SALARY,Row_Number() OVER (ORDER BY salary desc) rank FROM employee

empno	WORKDEPT	SALARY	Row_Number()
000010	A00	152750	1
000030	C01	98250	2
000070	D21	96170	3
000020	B01	94250	4
000090	E11	89750	5
000100	E21	86150	6
000050	E01	80175	7
000130	C11	73800	8
000060	D11	72250	9

小结：row_number() over()和rownum差不多，功能更强一点。（可以在各个分组内从1开始排序）

（3）rank() over() 是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

SELECT  WORKDEPT,SALARY,rank() OVER (partition by workdept ORDER BY salary ) as dense_rank_order FROM emp order by workdept

WORKDEPT	SALARY	rank()
A00	39250	1
A00	46500	2
A00	49250	3
A00	66500	4
A00	152750	5
B01	94250	1
C01	68420	1
C01	68420	1
C01	73800	3

（4）dense_rank() over() 是连续排序，有两个第二名时仍然跟着第三名。相比之下，row_number是没有重复值的

SELECT  WORKDEPT,SALARY,dense_rank() OVER (partition by workdept ORDER BY salary ) as dense_rank_order FROM emp order by workdept

WORKDEPT	SALARY	dense_rank()
A00	39250	1
A00	46500	2
A00	49250	3
A00	66500	4
A00	152750	5
B01	94250	1
C01	68420	1
C01	68420	1
C01	72420	2
C01	73800	3