1.打开运行命令窗口：输入 cmd
显示如下：
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>
2.输入 sqlplus system/orcl as sysdba
以sysdba身份登录oracle数据库
显示如下：
C:\Documents and Settings\Administrator>sqlplus system/orcl as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 8月 20 19:56:12 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
3.以soctt身份登录：输入 conn scott/thger
显示如下：
SQL> conn scott/thger
已连接。
SQL>
4.查询表 ： select * from dept;
显示如下：
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
5.查询列的原来的长度
select empno,ename,job from emp;
显示如下：
SQL> select empno,ename,job from emp;
EMPNO ENAME JOB
---------- -------------------- ------------------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
EMPNO ENAME JOB
---------- -------------------- ------------------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
已选择14行。
SQL>
6.改变长度：
col ename format a40;
查询改变后的列的长度：
select empno,ename,job from emp;
显示如下：
SQL> col ename format a40;
SQL> select empno,ename,job from emp;
EMPNO ENAME JOB
---------- ---------------------------------------- ------------------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
EMPNO ENAME JOB
---------- ---------------------------------------- ------------------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
已选择14行。
SQL>
7.设置列的对齐方式
col ename justify center;
查询： select empno,ename,job from emp;
显示如下：
SQL> col ename justify center;
SQL> select empno,ename,job from emp;
EMPNO ENAME JOB
---------- ---------------------------------------- ------------------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
EMPNO ENAME JOB
---------- ---------------------------------------- ------------------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
已选择14行。
SQL>
注意：对于number类型的数据默认为右对齐，其他默认为左对齐
8.控制显示：
col job noprint
查询：
select empno,ename,job from emp;
显示如下：
SQL> col job noprint
SQL> select empno,ename,job from emp;
EMPNO ENAME
---------- ----------------------------------------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
EMPNO ENAME
---------- ----------------------------------------
7900 JAMES
7902 FORD
7934 MILLER
已选择14行。
SQL>
9.格式化number类型列的显示：
column sal format $999,999.00
查询： select empno ,ename,sal from emp;
显示如下：
SQL> column sal format $999,999.00
SQL> select empno ,ename,sal from emp;
EMPNO ENAME SAL
---------- ---------------------------------------- ------------
7369 SMITH $800.00
7499 ALLEN $1,600.00
7521 WARD $1,250.00
7566 JONES $2,975.00
7654 MARTIN $1,250.00
7698 BLAKE $2,850.00
7782 CLARK $2,450.00
7788 SCOTT $3,000.00
7839 KING $5,000.00
7844 TURNER $1,500.00
7876 ADAMS $1,100.00
EMPNO ENAME SAL
---------- ---------------------------------------- ------------
7900 JAMES $950.00
7902 FORD $3,000.00
7934 MILLER $1,300.00
已选择14行。
SQL>
10.设置列值，若列植为空以text 代替
col comm null text
查询： select * from emp;
显示如下：
SQL> col comm null text
SQL> select * from emp;
EMPNO ENAME MGR HIREDATE
---------- ---------------------------------------- ---------- --------------
SAL COMM DEPTNO
------------ ---------- ----------
7369 SMITH 7902 17-12月-80
$800.00 text 20
7499 ALLEN 7698 20-2月 -81
$1,600.00 300 30
7521 WARD 7698 22-2月 -81
$1,250.00 500 30
EMPNO ENAME MGR HIREDATE
---------- ---------------------------------------- ---------- --------------
SAL COMM DEPTNO
------------ ---------- ----------
7566 JONES 7839 02-4月 -81
$2,975.00 text 20
7654 MARTIN 7698 28-9月 -81
$1,250.00 1400 30
7698 BLAKE 7839 01-5月 -81
$2,850.00 text 30
EMPNO ENAME MGR HIREDATE
---------- ---------------------------------------- ---------- --------------
SAL COMM DEPTNO
------------ ---------- ----------
7782 CLARK 7839 09-6月 -81
$2,450.00 text 10
7788 SCOTT 7566 19-4月 -87
$3,000.00 text 20
7839 KING 17-11月-81
$5,000.00 text 10
EMPNO ENAME MGR HIREDATE
---------- ---------------------------------------- ---------- --------------
SAL COMM DEPTNO
------------ ---------- ----------
7844 TURNER 7698 08-9月 -81
$1,500.00 0 30
7876 ADAMS 7788 23-5月 -87
$1,100.00 text 20
7900 JAMES 7698 03-12月-81
$950.00 text 30
EMPNO ENAME MGR HIREDATE
---------- ---------------------------------------- ---------- --------------
SAL COMM DEPTNO
------------ ---------- ----------
7902 FORD 7566 03-12月-81
$3,000.00 text 20
7934 MILLER 7782 23-1月 -82
$1,300.00 text 10
已选择14行。
SQL>
11.显示列的当前属性
column ename;
显示如下：
SQL> column ename;
COLUMN ename ON
FORMAT a40
JUSTIFY center
SQL>
12.重置为默认值：
clear columns;
重新查询：
select * from emp;
显示结果如下：
SQL> clear columns;
columns 已清除
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>
