SQL plus easy-to-Forget commands

Sqlplus username/password @ is the name of the local network service added to connect to Oracle.
C: \ Users \ Haiyang> sqlplus Scott/tiger @ myoracle9i

SQL * Plus: Release 11.2.0.1.0 production on Sunday February 20 22:12:15 2011

Copyright (c) 1982,201 0, Oracle. All rights reserved.

Connect:
Oracle9i Enterprise Edition Release 9.2.0.1.0-Production
With the partitioning, OLAP and Oracle Data Mining options
Jserver release 9.2.0.1.0-Production

SQL>

Desc tablename to display the table structure.

SQL> DESC EMP;
Is the name empty? Type
-----------------------------------------------------------------------------
Empno not null number (4)
Ename varchar2 (10)
Job varchar2 (9)
Mgr number (4)
Hiredate date
Sal number (7,2)
Comm number (7, 2)
Deptno number (2)

Set line [size] {80 | n}, where n is a natural number and 80 is the default value. This command sets the display output of the display to the width of n characters and 80 characters. The default display width of this command is as follows.
Set pagesize 120 is used to set the number of input rows per page.
Col hiredate for A15 is used to specify the length of 15 characters to display the column hiredate.

L (list) command. This command is used to display the content in the SQL Buffer. 
SQL> L
1 select *
2 * from EMP
SQL>

N text command to modify the error section, where N is the SQL statement in the SQL Buffer
The row number. Text is an SQL statement that replaces the error part.
SQL> L
1 select *
2 * from EMP
SQL> 2 from Dept
SQL> L
1 select *
2 * from Dept
SQL>

Run to re-run the SQL statement in the SQL Buffer.
SQL>/

Deptno dname Loc
-------------------------------------
10 Accounting New York
20 research Dallas
30 sales Chicago
40 operations Boston

The N command sets the current line, and the (append) command adds content to the current line.
SQL> L
1 select ename
2 from EMP
3 * order by Sal DESC
SQL> 1
1 * select ename
SQL> A, deptno, Sal
1 * select ename, deptno, Sal
SQL> L
1 select ename, deptno, Sal
2 from EMP
3 * order by Sal DESC
SQL>/

Ename deptno Sal
------------------------------
King 10 5000
Scott 20 3000
Ford 20 3000
Jones 20 2975
Blake 30 2850
Clark 10 2450
Allen 30 1600
Turner 30 1500
Miller 10 1300
Ward 30 1250
Martin 30 1250
Adams 20 1100
James 30 950
Smith 20 800

14 rows have been selected.

Use 0 text to insert a row of data before the first row. You can also use the current maximum number of rows + 1 text to add a row. 
SQL> L
1 select ename, deptno, Sal
2 from EMP
3 * order by Sal DESC
SQL> 0 CREATE TABLE emp_temp
SQL> L
1 create table emp_temp
2 select ename, deptno, Sal
3 from EMP
4 * order by Sal DESC
SQL>/

The table has been created.

SQL>

Delete row N using del n. If n is not specified, the current row is deleted. You can also use the del m n command to delete all contents from line m to line N.

SQL> L
1 create table emp_temp
2 select ename, deptno, Sal
3 from EMP
4 * order by Sal DESC
SQL> del 1
SQL> L
1 select ename, deptno, Sal
2 from EMP
3 * order by Sal DESC
SQL> Del
SQL> L
1 select ename, deptno, Sal
2 * from EMP
SQL>

SQL> L
1 create table emp_temp
2 select ename, deptno, Sal
3 from EMP
4 * order by Sal
SQL> del 2 4
SQL> L
1 * Create Table emp_temp
SQL>

Use the C [hange]/original/new body command to modify statements in the SQL Buffer. This command replaces "original" with "new text" in the current line ".
Note that there is no space between C/oldtext/newtext,/oldtext and/newtext. If C/DEPT/EMP is used in the following example, the "SP2-0023: no string found" error occurs.

SQL> select ename, deptno, Sal
2 from Dept
3 where SAL> 1000
4 order by Sal DESC;
Where SAL> 1000
*
Row 3 has an error:
The ORA-00904: "Sal": Invalid identifier

SQL> L
1 select ename, deptno, Sal
2 from Dept
3 where SAL> 1000
4 * order by Sal DESC
SQL> 2
2 * from Dept
SQL> C/DEPT/EMP
2 * from EMP
SQL> L
1 select ename, deptno, Sal
2 from EMP
3 where SAL> 1000
4 * order by Sal DESC
SQL>/

Ename deptno Sal
------------------------------
King 10 5000
Scott 20 3000
Ford 20 3000
Jones 20 2975
Blake 30 2850
Clark 10 2450
Allen 30 1600
Turner 30 1500
Miller 10 1300
Ward 30 1250
Martin 30 1250
Adams 20 1100

12 rows have been selected.

SQL>

The Save command saves the content of the SQL Buffer to the specified file, which is called a script file. The Save command creates a specified file, but the path must already exist. Otherwise, the file cannot be saved.
SQL * Plus commands are not stored in the SQL Buffer, and only the previous SQL statement is saved in the SQL Buffer. The SQL Buffer can store only one SQL statement.
After the script file is generated, you can use the GET command of SQL * Plus to load the script file into the SQL Buffer.

SQL> L
1 select ename, deptno, Sal
2 from EMP
3 where SAL> 1000
4 * order by Sal DESC
SQL> Save D: \ SQL _test \ sample
SP2-0110: Unable to create save file "D: \ SQL _test \ sample. SQL"
SQL> Save D: \ oracle_ SQL _study \ sample
File d: \ oracle_ SQL _study \ sample. SQL has been created.
SQL> select distinct deptno from EMP;

Deptno
----------
10
20
30

SQL> L
1 * select distinct deptno from EMP
SQL> get D: \ oracle_ SQL _study \ sample. SQL
1 select ename, deptno, Sal
2 from EMP
3 where SAL> 1000
4 * order by Sal DESC
SQL>/

Ename deptno Sal
------------------------------
King 10 5000
Scott 20 3000
Ford 20 3000
Jones 20 2975
Blake 30 2850
Clark 10 2450
Allen 30 1600
Turner 30 1500
Miller 10 1300
Ward 30 1250
Martin 30 1250
Adams 20 1100

12 rows have been selected.

SQL>

You can use the Ed [it] command of SQL * Plus to directly edit D: \ oracle_ SQL _study \ sample. SQL.

You can use the @ or start command of SQL * Plus to directly run the script file D: \ SQL \ sample. SQL.
@ Or the start command loads the content of the specified script file into the SQL Buffer and runs it.

SQL> @ D: \ oracle_ SQL _study \ sample. SQL

Ename deptno Sal
------------------------------
Allen 30 1600
Ward 30 1250
Jones 20 2975
Martin 30 1250
Blake 30 2850
Clark 10 2450
Scott 20 3000
King 10 5000
Turner 30 1500
Adams 20 1100
Ford 20 3000
Miller 10 1300

12 rows have been selected.

SQL>

Spool D: \ SQL \ output is named after spool.
Everything displayed must be stored in the output file under the D: \ SQL directory. Only when spool is off
To see the content in the output file. If you enter spool out, the content will be sent to the printer.

The spool command creates a specified file, but the path must already exist. Otherwise, the spool file cannot be created.

SQL> spool D: \ SQL _test \ output
SP2-0606: Unable to create spool file "D: \ SQL _test \ output. lst"
SQL> spool D: \ oracle_ SQL _study \ output
SQL> L
1 select ename, deptno, Sal
2 from EMP
3 * Where SAL> 1000
SQL>/

Ename deptno Sal
------------------------------
Allen 30 1600
Ward 30 1250
Jones 20 2975
Martin 30 1250
Blake 30 2850
Clark 10 2450
Scott 20 3000
King 10 5000
Turner 30 1500
Adams 20 1100
Ford 20 3000
Miller 10 1300

12 rows have been selected.

SQL> spool off
SQL>