sql-commands-cheat-sheet.md

SQL Commands Cheat Sheet

Data Definition Language (DDL)

Create Table

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    column3 datatype,
   ....
);

Drop Table

DROP TABLE table_name;

Alter Table

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Rename Table

ALTER TABLE old_table_name
RENAME TO new_table_name;

Data Manipulation Language (DML)

Insert Data

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

Update Data

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

Delete Data

DELETE FROM table_name
WHERE condition;

Select Data

SELECT column1, column2, ...
FROM table_name
WHERE condition;

SELECT * FROM table_name;

Data Query Language (DQL)

Select with Join

SELECT table1.column1, table2.column2, ...
FROM table1
JOIN table2 ON table1.common_column = table2.common_column;

Select with Alias

SELECT column_name AS alias_name
FROM table_name;

Select with Aggregate Functions

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SELECT SUM(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

SELECT MIN(column_name)
FROM table_name
WHERE condition;

Data Control Language (DCL)

Grant Privileges

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.table_name TO 'username'@'host';

Revoke Privileges

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

REVOKE SELECT, INSERT, UPDATE, DELETE ON database_name.table_name FROM 'username'@'host';

Transaction Control Language (TCL)

Begin Transaction

START TRANSACTION;

Commit Transaction

COMMIT;

Rollback Transaction

ROLLBACK;

Savepoint

SAVEPOINT savepoint_name;

Rollback to Savepoint

ROLLBACK TO SAVEPOINT savepoint_name;

Indexes

Create Index

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Drop Index

DROP INDEX index_name;

Views

Create View

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Drop View

DROP VIEW view_name;

Useful Clauses

Order By

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC|DESC;

Group By

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

Having

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > value;

Limit

SELECT column1, column2, ...
FROM table_name
LIMIT number;

String Functions

Concatenate

SELECT CONCAT(column1, column2) AS new_column
FROM table_name;

Substring

SELECT SUBSTRING(column_name, start, length) AS new_column
FROM table_name;

Replace

SELECT REPLACE(column_name, 'old_value', 'new_value') AS new_column
FROM table_name;

Date Functions

Current Date

SELECT CURRENT_DATE;

Current Time

SELECT CURRENT_TIME;

Date Add

SELECT DATE_ADD(column_name, INTERVAL value DAY) AS new_date
FROM table_name;

Date Sub

SELECT DATE_SUB(column_name, INTERVAL value DAY) AS new_date
FROM table_name;

Case Statements

Case

SELECT column_name,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result
    END AS new_column
FROM table_name;

SQL JOIN Commands Cheat Sheet

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

LEFT JOIN (or LEFT OUTER JOIN)

Returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.

Syntax

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side when there is no match.

Syntax

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example

SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

FULL JOIN (or FULL OUTER JOIN)

Returns all records when there is a match in either left (table1) or right (table2) table records. The result is NULL from the side where there is no match.

Syntax

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Example

SELECT employees.name, departments.name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.id;

CROSS JOIN

Returns the Cartesian product of both tables. This means it returns all possible combinations of rows from both tables.

Syntax

SELECT columns
FROM table1
CROSS JOIN table2;

Example

SELECT employees.name, departments.name
FROM employees
CROSS JOIN departments;

SELF JOIN

A self JOIN is a regular join but the table is joined with itself.

Syntax

SELECT a.columns, b.columns
FROM table a, table b
WHERE condition;

Example

SELECT a.employee_id, a.name, b.name AS manager_name
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;

NATURAL JOIN

A NATURAL JOIN is based on all columns in the two tables that have the same name and selects rows with equal values in the relevant columns.

Syntax

SELECT columns
FROM table1
NATURAL JOIN table2;

Example

SELECT *
FROM employees
NATURAL JOIN departments;

USING Clause

The USING clause is used to specify only those columns that should be used for an EQUIJOIN.

Syntax

SELECT columns
FROM table1
JOIN table2
USING (column);

Example

SELECT employees.name, departments.name
FROM employees
JOIN departments
USING (department_id);