Java programmer cainiao (3) Oracle BASICS (3) Common SQL plus commands

Oracle SQL * Plus is a client tool that interacts with Oracle. In SQL * Plus, you can run the SQL * Plus command and the SQL * Plus statement. The DML, DDL, and DCL statements we usually call are SQL * Plus statements. After they are executed, they can be stored in a memory area called SQL Buffer, in addition, only one recently executed SQL statement can be saved. We can modify the SQL statement stored in SQL buffer and execute it again. SQL * Plus generally deals with databases. Apart from SQL * Plus statements, other statements executed in SQL * Plus are called SQL * Plus commands. After they are executed, they are not stored in the memory area of the SQL Buffer. They are generally used to format and display the output results to facilitate the preparation of reports. Let's take a look at the commonly used SQL * Plus commands.

1. DESC command: View table structure command

2. Set line command: Set the display data width. The default display width is 80.

We can see the query result shown above with the default data width 80. You can clearly see that a row cannot display all the data. It looks ugly. The following figure shows the effect by setting the display data width.

It seems that the data is much better. All data fields are displayed in one row. This is the role of the Set line command.

3. l command and N text command: l command is to view the SQL command we just entered, that is, the SQL statement in the cache area. The N text command is used to modify the nth behavior of the SQL statement in the buffer zone.

We can see from the above example. We entered the l command to display the SQL statement of the last input country. Then the N text command is used to modify the SQL statement of the first line. Then the SQL statement that displays the buffer is used as the modified SQL statement,

4. "/" command: Execute the buffer SQL command

We know above that we can use the N text command to modify the last SQL statement we entered. How can we execute the modified SQL statement? Are you sure you want to input it again? In this case, the modification command is meaningless. Sqlplus provides the "/" command for us. Execute the SQL statement of the buffer.

5. N command and a command: Set the n-th row command and append the SQL command to the current row

Sometimes, when writing SQL statements, we forget to add some key statements to line N. We can use these two commands. Use the N command to select the current row of the SQL statement to be appended. Then, use the command to append the SQL statement to the current line.

For example:

Suddenly, we not only want to see the information of the empno field. We also want to see the job field information. We can do the following:

I wonder if you understand it? Give me a feedback.

6. Run the del n command to delete the nth command.

This command should be easy to understand. Let's look at the example:

7. C command: the "C/original/new body" command is mainly used to change the original content of the current line to a new body. That is, replace the new text with the new text;

Note that. You must set the row of the original text to be replaced to the current row before replacement.

We can see from the above. At first, I wrote the field name empno as empon. So I want to change the field name. First, set the row where empon is located to the current row. Then, use the C command to modify it.

8. How to survive a script file: Save the statements in the SQL Buffer to the specified. SQL file, which is a so-called script file.

Use the Save command to save data to a specified folder. Note: Make sure the folder exists before saving it.

9. Edit the script file.

1) Use the GET command to load the local script file to sqlplus.

Then we can use commands such as C/A/del to modify the file.

2 .). You can also use the Ed command to open the script file and directly modify it.

10. Run the script file directly.

From the above we can see that @ directly the script file we want to execute. We can directly run the SQL statement in the file. You can also run the script file using the start command.

11. Spool command: After this command, everything displayed on the screen is saved to the specified directory. After the spool command, you can execute some queries and enter the spool off command to view the report in the specified file. This is mainly used to save reports.