The solution to the modified data of the Mysql notes _mysql

The database is recorded in the table table by inserts, updates, and deletes, where

INSERT statement Implementation inserts data

UPDATE statement implementation updates data

DELETE statement implementation deletes data

Reference table

Insert data do not specify field name insert

Do not specify field name inserts

Copy Code code as follows:

Mysql> INSERT into the person values (1, ' John ', ' Male ', 1988);
Query OK, 1 row affected, 1 warning (0.03 sec)

Nsert into is the table name followed by the data that needs to be inserted

The data in values must match the field name, if the first field is NULL, enter NULL, and the tail is not entered

It's important to note that string data must be wrapped in quotes

Specify Field name insert

Copy Code code 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 followed by table name and field, where the field can be adjusted position

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

Insert more than one data at a time

Copy Code code as follows:

mysql> INSERT into person (id,name) VALUES (8, ' money Name '), (9, ' Zhang Yi ');
Query OK, 2 rows affected (0.04 sec)
Records:2 duplicates:0 warnings:0

Values are inserted into the data with multiple parentheses, separated by commas

As for the inserted field, just combine the two examples above to use

Insert query results into a table

Copy Code code 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 here that the number of fields and data types in the inserted fields and tables must be the same, otherwise the error will be

Copy a table

Copy Code code as follows:

Mysql> CREATE TABLE/As SELECT * from person;
Query OK, 1 row affected (0.16 sec)
Records:1 duplicates:0 warnings:0

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

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

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

Multi-field Update

Copy Code code as follows:

mysql> Update person set name= ' Little Red ', sex= ' where id=3;
Query OK, 1 row affected (0.03 sec)
Rows matched:1 changed:1 warnings:0

Multi-field updates only need to add more fields and data to be modified after set, separated by commas

If you want to update all records, you don't need to add a where

tips: use update with caution because it is possible for multiple records to satisfy a Where condition

It's a good idea to look at the side table and determine which records to update

Delete a field
Delete specified record

Copy Code code as follows:

Mysql> Delete from the person where id=9;
Query OK, 1 row affected (0.02 sec)

Deleting records also needs to keep up with where restrictions

tips: Unless you're pretty sure the WHERE clause deletes only the rows you want to delete

Otherwise, you should use Select to confirm the situation.

Delete all records

Copy Code code as follows:

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

Delete all records in the case where conditions are not qualified

There is also the TRUNCATE TABLE statement, which deletes the original table and then builds it more efficiently

tips: Delete here will not want any hint, say delete on delete, fast very

So be careful when you use it, it's best to back up the data first