1.sql plus 内部命令
SQL> select * from scott.emp
2 where sal>2000
3 order by ename;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7902 FORD ANALYST 7566 03-DEC-81 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
6 rows selected.
SQL> l 【列出上一条命令】
1 select * from scott.emp
2 where sal>2000
3*order by ename
SQL> l 2 【列出上一条命令的第2行（并将其改变为当前行）】
2*where sal>2000
SQL> l 1 2 【列出上一条命令的第1到第2行】
1 select * from scott.emp
2*where sal>2000
SQL> i order by sal 【在当前行后增加一行】
SQL> del 4 【删除指定行】
SQL> l
1 select * from scott.emp
2 where sal>2000
3*order by sal
SQL> c/by sal/by abc 【修改当前行中指定字串】
3*order by abc
SQL> c/by abc 【删除当前行指定字串】
3*order
SQL> a by sal 【在当前行尾添加指定字串 （两个空格）】
3*order by sal
SQL> save aaa; 【将当前命令存盘为aaa.sql】
SQL> get aaa; 【读入aaa.sql】
1 select * from scott.emp
2 where sal>2000
3 order by sal
4 /
5 select * from scott.emp
6 where sal>2000
7 order by sal
8 /
9 select * from scott.emp
10 where sal>2000
11 order by sal
12 /
13 select * from scott.emp
SQL> @aaa 【不读入而直接调用执行aaa.sql】
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30l/
SQL> spool bbb;【将以下 所有内容全部保存到文件bbb.lst中直至遇到spool off】
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ----------------------------- ---------- ---------- ----------
33jkl
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SQL> set term off[【内容中输出到文件而不输出到屏幕以提高速度】
SQL> @aaa
SQL> spool;【查看spool变量现在的状态】
currently spooling to bbb.lst
SQL> spool off;【关闭spool输出】
SQL> spool;
not spooling currently
SQL> desc scott.emp;【查看指定表结构】
SQL> show user【查看当前用户名】
USER is "SYS"
SQL> clear scr;【清屏】
SQL> host vi a.sql【不退出SQL执行操作系统命令】
SQL> set pause on【设置分屏显示结果】
SQL> set pagesize 30【设置分屏显示结果的行数】
SQL> set linesize 200【设置一行显示的宽度】
SQL> set numformat 999999999【设置所有数字类型列输出宽度】
SQL> set numformat 00000
SQL> set numformat "" 【恢复原有设置】
SQL>column empno format 99999 【修改单个数字列的宽度】
SQL>col job for a15 heading '工作职位' justify center;【修改标题居中及宽度】
SQL> select * from scott.emp;
EMPNO ENAME 工作职位 MGR HIREDATE SAL COMM DEPTNO
------ ---------- ------------------------- ------------------- ---------- ---------- ----------
33 jkl
7369 SMITH CLERK 79021980-12-17 00:00:00 800 20
SQL>col mgr just right null '无' 【设置空值替换】
SQL> select * from scott.emp;
EMPNO ENAME 工作职位 MGR HIREDATE SAL COMM DEPTNO
------ ---------- ------------------------- ------------------- ---------- ---------- ----------
33 jkl 无
7369 SMITH CLERK 79021980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 76981981-02-20 00:00:00 1600 300 30
SQL>col sal for $9999.99 【设置数字输出格式】
SQL> select * from scott.emp;
EMPNO ENAME 工作职位 会话 HIREDATE SAL COMM DEPTNO
------ ---------- ------------------------- ------------------- --------- ---------- ----------
33 jkl 无
7369 SMITH CLERK 79021980-12-17 00:00:00 $800.00 20
7499 ALLEN SALESMAN 76981981-02-20 00:00:00 $1600.00 300 30
SQL>col empno【查询指定列设置】
COLUMN empno ON
FORMAT 99999
SQL>col 【查询所有列设置】
COLUMN sal ON
FORMAT $9999.99
COLUMN mgr ON
HEADING '会话'
NULL '无'
JUSTIFY right
COLUMN mgt ON
NULL '无'
JUSTIFY right
COLUMN job ON
HEADING '工作职位'
FORMAT a15
JUSTIFY center
COLUMN empno ON
FORMAT 99999
COLUMN result_plus_xquery ON
HEADING 'Result Sequence'
COLUMN other_plus_exp ON
FORMAT a44
COLUMN other_tag_plus_exp ON
FORMAT a29
COLUMN object_node_plus_exp ON
FORMAT a8
COLUMN plan_plus_exp ON
FORMAT a60
COLUMN parent_id_plus_exp ON
HEADING 'p'
FORMAT 990
COLUMN id_plus_exp ON
HEADING 'i'
FORMAT 990
COLUMN droptime_plus_show_recyc ON
HEADING 'DROP TIME'
FORMAT a19
COLUMN objtype_plus_show_recyc ON
HEADING 'OBJECT TYPE'
FORMAT a12
COLUMN objectname_plus_show_recyc ON
HEADING 'RECYCLEBIN NAME'
FORMAT a30
COLUMN origname_plus_show_recyc ON
HEADING 'ORIGINAL NAME'
FORMAT a16
COLUMN value_col_plus_show_param ON
HEADING 'VALUE'
FORMAT a30
COLUMN name_col_plus_show_param ON
HEADING 'NAME'
FORMAT a36
COLUMN units_col_plus_show_sga ON
FORMAT a15
COLUMN name_col_plus_show_sga ON
FORMAT a24
COLUMN ERROR ON
FORMAT A65
word_wrap
COLUMN LINE/COL ON
FORMAT A8
COLUMN ROWLABEL ON
FORMAT A15
SQL>show all 【查询所有参数设置】
appinfo is OFF and set to"SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
SQL>col empno clear;【清除指定列设置】
SQL>col empno;
SP2-0046: COLUMN 'empno' not defined
SQL>clear columns【清除所有列设置】
columns cleared
SQL> show all;
appinfo is OFF a
SQL> col
SP2-0045: * no COLUMN defined
SQL(Structured query language)
数据定义DDL（Data definition language）
Create,alter,drop,truncate,grant,revoke,…….
数据操作DML（data manipulation language）
Select,insert,update,delete,locktable,…….
数据控制DCL（data control language）
Commit,savepoint,rollback,…….
数据查询
Selection(to select rows)
修改数据
SQL> update emp set deptno=99 where empno=33;
1 row updated.
SQL> select * from emp;
EMPNO ENAME DEPTNO EMP_ID EMP_NUM
---------- ---------- ---------- --------------------
33jkl 99
7911 KITTY 10
7499 ALLEN 30
SQL> update emp set emp_id=&1 whereename=&2;
Enter value for 1: 1
Enter value for 2: 'jkl'
old 1: update emp set emp_id=&1 where ename=&2
new 1: update emp set emp_id=1 where ename='jkl'
1 row updated.
SQL> select * from emp;
EMPNO ENAME DEPTNO EMP_ID EMP_NUM
---------- ---------- ---------- --------------------
33jkl 99 1
7911 KITTY 10
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
SQL> select deptno,decode (deptno,10,'会计',20,'yanjiu') from scott.dept;
DEPTNO DECODE
---------- ------
10会计
20yanjiu
30
40
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10ACCOUNTING NEW YORK
20RESEARCH DALLAS
30SALES CHICAGO
40OPERATIONS BOSTON
扫一扫
555
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
