1.函数的理解
1.1什么是函数
函数在计算机语言的使用中贯彻始终,函数的作用是什么?它可以把我们经常使用的代码封装起来,需要的时候直接调用即可。 这样既 提高了代码效率,又 提高了可维护性。在SQL中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数,可以极大地提高用户对数据库的管理效率。
从函数的定义角度出发,我们可以将函数分成 内置函数 和自定义函数。在SQL语言中,同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数,而自定义函数是我们根据自己的需要编写的。
1.2不同DBMS函数的差异
我们在使用SQL语言的时候,不是直接和这门语言打交道,而是通过它使用不同的数据库软件,即DBMS。DBMS之间的差异性很大,远大于同一个语言不同版本之间的差异。实际上,只有很少的函数是被DBMS同时支持的。比如,大多数DBMS使用(||)或者(+)来做拼接符,而在MySQL中的字符串拼接函数为concat()。大部分DBMS会有自己特定的函数,这就意味着采用SQL函数的代码可移植性是很差的,因此在使用函数的时候需要特别注意。
MySQL提供了丰富的内置函数,这些函数使得数据的维护与管理更加方便,能够更好地提供数据的分析与统计功能,在一定程度上提高了开发人员进行数据分析与统计的效率。
MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。
单行函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以嵌套
- 参数可以是一列或一个值
2.数值函数
2.1基本函数
ABS(x) | 返回x的绝对值 |
SIGN(x) | 返回X的符号。正数返回1,负数返回-1,0返回0 |
PI() | 返回圆周率的值 |
CEIL(x),CEILING(x) | 返回大于或等于某个值的最小整数 |
FLOOR(x) | 返回小于或等于某个值的最大整数 |
LEAST(e1,e2,e3...) | 返回列表中的最小值 |
GREATEST(e1,e2,e3) | 返回列表中的最大值 |
MOD(x,y) | 返回X除以Y的余数 |
RAND() | 返回0~1的随机值 |
RAND(x) | 返回0~1的随机值,其中x的值用作种子值,相同的x值会产生相同的随机数 |
ROUND(x) | 返回一个对x的值进行四舍五入后,最接近于x的整数 |
ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近x的值,并保留到小数点后面Y位 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
SQRT() | 返回x的平方根。当x的值为负数时,返回NULL |
举例
SELECT ABS(12),ABS(-12),SIGN(12),SIGN(-12),SIGN(0),PI(),CEIL(12.13),CEILING(-12.56),
FLOOR(12.56),FLOOR(-12.56)
FROM DUAL;
其查询结果如下:
SELECT MOD(12,5),RAND(),RAND(),RAND(11),RAND(11),RAND(22),RAND(22)
FROM DUAL;
其查询结果如下:
SELECT TRUNCATE(12.8768126,2),SQRT(4),SQRT(-4)
FROM DUAL;
其查询结果如下
2.2角度与弧度函数
函数 | 用法 |
---|---|
RADIANS(x) | 将角度转化为弧度,其中,参数x为角度值 |
DEGREES(x) | 将弧度转化为角度,其中,参数x为弧度制 |
SELECT RADIANS(30),RADIANS(45),RADIANS(60),DEGREES(20 * PI())
FROM DUAL;
其查询结果如下:
2.3三角函数
函数 | 用法 |
---|---|
SIN(x) | 返回x的正弦值,其中,参数x为弧度值 |
ASIN(x) | 返回x的反正弦值,即获取正弦为x的值。如果x的值不在-1到1之间,则返回NULL |
COS(x) | 返回x的余弦值,其中,参数为x的弧度值 |
ACOS() | 返回x的反余弦值,即获取余弦为x的值。如果x的值不在-1到1之间,则返回NULL |
TAN(x) | 返回x的正切值,其中,参数x为弧度值 |
ATAN(x) | 返回x的反正切值,即返回正切为x的值 |
ATAN2(m,n) | 返回两个参数的反正切值 |
COT(x) | 返回x的余切值,其中,x为弧度值 |
2.4指数对数函数
函数 | 用法 |
---|---|
POW(x,y),POWER(x,y) | 返回x的y次方 |
EXP(x) | 返回e的x次方,其中e是一个常数,2.71828 |
LN(x),LOG(x) | 返回以e为底x的对数,当X<= 0时,返回的结果为NULL |
LOG10(x) | 返回以10为底x的对数,当x<= 0时,返回的结果为NULL |
LOG2(x) | 返回以2为底x的对数,当x<= 0时,返回NULL |
SELECT POW(2,3),POWER(2,4),EXP(1),EXP(2),LN(2),LOG(2),LOG2(4)
FROM DUAL;
其查询结果如下:
2.5进制间的转换
函数 | 用法 |
---|---|
BIN(x) | 返回x的二进制编码 |
HEX(x) | 返回x的十六进制编码 |
OCT(x) | 返回x的八进制编码 |
CONV(x,f1,f2) | 将x从f1进制转换为f进制数 |
示例:
SELECT BIN(2),HEX(28),OCT(9),CONV(2,10,2)
FROM DUAL;
其查询结果如下:
3.字符串函数
函数 | 用法 |
---|---|
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,...,sn),但是每个字符串之间要加上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次的结果 |
REPACE(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中首次出现的位置。 |
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
|
注意:在MySQL中,字符串的位置是从1开始的。
4.日期和时间函数
4.1获取日期、时间
函数 | 用法 |
---|---|
CURDATE(),CURRENT_DATE() | 返回当前日期,只包含年、月、日 |
CURTIME()、CURRENT_TIME() | 返回当前时间,只包含时、分、秒 |
NOW() | 返回当前系统日期和时间 |
UTC_DATE() | 返回UTC(世界标准时间) 日期 |
TUTC_TIME() | 返回UTC(世界标准时间) 时间 |
示例:
SELECT CURDATE(),CURTIME(),NOW(),UTC_DATE(),UTC_TIME()
FROM DUAL;
其查询结果为:
4.2日期与时间戳的转换
函数 | 用法 |
---|---|
UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。 |
UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回 |
FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
示例
SELECT UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP(CURDATE()),UNIX_TIMESTAMP(CURTIME())
FROM DUAL;
其查询结果如下:
SELECT UNIX_TIMESTAMP('2022-02-25 20:03:04')
FROM DUAL;
其查询结果如下:
4.3获取月份、星期、星期数、天数等函数
函数 | 用法 |
---|---|
YEAR(date)/MONTH(date)/DAY(date) | 返回具体的日期值 |
HOUR(time)/MINUTE(time)/SECOND(time) | 返回具体的时间值 |
MONTHNAME(date) | 返回月份:January,... |
DAYNAME(date) | 返回星期几:MONDAY,TUESDAY......SUNDAY |
WEEKDAY(date) | 返回周几,注意,周一是0,周二是1,...周日是6 |
QUARTER(date) | 返回日期对应的季度,范围1~4 |
WEEK(date),WEEKOFYEAR(date) | 返回一年中的第几周 |
DAYOFYEAR(date) | 返回日期是一年中的第几天 |
DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
DAYOFWEEK(date) | 返回周几,注意:周日是1,周一是2,...周六是7 |
举例:
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURDATE()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
其查询结果如下:
SELECT MONTHNAME('2018-03-04'),DAYNAME('2018-03-04'),WEEKDAY('2018-03-04'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
其查询结果如下:
4.4EXTRACT(type FROM date)
函数 | 用法 |
---|---|
EXTRACT(type FROM date) | 返回日期中特定的部分,type指定返回的值 |
EXTRACT(type FROM date)函数中type的取值与含义:
type取值 | 含义 |
---|---|
MICROSECOND | 返回毫秒数 |
SECOND | 返回秒数 |
MINUTE | 返回分钟数 |
HOUR | 返回小时数 |
DAY | 返回天数 |
WEEK | 返回日期在一年中的第几个星期 |
MONTH | 返回日期在一年中的第几个月 |
QUARTER | 返回日期在一年中的第几个季度 |
YEAR | 返回日期的年份 |
SECOND_MICROSECOND | 返回秒和毫秒值 |
MINUTE_MICROSECOND | 返回分钟和毫秒值 |
MINUTE_SECOND | 返回分钟和秒值 |
HOUR_MICROSECOND | 返回小时和毫秒值 |
HOUR_SECOND | 返回小时和秒值 |
HOUR_MINUTE | 返回小时和分钟值 |
DAY_MICROSECOND | 返回天和毫秒值 |
DAY_SECOND | 返回天和秒值 |
DAY_MINUTE | 返回天和分钟值 |
DAY_HOUR | 返回天和小时 |
示例:
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT(WEEK FROM NOW()),
EXTRACT(QUARTER FROM NOW()),EXTRACT(MINUTE_SECOND FROM NOW())
FROM DUAL;
其查询结果为:
4.5时间和秒数转换的函数
函数 | 用法 |
---|---|
TIEM_TO_SEC(time) | 将time转化为秒并返回结果值。转化的公式为:小时*3600+分钟*60+秒 |
SEC_TO_TIME(seconds) | 将seconds描述转化为包含小时、分钟和秒的时间 |
示例:
SELECT TIME_TO_SEC(NOW())
FROM DUAL;
其查询结果如下:
SELECT SEC_TO_TIME(79571)
FROM DUAL;
4.6计算日期和时间的函数
函数 | 用法 |
---|---|
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时间间隔的日期 |
尚书函数中type的取值:
间隔类型 | 含义 |
---|---|
HOUR | 小时 |
MINUTE | 分钟 |
SECOND | 秒 |
YEAR | 年 |
MONTH | 月 |
DAY | 日 |
YEAR_MONTH | 年和月 |
DAY_HOUR | 日和小时 |
DAY_MINUTE | 日和分钟 |
DAY_SECOND | 日和秒 |
HOUR_MINUTE | 小时和分钟 |
HOUR_SECOND | 小时和秒 |
MINUTE_SECOND | 分钟和秒 |
举例:
SELECT ADDDATE('2022-02-03 12:08:22',INTERVAL 1 SECOND) AS coll,
DATE_ADD('2022-02-03 12:08:22',INTERVAL '1_1' MINUTE_SECOND) AS col2,
DATE_ADD(NOW(),INTERVAL -1 YEAR) AS col3,
DATE_ADD(NOW(),INTERVAL '1_1' YEAR_MONTH) AS col4
FROM DUAL
其查询结果如下:
SELECT DATE_SUB('2022-08-09 12:21:32',INTERVAL 31 DAY) AS col1,
SUBDATE('2022-08-09',INTERVAL 31 DAY) AS col2,
DATE_SUB('2022-08-09',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;
函数 | 用法 |
---|---|
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) | 返回从0000年1月1日起,N天以后的日期 |
TO_DAYS(dae) | 返回日期date距离0000年1月1日的天数 |
LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
MAKEDATE(year,n) | 返回time加上n后的时间 |
4.7日期的格式化与解析
函数参数常用的格式符:
5.流程控制函数
流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
函数 | 用法 |
---|---|
IF(value,value1,value2) | 如果value的值为TRUE,返回value1,否则返回value2 |
IFNULL(value1,value2) | 如果value均为NULL,返回null,否则返回value1或value1 |
CASE WHEN条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ... [ELSE return] END | 相当于Java中的if...else if ... else ... |
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 ... [ELSE值n]END | 相当于Java的switch...case... |
示例1:IF(value,value1,value2)
SELECT IF(1 > 0,'正确','错误'),IF(1 = 0,'正确','错误')
FROM DUAL;
其结果如下:
示例2:IFNULL(value1,value2)
SELECT IFNULL(NULL,NULL),IFNULL(NULL,'hello'),IFNULL('Hello Word',NULL)
FROM DUAL;
其结果如下:
示例3:CASE WHEN条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ... [ELSE return] END
SELECT CASE
WHEN 1 > 0
THEN '1 > 0'
WHEN 2 = 1
THEN '2 = 1'
END
其查询结果如下:
6.加密与解密函数
加密与解密函数主要用于数据库中的数据进行加密和解密处理,以防止被他人窃取。
函数 | 用法 |
---|---|
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 |
可以看到,ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。
举例:
SELECT MD5('yihjh'),MD5(NULL)
FROM DUAL;
其结果如下:
SELECT SHA('yihjh'),SHA(NULL)
FROM DUAL;
其结果如下:
7.MySQL信息函数
MySQL中内置了一些可以查询MySQL信心的函数,这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。
函数 | 用法 |
---|---|
VERSION() | 返回当前MySQL的版本号 |
CONNECTION_ID() | 返回当前MySQL服务器的连接数 |
DATABASE(),SCHEMA() | 返回MySQL命令当前所在的数据库 |
USER(),CURRENT_USER(), SYSTEM_USER(),SESSION_USER() | 返回当前连接MySQL的用户名,返回结果格式为“主机名@用户名” |
CHARSET(value) | 返回字符串value自变量的字符集 |
COLLATION(value) | 返回字符串value的比较规则 |
8.
函数 | 用法 |
---|---|
FORMAT(value,n) | 返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留到小数点后n位 |
CONV(value,from,to) | 将value的值进行不同进制之间的转换 |
INET_ATON(ip value) | 将以点分隔的IP地址转换为一个数字 |
INET_NTOA(value) | 将数字形式的IP地址转换为以点分隔的IP地址 |
BENCHMARK(n,expr) | 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间 |
CONVERT(value USING char_code) | 将value所使用的字符编码修改为char_code |
示例:
SELECT FORMAT(123456.132456,3),FORMAT(123456.123456,0),FORMAT(123456.123456,-2)
FROM DUAL;
#如果值小于或等于0,则只保留整数部分
其结果为:
SELECT CONV(16,10,2),CONV(88,10,16),CONV(NULL,10,2)
FROM DUAL;
其结果为:
SELECT INET_ATON('192.168.1.100')
FROM DUAL;
# "192.168.1.100",它的计算方式为192 * 256的三次方,加上168 * 256的平方,加上1 * 256 + 100
其结果为:
SELECT INET_NTOA(3232235876)
FROM DUAL;
其结果为:
9.练习示例
# 第七章_单行函数课后练习
# 1.显示系统时间(注:日期+时间)
SELECT NOW()
FROM DUAL;
SELECT CURDATE(),CURTIME()
FROM DUAL;
# 2.查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary)
SELECT employee_id,last_name,salary,salary * 1.2 "new salary"
FROM employees;
SELECT employee_id,last_name,salary,commission_pct,
(salary + IF(commission_pct IS NOT NULL,commission_pct,0)) * 1.2 "new salary"
FROM employees;
# 3.将员工的姓名按首字母排序,并写出姓名的长度(length)
SELECT last_name,LENGTH(last_name) "name_length"
FROM employees
ORDER BY last_name ASC;
# 4.查询员工id,last_name,salary,并作为一个列输出,别名为OUT_PUT
SELECT CONCAT(employee_id,last_name,salary) "OUT_PUT"
FROM employees;
# 5.查询公司各员工工作的年数、工作的天数,并按工作年数的降序排序
SELECT employee_id,TIMESTAMPDIFF(YEAR,hire_date,CURDATE()) "year",
TIMESTAMPDIFF(DAY,hire_date,CURDATE()) "day"
FROM employees
ORDER BY `year` DESC;
# 6.查询员工姓名,hire_date , department_id,
#满足以下条件:雇用时间在1997年之后,department_id
#为80 或 90 或110, commission_pct不为空
SELECT last_name,hire_date,department_id
FROM employees
#where hire_date >= '1997-01-01' #存在隐式转换
WHERE DATE_FORMAT(hire_date,'%Y-%m-%d') >= '1997-01-01' #显示转换,日期--->字符串
AND department_id IN(80,90,110)
AND commission_pct IS NOT NULL;
# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,hire_date "day"
FROM employees
WHERE TIMESTAMPDIFF(DAY,hire_date,CURDATE()) > 10000;
# 8.做一个查询,产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0),' monthly but wants ',TRUNCATE(salary * 3,0))
FROM employees;
# 9.使用case-when,按照下面的条件:
/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
*/
SELECT last_name "last_name",job_id "job_id" ,CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END "Grade"
FROM employees;