MySQL Tutorial

[ Directory ]

Summary

In this chapter, we looked at ways to insert, delete, and update data from the tables in our database.

Inserting Data

String values should be in quotes. Single quotes or backslashes within a string need to be escaped with a backslash.

Add data to tables with the INSERT statement:

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES ((expression | DEFAULT),...),(...),...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...

or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=(expression | DEFAULT), ...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

The REPLACE statement is just like INSERT, but it overwrites rows where a key clash occurs. INSERT fails or triggers the ON DUPLICATE KEY UPDATE clause when a key clash occurs.

Deleting Data

Avoid disasters with --i-am-a-dummy.

Delete data from tables with the DELETE statement:

   DELETE [LOW_PRIORITY] [QUICK] FROM table_name
          [WHERE where_definition]
          [ORDER BY ...]
          [LIMIT rows]
or DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...]
          FROM table-references
          [WHERE where_definition]
or DELETE [LOW_PRIORITY] [QUICK]
          FROM table_name[.*] [, table_name[.*] ...]
          USING table-references
          [WHERE where_definition]

The TRUNCATE TABLE statement deletes all rows from a table.

Updating Data

Update data in tables with the UPDATE TABLE statement:

   UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
       SET col_name1=expr1 [, col_name2=expr2 ...]
       [WHERE where_definition]
       [ORDER BY ...]
       [LIMIT rows]
or UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
       SET col_name1=expr1 [, col_name2=expr2 ...]
       [WHERE where_definition]

LOAD DATA INFILE

Use LOAD DATA INFILE to load the contents of a text file into a table:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(col_name,...)]

[ Directory ]