一 : 外键
现在有两个表category分类表
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| cid | varchar(32) | NO | PRI | NULL | |
| cname | varg { `char(100) | YES | | NULL | |
+-------+--------------+------+-----+------; 5 R i A---+-------+
与product商品表
+-------------+f F = e i o J -------------+------+t a 9 % B * R d d--. } p z t Z =---+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------_ % e--+------+-----+---------+-------+
| pid | varchar(32) |8 V l 6 NO | PRI | NULL | |
| pname | varchar(40) | YES | | N5 R w _ s }ULL | |
| price | double | YES | | NULL | |
|X v n category_id | varchar(32) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
其中在product表中的字段A Y scategory_id存放的b 6 q R G Z ,是 category表中cid(主键)的信息列称为外键. 此时分类称为主表,'ci- z * c F [d'称为主键,product称为从表,category_id称为外键,通过主表的主键和从表的外E p L ( S F键来描述主外键关系,呈现就是一对多的关系.
外键的特点 :
- 从表外键的值是对主表主键的引用.
- 从表外键类型,必须与主表主键类型一致.
声明外键约束
alter table 从表 add [constr| e ) g E D Q [aint][外键名称] foF _ = wreign key (从表字段名) w D M I %) references 主表(主表的主键)
外键名称 用于N 7 o删除外键约束的,一般建议_fk结尾
alter table 从表 drop foregin key 外键名称
使用外键目的是为了保证数据的完整性,删除的时候会有约束.
对例子进行外键约束
alter table prodS Z s a $uct add foreign key(category_id) references category(cid);
从表不能够添加(更新),主表o a E ` X 1 %中不存在的数据.
主表不能够删除(更新),从表a [ S r中已经使用的数据.
二 : 多表之间的关系
表与表数据之间的关系.
-
一对多关系 :
产品与产品类别, 一个产品对应一种类别,一个产品类别包含多种产品,举一个例子来说, 《蚁人》只属于漫威系列,《雷神》也只属于漫威系列,但是漫威宇宙还包含很多很多系列电影.^ & v
-
多对多关系 :
大学老师与学生的关系,一Z ~ R S ; h 1 M个学生可以从不同老师那里学习到知识,相对的一个老师可以教多个学生.
多对多关系建表原则 : 需要创建第三张表,中间表中至少两个字段,这两个字段分别作6 + ; { J t f ?为外键指向各自一方的主键,也就是将一个多对多拆分成两个一对多.
-
一对一关系:
在实际开发4 ? J ; R | I ;中应用不多,一对一可以用一张表完成.
外键唯一 : 主表的主键和从表的外键f d j E x( 唯一 ),形成主外键关系,unique
外键% L o c B 1 S 7 %是主键 : 主表的主键和从表的主键,形成主外键关系.
三 : 多表查询
建立多对多,订单表与商品表
订单表
create table orders(
oid varchar(32) primary key,
totalprice _ C s ! 1 % * double
);
订单项表
create table orderitem(
oid varco B ! q = 5 }har(50),
pid varchar(50)
);
联合主键
alter table orderitemD i 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/ 7 + _ U , )_product_fk foreign key(pid) references produc} t V R ; 0 G it(pid);
多对B s P $ k多关系构图
+-------------+-------------+------+-j [ L i )----+------* : r 0---+-------+V C m o . $
| Field | Type | Null | Key | Default | Extra |l r `
+----) Z (---------+-------------+------+-----+----V o-----+-------+
| pid | varchar(32) | NO | PRI | NULL | |
| pname | varchar(40) | YES | | NULL | |
| price | double | YES | | NULL | |
| category_id | varchar(32) | YES | MUL | NULL | |
+-------------+-------------+------+-----+---------+-------+
1
|
|
|
∞
+-------+-------------+------+-, e ~----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| oid | varchar(50) | NO | PRI | NULL | |
| pid | varchar(50) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+j i { : 4 G C q r-------+
∞
|
|
|
1
+------------+-------------+------+-----+--------C P [ T P F 7-+-------K f j+
| Field | Type | Null | Key | Default | Extra |
+---------_ 3 2 Z + x V---+-------------+------+-----+---------+-------+
| oid | varcharQ @ @ % v 4 t r Q(32) | NO | PRI | NULL | |
| totalprice | douC ; y 8 C R a *ble | YES | | NULL | |
+------------+Y * 5 j E a ~-------------+------+-----+---------+-------+
准备数据
+A 1 C }------+-----------------+-------p ~ p g *+-------------+
| pid | pname | prq l ` F 0 C $ J rice | category_id |
+------+5 ? - p : % p ^-----------------+-------+-------------+
| p001 | 苹: A ? ;果笔记本 | 14000 |n 8 a c001 |
| p0024 [ / | 苹果手机 |J O ) 9000 | c001 |
| p003 | 手写板 | 5000 | c001 |
| p004 | JACK JONES | 800 | c002 |
| p005 | 耐克 | 600 | c002 |
| p` Z . 2006 | 阿迪达斯 | 440 | c002 |
| p007 | 李宁 | 200 | c002 |
| p008 | 香奈儿 | 800 | c003 |
| p009 | 兰蔻 | 1000 | c003j M t h x |
+------+-----------------+-------+-------------+
+------+-----------+
| cid | cname |
+-+ - 6 z o i $ ,-----+-----------+
| c001 | 电子 |
| c002 | 服饰 |
| c: ? z N003 | 化妆品 |
+------+------- s *----+
-
交| Q +叉查询
两H j M L 6 F表之间的乘机,不常用
select * from A,B;
-
内连查询
隐式内连接
select * from A,B where 条件 ;
mysql> select * from category,T y 3 U ! c t ^ 8product where cid = category| . C_id;
+-----X { z d & C J-+-----------+------+-----------------+-------+-------------+
| cid | cname | pid | pname | price | category_id |O + ] 5 q O
+------+-----------+------+-----------------+-------+-------------+
| c001 | 电子 | p001 | 苹果笔记本 | 14000 | c001 |
| c001 | 电` X / z F { m 4子 | p002 | 苹果手机 | 9000 | c001 |
| c001 | 电子 | p003 | 手写板 | 5000 | c001 |
|Y P . f 5 C , d c002 | 服饰 | p004 | JACK JONEo SS | 800 | c002 |
| c002 | 服饰 | p005H * + S C c ^ | 耐克 | 600 | c002 |
| cj # m S E {002 | 服饰 | p006 | 阿迪达斯 | 440 | c002 |
| c002 | 服饰 | p007 | 李宁 | 200 | c002 |
| c003 | 化妆c X e q o品 | p008 | 香奈儿 | 800 | c003 |
| c003 | 化妆品 | p009 | 兰蔻 | 1v Y ) % b ! $000 | c003 |
+------+-----------+------+-----------------+-------+-------------+
显示内连接
selects : g v 7 : * from A inner jo= ! d ( m (in B on 条件;
mysql> select distinct cname from category c inner join producD k { V ` ^ o 2t p on c.cid = p.category_id;
+-----------+
| cname |
+-----------+
| 电子 |
| 服饰 |
| 化妆品 |
+-----------+
-
外连接查询
我们往 类别表与商品表分别添加两条数据
insert into category(c@ i H / U g o hname,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 条件
myf n 7 | Y `sql> select * from ca2 a x W . Ktegory c left outeR [ _ W + Pr join product p on c.cid = p.category_id;
+------+-----------+------+--------9 D 1 B L O n * /---------+-------+-------------+
| cid | cname | pid | pname | price | catQ 4 Fegory_id |
+------+-----------+------+-----------------+-------+-----------o T @ V t q W--+
| 5 |Z [ M L # 甜品 | NULL | NULL | NULL | NULL |
| c001 | 电子 | p001 | 苹果笔记本 | 14000 | c001 |
| c001 || . Y 8 2 1 i 电子 | p002 | 苹果手机 | 9000 | c001 |
| c001 | 电子 | p003 | 手写板 | 5000 | c001 |
| ci + T T002 | 服饰 | p004[ $ 5 Z / | JACK JONES | 800 | c002 |
| c002 | 服饰 | p005 | 耐克 | 600 | c002 |
| c002 | 服饰 | p006 | 阿迪达斯 | 440 | c002 |V w e
| c002 | 服饰 | p007 | 李宁 | 200 | c002 |
|w m ` i I A P c003 | 化妆品 | p008 | 香奈儿 | 800 | c003 |
| c003 | 化妆品 | p009 | 兰蔻 | 1000 | c003 |
+------+-----------+------+-----------------+-------+-----v T ~--------+
10 rows in set (0.00 sec)
mysql> select * from category c right outm 6 x I M Zer join product p on c.cid = p.cate7 T a 7 P - e H +gory_id;
+------+-----------R 8 N+------+-----------------+-------+-------------+
| cid | cname | pid | pname | price | category_id |
+------+-----------+------+-----------------+-------+--0 , Z R w k----------D 0 U-+
| c001 | 电子 | p001 | 苹果笔记本 | 14000 | c001 |
| c001 | 电子 | p002 | 苹果手机 | 9000 | c001 |
| c001 | 电子 | p003 | 手写板 | 5000 | c001 |
| c002 | 服饰 | p004 | JACK JONES | 800 | c002 |
| c002 | 服饰 | p005 | 耐克 | 600 | c002 |
| c002 | 服饰 | p006 | 阿迪达斯 | 49 o X w O f 0 X =40 | c002 |
| c002 | 服饰 | p007 | 李宁 | 200 | c002 |
| c003 | 化妆品 | p008 | 香奈儿 | 800 | c003 |
| c003 | 化妆品 | p009 | 兰蔻 | 14 + Y P b Y T H000 | cq | % /003 |
| NULL | NULL | p010 | 甜筒 | 14e i P | NULL |
+------+-----------+------+-----------------+-------+-------------+
注意观察上面左连接与右连接的查询结果分析其联系.
内连接 : 查询两个表交集
左外% O D y v & ( o 5连接 : 左表全D l N O 0 H C ^部以及两个表的交集
右外连接 : 右表全部以及[ 9 e ~ 0两个表的交集
四 : 子$ 2 f X U 8 V b查询
一条seY _ r 8 , Z Elect语句结果作为另一条语句的一部分(查询条件,查询结果,表等).
mysql> select * from product wherf & W _ Ie category_id = (select cid fW c v A Crom category where cname = '电子');
+------+---j : a 3-----f I t 9 4 Q---------+-------+---------$ Y Y D c----+
| pid | pnaY E K U c Nme | price | category_id |
+------+-----------------+-------+-------------+
| p001] U i / u 7 @ | 苹果笔记本 | 14000 | c001 |
| p002 | 苹果手机 | 9000 | c001 |
| p003 | 手写板 | 5000 | c001 |
+------+-----------------+-------+-------------+