拆表-
- 創(chuàng)建商品分類表
create table if not exists goods_cates(?
? ? id int unsigned primary key auto_increment,
? ? name varchar(40) not null-- 類別名稱 40字長度 不允許為空
);
-- 查詢goods表中商品的種類
select cate_name from goods group by cate_name;?
-- 將分組結(jié)果寫入到goods_cates數(shù)據(jù)表
insert into goods_cates (name) select cate_name from goods group by cate_name;-- select前不能加values??
-- 通過goods_cates數(shù)據(jù)表來更新goods表
update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;
-- 解釋：更新goods表起別名g —— inner join goods_cates關(guān)聯(lián)類別表 —— 給類別表起名c —— on后面相當(dāng)條件 —— g.cate_name=c.name 關(guān)聯(lián)條件商品表的類別名稱=類別表名稱 —— set g.cate_name=c.id設(shè)置商品表的類別名稱=……
-- 通過alter table語句修改表結(jié)構(gòu)（既改名字又改類型）change：重命名
alter table goods change cate_name cate_id int unsigned not null;-- 類別的名稱cate_name重命名為cate_id
-- 關(guān)聯(lián)外鍵
alter table goods add foreign key (cate_id) references goods_cates(id);
-- 給alter table goods添加一個(gè)foreign key（外鍵）給cate_id設(shè) —— references：關(guān)聯(lián) 和goods_cates類別表的id關(guān)聯(lián)
-- 在 goods 數(shù)據(jù)表中插入商品（用來測試外鍵的）
insert into goods (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印機(jī)', 12, 4,'1849');
-- 取消外鍵
alter table goods drop foreign key goods_ibfk_1;
二患雏、goods_brands
-- unsigned:無符號（非負(fù)數(shù)）
-- 創(chuàng)建商品分類表
create table if not exists goods_brands(?
? ? id int unsigned primary key auto_increment,
? ? name varchar(40) not null-- 類別名稱 40字長度 不允許為空
);
-- 查詢goods表中商品的種類
select brand_name from goods group by brand_name;
-- 將分組結(jié)果寫入到goods_cates數(shù)據(jù)表
insert into goods_brands (name) select brand_name from goods group by brand_name;-- select前不能加values??
-- 通過goods_cates數(shù)據(jù)表來更新goods表
-- 更新goods表起別名g —— inner join goods_cates關(guān)聯(lián)類別表 —— 給類別表起名c —— on后面相當(dāng)條件 —— g.cate_name=c.name 關(guān)聯(lián)條件商品表的類別名稱=類別表名稱 —— set g.cate_name=c.id設(shè)置商品表的類別名稱=……
update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;
-- 分別在goods_cates和goods_brands類別表中插入記錄
insert into goods_brands(name) values ('路由器'),('交換機(jī)'),('網(wǎng)卡');
-- 通過alter table語句修改表結(jié)構(gòu)（既改名字又改類型）change：重命名
alter table goods change brand_name brand_id int unsigned not null;-- 類別的名稱brand_name重命名為brand_id
-- 關(guān)聯(lián)外鍵
-- 給alter table goods添加一個(gè)foreign key（外鍵）給cate_id設(shè) —— references：關(guān)聯(lián) 和goods_cates類別表的id關(guān)聯(lián)
alter table goods add foreign key (brand_id) references goods_brands(id);
-- 在 goods 數(shù)據(jù)表中插入商品（用來測試外鍵的）
insert into goods (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印機(jī)', 12, 4,'1849');
-- 取消外鍵
alter table goods drop foreign key goods_ibfk_2;
Python 中操作 MySQL 步驟
1郭毕、引入模塊
在py文件中引入pymysql模塊
frompymysqlimport*
2浇辜、Connection 對象
用于建立與數(shù)據(jù)庫的連接
創(chuàng)建對象：調(diào)用connect()方法
conn=connect(參數(shù)列表)
參數(shù)host：連接的mysql主機(jī)朋魔，如果本機(jī)是'localhost'
參數(shù)port：連接的mysql主機(jī)的端口岖研，默認(rèn)是3306
參數(shù)database：數(shù)據(jù)庫的名稱
參數(shù)user：連接的用戶名
參數(shù)password：連接的密碼
參數(shù)charset：通信采用的編碼方式，推薦使用utf8
對象的方法
close()關(guān)閉連接
commit()提交
cursor()返回Cursor對象铺厨，用于執(zhí)行sql語句并獲得結(jié)果
3缎玫、Cursor對象
用于執(zhí)行sql語句，使用頻度最高的語句為select解滓、insert赃磨、update、delete
獲取Cursor對象：調(diào)用Connection對象的cursor()方法
cs1=conn.cursor()
對象的方法
close()關(guān)閉
execute(operation [, parameters ])執(zhí)行語句洼裤，返回受影響的行數(shù)邻辉，主要用于執(zhí)行insert、update腮鞍、delete語句值骇，也可以執(zhí)行create、alter移国、drop等語句
fetchone()執(zhí)行查詢語句時(shí)吱瘩，獲取查詢結(jié)果集的第一個(gè)行數(shù)據(jù)，返回一個(gè)元組
fetchall()執(zhí)行查詢時(shí)迹缀，獲取結(jié)果集的所有行使碾，一行構(gòu)成一個(gè)元組，再將這些元組裝入一個(gè)元組返回
對象的屬性
rowcount只讀屬性祝懂，表示最近一次execute()執(zhí)行后受影響的行數(shù)
connection獲得當(dāng)前連接對象
四票摇、增刪改查
1、增刪改
frompymysqlimport*defmain():# 創(chuàng)建Connection連接conn = connect(host='localhost',port=3306,database='jd',user='root',password='mysql',charset='utf8')# 獲得Cursor對象cs1 = conn.cursor()# 執(zhí)行insert語句砚蓬，并返回受影響的行數(shù)：添加一條數(shù)據(jù)# 增加count = cs1.execute('insert into goods_cates(name) values("硬盤")')#打印受影響的行數(shù)print(count)? ? count = cs1.execute('insert into goods_cates(name) values("光盤")')? ? print(count)# # 更新# count = cs1.execute('update goods_cates set name="機(jī)械硬盤" where name="硬盤"')# # 刪除# count = cs1.execute('delete from goods_cates where id=6')# 提交之前的操作矢门，如果之前已經(jīng)之執(zhí)行過多次的execute，那么就都進(jìn)行提交conn.commit()# 關(guān)閉Cursor對象cs1.close()# 關(guān)閉Connection對象conn.close()if__name__ =='__main__':? ? main()
2灰蛙、查詢一行數(shù)據(jù)
frompymysqlimport*defmain():# 創(chuàng)建Connection連接conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jd',charset='utf8')# 獲得Cursor對象cs1 = conn.cursor()# 執(zhí)行select語句祟剔，并返回受影響的行數(shù)：查詢一條數(shù)據(jù)count = cs1.execute('select id,name from goods where id>=4')# 打印受影響的行數(shù)print("查詢到%d條數(shù)據(jù):"% count)foriinrange(count):# 獲取查詢的結(jié)果result = cs1.fetchone()# 打印查詢的結(jié)果print(result)# 獲取查詢的結(jié)果# 關(guān)閉Cursor對象cs1.close()? ? conn.close()if__name__ =='__main__':? ? main()
3、查詢多行數(shù)據(jù)
frompymysqlimport*defmain():# 創(chuàng)建Connection連接conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jd',charset='utf8')# 獲得Cursor對象cs1 = conn.cursor()# 執(zhí)行select語句摩梧，并返回受影響的行數(shù)：查詢一條數(shù)據(jù)count = cs1.execute('select id,name from goods where id>=4')# 打印受影響的行數(shù)print("查詢到%d條數(shù)據(jù):"% count)# for i in range(count):#? ? # 獲取查詢的結(jié)果#? ? result = cs1.fetchone()#? ? # 打印查詢的結(jié)果#? ? print(result)#? ? # 獲取查詢的結(jié)果result = cs1.fetchall()? ? print(result)# 關(guān)閉Cursor對象cs1.close()? ? conn.close()if__name__ =='__main__':? ? main()
五物延、參數(shù)化
sql語句的參數(shù)化，可以有效防止sql注入
注意：此處不同于python的字符串格式化障本，全部使用%s占位
frompymysqlimport*defmain():find_name = input("請輸入物品名稱：")# 創(chuàng)建Connection連接conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')# 獲得Cursor對象cs1 = conn.cursor()# # 非安全的方式# # 輸入 " or 1=1 or "? (雙引號也要輸入)# sql = 'select * from goods where name="%s"' % find_name# print("""sql===>%s<====""" % sql)# # 執(zhí)行select語句教届，并返回受影響的行數(shù)：查詢所有數(shù)據(jù)# count = cs1.execute(sql)# 安全的方式# 構(gòu)造參數(shù)列表params = [find_name]# 執(zhí)行select語句，并返回受影響的行數(shù)：查詢所有數(shù)據(jù)count = cs1.execute('select * from goods where name=%s', params)# 注意：# 如果要是有多個(gè)參數(shù)驾霜，需要進(jìn)行參數(shù)化# 那么params = [數(shù)值1, 數(shù)值2....]案训，此時(shí)sql語句中有多個(gè)%s即可 # 打印受影響的行數(shù)print(count)# 獲取查詢的結(jié)果# result = cs1.fetchone()result = cs1.fetchall()# 打印查詢的結(jié)果print(result)# 關(guān)閉Cursor對象cs1.close()# 關(guān)閉Connection對象conn.close()if__name__ =='__main__':? ? main()
