外键:
现在我们有两张表”分类表“和”商品表“,为了表明商品属于哪个分类,通常情况下,我们将在商品表上加一列,用于存放 cid 的信息,此列称为:外键
category(分类) |
cid |
cname |
products(商品) |
pid |
name |
price |
category_id(外键字段) |
此时”分类表 category“称为主表,”cid“我们称之为主键,”商品表 products“称为从表,”category_id“称为外键,我们通过主表的主键和从表的外键来描述主外键关系,呈现出的是一对多的关系。
外键特点:
从表外键值是 对主表主键的引用
从表外键类型必须和主表主键类型一致
声明外键约束:
语法:alter table 从表 add [constraint] [外键名称] foreign key(从表外键字段名) refereces 主表 (主表的主键)
创建分类表:
CREATE TABLE category(
cid VARCHAR(32) PRIMARY KEY,
cname VARCHAR(100)
);
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
创建商品表:
CREATE TABLE category(
cid VARCHAR(32) PRIMARY KEY,
cname VARCHAR(100)
);
INSERT INTO product(pid,pname,price,category_id) VALUES('p001','联想','5000','c001');
INSERT INTO product(pid,pname,price,category_id) VALUES('p002','海尔','5000','c001');
INSERT INTO product(pid,pname,price,category_id) VALUES('p003','雷神','5000','c001');
INSERT INTO product(pid,pname,price,category_id) VALUES('p004','JACK JONES','800','c002');
INSERT INTO product(pid,pname,price,category_id) VALUES('p005','真维斯','200','c002');
INSERT INTO product(pid,pname,price,category_id) VALUES('p006','花花公子','440','c002');
INSERT INTO product(pid,pname,price,category_id) VALUES('p007','劲霸','2000','c002');
INSERT INTO product(pid,pname,price,category_id) VALUES('p008','香奈儿','800','c003');
INSERT INTO product(pid,pname,price,category_id) VALUES('p009','相宜本草','200','c003');
建立关联:
alter table product add foreign key (category_id) references category(cid)
使用外键的目的:
保证数据的完整性
一对多建表原则:
在多的一方创建一个字段,字段作为外键指向一的一方的主键
多对多关系建表原则:
需要创建第三张表,中间表至少l两个字段,这两个字段分别作为外键指向另一方主键。
创建订单表:
CREATE TABLE orders(
oid VARCHAR(32) PRIMARY KEY,
totalprice DOUBLE
);
创建订单项表:
CREATE TABLE orderitem(
oid VARCHAR(50),
pid VARCHAR(50)
);
订单表和订单项表的主外键关系:
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);
多表查询(三种方式及其区别)
1、交叉连接查询:
SELECT * FROM category,product;
1 queries executed, 1 success, 0 errors, 0 warnings
查询:select * from category,product LIMIT 0, 1000
返回了 27 行
执行耗时 : 0.042 sec
传送时间 : 0.001 sec
总耗时 : 0.044 sec
2、内连接查询(使用关键字 inner join --inner可以省略):
隐式内连接:select * from A,B where 条件;
显示内连接:select * from A inner join B on 条件;
3、外连接查询:(使用关键字 outer join --outer可以省略)
左外连接:left outer join
select * from A left outer join B on 条件
右外连接:right outer join
select * from A right outer join B on 条件
现在看看左外连和右外连接的区别:
INSERT INTO category VALUES('c004',NULL);
INSERT INTO product VALUES ('p010','海飞丝',0.5,NULL);
子查询:
一条 select 语句结果作为另一条 select 语法的一部分(查询条件, 查询结果, 表等)。
查询“化妆品”分类上架商品信息