oracle sql记录,oracle实验记录 （SQL*PLUS 命令操作）

SQL> help index

Enter Help [topic] for help.

@             COPY         PAUSE                    SHUTDOWN

@@            DEFINE       PRINT                    SPOOL

/             DEL          PROMPT                   SQLPLUS

ACCEPT        DESCRIBE     QUIT                     START

APPEND        DISCONNECT   RECOVER                  STARTUP

ARCHIVE LOG   EDIT         REMARK                   STORE

ATTRIBUTE     EXECUTE      REPFOOTER                TIMING

BREAK         EXIT         REPHEADER                TTITLE

BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE

CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE

CLEAR         HOST         RUN                      WHENEVER OSERROR

COLUMN        INPUT        SAVE                     WHENEVER SQLERROR

COMPUTE       LIST         SET

CONNECT       PASSWORD     SHOW

查看所有命令

SQL> ? Change~~~~~~~~~~~~~查看具体使用

CHANGE

------

Changes the first occurrence of the specified text on the current

line of the SQL buffer. The buffer has no command history list and

does not record SQL*Plus commands.

C[HANGE] sepchar old [sepchar [new[sepchar]]]

Not available in iSQL*Plus

常用的

SQL> select

2  *

3

~~不支持空格

SQL> set sqlblanklines on~~~~~~~~~~~~~~用这个支持了

SQL> select

2  *

3

4

5  from test;

A

----------

1

LIST:查看已写命令 可以简写成L

SQL> l

1  select

2  *

3

4

5* from test

change :修改

SQL> select * fro test;

select * fro test

*

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

SQL> l

1* select * fro test

SQL> c/fro/from

1* select * from test

SQL> l

1* select * from test

SQL> /

A

----------

1

del:删除

SQL> l

1* select * from test

SQL> del

SQL> l

SP2-0223: No lines in SQL buffer.

SQL> set sqlblanklines on

SQL> select

2  *

3

4  from test;

A

----------

1

SQL> ;

1  select

2  *

3

4* from test

SQL> del 3~~~~~~~~~~~~~~~~~~~~删除第几行

SQL> l

1  select

2  *

3* from test

SQL> /

A

----------

1

APPEND 追加

SQL> select * from tes;

select * from tes

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> l

1* select * from tes

SQL> append t

1* select * from test

SQL> l

1* select * from test

SQL> /

A

----------

1

SAVE:保存buffer               geT:显示不执行

edit:编辑

col对列修改

SQL> ? col

COLUMN

------

Specifies display attributes for a given column, such as:

- text for the column heading

- alignment for the column heading

- format for NUMBER data

- wrapping of column data

Also lists the current display attributes for a single column

or all columns.

COL[UMN] [{column | expr} [option ...] ]

where option represents one of the following clauses:

ALI[AS] alias

CLE[AR]

ENTMAP {ON|OFF}

FOLD_A[FTER]

FOLD_B[EFORE]

FOR[MAT] format

HEA[DING] text

JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}

LIKE {expr | alias}

NEWL[INE]

NEW_V[ALUE] variable

NOPRI[NT] | PRI[NT]

NUL[L] text

OLD_V[ALUE] variable

ON|OFF

WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]

SQL> col  a heading "b"

SQL> select * from test;

b

----------

1

SQL> col a format a5 heading "c"

SQL> select * from test;

c

----------

##########

报表的一些命令

SQL> select * from book;

ID BOOKNAME   PUB

---------- ---------- ----------

1 a          a

2 b          b

3 c          c

4 c          c

SQL> break on pub(该列重复的只显示1条)分类

SQL> select * from book;

ID BOOKNAME   PUB

---------- ---------- ----------

1 a          a

2 b          b

3 c          c

4 c

compute count of bookname on pub 计算bookname数 按pub分类

SQL> compute count of bookname on pub

SQL> select * from book;

ID BOOKNAME   PUB

---------- ---------- ----------

1 a          a

---------- **********

1 count

2 b          b

---------- **********

1 count

3 c          c

4 c

---------- **********

2 count

*********

SQL> passw xh~~~~~~~~~~~~~~~~~~SQLPLUS中改user密码

Changing password for xh

New password:

Retype new password:

Password changed

SQL>

SQL> passw zz

Changing password for zz

New password:

Retype new password:

Password changed

SQL> show user

USER is "XH"~~~~~~~~~~~改自己的

SQL> passw

Changing password for XH

Old password:

~~~~~~~~~~~~~~~~~~

SQL> define a=1~~~~~~~~~定义变量

SQL> select * from t1 where a=&a;

old   1: select * from t1 where a=&a

new   1: select * from t1 where a=1

no rows selected

SQL> define a

DEFINE A               = "1" (CHAR)~查看定义的

SQL>

SQL> accept b prompt 'input'~~~~定义变量提示输入变量value,另外还可以有format 和hide隐藏输出

input2~~~~~~~~~~~~属于变量value

SQL>

SQL> select * from t1 where a=&b;

old   1: select * from t1 where a=&b

new   1: select * from t1 where a=2

no rows selected

SQL> define b

DEFINE B               = "2" (CHAR)

SQL> undefine b~~~~~~~~~~~~~~~~~~~~~~~~~~清除变量

SQL> define b

SP2-0135: symbol b is UNDEFINED

~~~~~~~~~~~~~~~~

prompt ,pause

脚本里用

prompt '这是TEST,按回车'

pause

select count(*) from t1 ;         script中内容

SQL> @d:\1.txt

'这是TEST,按按回车'

COUNT(*)

----------

15

****************

variable 绑定变量

SQL> variable test number;~~~~定义

SQL> exec :test:=2;~~~~~~~~赋值

PL/SQL procedure successfully completed.

SQL> select * from t1 where a=:test;~使用

no rows selected

SQL> print test~~~~~~~~~~输出变量结果

TEST

----------

2

********************

set arraysize N 指定数目提取尺寸 DEFALUT 15,这个跟优化有关系,后面关于优化实验中单独介绍

SQL> set autocommit on;~~~~~~~~自动提交

SQL> update t1 set a=2;

15 rows updated.

Commit complete.

SQL> set autocommit off;

SQL> set colsep |~~~~~~~~~~设置分隔符号default 空格

SQL> select object_name,object_id from user_objects where rownum<2;

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_ID

----------

TEST_SQ

53795

SQL> col object_name format a20

SQL> select object_name,object_id from user_objects where rownum<2;

OBJECT_NAME         | OBJECT_ID

--------------------|----------

TEST_SQ             |     53795

SQL> set heading off~~~~~~~~不显示标题 列名 default on

SQL> select object_name,object_id from user_objects where rownum<2;

TEST_SQ             |     53795

set linesize行长  default 80

pagsize每页显示的行数 default 14

SQL> select * from t1;

A

----------

1

2

3

4

5

6

7

8

9

10

11

A

----------

12

13

14

15

16

17

18

19

20

20 rows selected.

SQL> select * from t1;

A~~~~~~~~~~~~

----------~~~~~~~~~~~~~~~~

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

A

----------~~~~~~~~~~~~~~这都算1行

18

19

20

SQL> set pagesize 23

SQL> select * from t1;

A

----------

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

20 rows selected.

20 rows selected.

SQL>

**********serveroutput

针对dbms_output的 default off 不输出

1  declare

2  begin

3  dbms_output.put_line('test');

4* end;

SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> /

test***************************

PL/SQL procedure successfully completed.

*********

termout 控制script. 输出default on ,off只运行不输出

SQL> set termout off

SQL> @d:\1.txt;

SQL>

SQL> set termout on

SQL> @d:\1.txt;

'这是TEST,按按回车'

COUNT(*)

----------

20

SQL>

**************

SQL> set time on~显示时间系统时间

15:25:41 SQL>

15:25:41 SQL> set time off

SQL>

SQL> set timing on~~~~~~~~~~~~~~~显示SQL语句运行时间

SQL> select count(*) from t1;

COUNT(*)

----------

20

Elapsed: 00:00:00.00

SQL> set define ~~~~~~改变量定义字符

SQL> define a=1

SQL> select count(*) from t1 where ;

old   1: select count(*) from t1 where

new   1: select count(*) from t1 where a=1

COUNT(*)

----------

1

SQL> select count(*) from t1 where a=&a;

SP2-0552: Bind variable "A" not declared.

SQL> set verify off~~~~~~~~~~~~~~不显示old,new行 (不显示验证消息)

SQL> define a=1

SQL> select count(*) from t1 where ;

COUNT(*)

----------

1

SQL> set verify on~~~~~~~~~~~~~~~~~~~~~

SQL> select count(*) from t1 where ;

old   1: select count(*) from t1 where

new   1: select count(*) from t1 where a=1

COUNT(*)

----------

1

****************

clear colunm_name clear 清除对列设置

clear buffer 清除buffer

clear compute清除报表

spool d: 位置     append追加,replace覆盖,create新建

spool off