多表查询

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

2.商品表
create table product(
pid varchar(32) primary key,
pname varchar(40),
price double,
category_id varchar(32)
);

 

4.
insert into category(cid,cname) values('c001','家电');
insert into category(cid,cname) values('c002','服饰');
insert into category(cid,cname) values('c003','化妆品');

 

 

5.

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

 

6.内连接查询

　　显示内连接：select * from catagory （inner） join product on cid=category_id;

　　

 

 

　　隐示内连接：select * from category c ,product p where c.cid=p.category_cid;

　　

 

7.外连接查询

　　左连接：select * from category left outer join product on cid=category_id;

 

　　右连接：select * from category right outer join product on cid=category_id;

　　

 

8.内连接外连接区别