mysql-Data Manipulation Language (DML)

mysql- Data Manipulation Language (DML)

1. Data insertion (add data)

(1) There are 3 different forms

① Form 1:

Insert into table name (field name 1, field Name 2, ...). Values (value A1, value A2, ...), (Value B1, value B2, ...), ..... ；

② Form 2:

INSERT into Table name 1 (field name 1, field Name 2, ...). ) Select Field Name 1, field Name 2, .... from table name 2;

③ Form 3:

Insert into table name set field name 1= value 1, field name 2= value 2, ... ；

(2) Explanatory notes:

① Form 1 and Form 2, can be inserted more than one data at a time;

② regardless of the form, there is a "one by one correspondence" relationship between "field" and "value".

The form of the ③ value, usually: The number is written directly, the string and time are enclosed in single quotes, but if the function value is not quoted

④ concept problem: Regardless of that form, it should be understood as: the unit of inserting data is "line";

⑤ fields usually do not need to insert data, the field name should not appear at this time: auto_increment, timestamp,

(3) Other statements similar to inserting data:

① loading external "form-neat" data:

Load data infile ' file full name (with path) ' into table table name;

② Copy the structure and data of a table:

CREATE TABLE Table Name 1 SELECT * from table Name 2;

2. Delete data

1. Basic grammatical form:

Delete from table name [WHERE condition] [order sort] [limit limit];

2. Explanatory notes:

① Concept problem: Deletion should also be understood as the "Act unit".

In the ② DELETE statement, the Where condition is usually written, because if you do not write, all the data is deleted, rarely in the application.

③order sort clauses and limit-qualified clauses, which are not normally required in the application.

The ③.①order sort clause is used to set the order in which data is deleted.

The ③.②limit limit clause is used to restrict the deletion of certain rows in the specified order.

3, similar to delete statement truncate:

The table name of the truncate [table], which is used to directly delete the entire table (structure) and recreate the table.

① Delete entire table (none of the data and structure)

② re-create the table (the new table).

What is the difference between a ③ and a DELETE statement without a where condition?

The main impact is similar to: auto_increment This type of field value:

Truncate results are recalculated, and delete can continue to grow.

3. Modify the data

(1) Basic syntax:

Update table name set field name 1= Value expression 1, field name 2= value Expression 2, .... [Where Condition] [Order Sort] [Limit limit];

(2) Syntax description

① concept problem: It is still understood that the update is in "line" units, although you can specify that only some of these fields be updated.

②where conditional clauses, which are almost always required in the application, are likely to lose meaning, like delete

③order sort clauses are not typically required to specify the order of updates for the data that will be updated.

④LIMIT qualifying clauses are not normally required to update partial (partial) data in the specified order of the data to be updated, such as the first 500 rows.

The value of the ⑤ field can be an expression, or a direct value, or a function that, if it is a direct value, also follows the notation of the value in the INSERT statement.

4. One problem: copying tables

(1) Copy table Method 1:

CREATE table tab2 like tab1; Copy structure.

INSERT INTO TAB2 select * from Tab1; Copying data

This method can be more complete.

(2) Copy table Method 2:

CREATE TABLE TAB2 select * from Tab1; Copy structure and data at the same time

This approach may throw some structural information, such as: indexes, constraints, self-growth properties

mysql-Data Manipulation Language (DML)