use test;
select * from user u, product p; # 合并查询，会显示所有表数据 user [as] u 设置别名，as可以省略
# 内联查询 查询交集部分
select u.name, p.product_name from user u, product p where p.user_id = u.id;
# 外联查询
# --左外联查询 可以查询左表所有数据
# 最后select到的数据会完全显示左表，不管右表是否有对应数据
select u.*, p.product_name from user u left join product p on p.user_id = u.id;
# 这里可以看到查询到了数据 下面我们删除外键和索引修改product的uid
alter table product drop foreign key publisher;
alter table product drop index publisher;
update product set user_id = 0 where product_name = 'QD';
select * from product; # 可以看到产品表uid已改
select u.*, p.product_name from user u left join product p on p.user_id = u.id;
# 可以看到左表数据完全显示，右表无对应数据则用null填充，右外联查询同理
# 自连接查询
alter table user add master int;
update user set master = 2 where id = 3;
update user set master = 3 where id = 5;
select * from user;

select u.id,u.name,i.id,i.name from user u,user i where i.master = u.id;
# 是不是有个数据没有查出来？
select u.id,u.name,i.id,i.name from user u left join user i on i.master = u.id;
# 使用左表查询就可以单独查询出来了

# 联合查询 union 比or效率高，不会导致索引失效
select user.name from user union select user.id from user;
select user.name from user union select user.name from user;
# 两次查询数据对比可以看到查询数据是按降序排列，根据查询先后顺序排成一列

# 子查询
insert into product values ('QF',8,0,now(),5);
select * from product;
select id from user where name = 'A'; # 查询A的id
select * from product where user_id = 5; # 查询发布人id为5的产品
select * from product where user_id = (select id from user where name = 'A'); # 合并
# 这就是子查询

# 列查询
select name from user where id in (select user_id from product);
# 内联列查询
select name,product_name from user, product where user.id in (select user_id from product);
# 行子查询 表懒得设置 参考下别人代码吧
-- 查询与xxx的薪资及直属领导相同的员工信息
select * from employee where (salary, manager) = (12500, 1);
select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');
# 表子查询
-- 查询与xxx1，xxx2的职位和薪资相同的员工
select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');
-- 查询入职日期是2006-01-01之后的员工，及其部门信息
select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;