–笛卡尔集
select empno,ename, 员工表.deptno, 部门表.deptno, dname
from 部门表, 员工表;
–添加合适的条件，可以避免笛卡尔集，从而得到正确的多表查询记录
select empno,ename, 员工表.deptno, 部门表.deptno, dname
from 部门表, 员工表
where 部门表.deptno = 员工表.deptno;
–查询员工信息，要求显示：员工号，姓名，职位，部门名称
–等值连接
select empno,ename,job,dname from emp, dept where emp.deptno = dept.deptno;
–多个条件的等值连接，使用and操作符
select e.empno,e.ename,e.job,d.dname,d.deptno from emp e, dept d where e.deptno = d.deptno and e.deptno=10;
–显示所有员工的员工号、姓名、工资及其工资的等级。
select * from salgrade;
–非等值连接
select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between losal and hisal;
–按部门统计员工的人数，要求显示：部门号，部门名称，人数
select d.deptno,d.dname,count(e.empno) from dept d, emp e where d.deptno = e.deptno group by d.deptno, d.dname;
select * from dept;
select * from emp where deptno=40;
–外连接
select d.deptno,d.dname,count(e.empno) from dept d, emp e where d.deptno = e.deptno(+) group by d.deptno, d.dname;
–自连接
–查询所有员工的姓名和直属上级的姓名
select e.ename,m.ename
from emp e, emp m
where e.mgr = m.empno;
–验证 ford–>jones
select * from emp;
–cross join
select d.dname, e.ename, d.deptno, e.deptno from dept d cross join emp e;
select count(*) from emp;
select count(*) from dept;
–natural join
–查询员工名、工资以及所在部门名称
select e.ename, e.sal, d.dname from dept d natural join emp e;
–内连接
–using子句
select e.ename,e.sal, d.dname from dept d join emp e using(deptno);
–通过on指定内连接的条件
select e.ename,e.sal, d.dname from dept d join emp e on d.deptno = e.deptno;
–内连接的关键字inner join, inner通常省略
select e.ename,e.sal, d.dname from dept d inner join emp e on d.deptno = e.deptno;
–左外连接
select e.ename,e.sal, d.dname from dept d left join emp e on d.deptno = e.deptno;
–右连接
select e.ename,e.sal, d.dname from dept d right join emp e on d.deptno = e.deptno;
–完全连接
select e.ename,e.sal, d.dname from dept d full join emp e on d.deptno = e.deptno;
–emp01
create table emp01
as
select * from emp where deptno in(10,20);
–emp02
create table emp02
as
select * from emp where deptno in(20,30);
–合并显示emp01表和emp02表所有雇员的部门编号、员工号、员工姓名。
–10号部门有3个、20号部门有5个、
select * from emp01;
–30号部门有6个、
select * from emp02;
–union 14个记录
select deptno, empno, ename from emp01
union
select deptno, empno, ename from emp02;
–union all
–通过部门号进行排序
select deptno, empno, ename from emp01
union all
select deptno, empno, ename from emp02
order by deptno;
–通过列值进行排序，1代表第一列
select deptno, empno, ename from emp01
union all
select deptno, empno, ename from emp02
order by 1;
–intersect
select deptno, empno, ename from emp01
intersect
select deptno, empno, ename from emp02;
–minus
select deptno, empno, ename from emp01
minus
select deptno, empno, ename from emp02;