IT Course Directory: VMware, Linux, Networking, and Raspberry Pi

MySQL Course: Database Design, SQL Queries, and Administration

Learn MySQL from fundamentals through database design, SQL queries, joins, transactions, security, performance, backups, and practical administration.

MySQL is a relational database management system (RDBMS). It stores structured information in tables and lets applications create, read, update, and delete that information with SQL. This course is designed for beginners, developers, and students who want practical experience with database design, querying, security, performance, and administration.

You should be comfortable navigating files and running basic command-line commands. Previous SQL experience is not required.

1. MySQL and relational database fundamentals

A database server is the running MySQL service that accepts connections and processes SQL statements. A database, also called a schema in many MySQL contexts, is a named collection of database objects. A table stores related data inside that database.

• A table represents an entity or subject, such as students or courses.
• A row, also called a record, represents one item in a table.
• A column represents an attribute, such as email or enrollment date.
• A schema describes the structure of databases, tables, columns, relationships, and constraints.

SQL, or Structured Query Language, has several broad roles. Data definition language (DDL) creates and changes structures with statements such as CREATE, ALTER, and DROP. Data manipulation language (DML) changes rows with INSERT, UPDATE, and DELETE. Queries normally use SELECT. Transaction statements such as COMMIT and ROLLBACK control groups of changes.

MySQL is commonly used for websites, APIs, business applications, content systems, reporting tools, and transactional workloads. The Community edition is commonly used for learning and many applications; commercial editions provide additional enterprise features and support. You can work with MySQL through the mysql command-line client, MySQL Shell, MySQL Workbench, application drivers, and administration tools.

2. Installing and connecting to MySQL

Install the MySQL Server package for your operating system, then install a client such as the command-line client or a graphical client. During installation, record the administrative account details and ensure the server is configured to start when appropriate.

The default MySQL TCP port is 3306. A connection identifies a host, port, username, and password. Local connections commonly use localhost or a local socket; remote connections require network access and a MySQL account permitted from the client host.

mysql -u username -p
mysql -h hostname -P 3306 -u username -p

The client prompts for the password when -p is used without a password value. Avoid placing passwords directly in shell history or scripts.

On systems using systemd, service management commonly looks like this:

sudo systemctl start mysql
sudo systemctl stop mysql
sudo systemctl status mysql

Service names and graphical service controls vary by operating system. If a connection fails, confirm that the service is running, the host and port are correct, the account is allowed from that host, and a firewall is not blocking the connection.

CREATE DATABASE training_db;
USE training_db;

CREATE DATABASE creates a working database and USE selects it for subsequent statements. Use SHOW DATABASES; to list databases and SELECT DATABASE(); to display the currently selected database.

3. Defining databases, tables, and constraints

Choose names that describe their contents. Use consistent plural or singular table naming, lowercase conventions where appropriate, and clear column names. Avoid reserved words and ambiguous names such as data or value when a more specific name is available.

NULL means that a value is unknown or not present; it is not the same as zero or an empty string. Use NOT NULL when a value is required. A DEFAULT supplies a value when an insert omits the column. AUTO_INCREMENT generates sequential numeric identifiers.

CREATE TABLE students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  active BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE = InnoDB;

A primary key uniquely identifies each row. A unique constraint prevents duplicate non-NULL values in a key column or column combination. A check constraint expresses a rule such as a nonnegative price; verify behavior against the MySQL version and SQL mode used by your deployment.

CREATE TABLE enrollments (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

Use ALTER TABLE to evolve a table carefully:

ALTER TABLE students ADD COLUMN phone VARCHAR(30);
ALTER TABLE students MODIFY COLUMN full_name VARCHAR(150) NOT NULL;
ALTER TABLE students RENAME COLUMN phone TO phone_number;
ALTER TABLE students DROP COLUMN phone_number;

DROP DATABASE and DROP TABLE remove definitions and their data. Treat them as destructive operations and verify the target before executing them.

4. Inserting, updating, and deleting data

INSERT INTO students (full_name, email)
VALUES ('Maya Chen', 'maya@example.test');

INSERT INTO students (full_name, email)
VALUES
  ('Owen Smith', 'owen@example.test'),
  ('Priya Shah', 'priya@example.test');

UPDATE students
SET email = 'maya.chen@example.test'
WHERE student_id = 1;

DELETE FROM students
WHERE student_id = 99;

Always make the WHERE clause explicit for an intended subset. Before an important UPDATE or DELETE, run an equivalent SELECT and inspect the matching rows. Constraints can reject an insert, prevent duplicate keys, reject invalid values, or prevent deletion of a parent row referenced by child rows.

Basic exports commonly use SQL dump files. An import executes the statements in that file, so inspect its source and target before restoring it.

5. Retrieving data with SELECT

SELECT student_id, full_name, email
FROM students
WHERE enrolled_at >= '2026-01-01'
ORDER BY full_name
LIMIT 20 OFFSET 0;

Prefer explicit columns over SELECT * in application and performance-sensitive queries. Use AS for readable aliases and DISTINCT when duplicate result values are intentionally removed.

SELECT DISTINCT active AS status
FROM students
WHERE full_name LIKE 'M%'
  AND student_id IN (1, 2, 3);

SELECT full_name, email
FROM students
WHERE enrolled_at BETWEEN '2026-01-01' AND '2026-12-31'
  AND email IS NOT NULL;

Comparison operators include =, <>, <, <=, >, and >=. Logical operators include AND, OR, and NOT. Arithmetic operators calculate values. Pattern matching uses LIKE; % matches any sequence of characters and _ matches one character. Use IS NULL and IS NOT NULL rather than equality comparisons with NULL.

Readable queries place one selected column per line, indent conditions, and use meaningful aliases. Parenthesize mixed AND/OR expressions so the intended logic is obvious.

6. Functions, grouping, and reports

Aggregate functions summarize multiple rows: COUNT counts rows or non-NULL values, SUM totals numeric values, AVG calculates an average, and MIN and MAX find boundary values.

SELECT course_id, COUNT(*) AS enrollment_total
FROM enrollments
GROUP BY course_id
HAVING COUNT(*) >= 5
ORDER BY enrollment_total DESC;

Useful functions include string functions such as CONCAT and LOWER, numeric functions such as ROUND, date functions such as YEAR and DATEDIFF, and conditional expressions such as COALESCE and CASE.

SELECT
  full_name,
  CASE WHEN active = TRUE THEN 'Current' ELSE 'Inactive' END AS student_status
FROM students;

7. Relationships and joins

A one-to-one relationship connects one row to at most one row. A one-to-many relationship connects one parent to many children, such as one instructor teaching many courses. A many-to-many relationship requires a junction table: students enroll in many courses and courses contain many students, so enrollments stores the pair of foreign keys.

SELECT s.full_name, c.course_name
FROM students AS s
INNER JOIN enrollments AS e ON e.student_id = s.student_id
INNER JOIN courses AS c ON c.course_id = e.course_id;

SELECT s.student_id, s.full_name, e.course_id
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.student_id;

Join on the actual key relationship and include every required condition. One-to-many joins legitimately return multiple rows for one parent. Do not hide an incorrect join with DISTINCT; first determine whether the duplication represents real cardinality or a missing predicate.

8. Subqueries, common table expressions, and set operations

A scalar subquery returns one value, a row subquery returns one row, and a table subquery returns a result set. Subqueries can appear in WHERE, FROM, or SELECT.

SELECT full_name
FROM students AS s
WHERE EXISTS (
  SELECT 1
  FROM enrollments AS e
  WHERE e.student_id = s.student_id
);

WITH course_counts AS (
  SELECT course_id, COUNT(*) AS total
  FROM enrollments
  GROUP BY course_id
)
SELECT course_id, total
FROM course_counts
WHERE total > 10;

EXISTS tests whether a related row exists and NOT EXISTS finds rows with no related match. A join is often preferable when you need columns from related tables or a straightforward relational plan; a subquery can express existence or a separately calculated value clearly.

UNION combines compatible result sets and removes duplicate rows. UNION ALL preserves duplicates and is usually more efficient when duplicates are meaningful or impossible. MySQL versions that support window functions can calculate values across related rows without collapsing them, for example using ROW_NUMBER() or SUM() OVER (...).

9. Database design and normalization

Start with requirements. Identify entities, their attributes, and relationships. An entity-relationship diagram (ERD) shows tables, keys, and relationship cardinality before implementation.

A candidate key is any minimal set of columns that uniquely identifies a row. The chosen candidate key becomes the primary key. A natural key comes from business data, such as a government-issued identifier; a surrogate key is an artificial identifier such as an auto-incrementing integer. Natural keys can be meaningful but may change or be unwieldy, while surrogate keys simplify references but do not replace business uniqueness constraints.

• First normal form: each column contains atomic values and repeating groups are separated.
• Second normal form: the table is in first normal form and every non-key attribute depends on the entire primary key, important for composite keys.
• Third normal form: the table is in second normal form and non-key attributes depend only on the key, not on another non-key attribute.

Normalization reduces duplicate data and prevents insert, update, and delete anomalies. Choose types and lengths based on valid requirements, then enforce quality with primary keys, foreign keys, unique constraints, NOT NULL, defaults, and checks. Deliberate denormalization can improve reporting performance, but it adds synchronization responsibility.

10. Indexes and query performance

An index is an auxiliary structure that helps MySQL locate rows without scanning an entire table. Primary keys and unique constraints create useful indexes. You can also create single-column or composite indexes.

CREATE INDEX idx_students_enrolled_at
ON students (enrolled_at);

EXPLAIN
SELECT student_id, full_name
FROM students
WHERE enrolled_at >= '2026-01-01';

Read EXPLAIN to inspect the table access type, possible keys, selected key, estimated rows, and extra operations. A full table scan may be reasonable for a tiny table but concerning for a large frequently queried table. Write index-friendly predicates: avoid wrapping an indexed column in a function when a range comparison can express the same condition. Indexes speed suitable reads but consume storage and make inserts, updates, and deletes more expensive.

11. Transactions and concurrency

A transaction groups operations into one logical unit. ACID describes its goals: atomicity makes all changes succeed or fail together; consistency preserves rules; isolation controls interaction between concurrent transactions; and durability preserves committed changes.

START TRANSACTION;
UPDATE products
SET stock = stock - 1
WHERE product_id = 42 AND stock > 0;
COMMIT;

Use ROLLBACK when validation or a later statement fails. Autocommit commits each statement automatically unless you explicitly start a transaction. InnoDB is normally chosen for transactional tables because it supports transactions, foreign keys, and row-level locking. Isolation levels determine which uncommitted or changing data a transaction can observe. Keep transactions short, access shared resources consistently, and plan how the application handles deadlocks or lock wait timeouts.

12. Users, privileges, and application security

Give each person or application a separate MySQL account. Apply least privilege: grant only the operations and objects required for the task, and avoid using an administrative account in application configuration.

CREATE USER 'app_user'@'localhost'
IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE
ON training_db.* TO 'app_user'@'localhost';
REVOKE DELETE ON training_db.* FROM 'app_user'@'localhost';

Use secure password storage and secret-management practices. The host portion of an account matters: an account permitted from localhost is not automatically permitted from every remote host. Parameterized queries keep values separate from SQL code and are the primary defense against SQL injection.

SELECT student_id, full_name
FROM students
WHERE email = ?;

Do not construct SQL by concatenating untrusted input. Validate input, use parameterized statements in the application driver, restrict network exposure, and grant only necessary privileges.

13. Views, routines, triggers, and scheduled events

A view is a saved query that provides a reusable interface to data. It can simplify reports and expose selected columns without granting direct access to every underlying column.

CREATE VIEW active_students AS
SELECT student_id, full_name, email
FROM students
WHERE active = TRUE;

SELECT * FROM active_students;

Stored procedures package database-side operations, while stored functions return a value and can be used in expressions. Triggers run automatically when specified table events occur and can enforce narrowly defined audit or consistency behavior. Scheduled events automate time-based work when enabled and appropriate.

Database-side logic can centralize rules and reduce network round trips, but it can also make deployment, testing, debugging, and portability harder. Decide deliberately whether a rule belongs in the database, the application, or both.

14. Backup, restore, and maintenance

A backup is useful only if it can be restored. Test recovery in a clean environment, verify table structures and row counts, document the restore procedure, and define recovery objectives. Routine maintenance includes monitoring storage, reviewing errors and slow queries, checking indexes and constraints, and keeping server and client versions compatible.

15. Practical project: course enrollment database

Build a small course system with students, instructors, courses, and enrollments. Give each main table a primary key. Add a foreign key from each course to its instructor and from each enrollment to both its student and course. Use a composite primary key on enrollments(student_id, course_id) to prevent duplicate enrollment.

1. Translate requirements into entities, attributes, and relationships; draw an ERD.
2. Create the database and tables in parent-to-child order.
3. Insert representative students, instructors, courses, and enrollment rows.
4. Implement CRUD operations, testing every destructive statement with a restrictive WHERE.
5. Write a report joining students, courses, and enrollments, then count enrollments by course.
6. Use HAVING to retain courses above a chosen enrollment threshold and sort by the count.
7. Add an index based on a real filter or join pattern and compare EXPLAIN output.
8. Create a limited application user and verify that an unauthorized operation fails.
9. Export the database, restore it into a clean test database, and verify tables and row counts.

16. Online store transaction exercise

An online store can use customers, products, orders, and order_items. The order item stores the product, quantity, and price captured at purchase time. Calculate an order total with SUM(quantity * unit_price). Use a LEFT JOIN to find customers without orders.

Creating an order and reducing inventory should be treated as one transaction. Validate stock, insert the order and its items, reduce inventory, and commit only if every statement succeeds. Otherwise roll back so the database does not contain a partial order.

17. Troubleshooting and exam notes

• Access denied: check the username, password, host portion of the account, and required privileges.
• Foreign-key failure: confirm the parent row exists, related definitions are compatible, and inserts occur in parent-to-child order.
• Unexpected mass update or delete: run a matching SELECT, inspect the predicate, check affected-row counts, and use a transaction.
• Slow query: inspect EXPLAIN, look for scans, review filter and join columns, avoid unnecessary columns, and avoid functions around indexed columns where possible.
• Duplicate join rows: check one-to-many cardinality, incomplete join predicates, and missing junction-table conditions.
• Grouping error: define the intended grouping grain and either group or aggregate every selected expression appropriately.
• Truncated or rejected value: inspect the column definition, strict SQL mode, input format, and selected data type.
• Restore failure: verify the dump file, target database, privileges, shell redirection, and version or collation compatibility.

Continue learning

Use the MySQL course curriculum to organize these subjects into a study sequence, and return to the MySQL course overview for the broader course context.