美文网首页
实例-MySQL-模拟京东电商数据库

实例-MySQL-模拟京东电商数据库

作者: 简一点点 | 来源:发表于2019-07-09 11:28 被阅读0次

    传智黑马Python37例子 自学中在网上找的视屏看,算是小白吧!没有其他的学习方法,之前看了一边,后面有看了一遍,把课中代码打了一边。 如果你有什么好方法赶紧给我分享啊!

    MySQL操作

    第一节:在mysql中操作例子

    一. 创建

    create database jing_dong charset=utf8;
    use jing_dong
    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 
                      );
    

    二. 导入

    insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',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英寸平板电脑','平板电脑','苹果','1999',default,default);
    insert into goods values(0,'iPad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
    insert into goods values(0,'iPad mini 配置 retine 显示屏','平板电脑','苹果','2788',default,default);
    insert into goods values(0,'ideacentre c3340 20英寸一体电脑','台式机','联想','3499',default,default);
    insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
    insert into goods values(0,'15.6 寸电脑屏保护膜','电脑配件','爱戴尔','29',default,default);
    insert into goods values(0,'优雅 复古 无线鼠标键盘','电脑配件','雷蛇','299',default,default);
    insert into goods values(0,'15寸 4K 液晶显示屏','电脑配件','索尼','1899',default,default);
    insert into goods values(0,'限量款 LOL 鼠标垫','电脑配件','唯爱','29',default,default);
    

    三. 查询

    1.简单查询
    mysql> select id as 编号,name as 商品名称,cate_name as 类型,brand_name as 品牌,price as 价钱 from goods as 商品表;
    +--------+---------------------------------------+--------------+-----------+--------+
    | 编号   | 商品名称                              | 类型         | 品牌      | 价钱   |
    +--------+---------------------------------------+--------------+-----------+--------+
    |      1 | r510vc 15.6英寸笔记本                 | 笔记本       | 华硕      |   3399 |
    |      2 | x550cc 15.6英寸笔记本                 | 笔记本       | 华硕      |   2799 |
    |      3 | x240 超极本                           | 超极本       | 联想      |   4880 |
    |      4 | u330p 13.3英寸超级本                  | 超极本       | 联想      |   4299 |
    |      5 | svp13226scb 触控超级本                | 超级本       | 索尼      |   7999 |
    |      6 | ipad mini 7.9英寸平板电脑             | 平板电脑     | 苹果      |   1999 |
    |      7 | iPad air 9.7英寸平板电脑              | 平板电脑     | 苹果      |   3388 |
    |      8 | iPad mini 配置 retine 显示屏          | 平板电脑     | 苹果      |   2788 |
    |      9 | ideacentre c3340 20英寸一体电脑       | 台式机       | 联想      |   3499 |
    |     10 | vostro 3800-r1206 台式电脑            | 台式机       | 戴尔      |   2899 |
    |     11 | 15.6 寸电脑屏保护膜                   | 电脑配件     | 爱戴尔    |     29 |
    |     12 | 优雅 复古 无线鼠标键盘                | 电脑配件     | 雷蛇      |    299 |
    |     13 | 15寸 4K 液晶显示屏                    | 电脑配件     | 索尼      |   1899 |
    |     14 | 限量款 LOL 鼠标垫                     | 电脑配件     | 唯爱      |     29 |
    +--------+---------------------------------------+--------------+-----------+--------+
    14 rows in set (0.00 sec)
    
    mysql> select id as 编号,name as 商品名称,price as 价钱,brand_name as 品牌,cate_name as 类型 from goods as 商品表  where cate_name='笔记本';
    +--------+----------------------------+--------+--------+-----------+
    | 编号   | 商品名称                   | 价钱   | 品牌   | 类型      |
    +--------+----------------------------+--------+--------+-----------+
    |      1 | r510vc 15.6英寸笔记本      |   3399 | 华硕   | 笔记本    |
    |      2 | x550cc 15.6英寸笔记本      |   2799 | 华硕   | 笔记本    |
    +--------+----------------------------+--------+--------+-----------+
    2 rows in set (0.00 sec)
    
    mysql> select id as 编号,name as 商品名称,price as 价钱,brand_name as 品牌,cate_name as 类型 from goods as 商品表  where 类型='笔记本';
    ERROR 1054 (42S22): Unknown column '类型' in 'where clause'
    
    
    mysql> select id as 编号,name as 商品名称,price as 价钱,brand_name as 品牌,cate_name as cate from goods as 商品表  where cate='笔记本';
    ERROR 1054 (42S22): Unknown column 'cate' in 'where clause'
    
    
    2.属性查询 与其他
    mysql> select distinct brand_name from goods;
    +------------+
    | brand_name |
    +------------+
    | 华硕       |
    | 联想       |
    | 索尼       |
    | 苹果       |
    | 戴尔       |
    | 爱戴尔     |
    | 雷蛇       | 
    | 唯爱       |
    +------------+
    8 rows in set (0.00 sec)
    
    -- 与 distinct 相似
    mysql> select brand_name from goods group by cate_name;
    +------------+
    | brand_name |
    +------------+
    | 华硕       |
    | 唯爱       |
    | 戴尔       |
    | 爱戴尔     |
    | 索尼       |
    | 联想       |
    | 苹果       |
    | 雷蛇       |
    +------------+
    8 rows in set (0.01 sec)
    
    -- 相比 distinct 功能较多
    mysql> select brand_name,group_concat(id) from goods group by brand_name;
    +------------+------------------+
    | brand_name | group_concat(id) |
    +------------+------------------+
    | 华硕       | 1,2              |
    | 唯爱       | 14               |
    | 戴尔       | 10               |
    | 爱戴尔     | 11               |
    | 索尼       | 5,13             |
    | 联想       | 3,4,9            |
    | 苹果       | 6,7,8            |
    | 雷蛇       | 12               |
    +------------+------------------+
    8 rows in set (0.28 sec)
    
    -- 可用 as
    mysql> select brand_name,group_concat(id) as group_id from goods group by branby brand_name;
    +------------+----------+
    | brand_name | group_id |
    +------------+----------+
    | 华硕       | 1,2      |
    | 唯爱       | 14       |
    | 戴尔       | 10       |
    | 爱戴尔     | 11       |
    | 索尼       | 5,13     |
    | 联想       | 3,4,9    |
    | 苹果       | 6,7,8    |
    | 雷蛇       | 12       |
    +------------+----------+
    8 rows in set (0.00 sec)
    
    3.最值查询
    -- 求平均值
    mysql> select avg(price) from goods;
    +------------+
    | avg(price) |xiangsi
    +------------+
    |  2871.7857 |
    +------------+
    1 row in set (0.00 sec)
    
    -- 保留两有效数字   #在以后实际开发的时候,尽量不要用小数,一般是放大10N倍存入,使用时在除
    mysql> select round(avg(price),2) from goods;
    +---------------------+
    | round(avg(price),2) |
    +---------------------+
    |             2871.79 |
    +---------------------+
    1 row in set (0.00 sec)
    
    
    
    -- 这有个问题,group_concat 不能这么用
    mysql> select brand_name,group_concat(avg(price)) from goods group by brand_name;
    ERROR 1111 (HY000): Invalid use of group function
    
    mysql> select brand_name,avg(price) from goods group by brand_name;
    +------------+------------+
    | brand_name | avg(price) |
    +------------+------------+
    | 华硕       |  3099.0000 |
    | 唯爱       |    29.0000 |
    | 戴尔       |  2899.0000 |
    | 爱戴尔     |    29.0000 |
    | 索尼       |  4949.0000 |
    | 联想       |  4226.0000 |
    | 苹果       |  2725.0000 |
    | 雷蛇       |   299.0000 |
    +------------+------------+
    8 rows in set (0.00 sec)
    
    
    -- 查询 多类 平均,最贵,最便宜,几个
    mysql> select brand_name,avg(price),max(price),min(price),count(*) from goods group by brand_name;
    +------------+------------+------------+------------+----------+
    | brand_name | avg(price) | max(price) | min(price) | count(*) |
    +------------+------------+------------+------------+----------+
    | 华硕       |  3099.0000 |       3399 |       2799 |        2 |
    | 唯爱       |    29.0000 |         29 |         29 |        1 |
    | 戴尔       |  2899.0000 |       2899 |       2899 |        1 |
    | 爱戴尔     |    29.0000 |         29 |         29 |        1 |
    | 索尼       |  4949.0000 |       7999 |       1899 |        2 |
    | 联想       |  4226.0000 |       4880 |       3499 |        3 |
    | 苹果       |  2725.0000 |       3388 |       1999 |        3 |
    | 雷蛇       |   299.0000 |        299 |        299 |        1 |
    +------------+------------+------------+------------+----------+
    8 rows in set (0.00 sec)
    
    -- 小于平均价格的
    mysql> select * from goods where price < (select avg(price) from goods);
    +----+-----------------------------------+--------------+------------+-------+---------+------------+
    | id | name                              | cate_name    | brand_name | price | is_show | is_saleoff |
    +----+-----------------------------------+--------------+------------+-------+---------+------------+
    |  2 | x550cc 15.6英寸笔记本             | 笔记本       | 华硕       |  2799 | �       |            |
    |  6 | ipad mini 7.9英寸平板电脑         | 平板电脑     | 苹果       |  1999 | �       |            |
    |  8 | iPad mini 配置 retine 显示屏      | 平板电脑     | 苹果       |  2788 | �       |            |
    | 11 | 15.6 寸电脑屏保护膜               | 电脑配件     | 爱戴尔     |    29 | �       |            |
    | 12 | 优雅 复古 无线鼠标键盘            | 电脑配件     | 雷蛇       |   299 | �       |            |
    | 13 | 15寸 4K 液晶显示屏                | 电脑配件     | 索尼       |  1899 | �       |            |
    | 14 | 限量款 LOL 鼠标垫                 | 电脑配件     | 唯爱       |    29 | �       |            |
    +----+-----------------------------------+--------------+------------+-------+---------+------------+
    7 rows in set (0.00 sec)
    
    4.进阶 查询 join
    -- 使用 left join  查询每类(品牌)最贵的商品信息
    
    -- 第一:
    mysql> select brand_name,max(price) as max_price from goods group by brand_name;
    +------------+-----------+
    | brand_name | max_price |
    +------------+-----------+
    | 华硕       |      3399 |
    | 唯爱       |        29 |
    | 戴尔       |      2899 |
    | 爱戴尔     |        29 |
    | 索尼       |      7999 |
    | 联想       |      4880 |
    | 苹果       |      3388 |
    | 雷蛇       |       299 |
    +------------+-----------+
    8 rows in set (0.47 sec)
    -- ×代码
    select * 
    from (select brand_name,max(price) as max_price from goods group by brand_name) as b_new
    left join goods as g 
    on b_new.brand_name=g.brand_name and b_new.max_price=g.price;
    
    mysql> select * 
        -> from (select brand_name,max(price) as max_price from goods group by brand_name) as b_new
        -> left join goods as g 
        -> on b_new.brand_name=g.brand_name and b_new.max_price=g.price;
    +------------+-----------+------+----------------------------------+--------------+------------+-------+---------+------------+
    | brand_name | max_price | id   | name                             | cate_name    | brand_name | price | is_show | is_saleoff |
    +------------+-----------+------+----------------------------------+--------------+------------+-------+---------+------------+
    | 华硕       |      3399 |    1 | r510vc 15.6英寸笔记本            | 笔记本       | 华硕       |  3399 | �       |            |
    | 联想       |      4880 |    3 | x240 超极本                      | 超极本       | 联想       |  4880 | �       |            |
    | 索尼       |      7999 |    5 | svp13226scb 触控超级本           | 超级本       | 索尼       |  7999 | �       |            |
    | 苹果       |      3388 |    7 | iPad air 9.7英寸平板电脑         | 平板电脑     | 苹果       |  3388 | �       |            |
    | 戴尔       |      2899 |   10 | vostro 3800-r1206 台式电脑       | 台式机       | 戴尔       |  2899 | �       |            |
    | 爱戴尔     |        29 |   11 | 15.6 寸电脑屏保护膜              | 电脑配件     | 爱戴尔     |    29 | �       |            |
    | 雷蛇       |       299 |   12 | 优雅 复古 无线鼠标键盘           | 电脑配件     | 雷蛇       |   299 | �       |            |
    | 唯爱       |        29 |   14 | 限量款 LOL 鼠标垫                | 电脑配件     | 唯爱       |    29 | �       |            |
    +------------+-----------+------+----------------------------------+--------------+------------+-------+---------+------------+
    8 rows in set (0.00 sec)
    
    5.其他

    四.修改

    1.拆表

    拆表 与其他有关操作

    
    -- 拆表 将商品类型 和 商品品牌 拆出成新的表格
    
    -- 1.新建 table (一个goods_cates 一个goods_brands)
    
    mysql> create table if not exists goods_cates(
        -> id int unsigned primary key auto_increment,
        -> name varchar(40) not null
        -> );
    Query OK, 0 rows affected (0.49 sec)
    
    mysql> create table if not exists goods_brands( 
        -< id int unsigned primary key auto_increment, 
        -< name varchar(40) not null );
    Query OK, 0 rows affected (0.35 sec)
    
    mysql> show tables;
    +---------------------+
    | Tables_in_jing_dong |
    +---------------------+
    | goods               |
    | goods_brands        |
    | goods_cates         |
    +---------------------+
    3 rows in set (0.00 sec)
    
    -- 2. 添加信息
    mysql> insert into goods_cates (name) select cate_name from goods group by cate_name;
    Query OK, 6 rows affected (0.07 sec)
    Records: 6  Duplicates: 0  Warnings: 0
    
    mysql> insert into goods_brands (name) select brand_name from goods group by brand_name;
    Query OK, 8 rows affected (0.06 sec)
    Records: 8  Duplicates: 0  Warnings: 0
    
    mysql> select * from goods_cates;
    +----+--------------+
    | id | name         |
    +----+--------------+
    |  1 | 台式机       |
    |  2 | 平板电脑     |
    |  3 | 电脑配件     |
    |  4 | 笔记本       |
    |  5 | 超极本       |
    |  6 | 超级本       |
    +----+--------------+
    6 rows in set (0.00 sec)
    
    mysql> select * from goods_brands;
    +----+-----------+
    | id | name      |
    +----+-----------+
    |  1 | 华硕      |
    |  2 | 唯爱      |
    |  3 | 戴尔      |
    |  4 | 爱戴尔    |
    |  5 | 索尼      |
    |  6 | 联想      |
    |  7 | 苹果      |
    |  8 | 雷蛇      |
    +----+-----------+
    8 rows in set (0.00 sec)
    
    -- 3.换参数
    -- 将 goods表中的cate_name 换成goods_cates 的id
    mysql> mysql> update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;
    Query OK, 14 rows affected (0.07 sec)
    Rows matched: 14  Changed: 14  Warnings: 0
    
    mysql> select * from goods;
    +----+---------------------------------------+-----------+------------+-------+---------+------------+
    | id | name                                  | cate_name | brand_name | price | is_show | is_saleoff |
    +----+---------------------------------------+-----------+------------+-------+---------+------------+
    |  1 | r510vc 15.6英寸笔记本                 | 4         | 华硕       |  3399 | �       |            |
    |  2 | x550cc 15.6英寸笔记本                 | 4         | 华硕       |  2799 | �       |            |
    |  3 | x240 超极本                           | 5         | 联想       |  4880 | �       |            |
    |  4 | u330p 13.3英寸超级本                  | 5         | 联想       |  4299 | �       |            |
    |  5 | svp13226scb 触控超级本                | 6         | 索尼       |  7999 | �       |            |
    |  6 | ipad mini 7.9英寸平板电脑             | 2         | 苹果       |  1999 | �       |            |
    |  7 | iPad air 9.7英寸平板电脑              | 2         | 苹果       |  3388 | �       |            |
    |  8 | iPad mini 配置 retine 显示屏          | 2         | 苹果       |  2788 | �       |            |
    |  9 | ideacentre c3340 20英寸一体电脑       | 1         | 联想       |  3499 | �       |            |
    | 10 | vostro 3800-r1206 台式电脑            | 1         | 戴尔       |  2899 | �       |            |
    | 11 | 15.6 寸电脑屏保护膜                   | 3         | 爱戴尔     |    29 | �       |            |
    | 12 | 优雅 复古 无线鼠标键盘                | 3         | 雷蛇       |   299 | �       |            |
    | 13 | 15寸 4K 液晶显示屏                    | 3         | 索尼       |  1899 | �       |            |
    | 14 | 限量款 LOL 鼠标垫                     | 3         | 唯爱       |    29 | �       |            |
    +----+---------------------------------------+-----------+------------+-------+---------+------------+
    14 rows in set (0.00 sec)
    
    -- 将goods表中的 brands_name 换成 goods_brands中的id
    mysql> update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;
    Query OK, 14 rows affected (0.05 sec)
    Rows matched: 14  Changed: 14  Warnings: 0
    
    mysql> select * from goods;
    +----+---------------------------------------+-----------+------------+-------+---------+------------+
    | id | name                                  | cate_name | brand_name | price | is_show | is_saleoff |
    +----+---------------------------------------+-----------+------------+-------+---------+------------+
    |  1 | r510vc 15.6英寸笔记本                 | 4         | 1          |  3399 | �       |            |
    |  2 | x550cc 15.6英寸笔记本                 | 4         | 1          |  2799 | �       |            |
    |  3 | x240 超极本                           | 5         | 6          |  4880 | �       |            |
    |  4 | u330p 13.3英寸超级本                  | 5         | 6          |  4299 | �       |            |
    |  5 | svp13226scb 触控超级本                | 6         | 5          |  7999 | �       |            |
    |  6 | ipad mini 7.9英寸平板电脑             | 2         | 7          |  1999 | �       |            |
    |  7 | iPad air 9.7英寸平板电脑              | 2         | 7          |  3388 | �       |            |
    |  8 | iPad mini 配置 retine 显示屏          | 2         | 7          |  2788 | �       |            |
    |  9 | ideacentre c3340 20英寸一体电脑       | 1         | 6          |  3499 | �       |            |
    | 10 | vostro 3800-r1206 台式电脑            | 1         | 3          |  2899 | �       |            |
    | 11 | 15.6 寸电脑屏保护膜                   | 3         | 4          |    29 | �       |            |
    | 12 | 优雅 复古 无线鼠标键盘                | 3         | 8          |   299 | �       |            |
    | 13 | 15寸 4K 液晶显示屏                    | 3         | 5          |  1899 | �       |            |
    | 14 | 限量款 LOL 鼠标垫                     | 3         | 2          |    29 | �       |           |
    +----+---------------------------------------+-----------+------------+-------+---------+------------+
    14 rows in set (0.00 sec)
    
    
    -- 向goods_cates 添加 数据 (goods_brand)
    mysql> insert into goods_cates(name) values ('路由器'),('交换机'),('网卡');
    Query OK, 3 rows affected (0.06 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select *  from goods_cates;
    +----+--------------+
    | id | name         |
    +----+--------------+
    |  1 | 台式机       |
    |  2 | 平板电脑     |
    |  3 | 电脑配件     |
    |  4 | 笔记本       |
    |  5 | 超极本       |
    |  6 | 超级本       |
    |  8 | 路由器       |
    |  9 | 交换机       |
    | 10 | 网卡         |
    +----+--------------+
    9 rows in set (0.00 sec)
    
    mysql> insert into goods_brands(name) values ('海尔'),('清华同方'),('神舟');
    Query OK, 3 rows affected (0.05 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    
    -- 向主表添加数据(发现有问题 cate_name,brand_name 超出goods_cates 和 goods_brands 不会报错)
    mysql> insert into goods (name,cate_name,brand_name,price) values('LaserJet Pro P1600dn 黑白打印机','12','10','1988');
    Query OK, 1 row affected (0.06 sec)
    
    mysql> select * from goods;
    +----+---------------------------------------+-----------+------------+-------+---------+------------+
    | id | name                                  | cate_name | brand_name | price | is_show | is_saleoff |
    +----+---------------------------------------+-----------+------------+-------+---------+------------+
    |  1 | r510vc 15.6英寸笔记本                 | 4         | 1          |  3399 | �       |            |
    |  2 | x550cc 15.6英寸笔记本                 | 4         | 1          |  2799 | �       |            |
    |  3 | x240 超极本                           | 5         | 6          |  4880 | �       |            |
    |  4 | u330p 13.3英寸超级本                  | 5         | 6          |  4299 | �       |            |
    |  5 | svp13226scb 触控超级本                | 6         | 5          |  7999 | �       |            |
    |  6 | ipad mini 7.9英寸平板电脑             | 2         | 7          |  1999 | �       |            |
    |  7 | iPad air 9.7英寸平板电脑              | 2         | 7          |  3388 | �       |            |
    |  8 | iPad mini 配置 retine 显示屏          | 2         | 7          |  2788 | �       |            |
    |  9 | ideacentre c3340 20英寸一体电脑       | 1         | 6          |  3499 | �       |            |
    | 10 | vostro 3800-r1206 台式电脑            | 1         | 3          |  2899 | �       |            |
    | 11 | 15.6 寸电脑屏保护膜                   | 3         | 4          |    29 | �       |            |
    | 12 | 优雅 复古 无线鼠标键盘                | 3         | 8          |   299 | �       |            |
    | 13 | 15寸 4K 液晶显示屏                    | 3         | 5          |  1899 | �       |            |
    | 14 | 限量款 LOL 鼠标垫                     | 3         | 2          |    29 | �       |            |
    | 15 | LaserJet Pro P1600dn 黑白打印机       | 12        | 10         |  1988 | �       |            |
    +----+---------------------------------------+-----------+------------+-------+---------+------------+
    15 rows in set (0.00 sec)
    
    -- 发现cate_name 和 brand_name(字符)存储的是(int)
    mysql> desc goods;
    +------------+------------------+------+-----+---------+----------------+
    | Field      | Type             | Null | Key | Default | Extra          |
    +------------+------------------+------+-----+---------+----------------+
    | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | name       | varchar(150)     | NO   |     | NULL    |                |
    | cate_name  | varchar(40)      | NO   |     | NULL    |                |
    | brand_name | varchar(40)      | NO   |     | NULL    |                |
    | price      | decimal(10,0)    | NO   |     | 0       |                |
    | is_show    | bit(1)           | NO   |     | b'1'    |                |
    | is_saleoff | bit(1)           | NO   |     | b'0'    |                |
    +------------+------------------+------+-----+---------+----------------+
    7 rows in set (0.03 sec)
    mysql> desc goods_cates;
    +-------+------------------+------+-----+---------+----------------+
    | Field | Type             | Null | Key | Default | Extra          |
    +-------+------------------+------+-----+---------+----------------+
    | id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(40)      | NO   |     | NULL    |                |
    +-------+------------------+------+-----+---------+----------------+
    2 rows in set (0.00 sec)
    
    mysql> desc goods_brands;
    +-------+------------------+------+-----+---------+----------------+
    | Field | Type             | Null | Key | Default | Extra          |
    +-------+------------------+------+-----+---------+----------------+
    | id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(40)      | NO   |     | NULL    |                |
    +-------+------------------+------+-----+---------+----------------+
    2 rows in set (0.00 sec)
    
    
    -- 修改 cate_name 为 cate_id (int) 和 brand_name 为 brand_id(int)
    mysql> alter  table goods change cate_name cate_id int unsigned not null;
    Query OK, 15 rows affected (1.08 sec)
    Records: 15  Duplicates: 0  Warnings: 0
    mysql> desc goods;
    +------------+------------------+------+-----+---------+----------------+
    | Field      | Type             | Null | Key | Default | Extra          |
    +------------+------------------+------+-----+---------+----------------+
    | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | name       | varchar(150)     | NO   |     | NULL    |                |
    | cate_id    | int(10) unsigned | NO   |     | NULL    |                |
    | brand_name | varchar(40)      | NO   |     | NULL    |                |
    | price      | decimal(10,0)    | NO   |     | 0       |                |
    | is_show    | bit(1)           | NO   |     | b'1'    |                |
    | is_saleoff | bit(1)           | NO   |     | b'0'    |                |
    +------------+------------------+------+-----+---------+----------------+
    7 rows in set (0.00 sec)
    
    mysql> alter table goods change brand_name brand_id int unsigned not null;
    Query OK, 15 rows affected (0.92 sec)
    Records: 15  Duplicates: 0  Warnings: 0
    
    2.设置外键
    -- 关联三个表,设置外键
    mysql> alter table goods add foreign key (cate_id) references goods_cates(id);
    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`jing_dong`.`#sql-438_2`, CONSTRAINT `#sql-438_2_ibfk_1` FOREIGN KEY (`cate_id`) REFERENCES `goods_cates` (`id`))
    -- 由于之前添加了 cate_name=12 不在goods_cates中才报错,下面将此信息删除掉
    mysql> delete from goods where id=15;
    Query OK, 1 row affected (0.04 sec)
    
    mysql> alter table goods add foreign key (cate_id) references goods_cates(id);
    Query OK, 14 rows affected (1.32 sec)
    Records: 14  Duplicates: 0  Warnings: 0
    mysql> alter table goods add foreign key (brand_id) references goods_brands(id);
    Query OK, 14 rows affected (1.21 sec)
    Records: 14  Duplicates: 0  Warnings: 0
    
    
    -- 在添加一个 外键值以外的数据 发现会出错,这就会减少数据输错的可能
    mysql> insert into goods (name,cate_id,brand_id,price) values('LaserJet Pro P1600dn 黑白打印机','12','10','1988');
    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`jing_dong`.`goods`, CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`cate_id`) REFERENCES `goods_cates` (`id`))
    
    -- 查看是否添加成功外键    下表  CONSTRAINT(约束,限制)后
    mysql> show create table goods;
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table    |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | goods | CREATE TABLE `goods` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(150) NOT NULL,
      `cate_id` int(10) unsigned NOT NULL,
      `brand_id` int(10) unsigned NOT NULL,
      `price` decimal(10,0) NOT NULL DEFAULT '0',
      `is_show` bit(1) NOT NULL DEFAULT b'1',
      `is_saleoff` bit(1) NOT NULL DEFAULT b'0',
      PRIMARY KEY (`id`),
      KEY `cate_id` (`cate_id`),
      KEY `brand_id` (`brand_id`),
      CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`cate_id`) REFERENCES `goods_cates` (`id`),
      CONSTRAINT `goods_ibfk_2` FOREIGN KEY (`brand_id`) REFERENCES `goods_brands` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    

    五.备份

    在 shell 中执行下面的命令

    ╭─python@b  ~/Documents/notebook/MySQL学习笔记
    ╰─$ mysqldump -uroot -pohmysql jing_dong > jd.sql
    mysqldump: [Warning] Using a password on the command line interface can be insecure.
    ╭─python@b  ~/Documents/notebook/MySQL学习笔记 
    ╰─$ ls
     jd.sql               'mysq基础操作(命令).md'   ubuntu下mysql连接问题.md     数据
    
    

    备份出的文件( jd.sql )长这样,不就是mysql 的命令吗?

    -- MySQL dump 10.13  Distrib 5.7.25, for Linux (x86_64)
    --
    -- Host: localhost    Database: jing_dong
    -- ------------------------------------------------------
    -- Server version   5.7.25-0ubuntu0.18.04.2
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    --
    -- Table structure for table `goods`
    --
    
    DROP TABLE IF EXISTS `goods`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `goods` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(150) NOT NULL,
      `cate_id` int(10) unsigned NOT NULL,
      `brand_id` int(10) unsigned NOT NULL,
      `price` decimal(10,0) NOT NULL DEFAULT '0',
      `is_show` bit(1) NOT NULL DEFAULT b'1',
      `is_saleoff` bit(1) NOT NULL DEFAULT b'0',
      PRIMARY KEY (`id`),
      KEY `cate_id` (`cate_id`),
      KEY `brand_id` (`brand_id`),
      CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`cate_id`) REFERENCES `goods_cates` (`id`),
      CONSTRAINT `goods_ibfk_2` FOREIGN KEY (`brand_id`) REFERENCES `goods_brands` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `goods`
    --
    
    LOCK TABLES `goods` WRITE;
    /*!40000 ALTER TABLE `goods` DISABLE KEYS */;
    INSERT INTO `goods` VALUES (1,'r510vc 15.6英寸笔记本',4,1,3399,_binary '�',_binary '\0'),(2,'x550cc 15.6英寸笔记本',4,1,2799,_binary '�',_binary '\0'),(3,'x240 超极本',5,6,4880,_binary '�',_binary '\0'),(4,'u330p 13.3英寸超级本',5,6,4299,_binary '�',_binary '\0'),(5,'svp13226scb 触控超级本',6,5,7999,_binary '�',_binary '\0'),(6,'ipad mini 7.9英寸平板电脑',2,7,1999,_binary '�',_binary '\0'),(7,'iPad air 9.7英寸平板电脑',2,7,3388,_binary '�',_binary '\0'),(8,'iPad mini 配置 retine 显示屏',2,7,2788,_binary '�',_binary '\0'),(9,'ideacentre c3340 20英寸一体电脑',1,6,3499,_binary '�',_binary '\0'),(10,'vostro 3800-r1206 台式电脑',1,3,2899,_binary '�',_binary '\0'),(11,'15.6 寸电脑屏保护膜',3,4,29,_binary '�',_binary '\0'),(12,'优雅 复古 无线鼠标键盘',3,8,299,_binary '�',_binary '\0'),(13,'15寸 4K 液晶显示屏',3,5,1899,_binary '�',_binary '\0'),(14,'限量款 LOL 鼠标垫',3,2,29,_binary '�',_binary '\0');
    /*!40000 ALTER TABLE `goods` ENABLE KEYS */;
    UNLOCK TABLES;
    
    --
    -- Table structure for table `goods_brands`
    --
    
    DROP TABLE IF EXISTS `goods_brands`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `goods_brands` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(40) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `goods_brands`
    --
    
    LOCK TABLES `goods_brands` WRITE;
    /*!40000 ALTER TABLE `goods_brands` DISABLE KEYS */;
    INSERT INTO `goods_brands` VALUES (1,'华硕'),(2,'唯爱'),(3,'戴尔'),(4,'爱戴尔'),(5,'索尼'),(6,'联想'),(7,'苹果'),(8,'雷蛇'),(16,'海尔'),(17,'清华同方'),(18,'神舟');
    /*!40000 ALTER TABLE `goods_brands` ENABLE KEYS */;
    UNLOCK TABLES;
    
    --
    -- Table structure for table `goods_cates`
    --
    
    DROP TABLE IF EXISTS `goods_cates`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `goods_cates` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(40) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `goods_cates`
    --
    
    LOCK TABLES `goods_cates` WRITE;
    /*!40000 ALTER TABLE `goods_cates` DISABLE KEYS */;
    INSERT INTO `goods_cates` VALUES (1,'台式机'),(2,'平板电脑'),(3,'电脑配件'),(4,'笔记本'),(5,'超极本'),(6,'超级本'),(8,'路由器'),(9,'交换机'),(10,'网卡');
    /*!40000 ALTER TABLE `goods_cates` ENABLE KEYS */;
    UNLOCK TABLES;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    -- Dump completed on 2019-04-23 14:54:00
    
    

    备份数据库所有数据

    mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql
    

    六.附录

    1.资料

    课上老师推荐课外资料 58到家数据库30条军规

    第二节:用Python操作

    用的是 pymysql

    一.查询

    大概流程如下

    修改 sql 中的命令就可实现不同功能了

    from pymysql import connect
    conn = connect(host = 'localhost', port = 3306,
                user = 'root', password = 'ohmysql',
                database = 'jing_dong', charset = 'utf8')
    
    
    # 获得Cursor对象  (游标)
    cursor = conn.cursor()
    sql = "select * from goods limit 20;"
    cursor.execute(sql)
    #fetchall() 查询全部; fetchmany(n)查询n个; fetchone()查询一个
    for i in cursor.fetchone():
        print(i)
    
    #断开连接
    cursor.close()
    conn.close()
    

    二.增删改

    增删改 的过程 与查询过程 相似 (修改sql 中的语句就可实现不同功能)

    唯一不同就是,命令 和 多了一步 确定 环节

    #执行后,mysql才对进行操作   就是yes
    conn.commit()
    
    #执行后,放弃操作,不会进行    no
    conn.rollback()
    
    In [1]: from pymysql import connect 
       ...:  
       ...: # 创建Connect连接 
       ...: conn = connect(host = 'localhost', port = 3306, 
       ...:     user = 'root', password = 'ohmysql', 
       ...:     database = 'jing_dong', charset = 'utf8') 
       ...: # 获得Cursor对象  (游标) 
       ...: cursor = conn.cursor() 
    
        
    In [2]: sql = "insert into goods_cates (name) values ("硬
       ...: 盘---> new") ;"                                      
      File "<ipython-input-2-69f27cbdfaa7>", line 1
        sql = "insert into goods_cates (name) values ("硬盘---> new") "
                                                          ^
    SyntaxError: invalid syntax
    
    
    In [3]: sql = """insert into goods_cates (name) values ("硬盘---> new");"""  
    
    In [4]: cursor.execute(sql)                                                             Out[4]: 1
    
    In [5]: conn.commit()
    
    In [6]: sql = """insert into goods_cates (name) values ("内存条---> new");"""                  
    In [7]: cursor.execute(sql)                                                             Out[5]: 1
    
    In [10]: conn.rollback() 
    
    

    没有执行 commit 和 rollback 时 MySQL中 的AUTO_INCREMENT=11 会变化 (MySQL 语句不会执行)

    mysql> show create table goods_cates;
    +-------------+------------------------------------------------------------------------------------------------+
    | Table       | Create Table   |
    +-------------+------------------------------------------------------------------------------------------------+
    | goods_cates | CREATE TABLE `goods_cates` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(40) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |
    +-------------+------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    

    三.示例代码

    面对对象思想,对不同功能进行封装

    from pymysql import connect
    
    class JD(object):
        def __init__(self):
            # 创建Connection连接
            self.conn = connect(host = 'localhost', port = 3306,
                user = 'root', password = 'ohmysql',
                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 temp in self.cursor.fetchall():
                print(temp)
            print()
    
        def show_all_items(self):
            '''显示所有的商品'''
            sql = "select * from goods;"
            self.execute_sql(sql) 
        
        def show_cates(self):
            # 显示所有商品种类
            sql = "select name from goods_cates;"
            self.execute_sql(sql)
    
        def show_brands(self):
            # 显示所有商品品牌
            sql = "select name from goods_brands;"
            self.execute(sql)
    
        def add_brands(self):
            item_name = input("输入新商品名称: ")
            sql = """insert into goods_cates (name) values("%s")""" % item_name
            self.cursor.execute(sql)
            self.conn.commit()
    
        @staticmethod
        def print_menu():
            print("-------> 京东 <-------")
            print("1.所有商品")
            print("2.所有商品分类")
            print("3.所有商品品牌")
            print("4.添加商品分类")
            print()
            return input("请输入你要查询的信息 :")
    
    
        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":
                    # 增加商品分类
                    self.add_brands()
                else:
                    print("输入有误,请重新输入你要查询的信息~~")
    
    def main():
        # 创建一个京东对象
        jd = JD()
    
        # 调用这个对象的run方法
        jd.run()
    
    if __name__ == '__main__':
        main()
    

    相关文章

      网友评论

          本文标题:实例-MySQL-模拟京东电商数据库

          本文链接:https://www.haomeiwen.com/subject/rxtxkctx.html