函数
-
函数可以把我们经常使用的代码封装起来, 需要的时候直接调用即可。这样既 提高了代码效率 ,又 提高了可维护性 。在 SQL 中我们也可以使用函数 对检索出来的数据进行函数操作。使用这些函数,可以极大地 提高用户对数据库的管理效率 。
-
不同DBMS函数的差异 我们在使用 SQL 语言的时候,不是直接和这门语言打交道,而是通过它使用不同的数据库软件,即 DBMS。DBMS 之间的差异性很大,远大于同一个语言不同版本之间的差异。实际上,只有很少的函数是 被 DBMS 同时支持的。大部分 DBMS 会有自己特定的函数,这就意味着采用 SQL 函数的代码可移植性是很差的,
-
因此在使用函数的时候需要特别注意
基本函数
角度与弧度互换函数
三角函数
指数与对数
进制间的转换
字符串函数
函数 | 用法 |
---|---|
ASCII(S) | 返回字符串S中的第一个字符的ASCII码值 |
CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关 |
CONCAT(s1,s2,…,sn) | 连接s1,s2,…,sn为一个字符串 |
CONCAT_WS(x, s1,s2,…,sn) | CONCAT(s1,s2,…)函数,但是每个字符串之间要加上x |
INSERT(str, idx, len, replacestr) | 将字符串str从第idx位置开始,len个字符长的子串替换为字符串replacestr |
REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
LEFT(str,n) | 返回字符串str最左边的n个字符 |
RIGHT(str,n) | 返回字符串str最右边的n个字符 |
LPAD(str, len, pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) | 去掉字符串s左侧的空格 |
RTRIM(s) | 去掉字符串s右侧的空格 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
TRIM(s1 FROM s) | 去掉字符串s开始与结尾的s1 |
TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 |
TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾处的s1 |
REPEAT(str, n) | 返回str重复n次的结果 |
SPACE(n) | 返回n个空格 |
STRCMP(s1,s2) | 比较字符串s1,s2的ASCII码值的大小 |
SUBSTR(s,index,len) | 返回从字符串s的index位置其len个字符,作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同 |
LOCATE(substr,str) | 返回字符串substr在字符串str中首次出现的位置,作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到,返回0 |
ELT(m,s1,s2,…,sn) | 返回指定位置的字符串,如果m=1,则返回s1,如果m=2,则返回s2,如 果m=n,则返回sn |
FIELD(s,s1,s2,…,sn) | 返回字符串s在字符串列表中第一次出现的位置 |
FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中出现的位置。其中,字符串s2是一个以逗号分 隔的字符串 |
REVERSE(s) | 返回s反转后的字符串 |
NULLIF(value1,value2) | 比较两个字符串,如果value1与value2相等,则返回NULL,否则返回 value1 |
日期和时间函数
函数 | 用法 |
---|---|
CURDATE() ,CURRENT_DATE() | 返回当前日期,只包含年、 月、日 |
CURTIME() , CURRENT_TIME() | 返回当前时间,只包含时、 分、秒 |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期和时间 |
UTC_DATE() | 返回UTC(世界标准时间) 日期 |
UTC_TIME() | 返回UTC(世界标准时间) 时间 |
UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884 |
UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回。 |
FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 |
HOUR(time) / MINUTE(time) / SECOND(time) | 返回具体的时间值 |
MONTHNAME(date) | 返回月份:January,… |
DAYNAME(date) | 返回星期几:MONDAY,TUESDAY…SUNDAY |
WEEKDAY(date) | 返回周几,注意,周1是0,周2是1,。。。周日是6 |
QUARTER(date) | 返回日期对应的季度,范围为1~4 |
返回日期对应的季度,范围为1~4 | 返回一年中的第几周 |
DAYOFYEAR(date) | 返回日期是一年中的第几天 |
DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
DAYOFWEEK(date) | 返回周几,注意:周日是1,周一是2,。。。周六是 7 |
TIME_TO_SEC(time) | 将 time 转化为秒并返回结果值。转化的公式为: 小时*3600+分钟 *60+秒 |
SEC_TO_TIME(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间 |
DATE_ADD(datetime, INTERVAL expr type), ADDDATE(date,INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时 间段的日期时间 |
DATE_SUB(date,INTERVAL expr type), SUBDATE(date,INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的 日期 |
ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时,代表的是 秒 ,可以为负数 |
SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时,代表的 是 秒 ,可以为负数 |
DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 |
TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 |
FROM_DAYS(N) | FROM_DAYS(N) |
LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回一个日期 |
MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 |
PERIOD_ADD(time,n) | 返回time加上n后的时间 |
EXTRACT(type FROM date) | 返回指定日期中特定的部分,type指定返回的值 |
EXTRACT(type FROM date)函数中type的取值与含义:
日期的格式化与解析
函数 | 用法 |
---|---|
DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 |
GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 |
STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析,解析为一个日期 |
函数中fmt参数常用的格式符:
格 式 符 | 说明 |
---|---|
%Y | 4位数字表示年份 |
%y | 表示两位数字表示年份 |
%M | 月名表示月份(January,…) |
%m | 两位数字表示月份 (01,02,03。。。) |
%b | 缩写的月名(Jan.,Feb.,…) |
%c | 数字表示月份(1,2,3,…) |
%D | 英文后缀表示月中的天数 (1st,2nd,3rd,…) |
%d | 两位数字表示月中的天数(01,02…) |
%e | 数字形式表示月中的天数 (1,2,3,4,5…) |
%H | 两位数字表示小数,24小时制 (01,02…) |
%h 和% | 两位数字表示小时,12小时制 (01,02…) |
%k | 数字形式的小时,24小时制(1,2,3) |
%l | 数字形式表示小时,12小时制 (1,2,3,4…) |
%i | 两位数字表示分钟(00,01,02) |
%S 和%s | 两位数字表示秒(00,01,02…) |
%W | 一周中的星期名称(Sunday…) |
%a | 一周中的星期缩写(Sun., Mon.,Tues.,…) |
%w | 以数字表示周中的天数 (0=Sunday,1=Monday…) |
%j | 以3位数字表示年中的天数(001,002…) |
%U | 以数字表示年中的第几周, (1,2,3。。)其中Sunday为周中第一 天 |
%u | 以数字表示年中的第几周, (1,2,3。。)其中Monday为周中第一 天 |
%T | 24小时制 |
%r | 12小时制 |
%p | AM或PM |
%% | 表示% |
流程控制函数
- 流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择。 MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
函数 | 用法 |
---|---|
IF(value,value1,value2) | 如果value的值为TRUE,返回value1, 否则返回value2 |
IFNULL(value1, value2 | 如果value1不为NULL,返回value1,否 则返回value2 |
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END | 相当于Java的if…else if…else… |
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END | 相当于Java的switch…case… |
SELECT CASE
WHEN XXX
THEN XXX
WHEN XXX
THEN XXX
ELSE XXX
END XXX
SELECT CASE A
WHEN 1 THEN '1'
WHEN 2 THEN '2'
ELSE '0'
加密与解密函数
函数 | 用法 |
---|---|
PASSWORD(str) | 返回字符串str的加密版本,41位长的字符串。加密结果 不可 逆 ,常用于用户的密码加密 |
MD5(str) | 返回字符串str的md5加密后的值,也是一种加密方式。若参数为 NULL,则会返回NULL |
SHA(str) | 从原明文密码str计算并返回加密后的密码字符串,当参数为 NULL时,返回NULL。 SHA加密算法比MD5更加安全 。 |
ENCODE(value,password_seed) | 返回使用password_seed作为加密密码加密value |
DECODE(value,password_seed) | 返回使用password_seed作为加密密码解密value |
聚合函数
聚合函数类型
- AVG() 、SUM()
- 可以对数值型数据使用AVG 和 SUM 函数。
- MAX() 、MIN()
- 可以对数值型数据使用AVG 和 SUM 函数。
- COUNT()
- COUNT(*)返回表中记录总数,适用于任意数据类型。
- COUNT(A) 返回A列不为空的记录总数。
非法使用聚合函数 : 不能在 WHERE 子句中使用聚合函数。
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY
- 可以使用GROUP BY子句将表中的数据分成若干组
- GROUP BY中使用WITH ROLLUP关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量
HAVING
- 行已经被分组。
- 使用了聚合函数。
- 满足HAVING 子句中条件的分组将被显示。
- HAVING 不能单独使用,必须要跟 GROUP BY 一起使用。
WHERE和HAVING的对比
- WHERE 可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件;HAVING 必须要与 GROUP BY 配合使用,可以把分组计算的函数和分组字段作为筛选条件。HAVING 可以完成 WHERE 不能完成的任务。这是因为, 在查询语法结构中,WHERE 在 GROUP BY 之前,所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之 后,可以使用分组字段和分组中的计算函数,对分组的结果集进行筛选,这个功能是 WHERE 无法完成 的。另外,WHERE排除的记录不再包括在分组中。 区别
- 如果需要通过连接从关联表中获取需要的数据,WHERE 是先筛选后连接,而 HAVING 是先连接 后筛选。 这一点,就决定了在关联查询中,WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选,用一 个筛选后的较小数据集和关联表进行连接,这样占用的资源比较少,执行效率也比较高。HAVING 则需要 先把结果集准备好,也就是用未被筛选的数据集进行关联,然后对这个大的数据集进行筛选,这样占用 的资源就比较多,执行效率也较低。
- WHERE 先筛选数据再关联,执行效率高 不能使用分组中的计算函数进行筛选
- HAVING 可以使用分组中的计算函数 在最后的结果集中进行筛选,执行效率较低