mysql預存程序及常用函數,mysql預存程序

來源:互聯網
上載者:User

mysql預存程序及常用函數,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;

預存程序運行於伺服器端,運行速度比較快,而且預存程序執行一次,它的執行規範駐留在高速緩衝儲存空間中,在以後的

操作中只需要從高速緩衝儲存空間調用已編譯好的二進位代碼,提高系統效能和系統回應時間。也可以確儲存儲過程安全,因為我們使用預存程序可以完成資料庫的所有操作,也可以通過編程的方式來控制對資料庫操作的存取權限。

 

 

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.