Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL CLI Cheatsheet

MySQL

Getting started

Related tutorials

Tools

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)

alias mysql=/Applications/MAMP/Library/bin/mysql
[mysqld]
max_allowed_packet=64M
@OlukaDenis
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?

@molekulerbiyolog
Copy link

molekulerbiyolog commented May 28, 2018

Thanks a lot!!!!

@bradw2k
Copy link

bradw2k commented May 30, 2018

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

@dash025
Copy link

dash025 commented Jun 11, 2018

Thanks, very helpful!

@jamalahmedmaaz
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.

Request/Suggestion.

Thanks for sharing the cheatsheet.

@Axe147
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

@samjco
Copy link

samjco commented Aug 3, 2018

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

@DamienPirsy
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.

@saitejayelubolu
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
1.tableone
2.tabletwo
3.tablethree

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

$serverName='localhost';
$userName='root';
$password='';
$databaseName='mydatabase';

//create connection
$connection=mysqli_connect($serverName,$userName,$password,$databaseName);

//check connection

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

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

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

?>

@manojkumarlinux
Copy link

manojkumarlinux commented Oct 5, 2018

change column

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

(Or)

More column using down

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

Thanks

@condescrim
Copy link

condescrim commented Oct 8, 2018

Thanks you! 👍

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

@JStoreInTheHills
Copy link

JStoreInTheHills commented Jan 8, 2019

Thanks. Great Job.

@123xylem
Copy link

123xylem commented Jan 14, 2019

Thanks!

@AaronCHH
Copy link

AaronCHH commented Jan 19, 2019

Nice!

@danfoust
Copy link

danfoust commented Feb 8, 2019

Find out the IP Address of the Mysql Host

SHOW VARIABLES WHERE Variable_name = 'hostname'

resource

A shorter syntax would be select @@hostname;

@technical-newfrontierdata

Nice one

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

@pranjaljately
Copy link

pranjaljately commented Apr 23, 2019

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

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

@udayakumar-ziffity
Copy link

udayakumar-ziffity commented May 28, 2019

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

@EXayer
Copy link

EXayer commented Jun 12, 2019

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

@Iicytower
Copy link

Iicytower commented Jan 13, 2020

love u! for learn its awesome.

@Ticiano-mw
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

@hofmannsven
Copy link
Author

hofmannsven commented May 12, 2020

@EXayer Thanks, I fixed it.

@santiagoelie
Copy link

santiagoelie commented Jun 2, 2020

Thanks for this one!

@matthew123987
Copy link

matthew123987 commented Nov 12, 2020

Thank you.

@sharwankr
Copy link

sharwankr commented Jan 30, 2021

Very helpful 4 Me

@m-elewa
Copy link

m-elewa commented Feb 8, 2021

useful reference https://devhints.io/mysql

@wanxinran
Copy link

wanxinran commented Nov 10, 2022

useful reference https://devhints.io/mysql

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