MySQL: Entering data in tables

[Botan]

Data entry in a table is via the INSERT statement. Obviously we need to have permission to INSERT on the table. Let us see what we can use different syntax:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] 
[INTO] tbl_name [(col_name ,...)] 
VALUES ({expression | DEFAULT },...),(...),... 
[ON DUPLICATE KEY UPDATE col_name = expression, ... ]

With this syntax we can use one or more rows in the table. Before the VALUES clause can specify the column names affected by the INSERT statement: these names will correspond to the values included in parentheses after each VALUES. To insert multiple rows will use more than one pair of parentheses after VALUES. If you do not specify a column list, we provide a value for each column in the order they are defined.

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] 
[INTO] tbl_name 
December column_name = {expression | DEFAULT}, ... 
[ON DUPLICATE KEY UPDATE col_name = expression, ... ]

In this case we use the SET clause to explicitly assign a value to each column indicated.

[Botan]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] 
[INTO] tbl_name [(col_name ,...)] 
SELECT ... 
[ON DUPLICATE KEY UPDATE col_name = expression, ... ]

Here we use a SELECT to directly supply values to the new table. With this system you can insert multiple rows. As in the first case, you can list the columns involved, otherwise the SELECT must provide values for all columns. Now let's see the function of various terms used:

• LOW PRIORITY: The inclusion is not made until there are customers who read on the table involved, this can also lead to long waits.
• DELAYED: in this case the insertion is delayed until the table is not free. The difference with the previous case is that the client is given the go ahead immediately, and the lines are kept to be included in a buffer managed by the server until the time of actual writing. Of course the lines will not be visible until a SELECT will not be placed on the table.
• HIGH PRIORITY: cancel the effect of a possible option - low-priority-updates that were active on the server.
• IGNORE: can handle errors that occur during insertion (duplicate keys or invalid values) instead of throwing fatal error, the records with double keys are simply discarded as invalid values are "adjusted" to the maximum value next.
• ON DUPLICATE KEY UPDATE: If you detect a key double the specified instruction is executed on the existing line. With this option you can not use DELAYED.

These values in the table with the first two syntax can be indicated by constants or expressions, or by calling explicitly DEFAULT. The default is also used for columns not specified. However, in strict mode, you must specify values for all columns that have no explicit default, otherwise you will get an error.

[Botan]

AUTO_INCREMENT columns are measured automatically, showing NULL (or omitted). To know the value generated may be used after the insertion, LAST_INSERT_ID () which returns the last value created during the current connection. In addition to the INSERT, REPLACE, MySQL offers education, which is an extension to the SQL standard and can replace existing rows with the rows inserted if there is a situation of dual key. In practice, using REPLACE, if you can not insert a row because a PRIMARY KEY or a UNIQUE index already exists on the table, MySQL deletes the old row and insert the new one. This behavior is opposite to that of INSERT IGNORE, with which the new line to be discarded.

To make a REPLACE we have permission to INSERT and DELETE statements, the syntax is almost identical to those of the INSERT, Let's see:

REPLACE [LOW_PRIORITY | DELAYED] 
[INTO] tbl_name [(col_name ,...)] 
VALUES ({expression | DEFAULT },...),(...),... 
or 
REPLACE [LOW_PRIORITY | DELAYED] 
[INTO] tbl_name 
December column_name = {expression | DEFAULT}, ... 
or 
REPLACE [LOW_PRIORITY | DELAYED] 
[INTO] tbl_name [(col_name ,...)] 
SELECT ...

Another way to insert data into a table that allows us to import a text file: LOAD DATA INFILE. Let's see the syntax:

LOAD DATA [LOW_PRIORITY | Concurrent] [LOCAL] INFILE 'file_name. Txt' 
[REPLACE | IGNORE] 
INTO TABLE table_name 
[FIELDS 
[TERMINATED BY 'string'] 
[[Optionally] ENCLOSED BY 'char'] 
[ESCAPED BY 'char'] 
] 
[LINES 
[STARTING BY 'string'] 
[TERMINATED BY 'string'] 
] 
[IGNORE number LINES] 
[(Name_column_o_variable ,...)] 
[SET col_name = expression ,...)]