Previously on MySQL
Hello everyone, today is the nth time I write about MySQL, but also recently learned MySQL, and also want to record my learning process, and share it with you ny!
Details of this chapter
This post will cover how to insert data into a table using SQL’s INSERT statement
Data insertion
preamble
SELECT is, without a doubt, the most frequently used SQL statement (that’s why the previous chapter was all about it). However, there are three other frequently used SQL statements to learn. The first one is INSERT (the next article covers the other two).
As the name suggests, INSERT is used to insert (or add) rows to a database table. Insert can be used in several ways:.
- Inserts the complete row;
- Inserts a portion of a row;
- Insert multiple rows
- Inserts the results of certain queries;
Some of these are described in more detail below
Insertion and system security
Insertion and system security can be done on a per-table or per-user basis by disabling the use of INSERT statements using MySQL’s security mechanisms, which will be covered in a later article.
Insert complete rows
The easiest way to insert data into a table is to use the basic INSERT syntax, which requires that you specify the table name and the value being inserted into the new row. An example is given below:
importation
INSERT INTO CustomersVALUES(NULL，'Pep E. LaPew', '100 Main Street',
'Los Angeles','CA','90046','USA','NULL','NULL');
analyze
This example inserts a new customer into the customers table. The data stored in each table column is given in the VALUES clause, and a value must be provided 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 the table allows null values to be specified for the column). The columns 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 the column is automatically incremented by MySQL each time a new row is inserted. You don’t want to give a value (that’s MySQL’s job) and you can’t omit this column (as mentioned, you must give each column), so specify a NULL value (which is ignored by MySQL, which inserts the next available cust id value here).
Caution!
🌻no output INSERT statements generally do not produce output
A safer (but more annoying) way to write an INSERT statement is as follows.
importation
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state
cust_zip,
cust_country,cust_contact,cust_emai1)VALUES('Pep ELaPew','100 Main Street','Los Angeles','CA','90046','USA','NULL','NULL');
analyze
This example accomplishes exactly the same thing as the previous INSERT statement, except that the column names are explicitly given in parentheses after the table name. When inserting rows, MySQL fills in the corresponding items in the list with the corresponding values from 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. 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 their specified order, not in the order in which each column appears in the actual table. The advantage is that this INSERT statement works correctly even if the structure of the table changes. You will notice that the NULL value for cust_id is unnecessary; the cust id column does not appear in the list, so no value is needed.
The following INSERT statement populates all the columns (as before), but in a different order. Because the column names are given, the inserts are still correct.
importation
INSERT INTO customers(cust_name,
cust_contact,cust_emai1,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('Pep E. LaPew',
NULL，NULL，
'100 Main Street',
'Los Angeles',
'90046','USA');
Always use a list of columns Generally do not use INSERT statements that do not explicitly give a list of columns. Using a list of columns allows the SOL code to continue to function even if the table structure changes
Using this syntax, columns can also be omitted. This means that it is possible to provide values only for some columns and no values for others. (You’ve actually seen examples of this: cust_id can be omitted when the column name is explicitly listed.)
improve performance
Improving Overall Performance The database is often accessed by multiple clients, and it is MySQL’s job to manage what requests are processed and in what order.The INSERT operation can be time-consuming (especially when there are a lot of indexes to update), and it can degrade the performance of SELECT statements that are waiting to be processed.
If data retrieval is paramount (which it usually is), then 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.
Inserting multiple rows
INSERT can insert a row into a table. But what if you want to insert multiple rows?
It is possible to use multiple INSERT statements, or even commit them all at once, with each statement terminated by a semicolon, as follows.
importation
INSERT INTO customers(cust_namecust_address,
cust_city,
cust_state,
cust_zip,
cust_country)VALUES('Pep ELaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA');INSERT INTO customers(cust_namecust_address,
cust_city,
cust_state,
cust_zip,
cust_country)VALUES('M.Martian',
'42 Galaxy way',
'New York',
'iNY',
'11213',
'USA');
Insert the retrieved data
INSERT is generally used to insert a row with a specified column value into a table. However, there is another form of INSERT that can be utilized to insert the result of a SELECT statement into a table. This is called INSERTSELECT, which, as the name suggests, consists of an INSERT statement and a SELECT statement.
Suppose you want to merge a list of customers from another table into your customers table. Instead of reading one row at a time and inserting it with INSERT, you can do the following.
importation
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state
cust_zip,cust_country)SELECT cust_id,
cust_contact,
cust_email,cust_name,
cust_address
cust_city,
cust_state,
cust_zip,
cust_country FROM custnew;
analyze
This example uses INSERT SELECT to import all the data from custnew into customers.The SELECT statement retrieves the values to be inserted from custnew instead of listing them.Each column listed in the SELECT corresponds to each column in the list that follows the name of the customers table. 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 customers.
How to consolidate learning
Tip: In the process of learning, we need to think on our own first, rather than encountering not to give up thinking directly look at the answer, if the last encounter really do not know the topic, we can properly watch the answer to see if their own thinking is correct, in making the right judgment!
Summary of this article
This post describes how to insert rows into a database table. We learned several ways to use INSERT and why it is important to use column names explicitly, and learned how to import rows from other tables using INSERT SELECT. The next chapter describes how to further manipulate table data using UPDATE and DELETE.
