SQL-based DML data processing (13)

Data Manipulation language

DML can be performed under the following conditions:

– Inserting data into the table

– Modify existing Data

– Delete existing data

A transaction is made up of DML statements that do several things

Add a new data –insert statement to the table

Inserting data into a table using the INSERT statement

insert into table [(column [, Column ...])]

Values (value [, value ...]);

Use this syntax to insert only one piece of data into a table at a time

Add a new value for each column

• List the values of individual columns in the default order of columns

• Arbitrarily list column names and their values in the INSERT clause

• Character and date type data should be enclosed in single quotes

INSERT INTO departments (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (304, ' System Control ', 202,1900 );

Implicit: Omit the value of the column in the Column name table.

INSERT INTO departments (Department_id,department_name) values (+, ' purchasing ');

Display mode: Specify a null value in the VALUES clause.

INSERT into departments values (+, ' 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 New Employee

INSERT into employees

VALUES (114,

' Den ', ' raphealy ',

' Drapheal ', ' 515.127.4561 ',

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

' Sa_rep ', 11000, 0.2, 100, 60);

Create a script

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

The & variable is placed in the values clause.

INSERT INTO Departments

(department_id, Department_name, location_id)

VALUES (&department_id, ' &department_name ', &location);

Copying data from other tables

You do not have to write the VALUES clause.

The list of values in the subquery should correspond to the column names 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% ';

modifying data

Updating data with the UPDATE statement allows you to update multiple data at once, if required.

Update table

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

[Where condition];

Use the WHERE clause to specify the data that needs to be updated:

Update Employees

Set department_id = 50

where employee_id = 113;

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

Update Copy_emp

Set department_id = 110;

Specifies column_name= NULL to update a column with a value of NULL.

Update two columns with a subquery

Update number 113th employee's work and salary to make it the same as employee number NO. 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

Update to data based on another table using the update subquery

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 a table

To delete data from a table using the DELETE statement

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 is 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 statements

Removes all rows from the table, preserving the empty table and the completed table structure.

Data definition Language (DDL), not DML statements, cannot use Undo

Grammar:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE copy_emp;

Database transaction control Statements COMMIT, ROLLBACK, and SavePoint

Database transactions are made up of the following sections:

One or more DML statements

A DDL statement

A DCL statement

Database transactions: Start and end

Start with execution of the first DML statement

End with one of the following:

–commit or ROLLBACK statements

–DDL or DCL statement (auto-commit)

–sql Developer or Sql*plus user exits

– System crashes

Advantages of COMMIT and rollback statements

Using the Commit and rollback statements, we can:

• ensure data integrity.

• The data changes are previewed before being submitted.

• Groups logically related operations.

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8E/B2/wKioL1jJWG_z_u-fAABrFvDbvdQ062.jpg "title=" Qq20170315210853.jpg "width=" "height=" 426 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:750PX;HEIGHT:426PX; "alt = "Wkiol1jjwg_z_u-faabrfvdbvdq062.jpg"/>

Rollback to save point

• Use the SavePoint statement to create a savepoint in the current transaction.

• Use the ROLLBACK to savepoint statement to roll back to the saved point that was created.

UPDATE ...

SavePoint Update_done;

INSERT ...

ROLLBACK to Update_done;

Implicit transaction processing

Autocommit is performed in the following cases:

The –DDL statement.

The –DCL statement.

– End the session normally without committing or rolling back with a commit or ROLLBACK statement.

An automatic rollback occurs because of an unexpected session end or a system exception.

The state of the data before committing or rolling back

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

Users performing DML operations can query for previous corrections through the SELECT statement

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

The rows involved in the DML statement are 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 is overwritten.

All users can view the results.

The locks on the affected rows are freed, and those rows are available for other users to manipulate.

All save points will be deleted.

To modify the data:

Delete FROM Employees

where employee_id = 99999;

INSERT INTO Departments

VALUES (290, ' corporate tax ', NULL, 1700);

Submit changes:

Commit

Data state after rollback

Use the ROLLBACK statement to invalidate data changes:

• Data changes will be undone.

• The data is restored to its previous state.

• The lock is released.

Delete from Copy_emp;

Rollback;

Data state after rollback: example

Delete from test;

25,000 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 a separate DML statement fails to execute, only the 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 the data

A user's changes to the data do not affect other users ' changes

For the same data read consistency guarantee:

– The query does not wait for modification.

– The modification does not wait for the query.

– Modify pending modification.

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/8E/B4/wKiom1jJWwWRB8MlAACu_Aj07yM792.jpg "title=" Qq20170315210853.jpg "width=" 730 "height=" 456 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:730PX;HEIGHT:456PX; "alt = "Wkiom1jjwwwrb8mlaacu_aj07ym792.jpg"/>

The FOR UPDATE clause in the SELECT statement

• Locks the row job_id in the EMPLOYEES table as 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 a ROLLBACK or COMMIT command, the lock is released.

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

The FOR UPDATE clause shows the column

You can use the FOR UPDATE clause when you query 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 for table EMPLOYEES and table departments are locked.

With the column name for UPDATE you are eligible to modify, only the rows queried are locked.

command	note
insert	insert row information
update	update information
delete	delete one line of information
truncate
commit
savepoint
rollback
for UPDATE clause  in select	Lock select query rows, You know that the Select End is not released

This article is from the "record a bit of learning life" blog, please make sure to keep this source http://ureysky.blog.51cto.com/2893832/1907042

SQL-based DML data processing (13)