MySQL Data manipulation

• An Introduction
• Two Insert data inserts
• Three Update operations update
• Quad Delete operation Delete
• Five query Operations SELECT

An introduction

In MySQL management software, you can use the DML language in SQL statements to implement data manipulation, including

1. Inserting data by using insert
2. Update to implement data updates
3. Using Delete to implement data deletion
4. Use Select to query the data.

Two Insert data inserts

#语法一: Inserting by field insert into table (field 1, field 2 ...) VALUES (value 1, value 2 ...); #语法二: Inserts insert into table by field order values (value 1, value 2 ...); #语法三: Inserting multiple records inserts into table values (value 1, value 2 ...), (value 1, value 2 ...)  , (value 1, value 2 ...), #语法四: Inserting query results insert into table (field 1, Field 2 ...)  Select field 1, Field 2 ... from table;    

Four ways to insert data: I. Inserting a piece of dataInsert  intoStudent (Id,name,age,sex,salary)Values(1,'Piglet', -,'male',2500); ii. inserting multiple dataInsert  intoStudent (Id,name,age,sex,salary)Values(1,'Piglet', -,'male',2500) ,(2,'Piglet 2', -,'male',2500),(3,'Piglet 3', -,'male',2500); iii. Direct InsertionInsert  intoStudentValues(1,'Piglet', -,'male',2500;p S: If the number and position of the inserted data match exactly the number of fields and the position of the table, you can save the field definition after the token name four. Query and insertInsert  intoStudent (Id,name,age)SelectId,name,age fromTB;p S: Querying data from the TB table and inserting it into the student table

code ExampleThree Update operations update

Way One:UpdateStudentSetName= 'Brother Pig'the values for all the name fields in the;p s:student table are all updated to'Brother Pig'Way Two:UpdateStudentSetName= 'Brother Pig', age= -  whereId= 2;p S: Update the value of the name and age fields in the student table and update only the ID=A record of 2

code Example

Quad Delete operation Delete

Way One: Delete  from Student;ps: Delete all data in the student table, note: If there is a self-increment primary key, the value of the primary key record is not deleted. Mode two: Delete  from where id=1;p s: only data with ID 1 is deleted. Way three:truncate  student;ps: Clear Table

code Example

What is the difference between truncate and delete? [Face question]

1,TRUNCATE  on various tables whether large or small are very fast. The delete operation is affected by the amount of data in the table and its execution efficiency. 2 , truncate is a DDL language and delete is a DML statement, and as with all other DDL languages, he will be implicitly committed and cannot use the rollback command with truncate.  3, truncate can not trigger trigger, DELETE will trigger trigger.  4. When the table is emptied, the index and the self-increment primary key of the table and table are reset to the initial size, and delete cannot.

Five query Operations SELECT

Depending on the query function, we can divide the query into the following categories:

1. Single-table query

For details, see: http://www.cnblogs.com/wangfengming/articles/8064956.html

2. Multi-Table Query

For details, see: http://www.cnblogs.com/wangfengming/articles/8067220.html

MySQL Data manipulation