VMware ESXi and vSphere Cluster Management

Introduction to MySQL for Beginners

Learn MySQL fundamentals from the ground up: databases, tables, SQL, keys, relationships, CRUD operations, joins, aggregation, design, and safe database practices.

MySQL is a popular relational database management system (RDBMS). This guide introduces databases, tables, SQL, relationships, and safe everyday database work without assuming previous database experience.

What MySQL Is

MySQL is software that stores and manages structured data. More precisely, it is a database server and an RDBMS: software for managing data organized into related tables.

A database server stores data, organizes it according to table definitions, retrieves matching records, and applies updates while enforcing rules. MySQL is not the same thing as a database. MySQL is the software that manages one or more databases. A database is a named collection of related data and database objects.

Applications such as websites, desktop programs, command-line tools, and administration tools connect to MySQL. They send SQL statements to the server and receive results. The application is a client; MySQL is the database system serving the request.

Relational Database Foundations

A table stores information about one kind of entity, such as books, students, customers, or orders. A table is made of columns and rows.

• A row, also called a record, represents one item. One row in books might represent one book.
• A column, also called a field or attribute, describes one property stored for each row, such as title or price.
• A value is the data in one row-column position, such as 19.99 or Example Title.
• A schema is the structure and organization of database objects, including tables, columns, data types, keys, and constraints. In MySQL, the terms database and schema are commonly used for the same database namespace.

Relational databases connect tables through relationships. For example, a customer can have many orders, while each order belongs to one customer. Keeping customers in a separate table avoids repeating the customer's address in every order and makes corrections more reliable.

Common relationships

• One-to-one: one row in one table relates to one row in another, such as a person and a single profile.
• One-to-many: one parent row relates to multiple child rows, such as one customer and many orders.
• Many-to-many: many rows on each side can relate to many rows on the other side, such as students and courses. A junction table, such as enrollments, stores each pairing.

Connecting to MySQL

Common ways to work with MySQL include the mysql command-line client, graphical administration tools, and application libraries. All of these are clients: they connect to a separately running MySQL server.

A connection normally requires a host, port, username, and password. The host identifies the computer running MySQL. The default MySQL port is commonly 3306. The username and password identify an account. After connecting, you can select a database.

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

The -p option asks for the password instead of placing it directly in the command. A local MySQL installation may include the server and client, but installing a client alone does not start or install a database server.

SQL Basics

SQL is the language used to define database structures, query data, and insert, change, or remove records. A SQL statement commonly ends with a semicolon.

• Keywords are language words such as SELECT, FROM, and WHERE.
• Identifiers are names of databases, tables, and columns, such as books and title.
• String literals are text values written in quotes, such as 'Example Title'.
• Numeric literals are written without quotes, such as 2025 and 19.99.
• Comments document SQL. Use -- comment with a following space or /* comment */.

-- Select the title and author of every book
SELECT title, author
FROM books;

When a statement runs, the client displays either a result set, such as rows returned by SELECT, or a success message describing an insert, update, or schema change.

Creating and Selecting Databases

CREATE DATABASE library_db;
SHOW DATABASES;
USE library_db;

CREATE DATABASE creates a named database. SHOW DATABASES lists databases that your account can see. USE selects the database for subsequent unqualified table commands.

Inspect a database with SHOW TABLES;. Removing a database is destructive because it removes its tables and data:

DROP DATABASE library_db;

Creating Tables and Choosing Data Types

A table definition gives every column a name, data type, and optional constraints. This example creates a personal library table:

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(150) NOT NULL,
    published_year INT,
    price DECIMAL(8,2),
    added_on DATE DEFAULT (CURRENT_DATE)
);

DESCRIBE books;

NULL means missing, unknown, or not applicable. It is not the same as zero, FALSE, or an empty string. Use NOT NULL when a value is required. Use a DEFAULT when the server should supply a value if the column is omitted.

SHOW TABLES;
DESCRIBE books;
SHOW CREATE TABLE books;

Keys, Constraints, and Data Integrity

A constraint is a rule enforced by the database. Constraints prevent invalid or inconsistent data.

Every table generally needs a stable primary key. A primary key may be one column or a combination of columns. A foreign key represents a relationship and supports referential integrity: a child row cannot reference a nonexistent parent when the constraint is enforced.

Adding, Changing, and Removing Data

CRUD describes the four basic data operations: create, read, update, and delete.

INSERT INTO books (title, author, published_year, price)
VALUES ('Example Title', 'Example Author', 2025, 19.99);

INSERT INTO books (title, author, published_year, price)
VALUES
    ('SQL Starter', 'A. Writer', 2024, 15.50),
    ('Data Design', 'B. Author', 2023, 22.00);

UPDATE books
SET price = 17.99
WHERE book_id = 1;

DELETE FROM books
WHERE book_id = 1;

SELECT * FROM books;

INSERT adds rows. UPDATE changes existing rows. DELETE removes rows. Always use a carefully chosen WHERE clause with UPDATE and DELETE; without it, every row may be changed or removed.

Retrieving Data with SELECT

SELECT * FROM books;
SELECT title, author, price FROM books;
SELECT title, price AS list_price
FROM books
WHERE price < 20
ORDER BY price ASC
LIMIT 10;

* selects all columns; naming columns explicitly usually makes queries clearer and avoids retrieving unnecessary data. An alias, created with AS, gives a column or table a temporary readable name.

Filtering rows

SELECT * FROM books WHERE published_year >= 2020;
SELECT * FROM books WHERE price BETWEEN 10 AND 25;
SELECT * FROM books WHERE author IN ('A. Writer', 'B. Author');
SELECT * FROM books WHERE title LIKE 'Data%';
SELECT * FROM books WHERE price IS NULL;
SELECT * FROM books WHERE published_year IS NOT NULL;
SELECT * FROM books
WHERE published_year >= 2020 AND price < 25;
SELECT * FROM books
WHERE author = 'A. Writer' OR author = 'B. Author';

• Comparison operators include =, <>, >, >=, <, and <=.
• AND requires all conditions; OR requires at least one.
• LIKE performs pattern matching. The % wildcard matches any sequence of characters.
• Use IS NULL or IS NOT NULL. Do not use = NULL, because NULL represents an unknown value rather than an ordinary value.

ORDER BY sorts results. Use ASC for ascending order or DESC for descending order. LIMIT restricts how many rows are returned, which is useful for previews and large result sets.

Summarizing and Grouping Data

Aggregate functions calculate a result from multiple rows.

SELECT COUNT(*) AS book_count,
       AVG(price) AS average_price,
       MIN(price) AS lowest_price,
       MAX(price) AS highest_price,
       SUM(price) AS total_price
FROM books;

COUNT counts rows, SUM adds numeric values, AVG calculates an average, and MIN and MAX find boundary values.

SELECT author, COUNT(*) AS book_count
FROM books
GROUP BY author
HAVING COUNT(*) > 1
ORDER BY book_count DESC;

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. For example, use WHERE published_year >= 2020 to remove older rows before counting, and HAVING COUNT(*) > 1 to keep only groups containing more than one row.

Working with Multiple Tables

A JOIN combines rows from related tables. An inner join returns rows where the join condition matches in both tables.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(150) NOT NULL
);

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

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

The composite primary key in enrollments prevents the same student from being enrolled in the same course twice. The table is a junction table for the many-to-many relationship between students and courses.

SELECT p.product_name, oi.quantity
FROM products AS p
LEFT JOIN order_items AS oi
    ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;

This left join retains every product and then identifies products without a matching order-item row. In an outer join, missing related values appear as NULL.

Changing Table Structures

Use ALTER TABLE to change a schema after a table exists.

ALTER TABLE books ADD COLUMN isbn VARCHAR(20);
ALTER TABLE books MODIFY COLUMN title VARCHAR(250) NOT NULL;
ALTER TABLE books DROP COLUMN isbn;
RENAME TABLE books TO library_books;
ALTER TABLE library_books RENAME COLUMN title TO book_title;

Exact rename syntax can depend on the MySQL version, so check the version-specific documentation before using it in production. Schema changes can fail when existing rows do not satisfy a new NOT NULL, uniqueness, or foreign-key rule. They can also lock or rewrite a large table. Inspect and back up important data before changing a live schema.

Database Design Basics

Design begins with the real-world problem. For a small store, identify entities such as customers, products, orders, and order items. Give each entity a table, list its attributes as columns, assign a primary key, and identify relationships with foreign keys.

A flawed order table might repeat a customer's name and address on every order. If the address changes, some rows may be updated while others are not. This is an update anomaly. Repeated data also increases storage and creates insertion and deletion problems.

Normalization is a practical method for reducing unnecessary duplication and improving consistency. Store customer facts in customers, order facts in orders, product facts in products, and the products belonging to each order in order_items. Connect them with keys rather than copying descriptive values into every related row.

Essential SQL Statement Families

Safe and Effective MySQL Usage

• Use descriptive, consistent names such as library_db, book_id, and published_year.
• Prefer explicit column lists in INSERT and SELECT statements.
• Preview rows with SELECT before UPDATE or DELETE.
• Use an account with only the permissions needed for routine work rather than an overly privileged administrative account.
• Protect passwords and avoid exposing credentials in shell history, source code, or shared files.
• Create backups and periodically test that they can be restored.

mysqldump -u username -p library_db > library_db.sql
mysql -u username -p library_db < library_db.sql

A backup is a recoverable copy of database data and, when needed, its structure. A dump should be stored safely and restoration should be tested rather than assumed.

Common Problems and Troubleshooting

Cannot connect to the MySQL server

Confirm that the server service is running. Check the host, port, username, and password. Try a known local connection before diagnosing remote access. The account may also lack permission to connect from the current host.

No database selected

Choose a database with USE database_name;, or qualify a table name such as library_db.books.

Unknown table or column

Check spelling, confirm the selected database, and inspect the structure:

SELECT DATABASE();
SHOW TABLES;
DESCRIBE books;

Duplicate entry

An inserted or updated value conflicts with a primary key or UNIQUE constraint. Inspect the constrained column and decide whether the existing row should be reused or updated. Do not bypass the constraint without understanding the data model.

Foreign-key constraint failure

The child row may reference a parent row that does not exist, or a referenced parent may still have dependent rows. Create the parent first, inspect dependent records, and verify that related key columns have compatible definitions.

Too many rows changed

A missing or overly broad WHERE clause can make an update or deletion affect many rows. Run the intended condition as a SELECT first and verify the affected-row count afterward.

Unexpected NULL results

Use IS NULL rather than = NULL. Also remember that a left join produces NULL values for columns from the right table when no match exists.

Beginner Practice Project

Build a personal library database by creating library_db, selecting it, creating books, and inserting several records. Then practice selecting recent books, sorting by title, changing one price safely, deleting one test row, and verifying every change.

Next, create students, courses, and enrollments. Add primary and foreign keys, insert parent rows before enrollment rows, join students to courses, and count enrollments by course:

SELECT course_id, COUNT(*) AS enrollment_count
FROM enrollments
GROUP BY course_id
HAVING COUNT(*) > 1;

These exercises cover the central CRUD, relationship, join, grouping, and data-integrity skills. Continue with the MySQL introduction ebook as a reference while practicing.

Key Takeaways

• MySQL is an RDBMS and database server; a database is the data collection it manages.
• Tables contain rows, columns, and values, while schemas define their structure.
• SQL is used to define, query, and modify relational data.
• Primary keys identify rows, foreign keys connect tables, and constraints protect integrity.
• WHERE filters rows, GROUP BY forms groups, HAVING filters groups, and joins combine related tables.
• Good design separates entities, reduces duplication, and makes updates consistent.
• Preview destructive changes, limit privileges, and maintain tested backups.