mysql一对多，多对一的外键关联操作以及内、外连接查询和子查询

一对多
第一步：创建两张表

.分类表
create table category(
cid varchar(32) primary key,
cname varchar(100)
);

.商品表
create table product(
pid varchar(32) primary key,
pname varchar(40),
price double,
category_id varchar(32)
);
添加外键列
alter table product add category_id varchar(32);

第二步关联外键
说明：其中 category表为主表，product表为从表。cid为主键，pid也为主键。
product中的 category_id为从键。
声明外键约束语法：
mysql一对多，多对一的外键关联操作以及内、外连接查询和子查询例如：
ALTER TABLE product ADD CONSTRAINT product_fk FOREIGN KEY (category_id) REFERENCES category(cid);

删除外键约束：
ALTER TABLE product DROP FOREIGN KEY product_fk ;

多对多
.订单表
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);

第二部分

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’,‘联想’,‘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’);

多表查询
1.交叉连接查询
select *from A,B（基本不会使用，得到的是两个表的积）
mysql一对多，多对一的外键关联操作以及内、外连接查询和子查询
（隐内连）SELECT *FROM product,category WHERE product.category_id = category.cid
（显内连）SELECT DISTINCT cname FROM category INNER JOIN product ON category.cid = product.category_id
另外一种写法
（隐内连）SELECT *FROM product p,category c WHERE p.category_id = c.cid
（显内连）SELECT DISTINCT cname FROM category c INNER JOIN product p ON c.cid = p.category_id
mysql一对多，多对一的外键关联操作以及内、外连接查询和子查询
左外连接：SELECT *FROM product LEFT JOIN category ON product.category_id=category.cid
右外连接：SELECT *FROM product RIGHT JOIN category ON product.category_id=category.`cid

内连，外联（左外连接，右外连接区别）
mysql一对多，多对一的外键关联操作以及内、外连接查询和子查询

子查询

先查主表的主键(根据指定的条件<化妆品，cname[属于主表category表中的内容，那么先查出主表的主键cid.然后将查出的内容作为条件。]>)
select cid from category where panme=’化妆品’

查询所有商品的详细信息(根据分类id查询)：
select * from product where category_id=cid[需要先从主表category中查询出来。]

select * from product where category_id=( select cid from category where cname=’化妆品’);

mysql一对多，多对一的外键关联操作以及内、外连接查询和子查询

相关推荐