MySQL learning inserts, deletes, and updates

First, insert data

Insert is used to insert (or add) rows into a database table. Insert to

To use in several ways:
? Insert the complete line;
? Insert part of the row;
? Insert multiple lines;
? Inserts the results of some queries.

1, simple but uneasy to go

INSERT  into Customers VALUES NULL , '  Person ');

This example inserts a new customer into the Customers table. The data stored in each table column is given in the values clause and must provide a value for each column. If a column does not have a value (such as the cust_contact and Cust_email columns above), a null value should be used (assuming that the table allows a null value to be specified for the column). Each column must be populated in the order in which they appear in the table definition. The first column, cust_id, is also null. This is because each time a new row is inserted, the column is automatically incremented by MySQL. You do not want to give a value (this is the work of MySQL), and you cannot omit this column (as mentioned above, each column must be given), so specify a null value (it is ignored by MySQL and MySQL inserts the next available cust_id value here).

Although this syntax is simple, it is not safe and should be avoided as much as possible. the above SQL statement is highly dependent on the order in which the columns in the table are defined, and also relies on information that is readily available in its order . Even if this sequence of information is available, there is no guarantee that the columns will remain in exactly the same order after the next table structure change. Therefore, it is not safe to write SQL statements that depend on a particular column order. If you do this, sometimes it is inevitable that something will go wrong.

2. The column name is explicitly given in parentheses after the table name

INSERT  into Customers (Cust_id,cust_name) VALUES NULL , ' Perso ');

Note: Regardless of which insert syntax you use, you must give the correct number of values. If you do not provide a column name, you must provide a value for each table column. If you provide a column name, you must give a value to each column listed.

You can omit some columns using the above, but the omitted columns must be nullable or have a default worth.

Performance: Insert operations can be time-consuming (especially when there are many indexes that need to be updated), and it may degrade the performance of SELECT statements waiting to be processed. If data retrieval is the most important (usually so), you can instruct MySQL to lower the priority of the INSERT statement by adding the keyword low_priority between insert and into, as follows: Also applies to the update and DELETE statements described.

INSERT  into Customers (Cust_id,cust_name) VALUES NULL , ' Perso ');

3. Insert multiple lines

INSERT  into Customers (Cust_id,cust_name) VALUES NULL , ' Perso ' ); INSERT  into Customers (Cust_id,cust_name) VALUES NULL , ' Perso ');

Or

INSERT  into Customers (Cust_id,cust_name) VALUES NULL , ' Perso '  NULL,'perso');

Where a single INSERT statement has multiple sets of values, each set of values is enclosed by a pair of parentheses, separated by commas.

4. Insert the retrieved data

INSERT  into custmoernew (cs_id,cs_name) SELECT cust_id,    from Customers;

Note: The SELECT statement retrieves the values to be inserted from customers , rather than listing them. each column listed in select corresponds to each column in the list followed by the Custmoernew table name . How many rows this statement will insert depends on how many rows are retrieved from the Customers table. If the table is empty, no rows are inserted (and no errors are generated because the operation is still legal). If the table does contain data, all data will be inserted into the customernew.

The SELECT statement in the Insert select can contain a WHERE clause to filter the inserted data.

Second, update the data

Using the UPDATE statement

The basic UPDATE statement consists of 3 parts, namely:

? the table to update;
? The column names and their new values;

? Determines the filter criteria to update rows.

UPDATE Customers SET cust_name="Winter"WHERE cust_id=1

Note: Ignore keyword if you update multiple rows with an UPDATE statement and an existing error occurs when one or more rows are updated, the entire update operation is canceled (all rows that were updated before the error occurred are restored to their original values). To continue updating even if an error occurs, you can use the IGNORE keyword as follows: Update IGNORE customers ...

To remove a column's value, you can set it to null if the table definition allows null values.

Third, delete operation

Delete does not require a column name or wildcard character. Delete Deletes the entire row instead of deleting the column. In order to delete the specified column, use the UPDATE statement.

DELETE  from custmoernew WHERE = 5;

Note: Faster removal If you want to remove all rows from the table, do not use delete. You can use the TRUNCATE table statement , which accomplishes the same work, but is faster (TRUNCATE actually deletes the original table and re-creates a table instead of deleting the data in the table row by line).

Here are the habits that many SQL programmers follow when using update or delete.

? Never use an UPDATE or DELETE statement without a WHERE clause unless you are sure you want to update and delete each row.

? Ensure that each table has a primary key, and use it as much as possible in the WHERE clause (you can specify a range of primary keys, multiple values, or values).

? Before using the WHERE clause on an UPDATE or DELETE statement, you should test with select to ensure that it filters the correct records in case the WHERE clause you are writing is incorrect.

? Use a database that enforces referential integrity, so that MySQL does not allow you to delete rows that have data associated with other tables.

MySQL learning inserts, deletes, and updates