To display the three earner names and salaries from the emp table:

SQL> select rownum as rank,ename,sal from (select ename,sal from
2 emp order by sal desc)
3 where rownum<=4;

RANK ENAME SAL
---------- ---------- ----------
1 KING 5000
2 SCOTT 3000
3 FORD 3000
4 JONES 2975
The subquery or the inline view includes the ORDER BY clause to ensure
that the ranking is in the desired order;For results retrieving the
largest values,a desc parameter is needed.
SQL> select rownum as senior,e.ename,e.hiredate from
2 (select ename,hiredate from emp order by hiredate) e
3 where rownum<=4;

SENIOR ENAME HIREDATE
---------- ---------- --------------
1 SMITH 17-12月-80
2 ALLEN 20-2月 -81
3 WARD 22-2月 -81
4 JONES 02-4月 -81
Sequence:generates primary key values,Automatically generates unique numbers,is a shared object,speed up the efficiency of access sequence values when cached in memory
SQL> create sequence dept_deptid_seq
2 increment by 10
3 start with 100
4 maxvalue 9999
5 nocache
6 nocycle;

序列已创建。

SQL> insert into dept(deptno,dname,loc) values(DEPT_DEPTID_SEQ.nextval,'SUPPORT','SHANG HAI');

已创建 1 行。

SQL> SELECT * FROM DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS
110 SUPPORT SHANG HAI

SQL> insert into dept(deptno,dname,loc) values(DEPT_DEPTID_SEQ.nextval,'MANAGER','BEI JING');

已创建 1 行。

SQL> SELECT * FROM DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS
110 SUPPORT SHANG HAI
120 MANAGER BEI JING

已选择6行。

Synonym: Alternative name for an object.
SQL> create public synonym employee for scott.emp;

同义词已创建。

SQL> select * from employee;
行将被截断

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
7521 WARD SALESMAN 7698 22-2月 -81 1250 500
7566 JONES MANAGER 7839 02-4月 -81 2975
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
7698 BLAKE MANAGER 7839 01-5月 -81 2850
7782 CLARK MANAGER 7839 09-6月 -81 2450
7788 SCOTT ANALYST 7566 19-4月 -87 3000
7839 KING PRESIDENT 17-11月-81 5000
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
7876 ADAMS CLERK 7788 23-5月 -87 1100

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950
7902 FORD ANALYST 7566 03-12月-81 3000
7934 MILLER CLERK 7782 23-1月 -82 1300
7400 ZHAOYU MANAGER 02-8月 -05 2410

已选择15行。
when not to create index
1.the table is small
2.the column are not often used as a condition in the query
3.Most queries are expected to retrieve more than 2 to 4% of the row in the table
4.The indexed columns are referenced as part of an expression

Function-Based indexes(function-based index is an index based on expressions):

SQL> create index upper_emp_sal_idx on
2 emp(to_number(to_char(sal)));

索引已创建。