Cheat Sheet - MySQL

# Login in as admin/root user
mysql -u admin -h my-cluster-dev.cluster-cckanhlmgu2w.eu-central-1.rds.amazonaws.com --database=customer_cms_dev -p

# Create database
CREATE DATABASE customer_cms_dev;

# Create user and grant permissions on a database
CREATE USER 'customer_cms_dev'@'%' IDENTIFIED BY 'idjCXg3XBrz2NL';
GRANT ALL PRIVILEGES ON customer_cms_dev.* TO 'customer_cms_dev'@'%';
flush privileges;

# Log in as db user
mysql -u customer_cms_dev -h my-cluster-dev.cluster-cckanhlmgu2w.eu-central-1.rds.amazonaws.com --database=customer_cms_dev -p

Common commands

Create a database.
  mysql> create database [databasename];

List all databases on the server.
  mysql> SHOW databases;

Switch to a database.
  mysql> use [db name];

To see all the tables in the db.
  mysql> SHOW tables;

To see table's field formats.
  mysql> describe [table name];

Show all data from a table.
  mysql> SELECT * FROM [table name];

To return columns and column information.
  mysql> SHOW columns from [table name];

Show particular rows with the given value.
  mysql> SELECT * FROM [table name] WHERE [field name] = "value";

Show all records containing the name "Something" AND the phone number '0123456789'.
  mysql> SELECT * FROM [table name] WHERE name = "Something" AND phone_number = '0123456789';

Show all records not containing the name "Something" AND the phone number '0123456789' order by the phone_number field.
  mysql> SELECT * FROM [table name] WHERE name != "Something" AND phone_number = '0123456789' order by phone_number;

Show all records starting with the letters 'Something' AND the phone number '0123456789'.
  mysql> SELECT * FROM [table name] WHERE name like "Something%" AND phone_number = '0123456789';

Show all records starting with letters 'Something' AND the phone number '0123456789' limit to records 1 through 5.
  mysql> SELECT * FROM [table name] WHERE name like "Something%" AND phone_number = '0123456789' limit 1,5;

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
  mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Show unique records.
  mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).
  mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.
  mysql> SELECT COUNT(*) FROM [table name];

Sum column.
  mysql> SELECT SUM(*) FROM [table name];

Permissions

Show privileges for a specific MySQL user.
  SHOW GRANTS FOR 'root'@'localhost';

Show user privileges for all MySQL users.
  SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;

You could copy and paste each statement and execute each line to get a list. That is a bit fiddly and time consuming. 
One way of achieving this more quickly would be to output the results to a file and then read the file back in line by line.

You can use OUTFILE and SOURCE to do this.
  SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user INTO outfile '/tmp/show_grants.txt';

Now read the file you have just produced
  SOURCE /tmp/show_grants.txt

Destructive commands

To delete a db.
  mysql> drop database [database name];

To delete a table.
  mysql> drop table [table name];