MySQL 运算符和函数

字符函数

CONCAT() 字符连接

SELECT CONCAT('a','-','b');
--结果为: a-b

CONCAT_WS() 使用指定的分隔符进行字符连接

SELECT CONCAT_WS('|','A','B','C'); 
--结果为： A|B|C

FORMAT() 数字格式化

对数字四舍五入,返回字符串,包括逗号','

SELECT FORMAT(12560.7,2); 
--结果：12,560.70
SELECT FORMAT(12560.78,1); 
--结果：12,560.8

LOWER() 转换成小写字母

UPPER() 转换成大写字母

LEFT() 截取左侧字符

SELECT LEFT('mysql',2);  
--结果：my

RIGHT() 截取右侧字符

LENGTH() 获取字符串长度

长度包含空格

LTRIM() 删除前导空格

等同于LEFT TRIM()

RTRIM() 删除后续空格

TRIM() 删除前后两边的指定字符(默认空格)

删除指定的前导和后续的字符,但不能删除中间的字符,如

SELECT TRIM(LEADING'?','??MYSQL????');  
--leading前导,结果：MYSQL????
SELECT TRIM(TRAILING'?','??MYSQL????');  
--trailing后序,结果：??MYSQL
SELECT TRIM(BOTH'?','??MYSQL???'); 
--结果：MYSQL

REPLACE() 替换字符

如将'?'替换为'-'

SELECT REPLACE('??MYSQL???','?','-');  
--结果：--MYSQL---

SUBSTRING(string，offset，length) 截取字符串

SELECT SUBSTRING('MYSQL',2,3);  
--结果：SQL

[NOT] LIKE 模糊匹配

ESCAPE可指定转义字符

• % 代表任意个字符，0个或多个
• _ 代表任意一个字符，只有一个

SELECT name FROM test WHERE name LIKE'%o%'; 
--结果：输入name 中带‘o’的name
SELECT name FROM test WHERE name LIKE'%1%%' ESCAPE '1';
--找到中间带% 的匹配name

数值运算

CEIL(数值) 向上取整

SELECT CEIL(3.01);
--结果是4

FLOOR(数值) 向下取整

SELECT FLOOR(3.99);
--结果是3;

DIV 除法,保留整数

如果使用'',如'3/4'结果为'0.75'

SELECT 3 DIV 4; 
--结果是0；因为3除以4，整数位为0

MOD 取模

相当于'%'取余运算符，可以用%号代替；

SELECT 4 MOD 3; 
--结果为1；
SELECT 5.3 MOD 3; 
--结果为2.3

POWER(数值,数值) 幂运算

SELECT POWER(3,3); 
--结果为27

ROUND(数值,小数的位数) 四舍五入

TRUNCATE(数值,截取位数)

和ROUND()类似,不四舍五入,直接截断,截取位数可以是负数，

SELECT TRUNCATE(125.68,-1); 
--结果为120

比较运算

[NOT] BETWEEN ... AND ...

SELECT 15 BETWEEND 1 AND 20 
-- 15在1到20之间 ,返回值是1
SELECT 15 NOT BETWEEND 1 AND 20 
--15在1到20之间,条件不成立 返回值是0

[NOT] IN()

判断值是否在给定的集合中,如果在返回1,不在返回0,或者相反

SELECT 10 IN(5,10,15) 
-- 返回1 
SELECT 10 NOT(5,10,15) 
-- 返回0 

IS [NOT] NULL

是否为NULL,成立返回1,不成立返回0

SELECT NULL IS NULL 
-- 返回1 
SELECT '' IS NULL
-- 返回0 , 除了NULL其它都是非空 返回都是1

聚合函数

聚合函数只有一个返回值

AVG() 平均值

SELECT ROUND(AVG(goods_price),2) AS avg_price 
FROM tdb_goods;

COUNT() 计数

SELECT COUNT(goods_id) AS counts 
FROM tdb_goods;

MAX() 最大值

SELECT MAX(goods_price) AS max 
FROM tdb_goods;

MIN() 最小值

SUM() 求和

SELECT SUM(goods_price) AS sum 
FROM tdb_goods;

加密函数

MD5() 摘要算法

SELECT MD5('admin'); 

PASSWORD() 密码算法

通过PASSWORD()修改MySQL当前用户和其他用户的密码

-- 把密码修改成dimitar。
SET PASSWORD=PASSWORD(‘dimitar’); 

日期时间函数

NOW() 当前日期,时间

CURDATE() 当前日期

CURTIME() 当前时间

DATE_ADD() 时间增减

INTERVAL可以为负值
单位 YEAR, MONTH, WEEK, DAY

SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY); 
-- 返回2015-3-12
-- 在原有给定的时间上增加365天 

DATEDIFF() 日期差值

单位为日,前面时间减去后面时间

SELECT DATEDIFF('2014-1-1','2015-1-1') 
-- 返回365

DATEDIFF() 日期格式化

SELECT DATE_FORMAT('2014-3-2','%m/%d/%Y');
-- 返回03/02/2014

内置信息函数

VERSION() MySQL版本信息

SELECT DATABASE() 当前数据库

USER() 当前用户

SELECT USER();

CONNECTION_ID() 当前用户的连接ID

SELECT CONNECTION_ID();

LAST_INSERT_ID() 最后插入的记录的 ID 号

ID为主键,必须自动编号AUTO_INCREMENT,可以不叫'ID'.
如果一次INSERT插入的是多条记录，得到的是多条记录中的第一条（而不是最后一条！）