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

MySQL Course: SQL Fundamentals and Database Management

Learn MySQL installation, command-line access, databases, users, tables, SQL queries, functions, aggregation, updates, deletes, and UNION operations.

What You Will Learn

This course introduces MySQL and SQL from the ground up. You will install the server and client, connect from a terminal, create a course enrollment database, manage users and privileges, define tables, insert records, query and modify data, and produce useful reports.

No previous database knowledge is required. Basic terminal use is helpful. For broader course navigation, see the MySQL course and its practice activity.

Introduction to MySQL

MySQL is a relational database management system (RDBMS). It stores structured information in tables and uses SQL, the Structured Query Language, to define, retrieve, and change that information.

Websites and applications commonly use MySQL for accounts, products, orders, course enrollments, messages, and reporting. An application sends SQL statements to the MySQL server; the server checks permissions, processes the statement, and returns results or a status message.

A database contains related objects, especially tables. A table contains rows (records), and each row has values in named columns (fields). A column has a data type and may have constraints such as NOT NULL or PRIMARY KEY. A database's logical structure is its schema.

Concept diagram: MySQL server → database → table → columns and rows. For example, the students table may have id, first_name, and email columns; one row represents one student.

The mysql command-line client is a text interface. You type a statement, terminate it with a semicolon, and press Enter. The client sends the complete statement to the server and prints the result.

Core Database Vocabulary

Database: an organized collection of related tables and other objects.
Table: a structured collection of records arranged in columns and rows.
Row or record: one stored item, such as one student.
Column or field: a named attribute shared by all rows in a table.
Schema: the tables, columns, data types, indexes, and constraints that define a database's structure.
Data type: the kind of value a column can store, such as an integer, text, or date.
Query: a SQL statement that requests or changes data.
Primary key: a column or set of columns that uniquely identifies each row.
User account: a MySQL identity, including a user name and connection host.
Privilege: permission to perform an action on a database object, such as SELECT or INSERT.
NULL: a marker for an absent or unknown value. It is not zero and is not an empty string.
Constraint: a rule that restricts values or enforces uniqueness and relationships.

Installing MySQL

Windows

Install the MySQL server and the client tools using the official MySQL Installer or an approved package provided by your organization. Select the server and command-line client components. During configuration, choose a suitable server configuration, enable the MySQL service, and set a strong password for the administrative root account. Record the password securely; do not place it in scripts or source code.

After installation, open a new Command Prompt or PowerShell window. If mysql is not recognized, add the directory containing mysql.exe to PATH, or run the executable using its full path. Reopen the terminal after changing PATH.

Linux

Use your distribution's package manager. Package names vary, but a Debian-based system commonly uses:

sudo apt update
sudo apt install mysql-server mysql-client
sudo systemctl enable --now mysql
sudo systemctl status mysql

On a Red Hat-based system, the package and service names may differ. Use the distribution's MySQL or compatible server package, then enable and start the corresponding service. Complete any initial security setup offered by the package, including configuring administrative authentication and removing unsafe test settings where appropriate.

The server is the background process that stores and processes data. The client is the command-line program that connects to it. You need a running server for database operations, but client tools can be installed separately.

Verify the installation

mysql --version
mysql -u root -p

Enter the password when prompted. A successful login displays a prompt such as mysql>. On Windows, verify the MySQL service in Services or with the installed service-management command. On Linux, use systemctl status. A refused connection usually means the service is stopped or the host or port is wrong.

Accessing MySQL from the Command Line

mysql -u root -p
mysql -u app_user -p -h localhost

-u supplies the user name, -p asks for a password securely, and -h specifies the server host. Avoid putting a password directly in the command because it can be exposed through shell history or process listings.

SHOW DATABASES;
USE course_db;
SHOW TABLES;
DESCRIBE students;
HELP SELECT;
EXIT;

USE selects the database for subsequent unqualified table references. You can also qualify a table as course_db.students. End SQL statements with ;. A statement can span several lines; the client executes it when it receives the terminator. Use \c to cancel an unfinished statement, \G to display a result vertically, and EXIT; or QUIT; to leave the client.

SQL Syntax Fundamentals

Keywords describe SQL operations, such as SELECT, FROM, and WHERE.
Identifiers name objects, such as students or email.
Literals are values written in a statement, such as 42, 'Ada', or '2026-01-15'.
Expressions calculate or compare values, such as price * quantity.

SQL keywords are conventionally written in uppercase and identifiers in lowercase, although MySQL's rules vary by object and operating system. Consistent capitalization improves readability. Use single quotes for string and date literals. MySQL permits backticks for identifiers when needed, for example `order`; avoid reserved words as names when possible.

-- A single-line comment
/* A multi-line comment */
SELECT first_name, email FROM students;

When a syntax error occurs, read the reported location and inspect the preceding clause. Common causes are a missing semicolon, misspelled keyword, incorrect quote, missing comma, or clauses in the wrong order. Simplify the statement and add clauses one at a time.

Create and Manage Databases

CREATE DATABASE course_db;
SHOW DATABASES;
USE course_db;
SHOW CREATE DATABASE course_db;

Use a distinct name for each database. If a test database is no longer needed, removal is permanent:

DROP DATABASE course_db;

Caution: Never run DROP DATABASE on a production database without verified backups, authorization, and a recovery plan.

Create Users and Assign Privileges

MySQL accounts include a host restriction. The following account can authenticate only as course_app from the local host:

CREATE USER 'course_app'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON course_db.*
TO 'course_app'@'localhost';
SHOW GRANTS FOR 'course_app'@'localhost';

Modern MySQL applies a successful GRANT immediately. Do not routinely use FLUSH PRIVILEGES after account statements; it is mainly relevant when system grant tables have been changed directly. Use it only when your server administration procedure requires it.

Follow the principle of least privilege: grant only the actions and database objects an account needs. An application user should normally not have administrative rights such as creating users or dropping databases. Use a separate administrative account for maintenance, and use a stronger host restriction than a broad wildcard when remote access is required.

Create Tables and Choose Data Types

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL,
  enrolled_on DATE,
  status VARCHAR(20) NOT NULL DEFAULT 'active'
);

INT stores whole numbers. Use decimal types such as DECIMAL(10,2) for exact monetary values rather than floating-point types. VARCHAR(n) stores variable-length text, while TEXT suits larger text whose maximum length is less convenient to declare. Use DATE for a calendar date, TIME for a time of day or duration, DATETIME for a date and time you control, and TIMESTAMP when MySQL-managed or time-zone-aware timestamp behavior is useful.

NOT NULL requires a value. Omitting it permits NULL. DEFAULT supplies a value when an insert does not specify that column. Choose a type according to the stored value, expected range, precision, and whether absence has meaning.

SHOW TABLES;
DESCRIBE students;
SHOW CREATE TABLE students;

Insert Records

INSERT INTO students (first_name, last_name, email, enrolled_on)
VALUES ('Ada', 'Lovelace', 'ada@example.test', '2026-01-15');

INSERT INTO students (first_name, last_name, email, enrolled_on)
VALUES
  ('Alan', 'Turing', 'alan@example.test', '2026-01-16'),
  ('Grace', 'Hopper', 'grace@example.test', '2026-01-17');

List target columns explicitly. This protects the statement from column-order changes and makes generated identifiers and defaults clear. The id value is omitted because AUTO_INCREMENT generates it and status uses its default.

Modify Table Structure

ALTER TABLE students ADD COLUMN phone VARCHAR(30);
ALTER TABLE students MODIFY COLUMN email VARCHAR(255) NOT NULL;
ALTER TABLE students RENAME COLUMN phone TO phone_number;
ALTER TABLE students DROP COLUMN phone_number;
ALTER TABLE students ADD INDEX idx_students_email (email);
ALTER TABLE students ADD UNIQUE KEY uq_students_email (email);
ALTER TABLE students RENAME TO learners;
DESCRIBE learners;

Syntax support can vary by MySQL version, especially for renaming columns. Test structural changes on a copy or development database first. Dropping a column destroys its stored values. Indexes can speed searches but add storage and write cost. A unique constraint also rejects duplicate values.

Query Data with SELECT

SELECT * FROM students;
SELECT id, first_name, last_name FROM students;
SELECT id, first_name, email
FROM students
WHERE enrolled_on >= '2026-01-01'
ORDER BY last_name ASC
LIMIT 10;

FROM identifies the source table. WHERE filters rows before the result is returned. Comparison operators include =, <>, <, <=, >, and >=. Use DISTINCT to remove duplicate result values:

SELECT DISTINCT status FROM students;
SELECT id, email FROM students WHERE email IS NULL;

Use IS NULL and IS NOT NULL; email = NULL does not test for missing values. Use \G after a query when wide output is difficult to read.

Filtering, Logic, Sorting, and Pagination

SELECT * FROM students
WHERE status = 'active' AND enrolled_on BETWEEN '2026-01-01' AND '2026-12-31';

SELECT * FROM students
WHERE email LIKE '%@example.test' OR last_name IN ('Turing', 'Hopper');

SELECT * FROM students
WHERE NOT (status = 'inactive' OR email IS NULL)
ORDER BY last_name ASC, first_name ASC
LIMIT 10 OFFSET 20;

IN tests membership in a list.
BETWEEN tests an inclusive range.
LIKE performs pattern matching; % matches any number of characters and _ matches one character.
AND requires both conditions; OR requires either condition; NOT reverses a condition.

AND normally binds more tightly than OR. Add parentheses to express the intended grouping. ORDER BY column ASC sorts ascending, while DESC sorts descending. LIMIT 10 OFFSET 20 skips 20 rows and returns up to 10; equivalently, LIMIT 20, 10 uses MySQL's two-argument form. Always sort when consistent pagination matters.

Update and Delete Data Safely

SELECT id, email FROM students WHERE id = 1;
UPDATE students
SET email = 'ada.lovelace@example.test'
WHERE id = 1;

SELECT id, first_name FROM students WHERE status = 'test';
DELETE FROM students WHERE id = 3;

Preview the exact rows with an equivalent SELECT before changing them. A missing or overly broad WHERE can update or delete every row. Prefer a primary-key or other unique condition, inspect the affected-row count, and use a transaction workflow where appropriate.

Primary Keys

A primary key reliably identifies one row. It must be unique and cannot be NULL. A single-column key such as id INT AUTO_INCREMENT PRIMARY KEY is common. A composite primary key uses several columns together:

CREATE TABLE enrollments (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrolled_on DATE NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

AUTO_INCREMENT generates a new numeric identifier, but it does not replace meaningful business rules such as a unique email constraint. Primary keys are also the normal basis for reliably targeting updates and deletes. Relationships and foreign keys are natural next topics after this foundation.

Functions and Aliases

A function accepts values and returns a calculated value. An alias gives an expression or table a temporary name, improving readability.

SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       UPPER(status) AS status_label,
       CHAR_LENGTH(email) AS email_length
FROM students;

SELECT s.first_name, s.last_name
FROM students AS s;

Table aliases such as s shorten qualified references. Functions in a filter or expression can make an index less useful because MySQL may need to calculate the function for many rows; compare this with filtering directly on the stored column when possible.

String Functions

CONCAT(first_name, ' ', last_name) combines text.
UPPER(status) and LOWER(email) change letter case.
CHAR_LENGTH(email) counts characters.
SUBSTRING(email, 1, 5) extracts part of a string.
TRIM(email) removes surrounding whitespace.
REPLACE(email, '@old.test', '@new.test') substitutes text.

SELECT TRIM(LOWER(email)) AS normalized_email
FROM students
WHERE LOWER(last_name) LIKE 'h%';

Date and Time Functions

SELECT CURRENT_DATE() AS today, CURRENT_TIMESTAMP() AS current_time;
SELECT YEAR(enrolled_on) AS enrollment_year,
       DATE_FORMAT(enrolled_on, '%Y-%m-%d') AS readable_date
FROM students;
SELECT DATE_ADD(enrolled_on, INTERVAL 30 DAY) AS follow_up_date
FROM students;
SELECT * FROM students
WHERE enrolled_on >= '2026-01-01' AND enrolled_on < '2027-01-01';

Use half-open ranges such as greater than or equal to the start and less than the next boundary when filtering date-time values. DATE stores only a date, TIME a time or duration, DATETIME a date-time value, and TIMESTAMP a timestamp with MySQL-specific time-zone and automatic-update behavior. Choose deliberately and verify the session and application time zones.

Aggregate Functions and Grouped Data

SELECT COUNT(*) AS student_count FROM students;
SELECT COUNT(email) AS known_emails FROM students;
SELECT AVG(id) AS average_id, MIN(id) AS first_id, MAX(id) AS last_id
FROM students;
SELECT status, COUNT(*) AS number_of_students
FROM students
GROUP BY status
HAVING COUNT(*) > 1;

COUNT, SUM, AVG, MIN, and MAX summarize rows. COUNT(*) counts rows; COUNT(column) ignores NULL. Most aggregate calculations also ignore NULL. GROUP BY creates groups, and HAVING filters those groups after aggregation.

Query flow: FROM supplies rows, WHERE filters rows, GROUP BY forms groups, aggregate functions calculate values, HAVING filters groups, and ORDER BY/LIMIT arrange and cap the final output.

WHERE versus HAVING

WHERE: filters individual rows before grouping; use it for conditions such as status = 'active'.
HAVING: filters grouped results; use it for conditions such as COUNT(*) > 1.

Combine SELECT Results with UNION

SELECT email AS contact_email FROM students
UNION
SELECT email AS contact_email FROM instructors
ORDER BY contact_email;

SELECT email AS contact_email FROM students
UNION ALL
SELECT email AS contact_email FROM instructors
ORDER BY contact_email
LIMIT 20;

Each SELECT in a union must return the same number of columns in the same order, with compatible corresponding types. Column names come from the first SELECT. UNION removes duplicate rows; UNION ALL retains them and is usually faster when duplicates are meaningful. Put the final ORDER BY and LIMIT after the combined query.

Core SQL Statement Reference

CREATE DATABASE: creates a database; destructive risk is low unless names collide.
CREATE USER and GRANT: create accounts and permissions; incorrect grants create security risk.
CREATE TABLE: defines columns, types, and constraints.
INSERT: adds rows.
SELECT: reads rows and normally does not change data.
ALTER TABLE: changes structure and may affect stored data or availability.
UPDATE: changes existing rows; always review its WHERE.
DELETE: removes rows; always review its WHERE.
DROP DATABASE, DROP TABLE, and DROP COLUMN: remove objects or data permanently.

Troubleshooting

“mysql is not recognized”: install the client tools, verify the executable location, add it to PATH, and reopen the terminal.
Connection refused: check that the server service is running, then verify host, port, firewall, and server networking settings.
Access denied: verify the password, user name, and host portion of the account. An account for localhost is not automatically the same as one for another host.
No database selected: run USE course_db; or qualify the table as course_db.students.
Syntax error: check the terminator, commas, clause order, spelling, and single quotes around string values.
Too many rows changed: stop, restore from a backup if necessary, and use a precise preview query and primary-key condition.
Unexpected NULL results: use IS NULL, add parentheses around mixed logic, and inspect the stored type and value.
UNION failure: make both queries return the same number of compatible columns.

Knowledge Assessment and Practice

Install MySQL, verify the service, log in, create course_db, and select it.
Create students and courses tables with primary keys, appropriate types, defaults, and required columns.
Insert at least three students and three courses using explicit column lists.
Create course_app with access limited to course_db.*, then inspect its grants.
Write a query that finds active students whose last names begin with a chosen letter, sorts them, and returns a page of results.
Preview and safely correct one email address, then remove one test row using its primary key.
Report counts by status and use HAVING to show only groups above a chosen size.
Produce a readable full name and formatted enrollment date using aliases and functions.
Combine two compatible email lists with both UNION and UNION ALL, and explain the difference in duplicate handling.

For each exercise, explain the result, identify whether a clause filters rows or groups, and correct at least one deliberate error such as email = NULL, a missing semicolon, or a union with mismatched column counts.

Next Steps

After mastering these fundamentals, study relational design and normalization, foreign keys and joins, indexes and query performance, transactions and ACID properties, backups and restore procedures, security administration, application connectivity, ORMs, and migrations.