USE |
Select database for query |
USE database_name;
|
USE db;
|
SELECT |
Returns the table with the columns specified |
SELECT column 1, column2 … FROM table_name; (use * for all columns)
|
SELECT * FROM Customers;
|
AS |
Set a column name to something else. You can use arithmetic with columns (column_name + 10) |
SELECT column 1 AS ‘new column name’, column2 … FROM table_name;
|
SELECT Price + 10 AS Price FROM Customers
|
WHERE |
Used to extract only the records that fulfill the specified condition |
SELECT column 1, column2, … FROM table_name WHERE condition;
|
Select all customers from Mexico: SELECT * FROM Customers WHERE Country = ‘Mexico’; |
SELECT DISTINCT |
Select columns but with no duplicates |
SELECT DISTINCT column 1, column2, … FROM table_name; (use * for all columns)
|
SELECT DISTINCT * FROM Customers;
|
Operators |
Operators used in WHERE clause: =, <=>, BETWEEN (between certain range), LIKE (search for a pattern), IN (specify multiple possible values for a column) |
=, <=>, !=, BETWEEN, LIKE, IN
|
SELECT * FROM Products WHERE Price BETWEEN 50 AND 60; SELECT * FROM Customers WHERE City LIKE 's%'; (starting with s) SELECT * FROM Customers WHERE City IN ('Paris','London');
|
AND/OR |
Allows for multiple conditions AND has higher precedence |
SELECT column 1, column2, … FROM table_name WHERE condition1 AND condition2 OR Condition3…;
|
SELECT * FROM Customers WHERE Country = 'Spain' AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');
|
NOT |
Used to negate conditions |
SELECT column1, column2, ... FROM table_name WHERE NOT condition;
|
SELECT * FROM Customers WHERE CustomerName NOT LIKE 'A%';
|
IN |
The IN operator allows you to specify multiple values in a WHERE clause. |
SELECT * FROM table_name WHERE column_name IN (value1, value2 …)
|
SELECT * FROM Customers WHERE state IN ('VA', 'GA', 'FL')
|
BETWEEN |
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. |
SELECT * FROM table_name WHERE column_name BETWEEN *value1* AND *value2*
|
SELECT * FROM Customers WHERE birth_date BETWEEN '1990-1-1' AND'2000-1-1'
|
LIKE |
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Common pattern notations here |
SELECT * FROM table_name WHERE column_name LIKE ‘*pattern*’
|
SELECT * FROM customers WHERE address LIKE '%trail%' OR address LIKE '%avenue%';
|
REGEXP |
The REGEXP operator is used in a WHERE clause to search for values that match a regular expression pattern in a column. It allows more complex pattern matching than LIKE. Common pattern notations here |
SELECT * FROM table_name WHERE column_name REGEXP ‘*pattern*’
|
SELECT * FROM customers WHERE last_name REGEXP ‘field|mac|rose’;
SELECT * FROM customers WHERE first_name REGEXP 'Elka|Ambur';
SELECT * FROM customers WHERE last_name REGEXP '(ey|on)$';
SELECT * FROM customers WHERE last_name REGEXP '^MY|SE';
SELECT * FROM customers WHERE last_name REGEXP 'B\[RU\]';
|
IS NULL |
Checks for null values |
SELECT * FROM table_name WHERE column_name IS NULL
|
SELECT * FROM orders WHERE shipped_date IS NULL
|
ORDER BY |
Used to sort the result-set in ascending or descending order. |
SELECT * FROM table_name ORDER BY column_name (DESC *optional*)
|
SELECT *, quantity * unit_price AS total_price FROM order_items WHERE order_id = 2 ORDER BY total_price DESC
|
LIMIT |
The LIMIT clause is used to specify the number of records to return. You can use the offset value to skip that number of rows. |
SELECT * FROM table_name LIMIT offset, number
|
SELECT * FROM customers ORDER BY points DESC LIMIT 3
|
‘INNER’ JOIN |
The INNER JOIN keyword selects records that have matching values in both tables. Prefix the table with the database name if it’s not in the current database |
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
|
SELECT o.order_id, c.first_name, c.last_name FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id;
|
Self Join |
A self join is a regular join, but the table is joined with itself. |
SELECT t1.column_name, t2.column_name FROM table_name t1 INNER JOIN table_name t2 ON t1.column_name = t2.column_name;
|
SELECT e.employee_id, e.first_name, m.first_name AS manager FROM employees e INNER JOIN employees m ON e.reports_to = m.employee_id
|
Joining Multiple Tables |
Running a join on multiple datasets |
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; (INNER) JOIN table3 ON table1.column_name = table3.column_name
|
SELECT o.order_id, o.order_date, c.first_name, c.last_name, os.name AS status FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_statuses os ON o.status = os.order_status_id
|
Compound Join Conditions |
When you have multiple conditions to join 2 tables. |
SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name = table2.column_name AND table1.column_name2 = table2.column_name2
|
SELECT * FROM order_items oi JOIN order_item_notes oin ON oi.order_id = oin.order_id AND oi.product_id = oin_product_id
|
IMPLICIT JOIN |
A way to join tables by listing them in the FROM clause, separated by commas, and specifying the join condition in the WHERE clause |
SELECT column_name(s) FROM table1, table2 WHERE table1.column_name = table2.column_name;
|
SELECT * FROM orders o, customers c WHERE o.customer_id = c.customer_id
|
LEFT JOIN |
Returns all records from the left table (table1), and the matching records (if any) from the right table (table2) |
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
|
SELECT p.product_id, p.name, oi.quantity FROM products p LEFT JOIN order_items oi ON p.product_id = oi.product_id
|
RIGHT JOIN |
Returns all records from the right table (table2), and the matching records (if any) from the left table (table1). |
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
|
SELECT e.employee_id, e.first_name, d.name AS department FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;
|
OUTER Joining Multiple Tables |
Running an outer join on multiple datasets |
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; LEFT JOIN table3 ON table1.column_name = table3.column_name
|
SELECT c.customer_id, c.first_name, o.order_id, sh.name AS shipper FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id ORDER BY c.customer_id
|
USING |
Simplifies table join operations by letting you specify common columns between tables |
SELECT column_name(s) FROM table1 JOIN table2 USING (column_name)
|
SELECT o.order_id, c.first_name, sh.name AS shipper FROM orders o JOIN customers c USING (customer_id) JOIN shippers sh USING (shipper_id)
|
NATURAL JOIN |
Performs a join between two tables based on columns that have the same name and compatible data types. This join operation automatically determines the common columns between the tables and removes duplicates in the result set. |
SELECT column_name(s) FROM table1 NATURAL JOIN table2
|
SELECT o.order_id, c.first_name FROM orders o NATURAL JOIN customers c
|
CROSS JOIN |
Returns all records from both tables (table1 and table2) |
SELECT column_name(s) FROM table1 CROSS JOIN table2
|
SELECT c.first_name AS customer, p.name AS product FROM customers c CROSS JOIN products p
|
IMPLICIT CROSS JOIN |
A way to join tables by listing them in the FROM clause, separated by commas |
SELECT column_name(s) FROM table1, table2
|
SELECT c.first_name AS customer, p.name AS product FROM customers c, products p
|
UNION |
Used to combine the result-set of two or more queries |
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
|
SELECT order_id, order_date, 'Active' AS status FROM orders WHERE order_date \>= '2019-01-01' UNION SELECT order_id, order_date, 'Archived' AS status FROM orders WHERE order_date \< '2019-01-01'
|
INSERT INTO |
Used to insert new records in a table. |
1. Specify both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
2. If you are adding values for all the columns of the table: INSERT INTO table_name VALUES (value1, value2, value3, ...); |
INSERT INTO customers VALUES (DEFAULT, 'John', 'Smith', '1990-01-01', NULL, 'address', 'city', 'CA', DEFAULT)
Multiple rows added example: INSERT INTO shippers (name) VALUES ('Shipper1'), ('Shipper2'), ('Shipper3')
|
CREATE TABLE |
Used to create a new table in a database |
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );
|
CREATE TABLE invoices_archived AS SELECT i.invoice_id, i.number, c.name AS client, i.invoice_total, i.payment_total, invoice_date, i.payment_date, i.due_date FROM invoices i JOIN clients c USING (client_id) WHERE i.payment_date IS NOT NULL
|
UPDATE / SET |
Used to modify the existing records in a table. |
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
|
UPDATE customers SET points = points \+ 50 WHERE birth_date \< '1990-01-01'
|
DELETE FROM |
Used to delete existing records in a table. |
DELETE FROM table_name WHERE condition;
|
DELETE FROM invoices WHERE client_id = (SELECT * FROM clients WHERE name = 'Myworks')
|
