多表查询

select * from tb_emp,tb_dept;这样会产生笛卡尔积
加上条件即可:
select * from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;

内连接:

隐式内连接:
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;
显式内连接:
select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id=tb_dept.id;

外连接:

左右连接:

select tb_emp.name,tb_dept.name from tb_emp right join tb_dept on tb_emp.dept_id=tb_dept.id;

select tb_emp.name,tb_dept.name from tb_emp right join tb_dept on tb_emp.dept_id=tb_dept.id;

左右连接只要调换名字就一样

子查询:

标量子查询:

select * from tb_emp where dept_id=(select id from tb_dept where name = '教研部');
select * from tb_emp where entrydate >(select entrydate from tb_emp where name='方东白');

列子查询:

select * from tb_emp where dept_id in (select id from tb_dept where name='教研部'or name='咨询部');

行子查询:

select * from tb_emp where (entrydate,job)=(select entrydate,job from tb_emp where name='韦一笑');

表子查询:

select e.*,d.name from (select * from tb_emp where entrydate >'2006-01-01') e,tb_dept d where e.dept_id = d.id;当作临时表使用