Common useful SQL commands in MySQL

Safe Update Mode:

If Safe Update Mode is enabled in MySQL, then you may encounter an error "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column." when you use UPDATE or DELETE statements without a WHERE keyword (Sometimes my statements contain WHERE, but still got this error, does anyone know why?). When you see this error, you can solve it by turning off safe update mode. There are two ways to do so:

1. You can put "SET SQL_SAFE_UPDATES=0;" in the beginning of your scripts;
2. You can turn off safe update mode from workbench, by doing so, you don't need to put "SET SQL_SAFE_UPDATES=0;" in your scripts every time. Edit -> Preferences -> SQL Editor. Scroll down, you will find" Safe Updates(rejects UPDATEs and DELETEs with no restrictions)" checkbox. Uncheck the checkbox, you won't have the troubles anymore.

Delete table from database:

DELETE FROM Database.Table;

Find rows with duplicated keys:

SELECT

    Name,                                                     -- 

    COUNT(Name)                                      -- Here give the count of duplicate number

FROM

    Database.Table

GROUP BY Name                                     -- Group rows with HAVING condition

HAVING COUNT(Name) > 1;

Dump table from database:

mysqldump -u usernane -p password database_name table1 table2 table3 > tables.sql