Oracle-05-sql statement Overview, classification & Sql*plus Overview (Insert,desc,list,r,del,a,c,n and other commands)

Source: Internet
Author: User

I. Overview of SQL statements

(1) SQL is a " Structured Query Language (structured Language)".

SQL is the standard query language used by most mainstream database systems.

(2) The advantages of SQL statement: The language structure is simple, powerful, simple and easy to learn.

(3) SQL development is for easier access to the relational database, so SQL can run the following operations:

① querying data from the database

② inserting data into the database

③ deleting data from the database

④ Creating and Manipulating Database objects (such as users, indexes, triggers, and so on)

⑤ control access to the database

⑥ Updating data

(4) Note: SQL is not a programming language.

It is simply a statement that provides input, retrieve, change, delete, and display data stored in the database.

(5) Assume that later in the use of SQL statement error message, can enter the http://ora-code.com site query information. is to assume that the error message ora-xxxxxx. The xxxxxx is a string of numbers. The three-bit may be five-bit, so this error message occurs. Ability to log in to the above site query.

Ii.SQL Classification (emphasis)

(1) Data definition language (DDL). such as: Create,drop,rename ... ( Once the operation is successful, it is almost impossible to revert to the original, with no room for manoeuvre, such as existing a table.) Rename it, assuming the operation is successful and cannot be changed. cannot be restored as it is)

(2) Data Manipulation language (DML), such as: Insert,update,delete ... ( data can be rolled back )

For example, the login database user is a sys, and the T table is queried with the following command, with no data information such as:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

Now add data to the T table:


Then query the T table. Find the data that you just added:


Now open a CMD form again, log in with the new user Lisi. Query the T table and find that there are no data just added to it, for example:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">


Continue to write the commit command within the cmd form of the original SYS login, for example:


Then go to the Lisi login cmd form to query the T table and find the newly added data. For example, with:


"Summary" assumes that the data is manipulated with DML. Must commit, the ability to guarantee the real deposit in the database.

(3) Data Control Language (DCL) such as: Grant, Revoke ...

(4) Query language, such as: Select ...

(5) Transaction Control Language (Transaction controls, TC). such as: Commit,rollback,savepoint ...

Iii. Overview of Sql*plus

The 1.sql*plus definition, which is an environment developed by Oracle to run SQL statements, provides a direct way to run a database connection.

2.sql*plus Features

It is a client application tool. such as command line Sql*plus, Sql*plus in Oracle

Or isql*plus they are able to communicate directly with the database management system.

3. Assume that the following interface is present, which is the sql*plus environment. For example, with:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

Four, display table structure syntax format: DESC table name

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

V. View the syntax format for SQL statements in the cache: L (or list)

For example, suppose you enter Select*fromall_users first, and then enter. Will see all the users, and then this command is stored in the Sql*plus cache, assuming that the input l at this time, a command message appears, for example:


"NOTE" The SQL statement will be written very long in the future. So a newline is required, for example:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

The method is to enter the first line after the carriage return, and then enter the second line, assuming that the input semicolon is the end, if you use L query the previous statement, can also be queried, for example:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

The figure * indicates positioning on the current line.

The SQL statement in the cache is executed once again, in the form of:/(or R)

This is actually a quick operation.

Assume write/Post enter. The SQL statements in the cache are executed directly. Suppose write R returns. The SQL statements in the cache are listed before the statement is executed.

Use of the Seven and N commands

Suppose you write an error in the SQL statement, you can use "n". The meaning of this "n" is to set the current line in Sql*plus, "n" is a detailed value (such as: ... )。

Syntax format: n

Like what. Write a 1 return car. Then locate the 1th line in the cache SQL command, and write a 2 return car. Then navigate to line 2nd in the cached SQL

Viii. Delete command

Suppose you want to delete a row. Ability to use the DEL command.

Syntax format del n

Delete the 2nd line of code in the cache after you enter del 2



Ix. Order of Accession

Suppose a line is now followed by some SQL statements that can use a (append).

Syntax format: a

For example: First use the list to view the buffer command, and then use a to join the command

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

Found two commands together, namely student and where are connected together. So when you use the a command to add a command to the buffer to hit two return, the first carriage return is a command must take the carriage return, the second carriage return is added as the beginning of the new code to the buffer, for example:



Q: Suppose the buffer has 3 rows of SQL commands, you need to join the command on the second line. How does it work?

A: The list command lists the buffer command, and then uses the n command, which is input 2 to navigate to line 2nd, and then uses the a command to add a new command after the line. Then use the list to view the new command, and finally execute it. Full steps for example with:

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/ I0jbqkfcma==/dissolve/70/gravity/center


Span style= "font-size:24px" > Ten, change command

syntax format: c/changed content/new content

(two spaces after C)

for example. The C command can be used to partially remove the effect. For example:


first list out buffers all commands, Then use the n command to navigate to line 2nd, and then use the C command to delete '%s% ' after the command, just c /or 1=1/can be, that is, the need to delete the part to replace the blank. Note c after two carriage returns.

Oracle-05-sql statement Overview, classification & Sql*plus Overview (commands such as insert,desc,list,r,del,a,c,n)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.