Complete MySQL CheatSheet Commands

This MySQL cheat sheet provides you with one-page that contains the most commonly used MySQL commands, statements and syntax that will help you work with MySQL more efficiently.

Simple MySQL Commands

1. To show databases available:

SHOW DATABASES;

2. To show tables available in a database:

SHOW TABLES;

3. To select a database:

USE database_name;

4. To check the current database:

SELECT database();

5. To create a table in a database:

CREATE TABKE table_name(
  id INT,
  name VARCHAR(256)
);

6. To see description of a table in a database:

DESCRIBE table_name;

MySQL Command-line client commands

1. To connect to MySQL server

mysql -u [username] -p;

2. To connect to MySQL server with a specified database

mysql -u [username] -p [database];

3. To exit MySQL command-line client

exit;

4. To export data using MySQL dump tool

mysqldump -u [username] -p [database] > data_backup.sql;

5. To clear MySQL screen console window on Linux

system clear;

WORKING WITH DATABASES

1. To create a database with a specified name if it does not exists in the database server

CREATE DATABASE [IF NOT EXISTS] database_name;

2. To use a database or change the current database to another database

USE database_name;

3. To drop a database with a specified name permanently. All physical files associated with the database will be deleted.

DROP DATABASE [IF EXISTS] database_name;

4. To show all available databases in the current MySQL database server.

SHOW DATABASES;

WORKING WITH TABLES

1. To show all tables in a current database

SHOW TABLES;

2. Creating table

CREATE TABLE [IF NOT EXISTS] table_name (
  column_list
);

3. Adding a new column from a table

ALTER TABLE table_name
ADD [COLUMN] column_name;

4. Adding index with a specific name to a table on a column

ALTER TABLE table_name
ADD INDEX [name] (column, ...);

5. Adding primary key into a table

ALTER TABLE table_name
ADD PRIMARY KEY (column_name, ...);

6. Removing the primary key of a table

ALTER TABLE table_name
DROP PRIMARY KEY;

7. Dropping a table

DROP TABLE [IF EXISTS] table_name;

8. Showing the columns of a table

DESCRIBE table_name;

WORKING WITH INDEXES

1. Creating an index with the specified name on a table

CREATE INDEX index_name
ON table_name (column, ...);

2. Dropping an index

DROP INDEX index_name;

3. Creating a unique index

CREATE UNIQUE INDEX index_name
ON table_name (column, ...);

WORKING WITH VIEWS

1. Creating a new view

CREATE VIEW [IF NOT EXISTS] view_name
AS
  select_statement;

2. Creating a new view with the WITH CHECK OPTION

CREATE VIEW [IF NOT EXISTS] view_name
AS select_statement
WITH CHECK OPTION;

3. Creating or replacing a view

CREATE OR REPLACE view_name
AS
  select_statement;

4. Dropping a view

DROP VIEW [IF EXISTS] view_name;

5. Dropping multiple views

DROP VIEW [IF EXISTS] view1, view2, ...;

6. Renaming a view

RENAME TABLE view_name TO new_view_name;

7. Showing views from a database

SHOW FULL TABLES
[{FROM | IN} database_name]
WHERE table_type = 'view';

WORKING WITH TRIGGERS

1. Creating a new trigger

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
trigger_body;

2. Dropping a trigger

DROP TRIGGER [IF EXISTS] trigger_name;

3. Showing triggers in a database

SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'patter' | WHERE search_condition];

WORKING WITH STORED PROCEDURES

1. Creating a stored procedure

DELIMITER $$
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
  body;
END $$
DELIMTER;

2. Dropping a stored procedure

DROP PROCEDURE [IF EXISTS] procedure_name;

3. Showing stored procedures

SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE search_condition];

WORKING WITH STORED FUNCTIONS

1. Creating a new stored functions

DELIMITER $$
CREATE FUNCTION function_name(parameter_list)
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
  -- statements
END $$
DELIMITER;

2. Dropping a stored function

DROP FUNCTION [IF EXISTS] function_name;

3. Showing stored functions

SHOW FUNCTION STATUS
[LIKE 'pattern' | WHERE search_condition];

QUERYING DATA FROM TABLES

1. Querying all data from a table

SELECT * FROM table_name;

2. Querying data from one or more column of a table

SELECT
  column1, column2, ...
FROM
  table_name;

3. Removing duplicate rows from the result of a query

SELECT
  DINSTINCT (column)
FROM
  table_name;

4. Querying data with a filter using a WHERE clause

SELECT select_list
FROM table_name
WHERE condition;

5. Changing the output of the column name using column alias

SELECT
  column1 AS alias_name,
  expression AS alias,
  ...
FROM
  table_name;

6. Querying data from multiple tables using inner join

SELECT select_list
FROM table1
INNER JOIN table2 ON condition;

7. Querying data from multiple tables using left join

SELECT select_list
FROM table1
LEFT JOIN tables ON condition;

8. Querying data from multiple tables using right join

SELECT select_list
FROM table1
RIGHT JOIN table2 ON condition;

9. Making a cartesian product of rows

SELECT select_list
FROM table1
CROSS JOIN table2;

10. Sorting a result set

SELECT
  select_list
FROM
  table_name
ORDER BY
  column1 ASC [DESC],
  column2 ASC [DESC];

11. Grouping rows using the GROUP BY clause

SELECT select_list
FROM table_name
GROUP BY column1, column2, ...;

12. Filtering group using the HAVING clause

SELECT select_list
FROM table_name
GROUP BY column1
HAVING condition;

MODIFYING DATA IN TABLES

1. Inserting a new row into a table

INSERT INTO table_name (column_list)
VALUES (value_list);

2. Inserting multiple rows into a table

INSERT INTO table_name (column_list)
VALUES (value_list1),
       (value_list2),
       (value_list3)
       ...;

3. Updating all rows in a table

UPDATE table_name
SET column1 = value1,
  ...;

4. Updating data for a set of row specified by a condition in WHERE clause

UPDATE table_name
SET column1 = value1,
  ...
WHERE condition;

5. Updating with join

UPDATE table1, table2
INNER JOIN table1 ON table.column1 = table2.column2
SET column1 = value1,
WHERE condition;

6. Deleting all rows in a table

DELETE FROM table_name;

7. Deleting rows specified by a condition

DELETE FROM table_name WHERE condition;

8. Deleting with join

DELETE table1, table2
FROM table1 INNER JOIN table2
ON table1.column1 = table2.column2
WHERE condition;

SEARCHING

1. Searching for data using the LIKE operator

SELECT select_list FROM table_name
WHERE column LIKE '%operator%';

2. Text searching using a regular expression with RLIKE operator

SELECT select_list FROM table_name
WHERE column RLIKE 'regular_expression';

I hope this article has been helpful to you. Don’t forget to give me a clap 👏 and follow me on twitter and facebook.