多表查询（续）

-- 多表查询连接
create table t1(key1 char, v1 int);
create table t2(key2 char, v2 int);

insert into t1 values('a',1), ('a',2),('b',3), ('c',4), ('a', 13);
insert into t2 values('b',10), ('b',11),('a',12),('a',13),('e',14);

select * from t1;
select * from t2;

-- 内连接
select * from t1 inner join t2 on t1.key1 = t2.key2;
-- 左连接
select * from t1 left join t2 on t1.key1 = t2.key2;
-- 右连接
select * from t1 right join t2 on t1.key1 = t2.key2;
select * from t2 left join t1 on t1.key1 = t2.key2;

-- 合并查询(去重)
select * from t1 union select * from t2;

-- 合并查询(不去重)
select * from t1 
union all
select * from t2;









create database cda;
use cda;

create table a_user(
userid varchar(10),
tel varchar(10)
);

create table b_order(
userid varchar(10),
end_time datetime
);

insert into a_user values('A1','123456789'),
						 ('A2','134577432'),
                         ('A3','677389494'),
                         ('A5','832526477'),
                         ('A6','432728448');
                         
insert into b_order values('A1','2019-5-1 12:00'),
						  ('A2','2019-3-2 13:00'),
                          ('A2','2019-5-3 10:00'),
                          ('A3','2019-5-2 11:00'),
                          ('A4','2019-5-1 17:00'),
                          ('A5','2019-5-9 14:00'),
                          ('A1','2019-5-6 15:00'),
                          ('A5','2019-5-8 16:00'),
                          ('A2','2019-5-4 13:00'),
                          ('A4','2019-5-5 18:00'),
                          ('A3','2019-5-7 13:00');
                          
select * from a_user;
select * from b_order;
-- 出现在表a却不在表b的userid
select * from a_user left join b_order on a_user.userid=b_order.userid
where b_order.userid is null;

select userid from a_user where userid not in (select userid from b_order);
-- 每个userid的最新结束时间

select 
userid, max(end_time)
from 
(select * from b_order
order by userid asc, end_time desc) as t
group by userid;

select userid, max(end_time) from b_order 
group by userid;
-- 用户结束时间在3月份的userid及tel
select b_order.userid, tel
from b_order left join a_user on a_user.userid = b_order.userid
where month(end_time) = 3;

select t.userid, tel from
(select * from b_order where month(end_time)=3) t
left join a_user on t.userid = a_user.userid;

 主要查询谁，把谁当做主表。