mysql运算符和常用函数+自定义函数

来源：互联网发布：淘宝皇冠值多少钱一个编辑：程序博客网时间：2024/06/08 13:38

在本文将总结概括mysql的运算符和常用函数，自定义函数

常用函数

字符函数
数值运算符函数
比较运算符和函数
日期时间函数
信息函数
聚合函数（用于数据表中）
加密函数

1. 字符函数

函数名解释 CONCAT() 字符连接 CONCAT_WS() 使用制定的分隔符进行字符连接 FORMAT() 数字格式化 LOWER() 转换未小写字母 UPPER() 转换成大写字母 LEFT() 获取左侧字符 RIGHT() 获取右侧字符 LENGTH() 获取字符串长度 LTRIM() 删除前导空格 RTRIM() 删除后导和后续空格 SUBSTRING() 字符串截取 [NOT]LIKE 模式匹配（%任意个字符，_任意一个字符） REPLACE() 字符串替换

CONCAT()mysql> select concat('imooc','mysql');+-------------------------+| concat('imooc','mysql') |+-------------------------+| imoocmysql              |+-------------------------+1 row in set (0.04 sec)mysql> select concat('imooc','-','mysql');+-----------------------------+| concat('imooc','-','mysql') |+-----------------------------+| imooc-mysql                 |+-----------------------------+1 row in set (0.00 sec)mysql> select concat_ws('-','imooc','51cto','bei');+--------------------------------------+| concat_ws('-','imooc','51cto','bei') |+--------------------------------------+| imooc-51cto-bei                      |+--------------------------------------+1 row in set (0.00 sec)mysql> select lower('MYSQL');+----------------+| lower('MYSQL') |+----------------+| mysql          |+----------------+1 row in set (0.00 sec)mysql> select upper('mysQl');+----------------+| upper('mysQl') |+----------------+| MYSQL          |+----------------+1 row in set (0.00 sec)mysql> select left('mysql',3);+-----------------+| left('mysql',3) |+-----------------+| mys             |+-----------------+1 row in set (0.00 sec)mysql> select lower(left('MYSQL',2));+------------------------+| lower(left('MYSQL',2)) |+------------------------+| my                     |+------------------------+1 row in set (0.00 sec)mysql> select right('mysql',1);+------------------+| right('mysql',1) |+------------------+| l                |+------------------+1 row in set (0.00 sec)mysql> select length('mysql');+-----------------+| length('mysql') |+-----------------+|               5 |+-----------------+1 row in set (0.03 sec)mysql> select ltrim('   mysql  ');+---------------------+| ltrim('   mysql  ') |+---------------------+| mysql               |+---------------------+1 row in set (0.00 sec)mysql> select ltrim('   mysql b  ');+-----------------------+| ltrim('   mysql b  ') |+-----------------------+| mysql b               |+-----------------------+1 row in set (0.00 sec)mysql> select length(ltrim('  mysql   '));+-----------------------------+| length(ltrim('  mysql   ')) |+-----------------------------+|                           8 |+-----------------------------+1 row in set (0.00 sec)mysql> select length(rtrim('  mysql   '));+-----------------------------+| length(rtrim('  mysql   ')) |+-----------------------------+|                           7 |+-----------------------------+1 row in set (0.00 sec)mysql> select trim(leading '?' from '???mysql???');+--------------------------------------+| trim(leading '?' from '???mysql???') |+--------------------------------------+| mysql???                             |+--------------------------------------+1 row in set (0.00 sec)mysql> select trim(trailing '?' from '???mysql???');+---------------------------------------+| trim(trailing '?' from '???mysql???') |+---------------------------------------+| ???mysql                              |+---------------------------------------+1 row in set (0.00 sec)mysql> select trim(both '?' from '???mysql???');+-----------------------------------+| trim(both '?' from '???mysql???') |+-----------------------------------+| mysql                             |+-----------------------------------+1 row in set (0.00 sec)mysql> select trim(both '?' from '???my??sql???');+-------------------------------------+| trim(both '?' from '???my??sql???') |+-------------------------------------+| my??sql                             |+-------------------------------------+1 row in set (0.00 sec)mysql> select replace('??mysql??sql??','?','');+----------------------------------+| replace('??mysql??sql??','?','') |+----------------------------------+| mysqlsql                         |+----------------------------------+1 row in set (0.00 sec)mysql> select replace('??mysql??sql??','?','2');+-----------------------------------+| replace('??mysql??sql??','?','2') |+-----------------------------------+| 22mysql22sql22                    |+-----------------------------------+1 row in set (0.00 sec)mysql> select replace('??mysql??sql??','??','2');+------------------------------------+| replace('??mysql??sql??','??','2') |+------------------------------------+| 2mysql2sql2                        |+------------------------------------+1 row in set (0.00 sec)mysql> select substring('mysql',1,2);+------------------------+| substring('mysql',1,2) |+------------------------+| my                     |+------------------------+1 row in set (0.00 sec)mysql> select substring('mysql',3);+----------------------+| substring('mysql',3) |+----------------------+| sql                  |+----------------------+1 row in set (0.00 sec)mysql> select substring('mysql',-1);+-----------------------+| substring('mysql',-1) |+-----------------------+| l                     |+-----------------------+1 row in set (0.02 sec)mysql> select substring('mysql',-3,2);+-------------------------+| substring('mysql',-3,2) |+-------------------------+| sq                      |+-------------------------+

2. 数值运算符函数

函数名解释 CEIL() 进一取整 DIV 整数除法 FLOOR() 舍一取整 MOD 取余数（取模） POWER() 幂运算 ROUND() 四舍五入 TRUNCATE() 数字截取

mysql> select ceil(3.01);+------------+| ceil(3.01) |+------------+|          4 |+------------+1 row in set (0.04 sec)mysql> select floor(3.9999);+---------------+| floor(3.9999) |+---------------+|             3 |+---------------+1 row in set (0.00 sec)mysql> select 3/4;+--------+| 3/4    |+--------+| 0.7500 |+--------+1 row in set (0.00 sec)mysql> select 3 div 4;+---------+| 3 div 4 |+---------+|       0 |+---------+1 row in set (0.00 sec)mysql> select 5%3;+------+| 5%3  |+------+|    2 |+------+1 row in set (0.00 sec)mysql> select 5 mod 3;+---------+| 5 mod 3 |+---------+|       2 |+---------+1 row in set (0.00 sec)mysql> select power(2,3);+------------+| power(2,3) |+------------+|          8 |+------------+1 row in set (0.05 sec)mysql> select round(3.5554,2);+-----------------+| round(3.5554,2) |+-----------------+|            3.56 |+-----------------+1 row in set (0.00 sec)mysql> select round(4.544,0);+----------------+| round(4.544,0) |+----------------+|              5 |+----------------+1 row in set (0.00 sec)mysql> select truncate(125.89,2);+--------------------+| truncate(125.89,2) |+--------------------+|             125.89 |+--------------------+1 row in set (0.00 sec)mysql> select truncate(125.89,1);+--------------------+| truncate(125.89,1) |+--------------------+|              125.8 |+--------------------+1 row in set (0.00 sec)mysql> select truncate(125.89,0);+--------------------+| truncate(125.89,0) |+--------------------+|                125 |+--------------------+1 row in set (0.00 sec)

3. 比较运算符和函数

函数名解释 [NOT] BETWEEN…AND… [不]在范围之内 [NOT] IN() [不]在列出值范围内 IS [NOT] NULL [不]为空

mysql> select 15 between 1 and 22;+---------------------+| 15 between 1 and 22 |+---------------------+|                   1 |+---------------------+1 row in set (0.00 sec)mysql> select 35 between 1 and 22;+---------------------+| 35 between 1 and 22 |+---------------------+|                   0 |+---------------------+1 row in set (0.00 sec)mysql> select 35 not between 1 and 22;+-------------------------+| 35 not between 1 and 22 |+-------------------------+|                       1 |+-------------------------+1 row in set (0.00 sec)mysql> select 10 in (15,10,12);+------------------+| 10 in (15,10,12) |+------------------+|                1 |+------------------+1 row in set (0.00 sec)mysql> select 100 in (15,10,12);+-------------------+| 100 in (15,10,12) |+-------------------+|                 0 |+-------------------+1 row in set (0.00 sec)mysql> select 100 not in (15,10,12);+-----------------------+| 100 not in (15,10,12) |+-----------------------+|                     1 |+-----------------------+1 row in set (0.00 sec)

4. 日期时间函数

函数名解释 NOW() 当前日期和时间 CURDATE() 当前日期 CURTIME() 当前时间 DATE_ADD() 日期变化 DATEDIFF() 日期差值 DATE_FORMAT() 日期格式化

mysql> select now();+---------------------+| now()               |+---------------------+| 2017-04-27 20:52:45 |+---------------------+1 row in set (0.07 sec)mysql> select curdate();+------------+| curdate()  |+------------+| 2017-04-27 |+------------+1 row in set (0.03 sec)mysql> select curtime();+-----------+| curtime() |+-----------+| 20:53:13  |+-----------+1 row in set (0.00 sec)mysql> select date_add('2014-3-12',interval 365 day);+----------------------------------------+| date_add('2014-3-12',interval 365 day) |+----------------------------------------+| 2015-03-12                             |+----------------------------------------+1 row in set (0.03 sec)mysql> select date_add('2014-3-12',interval 5 week);+---------------------------------------+| date_add('2014-3-12',interval 5 week) |+---------------------------------------+| 2014-04-16                            |+---------------------------------------+1 row in set (0.00 sec)mysql> select datediff('2013-3-12','2014-5-12');+-----------------------------------+| datediff('2013-3-12','2014-5-12') |+-----------------------------------+|                              -426 |+-----------------------------------+1 row in set (0.03 sec)mysql> select date_format('2017-3-12','%m/%d/%Y');+-------------------------------------+| date_format('2017-3-12','%m/%d/%Y') |+-------------------------------------+| 03/12/2017                          |+-------------------------------------+1 row in set (0.00 sec)

5. 信息函数

函数名解释 CONNECTION_ID() 连接ID DATABASE() 当前数据库名称 LAST_INSERT_ID() 最后插入记录的ID号 USER() 当前用户 VERSION() 版本信息

6. 聚合函数（用于数据表中）

函数名解释 AVG() 平均值 COUNT() 计数 MAX() 最大值 MIN() 最小值 SUM() 求和

7. 加密函数

函数名解释 MD5() 信息摘要算法 PASSWORD() 密码算法

自定义函数

CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])RETURNS {STRING|INTEGER|REAL}runtime_body

自定义函数的两个必要条件
1）参数（最多可以有1024个，实际开发中不需要那么多）
2）返回值（所有的函数都有返回值，包括自定义函数，返回值可以是任意类型的值）

关于函数体
1）函数体由合法的SQL语句构成；
2）函数体可以是简单的SELECT或INSERT语句；
3）函数体如果为复合结构则使用BEGIN…END语句；
4）复合结构可以包含声明，循环，控制结构

删除函数

DROP FUNCTION [IF EXISTS] function_name

一、创建一个不带参数的自定义函数

mysql> create function f1() returns varchar(30)    -> return date_format(now(),'%Y年%m月%d日 %H点:%i分:%s秒');Query OK, 0 rows affected (0.08 sec)mysql> select f1();+-------------------------------+| f1()                          |+-------------------------------+| 2017年04月27日 21点:35分:47秒 |+-------------------------------+1 row in set (0.03 sec)

二、创建带参数的自定义函数

mysql> create function f2(num1 smallint unsigned,num2 smallint unsigned)    -> returns float(10,2) unsigned    -> return (num1+num2)/2;Query OK, 0 rows affected (0.02 sec)mysql> select f2(2,3);+---------+| f2(2,3) |+---------+|    2.50 |+---------+1 row in set (0.02 sec)

三、创建具有复合结构函数体的自定义函数

mysql> create function addArticle(a_title varchar(20),a_content varchar(20),a_uid tinyint(1))    -> returns int unsigned    -> BEGIN    -> insert article (title,content,uid) values (a_title,a_content,a_uid);    -> return last_insert_id();    -> END    -> //Query OK, 0 rows affected (0.03 sec)mysql> select addArticle('标题','内容','3');+-------------------------------+| addArticle('标题','内容','3') |+-------------------------------+|                             5 |+-------------------------------+1 row in set (0.08 sec)

0 0