MySQL练习：达标数据库创建及连接python（京东项目）

学校的达标课考核测试题目较为基础的一个考核题，京东售卖项目。

达标题目要求，一个主表三个分类表根据主表创建，两个外键表及拆分一个子表。

下面是数据库创建过程：

create database jingdong char set = utf8;

use jingdong;

# 商品表
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(50) not null, #品牌名

    price decimal(10,3) not null default 0,
    is_show bit not null default 1, #是否显示

    is_saleoff bit not null default 0 #是否售馨

);


insert into goods values (0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default),
                         (0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default),
                         (0,'g150th 15.6英寸笔记本','游戏本','雷神','8499',default,default),
                         (0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default),
                         (0,'x240 超级本','超级本','联想','4880',default,default),
                         (0,'u330p 13.3英寸超级本','超级本','联想','4299',default,default),
                         (0,'svp13226scb 触控超级本','超级本','索尼','7999',default,default),
                         (0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default),
                         (0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default),
                         (0,'ipad mini 配备retina显示屏','平板电脑','苹果','2788',default,default),
                         (0,'ideacentre c340 20英寸一体电脑','台式机','联想','3499',default,default),
                         (0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default),
                         (0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default),
                         (0,'at7-7414lp 台式电脑 linux','台式机','苹果','3699',default,default),
                         (0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default),
                         (0,'powereedge ii服务器','服务器/工作站','戴尔','5388',default,default),
                         (0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default),
                         (0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default),
                         (0,'商务双肩背包','笔记本配件','索尼','99',default,default),
                         (0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default),
                         (0,'商务双肩背包','笔记本配件','索尼','99',default,default);




# 订单表
create table orders(
    id int unsigned primary key  auto_increment not null ,
    order_data_time datetime not null ,
    customers_id int unsigned not null
);

# 顾客表
create table customers(
    id int unsigned primary key auto_increment not null ,
    name varchar(150) not null ,
    address varchar(150) not null ,
    tel varchar(20) not null ,
    passwd varchar(30) not null
);

# 订单详情表
create table order_detail(
    id int unsigned auto_increment primary key not null ,
    quality int unsigned not null ,
    order_id int unsigned not null ,
    good_id int unsigned not null
);

# 设置外键
alter table orders add foreign key (customers_id) references customers(id);
alter table order_detail add foreign key (order_id) references orders(id);
alter table order_detail add foreign key (good_id) references goods(id);

-- 拆分商品类子表
-- 操作
CREATE TABLE IF NOT EXISTS goods_brand(
  id INT UNSIGNED PRIMARY KEY NOT NULL auto_increment,
  name VARCHAR(48) NOT NULL
)SELECT brand_name AS name FROM goods GROUP BY brand_name;

-- 同步两表数据
UPDATE goods g INNER JOIN goods_brand b ON g.brand_name = b.name SET g.brand_name = b.id;

-- 操作三 修改字段名（保证数据属性一致）
-- 通过一个表的字段来存储另一个表的主键时候，两个字段的数据必须相同
ALTER TABLE goods CHANGE brand_name brand_id INT UNSIGNED NOT NULL;

-- 操作四添加外键
ALTER TABLE goods ADD FOREIGN KEY (brand_id) REFERENCES goods_brand(id);



-- 拆分子表操作1创建商分类表，将总表中的数据插入进去 
 create table if not exists goods_cates 
 ( 
    id int unsigned primary key auto_increment not null, 
    name varchar(50) not null 
 );
    select cate_name as name 
    from goods 
    group by cate_name; 
-- 为指定字段间接插入数据 
 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; 

python连接部分：

# python 进行数据库查询
from pymysql import *


class JD(object):
    def __init__(self):
        self.conn = connect(host="localhost", port=3306, user='root',
                       password='root', db='jingdong', charset='utf8')
        self.cursor = self.conn.cursor()

    def __del__(self):
        self.cursor.close()
        self.conn.close()

    def executr_sql(self, sql):
        self.cursor.execute(sql)
        for temp in self.cursor.fetchall():
            print(temp)

    def show_all_items(self):
        sql = 'select * from goods'
        self.executr_sql(sql)

    def show_cates(self):
        sql = 'select name from goods_cates'
        self.executr_sql(sql)

    def show_brands(self):
        sql = 'select name from goods_brand'
        self.executr_sql(sql)

    @staticmethod
    def print_menu():
        print("--------京东数据库操作--------")
        print("1.查询所有商品")
        print("2.查询所有商品分类")
        print("3.查询所有商品品牌分类")
        print("4.退出")
        num = input("请输入对应功能序列号:")
        return num

    def run(self):
        while True:
            num = self.print_menu()
            if num == "1":
                # 显示所有商品
                self.show_all_items()

            elif num == "2":
                # 显示所有商品分类
                self.show_cates()

            elif num == "3":
                # 显示所有商品品牌分类
                self.show_brands()

            elif num == "4":
                # 退出
                break
            else:
                print("输入指令有误,请重新输入：")

def main():

    # 创建对象
    jd = JD()
    # 调用run方法
    jd.run()


if __name__ == "__main__":
    main()

python打印前17行数据输出：

from pymysql import *
def main():
    conn = connect(host="localhost",
                   port=3306,
                   user='root',
                   password='root',
                   database='jingdong',
                   charset='utf8')
    cs1 = conn.cursor()
    count = cs1.execute('select id,name from goods limit 0,17',)
    print("受影响的行数：", count)
    print(cs1.fetchall())
    for i in range(count):
            res = cs1.fetchone()
            print("------")
            print(res)


    cs1.close()
    conn.close()
if __name__ == '__main__':
    main()