Anvesanam Infotech

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

1	CREATE DATABASE [IF NOT EXISTS] database_name

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

1	USE database_name

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

1	DROP DATABASE [IF EXISTS] database_name

Show all available databases in database server

1	SHOW DATABASES

Working with Table

Lists all tables in a database.

1	SHOW TABLES

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

1	CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name

2	(create_clause ,...) [table_options]

3	[[IGNORE|REPLACE] select

]

Altering table structure

Actions can be one of the following actions:

1	ADD [COLUMN]

Add a new column into a table

1	DROP [COLUMN]

Drop an existing column in a table

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

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

1	DROP INDEX index_name Drop an index from a table

ADD PRIMARY KEY (column_name,...)
Add primary key into a tables

1	DROP PRIMARY KEY

Drop primary key from a table
Deleting table permanently

1	DROP TABLE [IF EXISTS] table_name [, name2, ...]

2	[RESTRICT | CASCADE]

Give information about the table or column.

1	DESCRIBE table [column_name]

2	DESC table [column_name

Working with Index

Creating an index with the specified name on a table

1	CREATE [UNIQUE|FULLTEXT] INDEX index_name

2	ON table (column_name,...)

Removing a specified index from table

1	DROP INDEX index_name

Retrieving Data

Retrieving complete data in a database table

1	SELECT * FROM table_name

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

1	SELECT column_name, column_name2….

2	FROM table_name

Retrieving unique records

1	SELECT DISTINCT (column_name)

2	FROM table_name

Retrieving data from multiples table using join

1

SELECT *

2	FROM table_name1

3	INNER JOIN table_name2 ON conditions

1

SELECT *

2	FROM table_name1

3	LEFT JOIN table_name2 ON conditions

1

SELECT *

2	FROM table_name1

Counting number of rows in a database table

1	SELECT COUNT (*)

2	FROM table_name

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

1	SELECT column_name, column_name2….

2	FROM table_name

3	ORDER BY column_name ASC [DESC], column_name2 ASC [DESC],...

Group the retrieved rows data

1

SELECT *

2	FROM table_name

3	GROUP BY column_name

Matching Data based on a pattern

Matching data using LIKE operator

view sourceprint?

1	SELECT * FROM table_name

2	WHERE column_name LIKE ‘%value%’

Matching data using regular expression

1	SELECT * FROM table_name

2	WHERE column_name RLIKE ‘regular_expression’