目录
一、单行函数
1、数值函数
1.1 基本函数
RNAD(数字)是伪随机数,只要里面数字一样,那么随机数就是一样的
四舍五入,截断操作:
SELECT ROUND(123.556),SELECT ROUND(123.556,0)//两者一样,保留到整数位,前者默认舍弃小数位。结果四舍五入。
SELECT ROUND(123.556,1), SELECT ROUND(123.556,-1) //前者保留一位小数,后者保留到十位
SELECT TRUNCATE(123.456,0)//截断操作,直接舍去,不进行四舍五入,并且必须有两个参数
1.2 角度和弧度互换函数
1.3 三角函数
2、字符串函数
1、 LENGTH表示字节数,UTF-8中每个汉字占3个字节,LENGTH('我们')输出6,
CHAR_LENGTH是字符数,一个汉字一个字符,CHAR_LENGTH("我们")输出2
2、CONCAT可以连接字符
3、INSERT索引是从1开始的(是替换的意思)
结果为haaaaaoworld
.
3、日期和时间函数
3.1 获取日期、时间
SELECT CURDATE(),CURRENT_TIME,NOW() FROM DUAL;
mysql> SELECT CURDATE(),CURRENT_TIME,NOW() FROM DUAL;
+------------+--------------+---------------------+
| CURDATE() | CURRENT_TIME | NOW() |
+------------+--------------+---------------------+
| 2022-05-21 | 13:13:37 | 2022-05-21 13:13:37 |
+------------+--------------+---------------------+
1 row in set (0.01 sec)
SELECT CURDATE()+0,CURTIME()+0,NOW()+0 FROM DUAL; //会把时间转换为数值
mysql> SELECT CURDATE()+0,CURTIME()+0,NOW()+0 FROM DUAL;
+-------------+-------------+----------------+
| CURDATE()+0 | CURTIME()+0 | NOW()+0 |
+-------------+-------------+----------------+
| 20220521 | 142702 | 20220521142702 |
+-------------+-------------+----------------+
3.2 日期和时间戳的转换
日期和毫秒数的转换
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2022-4-20 21:21:21') FROM DUAL;
mysql> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2022-4-20 21:21:21') FROM DUAL;
+------------------+--------------------------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP('2022-4-20 21:21:21') |
+------------------+--------------------------------------+
| 1653110196 | 1650460881 |
+------------------+--------------------------------------+
1 row in set (0.00 sec)
3.3 获取月份、星期、星期数、天数等函数
3.4 日期的操作函数
SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT(HOUR_MINUTE FROM NOW()) FROM DUAL;
mysql> SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT(HOUR_MINUTE FROM NOW()) FROM DUAL;
+----------------------------+-------------------------+---------------------------------+
| EXTRACT(SECOND FROM NOW()) | EXTRACT(DAY FROM NOW()) | EXTRACT(HOUR_MINUTE FROM NOW()) |
+----------------------------+-------------------------+---------------------------------+
| 49 | 21 | 1334 |
+----------------------------+-------------------------+---------------------------------+
3.5 时间和秒钟转换的函数
SELECT TIME_TO_SEC(CURTIME()) FROM DUAL;
mysql> SELECT TIME_TO_SEC(CURTIME()) FROM DUAL;
+------------------------+
| TIME_TO_SEC(CURTIME()) |
+------------------------+
| 49123 |
+------------------------+
1 row in set (0.01 sec)
3.6 计算日期和时间的函数
第一组:
SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),DATE_ADD(NOW(),INTERVAL -1 YEAR) FROM DUAL; //ADD -1和SUB 1 是一样的
mysql> SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),DATE_ADD(NOW(),INTERVAL -1 YEAR) FROM DUAL;
+---------------------+---------------------------------+----------------------------------+
| NOW() | DATE_ADD(NOW(),INTERVAL 1 YEAR) | DATE_ADD(NOW(),INTERVAL -1 YEAR) |
+---------------------+---------------------------------+----------------------------------+
| 2022-05-21 13:56:41 | 2023-05-21 13:56:41 | 2021-05-21 13:56:41 |
+---------------------+---------------------------------+----------------------------------+
1 row in set (0.01 sec)
'1_1'就是在后面的两个属性分别都加1。
第二组:
3.7 日期的格式化和解析(重点)
此时,我们谈的是日期的显式格式化和解析;
之前我们学过隐式的格式化或解析(自动的)
SELECT * FROM employees WHERE hiredate='2016-03-03'; //格式必须为这样的
mysql> SELECT * FROM employees WHERE hiredate='2016-03-03';
+-------------+------------+-----------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+------------+-----------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
| 200 | Jennifer | Whalen | JWHALEN | 515.123.4444 | AD_ASST | 4400.00 | NULL | 101 | 10 | 2016-03-03 00:00:00 |
| 201 | Michael | Hartstein | MHARTSTE | 515.123.5555 | MK_MAN | 13000.00 | NULL | 100 | 20 | 2016-03-03 00:00:00 |
| 202 | Pat | Fay | PFAY | 603.123.6666 | MK_REP | 6000.00 | NULL | 201 | 20 | 2016-03-03 00:00:00 |
| 203 | Susan | Mavris | SMAVRIS | 515.123.7777 | HR_REP | 6500.00 | NULL | 101 | 40 | 2016-03-03 00:00:00 |
| 204 | Hermann | Baer | HBAER | 515.123.8888 | PR_REP | 10000.00 | NULL | 101 | 70 | 2016-03-03 00:00:00 |
| 205 | Shelley | Higgins | SHIGGINS | 515.123.8080 | AC_MGR | 12000.00 | NULL | 101 | 110 | 2016-03-03 00:00:00 |
| 206 | William | Gietz | WGIETZ | 515.123.8181 | AC_ACCOUNT | 8300.00 | NULL | 205 | 110 | 2016-03-03 00:00:00 |
+-------------+------------+-----------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
7 rows in set (0.00 sec)
格式化:
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%H:%i:%S') FROM DUAL;
mysql> SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%H:%i:%S') FROM DUAL;
+-----------------------------------+-------------------------------+-----------------------------------+
| DATE_FORMAT(CURDATE(),'%Y-%M-%D') | DATE_FORMAT(NOW(),'%Y-%m-%d') | TIME_FORMAT(CURTIME(),'%H:%i:%S') |
+-----------------------------------+-------------------------------+-----------------------------------+
| 2022-May-21st | 2022-05-21 | 16:14:42 |
+-----------------------------------+-------------------------------+-----------------------------------+
1 row in set (0.00 sec)
解析:格式化逆过程:
SELECT STR_TO_DATE('2022-05-21','%Y-%m-%d') FROM DUAL; //
mysql> SELECT STR_TO_DATE('2022-05-21','%Y-%m-%d') FROM DUAL;
+--------------------------------------+
| STR_TO_DATE('2022-05-21','%Y-%m-%d') |
+--------------------------------------+
| 2022-05-21 |
+--------------------------------------+
1 row in set (0.01 sec)
SELECT GET_FORMAT(DATE,'USA') FROM DUAL; //得到其他国家使用的格式,可以便捷地得到上述函数的参数
mysql> SELECT GET_FORMAT(DATE,'USA') FROM DUAL;
+------------------------+
| GET_FORMAT(DATE,'USA') |
+------------------------+
| %m.%d.%Y |
+------------------------+
1 row in set (0.00 sec)
4、流程控制函数
5、加密和解密函数
加密:
PASSWORD(),ENCODE()和DECODE()在mysql8.0被弃用了
SELECT MD5('mysql'),SHA('mysql') FROM DUAL; //不可逆
mysql> SELECT MD5('mysql'),SHA('mysql') FROM DUAL;
+----------------------------------+------------------------------------------+
| MD5('mysql') | SHA('mysql') |
+----------------------------------+------------------------------------------+
| 81c3b080dad537de7e10e0987a4bf52e | f460c882a18c1304d88854e902e11b85d71e7e1b |
+----------------------------------+------------------------------------------+
1 row in set (0.01 sec)
6、MySQL信息函数
SELECT VERSION() FROM DUAL; //本机版本号是5.7
mysql> SELECT VERSION() FROM DUAL;
+------------+
| VERSION() |
+------------+
| 5.7.36-log |
+------------+
1 row in set (0.01 sec)