MySQL Cheat Sheet
MySQL cheat sheet provides the most commonly used MySQL statements that help you
practice MySQL more 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 the MySQL database server
SHOW DATABASES
Working with Table
Lists all tables in a database.
SHOW TABLES
Create table statement that defines the structure of table .
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name(
key type(size) NOT NULL PRIMARY KEY AUTO_INCREMENT,
c1 type(size) NOT NULL,
c2 type(size) NULL,
...
) Engine= [InnoDB|MyISAM]|...]
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 into a tables
ADD PRIMARY KEY (column_name,...)
Remove primary key from a table
DROP PRIMARY KEY
Deleting table permanently
DROP TABLE [IF EXISTS] table_name [, name2, ...]
[RESTRICT | CASCADE]
Get 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
Querying Data
Query complete data in a database table
SELECT * FROM table_name
Query specified data which is shown in the column list from a database table
SELECT column_name, column_name2….
FROM table_name
Query unique records
SELECT DISTINCT (column_name)
FROM table_name
Query 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 based on one or more columns:
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'
