//多表查询select*from emp , dept;-- 从emp和dept表中查询所有的字段数据--会产生笛卡尔积 所以要有限制条件select*from emp , dept where emp.dep_id =dept.did;//内连接查询//隐式内连接SELECT
t1. NAME,
t1.gender,
t2.dname
FROM
emp t1,
dept t2
WHERE
t1.dep_id = t2.did
//显式内连接select*from emp join dept on emp.dep_id = dept.did;//外连接select*from emp leftjoin dept on emp.dep_id = dept.did;//子查询select salary from emp where name ='猪八戒'-- 第一步得到3600select*from emp where salary >3600;-- 第二步select*from emp where salary >(select salary from emp where name ='猪八戒');-- 第三步//案例//单行单列-- 查询 '财务部' 或者 '市场部' 所有的员工的部门didselect did from dept where dname ='财务部'or dname ='市场部';select*from emp where dep_id in(select did from dept where dname ='财务部'or dname ='市场部');//单行多列-- 查询入职日期是 '2011-11-11' 之后的员工信息select*from emp where join_date >'2011-11-11';-- 将上面语句的结果作为虚拟表和dept表进行内连接查询select*from(select*from emp where join_date >'2011-11-11') t1, dept where
t1.dep_id = dept.did;