How to modify data in MySQL notes

The database inserts, updates, and deletes records in the table.

InsertStatement to insert data

UpdateStatement to update data

DeleteStatement to delete data

Reference Table

Insert data without specifying the field name

Insert field name not specified
Copy codeThe Code is as follows:
Mysql> insert into person values (1, 'zhang san', 'mal', 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 to be inserted

The data in values must match the field name. If the first field is null, the input is null.

Note that string data must be enclosed in quotation marks.

Insert field name
Copy codeThe Code is as follows:
Mysql> insert into person (id, name, sex, birth) values (6, 'wang fang', 'female, 1992 );
Query OK, 1 row affected, 1 warning (0.05 sec)

Insert into is followed by the table name and field. The field here can be adjusted.

However, a necessary condition is that the value of the value following must correspond to its field.

Insert multiple data records at the same time
Copy codeThe Code is as follows:
Mysql> insert into person (id, name) values (8, 'Qian name'), (9, 'zhangshuo ');
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0

Insert data with multiple parentheses after values and separate them with commas (,).

As for the inserted field, you only need to use it in combination with the two examples mentioned above.

Insert query results into the table
Copy codeThe 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

Note that the number of inserted fields and the number of fields in the table must be consistent with the data type; otherwise, an error is reported.

Copy a table
Copy codeThe 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 + Table name indicates the table to be updated. The content to be updated is set after set.

Where is used to restrict Update Conditions, followed by an expression. If the expression is true, the conditions are met.

Tips:Where 1 can also represent true, that is, all satisfy

Multi-Field update
Copy codeThe Code is as follows:
Mysql> update person set name = 'red', sex = 'female '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, which are separated by commas (,).

If you want to update all records, you do not need to add where

Tips:Be careful when using update, because multiple records may meet the where condition.

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

Delete Field
Delete a specified record
Copy codeThe Code is as follows:
Mysql> delete from person where id = 9;
Query OK, 1 row affected (0.02 sec)

You must keep up with the where limitation when deleting a record.

Tips:Unless you are very sure that the where clause will only delete the row you want to delete

Otherwise, select should be used to confirm the situation.

Delete all records
Copy codeThe 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 a truncate table statement, which will delete the original table and re-create it, which is more efficient.

Tips:There will be no prompt for deletion here. It is very fast to delete the deletion.

So be careful when using it. You 'd better back up the data first.