總體內(nèi)容
- 1.1、SQL語句的強(qiáng)化
- 1.2酣溃、python安裝pymysql
- 1.3、參數(shù)化(sql防注入)
一纪隙、SQL語句的強(qiáng)化
-
1.1赊豌、創(chuàng)建數(shù)據(jù)庫（jing_dong）、數(shù)據(jù)表（goods）绵咱、插入數(shù)據(jù)
創(chuàng)建 "京東" 數(shù)據(jù)庫create database jing_dong charset=utf8;使用 "京東" 數(shù)據(jù)庫
use class_data;創(chuàng)建一個(gè)商品goods數(shù)據(jù)表
create table goods( id int unsigned primary key auto_increment not null, name varchar(150) not null, cate_name varchar(40) not null, brand_name varchar(40) not null, price decimal(10,3) not null default 0, is_show bit not null default 1, is_saleoff bit not null default 0 );提示：
- id：主鍵
- name：商品的名字
- cate_name: 商品的類型
- brand_name：商品的品牌
- price：商品的價(jià)格
- is_show：是否顯示商品
- is_saleoff：商品是否賣完
插入數(shù)據(jù)碘饼，向goods表中插入數(shù)據(jù)
insert into goods values(0,'r510vc 15.6英寸筆記本','筆記本','華碩','3399',default,default); insert into goods values(0,'y400n 14.0英寸筆記本電腦','筆記本','聯(lián)想','4999',default,default); insert into goods values(0,'g150th 15.6英寸游戲本','游戲本','雷神','8499',default,default); insert into goods values(0,'x550cc 15.6英寸筆記本','筆記本','華碩','2799',default,default); insert into goods values(0,'x240 超極本','超級(jí)本','聯(lián)想','4880',default,default); insert into goods values(0,'u330p 13.3英寸超極本','超級(jí)本','聯(lián)想','4299',default,default); insert into goods values(0,'svp13226scb 觸控超極本','超級(jí)本','索尼','7999',default,default); insert into goods values(0,'ipad mini 7.9英寸平板電腦','平板電腦','蘋果','1998',default,default); insert into goods values(0,'ipad air 9.7英寸平板電腦','平板電腦','蘋果','3388',default,default); insert into goods values(0,'ipad mini 配備 retina 顯示屏','平板電腦','蘋果','2788',default,default); insert into goods values(0,'ideacentre c340 20英寸一體電腦 ','臺(tái)式機(jī)','聯(lián)想','3499',default,default); insert into goods values(0,'vostro 3800-r1206 臺(tái)式電腦','臺(tái)式機(jī)','戴爾','2899',default,default); insert into goods values(0,'imac me086ch/a 21.5英寸一體電腦','臺(tái)式機(jī)','蘋果','9188',default,default); insert into goods values(0,'at7-7414lp 臺(tái)式電腦 linux ）','臺(tái)式機(jī)','宏碁','3699',default,default); insert into goods values(0,'z220sff f4f06pa工作站','服務(wù)器/工作站','惠普','4288',default,default); insert into goods values(0,'poweredge ii服務(wù)器','服務(wù)器/工作站','戴爾','5388',default,default); insert into goods values(0,'mac pro專業(yè)級(jí)臺(tái)式電腦','服務(wù)器/工作站','蘋果','28888',default,default); insert into goods values(0,'hmz-t3w 頭戴顯示設(shè)備','筆記本配件','索尼','6999',default,default); insert into goods values(0,'商務(wù)雙肩背包','筆記本配件','索尼','99',default,default); insert into goods values(0,'x3250 m4機(jī)架式服務(wù)器','服務(wù)器/工作站','ibm','6888',default,default); insert into goods values(0,'商務(wù)雙肩背包','筆記本配件','索尼','99',default,default);插入數(shù)據(jù)后的商品表 -
1.2、SQL語句的強(qiáng)化練習(xí)
-
(1)悲伶、查詢類型cate_name為 '超級(jí)本' 的商品名稱艾恼、價(jià)格
select name,price from goods where cate_name = "超級(jí)本";查詢類型cate_name為 '超級(jí)本' 的商品名稱、價(jià)格 -
(2)麸锉、顯示商品的種類
// 僅僅顯示商品的種類钠绍，簡單快速 select distinct cate_name from goods; // 顯示商品的種類，功能更強(qiáng)大花沉，需要的時(shí)間比上面的 distinct 去重時(shí)間長 select cate_name from goods group by cate_name;顯示商品的種類 -
(3)柳爽、求所有電腦產(chǎn)品的平均價(jià)格,并且保留兩位小數(shù)
select round(avg(price),2) from goods; -
(4)、顯示每種商品的平均價(jià)格
select cate_name,round(avg(price),2) from goods group by cate_name;顯示每種商品的平均價(jià)格 -
(5)碱屁、查詢每種類型的商品中 最貴磷脯、最便宜、平均價(jià)忽媒、數(shù)量
select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;查詢每種類型的商品中 最貴争拐、最便宜腋粥、平均價(jià)晦雨、數(shù)量 -
(6)架曹、查詢所有價(jià)格大于平均價(jià)格的商品，并且按價(jià)格降序排序
select id,name,price from goods where price > (select round(avg(price),2) as avg_price from goods) order by price desc; -
(7)闹瞧、查詢每種類型中最貴的電腦信息
select * from goods inner join ( select cate_name, max(price) as max_price, min(price) as min_price, avg(price) as avg_price, count(*) from goods group by cate_name ) as goods_new_info on goods.cate_name=goods_new_info.cate_name and goods.price=goods_new_info.max_price;
-
-
1.3绑雄、創(chuàng)建 "商品分類"" 表 (也就是把上面的
goods表拆為多個(gè)表)，目標(biāo)把cate_name與brand_name拆為單獨(dú)的good_cates與good_brands兩個(gè)表,最后把goods里面的cate_name與brand_name改為cate_id與brand_id,類型改為int unsigned,也就是與新表的主鍵id類型保持一致create table if not exists good_cates( id int unsigned primary key auto_increment, name varchar(40) not null );-
查詢goods表中商品的種類
select cate_name from goods group by cate_name; -
將上面分組結(jié)果寫入到goods_cates數(shù)據(jù)表
insert into good_cates (name) select cate_name from goods group by cate_name;將上面分組結(jié)果寫入到goods_cates數(shù)據(jù)表提示：
insert into good_cates (name)后面不需要再加 values .....奥邮，查出來的數(shù)據(jù)直接寫入即可
-
-
1.4万牺、同步表數(shù)據(jù)
通過 goods_cates 數(shù)據(jù)表來更新goods表的 cate_name，也就是cate_name的數(shù)據(jù)用表goods_cates里面的id替換update goods as g inner join good_cates as c on g.cate_name=c.name set g.cate_name=c.id;
-
1.5洽腺、修改表結(jié)構(gòu)
-
查看 goods 的數(shù)據(jù)表結(jié)構(gòu),會(huì)發(fā)現(xiàn) cate_name 對(duì)應(yīng)的類型為 varchar 但是存儲(chǔ)的都是數(shù)字脚粟，要更改其類型
desc goods;查看表結(jié)構(gòu) -
通過
alter table語句修改表結(jié)構(gòu),也就是修改 cate_name 的類型為int unsigned,并把名字改為cate_idalter table goods change cate_name cate_id int unsigned not null;通過 `alter table語句` 修改表結(jié)構(gòu),也就是修改 cate_name 的類型為 `int unsigned`,并把名字改為 `cate_id`提示： change 與 modify 區(qū)別：
- change : 修改表-修改字段：重命名版(字段名字、類型蘸朋、及約束 都可以改變),如：
alter table 表名 change 原名 新名 類型及約束; - modify: 修改表-修改字段：不重命名版(字段名字不變核无，類型以及約束可以改變),如：
alter table 表名 modify 列名 類型及約束;
- change : 修改表-修改字段：重命名版(字段名字、類型蘸朋、及約束 都可以改變),如：
-
-
1.6、外鍵
-
在 goods_cates 表中插入記錄
insert into goods_cates(name) values ('路由器'),('交換機(jī)'),('網(wǎng)卡');在 goods_cates 表中插入記錄 -
在 goods 數(shù)據(jù)表中寫入任意記錄
insert into goods (name,cate_id,brand_name,price) values('LaserJet Pro P1606dn 黑白激光打印機(jī)', 12, '華碩','1849'); -
查詢所有商品的詳細(xì)信息 (通過內(nèi)連接)
select g.id,g.name,c.name,g.price from goods as g inner join good_cates as c on g.cate_id=c.id;查詢所有商品的詳細(xì)信息 (通過內(nèi)連接) 如何防止無效信息的插入,就是可以在插入前判斷類型或者品牌名稱是否存在呢? 可以使用之前講過的外鍵來解決
外鍵約束:對(duì)數(shù)據(jù)的有效性進(jìn)行驗(yàn)證
關(guān)鍵字: foreign key,只有 innodb數(shù)據(jù)庫引擎 支持外鍵約束
對(duì)于已經(jīng)存在的數(shù)據(jù)表 如何更新外鍵約束
-
讓 cate_id 成為外鍵
把 goods商品里面的最后一條數(shù)據(jù)刪掉藕坯，不然后面后面外鍵的設(shè)置會(huì)失敗delete from goods where id=22;設(shè)置 cate_id 成為外鍵
alter table goods add foreign key (cate_id) references good_cates(id);這個(gè)時(shí)候我們?nèi)绻诓迦胪?goods里面插入一條包含 cate_id = 12 的就會(huì)報(bào)錯(cuò)了团南，因?yàn)?12 在 good_cates 表里找不到
insert into goods (name,cate_id,price) values('LaserJet Pro P1606dn 黑白激光打印機(jī)', 12, '華碩','1849');報(bào)錯(cuò)信息：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (
jing_dong.goods, CONSTRAINTgoods_ibfk_1FOREIGN KEY (cate_id) REFERENCESgood_cates(id))
mysql>
-
-
1.7、如何在創(chuàng)建數(shù)據(jù)表的時(shí)候就設(shè)置 外鍵約束 呢?炼彪，注意: goods 中的 cate_id 的類型一定要和 goods_cates 表中的 id 類型一致
- 創(chuàng)表吐根，設(shè)置外鍵
create table goods( id int primary key auto_increment not null, name varchar(40) default '', price decimal(5,2), cate_id int unsigned, brand_id int unsigned, is_show bit default 1, is_saleoff bit default 0, foreign key(cate_id) references goods_cates(id), foreign key(brand_id) references goods_brands(id) );-
如何取消外鍵約束：
需要先獲取外鍵約束名稱,該名稱系統(tǒng)會(huì)自動(dòng)生成,可以通過查看表創(chuàng)建語句來獲取名稱
show create table goods;獲取名稱之后就可以根據(jù)名稱來刪除外鍵約束
alter table goods drop foreign key 外鍵名稱;獲取名稱之后就可以根據(jù)名稱來刪除外鍵約束提示： 在實(shí)際開發(fā)中,很少會(huì)使用到外鍵約束,會(huì)極大的降低表更新的效率
-
1.8、上面我們已經(jīng)完成了 表
good_cates的操作,下面我們來快速的創(chuàng)建一下good_brands表-
(1)辐马、通過create...select來創(chuàng)建數(shù)據(jù)表并且同時(shí)寫入記錄,一步到位
在創(chuàng)建數(shù)據(jù)表的時(shí)候一起插入數(shù)據(jù)create table good_brands ( id int unsigned primary key auto_increment, name varchar(40) not null) select brand_name as name from goods group by brand_name;創(chuàng)建數(shù)據(jù)表的時(shí)候一起插入數(shù)據(jù)注意：需要對(duì)brand_name 用as起別名拷橘，否則name字段就沒有值
-
(2)、同步數(shù)據(jù)
通過goods_brands數(shù)據(jù)表來更新goods數(shù)據(jù)表update goods as g inner join good_brands as b on g.brand_name=b.name set g.brand_name=b.id; -
(3)喜爷、修改表結(jié)構(gòu)
-
查看 goods 的數(shù)據(jù)表結(jié)構(gòu),會(huì)發(fā)現(xiàn) cate_name 和 brand_name對(duì)應(yīng)的類型為 varchar 但是存儲(chǔ)的都是數(shù)字
desc goods; -
通過alter table語句修改表結(jié)構(gòu)(下面是可以同事修改另兩個(gè)字段的膜楷，cate_name我們已經(jīng)修改過)
alter table goods change cate_name cate_id int unsigned not null,change brand_name brand_id int unsigned not null;alter table goods change brand_name brand_id int unsigned not null;通過alter table語句修改表結(jié)構(gòu)
-
-
(4)、brand_id 設(shè)置為 goods表的 外鍵
alter table goods add foreign key (brand_id) references good_brands(id); -
(5)贞奋、取消外鍵約束
需要先獲取外鍵約束名稱,該名稱系統(tǒng)會(huì)自動(dòng)生成,可以通過查看表創(chuàng)建語句來獲取名稱
show create table goods; 獲取外鍵約束名稱show create table goods;獲取名稱之后就可以根據(jù)名稱來刪除外鍵約束
alter table goods drop foreign key 外鍵名稱;
-
二赌厅、python安裝pymysql 與 Python 操作
2.1、在終端安裝
pymysql： pip3 install pymysql-
2.2轿塔、Python 中操作 MySQL 步驟
Python 中操作 MySQL 步驟-
(1)特愿、引入模塊：在py文件中引入pymysql模塊
from pymysql import * -
(2)、Connection 對(duì)象
用于建立與數(shù)據(jù)庫的連接
-
創(chuàng)建對(duì)象：調(diào)用
connect()方法conn=connect(參數(shù)列表) 例如： conn = connect(host='域名或者IP',port=3306,database='數(shù)據(jù)庫名',user='用戶名',password='密碼',charset='utf8') 參數(shù)host：連接的mysql主機(jī)勾缭，如果本機(jī)是'localhost'揍障，服務(wù)器的話就是：域名或者IP
參數(shù)port：連接的mysql主機(jī)的端口，默認(rèn)是3306
參數(shù)database：數(shù)據(jù)庫的名稱
參數(shù)user：連接的用戶名
參數(shù)password：連接的密碼
參數(shù)charset：通信采用的編碼方式俩由，推薦使用utf8
-
(3)毒嫡、對(duì)象的方法
- close()關(guān)閉連接
- commit()提交
- cursor()返回Cursor(游標(biāo))對(duì)象，用于執(zhí)行sql語句并獲得結(jié)果
-
(4)幻梯、Cursor對(duì)象
用于執(zhí)行sql語句兜畸，使用頻度最高的語句為select努释、insert、update咬摇、delete
-
獲取Cursor對(duì)象：調(diào)用Connection對(duì)象的cursor()方法
cs1=conn.cursor()
-
(5)伐蒂、對(duì)象的方法
close()關(guān)閉
-
execute(operation [, parameters ])執(zhí)行語句，返回受影響的行數(shù)肛鹏，主要用于執(zhí)行insert逸邦、update、delete語句在扰，也可以執(zhí)行create缕减、alter、drop等語句
count = cs1.execute('select * from goods') # 返回是表中數(shù)據(jù)的個(gè)數(shù) fetchone()執(zhí)行查詢語句時(shí)芒珠，獲取查詢結(jié)果集的第一個(gè)行數(shù)據(jù)烛卧，返回一個(gè)元組
fetchall()執(zhí)行查詢時(shí)，獲取結(jié)果集的所有行妓局，一行構(gòu)成一個(gè)元組总放，再將這些元組裝入一個(gè)元組返回
-
fetchmany(數(shù)量)執(zhí)行查詢時(shí)，獲取結(jié)果集的所有行好爬，一行構(gòu)成一個(gè)元組局雄，再將這些元組裝入一個(gè)元組返回，也就是元組套元組
cursor的三個(gè)方法
-
(6)存炮、對(duì)象的屬性
- rowcount只讀屬性炬搭，表示最近一次execute()執(zhí)行后受影響的行數(shù)
- connection獲得當(dāng)前連接對(duì)象
-
-
2.3、python操作sql：
查穆桂、增宫盔、改、刪數(shù)據(jù)-
共用代碼
from pymysql import * def main(): # 創(chuàng)建Connection連接 conn = connect(host='ironman.ren',port=3306,database='jing_dong',user='root',password='456123love',charset='utf8') # 獲得Cursor(游標(biāo))對(duì)象 cursor = conn.cursor() """"增刪改查的代碼""" # 關(guān)閉Cursor對(duì)象 cursor.close() # 關(guān)閉Connection對(duì)象 conn.close() -
(1)享完、查詢數(shù)據(jù)
查詢數(shù)據(jù)庫某個(gè)表的全部數(shù)據(jù)個(gè)數(shù)
count = cursor.execute('select * from goods') print("總數(shù)量count=%d"%count) 打印結(jié)果：21查詢數(shù)表goods里面所有的信息：
fetchall()cursor.execute('select * from goods') print(cursor.fetchall())查詢數(shù)表goods里面部分的信息：
fetchmany(數(shù)量)print(cursor.fetchmany(3))查詢數(shù)表goods里面一條信息：
fetchone()print(cursor.fetchone()) -
(2)灼芭、增加數(shù)據(jù)：執(zhí)行insert語句，并返回受影響的行數(shù)：添加一條數(shù)據(jù)
count = cursor.execute('insert into goods values(0,"笨蛋 超極本",5,4,4000,0,0)') # 提交之前的操作般又，如果之前已經(jīng)之執(zhí)行過多次的execute彼绷，那么就都進(jìn)行提交 conn.commit() -
(3)、改：更新數(shù)據(jù)
count = cursor.execute('update goods set name = "超級(jí)笨蛋 超極本" where name = "笨蛋 超極本" ') conn.commit() -
(4)茴迁、刪：數(shù)據(jù)
count = cursor.execute('delete from goods where name="超級(jí)笨蛋 超極本" ') conn.commit()
- 提示： 除了
查詢外寄悯，增、改堕义、刪都需要conn.commit() -
conn.commit(): 提交之前的操作猜旬，如果之前已經(jīng)之執(zhí)行過多次的execute，那么就都進(jìn)行提交
-
-
2.4、python操作goods表：所有數(shù)據(jù)洒擦、品牌分類椿争、所有的分類
from pymysql import * class JD(object): def __init__(self): # 1、創(chuàng)建連接 self.connection = connect(host='ironman.ren', port=3306, database='jing_dong', user='root', password='456123love', charset='utf8') # 2秘遏、獲取cursor(游標(biāo))對(duì)象 self.cursor = self.connection.cursor() def __del__(self): # 5丘薛、關(guān)閉游標(biāo) self.cursor.close() # 6嘉竟、關(guān)閉數(shù)據(jù)庫連接 self.connection.close() @staticmethod def print_munu(): print("京東商品查詢") print("1.所有商品") print("2.所有商品分類") print("3.所有商品品牌分類") return input("請(qǐng)輸入功能對(duì)應(yīng)的序號(hào)：") def execute_sql(self,sql): count = self.cursor.execute(sql) print(count) """ 所有數(shù)據(jù)邦危、品牌分類、所有的分類 """ for item in self.cursor.fetchall(): print(item) def select_run(self): while True: num = self.print_munu() if num == "1": # 所有商品 self.show_all_items() elif num == "2": # 所有商品分類 self.show_all_cates() elif num == "3": # 所有商品品牌分類 self.show_all_brand() def show_all_items(self): # 3舍扰、使用游標(biāo)對(duì)象 # 3.1倦蚪、查詢這個(gè)good表 sql = 'select * from goods' self.execute_sql(sql) def show_all_brand(self): # 3.2、查詢這個(gè)good表 sql = 'select * from good_brands' self.execute_sql(sql) def show_all_cates(self): # 3.3边苹、查詢這個(gè)good表 sql = 'select * from good_cates' self.execute_sql(sql) def main(): # 1陵且、創(chuàng)建京東對(duì)象 jd = JD() # 2、調(diào)用查詢 jd.select_run() if __name__ == "__main__": main()
三个束、參數(shù)化(sql防注入)
-
3.1慕购、我們來看一個(gè)用戶搜索商品的例子，根據(jù)用戶輸入的信息進(jìn)行查詢
from pymysql import * class JD(object): def __init__(self): # 1茬底、創(chuàng)建連接 self.connection = connect(host='ironman.ren', port=3306, database='jing_dong', user='root', password='456123love', charset='utf8') # 2沪悲、獲取cursor(游標(biāo))對(duì)象 self.cursor = self.connection.cursor() def __del__(self): # 5、關(guān)閉游標(biāo) self.cursor.close() # 6阱表、關(guān)閉數(shù)據(jù)庫連接 self.connection.close() def select_commodity(self): while True: select_content = input("請(qǐng)輸入搜索的商品內(nèi)容：") params = [select_content] self.cursor.execute("select * from goods where name = %s",params) for item in self.cursor.fetchall(): print(item) def main(): jd = JD() jd.select_commodity() if __name__ == "__main__": main() -
3.2殿如、提一下 3.1中的
def select_commodity(self):方法里面的sql拼接的問題,上面用的安全方式-
安全方式：把搜索的內(nèi)容放到數(shù)組里面，在
execute()里面最爬，如果要是有多個(gè)參數(shù)涉馁，需要進(jìn)行參數(shù)化，那么params = [數(shù)值1, 數(shù)值2....]爱致，此時(shí)sql語句中有多個(gè)%s即可 烤送，sql語句會(huì)自動(dòng)完成，不需要我們來做select_content = input("請(qǐng)輸入搜索的商品內(nèi)容：") # 構(gòu)造參數(shù)列表 params = [select_content] self.cursor.execute("select * from goods where name = %s",params) -
不安全方式：如果用戶輸入：' ' or 7=7,就會(huì)查出你數(shù)據(jù)庫所有的數(shù)據(jù)糠悯，假如用戶使用的刪除操作胯努，很可能把這個(gè)表給你全刪了??，sql注入防不勝防
select_content = input("請(qǐng)輸入搜索的商品內(nèi)容：") sql = "select * from goods where name = %s"% select_content self.cursor.execute(sql)
-
提示：sql語句的參數(shù)化逢防，可以有效防止sql注入
友情提示：不要通過sql注入做壞事哦
