MySQL学习(二)——MySQL多表

分页操作：使用limit(参数1，参数2)

    起始位置(参数1)=(第几页-)*每页显示的条数(参数2)

.分类表

create table category(

    cid varchar() primary key,

    cname varchar()

);

.商品表

create table product(

    pid varchar() primary key,

    pname varchar(),

    price double,

    category_id varchar()

);

alter table product add foreign key(category_id) references category(cid);

.添加外键列

alter table product add category_id varchar();

.添加约束

alter table product add constraint product_fk foreign key(category_id) references category(cid);

.订单表

create table orders(

    oid varchar() primary key,

    totalprice double

);

.订单项表

create table orderitem(

    oid varchar(),

    pid varchar()

);

.联合主键

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);

insert into category(cid,cname) values('c001','家电');

insert into category(cid,cname) values('c002','服饰');

insert into category(cid,cname) values('c003','化妆品');

insert into product(pid,pname,price,category_id) values('p001','联想','','c001');

insert into product(pid,pname,price,category_id) values('p002','海尔','','c001');

insert into product(pid,pname,price,category_id) values('p003','雷神','','c001');

insert into product(pid,pname,price,category_id) values('p004','JACK JONES','','c002');

insert into product(pid,pname,price,category_id) values('p005','真维斯','','c002');

insert into product(pid,pname,price,category_id) values('p006','花花公子','','c002');

insert into product(pid,pname,price,category_id) values('p007','劲霸','','c002');

insert into product(pid,pname,price,category_id) values('p008','香奈儿','','c003');

insert into product(pid,pname,price,category_id) values('p009','相宜本草','','c003');

声明外键约束

.从orderitem到product的关系

alter table orderitem add foreign key(pid) references product(pid);

1.1 删除外键约束

alter table 从表 drop foreign key 外键名称

.从orderitem到orders的关系