mysql el函数_Mysql 常用函数

##数学函数

1、distinct (去重)

##去掉显示的Student的重复行

select distinct Student.* from Student,SC where SC.Sid=Student.Sid;

2、ABS(绝对值)

MariaDB [m4]> SELECT ABS(-1);

+---------+

| ABS(-1) |

+---------+

| 1 |

+---------+

3、BIN(返回某个数的二进制)

MariaDB [m4]> SELECT BIN(6);

+--------+

| BIN(6) |

+--------+

| 110 |

+--------+

4、CEILING (返回大于X的最小整数)

MariaDB [m4]> SELECT CEILING(2.3) ;

+--------------+

| CEILING(2.3) |

+--------------+

| 3 |

+--------------+

5、 FLOOR(X)--返回小于X的最大整数值

MariaDB [m4]> select FLOOR(2.5);

+------------+

| FLOOR(2.5) |

+------------+

| 2 |

+------------+

6、 GREATEST(x1,x2,x3...xn)--返回集合中最大的数

MariaDB [m4]> select GREATEST('1','2','2.4','3.4','1.1');

+-------------------------------------+

| GREATEST('1','2','2.4','3.4','1.1') |

+-------------------------------------+

| 3.4 |

+-------------------------------------+

7、 LEAST(x1,x2,x3...xn) --返回集合中最小的数

MariaDB [m4]> select LEAST('1','2','0.2','3');

+--------------------------+

| LEAST('1','2','0.2','3') |

+--------------------------+

| 0.2 |

+--------------------------+

8、 MOD(x,y) (返回x除y的余数)

MariaDB [m4]> select MOD(9,5);

+----------+

| MOD(9,5) |

+----------+

| 4 |

+----------+

9、RAND() (返回小于1的随机数，如果有值数字就会成固定的数)

MariaDB [m4]> select RAND();(随机数)

+--------------------+

| RAND() |

+--------------------+

| 0.5363375009375974 |

+--------------------+

1 row in set (0.00 sec)

MariaDB [m4]> select RAND(2);(下面的是固定值)

+--------------------+

| RAND(2) |

+--------------------+

| 0.6555866465490187 |

+--------------------+

10、SIGN()负数返回-1、正数返回1、0返回0

MariaDB [m4]> SELECT SIGN(0.2);

+-----------+

| SIGN(0.2) |

+-----------+

| 1 |

+-----------+

二、聚合函数

SC表内容

MariaDB [m4]> select * from SC;

+------+------+-------+

| SId | CId | score |

+------+------+-------+

| 01 | 01 | 80.0 |

| 01 | 02 | 90.0 |

| 01 | 03 | 99.0 |

| 02 | 01 | 70.0 |

| 02 | 02 | 60.0 |

| 02 | 03 | 80.0 |

| 03 | 01 | 80.0 |

| 03 | 02 | 80.0 |

| 03 | 03 | 80.0 |

| 04 | 01 | 50.0 |

| 04 | 02 | 30.0 |

| 04 | 03 | 20.0 |

| 05 | 01 | 76.0 |

| 05 | 02 | 87.0 |

| 06 | 01 | 31.0 |

| 06 | 03 | 34.0 |

| 07 | 02 | 89.0 |

| 07 | 03 | 98.0 |

1、AVG(col)返回指定列的平均值

MariaDB [m4]> select avg(score) from SC;

+------------+

| avg(score) |

+------------+

| 68.55556 |

+------------+

2、COUNT(col)返回指定列中非NULL值的个数

MariaDB [m4]> select count(score) from SC;

+--------------+

| count(score) |

+--------------+

| 18 |

+--------------+

3、MIN(col) 返回指定列的最小值

MariaDB [m4]> select min(score) from SC;

+------------+

| min(score) |

+------------+

| 20.0 |

+------------+

4、MAX(col) 返回指定列的最大值

MariaDB [m4]> select max(score) from SC;

+------------+

| max(score) |

+------------+

| 99.0 |

+------------+

5、SUM(col) 返回指定列的所有值之和

MariaDB [m4]> select sum(score) from SC;

+------------+

| sum(score) |

+------------+

| 1234.0 |

+------------+

6、GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

MariaDB [m4]> select group_concat(score) from SC;

+-------------------------------------------------------------------------------------------+

| group_concat(score) |

+-------------------------------------------------------------------------------------------+

| 80.0,90.0,99.0,70.0,60.0,80.0,80.0,80.0,80.0,50.0,30.0,20.0,76.0,87.0,31.0,34.0,89.0,98.0 |

+-------------------------------------------------------------------------------------------+

三、字符串函数

1、ASCII(char) 返回一个字符的ASCII码值

MariaDB [m4]> select ASCII(2);

+----------+

| ASCII(2) |

+----------+

| 50 |

+----------+

2、BIT_LENGTH(str)返回字符串的比特长度

MariaDB [m4]> select BIT_LENGTH('aa');

+------------------+

| BIT_LENGTH('aa') |

+------------------+

| 16 |

+------------------+

3、 CONCAT(s1,s2...sn) 将s1...sn拼成字符串

MariaDB [m4]> SELECT CONCAT(1,3,2);

+---------------+

| CONCAT(1,3,2) |

+---------------+

| 132 |

+---------------+

4、CONCAT_WS(sep,s1,s2...sn)将s1...sn用符号sep拼成字符串

MariaDB [m4]> SELECT CONCAT_WS('x',2,3,5);

+----------------------+

| CONCAT_WS('x',2,3,5) |

+----------------------+

| 2x3x5 |

+----------------------+

5、INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换成字符串instr

MariaDB [m4]> select INSERT('dsadsadddd',1,3,'xxx');(将字符串'dsadsadddd'中的从1到3的字符串替换成xxx)

+--------------------------------+

| INSERT('dsadsadddd',1,3,'xxx') |

+--------------------------------+

| xxxdsadddd |

+--------------------------------+

#全部替换

MariaDB [m4]> select INSERT('dsadsadddd',1,-1,'xxx');

+---------------------------------+

| INSERT('dsadsadddd',1,-1,'xxx') |

+---------------------------------+

| xxx |

+---------------------------------+

6、 LCASE(str)/LOWER(str) (大写改成小写)

MariaDB [m4]> select lcase('SA');

+-------------+

| lcase('SA') |

+-------------+

| sa |

+-------------+

MariaDB [m4]> select lower('SAdas');

+----------------+

| lower('SAdas') |

+----------------+

| sadas |

+----------------+

7、UPPER(str) 全变大写

MariaDB [m4]> select upper('SAdas');

+----------------+

| upper('SAdas') |

+----------------+

| SADAS |

+----------------+

8、 LEFT(str,x) /RIGHT(str,x) 返回str中最左/右边的x个字符

MariaDB [m4]> select left('SAdas',2);(最左边)

+-----------------+

| left('SAdas',2) |

+-----------------+

| SA |

+-----------------+

MariaDB [m4]> select right('SAdas',2);(右边)

+------------------+

| right('SAdas',2) |

+------------------+

| as |

+------------------+

9、LENGTH(x) 返回x的中的字符数(字符串长度)

MariaDB [m4]> select length('sadsa');

+-----------------+

| length('sadsa') |

+-----------------+

| 5 |

+-----------------+

10、LTRIM(str)/RTRIM(str) 从字符串str中切掉开头的空格

MariaDB [m4]> select ltrim(' dasd ');

+----------------------+

| ltrim(' dasd ') |

+----------------------+

| dasd |

+----------------------+

11、REPLACE(str,srchstr,rplcstr) 返回str中用srchstr替换成rplcstr的结果

MariaDB [m4]> select replace('dsadadssaqwwwwwwqq','qq','**');

+-----------------------------------------+

| replace('dsadadssaqwwwwwwqq','qq','**') |

+-----------------------------------------+

| dsadadssaqwwwwww** |

+-----------------------------------------+

12、 REVERSE(str) 返回字符串str颠倒后的结果

MariaDB [m4]> select reverse('123456abc');

+----------------------+

| reverse('123456abc') |

+----------------------+

| cba654321 |

+----------------------+

13、 STRCMP(str1,str2) 比较str1和str2(一样返回0，不同返回-1)

MariaDB [m4]> select strcmp('1a','1a');

+-------------------+

| strcmp('1a','1a') |

+-------------------+

| 0 |

+-------------------+

1 row in set (0.00 sec)

MariaDB [m4]> select strcmp('2a','1a');

+-------------------+

| strcmp('2a','1a') |

+-------------------+

| 1 |

+-------------------+

四、时间函数

1、CURDATE()或CURRENT_DATE() 返回当前的日期(年月日)

MariaDB [m4]> select curdate();

+------------+

| curdate() |

+------------+

| 2018-11-27 |

+------------+

1 row in set (0.02 sec)

MariaDB [m4]> select CURRENT_DATE();

+----------------+

| CURRENT_DATE() |

+----------------+

| 2018-11-27 |

+----------------+

2、 CURTIME()或CURRENT_TIME() 返回当前的时间(时分秒)

MariaDB [m4]> select CURTIME();

+-----------+

| CURTIME() |

+-----------+

| 10:29:54 |

+-----------+

1 row in set (0.01 sec)

MariaDB [m4]> select CURRENT_TIME();

+----------------+

| CURRENT_TIME() |

+----------------+

| 10:30:06 |

+----------------+

3、DATE_ADD(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化)

年：year

月：month

日：day

时：hour

分钟：minute

秒：second

#输出当前时间减去3小时后的时间

MariaDB [m4]> select date_add('2018-11-27 10:21:22',interval -3 hour);

+--------------------------------------------------+

| date_add('2018-11-27 10:21:22',interval -3 hour) |

+--------------------------------------------------+

| 2018-11-27 07:21:22 |

+--------------------------------------------------+

4、DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值

MariaDB [m4]> select DATE_FORMAT('2018-11-27 07:21:22','%Y+%m=%d');

+-----------------------------------------------+

| DATE_FORMAT('2018-11-27 07:21:22','%Y+%m=%d') |

+-----------------------------------------------+

| 2018+11=27 |

+-----------------------------------------------+

五、加密函数

1、AES_ENCRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储

MariaDB [m4]> SELECT AES_ENCRYPT('root','key');

+---------------------------+

| AES_ENCRYPT('root','key') |

+---------------------------+

|

̐ᇸ񎘧i |

+---------------------------+

2、AES_DECRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果

3、DECODE(str,key) 使用key作为密钥解密加密字符串str

4、 ENCRYPT(str,salt) 使用UNIX crypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str

MariaDB [m4]> SELECT ENCRYPT('root','salt');

+------------------------+

| ENCRYPT('root','salt') |

+------------------------+

| saFKJij3eLACw |

+------------------------+

5、ENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储

MariaDB [m4]> SELECT ENCODE('xufeng','key');

+------------------------+

| ENCODE('xufeng','key') |

+------------------------+

| В |

+------------------------+

6、MD5() 计算字符串str的MD5校验和(常用)

MariaDB [m4]> select md5('a');

+----------------------------------+

| md5('a') |

+----------------------------------+

| 0cc175b9c0f1b6a831c399e269772661 |

+----------------------------------+

7、PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。(常用)

MariaDB [m4]> select password('a');

+-------------------------------------------+

| password('a') |

+-------------------------------------------+

| *667F407DE7C6AD07358FA38DAED7828A72014B4E |

+-------------------------------------------+

8、SHA() 计算字符串str的安全散列算法(SHA)校验和(常用)

MariaDB [m4]> select sha('a');

+------------------------------------------+

| sha('a') |

+------------------------------------------+

| 86f7e437faa5a7fce15d1ddcb9eaeaea377667b8 |

+------------------------------------------+