Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to insert row records into the MySQL data table, with a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Summary: it is one of the important responsibilities of administrators to load data, and because it is important, MySQL provides a wide range of methods. INSERT and LOAD statements are mainly used.

Insert new data using insert statement

Syntax: INSERT [INTO] tbl_name [(col_name,...)] VALUES (pression,...), …

INSERT [INTO] tbl_name SET col_name=expression,...

Let's start adding records with the INSERT statement, which is a SQL statement that needs to specify the table in which you want to insert rows of data or put values in rows. INSERT statements come in several forms:

You can specify values for all columns:

For example:

Shell >-u root-p

Mysql > use mytest

Mysql > insert into worker values ("tom", "tom@yahoo.com")

The word "INTO" has been optional since MySQL 3.22.5. (this is also true for other forms of INSERT statements. The VALUES table must contain the values of each column in the table and be given in the order in which the columns in the table are stored. (in general, this is the order in which columns are defined when the table is created. If you're not sure, you can use DESCRIBE tbl_name to check this order. )

Using multiple value tables, you can provide multiple rows of data at a time.

Mysql > insert into worker values ('tom','tom@yahoo.com'), (' paul','paul@yahoo.com')

INSERT with multiple value tables. The form of VALUES is supported in MySQL 3.22.5 or later.

You can give the column to which you want to assign, and then list the value. This is useful when you want to create records with only a few columns that need to be set up initially.

For example:

Mysql > insert into worker (name) values ('tom')

Since MySQL 3.22.5, this form of INSERT has also allowed multiple value tables:

Mysql > insert into worker (name) values ('tom'), (' paul')

Columns that are not named in the list of columns will be assigned default values.

Since MySQL 3.22. 10, columns and values can be given in the form of col_name = value.

For example:

Mysql > insert into worker set name='tom'

Unnamed lines in the SET clause are assigned a default value.

You cannot insert multiple rows using this form of INSERT statement.

An expression can reference any column previously set in a values table. For example, you can do this:

Mysql > INSERT INTO tbl_name (col1,col2) VALUES (15recom col1Christ 2)

But you can't do this:

Mysql > INSERT INTO tbl_name (col1,col2) VALUES (col2*2,15)

Use INSERT... SELECT statement inserts rows selected from other tables

When we learned to create tables in the previous section, we knew that we could use select to create tables directly from other tables, or even copy data records at the same time. If you already have a table, you can also benefit from the cooperation of select statements.

Enter data from other tables, such as:

Mysql > insert into tbl_name1 (col1,col2) select col3,col4 from tbl_name2

You can also omit the column list of the destination table if you have data entry for each column.

Mysql > insert into tbl_name1 select col3,col4 from tbl_name2

INSERT INTO... The SELECT statement satisfies the following conditions:

The query cannot contain an ORDER BY clause.

The destination table of the INSERT statement cannot appear in the FROM clause of the SELECT query section because it is forbidden to SELECT from the table you are inserting in ANSI SQL. The problem is that SELECT may find records that were previously inserted during the same run period. When using a sub-selection clause, the situation can be easily confused)

Use replace, replace... Select statement insertion

The REPLACE function is exactly the same as INSERT, except that if an old record in a table has a new record on a unique index with the same value, the old record is deleted before the new record is inserted. In this case, the insert statement behaves as an error.

REPLACE statements can also be matched with brown SELECT, so the contents of the last two sections are perfect for REPALCE.

It should be noted that because the REPLACE statement may change the original record, be careful when using it.

Use load statement to enter data in batch

Earlier in this chapter, we discussed how to insert data into a table using SQL. However, if you need to add many records to a table, it is inconvenient to enter data using SQL statements. Fortunately, MySQL provides ways to enter data in bulk, making it easy to add data to a table. These methods are described in this section and in the next section. This section describes workarounds at the SQL language level.

1. Basic grammar

Syntax: the LOAD DATA [LOCAL] INFILE file_name.txt [REPLACE | IGNORE] INTO TABLE tbl_name LOAD DATA INFILE statement is read into a table at a high speed from a text file. If you specify the LOCAL keyword, read the file from the client host. If LOCAL is not specified, the file must be located on the server. (LOCAL is available in MySQL3.22.6 or later.)

For security reasons, when reading a text file located on the server, the file must be in the database directory or be readable by everyone. In addition, in order to use LOAD DATA INFILE for files on the server, you must have file permissions on the server host. See chapter 7, database security.

The REPLACE and IGNORE keywords control duplicate processing of existing unique key records. If you specify REPLACE, the new row replaces the existing row with the same unique key value. If you specify IGNORE, skip the input of duplicate lines of existing lines with a unique key. If you do not specify any of the options, an error occurs when a duplicate key is found and the rest of the text file is ignored.

If you use the LOCAL keyword to load data from a local file, the server has no way to stop the file transfer during the operation, so the default behavior is as if IGNORE is specified.

2. The principle of searching documents

When looking for files on the server host, the server uses the following rules:

If an absolute pathname is given, the server uses that pathname.

If a relative pathname with one or more front parts is given, the server searches the file relative to the server's data directory.

If you give a file name without a front part, the server looks for the file in the database directory of the current database.

Note that these rules mean that a file like ". / myfile.txt" is read from the server's data directory, while a file given as "myfile.txt" is read from the database directory of the current database. Also note that the db1 file is read from the database directory instead of db2 for which of the following statements:

Mysql > USE db1

Mysql > LOAD DATA INFILE ". / data.txt" INTO TABLE db2.my_table

3. Syntax of FIELDS and LINES clauses

If you specify a FIELDS clause, each of its clauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY and ESCAPED BY) is also optional, except that you must specify at least one of them.

If you do not specify a FIELDS clause, the default value is the same as if you write this:

FIELDS TERMINATED BY ENCLOSED BY ESCAPED BY\

If you do not specify a LINES clause, the default value is the same as if you write this:

LINES TERMINATED BY

In other words, the default value causes the LOAD DATA INFILE to behave as follows when reading input:

Look for line boundaries at newline characters

Break lines into fields at the locator

Do not expect fields to be encapsulated by any quote characters

Part of the literal characters that are interpreted as field values by locators, newlines, or "\" that begin with ""

LOAD DATA INFILE can be used to read files obtained from external sources. For example, a file in dBASE format will have fields separated by commas and enclosed in double quotes. If the line in the file is terminated by a newline character, the command shown below describes the fields and line processing options that you will use to load the file:

Mysql > LOAD DATA INFILE data.txt INTO TABLE tbl_name

FIELDS TERMINATED BY, ENCLOSED BY "

LINES TERMINATED BY

Any field or row processing option can specify an empty string (). If not empty, the FIELDS [OPTIONALLY] ENCLOSED BY and FIELDS ESCAPED BY values must be a single character. FIELDS TERMINATED BY and LINES TERMINATED BY values can be more than one character. For example, write to a line terminated by a carriage return newline pair (CR+LF), or read a file that contains such a line, specifying a LINES TERMINATED BY

Clause.

The enclosing character of the FIELDS [OPTIONALLY] ENCLOSED BY control field. For the output (SELECT... INTO OUTFILE), if you omit OPTIONALLY, all fields are surrounded by ENCLOSED BY characters. An example of such output (using a comma as a field delimiter) is shown below:

"1", "a string", "100.20"

"2", "a string containing a, comma", "102.20"

3 "," a string containing a "quote", "102.20"

"4", "a string containing a", quote and comma "," 102.20 "

If you specify that the OPTIONALLY,ENCLOSED BY character is only used to surround the CHAR and VARCHAR fields:

1, "a string", 100.20

2, "a string containing a comma", 102.20

3, "a string containing a" quote ", 102.20

4, "a string containing a", "quote and comma", 102.20

Note that the occurrence of the ENCLOSED BY character in a field value is escaped by using the ESCAPED BY character as its prefix. Also note that if you specify an empty ESCAPED by value, it may produce output that cannot be read correctly by LOAD DATA INFILE. For example, if the escape character is empty, the output shown above is shown below. Notice that the second field in the fourth line contains a comma followed by quotation marks, which (mistakenly) seems to terminate the field:

1, "a string", 100.20

2, "a string containing a comma", 102.20

3, "a string containing a" quote ", 102.20

4, "a string containing a", "quote and comma", 102.20

FIELDS ESCAPED BY controls how special characters are written or read. If the FIELDS ESCAPED BY character is not empty, it is used to prefix the following characters on the output:

FIELDS ESCAPED BY character

FIELDS [OPTIONALLY] ENCLOSED BY character

The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values

ASCII0 (actually write subsequent escape characters as ASCII0 instead of a zero byte)

If the FIELDS ESCAPED BY character is empty, no character is escaped. It may not be a good idea to specify an empty escape character, especially if the field value in your data contains any of the characters in the table just given.

For input, if the FIELDS ESCAPED BY character is not empty, the occurrence of the character is stripped and the subsequent character is literally treated as a word

Thank you for reading this article carefully. I hope the article "how to insert row records into a MySQL data table" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.