1.列标题和数据的默认显示格式
SQL> alter session
2 set nls_date_language = 'AMERICAN';
Session altered
SQL> col hiredate for a15;
SQL> col ename for a8;
例:观察不同数据类型的显示方式。
SQL> select *
2 from emp;
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 1982-12-9 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 1983-1-12 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
1234 ASK MANAGER 1 2009-8-31 10.00 10.00 10
15 rows selected
2.连接操作符
可使用连接符“||”,用于列与列、列与算术表达式或列与常理之间创建一个字符表达式。
任务:将姓名和工作连接作为整体输出
SQL> select ename || job as "Employee"
2 from emp;
Employee
-------------------
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN
ADAMSCLERK
JAMESCLERK
FORDANALYST
MILLERCLERK
ASKMANAGER
15 rows selected
3.原义字符串
任务:使用原义字符串"is a"连接ename和job,可增强输出的可读性。
SQL> select ename || ' is a ' || job
2 as "Employee Details"
3 from emp;
Employee Details
-------------------------
SMITH is a CLERK
ALLEN is a SALESMAN
WARD is a SALESMAN
JONES is a MANAGER
MARTIN is a SALESMAN
BLAKE is a MANAGER
CLARK is a MANAGER
SCOTT is a ANALYST
KING is a PRESIDENT
TURNER is a SALESMAN
ADAMS is a CLERK
JAMES is a CLERK
FORD is a ANALYST
MILLER is a CLERK
ASK is a MANAGER
15 rows selected
4.显示表结构
可以使用describe命令显示一个表的结构。(列名、数据类型以及是否为空)
SQL> describe dept
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
本文作者:网友 来源: http://www.cnblogs.com/benio/archive/2009/10/28/1591425.html
CIO之家 www.ciozj.com 微信公众号:imciow