8-mysql data manipulation language (DML)

Data Manipulation Language (DML)

SQL (Structure Query Language) language is the core language of the database.

SQL language is divided into four categories: data query language DQL, data manipulation language DML, data definition language DDL, data control language DCL.

DML (data manipulation language) is a data manipulation language, with INSERT, UPDATE, and DELETE as the core instructions, which represent insert, update, and delete respectively, and are mainly used to perform some operations on database data.

Data insertion (add data)

There are 3 forms of inserting data:

形式1:可以一次插入多条数据

The field list part can be omitted (not recommended), at this time the value list part must correspond to each field of the table in turn;

insert into  表名 [(字段名1,字段名2,...)] values (值a1,值a2, ...),(值b1,值b2,...),...;

形式2:可以一次插入多条数据

The data source is obtained by the select statement.

The field list part can be omitted (not recommended), at this time the value list part must correspond to each field of the table in turn;

insert into  表名1 [(字段名1,字段名2,...)] select  字段名1,字段名2,...  from  表名2;

形式3:

insert  into  表名  set  字段名1=值1,字段名2=值2, ...;

注意:

  1. The field name and field value have a "one-to-one correspondence" relationship: quantity, order and type should all correspond.
  2. Every time data is inserted, it must be done in units of "rows", and either a row is inserted successfully or it fails.
  3. For fields that are not given in the statement, a successful insert will inevitably obtain the "default value" according to the table or system settings (sometimes the default value is the null value "null").
  4. The literal values ​​of string type and time and date type usually need to be enclosed in single quotes, such as:'abc','web front end', '2021-4-5 6:7:8"
  5. The form of the value is usually this: the number is written directly, the string and the time are enclosed in single quotes, but if it is a function value, it cannot be enclosed in quotes.
  6. The order of the field names given in the statement may not be the actual order in the table.
  7. Some fields usually do not need to insert data, such as fields with auto_increment attributes, or fields of type timestamp.

Example of inserting data:

Insert picture description here

Similar to insert data statement

载入文本数据:

grammar:

load  data  infile  '文件完整名(含路径)'  into  table  表名;

Function: Import data from an external notepad file to a table. The data in the notepad is usually required to be "comparatively neat", a line of text corresponds to a line of data in the table, and a certain character (usually tab character) is used to separate the value of a field in a line.

Something like this:

Insert picture description here

The syntax of the statement is simply explained as shown in the figure:

Insert picture description here


Insert data example:

Insert picture description here

delete data

Basic grammatical form:

delete from 表名 [where条件] [order排序] [limit限定];

注意:

  1. Deletion is performed "in units of rows", and a row of data is either deleted successfully or failed.
  2. In the delete statement, the where condition is optional, but it is usually required to delete data that meets the condition; if no condition is set, all data will be deleted, which is rarely the case in applications.
  3. The order sorting clause is optional and is usually not needed in applications. It is used to specify deletion of the data in the set order.
  4. The limit clause is optional, and is usually not needed in applications. It is used to limit which rows of data to delete (referring to how many rows from the first row).

Similar to delete data statement truncate

Truncate is used to directly delete the entire table (structure) and re-create the table.

truncate [table] 表名;

At this time, all the data is naturally gone, and the table is restored to the initial state (for example, the current value of auto_increment becomes the initial value, and if you delete all data with delete, the auto_increment value will remain).

  1. Delete the entire table (the data and structure are gone).
  2. Re-create the table (new table).
  3. What is the difference with the delete statement without the where condition?
  4. The main impact is the field value of this type like auto_increment: the runcate result will be recalculated, and the delete can continue to grow.

Example:

Insert picture description here

Translation: Successful execution, 0 lines affected (0.00 seconds)

change the data

Basic syntax:

update 表名 set 字段名1=值表达式1,字段名2=值表达式2,....[where条件] [order排序] [limit限定];

注意:

  1. The modification is carried out in the unit of "row". A row of data is either successfully modified or not modified. Although it can be set to modify only part of the fields in the table, it should also be understood in the unit of "row".
  2. The where condition clause is optional, but it is usually required to modify the data that meets the condition; if no conditions are set, all data will be modified.
  3. The order sorting clause is optional, usually not needed, and is used to specify the modification according to the set data order.
  4. The limit clause is optional, usually not needed. It is used to limit which rows of data to modify (referring to how many rows from the first few rows, for example: the first 500 rows).
  5. The value of the field can be an expression, or a direct value, or a function. If it is a direct value, it also follows the writing of the value in the insert statement.

Modified data example:

Insert picture description here

Copy table

复制表结构:

create table tab2 like tab1;

复制表数据:

insert into tab2 select * from tab1;

复制一个表:

Copy a table completely (including table structure and table data):

create table 新表名 select * from 原表名;

This method may lose some structural information, such as indexes, constraints, and self-growth attributes.