DML of SQL

DML(datamanipulation Language) Manipulation Language statements is used for managing data within schema Objects.

Provided by the DBMS for use by a user or programmer to implement operations on data in a database.
DML is divided into two classes: interactive DML and embedded DML.
Depending on the level of the language, DML can be divided into procedural DML and non-procedural DML two species.
A commit is required.
SELECT
INSERT
UPDATE
DELETE
MERGE
Pager
EXPLAIN PLAN
LOCK TABLE

SQL SELECT statement SQL SELECT statement

Select statements are used to select data from a table.

The result is stored in a result table, called a result set.

SQL SELECT Syntax

SELECT  from table name and: SELECT *  from Table name Comment: The SQL statement is not case sensitive.  SelectSelect.

SQL SELECT Instance

SELECT statement: SELECT  from Persons

"Persons" table: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Results:

LastName	FirstName
Adams	John
Bush	George
Carter	Thomas

SQL SELECT * Instance

* Replace the name of the column, just like this: SELECT *  from persons Tip: asterisk ( *) is a shortcut to select all columns.

Results: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Navigating in the result set (Result-set)

The results obtained by the SQL query program are stored in a result set. Most database software systems allow the use of programming functions to navigate the result set, such as: Move-to-first-record, Get-record-content, Move-to-next-record, and so on.

SQL INSERT INTO statement INSERT into statement

The INSERT INTO statement is used to insert a new row into the table.

Grammar

INSERT  into VALUES (value 1, value 2,....) We can also specify the columns to be inserted into the data:insertintovalues (value 1, value 2,....)

Insert a new line "Persons" table: City

LastName	FirstName	Address
Carter	Thomas	Changan Street	Beijing

SQL statements:

INSERT  into VALUES ('Gates'Bill'xuanwumen'  'Beijing')

Results: City

LastName	FirstName	Address
Carter	Thomas	Changan Street	Beijing
Gates	Bill	Xuanwumen 10	Beijing

Insert the data "Persons" table in the specified column: City

LastName	FirstName	Address
Carter	Thomas	Changan Street	Beijing
Gates	Bill	Xuanwumen 10	Beijing

SQL statements:

INSERT  into VALUES ('Wilson'champs-elysees')

Results: City

LastName	FirstName	Address
Carter	Thomas	Changan Street	Beijing
Gates	Bill	Xuanwumen 10	Beijing
Wilson		Champs-elysees

SQL UPDATE Statement

Update statement

The Update statement is used to modify the data in the table.

Grammar:

UPDATE SET = WHERE = a value

Person: City

LastName	FirstName	Address
Gates	Bill	Xuanwumen 10	Beijing
Wilson		Champs-elysees

To update a column in a row

We add FirstName for LastName who is "Wilson":

UPDATE SET = ' Fred ' WHERE = ' Wilson '

Results: City

LastName	FirstName	Address
Gates	Bill	Xuanwumen 10	Beijing
Wilson	Fred	Champs-elysees

To update several columns in a row

We will modify the address and add the city name:

UPDATE SET = ' Zhongshan ' = ' Nanjing ' WHERE = ' Wilson '

Results: City

LastName	FirstName	Address
Gates	Bill	Xuanwumen 10	Beijing
Wilson	Fred	Zhongshan 23	Nanjing

SQL DELETE statement DELETE statement

The DELETE statement is used to delete rows in a table.

Grammar

DELETE  from WHERE = Value

Person: City

LastName	FirstName	Address
Gates	Bill	Xuanwumen 10	Beijing
Wilson	Fred	Zhongshan 23	Nanjing

Delete a row

"Fred Wilson" will be removed:

DELETE  from WHERE = ' Wilson '

Results: City

LastName	FirstName	Address
Gates	Bill	Xuanwumen 10	Beijing

Delete all rows

You can delete all rows without deleting the table. This means that the structure, properties, and indexes of the table are complete:

DELETE  from table_name OR: DELETE *  from table_name

Common SQL Summary

SELECTStatementSELECTColumn Name fromTable nameSELECT *  fromtable name to select only a different value from the company column, we need to use theSELECT DISTINCTstatement:SELECT DISTINCTCompany fromOrders

INSERT  intostatement SyntaxINSERT  intoTable nameVALUES(value 1, value 2,....) We can also specify the columns for which you want to insert data:INSERT  intoTABLE_NAME (column 1, column 2,...)VALUES(value 1, value 2,....)

Updatestatement syntax:UPDATETable nameSETColumn Name=New valueWHEREColumn Name=a value

DELETEstatement SyntaxDELETE  fromTable nameWHEREColumn Name=The value can delete all rows without deleting the table. This means that the structure, properties, and indexes of the table are complete:DELETE  fromtable_name OR:DELETE *  fromtable_name

DML of SQL