📖 Glossary
Definitions of key terms and concepts from the MySQL Foundations course, organized alphabetically.
A
ACID
The four properties that guarantee reliable database transactions: Atomicity (all or nothing), Consistency (data stays valid), Isolation (transactions don't interfere), and Durability (committed changes survive crashes). See Lesson 18.
Aggregate Function
A function that operates on a set of rows and returns a single value. Examples: COUNT(), SUM(), AVG(), MIN(), MAX(). Used with GROUP BY to summarize data. See Lesson 9.
Alias
A temporary name given to a table or column in a query using AS. Example: SELECT price AS cost FROM books. Aliases make output more readable and are required for derived tables.
ALTER TABLE
A SQL statement that modifies an existing table's structure — adding, dropping, or changing columns, constraints, or indexes. See Lesson 4.
AUTO_INCREMENT
A column attribute that automatically generates a unique sequential integer for each new row. Typically used on primary key columns. See Lesson 5.
B
Backup
A copy of your database that can be used to restore data after loss or corruption. mysqldump creates logical backups as .sql files. See Lesson 20.
C
Cardinality
The number of unique values in a column. High cardinality (many unique values, like email) makes good index candidates. Low cardinality (few unique values, like boolean) generally does not.
CASCADE
A referential action on a foreign key. ON DELETE CASCADE automatically deletes child rows when the parent row is deleted. ON UPDATE CASCADE updates child values when the parent key changes. See Lesson 12.
CLI (Command-Line Interface)
A text-based interface for interacting with software by typing commands. The mysql client is a CLI tool for running SQL queries. See Lesson 3.
Column
A named attribute in a table that stores one type of data. Each column has a data type (e.g., VARCHAR, INT). Also called a field.
COMMIT
A transaction command that permanently saves all changes made during the transaction. Once committed, the changes are durable and cannot be rolled back. See Lesson 18.
Composite Index
An index built on two or more columns. The order of columns matters — the index is most effective when queries filter on the leftmost columns first. See Lesson 14.
Constraint
A rule enforced on a column or table to ensure data integrity. Examples: PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, CHECK, DEFAULT. See Lesson 5.
CRUD
The four basic database operations: Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE). See Lesson 6.
CSV (Comma-Separated Values)
A plain-text file format where each line is a row and values are separated by commas. Used for importing/exporting data between MySQL and spreadsheets. See Lesson 20.
D
Database
An organized collection of structured data stored electronically. In MySQL, a database is a container that holds related tables, views, procedures, and other objects.
Data Type
The kind of data a column can hold. Common types include INT (numbers), VARCHAR (text), DECIMAL (exact numbers), DATE (dates), and BOOLEAN (true/false). See Lesson 5.
DDL (Data Definition Language)
SQL statements that define or modify database structure: CREATE, ALTER, DROP, TRUNCATE.
Denormalization
Intentionally adding redundancy to a normalized schema to improve read performance. The trade-off is more complex writes and potential data inconsistency. See Lesson 16.
Derived Table
A subquery used in the FROM clause. It acts as a temporary table within the query and must have an alias. See Lesson 10.
DML (Data Manipulation Language)
SQL statements that manipulate data within tables: SELECT, INSERT, UPDATE, DELETE.
E
ER Diagram (Entity-Relationship Diagram)
A visual representation of tables (entities), their columns (attributes), and the relationships between them. Used during database design. See Lesson 11.
EXPLAIN
A command that shows how MySQL will execute a query — which indexes it will use, how many rows it will scan, and the join strategy. Essential for performance tuning. See Lesson 14.
F
Foreign Key
A column (or set of columns) in one table that references the primary key of another table. Foreign keys enforce referential integrity — ensuring child records always point to valid parent records. See Lesson 12.
Full Table Scan
When MySQL reads every row in a table to find matching records. This is slow on large tables and usually indicates a missing index. Visible in EXPLAIN output as type: ALL.
G
GRANT
A SQL statement that gives specific privileges (like SELECT, INSERT) to a user account. Part of MySQL's access control system. See Lesson 19.
GROUP BY
A clause that groups rows sharing a common value so aggregate functions can be applied to each group. Example: GROUP BY author_id lets you count books per author. See Lesson 9.
H
HAVING
A clause that filters groups (created by GROUP BY) based on aggregate values. Like WHERE, but for groups instead of individual rows. Example: HAVING COUNT(*) > 5. See Lesson 9.
I
Index
A data structure that speeds up data retrieval by allowing MySQL to find rows without scanning the entire table. Similar to a book's index. Trade-off: faster reads, slightly slower writes. See Lesson 14.
InnoDB
MySQL's default storage engine. Supports transactions, foreign keys, row-level locking, and crash recovery. The recommended engine for most use cases.
INNER JOIN
A join that returns only rows where there is a match in both tables. Rows with no match are excluded from the result. See Lesson 13.
J
JOIN
A SQL operation that combines rows from two or more tables based on a related column. Types include INNER, LEFT, RIGHT, CROSS, and self-joins. See Lesson 13.
Junction Table
A table used to implement many-to-many relationships. It contains foreign keys to both related tables. Example: book_genres links books and genres. Also called a bridge table, linking table, or associative table. See Lesson 11.
L
LAMP Stack
Linux, Apache, MySQL, PHP — a popular open-source web development stack. This course uses a LAMP setup on Ubuntu/WSL. See Lesson 2.
LEFT JOIN
A join that returns all rows from the left table plus matching rows from the right table. If there's no match, the right side columns contain NULL. See Lesson 13.
LOAD DATA INFILE
A SQL statement for bulk-importing data from a file (typically CSV) into a table. Much faster than individual INSERT statements. See Lesson 20.
M
Many-to-Many (M:M)
A relationship where records in Table A can relate to multiple records in Table B, and vice versa. Implemented with a junction table. Example: books and genres. See Lesson 11.
mysqldump
A command-line utility that creates logical backups of MySQL databases as .sql files. The most common backup tool for MySQL. See Lesson 20.
N
Normalization
The process of organizing a database schema to reduce redundancy and prevent anomalies. Normal forms include 1NF, 2NF, and 3NF — each building on the previous. See Lesson 16.
NULL
A special value representing the absence of data — "unknown" or "not applicable." NULL is not the same as zero, an empty string, or false. Test for it with IS NULL or IS NOT NULL.
O
One-to-Many (1:M)
The most common relationship type. One record in Table A relates to many records in Table B. Example: one author has many books. Implemented with a foreign key in the "many" table. See Lesson 11.
One-to-One (1:1)
A relationship where one record in Table A relates to exactly one record in Table B. Less common; used to split large tables or store optional data separately. See Lesson 11.
ORM (Object-Relational Mapper)
A library that lets you interact with a database using objects and methods in your programming language instead of writing raw SQL. Examples: SQLAlchemy (Python), Eloquent (PHP), Prisma (JS). See Lesson 21.
P
phpMyAdmin
A web-based GUI for managing MySQL databases. Allows you to create databases, run queries, import/export data, and manage users through a browser interface. See Lesson 3.
Prepared Statement
A precompiled SQL statement with placeholders for parameters. Prevents SQL injection by separating the query structure from user input. Supported by all major language connectors.
Primary Key
A column (or combination of columns) that uniquely identifies each row in a table. Every table should have one. Cannot be NULL. See Lesson 5.
Principle of Least Privilege
A security practice: grant each user or application only the minimum permissions needed for their specific task. See Lesson 19.
Q
Query
A request for data or action sent to the database using SQL. A SELECT query reads data; an INSERT/UPDATE/DELETE query modifies data.
R
Referential Integrity
The guarantee that foreign key values always point to existing parent records. Enforced by foreign key constraints. See Lesson 12.
Relational Database
A database that organizes data into tables (relations) with rows and columns, where tables can be linked through shared keys. MySQL, PostgreSQL, and SQLite are relational databases. See Lesson 1.
REVOKE
A SQL statement that removes previously granted privileges from a user. The opposite of GRANT. See Lesson 19.
Role
A named collection of privileges (MySQL 8+). Roles can be assigned to multiple users, making permission management easier. See Lesson 19.
ROLLBACK
A transaction command that undoes all changes made during the current transaction, restoring data to its state before the transaction began. See Lesson 18.
Row
A single record in a table. Each row contains values for every column in the table. Also called a record or tuple.
S
Savepoint
A named checkpoint within a transaction. You can roll back to a savepoint without undoing the entire transaction. See Lesson 18.
Schema
The structure or blueprint of a database — its tables, columns, data types, relationships, constraints, and indexes. In MySQL, "schema" and "database" are often used interchangeably.
SQL (Structured Query Language)
The standard language for managing and querying relational databases. Pronounced "sequel" or "S-Q-L." All relational databases use SQL (with minor dialect differences). See Lesson 1.
SQL Injection
A security vulnerability where an attacker inserts malicious SQL code into a query through user input. Prevented by using prepared statements / parameterized queries. See Lesson 21.
Stored Procedure
A precompiled set of SQL statements stored in the database and executed by name with CALL. Useful for encapsulating complex logic and reusing it across applications. See Lesson 17.
Subquery
A query nested inside another query. Can appear in WHERE, FROM, or SELECT clauses. Returns a value, a row, or a set of rows. See Lesson 10.
T
Table
A structured collection of data organized into rows and columns. Tables are the fundamental storage unit in a relational database. Also called a relation.
Transaction
A sequence of SQL operations treated as a single unit of work. Either all operations succeed (COMMIT) or all are undone (ROLLBACK). See Lesson 18.
U
utf8mb4
MySQL's true UTF-8 character encoding, supporting all Unicode characters including emojis (4 bytes per character). Always use utf8mb4 instead of MySQL's legacy utf8 (which only supports 3 bytes).
V
View
A virtual table defined by a saved SELECT query. Views simplify complex queries and can restrict which data users can see. They don't store data themselves. See Lesson 15.
W
WHERE
A clause that filters rows based on conditions. Used with SELECT, UPDATE, and DELETE. Evaluated before GROUP BY and HAVING. See Lesson 7.
Wildcard
A special character used in LIKE patterns for flexible matching. % matches zero or more characters. _ matches exactly one character. See Lesson 7.