MySQL数据库——基础运算符和函数总结(一步到位)

由于本文较长，可以直接将以下‘分标题’名称复制下来，然后ctrl+f查找(chrome)直接跳转到指定标题（原谅本博主，markdown的当页跳转技巧渣渣，待学习，沉思…）
1.字符函数
2.数值运算符和函数
3.比较运算符和函数
4.日期时间函数
5.信息函数
6.聚合函数
7.加密函数

1.字符函数

--CONCAT()，字符连接

--多个连接之间用逗号隔开
SELECT CONCAT('l','_','SQL');
--例（假设以下示例前提条件都已满足）:
SELECT CONCAT(f_name,l_name) AS fullname FROM students;

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

--第一个为指定分割符，后面为连接字符
SELECT CONCAT_WS('_','A','B');

--FORMAT()，数字格式化

--最后的参数是保留小数点后数位(0即为保留整数)
SELECT FORMAT(12345.666,2);

--LOWER()，转换为小写字母
--UPPER()，转换为大写字母

SELECT LOWER('SQL');
SELECT UPPER('sql');

--LEFT()，获取左侧字符
--RIGHT()，获取右侧字符

--最后的参数是从左边开始获取几位
SELECT LEFT('MySQL',3);
SELECT RIGHT('MySQL',3);

--LENGTH()，获取字符串长度

--空格也算在长度之内
SELECT LENGTH('  SQL  ');

--LTRIM()，删除前导空格
--RTRIM()，删除后续空格

--可以配合LENGTH()查看是否已删除了空格
SELECT LENGTH(LTRIM('  SQL '));
SELECT LENGTH(RTRIM('  SQL '));

--TRIM()，删除前导和后续空格，还可以定制

--配合LENGTH()查看是否已删除了空格
SELECT LENGTH(TRIM('  SQL '));

--定制删除字符
--删除指定前导的字符
SELECT TRIM(LEADING '-' FROM '-|MYSQL|-');
--删除指定后续的字符
SELECT TRIM(TRAILING '-' FROM '-|MYSQL|-');
--删除指定全部的字符(中间的字符无法去除，REPLACE()可以,下面有示例)
SELECT TRIM(BOTH '-' FROM '-|MY--SQL|-');

--SUBSTRING()，字符串截取

--第一个参数为字符串，第二个为从第几位开始截取，第三个为截取几个字符
SELECT SUBSTRING('abcde',1,2);
--！特别注意的是！，SQL是从1开始计算，而非常见编程语言中的0开始

--若是没有指定第三个参数，则从截取位置开始，一直截取到结尾
SELECT SUBSTRING('abcde',1);

--索引位置支持使用负数(倒数第一位)
SELECT SUBSTRING('abcde',-1);

--第三个参数为负数时
SELECT SUBSTRING('abcde',-1,-3);
--结果为空，而不是error
--但注意MySQL是不支持这种格式的，但是某些编程语言支持，所以这里要注意

--[NOT] LIKE，模式匹配

--'%'相当于'*'，任意零个或多个字符
--ps.'_'下划线，代表任意一个字符
SELECT 'MySQL' LIKE 'M%';  --返回1,即true


--取得匹配字符中含有%的全部数据(假设已满足前提条件)

--错误示例(SQL会以为全部都是通配符，不会格外区分%):
SELECT * FROM table WHERE test LIKE '%%%';

--参考示例(1为随便加的字符，旨在让SQL理解操作者意图)
SELECT * FROM table WHERE test LIKE '%1%%' ESCAPE '1';
--ESCAPE 告诉SQL，’1’后面的‘%’不再认为是通配符了，直接认为是普通百分号

--REPLACE()，字符串替换

--第一个参数为字符串，第二个为欲替换字符，第三个为替换后字符
SELECT REPLACE('--M-Y-S-Q-L--','-',' * ');
--若第三个替换字符为''，则可理解为去除字符，而不是替换为新的字符

2.数值运算符和函数

--CEIL()，进一取整(向上取整)
--FLOOR()，舍一取整(向下取整)

SELECT CEIL(3.1);  --返回4
SELECT FLOOR(3.99);  --返回3

--DIV，整数除法
--MOD，取余数(取模)

--除法
SELECT 3/4;  --返回0.75
SELECT 3 DIV 4; --返回0

--取余
SELECT 5 % 3;  --返回2
SELECT 5.2 MOD 3;  --返回2.2

--ROUND()，四舍五入

--第二个参数为保留小数点后几位(0即为保留到整数)
SELECT ROUND(3.616,2);  --返回3.62

--POWER()，幂运算

--2的3次方
SELECT POWER(2,3);

--TRUNCATE()，数字截取

--截取数字，并不发生四舍五入
SELECT TRUNCATE(123.456,2);  --返回123.45

3.比较运算符和函数

--[NOT] BETWEEN .. AND ..，[不]在范围之内

--指定一个范围，只要选择的数字在这个范围内即可
SELECT 10 BETWEEN 1 AND 20;  --返回1
SELECT 30 BETWEEN 1 AND 20;  --返回0
SELECT 30 NOT BETWEEN 1 AND 20;  --返回1

--[NOT] IN()，[不]在列出值范围内

--指定列出值，只要选择的数字存在其中即可
SELECT 10 IN(5,10,15,20);  --返回1
SELECT 11 IN(5,10,15,20);  --返回0

--IS [NOT] NULL，[不]为空

SELECT NULL IS NULL;  --返回1
SELECT '' IS NULL;  --返回0
SELECT 0 IS NOT NULL;  --返回1

4.日期时间函数

--NOW()，当前日期和时间
--CURDATE()，当前日期
--CURTIME()，当前时间

SELECT NOW();
SELECT CURTIME();
SELECT CURDATE();

--DATE_ADD()，日期变化(可增加或减少)

--在指定日期基础上，加上365天
SELECT DATE_ADD('2017-1-1',INTERVAL 365 DAY);
--在指定日期基础上，减去365天
SELECT DATE_ADD('2017-1-1',INTERVAL -365 DAY);
--在指定日期基础上，加上5个星期
SELECT DATE_ADD('2017-1-1',INTERVAL 5 WEEK);

--DATEDIFF()，日期差值

--指定两个日期，得出差值
SELECT DATEDIFF('2017-1-1','2016-1-1');

--DATE_FORMAT()，日期格式化

--'%m'代表月，'%d'代表日，'%Y'代表年
SELECT DATE_FORMAT('2017-1-1','%m/%d/%Y');

5.信息函数

--CONNECTION_ID()，连接ID数

SELECT CONNECTION_ID();

--DATEBASE()，当前数据库

SELECT DATEBASE();

--LAST_INSERT_ID()，最后插入记录的ID号
--!注意，要得到这个信息，要表中存在一个自动编号的字段(名称不一定为ID，主键)

SELECT LAST_INSERT_ID();
--注意，若是同时插入多条记录时，返回的是第一条记录的ID

--USER()，当前用户

SELECT USER();

--VERSION()，版本信息

SELECT VERSION();

6.聚合函数

聚合函数特点：只有一个返回数

--AVG()，平均值

--错误示例
SELECT AVG(3,4,5);

--示例（假设以下示例前提条件都已满足）
SELECT AVG(price) FROM table;

--COUNT()，计数

--示例（假设以下示例前提条件都已满足）
SELECT COUNT(id) FROM table;

--MAX()，最大值
--MIN()，最小值

--示例（假设以下示例前提条件都已满足）
SELECT MAX(price) FROM table;
SELECT MIN(price) FROM table;

--SUM()，求和

--示例（假设以下示例前提条件都已满足）
SELECT SUM(price) FROM table;

7.加密函数

--MD5()，信息摘要算法，web中尽量使用MD5

SELECT MD5('123456');

--PASSWORD(),密码算法，主要用于修改客户端密码

SELECT PASSWORD('123456');

--修改自己客户端的密码
SET PASSWORD=PASSWORD('666666');