當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL— 进阶

發布時間：2025/3/15 数据库 17 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL— 进阶小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

-- 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN... END-- 插入后 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN... END-- 刪除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN... END-- 刪除后 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW BEGIN... END-- 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN... END-- 更新后 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN... END

插入前觸發器

delimiter // CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGINIF NEW. NAME = 'alex' THENINSERT INTO tb2 (NAME) VALUES('aa'); END IF; END // delimiter ;

插入后觸發器

delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGINIF NEW. num = 666 THENINSERT INTO tb2 (NAME)VALUES('666'),('666') ;ELSEIF NEW. num = 555 THENINSERT INTO tb2 (NAME)VALUES('555'),('555') ;END IF; END// delimiter ;

特別的：NEW表示即將插入的數據行，OLD表示即將刪除的數據行。

2. 刪除觸發器

DROP TRIGGER tri_after_insert_tb1;

3. 使用觸發器

觸發器無法由用戶直接調用，而只由對表的【增/刪/改】操作被動引發。

insert into tb1(num) values(666);

三、函數

MySQL中提供了許多內置函數，例如：

CHAR_LENGTH(str)返回值為字符串str 的長度，長度的單位為字符。一個多字節字符算作一個單字符。對于一個包含五個二字節字符集, LENGTH()返回值為 10, 而CHAR_LENGTH()的返回值為5。CONCAT(str1,str2,...)字符串拼接如有任何一個參數為NULL ，則返回值為 NULL。CONCAT_WS(separator,str1,str2,...)字符串拼接（自定義連接符）CONCAT_WS()不會忽略任何空字符串。 (然而會忽略所有的 NULL）。CONV(N,from_base,to_base)進制轉換例如：SELECT CONV('a',16,2); 表示將 a 由16進制轉換為2進制字符串表示FORMAT(X,D)將數字X 的格式寫為'#,###,###.##',以四舍五入的方式保留小數點后 D 位，并將結果以字符串的形式返回。若 D 為 0, 則返回結果不帶有小數點，或不含小數部分。例如：SELECT FORMAT(12332.1,4); 結果為： '12,332.1000'INSERT(str,pos,len,newstr)在str的指定位置插入字符串pos：要替換位置其實位置len：替換的長度newstr：新字符串特別的：如果pos超過原字符串長度，則返回原字符串如果len超過原字符串長度，則由新字符串完全替換INSTR(str,substr)返回字符串 str 中子字符串的第一個出現位置。LEFT(str,len)返回字符串str 從開始的len位置的子序列字符。LOWER(str)變小寫UPPER(str)變大寫LTRIM(str)返回字符串 str ，其引導空格字符被刪除。RTRIM(str)返回字符串 str ，結尾空格字符被刪去。SUBSTRING(str,pos,len)獲取字符串子序列LOCATE(substr,str,pos)獲取子序列索引位置REPEAT(str,count)返回一個由重復的字符串str 組成的字符串，字符串str的數目等于count 。若 count <= 0,則返回一個空字符串。若str 或 count 為 NULL，則返回 NULL 。REPLACE(str,from_str,to_str)返回字符串str 以及所有被字符串to_str替代的字符串from_str 。REVERSE(str)返回字符串 str ，順序和字符順序相反。RIGHT(str,len)從字符串str 開始，返回從后邊開始len個字符組成的子序列SPACE(N)返回一個由N空格組成的字符串。SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)不帶有len 參數的格式從字符串str返回一個子字符串，起始于位置 pos。帶有len參數的格式從字符串str返回一個長度同len字符相同的子字符串，起始于位置 pos。使用 FROM的格式為標準 SQL 語法。也可能對pos使用一個負值。假若這樣，則子字符串的位置起始于字符串結尾的pos 字符，而不是字符串的開頭位置。在以下格式的函數中可以對pos 使用一個負值。mysql> SELECT SUBSTRING('Quadratically',5);-> 'ratically'mysql> SELECT SUBSTRING('foobarbar' FROM 4);-> 'barbar'mysql> SELECT SUBSTRING('Quadratically',5,6);-> 'ratica'mysql> SELECT SUBSTRING('Sakila', -3);-> 'ila'mysql> SELECT SUBSTRING('Sakila', -5, 3);-> 'aki'mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);-> 'ki'TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)返回字符串 str ，其中所有remstr 前綴和/或后綴都已被刪除。若分類符BOTH、LEADIN或TRAILING中沒有一個是給定的,則假設為BOTH 。 remstr 為可選項，在未指定情況下，可刪除空格。mysql> SELECT TRIM(' bar ');-> 'bar'mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');-> 'barxxx'mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');-> 'bar'mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');-> 'barx' 部分內置函數

更多函數：中文猛擊這里?OR 官方猛擊這里

1. 自定義函數

delimiter \\ create function f1(i1 int,i2 int ) returns int BEGINdeclare num int;set num = i1 + i2;return(num); END \\ delimiter ;

2. 執行函數

-- 獲取返回值 declare @i VARCHAR(32); select UPPER('alex') into @i; select @i;-- 在查詢中使用 select f1(11,nid) ,name from tb2;

3. 刪除函數

drop function func_name;

四、存儲過程

存儲過程是一個SQL語句集合，當主動去調用存儲過程時，其中內部的SQL語句會按照邏輯執行。

1. 創建存儲過程

-- 創建存儲過程無參數存儲過程 delimiter // create procedure p1() BEGINselect * from t1; END// delimiter;-- 執行存儲過程 call p1();

對于存儲過程，可以接收參數，其參數有三類：

in ? ? ? ? ?僅用于傳入參數用
out ? ? ? ?僅用于返回值用
inout ? ? 既可以傳入又可以當作返回值

-- 創建存儲過程有參數的存儲過程 delimiter \\ create procedure p1(in i1 int,in i2 int,inout i3 int,out r1 int ) BEGINDECLARE temp1 int;DECLARE temp2 int default 0;set temp1 = 1;set r1 = i1 + i2 + temp1 + temp2;set i3 = i3 + 100;end\\ delimiter ;-- 執行存儲過程 set @t1 =4; set @t2 = 0; CALL p1 (1, 2 ,@t1, @t2); SELECT @t1,@t2;

應用：

a.?獲得結果集

delimiter // create procedure p1() beginselect * from v1; end //delimiter ;

b.?獲得結果集+out值

delimiter // create procedure p2(in n1 int,inout n3 int,out n2 int,) begindeclare temp1 int ;declare temp2 int default 0;select * from v1;set n2 = n1 + 100;set n3 = n3 + n1 + 100; end // delimiter ;

c. 事務

delimiter \\ CREATE PROCEDURE p1 (OUT p_return_code TINYINT) BEGINDECLAREEXIT HANDLER FOR SQLEXCEPTIONBEGIN-- ERROR SET p_return_code = 1 ; ROLLBACK ;END ; DECLAREEXIT HANDLER FOR SQLWARNINGBEGIN-- WARNING SET p_return_code = 2 ; ROLLBACK ;END ; START TRANSACTION ; DELETEFROMtb1 ; INSERT INTO tb2 (NAME)VALUES('seven') ; COMMIT ; -- SUCCESS SET p_return_code = 0 ;END\\delimiter ;

d. 游標 cursor

delimiter // CREATE PROCEDURE p3 () BEGINDECLAREssid INT ; -- 自定義變量1 DECLAREssname VARCHAR (50) ; -- 自定義變量2 DECLAREdone INT DEFAULT FALSE ; DECLAREmy_cursor CURSOR FOR SELECTsid,snameFROMstudent ; DECLARECONTINUE HANDLER FOR NOT FOUNDSET done = TRUE ; OPEN my_cursor ; xxoo :LOOPFETCH my_cursor INTO ssid,ssname ;IF done THENLEAVE xxoo ;ENDIF ; INSERT INTO teacher (tname)VALUES(ssname) ;ENDLOOPxxoo ; CLOSE my_cursor ; END//delimter ;

e. 動態執行SQL

delimiter \\ CREATE PROCEDURE p4 (IN nid INT) BEGINPREPARE prod FROM'select * from student where sid > ?' ;
　　 EXECUTE prod USING @nid ;
　　 DEALLOCATE PREPARE prod ; END\\delimiter ;

2. 刪除存儲過程

drop procedure proc_name;

3. 執行存儲過程

若執行存儲過程前，該存儲過程已被修改，則不會應用最新修改，需要重新建立存儲過程，或將原存儲過程刪除后重新運行定義部分

-- 無參數 call proc_name();-- 有參數，全in call proc_name(1,2);-- 有參數，有in，out，inout set @t1=0; set @t2=3; call proc_name(1,2,@t1,@t2);

pycharm執行存儲過程

#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 執行存儲過程 cursor.callproc('p1', args=(1, 22, 3, 4)) # 獲取執行完存儲的參數 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = cursor.fetchall()conn.commit() cursor.close() conn.close()print(result)

五、事務

事務用于將某些操作的多個SQL作為原子性操作，一旦有某一個出現錯誤，即可回滾到原來的狀態，從而保證數據庫數據完整性。

?參考資料：

1.?http://www.cnblogs.com/wupeiqi/articles/5713323.html

轉載于:https://www.cnblogs.com/OldJack/p/6973579.html

總結

以上是生活随笔為你收集整理的MySQL— 进阶的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

MySQL
进阶

上一篇：前台打断点
下一篇： webpack之font-awesome

生活随笔