上文基礎(chǔ)命令直達(dá)鏈接:MySQL命令行客戶端常用命令匯總
PyMySQL的使用
在Python中連接操作mysql需要先安裝第三方庫(kù)pymysql,執(zhí)行命令:pip install pymysql
- pymysql使用:
- 導(dǎo)入pymysql模塊
import pymysql
- 創(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")
- 獲取游標(biāo)對(duì)象
獲取游標(biāo)對(duì)象的目標(biāo)就是要執(zhí)行sql語(yǔ)句侧漓，完成對(duì)數(shù)據(jù)庫(kù)的增、刪监氢、改、查操作浪腐。代碼執(zhí)行完畢應(yīng)執(zhí)行關(guān)閉操作.
cursor= conn.cursor()
- 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()
- 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注入
SQL語(yǔ)言中的參數(shù)使用%s來占位鳄炉，此處不是python中的字符串格式化操作
將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ù)的四大特性
- 原子性(Atomicity)：
事務(wù)內(nèi)不可分割憔辫，要么全部提交執(zhí)行趣些，要么全部回滾不執(zhí)行。 - 一致性(Consistency)：
在事務(wù)開始之前和事務(wù)結(jié)束以后贰您，數(shù)據(jù)庫(kù)的完整性沒有被破壞坏平。 - 隔離性(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ù)的不一致。 - 持久性(Durability)：
事務(wù)處理結(jié)束后杠园，對(duì)數(shù)據(jù)的修改就是永久的顾瞪，即便系統(tǒng)故障也不會(huì)丟失。
- 原子性(Atomicity)：
- 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ù)
- 開啟事務(wù)有兩種方式：
在開啟事務(wù)的語(yǔ)句后的sql語(yǔ)句將遵循事務(wù)的特性成為一個(gè)整體钉跷。
begin;
start transaction;- 提交事務(wù):
將本地緩存文件中的數(shù)據(jù)提交到物理表中，完成數(shù)據(jù)的更新肚逸。
commit爷辙；
- 回滾事務(wù)
放棄本地緩存文件中的緩存數(shù)據(jù), 表示回到開始事務(wù)前的狀態(tài)
rollback彬坏；
- 開啟事務(wù)有兩種方式：
說明:
開啟事務(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í)行commit和rollback表示該事務(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);
- 聯(lián)合索引又叫復(fù)合索引申窘，即一個(gè)索引覆蓋表中兩個(gè)或者多個(gè)字段，一般用在多個(gè)字段一起查詢的時(shí)候碎捺。
- 減少磁盤空間開銷收厨，因?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è)字段上不同值較多可是建立索引滔悉。
