If knowledge is power, then sharing knowledge is ultimate powertrip
Latest Updates
You are here: Home >> MySql >> MySQL Cheat Sheet

MySQL Cheat Sheet

MySQL Cheat Sheet for your reference.

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 the MySQL database server

1

SHOW DATABASES

Working with Table

Lists all tables in a database.

1

SHOW TABLES

Create table statement that defines the structure of table .

1
2
3
4
5
6

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:

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 into a tables

1

ADD PRIMARY KEY (column_name,…)

Remove primary key from a table

1

DROP PRIMARY KEY

Deleting table permanently

1
2

DROP TABLE [IF EXISTS] table_name [, name2, …]
[RESTRICT | CASCADE]

Get information about the table or column.

1
2

DESCRIBE table [column_name]
DESC table [column_name

Working with Index

Creating an index with the specified name on a table

1
2

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

Removing a specified index from table

1

DROP INDEX index_name

Querying Data

Query complete data in a database table

1

SELECT * FROM table_name

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

1
2

SELECT column_name, column_name2….
FROM table_name

Query unique records

1
2

SELECT DISTINCT (column_name)
FROM table_name

Query data from multiples table using join

1
2
3

SELECT *
FROM table_name1
INNER JOIN table_name2 ON conditions

 
1
2
3

SELECT *
FROM table_name1
LEFT JOIN table_name2 ON conditions

 
1
2

SELECT *
FROM table_name1

Counting number of rows in a database table

1
2

SELECT COUNT (*)
FROM table_name

Sorting ascending or descending based on one or more columns:

1
2
3

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

Group the retrieved rows data:

1
2
3

SELECT *
FROM table_name
GROUP BY column_name

Matching Data based on a pattern

Matching data using LIKE operator:

1
2

SELECT * FROM table_name
WHERE column_name LIKE '%value%'

Matching data using regular expression

 
1
2

SELECT * FROM table_name
WHERE column_name RLIKE 'regular_expression'

About Jawed Shamshedi

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Scroll To Top