MySQL Cheatsheet | Revise the Concepts Quickly

that1. Creation and Working with databases

1. Create a database

CREATE DATABASE [IF NOT EXISTS] database_name;

Here, IF NOT EXISTS is optional. 

   2. Use a database

USE database_name;

   3. Drop a database permanently from the server

DROP DATABASE [IF EXISTS] database_name;

   4. Show all databases in the MySQL server

SHOW DATABASES;

2. Working With Tables

1. Create a new table

CREATE TABLE table_name(
column_definition,
....,
);

    2. Show all tables in a database

SHOW TABLES;

    3. Show the columns of a table

DESCRIBE table_name; 

    4. Show the information of a column in a table

DESCRIBE table_name column_name

    5. Delete a table

DROP TABLE [IF EXISTS] table_name;

    6. Add a new row into a table

INSERT INTO table_name(column1, column2, column3,...)
VALUES(value1, value2, value3, ...);

    7. Add a new column into a table

ALTER TABLE table_name
ADD [COLUMN] column_name;

    8. Drop a column from a table

ALTER TABLE table_name
DROP [COLUMN] column_name;

    9. Delete all rows from a table

DELETE FROM table_name;

    10. Delete rows with conditions

DELETE FROM table_name
WHERE conditions;

Working with Indexes

1. Create an Index

CREATE INDEX index_name
ON table_name (column_list);

    2. Create a unique index

CREATE UNIQUE INDEX index_name 
ON table_name (column_list);

    3. Drop an Index

DROP INDEX index_name;

Querying data from tables

1. Select all the columns from a table

SELECT * FROM table_name;

    2. Select data from some specified columns

SELECT column_list FROM table_name;

    3. Select data up to a specified number of rows

SELECT * FROM table_name
LIMIT count_of_rows;

    4. Select distinct data from columns

SELECT DISTINCT column_name FROM table_name;

    5. Change the column name of the output table

SELECT column_name AS alias_name
FROM table_name;

    6. Select data with conditions using WHERE clause

SELECT column_list
FROM table_name
WHERE conditions;

    7. Select data with conditions using the HAVING clause

SELECT column_list
FROM table_name
HAVING conditions;

    8. Grouping rows 

SELECT column_list
FROM table_name
GROUP BY column1, column2, column3, ...;

    9. Selecting data using pattern

SELECT column_name FROM table_name
WHERE column_name LIKE searching_pattern;

Updating data in a table

1. Update data for all rows

UPDATE table_name
SET column1 = value1,
 column2 = value2,
 .......;

    2. Update rows with conditions

UPDATE 
table_name


SET column1 = value1,
.........
WHERE conditions;

Selecting rows using join

1. Using Cross Join

SELECT column_name
FROM table_name
WHERE conditions;

    2. Using Left Join

SELECT column_list
FROM table1 
LEFT JOIN table2 ON condition;

    3. Using Right Join

SELECT column_list
FROM table1 
RIGHT JOIN table2 ON condition;

    4. Using Inner Join

SELECT column_list
FROM table1 
INNER JOIN table2 ON condition;

Sorting selected data 

1. Sort data of a column of output table in ascending order

SELECT * FROM table_name 
ORDER BY column_name ASC;

Here, ASC is optional, by default the data is sorted in ascending order.    

    2. Sort data of a column of output table in descending order

SELECT * FROM table_name 
ORDER BY column_name DESC;

Some MySQL functions

1. To count some data

SELECT COUNT(column_name) 
FROM table_name
WHERE condition;

    2. To sum the data 

SELECT SUM(column_name) 
FROM table_name
WHERE condition;

    3. To calculate the average

SELECT AVG(column_name) 
FROM table_name
WHERE condition;

Comment in MySQL

1. To comment the single line

SELECT * FROM table_name
-- WHERE conditions;

Here, --is used to comment the line.

    2. To comment multiple lines

SELECT * FROM table_name
/*ORDER BY column_name ASC
WHERE conditions*/;

Here, all lines between /* and */ are commented out.

My SQL Operators

1. Arithmetic Operators

    2. Binary Operators

    3. Logical Operators