DML Data Manipulation language

I. DML data manipulation language (DQL Select)

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: 　　Syntax : 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 constraintsCREATETABLEStuinfo (stunameVARCHAR2 (20)NotNullCONSTRAINT Pk_namePRIMARYKEY, StupassVARCHAR2 (20)not null constraint Ck_ Pass check (LENGTH (stupass) > 3),   stuage number (3,not null constraint ck_age CHECK (Stuage>18),   birthday DATE default Sysdate) inserting data into the Learner information table --Insert a record into the student tableINSERTIntoStuinfo (Stuname,stupass,stuage,birthday)VALUES (‘Tom‘,‘123123 ", 20,) -- Add data for a custom date type insert into Stuinfo values ( John Doe " 123456 ', 25,to_date ( 1999-9-20 ", 　　Precautions: 　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 keywordINSERTIntoStuinfo (Stuname,stupass,stuage,birthday)SELECT‘Harry‘,‘888888‘,20,to_date ( ' 1999-8-8 ' 25,to_date ( 195-8-20 ", from dual 　　　　　　　　　　　　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 name,pass,age,sysdate 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 for the data you are querying CREATE TABLE backuser 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 (UPDATE) Grammar: UPDATE table Set column Name 1 = ' value ', column name 2 = ' value ' ... Where condition; Example: --Change the password for Zhao Liu to 8 6, then change the date of birth to 1989-5-20UPDATEStuinfoSET Stupass= ' 666666 ,birthday=to_date ( 1989-5-20 ", --where stuname = ' Zhao Liu -- Specifies the condition that modifies the data, the condition does not meet any action commit; -- commit automatically after execution 　 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 table name where column name = ' Value a ';--delete row of data with column named a Delete table name;--Deletes all information in this 　　 　　2) TRUNCATE Table empty tables Grammar: TRUNCATE TableName; Description Truncate: Clears the keyword. TableName: To clear the show. Cases: TRUNCATE TABLE es_product; --Empty All the information in the Es_product 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.

DML Data Manipulation language