Oracle Foundation (10) DML data operations

I. DML data Manipulation language

Primarily used to retrieve, insert, and modify database information. It is the most commonly used SQL command, such as insert (insert), UPDATE, select (select), delete (remove).

　　1. Insert Insertion Statement:

Grammar:

INSERT into Tablename[column1[,column2 ...]

VALUES (Value1[,value2 ...]);

Description

INSERT INTO: the keyword to insert.

TableName: Represents the table to be inserted.

Column1: Optional parameter that represents the column to be inserted, with multiple columns used, delimited.

VALUES (value1.) : The inserted value, value1 here, must correspond to the preceding column, and if the column is not written, it must be consistent with the structure of the table.

Cases:

--Create a Learner information table and add constraintsCREATE TABLEStuinfo (StunameVARCHAR2( -) not NULL CONSTRAINTPk_namePRIMARY KEY, StupassVARCHAR2( -) not NULL CONSTRAINTCk_passCHECK(LENGTH (Stupass)> 3), Stuage Number(3,0) not NULL CONSTRAINTCk_ageCHECK(Stuage> -), Birthday DATEDEFAULTsysdate)

　　Inserting data into the Learner information table

--insert a record into the student tableINSERT  intostuinfo (stuname,stupass,stuage,birthday)VALUES('Zhang San','123123', -,DEFAULT)--add data for a custom date typeINSERT  intoStuinfoVALUES('John Doe','123456', -, To_date ('1999-9-20','YYYY-MM-DD'))

Here's what to note:

　1) The inserted column must correspond to the inserted value one by one, including the data type, number, and order must be identical.

2) If the data table contains default values, you can use the default keyword to insert defaults.

3) The inserted data must meet the data constraints. Otherwise, the insert fails.

4) inserted columns can be omitted, but data must be inserted in the order of the columns in the table.

5) Complete the addition of date function data through to_date.

6) Insert String type must be used ' included.

　　To insert multiple rows of records:

1. Insert: Into...select ... Generate Custom Data

--Insert multiple rows of data at one time and query through Union keywordINSERT  intostuinfo (stuname,stupass,stuage,birthday)SELECT 'Harry','888888', -, To_date ('1999-8-8','YYYY-MM-DD') fromDualUNIONSELECT 'Zhao Liu','666666', -, To_date ('195-8-20','YYYY-MM-DD') fromDual

Splicing multiple query results one at a time into the database, where the type data queried must be exactly the same as the type and order of data inserted.

2. Insert: Into...select ... Inserting records that already exist in a table into a new table

INSERT  into stuinfo (stuname,stupass,stuage,birthday) SELECT  from Backuser

Inserts the result of the query into an existing table and an error if the table does not exist.

3. Select...into ...

-- generate a new table of data to be queried CREATE TABLE  as SELECT Stuname,stupass,stuage,birthday  from Stuinfo

Insert the result of the query into a new table, the table must not exist, if there is an error.

　　

　　2. UPDATE statement

Grammar:

Update tablename set Column_name=value[,column=value,...]

[Where=condition];

Description

UPDATE: keyword

TableName: Indicates

Column=value: Sets the value of the column, multiple columns are used, separated, and no set is required.

Where Condition: condition, must be a Boolean expression. If you do not have a condition, you should be cautious about all the data in the table.

Cases:

--change the password for Zhao Liu to 8 6, and then change the date of birth to 1989-5-20UPDATEStuinfoSETStupass='666666', Birthday=To_date ('1989-5-20','YYYY-MM-DD')--Insert date requires format conversionWHEREStuname= 'Zhao Liu'                       --specify conditions to modify data, conditions not satisfied do not perform any actionCommit;--submit automatically after execution is complete

Attention:

　　1) The modified data must also satisfy the constraints in the database.

2) modified data if you need to commit a transaction using Comit in Plsql, the data is modified successfully.

3) The modified conditions need to be followed, otherwise, all records in the table will be modified.

　　

　　3. Delete statements

　　1) Delete syntax:

DELETE [from] TableName

[WHERE condition];

Description

Delete [from]: the keyword to delete. The from can be omitted and not written.

TableName: The table to delete data from.

Where Condition: condition to delete data, delete all records in table without writing

Cases:

DELETE WHERE = ' Zhao Liu '  -- Delete User information named ' Zhao Liu ';  Delete stuinfo                        -- deletes all records in the table

　　

　　2) TRUNCATE Table empty tables

Grammar:

TRUNCATE TableName;

Description

Truncate: Clears the keyword.

TableName: To clear the show.

Cases:

TRUNCATE TABLE backuser;  -- emptying the Backuser table

　　The difference between delete and truncate:

A) truncate quickly deletes records and frees up space, does not apply transactions, and therefore cannot be rolled back, and the delete command can be undone by rollback after the delete is performed.

b) Truncate will delete all the records in the table, and delete can delete not only the records in the table, but also some of the data in the table through the Where condition.

Oracle Foundation (10) DML data operations