MySQL Cheat Sheet

The MySQL cheat sheet provides you with one page that contains the most commonly used MySQL commands and statements that help you work with MySQL more effectively.

MySQL command-line client Commands

Connect to MySQL server using mysql  command-line client with a username and password (MySQL will prompt for a password):

mysql -u [username] -pCode language: SQL (Structured Query Language) (sql)

Connect to MySQL Server with a specified database using a username and password:

mysql -u [username] -p [database]Code language: SQL (Structured Query Language) (sql)

Exit mysql command-line client:

exitCode language: SQL (Structured Query Language) (sql)

Export data using mysqldump tool

mysqldump -u [username] -p [database] > data_backup.sqlCode language: SQL (Structured Query Language) (sql)

To clear the MySQL screen the console on Linux, you use the following command:

mysql> system clear;Code language: SQL (Structured Query Language) (sql)

Currently, there is no command available on Windows for clearing the MySQL screen console window.

Working with databases

Create a database with a specified name if it does not exist in the database server

CREATE DATABASE [IF NOT EXISTS] database_name;Code language: SQL (Structured Query Language) (sql)

Use a database or change the current database to another database that you are working with:

USE database_name;Code language: SQL (Structured Query Language) (sql)

Drop a database with a specified name permanently. All physical files associated with the database will be deleted.

DROP DATABASE [IF EXISTS] database_name;Code language: SQL (Structured Query Language) (sql)

Show all available databases in the current MySQL database server

SHOW DATABASES;Code language: SQL (Structured Query Language) (sql)

Working with tables

Show all tables in a current database.

SHOW TABLES;Code language: SQL (Structured Query Language) (sql)

Create a new table

CREATE TABLE [IF NOT EXISTS] table_name(
  column_list
);Code language: SQL (Structured Query Language) (sql)

Add a new column to a table:

ALTER TABLE table 
ADD [COLUMN] column_name;Code language: SQL (Structured Query Language) (sql)

Drop a column from a table:

ALTER TABLE table_name
DROP [COLUMN] column_name;Code language: SQL (Structured Query Language) (sql)

Add an index with a specific name to a table on a column:

ALTER TABLE table 
ADD INDEX [name](column, ...);Code language: SQL (Structured Query Language) (sql)

Add primary key into a table:

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

Remove the primary key of a table:

ALTER TABLE table_name
DROP PRIMARY KEY;

Drop a table:

DROP TABLE [IF EXISTS] table_name;Code language: SQL (Structured Query Language) (sql)

Show the columns of a table:

DESCRIBE table_name;Code language: SQL (Structured Query Language) (sql)

Show the information of a column in a table:

DESCRIBE table_name column_name;Code language: SQL (Structured Query Language) (sql)

Working with indexes

Creating an index with the specified name on a table:

CREATE INDEX index_name
ON table_name (column,...);Code language: SQL (Structured Query Language) (sql)

Drop an index:

DROP INDEX index_name;Code language: SQL (Structured Query Language) (sql)

Create a unique index:

CREATE UNIQUE INDEX index_name 
ON table_name (column,...);Code language: SQL (Structured Query Language) (sql)

Working with views

Create a new view:

CREATE VIEW [IF NOT EXISTS] view_name 
AS 
  select_statement;Code language: SQL (Structured Query Language) (sql)

Create a new view using the WITH CHECK OPTION:

CREATE VIEW [IF NOT EXISTS] view_name 
AS 
select_statement
WITH CHECK OPTION;Code language: SQL (Structured Query Language) (sql)

Create or replace a view:

CREATE OR REPLACE view_name 
AS 
select_statement;Code language: SQL (Structured Query Language) (sql)

Drop a view:

DROP VIEW [IF EXISTS] view_name;Code language: SQL (Structured Query Language) (sql)

Drop multiple views:

DROP VIEW [IF EXISTS] view1, view2, ...;Code language: SQL (Structured Query Language) (sql)

Rename a view:

RENAME TABLE view_name
TO new_view_name;Code language: SQL (Structured Query Language) (sql)

Show views from a database:

SHOW FULL TABLES
[{FROM | IN } database_name]
WHERE table_type = 'VIEW';Code language: SQL (Structured Query Language) (sql)

Working with triggers

Create a new trigger:

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;Code language: SQL (Structured Query Language) (sql)

Drop a trigger:

DROP TRIGGER [IF EXISTS] trigger_name;Code language: SQL (Structured Query Language) (sql)

Show triggers in a database:

SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];Code language: SQL (Structured Query Language) (sql)

Working with stored procedures

Create a stored procedure:

DELIMITER $$

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

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Drop a stored procedure:

DROP PROCEDURE [IF EXISTS] procedure_name;
Code language: SQL (Structured Query Language) (sql)

Show stored procedures:

SHOW PROCEDURE STATUS 
[LIKE 'pattern' | WHERE search_condition];Code language: SQL (Structured Query Language) (sql)

Working with stored functions

Create a new stored function:

DELIMITER $$
 
CREATE FUNCTION function_name(parameter_list)
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
 -- statements
END $$
 
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Drop a stored function:

DROP FUNCTION [IF EXISTS] function_name;Code language: SQL (Structured Query Language) (sql)

Show stored functions:

SHOW FUNCTION STATUS 
[LIKE 'pattern' | WHERE search_condition];Code language: SQL (Structured Query Language) (sql)

Querying data from tables

Query all data from a table:

SELECT * FROM table_name;Code language: SQL (Structured Query Language) (sql)

Query data from one or more columns of a table:

SELECT 
    column1, column2, ...
FROM 
    table_name;Code language: SQL (Structured Query Language) (sql)

Remove duplicate rows from the result of a query:

SELECT 
    DISTINCT (column)
FROM 
   table_name;Code language: SQL (Structured Query Language) (sql)

Query data with a filter using a WHERE clause:

SELECT select_list
FROM table_name
WHERE condition;Code language: SQL (Structured Query Language) (sql)

Change the output of the column name using the column alias:

SELECT 
    column1 AS alias_name,
    expression AS alias,
    ...
FROM 
    table_name;
Code language: SQL (Structured Query Language) (sql)

Query data from multiple tables using inner join:

SELECT select_list
FROM table1
INNER JOIN table2 ON condition;Code language: SQL (Structured Query Language) (sql)

Query data from multiple tables using left join:

SELECT select_list
FROM table1 
LEFT JOIN table2 ON condition;Code language: SQL (Structured Query Language) (sql)

Query data from multiple tables using right join:

SELECT select_list 
FROM table1 
RIGHT JOIN table2 ON condition;Code language: SQL (Structured Query Language) (sql)

Make a Cartesian product of rows:

SELECT select_list
FROM table1
CROSS JOIN table2;Code language: SQL (Structured Query Language) (sql)

Counting rows in a table.

SELECT COUNT(*)
FROM table_name;Code language: SQL (Structured Query Language) (sql)

Sorting a result set:

SELECT 
    select_list
FROM 
    table_name
ORDER BY 
    column1 ASC [DESC], 
    column2 ASC [DESC];Code language: SQL (Structured Query Language) (sql)

Group rows using the GROUP BY clause.

SELECT select_list
FROM table_name
GROUP BY column_1, column_2, ...;Code language: SQL (Structured Query Language) (sql)

Filter group using the HAVING clause:

SELECT select_list
FROM table_name
GROUP BY column1
HAVING condition;Code language: SQL (Structured Query Language) (sql)

Modifying data in tables

Insert a new row into a table:

INSERT INTO table_name(column_list)
VALUES(value_list);Code language: SQL (Structured Query Language) (sql)

Insert multiple rows into a table:

INSERT INTO table_name(column_list)
VALUES(value_list1),
      (value_list2),
      (value_list3),
      ...;Code language: SQL (Structured Query Language) (sql)

Update all rows in a table:

UPDATE table_name
SET column1 = value1,
    ...;Code language: SQL (Structured Query Language) (sql)

Update data for a set of rows specified by a condition in WHERE clause.

UPDATE table_name
SET column_1 = value_1,
    ...
WHERE conditionCode language: SQL (Structured Query Language) (sql)

Update with join

UPDATE 
    table1, 
    table2
INNER JOIN table1 ON table1.column1 = table2.column2
SET column1 = value1,
WHERE condition;Code language: SQL (Structured Query Language) (sql)

Delete all rows in a table

DELETE FROM table_name;Code language: SQL (Structured Query Language) (sql)

Delete rows specified by a condition:

DELETE FROM table_name
WHERE condition;Code language: SQL (Structured Query Language) (sql)

Delete with join

DELETE table1, table2
FROM table1
INNER JOIN table2
    ON table1.column1 = table2.column2
WHERE condition;Code language: SQL (Structured Query Language) (sql)

Searching

Search for data using the LIKE operator:

SELECT select_list
FROM table_name
WHERE column LIKE '%pattern%';Code language: SQL (Structured Query Language) (sql)

Text search using a regular expression with RLIKE operator.

SELECT select_list
FROM table_name
WHERE column RLIKE 'regular_expression';Code language: SQL (Structured Query Language) (sql)