Shulou(Shulou.com)06/01 Report--

Data manipulation language

DML can be executed under the following conditions:

-insert data into the table

-modify existing data

-Delete existing data

A transaction is made up of DML statements that complete several tasks

Add new data to the table-INSERT statement

Insert data into a table using INSERT statements

Insert into table [(column [, column...])]

Values (value [, value...])

Only one piece of data can be inserted into a table at a time using this syntax

Add a new value for each column

List the values of each column in the default order of the column

Randomly list column names and their values in the INSERT clause

Character and date data should be enclosed in single quotation marks

Insert into departments (department_id,department_name,manager_id,location_id) values (304gramme system Control',202,1900)

Implicitly: omit the value of the column in the list table.

Insert into departments (department_id,department_name) values (30, 'purchasing')

Display: specify a null value in the VALUES clause.

Insert into departments values (100,' finance', null, null)

The SYSDATE function records the date and time of the current system.

INSERT INTO employees (employee_id

First_name, last_name

Email, phone_number

Hire_date, job_id, salary

Commission_pct, manager_id

Department_id)

VALUES (113)

'Louis', 'Popp'

'LPOPP', '515.124.4567'

SYSDATE, 'AC_ACCOUNT', 6900

NULL, 205,110)

Add a new employee

INSERT INTO employees

VALUES

'Den', 'Raphealy'

'DRAPHEAL', '515.127.4561'

TO_DATE ('FEB 3, 1999,' MON DD, YYYY')

'SA_REP', 11000, 0.2,100,60)

Create script

Use the & variable in the SQL statement to specify the column value.

& variables are placed in the VALUES clause.

Insert into departments

(department_id, department_name, location_id)

Values (& department_id,'& department_name',&location)

Copy data from other tables

There is no need to write the VALUES clause.

The list of values in the subquery should correspond to the column name in the INSERT clause.

Insert into sales_reps (id, name, salary, commission_pct)

Select employee_id, last_name, salary, commission_pct

From employees

Where job_id like'% REP%'

Modify data

Use UPDATE statements to update data, and you can update more than one piece of data at a time, if needed.

Update table

Set column = value [, column = value,...]

[where condition]

Use the WHERE clause to specify the data you want to update:

Update employees

Set department_id = 50

Where employee_id = 113,

If you omit the WHERE clause, all data in the table is updated:

Update copy_emp

Set department_id = 110,

Specify that the value of the column_name= NULL update column is NULL.

Update two columns using a subquery

Update the work and salary of employee 113 to be the same as employee 205

Update employees

Set job_id = (select job_id

From employees

Where employee_id = 205)

Salary = (select salary

From employees

Where employee_id = 205)

Where employee_id = 113,

Update data based on another table

Use the UPDATE subquery to update based on the data in another table

Update copy_emp

Set department_id = (select department_id

From employees

Where employee_id = 100)

Where job_id = (select job_id

From employees

Where employee_id = 200)

Delete data from the table

Use the DELETE statement to delete data from the table

Delete [from] table [where condition]

Use the WHERE clause to specify deleted records

Delete from departments where department_name = 'finance'

If you omit the WHERE clause, all data in the table will be deleted:

Delete from copy_emp

Delete data based on another table

Delete from employees

Where department_id = (select department_id

From departments

Where department_name

Like'% public%')

TRUNCATE statement

Delete all rows from the table, leaving the empty table and the completed table structure.

Data definition language (DDL), not a DML statement, cannot use undo

Syntax:

TRUNCATE TABLE table_name

Example:

TRUNCATE TABLE copy_emp

Database transaction control statements COMMIT, ROLLBACK, and SAVEPOINT

A database transaction consists of the following parts:

One or more DML statements

A DDL statement

A DCL statement

Database transactions: start and end

Start with the execution of the first DML statement

End with one of the following:

-COMMIT or ROLLBACK statement

-DDL or DCL statement (auto-commit)

-SQL Developer or SQL*Plus user exits

-system crash

Advantages of COMMIT and ROLLBACK statements

Using COMMIT and ROLLBACK statements, we can:

Ensure data integrity.

Preview data changes before they are submitted.

Group logically related operations.

Roll back to SavePoint

Use the SAVEPOINT statement to create a save point in the current transaction.

Use the ROLLBACK TO SAVEPOINT statement to roll back to the SavePoint you created.

UPDATE...

SAVEPOINT update_done

INSERT...

ROLLBACK TO update_done

Implicit transaction processing

Autocommit is performed in the following situations:

-DDL statement.

-DCL statement.

-end the session normally without using COMMIT or ROLLBACK statements to commit or roll back.

An abnormal end of a session or a system exception can cause an automatic rollback.

Data status before commit or rollback

The state of the data before the change can be restored.

Users who perform DML operations can query previous corrections through the SELECT statement

Other users cannot see the changes made by the current user until the current user ends the transaction.

The row involved in the DML statement is locked and cannot be manipulated by other users.

Status of data after COMMIT

Changes to the data are saved in the database.

The previous data was overwritten.

All users can view the results.

Those rows whose locks on the affected rows are released for other users to manipulate.

All save points will be deleted.

Modify the data:

Delete from employees

Where employee_id = 99999

Insert into departments

Values (290, 'corporate tax', null, 1700)

Submit changes:

Commit

Data status after rollback

Use the ROLLBACK statement to invalidate data changes:

All data changes are undone.

The data is restored to its previous state.

The lock is released.

Delete from copy_emp

Rollback

Data status after rollback: exampl

Delete from test

25000 rows deleted.

Rollback

Rollback complete.

Delete from test where id = 100

1 row deleted.

Select * from test where id = 100

No rows selected.

Commit

Commit complete.

Statement-level rollback

When the execution of a separate DML statement fails, only that statement is rolled back.

The Oracle server automatically creates an implicit retention point.

Other data changes are still retained.

The user should execute a COMMIT or ROLLBACK statement to end the transaction.

Read consistency

Read consistency provides a consistent view of data

The change of data made by one user will not affect the change of other users.

Guarantee of read consistency for the same data:

-the query does not wait for modification.

-Modification does not wait for query.

-the modification is waiting to be modified.

FOR UPDATE clause in SELECT statement

Lock the row in the EMPLOYEES table where job_id is SA_REP.

Select employee_id, salary, commission_pct, job_id

From employees

Where job_id = 'SA_REP'

For update

Order by employee_id

When you issue the ROLLBACK or COMMIT command, the lock is released.

If the SELECT statement attempts to lock a row locked by another user, the database will wait until the row is available before returning the result of the SELECT statement.

FOR UPDATE clause display

You can use the FOR UPDATE clause when querying multiple tables in a SELECT statement.

Select e.employee_id, e.salary, e.commission_pct

From employees e join departments d

Using (department_id)

Where job_id like 'st_clerk'

And location_id = 1500

For update

Order by e.employee_id

The rows of table EMPLOYEES and table DEPARTMENTS are locked.

You are qualified to modify the column names using FOR UPDATE, and only the queried rows are locked.

Command comment INSERT insert row information UPDATE update information DELETE deletes a row of information TRUNCATE deletes all row contents in the table, but the table structure saves COMMIT to turn the state of all pending into a permanent SAVEPOINT that discards all pending data using the save point created by the rollback ROLLBACK

FOR UPDATE clause in SELECT locks the select query line and does not release it until the end of select

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.