mysql存储过程及经常使用函数

一.函数

1.数学函数

CEIL()进一取整

SELECT CEIL(1.2);2

FLOOR()舍一取整

SELECT FLOOR(1.9);9

MOD取余数（取模）

SELECT MOD(3,8);3--3对8取模

POWER()幂运算

SELECT POWER(3,2);9

ROUND()四舍五入

SELECT ROUND(123.456,2);123.46

TRUNCTATE数字截取

SELECT TRUNCATE(123.456,2);123.45

ABS()取绝对值

SELECT ABS(-123);123

PI()圆周率

SELECT PI();3.14

RAND()和RAND(X)

SELECT RAND();0到1之间的一个随机数

SELECT RAND(2);0到1之间的一个随机数

ORDER BY RAND();按随机排序

SIGN(X)得到数值符合正数0和负数1

EXP(X)e的X方

2.字符串函数

LENGTH返回字符串长度

SELECT LENGTH(‘KAIKEBA‘);7

SELECT LENGTH(‘开课吧‘);6

CHAR_LENGTH返回字符串的字符数

SELECT CHAR_LENGTH(‘KAIKEBA‘);7

SELECT CHAR_LENGTH(‘开课吧‘);3

CONCAT(S1，S2,……)将字符串连接到一起

SELECT CONCAT(‘S‘,‘S‘,‘M‘);SSM

SELECT CONCAT(‘S‘,‘ ‘,‘M‘);SM

SELECT CONCAT(‘S‘,‘S‘,NULL);NULL字符串中有NULL。结果为NULL.

CONCAT_WS(S1，S2,……)以指定的分隔符将字符串连接到一起

SELECT CONCAT_WS(‘#‘,‘S‘,‘w‘,‘M‘);S#w#M

SELECTCONCAT_WS(‘#‘,‘S‘,‘w‘,‘M‘,NULL);S#w#M

SELECT CONCAT_WS(NULL,‘S‘,‘w‘,‘M‘);NULL

UPPER(S)/UCASE(S)将字符串中的小写变大写

SELECTUPPER(‘RanDongmei‘),UCASE(‘RanDongmei‘);RANDONGMEI RANDONGMEI

LOWER(s)/LCASE(S)将字符串中的大写变小写

LEFT(S,N)/RIGHT(S,N)返回字符串S前N个字符，后N个字符

SELECT LEFT(‘kaikeba‘,3);kai

SELECT RIGHT(‘天天向上。加油‘,2);油

LPAD|RPAD使用指定字符左填充或右填充至规定字符数

SELECT LPAD(‘KAI‘,5,‘!‘);!!KAI

SELECT RPAD(‘KAI‘,5,‘!‘);KAI!!

LTRIM|RTRIM|TRIM去掉字符串左边|右边|两边的空格

SELECT ‘ abc‘;

SELECT CONCAT(‘+‘,‘ AVC ‘,‘+‘);+ AVC +

SELECT CONCAT(‘+‘,LTRIM(‘ AVC ‘),‘+‘);+AVC+

SELECT CONCAT(‘+‘,RTRIM(‘ AVC ‘),‘+‘);+AVC+

SELECT CONCAT(‘+‘,TRIM(‘ AVC ‘),‘+‘);+AVC+

仅仅有TRIM採有以下两种形式,LTRIM与RTRIM没有

SELECT TRIM(‘ABCBA‘FROM ‘A‘);A

SELECT TRIM(‘A‘FROM ‘ABCNA‘);BCN

SELECT TRIM(‘A‘FROM ‘ABCANA‘);BCAN

REPEAT(S,N)将字符串S反复N次

SELECT REPEAT(‘A‘,10);AAAAAAAAAA

SPACE(N)填充N个空格

SELECT SPACE(5);

SELECT CONCAT(‘+‘,SPACE(5),‘+‘);+     +

REPLACE(S,S1，S2);将字符串S中的S1替换为S2

SELECTREPLACE(‘AAAASDDFO‘,‘A‘,‘R‘);RRRRSDDFO

STRCMP(S1，S2);比較字符串S1和S2，若相等返回0，若S1>S2返回1。若S1<S2返回-1.

SELECT STRCMP(‘A‘,‘A‘);0

SELECT STRCMP(‘A‘,‘B‘);-1

SELECT STRCMP(‘B‘,‘A‘);1第一个字符串大于第二个字符串

SELECT STRCMP(‘A‘,‘a‘);0不区分大写和小写

SUBSTRINT(S,a,b)从字符串的第a个字符取到第b个字符

SELECT SUBSTRING(‘KAIKEBA‘,1,3);KAI

从1開始，截取三个字符

SELECT SUBSTRING(‘KAIKEBA‘,-2,3);BA

SELECT SUBSTRING(‘KAIKEBA‘,-2,1);B

REVERSE(S);反转字符串S

SELECT REVERSE(‘ABCDE‘);EDCBA

ELT(N,S1,S2,S3,……);返回指定位置的字符串

SELECT ELT(3,‘a‘,‘b‘,‘c‘,‘d‘);c

 

3.日期时间函数

返回当前日期

SELECT CURDATE();

SELECT CURRENT_DATE();

返回当前时间

SELECT CURTIME();

SELECT CURRENT_TIME();

返回当前日期时间

SELECT NOW();2014-11-07

SELECT SYSDATE();

返回月份

SELECT MONTH(‘2014-12-16‘);12

SELECT MONTHNAME(NOW());NOVEMBER

返回星期几

SELECT DAYNAME(NOW());Friday

一周的第几天，1：星期日

SELECT DAYOFWEEK(NOW());6

返回日期是星期几。0代表星期一

SELECT WEEKDAY(NOW());4

SELECT WEEK(NOW());44一年中的第44个星期

SELECT YEAR(NOW());2014

SELECT HOUR(NOW());

SELECT MINUTE(NOW());

SELECT SECOND(NOW());

计算两个日期间相隔天数

SELECTDATEDIFF(‘1988-12-16‘,‘2014-11-07‘);-9457天

SELECTDATEDIFF(‘1964-04-16‘,‘2014-11-07‘);-18467天

 

4.系统函数

SELECT VERSION();当前数据库版本

SELECT CONNECTION_ID();当前server的连接数

当前数据库：

SELECT DATABASE();

SELECT SCHEMA();

当前用户

SELECT USER();

SELECT SYSTEM_USER();

SELECT CURRENT_USER();

SELECT CURRENT_USER;

返回字符串STR的字符集

SELECT CHARSET(‘AFA‘);

SELECT LAST_INSERT_ID();最后一条插入记录ID号

 

5.加密函数

MD5()

PASSWORD();

SELECT MD5(‘ROOT‘);

SELECT PASSWORD(‘ROOT‘);

PASSWORD针对用户password加密

SELECT * FROM user WHERE User=‘root‘\G;

加入权限时须要加入PASSWORDpassword

 

6.流程函数

IF

SELECT IF(3>1,‘A‘,‘B‘);

SELECT id,username,IF(age>18,‘成年‘,‘未成年‘) FROM employee;

SELECT IFNULL(NULL,‘THIS IS NULL‘);THIS ISNULL

SELECT IFNULL(1,‘THIS IS NULL‘);1，仅仅有为空时，才干变成后面这个值

 

二.存储过程

将SQL语句放在集合里，然后调用存储过程和函数来运行已经定义好的这些SQL语句。存储过程和函数能够避免程序开发者反复编写同样的SQL语句存储过程和函数保存在mysqlserver中的一个存储和运行。能够降低client和server端传输数据的消耗。

将一组SQL语句组合在一起，而且将这些SQL语句当作一个总体，存储在mysqlserver中。

存储过程就是：存储在mysqlserver的一组当作一个总体运行的SQL语句集合。

存储过程运行于server端。运行速度比較快，并且存储过程运行一次，它的运行规范驻留在快速缓冲存储器中，在以后的操作中仅仅须要从快速缓冲存储器调用已编译好的二进制代码，能够避免程序开发者反复编写同样的SQL语句、降低client和server端传输数据的消耗、提高系统性能和系统响应时间。也能够确保存储过程安全。由于我们使用存储过程能够完毕数据库的全部操作，也能够通过编程的方式来控制对数据库操作的訪问权限。

CREATE PROCEDURE

sp_name

([proc_parameter[...]])

[characteristic...]

routine_body

參数：三部分：

（1）输入输出的參数IN OUT INOUT

（2）名称

（3）类型

特性：

LANGUAGE SQL：运行体由SQL组成

DETERMINISTIC运行结果确定：同样输入得到同样输出

NOT DETERMINISTIC运行结果不确定

 

子程序使用SQL的一个限制：

CONTAINS SQL:子程序包括sql语句（默认）

NO SQL：子程序中不包括sql语句

READS SQL DATA子程序中包括查询数据语句

MODIFIES SQL DATA子程序中包括写数据语句

 

谁有权限运行存储过程

SQL SECURITY DEFINER|INVOKER

DEFINER:定义者（默认）

INVOKER:调用者

 

凝视

COMMENT ‘string‘

 

1>创建存储过程

改变分解符

DELIMITER //

CREATE PROCEDURE sp_demo()

BEGIN

 SELECT * FROM user;

END

//

DELIMITER ;

 

DELIMITER //

CREATE PROCEDURE age_from_user(IN user_idINT,OUT user_age INT)

READS SQL DATA

BEGIN

 SELECT age FROM user WHERE id=user_id;

END

//

DELIMITER ;

2>创建存储函数

CREATE FUNCTION sp_name

[func_parameter[..]]

RETURNS type

 

DELIMITER //

CREATE FUNCTION username_from_user(user_idINT)

RETURNS VARCHAR(20)

BEGIN

 RETURN (SELECT username FROM user WHERE id=user_id);

END

//

DELIMITER ;

 

3>调用存储过程和函数

CALL sp_name([paramer..])

调用时必须有权限

CALL sp_demo();

CALL age_from_user(1,@user_age);

CALL age_from_user(2,@user_age);

调用存储函数

SELECT username_from_user(3);

4>查看创建好的存储过程和函数

SHOW PROCEDURE STATUS LIKE ‘sp_demo‘\G;

SHOW FUNCTION STATUS LIKE‘username_from_user‘\G;

详细存储过程与函数的定义

SHOW CREATE PROCEDURE sp_demo\G;

SHOW CREATE FUNCTION username_from_user\G;

创建的存储过程与函数存在information_schema数据库以下的ROUTINES表中

USE information_schema;

SELECT * FROM ROUTINES\G;

5>改动存储过程与函数

ALTER PROCEDURE|FUNCTION

ALTER PROCEDURE sp_demo COMMENT ‘THIS IS ATEST PROCEDURE‘;

SHOW CREATE PROCEDURE sp_demo\G;

ALTER FUNCTION username_from_user COMMENT‘THIS IS A TEST FUNCTION‘;

SHOW CREATE FUNCTION username_from_user\G;

 

6>删除存储过程与函数

DROP PROCEDURE sp_demo;

DROP PROCEDURE IF  EXISTS sp_demo;

SHOW WARNINGS;

DROP FUNCTION username_from_user;

存储过程执行于server端，执行速度比較快。并且存储过程执行一次，它的执行规范驻留在快速缓冲存储器中，在以后的

操作中仅仅须要从快速缓冲存储器调用已编译好的二进制代码，提高系统性能和系统响应时间。也能够确保存储过程安全，由于我们使用存储过程能够完毕数据库的全部操作。也能够通过编程的方式来控制对数据库操作的訪问权限。