« MySQL/Manipulation de données » : différence entre les versions

 

Insert value1 into Column1, value2 into Column2, and value3 into Column3:

INSERT INTO TableName (Column1, Column2, Column3)

VALUES (value1, value2, value3)

Insert one record (values are inserted in the order that the columns appear in the database):

INSERT INTO TableName

VALUES (value1, value2, value3)

Insert two records:

INSERT INTO TableName

VALUES (value1, value2, value3), (value4, value5, value6)

INSERT INTO antiques VALUES (21, 01, 'Ottoman', 200.00);

INSERT INTO antiques (buyerid, sellerid, item) VALUES (01, 21, 'Ottoman');

You can also insert records 'selected' from other table.

INSERT INTO table1(field1, field2)

SELECT field1, field2

 

INSERT INTO World_Events SELECT * FROM National_Events

'''Performance tips:'''

 

== UPDATE ==

The syntax is:

UPDATE table SET field = newvalue WHERE criteria ORDER BY field LIMIT n

Examples are:

UPDATE owner SET ownerfirstname = 'John'

WHERE ownerid = (SELECT buyerid FROM antiques WHERE item = 'Bookcase');

UPDATE posts SET deleted=True

ORDER BY date LIMIT 1

With ORDER BY you can order the rows before updating them, and only update a given number of rows (LIMIT).

 

 

== DELETE and TRUNCATE ==

DELETE [QUICK] FROM `table1`

TRUNCATE [TABLE] `table1`

* If you don't use a WHERE clause with DELETE, all records will be deleted.

* It can be very slow in a large table, especially if the table has many indexes.

* After DELETing many rows (about 30%), an OPTIMIZE TABLE command should make next statements faster.

* For a InnoDB table with FOREIGN KEYs constraints, TRUNCATE behaves like DELETE.

DELETE FROM `antiques`

WHERE item = 'Ottoman'

ORDER BY `id`

LIMIT 1

You can order the rows before deleting them, and then delete only a given number of rows.