原文: mysql存儲過程及常用函數(shù)

一.函數(shù)

1.數(shù)學函數(shù)

CEIL()進一取整

SELECT CEIL(1.2);2

FLOOR()舍一取整

SELECT FLOOR(1.9);9

MOD取余數(shù)（取模）

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

POWER()冪運算

SELECT POWER(3,2);9

ROUND()四舍五入

SELECT ROUND(123.456,2);123.46

TRUNCTATE數(shù)字截取

SELECT TRUNCATE(123.456,2);123.45

ABS()取絕對值

SELECT ABS(-123);123

PI()圓周率

SELECT PI();3.14

RAND()和RAND(X)

SELECT RAND();0到1之間的一個隨機數(shù)

SELECT RAND(2);0到1之間的一個隨機數(shù)

ORDER BY RAND();按隨機排序

SIGN(X)得到數(shù)值符合正數(shù)0和負數(shù)1

EXP(X)e的X方

2.字符串函數(shù)

LENGTH返回字符串長度

SELECT LENGTH('KAIKEBA');7

SELECT LENGTH('開課吧');6

CHAR_LENGTH返回字符串的字符數(shù)

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，結(jié)果為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使用指定字符左填充或右填充至規(guī)定字符數(shù)

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重復(fù)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不區(qū)分大小寫

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);反轉(zhuǎn)字符串S

SELECT REVERSE('ABCDE');EDCBA

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

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

?

3.日期時間函數(shù)

返回當前日期

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());

計算兩個日期間相隔天數(shù)

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

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

?

4.系統(tǒng)函數(shù)

SELECT VERSION();當前數(shù)據(jù)庫版本號

SELECT CONNECTION_ID();當前服務(wù)器的連接數(shù)

當前數(shù)據(jù)庫：

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.加密函數(shù)

MD5()

PASSWORD();

SELECT MD5('ROOT');

SELECT PASSWORD('ROOT');

PASSWORD針對用戶密碼加密

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

添加權(quán)限時需要添加PASSWORD密碼

?

6.流程函數(shù)

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語句放在集合里，然后調(diào)用存儲過程和函數(shù)來執(zhí)行已經(jīng)定義好的這些SQL語句，存儲過程和函數(shù)可以避免程序開發(fā)人員重復(fù)編寫相同的SQL語句存儲過程和函數(shù)保存在mysql服務(wù)器中的一個存儲和執(zhí)行，可以減少客戶端和服務(wù)器端數(shù)據(jù)傳輸?shù)南摹?

將一組SQL語句組合在一起，并且將這些SQL語句當作一個整體，存儲在mysql服務(wù)器中。

存儲過程就是：存儲在mysql服務(wù)器的一組當作一個整體執(zhí)行的SQL語句集合。存儲過程運行于服務(wù)器端，運行速度比較快，而且存儲過程執(zhí)行一次，它的執(zhí)行規(guī)范駐留在高速緩沖存儲器中，在以后的操作中只需要從高速緩沖存儲器調(diào)用已編譯好的二進制代碼，可以避免程序開發(fā)人員重復(fù)編寫相同的SQL語句、減少客戶端和服務(wù)器端數(shù)據(jù)傳輸?shù)南摹⑻岣呦到y(tǒng)性能和系統(tǒng)響應(yīng)時間，也可以確保存儲過程安全，因為我們使用存儲過程可以完成數(shù)據(jù)庫的所有操作，也可以通過編程的方式來控制對數(shù)據(jù)庫操作的訪問權(quán)限。

CREATE PROCEDURE

sp_name

([proc_parameter[...]])

[characteristic...]

routine_body

參數(shù)：三部分：

（1）輸入輸出的參數(shù)IN OUT INOUT

（2）名稱

（3）類型

特性：

LANGUAGE SQL：執(zhí)行體由SQL組成

DETERMINISTIC執(zhí)行結(jié)果確定：相同輸入得到相同輸出

NOT DETERMINISTIC執(zhí)行結(jié)果不確定

?

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

CONTAINS SQL:子程序包含sql語句（默認）

NO SQL：子程序中不包含sql語句

READS SQL DATA子程序中包含查詢數(shù)據(jù)語句

MODIFIES SQL DATA子程序中包含寫數(shù)據(jù)語句

?

誰有權(quán)限執(zhí)行存儲過程

SQL SECURITY DEFINER|INVOKER

DEFINER:定義者（默認）

INVOKER:調(diào)用者

?

注釋

COMMENT 'string'

?

1>創(chuàng)建存儲過程

改變分解符

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>創(chuàng)建存儲函數(shù)

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>調(diào)用存儲過程和函數(shù)

CALL sp_name([paramer..])

調(diào)用時必須有權(quán)限

CALL sp_demo();

CALL age_from_user(1,@user_age);

CALL age_from_user(2,@user_age);

調(diào)用存儲函數(shù)

SELECT username_from_user(3);

4>查看創(chuàng)建好的存儲過程和函數(shù)

SHOW PROCEDURE STATUS LIKE 'sp_demo'\G;

SHOW FUNCTION STATUS LIKE'username_from_user'\G;

具體存儲過程與函數(shù)的定義

SHOW CREATE PROCEDURE sp_demo\G;

SHOW CREATE FUNCTION username_from_user\G;

創(chuàng)建的存儲過程與函數(shù)存在information_schema數(shù)據(jù)庫下面的ROUTINES表中

USE information_schema;

SELECT * FROM ROUTINES\G;

5>修改存儲過程與函數(shù)

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>刪除存儲過程與函數(shù)

DROP PROCEDURE sp_demo;

DROP PROCEDURE IF? EXISTS sp_demo;

SHOW WARNINGS;

DROP FUNCTION username_from_user;

存儲過程運行于服務(wù)器端，運行速度比較快，而且存儲過程執(zhí)行一次，它的執(zhí)行規(guī)范駐留在高速緩沖存儲器中，在以后的

操作中只需要從高速緩沖存儲器調(diào)用已編譯好的二進制代碼，提高系統(tǒng)性能和系統(tǒng)響應(yīng)時間。也可以確保存儲過程安全，因為我們使用存儲過程可以完成數(shù)據(jù)庫的所有操作，也可以通過編程的方式來控制對數(shù)據(jù)庫操作的訪問權(quán)限。

?

?

mysql存儲過程及常用函數(shù)