CREATE TABLE: Create a new table with a primary key and a foreign key
CREATE TABLE table1 (
column1_table1 INT PRIMARY KEY,
column2_table1 VARCHAR(255) NOT NULL,
column3_table1 INT,
FOREIGN KEY (column3_table1) REFERENCES table2(column1_table2)
);
Basic Commands
SELECT: Retrieve data from a database
SELECT column1, column2
FROM table_name;
SELECT *
FROM table_name; -- Select all columns
INSERT: Insert data into a table
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);
UPDATE: Update existing data within a table
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
DELETE: Delete data from a table
DELETE FROM table_name
WHERE condition;
Filtering and Sorting
WHERE: Filter records
SELECT column1, column2
FROM table_name
WHERE condition;
AND, OR, NOT: Combine multiple conditions
SELECT * FROM table_name
WHERE condition1 AND condition2;
SELECT * FROM table_name
WHERE condition1 OR condition2;
SELECT * FROM table_name
WHERE NOT condition;
ORDER BY: Sort the result set
SELECT column1, column2
FROM table_name
ORDER BY column1 ASC; -- Ascending order
SELECT column1, column2
FROM table_name
ORDER BY column1 DESC; -- Descending order
Aggregate Functions
COUNT(): Count the number of rows
SELECT COUNT(column_name)
FROM table_name;
SUM(): Calculate the sum
SELECT SUM(column_name)
FROM table_name;
AVG(): Calculate the average
SELECT AVG(column_name)
FROM table_name;
MIN() and MAX(): Find the minimum and maximum value
SELECT MIN(column_name)
FROM table_name;
SELECT MAX(column_name)
FROM table_name;
Grouping
GROUP BY: Group rows that have the same values
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;
HAVING: Filter groups
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 1;
Joins
INNER JOIN: Return records with matching values in both tables
SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_field = b.common_field;
LEFT JOIN: Return all records from the left table, and the matched records from the right table
SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_field = b.common_field;
RIGHT JOIN: Return all records from the right table, and the matched records from the left table
SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_field = b.common_field;
FULL JOIN: Return all records when there is a match in either left or right table
SELECT a.column1, b.column2
FROM table1 a
FULL OUTER JOIN table2 b ON a.common_field = b.common_field;
Subqueries
Subquery in SELECT: Use a query inside another query
SELECT column1,
(SELECT COUNT(*) FROM table2 WHERE table2.column = table1.column) as count
FROM table1;
Subquery in WHERE: Use a query in a WHERE clause
SELECT column1
FROM table_name
WHERE column2 = (SELECT column2 FROM table2 WHERE condition);
String Functions
CONCAT(): Concatenate two or more strings
SELECT CONCAT(column1, ' ', column2) AS new_column
FROM table_name;
SUBSTRING(): Extract characters from a string
SELECT SUBSTRING(column_name, start_position, length) FROM table_name;
UPPER() and LOWER(): Convert to uppercase or lowercase
SELECT UPPER(column_name)
FROM table_name;
SELECT LOWER(column_name)
FROM table_name;
REPLACE(): Replace occurrences of a specified string
SELECT REPLACE(column_name, 'old_string', 'new_string') FROM table_name;
Date Functions
CURRENT_DATE(): Get the current date
SELECT CURRENT_DATE();
DATE_ADD(): Add an interval to a date
SELECT DATE_ADD(date_column, INTERVAL 7 DAY)
FROM table_name;
DATEDIFF(): Calculate the difference between two dates
SELECT DATEDIFF(end_date, start_date)
FROM table_name;
DATE_FORMAT(): Format a date
SELECT DATE_FORMAT(date_column, '%Y-%m-%d')
FROM table_name;
Case Statements
CASE: Create conditional logic
SELECT column1,
CASE
WHEN condition1 THEN 'Result1'
WHEN condition2 THEN 'Result2'
ELSE 'Result3'
END as alias_name
FROM table_name;
Explanation: the CASE
statement is a conditional expression that allows you to create new calculated fields based on different conditions. The query above specifies that when condition1
is true, the value 'Result1'
will be returned. If the condition2 is true, the ‘Result2’ will be returned. If none of the WHEN
conditions are met, the ELSE
part will return 'Result3'
. Where the CASE
expression ends, the result is given an alias (column name) called alias_name
.