SQL中常用函数的使用

1、普通的常用函数
1.version() # 用来查询当前数据库的版本
mysql> select version();
±----------+
| version() |
±----------+
| 5.7.14 |
±----------+
1 row in set (0.27 sec)

2.user() # 查询当前登录用户
mysql> select user();
±---------------+
| user() |
±---------------+
| root@localhost |
±---------------+
1 row in set (0.10 sec)

3.database() # 查询当前所在的数据库
mysql> select database();
±-----------+
| database() |
±-----------+
| db_yckd |
±-----------+
1 row in set (0.00 sec)

4.uuid() # 返回uuid的值，分布式情况下数据库主键不重复的解决方案
mysql> select uuid();
±-------------------------------------+
| uuid() |
±-------------------------------------+
| b5d85a03-0d4b-11eb-8250-000c293dcf91 |
±-------------------------------------+
1 row in set (0.16 sec)

2、聚合函数
mysql> select id from student;
±----+
| id |
±----+
| 901 |
| 902 |
| 903 |
| 904 |
| 905 |
| 906 |
±----+
6 rows in set (0.01 sec)

1.count(列名称) # 统计总共有多少行行
mysql> select count(id) from student;
±----------+
| count(id) |
±----------+
| 6 |
±----------+
1 row in set (0.00 sec)

2.max(列名称) # 最大值
mysql> select max(id) from student;
±--------+
| max(id) |
±--------+
| 906 |
±--------+
1 row in set (0.00 sec)

3.min(列名称) # 最小值
mysql> select min(id) from student;
±--------+
| min(id) |
±--------+
| 901 |
±--------+
1 row in set (0.00 sec)

4.sum(列名称) # 求和统计
mysql> select sum(id) from student;
±--------+
| sum(id) |
±--------+
| 5421 |
±--------+
1 row in set (0.00 sec)

5.avg(列名称) # 求平均数
mysql> select avg(id) from student;
±---------+
| avg(id) |
±---------+
| 903.5000 |
±---------+
1 row in set (0.00 sec)

3、数值型函数
1.abs(num) # 求绝对值

mysql> select abs(-1);
±--------+
| abs(-1) |
±--------+
| 1 |
±--------+
1 row in set (0.00 sec)

2.sqrt(num) # 开平方根
mysql> select sqrt(9);
±--------+
| sqrt(9) |
±--------+
| 3 |
±--------+
1 row in set (0.00 sec)

3.pow(x, y)/power # 幂次方

mysql> select pow(2,3);
±---------+
| pow(2,3) |
±---------+
| 8 |
±---------+
1 row in set (0.11 sec)

4.mod(x, y) # 求余

mysql> select mod(10,3);
±----------+
| mod(10,3) |
±----------+
| 1 |
±----------+
1 row in set (0.00 sec)

5.ceil(num)/ceiling() # 向上取整

mysql> select ceil (4.1);
±-----------+
| ceil (4.1) |
±-----------+
| 5 |
±-----------+
1 row in set (0.00 sec)

6.floor(num) # 向下取整

mysql> select floor(4.9);
±-----------+
| floor(4.9) |
±-----------+
| 4 |
±-----------+
1 row in set (0.00 sec)

7.round(num) # 四舍五入

mysql> select round(4.5);
±-----------+
| round(4.5) |
±-----------+
| 5 |
±-----------+
1 row in set (0.00 sec)

mysql> select round(4.4);
±-----------+
| round(4.4) |
±-----------+
| 4 |
±-----------+
1 row in set (0.00 sec)

8.rand() # 0~1之间的随机数

mysql> select rand();
±-------------------+
| rand() |
±-------------------+
| 0.4886731826594781 |
±-------------------+
1 row in set (0.00 sec)

9.sign(num) # 返回自然数的符号（正：1， 负：-1，0为0）

mysql> select sign(-5);
±---------+
| sign(-5) |
±---------+
| -1 |
±---------+
1 row in set (0.00 sec)

mysql> select sign(0);
±--------+
| sign(0) |
±--------+
| 0 |
±--------+
1 row in set (0.00 sec)
3.concat(s1,s2…) # 拼接字符串
mysql> select concat(‘学习’,‘mysql’);
±-------------------------+
| concat(‘学习’,‘mysql’) |
±-------------------------+
| 学习mysql |
±-------------------------+
1 row in set (0.40 sec)

4.insert(str,pos,len,newstr) # 替换字符串
mysql> select insert(‘it is a dog’,4,2,‘is not’);
±-----------------------------------+
| insert(‘it is a dog’,4,2,‘is not’) |
±-----------------------------------+
| it is not a dog |
±-----------------------------------+
1 row in set (0.36 sec)

5.lower() # 转换为小写
mysql> select lower(‘adCD’);
±--------------+
| lower(‘adCD’) |
±--------------+
| adcd |
±--------------+
1 row in set (0.00 sec)

6.upper() # 转大写
mysql> select upper(‘adCD’);
±--------------+
| upper(‘adCD’) |
±--------------+
| ADCD |
±--------------+
1 row in set (0.01 sec)

7.left(s, len) # 从左侧截取len长度的字符串
mysql> select left(‘string = str’,3);
±-----------------------+
| left(‘string = str’,3) |
±-----------------------+
| str |
±-----------------------+
1 row in set (0.00 sec)

8.right(s, len) # 从右侧截取len长度的字符串
mysql> select right(‘string = str’,5);
±------------------------+
| right(‘string = str’,5) |
±------------------------+
| = str |
±------------------------+
1 row in set (0.00 sec)

9.trim() # 清除字符串两侧空格
mysql> select trim(’ string = str ‘);
±---------------------------+
| trim(’ string = str ') |
±---------------------------+
| string = str |
±---------------------------+
1 row in set (0.00 sec)

10.replace(s,s1, s2) # 替换字符串
mysql> select replace(‘it is a dog’,‘is’,‘isnot’);
±------------------------------------+
| replace(‘it is a dog’,‘is’,‘isnot’) |
±------------------------------------+
| it isnot a dog |
±------------------------------------+
1 row in set (0.01 sec)

11.substring(s, pos, len) # 截取字符串
mysql> select substring(‘this is a good day’,6,2);
±------------------------------------+
| substring(‘this is a good day’,6,2) |
±------------------------------------+
| is |
±------------------------------------+
1 row in set (0.00 sec)

12.reverse(str) # 翻转字符串
mysql> select reverse(‘rng’);
±---------------+
| reverse(‘rng’) |
±---------------+
| gnr |
±---------------+
1 row in set (0.00 sec)

13.strcmp(expr1,expr2) # 比较两个表达式的顺序。若expr1 小于 expr2 ，则返回 -1，0相等，1则相反
mysql> select strcmp(‘rng1’,‘rng2’);
±----------------------+
| strcmp(‘rng1’,‘rng2’) |
±----------------------+
| -1 |
±----------------------+
1 row in set (0.36 sec)

mysql> select strcmp(‘rng2’,‘rng1’);
±----------------------+
| strcmp(‘rng2’,‘rng1’) |
±----------------------+
| 1 |
±----------------------+
1 row in set (0.00 sec)
14.instr(str,s) # 返回第一次出现子串的位置
mysql> select instr(‘this is a good day’,‘is’);
±---------------------------------+
| instr(‘this is a good day’,‘is’) |
±---------------------------------+
| 3 |
±---------------------------------+
1 row in set (0.00 sec)

15.locate(s, str [,pos]) # 返回第一次出现子串的位置，pos表示匹配位置
mysql> select locate(‘is’,‘this is a good day’,5);
±------------------------------------+
| locate(‘is’,‘this is a good day’,5) |
±------------------------------------+
| 6 |
±------------------------------------+
1 row in set (0.00 sec)