Oracle数据库之过滤和排序

发布时间：2020-12-12 15:29:49 所属栏目：百科来源：网络整理

导读：oracle安装参照： Oracle数据库之安装教程 Oracle数据库总结： Oracle数据库之基本查询 Oracle数据库之单行函数 Oracle数据库之多行函数 Oracle数据库之多表查询 SQL –查询10号部门的员工 SQL select * 2 from emp 3 where deptno=10; EMPNO ENAME JOB MGR

oracle安装参照： Oracle数据库之安装教程

Oracle数据库总结：

Oracle数据库之基本查询
Oracle数据库之单行函数
Oracle数据库之多行函数
Oracle数据库之多表查询

SQL> –查询10号部门的员工
SQL> select *
2 from emp
3 where deptno=10;

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10

***SQL> –字符串大小写敏感
SQL> –查询名叫KING的员工*
SQL> select *
2 from emp
3 where ename=’KING’;

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10

***SQL> –日期格式敏感
SQL> –查询入职日期是17-11月-81的员工信息*
SQL> select *
2 from emp
3 where hiredate=’17-11月-81’;

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10

SQL> –改日期格式
SQL> select * from v$nls_parameters;

PARAMETER                                                        VALUE                                                                                
---------------------------------------------------------------- ----------------------------------------------------------------                     
NLS_LANGUAGE                                                     SIMPLIFIED CHINESE                                                                   
NLS_TERRITORY                                                    CHINA                                                                                
NLS_CURRENCY                                                     ￥                                                                                   
NLS_ISO_CURRENCY                                                 CHINA                                                                                
NLS_NUMERIC_CHARACTERS                                           .,NLS_CALENDAR                                                     GREGORIAN                                                                            
NLS_DATE_FORMAT                                                  DD-MON-RR                                                                            
NLS_DATE_LANGUAGE                                                SIMPLIFIED CHINESE                                                                   
NLS_CHARACTERSET                                                 ZHS16GBK                                                                             
NLS_SORT                                                         BINARY                                                                               
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM                                                                       

PARAMETER                                                        VALUE                                                                                
---------------------------------------------------------------- ----------------------------------------------------------------                     
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM                                                             
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR                                                                   
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR                                                         
NLS_DUAL_CURRENCY                                                ￥                                                                                   
NLS_NCHAR_CHARACTERSET                                           AL16UTF16                                                                            
NLS_COMP                                                         BINARY                                                                               
NLS_LENGTH_SEMANTICS                                             BYTE                                                                                 
NLS_NCHAR_CONV_EXCP                                              FALSE

SQL> alter session set NLS_DATE_FORMAT=’yyyy-mm-dd’;

会话已更改。

*SQL> select
2 from emp
3 where hiredate=’1981-11-17’;**

EMPNO ENAME    JOB              MGR HIREDATE     SAL       COMM     DEPTNO                                                                       
---------- -------- --------- ---------- ---------- ----- ---------- ----------                                                                       
      7839 KING     PRESIDENT            1981-11-17  5000                    10

SQL> alter session set NLS_DATE_FORMAT=’DD-MON-RR’;

会话已更改。

***SQL> –between… and
SQL> –查询薪水1000~2000之间的员工*
SQL> select *
2 from emp
3 where sal between 1000 and 2000;

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10

***SQL> –in 在集合中
SQL> –查询部门号是10和20的员工*
SQL> select *
2 from emp
3 where deptno in (10,20);

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                                                                   
      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                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10

***SQL> –模糊查询
SQL> –查询名字以S打头的员工*
SQL> select *
2 from emp
3 where ename like ‘S%’;

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20

SQL> –查询名字是4个字的员工
1 select *
2 from emp
3* where ename like ‘__’
SQL> /

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20

SQL> – 插入记录
SQL> insert into emp(empno,ename,sal,deptno) values(1001,’Tom_AB’,3000,10);

已创建 1 行。

SQL> select * from emp;

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      1001 Tom_AB                                        3000                    10                                                                   
      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                                                                   
      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                                                                   
      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                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10

SQL> –查询名字中含有下划线的员工
SQL> select *
2 from emp
3 where ename like ‘%_%’;

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      1001 Tom_AB                                        3000                    10                                                                   
      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                                                                   
      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                                                                   
      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                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10

SQL> –转义字符
1 select *
2 from emp
3* where ename like ‘%_%’ escape ‘’
SQL> /

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      1001 Tom_AB                                        3000                    10

SQL> –查询员工信息，按照月薪排序
SQL> select *
2 from emp
3 order by sal;

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10

SQL> –order by 后面 + 列，表达式，别名，序号
SQL> select ename,sal*12
2 from emp
3 order by sal*12 desc;

ENAME      SAL     SAL*12                                                                                                                             
-------- ----- ----------                                                                                                                             
KING      5000      60000                                                                                                                             
FORD      3000      36000                                                                                                                             
SCOTT     3000      36000                                                                                                                             
JONES     2975      35700                                                                                                                             
BLAKE     2850      34200                                                                                                                             
CLARK     2450      29400                                                                                                                             
ALLEN     1600      19200                                                                                                                             
TURNER    1500      18000                                                                                                                             
MILLER    1300      15600                                                                                                                             
WARD      1250      15000                                                                                                                             
MARTIN    1250      15000                                                                                                                             

ENAME      SAL     SAL*12                                                                                                                             
-------- ----- ----------                                                                                                                             
ADAMS     1100      13200                                                                                                                             
JAMES      950      11400                                                                                                                             
SMITH      800       9600

SQL> –多个列排序
SQL> select *
2 from emp
3 order by deptno,sal;

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30

SQL> –order by 作用于后面所有的列，desc只作用于离他最近的一列

SQL> –查询员工信息，按照奖金排序
SQL> select *
2 from emp
3 order by comm;

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10

SQL> –a命令 append
SQL> a desc
3* order by comm desc
SQL> /

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                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30

Oracle数据库总结：

Oracle数据库之集合运算
Oracle数据库之数据处理
Oracle数据库之建表和管理表
Oracle数据库之对象视图、索引、序列、同义词

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!