实际开发中,总是多表情况,所以学会设计多表的情况是及其必要的
分类表:
create table category(
cid varchar(32) primary key comment '分类编号',
cname varchar(100) comment '分类名称'
)comment '分类表';
商品表:
create table product(
pid varchar(32) primary key,
pname varchar(40),
price double,
category_id varchar(32) comment 'category表的外键'
)comment '商品表';
添加外键列category_id指向上一个表的主键cid:
//添加外键列
alter table product add foreign key(category_id) references category(cid);
添加约束:
“`
//添加约束
alter table product add constraint product_fk foreign key(category_id) references category(cid);
插入数据:
insert into category(cid,cname) value('c001','家电'); insert into category(cid,cname) value('c002','服饰'); insert into category(cid,cname) value('c003','化妆品'); 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','花花公子','1000','c002'); insert into product(pid,pname,price,category_id) values('p005','真维斯','400','c002'); insert into product(pid,pname,price,category_id) values('p006','劲霸','600','c002'); insert into product(pid,pname,price,category_id) values('p007','雅诗兰黛','800','c003'); insert into product(pid,pname,price,category_id) values('p008','雪肌','900','c003'); insert into product(pid,pname,price,category_id) values('p009','相宜本草','400','c003');
插入中文,所以之前要设置: set names gbk;
如果要在从表中增加新的记录,则需要关注主表:
insert into category values(‘c004’,null);
insert into product values(‘p010’,’海飞丝’,50,null);
从表:product
主表:category
多表设计时常用的是多表查询:
//交叉连接查询【基本不会使用,得到的是两个表的乘积】
select * from A,B
//内连接查询【inner join, -inner可省略】
–隐式内连接:select * from A,B where 条件
–显示内连接:select * from A inner join B on 条件
//外联结查询[outer join -outer可省略]
–左外连接:
left outer join:
select * from A left outer join B on 条件
–右外连接
左外连接:
左表全部及两个表的交集
右外连接:
右表全部以及两个表的交集
内连接:两表交集
左外连接:
内连接: