Modify and DELETE data records (DELETE \ UPDATE)

Sometimes you want to remove some records or change their content. The DELETE and UPDATE statements allow us to do this.

Modify record with update

UPDATE tbl_name SET column to be changed

WHERE record to be updated

The WHERE clause is optional. Therefore, if not specified, each record in the table is updated.

For example, in the pet table, we find that the gender of the pet Whistler is not specified, so we can modify this record as follows:

Mysql> update pet set sex = 'F' where name = "Whistler ";

Delete a record

The DELETE statement has the following format:

Delete from tbl_name WHERE record to be deleted

The WHERE clause specifies which records should be deleted. It is optional, but if it is not selected, all records will be deleted. This means that the simplest DELETE statement is also the most dangerous.
This query clears all contents in the table. Be careful!

To delete a specific record, you can use the WHERE clause to select the record to be deleted. This is similar to the WHERE clause in the SELECT statement.

Mysql> delete from pet where name = "Whistler ";

You can use the following statement to clear the entire table:

Mysql> delete from pet;

Summary

This section describes the usage of two SQL statements. Be careful when using the UPDATE and DELETE statements, because it may cause danger to your data. Especially the DELETE statement, it is easy to DELETE a large amount of data. Be careful when using it.

Questions

1. Follow the steps described in this chapter to enable the MySQL server to automatically start when the Linux system is started. Try other methods to start, restart, and shut down the server.

2. Now there is a MySQL server located in the host database.domain.net. Use the root user's identity and password newpass to connect to the database test. How can we provide appropriate command lines? If the option file is used, how do I add the option?

3. Create a table pet described in the example in this chapter in the test database. Its structure is described as follows:

Name: a fixed-length string of 30 characters in width.

Owner: 30 fixed-length string in width

Species: a fixed-length string of 10 widths.

Sex: A non-null Enumeration type consisting of m and f

Birth: date type

Death: date type

4. Data Entry table in pet table in this chapter:

+ ---------- + -------- + --------- + ------ + ------------ +

| Name | owner | species | sex | birth | death |

+ ---------- + -------- + --------- + ------ + ------------ +

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |

| Fang | Benny | dog | m | 1990-08-27 | NULL |

| Boane | Diane | dog | m |

| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |

| Slim | Benny | snake | m | 1996-04-29 | NULL |

| Puffball | Diane | hamster | f | © 3-30 | NULL |

+ ---------- + -------- + --------- + ------ + ------------ +

Record the DATA to a DATA file and use the load data infile statement to LOAD the DATA. Tip: if the file is in Windows, the line break is "\ r \ n ".

If you are using the utility mysqlimport command line how to write.

1. MySQL database startup and Termination
2. Detailed description of data table operations in MySQL