mysql中左右连接，全连接

# 左连接
-- 执行下列语句：创建如下两个表，POST职务表，STAFF雇员表：
use mydb;
use mydb;
create table post(
	id int auto_increment primary key comment '编号',
    aname varchar(20) comment '职位');
create table staff(
	id int auto_increment primary key comment '编号',
	bname varchar(20) comment '姓名',
    pid int comment '职位ID' ); -- 取值依赖于POSITION表中的ID（外键关系）
insert into post(aname) values('总经理');
insert into post(aname) values('经理');
insert into staff(bname,pid) values('张三',1);
insert into staff(bname,pid) values('李四',null);
select * from staff;
select * from post;
-- inner join
select * from staff ti inner join post t2 on t1.pid=t2.id; -- t1代指staff表，t2代指 post表
-- left join
select t1.*,t2.aname from staff t1 left join post t2 on t1.pid=t2.id; -- 左边的表放在主要的位置，与右边的表进行交叉连接
-- right join
select * from staff t1 right join post t2 on t1.pid=t2.id;    -- 连接的依据就是2个表中相同的地方，表一的pid的1对应职位表post的ID为1，就可以显示对应ID下的职位
-- 全连接
select * from staff t1 left join post t2 on t1.pid=t2.id union select * from staff t1 right join post t2 on t1.pid=t2.id;
-- 全连接则是把2个表拼在一起，并且把相同的行归类排列,一般常用于2个表的结合

create table cate(
`id` int primary key auto_increment,
`cname` varchar(50) ,
`pid` int 
);
insert into cate values
(default,'电子',null),
(default,'服务',null),
(default,'手机',1),
(default,'电脑',1),
(default,'外套',2),
(default,'围巾',2);
select * from cate;
select * from staff ti inner join post t2 on t1.pid=t2.id; -- t1代指staff表，t2代指 post表
-- 表内连接
select * from cate c1  inner join cate c2 on c1.id=c2.pid ;
select c2.* from cate c1 ,cate c2 where c1.cname='服饰' and c1.id=c2.pid ;
select *,(select cname from cate where id=cate.pip)from cate ;

-- 子查询
select *,(select aname from post where id=t1.pid) from staff t1;

drop table if exists cate1;
create table cate1(
`id` int primary key auto_increment,
`cname` varchar(50) ,
`pid` int 
);
insert into cate1 values
(default,'电子',null),
(default,'服务',null),
(default,'手机',1),
(default,'电脑',1),
(default,'外套',2),
(default,'围巾',2);
select * from cate1;
select * from cate1 c1  inner join cate1 c2 on c1.id=c2.pid ;
select * from cate1 where pid=(select id from cate1 where cname='服饰') -- 子查询查找有关服饰类的商品

-- 组合两个表
Create table If Not Exists Person (personId int, firstName varchar(255), lastName varchar(255));
Create table If Not Exists Address (addressId int, personId int, city varchar(255), state varchar(255));
insert into Person (personId, lastName, firstName) values ('1', 'Wang', 'Allen');
insert into Person (personId, lastName, firstName) values ('2', 'Alice', 'Bob');

insert into Address (addressId, personId, city, state) values ('1', '2', 'New York City', 'New York');
insert into Address (addressId, personId, city, state) values ('2', '3', 'Leetcode', 'California');
select * from person;
select * from address;

-- 全连接，可以把空值显示出来
select * from person t1 left join address t2 on t1.personId=t2.personid union select * from person t1 right join address t2 on t1.personId=t2.personid;

-- 方法二
select firstname,lastname,city,state from person left join address on person.personid-address.personid;