MySQL — Important Points
1. Introduction to MySQL
• MySQL is an open-source Relational Database Management System (RDBMS).
• Uses SQL (Structured Query Language) to manage and query data.
• Owned by Oracle Corporation; widely used with PHP, [Link], Python (LAMP/MERN stacks).
• Data is stored in tables consisting of rows (records) and columns (fields).
2. MySQL Data Types
Category Types Notes
Numeric INT, TINYINT, BIGINT, FLOAT, DOUBLE, DECIMAL is exact — use for money
DECIMAL
String CHAR, VARCHAR, TEXT, ENUM, SET CHAR fixed length, VARCHAR
variable
Date/Time DATE, DATETIME, TIMESTAMP, TIME, YEAR TIMESTAMP auto-updates with
timezone
Other BLOB, JSON, BOOLEAN JSON type since MySQL 5.7
3. Categories of SQL Commands
DDL (Data Definition Language)
• CREATE, ALTER, DROP, TRUNCATE — define/modify database structure.
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50));
DML (Data Manipulation Language)
• SELECT, INSERT, UPDATE, DELETE — manipulate data inside tables.
INSERT INTO students (id, name) VALUES (1, 'Riya');
DCL (Data Control Language)
• GRANT, REVOKE — control access permissions.
TCL (Transaction Control Language)
• COMMIT, ROLLBACK, SAVEPOINT — manage transactions.
4. Keys in MySQL
• Primary Key — uniquely identifies each row; cannot be NULL.
• Foreign Key — links a column to the primary key of another table.
• Unique Key — ensures all values in a column are distinct.
• Composite Key — primary key made of two or more columns.
• Candidate Key — column(s) eligible to become the primary key.
5. Joins
Join Type Description
INNER JOIN Returns rows with matching values in both tables
LEFT JOIN All rows from left table + matched rows from right
RIGHT JOIN All rows from right table + matched rows from left
Join Type Description
FULL JOIN All rows from both tables (not natively supported; use UNION)
SELF JOIN A table joined with itself
CROSS JOIN Cartesian product of both tables
SELECT [Link], b.order_id FROM customers a INNER JOIN orders b ON [Link] = b.customer_id;
6. Constraints
• NOT NULL — column cannot have a NULL value.
• UNIQUE — ensures all values in a column are different.
• DEFAULT — sets a default value for a column.
• CHECK — validates values against a condition (MySQL 8.0.16+).
• AUTO_INCREMENT — automatically generates sequential numbers.
7. Indexes
• Improve the speed of data retrieval operations.
• Types: PRIMARY, UNIQUE, INDEX (normal), FULLTEXT, SPATIAL.
• Trade-off: faster reads but slower writes (INSERT/UPDATE) and extra storage.
CREATE INDEX idx_name ON students(name);
8. Normalization
• Process of organizing data to reduce redundancy and improve integrity.
• 1NF — atomic values, no repeating groups.
• 2NF — 1NF + no partial dependency on composite key.
• 3NF — 2NF + no transitive dependency.
• BCNF — stricter version of 3NF.
9. Aggregate & Common Functions
Function Purpose
COUNT() Counts number of rows
SUM() Adds numeric values
AVG() Calculates average
MIN() / MAX() Finds smallest/largest value
GROUP BY Groups rows sharing a value
HAVING Filters grouped results (like WHERE for groups)
10. Transactions & ACID Properties
• Atomicity — all operations succeed or none do.
• Consistency — database moves from one valid state to another.
• Isolation — concurrent transactions don't interfere with each other.
• Durability — committed changes persist even after a crash.
START TRANSACTION; UPDATE accounts SET balance = balance - 500 WHERE id = 1; UPDATE
accounts SET balance = balance + 500 WHERE id = 2; COMMIT;
11. Storage Engines
• InnoDB — default engine; supports transactions, foreign keys, row-level locking.
• MyISAM — faster reads, no transaction support, table-level locking.
• MEMORY — stores data in RAM for very fast temporary access.
12. Views, Procedures & Triggers
• View — a virtual table based on the result of a SQL query.
• Stored Procedure — a saved set of SQL statements that can be executed repeatedly.
• Trigger — automatically executes in response to INSERT/UPDATE/DELETE events.
CREATE VIEW active_students AS SELECT * FROM students WHERE status = 'active';
13. Common Clauses
• WHERE — filters rows based on a condition.
• ORDER BY — sorts result set (ASC/DESC).
• LIMIT — restricts number of rows returned.
• DISTINCT — removes duplicate rows from results.
• LIKE, IN, BETWEEN — used for pattern and range matching.
14. Best Practices
• Always use indexes on columns frequently used in WHERE/JOIN clauses.
• Avoid SELECT * in production queries — select only needed columns.
• Use appropriate data types to save storage and improve performance.
• Normalize schema design but denormalize selectively for performance when needed.
• Use prepared statements to prevent SQL injection.
• Regularly back up databases using mysqldump or binary logs.