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

MySQL Course: SQL, Database Design, Queries, and Administration

Learn MySQL from the fundamentals through SQL queries, relational design, joins, transactions, security, backups, performance, and application integration.

This comprehensive MySQL course teaches relational database concepts, SQL, schema design, querying, administration, security, backups, and application integration. It is suitable for beginners, web developers, database students, and junior administrators.

You need basic command-line familiarity. Programming experience is helpful but not required. Use the MySQL course curriculum to organize your study and the course activities to practise each skill.

MySQL and relational database foundations

MySQL is a relational database management system, or DBMS. A DBMS stores structured data, provides SQL commands for reading and changing it, enforces rules, manages concurrent users, and supports backup and recovery.

A MySQL server can contain multiple databases, also called schemas. A database contains tables. A table contains columns and rows. A column describes one attribute, such as email or price; a row is one record; and a field is a particular value at the intersection of a row and column.

The relational model represents entities as tables and relationships through keys. For example, a customer can have many orders. Instead of repeating customer details in every order row, store the customer once and reference it with a key.

Installation and connection

Install MySQL Server for the database service and a client tool for sending SQL. The command-line client is useful for scripts and troubleshooting. MySQL Workbench is an optional graphical tool for editing SQL, browsing schemas, and visualizing relationships.

After installation, start the MySQL service using your operating system's service manager. Stop it through the same manager when maintenance requires it. A local connection commonly uses:

mysql -u username -p

For a remote server, specify its host and port:

mysql -h hostname -P port -u username -p database_name

Connection failures may result from a stopped server, incorrect host or port, invalid credentials, firewall rules, or an account that is not permitted from the connecting host.

Creating a practice store database

The following schema models customers, products, orders, and order items. An order item is a junction-style table: it connects an order to a product and records quantity and the price at the time of purchase.

CREATE DATABASE store_practice;
USE store_practice;

CREATE TABLE customers (
  customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  category VARCHAR(80) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  ordered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE order_items (
  order_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
);

Use SHOW DATABASES; to list databases, USE store_practice; to select one, and SHOW TABLES; to list its tables. Inspect definitions with DESCRIBE products; or SHOW CREATE TABLE products;. Rename or modify objects with ALTER TABLE; use DROP DATABASE or DROP TABLE only when permanent removal is intended.

Data types and constraints

NULL means that a value is unknown or not supplied; it is not the same as an empty string or zero. Use NOT NULL when a value is required. DEFAULT supplies a value when one is omitted. AUTO_INCREMENT generates a new numeric identifier. A primary key identifies each row, while a unique constraint prevents duplicate values.

CRUD: inserting, reading, updating, and deleting

INSERT INTO customers (name, email)
VALUES ('Asha Patel', 'asha@example.test');

INSERT INTO products (name, category, price, stock)
VALUES ('Keyboard', 'Accessories', 49.99, 20),
       ('Monitor', 'Displays', 199.00, 8);

SELECT customer_id, name, email
FROM customers;

UPDATE customers
SET email = 'asha.patel@example.test'
WHERE customer_id = 1;

DELETE FROM customers
WHERE customer_id = 1;

Always test an UPDATE or DELETE condition with a SELECT first. A missing WHERE clause can change or remove every row. Use a transaction for risky changes. DELETE removes matching rows and supports WHERE. TRUNCATE TABLE quickly removes all rows from a table and does not support a row filter. DROP TABLE removes the table definition and its data.

Filtering, sorting, and limiting results

SELECT name, price
FROM products
WHERE category IN ('Accessories', 'Displays')
  AND price BETWEEN 25 AND 250
  AND name LIKE '%o%'
ORDER BY price DESC
LIMIT 10;

DISTINCT removes duplicate result combinations. ORDER BY determines presentation order, and LIMIT restricts the number of rows. Offset pagination can use LIMIT 20 OFFSET 40, but keyset pagination is often more efficient for large, changing datasets.

Expressions, functions, and aliases

SELECT p.name AS product_name,
       ROUND(p.price * 1.20, 2) AS price_with_tax,
       UPPER(p.category) AS category_name,
       COALESCE(p.stock, 0) AS available_stock,
       CASE WHEN p.stock = 0 THEN 'out of stock'
            WHEN p.stock < 5 THEN 'low stock'
            ELSE 'available' END AS stock_status
FROM products AS p;

Aliases make column and table references readable. Arithmetic expressions can calculate totals. Common string functions include CONCAT, LOWER, UPPER, SUBSTRING, and TRIM. Numeric functions include ROUND, ABS, and CEIL. Date functions include CURDATE, NOW, DATE, YEAR, and MONTH.

COALESCE returns the first non-NULL expression; IFNULL handles a two-choice replacement; NULLIF returns NULL when two expressions are equal. Conditional business logic can be expressed with CASE.

Aggregation and grouping

SELECT category,
       COUNT(*) AS product_count,
       AVG(price) AS average_price,
       MIN(price) AS lowest_price,
       MAX(price) AS highest_price,
       SUM(stock) AS total_stock
FROM products
WHERE price > 0
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY average_price DESC;

COUNT, SUM, AVG, MIN, and MAX are aggregate functions. WHERE filters individual rows before grouping; HAVING filters groups after aggregation. Grouping by multiple columns creates one group for each combination. Every selected non-aggregate column should be represented appropriately in GROUP BY.

Joining related tables

Normalized data is split into related tables to reduce repetition. A join combines rows using a relationship condition.

SELECT o.order_id, c.name AS customer_name,
       SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.order_id, c.name;

An incomplete join condition can produce an accidental Cartesian result or duplicate rows. Check the cardinality of each relationship: one-to-many joins legitimately repeat the parent for each child. Use aggregation or DISTINCT only when it matches the intended meaning, not merely to hide a faulty join.

Subqueries and set operations

A scalar subquery returns one value, a row subquery returns one row, and a table subquery returns a result set. IN tests membership; EXISTS tests whether at least one related row exists.

SELECT name
FROM products AS p
WHERE p.product_id IN (
  SELECT oi.product_id
  FROM order_items AS oi
);

SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

A correlated subquery refers to a value from the outer query and may be evaluated repeatedly. A derived table is a subquery in the FROM clause. Common table expressions, introduced with WITH, make multi-step queries easier to read where the MySQL version supports them. Use joins when they express the relationship clearly and allow the optimizer to form an efficient plan; use subqueries when existence or staged logic is clearer.

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;

UNION removes duplicate rows; UNION ALL preserves them and is usually faster when duplicates are meaningful or impossible.

Relational design and normalization

Begin schema design by identifying entities, their attributes, and their relationships. A one-to-one relationship pairs one row with one row. A one-to-many relationship lets one parent have many children. A many-to-many relationship requires a junction table containing foreign keys to both sides.

Normalization organizes data so each fact is stored in an appropriate place. First normal form uses atomic values rather than repeating lists. Second normal form removes attributes that depend on only part of a composite key. Third normal form removes attributes that depend on another non-key attribute. The goal is to prevent insertion, update, and deletion anomalies while keeping queries practical.

For student enrollment, use students, courses, and enrollments(student_id, course_id). The junction table can have a composite primary key and additional attributes such as enrollment date or grade. Foreign keys enforce referential integrity and prevent orphaned rows.

Indexes and query performance

An index is an additional data structure that helps MySQL locate rows without scanning every row. Primary keys and unique constraints create indexes; secondary indexes support other access patterns.

Composite indexes follow the leftmost-prefix principle: an index on (category, price) is useful for category alone and category plus price, but generally not for price alone. Avoid indexing every column. Indexes improve some reads but require storage and maintenance during writes.

EXPLAIN SELECT product_id, name, price
FROM products
WHERE category = 'Displays' AND price > 100
ORDER BY price;

Use EXPLAIN to inspect the query plan. A full table scan can be reasonable for a small table, but on a large table it may indicate a missing or unsuitable index. Also select only needed columns, filter early, use correct data types, avoid unnecessary functions on indexed columns, and inspect expensive joins.

Views, stored programs, and automation

A view is a saved query that behaves like a virtual table:

CREATE VIEW customer_order_totals AS
SELECT c.customer_id, c.name,
       COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
LEFT JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY c.customer_id, c.name;

Stored procedures package reusable operations and can accept parameters. Stored functions return a value and can be used in expressions, but should remain simple and predictable. Triggers run automatically when specified table events occur; they are useful for tightly controlled auditing, but hidden side effects can make applications difficult to maintain. Events schedule database tasks. Keep business logic in one clear layer and document stored programs, triggers, and events.

Transactions and concurrency

A transaction groups operations into one logical unit. ACID means atomicity, consistency, isolation, and durability. InnoDB is the typical MySQL storage engine for transactional applications.

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

-- Use this instead of COMMIT if a step fails:
ROLLBACK;

Isolation controls how concurrent transactions observe one another. Locks protect rows during conflicting updates. Keep transactions short, update rows in a consistent order, and handle deadlocks by retrying safely. Never allow a bank-transfer-style operation to commit only one balance update.

Users, permissions, and security

CREATE USER 'store_app'@'localhost' IDENTIFIED BY 'strong-secret';
GRANT SELECT, INSERT, UPDATE, DELETE
ON store_practice.* TO 'store_app'@'localhost';
REVOKE DELETE ON store_practice.* FROM 'store_app'@'localhost';

MySQL accounts include a username and host restriction. Check account grants when authentication succeeds but authorization fails. Use separate application and administrative accounts, grant only required privileges, protect credentials in a secret store or environment configuration, and never commit passwords to source control.

Application code must use prepared statements or parameterized queries. Do not concatenate user input into SQL. Parameterization prevents SQL injection by keeping data separate from SQL syntax.

Backup, restore, and data import

A backup is useful only if it can be restored. Test restoration into a separate database, record backup dates and retention rules, protect dump files because they may contain sensitive data, and define recovery objectives for acceptable data loss and downtime.

Administration and maintenance

Inspect databases with SHOW DATABASES, tables with SHOW TABLES, definitions with SHOW CREATE TABLE, and server behavior with status and configuration inspection commands. Storage engines determine features such as transactions and indexing; use InnoDB for normal transactional workloads.

A character set defines how characters are encoded. A collation defines comparison and sorting rules. Use a consistent Unicode strategy across server, database, tables, columns, connections, and clients to avoid corrupted text or unexpected sorting. Monitor error logs, resource use, connections, and slow-query logs. Routine maintenance includes reviewing unused indexes, checking growth, testing backups, applying updates, and investigating recurring errors.

Application integration

A server-side application typically configures a host, port, database name, username, password, character set, and connection timeout. Use a connection pool or carefully managed connections, close resources, report useful diagnostics without exposing secrets, and separate database access from presentation logic.

Application CRUD should use prepared statements, explicit transactions for related changes, validation before writes, and consistent error handling. A repository or data-access layer can keep SQL separate from HTTP handlers, templates, or other presentation code.

Troubleshooting checklist

• Cannot connect: confirm the server is running, then check host, port, credentials, firewall access, and host restrictions.
• Access denied: confirm the exact 'user'@'host' account, inspect grants, and avoid solving the problem with unnecessary global privileges.
• Foreign key failure: verify that the parent row exists, the column types match, referenced keys are indexed, and both tables use a compatible engine.
• Duplicate query rows: review join predicates and relationship cardinality. Duplicates may represent legitimate child combinations.
• Too many rows changed: run the same condition as a SELECT first, use key-based filters, and use a transaction for risky work.
• Wrong characters or sorting: inspect character sets and collations at the connection, database, table, and column levels.
• Slow query: run EXPLAIN, identify full scans and expensive joins, review indexes, and avoid unnecessary columns.
• Missing data after a change: check the selected database, connection, transaction commit status, and WHERE clause. Use a tested backup when recovery is required.

Practical projects and exam notes

1. Build the store database and write CRUD queries for customers and products.
2. Produce a sales report by joining orders, customers, products, and order items; calculate totals, group by month or category, filter with HAVING, then sort and limit.
3. Model student enrollment with a many-to-many junction table and query students enrolled in a selected course.
4. Use EXPLAIN to compare a product search before and after adding an appropriate index.
5. Implement a two-account transfer with START TRANSACTION, COMMIT, and ROLLBACK.
6. Create a restricted application user and test allowed and denied statements.
7. Dump a practice database, restore it under a new name, and verify tables, row counts, and sample records.