MySQL learning notes-insert row records into data tables-MySQL

MySQL study Notes-insert row records into data tables 1. use the INSERT statement to INSERT new data

Syntax:

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

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

Let's start to use the INSERT statement to add records. this is an SQL statement. you need to specify the table for which you want to INSERT data rows or the table that puts values by row. The INSERT statement has several forms:

Values can specify the values of all columns:

For example:

Shell> mysql-u root-p

Mysql> use mytest;

Mysql> insert into worker values ("tom", "tom@yahoo.com"); the word "INTO" is optional since MySQL 3.22.5. (This is also true for other forms of INSERT statements .) VALUES

The table must contain the values of each column in the table and are given in the column storage order. (Generally, this is the sequence in which columns are defined when a table is created. If not, use DESCRIBE tbl_name to view the order .)

Tables uses multiple value tables to provide multiple rows of data at a time.

Mysql> insert into worker values ('Tom ', 'Tom @ yahoo.com'), ('Paul ', 'Paul @ yahoo.com ');

The INSERT... VALUES format with multiple value tables is supported in MySQL 3.22.5 or later versions.

Values can be used to specify the column to be assigned and then list the values. This is useful for creating records that require initial settings for only a few columns.

For example:

Mysql> insert into worker (name) values ('Tom ');

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

Mysql> insert into worker (name) values ('Tom '), ('Paul ');

All columns without names in the column list will be given the default value.

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 ';

A default value is assigned to all rows not named in the SET clause.

INSERT statements in this form cannot INSERT multiple rows.

A comment expression can reference any column previously set in a value table. For example, you can:

Mysql> insert into tbl_name (col1, col2) VALUES (15, col1 * 2 );

But it cannot be like this:

Mysql> insert into tbl_name (col1, col2) VALUES (col2 * 2, 15 );

II. use INSERT... SELECT statement insert selected rows from other tables

When we learn how to create a table in the previous section, we know that you can use select to directly create a table from other tables, or even copy data records at the same time. If you already have a table, you can also get it from the combination of select statements.

Benefits. Input data from other tables, for example:

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

You can also skip the column list of the target table if each column has data input.

Mysql> insert into tbl_name1 select col3, col4 from tbl_name2;

The insert into... SELECT statement meets the following conditions:

The limit query cannot contain an order by clause.

The target table of the explain INSERT statement cannot appear in the FROM clause in the SELECT query section, because this is forbidden in ansi SQL to allow the SELECT statement FROM the table you are inserting. (The problem is that SELECT may find that

Previously inserted records. When a sub-selection clause is used, it is easy to confuse ).

3. use replace, replace... Insert select statement

The REPLACE function is exactly the same as the INSERT function. If an old record in the table has the same value for a new record with a unique index, the old record is deleted before the new record is inserted. In this case, insert

The statement produces an error.

The REPLACE statement can also be used with the Brown SELECT statement. Therefore, the content in the last two sections is suitable for REPALCE ..

Note that the REPLACE statement may change the original record, so be careful when using it.

4. use the LOAD statement to Batch input data

This chapter describes how to use SQL statements to insert data into a table. However, if you need to add many records to a table, it is inconvenient to input data using SQL statements. Fortunately, MySQL provides some methods

It is used to input data in batches, making it easy to add data to the table. This section and the next section describe these methods. This section describes the SQL language-level solutions.

1. Basic syntax

Syntax:

Load data [LOCAL] INFILE 'file_name.txt '[REPLACE | IGNORE] into table tbl_name

The load data infile statement reads a table from a text file at a high speed. If you specify the LOCAL keyword, read the file from the client host. If LOCAL is not specified, the file must be on the server. (LOCAL

MySQL 3.22.6 or later versions are available .)

For security reasons, when reading text files on the server, the files must be in the database directory or can be read by everyone. In addition, to use load data infile for files on the server, you must

File permission is required. See Chapter 7 database security.

The REPLACE and IGNORE keywords control repeated 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 existing row with a unique key.

. If you do not specify any option, an error occurs when the duplicate key is found and the remaining part of the text file is ignored.

If you use the LOCAL keyword to load data from a LOCAL file, the server cannot stop file transmission during the operation. Therefore, the default behavior is as if IGNORE was specified.

2. file search principles

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

If the server returns an absolute path name, the server uses this path name.

If the relative path name of one or more front parts is provided, the server searches for files in the data directory of the server relative to the server.

If a file name without a front-end component is provided, the server searches for a file in the Database Directory of the current database.

Note that these rules mean that a file such as "./myfile.txt" is read from the data directory of the server and written as "prepare myfile.txt". a file is read from the Database Directory of the current database. Also note

Which of the following statements are used to read the db1 file from the database directory instead of db2:

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 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 statement:

Fields terminated by '/t' enclosed by ''escaped '//'

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

Lines terminated by '/N'

In other words, when the default value causes reading input, load data infile performs as follows:

• Seek line boundary at line breaks
• The operator splits the row into fields at the location operator.
• Do not expect fields to be enclosed by any quotation marks
• The delimiter is a part of the literal character of the field value.

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 by double quotation marks. If the row in the file is terminated by a line break

It indicates the fields and row processing options you will use to load files:

Mysql> load data infile 'data.txt 'into table tbl_name

Fields terminated by ', 'enabledby '"'

Lines terminated by '/N ';

Any field or line processing option can specify an empty string (''). If it is not null, the value of FIELDS [OPTIONALLY] enclosed by and fields escaped by must be a single character. FIELDS TERMINATED

And lines terminated by can be more than one character. For example,

Write the row terminated by the carriage return newline (CR + LF) or read a file containing such a row and specify a line terminated by '/r/N' clause.

FIELDS [OPTIONALLY] enclosed by control field surrounded BY characters. For output (SELECT... into outfile), if OPTIONALLY is omitted, all fields are surrounded by enclosed by characters. For such a loss

An example (using a comma as the field separator) 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 OPTIONALLY, the enclosed by character is only used to enclose 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 enclosed by character in a field value is escaped by using the escaped by character as its prefix. Note that if you specify an empty escaped by value

Output read correctly. For example, if the escape character is empty, the output shown above is as follows. Note that the second field in the fourth row contains a comma following the quotation marks. it (incorrectly) seems to end 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 to write or read special characters. 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 fields terminated by and lines terminated by values.
• ASCII 0 (in fact, the subsequent escape characters are written as '0' rather than a zero-value 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 character in the table just given.

For input, if the fields escaped by character is not empty, the appearance of this character is stripped and subsequent characters are literally part of the field value. The exception is an escape "0" or "N" (that is,/0 OR/N, such

If the escape character is "/"). These sequences are interpreted as ASCII 0 (a zero-value byte) and NULL. See the following rules for NULL processing.