Notes for Oracle beginners (1)-basic content (updating in real time ..)

1. view the Host Name:

SQL>Select host_name from V $ instance;

Host_name
----------------------------------------------------------------
Mycomputer-PC

2. Data in the three main tables:

(1)EMP employee table
SQL & gt; Set line 120;
SQL> select * from EMP;

Empno ename job Mgr hiredate Sal comm deptno
-----------------------------------------------------------------------------------
7369 Smith clerk 7902-12-80 800 20
7499 Allen salesman 7698 20-2 month-81 1600 300 30
7521 ward salesman 7698 22-2 month-81 1250 500 30
7566 Jones manager 7839 2975-81 20
7654 Martin salesman 7698 28-9 month-81 1250 1400 30
7698 Blake manager 7839 01-5 months-81 2850 30
7782 Clark manager 7839-81 2450 10
7788 Scott analyst 7566-87 3000 20
7839 King President 17-11 month-81 5000 10
7844 Turner salesman 7698 month-81 1500 0 30
7876 Adams clerk 7788 month-87 1100 20

Empno ename job Mgr hiredate Sal comm deptno
-----------------------------------------------------------------------------------
7900 James Clerk 7698 03-12 months-81 950 30
7902 Ford analyst 7566 03-12 months-81 3000 20
7934 Miller clerk 7782 23-1 month-82 1300 10

(2)Salgrade: wage level table
SQL> select * From salgrade;

Grade losal hisal
------------------------------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

(3)Dept: department table
SQL> select * from Dept;

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

3. view 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)

4. SQL statements displayed in SQL * Plus:Enter "L"And modify SQL statements:Enter "n text"
SQL> select SQL // The SQL column does not exist in the EMP table, so it cannot be queried correctly.
2 from EMP
3 where SAL> 1000;
Select SQL
*
Row 3 has an error:
ORA-00904: "SQL": Invalid identifier

SQL> L// Enter "L" to display the SQL statement in the buffer zone.
1 select SQL
2 from EMP
3 * Where SAL> 1000
SQL> 1 select Sal// Enter "n text". N indicates the row number, and text indicates the value to be replaced.
SQL>/// Enter "// or R" to re-run the statements in the SQL Buffer.

Sal
----------
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
3000

Sal
----------
1300

12 rows have been selected.

5. Statements for deleting rows in SQL * Plus:Enter the keyword "del N"

SQL> select *
2 from Dept
3 where deptno = 10;

Deptno dname Loc
------------------------------------
10 Accounting New York

SQL> L
1 select *
2 from Dept
3 * Where deptno = 10
SQL> del 3// Enter the keyword "del N", indicating the statement for deleting row n.
SQL>/// Re-run the statements in the buffer

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

6. Modify (replace) the specified statement content:Enter "C/original content/content to be replaced"
SQL> select dname

2 from Dept
3 where deptno = 10;

Dname
--------------
Accounting

SQL> L// Enter "L" to read the statements in the buffer.
1 select dname
2 from Dept
3 * Where deptno = 10
SQL> C/dname/deptno// Enter "C/original content/content to be replaced" directly. If no row is specified, the string cannot be found.
SP2-0023: no string found
SQL> L
1 select dname
2 from Dept
3 * Where deptno = 10
SQL> 1// Enter the specified row number.
1 * select dname
SQL> C/dname/deptno;
1 * select deptno
SQL>/

Deptno
----------
10

7. How to generate a. SQL script file:Enter "Save path/file name"
SQL> L

1 select deptno
2 from Dept
3 * Where deptno = 10
SQL> Save E:/sqltest. SQL;
File E:/sqltest. SQL has been created.// Note: the content in the saved SQL script file is the statement content in the buffer, while the buffer saves the last statement executed.

8. How to run the SQL script file:Enter "@ (start) path/file name"
 SQL> @ E:/sqltest. SQL;

 

Deptno
----------
10

 

9. How to get the script content: Enter "get"Path/file name"

SQL> Get E:/sqltest. SQL;

1 select deptno
2 from Dept
3 * Where deptno = 10

You can also open and edit in Notepad:Ed path/file name

10. Important spool commands:Save the complete script and Result

SQL> spool E:/spooltest. SQL;
SQL> select *
2 from Dept;

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

SQL> select deptno
2 from Dept;

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

SQL> spool off;
SQL>/

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

Content in the generated script

 

This is the first note of today. I hope it will be helpful for beginners like me.