Database--Add, modify, delete

that need to be performed SQL statements:

The addition, modification, and deletion of data in the database are all part of the Database manipulation language ( DML), such types of SQL statements can only be made to work by executing commits in the Data Control Language (DCL), which is used by rollback in the DCL language to undo DML language operations:

Add Data :

There are two ways to add data:

The first type :

Grammar:

INSERT INTO table_name [(column_name1,column_name2,..., column_namen)] values (value1,value2,... Valuen)

e.g

Attention:

1, the Order of the field list can not be listed in the order of the fields in the table, but the corresponding value list must correspond to the field list;

e.g

2. If the value in the corresponding value list is a character type or a date type, then single quotation marks are required;

3. If there is no field list, the order of the values in the value list must be arranged in the default order of the fields in the actual table;

e.g

The second type :

Grammar:

INSERT INTO To_table_name [(column_name1,column_name2,..., column_namen)]

Select Column_name1,column_name2,..., column_namen from From_table_name

e.g

Attention:

1. The number and data type of the list of field names in the INSERT clause must match the number of field lists selected in the SELECT clause and the data type. The data type does not consider length, but must be longer or equal than the length of the corresponding column being checked out.

2. The Select query field name can be inconsistent with the field name of the Insert Insert table.

To Modify the data :

Grammar:

UPDATE table_name

Set Column1=value1, Column2=value2 ...

[Where ...]

e.g

Delete Data :

Grammar:

Delete [from] table_name

[Where ...]

e.g

Note: The FROM keyword may not be available in the Oracle database, but must be in the MySQL database

Transaction Operations :

when executing a set of DML operations, in order to ensure data consistency and to prevent the database from generating erroneous data, it is necessary to make the group DML operations succeed or fail at the same time through a transaction (Transaction), for example:

when you perform a first When a DML SQL statement occurs, the transaction begins, and the transaction ends when the following conditions occur:

1, executed a commit or ROLLBACK statement;

2. A DDL statement (such as a build table statement) or a DCL statement (such as authorizing a user) is executed, as follows:

3, the database client program exits or the database crashes, in order to maintain data consistency, will also end the transaction.

Transaction control Statements:

1,commit: Commit the transaction, so that the changes made before the permanent effect on the database;

2,rollback: Rollback The current transaction, discard all pending data changes;

3. SavePoint name: Mark the savepoint in the current transaction (non- ansl SQL Standard);

4. Rollback to Name: rolls back the current transaction to the specified savepoint, discarding any changes to the database after the savepoint;

e.g

Attention:

1. If you need to query the database for real changes when executing DML statements and do not end the transaction, you will be asked to execute the query in a new SQL window : Because each operation in a transaction is temporary before the transaction is committed , the DML statement first affects the user's database buffer before the COMMIT or ROLLBACK statement executes, because these operations can be restored, and because it is in the user's data buffer, So in the original SQL window is the result of the data buffer, not the real data of the database, to get the real data of the database, you need to execute the query in the new SQL window, so that for different users, The Oracle server uses read consistency to ensure that each user sees the same data as the last commit.

2. When a user executes a DML statement, the affected row is locked if the object does not end, and other users cannot change the data in the affected row, and the DML statement executed by the user is executed until the previous user executes the DML statement transaction ends , such as:

A, execute the following SQL statement in a SQL window, and do not execute a transaction closing sentence

b, and then execute the following SQL statement in a new SQL window

Modify After the SQL statement is executed, PL + developer shows that the statement is known to be in the execution state, as follows:

when the transaction that modifies the data above ends, the data is deleted below The SQL statement ends the execution state.

Database--Add, modify, delete