基本表查询

多表查询

--合并结果集(列数和列数类型要一致)

select name,score from a
union 
select name,score from b;//并集结果集


select * from a
union all

select * from b;//将两个结果都显示出来

--连接查询

select * from student s,teacher t where s.sid=t.sid;

--内连接查询

select * from student s join teacher t on s.sid=t.sid;(注：on只能是主外键判断)

--外键查询

分左右外连接

select * from student s left join teacher t on s.sid=t.sid;参考左表

select * from student s right join teacher t on s.sid=t.sid;参考右表

--多表连查

select * from student s,teacher t,course c where s.sid=t.sid and s.sid=c.sid;99查询法

select * from student s join teacher t on s.sid=t.sid join course c on s.sid=c.sid;

--自然连接

select * from student natural join teacher;

--子查询

一个selec语句中包含另一个完整的select语句，嵌套查询

子查询的位置：where后面，作为第一个select语句中where的条件eg:select * from emp where deptno=(select deptno from emp where ename='SCOTT');动态传参

select * from emp where (job,sal) in (select job ,sal from emp where ename='Jack');多列

也可以在from后面