MySQL Introduction-11: Data increase, deletion, change

1. Data insertion

2. Data Update

3. Data deletion

4. Guidelines for updates and deletions

1. Data Insertion-insert

There are several ways to insert data:

• Insert a full line

• Insert part of a row

• Inserting multiple lines

• Insert the results of some queries
  

Syntax:insert [Low_priority | DELAYED |    High_priority] [IGNORE] [into] tbl_name [(Col_name,...)] {VALUES | VALUE} ({expr |    DEFAULT},...), (...),... [on DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ...]

Always use the list of columns:

Generally do not use INSERT statements that are not explicitly given to a list of dequeue. Using a list of columns allows SQL code to continue to work even if the table structure changes.

Omit columns:

If the table definition allows, you can omit some columns from the insert operation. The omitted column must meet one of the following conditions.

• The column is defined to allow null values

• The default value is given in the table definition.

If no value is given to a column that does not allow null values in the table and does not have a default value, MySQL generates an error message and the corresponding row insertion is unsuccessful.

2. Data Update

Syntax:single-table syntax:update [low_priority] [IGNORE] table_reference SET col_name1={expr1| DEFAULT} [, col_name2={expr2|    DEFAULT}] ...    [WHERE Where_condition]    [ORDER by ...] [LIMIT Row_count]

To use a subquery in an UPDATE statement:

A subquery can be used in the UPDATE statement to update the column data with the data retrieved from the SELECT statement.

3. Data deletion

Syntax:single-table Syntax:delete [low_priority] [QUICK] [IGNORE] from Tbl_name [WHERE where_condition] [ORDER by].    .] [LIMIT Row_count]

Delete Deletes the contents of the table rather than the table structure

Delete statements remove rows from a table, or even all rows in a table. However, delete does not delete the table itself.

Faster deletion:

If you want to remove all rows from the table, do not use delete. You can use the Trunate table statement, which does the same work, but faster (truncate actually deletes the original table and re-creates a table instead of row-by-row deletion of the table)

4. Guidelines for updates and deletions

Update and DELETE statements generally have a WHERE clause, and if the WHERE clause is omitted, the update or delete is applied to all rows in the table.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/3B/CC/wKiom1O_sZCinCE4AAHCjrnxlug239.jpg "title=" 9.png " alt= "Wkiom1o_szcince4aahcjrnxlug239.jpg"/>

This article is from the "Share Your Knowledge" blog, so be sure to keep this source http://skypegnu1.blog.51cto.com/8991766/1437196