数据库-内外连接 子查询

内外连接

select * from forum inner join reply on forum.id = reply.t_id;#inner是内连接，查询满足relay.id = forum.id的两个表数据

select * from forum left outer join reply on forum.id = reply.t_id;#查询forum和对应的relay表中的回复，如果没有回复，用null填充

内外连接区别：1. 内连接只显示满足on条件的内容 2. 外链接根据是左外链接还是右外连接，先把这一侧的表内容显示出来，另一测不满足on条件 的，字段用null填充

多表连接查询

select m.id,s.name,c.tel,m.createDate,s2.content from mission m
inner join salesstaff s on m.salesstaff_id = s.id
inner join customer c on m.customer_id = c.id
inner join status s2 on m.status_id = s2.id;

子查询


查询：技术部人员工资，展示字段：姓名、工资、部门

select salary.name,salary.money,department.name from salary inner joi
n department on salary.d_id = department.id where department.name =
"技术部";

查询：非技术部的工资，展示字段：姓名、工资、部门

select salary.name,salary.money,d.name from salary inner join departm
ent d on salary.d_id = d.id where d.name != "技术部";

查询：非技术部人员，且工资比技术部任何人员工资都高的人员信息
方法1

select salary.name,salary.money,d.name from salary inner join departm
ent d on salary.d_id = d.id
where
d.name != "技术部"
and
salary.money > (
select max(salary.money) from salary inner join department on salar
y.d_id = department.id where department.name ="技术部"
);

方法2

select salary.name,salary.money,d.name from salary inner join departm
ent d on salary.d_id = d.id
where
d.name != "技术部"
and
salary.money > all(
select salary.money from salary inner join department on salary.d_id
 = department.id where department.name ="技术部"
);

查询：非技术部人员，工资高于技术部最低工资

select * from salary where salary.money>
(select min(salary.money) from salary inner join department d on sala
ry.d_id = d.id and d.name ="技术部");