๐ง SQL Cheatsheet (In Progress...)
๐ What is a Database?
- A database is a collection of related information or data that can be stored, managed, and retrieved.
๐ SQL vs NoSQL
- SQL: Structured Query Language used to manage Relational Databases (RDBMS).
- NoSQL: Used for Non-Relational Databases (NRDBMS) like MongoDB, Redis, etc.
๐ Arch Linux Tip
- In Arch, MariaDB is a drop-in replacement for MySQL.
๐ Table Creation & Management
โ CREATE TABLE
CREATE TABLE users (
id INT AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
โ DESCRIBE
DESCRIBE users;
โ ALTER TABLE
- Add column
ALTER TABLE users ADD age INT;
- Modify column
ALTER TABLE users MODIFY age DECIMAL(5,2);
- Drop column
ALTER TABLE users DROP COLUMN age;
โ DROP TABLE
DROP TABLE users;
๐ Keys in SQL
๐น Primary Key
A column (or set of columns) that uniquely identifies each row in a table.
- Must be unique
- Cannot be NULL
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100)
);
๐น Foreign Key
A column that references the primary key of another table to establish a relationship between them.
- Maintains referential integrity
- Can be NULL (if optional relationship)
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
);
๐น Surrogate Key
An artificial key used as a unique identifier (usually AUTO_INCREMENT
or UUID
)
- Has no business meaning
- Used instead of natural keys to avoid complications
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
๐น Composite Key
A primary key made of multiple columns. Used when a single column canโt uniquely identify a row.
CREATE TABLE enrollment (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);
๐น Candidate Key
Any column (or set of columns) that could be a primary key (i.e., unique and non-null).
- One becomes the primary key, others are alternate keys.
Alternate Key
A candidate key that was not chosen as the primary key but can still uniquely identify rows.
๐ Data Types
Type | Description |
---|---|
INT | Integer values |
DECIMAL | Fixed-point decimal (money, etc.) |
VARCHAR | Variable-length text |
BLOB | Binary large object (images, files) |
DATE | Stores year-month-day |
TIMESTAMP | Stores date & time |
๐ Querying & Data Insertion
โ SELECT
SELECT * FROM users;
โ INSERT
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
๐ Constraints
Constraint | Use Case |
---|---|
NOT NULL | Prevents null entries in a column |
UNIQUE | Ensures unique value in a column |
DEFAULT | Sets default value if none is provided |
AUTO_INCREMENT | Automatically increments numeric value |
๐ UPDATE & DELETE in SQL (MySQL)
๐ UPDATE
โ Modify Existing Records
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
โ Example:
-- Update a student's name where ID is 1
UPDATE students
SET name = 'Tanvir Ahmed'
WHERE id = 1;
โ ๏ธ Always use
WHERE
withUPDATE
to avoid updating all rows!
โ DELETE
โ Remove Records from a Table
DELETE FROM table_name
WHERE condition;
โ Example:
-- Delete a student where ID is 3
DELETE FROM students
WHERE id = 3;
โ ๏ธ
DELETE
without aWHERE
clause removes all rows! Use carefully.
๐งช Pro Tips
- ๐ Always
SELECT
the rows first before doing anUPDATE
orDELETE
:SELECT * FROM students WHERE id = 1;
- ๐งฏ Use transactions if you're unsure:
START TRANSACTION; UPDATE students SET name = 'Test' WHERE id = 1; ROLLBACK; -- or COMMIT;
๐ Basic Queries
- Retrieve all columns:
SELECT * FROM table_name;
- Retrieve specific columns:
SELECT name, age FROM users;
- Filtering:
SELECT * FROM users WHERE age > 18;
- Sorting:
SELECT * FROM users ORDER BY name ASC;
- Limiting:
SELECT * FROM users LIMIT 5;
๐งฑ Creating a Simple Schema
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
age INT,
email VARCHAR(100) UNIQUE
);
๐งฎ Functions & Aggregates
COUNT(*)
โ total rowsAVG(column)
โ averageSUM(column)
โ total sumMAX(column)
/MIN(column)
โ max/min valuesNOW()
โ current datetime
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM students;
๐ JOINS
- INNER JOIN: Only matching rows
SELECT * FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;
- LEFT JOIN: All from left + matched from right
- RIGHT JOIN: All from right + matched from left
๐ฅ Nested Queries
SELECT name FROM students
WHERE id IN (
SELECT student_id FROM enrollments WHERE course_id = 1
);
๐งฌ UNION
- Combine result sets (must have same number of columns)
SELECT name FROM teachers
UNION
SELECT name FROM students;
๐ Wildcards
%
= any number of characters_
= a single character
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM products WHERE code LIKE '_23%';
๐ ON DELETE SET NULL / CASCADE
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE SET NULL
);
-- or
ON DELETE CASCADE
-- deletes all related orders when user is deleted
๐ Advanced SQL & Database Design Concepts
๐งฉ DELIMITER in SQL
By default, SQL statements end with a semicolon ;
. But when writing complex statements like stored procedures or triggers, we temporarily change the delimiter to avoid premature termination.
๐ง Usage:
DELIMITER $$
CREATE PROCEDURE example_proc()
BEGIN
-- Multiple SQL statements
SELECT "Hello";
SELECT "World";
END $$
DELIMITER ;
DELIMITER $$
tells the MySQL interpreter to treat$$
as the end of the command block instead of;
. This is useful for functions, procedures, and triggers.
โก Triggers
Triggers are special stored procedures that automatically run in response to specific events on a table.
๐ง Types of Triggers:
BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE
โ Syntax:
DELIMITER $$
CREATE TRIGGER before_insert_user
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END $$
DELIMITER ;
NEW
andOLD
are special keywords to access values during trigger execution.
๐งฌ ER Diagrams (Entity-Relationship Diagrams)
ER diagrams visually represent how entities (tables) relate to one another in a database.
๐งฑ Key Concepts:
๐งพ Entities:
- Represent real-world objects (e.g., Student, Course).
- Become tables in the database.
๐ Attributes:
- Properties of an entity (e.g., name, age, ID).
- Types:
- Simple Attribute: Cannot be divided (e.g., age).
- Composite Attribute: Can be divided (e.g., name โ first, last).
- Derived Attribute: Computed (e.g., age from DOB).
- Multivalued Attribute: Has multiple values (e.g., phone numbers).
๐ Relationships:
- Connects two or more entities.
- Cardinality types:
- One-to-One
- One-to-Many
- Many-to-Many
๐งฉ Keys:
- Primary Key: Uniquely identifies a record.
- Foreign Key: Creates a link between tables.
๐ Participation:
- Total Participation: Every entity must participate in a relationship.
- Partial Participation: Optional participation.
๐ Example ER Scenario:
Entities: Student, Course, Enrollment
Relations:
- A
Student
can enroll in multipleCourses
. - A
Course
can have manyStudents
.
This forms a many-to-many relationship, usually resolved with an intermediate Enrollment table.
ER diagrams help with planning, normalization, and identifying relationships before creating the schema.