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

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

Thanks, very helpful!

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.

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

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

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.

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_Name	Brand	Type	Package_Qty	Price	tablename
" . $row['Manufacturer_Name'] . "	" . $row['Brand'] . "	" . $row['Type'] . "	" . $row['Package_Qty'] . "	" . $row['Price'] . "	" . $row['tablename'] . "

";
}else{
echo "NOTE: enter the table name";
}

?>

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

Thanks you! 👍

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

Thanks. Great Job.

Thanks!

Nice!

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!