Useful Data Manipulation

Deleting Data

So the classic DELETE FROM will empty the table of all its rows, however it will report the number of rows deleted. If you don't need this count then use TRUNCATE TABLE which will be quicker as it does not have to count the number of rows being deleted. You can use a WHERE clause to specify which rows to delete and you can also use LIMIT to set a maximum number of rows to delete, which might be best combined with an ORDER BY clause. Some examples follow:

DELETE FROM my_table; -- delete all rows from the table, report count of rows deleted
TRUNCATE TABLE my_table; -- delete all rows from the table, without count, hence faster
DELETE FROM my_table WHERE column1 > 12; -- delete all rows where the value in column1 is greater than 12
DELETE FROM my_table ORDER BY updated_date LIMIT 1000; -- delete the 1000 oldest rows

Inserting Data

As well as the regular INSERT statement MySQL and MariaDB also support INSERT IGNORE which will ignore duplicate primary keys and INSERT ON DUPLICATE KEY UPDATE which helps you do an "Insert or Update", see INSERT IGNORE - MariaDB Knowledge Base and INSERT ON DUPLICATE KEY UPDATE - MariaDB Knowledge Base for more details.