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

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)