Shulou(Shulou.com)06/01 Report--

How does mysql modify data operations? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Mysql modifies the data operation method: 1, insert statement to insert data; 2, update statement to update data; 3, delete statement to delete data.

Mysql's method of modifying data operations:

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

Insert statement to insert data

Update statement to update data

Delete data by delete statement

Insert data insert without specifying field name

Insert without specifying field name

The code is as follows:

Mysql > insert into person values (1 mysql 'Zhang San', 'male', 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. Enter null if the first field is empty, but not at the end.

It is important to note that string data must be wrapped in quotation marks

Specify field name insertion

The code is as follows:

Mysql > insert into person (id,name,sex,birth) values (6pm 'Wang Fang', 'female', 1992); Query OK, 1 row affected, 1 warning (0.05sec)

Insert into is followed by a table name and fields, where the fields can be repositioned

But a necessary condition is that the subsequent 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 Duplicates' money name'), (9 rows affected 'Zhang Shuo'); Query OK, 2 Duplicates: 0 Warnings: 0

After values, insert data with multiple parentheses, separated by commas.

As for the inserted fields, you only need to use the two examples mentioned above.

Insert the query results 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 of fields inserted and the number of fields in the table must be the same as the data type, 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

The update+ table name represents the table to be updated, and the content to be updated is set after the set.

Where is used to restrict the update condition, followed by an expression that satisfies the condition as long as the expression is true

Tips:where 1 can also represent truth, that is, full satisfaction.

Multi-field update

The code is as follows:

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

To update multiple fields, 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, you don't need to add where.

Tips: be very careful when using update, as it is possible that multiple records meet the where condition

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

Delete a 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 a record also needs to keep up with the where limit

Tips: unless you are quite sure that the where clause will delete only the lines you want to delete

Otherwise, select should be used to confirm the situation.

Delete all records

The code is as follows:

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

All records can be deleted one by one without following the where qualification

In addition, there is a truncate table statement, which deletes the original table and re-builds it, which is more efficient

Tips: you don't need any hint to delete here. Delete it if you want to delete it. It's very fast.

Therefore, you should be extra careful when using it. It is best to back up the data first.

Thank you for reading! After reading the above, do you have a general understanding of mysql's method of modifying data operations? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.