MySql中存储过程，函数，触发器，查询和系统函数等的相关精简语法基础

当今MySql数据库在项目中使用越来越多，相关的存储过程，函数，触发器，查询和系统函数等的相关语法基础也需要掌握，以便在项目中灵活应用，下面就大致整理一下精简的语法基础；

一般情况下MYSQL以分号 ; 结尾表示确认输入并执行语句，但在存储过程中；不是表示结束，因此可以用 DELIMITER 该命令将分号 (;) 号改为 // 表示确认输入并执行。也即 DELIMITER 可以定义语句结束符；

一，mysql 中存储过程的创建使用删除基本语法；

1.基本语法；

DELIMITER //  -- 声明语句结束符为双斜杠，仅在存储过程中使用; 
create procedure proc_name (
[IN | OUT | INOUT] param1 数据类型, 
[IN | OUT | INOUT] param2 数据类型
)
begin
.........
end
//
DELIMITER ;  -- 声明语句结束符为分号，恢复原来的结束符; 

2，参数传递；

因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代；存储过程中使用 return 关键字; 存储过程中的每条语句的末尾，都要加上分号( ; )， 参数不能指定默认值;

3，调用存储过程；

基本语法：call proc_name(param1, param2);
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

4，删除存储过程；

基本语法：drop procedure [ IF EXISTS ] proc_name;
注意事项：
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程；

5，其他常用命令；

show procedure status

显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等；

show create procedure proc_name
显示某一个MySQL存储过程（proc_name）的详细信息；

6，mysql存储过程例子；

-- 创建
create procedure proc_add (a int,b int)
begin
	declare c int;
	if a is null then
		set a = 0;
	end if;
	if b is null then
		set b = 0;
	end if;
	set c = a + b;
	select c as sum;
end
//

-- 调用
/* 
也可不用事前申明，在用的时候直接用“@变量名”使用就可以了;
使用set或select直接赋值，变量名以@开头;
对用户变量赋值有两种方式，一种是直接用"="号，另一种是用":="号，
set命令对用户变量赋值时，两种方式都可用；当用select语句对用户变量赋值时，只能用":="方式;
*/
declare a int defaul 0;
declare b int defaul 0;
set @a = 10;  
set @b = 20;
call proc_add(a, b);

-- 删除
drop procedure IF EXISTS proc_add;

二，mysql 中函数的创建使用删除基本语法；

1，基本语法

delimiter //　	-- 声明语句结束符为双斜杠，仅在函数中使用; 
create function 函数名(形参列表) 
returns 返回类型　　-- 注意是 retruns
begin
　	函数体　
　 	return 返回值;
end
//
delimiter ;  -- 声明语句结束符为分号，恢复原来的结束符; 

2，查看函数

show function status [like 'pattern'];　
-- 查看所有自定义函数, 自定义函数只能在本数据库使用。

show create function 函数名;　
-- 查看函数创建语句

3，调用

set @v = 函数名(参数列表);
select @v := 函数名(参数列表) as retValue;

4，删除函数

drop function IF EXISTS 函数名;

5，例子

-- 定义，计算1 ~ 指定数据之间的和
delimiter //
create function my_sum(x int) returns int
begin
    set @i = 1;
    set @sum = 0;
    while @i <= x do
        set @sum = @sum + @i;
        set @i = @i + 1;
    end while;
    return @sum;
end
//
delimiter ;

-- 调用
select @v := my_sum(5) as retSum;

-- 删除
drop function my_sum;

三，mysql 中触发器的创建使用删除基本语法；

1，基本语法

DELIMITER // -- 声明语句结束符为双斜杠，仅在触发器中使用;
create trigger 触发器名称
(after | before) 		-- 触发时间
(insert | update | delete)      -- 触发事件
on 表名				-- 触发目标表名
for each row   	-- 这句话在mysql是固定的
begin
	-- 操作sql语句;
end;
//
DELIMITER ; -- 声明语句结束符为分号，恢复原来的结束符; 

2，查看触发器

SHOW TRIGGERS [FROM schema_name];

3，删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

4，例子

-- 定义
DELIMITER //  -- 声明语句结束符为双斜杠，仅在触发器中使用;
create trigger triggerName1
after 
insert on myTable
for each row 
begin
	update myTable2 set num = num-3 where id = 1;
end;
//
DELIMITER ;  -- 声明语句结束符为分号，恢复原来的结束符; 

-- 删除
drop trigger simple_trigger;

四，mysql 中分页查询语法；

-- 查询返回100条记录
select * from myTable [where 条件] [order by 排序字段 asc|desc] limit 100;

-- 查询第10条记录之后起，返回20条记录
select * from myTable [where 条件] [order by 排序字段 asc|desc]  limit 10,20;
 
-- 查询第 pageNo 页，每页 pageSize 条记录，
-- 注意mysql语句的计算性比较弱,应直接给出结果值,如(pageNo-1)*pageSize应用java代码计算给出结果值;
select * from myTable [where 条件] [order by 排序字段 asc|desc]  
limit (pageNo-1)*pageSize, pageSize;

五，mysql 中一些系统函数的基本语法；

ABS(X):返回X的绝对值
eg.: select ABS(-32); -- 返回 32

MOD(N,M)或%:返回N被M除的余数。 
select MOD(15,7); -- 返回 1
select 15 % 7; -- 返回 1

FLOOR(X):返回不大于X的最大整数值。 
select FLOOR(1.23); -- 返回 1
select FLOOR(-1.23); -- 返回 -2

CEILING(X):返回不小于X的最小整数值。 
select CEILING(1.23); -- 返回 2
select CEILING(-1.23); -- 返回 -1

ROUND(X) :返回参数X的四舍五入的一个整数。 
select ROUND(1.58); -- 返回 2
select ROUND(-1.58); -- 返回 -2

ASCII(str):返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0。如果str是NULL，返回NULL。 
select ASCII('2'); -- 返回 50
select ASCII(2); -- 返回 50
select ASCII('dx') -- 返回 100

CONCAT(str1,str2,...):返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。 
select CONCAT('My', 'S', 'QL'); -- 返回 MySQL
select CONCAT('My', NULL, 'QL'); -- 返回 null
select CONCAT(14.3); -- 返回 14.3

LENGTH(str):返回字符串str的长度。
select LENGTH('text'); -- 返回 4

LOCATE(substr,str):返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0. 
select LOCATE('bar', 'foobarbar'); -- 返回 4
select LOCATE('xbar', 'foobar'); -- 返回 0

INSTR(str,substr):返回子串substr在字符串str中的第一个出现的位置。
select INSTR('foobarbar', 'bar'); -- 返回 4
select INSTR('xbar', 'foobar'); -- 返回 0

LEFT(str,len):返回字符串str的最左面len个字符。
select LEFT('foobarbar', 5); -- 返回 fooba

RIGHT(str,len):返回字符串str的最右面len个字符。 
select RIGHT('foobarbar', 4); -- 返回 rbar

SUBSTRING(str,pos):从字符串str的起始位置pos返回一个子串。 
select SUBSTRING('Quadratically',5); -- 返回 ratically

TRIM(str):返回字符串str，所有前缀或后缀被删除了。
select TRIM(' bar '); -- 返回 "bar"  (除去引号)

LTRIM(str):返回删除了其前置空格字符的字符串str。
select LTRIM(' barbar'); -- 返回 "barbar" (除去引号)

RTRIM(str):返回删除了其拖后空格字符的字符串str。
select RTRIM(‘barbar ’); -- 返回 "barbar" (除去引号)

REPLACE(str,from_str,to_str):返回字符串str，其字符串from_str的所有出现由字符串to_str代替。 
select REPLACE('www.mysql.com', 'w', 'Ww'); 
-- 返回 WwWwWw.mysql.com

REPEAT(str,count):返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。 
select REPEAT('MySQL', 3); -- 返回 MySQLMySQLMySQL

REVERSE(str):返回颠倒字符顺序的字符串str。 
select REVERSE('abc'); -- 返回 cba

INSERT(str,pos,len,newstr):返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。 
select INSERT(‘whatareyou', 5, 3, ‘is'); -- 返回 whatisyou

DAYOFMONTH(date):返回date的月份中的日期，在1到31范围内。 
select DAYOFMONTH('1998-02-03'); -- 返回 3

DAYOFYEAR(date):返回date在一年中的日数, 在1到366范围内。 
select DAYOFYEAR('1998-02-03'); -- 返回 34

MONTH(date):返回date的月份，范围1到12。 
select MONTH('1998-02-03'); -- 返回 2

YEAR(date):返回date的年份，范围在1000到9999。 
select YEAR('98-02-03'); -- 返回 1998

HOUR(time):返回time的小时，范围是0到23。
select HOUR('10:05:03'); -- 返回 10

MINUTE(time):返回time的分钟，范围是0到59。 
select MINUTE('98-02-03 10:05:03'); -- 返回 5

SECOND(time):回来time的秒数，范围是0到59。 
select SECOND('10:05:03'); -- 返回 3

DATE_ADD(date,INTERVAL expr type) ,进行日期增加的操作，可以精确到秒；
DATE_SUB(date,INTERVAL expr type) ，进行日期减少的操作，可以精确到秒；

SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; 
-- 返回 1998-01-01 00:00:00

SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND); 
-- 返回 1998-01-01 00:01:00

SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY); 
-- 返回 1997-12-02

CURRENT_DATE:以‘YYYY-MM-DD’或YYYYMMDD格式返回今天日期值，取决于函数在一个字符串还是数字上下文被使用。
select CURDATE(); -- 返回 2018-05-23

CURRENT_TIME:以‘HH:MM:SS’或HHMMSS格式返回当前时间值
select CURTIME(); -- 返回 12:20:36

NOW():以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回当前的日期和时间 
select NOW();  -- 返回 2018-05-23 12:20:36