Oracle语句及查询结果

来源：互联网发布：java字符串转date类型编辑：程序博客网时间：2024/05/02 20:53
  SQL> conn scott/scott已连接。SQL> --1.查询员工的所用数据SQL> select * from emp;     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7369 SMITH      CLERK           7902 17-12月-80            800                    20                                                                                                                                                            7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30                                                                                                                                                            7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30                                                                                                                                                           EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                                                                                                                                                            7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                                                                                                                                                            7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                                                                                                                                                           EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                                                                                                                                                            7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20                                                                                                                                                            7839 KING       PRESIDENT            17-11月-81           5000                    10                                                                                                                                                           EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30                                                                                                                                                            7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                                                                                                                                                            7900 JAMES      CLERK           7698 03-12月-81            950                    30                                                                                                                                                           EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7902 FORD       ANALYST         7566 03-12月-81           3000                    20                                                                                                                                                            7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                                                                                                                                                      已选择14行。SQL> --2.查询职位（JOB）为“PRESIDENT”的员工的工资SQL> select sal from emp where job ='PRESIDENT';       SAL                                                                     ----------                                                                            5000                                                                      SQL> --3.查询佣金（COMM）为0或为NULL的员工信息SQL> select * from emp where comm=0 or comm is null;     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7369 SMITH      CLERK           7902 17-12月-80            800                    20                                                                                                                                                            7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                                                                                                                                                            7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                                                                                                                                                           EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                                                                                                                                                            7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20                                                                                                                                                            7839 KING       PRESIDENT            17-11月-81           5000                    10                                                                                                                                                           EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30                                                                                                                                                            7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                                                                                                                                                            7900 JAMES      CLERK           7698 03-12月-81            950                    30                                                                                                                                                           EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7902 FORD       ANALYST         7566 03-12月-81           3000                    20                                                                                                                                                            7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                                                                                                                                                      已选择11行。SQL> --4.查询入职时间在1981-5-1到1981-5-31之间的所有员工信息SQL> select * from emp where hiredate between '01-5月-81'and'31-5月-81';     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                                                                                                                                                      SQL> --5.查询所有名字长度为4的员工的员工编号SQL> select deptno , ename from emp where ename like '____';    DEPTNO ENAME                                                               ---------- ----------                                                                   30 WARD                                                                         10 KING                                                                         20 FORD                                                                 SQL> --6.显示10号部门的所有经理（“MANAGER”）和20号部门的所有职员（“CLERK”）详细SQL> select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK';     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7369 SMITH      CLERK           7902 17-12月-80            800                    20                                                                                                                                                            7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                                                                                                                                                            7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                                                                                                                                                      SQL> --7.显示姓名中没有L字的员工的详细信息或含有SM子的员工信息SQL> select * from emp where ename like 'is not L' or ename like '%SM%';     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7369 SMITH      CLERK           7902 17-12月-80            800                    20                                                                                                                                                      SQL> --8.显示各部门经理（“MANAGER”）的工资SQL> select sal from emp where job ='MANAGER';       SAL                                                                     ----------                                                                            2975                                                                            2850                                                                            2450                                                                      SQL> --9.显示佣金（‘COMM’）收入比工资（‘SAL’）高的员工的详细信息SQL> select * from emp where comm>sal;     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM---------- ---------- --------- ---------- -------------- ---------- ----------     DEPTNO                                                                      ----------                                                                            7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                                                                                                                                                      SQL> exit