MySQL tutorials Delete or update existing records

1.4.9 Delete or update existing records
Sometimes, you want to drop some records or change their contents. The DELETE and UPDATE statements enable us to do this.
The DELETE statement has the following format:
Delete from Tbl_namewhere records to delete
The WHERE clause specifies which records should be deleted. It is optional, but if not selected, all records will be deleted. This means that the simplest DELETE statement is also the most dangerous.
DELETE from Tb1_name
This query clears all the contents of the table. You must be careful! To delete a specific record, you can select the record you want to delete by using the WHERE clause. This is similar to the select language
The WHERE clause in the sentence. For example, to remove all presidential records born in Ohio in the President table, you can use the following query:

One limitation of the WHERE clause in the DELETE statement is the ability to reference only the columns in the table that you want to delete records from. Before you publish a DELETE statement, it is a good idea to test the appropriate WHERE clause with the SELECT statement to make sure that the records that are actually deleted are the records that you actually want to delete (and only those records are deleted). If you want to delete the records for Te D D yroosevelt. Can the following query complete the work?

Yes, it feels like it can remove the record you're trying to delete in your head. However, it is wrong, in fact, it can also delete the records of Franklin Roosevelt. It is safe to check with the WHERE clause first, as follows:

Now that we understand the WHERE clause where we can select the record we want, the delete query can be correctly constructed as follows:

It seems like deleting a record requires a lot of work, doesn't it? But Safety first! (If you want to make keyboard input work as little as possible, you can use copy and paste technology or input line editing techniques.) For more information, see the "Tips for interacting with MySQL" section. To modify an existing record, you can use the UPDATE statement, which has the following format:
Updatet b l_name Set the column to change where to update the WHERE clause here is optional, just as the DELETE statement is, so each record in the table is updated if you do not specify it. The following query changes each student's name to "G e o RG E":

Obviously, such queries must be extremely cautious. Be more careful with the records that are being updated generally. It is assumed that a new record has recently been added to the history league, but only a few columns of this entity have been filled in:

Then realize that they forgot to set their membership expiration date. Then you can set the following:

You can update multiple columns at the same time. The following statement updates the Jerome e-mail and mailing address:

You can also "do not set" this column by setting the value of a column to null (assuming that this column allows null values). If at some point in the future Jerome decides to pay the membership renewal fee for a lifetime member, then the expiration date of its record can be set to null ("permanent") to mark him as a lifelong member. The specific settings are as follows:

As with the DELETE statement, for update, it is a good idea to test the WHERE clause with the SELECT statement to make sure that the correct update record is selected. If you select a range that is too narrow or too wide, the updated record is too small or too large. If you have experimented with the queries in this section, you must have deleted and modified the records in the samp_db table. You should undo these changes before you continue to learn the contents of the next section. Press 1. 4. The final note of section 7, "Add new record", reload the contents of the table to complete the work.
1.4.10 change the structure of a table
Looking back at the time we created the History League member table, we were missing a membership number, so we could do a ALTE Rtable statement exercise. You need to use ALTER TABLE to rename the table, add or remove columns, change the type of columns, and so on. Here is an example of how to add a new column. For more information about the Alter TABLE feature, see Chapter 3rd. The main consideration for increasing the membership number to the member table is that the value should be unique in order to avoid confusion among member entries. Auto_increment column Here is useful because we can add new numbers in a season when MySQL automatically generates a unique number. In the Create TABLE statement, the description of such a column is as follows:

For Alter TABLE, the corresponding syntax is similar. You can add this column by executing the following query:

We already have a column that holds the membership number, so how do we assign the membership number to the existing record in the member table? It's easy! MySQL has done the work. When you add a column to a table, MySQL initializes the column value with the default value. For auto_increment columns, each row will produce a new sequential number.