Blog

MySQL CLI Cheatsheet

Rahmat Subandi / 25 August, 2021

4 min read • ––– views •

Greetings!

Hello everyone, on this occasion and in this discussion I want to share a brief cheat sheet regarding the MYSQL CLI command. Let's get started! 🐱‍💻

Getting started

  • SQL Teaching
  • Learn SQL
  • Languange SQL

Related tutorials

Tools

Cheatsheet

Access monitor:

bash
mysql -u [username] -p;
// (will prompt for password)

Show all databases:

bash
show databases;

Access database:

bash
mysql -u [username] -p [database]
// (will prompt for password)

Create new database:

bash
create database [database];

Select database:

bash
use [database];

Determine what database is in use:

bash
select database();

Show all tables:

bash
show tables;

Show table structure:

bash
describe [table];

List all indexes on a table:

bash
show index from [table];

Create new table with columns:

bash
CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);

Adding a column:

bash
ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);

Adding a column with an unique, auto-incrementing ID:

bash
ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

Inserting a record:

bash
INSERT INTO [table] ([column], [column]) VALUES ('[value]', '[value]');

MySQL function for datetime input:

bash
NOW()

Selecting records:

bash
SELECT * FROM [table];

Explain records:

bash
EXPLAIN SELECT * FROM [table];

Selecting parts of records:

bash
SELECT [column], [another-column] FROM [table];

Counting records:

bash
SELECT COUNT([column]) FROM [table];

Counting and selecting grouped records:

bash
SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];

Selecting specific records:

bash
SELECT * FROM [table] WHERE [column] = [value];
// (Selectors: <, >, !=; combine multiple selectors with AND, OR)

Select records containing [value]:

bash
SELECT * FROM [table] WHERE [column] LIKE '%[value]%';

Select records starting with [value]:

bash
SELECT * FROM [table] WHERE [column] LIKE '[value]%';

Select records starting with val and ending with ue:

bash
SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';

Select a range:

bash
SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];

Select with custom order and only limit:

bash
SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value];
// (Order: DESC, ASC)

Updating records:

bash
UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];

Deleting records:

bash
DELETE FROM [table] WHERE [column] = [value];

Delete all records from a table (without dropping the table itself):

bash
DELETE FROM [table];
// (This also resets the incrementing counter for auto generated columns like an id column.)

Delete all records in a table:

bash
truncate table [table];

Removing table columns:

bash
ALTER TABLE [table] DROP COLUMN [column];

Deleting tables:

bash
DROP TABLE [table];

Deleting databases:

bash
DROP DATABASE [database];

Custom column output names:

bash
SELECT [column] AS [custom-column] FROM [table];

Export a database dump (more info here):

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

bash
mysql -u [username] -p -h localhost [database] < db_backup.sql

Logout:

bash
exit;

Aggregate functions

Select but without duplicates:

bash
SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00;

Calculate total number of records:

bash
SELECT SUM([column]) FROM [table];

Count total number of [column] and group by [category-column]:

bash
SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];

Get largest value in [column]:

bash
SELECT MAX([column]) FROM [table];

Get smallest value:

bash
SELECT MIN([column]) FROM [table];

Get average value:

bash
SELECT AVG([column]) FROM [table];

Get rounded average value and group by [category-column]:

bash
SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];

Multiple tables

Select from multiple tables:

bash
SELECT [table1].[column], [table1].[another-column], [table2].[column] FROM [table1], [table2];

Combine rows from different tables:

bash
SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];

Combine rows from different tables but do not require the join condition:

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

bash
SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];

Users functions

List all users:

bash
SELECT User,Host FROM mysql.user;

Create new user:

bash
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant ALL access to user for * tables:

bash
GRANT ALL ON database.* TO 'user'@'localhost';

Find out the IP Address of the Mysql Host

bash
SHOW VARIABLES WHERE Variable_name = 'hostname';

References

hofmannsven

And what next?