MySQL Database (5)----Delete or update an existing row

Sometimes, you'll need to delete some rows, or modify their contents. This is the need to use the DELETE statement and the UPDATE statement.

1. The basic format of the DELETE statement is as follows:

DELETE   from Tbl_name WHERE    which rows to delete;

Where clause is optional, and it is used to specify which rows need to be dropped. If there is no WHERE clause, all rows inside the table will be deleted. This means that the simpler the DELETE statement is, the more dangerous it is, such as the following statement:

DELETE from Tbl_name

It will completely remove the contents of the table, so be sure to use it carefully.

If you don't know exactly which rows a DELETE statement will delete, it's a good idea to put the WHERE clause of the statement in a SELECT statement to see which rows the SELECT statement can detect. This helps to confirm that they are indeed the rows that you want to delete, and that there is no more and no less.

Here is an example of use:

mysql> Select*from student;+--------+-----+------------+| name | sex | student_id |+--------+-----+------------+| Jim | M |1|| Tom | M |2|| Lily | F |3|| Lucy | F |4|| Travis | M |5|| Steve | M |6|+--------+-----+------------+6RowsinchSet (0.00sec) MySQL > Delete fromStudentwheresex='F'; Query OK,2Rows Affected (0.01sec) MySQL > Select*from student;+--------+-----+------------+| name | sex | student_id |+--------+-----+------------+| Jim | M |1|| Tom | M |2|| Travis | M |5|| Steve | M |6|+--------+-----+------------+4RowsinchSet (0.00Sec

2. If you want to modify an existing record row, you need to use the UPDATE statement, which has the following basic format:

UPDATE  Tbl_name SET    which Columns to change      which rows to update;

The WHERE clause here is similar to the one in the DELETE statement, which is optional, so each row in the table is updated when it is not specified. For example, the following statement will change the name of each student to George:

mysql> UPDATE student SET name='George';

Obviously, this type of query must be treated with caution, so it is often necessary to add a WHERE clause, which is used to more specifically indicate the rows that need to be more rows.

Suppose you add a row in the student table, but forget to specify the contents of the Sex column, by default, the column will be set to ' F ':

mysql>INSERT into student (name)VALUES ('Julian'); Query OK,1Row affected (0.00sec) MySQL >SELECT *from student;+--------+-----+------------+| name | sex | student_id |+--------+-----+------------+| Jim | M |1|| Tom | M |2|| Travis | M |5|| Steve | M |6|| Hellen | M |7||Julian|F|8|+--------+-----+------------+6RowsinchSet (0.00Sec

At this point, you can fix it with an UPDATE statement that contains a WHERE clause that identifies the line that needs to be updated:

mysql>UPDATE StudentSET sex='M'--WHERE Name='Julian'; Query OK,1Row affected (0.00sec) Rows matched:1Changed:1Warnings:0mysql>SELECT *from student;+--------+-----+------------+| name | sex | student_id |+--------+-----+------------+| Jim | M |1|| Tom | M |2|| Travis | M |5|| Steve | M |6|| Hellen | M |7|| Julian | M |8|+--------+-----+------------+6RowsinchSet (0.00Sec

MySQL Database (5)----Delete or update an existing row