How to modify data in MySQL notes _ MySQL

Source: Internet
Author: User
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

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

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

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

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

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

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

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

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.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.