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