Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
MySQL CLI Cheatsheet


Getting started

Related tutorials



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)

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

serhiihoroshko commented May 14, 2018 has 404 on codecademy link. Now SQL for codecademy Thanks you

Copy link

OlukaDenis commented May 16, 2018

How can I create two alias names for more than one column?
I tried this;
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
and it was giving me an error.
what is the best way to manipulate that command?

Copy link

molekulerbiyolog commented May 28, 2018

Thanks a lot!!!!

Copy link

bradw2k commented May 30, 2018

Very good! Need to add -h to the first line, for selecting the host machine.

Copy link

dash025 commented Jun 11, 2018

Thanks, very helpful!

Copy link

jamalahmedmaaz commented Jul 8, 2018

Nice Post,

  1. Is it possible to add summarization and window functions?
  2. In the real world, any project will require these functionalities.
  3. Also similar to Postgres, how would you do create channels and listen on channels.
  4. How to get the current transaction id. building a use case around and explain with the cheat sheet, will help everyone.


Thanks for sharing the cheatsheet.

Copy link

Axe147 commented Jul 20, 2018

I am new to this, Can someone help me out with the tricks on how to get command together for execution using Mysql
I have studied the command for my SQL but i don't know how to get them together for execution
I have everything ready

Copy link

samjco commented Aug 3, 2018

It would be nice if you can break the text from the commands for a better read.

Copy link

DamienPirsy commented Sep 19, 2018

It's worth noting that the GRANT FILE privilege isn't included into the GRANT ALL, since this privilege is given GLOBALLY to the user and cannot be given to a specific database.
GRANT FILE ON *.* TO 'root'@'localhost';
affects every database of the system (since the user needs to write on the filesystem space, not the table space).
So, if you need to make a LOAD DATA INFILE or SELECT...INTO OUTFILE you need to explicitly give this permission to the database user that requires it.

Copy link

saitejayelubolu commented Sep 19, 2018

how to fetch tables data from the database using mysql php html?

In my database totally three tables are there

so, here my question is

from the html search bar, i need to search the table data

in the html search bar when i type "tabletwo" it have to display the tabletwo data and when i type "tablethree" it has to display tablethree data
Note : One more thing from these three column names are same. And in the "tablename" column 'table name' is data for every row in that column.

below code is searching only 1 table only not for multiple tables

<DOCTYPE! html>

//set variables


//create connection

//check connection

/* if (!$connection){
die("connection failed: ".mysqli_connect_error());
echo "connected successfully!!!
"; */

$value = $_POST['Valuetosearch'];
$sql2 = "SELECT * FROM tableone WHERE tablename='$value'";
$result = mysqli_query($connection, $sql2);

echo "";
while($row = mysqli_fetch_array($result)){
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "
" . $row['Manufacturer_Name'] . "" . $row['Brand'] . "" . $row['Type'] . "" . $row['Package_Qty'] . "" . $row['Price'] . "" . $row['tablename'] . "
echo "NOTE: enter the table name";


Copy link

manojkumarlinux commented Oct 5, 2018

change column

                ALTER TABLE "table_name" CHANGE "column_old_name" "column_new_name" varchar(50);


More column using down

    ALTER TABLE "table_name" CHANGE "column 1" "change_column 1" ["Data Type"], CHANGE "column 2" "change_column 2" ["Data Type"];


Copy link

condescrim commented Oct 8, 2018

Thanks you! 👍

Copy link

saarques commented Dec 18, 2018

To add a column with a default value:
ALTER TABLE [table name] ADD COLUMN [column name] [int()/varchar()] default [value];

Copy link

JStoreInTheHills commented Jan 8, 2019

Thanks. Great Job.

Copy link

123xylem commented Jan 14, 2019


Copy link

AaronCHH commented Jan 19, 2019


Copy link

danfoust commented Feb 8, 2019

Find out the IP Address of the Mysql Host

SHOW VARIABLES WHERE Variable_name = 'hostname'


A shorter syntax would be select @@hostname;


Nice one

Copy link

NormanEdance commented Feb 20, 2019

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,
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";

Copy link

pranjaljately commented Apr 23, 2019

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

Copy link

noncent commented May 21, 2019

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 :)

Copy link

udayakumar-ziffity commented May 28, 2019

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

Copy link

EXayer commented Jun 12, 2019

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

Copy link

Iicytower commented Jan 13, 2020

love u! for learn its awesome.

Copy link

Ticiano-mw commented May 12, 2020

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

Copy link

hofmannsven commented May 12, 2020

@EXayer Thanks, I fixed it.

Copy link

santiagoelie commented Jun 2, 2020

Thanks for this one!

Copy link

matthew123987 commented Nov 12, 2020

Thank you.

Copy link

sharwankr commented Jan 30, 2021

Very helpful 4 Me

Copy link

m-elewa commented Feb 8, 2021

useful reference

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment