函数:
version() # 用来查询当前数据库的版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.14 |
+-----------+
user() # 查询当前登录用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
database() # 查询当前所在的数据库
mysql> select database();
+------------+
| database() |
+------------+
| db_yckd |
+------------+
uuid() # 返回uuid的值,分布式情况下数据库主键不重复的解决方案
mysql> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 8da89043-0c89-11eb-929c-000c29454843 |
+--------------------------------------+
聚合函数:
count(列名称) # 统计行
mysql> select count("职工号") from worker;
+--------------------+
| count("职工号") |
+--------------------+
| 6 |
+--------------------+
max(列名称) # 最大值
mysql> SELECT MAX(`工资`) from worker;
+---------------+
| MAX(`工资`) |
+---------------+
| 8500.00 |
+---------------+
min(列名称) # 最小值
mysql> SELECT MIN(`工资`) from worker;
+---------------+
| MIN(`工资`) |
+---------------+
| 3200.00 |
+---------------+
sum(列名称) # 求和统计
mysql> SELECT SUM(`工资`) from worker;
+---------------+
| SUM(`工资`) |
+---------------+
| 30000.00 |
+---------------+
avg(列名称) # 求平均数
mysql> SELECT AVG(`工资`) from worker;
+---------------+
| AVG(`工资`) |
+---------------+
| 5000.000000 |
+---------------+
如果使用了聚合函数,建议和别名配合使用!!
数值型函数:
abs(num) # 求绝对值
mysql> select abs(1);
+--------+
| abs(1) |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
sqrt(num) # 开平方根
mysql> select sqrt(16);
+----------+
| sqrt(16) |
+----------+
| 4 |
+----------+
pow(x, y)/power # 幂次方
mysql> select pow(2,3);
+----------+
| pow(2,3) |
+----------+
| 8 |
+----------+
mod(x, y) # 求余
mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
| 1 |
+-----------+
ceil(num)/ceiling() # 向上取整
mysql> select ceil(4.3356);
+--------------+
| ceil(4.3356) |
+--------------+
| 5 |
+--------------+
floor(num) # 向下取整
mysql> select floor(4.3356);
+---------------+
| floor(4.3356) |
+---------------+
| 4 |
+---------------+
round(num) # 四舍五入
mysql> select round(4.3356);
+---------------+
| round(4.3356) |
+---------------+
| 4 |
+---------------+
RAND() # 随机数
mysql> select RAnD();
+--------------------+
| RAnD() |
+--------------------+
| 0.7527357840470371 |
+--------------------+
sign(num) # 返回自然数的符号(正:1, 负:-1,0为0)
mysql> select sign(-95);
+-----------+
| sign(-95) |
+-----------+
| -1 |
+-----------+
字符串函数
# 获取字符串存储长度,注意中文编码问题
mysql> select length('this is good day');
+----------------------------+
| length('this is good day') |
+----------------------------+
| 16 |
+----------------------------+
char_length() # 字符长度
mysql> select char_length('this');
+---------------------+
| char_length('this') |
+---------------------+
| 4 |
+---------------------+
concat(s1,s2...) # 拼接字符串
mysql> select concat('haha','heihei');
+-------------------------+
| concat('haha','heihei') |
+-------------------------+
| hahaheihei |
INSERT(str,pos,len,newstr) # 替换字符串
mysql> select insert('this is good day', '6', '2', 'isnot');
+-----------------------------------------------+
| insert('this is good day', '6', '2', 'isnot') |
+-----------------------------------------------+
| this isnot good day |
+-----------------------------------------------+
lower() # 转换为小写
mysql> select lower('THIS IS GOOD DAY');
+---------------------------+
| lower('THIS IS GOOD DAY') |
+---------------------------+
| this is good day |
+---------------------------+
upper() # 转大写
mysql> select upper('this is good day');
+---------------------------+
| upper('this is good day') |
+---------------------------+
| THIS IS GOOD DAY |
left(s, len) # 从左侧截取len长度的字符串
mysql> select left('this is good day', 6)
-> ;
+-----------------------------+
| left('this is good day', 6) |
+-----------------------------+
| this i |
+-----------------------------+
right(s, len) # 从右侧截取len长度的字符串
mysql> select right('this is good day', 6);
+------------------------------+
| right('this is good day', 6) |
+------------------------------+
| od day |
+------------------------------+
trim() # 清除字符串两侧空格
mysql> select trim(' ok ');
+-------------------+
| trim(' ok ') |
+-------------------+
| ok |
+-------------------+
replace(s,s1, s2) # 替换字符串
mysql> select replace('this is good day', 'is', 'isnot');
+--------------------------------------------+
| replace('this is good day', 'is', 'isnot') |
+--------------------------------------------+
| thisnot isnot good day |
+--------------------------------------------+
substring(s, pos, len) # 截取字符串
mysql> select substring('this is good day', 3, 4);
+-------------------------------------+
| substring('this is good day', 3, 4) |
+-------------------------------------+
| is i |
+-------------------------------------+
reverse(str) # 翻转字符串
mysql> select reverse('this is good day');
+-----------------------------+
| reverse('this is good day') |
+-----------------------------+
| yad doog si siht |
+-----------------------------+
STRCMP(expr1,expr2) # 比较两个表达式的顺序。若expr1 小于 expr2 ,则返回 -1,0相等,1则相反
mysql> select strcmp('haha1', 'haha2');
+--------------------------+
| strcmp('haha1', 'haha2') |
+--------------------------+
| -1 |
+--------------------------+
INSTR(str,s) # 返回第一次出现子串的位置
mysql> select instr('this is good day', 'is')
-> ;
+---------------------------------+
| instr('this is good day', 'is') |
+---------------------------------+
| 3 |
+---------------------------------+
locate(s, str [,pos]) # 返回第一次出现子串的位置,pos表示匹配位置
mysql> select locate('is', 'this is good day', 5);
+-------------------------------------+
| locate('is', 'this is good day', 5) |
+-------------------------------------+
| 6 |
+-------------------------------------+
日期和时间函数
SELECT CURDATE();
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-10-13 |
+------------+
SELECT CURRENT_DATE();
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2020-10-13 |
+----------------+
SELECT CURRENT_DATE;
mysql> select current_date;
+--------------+
| current_date |
+--------------+
| 2020-10-13 |
+--------------+
SELECT CURTIME();
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 20:16:57 |
+-----------+
SELECT CURRENT_TIME();
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 20:14:46 |
+----------------+
SELECT CURRENT_TIME;
mysql> select current_time;
+--------------+
| current_time |
+--------------+
| 20:17:28 |
+--------------+
SELECT NOW();
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-10-13 20:17:54 |
+---------------------+
SELECT SYSDATE();
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2020-10-13 20:18:12 |
+---------------------+
# 获取给定时间的日期
SELECT DATE(now());
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2020-10-13 |
+-------------+
SELECT DATE('2002-03-26 01:01:13');
mysql> select date('2020-10-01');
+--------------------+
| date('2020-10-01') |
+--------------------+
| 2020-10-01 |
+--------------------+
SELECT TIME(SYSDATE());
SELECT TIME('2002-03-26 01:01:13');
mysql> select time('2001-10-05 10:01:01');
+-----------------------------+
| time('2001-10-05 10:01:01') |
+-----------------------------+
| 10:01:01 |
+-----------------------------+
SELECT MONTH(now());
SELECT MONTHNAME(now());
mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| October |
+------------------+
# 每月的第几天
SELECT DAY(now());
mysql> select day(now());
+------------+
| day(now()) |
+------------+
| 13 |
+------------+
-- 星期数
SELECT DAYNAME(now());
SELECT DAYOFWEEK(now()) # 0是星期天,以此类推
mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
| 3 |
+------------------+
SELECT year(now());
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2020 |
+-------------+
# 查询一年中的那一天
SELECT DAYOFYEAR(now());
mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
| 287 |
+------------------+
SELECT DATEDIFF('2020-10-10', "20200808");
mysql> select datediff('2020-10-01', '2020-06-01');
+--------------------------------------+
| datediff('2020-10-01', '2020-06-01') |
+--------------------------------------+
| 122 |
+--------------------------------------+