Mysql數(shù)據(jù)庫(kù)高級(jí)使用-PyMySQL盲链、SQL注入與防止、事務(wù)迟杂、索引

上文基礎(chǔ)命令直達(dá)鏈接:MySQL命令行客戶端常用命令匯總

PyMySQL的使用

在Python中連接操作mysql需要先安裝第三方庫(kù)pymysql,執(zhí)行命令:pip install pymysql

  • pymysql使用:
  1. 導(dǎo)入pymysql模塊
import pymysql
  1. 創(chuàng)建連接對(duì)象
    調(diào)用pymysql模塊中的connect()函數(shù)來創(chuàng)建連接對(duì)象,連接到數(shù)據(jù)庫(kù)需要傳入較多參數(shù):

參數(shù)host:連接的mysql主機(jī)刽沾,如果本機(jī) 是'localhost'
參數(shù)port:連接的mysql主機(jī)的端口,默認(rèn)是3306
參數(shù)user:連接的用戶名
參數(shù)password:連接的密碼
參數(shù)database:數(shù)據(jù)庫(kù)的名稱
參數(shù)charset:通信采用的編碼方式排拷,推薦使用utf8

conn = pymysql.connect(host="localhost",
                       port=3306,
                       user="root",
                       password="mysql",
                       database="python",
                       charset="utf8")
  1. 獲取游標(biāo)對(duì)象
    獲取游標(biāo)對(duì)象的目標(biāo)就是要執(zhí)行sql語(yǔ)句侧漓,完成對(duì)數(shù)據(jù)庫(kù)的增、刪监氢、改、查操作浪腐。代碼執(zhí)行完畢應(yīng)執(zhí)行關(guān)閉操作.
cursor= conn.cursor()
  1. pymysql完成數(shù)據(jù)的查詢操作示例
import pymysql

# 創(chuàng)建連接對(duì)象
conn = pymysql.connect(
                    host="localhost",
                    port=3306,
                    user="root",
                    password="mysql",
                    database="python",
                    charset="utf8"
                       )

# 獲取游標(biāo)對(duì)象
cursor = conn.cursor()

# 查詢 SQL 語(yǔ)句
sql = "select * from students;"
# 執(zhí)行 SQL 語(yǔ)句 返回值就是 SQL 語(yǔ)句在執(zhí)行過程中影響的行數(shù)
row_count = cursor.execute(sql)
print("SQL 語(yǔ)句執(zhí)行影響的行數(shù)%d" % row_count)

# 取出結(jié)果集中一行數(shù)據(jù), 例如:(1, '張三')
# print(cursor.fetchone())

# 取出結(jié)果集中的所有數(shù)據(jù), 例如:((1, '張三'), (2, '李四'), (3, '王五'))
for line in cursor.fetchall():
    print(line)

# 關(guān)閉游標(biāo)
cursor.close()

# 關(guān)閉連接
conn.close()
  1. pymysql完成對(duì)數(shù)據(jù)的增刪改示例
    代碼中使用的commit()和rollback()方法為事務(wù)的操作,在我另一篇文章單獨(dú)講到.
import pymysql

# 創(chuàng)建連接對(duì)象
conn = pymysql.connect(
                    host="localhost",
                    port=3306,
                    user="root",
                    password="mysql",
                    database="python",
                    charset="utf8"
                       )
# 獲取游標(biāo)對(duì)象
cursor = conn.cursor()
try:
    # 添加 SQL 語(yǔ)句
    # sql = "insert into students(name) values('胖太'), ('老師');"
    # 刪除 SQ L語(yǔ)句
    # sql = "delete from students where id = 5;"
    # 修改 SQL 語(yǔ)句
    sql = "update students set name = '夏目' where id = 5;"
    # 執(zhí)行sql語(yǔ)句
    row_count = cursor.execute(sql)
    print("SQL語(yǔ)句影響的行數(shù)為%d" % row_count)
    # 提交事務(wù)
    conn.commit()
except Exception as e:
    print(e)
    # 提交失敗,回滾事務(wù)泽谨,即撤銷剛執(zhí)行的SQL操作
    conn.rollback()
# 關(guān)閉游標(biāo)
cursor.close()
# 關(guān)閉鏈接
conn.close()

conn.commit() 表示將修改操作提交到數(shù)據(jù)庫(kù)
conn.rollback() 表示回滾數(shù)據(jù)

SQL注入與防止
  • SQL注入是什么?
    用戶提交帶有惡意的數(shù)據(jù)與SQL語(yǔ)句進(jìn)行字符串方式拼接,從而影響后臺(tái)SQL語(yǔ)句的含義,最終產(chǎn)生數(shù)據(jù)泄露的現(xiàn)象.
    例如,當(dāng)前存在數(shù)據(jù)庫(kù)''jing_dong'',其中有數(shù)據(jù)表''goods'',此表中存放有大量商品信息。
    在下面代碼中模擬用戶查詢輸入:
import pymysql
# 創(chuàng)建連接對(duì)象
conn = pymysql.connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')

find_name = input("請(qǐng)輸入商品名稱:")
# 獲取游標(biāo)對(duì)象
cursor = conn.cursor()
sql = "select * from goods WHERE name = '%s'" % find_name
print("實(shí)際后臺(tái)執(zhí)行的SQL語(yǔ)句為:",sql)
count = cursor.execute(sql)
print("SQL語(yǔ)句影響的行數(shù):%d" % count)
# 獲取查詢結(jié)果
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()

用戶正常輸入時(shí),將返回我們需要的指定商品信息:

請(qǐng)輸入商品名稱:商務(wù)雙肩背包
實(shí)際后臺(tái)執(zhí)行的SQL語(yǔ)句為: select * from goods WHERE name = '商務(wù)雙肩背包'
SQL語(yǔ)句影響的行數(shù):2
((19, '商務(wù)雙肩背包', 6, 6, Decimal('99.000'), b'\x01', b'\x00'), (21, '商務(wù)雙肩背包', 6, 6, Decimal('99.000'), b'\x01', b'\x00'))

用戶惡意輸入時(shí)隔盛,將會(huì)出現(xiàn)整體數(shù)據(jù)泄露的問題:

請(qǐng)輸入商品名稱:' or 1=1 or '
實(shí)際后臺(tái)執(zhí)行的SQL語(yǔ)句為: select * from goods WHERE name = '' or 1=1 or ''
SQL語(yǔ)句影響的行數(shù):21
((1, 'r510vc 15.6英寸筆記本', 5, 2, Decimal('3399.000'), b'\x01', b'\x00'), (2, 'y400n 14.0英寸筆記本電腦', 5, 7, Decimal('4999.000'), b'\x01', b'\x00'), (3, 'g150th 15.6英寸游戲本', 4, 9, Decimal('8499.000'), b'\x01', b'\x00'), (4, 'x550cc 15.6英寸筆記本', 5, 2, Decimal('2799.000'), b'\x01', b'\x00'), (5, 'x240 超極本', 7, 7, Decimal('4880.000'), b'\x01', b'\x00'), (6, 'u330p 13.3英寸超極本', 7, 7, Decimal('4299.000'), b'\x01', b'\x00'), (7, 'svp13226scb 觸控超極本', 7, 6, Decimal('7999.000'), b'\x01', b'\x00'), (8, 'ipad mini 7.9英寸平板電腦', 2, 8, Decimal('1998.000'), b'\x01', b'\x00'), (9, 'ipad air 9.7英寸平板電腦', 2, 8, Decimal('3388.000'), b'\x01', b'\x00'), (10, 'ipad mini 配備 retina 顯示屏', 2, 8, Decimal('2788.000'), b'\x01', b'\x00'), (11, 'ideacentre c340 20英寸一體電腦 ', 1, 7, Decimal('3499.000'), b'\x01', b'\x00'), (12, 'vostro 3800-r1206 臺(tái)式電腦', 1, 5, Decimal('2899.000'), b'\x01', b'\x00'), (13, 'imac me086ch/a 21.5英寸一體電腦', 1, 8, Decimal('9188.000'), b'\x01', b'\x00'), (14, 'at7-7414lp 臺(tái)式電腦 linux )', 1, 3, Decimal('3699.000'), b'\x01', b'\x00'), (15, 'z220sff f4f06pa工作站', 3, 4, Decimal('4288.000'), b'\x01', b'\x00'), (16, 'poweredge ii服務(wù)器', 3, 5, Decimal('5388.000'), b'\x01', b'\x00'), (17, 'mac pro專業(yè)級(jí)臺(tái)式電腦', 3, 8, Decimal('28888.000'), b'\x01', b'\x00'), (18, 'hmz-t3w 頭戴顯示設(shè)備', 6, 6, Decimal('6999.000'), b'\x01', b'\x00'), (19, '商務(wù)雙肩背包', 6, 6, Decimal('99.000'), b'\x01', b'\x00'), (20, 'x3250 m4機(jī)架式服務(wù)器', 3, 1, Decimal('6888.000'), b'\x01', b'\x00'), (21, '商務(wù)雙肩背包', 6, 6, Decimal('99.000'), b'\x01', b'\x00'))

此處數(shù)據(jù)表內(nèi)的全部信息都返回給了用戶吮炕,原因在于用戶惡意輸入的內(nèi)容和后臺(tái)SQL查詢語(yǔ)拼接,導(dǎo)致語(yǔ)義改變访得,最終產(chǎn)生數(shù)據(jù)泄露陕凹。
使SQL語(yǔ)義改變的最終原因就是字符串拼接產(chǎn)生的漏洞

  • 防止SQL注入
    1. SQL語(yǔ)言中的參數(shù)使用%s來占位鳄炉,此處不是python中的字符串格式化操作

    2. 將SQL語(yǔ)句中%s占位所需要的參數(shù)存在一個(gè)列表中,把參數(shù)列表傳遞給execute方法中第二個(gè)參數(shù)
      代碼示例:

import pymysql
# 創(chuàng)建連接對(duì)象
conn = pymysql.connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')

find_name = input("請(qǐng)輸入商品名稱:")
# 獲取游標(biāo)對(duì)象
cursor = conn.cursor()

# 構(gòu)造參數(shù)列表
params = [find_name]
sql = "select * from goods WHERE name = %s"
count = cursor.execute(sql,params)
# 注意:
# 如果要是有多個(gè)參數(shù)拂盯,需要進(jìn)行參數(shù)化
# 那么params = [數(shù)值1, 數(shù)值2....],此時(shí)sql語(yǔ)句中有多個(gè)%s即可
# %s 不需要帶引號(hào)

print("SQL語(yǔ)句影響的行數(shù):%d" % count)
# 獲取查詢結(jié)果
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()

這樣就不能再通過利用參數(shù)對(duì)SQL語(yǔ)句進(jìn)行字符串拼接改變語(yǔ)義了谈竿。

事務(wù)

事務(wù)就是用戶定義的一系列執(zhí)行SQL語(yǔ)句的操作, 這些操作要么完全地執(zhí)行,要么完全地都不執(zhí)行空凸, 它是一個(gè)不可分割的工作執(zhí)行單元嚎花。

事務(wù)的使用場(chǎng)景:
在日常生活中,有時(shí)我們需要進(jìn)行銀行轉(zhuǎn)賬呀洲,這個(gè)銀行轉(zhuǎn)賬操作背后就是需要執(zhí)行多個(gè)SQL語(yǔ)句紊选,假如這些SQL執(zhí)行到一半突然停電了,那么就會(huì)導(dǎo)致這個(gè)功能只完成了一半道逗,這種情況是不允許出現(xiàn)兵罢,要想解決這個(gè)問題就需要通過事務(wù)來完成。

  • 事務(wù)的四大特性
    1. 原子性(Atomicity):
      事務(wù)內(nèi)不可分割憔辫,要么全部提交執(zhí)行趣些,要么全部回滾不執(zhí)行。
    2. 一致性(Consistency):
      在事務(wù)開始之前和事務(wù)結(jié)束以后贰您,數(shù)據(jù)庫(kù)的完整性沒有被破壞坏平。
    3. 隔離性(Isolation):
      一個(gè)事務(wù)所做的修改操作在提交事務(wù)之前,對(duì)于其他事務(wù)來說是不可見的锦亦。數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力舶替,隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。
    4. 持久性(Durability):
      事務(wù)處理結(jié)束后杠园,對(duì)數(shù)據(jù)的修改就是永久的顾瞪,即便系統(tǒng)故障也不會(huì)丟失。
  • MySQL數(shù)據(jù)庫(kù)支持的表的存儲(chǔ)引擎

-- 查看MySQL數(shù)據(jù)庫(kù)支持的表的存儲(chǔ)引擎
show engines;

說明:常用的表的存儲(chǔ)引擎是 InnoDB 和 MyISAM抛蚁;
1. InnoDB 是支持事務(wù)的
2. MyISAM 不支持事務(wù)陈醒,優(yōu)勢(shì)是訪問速度快,對(duì)事務(wù)沒有要求或者以select瞧甩、insert為主的都可以使用該存儲(chǔ)引擎來創(chuàng)建表

  • 開啟事務(wù)/提交事務(wù)

    1. 開啟事務(wù)有兩種方式:
      在開啟事務(wù)的語(yǔ)句后的sql語(yǔ)句將遵循事務(wù)的特性成為一個(gè)整體钉跷。

    begin;
    start transaction;

    1. 提交事務(wù):
      將本地緩存文件中的數(shù)據(jù)提交到物理表中,完成數(shù)據(jù)的更新肚逸。

    commit爷辙;

    1. 回滾事務(wù)
      放棄本地緩存文件中的緩存數(shù)據(jù), 表示回到開始事務(wù)前的狀態(tài)

    rollback彬坏;

說明:

  • 開啟事務(wù)后執(zhí)行修改命令,變更數(shù)據(jù)會(huì)保存到本地緩存文件中膝晾,而不維護(hù)到物理表中

  • MySQL數(shù)據(jù)庫(kù)默認(rèn)采用自動(dòng)提交(autocommit)模式栓始,如果沒有顯示的開啟一個(gè)事務(wù),那么每條sql語(yǔ)句都會(huì)被當(dāng)作一個(gè)事務(wù)執(zhí)行提交的操作

  • 使用命令select @@autocommit;查看當(dāng)前自動(dòng)提交事務(wù)的設(shè)置狀態(tài),默認(rèn)為1血当,即每行sql語(yǔ)句自動(dòng)提交事務(wù)幻赚。

  • 當(dāng)設(shè)置autocommit=0就是取消了自動(dòng)提交事務(wù)模式,直到顯示的執(zhí)行commitrollback表示該事務(wù)結(jié)束歹颓。

  • set autocommit = 0 表示取消自動(dòng)提交事務(wù)模式坯屿,需要手動(dòng)執(zhí)行commit完成事務(wù)的提交

  • pymysql 里面的 conn.commit() 操作就是提交事務(wù)

  • pymysql 里面的 conn.rollback() 操作就是回滾事務(wù)

索引

索引在MySQL中也叫做“鍵”,它是一個(gè)特殊的文件巍扛,它保存著數(shù)據(jù)表里所有記錄的位置信息,更通俗的來說乏德,數(shù)據(jù)庫(kù)索引好比是一本書前面的目錄,能加快數(shù)據(jù)庫(kù)的查詢速度胧瓜。

應(yīng)用場(chǎng)景:
當(dāng)數(shù)據(jù)庫(kù)中數(shù)據(jù)量很大時(shí)郑什,查找數(shù)據(jù)會(huì)變得很慢,我們就可以通過索引來提高數(shù)據(jù)庫(kù)的查詢效率钝满。

  • 查看表中已有索引:

show index from 表名;

  • 主鍵列會(huì)自動(dòng)創(chuàng)建索引
  • 為字段添加索引(示例為goods表name字段添加名為index_name的索引):

alter table goods add index index_name(name);

  • 聯(lián)合索引

-- 創(chuàng)建聯(lián)合索引
alter table goods add index (name,price);

  1. 聯(lián)合索引又叫復(fù)合索引申窘,即一個(gè)索引覆蓋表中兩個(gè)或者多個(gè)字段,一般用在多個(gè)字段一起查詢的時(shí)候碎捺。
  2. 減少磁盤空間開銷收厨,因?yàn)槊縿?chuàng)建一個(gè)索引优构,其實(shí)就是創(chuàng)建了一個(gè)索引文件,索引文件過多會(huì)增加磁盤空間的開銷黎休。
  • 聯(lián)合索引最左原則

在使用聯(lián)合索引的查詢數(shù)據(jù)時(shí)候一定要保證聯(lián)合索引的最左側(cè)字段出現(xiàn)在查詢條件里面,否則聯(lián)合索引失效

在使用聯(lián)合索引的時(shí)候势腮,我們要遵守一個(gè)最左原則,即index(name,price)支持 name 、name 和 price 組合查詢,而不支持單獨(dú) price 查詢捎拯,因?yàn)闆]有用到創(chuàng)建的聯(lián)合索引。

  • MySQL中索引的優(yōu)點(diǎn)和缺點(diǎn)和使用原則
    優(yōu)點(diǎn):加快數(shù)據(jù)的查詢速度
    缺點(diǎn):創(chuàng)建索引會(huì)耗費(fèi)時(shí)間和占用磁盤空間祸泪,并且隨著數(shù)據(jù)量的增加所耗費(fèi)的時(shí)間也會(huì)增加

使用原則:
1. 通過優(yōu)缺點(diǎn)對(duì)比建芙,不是索引越多越好禁荸,而是需要自己合理的使用。
2. 對(duì)經(jīng)常更新的表就避免對(duì)其進(jìn)行過多索引的創(chuàng)建瑰妄,對(duì)經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引映砖,
3. 數(shù)據(jù)量小的表最好不要使用索引,因?yàn)橛捎跀?shù)據(jù)較少竹宋,可能查詢?nèi)繑?shù)據(jù)花費(fèi)的時(shí)間比遍歷索引的時(shí)間還要短瓜饥,索引就可能不會(huì)產(chǎn)生優(yōu)化效果。
4. 在一字段上相同值比較多不要建立索引宪潮,比如在學(xué)生表的"性別"字段上只有男趣苏,女兩個(gè)不同值。相反的尽棕,在一個(gè)字段上不同值較多可是建立索引滔悉。

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市曹宴,隨后出現(xiàn)的幾起案子歉提,更是在濱河造成了極大的恐慌,老刑警劉巖版扩,帶你破解...
    沈念sama閱讀 218,858評(píng)論 6 508
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件礁芦,死亡現(xiàn)場(chǎng)離奇詭異悼尾,居然都是意外死亡,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,372評(píng)論 3 395
  • 文/潘曉璐 我一進(jìn)店門舷胜,熙熙樓的掌柜王于貴愁眉苦臉地迎上來活翩,“玉大人,你說我怎么就攤上這事沮焕±冢” “怎么了?”我有些...
    開封第一講書人閱讀 165,282評(píng)論 0 356
  • 文/不壞的土叔 我叫張陵魁巩,是天一觀的道長(zhǎng)谷遂。 經(jīng)常有香客問我卖鲤,道長(zhǎng)畴嘶,這世上最難降的妖魔是什么窗悯? 我笑而不...
    開封第一講書人閱讀 58,842評(píng)論 1 295
  • 正文 為了忘掉前任甩恼,我火速辦了婚禮,結(jié)果婚禮上悦污,老公的妹妹穿的比我還像新娘钉蒲。我一直安慰自己,他們只是感情好踏枣,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,857評(píng)論 6 392
  • 文/花漫 我一把揭開白布茵瀑。 她就那樣靜靜地躺著躬厌,像睡著了一般。 火紅的嫁衣襯著肌膚如雪鸿捧。 梳的紋絲不亂的頭發(fā)上疙渣,一...
    開封第一講書人閱讀 51,679評(píng)論 1 305
  • 那天妄荔,我揣著相機(jī)與錄音,去河邊找鬼灶轰。 笑死刷钢,一個(gè)胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的伴澄。 我是一名探鬼主播,決...
    沈念sama閱讀 40,406評(píng)論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼举农,長(zhǎng)吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼颁糟!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起棱貌,我...
    開封第一講書人閱讀 39,311評(píng)論 0 276
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤婚脱,失蹤者是張志新(化名)和其女友劉穎勺像,沒想到半個(gè)月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體篮洁,經(jīng)...
    沈念sama閱讀 45,767評(píng)論 1 315
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡嘀粱,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,945評(píng)論 3 336
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了垄分。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 40,090評(píng)論 1 350
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡叫倍,死狀恐怖豺瘤,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情蚕泽,我是刑警寧澤,帶...
    沈念sama閱讀 35,785評(píng)論 5 346
  • 正文 年R本政府宣布仔蝌,位于F島的核電站荒吏,受9級(jí)特大地震影響,放射性物質(zhì)發(fā)生泄漏瞧挤。R本人自食惡果不足惜儡湾,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,420評(píng)論 3 331
  • 文/蒙蒙 一盒粮、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧丹皱,春花似錦、人聲如沸讼油。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,988評(píng)論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)瘦赫。三九已至蛤迎,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間校辩,已是汗流浹背辆童。 一陣腳步聲響...
    開封第一講書人閱讀 33,101評(píng)論 1 271
  • 我被黑心中介騙來泰國(guó)打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留故黑,地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 48,298評(píng)論 3 372
  • 正文 我出身青樓概疆,卻偏偏與公主長(zhǎng)得像峰搪,于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子使套,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,033評(píng)論 2 355