數據庫之MySQL補充 · 一個碼農的摸魚之路

## union 　　之前說過一個join連表操作，join表示左右連表，還有一個聯合查詢union，表示上下連表。PS：兩張表的字段個數要相同。 ~~~ #自動處理重合（如果有完全一樣的數據會自動合并成一條數據） select nickname from A union select name from B #不處理重合（即使有完全一樣的數據也不會進行合并） select nickname from A union all select name from B ~~~ ## 視圖　　視圖是一個虛擬表（非真實存在），其本質是根據SQL語句獲取動態的數據集，并為其命名，用戶使用時只需使用命名的名稱即可獲取結果集，并可以將其當作表來使用。 ~~~ SELECT * FROM ( SELECT nid,NAME FROM tb1 WHERE nid > 2) AS A WHERE A.nid > 5; #創建臨時表 ~~~ **創建視圖** ~~~ 格式：CREATE VIEW 視圖名稱 AS SQL語句例：CREATE VIEW v1 AS SELET nid, name FROM A WHERE nid > 4 ~~~ **刪除視圖** ~~~ DROP VIEW v1 ~~~ **修改視圖** ~~~ 格式：ALTER VIEW 視圖名稱 AS SQL語句例：ALTER VIEW v1 AS SELET A.nid,B.NAME FROM A LEFT JOIN B ON A.id = B.nid LEFT JOIN C ON A.id = C.nid WHERE A.id > 2 AND C.nid < 5 ~~~ **使用視圖** 　　使用視圖時，將其當作表進行操作即可，由于視圖是虛擬表，所以無法使用其對真實表進行創建、更新和刪除操作，僅能做查詢用。 ~~~ select * from v1 ~~~ ## 觸發器　　對某個表進行增/刪/改操作的前后如果希望觸發某個特定的行為時，可以使用觸發器，觸發器用于定制用戶對表的行進行增/刪/改前后的行為。 ``` # 插入前 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 // #修改mysql中的結束標志為// CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN INSERT INTO tb2 (NAME) VALUES (NEW.name)； #如果新插入的數據的name為alex，那么就往tb2表里面插入一條數據 END// delimiter ; #將mysql中的結束標志改回來為； ~~~ PS：NEW表示即將插入的數據行，OLD表示即將刪除的數據行。 **刪除觸發器** ~~~ DROP TRIGGER tri_after_insert_tb1; ~~~ **使用觸發器** 　　觸發器無法由用戶直接調用，由于對表的增/刪/改的操作滿足觸發器的觸發條件時被動引發的觸發器執行。 ## 函數　　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' 部分內置函數 ``` **自定義函數** ~~~ delimiter \\ create function f1( i1 int, #i1,i2表示函數的參數，類型為int i2 int) returns int #定義函數返回值的類型 BEGIN declare num int; #聲明一個類型為int的session級別的變量 set num = i1 + i2; return(num); END \\ delimiter ; ~~~ **刪除函數** ~~~ drop function func_name; ~~~ **執行函數** ~~~ # 獲取返回值 declare @i VARCHAR(32); select UPPER('alex') into @i; SELECT @i; # 在查詢中使用 select f1(11,22),name from tb2; ~~~ ## ?存儲過程存儲過程是一個SQL語句集合，當主動去調用存儲過程時，其中內部的SQL語句會按照邏輯執行。 **創建存儲過程** ``` #創建存儲過程 delimiter // create procedure p1() BEGIN select * 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 ) BEGIN DECLARE 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; 傳參存儲過程 ``` ``` delimiter \\ CREATE PROCEDURE p4 ( in nid int ) BEGIN PREPARE prod FROM 'select * from student where sid > ?'; EXECUTE prod USING @nid; DEALLOCATE prepare prod; END\\ delimiter ; 動態執行SQL ``` **刪除存儲過程** ~~~ drop procedure proc_name; ~~~ **執行存儲過程** ~~~ -- 無參數 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) 執行存儲過程 ~~~ ``` #!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = 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) pymysql執行存儲過程 ``` ## 索引 MySQL中常見索引有： * 普通索引 * 唯一索引 * 主鍵索引 * 組合索引 **1、普通索引** 普通索引僅有一個功能：加速查詢 ``` create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) 創建表 + 普通索引 ``` ``` create index index_name on table_name(column_name) 創建完表后額外創建索引 drop index_name on table_name; 刪除索引 show index from table_name; 查看索引 ``` 注意：對于創建索引時如果是BLOB?和?TEXT?類型，必須指定length。 ~~~ create index ix_extra on in1(extra(32)); ~~~ **2、唯一索引** 唯一索引有兩個功能：加速查詢和唯一約束（可含null） ``` create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, unique ix_name (name) ) 創建表 + 唯一索引 ``` ``` create unique index 索引名 on 表名(列名) 創建完表后額外創建唯一索引 drop unique index 索引名 on 表名刪除唯一索引 ``` **3、主鍵索引** 主鍵有兩個功能：加速查詢和唯一約束（不可含null） ``` create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) OR create table in1( nid int not null auto_increment, name varchar(32) not null, email varchar(64) not null, extra text, primary key(ni1), index ix_name (name) ) 創建表 + 創建主鍵 ``` ``` alter table 表名 add primary key(列名); 創建完表后創建主鍵索引 alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key; 刪除主鍵索引 ``` **4、組合索引（聯合索引）** 組合索引是將n個列組合成一個索引其應用場景為：頻繁的同時使用n列來進行查詢，如：where n1 = 'alex' and n2 = 666。 ``` create table in3( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text ) ``` ``` create index ix_name_email on in3(name,email); 創建組合索引 ``` 組合索引遵循最左前綴原則，如上創建組合索引之后，查詢： * name and email ?-- 使用索引 * name ? ? ? ? ? ? ? ? -- 使用索引 * email ? ? ? ? ? ? ? ? -- 不使用索引注意：對于同時搜索n個條件時，組合索引的性能好于多個單一索引合并。有關索引補充可查看博客：[https://www.cnblogs.com/wupeiqi/articles/5716963.html](https://www.cnblogs.com/wupeiqi/articles/5716963.html)