AWS EC2 & RDS

ssh -i ".pem" <user@public IP>
mysql -h <rds endpoint> -u <username> -p

MySQL Cheat Sheet

Can be used as payloads for SQL injection

# Access terminal
$ mysql -u [username] -p (prompt password)

# Logout
$ MySQL> exit;

# SHOW databases
$ MySQL> show databases;

# CREATE databases
$ MySQL> create database [dbname];
	# or
$ MySQL> CREATE DATABASE [dbname];

# Choose database
$ MySQL> use [dbname];

# Determine what db is in use
$ MySQL> select database();
	# or
$ MySQL> SELECT database();

# SHOW tables
$ MySQL> show tables;

# Display table struct
$ MySQL> describe [tablename];

# List indexes on a table
$ MySQL> show index from [tablename];

# CREATE table with columns
$ MySQL> CREATE TABLE IF NOT EXISTS [tablename]([column1] INT NOT NULL AUTO_INCREMENT PRIMARY KEY, [column2] VARCHAR(255)) ENGINE=INNODB;

$$MySQL db default storage engine is MyISAM, it does not support transactions by tables.
$$InnoDB supports row level locking, inserting and updating is much faster.

# Edit table
$ MySQL> ALTER TABLE [tablename] ADD COLUMN [column] text;

# Inserting record
$ MySQL> INSERT INTO [tablename] ([column1], [column2]) VALUES ('[value1]','[value2]');

# EXPLAIN (describes how SELECT will be processed including info about JOINS)
$ MySQL> EXPLAIN SELECT * FROM [tablename];

# EXPLAIN EXTENDED (estimates number of table rows that are filterd by the condition)
// Need more research

# SELECT *
$ MySQL> SELECT * FROM [tablename];

# SELECT [column]
$ MySQL> SELECT [column] FROM [tablename];

# Count records of [column]
$ MySQL> SELECT COUNT([column]) FROM [tablename];

# Counting and selecting grouped records of [column]
$ MySQL> SELECT *, (SELECT COUNT([column]) FROM [table name]) AS count FROM [tablename] GROUP BY [column];

# Selectors: <, >, =,!=; combine with AND, OR
$ MySQL> SELECT * FROM [table] WHERE [column] = [value]

# Containing [value]
$ MySQL> SELECT * FROM [tablename] WHERE [column] LIKE '%[value]%'

# Start with [value]
$ MySQL> SELECT * FROM [tablename] WHERE [column] LIKE '[value]%'

# Staring with val and end with ue
$ MySQL> SELECT * FROM [tablename] WHERE [column] LIKE '[val_ue]'

# Select range
$ MySQL> SELECT * FROM [tablename] WHERE [column] BETWEEEN [value1] and [value2];

# Select order (DESC, ASC)
$ MySQL> SELECT * FROM [tablename] WHERE [column] ORDER BY [column] ASC LIMIT [value]; 

# Update record
$ MySQL> UPDATE [tablename] SET [column] = '[new value]' WHERE [column] = [value];

# Delete record
$ MySQL> DELETE FROM [tablename] WHERE [column] = [value];

Dangerous

# Delete all record without dropping table (resets counter)
$ MySQL> DELETE FROM [tablename];

# Delete all records in a table
$ MySQL> truncate table [tablename];

# Remove column
$ MySQL> ALTER TABLE [tablename] DROP COLUMN [column];

# Delete Table
$ MySQL> DROP TABLE [tablename];

# Delete db
$ MySQL> DROP DATABASE [dbname];