Oracle数据库----查询

迷南。 2021-12-12 19:39 370阅读 0赞

\--笛卡尔集  
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;

转载于:https://www.cnblogs.com/xiaomifeng1010/p/11111914.html

还没有评论，来说两句吧...