Shulou(Shulou.com)06/01 Report--

The following mainly brings you the basic operation of inserting data in MYSQL database. I hope these contents can bring you practical use, which is also the main purpose of this article that I edit the basic operation of inserting data in MYSQL database. All right, don't talk too much nonsense, let's just read the following.

Insert data

When creating an empty database and data table, the first thing to consider is how to add data to the data table, which can be done using the insert statement. Use the insert statement to insert a new row into an existing data table, that is, to insert a new row of records.

In MySQL, there are three syntax formats for INSERT statements, which are INSERT... VALUES statement, INSERT... SET statement and INSERT... SELECT statement.

Use INSERT... VALUES statement inserts data

Use INSERT... VALUES statements insert data, which is the most commonly used syntax format of INSERT statements.

The syntax format is as follows:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] Datasheet name [(field name,...)]

VALUES ({value | DEFAULT},...), (...),...

[ON DUPLICATE KEY UPDATE field name = expression,...]

Example:

Create database test; # create a library

Use test; # enter the library

Create table info (id int not null,name char (16), score decimal (5jue 2), age int (8); # create a table

Insert test data

Insert into info (id,name,score,age) values

Insert into info (id,name,score,age) values (2 records48 and 31)

Insert into info (id,name,score,age) values

Insert into info (id,name,score,age) values (4 recordings, pwelling pwagons, 98, 25)

Insert into info (id,name,score,age) values (5 recorders, wlkstores, 19,537)

Insert into info (id,name,score,age) values (3 recordsLihuaZhi 58 Magi 23)

SELECT * FROM info

Use INSERT... SET statement inserts data

In MySQL, in addition to using INSERT... VALUES statements can be inserted in addition to data, and you can also use INSERT... The SET statement inserts data. This syntax format is used to insert specified data by directly assigning corresponding values to some fields in the table, and default values are used to add fields that do not specify values.

INSERT... The syntax format of the SET statement is as follows:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] Datasheet name

SET field name = {value | DEFAULT},...

[ON DUPLICATE KEY UPDATE field name = expression,...]

Example:

Insert into info SET id = 8, name='xiaohong',score=88,age=56

SELECT * FROM info

Insert query results

In MySQL, it is supported to insert query results into a specified data table, which can be achieved through the > INSERT...SELECT statement.

The syntax is as follows:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] Datasheet name [(field name,...)]

SELECT...

[ON DUPLICATE KEY UPDATE field name = expression,...]

Example:

Insert into info (id,name,score,age) SELECT * from info where id=4

SELECT * FROM info

Modify data

To perform the modified operation, you can use the update statement

The syntax is as follows:

UPDATE [LOW_PRIORITY] [IGNORE] Datasheet name

SET field 1 = value 1 [, field 2 = value 2.]

[WHERE conditional expression]

[ORDER BY...]

[number of LIMIT rows]

Example:

UPDATE info set score=77 where age truncate > delete.

The 7.delete statement is the database operation language (dml). This operation is put into rollback segement and takes effect only after the transaction is committed; if there is a corresponding trigger, it will be triggered when executed.

8.truncate and drop are database definition languages (ddl). The operation takes effect immediately. The original data is not put into rollback segment, cannot be rolled back, and the operation does not trigger trigger.

For the above basic operation of inserting data into MYSQL database, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.