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

最新推荐文章于 2024-07-23 04:30:00 发布

Jaystrong

最新推荐文章于 2024-07-23 04:30:00 发布

阅读量1.2k

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/jaystrong/article/details/70880480

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

在本文将总结概括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)