MySQL CLI Cheatsheet

README.md

MySQL

Getting started

• http://www.sqlteaching.com/
• https://www.codecademy.com/learn/learn-sql
• https://www.codecademy.com/catalog/language/sql

Related tutorials

• MySQL-CLI
• Analyzing Business Metrics
• SQL joins infografic

Tools

• TablePlus
• DataGrip
• Sequel Pro (abandoned)

Commands

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

Show all databases: show databases;

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

Create new database: create database [database];

Select database: use [database];

Determine what database is in use: select database();

Show all tables: show tables;

Show table structure: describe [table];

List all indexes on a table: show index from [table];

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

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 a record: INSERT INTO [table] ([column], [column]) VALUES ('[value]', '[value]');

MySQL function for datetime input: NOW()

Selecting records: SELECT * FROM [table];

Explain records: EXPLAIN SELECT * FROM [table];

Selecting parts of records: SELECT [column], [another-column] FROM [table];

Counting records: SELECT COUNT([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)

Updating records: UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];

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];

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 (more info here): mysql -u [username] -p -h localhost [database] < db_backup.sql

Logout: exit;

Aggregate functions

Select but without duplicates: SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00

Calculate total number of records: SELECT SUM([column]) FROM [table];

Count total number of [column] and group by [category-column]: SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];

Get largest value in [column]: SELECT MAX([column]) FROM [table];

Get smallest value: SELECT MIN([column]) FROM [table];

Get average value: SELECT AVG([column]) FROM [table];

Get rounded average value and group by [category-column]: SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];

Multiple tables

Select from multiple tables: SELECT [table1].[column], [table1].[another-column], [table2].[column] FROM [table1], [table2];

Combine rows from different tables: SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];

Combine rows from different tables but do not require the join condition: SELECT * FROM [table1] LEFT OUTER JOIN [table2] ON [table1].[column] = [table2].[column]; (The left table is the first table that appears in the statement.)

Rename column or table using an alias: SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];

Users functions

List all users: SELECT User,Host FROM mysql.user;

Create new user: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant ALL access to user for * tables: GRANT ALL ON database.* TO 'user'@'localhost';

Find out the IP Address of the Mysql Host

SHOW VARIABLES WHERE Variable_name = 'hostname'; (source)

bash_profile

alias mysql=/Applications/MAMP/Library/bin/mysql

my.cnf

[mysqld]
max_allowed_packet=64M

Find out the IP Address of the Mysql Host

SHOW VARIABLES WHERE Variable_name = 'hostname'

resource

A shorter syntax would be select @@hostname;

Nice one

Check the default character set and collation

For a given database:

USE Music;
SELECT @@character_set_database, @@collation_database;

For Schemas via Querying the information_schema.schemata Table:

This eliminates the need to change the default database (like in the previous statement)

SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA 
WHERE schema_name = "schemaname";

For Tables:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "schemaname"
  AND T.table_name = "tablename";

For Columns:

SELECT character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "schemaname"
  AND table_name = "tablename"
  AND column_name = "columnname";

Thanks! Very useful.
Might be useful to change List all users: SELECT User FROM mysql.user;

Very nice collection :), and another tips are:

Import MySQL file to database:

mysql -u <user-name> -p < </full/path/database_import.sql>

Export MySQL file to database:

mysql -u <user-name> -p database > </full/path/database_export.sql>

Show all database sizes:

SELECT table_schema "<MY-DATABASE-NAME>", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema; 

Show all tables sizes for database:

SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = "<MY-DATABASE-NAME>"; 

Thank you :)

Use "--single-transaction" flag with mysqldump command to avoid database locks.

Typo in string below, missed one - '
Inserting a record: INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');

love u! for learn its awesome.

just what I wanted - thanks :)

To contribute something - I haven't seen it mentioned yet: if the output is very scrambled or illegible (e.g. because there are long strings) just use "\G" instead of ";" at the end of the command. Example:

SELECT * FROM mysql.user\G

@EXayer Thanks, I fixed it.

Thanks for this one!

Thank you.

Very helpful 4 Me

useful reference https://devhints.io/mysql

useful reference https://devhints.io/mysql

Thank you a lot! This is super helpful!