SELECT
*
FROM
emp;
SELECT
*
FROM
dept;– 多表查询
SELECT
*
FROM
emp,
dept;– 笛卡尔积 有A和B两个集合 取AB所有的组合情况
— 消除无效数据
— 查询emp和dept的数据，emp.dep_id=dept.did
— 隐式内连接
SELECT
*
FROM
emp,
dept
WHERE
emp.dep_id = dept.did;– 查询emp表的name,gender,dept表的dname
SELECT
emp.NAME,
emp.gender,
dept.dname
FROM
emp,
dept
WHERE
emp.dep_id = dept.did;– 给表起别名
SELECT
t1.NAME as ‘姓名’,
t1.gender as ‘性别’,
t2.dname as ‘部门’
FROM
emp t1,
dept t2
WHERE
t1.dep_id = t2.did;
— 显示内连接
SELECT * from emp INNER join dept on emp.dep_id = dept.did;
— 显式内连接inner 可以省略
SELECT * from emp join dept on emp.dep_id = dept.did;
— 左外连接
— 查询emp表所有数据和对应的部门信息
SELECT * from emp LEFT JOIN dept on emp.dep_id=dept.did;
— 右外连接
— 查询dept表所有数据和对应的员工信息
SELECT * from emp RIGHT JOIN dept on emp.dep_id=dept.did;
SELECT * from dept LEFT JOIN emp on emp.dep_id=dept.did;
— 子查询
— 查询工资高于猪八戒的员工信息
— 1.查询猪八戒的工资
SELECT salary from emp where name=’猪八戒’;
— 2.查询工资高于猪八戒的员工信息
SELECT * from emp where salary >3600;
SELECT * from emp where salary >(SELECT salary from emp where name=’猪八戒’);
— 单行单列 作用：
— 查询财务部和市场部所有的员工信息
SELECT 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′;
SELECT * from (SELECT * from emp where join_date>’2011-11-11′) t1 ,dept where t1.dep_id=dept.did;
原文地址：http://www.cnblogs.com/cy-xt/p/16928614.html
