数据库篇-mysql详解之多表关系( 二 )

一 : 外键

现在有两个表category分类表

| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| cid   | varchar(32)  | NO   | PRI | NULL    |       || cname | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

与product商品表

+-------------+-------------+------+-----+---------+-------+| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| pid         | varchar(32) | NO   | PRI | NULL    |       || pname       | varchar(40) | YES  |     | NULL    |       |
| price       | double      | YES  |     | NULL    |       || category_id | varchar(32) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

其中在product表中的字段category_id存放的是 category表中cid(主键)的信息列称为外键. 此时分类称为主表,'cid'称为主键,product称为从表,category_id称为外键,通过主表的主键和从表的外键来描述主外键关系,呈现就是一对多的关系.

外键的特点 :

• 从表外键的值是对主表主键的引用.

• 从表外键类型,必须与主表主键类型一致.

声明外键约束

alter table 从表 add [constraint][外键名称] foreign key (从表字段名) references 主表(主表的主键)

外键名称 用于删除外键约束的,一般建议_fk结尾

alter table 从表 drop foregin key 外键名称

使用外键目的是为了保证数据的完整性,删除的时候会有约束.

对例子进行外键约束

alter table product add foreign key(category_id) references category(cid);

从表不能够添加(更新),主表中不存在的数据.
主表不能够删除(更新),从表中已经使用的数据.

二 : 多表之间的关系

表与表数据之间的关系.

• 一对多关系 :

产品与产品类别, 一个产品对应一种类别,一个产品类别包含多种产品,举一个例子来说, 《蚁人》只属于漫威系列,《雷神》也只属于漫威系列,但是漫威宇宙还包含很多很多系列电影.

• 多对多关系 :

大学老师与学生的关系,一个学生可以从不同老师那里学习到知识,相对的一个老师可以教多个学生.

多对多关系建表原则 : 需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键,也就是将一个多对多拆分成两个一对多.

• 一对一关系:

在实际开发中应用不多,一对一可以用一张表完成.
外键唯一 : 主表的主键和从表的外键( 唯一 ),形成主外键关系,unique
外键是主键 : 主表的主键和从表的主键,形成主外键关系.

三 : 多表查询

建立多对多,订单表与商品表

订单表

create table orders(
    oid varchar(32) primary key,
    totalprice double);

订单项表

create table orderitem(
    oid varchar(50),
    pid varchar(50));

联合主键

alter table orderitem add primary key(oid,pid);

订单表和订单项表主外键关系

alter table orderitem add constraint orderitem_orders_fk foreign key(oid) references orders(oid);

商品表和订单项表的主外键关系

alter table orderitem add constraint orderitem_product_fk foreign key(pid) references product(pid);

多对多关系构图

+-------------+-------------+------+-----+---------+-------+| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| pid         | varchar(32) | NO   | PRI | NULL    |       || pname       | varchar(40) | YES  |     | NULL    |       |
| price       | double      | YES  |     | NULL    |       || category_id | varchar(32) | YES  | MUL | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

1
|
|
|
∞

+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| oid   | varchar(50) | NO   | PRI | NULL    |       || pid   | varchar(50) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

∞
|
|
|
1

+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| oid        | varchar(32) | NO   | PRI | NULL    |       || totalprice | double      | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

准备数据

+------+-----------------+-------+-------------+| pid  | pname           | price | category_id |
+------+-----------------+-------+-------------+
| p001 | 苹果笔记本      | 14000 | c001        || p002 | 苹果手机        |  9000 | c001        |
| p003 | 手写板          |  5000 | c001        || p004 | JACK JONES      |   800 | c002        |
| p005 | 耐克            |   600 | c002        || p006 | 阿迪达斯        |   440 | c002        |
| p007 | 李宁            |   200 | c002        || p008 | 香奈儿          |   800 | c003        |
| p009 | 兰蔻            |  1000 | c003        |+------+-----------------+-------+-------------+

+------+-----------+| cid  | cname     |
+------+-----------+
| c001 | 电子      || c002 | 服饰      |
| c003 | 化妆品    |+------+-----------+

• 交叉查询

两表之间的乘机,不常用

select * from A,B;

• 内连查询

隐式内连接

select * from A,B where 条件 ;

mysql> select * from category,product where cid = category_id;
+------+-----------+------+-----------------+-------+-------------+| cid  | cname     | pid  | pname           | price | category_id |
+------+-----------+------+-----------------+-------+-------------+
| c001 | 电子      | p001 | 苹果笔记本      | 14000 | c001        || c001 | 电子      | p002 | 苹果手机        |  9000 | c001        |
| c001 | 电子      | p003 | 手写板          |  5000 | c001        || c002 | 服饰      | p004 | JACK JONES      |   800 | c002        |
| c002 | 服饰      | p005 | 耐克            |   600 | c002        || c002 | 服饰      | p006 | 阿迪达斯        |   440 | c002        |
| c002 | 服饰      | p007 | 李宁            |   200 | c002        || c003 | 化妆品    | p008 | 香奈儿          |   800 | c003        |
| c003 | 化妆品    | p009 | 兰蔻            |  1000 | c003        |+------+-----------+------+-----------------+-------+-------------+

显示内连接

select * from A inner join B on 条件;

mysql> select distinct cname from category c inner join product p on c.cid = p.category_id;
+-----------+| cname     |+-----------+| 电子      || 服饰      || 化妆品    |+-----------+

• 外连接查询

我们往 类别表与商品表分别添加两条数据

 insert into category(cname,cid) values('甜品',5);

 insert into product(pid,pname,price,category_id) values('p010','甜筒',14,null);

左外连接 : left outer join

select * from A left outer join B on 条件

右外连接 : right outer join

select * from A right outer join B on 条件

mysql> select * from category c left outer join product p on c.cid = p.category_id;
+------+-----------+------+-----------------+-------+-------------+| cid  | cname     | pid  | pname           | price | category_id |
+------+-----------+------+-----------------+-------+-------------+
| 5    | 甜品      | NULL | NULL            |  NULL | NULL        || c001 | 电子      | p001 | 苹果笔记本      | 14000 | c001        |
| c001 | 电子      | p002 | 苹果手机        |  9000 | c001        || c001 | 电子      | p003 | 手写板          |  5000 | c001        |
| c002 | 服饰      | p004 | JACK JONES      |   800 | c002        || c002 | 服饰      | p005 | 耐克            |   600 | c002        |
| c002 | 服饰      | p006 | 阿迪达斯        |   440 | c002        || c002 | 服饰      | p007 | 李宁            |   200 | c002        |
| c003 | 化妆品    | p008 | 香奈儿          |   800 | c003        || c003 | 化妆品    | p009 | 兰蔻            |  1000 | c003        |
+------+-----------+------+-----------------+-------+-------------+
10 rows in set (0.00 sec)

mysql> select * from category c right outer join product p on c.cid = p.category_id;
+------+-----------+------+-----------------+-------+-------------+| cid  | cname     | pid  | pname           | price | category_id |
+------+-----------+------+-----------------+-------+-------------+
| c001 | 电子      | p001 | 苹果笔记本      | 14000 | c001        || c001 | 电子      | p002 | 苹果手机        |  9000 | c001        |
| c001 | 电子      | p003 | 手写板          |  5000 | c001        || c002 | 服饰      | p004 | JACK JONES      |   800 | c002        |
| c002 | 服饰      | p005 | 耐克            |   600 | c002        || c002 | 服饰      | p006 | 阿迪达斯        |   440 | c002        |
| c002 | 服饰      | p007 | 李宁            |   200 | c002        || c003 | 化妆品    | p008 | 香奈儿          |   800 | c003        |
| c003 | 化妆品    | p009 | 兰蔻            |  1000 | c003        || NULL | NULL      | p010 | 甜筒            |    14 | NULL        |
+------+-----------+------+-----------------+-------+-------------+

注意观察上面左连接与右连接的查询结果分析其联系.
内连接 : 查询两个表交集
左外连接 : 左表全部以及两个表的交集
右外连接 : 右表全部以及两个表的交集

四 : 子查询

一条select语句结果作为另一条语句的一部分(查询条件,查询结果,表等).

mysql> select * from product where category_id = (select cid from category where cname = '电子');
+------+-----------------+-------+-------------+| pid  | pname           | price | category_id |
+------+-----------------+-------+-------------+
| p001 | 苹果笔记本      | 14000 | c001        || p002 | 苹果手机        |  9000 | c001        |
| p003 | 手写板          |  5000 | c001        |+------+-----------------+-------+-------------+

作者：TianTianBaby223
链接：https://www.jianshu.com/p/4b86d498f2cb