MySQL Cheat Sheet

Working with Database

Create a database with a specified name if it is not exists 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

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] table_name
 (create_clause ,...) [table_options]
 [[IGNORE|REPLACE] select]

Altering table structure

ALTER [IGNORE] TABLE table_name actions

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] table_name [, name2, ...]
 [RESTRICT | CASCADE]

Give information about the table or column.

DESCRIBE table [column_name] 
DESC table [column_name]

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 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
RIGHT JOIN table_name2 ON conditions

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 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’