Write in front: the blogger is a real combat development after training into the cause of the “hill pig”, nickname from the cartoon “Lion King” in “Peng Peng”, always optimistic, positive attitude towards things around. My technical path from Java full stack engineer all the way to big data development, data mining field, now there are small achievements, I would like to share with you what I have learned in the past, I hope to help you on the way of learning. At the same time, the blogger also wants to build a perfect technical library through this attempt. Any anomalies, errors and matters needing attention related to the technical points of the article will be listed at the end, and everyone is welcome to provide materials in various ways.

  • Please criticize any mistakes in the article and revise them in time.
  • If you have any questions you would like to discuss or learn, please contact me at [email protected].
  • The style of the published article varies from column to column, and all are self-contained. Please correct the deficiencies.

Data manipulation language – DML

Keywords: database, data manipulation language, DML\

The article directories

Before we have understood the classification of SQL language, can be divided into: DDL (data definition language), DML (data manipulation language), DQL (data query language), DCL (data control language), TPL (transaction processing language), CCL (pointer control language), this article will introduce DML.

A, the INSERT

The INSERT statement is used to INSERT data into a data table. We need to pay attention to the matching of data types and the order in which data is inserted.

1. Full field insertion

INSERT INTO all columns of a table according to the table structure. In this case, you can use the INSERT INTO statement to omit the column name. Before inserting, you can check the table structure or run the DESC command to check the table structure.

DESC Student;
Copy the code



When doing full field inserts, we need to adjust the order in which the data is inserted exactly to the order in which the fields were predefined when defining the data table. The database verifies the data type only when inserting data.

INSERT INTO Student VALUES('001103'.'js0001'.'Xiao Ming Wang'.'male'.'1981-10-07 00:00:00');
Copy the code

2. Specify field insertion

If field insertion is specified, data can be inserted into only a few columns of the data table. Other columns are filled with default values or NULL values. Note that columns that do not have data inserted have default values or are allowed to be NULL. Because we are inserting certain columns in the table, the database itself cannot determine which columns we are inserting data into, so it is important to specify the target column after the table name and the order of the VALUES in VALUES is the same as the order of the columns.

INSERT INTO Student(S_no,Class_no,S_name) VALUES('001103'.'js0001'.'Xiao Ming Wang');
Copy the code

3. Insert multiple pieces of data simultaneously

If you want to insert multiple VALUES into a table, separate VALUES with commas.

INSERT INTO 
Student(S_no,Class_no,S_name) 
VALUES
('001103'.'js0001'.'Xiao Ming Wang'),
('001104'.'js0002'.'Zhang Xiaoming'),
('001105'.'js0003'.'Li Xiaoming');
Copy the code

4. Insert the query result directly

If you want to copy data from a table to a table other than the name, you can use the INSERT INTO SELECT statement. The premise is that the two tables have similar structure, matching data type and compatible data length, that is, the queried data can be put into the target data table.

INSERT INTO'Target table'SELECT * FROM'Source table';Copy the code
INSERT INTO'Target table' (' column name '...)SELECT` column `,...FROM'Source table';Copy the code

Second, the DELETE

1. Delete data

Be especially careful when using DELETE, because without any conditions, the data in the data table is cleared.

DELETE FROM` table name `;Copy the code

2. Difference between DELETE and DROP

DROP (portal address:Data definition Language – DDL), which is confusing at first, but actually easy to understand. DROP drops the table structure. If the table structure is dropped, the data is also dropped. DELETE simply deletes the data, while the table structure remains.

3. Filter and delete

Delete operations can only be performed in units of action (UPDATE is required if a column is to be emptied), by adding a WHERE clause, using the same rules as when querying, through the portal: database single table query – Simple filter query.

DELETE FROM` ` table nameWHEREScreening conditions;Copy the code

Third, the UPDATE

1. Full update

Similar to DELETE, when no filter is added, the entire table is updated. Multiple columns can be updated simultaneously, separated by commas, using the assignment operator (=).

UPDATE 'table name'SET` column `=Value or expression, 'column name'=A value or expression;Copy the code

2. Select Update

The WHERE clause allows you to add filtering criteria to update the data that matches the criteria.

UPDATE 'table name'SET` column `=Value or expression, 'column name'=Value or expressionWHEREScreening conditions;Copy the code