1.子查询
将查询的结果作为另一个查询的条件
优点:好用,缺点:性能不高
要求:查询的结果只能是单列的
select * from table1 where id in (select userid from table2);
2.联合查询
--自然查询,笛卡尔积查询
select * from table1,table2;
--等值查询
select * from table1 u,table2 u1 on u.id=u1.id;
--内连接,
select * from table1 u inner join table2 u1 on u.id=u1.id;
--左外连接,
select * from table1 u left join table2 u1 on u.id=u1.id;
右外连接
select * from table1 u right join table2 u1 on u.id=u1.id;
全连接查询
select * from table1 u full join table2 u1 on u.id=u1.id;
3.分页查询 rownum
select * from (select u1.*,rownum rn from table1 where rownum<3) u where u.rn>0;
--分页查询配合排序使用
select * from (select u1.*,rownum rn
from (select * from table order by id desc)u1
where rownum<3) u where u.rn>0;