一 : 外键
现在有两个表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 |
+------+-----------------+-------+-------------+
