MySQL must know-19th chapter-Inserting data

19th Chapter-Inserting data

This chapter describes how to insert data into a table using an INSERT statement from SQL.

19.1 Data Insertion

There is no doubt that select is the most commonly used SQL statement (which is why the first 17 chapters are about it). However, there are 3 other frequently used SQL statements that need to be learned. The first one is insert (the next chapter describes the other two). As the name implies, insert is used to insert (or add) rows into a database table. Inserts can be used in several ways:

• Insert the complete line;
• Insert part of the row;
• Insert multiple lines;
• Inserts the results of some queries.
  These are described in the following sections.
  Insert and system security you can use the security mechanism of MySQL to prohibit INSERT statements for each table or per user, as described in chapter 28th.

19.2 inserting the full line

The simplest way to insert data into a table is to use the basic Insert syntax, which requires 177132 to specify the table name and the value to be inserted into the new row. Here's an example:

Output is not normally produced without an output INSERT statement.
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. The more secure (but more cumbersome) way to write INSERT statements is as follows:

This example completes exactly the same work as the previous INSERT statement, but explicitly gives the column name in parentheses after the table name. When the row is inserted, MySQL fills in the corresponding item in the list with the corresponding value in the values list. The first value in values corresponds to the first specified column name. The second value corresponds to the second column name, and so on. Because column names are provided, values must match the specified column names in the order in which they are specified, not necessarily in the order in which they are listed in the actual table. The advantage is that this INSERT statement works correctly even if the structure of the table changes. You will find that the null value of cust_id is unnecessary, and the cust_id column does not appear in the list, so no value is required. The following insert statement fills all columns (as in the previous one), but fills them in a different order. Because the column name is given, the insert result is still correct:

Always use a list of columns generally do not use an INSERT statement that does not explicitly give a list to the column. Using a list of columns allows SQL code to continue to work even if the table structure changes.

Give the value carefully regardless of which insert syntax is used, 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. If this is not the case, an error message is generated and the corresponding row insertion is unsuccessful. With this syntax, you can also omit columns. This means that you can provide values for only some columns, and not for other columns. (In fact, you've seen this example: cust_id can be omitted when the column name is explicitly listed.) ）

Omit columns if the definition of the table 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 (no values or null values).
• The default value is given in the table definition. This means that if a value is not given, the default value will be used.
  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.

Improving the overall performance database is often accessed by multiple customers, and managing what requests and in what order Processing is a MySQL task. The insert operation can be time-consuming (especially if there are many indexes that need to be updated), and it may degrade the performance of the SELECT statement 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:
INSERT low_priority into
By the way, this also applies to the update and DELETE statements described in the next chapter.

19.3 inserting multiple rows

Insert can insert a row into a table. But what if you want to insert multiple rows?
You can use multiple INSERT statements and even commit them one at a time, with each statement ending with a semicolon, as follows:

Or, as long as the column name (and order) in each INSERT statement is the same, you can combine the statements as follows:

Where a single INSERT statement has multiple sets of values, each set of values is enclosed by a pair of parentheses, separated by commas.
Improve insert performance This technique can improve the performance of database processing because MySQL handles multiple insertions with a single INSERT statement faster than using multiple INSERT statements.

19.4 inserting the retrieved data

Insert is typically used to insert a row for a table with a specified column value. However, there is another form of insert that can be used to insert the results of a SELECT statement into a table. This is called an Insert Select, which, as its name implies, consists of an INSERT statement and a SELECT statement. If you want to merge the customer list from another table into your Customers table. You do not need to read one line at a time, and then insert it with INSERT, which can be done as follows:
A description of the new example imports data from a table named Custnew into the Customers table. To experiment with this example, you should first create and populate the Custnew table. The structure of the Custnew table is identical to the Customers table described in Appendix B. When populating custnew, you should not use cust_id values that have already been used in customers (if the primary key value repeats, subsequent insert operations will fail) or only omit this column value to allow MySQL to generate new values during data import.

This example uses insert Select to import all data from Custnew to customers. The SELECT statement retrieves the values to be inserted from custnew, rather than listing them. Each column listed in select corresponds to each column in the list followed by the Customers table name. How many rows this statement will insert depends on how many rows are in the Custnew 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 customers. This example imports cust_id (assuming you can ensure that the value of cust_id is not duplicated). You can also simply omit this column (from insert and select) so that MySQL generates a new value.
The column names in the Insert select are simple, and this example uses the same column names in the INSERT and SELECT statements. However, column names are not necessarily required to match. In fact, MySQL doesn't even care about the column names returned by SELECT. It uses the position of the column, so the first column in select (regardless of its column name) is used to populate the first column specified in the table column, and the second column is used to populate the second column specified in the table column, and so on. This is useful for importing data from tables that use different column names. The SELECT statement in the Insert select can contain a WHERE clause to filter the inserted data.
More examples if you want to see more examples of insert usage, see the sample table Fill script given in Appendix B, which is used primarily to create the sample tables used in this book.

19.5 Summary

This chapter describes how to insert rows into a database table. We learned several ways to use INSERT, and why explicitly use column names to learn how to import rows from other tables with the Insert Select. The next chapter describes how to use update and delete to further manipulate table data.

MySQL must know-19th chapter-Inserting data