Cheatsheet - MySQL

Connecting & Command-Line

Connect to a MySQL server from your system's shell:

# Connect as a specific user (will prompt for password)
mysql -u <username> -p

# Connect to a specific database
mysql -u <username> -p <database_name>

# Connect to a remote host
mysql -h <hostname> -u <username> -p

# Execute a single command without entering the client
mysql -u <username> -p -e "SHOW DATABASES;"

`mysql` Client Commands

Once you are inside the mysql> interactive client, you can use these commands. Note that they are not terminated with a semicolon, except for USE.

Command	Description
`SHOW DATABASES;`	List all databases on the server.
`USE <database_name>;`	Select a database to use for subsequent commands.
`SHOW TABLES;`	List all tables in the currently selected database.
`DESCRIBE <table_name>;`	Show the structure of a table (columns, data types, etc.).
`SHOW CREATE TABLE <table_name>\G`	Show the exact `CREATE TABLE` statement used to create the table. `\G` provides a clean, vertical format.
`SHOW WARNINGS;`	Display any warnings generated by the previous command.
`STATUS;` or `\s`	Show the current server status and connection information.
`HELP;`	Display a list of available commands.
`EXIT;` or `QUIT;`	Exit the `mysql` client.
`source <filename.sql>;`	Execute SQL commands from a `.sql` file.

Data Definition Language (DDL)

Commands for defining and managing the structure of your database.

Create a Database:
```
CREATE DATABASE database_name;
```
Drop a Database:
```
DROP DATABASE database_name;
```

Create a Table:

CREATE TABLE table_name (
    column1_name data_type constraints,
    column2_name data_type,
    ...
);

Example: Create a Table with Primary Key & Auto Increment:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Drop a Table:
```
DROP TABLE table_name;
```
Alter a Table:
- Add a column: ALTER TABLE table_name ADD COLUMN new_column_name data_type;
- Drop a column: ALTER TABLE table_name DROP COLUMN column_name;
- Modify a column's data type: ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;
- Rename a column: ALTER TABLE table_name RENAME COLUMN old_name TO new_name; (Newer MySQL versions)
- Add an index: ALTER TABLE table_name ADD INDEX index_name (column_name);

Data Manipulation Language (DML)

Commands for adding, modifying, and removing data.

Insert a Single Row:

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Insert Multiple Rows:

INSERT INTO table_name (column1, column2) VALUES
    (value1a, value2a),
    (value1b, value2b);

Update Records:
```
UPDATE table_name SET column1 = new_value WHERE condition;
```
Warning: Always use a WHERE clause with UPDATE unless you intend to update all rows.
Delete Records:
```
DELETE FROM table_name WHERE condition;
```
Warning: Always use a WHERE clause with DELETE unless you intend to delete all rows.

Data Query Language (DQL) - The `SELECT` Statement

Commands for retrieving data.

Select All Columns: SELECT * FROM table_name;
Select Specific Columns: SELECT column1, column2 FROM table_name;
Filtering with WHERE: SELECT * FROM table_name WHERE column_name > 100;
- Operators: =, !=, >, <, >=, <=, LIKE, IN, BETWEEN, IS NULL, IS NOT NULL
- Combining: AND, OR, NOT
Sorting with ORDER BY: SELECT * FROM table_name ORDER BY column_name ASC|DESC;
Limiting Results: SELECT * FROM table_name LIMIT 10;
Limiting with an Offset: SELECT * FROM table_name LIMIT 5 OFFSET 10; (Skips 10 records, returns the next 5)
Unique Values: SELECT DISTINCT column_name FROM table_name;

Aggregate Functions & Grouping

Used to perform calculations on a set of rows.

Common Functions: COUNT(), SUM(), AVG(), MAX(), MIN()
Counting All Rows: SELECT COUNT(*) FROM table_name;
Grouping Data: SELECT category, COUNT(*) FROM products GROUP BY category;

Filtering Groups with HAVING:

SELECT category, COUNT(*) FROM products
GROUP BY category
HAVING COUNT(*) > 5;

Joins

Combine rows from two or more tables.

Inner Join: Returns records that have matching values in both tables.

SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

Left Join: Returns all records from the left table, and the matched records from the right table.

SELECT customers.name, orders.id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Right Join: Returns all records from the right table, and the matched records from the left table.

SELECT customers.name, orders.id
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

Common Data Types

Category	Data Types
Numeric	`INT`, `BIGINT`, `TINYINT`, `DECIMAL(precision, scale)`, `FLOAT`, `DOUBLE`
String	`VARCHAR(size)`, `CHAR(size)`, `TEXT`, `LONGTEXT`
Date/Time	`DATE`, `TIME`, `DATETIME`, `TIMESTAMP`
Boolean	`TINYINT(1)` (Use `0` for false, `1` for true)
JSON	`JSON` (As of MySQL 5.7)

User and Permission Management

Create a User:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Drop a User:
```
DROP USER 'username'@'host';
```

Grant Privileges:

-- Grant all privileges on a specific database
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

-- Grant specific privileges on a table
GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'host';

Revoke Privileges:

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

Apply Privilege Changes:
```
FLUSH PRIVILEGES;
```

Backup and Restore (Command-Line)

Use these utilities from your system's shell, not the mysql client.

Backup a Database (mysqldump):

mysqldump -u <username> -p <database_name> > backup_file.sql

Backup All Databases:

mysqldump -u <username> -p --all-databases > all_databases.sql

Restore a Database:

# First, create an empty database if it doesn't exist
mysql -u <username> -p -e "CREATE DATABASE database_name;"

# Then, import the data
mysql -u <username> -p <database_name> < backup_file.sql

Users and Privileges

# Show all the users
mysql> select host, user, password from mysql.user;

# Create a new user
mysql> CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

# Grant privileges
mysql> GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost';

# Show grants:
mysql> show grants FOR 'admin'@'localhost';

Database

# Show databases
mysql> show databases;

# Create databases
mysql> create database publications;

# Choose databases
mysql> use publications;

# Grant permission:
mysql> grant all on publications.* to 'root' identified by 'mypasswd';

Table

# Show Table List:
mysql> show tables;

# Describe a Table:
mysql> describe classics;

# Create a Table:
mysql> create table classics (
    -> author varchar(128),
    -> title varchar(128),
    -> type varchar(16),
    -> year char(4)) ENGINE MyISAM;

# Rename a Table:
mysql> alter table pre1900 rename classics;

# Drop table:
mysql> drop table classics;

# Change Engine:
ALTER TABLE tablename ENGINE = MyISAM;

Column

# Add Column:
mysql> alter table classics add id int unsigned not null auto_increment key;

# Remove Column:
mysql> alter table classics drop id;

# Change Column Data Type:
mysql> alter table classics modify year smallint;

# Rename Column(change from 'type' to 'category', need to specify the data type):
mysql> alter table classics change type category varchar(16);

Row

# Add Row:
mysql> insert into classics(author, title, type, year)
    -> values('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');

# Update Row:
mysql> UPDATE classics SET isbn='9781598184891' WHERE year='1876';

# Select:
mysql> select * from classics;

Index

mysql> alter table classics add index(author(20));
mysql> create index author on classics (author(20));
(CREATE INDEX cannot be used to create a PRIMARY KEY)

CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
category VARCHAR(16),
year SMALLINT,
INDEX(author(20)),
INDEX(title(20)),
INDEX(category(4)),
INDEX(year)) ENGINE MyISAM;

Add FULLTEXT Index
ALTER TABLE classics ADD FULLTEXT(author,title);

Transactions

use ENGINE InnoDB

BEGIN;
UPDATE accounts SET balance=balance+25.11 WHERE number=12345;
COMMIT;

ROLLBACK;

Explain

EXPLAIN SELECT * FROM accounts WHERE number='12345';

Back up

LOCK TABLES tablename1 tablename2 ... READ
mysqldump -u user -p password database
UNLOCK TABLES;

mysqldump

$ mysqldump -u user -ppassword publications > publications.sql

Example

To add primary key(not null), edit each row then add primary key.

ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn='9781598184891' WHERE year='1876';
...
ALTER TABLE classics ADD PRIMARY KEY(isbn);

Calculate Sizes

SELECT TABLE_NAME, table_rows, data_length, index_length,  round(((data_length + index_length) / 1024 / 1024),2) \"Size in MB\" FROM information_schema.TABLES
WHERE table_schema='${a}';

To call it in a bash script

#!/bin/bash

for db in `mysql -h db0 -u username -e "show databases;"`
do
    mysql -h db0 -u username -e "SELECT TABLE_NAME, table_rows, data_length, index_length,  round(((data_length + index_length) / 1024 / 1024),2) \"Size in MB\" FROM information_schema.TABLES WHERE table_schema='${db}';"
done

Print Tables

#!/bin/bash
DBS=`mysql -uroot  -e"show databases"`
for b in $DBS ;
do
    mysql -uroot -e"show tables from $b"
done

MySQLAdmin

$ mysqladmin -h host -u root -p variables

Install MySQL Server

$ sudo yum install mysql-server

Start mysqld

$ sudo /sbin/service mysqld start

Install MySQL

$ sudo yum install mysql

$ mysql -u <user_name> -p