Linux command: part 7 of MySQL series-- exercises related to INSERT, DELET and UPDATE sentences

I. INSERT insert statement

1. Insert field data in bulk

INSERT INTO tb_name (col1,col2,...) VALUES (val1,val2,...), (val1,val2,...)

Usage:INSERT INTO class (Name,Age,Gender) VALUES (stu1,Age1,Gender1), (stu2,Age2,Gender2)

When inserting data, you should be aware of the following:

Character type: to be enclosed in single quotation marks

Numeric: no quotation marks required

Date and time type: no quotation marks are required

Null value: must be written as NULL

2. Insert one piece of data at a time

INSERT INTO tb_name SET col1='values',col2='values'

Usage: INSERT INTO class SET Name='stu1',Age=23,Gender='M'

View the last piece of data:

SELECT * FROM class ORDER BY ID DESC LIMIT 1; sort by the descending order of the ID field, and take the first data, that is, the last data.

3. Insert the queried data into the specified table (the corresponding data fields had better be consistent, otherwise the data is not uniform)

INSERT INTO tb_name (col1,col2,col3) SELECT col1,col2,col3 FROM tb_name1 WHERE AGE > 20

Usage: INSERT INTO class (Name,Age,Gender) SELECT Name,Age,Gender FROM students

WHERE Age > 20; data on the age and sex of names with Age greater than 20 found in the students table are inserted into the class table.

II. DELETE delete statement

Format: DELETE FROM tb_name WHERE conditon; removes the option that meets the criteria in the tb_ name table

TRUNCATE tb_name empties the table and resets the AUTOINCREMENT counter

Usage: DELETE FROM students; deletes all data in the students table, but the automatically growing field data is not deleted

TRUNCATE students deletes the students table and its automatically growing field data

III. UPDATE update statement

Format: UPDATE tb_name SET col1=...,col2=... WHERE...

IV. Lock lock statement

Format: lock tables tb_name read | write; locks the tb_name table. The level is read read lock, which means it can be read.

Write write lock, indicating that you can read and write, but cannot delete the table

Unlock tables unlock