mySQL Commands:
----------------

LIST:
-------------
Show all databases: show databases;
Show all tables: show tables;
Show table structure: describe [table];
List all indexes on a table: show index from [table];

CREATING:
-------------
Create new database: create database [database];
Create new table with columns: CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);

ADDING:
-------------
Adding a column: ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);
Adding a column with an unique, auto-incrementing ID: 
        ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

INSERTING:
-------------
Inserting a record: INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');

SELECTING:
-------------
Select database: use [database];
Determine what database is in use: select database();
Selecting records: SELECT * FROM [table];
Selecting parts of records: SELECT [column], [another-column] FROM [table];
Counting and selecting grouped records: 
    SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];
Selecting specific records: 
    SELECT * FROM [table] WHERE [column] = [value]; (Selectors: <, >, !=; combine multiple selectors with AND, OR)
Select records containing [value]: SELECT * FROM [table] WHERE [column] LIKE '%[value]%';
Select records starting with [value]: SELECT * FROM [table] WHERE [column] LIKE '[value]%';
Select records starting with val and ending with ue: SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';
Select a range: SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];
Select with custom order and only limit: 
    SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value]; (Order: DESC, ASC)

ACCESSING:
-------------
Access monitor: mysql -u [username] -p; (will prompt for password)
Access database: mysql -u [username] -p [database] (will prompt for password)

MISC COMMANDS:
--------------
MySQL function for datetime input: NOW()
Explain records: EXPLAIN SELECT * FROM [table];
Counting records: SELECT COUNT([column]) FROM [table];
Updating records: UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];
Custom column output names: SELECT [column] AS [custom-column] FROM [table];
Export a database dump (more info here): mysqldump -u [username] -p [database] > db_backup.sql
Use --lock-tables=false option for locked tables (more info here).
Import a database dump: mysql -u [username] -p -h localhost [database] < db_backup.sql
Logout: exit;

DELETING:
------------
Deleting records: DELETE FROM [table] WHERE [column] = [value];
Delete all records from a table (without dropping the table itself): 
    DELETE FROM [table]; (This also resets the incrementing counter for auto generated columns like an id column.)
Delete all records in a table: truncate table [table];
Removing table columns: ALTER TABLE [table] DROP COLUMN [column];
Deleting tables: DROP TABLE [table];
Deleting databases: DROP DATABASE [database];