文章目录
- 1.desc指令使用
- 2.column指令
- 1）FOR[MAT] format
- 2）CLE(AR)
- 3）HEA[DING] text
- 4）JUS[TIFY]{L[EFT]|C[ENTER]|R[IGHT]}
- 5）NEWL(INE)
- 6）NOPRI[NT]|PRI[NT]
- 7）NUL[L] text
- 8）ON|OFF
- 3.run或“/”指令
- 4.L(ist)指令和n指令
- 5.change指令和n(next)指令
- 6.附加（a）指令
- 7.del指令
- 8.set line指令
- 9.spool指令
- 10.脚本文件
1.desc指令使用
SQL> alter user scott account unlock;
用户已更改。 #我用tiger密码无法登陆，所以需要修改密码
SQL> alter user scott identified by Password;
用户已更改。
SQL> conn scott/Password #connect username/password@sid 使用默认数据库可不用服务名
已连接。
SQL> desc dept;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
2.column指令
1）FOR[MAT] format
1.1）格式化模式‘9’ (number)
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
#由于3列数据类型均为NUMBER所以使用“9”，99是格式化模式，每个“9”表示一位数字
SQL> col grade for 99 #表示占用两位数字的宽度
SQL> col losal for 9999 #表示占用四位数字的宽度
SQL> col hisal for 9999
SQL> select * from salgrade;
GRADE LOSAL HISAL
----- ----- -----
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> col hisal for 9999.99 #表示带小数点
SQL> select * from sAlgrade;
GRADE LOSAL HISAL
----- ----- --------
1 700 1200.00
2 1201 1400.00
3 1401 2000.00
4 2001 3000.00
5 3001 9999.00
1.2）格式化模式‘a’ (字符)
SQL> col object_name for a20 #格式化为20个字符宽度
SQL> select object_name,object_type from user_objects
2 where object_type = 'TABLE';
OBJECT_NAME OBJECT_TYPE
-------------------- -------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
1.3）格式化模式‘$’ (美元)
SQL> col losal for $9999
SQL> col hisal for $9999
SQL> select * from salgrade;
GRADE LOSAL HISAL
----- ------ ------
1 $700 $1200
2 $1201 $1400
3 $1401 $2000
4 $2001 $3000
5 $3001 $9999
1.4）格式化模式‘L’ (人名币)
SQL> col losal for L9999
SQL> col hisal for L9999
SQL> select * from salgrade;
GRADE LOSAL HISAL
----- --------------- ---------------
1 ￥700 ￥1200
2 ￥1201 ￥1400
3 ￥1401 ￥2000
4 ￥2001 ￥3000
5 ￥3001 ￥9999
查看当前数据库支持的字符集
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
ZHS16GBK<语言><比特位><编码>
1.5）查看列显示格式
SQL> col losal
COLUMN losal ON
FORMAT L9999
SQL> col hisal
COLUMN hisal ON
FORMAT L9999
2）CLE(AR)
删除格式化设置
SQL> col losal clear
SQL> col hisal clear
SQL> col losal
SP2-0046: COLUMN 'losal' 未定义
SQL> col hisal
SP2-0046: COLUMN 'hisal' 未定义
3）HEA[DING] text
格式化列属性名称
SQL> col losal heading '低工资'
SQL> col hisal heading '高工资'
SQL> select * from salgrade;
GRADE 低工资 高工资
----- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
4）JUS[TIFY]{L[EFT]|C[ENTER]|R[IGHT]}
JUS[TIFY] 靠左 居中 偏右
奇怪的是对我没用
SQL> col dname jus center
SQL> col loc jus center
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
5）NEWL(INE)
使所有列的显示另起一行 用的少
SQL> col dname newline
SQL> select * from dept;
DEPTNO
----------
DNAME LOC
-------------- -------------
10
ACCOUNTING NEW YORK
6）NOPRI[NT]|PRI[NT]
使得格式化的数据不显示或显示
SQL> col loc noprint
SQL> col dname clear
SP2-0046: COLUMN 'dname' 未定义
SQL> select * from dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
7）NUL[L] text
SQL> insert into dept (deptno,dname,loc) values (50,'Marcketing','');
已创建 1 行。
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 Marcketing
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> col loc null 'TEMP';
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 Marcketing TEMP #填充数据只是显示，不会修改实际数据
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
8）ON|OFF
OFF后，之前的格式化操作将取消，之后格式化修改将无效
ex: SQL> col loc off
3.run或“/”指令
run或“/”指令可重复执行SQL缓冲区的语句，run会显示sql语句反馈，“/”不会
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 Marcketing TEMP
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> run
1* select * from dept
DEPTNO DNAME LOC
---------- -------------- -------------
50 Marcketing TEMP
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
50 Marcketing TEMP
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4.L(ist)指令和n指令
L(ist)指令列出当前SQL缓冲区的SQL命令
SQL> select empno,ename,job,mgr,hiredate,sal
2 from emp
3 where job ='MANAGER';
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- -------------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975
7698 BLAKE MANAGER 7839 01-5月 -81 2850
7782 CLARK MANAGER 7839 09-6月 -81 2450
SQL> list
1 select empno,ename,job,mgr,hiredate,sal
2 from emp
3* where job ='MANAGER'
SQL> 1 #定位到第一行
1* select empno,ename,job,mgr,hiredate,sal
5.change指令和n(next)指令
1) change
前面已经定位到第一行，使用change命令将列sal改为deptno
SQL> ch /sal/deptno
1* select empno,ename,job,mgr,hiredate,deptno
SQL> /
EMPNO ENAME JOB MGR HIREDATE DEPTNO
---------- ---------- --------- ---------- -------------- ----------
7566 JONES MANAGER 7839 02-4月 -81 20
7698 BLAKE MANAGER 7839 01-5月 -81 30
7782 CLARK MANAGER 7839 09-6月 -81 10
2) n
n为SQL语句的行号，随后输入替换的行
SQL> list
1 select empno,ename,job,mgr,hiredate,deptno
2 from emp
3* where job ='MANAGER'
SQL> 1 select empno,ename,job,mgr
SQL> list
1 select empno,ename,job,mgr
2 from emp
3* where job ='MANAGER'
6.附加（a）指令
在行末尾添加一些语句或属性信息
SQL> list
1 select empno,ename,job,mgr
2 from emp
3* where job ='MANAGER'
SQL> 1
1* select empno,ename,job,mgr
SQL> a,deptno #将,loc添加到第一行的SQL末尾
1* select empno,ename,job,mgr,deptno
SQL> list
1 select empno,ename,job,mgr,deptno
2 from emp
3* where job ='MANAGER'
7.del指令
语法格式： del n (n为行号)
SQL> list
1 select empno,ename,job,mgr,deptno
2 from emp
3* where job ='MANAGER'
SQL> del 3
SQL> list
1 select empno,ename,job,mgr,deptno
2* from emp
8.set line指令
格式：set line {80/n}
将查询的数据输出设置为n个字符宽度显示，默认80个字符宽度输出
SQL> list
1 select empno,ename,job,mgr,deptno
2* from emp
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
SQL> set line 100#设置显示行的长度为100个字符
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
9.spool指令
将用户输入的sql语句和查询结果存储到指定的文件中
SQL> show spool
spool OFF
SQL> spool d:\spool_test
SQL> show spool
spool ON
SQL> select empno,ename,job,mgr,sal
2 from emp
3 where job = 'MANAGER';
EMPNO ENAME JOB MGR SAL
---------- ---------- --------- ---------- ----------
7566 JONES MANAGER 7839 2975
7698 BLAKE MANAGER 7839 2850
7782 CLARK MANAGER 7839 2450
SQL> spool off
执行完后在D盘生成了spool_test.lst文件
10.脚本文件
1）创建脚本文件
SQL> select empno,ename,job,mgr,hiredate,sal
2 from emp
3 where job = 'MANAGER'
4 order by sal;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- -------------- ----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
7698 BLAKE MANAGER 7839 01-5月 -81 2850
7566 JONES MANAGER 7839 02-4月 -81 2975
#save命令创建
SQL> save d:\SELECT_emp
已创建 file d:\SELECT_emp.sql
可在D盘找到该文件
2）运行脚本文件
SQL> @d:\SELECT_emp
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- -------------- ----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
7698 BLAKE MANAGER 7839 01-5月 -81 2850
7566 JONES MANAGER 7839 02-4月 -81 2975
SQL> start d:\SELECT_tmp
SP2-0310: 无法打开文件 "d:\SELECT_tmp.sql"
SQL> start d:\SELECT_emp
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- -------------- ----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
7698 BLAKE MANAGER 7839 01-5月 -81 2850
7566 JONES MANAGER 7839 02-4月 -81 2975
3)sqlplus下编辑脚本文件
3.1)get装载到缓冲区后修改
SQL> get d:\SELECT_emp
1 select empno,ename,job,mgr,hiredate,sal
2 from emp
3 where job = 'MANAGER'
4* order by sal
SQL> 1
1* select empno,ename,job,mgr,hiredate,sal
SQL> change \sal\depno
1* select empno,ename,job,mgr,hiredate,depno
SQL> list #实际脚本文件未修改
1 select empno,ename,job,mgr,hiredate,depno
2 from emp
3 where job = 'MANAGER'
4* order by sal