内外连接
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 ="技术部");