Usage of Update and Insert statements in MySQL

We can use the Update and Insert commands to Update and save mysql Data. Next I will introduce the usage and differences between Update and Insert, for your reference.

MySQL Update data Update statement

Definition of update statement:

The UPDATE syntax uses the new value to UPDATE columns in the original table rows. Let's take a look at the standard definition of the update statement, 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 the columns to be modified and the values to be given. The where clause specifies the rows to be updated. If the where clause of update is omitted, the first single row in the table will be affected by the update statement. This is very dangerous, and the consequence is that you are crying, not only are you not praised for working overtime, but you are always white-eyed. What is often said in the header is "Hard Disk price, data is priceless", truth.

Update statement example:

To briefly describe the results, we still use the table structure that we used in the previous description of 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 username xiaoxiaozi in the existing database to simaopig. The SQL statement is as follows:

The Code is as follows:	Copy code
Update links set name = 'simaopig 'where name = 'xiaoxiaozi'

If you change all the link addresses in the database to hzhti, the following SQL statement is used:

The Code is as follows:	Copy code
Update links set url = 'HTTP: // www.hzhuti.com ';

The update statement can also execute calculations or call functions:

This is the same as the insert statement. You can use the update statement to execute calculations or call functions, and then update the results of these operations. This example is very good. When we have installed the mysql database, the root user usually has no password. In this case, we can use the following statement to set the password for the root user to 123456.

 

The Code is as follows:	Copy code
Update user set Password = password ('000000') where User = 'root ';

MySQL Insert statement

Insert statement Definition:

INSERT is used to INSERT a new row into an existing table. The INSERT... VALUES statement inserts rows based on the specified VALUES. Let's take a look at the standard definition of the insert statement, 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 syntax is very simple, according to the meaning of English words can be directly translated: insert Table Name (Field 1, Field 2) values (Field 1 value, value of Field 2 );

Insert statement example:

To briefly describe the results, we will create a Mysql DATA table with the following structure to facilitate the following examples:

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 is set to xiaoxiaozi, url is set to http://www.bKjia. c0m can use the following syntax

The Code is as follows:	Copy code
Insert into links (name, url) values ('xiaoxiaozi', 'HTTP: // www. bKjia. c0m ');

After the data is inserted, we can use the select * from links; statement to check whether the data has been successfully inserted.

Example of field names omitted by the insert statement:

When using the insert statement, we can omit the field name. In this case, we need to insert data into the database according to the Field Sequence defined by the database. The table structure in the preceding example defines the name and then the field url.

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

The Code is as follows:	Copy code
Insert into links values ('xiaoxiaozi', 'HTTP: // www. bKjia. c0m ');

Insert statements insert multiple data records at a time:

What if we want to insert multiple data entries into the database at one time? Must I write multiple statements? Certainly not, because MySQL is designed very human. It provides a non-standard format of the insert statement, that is, values (field value 1, field value 2, field value 3), (value of another field 1, value of another field 2, value of another field 3 );

The Code is as follows:	Copy code
# Insert two pieces of data at the same time. Check the syntax description. I have omitted the. Insert links (name, url) values ('xiaoxiaozi', 'HTTP: // www. bKjia. c0m'), ('xiaoxiaozi', 'HTTP: // www. bKjia. c0m ');

Insert statements insert data using the set method of the update statement:

Mysql also provides another method to insert data. The insert statement is also used, but the syntax is not standard. You can understand it as a shanzhai. Haha. MySQL allows the insert statement to insert data using the update set structure:

 

The Code is as follows:	Copy code
# Using the insert set structure to insert data Insert into links set name = 'xiaoxiaozi', url = 'HTTP: // www. bKjia. c0m ';

Description of the insert statement:

In fact, it is not an example in the book. It is just a lazy knock, and it doesn't make much sense. It all needs to be understood by everyone. The same is true if you do not give an example.

1. this is because the field can have a DEFAULT value when I create a table structure in the log. MySQL 4.0.3 and later support a DEFAULT keyword. When we use the insert statement, the field value can be equal to the DEFAULT keyword to make it equal to the default value when the database is created.

2. AUTOINCREMENT auto-increment field. We do not need to provide a value because the system will automatically auto-increment this field. However, if you want to, you can also pass the value, depending on your mood.

3. UNIQUE, which we also said, is the UNIQUE meaning of the field. For example, if the user id is set to UNIQUE, a data with the user id of 1 already exists, if you want to insert another data with a user ID of 1 at this time, the system will fail.

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