» MySQL Cheat Sheet

MySQL Cheat Sheet

In this article, you will find common usage MySQL commands in one page to help you to practice with MySQL fast and effectively.

Working with Database

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

CREATE DATABASE [IF NOT EXISTS] database_name

Use database or change current database to another database you are working with

USE database_name

Drop a database with specified name permanently. All physical file associated with the database is no longer exists.

DROP DATABASE [IF EXISTS] database_name

Show all available databases in database server

SHOW DATABASES

Working with Table

Lists all tables in a database.

SHOW TABLES

Create table statement defines the structure of table in a database.

 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <em>table_name</em>
 (<em>create_clause ,</em>...) [<em>table_options</em>]
 [[IGNORE|REPLACE] <em>select</em>]

Altering table structure

Actions can be one of the following actions:

ADD [COLUMN]

Add a new column into a table

DROP [COLUMN]

Drop an existing column in a table

ADD INDEX [name](column_name, ...)

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

DROP INDEX index_name Drop an index from a table

ADD PRIMARY KEY (column_name,...)  

Add primary key into a tables

DROP PRIMARY KEY

Drop primary key from a table

Deleting table permanently

 DROP TABLE [IF EXISTS] <em>table_name</em> [, <em>name2</em>, ...]
 [RESTRICT | CASCADE]

Give information about the table or column.

DESCRIBE table [column_name] 
DESC table [column_name

Working with Index

Creating an index with the specified name on a table

CREATE [UNIQUE|FULLTEXT] INDEX index_name
ON table (column_name,...)

Removing a specified index from table

DROP INDEX index_name

Retrieving Data

Retrieving complete data in a database table

SELECT * FROM table_name

Retrieving specified data which is shown in the column list from a database table

SELECT column_name, column_name2….
FROM table_name

Retrieving unique records

SELECT DISTINCT (column_name)
FROM table_name

Retrieving data from multiples table using join

SELECT * 
FROM table_name1
INNER JOIN table_name2 ON conditions

SELECT * 
FROM table_name1
LEFT JOIN table_name2 ON conditions

SELECT *
FROM table_name1

Counting number of rows in a database table

SELECT COUNT (*)
FROM table_name

Sorting ascending or descending retrieved data based on one or more column

SELECT column_name, column_name2….
FROM table_name
ORDER BY column_name ASC [DESC], column_name2 ASC [DESC],...

Group the retrieved rows data

SELECT *
FROM table_name
GROUP BY column_name

Matching Data based on a pattern

Matching data using LIKE operator

SELECT * FROM table_name
WHERE column_name LIKE ‘%value%’

Matching data using regular expression

SELECT * FROM table_name
WHERE column_name RLIKE ‘regular_expression’
Search