5：多表查询

1：笛卡乐积

展示的内容以第一个表为主（不变），第二个表的数据每行重复展示

select * from employee_tbl,user

想要展示公共字段必须加表名

select user.name,date,address,chinese from employee_tbl,user where employee_tbl.name=user.name

2：自连接（把表当做两张）

select worker.ename as '职员'，boss.ename as '上级名' from emp as worker，emp as boss where worker.mgr=boss.empno

 

3：子查询

单行子查询

select deptno from emp where deptno=(select deptno from emp where ename='smith')

多行子查询

select * from employee where deptNO in (select id from dept where deptName='明教')

-- 左联 left join on（左表为主--字段数据全部展示，没有的null展示）
select * from employee left join dept on dept.id=employee.deptNO

select employee_tbl.name,date,address  from employee_tbl LEFT JOIN user on employee_tbl.name=user.name