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

Foreword:

In the last article, we mainly introduced the usage of DDL sentence, which may have been discovered by careful students. This article will mainly focus on DML statements to explain table data-related operations.

It is explained here that the classification of DDL and DML sentences may not be clear to some students.

DDL (Data Definition Language): data definition language for creating, deleting, modifying, library or table structures, and structural operations on databases or tables. Common ones are create,alter,drop and so on.

DML (Data Manipulation Language): data manipulation language, mainly to update table records (add, delete, change). Common ones are insert,update,delete and so on.

1. Insert data

Insert syntax is mainly used to insert data, and many options are also given in the official documentation:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name [, partition_name]...)] [(col_name [, col_name]...)] {VALUES | VALUE} (value_list) [, (value_list)]. [ON DUPLICATE KEY UPDATE assignment_list] INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name [, partition_name]...)] SET assignment_list [ON DUPLICATE KEY UPDATE assignment_list] INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name [, partition_name]...)] [(col_name [, col_name]...)] SELECT... [ON DUPLICATE KEY UPDATE assignment_list] value: {expr | DEFAULT} value_list: value [, value]... assignment: col_name = valueassignment_list: assignment [, assignment].

Students who are interested can consult and study the above options. Below, I will introduce you to some commonly used grammars.

INSERT INTO... VALUES (...)

This is probably the most common way for you to write insert statements, and the standard usage is:

INSERT INTO [,... ]] VALUES (value 1) [… , (value n)]; # insert multiple lines of INSERT INTO table (column1,column2...) VALUES (value1,value2,...), (value1,value2,...),...

The syntax is as follows:

Specifies the name of the table being manipulated Specifies the name of the column in which the data needs to be inserted If you insert data into all the columns in the table, all column names can be omitted and directly use INSERTVALUES (...) That's it. VALUES or VALUE clause: this clause contains the list of data to insert. The order of the data in the data list corresponds to the order of the columns.

INSERT... SET...

Insert... The set statement can insert only one piece of data at a time, and you can insert the values of some columns into the table, which is more flexible.

INSERT INTO SET =, =,... # where INTO can be omitted

INSERT INTO... SELECT...

INSERT INTO... SELECT... The FROM statement is used to quickly pull data from one or more tables and insert that data into another table as row data.

The SELECT clause returns a result set of a query, and the INSERT statement inserts the result set into the specified table. The number of fields in each row of data in the result set and the data type of the fields must be exactly the same as the table being manipulated.

For example, if the structure of the test table and the test_ Bak table are exactly the same, and we want to insert the data from the test table into the test_ Bak table, then we can do this:

INSERT INTO test_bak select * from test

INSERT... ON DUPLICATE KEY UPDATE

If the new row to be inserted violates the primary key (PRIMARY KEY) or UNIQUE constraint, MySQL reports an error, and this syntax is designed to resolve this error. When a record exists in the database, executing this statement updates it, and when it does not exist, it is inserted.

Here is an example to demonstrate the effect:

# suppose the student table structure and raw data are as follows: CREATE TABLE `student` (`xuehao` int (11) primary key, `name` varchar (255) DEFAULT NULL, `age` int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql > select * from student +-+ | xuehao | name | age | +-+ | 1001 | aaa | 18 | 1002 | bbb | 19 | 1003 | ccc | 20 | +-+ # for example, we want to insert this data When MySQL finds that the primary key is duplicated, it executes the following update statement, insert into student (xuehao,name,age) values (1003 on DUPLICATE KEY UPDATE age cccccccccccjpdpl 19). # after execution, it is found that the data becomes like mysql > select * from student +-+ | xuehao | name | age | +-+ | 1001 | aaa | 18 | 1002 | bbb | 19 | 1003 | ccc | 19 | +-+ # that is, the previous statement is equivalent to executing update student set age = 19 where xuehao = 1003

REPLACE INTO... VALUES...

The function of replace into is similar to that of insert, except that replace into first tries to insert data into the table. If you find that there is already this row of data in the table (based on the primary key or unique index), delete the row first, and then insert the new data. two。 Otherwise, insert the new data directly.

Let me also give you an example:

# still the student table above. Xuehao is the primary key. The original data is mysql > select * from student. +-+ | xuehao | name | age | +-+ | 1001 | aaa | 18 | 1002 | bbb | 19 | 1003 | ccc | 19 | +-# if replace into student values is executed (1003) # then the new table data is mysql > select * from student +-+ | xuehao | name | age | +-+ | 1001 | aaa | 18 | 1002 | bbb | 19 | 1003 | ccc | 17 | +-+ # effect is equivalent to deleting the row whose xuehao is 1003, and then inserting new line 2. Update data

The update statement is used to update table data. The officially recommended syntax is:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET assignment_list [WHERE where_condition] [ORDER BY...] [LIMIT row_count] value: {expr | DEFAULT} assignment: col_name = valueassignment_list: assignment [, assignment].

Similarly, I will only introduce you to the commonly used single table update syntax:

UPDATE SET field 1 = value 1 [, field 2 = value 2... [WHERE clause] [ORDER BY clause] [LIMIT clause]

The syntax is as follows:

Used to specify the name of the table to update SET clause: used to specify the column name to be modified in the table and its column value. Where each specified column value can be an expression or a default value for that column. If the default value is specified, the DEFAULT keyword is available to indicate the column value. WHERE clause: optional. Used to limit the rows in the table to be modified. If not specified, all rows in the table are modified. ORDER BY clause: optional. Used to limit the order in which rows in the table are modified. LIMIT clause: optional. Used to limit the number of rows to be modified. 3. Delete data

The delete statement is used to delete table data. The official document recommends the syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [PARTITION (partition_name [, partition_name]...)] [WHERE where_condition] [ORDER BY...] [LIMIT row_count]

Use the DELETE statement to delete data from a single table in the syntax format:

DELETE FROM [WHERE clause] [ORDER BY clause] [LIMIT clause]

The syntax is as follows:

Specifies the name of the table to delete the data ORDER BY clause: optional. When you delete, the rows in the table are deleted in the order specified in the clause. WHERE clause: optional. Indicates that the deletion condition is qualified for the delete operation, and if the clause is omitted, all rows in the table are deleted. LIMIT clause: optional. Used to tell the server the maximum value of the deleted row before the control command is returned to the client.

Summary:

This paper mainly introduces the syntax of three kinds of DML statements, which seems simple, but in fact, various options are still very complex, especially insert statements, there are many options that are often used. I would also like to remind you that you must be careful when executing update or delete statements. If you do not use where conditions, all data will be updated or deleted.

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.