mysql modify data operation

Mysql method of modifying data operations: 1. The insert statement implements inserting data; 2. The update statement implements updating data; 3. The delete statement implements deleting data.

Mysql method of modifying data operations:

The database uses insert, update, and delete methods to modify the table. The records in

• insert statement implements inserting data

• update statement implements updating data

• delete statement implements deletion of data

Insert data without specifying a field nameInsert

Insert without specifying a field name

The code is as follows:

mysql> insert into person values(1,'张三','男',1988);
Query OK, 1 row affected, 1 warning (0.03 sec)

nsert into is followed by the table name, and values ​​is followed by the data that needs to be inserted.

The data in values ​​must match the field name. If the first field is a null value, enter null. The tail does not need to be entered.

It should be noted that the string data must be wrapped in quotation marks

Specify the field name to insert

The code is as follows:

mysql> insert into person(id,name,sex,birth) values(6,'王芳','女',1992);
Query OK, 1 row affected, 1 warning (0.05 sec)

insert into the back Connect the table name and fields. The positions of the fields here can be adjusted

But a necessary condition is that the following values ​​must correspond to their fields

Insert multiple pieces of data at the same time

The code is as follows :

mysql> insert into person(id,name) values(8,'钱名'),(9,'章硕');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

Use multiple brackets after values ​​to insert data, separated by commas

As for the inserted fields, just use the two examples mentioned above to use

to query The results are inserted into the table

The code is as follows:

mysql> insert into person2(id,name,sex,birth) select * from person;
Query OK, 9 rows affected, 6 warnings (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 6

It should be noted here that the number and data type of the inserted fields and the fields in the table must be consistent, otherwise an error will be reported

Copy a table

The code is as follows:

mysql> CREATE TABLE per AS SELECT * FROM person;
Query OK, 1 row affected (0.16 sec)
Records: 1  Duplicates: 0  Warnings: 0

update The table name represents the table to be updated, and the content that needs to be updated is set after set

where is used as a restriction The update condition is followed by an expression. As long as the expression is true, the condition is met.

Tips: where 1 can also represent true, that is, all are satisfied.

Multiple field update

Code As follows:

mysql> update person set name='小红',sex='女' where id=3;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

For multi-field update, you only need to add multiple fields and data to be modified after the set, separated by commas

If you want to update all records, there is no need to add where

tips: Be especially careful when using update, because multiple records may satisfy the where condition

It is best to check one table first to determine the record to be updated

Delete Field

Delete the specified record

The code is as follows:

mysql> delete from person where id=9;
Query OK, 1 row affected (0.02 sec)

Deleting records also needs to follow the where limit

tips: Unless you are very sure where The clause will only delete the rows you want to delete

Otherwise, you should use select to confirm the situation

Delete all records

The code is as follows:

mysql> delete from person;
Query OK, 8 rows affected (0.03 sec)

You can delete all records one by one without specifying where conditions.

In addition, there is also the truncate table statement, which will delete the original table and then re-establish it, which is more efficient

tips: There will be no prompts for deletion here. Just delete it as soon as you say it. It’s very fast. So be extra careful when using it. It’s best to back up the data first.

Related free learning recommendations:

mysql database(Video)

The above is the detailed content of mysql modify data operation. For more information, please follow other related articles on the PHP Chinese website!