MySQL update and INSERT statement usage detailed

MySQL Updates Data UPDATE statement

Definition of the UPDATE statement:

The update syntax can update columns in existing table rows with new values. Let's take a look at the definition of the UPDATE statement standard, which can be omitted in []:

The code is as follows	Copy Code
UPDATE [low_priority] [IGNORE] Tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition] [Order BY ...] [LIMIT Row_count]

The SET clause indicates which columns to modify and what values to give. The WHERE clause specifies which rows should be updated. If you omit the WHERE clause of the update, the first individual row in the table will be affected by the UPDATE statement, which is dangerous, and the consequence is that you want to cry without tears, overtime instead of being praised instead of everywhere. The head often says the word is "hard disk has the price, the data is priceless", the truth.

UPDATE Statement Example:

To illustrate the effect, we still use the table structure we used when we explained the INSERT statement:

The code is as follows	Copy Code
CREATE table links (name varchar (255) NOT null default ", URL varchar (255) NOT NULL default");

Change the name of the user named Xiaoxiaozi in the existing database to Simaopig,sql as follows:

The code is as follows	Copy Code
Update links set name= ' Simaopig ' where name= ' Xiaoxiaozi '

To change all the link addresses in the database to Hzhuti, use the following sql:

The code is as follows	Copy Code
Update links set url= ' http://www.111cn.net ';

The UPDATE statement can also perform calculations or call functions:

This is the same as the INSERT statement, and you can use the UPDATE statement to perform a calculation or call a function, and then use the results of these operations to update. This example is well given that when we install the MySQL database, the root user usually does not have the password, at this time we can use the following statement for root user set password: 123456

The code is as follows	Copy Code
Update user Set Password = Password (' 123456 ') where user = ' root ';

MySQL Inserts data INSERT statement

Definition OF INSERT statement:

Insert is used to insert new rows into an existing table. INSERT ... The values statement inserts a row based on an explicitly specified value. Let's take a look at the definition of the INSERT statement standard, which can be omitted in []:

The code is as follows	Copy Code
INSERT [Low_priority | Delayed | High_priority] [IGNORE] [Into] tbl_name [(Col_name,...)] VALUES ({expr | DEFAULT},...), (...),... [on DUPLICATE KEY UPDATE col_name=expr, ...]

This grammar is very simple, according to the meaning of English words can be directly translated: Insert Table name (Field 1, Field 2) values (the value of the field 1, the value of the field 2);

Insert Statement Example:

To illustrate the effect, let's create a MySQL datasheet with the following structure to facilitate some of the examples that follow:

The code is as follows	Copy Code
CREATE table links (name varchar (255) NOT null default ", URL varchar (255) NOT NULL default");

Insert a piece of data, name set to Xiaoxiaozi,url set to Http://www.111cn.net can use the following syntax

The code is as follows	Copy Code
Insert into links (name,url) VALUES (' Xiaoxiaozi ', ' http://www.111cn.net ');

After inserting the data, we can use the SELECT * FROM links statement to query to see if the data has been inserted successfully.

Insert statement Omit field name example:

When we use the INSERT statement, we can omit the field name, and then we need to insert the data into the database according to the order of the fields defined by the database. As the table structure of the example above, define the name first, and then define the field URL

We can insert the same data as the previous example using the following code:

The code is as follows	Copy Code
Insert into links values (' Xiaoxiaozi ', ' http://www.111cn.net ');

INSERT statement inserts more than one data at a time:

What if we want to insert more than one data into the database at once? Are you sure you want to write more than one statement? Certainly not, because the MySQL design is still very human. It provides a non-standard format for INSERT statements, that is, values (field value 1, field value 2, field value 3), (the value of another field 1, the value of another field 2, the value of another field 3);

The code is as follows	Copy Code
# Insert two data at the same time, look at the syntax description, that into was omitted by me Insert links (Name,url) VALUES (' Xiaoxiaozi ', ' http://www.111cn.net '), (' Xiaoxiaozi ', ' http://www.111cn.net ');

The INSERT statement inserts data using the Set method of the UPDATE statement:

MySQL also provides another way to insert data, but also to use the INSERT statement, but the syntax is non-standard, you can understand the cottage. Oh, the cottage martial. MySQL allows us to have the INSERT statement use the update's set structure to insert the data:

The code is as follows	Copy Code
# Insert data using INSERT set structure Insert into links set name= ' Xiaoxiaozi ', url= ' http://www.111cn.net ';

For A few notes on INSERT statements:

In fact, is not what the description, are the examples above, but lazy knock, and feel that there is no great significance, are needed to understand. Give no example is the same.

1. Because before I introduced the creation of table structure in the log, the field can have default value, in MySQL4.0.3 and later versions all support a default keyword, when we use INSERT statement, we can make the value of the field equal to the default keyword, To make it equal to the default value when the database was created.

2. AutoIncrement field, this we do not have to give a value, because the system will automatically for the field to increase, but if you want to, but also can pass the value, see your mood.

3. Unique This we also said that is the only meaning of the field, such as the user ID set unique, there is already a user ID 1 data, if you want to insert a user ID 1 data is not successful, the system will be wrong.

4. If the database field allows null values, we can also set the field value to NULL in the INSERT INSERT statement.