MySQL

Syntax

• Select: SELECT column1, column2, ... FROM table_name WHERE condition1;
• Select with AND condition: SELECT column1 FROM table_name WHERE condition1 AND condition2;
• Select with OR condition: SELECT column1 FROM table_name WHERE condition1 OR condition2;
• Select with NOT: SELECT column1 FROM table_name where NOT condition1;
• Order by ASC/DESC: SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
• Null/Not Null: SELECT column_names FROM table_name WHERE column_name IS|IS NOT NULL;
• Update: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
• Delete: DELETE FROM table_name WHERE condition;
• Having clause for use with aggregate functions: SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

Commands

• Quit: \q
• Example db loading date; gunzip < filename.sql.gz | mysql -u user database_name; date
• Example db dumping
  • mysqldump -u user database_name --tables table_name|gzip -9 > filename.sql.gz
  • mysqldump -u user -h localhost database_name | gzip -9 > filename.sql.gz
  • mysqldump --single-transaction --column-statistics=0 -u user -h host --ssl-mode=DISABLED -p database_name | gzip > db_`date +%F`.sql.gz
  • mysqldump --single-transaction -u user -h localhost database_name | gzip > db_`date +%F`.sql.gz