How to input data into a MySQL database table

When you have created a database and a table, you first think of entering data into the database table. this involves how to add data to the database. next we will discuss this question: 1. in general, the commonly used method is the insert Statement (assuming that all versions are not very low ). she has the following forms: insertintotablenamevalues (value1

When you have created a database and a table, you first think of entering data into the database table. this involves how to add data to the database. next we will discuss this question: 1. in general, the commonly used method is the insert Statement (assuming that all versions are not very low ). she has the following forms: insert into tablename values (value1

When you have created a database and a table, the first thing you think of is to input data into the database table. This involves how to add data to the database. Next we will discuss this question:

1. The general method we use is the insert Statement (assuming that your version is not very low). She has the following forms:

Insert into tablename values (value1, value2 ,...)

Insert into tablename (fieldname1, fieldname2,...) values (value1, value2 ,...)

Insert into tablename set fieldname1 = value1, fieldname2 = value2 ,...

Insert into tablename (fieldname1, fieldname2) select fieldname1, fieldname2 from tablename1

The above basically lists how to insert data with insert. Of course, you can also use insert statements to insert several rows into a table at the same time, as shown below:

Insert into tablename values (...),(...),...

2. in the mysql runtime environment, we can also use the load data statement to read the DATA in the file into the table. generally, this file is directly read by the server on the host. therefore, you must have the file permission and the file must be fully readable. if your version is new enough, you can provide local information. As you read the file from the client and transmit the content to the server, you do not need the file permission.

If you do not provide a local address, the server locates it as follows:

1) if your filename is an absolute path, the server will start searching for the file from the root directory.

2) If your filename is a relative path, the server will start searching for the file from the database data directory.

If you provide local, the file will be located as follows:

1) if your filename is an absolute path, the client will start searching for the file from the root directory.

2) If your filename is a relative path, the client will start searching for the file from the current directory.

After talking about it for a long time, maybe you still don't understand the specific format. Let's look at it first:

Load data [LOCAL] INFILE 'filename' into table tablename import_options [(fieldname_list)]

The import options syntax is:

Fields

Terminated by 'Char'

Enclosed by 'Char'

Escaped by 'Char'

Lines

Terminated by 'string'

Below we will explain it:

1) fields terminated by char specifies the characters of the columns to be separated. The missing time is assumed that the column values are separated by tabs.

2) fields enclosed by char indicates that the column value should be included in the specified character. quotation marks are usually used. Missing time is saved. It is assumed that the column value is not included in any character.

3) fields escaped by char indicates the escape character used to escape special characters. Missing time indicates no escape character

4) lindes escaped by string specifies the string to end the input line (which can be multiple characters). The line break is assumed to terminate the line break.

Note the use of escape characters. for example, 'indicates single quotation marks, "indicates double quotation marks, and so on. there are also some options not mentioned here, such as ignore n lines, which can control the first n rows of the file not to be read into the database. for more information, see mysql Chinese reference.

After talking about the above, let's take a look at a specific line of commands:

Load data [local] infile "sample.txt" into table sample fields terminated by "," enclosed """

3. in the system command line, you can use the mysqlimport utility to read the content of the text file to an existing table (in fact, mysqlimport is used to implement the load data command line interface ), after mysqlimport is called, she generates a load data statement. the syntax is basically the same as load data:

Mysqlimport [options] sample_db filename

Similarly, let's take a look at the command:

Mysqlimport Cu root [-- local] Cfields-terminated-by = ","-fields-enclosed-by = "samp_db samp_table.txt

(REMARK: samp_db in this statement is the specified database. The table for data entry is determined by the file name parameter. remove any extension of the file name, leaving the table name. therefore, the name of the table entered in this sentence is samp_table. that is to say, when executing this statement, we should first make sure that the table samp_table exists. in addition, if you have multiple files that need to be loaded into the same table, you can use samp_table.n.txt for processing)