原文: 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();當前服務器的連接數

當前數據庫：

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針對用戶密碼加密

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

添加權限時需要添加PASSWORD密碼

?

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語句存儲過程和函數保存在mysql服務器中的一個存儲和執行，可以減少客戶端和服務器端數據傳輸的消耗。

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

存儲過程就是：存儲在mysql服務器的一組當作一個整體執行的SQL語句集合。存儲過程運行于服務器端，運行速度比較快，而且存儲過程執行一次，它的執行規范駐留在高速緩沖存儲器中，在以后的操作中只需要從高速緩沖存儲器調用已編譯好的二進制代碼，可以避免程序開發人員重復編寫相同的SQL語句、減少客戶端和服務器端數據傳輸的消耗、提高系統性能和系統響應時間，也可以確保存儲過程安全，因為我們使用存儲過程可以完成數據庫的所有操作，也可以通過編程的方式來控制對數據庫操作的訪問權限。

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;

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

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

?

?

mysql存儲過程及常用函數