SQL> l
1 select deptno, ename, job, mgr, hiredate
2 from emp
3* order by 1
SQL 与SQL*Plus的区别
(1)定义
SQL(Structured Query Language)结构化查询语言，是所有关系型数据库通用的一种语言。
SQL*Plus是oracle的一种专门的工具，它是用户和oracle数据库的接口工具，它不是用户和oracle数据库交互的唯一工具。
(2)命令
SQL命令举例
select * from emp;
delete from emp where job = 'manager';
insert into emp(empno, ename, job) values(9999, 'William Wycherley', 'Sales Manager');
commit;
rollback; #this is the opposite of 'commit'
SQL*PLUS命令举例
set linesize 120
set pagesize 60
col ename format a15
col sal format $999,999.00
set pause on
set pause off
connect
ed
save
spool
help index
(3)缓存的区别
SQL*PLUS 不会保存buffer，当你退出SQL*PLUS，重新登陆时，它会忘记你之前输入的命令。
SQL> /
DEPTNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- ---------
10 CLARK MANAGER 7839 09-JUN-81
10 KING PRESIDENT 17-NOV-81
10 MILLER CLERK 7782 23-JAN-82
20 JONES MANAGER 7839 02-APR-81
20 FORD ANALYST 7566 03-DEC-81
20 ADAMS CLERK 7788 23-MAY-87
20 SMITH CLERK 7902 17-DEC-80
20 SCOTT ANALYST 7566 19-APR-87
30 WARD SALESMAN 7698 22-FEB-81
30 TURNER SALESMAN 7698 08-SEP-81
30 ALLEN SALESMAN 7698 20-FEB-81
30 JAMES CLERK 7698 03-DEC-81
30 BLAKE MANAGER 7839 01-MAY-81
30 MARTIN SALESMAN 7698 28-SEP-81
14 rows selected.
SQL> alter session set nls_date_format ='mm/dd/yyyy';
Session altered.
SQL> /
Session altered.
SQL> select deptno, ename, job, mgr, hiredate
2 from emp
3 order by 2;
DEPTNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- ----------
20 ADAMS CLERK 7788 05/23/1987
30 BLAKE MANAGER 7839 05/01/1981
10 CLARK MANAGER 7839 06/09/1981
20 FORD ANALYST 7566 12/03/1981
30 JAMES CLERK 7698 12/03/1981
20 JONES MANAGER 7839 04/02/1981
10 KING PRESIDENT 11/17/1981
30 MARTIN SALESMAN 7698 09/28/1981
10 MILLER CLERK 7782 01/23/1982
20 SCOTT ANALYST 7566 04/19/1987
20 SMITH CLERK 7902 12/17/1980
30 TURNER SALESMAN 7698 09/08/1981
30 WARD SALESMAN 7698 02/22/1981
14 rows selected.
修改sal列的数字显示格式。
SQL> select deptno, ename, job, mgr, hiredate, sal
2 from emp
3 order by 1;
DEPTNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ---------- ----------
10 CLARK MANAGER 7839 06/09/1981 2450
10 KING PRESIDENT 11/17/1981 5000
10 MILLER CLERK 7782 01/23/1982 1300
20 JONES MANAGER 7839 04/02/1981 2975
20 FORD ANALYST 7566 12/03/1981 3000
20 ADAMS CLERK 7788 05/23/1987 1100
20 SMITH CLERK 7902 12/17/1980 800
20 SCOTT ANALYST 7566 04/19/1987 3000
30 WARD SALESMAN 7698 02/22/1981 1250
30 TURNER SALESMAN 7698 09/08/1981 1500
30 ALLEN SALESMAN 7698 02/20/1981 1600
30 JAMES CLERK 7698 12/03/1981 950
30 BLAKE MANAGER 7839 05/01/1981 2850
30 MARTIN SALESMAN 7698 09/28/1981 1250
14 rows selected.
SQL> col sal format $999,999.00
SQL> /
DEPTNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ---------- ------------
10 CLARK MANAGER 7839 06/09/1981 $2,450.00
10 KING PRESIDENT 11/17/1981 $5,000.00
10 MILLER CLERK 7782 01/23/1982 $1,300.00
20 JONES MANAGER 7839 04/02/1981 $2,975.00
20 FORD ANALYST 7566 12/03/1981 $3,000.00
20 ADAMS CLERK 7788 05/23/1987 $1,100.00
20 SMITH CLERK 7902 12/17/1980 $800.00
20 SCOTT ANALYST 7566 04/19/1987 $3,000.00
30 WARD SALESMAN 7698 02/22/1981 $1,250.00
30 TURNER SALESMAN 7698 09/08/1981 $1,500.00
30 ALLEN SALESMAN 7698 02/20/1981 $1,600.00
30 JAMES CLERK 7698 12/03/1981 $950.00
30 BLAKE MANAGER 7839 05/01/1981 $2,850.00
30 MARTIN SALESMAN 7698 09/28/1981 $1,250.00
14 rows selected.
修改SAL列的列名。
SQL> col sal heading salary;
SQL> /
DEPTNO ENAME JOB MGR HIREDATE salary
-------