Mysql 與 Pymysql

這次介紹mysql以及在python中如何用pymysql運算元據庫, 以及在mysql中儲存過程, 觸發器以及事務的實現, 對應到pymysql中應該如何操作.

---

首先我們在cmd視窗中展示常見的sql命令:

• 連線資料庫 mysql -u root -p

• 顯示資料庫 show databases;

• 建立資料庫, 設定預設編碼為utf-8以及預設的排序方式 create database pymysql_db default charset utf8 collate utf8_general_ci;
• 選擇用那個資料庫 use pymysql_db;
• 顯示當前資料庫所有的表 show tables;

---

使用者操作以及使用者許可權部分(不做演示, 不常用, 備查即可):

• 建立使用者 create user 使用者名稱 @ ip地址 identified by 密碼;
• 刪除使用者 drop user 使用者名稱 @ ip地址;
• 使用者名稱修改 rename user 使用者名稱 @ ip地址 to 新使用者名稱@ip地址;
• 修改指定使用者密碼 set password for 使用者名稱 @ ip地址 = Password(新密碼)
• 檢視許可權 show grants for 使用者 @ ip地址
• 給指定使用者增加許可權 grant 許可權 on 資料庫.表 to 使用者 @ ip地址
• 取消指定使用者的許可權 revoke 許可權 on 資料庫.表 from 使用者 @ ip地址
• 常見許可權: 除grant外的所有許可權 all privileges; 無訪問許可權 usage; 查詢許可權 select; 建立表許可權 create; 刪除表內容許可權 delete
• 使用者 @ ip地址表示使用者在指定ip地址下才能訪問, 當ip地址為%時候表示任意地址均可訪問(預設即是)

---

資料表的相關操作:

userinfo表建立如下:

-- 建立一個名為userinfo的表
CREATE TABLE `userinfo` (
    --     建立一個int型別的欄位nid, 該欄位不能為空, 且自動遞增(注意: 一個表中只能允許一個自增的欄位)
    `nid` INT (11) NOT NULL AUTO_INCREMENT,
    --     建立一個varchar型別的欄位name, 預設為空(varchar為變長型別, 這裡指的是該欄位最多佔32位, 但是查詢效率不如char定長型別)
    `name` VARCHAR (32) DEFAULT NULL,
    `color_nid` INT (11) DEFAULT NULL,
    --     指定nid為主鍵(主鍵在一個表中是唯一不重複的, 此處可以使用多個欄位組合成主鍵, 只要組合不唯一即可, 主鍵預設會自動建立索引)
    PRIMARY KEY (`nid`),
    --   宣告一個名為userinfo_ibfk_1的外來鍵, 該外來鍵由當前表中的color_nid與color表中的nid對應(說白了就是color_nid必須是color表中nid欄位具有的值才行)
    CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`color_nid`) REFERENCES `color` (`nid`)
--     指定當前資料庫的引擎為INNODB, 預設字符集為utf-8(INNODB可支援事務)
) ENGINE = INNODB DEFAULT CHARSET = utf8;複製程式碼

表color建立如下:

CREATE TABLE `color` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `tag` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;複製程式碼

• 刪除表(表結構以及表內容) drop table 表名
• 刪除表內容(自增不會影響) delete from 表名
• 刪除表內容(自增也清零) truncate table 表名
• 修改表結構
  • 增加一列 alter table 表名 add 列名 型別
  • 刪除一列 alter table 表名 drop column 列名
  • 修改列型別 alter table 表名 modify column 列名 新型別;
  • 修改列名稱和列型別(型別保持不變也可) alter table 表名 change 原列名 新列名 型別;
• 指定具體列為主鍵 alter table 表名 add primary key(列名);
• 刪除指定列的主鍵 alter table 表名 modify 列名 int, drop primary key;
• 刪除當前表中所有列的主鍵 alter table 表名 drop primary key;
• 為當前表中列指定外來鍵 alter table 從表(當前表) add constraint 外來鍵名稱（形如：FK_從表_主表） foreign key 從表(外來鍵欄位) references 主表(主鍵欄位);
• 刪除外來鍵 alter table 表名 drop foreign key 外來鍵名稱
• 修改預設值 alter table 表名 alter 列名 set default 新預設值;
• 刪除預設值 alter table 表名 alter 列名 drop default;

---

mysql中欄位的常見型別:

• 二進位制位 bit(長度)
• tinyint[(長度)] [有無符號unsigned] [位數低於長度時候是否填充零zerofill] 有符號表示範圍-128 ~ 127, 無負號表示範圍 0 ~ 255; 可用tinyint(1)來模擬boolean型別; 整型中的長度不做限制用, 僅僅做顯示用, 即長度大於位數時候是否選擇用零填充顯示
• int[(長度)] [unsigned] [zerofill]
• bigint[(長度)] [unsigned] [zerofill]
• decimal[(數字總個數 [, 小數點個數])] [unsigned] [zerofill] 該型別會以字串型別儲存在mysql, 以此來表示準確的浮點型
• float[(數字總個數, 小數個數)] [unsigned] [zerofill]
• double[(數字總個數, 小數個數)] [unsigned] [zerofill]
• char(長度) 用固定長度儲存字元型別, 這裡的長度表示所有字元所佔長度, 最長為255個字元
• varchar(長度) 變長型別儲存字元型別, 這裡的長度表示人為定製的最大長度, 查詢速度不如char定長型別
• text 變長型別儲存大字串, 最多2**16 − 1個字元
• mediumtext 最多2**24 − 1個字元
• longtext 最多2**32 − 1個字元
• enum(v1 [,v2, v3]) 列舉型別, v1表示可選的值
• set(v1 [, v2, v3]) 集合型別, v1表示可選的不重複的值
• date 以yyyy-mm-dd形式儲存
• time 以hh:mm:ss 新式儲存
• year 以yyyy新式儲存
• datetime 以yyyy-MM-dd hh:mm:ss 形式儲存

---

表中資料的操作

• 增加內容 insert into 表 (列名01,列名02...) values (值,值,值...) [,(值,值,值...)] 可以一次增加多條資料
• 刪除具體資料 delete from 表 where 條件'
• 修改表中具體資料 update 表 set 列名＝ 值 where 條件
• 查詢具體內容 select 列名01 as 別名 , 列名02 from 表 where 條件
• 常見條件: 且關係and; 區間關係between 開始位置 and 結束位置; 在其中的關係in (v1, v2, v3); 不在其中的關係not in (v1, v2, v3); 在某種條件下in (select語句)
• mysql中的兩種萬用字元: %匹配任意零個字元或者任意多個字元; _匹配任意一個
• mysql中的限制條件: limit number 表前number行以內; limit start, number 表示從start行起始的number行以內; limit number offset start 表示從第start'開始的number行以內
• mysql中排序顯示: order by 列1 desc,列2 asc 表示先以列1遞減排序, 若列1相同時候則以列2遞增排序
• 分組: select count(列名),sum(列名),max(列名),min(列名) from 表 where 條件 group by 列名01,列名02 order by 列名 這裡的group by需要放在where與order之間, where和order可以不存在
• 連表查詢結果
  • select A.xx B.oo from A, B where A.x=B.o 沒有A.x=B.o對應的資料則不顯示任何結果
  • select A.xx B.oo from A inner join B where A.x=B.o A和B具有對等位置, 沒有A.x=B.o對應的資料則不顯示任何結果
  • select A.xx B.oo from A left join B where A.x=B.o A表顯示所有, 對於B表若無符合A.x=B.o的資料則其值為null來顯示
  • select A.xx B.oo from B right join A where A.x=B.o A表顯示所有, 對於B表若無符合A.x=B.o的資料則其值為null來顯示
• 組合不去重複顯示所有查詢結果 select 列名 from 表 union all select 列名 from 表
• 組合去重顯示 select 列名 from 表 union select 列名 from 表

---

在mysql中的檢視概念, 它並不是一個真實存在的表,而是根據自己寫的sql語句執行所得的結果集, 方便查詢過程和結果比較複雜時候暫存結果以便它用. 使用檢視時候, 直接將它作為表來使用即可

• 檢視建立

  -- 建立一個名為vw1的檢視, 檢視內容為select的語句執行結果
  CREATE VIEW vw1 AS
  SELECT
    userinfo.`name` AS uname, color.tag AS color
  FROM
    userinfo
  LEFT JOIN color ON userinfo.color_nid = color.nid複製程式碼

• 檢視使用 SELECT * from vw1;

• 刪除檢視 drop view vw1
• 修改檢視

  -- 修改檢視vw1, 修改內容直接寫上現今要執行的sql語句即可
  ALTER VIEW vw1 AS
  SELECT
    userinfo.nid,userinfo.`name` AS uname, color.tag AS color
  FROM
    userinfo
  LEFT JOIN color ON userinfo.color_nid = color.nid複製程式碼

---

在介紹觸發器, 儲存過程, 函式以及事務之前我們先簡單過一下mysql中的條件和迴圈語句塊

• 條件判斷

  if 條件 then
    普通sql語句;
  elseif 條件 then
     普通sql語句;
  else
     普通sql語句;
  end if;複製程式碼

• 迴圈語句
  • while迴圈

    while 條件 do
       普通sql語句;
    end while;複製程式碼

  • repeat迴圈

    repeat
      普通sql語句;
      until 條件; 
    end repeat;複製程式碼

  • loop迴圈

    loop_label: 標籤名
        普通sql語句;
        -- 繼續迴圈
        iterate loop_label;
        -- 跳出迴圈
        leave loop_label;
    end loop;複製程式碼

---

觸發器是在對某個表執行操作(增加, 刪除和修改)的前後執行使用者特定的行為, 比如對其他的表執行增刪改的操作

• 建立觸發器

  -- 定義結束符為$, 在mac和linux中很有必要
  delimiter $
  -- 如果存在tri_before_update_userinfo觸發器則刪除, 方便除錯和修改
  DROP TRIGGER if EXISTS tri_before_update_userinfo $
  -- 建立tri_before_update_userinfo觸發器, 該觸發器會在更新userinfo表之前執行begin和end之間的內容(before表示之前, after表示之後)
  CREATE TRIGGER tri_before_update_userinfo BEFORE UPDATE ON userinfo FOR EACH ROW 
  BEGIN
    -- 如果在userinfo表中更改name為tom的行則會在color表中插入一行(old表示原來的資料)
    IF old.name = 'tom' THEN
        INSERT INTO color(tag) VALUES('black');
    -- 如果在userinfo表中有name修改後為cc則會在color表中插入一行(new表示修改後的資料)
    ELSEIF new.name = 'cc' THEN
        INSERT INTO color(tag) VALUES('yellow');
    END IF;
  end $
  delimiter ;複製程式碼

• 執行觸發器只需要修改userinfo表中的資料即可; 對於update操作既有old又有new關鍵字, 對於insert操作只有new關鍵字, 對於delete操作只有old關鍵字
• 刪除指定觸發器 drop trigger if exists 觸發器名

---

儲存過程相當於一些sql語句的堆積, 但是sql語句執行後的結果集以及變數都可以返回給使用者; 而函式不能返回結果集, 僅僅是變數的操作

• 建立儲存過程

  delimiter $
  DROP PROCEDURE IF EXISTS p1 $
  CREATE PROCEDURE p1(
  --     宣告僅用傳入引數用的整型形參
    in in_1 INT,
  --     宣告既可以傳入又可以當返回值的整型形參
    INOUT inout_1 int,
  --     宣告僅用做返回值的整型形參
    OUT out_1 INT 
  )
  BEGIN
  --     宣告語句塊中的臨時變數
    DECLARE tmp_in_1 INT;
    DECLARE tmp_inout_1 INT;
  --     賦值語句
    SET tmp_in_1 = in_1;
    set tmp_inout_1 = inout_1;
    SET out_1 = tmp_in_1 + tmp_inout_1;
  --     正常的sql查詢語句
    SELECT * from userinfo LIMIT in_1, inout_1;
  end $
  delimiter ;複製程式碼

• 使用儲存過程

  -- 設定使用者變數傳值,in型別可不用變數傳值, out型別不能傳入值, 
  -- set @in_1_tmp=1;
  set @inout_1_tmp=3;
  -- 呼叫儲存過程, 傳入引數
  CALL p1 (1,@inout_1_tmp,@out_1_tmp);
  -- 取得儲存過程的執行結果, 包括sql語句結果集以及變數值(in, inout以及out型別變數都能取得他們的值)
  SELECT @in_1,@inout_1_tmp,@out_1_tmp;複製程式碼

• 刪除儲存過程 drop procedure 儲存過程名

---

mysql中有許多對變數進行操作的內建函式, 同時我們也可以自定義函式

• 內建函式第一部分

  SELECT
    CHAR_LENGTH("test") AS "字串長度",
  --     拼接的任意一個引數為null, 則拼接結果為null
    CONCAT("C://", "workplace") AS "字串拼接",
    CONCAT_WS("-","nick","tom") AS "自定義連線符拼接",
    CONV('c',16,10) AS "進位制轉換",
    FORMAT(10000.00041,4) AS "格式化數字",
    INSERT("teach",1,2,'xxxx') AS "字串替換"複製程式碼

• 內建函式第二部分

  SELECT
    INSTR("mttm","tt") AS "字串位置",
    LEFT("hello, world",5) AS "從左擷取字串",
    LOWER("HELLO") AS "轉換小寫",
    UPPER("world") AS "轉換大寫",
    LTRIM("     test   ") AS "開始去空格",
    RTRIM("        now     ") AS "結尾去空格",複製程式碼

• 內建函式第三部分

  SELECT
    LOCATE("tt","hehettlolo",2) AS "獲取子序列位置",
    REPEAT(" | roor",5) AS "重複字串生成",
    REPLACE("hello","ll","ww") AS "字串替換",
    REVERSE("123456") AS "字串反轉",
    RIGHT("hello",3) AS "從右擷取字串",
    SUBSTRING("hello, test, world" FROM -11 FOR 4) AS "自定義擷取字串",
    SPACE(5) AS "返回空格字串",
    TRIM("  test  ") AS "去除空格"複製程式碼

• 建立函式

  delimiter $
  DROP FUNCTION IF EXISTS func1 $
  CREATE FUNCTION func1(
  --     定義整型形參
    i1 int,
    i2 int
  )
  -- 定義返回引數型別
  RETURNS INT
  BEGIN
    DECLARE tmp INT DEFAULT 0;
    SET tmp = i1 + i2;
    RETURN tmp;
  END $
  delimiter ;複製程式碼

• 使用自定義函式 SELECT func1(1,1);
• 刪除自定義函式 DROP FUNCTION IF EXISTS 函式名;

---

事務的本質就是在儲存過程中將多條sql語句作為一個原子操作來執行, 其中之一未執行成功則直接回滾到原始狀態

• 建立事務

  delimiter $
  CREATE PROCEDURE tp1(
  --     定義返回結果引數
    OUT num_flag_retunr INT
  )
  BEGIN
  --     sql執行發生異常時候, 返回值為2, 並回滾到原始狀態
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET num_flag_retunr=2;
        ROLLBACK;
    END;
  --     sql語言發生警告時候, 返回值為1, 並回滾到原始狀態
    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
        SET num_flag_retunr=1;
        ROLLBACK;
    END;
  --     開始事務具體要執行的內容, 正確則提交執行結果否則執行上面的異常
    START TRANSACTION;
        DELETE FROM userinfo WHERE nid=4;
        INSERT INTO color(tag) VALUES("green");
    COMMIT;
    SET num_flag_retunr=0;
  END $
  delimiter ;複製程式碼

• 執行事務

  CALL tp1(@out_1);
  SELECT @out_1;複製程式碼

• 刪除事務 DROP PROCEDURE IF EXISTS 事務名

---

索引相當於為我們指定的列建立一個目錄, 根據目錄我們能快速查詢到所需資料

• 索引種類
  • 普通索引 index : 僅僅加速查詢, 無約束
  • 唯一索引 unique : 加速查詢, 指定的列不能重複, 可出現一次null
  • 主鍵索引 primary key : 加速查詢, 列值唯一切不能為null
  • 組合索引 : 多列作為共同體組成索引, 效率高於多個索引列合併查詢
  • 全文索引: 資料庫儲存時候對儲存內容進行分詞儲存便於搜尋查詢
• 建立普通索引 CREATE INDEX name_index ON userinfo (name) 或者在建立表時候在表末尾加上 index 索引名 (列); 對於其他索引也是類似
• 刪除索引 drop 索引名 on 表名;
• 檢視當前表那些欄位有索引 SHOW INDEX FROM userinfo;

• 以下情況即使建立了索引也不會使用索引:
  • %開頭的模糊匹配條件: select * from 表名 where 列名 like '%其他';
  • 對建立索引的列使用函式查詢: select * from 表名 where 函式名(列名) = 其他條件
  • 列型別匹配錯誤的條件查詢時候 : select * from 表名 where 列名 = 不正確的型別;
  • 當or條件中含有未建立索引的列時: select * from 表名 where 條件01 or 條件02';
  • 匹配條件為不等於時候(主鍵例外) : select * from 表名 where 非主鍵列 != 其他;
  • 匹配條件為大於的時候(主鍵或索引為整型例外) : select * from 表名 where 列名 > 其他;
  • 排序時候選擇的對映與排序列不一樣即使都是索引列也不走索引(主鍵例外) : select 索引列-01 from 表名 order by 索引列-02 desc;
  • 組合索引為 索引列-01和索引列-02, 當先查索引列-02時候也不走索引
• 用執行計劃可以詳細顯示sql語句(使用查詢語句才有意義)的執行情況, 例如如執行EXPLAIN SELECT * from userinfo;

• 關於上述表中詳細欄位解釋 :
  • id 查詢順序標識, 表示sql查詢語句的執行順序
  • select_type 查詢語句的型別: 簡單查詢SIMPLE, 最外層查詢PRIMARY, 子查詢DERIVED, 對映是子查詢SUBQUERY, 聯合查詢UNION, 聯合查詢所得UNION RESULT
  • table 正在訪問的表名
  • partitions 分割槽型別
  • type 查詢方式, 查詢時間效能排序 : 全表掃描all > 全索引掃描index > 索引部分範圍內掃描range > 多單列索引合併掃描index_merge > 索引匹配一個或多個值掃描ref > 聯合主鍵索引或者唯一索引掃描eq_ref > 最多有一個匹配行優化後作為常量表掃描const, 還有system特列, 效能與const近似
  • possible_keys 可能使用的索引
  • key 實際使用的索引
  • key_len 位元組長度
  • rows 預測找到所需內容要查詢的行數
  • extra 其他資訊, 多為mysql解決查詢的詳細資訊
• limit分頁優化

  -- 查詢第1000條資料之後的10條資料
  -- 這種查詢方式會進行全文掃描
  SELECT * FROM userinfo LIMIT 1000,5;
  -- 這種方式僅僅優化了一點, 使用了一些普通索引和索引合併查詢
  EXPLAIN SELECT * FROM userinfo WHERE nid > (SELECT nid FROM userinfo LIMIT 1000, 1) LIMIT 5;
  -- 直接根據計算所得後的最大條數倒序排列查詢
  SELECT * FROM userinfo WHERE nid < 上次最大條數 ORDER BY nid DESC LIMIT 5;複製程式碼

• 慢查詢日誌可以根據自定義設定記錄那些查詢效能查的sql語句 :
  • 檢視全域性變數 show global variables like "%名稱"
  • 設定全域性變數 set global 變數名=值
  • 是否開啟慢日誌全域性變數 slow_query_log = OFF
  • 時間限制全域性變數 long_query_time = 2
  • 日誌檔案存放位置全域性變數 slow_query_log_file = /usr/slow.log
  • 是否記錄未使用索引的查詢語句全域性變數 log_queries_not_using_indexes = OFF
• 格式化檢視慢日誌 mysqldumpslow [option] 日誌存放位置, 常用option如下:
  • 版本 -v或者--verbose
  • 除錯模式 -d或者--debug
  • 排序方式 -s 規則選項, 預設是平均查詢時間at
  • 倒序排列顯示 -r
  • 顯示前number條 -t number
  • 不要將sql中數字轉換成N，字串轉換成S, 選項為, -a

---

python使用pymysql運算元據庫

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

# 建立連線通道, 設定連線ip, port, 使用者, 密碼以及所要連線的資料庫
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='pymysql_db')

# 建立遊標, 運算元據庫, 指定遊標返回內容為字典型別
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 呼叫儲存過程, 傳入引數
cursor.callproc('p1', (1, 5, 0))

# 得到結果集1, 即sql語句執行結果
select_result = cursor.fetchone()
print(select_result)

# 執行儲存過程, 獲取返回值, @_儲存過程名_第一個引數
cursor.execute("select @_p1_0,@_p1_1,@_p_2")
select_result = cursor.fetchone()
print(select_result)

cursor.close()
conn.close()複製程式碼