传智黑马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()