当前位置: 代码迷 >> MySQL >> MySQL学习笔记十(MySQL函数)
  详细解决方案

MySQL学习笔记十(MySQL函数)

热度:389   发布时间:2016-05-05 16:22:02.0
MySQL学习笔记10(MySQL函数)

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' ENDLEVEL 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处理表达式的速度。

  相关解决方案