Linux command: MySQL series of seven--insert, delet, UPDATE statement related exercises

One, insert insertion statement

1. BULK INSERT field data

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, be aware of the following points:

Character type: Enclosed in single quotation marks

Numeric type: No quotation marks required

DateTime 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 ';

To view the last piece of data:
SELECT * from class ORDER by ID DESC LIMIT 1; The first data, which is the last data, is sorted by the ID field in descending order.

3, with the query to insert the data into the specified table (the corresponding data field is best 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; From the students table, find the age-greater-than-20 names of older sex data inserted into the class table.

Second, delete DELETE statement

format: Delete from Tb_name WHERE conditon; remove options that meet the criteria in the Tb_name table

TRUNCATE tb_name emptying the table and resetting the AutoIncrement counter

Usage:delete * from students; Delete all data from the students table, but the Automatically growing field data is not deleted

TRUNCATE Students Delete Students table and its auto-growing field data

III. UPDATE statement

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

Four, Lock locking statement

Format: Lock tables Tb_name read|write; Locks the Tb_name table, the level is read lock, indicates can read

Write lock, which indicates that the table can be read and written but not deleted

Unlock tables Unlock

This article is from the "Learn Linux history" blog, please be sure to keep this source http://woyaoxuelinux.blog.51cto.com/5663865/1923227

Linux command: MySQL series of seven--insert, delet, UPDATE statement related exercises