mysql推荐关系数据表_数据库篇-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 |

+------+-----------------+-------+-------------+