一、常用函数大全
1. 数学函数
ABS(x)返回x的绝对值BIN(x)返回x的二进制（OCT返回八进制，HEX返回十六进制）CEILING(x)返回大于x的最小整数值——向上取整EXP(x)返回值e（自然对数的底）的x次方
FLOOR(x)返回小于x的最大整数值——向下取整GREATEST(x1,x2,...,xn)返回集合中最大的值LEAST(x1,x2,...,xn)返回集合中最小的值LN(x)返回x的自然对数LOG(x,y)返回x的以y为底的对数MOD(x,y)返回x/y的模（余数）PI()返回pi的值（圆周率）RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。ROUND(x,y)返回参数x的四舍五入的有y位小数的值SIGN(x)返回代表数字x的符号的值SQRT(x)返回一个数的平方根POWER(Value,N)— 返回Value的N次方；POWER(Value,1/N)— 返回Value的N次方TRUNCATE(x,y)返回数字x截短为y位小数的结果
2. 聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值COUNT(col)返回指定列中非NULL值的个数MIN(col)返回指定列的最小值MAX(col)返回指定列的最大值SUM(col)返回指定列的所有值之和GROUP_CONCAT(col)返回由属于一组的列值连接组合而成的结果
3. 字符串函数
ASCII(char)返回字符的ASCII码值BIT_LENGTH(str)返回字符串的比特长度CONCAT(s1,s2...,sn)将s1,s2…,sn连接成字符串CONCAT_WS(sep,s1,s2...,sn)将s1,s2…,sn连接成字符串，并用sep字符间隔INSERT(str,x,y,instr)将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果FIND_IN_SET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置LCASE(str)或LOWER(str)返回将字符串str中所有字符改变为小写后的结果LEFT(str,x)返回字符串str中最左边的x个字符LENGTH(s)返回字符串str中的字符数LTRIM(str)从字符串str中切掉开头的空格POSITION(substr,str)返回子串substr在字符串str中第一次出现的位置QUOTE(str)用反斜杠转义str中的单引号REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果REVERSE(str)返回颠倒字符串str的结果RIGHT(str,x)返回字符串str中最右边的x个字符RTRIM(str)返回字符串str尾部的空格STRCMP(s1,s2)比较字符串s1和s2TRIM(str)去除字符串首部和尾部的所有空格UCASE(str)或UPPER(str)返回将字符串str中所有字符转变为大写后的结果
4. 日期和时间函数
CURDATE()或CURRENT_DATE()返回当前的日期CURTIME()或CURRENT_TIME()返回当前的时间DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化), keyword可为任意时间间隔类型；如：SELECT DATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);DATE_FORMAT(date,fmt)依照指定的fmt格式格式化日期date值，fmt格式；常用：‘%Y-%m-%d’ , 年-月-日
| 格式 | 描述 |
|---|---|
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值(00-31) |
| %e | 月的天，数值(0-31) |
| %f | 微秒 |
| %H | 小时 (00-23) |
| %h | 小时 (01-12) |
| %I | 小时 (01-12) |
| %i | 分钟，数值(00-59) |
| %j | 年的天 (001-366) |
| %k | 小时 (0-23) |
| %l | 小时 (1-12) |
| %M | 月名 |
| %m | 月，数值(00-12) |
| %p | AM 或 PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒(00-59) |
| %s | 秒(00-59) |
| %T | 时间, 24-小时 (hh:mm:ss) |
| %U | 周 (00-53) 星期日是一周的第一天 |
| %u | 周 (00-53) 星期一是一周的第一天 |
| %V | 周 (01-53) 星期日是一周的第一天，与 %X 使用 |
| %v | 周 (01-53) 星期一是一周的第一天，与 %x 使用 |
| %W | 星期名 |
| %w | 周的天 （0=星期日, 6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
DATE_SUB(date,INTERVAL int keyword)返回日期date减去间隔时间int的结果(int必须按照关键字进行格式化), keyword可为任意时间间隔类型；如：SELECT DATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);DAYOFWEEK(date)返回date所代表的一星期中的第几天(1~7)DAYOFMONTH(date)返回date是一个月的第几天(1~31)DAYOFYEAR(date)返回date是一年的第几天(1~366)DAYNAME(date)返回date的星期名(Sun…)，如：SELECT DAYNAME(CURRENT_DATE);FROM_UNIXTIME(ts,fmt)根据指定的fmt格式，格式化UNIX时间戳tsHOUR(time)返回time的小时值(0~23)MINUTE(time)返回time的分钟值(0~59)MONTH(date)返回date的月份值(1~12)MONTHNAME(date)返回date的月份名(Jan…)，如：SELECT MONTHNAME(CURRENT_DATE);NOW()返回当前的日期和时间QUARTER(date)返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);WEEK(date)返回日期date为一年中第几周(0~53)YEAR(date)返回日期date的年份(1000~9999)EXTRACT(date)Extract() 函数除了没有date(),time() 的功能外，其他功能一应具全。period_add(P,N)日期加/减去N月。period_diff(P1,P2)日期 P1-P2，返回 N 个月datediff(date1,date2)两个日期相减 date1 - date2，返回天数TIMEDIFF(time1,time2)两个日期相减 time1 - time2，返回 TIME 差值TIMESTAMPDIFF(DAY/MONTH/YEAR/WEEK/MINUTE,time1,time2)两个日期相减 time1 - time2，返回相应日期类型的差值TO_DAYS(date)给出一个日期 date，返回一个天数(从 0 年开始的天数)FROM_DAYS(N)给出一个天数 N，返回一个 DATE 值
一些示例：
1. 返回两个日期值之间的差值(月数)：
1 | SELECT PERIOD_DIFF(200302,199802); |
2. 在Mysql中计算年龄：
1 | SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee; |
注：此处利用to_days()将年龄转换为从0年经过的时间，再用date_format求出年份即为年龄。
3. EXTRACT()提取函数
1 | SELECT EXTRACT(YEAR FROM '2017-05-15 10:37:14.123456'); -- 年 |
5. 控制流函数
CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回defaultCASE [test] WHEN[val1] THEN [result]...ELSE [default]END如果test和valN相等，则返回resultN，否则返回defaultIF(test,t,f)如果test是真，返回t；否则返回fIFNULL(arg1,arg2)如果arg1不是空，返回arg1，否则返回arg2NULLIF(arg1,arg2)如果arg1=arg2返回NULL；否则返回arg1
这些函数的第一个是IFNULL()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是NULL，函数就会向调用者返回第一个参数；如果是NULL,将返回第二个参数。
如：SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');
NULLIF()函数将会检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等，就返回第一个参数。
如：SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1);
和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件测试，这个函数有三个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数，如果为假，IF()将会返回第三个参数。
如：SELECT IF(1<10,2,3),IF(56>100,'true','false');
IF()函数在只有两种可能结果时才适合使用。然而，在现实世界中，我们可能发现在条件测试中会需要多个分支。在这种情况下，MySQL提供了CASE函数，它和PHP及Perl语言的switch-case条件例程一样。
IFNULL(arg1, arg2)解决Mysql聚合函数count(1) sum(1)结果返回0和NULL
count(1)
返回为0
如果所查询的表或者where条件筛选后得到的结果集为空，则 count(1)返回为 0
如：
select count(id) from test;
select count(id) from test where id < 0;返回为NULL
如果所查询的表或者where条件筛选后得到的结果集为空且当前层查询中使用了group by ，则 count(1)返回为 NULL
如：
select count(id) from test group by id;
select count(id) from test where id < 0 group by id;
sum(1)
返回为NULL
如果所查询的表或者where条件筛选后得到的结果集为空 ，则 sum(1)返回为 NULL
如：
select sum(id) from test;
select sum(id) from test where id < 0;
注：
如果想NULL转为0返回，可以使用IFNULL（expression_1，expression_2）；表示如果expression_1不为NULL，则IFNULL函数返回expression_1；否则返回expression_2的结果。如IFNULL（sum（id），0）
CASE函数的格式有些复杂，通常如下所示：
1. 类似于java中点switch语句，一般用于实现等值判断
1 | CASE [expression to be evaluated] |
2. 类似于java中的多重if语句，一般用于实现区间判断
1 | CASE |
3. 例子
1 | -- 数值判断 |
1 | -- IF()函数 |
6. 格式化函数
DATE_FORMAT(date,fmt)依照字符串fmt格式化日期date值FORMAT(x,y)把x格式化为以逗号隔开的数字序列，y是结果的小数位数INET_ATON(ip)返回IP地址的数字表示INET_NTOA(num)返回数字所代表的IP地址TIME_FORMAT(time,fmt)依照字符串fmt格式化时间time值
1 | SELECT FORMAT(34234.34323432,3); |
二、存储过程
1. 创建存储过程
创建存储过程的语句为 CREATE PROCEDURE，创建存储函数的语句为CREATE FUNCTION。
调用存储过程的语句为CALL。
调用存储函数的形式就像调用MySQL内部函数一样。
1. 创建存储过程示例
1. 创建存储过程简单示例：
1
2
3
4
5
6
7
8
9
10DELIMITER //
CREATE PROCEDURE ccgc()
BEGIN
SELECT * FROM TEXT;
SELECT * FROM s_user;
END
//DELIMITER ;
-- 调用
CALL ccgc()
2. 创建带参数的存储过程：
1
2
3
4
5
6
7
8
9
10DELIMITER //
CREATE PROCEDURE ccgc2(IN c_name VARCHAR(32))
BEGIN
SELECT * FROM TEXT WHERE NAME = c_name;
SELECT * FROM s_user;
END
//DELIMITER ;
-- 调用
CALL ccgc2(参数)
3. 创建输出参数存储过程
1
2
3
4
5
6
7
8
9
10DELIMITER //
CREATE PROCEDURE ccgc3(OUT c_name VARCHAR(32))
BEGIN
SET c_name = '666';
SELECT c_name;
END
//DELIMITER ;
-- 调用
CALL ccgc3(@c_name)
4. 输入输出和变量
1
2
3
4
5
6
7
8
9DELIMITER //
CREATE PROCEDURE ccgc7(INOUT c_name VARCHAR(32))
BEGIN
DECLARE hh VARCHAR(32) DEFAULT '1111111111';
SELECT * FROM TEXT WHERE NAME = hh;
END
//DELIMITER ;
CALL ccgc7(@c_name)
5. 对变量的定义和赋值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16-- 定义
select @变量名 或者 select @变量名:= 字段名 from 表名 where 过滤语句;
set @变量名;
-- 赋值 @num为变量名，value为值
set @num=value;或select @num:=value;
-- 混合使用
declare i int; #申明变量
set i = 0; #变量赋值
-- 局部变量（只在begin...end之间有效）
declare var_name [, var_name]... data_type [ DEFAULT value ];
-- Select into进行赋值
select col_name[,...] into var_name[,...] table_expr [where...];在MySQL存储过程中使用SELECT …INTO语句为变量赋值：用来将查询返回的一行的各个列值保存到局部变量中。
要求：查询的结果集中只能有1行。
1、单一变量赋值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18-- 创建过程，得到指定球员的所有罚款总额
delimiter $$
create procedure total_penalties_player(in p_playerno int,out total_penalties dec(8,2))
begin
select sum(amount)
into total_penalties
from PENALTIES
where playerno=p_playerno;
end $$
delimiter ;
call total_penalties_player(27,@total);
select @total;
+--------+
| @total |
+--------+
| 175.00 |
+--------+2、多变量赋值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19-- 创建过程，得到一个给定球员的地址
DELIMITER $$
CREATE PROCEDURE get_address(IN p_playerno SMALLINT,OUT p_street VARCHAR(30),
OUT p_houseno VARCHAR(4),OUT p_town VARCHAR(30),OUT p_postcode VARCHAR(6))
BEGIN
SELECT street, houseno, town, postcode
INTO p_street, p_houseno, p_town, p_postcode
FROM PLAYERS
WHERE playerno = p_playerno;
END $$
DELIMITER ;
call get_address(27,@s,@h,@t,@p);
select @s,@h,@t,@p;
+------------+------+--------+--------+
| @s | @h | @t | @p |
+------------+------+--------+--------+
| Long Drive | 804 | Eltham | 8457DK |
+------------+------+--------+--------+
注意：
在使用SELECT …INTO语句时，变量名和数据表中的字段名不能相同，否则会出错。
总结：
- 如果仅仅想把数据传给MySQL存储过程，那就用in类型参数；
- 如果仅仅从MySQL存储过程返回值，那就用out类型参数；
- 如果需要把数据传给MySQL存储过程经过计算再传回给我们，那就用inout类型参数
- 5. 其他存储过程方法
1 | -- 查看存储过程; |
2. 存储过程实践
1. 定义一个根据ID查询学生记录的存储过程。
1 | DROP PROCEDURE IF EXISTS getStuById; |
2. 查看刚才创建的存储过程。
1 | SHOW PROCEDURE STATUS LIKE 'g%'; |
3. 下面是调用存储过程。对于存储过程提供的临时变量而言，MySQL规定要加上@开头。
1 | # study是当前数据库名称 |
2. 创建存储函数
1. 创建存储函数
1 | DROP FUNCTION IF EXISTS getStuNameById; |
2. 使用存储函数
1 | SELECT getStuNameById(1); |
总结： 从上述存储函数的写法上来看，存储函数有一定的缺点。首先与存储过程一样，只能返回一条结果记录。另外就是存储函数只能指明一列数据作为结果，而存储过程能够指明多列数据作为结果。
三、循环语句
1. while循环
1 | delimiter // #定义标识符为双斜杠 |
2.repeat循环
1 | delimiter // #定义标识符为双斜杠 |
3. loop循环
1 | delimiter // #定义标识符为双斜杠 |
四、Limit详解
1. 作用
Limit子句可以被用于强制 SELECT 语句返回指定的记录数。Limit接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
1 | -- 初始记录行的偏移量是 0(而不是 1); |
2. Limit效率
1. Limit高效率的原理是：避免全表扫描，提高查询效率。
在一种情况下，使用limit效率低，那就是：只使用limit来查询语句，并且偏移量特别大的情况
所以应当先使用where等查询语句，配合limit使用，效率才高
PS：在sql语句中，limit关键字是最后才用到的。以下条件的出现顺序一般是：where->group by->having-order by->limit
2. 与OFFSET搭配
比较下面两句SQL语句
1 | -- 从数据库中第三条开始查询，取一条数据，即第三条数据读取，一二条跳过 |
注意：
- 数据库数据计算是从0开始的
- Offset X是跳过X个数据，limit Y是选取Y个数据
- Limit X,Y 中X表示跳过X个数据，读取Y个数据
3. Order by多个字段
orderby多个字段时，用逗号分隔每一个字段，如果字段不指明排序方式，默认是增序。排序的方法是先按第一个字段排序，如果有相同的再按后续的字段依次排序。
五、生成自增序列
1. 作用
对记录按照某种顺序进行排列后，通过序号来进行进一步筛选
1 | select (@i:=@i+1) as rownum, surname, personal_name from student, (select @i:=0) as init; |
1 | select (@i:=@i+5) as rownum, surname, personal_name from student, (select @i:=100) as init; |
如果是多表联查，跟上述sql类似，连查完后定义一个初始化序列号即可：
1 | select (@i:=@i+1) as rownum, A.surname, B.uname from student A |
2. 中位数运算
- 定义：一串数字，按从小到大排列，当总数是奇数时，取最中间的数；当总数是偶数时，取最中间两个数的平均数。
- 解决思路：按定义来，先排列，找出最中间的数，再取平均值。
准备表如下：
学生表，含姓名和分数
1
2
3
4
5
6
7
8
9
10
11create table student (
id varchar(32) primary key,
value int
);
insert into student (id,value) values ('A',40);
insert into student (id,value) values ('B',50);
insert into student (id,value) values ('C',60);
insert into student (id,value) values ('D',70);
insert into student (id,value) values ('E',80);
insert into student (id,value) values ('F',90);
最终sql如下：
1
2
3
4
5select group_concat(id) gcid, avg(value) avgv from ( #最外层开始
select id, value from ( #第二层开始
select id, @index:=@index+1 as myindex, value from student, (select @index:=0) AS initvar order by value #最内层
) as t where floor(@index/2+1)=myindex or ceil(@index/2)=myindex #第二层结束
) as x #最外层结束
解释：
SQL分三层：最内层，第二层和最外层。
最内层是对成绩排序，并标注出名次，只执行最内层时结果如下：
对@变量不熟悉的同学自行百度，效果类似rownum。注意：执行完最内层时，@index的值就是总行数
![zxk]()
第二层取出最中间的两个值，
注意：floor和ceil的顺序：
当@index是奇数时，floor(@index/2+1)和ceil(@index/2)的值一样的，@index=7时，floor(4.5) = ceil(3.5) = 4
当@index是偶数时，floor(@index/2+1)和ceil(@index/2)的值不一样，@index=6时，floor(4) = 4，ceil(3) = 3
这就解决了中位数定义中如果总个数是奇数只取一位，总个数是偶数取中间两个的平均数的问题。
第二层执行完的结果如下：
![zxk]()
注：如果总个数是奇数，第二层执行完只会出来一行记录
最外层就简单了，取个平均数就行了。group_concat(id)只是用来看下中位数的学生是谁，可去掉。
gcid avgv C, D 65 group_concat()：
![zxk]()
有没有更直观的方法，既让每个名字都只出现一次，又能够显示所有的名字相同的人的id呢？——使用group_concat()
- group_concat()用法：
1、功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
2、语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。
示例：使用group_concat()和group by显示相同名字的人的id号：
![zxk]()
