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 a=@a;
old 1: select count(*) from t1 where a=@a
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 a=@a;
COUNT(*)
----------
1
SQL> set verify on~~~~~~~~~~~~~~~~~~~~~
SQL> select count(*) from t1 where a=@a;
old 1: select count(*) from t1 where a=@a
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-611497/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 安全：snoopy： 只能記錄root的操作命令OOP
- Git命令實操記錄Git
- G014-ORACLE-ASK O SQL*PLUS / 達夢DiSQL / 高斯ZSQL 命令列翻頁OracleSQL命令列
- 1.3.3.5.1. SQL*Plus 連線命令語法SQL
- mybatis plus的學習記錄MyBatis
- 大資料實驗記錄大資料
- 命令記錄
- ORACLE DBA必須記住的常用SQL命令和檢視OracleSQL
- Oracle實驗6--掌握Oracle資料庫的日誌操作Oracle資料庫
- Linux下使用script命令記錄終端操作顯示Linux
- Oracle 常用SQL筆記OracleSQL筆記
- mysql load 相關實驗記錄MySql
- 記錄一次驚心動魄的誤操作(Oracle)Oracle
- 【PDB】Oracle pdb維護常用sql命令OracleSQL
- ipmitool命令記錄MIT
- docker 命令記錄Docker
- linux操作記錄Linux
- [記錄]curl命令筆記筆記
- SpringCloud或SpringBoot+Mybatis-Plus利用AOP+mybatis外掛實現資料操作記錄及更新對比GCCloudSpring BootMyBatis
- 記錄一個sqlSQL
- Oracle 12c 檢視CDB&PDBs資訊（SQL*PLUS）OracleSQL
- 記錄springboot 3.3.5 版本整合 mybatis-plusSpring BootMyBatis
- Git 命令使用記錄Git
- MongoDB命令--自用記錄MongoDB
- Lumen 實時記錄 SQL 執行解決方案SQL
- 實戰記錄之SQL server報錯手工注入SQLServer
- Conda常用命令記錄（自用記錄）
- linux常用操作記錄Linux
- mongodb聚合操作記錄MongoDB
- SQL優化工作記錄SQL優化
- Laravel sql 日誌記錄LaravelSQL
- STM32F207DAC實驗記錄
- MsSql資料庫使用SQL plus建立DDL和DML操作方法SQL資料庫
- 欄位修改記錄操作日誌的實現
- 一次SQL隱碼攻擊實戰記錄SQL
- SEO 經驗記錄
- MAC 終端 命令 記錄Mac
- linux系統命令記錄Linux
- Vim 的命令模式記錄模式