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

The data stored in the system is the core of the database management system (DBMS). The database is designed to manage data storage, access and maintain data integrity. MySQL provides a wealth of data management statements, including INSERT for inserting data, UPDATE for updating data, and DELETE statements for deleting data.

7.1. Insert data

Insert data for all fields of the table

Insert data using a basic INSERT statement that requires the table name and the value to be inserted into the new record. The basic syntax is:

INSERT INTO tbl_name (column_list) VALUES (value_list)

Before inserting data, first create a table:

Mysql > CREATE TABLE person-> (- > id INT UNSIGNED NOT NULL AUTO_INCREMENT,-> name CHAR (40) NOT NULL DEFAULT',-> age INT NOT NULL DEFAULT 0,-> info CHAR (50) NULL,-> PRIMARY KEY (id)->); Query OK, 0 rows affected (0.03 sec)

In the person table, insert a new record with id value 1, name value Green, age value 21, and info value Lawyer

Mysql > INSERT INTO person (id, name, age, info)-> VALUES (1 row affected Greenbelt, 21, 'Lawyer'); Query OK, 1 row affected (0.02 sec) mysql > SELECT * FROM person +-+ | id | name | age | info | +-+ | 1 | Green | 21 | Lawyer | +-+ 1 row in set (0.00 sec)

In the person table, insert a new record with id value 2, name value Suse, age value 22, and info value dancer

Mysql > INSERT INTO person (age, name, id, info)-> VALUES (22, 'Suse', 2,' dancer'); Query OK, 1 row affected (0.02 sec) mysql > SELECT * FROM person +-+ | id | name | age | info | +-+ | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | +-+ 2 rows in set (0.00 sec)

Inserts data for the specified field of the table

In the person table, insert a new record with the name value of Willam, the age value of 20, and the info value of sports man

Mysql > INSERT INTO person (name, age,info)-> VALUES ('Willam', 20,' sports man'); Query OK, 1 row affected (0.02 sec) mysql > SELECT * FROM person +-+ | id | name | age | info | +-+ | 1 | Green | 21 | Lawyer | | 2 | Suse | | 22 | dancer | | 3 | Willam | 20 | sports man | +-+ 3 rows in set (0.00 sec) |

In the person table, insert a new record with a name value of laura and an age value of 25

Mysql > INSERT INTO person (name, age) VALUES ('Laura', 25); Query OK, 1 row affected (0.01sec) mysql > SELECT * FROM person +-+ | id | name | age | info | +-- + | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | | 3 | Willam | 20 | sports man | | 4 | Laura | 25 | NULL | +-+ 4 rows in set (0.00 sec)

You can find that the id field increases automatically when no value is assigned after the data is inserted. Here, the id field is the primary key of the table and cannot be empty. Copper will automatically insert self-increasing sequence values for the field.

Insert multiple records at the same time

The INSERT statement can insert multiple records into a data table at the same time, specifying multiple value lists, each separated by a comma. The basic syntax is as follows:

INSERT INTO tbl_name (column_list) VALUES (value_list1), (value_list2), (value_list3)

In the person table, specify insert values in the name, age, and info fields, and insert 3 new records at the same time

Mysql > INSERT INTO person (name, age, info)-> VALUES ('Evans',27,' secretary'),-> ('Dale',22,' cook'),-> ('Edison',28,' singer'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > SELECT * FROM person +-+ | id | name | age | info | +-+ | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | | 3 | Willam | 20 | sports man | | 4 | Laura | 25 | NULL | 5 | Evans | 27 | secretary | | 6 | Dale | 22 | cook | | 7 | Edison | 28 | singer | +-+ 7 rows in set (0.00 sec)

In the person table, no insert list is specified and 2 new records are inserted at the same time

Mysql > INSERT INTO person-> VALUES,-> (NULL,'Harriet',19, 'pianist'); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > SELECT * FROM person +-+ | id | name | age | info | +-+ | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | | 3 | Willam | 20 | sports man | | 4 | Laura | 25 | NULL | 5 | Evans | 27 | secretary | 6 | Dale | 22 | cook | 7 | Edison | 28 | singer | 9 | Harry | 21 | magician | 10 | Harriet | 19 | pianist | +-- + 9 rows in set (0.00 sec) |

Insert query results into data

The INSERT statement is used to specify the column value of the inserted record when inserting a record into a data table. INSERT can also insert the results of the SELECT statement query into the list, and its basic syntax is:

INSERT INTO tbl_name1 (column_list1) SELECT (column_list2) FROM table_name2 WHERE (condition)

Query all records from the person_ old table and insert them into the person table

First, create a data table called person_old with the same structure as person mysql > CREATE TABLE person_old-> (- > id INT UNSIGNED NOT NULL AUTO_INCREMENT,-> name CHAR (40) NOT NULL DEFAULT',-> age INT NOT NULL DEFAULT 0,-> info CHAR (50) NULL,-> PRIMARY KEY (id)->) Query OK, 0 rows affected (0. 11 sec) add two records mysql > INSERT INTO person_old-> VALUES to the person_old table (11 rows affected: 0mysql > SELECT * FROM person_old), (12 rows affected: 0 Warnings: 0mysql > SELECT * FROM person_old) +-+ | id | name | age | info | +-+ | 11 | Harry | 20 | student | | 12 | Beckham | 31 | police | +-+- + 2 rows in set (0.00 sec) insert data into person table mysql > INSERT INTO person (id Name, age, info)-> SELECT id, name, age, info FROM person_old Query OK, 2 rows affected (0.01sec) Records: 2 Duplicates: 0 Warnings: 0mysql > SELECT * FROM person +-+ | id | name | age | info | +-- + | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | | 3 | Willam | 20 | sports man | 4 | Laura | 25 | NULL | | 5 | Evans | 27 | secretary | | 6 | Dale | 22 | cook | | 7 | Edison | 28 | singer | 9 | Harry | 21 | magician | | 10 | Harriet | 19 | pianist | 11 | Harry | 20 | student | 12 | Beckham | 31 | police | +-+ 11 rows in set (0.00 sec) |

7.2. Update data

After you have data in the table, you can update the data. The basic syntax is:

UPDATE table_name SET col_name1=value1,col_name2=value2,..., WHERE where_condition

In the person table, update the record with id value 11, change the age field value to 15, and change the name field value to LiMing

Mysql > UPDATE person SET age = 15, name='LiMing' WHERE id=11; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > SELECT * FROM person WHERE id=11 +-+ | id | name | age | info | +-+ | 11 | LiMing | 15 | student | +-+ 1 row in set (0.00 sec)

In the person table, update the record with an age value of 19: 22 and change the values of the info field to student

Mysql > UPDATE person SET info='student' WHERE age BETWEEN 19 AND 22; Query OK, 6 rows affected (0.02 sec) Rows matched: 6 Changed: 6 Warnings: 0mysql > SELECT * FROM person WHERE age BETWEEN 19 AND 22 +-+ | id | name | age | info | +-- + | 1 | Green | 21 | student | | 2 | Suse | 22 | student | 3 | Willam | 20 | student | 6 | Dale | 22 | student | | 9 | Harry | | 21 | student | | 10 | Harriet | 19 | student | +-+ 6 rows in set (0.00 sec) |

7.3. Delete data

Delete data from the data table using the delete statement, whose basic syntax is:

DELETE FROMN table_name [WHERE]

In the person table, delete records with id equal to 11

Before performing the delete operation, use the select statement to view the records of the current id=11 mysql > SELECT * FROM person WHERE id=11 +-+ | id | name | age | info | +-+ | 11 | LiMing | 15 | student | +-+ 1 row in set (0.00 sec) The DELETE statement deletes the record mysql > DELETE FROM person WHERE id = 11 Query OK, 1 row affected (0.02 sec) statement has been executed, check the execution result: mysql > SELECT * FROM person WHERE id=11;Empty set (0.00 sec)

In the person table, delete multiple records at the same time using the delete statement, deleting records with age fields between 19 and 22

Before performing the delete operation, use the select statement to view the current data mysql > SELECT * FROM person WHERE age BETWEEN 19 AND 22 +-+ | id | name | age | info | +-- + | 1 | Green | 21 | student | | 2 | Suse | 22 | student | 3 | Willam | 20 | student | | 6 | Dale | 22 | student | 9 | Harry | 21 | student | | 10 | Harriet | | 19 | student | +-+ 6 rows in set (0.00 sec) DELETE deletes these records mysql > DELETE FROM person WHERE age BETWEEN 19 AND 22 | Query OK, 6 rows affected (0. 01 sec) View the execution result mysql > SELECT * FROM person WHERE age BETWEEN 19 AND 22 sec

To delete all records in the person table, the SQL statement is as follows

Before performing the delete operation, use the select statement to view the current data: mysql > SELECT * FROM person +-+ | id | name | age | info | +-- + | 4 | Laura | 25 | NULL | | 5 | Evans | 27 | secretary | | 7 | Edison | 28 | singer | 12 | Beckham | 31 | police | + +-+ 4 rows in set (0.00 sec) execute DELETE statement to delete the four records mysql > DELETE FROM person Query OK, 4 rows affected (0.01 sec) to view the execution result: mysql > SELECT * FROM person;Empty set (0.00 sec)

If you want to delete all the records in the table, you can also use the TRUNCATE TABLE statement. TRUNCATE will directly delete the original table and recreate a table with a syntax structure of TRUNCATE TABLE table_name. TRUNCATE deletes the table rather than the record directly, so it executes faster than DELETE.

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.