Play with MYSQL's addition, deletion, modification and query

The dml statement is one of the sql data operation statements. It can add, delete, modify and query the table. This article is very helpful for beginners or people who need to review sql. Follow the editor Let’s learn together.

Before describing how to add, delete, modify, and query the data table, we first create a data table called students to facilitate all subsequent operations, as shown in the following figure:

Insert record

insert into 表名(字段1,字段2, ...,字段n) values(值1,值2,... ,值n);

Among them:

• Table name: It is the data table we operate on. For example, I am operating the table students;

• field: it is the field that needs to be operated;

• value: is the data that needs to be added.

You can add data to our fields by insert into. You don’t need to specify the field name, but the order after the values ​​should be consistent with the field sorting. Both of the following can be used to insert data into the data table. The sql statement and its display results are as follows:

insert intoNot only can you insert one piece of data at a time, but you can also insert multiple records at one time.

insert into 表名(字段1,字段2, ...,字段n) 
values
(值1,值2,... ,值n),
(值1,值2,... ,值n)
;

The sql statement and its output results can be seen in the figure below:

Query data

In this article we only talk about the simplest query.

select * from 表名

Through this statement, we can query records that meet the conditions. * represents all the fields in your query table. If we query a certain field, we only need to change * to that field.

We use select * from when inserting data. We can see the result by looking at the code for inserting data above.

If we want to query the data of the id field, we only need to change * to id.

#This will display the data in the id field.

Update record

Update a table

update 表名 set 字段1=值1,字段2=值2,...,字段n=值n[where条件];

Among them:

• Condition: We update the table requirements that need to be met.

If there are no conditions required for writing updates, all values ​​will be updated. For example:

You can see that all 6 statements have changed. If you don’t want all statements to be updated, we need to write conditions, such as:

Because the condition is written, only the third statement is updated.

Updating data in multiple tables

update 表1,表2,...表n  set 表1.字段1=表达式1,...,表n.字段n=表达式n[where条件];

Multiple table updates are mostly used to dynamically update fields in another table based on one target field. We can create a new data table named stu and put data in it:

With this data table we can update multiple tables, sql statements and their results As follows:

We can see that the contents of both data tables have been updated.

Delete records

delete from 表名 [where 条件];

Deleting records has the same condition as updating records. Without the where condition, all records in the table will be deleted, so be careful when operating.

It’s useless to just say it, let’s look at an example:

Since all data will be deleted without conditions, I won’t demonstrate it here.

Recommended tutorial: mysql video tutorial

The above is the detailed content of Play with MYSQL's addition, deletion, modification and query. For more information, please follow other related articles on the PHP Chinese website!