Python-mysql 深入

數(shù)據(jù)庫設(shè)計(jì)

**三范式

**

• 經(jīng)過研究和對(duì)使用中問題的總結(jié)，對(duì)于設(shè)計(jì)數(shù)據(jù)庫提出了一些規(guī)范，這些規(guī)范被稱為范式 (Nomal Form)，目前有跡可錄的共有8種范式，一般需要遵守3范式即可
• 第一范式（1NF） ： 強(qiáng)調(diào)列的原子性，即列不能再分成其他幾列
  • 舉例： 設(shè)計(jì)一個(gè)表，有 姓名、年齡，電話 字段，如果電話有 移動(dòng)電話和固定電話，就不符合 這一范式。應(yīng)這么設(shè)計(jì)： 姓名、年齡、移動(dòng)電話、固定電話
• 第二范式（2NF） ：基于1NF之后，另外表里面必須有一個(gè)主鍵； 沒有包含在主鍵中的列必須完全依賴于主鍵，而不能只依賴于主鍵的一部分
  • 舉例： 設(shè)計(jì)一個(gè)訂單收貨地址表，有 訂單號(hào)、區(qū)域價(jià)、收貨記錄id、收貨人詳細(xì)地址、收貨人名稱， 這時(shí)的主鍵應(yīng)為 （訂單號(hào)、收貨記錄id）， 區(qū)域價(jià) 需要依賴主鍵（訂單號(hào)、收貨記錄id），而 收貨人名稱和收貨人詳細(xì)地址，則只需依賴（收貨記錄id），所以這不符合這一范式。 應(yīng)這么設(shè)計(jì)：將訂單收貨地址表拆分成兩個(gè)， 訂單信息表、收貨地址信息表。訂單信息表的字段為（訂單號(hào)、收貨記錄id、區(qū)域價(jià)）；收貨地址信息表（收貨記錄id、收貨人名稱、收貨人詳細(xì)地址）
• 第三范式（3NF） ：基于2NF之后，另外非主鍵列必須直接依賴于主鍵，不能傳遞依賴，即不能存在 非主鍵A依賴非主鍵B，非主鍵B依賴主鍵的情況
  • 舉例：設(shè)計(jì)一個(gè)訂單表，有 訂單號(hào)、訂單金額、下單時(shí)間、采購(gòu)人ID、采購(gòu)人名稱、采購(gòu)人地址，主鍵是（訂單號(hào)）。 這里面 采購(gòu)人名稱、采購(gòu)人地址是直接依賴 采購(gòu)人id，不是直接依賴主鍵。應(yīng)這么設(shè)計(jì)： 拆分成 訂單表和 采購(gòu)人信息表， 訂單表字段為（訂單號(hào)、訂單金額、下單時(shí)間、采購(gòu)人ID），采購(gòu)人信息表（采購(gòu)人ID、采購(gòu)人名稱、采購(gòu)人地址）

Python與MySql交互

步驟

• 安裝模塊： 使用pip命令安裝pymysql

  • pip install pymysql
    

• 引入模塊： 在py文件中引入pymysql模塊

  • from pymysql import *
    

• Connection對(duì)象

  • 用于建立Python與數(shù)據(jù)庫的連接

  • 創(chuàng)建對(duì)象：調(diào)用connect()方法

  • conn=connect(參數(shù)列表)
    

    • 參數(shù)host：連接的mysql主機(jī)，當(dāng)前機(jī)器可以寫成 localhost
    • 參數(shù)post：連接mysql的端口，默認(rèn)是 3306
    • 參數(shù)database：數(shù)據(jù)庫的名稱
    • 參數(shù)user：連接的用戶名
    • 參數(shù)password：連接的密碼
    • 參數(shù)charset：通信采用的編碼方式，推薦使用utf8

  • Connection對(duì)象的方法

    • close() 關(guān)閉連接
    • commit() 提交
    • cursor() 返回Cursor對(duì)象，用于執(zhí)行sql語句并獲得結(jié)果
• Cursor對(duì)象
  • 用于執(zhí)行sql語句，使用頻率最高的語句是 select、insert、update、delete
  • 獲取Cursor對(duì)象：調(diào)用Connection對(duì)象的cursor()方法

    • cus=conn.cursor()
      

  • Cursor對(duì)象的方法
    • close() 關(guān)閉
    • execute(self, query, args) 執(zhí)行語句，接收的參數(shù)為sql語句本身和使用的參數(shù)列表,返回值為受影響的行數(shù)
    • fetchone()執(zhí)行查詢語句，獲取查詢結(jié)果集的第一行數(shù)據(jù)，返回一個(gè)元組
    • fetchmany()執(zhí)行查詢時(shí)，獲取所有結(jié)果行，每行是一個(gè)元組，再將這些元組放入一個(gè)元組中返回
  • 對(duì)象的屬性
    • rowcount只讀屬性，表示最近一次execute()執(zhí)行后受影響的行數(shù)
    • connection獲得當(dāng)前連接對(duì)象

示例

from pymysql import *


# 建立數(shù)據(jù)庫連接
conn=connect(host="localhost",port=3306,user="root",password="123456",database="python01",charset="utf8")
# 獲取Cursor對(duì)象
cursor=conn.cursor()


# 查詢數(shù)據(jù)表
cursor.execute("select *from person")
# 獲取所有結(jié)果集
lines=cursor.fetchall()
print(lines)
for line in lines:
    print(line)


# 插入數(shù)據(jù)
count=cursor.execute("insert into person(id,name,age) values(12,'李天王',333),(13,'哪吒',222)")
print("插入成功條數(shù)：",count)


# 修改表數(shù)據(jù)
count=cursor.execute("update person set name='白蛇' where id=8")
print("修改成功條數(shù)：",count)


# 刪除表數(shù)據(jù)
count=cursor.execute("delete from person where id=5")
print("刪除成功條數(shù)：",count)


# 修改數(shù)據(jù)表后，需要提交
conn.commit()


# 再次查詢數(shù)據(jù)表
cursor.execute("select *from person")
print(cursor.fetchall())


# 關(guān)閉Cursor對(duì)象,關(guān)閉數(shù)據(jù)庫連接
cursor.close()
conn.close()

**輸出結(jié)果

**

MySQL高級(jí)

**視圖

**

• 視圖就是一條 SELECT 語句執(zhí)行后返回的結(jié)果集 ，所以我們?cè)趧?chuàng)建視圖的時(shí)候，主要的工作就落在了SELECT查詢語句上
• 視圖是對(duì)若干張基本表的引用，一張?zhí)摫?，查詢語句執(zhí)行的結(jié)果，不存儲(chǔ)具體的數(shù)據(jù)（基本表數(shù)據(jù)發(fā)生了改變，視圖也會(huì)跟著改變）

定義視圖

• 建議以 v_ 開頭

create view 視圖名稱 as select 語句;

查看視圖

• 查看表會(huì)將所有的視圖也列出來

show tables;

使用視圖

• 視圖的用途就是查詢

select * from v_person

刪除視圖

drop view 視圖名稱;

視圖的作用

• 提高了重用性，就像是一個(gè)函數(shù)
• 創(chuàng)建視圖的源數(shù)據(jù)被修改了，視圖中的數(shù)據(jù)也會(huì)被修改，與windows的快捷方式很像（對(duì)數(shù)據(jù)庫重構(gòu)，卻不影響程序的運(yùn)行）
• 提高了安全性，可以對(duì)不同的用戶
• 讓數(shù)據(jù)更加清晰

示例

# 創(chuàng)建視圖, 查詢訂單與訂單收貨 信息表，生成一個(gè)虛擬表（視圖）
create view v_order_info as  SELECT a.order_no,a.order_price,b.receive_name,b.receive_phone FROM `order` a, order_receive b where a.order_no=b.order_no;


# 查看視圖的內(nèi)容
SELECT * from v_order_info;

**事務(wù)

**

• 所謂事務(wù)，它是一個(gè)操作序列，這些操作要么都執(zhí)行成功，要么都執(zhí)行失敗， 是一個(gè)不可分割的工作單位。
  • 舉例：銀行轉(zhuǎn)賬，第一次轉(zhuǎn)100，第二次轉(zhuǎn)200，都放到一個(gè)事務(wù)里面，要么全部轉(zhuǎn)成功，要么都失敗

事務(wù)的四大特性（ACID）

• 原子性
  • 強(qiáng)調(diào)事務(wù)不可分割，整個(gè)事務(wù)中的所有操作要么全部成功，要么全部失敗
• 一致性
  • 事務(wù)的執(zhí)行的前后數(shù)據(jù)的完整性保持一致，即上面轉(zhuǎn)賬的例子中，如果轉(zhuǎn)了一半的錢，系統(tǒng)崩潰了，但是事務(wù)最終沒有提交，那么在事務(wù)中做的修改也不會(huì)保存到數(shù)據(jù)庫中
• 隔離性
  • 一個(gè)事務(wù)執(zhí)行的過程中，不應(yīng)該受到其它事務(wù)的干擾
• 持久性
  • 一旦事務(wù)提交，則其所做的修改會(huì)永久保存到數(shù)據(jù)庫

**事務(wù)的命令

**

• 表的引擎類型必須是 innodb類型才可以使用事務(wù)，這是mysql表的默認(rèn)引擎
• 開啟事務(wù)
  • 開啟事務(wù)后執(zhí)行修改命令，變更會(huì)維護(hù)到本地緩存中，而不維護(hù)到物理表中

  • begin;
    或者
    start transaction;
    

• 提交事務(wù)
  • 將緩存中的數(shù)據(jù)變更維護(hù)到物理表中

  • commit;
    

• 回滾事務(wù)
  • 放棄緩存中變更的數(shù)據(jù)

  • rollback;
    

• 注意
  • 默認(rèn)修改數(shù)據(jù)的sql語句會(huì)自動(dòng)觸發(fā)事務(wù)（開啟與提交），即 insert、update、delete 語句
  • 一般在sql語句中手動(dòng)開啟事務(wù)的原因是：可以進(jìn)行多次數(shù)據(jù)的修改，如果成功則一起成功，否則一起失敗

**示例

**

BEGIN;


# 修改person表，id=1 的名稱 (此語句執(zhí)行后，由于沒有提交，別的查詢語句查詢時(shí)，不會(huì)查到修改的數(shù)據(jù))
update person set name='小白' where id=1;


# 提交事務(wù),這個(gè)語句執(zhí)行后，數(shù)據(jù)表中的名稱變更為 小白
COMMIT;

事務(wù)隔離級(jí)別要解決的問題

• 臟讀
  • 臟讀指的是讀到了其他事務(wù)未提交的數(shù)據(jù)，未提交意味著這些數(shù)據(jù)可能會(huì)回滾，也就是可能最終不會(huì)存到數(shù)據(jù)庫中，也就是不存在的數(shù)據(jù)。讀到了并一定最終存在的數(shù)據(jù)，這就是臟讀
  • 舉例： 小明的媳婦給小明打500塊錢買衣服，但是不小心按成了1000塊，事務(wù)還沒有提交，小明這時(shí)查到卡里有1000元，小明的媳婦發(fā)現(xiàn)不對(duì)后就回滾了事務(wù)，這時(shí)小明卡里的錢就沒了
• 不可重復(fù)讀
  • 不可重復(fù)讀指的是在同一事務(wù)內(nèi)，不同的時(shí)刻讀到的同一批數(shù)據(jù)可能是不一樣的，可能會(huì)受到其他事務(wù)的影響，比如其他事務(wù)改了這批數(shù)據(jù)并提交了
  • 舉例： 小明卡里有1000元，買了衣服準(zhǔn)備結(jié)賬（事務(wù)開啟），這時(shí)他媳婦將小明卡里的錢轉(zhuǎn)出來了，收費(fèi)系統(tǒng)提示卡里面沒錢了，小明郁悶了。同一事務(wù)內(nèi)相同的查詢語句，出現(xiàn)了不同的結(jié)果就是不可重復(fù)讀
• 幻讀
  • 幻讀是針對(duì)數(shù)據(jù)插入（INSERT）操作來說的。假設(shè)事務(wù)A對(duì)某些行的內(nèi)容作了更改，但是還未提交，此時(shí)事務(wù)B插入了與事務(wù)A更改前的記錄相同的記錄行，并且在事務(wù)A提交之前先提交了，而這時(shí)，在事務(wù)A中查詢，會(huì)發(fā)現(xiàn)好像剛剛的更改對(duì)于某些數(shù)據(jù)未起作用，但其實(shí)是事務(wù)B剛插入進(jìn)來的，讓用戶感覺很魔幻，感覺出現(xiàn)了幻覺，這就叫幻讀。
  • 舉例：小明某天花了1000元錢消費(fèi)，他媳婦查看了當(dāng)天的消費(fèi)記錄（全表掃描，事務(wù)開啟），看到確實(shí)花了1000元，就在這時(shí)，小明又花了1000元買了件衣服，并提交了事務(wù)，當(dāng)他媳婦打印消費(fèi)清單時(shí)（事務(wù)提交），發(fā)現(xiàn)花了2000元，以為出現(xiàn)了幻覺，這就是幻讀

事務(wù)的隔離級(jí)別

• SQL 標(biāo)準(zhǔn)定義了四種隔離級(jí)別，MySQL 全都支持。這四種隔離級(jí)別分別是：
  • 讀未提交（READ UNCOMMITTED)
  • 讀已提交（READ COMMITTED）
  • 可重復(fù)讀（REPEATABLE READ）
  • 串行化（SERIALIZABLE）
• 從上往下，隔離強(qiáng)度逐漸增強(qiáng)，性能逐漸變差。采用哪種隔離級(jí)別要根據(jù)系統(tǒng)需求權(quán)衡決定，其中， 可重復(fù)讀是 MySQL 的默認(rèn)級(jí)別 。
• 事務(wù)隔離其實(shí)就是為了解決上面提到的臟讀、不可重復(fù)讀、幻讀這幾個(gè)問題，下面展示了 4 種隔離級(jí)別對(duì)這三個(gè)問題的解決程度

索引

• 索引是一種特殊的文件 （InnoDB 數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分），它們包含著對(duì)數(shù)據(jù)表里面所有記錄的引用指針。通俗來講，索引就好比一本書前面的目錄，能加快數(shù)據(jù)查詢的速度
• 索引的目的在于提高查詢效率，可以類比字典

索引的使用

• 查看索引

show index form 表名;

• 創(chuàng)建索引

ALTER TABLE 表名
ADD UNIQUE INDEX 索引名 (字段) USING BTREE ;

• 刪除索引

ALTER TABLE 表名
DROP INDEX 索引名;

示例

• **創(chuàng)建一個(gè)數(shù)據(jù)表，并插入99萬條記錄，用作測(cè)試索引

  **

CREATE TABLE `test_index` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

from pymysql import *


# 建立數(shù)據(jù)庫連接
conn=connect(host="localhost",port=3306,user="root",password="123456",database="python01",charset="utf8")
# 獲取Cursor對(duì)象
cursor=conn.cursor()


for i in range(100000):
    cursor.execute("insert into test_index(id,name) values (%d,'testindex')" %i)


# 修改數(shù)據(jù)表后，需要提交
conn.commit()


# 關(guān)閉Cursor對(duì)象,關(guān)閉數(shù)據(jù)庫連接
cursor.close()
conn.close()

• 開啟運(yùn)行時(shí)間監(jiān)測(cè)

set profiling=1

• 在整個(gè)表中查找 id=88888的記錄

SELECT * from test_index where id=88888;

• 查看執(zhí)行時(shí)間

show profiles;

• 為id字段添加索引

ALTER TABLE `test_index`
ADD UNIQUE INDEX `idx_id` (`id`) USING BTREE ;

• 添加索引后再次查詢

SELECT * from test_index where id=88888;

• 再次查看執(zhí)行時(shí)間

show PROFILES;

最終結(jié)果

注意

• **建立太多的索引會(huì)影響更新和插入數(shù)據(jù)的速度，因?yàn)樗枰瑯痈旅總€(gè)索引文件，對(duì)于一個(gè)經(jīng)常更新和插入的表格，沒有必要為一個(gè)很少使用的where子句單獨(dú)建立索引。 **

• **建立索引會(huì)占用磁盤空間

  **

MySQL的主從

• 定義
  • 主從同步使用數(shù)據(jù)可以從一個(gè)服務(wù)器上復(fù)制到其它服務(wù)器上，在復(fù)制數(shù)據(jù)時(shí)，一個(gè)服務(wù)充當(dāng)主服務(wù)器（master)，其余的服務(wù)器充當(dāng)從服務(wù)器(slave)，因?yàn)閺?fù)制是異步進(jìn)行的，所以從服務(wù)器不需要一直連接主服務(wù)器，從服務(wù)器甚至可以通過撥號(hào)斷斷續(xù)續(xù)地連接主服務(wù)器，通過配置文件可以指定復(fù)制所有的數(shù)據(jù)庫、某個(gè)數(shù)據(jù)庫、甚至某個(gè)數(shù)據(jù)上的某個(gè)表
• 主從同步的好處
  • 通過增加從服務(wù)器來提高數(shù)據(jù)庫的性能，在主服務(wù)器上執(zhí)行寫入和更新，在從服務(wù)器上向外提供讀功能，可以動(dòng)態(tài)的調(diào)整從服務(wù)器的數(shù)量，從而調(diào)整整個(gè)數(shù)據(jù)庫的性能。
  • 提高數(shù)據(jù)的安全性，因?yàn)閿?shù)據(jù)復(fù)制到了從數(shù)據(jù)庫，如果一旦主服務(wù)器掛掉了，可以使用從服務(wù)器上的數(shù)據(jù)
  • 在主服務(wù)器上生成實(shí)時(shí)數(shù)據(jù)，在從服務(wù)器上分析這些數(shù)據(jù)，從而提高服務(wù)器的性能
• 主從同步機(jī)制
  • Mysql服務(wù)器之間的主從同步是基于二進(jìn)制日志機(jī)制，主服務(wù)器使用二進(jìn)制日志來記錄數(shù)據(jù)庫的變動(dòng)情況，從服務(wù)器通過讀取和執(zhí)行該日志文件來保持和主服務(wù)器的數(shù)據(jù)一致性
  • 在使用二進(jìn)制日志時(shí)，主服務(wù)器的所有操作都會(huì)被記錄下來，然后從服務(wù)器會(huì)接收到該日志的一個(gè)副本。從服務(wù)器可以指定執(zhí)行該日志文件中的哪一類事件（比如只插入數(shù)據(jù)或只更新數(shù)據(jù)）。默認(rèn)會(huì)執(zhí)行日志中的所有語句

• 配置主從同步的基本步驟
  • 在主服務(wù)器上，必須開啟二進(jìn)制日志機(jī)制和配置一個(gè)獨(dú)立的ID
  • 在每一個(gè)服務(wù)器上，配置一個(gè)唯一的ID，創(chuàng)建一個(gè)用來專門復(fù)制主服務(wù)器數(shù)據(jù)的賬號(hào)
  • 在開始復(fù)制進(jìn)程前，在主服務(wù)器上記錄二進(jìn)制文件的位置信息
  • 如果在開始復(fù)制之前，數(shù)據(jù)庫中已經(jīng)有數(shù)據(jù)，就必須先創(chuàng)建一個(gè)數(shù)據(jù)快照（可以使用mysqldump導(dǎo)出數(shù)據(jù)庫，或者直接復(fù)制數(shù)據(jù)文件）
  • 配置從服務(wù)器要連接的主服務(wù)器的ip地址和登陸授權(quán)，二進(jìn)制日志文件名和位置
• 詳細(xì)配置主從同步方法
  • 主和從的身份可以自己指定，我將虛擬機(jī)centOs中的Mysql作為主服務(wù)器，將window中的mysql作為從服務(wù)器，在主從設(shè)置之前，要保證centOs與windows間的網(wǎng)絡(luò)能互通
  • 如果 在設(shè)置主從同步前，主服務(wù)器上已有大量數(shù)據(jù)，可以使用mysqldump進(jìn)行數(shù)據(jù)備份并還原到從服務(wù)器上實(shí)現(xiàn)數(shù)據(jù)復(fù)制
  • 在主服務(wù)器上執(zhí)行命令進(jìn)行備份

    • mysqldump -uroot -pmysql --all-databases --lock-all-tables > /tmp/master_data.sql
      

    • 說明
      • -u：用戶名
      • -p：密碼
      • --all-databases：表示導(dǎo)出所有數(shù)據(jù)庫
      • --lock-all-tables：執(zhí)行操作時(shí)鎖住所有表，防止操作時(shí)數(shù)據(jù)被修改
      • /tmp/master_data.sql：導(dǎo)出文件存放的位置
  • 在從服務(wù)器上讀取剛在主服務(wù)器上生存的數(shù)據(jù)文件

    • mysql -uroot -p密碼 新數(shù)據(jù)庫名 < master_data.sql
      

  • 編寫mysqld的配置文件，設(shè)置Log_bin和server-id （注：把#刪除）

    • sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
      

    • log_bin  = var/log/mysql/mysql-bin.log
      server-id = 1
      

    • 重啟mysql服務(wù)

    • server mysql restart
      

  • 登陸主服務(wù)器中的Mysql,創(chuàng)建用于從服務(wù)器同步數(shù)據(jù)使用的賬號(hào)

    • mysql -uroot -p密碼
      

    • GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
      

    • FLUSH PRIVILEGES;
      

  • 獲取主服務(wù)器二進(jìn)制日志信息

    • SHOW MASTER STARTS;
      

  • 配置從服務(wù)器的mysqld文件，server-id與主服務(wù)器不一致即可，然后重新啟動(dòng)
  • 在從服務(wù)器進(jìn)入mysql中執(zhí)行以下命令

    • change master to master_host='主服務(wù)器ip地址' ,master_user=’slave',master_password='slave',master_log_file='mysql-bin.000006',master_log_pos=590
      

    • 注：以上配置的master_log_file 和 master_log_pos 可通過在主服務(wù)器上進(jìn)入mysql輸入以下命令查看

    • show master status;
      

  • 開啟同步，查看同步狀態(tài)(進(jìn)入mysql，執(zhí)行以下命令)

    • start slave;
      

    • show slave status \\G;
      

    • **在顯示的信息中看到 Slave_IO_Runnig: YES 和 Slave_SQL_Running:YES 就表示同步成功 **
    • 注： 如果沒有顯示YES，說明沒配置成功，原因可能是 從命令中的 change master中的配置沒配置好，要仔細(xì)檢查