2018.5.9 Oracle数据库查询命令
0.查询所有数据（最简单，但是时间很久）
select * from emp;
Result:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
1.查询系统时间 （这个表是用户的数据库里面没有建立所以要用虚表 dual ）
select sysdate from dual;
Result：
SYSDATE
-----------
2018/5/9 20
2.去除重复的查询数据
select distinct(deptno) from emp;
Result：
DEPTNO
------
30
20
10
3.查询数据库起别名显示
select ename "姓名" ,sal*12 "年薪" from emp;
Result
姓名 年薪
---------- ----------
SMITH 9600
ALLEN 19200
WARD 15000
JONES 35700
MARTIN 15000
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
JAMES 11400
FORD 36000
MILLER 15600
4.进行运算
select 3*4+1*2 from dual;
Result:
3*4+1*2
----------
14
5.NVL函数可以处理有空值的字段
6.查询部门编号为10的员工信息 （需要加上where条件过滤）
select * from emp where DEPTNO=10;
Result:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7839 KING PRESIDENT 1981/11/17 5000.00 10
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
7.查询编号不为10的部门的员工信息
select ename,sal,deptno from emp where deptno <>10;
Result
ENAME SAL DEPTNO
---------- --------- ------
SMITH 800.00 20
ALLEN 1600.00 30
WARD 1250.00 30
JONES 2975.00 20
MARTIN 1250.00 30
BLAKE 2850.00 30
SCOTT 3000.00 20
TURNER 1500.00 30
ADAMS 1100.00 20
JAMES 950.00 30
FORD 3000.00 20
8.范围查询显示结果
select ename,sal from emp where sal between 800 and 1500;
Result:
ENAME SAL
---------- ---------
SMITH 800.00
WARD 1250.00
MARTIN 1250.00
TURNER 1500.00
ADAMS 1100.00
JAMES 950.00
MILLER 1300.00
或者 select ename,sal from emp where sal in(800,1500,2000);
Result
ENAME SAL
---------- ---------
SMITH 800.00
TURNER 1500.00
9.模糊查询
select * from emp where ename like '%ALL%';
Result:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
10.查询第二个是A开头的字母的员工信息
select * from emp where ename like '%_A%';
Result
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
11.查询薪水在800-2000之间的员工信息 并且按照部门的降序排序输出
select * from emp where sal between 800 and 2000 order by deptno desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
练习题
--查询部门名称为ACCOUNTING和RESEARCH的员工信息
select deptno from dept where dname in('ACCOUNTING', 'RESEARCH');
select * from emp where deptno in(select deptno from dept where dname in('ACCOUNTING', 'RESEARCH'));
--查询不是经理的员工的信息
--查询工资比10号部门都要低的员工信息
select min(sal) from emp where deptno=10;
--显示和KING同部门，工资低于FORD的雇员有哪些
select deptno from emp where ename='KING';
select sal from emp where ename='FORD';
select * from emp where deptno=(select deptno from emp where ename='KING') and sal<(select sal from emp where ename='FORD');
--找出部门30中所有的经理（MANAGER）和部门20中所有办事员（CLERK）
select ename,job from emp where deptno=30 and job='MANAGER';
select ename,job from emp where deptno=20 and job='CLERK';
select ename,job from emp where (deptno=30 and job='MANAGER') or (deptno=20 and job='CLERK');
--查询员工“SMITH”所在部门的员工，但不包含“FORD”的员工姓名、入职时间及部门编号
select deptno from emp where ename='SMITH';
select * from emp where deptno=(select deptno from emp where ename='SMITH') and ename not in('FORD');
--查询大于员工平均工资的所有员工，显示编号、姓名、工资，并按照升序排序
select avg(sal) from emp;
select empno,ename,sal from emp where sal>(select avg(sal) from emp) order by sal;
--查询员工姓名中包含“U”的员工所在部门编号下的所有员工的编号、姓名。
select empno,ename from emp where deptno=(select deptno from emp where ename like '%U%');
--查询经理“KING”下的所有员工的姓名、工资
select empno from emp where ename='KING';--7399
select ename,sal from emp where MGR=(select empno from emp where ename='KING');
--查询属于“ACCOUNTING”部门下所有的员工的部门编号、员工姓名及工作
select deptno from dept where dname='ACCOUNTING';
select deptno,ename,job from emp where deptno=(select deptno from dept where dname='ACCOUNTING');
--查询员工中包含“U”员工所在部门下的员工工资大于平均工资的员工，显示员工编号、姓名、及工资
select deptno from emp where ename like '%U%'; --30
select avg(sal) from emp; --2073.11
select * from emp where sal>(select avg(sal) from emp) and deptno=(select deptno from emp where ename like '%U%');
