Introduction
Structured Query Language (SQL) is the standard language used to manage relational databases.
It allows us to create, manipulate, and secure data efficiently, regardless of the database system (MySQL, MariaDB, PostgreSQL, SQLite, Oracle, etc.).
This post summarizes the main components of SQL and their most important commands.
Each section includes the type of command, syntax, and a concise description, serving as a quick reference or learning manual.
🧩 Data Definition Language (DDL)
DDL is used to define and modify the structure of database objects such as tables, schemas, and views.
| Type | Syntax | Description |
|---|---|---|
CREATE DATABASE |
CREATE DATABASE database_name; |
Creates a new database. |
CREATE TABLE |
CREATE TABLE table_name (column_name data_type, ...); |
Defines a new table with specified columns and data types. |
ALTER TABLE |
`ALTER TABLE table_name ADD | DROP |
{% raw %}DROP TABLE
|
DROP TABLE table_name; |
Permanently deletes a table and its data. |
TRUNCATE TABLE |
TRUNCATE TABLE table_name; |
Deletes all data from a table but keeps its structure. |
RENAME TABLE |
RENAME TABLE old_name TO new_name; |
Renames a table. |
CREATE INDEX |
CREATE INDEX index_name ON table_name (column_name); |
Creates an index to improve query performance. |
DROP INDEX |
DROP INDEX index_name ON table_name; |
Deletes an index. |
🧮 Data Manipulation Language (DML)
DML is used to insert, update, delete, and retrieve data from database tables.
🟢 SELECT — Retrieve Data
| Type | Syntax | Description |
|---|---|---|
| Select all columns | SELECT * FROM table_name; |
Retrieves all data from a table. |
| Select specific columns | SELECT column1, column2 FROM table_name; |
Retrieves specific columns. |
| With condition | SELECT * FROM table_name WHERE column = value; |
Filters rows based on a condition. |
| With multiple conditions | SELECT * FROM table_name WHERE col1='x' AND col2>10; |
Uses logical operators (AND, OR, NOT). |
| With sorting | `SELECT * FROM table_name ORDER BY column ASC | DESC;` |
| With grouping | SELECT column, COUNT(*) FROM table_name GROUP BY column; |
Groups results for aggregation. |
| With limit | SELECT * FROM table_name LIMIT 10; |
Restricts the number of returned rows. |
🟠 INSERT — Add Data
| Type | Syntax | Description |
|---|---|---|
| Insert all columns | INSERT INTO table_name VALUES (value1, value2, ...); |
Inserts data into all columns in order. |
| Insert specific columns | INSERT INTO table_name (col1, col2) VALUES (val1, val2); |
Inserts data into specific columns. |
| Insert multiple rows | INSERT INTO table_name (col1, col2) VALUES (v1, v2), (v3, v4); |
Inserts multiple rows at once. |
🔵 UPDATE — Modify Data
| Type | Syntax | Description |
|---|---|---|
| Basic update | UPDATE table_name SET column=value; |
Updates all rows in a table. |
| Conditional update | UPDATE table_name SET column=value WHERE condition; |
Updates only rows that match the condition. |
| Multiple columns | UPDATE table_name SET col1=val1, col2=val2 WHERE id=1; |
Updates several columns simultaneously. |
🔴 DELETE — Remove Data
| Type | Syntax | Description |
|---|---|---|
| Delete all rows | DELETE FROM table_name; |
Removes all data from the table. |
| Conditional delete | DELETE FROM table_name WHERE condition; |
Removes rows that meet the condition. |
👁️ View Definition
Views are virtual tables based on the result of a query.
| Type | Syntax | Description |
|---|---|---|
| Create a view | CREATE VIEW view_name AS SELECT column1, column2 FROM table_name; |
Defines a view to simplify queries. |
| Modify a view | ALTER VIEW view_name AS SELECT ...; |
Changes the view definition. |
| Delete a view | DROP VIEW view_name; |
Removes a view from the database. |
🔄 Transaction Control
Transactions ensure data integrity by grouping operations into atomic units.
| Type | Syntax | Description |
|---|---|---|
| Start transaction |
BEGIN; or START TRANSACTION;
|
Begins a new transaction block. |
| Commit transaction | COMMIT; |
Saves all operations within the transaction. |
| Rollback transaction | ROLLBACK; |
Cancels the transaction and reverts changes. |
| Savepoint | SAVEPOINT name; |
Creates a point within a transaction to roll back to. |
| Rollback to savepoint | ROLLBACK TO SAVEPOINT name; |
Undoes operations back to a savepoint. |
| Release savepoint | RELEASE SAVEPOINT name; |
Deletes a savepoint marker. |
🧱 Integrity Constraints
Constraints ensure data validity and consistency within the database.
| Type | Syntax | Description |
|---|---|---|
| Primary key | PRIMARY KEY (column_name) |
Ensures unique and non-null values per row. |
| Foreign key | FOREIGN KEY (col) REFERENCES other_table(col) |
Enforces referential integrity between tables. |
| Unique | UNIQUE (column_name) |
Prevents duplicate values. |
| Not null | column_name data_type NOT NULL |
Disallows empty (NULL) values. |
| Check | CHECK (condition) |
Validates data against a logical condition. |
| Default | column_name data_type DEFAULT value |
Sets a default value for a column. |
🔐 Authorization and Security
Defines access permissions for users and roles.
| Type | Syntax | Description |
|---|---|---|
| Create user | CREATE USER 'username'@'host' IDENTIFIED BY 'password'; |
Creates a new user account. |
| Grant privilege | GRANT privilege ON database.table TO 'username'@'host'; |
Gives permissions (e.g., SELECT, INSERT). |
| Revoke privilege | REVOKE privilege ON database.table FROM 'username'@'host'; |
Removes permissions. |
| Drop user | DROP USER 'username'@'host'; |
Deletes a user. |
| Show privileges | SHOW GRANTS FOR 'username'@'host'; |
Displays granted permissions. |
💬 Embedded and Dynamic SQL
Used to integrate SQL into programming languages.
| Type | Syntax | Description |
|---|---|---|
| Embedded SQL | EXEC SQL statement; |
SQL written directly inside host language code. |
| Dynamic SQL | PREPARE stmt FROM 'SQL statement'; EXECUTE stmt; |
Builds and executes SQL statements dynamically at runtime. |
🏁 Conclusion
SQL is a vast language that unifies the way databases are created, manipulated, and secured.
By understanding its main components — DDL, DML, Views, Transactions, Integrity, Authorization, and Embedded SQL — developers can efficiently design robust and scalable database systems.
This guide serves as a complete quick reference to keep at hand while coding or learning database management.
Master these commands step by step, and you’ll have the foundation of nearly every relational database in the world.
Top comments (1)
Nicely explained 😊