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  ,...)]