標籤：
7、自訂函數使用者自訂函數（user-defined function，UDF）是一種對MySQL擴充的途徑，其用法與內建函數相同。包含了兩個必要條件，參數與傳回值。沒有必然內在聯絡。函數可以返回任意類型的值，同樣可以接收這些類型的參數；建立自訂函數：CREATE FUNCTION function_name #函數名RETURNS{STRING | INTEGER | REAL | DECIMAL} #傳回值類型routine_body #函數體函數體可以包含合法的SQL語句；也可以是簡單的SELECT或INSERT語句；函數體如果是複合結構則可以使用BEGIN ... END 語句；複合結構可以包含聲明，迴圈，控制結構。建立不帶參數的函數#SELECT DATE_FORMAT(NOW(),‘%Y年%m月%d日 %H點：%i分：%s秒‘)； #將時間顯示為年月日，時分秒， 函數要實現的功能#CREATE FUNCTION f1() RETURNS VARCHAR(30)RETURN DATE_FORMAT(NOW(),‘%Y年%m月%d日 %H點：%i分：%s秒‘)； #此時使用select f1（） 即可獲得上面的結果建立帶參數的函數#CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)RETURNS FLOAT(10,2) UNSIGEDRETURN (num1 + num2)/2; #計算兩個值的平均值建立帶多個參數的函數CREATE FUNCTION adduser（username VARCHAR(20)）
RETURNS INT UNSIGNEDBEGIN #由於使用了兩個語句所以需要使用BEGIN 。。END語句INSERT test(username) VALUES(username); #將username值插入
RETURN LAST_INSERT_ID(); #返回插入的IDEND// #修改後的分隔字元 如果不修改會報錯，需要將預設分隔符號分號修改為//或其他的符號，命令為DELIMITER //刪除函數DROP FUNCTION [IF EXISTS] function_name;#DROP FUNCTION f2；8、預存程序MySQL的執行過程：預存程序是SQL語句和控制語句的先行編譯集合，以一個名稱儲存並作為一個單元處理。優點：增強SQL語句的功能和靈活性實現了較快的執行速度減少了網路流量建立預存程序：CREATE[DEFINER = { user | CURRENT_USER } ] #指向建立者PROCEDURE sp_name ([proc_parameter [,...] ]) #帶0個或多個參數[characteristic ...] routine_body #特性 proc_parameter: #參數的寫法[IN | OUT | INOUT ] param_name type #IN，表示該參數的值必須在調用預存程序時指定；OUT，表示該參數的值可以被預存程序改變，並且可以返回 ； INOUT，表示該參數的調用時指定，並且可以被改變和返回。 特性：characteristic ‘string‘| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }#COMMENT： 注釋CONTAINS SQL : 包含SQL語句，但不包含讀或寫資料的語句NO SQL： 不包含SQL語句READS SQL DATA ： 包含讀資料的語句MODIFIES SQL DATA : 包含寫資料的語句SQL SECURITY { DEFINER | INVOKER } 指明誰有許可權來執行 過程體過程體由合法的SQL語句構成，可以是任意（對記錄的增刪改查，多表的連結操作）SQL語句；過程體如果為複合結構則使用BEGIN ... END語句；複合結構可包含聲明，迴圈，控制結構； 建立沒有參數的過程：CREATE PROCEDURE sp1() SELECT VERSION();調用預存程序CALL sp_name ([parameter [, ... ] ])CALL sp_name [ ( ) ] #帶參數 小括弧不可以省略 建立一個帶有IN型別參數的過程：CREATE PROCEDURE removeUserById （IN p_id INT UNSIGNED ) #建立過程，為刪除userID
BEGINDELETE FROM users WHERE id = p_id; #第一個ID是資料表的欄位，第二個為過程傳遞的參數END // #需要將該過程分隔字元修改為//建立過程需要將欄位和參數的名稱分別開來，不然會認為兩個均為欄位，將過程傳參修改為p_id 建立一個帶有OUT類型的過程CREATE PROCEDURE removeUserAndRetrunUserNums （IN p_id INT UNSIGNED , OUT userNums INT UNSIGNED） #建立過程，刪除user 返回userIDBEGINDELETE FROM users WHERE id = p_id;SELECT count(id) FROM users INTO userNums;END//調用該過程CALL removeUserAndRetrunUserNums (24,@Nums); # @Nums 為局部變數，只在BEGIN。。END之間聲明SELECT @Nums ; 建立一個帶有INOUT類型的過程CREATE PROCEDURE removeUserByAgeAndRrturnInfos( IN p_age SMALLINT UNSIGNED ,OUT deleteUsers SMALLINT UNSIGNEND, OUT userCounts SMALLINT UNSIGNED)
BEGINDELETE FROM users WHERE age = p_age;SELECT ROW_COUNT( ) INTO deleteUsers; # ROW_COUNT( )用來統計最近受影響的行數，結果為數值 幾行SELECT COUNT( id ) FROM users INTO userCounts; END// #此處需要修改分隔字元CALL removeUserByAgeAndRrturnInfos（23，@a，@b）； #此處@a，@b為局部變數
預存程序與自訂函數的區別1、預存程序實現的功能要複雜一些；而函數的針對性更強2、預存程序可以返回多個值，函數只有一個傳回值；3、預存程序一般獨立的來執行，而函數可以作為其他SQL語句的組成部分來實現。 9、儲存引擎MyISAM InnoDBMemoryCSVArchive並發控制：當多個串連對記錄進行修改時保證資料的一致性和完整性。共用鎖定（讀鎖）：同一時間段內，多個使用者可以讀取同一個資源，讀取過程中任何資料不會發生變化。獨佔鎖定（寫鎖）：在任何時候只能有一個使用者寫入資源，當進行寫鎖時會阻塞其他的讀鎖或寫鎖操作。鎖策略表鎖，是一種開銷最小的鎖策略。鎖表行鎖，是一種開銷最大的鎖策略。鎖行事物：用於保證資料庫的完整性。外鍵：是保證資料一致性的策略。索引：是對資料表中的一列或者多列的值進行排序的一種結構。修改儲存引擎的方法：1、通過修改MySQL的設定檔實現- default-storage-engine = engine2、通過建立資料表的命令實現-CREATE TABLE tbl_name( ...) ENGINE = engine 10、MySQL的管理工具PHPMyAdminNavicatMySQL Workbench
菜鳥的MySQL學習筆記（五）
