MySQL 运算符和函数

MySQL 中的函数。

1. 字符函数

常用字符函数如下：

用法举例：

字符连接：mysql> SELECT CONCAT(‘Hello’, ‘World’);

对于下表：

连接 name 和 age 字段：mysql> SELECT CONCAT(username, ‘:’, age) FROM users;

用指定分隔符的函数（其中第一个为分隔符）：mysql> SELECT CONCAT_WS(‘|’ ,username, age) FROM users;

数字格式化函数：mysql> SELECT FORMAT(1234.56,1);

PS: 这么做会进行四舍五入保留相应的位数。

获取左侧字符：mysql> SELECT LEFT(‘MySQL’,2);

注：参数为字符串和截取位数；获取右侧字符用法类似。

函数嵌套使用（截取左侧两个字符，并转换为小写）： mysql> SELECT LOWER(LEFT(‘MySQL’,2));

其他一些字符函数：

删除特殊字符：

• 删除前面的特殊字符： mysql> SELECT TRIM(LEADING ‘?’ FROM ‘??MySQL??’);
  

• 删除后面的特殊字符： mysql> SELECT TRIM(TRAILING ‘?’ FROM ‘??MySQL??’);
  

• 前后都删除： mysql> SELECT TRIM(BOTH ‘?’ FROM ‘??MySQL??’);
  

替换：
例如： mysql> SELECT REPLACE(‘??My??SQL??’, ‘?’, ”);

注意：可以一对多或多对一，即一个字符可替换为多个字符，或多个字符替换为一个字符。

字符串截取：
例如，从第一个开始，截取两位： mysql> SELECT SUBSTRING(‘MySQL’, 1, 2);

注意：这里的字符串从1开始，而非0。

若未给出截取多少位，则截取到末尾。例如：
mysql> SELECT SUBSTRING(‘MySQL’, 3);

起始位置可以为负数，从右往左依次为-1， -2…例如：
mysql> SELECT SUBSTRING(‘MySQL’, -3);

注意：长度不能为负值。

[NOT]LIKE
mysql> SELECT ‘MySQL’ LIKE ‘M%’;

注：%表示0个或多个字符。

2. 数值运算符与函数

常用的数值运算符函数：

3. 比较运算符与函数

常用比较运算函数：

mysql> SELECT 20 BETWEEN 1 AND 10;

mysql> SELECT 20 BETWEEN 1 AND 100;

mysql> SELECT 12 IN (1, 2, 12);

mysql> SELECT NULL IS NULL;

mysql> SELECT ” IS NULL;

4. 日期时间函数

常用的日期时间函数：

查看当前时间：mysql> SELECT NOW();

日期加：
mysql> SELECT DATE_ADD(‘2015-7-14’, INTERVAL 365 DAY);

日期减：
mysql> SELECT DATE_ADD(‘2015-7-14’, INTERVAL -365 DAY);

单位还可以为 WEEK

日期差值计算：
mysql> SELECT DATEDIFF(‘2015-7-14’, ‘2016-7-14’);

mysql> SELECT DATE_FORMAT(‘2015-7-14’, ‘%m/%d/%Y’);

5. 信息函数

常见的信息函数：

mysql> SELECT LAST_INSERT_ID();
同时写入多条记录时，只返回第一条记录。

查看数据库版本：mysql> SELECT VERSION();

查看当前用户：mysql> SELECT USER();

6. 聚合函数

聚合函数：最大值、最小值、平均值、计数、求和等。
共同点：最终只返回一个结果。常用的聚合函数如下：

例如，原表：

查找结果：
mysql> SELECT gender, age FROM users GROUP BY 1 HAVING COUNT(id) = 2;

7. 加密函数

常用加密函数有以下两种：

建议：若为web页面做准备，尽量用MD5.

用法示例：
mysql> SELECT MD5(‘Hello’);

mysql> SELECT PASSWORD(‘Hello’);

此外，更改密码：mysql> SET PASSWORD=PASSWORD(‘hello’);

8. 自定义函数：

自定义函数是对 MySQL 的扩展，其用法与内置函数相同。但自定义函数用的并不多。

自定义函数两个必要条件：
1. 参数：零个或多个；
2. 返回值：只能有一个。

创建自定义函数示例：

不含参的函数：
mysql> SELECT DATE_FORMAT(NOW(), ‘%Y年%m月%d日 %H点:%i分:%s秒’);

定义函数：

mysql> CREATE FUNCTION f1() RETURNS VARCHAR(30)    -> RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点:%i分:%s秒');

使用定义的函数：

含参的函数：
定义：

mysql> CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)    -> RETURNS FLOAT(10, 2) UNSIGNED    -> RETURN (num1+num2)/2;

使用：
mysql> SELECT f2(1,2);

删除函数： mysql> DROP FUNCTION f1;

修改分隔符： mysql> DELIMITER //
注：可自定义

mysql> CREATE FUNCTION adduser(name VARCHAR(20))    -> RETURNS INT UNSIGNED    -> BEGIN    -> INSERT user(name) VALUES(name);    -> RETURN LAST_INSERT_ID();    -> END    -> //

使用：
mysql> SELECT adduser(‘Jack’)//

慕课网 MySQL学习笔记