MySQL Database theory base seven-insert, DELETE, update the statement

First, Introduction

developed by MySQL AB, is the most popular open source SQL database management system, the main features:

• 1, is a database management system

• 2, is an association database management system

• 3, is an open source software, and there are a large number of available shared MySQL software

• 4, MySQL database server is fast, reliable and easy to use features

• 5. mysql server works in client/server mode, or embedded system

• 
  

• The InnoDB storage engine saves the InnoDB table in a table space, which can be created by several files. In this way, the size of the table can exceed the maximum capacity of the individual file. Table spaces can include raw disk partitions, making large tables possible. The maximum capacity of a tablespace is 64TB.

• 
  

Second, INSERT

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

Look at the last piece of data: Sort by the ID field in descending order, and take the first data, which is the last data.

SELECT * from class ORDER by ID DESC LIMIT 1;

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 > 10;

Usage: from the students table, find the age-greater-than-20 names of older sex data inserted into the class table.

INSERT into Class (Name,age,gender) SELECT Name,age,gender from students WHERE age > 20;

Third, DELETE

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

Iv. UPDATE

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

V. LOCK LOCK 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

---end-9---

This article is from the "Wind No Trace" blog, please be sure to keep this source http://wangfx.blog.51cto.com/1697877/1933959

MySQL Database theory base seven-insert, DELETE, update the statement