常见函数
概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:
1、隐藏了实现细节
2、提高代码的重用性
调用:select 函数名(实参列表) 【from 表】;
特点:
①叫什么(函数名)
②干什么(函数功能)
分类:
1、单行函数(处理一行返回一行)
如 concat、length、ifnull等
2、分组函数(处理多行返回一行)
功能:做统计使用,又称为统计函数、聚合函数、组函数。
1.单行函数
1.字符串函数
函数 | 描述 |
---|---|
CONCAT(S1,S2,…,Sn) | 连接S1,S2,…,Sn为一个字符串。 |
CHAR_LENGTH(s) | 返回字符串s的字符数。 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关。 |
INSERT(str, index , len, instr) | 将字符串str从第index位置开始,len个字符长的子串替换为字符串instr。 |
INSTR(str,substr) | 获取字符第一次出现的索引。 |
UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母。 |
LOWER(s) 或 LCASE(s) | 将字符串s的所有字母转成小写字母。 |
LEFT(s,n) | 返回字符串s最左边的n个字符。 |
RIGHT(s,n) | 返回字符串s最右边的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(【BOTH 】s1 FROM s) | 去掉字符串s开始与结尾的s1。 |
TRIM(【LEADING】s1 FROM s) | 去掉字符串s开始处的s1。 |
TRIM(【TRAILING】s1 FROM s) | 去掉字符串s结尾处的s1。 |
REPEAT(str, n) | 返回str重复n次的结果。 |
REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a。 |
STRCMP(s1,s2) | 比较字符串s1,s2大小。 |
SUBSTRING(s,index,len) | 返回从字符串s的index位置其len个字符。 |
1.CONCAT
# CONCAT(S1,S2,......,Sn):连接S1,S2,......,Sn为一个字符串。
mysql> SELECT CONCAT(last_name,'_',first_name) 姓名,last_name,first_name FROM employees;
+-------------------+-------------+-------------+
| 姓名 | last_name | first_name |
+-------------------+-------------+-------------+
| K_ing_Steven | K_ing | Steven |
| Kochhar_Neena | Kochhar | Neena |
| De Haan_Lex | De Haan | Lex |
| Hunold_Alexander | Hunold | Alexander |
| Ernst_Bruce | Ernst | Bruce |
| Austin_David | Austin | David |
| Pataballa_Valli | Pataballa | Valli |
| Lorentz_Diana | Lorentz | Diana |
| Greenberg_Nancy | Greenberg | Nancy |
| Faviet_Daniel | Faviet | Daniel |
2.CHAR_LENGTH
# CHAR_LENGTH(s):返回字符串s的字符数。
mysql> SELECT CHAR_LENGTH(last_name) '姓名字符长度',last_name FROM employees;
+--------------+-------------+
| 姓名字符长度 | last_name |
+--------------+-------------+
| 5 | K_ing |
| 7 | Kochhar |
| 7 | De Haan |
| 6 | Hunold |
| 5 | Ernst |
| 6 | Austin |
| 9 | Pataballa |
| 7 | Lorentz |
| 9 | Greenberg |
| 6 | Faviet |
3.LENGTH
# LENGTH(s):返回字符串s的字节数,和字符集有关。
mysql> SELECT LENGTH(last_name) '姓名字节长度',last_name FROM employees;
+--------------+-------------+
| 姓名字节长度 | last_name |
+--------------+-------------+
| 5 | K_ing |
| 7 | Kochhar |
| 7 | De Haan |
| 6 | Hunold |
| 5 | Ernst |
| 6 | Austin |
| 9 | Pataballa |
| 7 | Lorentz |
| 9 | Greenberg |
| 6 | Faviet |
4.INSERT
# INSERT(str, index , len, instr):将字符串str从第index位置开始,len个字符长的子串替换为字符串instr。
# 注意:索引从1开始。
mysql> SELECT INSERT('123456789',1,3,'hello');
+---------------------------------+
| INSERT('123456789',1,3,'hello') |
+---------------------------------+
| hello456789 |
+---------------------------------+
1 row in set (0.03 sec)
5.INSTR
# INSTR(str,substr):获取字符第一次出现的索引,这个函数可以判断一个大串中是否包含一个小串。
mysql> SELECT INSERT('123456789',1,3,'hello');
+---------------------------------+
| INSERT('123456789',1,3,'hello') |
+---------------------------------+
| hello456789 |
+---------------------------------+
1 row in set (0.03 sec)
# 判断结果0代表false,1代表true。
mysql> SELECT INSTR('aabbccddeeff','aa') < 0;
+------------------------------+
| INSTR('aabbccddeeff','aa')<0 |
+------------------------------+
| 0 |
+------------------------------+
1 row in set (0.03 sec)
6.UPPER 或 UCASE
# UPPER(s) 或 UCASE(s):将字符串s的所有字母转成大写字母。
mysql> SELECT UPPER('hello,world');
+----------------------+
| UPPER('hello,world') |
+----------------------+
| HELLO,WORLD |
+----------------------+
1 row in set (0.06 sec)
mysql> SELECT UCASE('hello,world');
+----------------------+
| UCASE('hello,world') |
+----------------------+
| HELLO,WORLD |
+----------------------+
1 row in set (0.01 sec)
7.LOWER 或 LCASE
# LOWER(s) 或 LCASE(s):将字符串s的所有字母转成小写字母。
mysql> SELECT LOWER('HELLO,WORLD');
+----------------------+
| LOWER('HELLO,WORLD') |
+----------------------+
| hello,world |
+----------------------+
1 row in set (0.02 sec)
mysql> SELECT LCASE('HELLO,WORLD');
+----------------------+
| LCASE('HELLO,WORLD') |
+----------------------+
| hello,world |
+----------------------+
1 row in set (0.02 sec)
8.LEFT
# LEFT(s,n):返回字符串s最左边的n个字符。
mysql> SELECT LEFT('黑帮教父-格雷福斯',4);
+-----------------------------+
| LEFT('黑帮教父-格雷福斯',4) |
+-----------------------------+
| 黑帮教父 |
+-----------------------------+
1 row in set (0.06 sec)
9.RIGHT
# RIGHT(s,n):返回字符串s最右边的n个字符。
mysql> SELECT RIGHT('黑帮教父-格雷福斯',4);
+------------------------------+
| RIGHT('黑帮教父-格雷福斯',4) |
+------------------------------+
| 格雷福斯 |
+------------------------------+
1 row in set (0.02 sec)
10.LPAD
# LPAD(str, len, pad):用字符串pad对str最左边进行填充,直到str的长度为len个字符。
mysql> SELECT LPAD('格雷福斯',10,'a');
+-------------------------+
| LPAD('格雷福斯',10,'a') |
+-------------------------+
| aaaaaa格雷福斯 |
+-------------------------+
1 row in set (0.02 sec)
11.RPAD
# RPAD(str ,len, pad):用字符串pad对str最右边进行填充,直到str的长度为len个字符。
mysql> SELECT RPAD('格雷福斯',10,'a');
+-------------------------+
| RPAD('格雷福斯',10,'a') |
+-------------------------+
| 格雷福斯aaaaaa |
+-------------------------+
1 row in set (0.03 sec)
12.LTRIM
# LTRIM(s):去掉字符串s左侧的空格。
mysql> SELECT LTRIM(' 格雷福斯 ');
+---------------------------------------+
| LTRIM(' 格雷福斯 ') |
+---------------------------------------+
| 格雷福斯 |
+---------------------------------------+
1 row in set (0.04 sec)
13.RTRIM
# RTRIM(s):去掉字符串s右侧的空格。
mysql> SELECT RTRIM(' 格雷福斯 ');
+---------------------------------------+
| RTRIM(' 格雷福斯 ') |
+---------------------------------------+
| 格雷福斯 |
+---------------------------------------+
1 row in set (0.03 sec)
14.TRIM
# TRIM(s):去掉字符串s开始与结尾的空格。
mysql> SELECT TRIM(' 格雷福斯 ');
+--------------------------------------+
| TRIM(' 格雷福斯 ') |
+--------------------------------------+
| 格雷福斯 |
+--------------------------------------+
1 row in set (0.01 sec)
# LEADING(尾随),TRAILING(前导)或BOTH(前导和尾随)选项明确指示TRIM()函数从字符串中删除前导,尾随或前导和尾随的不必要的字符。
# 如果您没有指定任何内容,TRIM()函数默认使用BOTH选项。
# TRIM(【BOTH 】s1 FROM s):去掉字符串s开始与结尾的s1。
# 如果不指定s1,默认除去空格。
mysql> SELECT TRIM(BOTH FROM ' 格雷福斯 ');
+------------------------------------------------+
| TRIM(BOTH FROM ' 格雷福斯 ') |
+------------------------------------------------+
| 格雷福斯 |
+------------------------------------------------+
1 row in set (0.04 sec)
# 如果指定了s1,就是去除s1指定的值。
mysql> SELECT TRIM(BOTH 'a' FROM 'aaaaaaaaaa格雷aaaaaaaaaa福斯aaaaaaaaaa');
+--------------------------------------------------------------+
| TRIM(BOTH 'a' FROM 'aaaaaaaaaa格雷aaaaaaaaaa福斯aaaaaaaaaa') |
+--------------------------------------------------------------+
| 格雷aaaaaaaaaa福斯 |
+--------------------------------------------------------------+
1 row in set (0.04 sec)
# TRIM(【LEADING】s1 FROM s):去掉字符串s开始处的s1。
# 如果不指定s1,默认除去空格。
mysql> SELECT TRIM(LEADING FROM ' 格雷福斯 ');
+---------------------------------------------------+
| TRIM(LEADING FROM ' 格雷福斯 ') |
+---------------------------------------------------+
| 格雷福斯 |
+---------------------------------------------------+
1 row in set (0.03 sec)
# 如果指定了s1,就是去除s1指定的值。
mysql> SELECT TRIM(LEADING 'a' FROM 'aaaaaaaaaa格雷aaaaaaaaaa福斯aaaaaaaaaa');
+-----------------------------------------------------------------+
| TRIM(LEADING 'a' FROM 'aaaaaaaaaa格雷aaaaaaaaaa福斯aaaaaaaaaa') |
+-----------------------------------------------------------------+
| 格雷aaaaaaaaaa福斯aaaaaaaaaa |
+-----------------------------------------------------------------+
1 row in set (0.03 sec)
# TRIM(【TRAILING】s1 FROM s):去掉字符串s结尾处的s1。
# 如果不指定s1,默认除去空格。
mysql> SELECT TRIM(TRAILING FROM ' 格雷福斯 ');
+----------------------------------------------------+
| TRIM(TRAILING FROM ' 格雷福斯 ') |
+----------------------------------------------------+
| 格雷福斯 |
+----------------------------------------------------+
1 row in set (0.04 sec)
# 如果指定了s1,就是去除s1指定的值。
mysql> SELECT TRIM(TRAILING 'a' FROM 'aaaaaaaaaa格雷aaaaaaaaaa福斯aaaaaaaaaa');
+------------------------------------------------------------------+
| TRIM(TRAILING 'a' FROM 'aaaaaaaaaa格雷aaaaaaaaaa福斯aaaaaaaaaa') |
+------------------------------------------------------------------+
| aaaaaaaaaa格雷aaaaaaaaaa福斯 |
+------------------------------------------------------------------+
1 row in set (0.04 sec)
15.REPEAT
# REPEAT(str, n):返回str重复n次的结果。
mysql> SELECT REPEAT('a',3);
+---------------+
| REPEAT('a',3) |
+---------------+
| aaa |
+---------------+
1 row in set (0.03 sec)
mysql> SELECT REPEAT('aa',2);
+----------------+
| REPEAT('aa',2) |
+----------------+
| aaaa |
+----------------+
1 row in set (0.03 sec)
16.REPLACE
# REPLACE(str, a, b):用字符串b替换字符串str中所有出现的字符串a。
mysql> SELECT REPLACE('AAABBBCCCDDDAAA','AAA','111');
+----------------------------------------+
| REPLACE('AAABBBCCCDDDAAA','AAA','111') |
+----------------------------------------+
| 111BBBCCCDDD111 |
+----------------------------------------+
1 row in set (0.03 sec)
mysql> SELECT REPLACE('AAABBBCCCDDDAAA','AA','111');
+---------------------------------------+
| REPLACE('AAABBBCCCDDDAAA','AA','111') |
+---------------------------------------+
| 111ABBBCCCDDD111A |
+---------------------------------------+
1 row in set (0.03 sec)
17.STRCMP
# STRCMP(s1,s2):比较字符串s1,s2大小。
mysql> SELECT STRCMP('abc','bcd');
+---------------------+
| STRCMP('abc','bcd') |
+---------------------+
| -1 |
+---------------------+
1 row in set (0.02 sec)
mysql> SELECT STRCMP('abc','abc');
+---------------------+
| STRCMP('abc','abc') |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.03 sec)
mysql> SELECT STRCMP('bcd','abc');
+---------------------+
| STRCMP('bcd','abc') |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.03 sec)
18.SUBSTRING
# SUBSTRING(s,index,len):返回从字符串 s 的 index 位置其 len 个字符。
# SUBSTRING()函数也可以简写成SUBSTR()
mysql> SELECT SUBSTRING('123456789',1,3);
mysql> SELECT SUBSTRING('123456789' FROM 1 FOR 3);
+----------------------------+
| SUBSTRING('123456789',1,3) |
+----------------------------+
| 123 |
+----------------------------+
1 row in set (0.00 sec)
# SUBSTRING(s,index):返回从字符串s的index位置到末尾的字符。
mysql> SELECT SUBSTRING('123456789',3);
mysql> SELECT SUBSTRING('123456789' FROM 3);
+--------------------------+
| SUBSTRING('123456789',3) |
+--------------------------+
| 3456789 |
+--------------------------+
1 row in set (0.00 sec)
2.数学函数
函数 | 描述 |
---|---|
ABS(x) | 返回x的绝对值。 |
CEIL(x) | 返回大于x的最小整数值。(向上取整) |
FLOOR(x) | 返回小于x的最大整数值。(向下取整) |
MOD(x,y) | 返回x/y的模。 |
RAND(x) | 返回0~1的随机值。 |
ROUND(x,y) | 返回参数x的四舍五入的有y位的小数的值。 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果。 |
SQRT(x) | 返回x的平方根。 |
POW(x,y) | 返回x的y次方。 |
1.ABS
# ABS(x):返回x的绝对值。
mysql> SELECT ABS(-100);
+-----------+
| ABS(-100) |
+-----------+
| 100 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT ABS(100);
+----------+
| ABS(100) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)
2.CEIL
# CEIL(x):返回大于x的最小整数值。(向上取整)
mysql> SELECT CEIL(100.1);
+-------------+
| CEIL(100.1) |
+-------------+
| 101 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT CEIL(100.01);
+--------------+
| CEIL(100.01) |
+--------------+
| 101 |
+--------------+
1 row in set (0.00 sec)
3.FLOOR
# FLOOR(x):返回大于x的最大整数值。(向下取整)
mysql> SELECT FLOOR(100.1);
+--------------+
| FLOOR(100.1) |
+--------------+
| 100 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT FLOOR(100.01);
+---------------+
| FLOOR(100.01) |
+---------------+
| 100 |
+---------------+
1 row in set (0.00 sec)
4.MOD
# MOD(x,y):返回x/y的模。
mysql> SELECT MOD(10,3);
+-----------+
| MOD(10,3) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT MOD(11,3);
+-----------+
| MOD(11,3) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
5.RAND
# RAND(x):返回0~1的随机值。
mysql> SELECT RAND();
+----------------------+
| RAND() |
+----------------------+
| 0.033479877778775875 |
+----------------------+
1 row in set (0.00 sec)
# 我想要0-50之间的随机数。
mysql> SELECT FLOOR(RAND()*50+1);
+--------------------+
| FLOOR(RAND()*50+1) |
+--------------------+
| 8 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT FLOOR(RAND()*50+1);
+--------------------+
| FLOOR(RAND()*50+1) |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
6.ROUND
# ROUND(x,y):返回参数x的四舍五入的有y位的小数的值。
mysql> SELECT ROUND(10.123,2);
+-----------------+
| ROUND(10.123,2) |
+-----------------+
| 10.12 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT ROUND(10.125,2);
+-----------------+
| ROUND(10.125,2) |
+-----------------+
| 10.13 |
+-----------------+
1 row in set (0.00 sec)
7.TRUNCATE
# TRUNCATE(x,y):返回数字x截断为y位小数的结果。
mysql> SELECT TRUNCATE(10.123,2);
+--------------------+
| TRUNCATE(10.123,2) |
+--------------------+
| 10.12 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(10.1234,3);
+---------------------+
| TRUNCATE(10.1234,3) |
+---------------------+
| 10.123 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(10.1235,3);
+---------------------+
| TRUNCATE(10.1235,3) |
+---------------------+
| 10.123 |
+---------------------+
1 row in set (0.00 sec)
8.SQRT
# SQRT(x):返回x的平方根。
mysql> SELECT SQRT(16);
+----------+
| SQRT(16) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT SQRT(7);
+--------------------+
| SQRT(7) |
+--------------------+
| 2.6457513110645907 |
+--------------------+
1 row in set (0.00 sec)
9.POW
# POW(x,y):返回x的y次方。
mysql> SELECT POW(3,3);
+----------+
| POW(3,3) |
+----------+
| 27 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT POW(2,4);
+----------+
| POW(2,4) |
+----------+
| 16 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT POW(8,3);
+----------+
| POW(8,3) |
+----------+
| 512 |
+----------+
1 row in set (0.00 sec)
3.日期和时间函数
函数 | 描述 |
---|---|
CURDATE() 或 CURRENT_DATE() | 返回当前日期。 |
CURTIME() 或 CURRENT_TIME() | 返回当前时间。 |
NOW() | 返回当前系统日期时间。 |
SYSDATE() | 返回当前系统日期时间。 |
CURRENT_TIMESTAMP() | 返回当前系统日期时间。 |
CURRENT_TIME() | 返回当前系统日期时间。 |
LOCALTIME() | 返回当前系统日期时间。 |
LOCALTIMESTAMP() | 返回当前系统日期时间。 |
YEAR(date) | 返回时间所在的年。 |
MONTH(date) | 返回时间所在的月。 |
DAY(date) | 返回时间所在的天。 |
HOUR(time) | 返回时间所在的小时。 |
MINUTE(time) | 返回时间所在的分钟。 |
SECOND(time) | 返回时间所在的秒。 |
WEEK(date) | 返回一年中的第几周。 |
WEEKOFYEAR(date) | 返回一年中的第几周。 |
DAYOFWEEK() | 返回周几,注意:周日是1,周一是2,。。。周六是7 |
WEEKDAY(date) | 返回周几,注意,周1是0,周2是1,。。。周日是6 |
DAYNAME(date) | 返回星期:MONDAY,TUESDAY…SUNDAY。 |
MONTHNAME(date) | 返回月份:January,。。。。。 |
DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔。 |
TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔。 |
DATE_ADD(datetime, INTERVALE expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间。 |
DATE_FORMAT(datetime ,fmt) | 按照字符串fmt格式化日期datetime值。 |
STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析,解析为一个日期。 |
1.CURDATE
# CURDATE() 或 CURRENT_DATE():返回当前日期。
mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2021-02-22 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2021-02-22 |
+----------------+
1 row in set (0.00 sec)
2.CURTIME
# CURTIME() 或 CURRENT_TIME():返回当前时间。
mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 19:54:59 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 19:55:09 |
+----------------+
1 row in set (0.00 sec)
3.NOW
# NOW():返回当前系统日期时间。
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2021-02-22 19:56:10 |
+---------------------+
1 row in set (0.00 sec)
4.SYSDATE
# SYSDATE():返回当前系统日期时间。
mysql> SELECT SYSDATE();
+---------------------+
| SYSDATE() |
+---------------------+
| 2021-02-22 19:58:34 |
+---------------------+
1 row in set (0.00 sec)
5.CURRENT_TIMESTAMP
# CURRENT_TIMESTAMP():返回当前系统日期时间。
mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2021-02-25 21:50:35 |
+---------------------+
1 row in set (0.04 sec)
6.LOCALTIME
# LOCALTIME():返回当前系统日期时间。
mysql> SELECT LOCALTIME();
+---------------------+
| LOCALTIME() |
+---------------------+
| 2021-02-25 20:48:02 |
+---------------------+
1 row in set (0.01 sec)
7.LOCALTIMESTAMP
# LOCALTIMESTAMP():返回当前系统日期时间。
mysql> SELECT LOCALTIMESTAMP();
+---------------------+
| LOCALTIMESTAMP() |
+---------------------+
| 2021-02-25 20:50:13 |
+---------------------+
1 row in set (0.02 sec)
8.YEAR
# YEAR(date):返回时间所在的年。
mysql> SELECT YEAR(NOW()),NOW();
+-------------+---------------------+
| YEAR(NOW()) | NOW() |
+-------------+---------------------+
| 2021 | 2021-02-25 21:55:12 |
+-------------+---------------------+
1 row in set (0.04 sec)
mysql> SELECT (YEAR('1998-01-01'));
+----------------------+
| (YEAR('1998-01-01')) |
+----------------------+
| 1998 |
+----------------------+
1 row in set (0.03 sec)
mysql> SELECT YEAR(hiredate),hiredate FROM employees;
+----------------+---------------------+
| YEAR(hiredate) | hiredate |
+----------------+---------------------+
| 1992 | 1992-04-03 00:00:00 |
| 1992 | 1992-04-03 00:00:00 |
| 1992 | 1992-04-03 00:00:00 |
| 1992 | 1992-04-03 00:00:00 |
| 1992 | 1992-04-03 00:00:00 |
| 1998 | 1998-03-03 00:00:00 |
| 1998 | 1998-03-03 00:00:00 |
| 1998 | 1998-03-03 00:00:00 |
| 1998 | 1998-03-03 00:00:00 |
| 1998 | 1998-03-03 00:00:00 |
9.MONTH
# MONTH(date):返回时间所在的月。
mysql> SELECT MONTH(NOW()),NOW();
+--------------+---------------------+
| MONTH(NOW()) | NOW() |
+--------------+---------------------+
| 2 | 2021-02-25 21:56:44 |
+--------------+---------------------+
1 row in set (0.03 sec)
mysql> SELECT MONTH('1998-01-01');
+---------------------+
| MONTH('1998-01-01') |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.03 sec)
mysql> SELECT MONTH(hiredate),hiredate from employees;
+-----------------+---------------------+
| MONTH(hiredate) | hiredate |
+-----------------+---------------------+
| 4 | 1992-04-03 00:00:00 |
| 4 | 1992-04-03 00:00:00 |
| 4 | 1992-04-03 00:00:00 |
| 4 | 1992-04-03 00:00:00 |
| 4 | 1992-04-03 00:00:00 |
| 3 | 1998-03-03 00:00:00 |
| 3 | 1998-03-03 00:00:00 |
| 3 | 1998-03-03 00:00:00 |
| 3 | 1998-03-03 00:00:00 |
| 3 | 1998-03-03 00:00:00 |
10.DAY
# DAY(date):返回时间所在的天。
mysql> SELECT DAY(NOW()),NOW();
+------------+---------------------+
| DAY(NOW()) | NOW() |
+------------+---------------------+
| 25 | 2021-02-25 22:00:32 |
+------------+---------------------+
1 row in set (0.04 sec)
mysql> SELECT DAY('1998-01-01');
+-------------------+
| DAY('1998-01-01') |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.04 sec)
mysql> SELECT DAY(hiredate),hiredate from employees;
+---------------+---------------------+
| DAY(hiredate) | hiredate |
+---------------+---------------------+
| 3 | 1992-04-03 00:00:00 |
| 3 | 1992-04-03 00:00:00 |
| 3 | 1992-04-03 00:00:00 |
| 3 | 1992-04-03 00:00:00 |
| 3 | 1992-04-03 00:00:00 |
| 3 | 1998-03-03 00:00:00 |
| 3 | 1998-03-03 00:00:00 |
| 3 | 1998-03-03 00:00:00 |
| 3 | 1998-03-03 00:00:00 |
| 3 | 1998-03-03 00:00:00 |
11.HOUR
# HOUR(time):返回时间所在的小时。
mysql> SELECT HOUR(NOW()),NOW();
+-------------+---------------------+
| HOUR(NOW()) | NOW() |
+-------------+---------------------+
| 21 | 2021-02-25 21:43:44 |
+-------------+---------------------+
1 row in set (0.05 sec)
mysql> SELECT HOUR('1998-01-01');
+--------------------+
| HOUR('1998-01-01') |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.04 sec)
mysql> SELECT HOUR(hiredate),hiredate FROM employees;
+----------------+---------------------+
| HOUR(hiredate) | hiredate |
+----------------+---------------------+
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
12.MINUTE
# MINUTE(time):返回时间所在的分钟。
mysql> SELECT MINUTE(NOW()),NOW();
+---------------+---------------------+
| MINUTE(NOW()) | NOW() |
+---------------+---------------------+
| 6 | 2021-02-25 22:06:41 |
+---------------+---------------------+
1 row in set (0.04 sec)
mysql> SELECT MINUTE('1998-01-01');
+----------------------+
| MINUTE('1998-01-01') |
+----------------------+
| NULL |
+----------------------+
1 row in set (0.04 sec)
mysql> SELECT MINUTE(hiredate),hiredate FROM employees;
+------------------+---------------------+
| MINUTE(hiredate) | hiredate |
+------------------+---------------------+
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
13 .SECOND
# SECOND(time):返回时间所在的秒。
mysql> SELECT SECOND(NOW()),NOW();
+---------------+---------------------+
| SECOND(NOW()) | NOW() |
+---------------+---------------------+
| 12 | 2021-02-25 22:10:12 |
+---------------+---------------------+
1 row in set (0.01 sec)
mysql> SELECT SECOND('1998-01-01');
+----------------------+
| SECOND('1998-01-01') |
+----------------------+
| NULL |
+----------------------+
1 row in set (0.01 sec)
mysql> SELECT SECOND(hiredate),hiredate from employees;
+------------------+---------------------+
| SECOND(hiredate) | hiredate |
+------------------+---------------------+
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1992-04-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
| 0 | 1998-03-03 00:00:00 |
14.WEEK
# WEEK(date):返回一年中的第几周。
mysql> SELECT WEEK(NOW()),NOW();
+-------------+---------------------+
| WEEK(NOW()) | NOW() |
+-------------+---------------------+
| 8 | 2021-02-26 10:02:46 |
+-------------+---------------------+
1 row in set (0.06 sec)
mysql> SELECT WEEK('1998-01-01');
+--------------------+
| WEEK('1998-01-01') |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.06 sec)
mysql> SELECT WEEK(hiredate),hiredate from employees;
+----------------+---------------------+
| WEEK(hiredate) | hiredate |
+----------------+---------------------+
| 13 | 1992-04-03 00:00:00 |
| 13 | 1992-04-03 00:00:00 |
| 13 | 1992-04-03 00:00:00 |
| 13 | 1992-04-03 00:00:00 |
| 13 | 1992-04-03 00:00:00 |
| 9 | 1998-03-03 00:00:00 |
| 9 | 1998-03-03 00:00:00 |
| 9 | 1998-03-03 00:00:00 |
| 9 | 1998-03-03 00:00:00 |
| 9 | 1998-03-03 00:00:00 |
15.WEEKOFYEAR
# WEEKOFYEAR(date):返回一年中的第几周。
mysql> SELECT WEEKOFYEAR(NOW()),NOW();
+-------------------+---------------------+
| WEEKOFYEAR(NOW()) | NOW() |
+-------------------+---------------------+
| 8 | 2021-02-26 10:47:41 |
+-------------------+---------------------+
1 row in set (0.06 sec)
mysql> SELECT WEEKOFYEAR('1998-01-01');
+--------------------------+
| WEEKOFYEAR('1998-01-01') |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.04 sec)
mysql> SELECT WEEKOFYEAR(hiredate) FROM employees;
+----------------------+
| WEEKOFYEAR(hiredate) |
+----------------------+
| 14 |
| 14 |
| 14 |
| 14 |
| 14 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
16.DAYOFWEEK
# DAYOFWEEK():返回周几,注意:周日是1,周一是2,。。。周六是7。
mysql> SELECT DAYOFWEEK(NOW()),NOW();
+------------------+---------------------+
| DAYOFWEEK(NOW()) | NOW() |
+------------------+---------------------+
| 6 | 2021-02-26 10:50:36 |
+------------------+---------------------+
1 row in set (0.07 sec)
mysql> SELECT DAYOFWEEK('1998-01-01');
+-------------------------+
| DAYOFWEEK('1998-01-01') |
+-------------------------+
| 5 |
+-------------------------+
1 row in set (0.08 sec)
mysql> SELECT DAYOFWEEK(hiredate) FROM employees;
+---------------------+
| DAYOFWEEK(hiredate) |
+---------------------+
| 6 |
| 6 |
| 6 |
| 6 |
| 6 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
17.WEEKDAY
# WEEKDAY(date)|返回周几,注意,周1是0,周2是1,。。。周日是6。
mysql> SELECT DAYOFWEEK(NOW()),NOW();
+------------------+---------------------+
| DAYOFWEEK(NOW()) | NOW() |
+------------------+---------------------+
| 6 | 2021-02-26 10:50:36 |
+------------------+---------------------+
1 row in set (0.07 sec)
mysql> SELECT DAYOFWEEK('1998-01-01');
+-------------------------+
| DAYOFWEEK('1998-01-01') |
+-------------------------+
| 5 |
+-------------------------+
1 row in set (0.08 sec)
mysql> SELECT DAYOFWEEK(hiredate) FROM employees;
+---------------------+
| DAYOFWEEK(hiredate) |
+---------------------+
| 6 |
| 6 |
| 6 |
| 6 |
| 6 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
18.DAYNAME
# DAYNAME(date)|返回星期:MONDAY,TUESDAY.....SUNDAY。
mysql> SELECT DAYNAME(NOW()),NOW();
+----------------+---------------------+
| DAYNAME(NOW()) | NOW() |
+----------------+---------------------+
| Friday | 2021-02-26 10:53:58 |
+----------------+---------------------+
1 row in set (0.07 sec)
mysql> SELECT DAYNAME('1998-01-01');
+-----------------------+
| DAYNAME('1998-01-01') |
+-----------------------+
| Thursday |
+-----------------------+
1 row in set (0.06 sec)
mysql> SELECT DAYNAME(hiredate) FROM employees;
+-------------------+
| DAYNAME(hiredate) |
+-------------------+
| Friday |
| Friday |
| Friday |
| Friday |
| Friday |
| Tuesday |
| Tuesday |
| Tuesday |
| Tuesday |
| Tuesday |
19.MONTHNAME
# MONTHNAME(date):返回月份:January,。。。。。
mysql> SELECT MONTHNAME(NOW()),NOW();
+------------------+---------------------+
| MONTHNAME(NOW()) | NOW() |
+------------------+---------------------+
| February | 2021-02-26 10:55:37 |
+------------------+---------------------+
1 row in set (0.09 sec)
mysql> SELECT MONTHNAME('1998-01-01');
+-------------------------+
| MONTHNAME('1998-01-01') |
+-------------------------+
| January |
+-------------------------+
1 row in set (0.06 sec)
mysql> SELECT MONTHNAME(hiredate) FROM employees;
+---------------------+
| MONTHNAME(hiredate) |
+---------------------+
| April |
| April |
| April |
| April |
| April |
| March |
| March |
| March |
| March |
| March |
20.DATEDIFF
# DATEDIFF(date1,date2):返回date1 - date2的日期间隔。
mysql> SELECT DATEDIFF('1998-01-01',NOW()),NOW();
+------------------------------+---------------------+
| DATEDIFF('1998-01-01',NOW()) | NOW() |
+------------------------------+---------------------+
| -8457 | 2021-02-26 21:44:48 |
+------------------------------+---------------------+
1 row in set (0.03 sec)
mysql> SELECT DATEDIFF(NOW(),'1998-01-01'),NOW();
+------------------------------+---------------------+
| DATEDIFF(NOW(),'1998-01-01') | NOW() |
+------------------------------+---------------------+
| 8457 | 2021-02-26 21:45:22 |
+------------------------------+---------------------+
21.TIMEDIFF
# TIMEDIFF(time1, time2):返回time1 - time2的时间间隔。
mysql> SELECT TIMEDIFF(NOW(),'01:00:00'),NOW();
+----------------------------+---------------------+
| TIMEDIFF(NOW(),'01:00:00') | NOW() |
+----------------------------+---------------------+
| NULL | 2021-02-26 21:55:44 |
+----------------------------+---------------------+
1 row in set (0.03 sec)
mysql> SELECT TIMEDIFF(CURTIME(),'01:00:00'),NOW();
+--------------------------------+---------------------+
| TIMEDIFF(CURTIME(),'01:00:00') | NOW() |
+--------------------------------+---------------------+
| 20:55:48 | 2021-02-26 21:55:48 |
+--------------------------------+---------------------+
1 row in set (0.02 sec)
mysql> SELECT TIMEDIFF(CURTIME(),'1998-01-01'),NOW();
+----------------------------------+---------------------+
| TIMEDIFF(CURTIME(),'1998-01-01') | NOW() |
+----------------------------------+---------------------+
| NULL | 2021-02-26 21:56:03 |
+----------------------------------+---------------------+
1 row in set (0.03 sec)
22.DATE_ADD
# DATE_ADD(datetime, INTERVALE expr type):返回与给定日期时间相差INTERVAL时间段的日期时间。
mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR),NOW();
+----------------------------------+---------------------+
| DATE_ADD(NOW(), INTERVAL 1 YEAR) | NOW() |
+----------------------------------+---------------------+
| 2022-02-27 09:19:49 | 2021-02-27 09:19:49 |
+----------------------------------+---------------------+
1 row in set (0.01 sec)
mysql> SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR),NOW(); #可以是负数
+-----------------------------------+---------------------+
| DATE_ADD(NOW(), INTERVAL -1 YEAR) | NOW() |
+-----------------------------------+---------------------+
| 2020-02-27 09:19:59 | 2021-02-27 09:19:59 |
+-----------------------------------+---------------------+
1 row in set (0.02 sec)
mysql> SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH),NOW(); #需要单引号
+--------------------------------------------+---------------------+
| DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) | NOW() |
+--------------------------------------------+---------------------+
| 2022-03-27 09:20:11 | 2021-02-27 09:20:11 |
+--------------------------------------------+---------------------+
1 row in set (0.01 sec)
表达式类型 |
---|
YEAR |
MONTH |
DAY |
HOUR |
MINUTE |
SECOND |
YEAR_MONTH |
DAY_HOUR |
DAY_MINUTE |
DAY_SECOND |
HOUR_MINUTE |
HOUR_SECOND |
MINUTE_SECOND |
23.DATE_FORMAT和STR_TO_DATE
DATE_FORMAT(fmt:指定是输出格式。)
# DATE_FORMAT(datetime,fmt):按照字符串fmt格式化日期datetime值。
mysql> SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时%i分%s秒'),NOW();
+------------------------------------------------+---------------------+
| DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时%i分%s秒') | NOW() |
+------------------------------------------------+---------------------+
| 2021年02月27日 09时56分45秒 | 2021-02-27 09:56:45 |
+------------------------------------------------+---------------------+
1 row in set (0.02 sec)
mysql> SELECT DATE_FORMAT(NOW(),'%Y/%m/%d %H/%i/%s'),NOW();
+----------------------------------------+---------------------+
| DATE_FORMAT(NOW(),'%Y/%m/%d %H/%i/%s') | NOW() |
+----------------------------------------+---------------------+
| 2021/02/27 09/59/58 | 2021-02-27 09:59:58 |
+----------------------------------------+---------------------+
1 row in set (0.02 sec)
STR_TO_DATE(fmt:指定是输入格式,字符串日期要符合该格式才有效。)
# STR_TO_DATE(str,fmt):按照字符串fmt对str进行解析,解析为一个日期。
mysql> SELECT STR_TO_DATE('1998-01-01','%Y/%m/%d');
+--------------------------------------+
| STR_TO_DATE('1998-01-01','%Y/%m/%d') |
+--------------------------------------+
| NULL |
+--------------------------------------+
1 row in set (0.03 sec)
mysql> SELECT STR_TO_DATE('1998-01-01','%Y-%m-%d');
+--------------------------------------+
| STR_TO_DATE('1998-01-01','%Y-%m-%d') |
+--------------------------------------+
| 1998-01-01 |
+--------------------------------------+
1 row in set (0.03 sec)
DATE_FORMAT(datetime ,fmt)和STR_TO_DATE(str, fmt)。
常用的格式符
格式符 | 说明 |
---|---|
%Y | 四位的年份 |
%y | 2位的年份 |
%m | 月份(01,02…11,12) |
%c | 月份(1,2,…11,12) |
%d | 日(01,02,…) |
%H | 小时(24小时制) |
%h | 小时(12小时制) |
%i | 分钟(00,01…59) |
%s | 秒(00,01,…59) |
更多的格式符
格式符 | 说明 |
---|---|
%M | 月名表示月份(January,…) |
%b | 缩写的月名(Jan.,Feb.,…) |
%D | 英文后缀表示月中的天数(1st,2nd,3rd,…) |
%e | 数字形式表示月中的天数(1,2,3,4,5…) |
%k | 数字形式的小时,24小时制(1,2,3) |
%W | 一周中的星期名称(Sunday…) |
%w | 以数字表示周中的天数(0=Sunday,1=Monday…) |
%j | 以3位数字表示年中的天数(001,002…) |
%u | 以数字表示年中的第几周,(1,2,3。。)其中Monday为周中第一天 |
%T | 24小时制 |
%p | AM或PM |
%h和%I | 两位数字表示小时,12小时制(01,02…) |
%l | 数字形式表示小时,12小时制(1,2,3,4…) |
%S和%s | 两位数字表示秒(00,01,02…) |
%a | 一周中的星期缩写(Sun.,Mon.,Tues.,…) |
%U | 以数字表示年中的第几周,(1,2,3。。)其中Sunday为周中第一天 |
%r | 12小时制 |
%% | 表示% |
4.流程函数
1.IF
# IF(value,t ,f):如果value是真,返回t,否则返回f。
mysql> SELECT IF(100>9,'好','坏');
+---------------------+
| IF(100>9,'好','坏') |
+---------------------+
| 好 |
+---------------------+
1 row in set (0.03 sec)
#需求:如果有奖金,则显示最终奖金,如果没有,则显示0。
mysql> SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct) 奖金,commission_pct FROM employees;
+----------+----------------+
| 奖金 | commission_pct |
+----------+----------------+
| 0.00 | NULL |
| 0.00 | NULL |
| 0.00 | NULL |
| 0.00 | NULL |
| 0.00 | NULL |
| 0.00 | NULL |
| 0.00 | NULL |
| 0.00 | NULL |
| 0.00 | NULL |
| 0.00 | NULL |
2.IFNULL
# IFNULL(value1, value2):如果value1不为空,返回value1,否则返回value2
mysql> SELECT IFNULL(commission_pct,0) 奖金,commission_pct FROM employees;
| 0.00 | NULL |
| 0.00 | NULL |
| 0.00 | NULL |
| 0.00 | NULL |
| 0.00 | NULL |
| 0.40 | 0.40 |
| 0.30 | 0.30 |
| 0.30 | 0.30 |
| 0.30 | 0.30 |
| 0.20 | 0.20 |
3.CASE WEHN
条件(相当于Java的if…else if…)
# 案例:查询员工的工资的情况
# 如果工资>20000,显示A级别
# 如果工资>15000,显示B级别
# 如果工资>10000,显示C级别
# 否则,显示D级别
mysql> SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
+----------+----------+
| salary | 工资级别 |
+----------+----------+
| 24000.00 | A |
| 17000.00 | B |
| 17000.00 | B |
| 9000.00 | D |
| 6000.00 | D |
| 4800.00 | D |
| 4800.00 | D |
| 4200.00 | D |
| 12000.00 | C |
| 9000.00 | D |
常量值(相当于Java的switch)
# 案例:查询员工的工资,要求
# 部门号=30,显示的工资为1.1倍
# 部门号=40,显示的工资为1.2倍
# 部门号=50,显示的工资为1.3倍
# 其他部门,显示的工资为原工资
mysql> SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
+----------+---------------+----------+
| 原始工资 | department_id | 新工资 |
+----------+---------------+----------+
| 24000.00 | 90 | 24000.00 |
| 17000.00 | 90 | 17000.00 |
| 17000.00 | 90 | 17000.00 |
| 9000.00 | 60 | 9000.00 |
| 6000.00 | 60 | 6000.00 |
| 4800.00 | 60 | 4800.00 |
| 4800.00 | 60 | 4800.00 |
| 4200.00 | 60 | 4200.00 |
| 12000.00 | 100 | 12000.00 |
5.系统信息函数
1.VERSION
# VERSION():返回数据库的版本号。
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.15 |
+-----------+
1 row in set (0.04 sec)
2.CONNECTION_ID
# CONNECTION_ID():返回服务器的连接数。
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 137 |
+-----------------+
1 row in set (0.04 sec)
3.DATABASE
# DATABASE():返回当前数据库名。
mysql> SELECT DATABASE();
+-------------+
| DATABASE() |
+-------------+
| myemployees |
+-------------+
1 row in set (0.03 sec)
4.SCHEMA
# SCHEMA():返回当前数据库名。
mysql> SELECT SCHEMA();
+-------------+
| SCHEMA() |
+-------------+
| myemployees |
+-------------+
1 row in set (0.03 sec)
5.USER
# USER():返回当前用户名称。
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.03 sec)
5.SYSTEM_USER
# SYSTEM_USER():返回当前用户名称。
mysql> SELECT SYSTEM_USER();
+----------------+
| SYSTEM_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.03 sec)
6.SESSION_USER
# SESSION_USER():返回当前用户名称。
mysql> SELECT SESSION_USER();
+----------------+
| SESSION_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.03 sec)
7.CURRENT_USER
# CURRENT_USER():返回当前用户名称。
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.03 sec)
8.CHARSET
# CHARSET(str):返回字符串str的字符编码。
mysql> SELECT CHARSET('我叫');
+-----------------+
| CHARSET('我叫') |
+-----------------+
| utf8mb4 |
+-----------------+
1 row in set (0.03 sec)
mysql> SELECT CHARSET('1998');
+-----------------+
| CHARSET('1998') |
+-----------------+
| utf8mb4 |
+-----------------+
1 row in set (0.03 sec)
mysql> SELECT CHARSET('A');
+--------------+
| CHARSET('A') |
+--------------+
| utf8mb4 |
+--------------+
1 row in set (0.03 sec)
9.COLLATION
# COLLATION(str):返回字符串str的字符排列方式。
mysql> SELECT COLLATION('格雷福斯');
+-----------------------+
| COLLATION('格雷福斯') |
+-----------------------+
| utf8mb4_general_ci |
+-----------------------+
1 row in set (0.03 sec)
10.LAST_INSET
# LAST_INSET():返回最后生成的auto_increment(自动增长)的值。
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 0 |
+------------------+
1 row in set (0.03 sec)
6.加密函数
加密函数是MySQL中用来对数据进行加密的函数。因为数据库中有些很敏感的信息不希望被其他人看到,就应该通过加密方式来使这些数据变成看似乱码的数据。例如用户的密码,就应该经过加密。本小节将详细讲解加密函数的作用和使用方法。
下面是各种加密函数的名称、作用和使用方法。
1.PASSWORD
PASSWORD(str)函数可以对字符串str进行加密。一般情况下,PASSWORD(str)函数主要是用来给用户的密码加密的。下面使用PASSWORD(str)函数为字符串“abcd”加密。
mysql> SELECT PASSWORD('abcd');
+-------------------------------------------+
| PASSWORD('abcd') |
+-------------------------------------------+
| *A154C52565E9E7F94BFC08A1FE702624ED8EFFDA |
+-------------------------------------------+
1 row in set (0.03 sec)
2.MD5
MD5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。下面使用MD5(str)函数为字符串“abcd”加密。
mysql> SELECT MD5('abcd');
+----------------------------------+
| MD5('abcd') |
+----------------------------------+
| e2fc714c4727ee9395f324cd2e7f331f |
+----------------------------------+
1 row in set (0.03 sec)
3.ENCODE
ENCODE(str,pswd_str)函数可以使用字符串pswd_str来加密字符串str。加密的结果是一个二进制数,必须使用BLOB类型的字段来保存它。
mysql> SELECT ENCODE('abcd','123');
+----------------------+
| ENCODE('abcd','123') |
+----------------------+
| ���Q |
+----------------------+
1 row in set (0.39 sec)
mysql> SELECT ENCODE('abcd','12');
+---------------------+
| ENCODE('abcd','12') |
+---------------------+
| :�� |
+---------------------+
1 row in set (0.03 sec)
DECODE(crypt_str,pswd_str)函数可以使用字符串pswd_str来为crypt_str解密。crypt_str是通过ENCODE(str,pswd_str)加密后的二进制数据。字符串pswd_str应该与加密时的字符串pswd_str是相同的。下面使用DECODE(crypt_str,pswd_str)为ENCODE(str,pswd_str)加密的数据解密。
2.多行函数
聚合函数是用来做纵向运算的函数:
- COUNT():统计指定列不为NULL的记录行数;
- MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
- MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
- SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
- AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
案例
聚合函数因为很简单就不举例了直接案例
#案例1 :查询员工信息表中,所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数。
mysql> SELECT
SUM( salary ) 工资和,
AVG( salary ) 工资平均值,
MIN( salary ) 最低工资,
MAX( salary ) 最高工资,
COUNT( salary) 有工资的个数
FROM
employees;
+-----------+-------------+----------+----------+--------------+
| 工资和 | 工资平均值 | 最低工资 | 最高工资 | 有工资的个数 |
+-----------+-------------+----------+----------+--------------+
| 691400.00 | 6461.682243 | 2100.00 | 24000.00 | 107 |
+-----------+-------------+----------+----------+--------------+
1 row in set (0.03 sec)
# 案例2:添加筛选条件
# 1、查询emp表中记录数:
SELECT COUNT(employee_id) FROM employees;
mysql> SELECT COUNT(employee_id) FROM employees;
+--------------------+
| COUNT(employee_id) |
+--------------------+
| 107 |
+--------------------+
1 row in set (0.03 sec)
# 2、查询emp表中有佣金的人数:
mysql> SELECT COUNT(salary) FROM employees;
+---------------+
| COUNT(salary) |
+---------------+
| 107 |
+---------------+
1 row in set (0.03 sec)
# 3、查询emp表中月薪大于2500的人数:
mysql> SELECT COUNT(salary) FROM employees WHERE salary>2500;
+---------------+
| COUNT(salary) |
+---------------+
| 96 |
+---------------+
1 row in set (0.04 sec)
# 4、查询有领导的人数:
mysql> SELECT COUNT(manager_id) FROM employees;
+-------------------+
| COUNT(manager_id) |
+-------------------+
| 106 |
+-------------------+
1 row in set (0.03 sec)
# 思考:每个部门的总工资、平均工资?
mysql> SELECT SUM(salary) ,department_id
FROM employees
GROUP BY department_id;
+-------------+---------------+
| SUM(salary) | department_id |
+-------------+---------------+
| 7000.00 | NULL |
| 4400.00 | 10 |
| 19000.00 | 20 |
| 24900.00 | 30 |
| 6500.00 | 40 |
| 156400.00 | 50 |
| 28800.00 | 60 |
| 10000.00 | 70 |
| 304500.00 | 80 |
| 58000.00 | 90 |
| 51600.00 | 100 |
| 20300.00 | 110 |
+-------------+---------------+
12 rows in set (0.05 sec)
补充
# count的补充。
# 1、参数支持哪些类型。
mysql> SELECT SUM(last_name) ,AVG(last_name) FROM employees;
+----------------+----------------+
| SUM(last_name) | AVG(last_name) |
+----------------+----------------+
| 0 | 0 |
+----------------+----------------+
1 row in set (0.01 sec)
mysql> SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;
+-------------------------+---------------------------+
| SUM(hiredate) | AVG(hiredate) |
+-------------------------+---------------------------+
| 2148552443000000.000000 | 20079929373831.7770000000 |
+-------------------------+---------------------------+
1 row in set (0.01 sec)
mysql> SELECT MAX(last_name),MIN(last_name) FROM employees;
+----------------+----------------+
| MAX(last_name) | MIN(last_name) |
+----------------+----------------+
| Zlotkey | Abel |
+----------------+----------------+
1 row in set (0.01 sec)
mysql> SELECT MAX(hiredate),MIN(hiredate) FROM employees;
+---------------------+---------------------+
| MAX(hiredate) | MIN(hiredate) |
+---------------------+---------------------+
| 2016-03-03 00:00:00 | 1992-04-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.06 sec)
mysql> SELECT COUNT(commission_pct) FROM employees;
+-----------------------+
| COUNT(commission_pct) |
+-----------------------+
| 35 |
+-----------------------+
1 row in set (0.03 sec)
mysql> SELECT COUNT(last_name) FROM employees;
+------------------+
| COUNT(last_name) |
+------------------+
| 107 |
+------------------+
1 row in set (0.03 sec)
# 2、统计结果集的行数,推荐使用count(*)。
mysql> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
| 107 |
+----------+
1 row in set (0.03 sec)
mysql> SELECT COUNT(*) FROM employees WHERE department_id = 30;
+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
1 row in set (0.03 sec)
mysql> SELECT COUNT(1) FROM employees;
+----------+
| COUNT(1) |
+----------+
| 107 |
+----------+
1 row in set (0.03 sec)
mysql> SELECT COUNT(1) FROM employees WHERE department_id = 30;
+----------+
| COUNT(1) |
+----------+
| 6 |
+----------+
1 row in set (0.03 sec)
# 3、搭配distinct实现去重的统计。
# 需求:查询有员工的部门个数。
mysql> SELECT COUNT(DISTINCT department_id) FROM employees;
+-------------------------------+
| COUNT(DISTINCT department_id) |
+-------------------------------+
| 11 |
+-------------------------------+
1 row in set (0.03 sec)
1.COUNT补充
注:下面的讨论和结论是基于 InnoDB 引擎的。
首先要弄清楚 count() 的语义。count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是 NULL,累计值就加 1,否则不加。最后返回累计值。
所以,count(*)、count(1)和count(主键 id) 都表示返回满足条件的结果集的总行数;而 count(字段),则表示返回满足条件的数据行里面,参数“字段”不为 NULL 的总个数。
至于分析性能差别的时候,记住这么几个原则:
- server 层要什么就给什么;
- InnoDB 只给必要的值;
- 现在的优化器只优化了 count(*) 的语义为“取行数”,其他“显而易见”的优化并没有做。
count(可空字段)
扫描全表,读到server层,判断字段可空,拿出该字段所有值,判断每一个值是否为空,不为空则累加。
count(非空字段)与count(主键 id)
扫描全表,读到server层,判断字段不可空,按行累加。
count(1)
扫描全表,但不取值,server层收到的每一行都是1,判断不可能是null,按值累加。
注意:count(1)执行速度比count(主键 id)快的原因:从引擎返回 id 会涉及到解析数据行,以及拷贝字段值的操作。
count(*)
MySQL 执行count(*)在优化器做了专门优化。因为count(*)返回的行一定不是空。扫描全表,但是不取值,按行累加。
看到这里,你会说优化器就不能自己判断一下吗,主键 id 肯定是非空的,为什么不能按照 count(*) 来处理,多么简单的优化。当然 MySQL 专门针对这个语句进行优化也不是不可以。但是这种需要专门优化的情况太多了,而且 MySQL 已经优化过 count(*) 了,你直接使用这种语句就可以了。
性能对比结论
count(可空字段) < count(非空字段) = count(主键 id) < count(1) ≈ count(*)