DQL -- 多表联合查询
2018-09-08 本文已影响131人
GHope
现实生活中,我们需要查询的数据并不独立存在在某一张表中,此时就需要进行多表联合,进行查询操作了。多表联合主分四种:隐式内连接,现实内连接,左外连接和右外连接。左外连接和右外连接同时使用称为全外连接。
数据表准备(用户,商品):
create table user(
-> id int auto_increment,
-> name varchar(30) not null,
-> gid varchar(10) default 0,
-> primary key(id)
-> );
create table goods(
-> gid int auto_increment,
-> name varchar(30) not null,
-> price int not null,
-> primary key(gid)
-> );

insert into user(neme,gid) values('GHope',3),
-> ('老子',1),
-> ('孔子',0),
-> ('鬼谷子',1),
-> ('孟子',1),
-> ('孙子',2),
-> ('庄子',2),
-> ('晏子',0);
insert into goods(name,price) values('四库全书',200),
-> ('逍遥游',99),
-> ('时间简史',897),
-> ('世界通史',765),
-> ('进化论',233);


隐式内连接

查询数据库中谁买了哪本书
select user.neme,goods.name from user,goods where user.gid=goods.gid;

显示内连接

查询数据库中谁买了哪本书
select user.neme as uname,goods.name as gname from user join goods on user.gid=goods.gid;


左外连接

查询用户购买书籍情况
select user.neme as uname,goods.name as gname from user left join goods on user.gid=goods.gid;

右外连接

查看书籍被购买情况
select user.neme as uname,goods.name as gname from user right join goods on user.gid=goods.gid;

记录联合

查看数据库内所有关于用户与书籍相关信息
select user.neme as uname,goods.name as gname from user left join goods on user.gid=goods.gid union select user.neme as uname,goods.name as gname from user right join goods on user.gid=goods.gid;

子(嵌套)查询

查询购买四库全书的用户
select neme from user where gid in (select gid from goods where name='四库全书');
