SQL CheatSheet

SQL cheat sheet for simple sql commands.

Summary

• SELECT - Select data from a table.
  
• WHERE - Add conditions on queries result.
  
• ORDER BY - Sort the result of values.
  
• LIKE - Filter values like a pattern.
  
• LIMIT - Limit the number of result rows of a query.
  
• JOIN - Join the columns of several tables together.
  
• GROUP BY - Group the same row in column.
  
• HAVING - Use having for filter result after group by.
  
• UNION - Merge two select command.
  
• INSERT - Insert a row to a table.
  
• UPDATE - Update a row or rows from table.
  
• DELETE - Delete a row or rows from table.
  
• TRUNCATE TABLE - Delete all rows more efficiently.
  
• SELECT INTO - Select columns from table and insert them in another table.
  
• CREATE & DROP - Create and drop a database or table.
  
• ALTER - Change the table properties.
  
• INDEX - Add and remove index in table.
  
• CONSTRAINT - Add constraints to table columns.

SELECT

SELECT: Select data from a table.

SELECT column_name
FROM table_name;

SELECT *: Select all data from a table.

SELECT * FROM table_name;

DISTINCT: Remove Duplicate values from result.

SELECT DISTINCT column1
FROM table_name;

COUNT: Show values count.

SELECT COUNT(column1)
FROM table_name;

OPERATORS: Performing calculations on values with a same data type.

SELECT column1, (column2 - column3) AS distance
FROM table_name;

WHERE

WHERE: Add conditions on queries result.

SELECT column1
FROM table_name
WHERE column1 > 10;

AND: And operator.

SELECT column1
FROM table_name
WHERE column1 > 10 AND column1 < 3;

OR: Or operator.

SELECT column1
FROM table_name
WHERE column1 = 10 OR column1 = 3;

NOT: Not operator.

SELECT column1
FROM table_name
WHERE NOT column1 < 3;

BETWEEN: Select all values between x and y.

SELECT column1
FROM table_name
WHERE column1 BETWEEN 3 AND 10;

IN: Filter all values in the list.

SELECT column1
FROM table_name
WHERE column1 IN (2, 3, 10, 15);

ORDER BY

ASC: Sort values in ascending order.

SELECT *
FROM table_name
ORDER BY column1 ASC;

DESC: Sort values in descending order.

SELECT *
FROM table_name
ORDER BY column1 DESC;

MAX: Return maximum value of column.

SELECT MAX(column1)
FROM table_name;

MIN: Return minimum value of column.

SELECT MIN(column1)
FROM table_name;

AVG: Return average of column values.

SELECT AVG(column1)
FROM table_name;

SUM: Return sum of column values.

SELECT SUM(column1)
FROM table_name;

LIKE

LIKE %: Filter values that contain none or any number of characters instead of %.

SELECT column1
FROM table_name
WHERE column1 LIKE 'A%';

LIKE _: Filter values that have exactly one letter instead of _.

SELECT column1
FROM table_name
WHERE column1 LIKE 'A_f';

LIMIT

LIMIT: Limit the number of result rows of a query.

SELECT column1
FROM table_name
LIMIT 30;

JOIN

INNER JOIN: Merges several columns.

SELECT username, product, order_cost
FROM users
INNER JOIN products
    ON users.id = products.user_id
INNER JOIN orders
    ON products.id = orders.product_id;

LEFT JOIN: Merges multiple columns and returns null for values that exist in the left column but not in the right column.

SELECT username, product
FROM users
LEFT JOIN products
    ON users.id = products.user_id

RIGHT JOIN: Merges multiple columns and returns null for values that exist in the right column but not in the left column.

SELECT username, product
FROM users
RIGHT JOIN products
    ON users.id = products.user_id

FULL JOIN: Merges multiple columns and returns all column1 and column2 values, Even if they do not have corresponding values.

SELECT username, product
FROM users
FULL JOIN products
    ON users.id = products.user_id

SELF JOIN: Merge the column with itself to compare its own values.

SELECT t1.column1, t2.column1
FROM table1 t1, table1 t2
WHERE t1.column1 > t2.column1;

GROUP BY

GROUP BY: Group the same row in column and return it once.

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

HAVING

HAVING: When we want add condition after group by use having.

SELECT
    products.name,
    SUM(products.price * order_items.product_count) AS total
FROM products
INNER JOIN orders
    ON  orders.product_id = products.id
INNER JOIN order_items
    ON order_items.order_id = orders.id
GROUP BY products.name
HAVING SUM(products.price * order_items.product_count) > 2000
ORDER BY products.name ASC

UNION

UNION: Merge two select and remove same values from result.

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

UNION ALL: Merge two select and don't remove same values from result.

SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

INSERT INTO

INSERT INTO: Adds a row to the end of the rows of the table, if you don't set a value for a column, it sets null for it.

INSERT INTO table_name(column1, column2, column3)
VALUES ("Mohammad", "Dori", 17)

UPDATE

UPDATE: Update a row or rows with new values.

UPDATE table_name
SET column1='bob', column2=10
WHERE column1='jack';

DELETE

DELETE: Delete a row or rows from table.

DELETE FROM table_name
WHERE column1=20;

TRUNCATE TABLE

TRUNCATE TABLE: Truncate all rows from a table which is faster than delete and uses less transaction logs.

TRUNCATE TABLE table_name

SELECT INTO

SELECT INTO: Backup a table columns and insert in the new table.

SELECT column1, column2
INTO new_table
FROM table_name
WHERE column1 IN ("Iran", "Spain", "Italia");

INSERT INTO SELECT: Back a table columns and insert in the available table.

INSERT INTO table2
SELECT column1, column2
FROM table1
WHERE column1 > 10;

CREATE & DROP

CREATE DATABASE: Create a new database.

CREATE DATABASE db_name;

DROP DATABASE: Remove a database.

DROP DATABASE db_name;

CREATE TABLE: Create a new table. All SQL data types

CREATE TABLE table_name(
    column1 DATA TYPE,
    column2 DATA TYPE,
    column3 DATA TYPE
);

DROP Table: Remove a table.

DROP TABLE table_name;

ALTER

RENAME COLUMN: Rename the column.

ALTER TABLE table_name
RENAME column_old_name TO column_new_name;

RENAME TABLE: Rename the table.

ALTER TABLE table_old_name
RENAME TO table_new_name;

CREATE COLUMN: Add new column to the table.

ALTER TABLE table_name
ADD column_name DATA TYPE;

DROP COLUMN: Remove a column from the table.

ALTER TABLE table_name
DROP COLUMN column_name;

NEW DATA TYPE: Change column data type.

ALTER TABLE table_name
ALTER COLUMN column_name
    SET DATA TYPE new_data_type;

INDEX

INDEX: Index the table.

CREATE INDEX index_name
ON table_name (column_name);

UNIQUE INDEX: Uniquely index the table.

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

DROP INDEX: Remove a table index.

DROP INDEX index_name;

CONSTRAINT

NOT NULL: The column can't be null.

CREATE TABLE table_name(
    column1 DATA TYPE NOT NULL
);

UNIQUE: Creating a unique column, that is, only one of it can be in the table.

CREATE TABLE table_name(
    column1 DATA TYPE UNIQUE
);

PRIMARY KEY: Creating a primary key, it's unique, not null and connection point to other tables.

CREATE TABLE table_name(
    column1 DATA TYPE PRIMARY KEY
);

FOREIGN KEY: Creating a foreign key, it's connect to another table primary key.

CREATE TABLE table_name(
    column1 DATA TYPE PRIMARY KEY,
    column2 DATA TYPE,
    FOREIGN KEY (column2)
        REFERENCES other_table(primary_key_column)
);

DEFAULT: If no value is set for this column, it sets the default value for the columns. The opposite of NOT NULL.

CREATE TABLE table_name(
    column1 DATA TYPE PRIMARY KEY,
    column2 DATA TYPE DEFAULT default_value
);

CHECK: Checked the values of columns they are going to be added.

CREATE TABLE table_name(
    column1 DATA TYPE,
    column2 DATA TYPE,
    CONSTRAINT check_name CHECK (
        column2 > 30 AND
        LENGTH(column1) > 3
    )
)

DROP CONSTRAINT: Remove a check.

ALTER TABLE table_name
DROP CONSTRAINT check_name;

Links

Download Cheat Sheet: Click Here

My Github Account: Click Here