多表之间的关系

表之间的关系

一对多：

常见实例：客户和订单，分类和商品，部门和员工
一对多建表原则：在从表(多方)创建一个字段，该字段作为外键指向主表(一方)的主键

实现：
(1)创建数据库

    create database web09;
    use eb09;

(2)建立product和category表

    create table category(
        cid varchar(32) primary key,
        cname varchar(100)
    );
    create table product(
        pid varchar(32) primary key,
        pname varchar(40),
        price double,
        category_id varchar(32)
    );

(3)分别向表中插入数据

#category表
insert into category(cid,cname) values('c001','家电');
insert into category(cid,cname) values('c002','服饰');
insert into category(cid,cname) values('c003','化妆品');
#product表
insert into product(pid,pname,price,category_id) values('p001','联想','5000','c001');
insert into product(pid,pname,price,category_id) values('p002','海尔','5000','c001');
insert into product(pid,pname,price,category_id) values('p003','雷神','5000','c001');
insert into product(pid,pname,price,category_id) values('p004','JACK JONES','800','c002');
insert into product(pid,pname,price,category_id) values('p005','真维斯','200','c002');
insert into product(pid,pname,price,category_id) values('p006','花花公子','440','c002');
insert into product(pid,pname,price,category_id) values('p007','劲霸','2000','c002');
insert into product(pid,pname,price,category_id) values('p008','香奈儿','800','c003');
insert into product(pid,pname,price,category_id) values('p009','相宜本草','200','c003');

查看一下建好没有：

select * from product;

+------+------------+-------+-------------+
| pid  | pname      | price | category_id |
+------+------------+-------+-------------+
| p001 | 联想       |  5000 | c001        |
| p002 | 海尔       |  5000 | c001        |
| p003 | 雷神       |  5000 | c001        |
| p004 | JACK JONES |   800 | c002        |
| p005 | 真维斯     |   200 | c002        |
| p006 | 花花公子   |   440 | c002        |
| p007 | 劲霸       |  2000 | c002        |
| p008 | 香奈儿     |   800 | c003        |
| p009 | 相宜本草   |   200 | c003        |
+------+------------+-------+-------------+
select * from categroy;
+------+--------+
| cid  | cname  |
+------+--------+
| c001 | 家电   |
| c002 | 服饰   |
| c003 | 化妆品 |
+------+--------+

如果现在想要删除categroy中的cid=c003这条数据，是不行的

mysql> delete form category where cid='c003';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'category where cid='c003'' at line 1

因为其关联的product表中存在着categroy_id=c003的数据，要删除的画有两格办法：
第一种办法：当从表中没有category_id=c003的数据时，可以删除category中的cid=c003的数据；
第二种办法：先解除两张表的外键引用关系

(4)查询

mysql> select * from category,product;
+------+--------+------+------------+-------+-------------+
| cid  | cname  | pid  | pname      | price | category_id |
+------+--------+------+------------+-------+-------------+
| c001 | 家电   | p001 | 联想       |  5000 | c001        |
| c002 | 服饰   | p001 | 联想       |  5000 | c001        |
| c003 | 化妆品 | p001 | 联想       |  5000 | c001        |
| c001 | 家电   | p002 | 海尔       |  5000 | c001        |
| c002 | 服饰   | p002 | 海尔       |  5000 | c001        |
| c003 | 化妆品 | p002 | 海尔       |  5000 | c001        |
| c001 | 家电   | p003 | 雷神       |  5000 | c001        |
| c002 | 服饰   | p003 | 雷神       |  5000 | c001        |
| c003 | 化妆品 | p003 | 雷神       |  5000 | c001        |
| c001 | 家电   | p004 | JACK JONES |   800 | c002        |
| c002 | 服饰   | p004 | JACK JONES |   800 | c002        |
| c003 | 化妆品 | p004 | JACK JONES |   800 | c002        |
| c001 | 家电   | p005 | 真维斯     |   200 | c002        |
| c002 | 服饰   | p005 | 真维斯     |   200 | c002        |
| c003 | 化妆品 | p005 | 真维斯     |   200 | c002        |
| c001 | 家电   | p006 | 花花公子   |   440 | c002        |
| c002 | 服饰   | p006 | 花花公子   |   440 | c002        |
| c003 | 化妆品 | p006 | 花花公子   |   440 | c002        |
| c001 | 家电   | p007 | 劲霸       |  2000 | c002        |
| c002 | 服饰   | p007 | 劲霸       |  2000 | c002        |
| c003 | 化妆品 | p007 | 劲霸       |  2000 | c002        |
| c001 | 家电   | p008 | 香奈儿     |   800 | c003        |
| c002 | 服饰   | p008 | 香奈儿     |   800 | c003        |
| c003 | 化妆品 | p008 | 香奈儿     |   800 | c003        |
| c001 | 家电   | p009 | 相宜本草   |   200 | c003        |
| c002 | 服饰   | p009 | 相宜本草   |   200 | c003        |
| c003 | 化妆品 | p009 | 相宜本草   |   200 | c003        |
+------+--------+------+------------+-------+-------------+
27 rows in set (0.02 sec)
mysql> select * from category,product where cid=category_id;
+------+--------+------+------------+-------+-------------+
| cid  | cname  | pid  | pname      | price | category_id |
+------+--------+------+------------+-------+-------------+
| c001 | 家电   | p001 | 联想       |  5000 | c001        |
| c001 | 家电   | p002 | 海尔       |  5000 | c001        |
| c001 | 家电   | p003 | 雷神       |  5000 | c001        |
| c002 | 服饰   | p004 | JACK JONES |   800 | c002        |
| c002 | 服饰   | p005 | 真维斯     |   200 | c002        |
| c002 | 服饰   | p006 | 花花公子   |   440 | c002        |
| c002 | 服饰   | p007 | 劲霸       |  2000 | c002        |
| c003 | 化妆品 | p008 | 香奈儿     |   800 | c003        |
| c003 | 化妆品 | p009 | 相宜本草   |   200 | c003        |
+------+--------+------+------------+-------+-------------+
9 rows in set (0.01 sec)

mysql> select * from category c, product p where c.cid=p.category_id;
+------+--------+------+------------+-------+-------------+
| cid  | cname  | pid  | pname      | price | category_id |
+------+--------+------+------------+-------+-------------+
| c001 | 家电   | p001 | 联想       |  5000 | c001        |
| c001 | 家电   | p002 | 海尔       |  5000 | c001        |
| c001 | 家电   | p003 | 雷神       |  5000 | c001        |
| c002 | 服饰   | p004 | JACK JONES |   800 | c002        |
| c002 | 服饰   | p005 | 真维斯     |   200 | c002        |
| c002 | 服饰   | p006 | 花花公子   |   440 | c002        |
| c002 | 服饰   | p007 | 劲霸       |  2000 | c002        |
| c003 | 化妆品 | p008 | 香奈儿     |   800 | c003        |
| c003 | 化妆品 | p009 | 相宜本草   |   200 | c003        |
+------+--------+------+------------+-------+-------------+
9 rows in set (0.00 sec)

mysql> select name from category c, product p where c.cid=p.category_id;
ERROR 1054 (42S22): Unknown column 'name' in 'field list'
mysql> select cname from category c, product p where c.cid=p.category_id;
+--------+
| cname  |
+--------+
| 家电   |
| 家电   |
| 家电   |
| 服饰   |
| 服饰   |
| 服饰   |
| 服饰   |
| 化妆品 |
| 化妆品 |
+--------+
9 rows in set (0.00 sec)

mysql> select distinct cname from category c, product p where c.cid=p.category_id;
+--------+
| cname  |
+--------+
| 家电   |
| 服饰   |
| 化妆品 |
+--------+
3 rows in set (0.00 sec)

多对多：（暂时没有记录）

常见实例：学生和课程，商品和订单，人和角色
多对多建表原则：需要创建第三张表，中间表至少两个字段，这两个字段分别指向各自一方的主键(就是将一个多对多拆分成一对多)
两张表都是主表，第三张表为从表，提供两个字段，都是外键

一对一：(实际用的不多)

在实际开发中用的不多，因为对一可以创建成一张表

内连接和外连接的区别