一、準備MySQL資料

-- 建立 "京東" 資料庫
create database jing_dong charset=utf8;

-- 使用 "京東" 資料庫
use jing_dong;

-- 建立一個商品goods資料表
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
);

-- 向goods表中插入資料

insert into goods values(0,'r510vc 15.6英寸筆記本','筆記本','華碩','3399',default,default); 
insert into goods values(0,'y400n 14.0英寸膝上型電腦','筆記本','聯想','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 超極本','超級本','聯想','4880',default,default); 
insert into goods values(0,'u330p 13.3英寸超極本','超級本','聯想','4299',default,default); 
insert into goods values(0,'svp13226scb 觸控超極本','超級本','索尼','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英寸一體電腦 ','桌上型電腦','聯想','3499',default,default); 
insert into goods values(0,'vostro 3800-r1206 臺式電腦','桌上型電腦','戴爾','2899',default,default); 
insert into goods values(0,'imac me086ch/a 21.5英寸一體電腦','桌上型電腦','蘋果','9188',default,default); 
insert into goods values(0,'at7-7414lp 臺式電腦 linux ）','桌上型電腦','巨集碁','3699',default,default); 
insert into goods values(0,'z220sff f4f06pa工作站','伺服器/工作站','惠普','4288',default,default); 
insert into goods values(0,'poweredge ii伺服器','伺服器/工作站','戴爾','5388',default,default); 
insert into goods values(0,'mac pro專業級臺式電腦','伺服器/工作站','蘋果','28888',default,default); 
insert into goods values(0,'hmz-t3w 頭戴顯示裝置','筆記本配件','索尼','6999',default,default); 
insert into goods values(0,'商務雙肩揹包','筆記本配件','索尼','99',default,default); 
insert into goods values(0,'x3250 m4機架式伺服器','伺服器/工作站','ibm','6888',default,default); 
insert into goods values(0,'商務雙肩揹包','筆記本配件','索尼','99',default,default);

二、SQL演練

1、將查詢結果插入到另一張表中

（1）、建立商品分類表

-- 建立商品分類表
create table if not exists goods_cates(
    id int unsigned primary key auto_increment,
    name varchar(40) not null
);

（2）、查詢goods中的所有商品種類

select cate_name from goods group by cate_name;

（3）、將查詢結果插入到商品分類表中

insert into goods_cates (name) select cate_name from goods group by cate_name;

2、根據商品分類表中的資料更新商品表資料

update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

3、修改商品表中的欄位並且和商品分類表關聯

（1）、修改表欄位

alter table goods change cate_name cate_id int unsigned not null;

（2）、關聯外來鍵

alter table goods add foreign key (cate_id) references goods_cates(id);

4、建立商品品牌表的同時插入資料

create table goods_brands (
    id int unsigned primary key auto_increment,
    name varchar(40) not null) 
    select brand_name as name from goods group by brand_name;

5、取消表中關聯的外來鍵

-- 需要先獲取外來鍵約束名稱,該名稱系統會自動生成,可以通過檢視錶建立語句來獲取名稱
show create table goods;
-- 獲取名稱之後就可以根據名稱來刪除外來鍵約束
alter table goods drop foreign key 外來鍵名稱;

三、Python與MySQL互動

1、ubuntu中安裝pymysql模組

root@ubuntu:/# pip install pymysql

2、Python中操作MySQL步驟

3、引入模組

在.py檔案中引入pymysql模組

from pymysql import *

4、Connection物件

（1）、作用

        用於建立與資料庫的連結

（2）、建立Connection物件

conn=connect(引數列表)

引數host：連線的mysql主機ip，如果本機是'localhost'

引數port：連線的mysql主機的埠，預設是3306

引數database：資料庫的名稱

引數user：連線的使用者名稱

引數password：連線的密碼

引數charset：通訊採用的編碼方式，推薦使用utf8

（3）、物件的方法

close()關閉連線

commit()提交

rollback()回滾

cursor()返回Cursor物件，用於執行sql語句並獲得結果

5、Cursor物件

（1）、作用

        用於執行sql語句，使用頻度最高的語句為select、insert、update、delete

        獲取Cursor物件：呼叫Connection物件的cursor()方法

（2）、建立物件的方法

cs1=conn.cursor()

（3）、cursor物件的API

close()關閉

execute(operation [, parameters ])執行語句，返回受影響的行數，主要用於執行insert、update、delete語句，也可以執行create、alter、drop等語句

fetchone()執行查詢語句時，獲取查詢結果集的第一個行資料，返回一個元組

fetchall()執行查詢時，獲取結果集的所有行，一行構成一個元組，再將這些元組裝入一個元組返回

（4）、cursor物件的屬性

rowcount只讀屬性，表示最近一次execute()執行後受影響的行數

connection獲得當前連線物件

6、簡單的增加、查詢案例

from pymysql import connect


class JD(object):

    def __init__(self):
        # 建立連結
        self.conn = connect(host='192.168.37.128', port=3306, user='root', password='admin123', database='jing_dong',
                       charset='utf8')
        # 獲取cursor物件
        self.cursor = self.conn.cursor()

    def __del__(self):
        # 關閉cursor物件
        self.cursor.close()
        self.conn.close()

    def execute_sql(self,sql):
        self.cursor.execute(sql)
        # 遍歷資料
        for good in self.cursor.fetchall():
            print(good)

    def show_all_goods(self):
        """顯示所有商品"""
        # 編寫sql
        sql = "SELECT * FROM goods"
        # 執行sql
        self.execute_sql(sql)

    def show_all_cates(self):
        # 編寫sql
        sql = "select * from goods_cates"
        self.execute_sql(sql)

    def insert_goods(self):
        cate = input("請輸入要新增的分類名稱")
        sql = """insert into goods_cates(name) values("%s")""" % cate
        # 執行sql
        self.cursor.execute(sql)
        self.conn.commit()

    @staticmethod
    def print_menu():
        print("---------- welcome to JD ----------")
        print("1：查詢所有商品")
        print("2：查詢所有商品分類")
        print("3：查詢所有商品品牌")
        return input("---------- 請輸入功能對應的序號 ----------")

    def run(self):
        while True:
            num = self.print_menu()
            if num == "1":
                # 查詢所有商品
                self.show_all_goods()
            elif num == "2":
                # 查詢所有分類
                self.show_all_cates()
            elif num == "3":
                # 新增商品
                self.insert_goods()


def main():
    # 1.建立一個京東商城物件
    jd = JD()

    # 2.呼叫這個物件的run方法，讓其執行
    jd.run()


if __name__ == "__main__":
    main()

7、防止SQL隱碼攻擊的新增

from pymysql import connect


class JD(object):

    def __init__(self):
        # 建立連結
        self.conn = connect(host='192.168.37.128', port=3306, user='root', password='admin123', database='jing_dong',
                       charset='utf8')
        # 獲取cursor物件
        self.cursor = self.conn.cursor()

    def __del__(self):
        # 關閉cursor物件
        self.cursor.close()
        self.conn.close()

    def run(self):
        find_name = input("請輸入要查詢的商品名稱：")
        sql = "select * from goods where name = %s"
        """
            將查詢的引數放入列表中，使用execute的帶2個引數的方法執行查詢，即可以防止sql注入問題
        """
        param = [find_name]
        self.cursor.execute(sql, param)
        for temp in self.cursor.fetchall():
            print(temp)


def main():
    # 1.建立一個京東商城物件
    jd = JD()

    # 2.呼叫這個物件的run方法，讓其執行
    jd.run()


if __name__ == "__main__":
    main()