MYSQL 多表操作<五>

作者: 那是一阵清风_徐来 | 来源:发表于2018-09-26 14:52 被阅读9次

MYSQL 多表操作<五>
MYSQL 多表操作<六>
MongoDB 条件操作符
Mybatis中特殊符号转移
test
无标题文章
Mybatis 中大于等于小于等于的写法
shell-判断
MyBatis大于小于转义
Mybatis中 < > <= >= 等符号的使用

承上启下

承接上一个博客

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)