MYSQL 多表操作<五>

承上启下

承接上一个博客

mysql> show tables;
+-----------------+
| Tables_in_day06 |
+-----------------+
| category        |
| product         |
| Student         |
+-----------------+
3 rows in set (0.01 sec)

--  所有商品
1、商品id
2、商品名称
3、商品价格
4、商品日期
5、商品分类id--> category

mysql> select * from product;
+-----+--------------+-------+-------+------+
| pid | pname        | price | pdate | con  |
+-----+--------------+-------+-------+------+
|   1 | 小米         |   998 | NULL  |    1 |
|   2 | 特步         |   300 | NULL  |    1 |
|   3 | 老村长       |    99 | NULL  |    2 |
|   4 | 劲酒         |    88 | NULL  |    3 |
|   5 | 小熊饼干     |    35 | NULL  |    3 |
|   6 | 红牛         |     5 | NULL  |    4 |
|   7 | 三只松鼠     |   135 | NULL  |    5 |
|   8 | 王老吉       |     5 | NULL  |    4 |
|   9 | Mac          | 10000 | NULL  |    1 |
+-----+--------------+-------+-------+------+
9 rows in set (0.00 sec)


--  商品分类：手机数码、鞋靴箱包
1、分类的id
2、分类的名称
3、分类的描述
mysql> select * from category;
+-----+--------------+--------------------------------+
| cid | cname        | cdesc                          |
+-----+--------------+--------------------------------+
|   1 | 手机数码     | 电子产品，三星出品             |
|   2 | 鞋靴箱包     | made in china                  |
|   3 | 香烟酒水     | 生活用品，茅台芙蓉王           |
|   4 | 红牛         | 饮料                           |
|   5 | 三只松鼠     | 零食                           |
+-----+--------------+--------------------------------+
5 rows in set (0.00 sec)

技术分析

多表之间的关系如何维护
外键约束（foreign key）

给product中的con 添加一个外键约束
alter table product add foreign key(con) references category(cid);

从分类category中删除cid = 5 的数据
    delete from category where cid=5;   删除失败

    首先得去product表中删除所有分类id为5的商品
    delete from product where con =5;

给product中的con 添加一个外键约束

mysql> alter table product add foreign key(con) references category(cid);
Query OK, 9 rows affected (0.07 sec)
Records: 9 Duplicates: 0 Warnings: 0

mysql> delete from category where cid=5;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (day06.product, CONSTRAINT product_ibfk_1 FOREIGN KEY (con) REFERENCES category (cid))
mysql>

首先得去product表中删除所有分类id为5的商品

mysql> delete from category where cid=5;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (day06.product, CONSTRAINT product_ibfk_1 FOREIGN KEY (con) REFERENCES category (cid))
mysql> delete from product where con =5;
Query OK, 1 row affected (0.09 sec)

mysql> delete from category where cid=5;
Query OK, 1 row affected (0.10 sec)

建数据库原则
通常情况下，一个项目/应用建立一个数据库
多表之间的建表原则
- 一对多：商品和分类
建表原则：在多的一方添加一个外键指向少的一方的主键
- 多对多：老师跟学生、学生跟课程
建表原则：建立一张中间表，将多对多的关系拆分成一对多的关系，中间表至少要有两个外键，分别指向原来的那两张表
- 一对一：班级跟班长，公民身份证与公民，国家跟国旗
建表原则：
1）将一对一情况，当作是一对多情况处理，在任意一张表添加一个外键，并且这个外键要唯一执行另外一张表
2）直接将两张表合并成一张表
3）将两张表的主键建立连接，让两张表里面主键相等
实际用途. 拆表操作：

案例【商城】

如下图：

案例.png

案例【详细分析】
用户表


create table user (
    uid int primary key auto_increment,
    userName varchar(31),
    password varchar(31),
    phone varchar(31)
);

insert into user values(null,'zhangsan','123456','15576762548');
insert into user values(null,'Lisi','131415','18676729358');

订单表

订单编号、总价、订单时间、地址、外键用户id

create table `orders`(
    `oid` int primary key auto_increment,
    `sum` double not null,
    `otime` timestamp,
    `address` varchar(100),
    `uno` int,
     foreign key(`uno`) references user(`uid`)
);

insert into `orders` values(null,200.0,null,'西湖区',1);
insert into `orders` values(null,150.7,null,'余杭区',2);

商品表

商品id、商品名称、商品价格、分类外键cno
create table `product`(
    `pid` int primary key auto_increment,
    `pname` varchar(10),
    `price` double,
    `cno` int,
     foreign key(`cno`) references category(`cid`)
);

insert into `product` values(null,'iphone',5300,1);
insert into `product` values(null,'茅台',1800,2);
insert into `product` values(null,'卫生纸',5,3);
insert into `product` values(null,'香蕉',10,4);
insert into `product` values(null,'大白菜',30,5);
insert into `product` values(null,'三只松鼠',100,6);
insert into `product` values(null,'葡萄',15,4);
insert into `product` values(null,'五粮液',800,2);

分类表

分类id、分类名称、分类描述

create table `category`(
   `cid` int primary key auto_increment,
   `cname` varchar(31),
   `cdesc` varchar(100)
);

insert into `category` values(null,'电器','电器类商品');
insert into `category` values(null,'副食品','烟酒类');
insert into `category` values(null,'生活用品','用于生活');
insert into `category` values(null,'水果','吃吃更健康');
insert into `category` values(null,'蔬菜','绿色食品');
insert into `category` values(null,'干果','好吃的零食');

订单项：中间表

***
**
订单表与商品表 多对多的关系
订单表 ->  订单项 : 一对多
商品表 ->  订单项 : 一对多
**
**
订单id、商品id、商品数量、订单项总价


create table orderItem(
  `ono` int,
  `pno` int,
  foreign key(`pno`) references `product`(`pid`),
  foreign key(`ono`) references `orders`(`oid`),
  ocount int,
  subSum double
);

给1号订单添加商品 400块的商品
insert into `orderItem` values(1,3,40,200);  //卫生纸
insert into `orderItem` values(1,4,20,200);  //香蕉

给2号订单添加商品 250块的商品
insert into `orderItem` values(2,6,10,200);  //三只松鼠
insert into `orderItem` values(2,4,5,50);  //香蕉

查看数据

mysql>  select * from user;
+-----+----------+----------+-------------+
| uid | userName | password | phone       |
+-----+----------+----------+-------------+
|   1 | zhangsan | 123456   | 15576762548 |
|   2 | Lisi     | 131415   | 18676729358 |
+-----+----------+----------+-------------+
2 rows in set (0.01 sec)

mysql>  select * from orders;
+-----+-------+-------+-----------+------+
| oid | sum   | otime | address   | uno  |
+-----+-------+-------+-----------+------+
|   1 |   200 | NULL  | 西湖区    |    1 |
|   2 | 150.7 | NULL  | 余杭区    |    2 |
+-----+-------+-------+-----------+------+
2 rows in set (0.00 sec)

mysql>  select * from  product;
+-----+--------------+-------+------+
| pid | pname        | price | cno  |
+-----+--------------+-------+------+
|   1 | iphone       |  5300 |    1 |
|   3 | 茅台         |  1800 |    2 |
|   4 | 卫生纸       |     5 |    3 |
|   5 | 香蕉         |    10 |    4 |
|   6 | 大白菜       |    30 |    5 |
|   7 | 三只松鼠     |   100 |    6 |
|   8 | 葡萄         |    15 |    4 |
|   9 | 五粮液       |   800 |    2 |
+-----+--------------+-------+------+
8 rows in set (0.00 sec)

mysql>  select * from category;
+-----+--------------+-----------------+
| cid | cname        | cdesc           |
+-----+--------------+-----------------+
|   1 | 电器         | 电器类商品      |
|   2 | 副食品       | 烟酒类          |
|   3 | 生活用品     | 用于生活        |
|   4 | 水果         | 吃吃更健康      |
|   5 | 蔬菜         | 绿色食品        |
|   6 | 干果         | 好吃的零食      |
+-----+--------------+-----------------+
6 rows in set (0.00 sec)

mysql>  select * from orderItem;
+------+------+--------+--------+
| ono  | pno  | ocount | subSum |
+------+------+--------+--------+
|    1 |    3 |     40 |    200 |
|    1 |    4 |     20 |    200 |
|    2 |    6 |     10 |    200 |
|    2 |    4 |      5 |     50 |
+------+------+--------+--------+
4 rows in set (0.00 sec)    
    
//查看 一个订单对应很多商品
mysql>  select * from orderItem where ono = 1;
+------+------+--------+--------+
| ono  | pno  | ocount | subSum |
+------+------+--------+--------+
|    1 |    3 |     40 |    200 |
|    1 |    4 |     20 |    200 |
+------+------+--------+--------+

//查看 一个商品对应很多订单
mysql> select * from orderItem where pno = 4;
+------+------+--------+--------+
| ono  | pno  | ocount | subSum |
+------+------+--------+--------+
|    1 |    4 |     20 |    200 |
|    2 |    4 |      5 |     50 |
+------+------+--------+--------+
2 rows in set (0.00 sec)

未完待续....

最后编辑于：2018.09.27 11:30:39

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,921评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,635评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,393评论 0赞 338
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,836评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,833评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,685评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,043评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,694评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 42,671评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,670评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,779评论 1赞 332
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,424评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,027评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,984评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,214评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,108评论 2赞 351
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,517评论 2赞 343

MYSQL 多表操作<五>

承上启下

技术分析

查看数据

推荐阅读更多精彩内容