Wikipedia:
Structured Query Language (SQL) is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.
TODO: structure below reference into sub notes.
# SQL Quick Reference Guide
Source: ChatGPT
# 1. Data Definition Language (DDL)
# Create Table
1
2
3
4
5
| CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);
|
# Alter Table
1
2
3
4
5
6
7
8
| ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE table_name
MODIFY column_name datatype;
|
# Drop Table
# Truncate Table
1
| TRUNCATE TABLE table_name;
|
# 2. Data Manipulation Language (DML)
# Insert Data
1
2
| INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
|
# Update Data
1
2
3
| UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
|
# Delete Data
1
2
| DELETE FROM table_name
WHERE condition;
|
# 3. Data Query Language (DQL)
# Select Data
1
2
3
4
5
6
7
| SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column ASC|DESC
LIMIT number;
|
# 4. Data Control Language (DCL)
# Grant Permissions
1
| GRANT privilege ON object TO user;
|
# Revoke Permissions
1
| REVOKE privilege ON object FROM user;
|
# 5. Transaction Control Language (TCL)
# Commit
# Rollback
# Savepoint
1
2
| SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;
|
# 6. Common SQL Clauses
# Where Clause
1
2
3
| SELECT column1, column2
FROM table_name
WHERE condition;
|
# Like Clause
1
2
3
| SELECT column1, column2
FROM table_name
WHERE column_name LIKE pattern;
|
# Between Clause
1
2
3
| SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
|
# In Clause
1
2
3
| SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, ...);
|
# 7. Aggregate Functions
# Count
1
| SELECT COUNT(column_name) FROM table_name;
|
# Sum
1
| SELECT SUM(column_name) FROM table_name;
|
# Avg
1
| SELECT AVG(column_name) FROM table_name;
|
# Min
1
| SELECT MIN(column_name) FROM table_name;
|
# Max
1
| SELECT MAX(column_name) FROM table_name;
|
# 8. Joins
# Inner Join
1
2
3
4
| SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
|
# Left Join
1
2
3
4
| SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
|
# Right Join
1
2
3
4
| SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
|
# Full Outer Join
1
2
3
4
| SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
|
# 9. Subqueries
# Inline Subquery
1
2
3
| SELECT column1, column2
FROM table_name
WHERE column_name = (SELECT column_name FROM table_name WHERE condition);
|
1
2
3
| SELECT column1
FROM table1
WHERE column1 = (SELECT column2 FROM table2 WHERE table1.column3 = table2.column3);
|
# 10. Indexes
# Create Index
1
| CREATE INDEX index_name ON table_name (column1, column2, ...);
|
# Drop Index
# 11. Views
# Create View
1
2
3
4
| CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;
|
# Drop View
# 12. Constraints
# Add Constraint
1
2
| ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);
|
# Drop Constraint
1
2
| ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
|
# Notes
- Replace
table_name, column_name, and other placeholders with actual table and column names. - Use semicolons (
;) to terminate SQL statements.