Shulou(Shulou.com)06/01 Report--

The following brings you about how to insert, delete, update statements in the Mysql database, if you are interested, let's take a look at this article. I believe it is more or less helpful for you to read how to insert, delete and update statements in the Mysql database.

I. brief introduction

Developed by MySQL AB, it is the most popular open source SQL database management system with the following main features:

1. It is a database management system.

2. It is an associated database management system.

3. It is a kind of open source software, and there are a lot of shared MySQL software available

4. MySQL database cloud servers are fast, reliable and easy to use.

5. MySQL CVM works in client / CVM mode, or in embedded system

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

II. INSERT

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: first sort by the descending order of the ID field, and take the first data, that is, the last data.

SELECT * FROM class ORDER BY ID DESC LIMIT 1

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

Usage: data on the age and gender of names with Age greater than 20 found in the students table are inserted into the class table.

INSERT INTO class (Name,Age,Gender) SELECT Name,Age,Gender FROM students WHERE Age > 20

III. DELETE

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

IV. UPDATE

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

5. 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

Read the details of the above about how to insert, delete and update statements in the Mysql database, and whether you have gained anything. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.