MYSQL学习笔记(五)：单行函数(字符串、数学、日期时间、条件判断、信息、加密、进制转换函数)讲解

前言：

• 学习和使用数据库可以说是程序员必须具备能力，这里将更新关于MYSQL的使用讲解，大概应该会更新30篇+，涵盖入门、进阶、高级(一些原理分析);
• 这一篇是讲解单行函数，当然mysql函数很多哈，只有多用才能记得住；
• 这些函数，如果不用，记得再牢都会忘记(我是这样的)，但是可以先看一下，动手打一下，会现用现查即可，而且现在AI这么发达不是么；
• 虽然MYSQL命令很多，但是自己去多敲一点，到后面忘记了，查一下就可以回忆起来使用了；
• 这一系列也是本人学习MYSQL做的笔记，也是为了方便后面忘记查询；
• 参考资料：尚硅谷、黑马、csdn和知乎博客；
• 欢迎收藏 + 关注，本人将会持续更新。

课程使用数据文件: 后台私信获取。

单行函数

MySQL 函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。

MySQL 包含了大量并且丰富的函数，这里只讲解一部分，剩下的比较罕见的函数我们可以到「MySQL 参考手册」查询。

字符串函数

函数	作用
UPPER(列|字符串)	将字符串每个字符转为大写
LOWER(列|字符串)	将字符串每个字符转为小写
CONCAT(str1,str2,…)	将所有字符串连接成一个字符串
REPLACE(列|字符串,新字符串)	使用新字符串替换旧字符串
LENGTH(列|字符串)	求字符串长度
SUBSTR(列|字符串,开始点[,长度])	字符串截取(字符串，开始点)
LEFT(str,len)	获取字符串左边len个字符组成的字符串
RIGHT(str,len)	获取字符串右边len个字符组成的字符串
MID(str,pos,len)	获取字符串中从pos(第几个)位置开始，长度为len的字符串
ASCII(字符)	返回与指定字符对应的十进制整数
CHR(数字)	返回与整数对应的字符
RPAD(列|字符串,长度,填充字符) LPAD(列|字符串,长度,填充字符)	用指定的字符在字符串右或左填充
LTRIM(字符串)、RTRIM(字符串)	去掉字符串左或右的空格
TRIM(列|字符串)	去掉字符串左右空格
INSTR(列|字符串,要查找的字符串,开始位置,出现位置)	查找一个子字符串是否在指定的位置上出现

1. 把员工姓名首字母大写，其他字母小写

mysql> select concat(upper(left(ename, 1)), lower(substr(ename, 2))) from emp;
+--------------------------------------------------------+
| concat(upper(left(ename, 1)), lower(substr(ename, 2))) |
+--------------------------------------------------------+
| Yxz                                                    |
| Smith                                                  |
| Allen                                                  |
| Ward                                                   |
| Jones                                                  |
| Martin                                                 |
| Blake                                                  |
| Clark                                                  |
| Scott                                                  |
| King                                                   |
| Turner                                                 |
| Adams                                                  |
| James                                                  |
| Ford                                                   |
| Miller                                                 |
+--------------------------------------------------------+
15 rows in set (0.00 sec)

2. 查询出姓名长度是5的所有员工信息

mysql> select * from emp e where length(e.ename)=5;
+-------+-------+----------+------+------------+------+------+--------+
| empno | ename | job      | mgr  | hiredate   | sal  | comm | deptno |
+-------+-------+----------+------+------------+------+------+--------+
|  7369 | SMITH | CLERK    | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7566 | JONES | MANAGER  | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7698 | BLAKE | MANAGER  | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK | MANAGER  | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT | ANALYST  | 7566 | 1987-04-19 | 3000 | NULL |     20 |
|  7876 | ADAMS | CLERK    | 7788 | 1987-05-23 | 1100 | NULL |     20 |
|  7900 | JAMES | CLERK    | 7698 | 1981-12-03 |  950 | NULL |     30 |
+-------+-------+----------+------+------------+------+------+--------+
8 rows in set (0.00 sec)

3. 查询出员工姓名前三个字母是‘JAM’的员工

mysql> select * from emp e where substr(e.ename, 1, 3)='JAM';
+-------+-------+-------+------+------------+------+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal  | comm | deptno |
+-------+-------+-------+------+------------+------+------+--------+
|  7900 | JAMES | CLERK | 7698 | 1981-12-03 |  950 | NULL |     30 |
+-------+-------+-------+------+------------+------+------+--------+
1 row in set (0.00 sec)

4. 查询所有员工姓名的前三个字母

mysql> select substr(ename, 3) from emp;
+------------------+
| substr(ename, 3) |
+------------------+
| z                |
| ITH              |
| LEN              |
| RD               |
| NES              |
| RTIN             |
| AKE              |
| ARK              |
| OTT              |
| NG               |
| RNER             |
| AMS              |
| MES              |
| RD               |
| LLER             |
+------------------+
15 rows in set (0.00 sec)

数学函数

函数	作用
ROUND(数字[,保留位数])	根据指定的保留位数对小数进行四舍五入，如果不指定保留位数，则小数点之后的数字全部进行全部四舍五入
TRUNCATE(数字[,截取位数])	保留指定位数的小数，如果不指定，则表示不保留
CEIL(数字)，CEILING(数字)	对小数向上取整
FLOOR(数字)	对小数向下取整
RAND([n])	随机返回0到1.0之间的小数，如果指定n每次产生的就都是重复的

通过数据库的函数，生成一个六位数字的随机验证码。

SELECT LPAD(TRUNCATE(RAND()*1000000,0),6,'0');# 随机生产，如果少于6位，则用0填充
mysql> select lpad(truncate(rand()*1000000, 0), 6, '0');
+-------------------------------------------+
| lpad(truncate(rand()*1000000, 0), 6, '0') |
+-------------------------------------------+
| 870583                                    |
+-------------------------------------------+
1 row in set (0.00 sec)

日期和时间函数

函数	作用
CURDATE()，CURRENT_DATE()	返回当前日期
CURTIME()，CURRENT_TIME()	返回当前时间
NOW()，CURRENT_TIMESTAMP()，LOCALTIME()， SYSDATE()，LOCALTIMESTAMP()	返回会当前日期和事件
YEAR(日期)	返回日期中的年份
MONTH(日期)	返回日期中的月份[1,12]
DAY(日期)	返回日期中的天[1,31]
HOUR(时间)	返回时间中的小时
MINUTE(时间)	返回实践中的分钟
SECOND(时间)	返回时间中的秒
DAYOFWEEK(日期)	返回星期几，1星期日，2星期一
WEEKDAY(日期)	返回星期几，0星期一，1星期二
DAYOFYEAR(日期)	计算指定日期是本年第几天
DAYOFMONTH(日期)	计算指定日期是本月第几天
QUARTER(日期)	计算指定日期是第几季度
TIME_TO_SEC(time)	将指定时间转换为秒
SEC_TO_TIME(sec)	将以秒为单位的时间转换为时分秒的格式
DATEDIFF(d1,d2)	计算两个日期相隔的天数
ADDDATE(date,n)	计算指定日期加上n天后的日期
SUBDATE(date,n)	计算指定日期减去n天后的日期
ADDTIME(time,n)	计算指定时间加上n秒后的时间
SUBTIME(time,n)	计算指定时间减去n秒后的时间
DATE_FORMAT(date,f)	按格式返回日期
TIME_FORMAT(time,f)	按格式返回时间

条件判断函数

函数	作用
IF(expr,v1,v2)	如果表达式expr成立，返回结果v1；否则，返回结果v2。
IFNULL(v1,v2)	如果v1的值不为NULL，则返回v1，否则返回v2
CASE	分支(详见下面)

• IF(expr,v1,v2)

SELECT IF(1>0,'true','false');
->true

• IFNULL(v1,v2)

SELECT IFNULL(NULL,'wy');
->wy

• CASE

  语法1：

CASEWHEN expr1 THEN V1WHEN expr2 THEN V2...ELSE vn
END	

SELECT CASE WHEN 1 > 0THEN '1 > 0'WHEN 2 > 0THEN '2 > 0'ELSE '3 > 0'END
->1 > 0

语法2：

CASE expr WHEN e1 THEN v1WHEN e1 THEN v1...ELSE vn
END

SELECT CASE 1 WHEN 1 THEN '我是1'WHEN 2 THEN '我是2'
ELSE '你是谁'

系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。

函数	作用
VERSION()	返回数据库的版本号
CONNECTION_ID()	返回服务器的连接数
DATABASE()、SCHEMA	返回当前数据库名
USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()、CURRENT_USER	返回当前用户
CHARSET(str)	返回字符串str的字符集
COLLATION(str)	返回字符串str的字符排列方式

加密函数

加密函数是MySQL用来对数据进行加密的函数.

函数	作用
MD5(str)	对字符串str进行散列，可以用于一些普通的不需要解密的数据加密，如密码
sha1	对字符串str进行散列，可以用于一些普通的不需要解密的数据加密，如密码，比MD5更安全

进制转换

函数	作用
BIN(x)	返回x的二进制
HEX(x)	返回x的十六进制
OCT(x)	返回x的八进制
CONV(x,from_base,to_base)	将from_base进制的x，转成to_base进制

SELECT BIN(10),HEX(10),OCT(10),CONV(16,16,10);
->1010 A 12 22

2.IP地址转换

函数	作用
INET_ATON(IP)	将点分十进制的IP地址转为数字
INET_NTOA(number)	将数字形式的IP转为点分十进制

SELECT INET_ATON('192.168.0.1')->3232235521
SELECT INET_NTOA(3232235521)->192.168.0.1