MySQL学习笔记10

MySQL函数

MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。SELECT语句及其条件表达式都可以使用这些函数。同时，INSERT、UPDATE、DELECT语句及其条件表达式也可以使用这些函数。

1：数学函数

数学函数是M有SQL中常用的一类函数。主要用于处理数字，包括整型、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数、获取随机数的函数等。

2：字符串函数

字符串函数主要用于处理表中的字符串。字符串函数包括求字符串长度、合并字符串、在字符串中插入子串、大小写字母之间切换等函数。

2.1 计算字符串字符数的函数和字符串长度的函数

CHAR_LENGTH(s)函数计算字符串s的字符数；
LENGTH(s)函数计算字符串s的长度。

2.2 合并字符串的函数

CONCAT(s1,s2,、、、)函数和CONCAT_WS(x,s1.s2,、、、)函数都可以将s1、s2等多个字符串合并成一个字符串。但CONCAT_WS(x,s1,s3,、、、)可以将各字符串直接用参数x隔开。

SELECT CONCAT('bei','ji','ng'),CONCAT_WS('-','bei','ji','ng');

运行结果：

CONCAT(‘bei’,’ji’,’ng’)	CONCAT_WS(‘-‘,’bei’,’ji’,’ng’)
beijing	bei-ji-ng

2.3 替换字符串的函数

INSERT(s1,x,len,s2)函数将字符串s1中x位置开始长度为Len的字符串用s2替换。

SELECT s,INSERT(s,4,4,'fang') FROM t2;

运行结果：

s	INSERT(s,4,4,’fang’)
beijing	beifang

2.4 字母大小写转换函数

UPPER(s)函数和UCASE(s)函数将字符串s的所遇字母变成大写字母；LOWER(s)函数和LCASE(s)函数将字符串s的所有字母变成小写字母。

SELECT UPPER('mysql'),UCASE('mysql'),LOWER('MYSQL'),LCASE('MYSQL');

运行结果：

UPPER(‘mysql’)	UCASE(‘mysql’)	LOWER(‘MYSQL’)	LCASE(‘MYSQL’)
MYSQL	MYSQL	mysql	mysql

2.5 获取指定长度的字符串的函数

LEFT(s,n)函数返回字符串s的前n个字符；RIGHT(s,n)函数返回字符串s的后n个字符。

SELECT s, LEFT(s,3),RIGHT(s,4) FROM t2;

运行结果：

s	LEFT(s,3)	RIGHT(s,4)
beijing	bei	jing

2.6 填充字符串的函数

LPAD(s1,len,s2)函数将字符串s2填充到s1的开始处，使字符串长度达到len；RPAD(s1,len,s2)函数将字符串s2填充到s1的结尾处，使字符串达到len.

SELECT s,LPAD(s,10,'+-'),RPAD(s,10,'+-') FROM t2;

运行结果：

s	LPAD(s,10,’+-‘)	RPAD(s,10,’+-‘)
beijing	+-+beijing	beijing+-+

2.7 删除空格函数

LTTIM(s)函数将去点字符串s开始处的空格；
RTRIM(s)函数将去点字符串s结尾处的空格；
TRIM(s)函数将去掉字符串s开始处和结尾处的空格。

SELECT CONCAT('+',' me ','+'),CONCAT('+',LTRIM(' me '),'+');

运行结果：

CONCAT(‘+’,’ me ‘,’+’)	CONCAT(‘+’,LTRIM(’ me ‘),’+’)
me +	+me +

2.8 删除指定字符串的函数

TRIM(s1 FROM s)函数将去掉字符串s中开始处和结尾处的字符串s1。

SELECT TRIM('ab' FROM 'ababddddabddab');

运行结果：

TRIM(‘ab’ FROM ‘ababddddabddab’)
ddddabdd

2.9 重复生成字符串的函数

REPEAT(s,n)函数将字符串s重复n次。

SELECT REPEAT('MYSQL-',5);

运行结果：

REPEAT(‘MYSQL-‘,5)
MYSQL-MYSQL-MYSQL-MYSQL-MYSQL-

2.10 空格函数和替换函数

SPACE(n)函数返回n个空格；REPLACE(s,s1,s2)函数将字符串s2替代字符串s中的字符串s1。

SELECT CONCAT('+',SPACE(4),'+'),REPLACE('mysql','sql','book');

运行结果：

CONCAT(‘+’,SPACE(4),’+’)	REPLACE(‘mysql’,’sql’,’book’)
+	mybook

2.11 比较字符串大小的函数

STRCMP(s1,s2)函数用来比较字符串s1和s2。如果s1大于s2,结果返回1；如果s1等于s2,结果返回0；如果s1小于s2,结果返回-1.

SELECT STRCMP('abc','abb'),STRCMP('abc','abc'),STRCMP('abc','abd');

运行结果：

STRCMP(‘abc’,’abb’)	STRCMP(‘abc’,’abc’)	STRCMP(‘abc’,’abd’)
1	0	-1

2.12 获取子串的函数

SUBSTUING(s,n,len)函数和MID(s,n,len)函数从字符串s的第n个位置开始获取长度为len的字符串。下面将演示SUBSTRING(s,n,len)函数和MID(s,n,len)函数的使用。

SELECT s ,SUBSTRING(s,4,3),MID(s,4,3) FROM t2;

运行结果：

s	SUBSTRING(s,4,3)	MID(s,4,3)
beijing	jin	jin

2.13 匹配字符串开始位置的函数

LOCATE(s1,s)、POSITION(s1 IN s)和INSTR(s,s1)这三个函数从字符串s中获取s1的开始位置。

SELECT s,LOCATE('jin',s),POSITION('jin' IN s),INSTR(s,'jin') FROM t2;

运行结果：

s	LOCATE(‘jin’,s)	POSITION(‘jin’ IN s)	INSTR(s,’jin’)
beijing	4	4	4

2.14 字符串逆序的函数

REVERSE(s)函数将字符串s的顺序反过来。

SELECT s,REVERSE(s) FROM t2;

运行结果：

s	REVERSE(s)
beijing	gnijieb

2.15 返回指定位置的字符串函数

BLT(n,s1,s2,、、、)函数返回第n个字符串。

2.16 返回指定字符串位置的函数

FIFLD(s,s1,s2,、、、)函数返回第一个与字符串s匹配的字符串的位置。

2.17 返回子串位置的函数

FIND_IN_SET(s1,s2)函数返回在字符串s2中与s1匹配的字符串的位置。其中，字符串s2中包含了若干个用逗号隔开的字符串。

SELECT FIND_IN_SET('like','i,like,bei,jing');

运行结果：

FIND_IN_SET(‘like’,’i,like,bei,jing’)
2

2.18 选取字符串的函数

MAKE_SET(x,s1,s2,、、、)函数按x的二进制数从s1,s2,、、、sn中选取字符串。例如12的二进制是1100.这个二进制数从右到左的第三位和第四位是1，所以选取s3和s4。

3：日期和时间函数

日期和时间函数主要用于处理表中的日期和时间数据。日期和时间函数包括获取当前日期的函数、获取当前时间的函数、计算日期的函数、计算时间的函数等。

3.1 获取当前日期的函数和获取当前时间的函数

CURDATE()和CURRENT_DATE()函数获取当前日期；
CURTIME()和CURRENT_TIME()函数获取当前时间。

SELECT CURDATE(),CURTIME(),CURRENT_DATE(),CURRENT_TIME();

运行结果：

CURDATE()	CURTIME()	CURRENT_DATE()	CURRENT_TIME()
2016-04-09	20:21:13	2016-04-09	20:21:13

3.2 获取当前日期和时间的函数

NOW()、CURRENT_TIMESTAMP()、LOCALTIME()和SYSDATE()这四个函数都用来获取当前的日期和时间。

3.3 UNIX时间戳函数

UNIX_TIMESTAMP()函数以UNIX时间戳的形式返回当前时间；
UNIX_TIMESTAMP(d)函数将时间d以UNIX时间戳的形式返回；
FROM_UNIXTIME(d)函数把UNIX时间戳的时间转换为普通格式的时间。
UNIX_TIMESTAMP(d)函数和FROM_UNIXTIME(d)互为反函数。

SELECT NOW(), UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW());

NOW()	UNIX_TIMESTAMP()	UNIX_TIMESTAMP(NOW())
2016-04-09 20:30:05	1460205005	1460205005

3.4 获取月份的函数

MONTH(d)函数返回日期d中的月份值，其取值范围是1~12；MONTHNAME(d)函数返回日期d中的月份的英文名称，其中参数d可以是日期和时间，也可以是日期。

SELECT NOW(),MONTH(NOW()),MONTHNAME(NOW());

运行结果：

NOW()	MONTH(NOW())	MONTHNAME(NOW())
2016-04-09 20:38:05	4	April

3.5 获取星期的函数

DAYNAME(d)函数返回日期d是星期几，显示其英文名；
DAYOFWEEK(d)函数也返回日期d是星期几，1表示星期日，2表示星期一，
WEEKDAY(d)函数也返回日期d是星期几，0表示星期一，1表示星期二，

SELECT NOW(),DAYNAME(NOW()),DAYOFWEEK(NOW()),WEEKDAY(NOW());

运行结果：

NOW()	DAYNAME(NOW())	DAYOFWEEK(NOW())	WEEKDAY(NOW())
2016-04-09 20:42:55	Saturday	7	5

3.6 获取星期数的函数

WEEK(d)函数和WEEKOFYEAR(d)函数都是计算日期d是本年的第几个星期。

SELECT NOW(),WEEK(NOW()),WEEKOFYEAR(NOW());

运行结果：

NOW()	WEEK(NOW())	WEEKOFYEAR(NOW())
2016-04-09 20:46:15	14	14

3.7 获取天数的函数

DAYOFYEAR(d)函数日期d是本年的第几天；
DAYOFMONTH(d)函数返回计算日期d是本月的第几天。

SELECT NOW(),DAYOFYEAR(NOW()),DAYOFMONTH(NOW());

运行结果：

NOW()	DAYOFYEAR(NOW())	DAYOFMONTH(NOW())
2016-04-09 20:49:01	100	9

3.8 获取年份、季度、小时、分钟、秒钟的函数

YEAR(d)函数返回日期d中的年分值；
QUARTER(d)函数返回季度值；
HOUR(t)函数返回时间t的小时值；
MINUTE(t)函数返回时间t中的分钟值；
SECOND(t)函数返回时间t中的秒钟值。

SELECT NOW(),YEAR(NOW()),QUARTER(NOW());

运行结果：

NOW()	YEAR(NOW())	QUARTER(NOW())
2016-04-09 20:55:24	2016	2

SELECT CURTIME(),HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME());

运行结果：

CURTIME()	HOUR(CURTIME())	MINUTE(CURTIME())	SECOND(CURTIME())
20:57:08	20	57	8

3.9 获取日期的指定值的函数

EXTRACT(type FROM d)函数从日期d中获取指定的值。这个值是什么有type决定。Type可以取YEAR、MONTH、DAY、HOUR、MINUTE、SECOND如果type的值是YEAR，结果返回年分值。

SELECT NOW(),EXTRACT(MONTH FROM NOW());

运行结果：

NOW()	EXTRACT(MONTH FROM NOW())
2016-04-09 21:03:06	4

3.10 时间和秒钟转换的函数

TIME_TO_SEC(t)函数将时间t转换为以秒为单位的时间；
SEC_TO_TIME(s)函数将以秒为单位的时间s转换为时分秒的格式。

SELECT CURTIME(),TIME_TO_SEC(CURTIME()),SEC_TO_TIME(76084);

运行结果：

CURTIME()	TIME_TO_SEC(CURTIME())	SEC_TO_TIME(76084)
21:08:30	76110	21:08:04

3.11 计算日期和时间的函数

1、TO_DAYS(d)、FROM_DAYS(n)和DATEDIFF(d1,d2)函数
2、ADDDATE(d,n)在日期d增加n天、SUBDATE(d,n) 在日期d减去n天、ADDTIME(t,n)在时间t上增加n秒 和SUBTIME(t,n) 在时间t上减少n秒函数
3、ADDDATE(d,INTERVAL expr type)和DATE_ADD(d,INTERVAL expr type)函数

1：

SELECT 
CURDATE(),TO_DAYS(CURDATE()),FROM_DAYS(76084),
DATEDIFF(CURDATE(),'2016-04-12');

运行结果：
| CURDATE() | TO_DAYS(CURDATE()) | FROM_DAYS(76084) | DATEDIFF(CURDATE(),’2016-
04-12’) |

2016-04-09	736428	0208-04-24
-3

3.12 将日期和时间格式化的函数

DATE_FORMAT(d,f)函数
TIME_FORMATE(t,f)函数
GET_FORMAT(type,s)函数

SELECT CURDATE(),DATE_FORMAT(CURDATE(),'%b %D %Y');

运行结果：

CURDATE()	DATE_FORMAT(CURDATE(),’%b %D %Y’)
2016-04-09	Apr 9th 2016

4：条件判断函数

条件判断函数用来在SQL语句中进行条件判断。根据是否满足判断条件，SQL语句执行不同的分支。例如，从员工表中查询员工的业绩。如果业绩高于指定值n,则输出“good”。否则，输出“bad”。

4.1 IF(expr,V1,V2)函数

IF(expr,V1,V2)函数中，如果表达式expr成立，返回结果V1；否则，返回结果V2。

SELECT num,score,IF(score>=90, 'PASS', 'FAIL') FROM grade LIMIT 4;

运行结果：

num	score	IF(score>=90, ‘PASS’, ‘FAIL’)
1001	80	FAIL
1001	90	PASS
1001	85	FAIL
1001	95	PASS

4.2 IFNULL(V1,V2)函数

IFNULL(V1,V2)函数中，如果V1的不为空，就显示V1的值；否则就显示V2的值。

4.3 CASE函数

1、CASE WHEN expr1 THEN v1 [WHEN expr2 THEN v2、、、] [ELSE vn] END
2、CASE expr WHEN e1 THEN v1 [WHEN e2 THEN v2、、、] [ELSE vn] END

SELECT id,grade,
CASE WHEN grade>60 THEN 'GOOD' WHEN grade=60 THEN 'PASS' ELSE 'FAIL' END
LEVEL FROM t6;

5:系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。例如，查询数据库的版本，查询数据库的当前用户。

5.1 获取MySQL版本号、连接数、数据库名的函数

VERSION()函数返回数据库的版本号；
CONNECTION_ID()函数返回服务器的连接数，也就是到现在为止MySQL服务的连接次数；
DATABASE()和SCHEMA()返回当前数据库名

5.2 获取用户名的函数

USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()和CURRENT_USER这几个函数可以返回当前用户的名称。

5.3 获取字符串的字符集和排序方式的函数

CHARSET(str)函数返回字符串str的字符集，一般情况这个字符集就是系统的默认字符集；COLLATION(str)函数返回字符串str的字符排列方式。

5.4 获取最后一个自动生成的ID值的函数

LAST_INSERT_ID()函数返回最后生成的AUTO_INCREMENT值。

6：加密函数

加密函数是MySQL中用来对数据进行加密的函数。因为数据库中有些很敏感的信息不希望被其他人看到，就应该通过加密方式来使这些数据变成看似乱码的数据。

6.1 加密函数PASSWORD(str)

PASSWORD(str)函数可以对字符串str进行加密，一般情况下，PASSWORD(str)函数主要是用来给用户的密码加密的。

SELECT PASSWORD('abcd');

运行结果：

PASSWORD(‘abcd’)
*A154C52565E9E7F94BFC08A1FE702624ED8EFFDA

6.2 加密函数MD5(str)

MD5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。

SELECT MD5('abcd');

运行结果：

MD5(‘abcd’)
e2fc714c4727ee9395f324cd2e7f331f

6.3 加密函数ENCODE(str,pswd_str)

ENCODE(str,pswd_str)函数可以使用字符串pswd_str来加密字符串str。加密的结果是一个二进制数，必须使用BLOB类型的字段来保存它。

6.4解密函数DECODE(crypt_str,pswd_str)

DECODE(crypt_str,pawd_str)函数可以使用字符串pswd_str来为crypt_str解密。Crypt_str是通过ENCODE(str，pswd_str)加密后的二进制数据。字符串pswd_str应该与加密时的字符串pswd_str是相同的。

SELECT DECODE(ENCODE('abcd','aa'),'aa');

运行结果：

DECODE(ENCODE(‘abcd’,’aa’),’aa’)
abcd

7：其他函数

MySQL中除了上诉函数以外，还包含了很多函数。例如FORMAT(x,n)函数用来格式化数字x，INET_ATON()函数可以将IP转换为数字。

7.1 格式化函数

FORMAT(x,n)函数可以将数字x进行格式化，将x保留到小数点后n位。

7.2 不同进制的数字进行转换的函数

ASCII(s)返回字符串s的第一个字符的ASCII码；BIN(x)返回x的二进制编码；HEX(x)返回x的十六进制编码；OCT(x)返回x的八进制编码；CONV(x,f1,f2)将x从f1进制数变成f2进制数。

7.3 IP地址与数字相互转换的函数

INET_ATON(IP)函数可以将IP地址转换为数字表示；
INET_NTOA(n)函数可以将数字n转换成IP的形式。
其中INET_ATON(IP)函数中IP值需要加上引号。

7.4 加锁函数和解锁函数

GET_LOCT(name，time)函数定义一个名称为name、持续时间长度为time秒的锁。如果锁定成功，返回1；如果尝试超时，返回0，如果遇到错误，返回NULL.
RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功，返回1；如果尝试超时，返回0；如果解锁失败，返回NULLL；
IS_FREE_LOCK(name)函数判断是否使用名为name的锁。如果使用，返回0；否则，返回1.

7.5 重复执行指定操作的函数

BENCHMARK(count，expr)函数将表达式expr重复执行count次，然后返回执行时间，该函数可以用来判断MySQL处理表达式的速度。