Skip to content

Instantly share code, notes, and snippets.

Last active April 19, 2024 11:45
Show Gist options
  • Save hofmannsven/9164408 to your computer and use it in GitHub Desktop.
Save hofmannsven/9164408 to your computer and use it in GitHub Desktop.
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

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

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

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

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

Thanks you! 👍

Copy link

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

Copy link

Thanks. Great Job.

Copy link


Copy link


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

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

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

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

love u! for learn its awesome.

Copy link

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

@EXayer Thanks, I fixed it.

Copy link

Thanks for this one!

Copy link

Thank you.

Copy link

Very helpful 4 Me

Copy link

m-elewa commented Feb 8, 2021

useful reference

Copy link

useful reference

Thank you a lot! This is super helpful!

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