MySQL Learning Note 005 -- Adding, deleting and updating

Add data to tables

INSERT INTO table_name VALUES(col_1, col_2, col_3, ...); 

// 按顺序填入所有fields，字符串要加引号, 每个field之间要加逗号，结尾要加分号

• Col values after VALUES have to strictly follow the table schema
  • if schema changes, SQL breaks
• Use null when the field is auto-generated
  • override auto value by manually providing a valuef
• Use ignore to disregard duplicate key error -- RARE

INSERT IGNORE INTO .....

Insert multiple lines

INSERT INTO table_name VALUES (col_1, col_2, col_3, ...), (col_1, col_2, col_3, ...), (col_1, col_2, col_3, ...), ...; 

// 多个record用逗号隔开

Bulk insertion output:

Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

• rows affected: rows successfully inserted
• records: rows processed (may include failed ones)
• duplicates: rows that are duplicated but inserted by ignore.

Insert without col_name order

INSERT INTO table_name (col_1_name, col_2_name, col_3_name, ...) VALUES (col_1, col_2, col_3, ...); 

// specify column names in '()' after table name

• col_name is specified in "()", values are specified after VALUES keyword, need to match
  • No need to specify col_name in order (corresponding to schema)
  • No need to insert every columns into the table
    • unspecified values will be set to default value by mysql (otherwise null)
    • Insert DEFAULT to use the default value

Insert ... set ...

INSERT INTO table_name SET 
    col_1_name = val_1, 
    col_2_name = val_2;

---

Deleting all the rows in a table

DELETE FROM table_name;

• delete does NOT remove the table
• delete clear table by removing records one by one
• if the table has a relationship with another table, the delete might fail

TRUNCATE TABLE [table_name];

• truncate drop and re-create the table
• truncate does NOT remove records one by one
• truncate cannot rollback
• truncate is faster than delete

Selected deletion

• You can include all the conditions after WHERE to perform a more selected deletion

DELETE FROM [table_name] WHERE [condition];

Inner join + Delete

• inner join tables to bring in information the selection criteria

SELECT [col_names] FROM [table_name_1] 
    INNER JOIN [table_name_2] 
    ON table_name_1.field = table_name_2.field 
    WHERE [selection_condition]
    

• after joining, delete based on the new criteria

DELETE FROM [table_name] WHERE [condition];

limit the number of rows deleted

• delete前amount个record

DELETE FROM [table_name] ORDER BY [col_name] LIMIT [amount];

---

Update all rows at the same time

UPDATE [table_name] SET [col_name = val];

Selected update

UPDATE [table_name] SET [col_name = val]
    WHERE [condition]
    ORDER BY [col_name]
    LIMIT [amount];