MySQL Data Definition and Data Manipulation

Introduction

A database is useful only when its structure accurately represents the business domain and its data can be created, retrieved, modified, and removed reliably. In MySQL, these responsibilities are divided broadly into Data Definition Language (DDL) and Data Manipulation Language (DML). DDL defines database objects such as databases, tables, indexes, views, and constraints. DML operates on the rows stored in those objects through INSERT, SELECT, UPDATE, and DELETE statements.

Good SQL is more than syntax. A professional developer must select appropriate data types, enforce valid relationships, protect data integrity, understand transaction behavior, and design queries that remain maintainable and efficient as the application grows. This chapter develops those skills using MySQL 8.x syntax.

Learning Objectives

By the end of this chapter, you should be able to:

• create, inspect, alter, and remove MySQL databases and tables;
• choose suitable numeric, character, date, time, and JSON data types;
• define primary keys, foreign keys, unique constraints, checks, and defaults;
• create and evaluate indexes and views;
• perform reliable CRUD operations;
• use filtering, operators, expressions, functions, grouping, and joins;
• apply transactions and validation rules to protect consistency;
• diagnose common design and implementation problems.

1. The Relational Foundation

MySQL is a relational database management system. Data is organized into tables, each of which consists of columns and rows. A column describes an attribute, such as email or price; a row represents one occurrence of an entity, such as a customer or product.

A schema is the logical design of a database. It identifies entities, attributes, relationships, constraints, and access paths. In MySQL, the terms database and schema are commonly used almost interchangeably, although “schema” can also mean the complete logical structure.

A well-designed table normally has:

1. a stable primary key;
2. columns with precise data types;
3. constraints that express business rules;
4. minimal unnecessary duplication;
5. indexes supporting important access patterns.

Keys and Relationships

A primary key uniquely identifies each row and cannot contain NULL. A candidate key is any column or combination of columns capable of uniquely identifying a row. A foreign key stores a value that refers to a candidate key, usually the primary key, in another table.

For example, one customer may place many orders. The relationship is represented by orders.customer_id referencing customers.customer_id. The foreign key prevents an order from referring to a nonexistent customer, unless the constraint is deliberately disabled or the column permits NULL for an optional relationship.

2. Data Definition Language

Creating and Selecting a Database

CREATE DATABASE shop_db;
USE shop_db;

A safer development script may use:

CREATE DATABASE IF NOT EXISTS shop_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

utf8mb4 supports the full Unicode range and is preferable to older utf8 configurations that may not support every Unicode character. Character-set and collation choices affect storage, comparison, sorting, and case sensitivity.

Useful inspection statements include:

SHOW DATABASES;
SELECT DATABASE();
SHOW TABLES;

A database can be removed with:

DROP DATABASE shop_db;

DROP DATABASE is destructive. Production systems should require authorization, backups, and a tested recovery procedure before such an operation.

Choosing Data Types

The data type communicates what values are valid and how they are stored. It also affects comparison behavior, indexing, and storage efficiency.

DECIMAL(p,s) stores exact numbers with precision. Here, p is the total number of digits and s is the number of digits after the decimal point. Thus, DECIMAL(10,2) permits up to eight digits before and two digits after the decimal point, subject to the sign and MySQL range rules. It is generally suitable for currency, whereas binary floating-point types can introduce representation approximations.

CHAR(n) is useful for fixed-length values such as country codes. VARCHAR(n) is usually preferable for variable-length names and emails. TEXT is intended for larger text and has different indexing and default-value considerations.

DATETIME represents a date and time without automatic time-zone conversion. TIMESTAMP is often used for system event times and has time-zone-related conversion behavior. The choice should be documented, especially in distributed applications.

Creating Tables

CREATE TABLE customers (
    customer_id INT UNSIGNED AUTO_INCREMENT,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(30),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (customer_id),
    CONSTRAINT uq_customers_email UNIQUE (email)
) ENGINE = InnoDB;

Important elements include:

• AUTO_INCREMENT: generates a new numeric value, but does not guarantee that values are gap-free;
• NOT NULL: requires a value;
• DEFAULT: supplies a value when one is not explicitly provided;
• PRIMARY KEY: establishes row identity;
• UNIQUE: prevents duplicate values;
• ENGINE = InnoDB: supports transactions and foreign keys.

A related table can be defined as follows:

CREATE TABLE orders (
    order_id BIGINT UNSIGNED AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'NEW',
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id),
    CONSTRAINT chk_orders_total
        CHECK (total_amount >= 0),
    CONSTRAINT chk_orders_status
        CHECK (status IN ('NEW', 'PAID', 'SHIPPED', 'CANCELLED'))
) ENGINE = InnoDB;

A CHECK constraint expresses a row-level validity rule. It is not a substitute for application authorization or complex business workflows, but it provides an important database-level safeguard.

Altering Tables

Requirements change. A table can be modified with ALTER TABLE:

ALTER TABLE customers ADD COLUMN marketing_opt_in BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE customers MODIFY COLUMN phone VARCHAR(40);

ALTER TABLE customers ADD CONSTRAINT uq_customers_phone UNIQUE (phone);

Renaming and dropping columns may break application code and dependent views. Schema migrations should therefore be version-controlled, tested against realistic data, and designed with deployment order in mind. A constraint should not be added until existing rows satisfy it, or the migration will fail.

Indexes

An index is an additional data structure that helps MySQL locate rows without scanning the entire table. A primary key automatically creates an index, and a unique constraint creates a unique index.

CREATE INDEX idx_orders_customer_date
    ON orders(customer_id, order_date);

The order of columns matters. A composite index on (customer_id, order_date) is useful for searches beginning with customer_id, such as retrieving one customer's orders by date. It is not generally equivalent to an index on order_date alone.

Indexes improve reads but impose storage and write costs. Excessive or redundant indexes slow INSERT, UPDATE, and DELETE operations. Use EXPLAIN to inspect a query plan:

EXPLAIN
SELECT order_id, order_date
FROM orders
WHERE customer_id = 12
ORDER BY order_date DESC;

An index is not automatically beneficial. A column with very few distinct values may provide limited filtering benefit, and expressions or implicit type conversions can prevent efficient index use.

Views

A view is a named query that presents data as a virtual table:

CREATE VIEW customer_order_summary AS
SELECT c.customer_id,
       c.full_name,
       COUNT(o.order_id) AS order_count,
       COALESCE(SUM(o.total_amount), 0.00) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name;

Users can query the view:

SELECT *
FROM customer_order_summary
WHERE total_spent > 1000;

Views can simplify reporting, hide unnecessary columns, and provide a stable interface. They do not automatically solve performance problems; a complex view may still execute a complex underlying query. Some views are not updatable, particularly those containing grouping, aggregation, or set operations.

3. Data Manipulation Language

Inserting Rows

Explicitly naming columns makes an insert safer and more readable:

INSERT INTO customers (full_name, email, phone)
VALUES ('Amina Rahman', '[email protected]', '+44 7000 111111');

Multiple rows can be inserted in one statement:

INSERT INTO customers (full_name, email)
VALUES
    ('Luis Chen', '[email protected]'),
    ('Sara Patel', '[email protected]');

If the email is unique, a duplicate insert fails. MySQL also supports conflict-handling patterns such as INSERT ... ON DUPLICATE KEY UPDATE, but these should be used deliberately because they can update an existing row rather than report an error.

Selecting Rows

The basic form is:

SELECT customer_id, full_name, email
FROM customers
WHERE email LIKE '%@example.com'
ORDER BY full_name ASC
LIMIT 20;

A query is evaluated conceptually through stages: rows are obtained from FROM, filtered by WHERE, grouped by GROUP BY, filtered after aggregation by HAVING, ordered by ORDER BY, and restricted by LIMIT. This conceptual order differs from the written order.

Common operators include:

• comparison: =, <>, <, >, <=, >=;
• logical: AND, OR, NOT;
• ranges: BETWEEN ... AND ...;
• membership: IN (...);
• pattern matching: LIKE;
• missing values: IS NULL and IS NOT NULL.

NULL means unknown or absent; it is not equal to zero or an empty string. Therefore, WHERE phone = NULL is incorrect. Use WHERE phone IS NULL.

Functions and Expressions

MySQL functions transform or summarize values:

SELECT
    UPPER(full_name) AS display_name,
    LOWER(email) AS normalized_email,
    CHAR_LENGTH(full_name) AS name_length
FROM customers;

Date functions include CURRENT_DATE, CURRENT_TIMESTAMP, DATE(), and DATEDIFF(). Numeric functions include ROUND(), CEILING(), and FLOOR(). Conditional logic can be expressed with CASE:

SELECT order_id,
       CASE
           WHEN total_amount >= 1000 THEN 'HIGH'
           WHEN total_amount >= 250 THEN 'MEDIUM'
           ELSE 'LOW'
       END AS order_band
FROM orders;

Aggregate functions operate over sets of rows. COUNT(*) counts rows, while COUNT(column) excludes rows in which that column is NULL. SUM, AVG, MIN, and MAX also require careful interpretation when missing values are present.

Joins and Aggregation

An inner join returns matching rows from both tables:

SELECT o.order_id, c.full_name, o.order_date, o.total_amount
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id;

A left join preserves every row from the left table, even if no matching row exists on the right:

SELECT c.customer_id, c.full_name, COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name;

The condition COUNT(o.order_id) is appropriate here because unmatched customers have a NULL order ID and should receive a count of zero. COUNT(*) would count the preserved left-side row and could incorrectly produce one.

Updating Rows

Always test the corresponding SELECT before executing an update:

SELECT order_id, status
FROM orders
WHERE status = 'NEW' AND order_date < '2025-01-01';

Then perform the intended change:

UPDATE orders
SET status = 'CANCELLED'
WHERE status = 'NEW' AND order_date < '2025-01-01';

An UPDATE without a WHERE clause affects every row. This may be intentional, but it must never be accidental. Updates should normally use parameterized statements in application code rather than string concatenation.

Deleting Rows

DELETE FROM orders
WHERE order_id = 105;

Foreign keys determine whether a parent row can be deleted. The default restrictive behavior prevents deletion when dependent child rows exist. Alternatives such as ON DELETE CASCADE can be suitable for dependent data, but they must be chosen carefully because one deletion may remove many rows.

4. Transactions and Reliable CRUD

A transaction groups operations into one logical unit. InnoDB provides transaction support and follows the ACID properties:

• Atomicity: all operations succeed or none take effect;
• Consistency: constraints remain satisfied;
• Isolation: concurrent transactions are controlled according to the isolation level;
• Durability: committed changes survive ordinary system failure.

Example:

START TRANSACTION;

INSERT INTO orders (customer_id, total_amount)
VALUES (12, 450.00);

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 7 AND quantity >= 1;

-- Application checks that exactly one inventory row was changed.
COMMIT;

If the inventory update does not confirm available stock, the application should issue ROLLBACK rather than commit an order that cannot be fulfilled. Transactions should be short enough to reduce locking and contention. They should not remain open while waiting for user input or remote services.

Prepared statements are essential for security and reliability:

SELECT customer_id, full_name
FROM customers
WHERE email = ?;

The application supplies the parameter separately. This reduces SQL injection risk and permits the database driver to handle quoting and type conversion correctly.

5. Validation, Security, and Governance

Validation should occur at multiple layers:

1. User-interface validation gives immediate feedback.
2. Application validation implements contextual rules and authorization.
3. Database constraints protect integrity even when several applications write to the database.

A database constraint cannot determine whether a user is authorized to approve a refund, but it can ensure that a refund amount is not negative. Sensitive information should be minimized, encrypted where appropriate, access-controlled, and retained only as long as justified by law and business purpose.

Common security practices include least-privilege accounts, separate development and production credentials, encrypted connections, audit logging, regular backups, tested restoration, and parameterized queries. Personally identifiable information should not be exposed through unrestricted views or unnecessary application logs.

6. Applied Case: Campus Equipment Rental

Background

An educational institution rents laptops and cameras to students. The initial application stored all rental information in one spreadsheet, producing duplicate student names, inconsistent equipment descriptions, and unclear return status.

Problem Analysis

The system requires students, equipment items, and rentals. One student can make many rentals, while one physical equipment item should not be simultaneously rented to two students. These are separate entities and should be represented by separate tables.

Approach

A suitable design includes:

CREATE TABLE equipment (
    equipment_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_tag VARCHAR(30) NOT NULL UNIQUE,
    description VARCHAR(120) NOT NULL,
    available BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE = InnoDB;

CREATE TABLE rentals (
    rental_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id INT UNSIGNED NOT NULL,
    equipment_id INT UNSIGNED NOT NULL,
    rented_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    returned_at DATETIME NULL,
    CONSTRAINT fk_rentals_student
        FOREIGN KEY (student_id) REFERENCES customers(customer_id),
    CONSTRAINT fk_rentals_equipment
        FOREIGN KEY (equipment_id) REFERENCES equipment(equipment_id),
    CONSTRAINT chk_return_time
        CHECK (returned_at IS NULL OR returned_at >= rented_at)
) ENGINE = InnoDB;

The application starts a transaction, verifies that the equipment is available, inserts the rental, and changes available to false. A return transaction records returned_at and makes the item available again. The workflow should also use appropriate locking or an atomic conditional update to prevent two users from renting the same item concurrently.

Outcome and Learning Points

The redesigned system eliminates repeated equipment descriptions, validates student references, records rental history, and supports reliable reporting. The central lesson is that CRUD statements are most dependable when supported by normalized entities, explicit constraints, transactions, and carefully selected indexes.

7. Challenges and Contemporary Practice

Database work involves trade-offs. Strong constraints improve correctness but can complicate imports and migrations. Normalization reduces update anomalies, while carefully chosen denormalization may improve read performance in reporting systems. Indexes accelerate retrieval but consume resources. Views simplify interfaces but can obscure query complexity.

Modern systems may combine relational data with JSON columns, full-text search, caches, or analytical platforms. JSON is valuable for genuinely variable attributes, but core fields used for joins, validation, or frequent filtering generally belong in typed relational columns. AI-assisted coding tools can generate SQL, but generated queries must be reviewed for injection risk, incorrect joins, missing filters, privacy issues, and performance. Human accountability remains essential.

Best-Practice Checklist

• Use utf8mb4 for modern Unicode data.
• Give every important table a stable primary key.
• Use DECIMAL for exact monetary values.
• Define foreign keys and meaningful constraint names.
• Treat NULL explicitly.
• Name columns in INSERT statements.
• Test UPDATE and DELETE predicates with SELECT first.
• Use parameterized queries.
• Use transactions for multi-step business operations.
• Inspect performance with EXPLAIN.
• Keep indexes purposeful and review them as workloads change.
• Version-control schema migrations and test backups and recovery.

Chapter Summary

MySQL DDL creates and changes the structures that hold data, while DML performs reliable CRUD operations on those structures. Effective table design depends on precise data types, primary and foreign keys, uniqueness rules, defaults, checks, and appropriate handling of NULL. Indexes provide access paths but impose write and storage costs; views provide reusable interfaces over queries but do not automatically improve performance.

Reliable database programming combines correct SQL with transactions, prepared statements, validation, least privilege, and tested operational procedures. Joins, grouping, functions, and conditional expressions support useful information retrieval, while constraints preserve integrity even when application code fails. Professional developers must balance normalization, performance, security, maintainability, and evolving business requirements.

Key Terms

Database: A managed collection of organized data and metadata.

Schema: The logical design of database objects and their relationships.

DDL: SQL used to define or modify database structures.

DML: SQL used to insert, retrieve, update, and delete data.

Primary key: A non-null value or combination of values uniquely identifying a row.

Foreign key: A column or columns referencing a key in another table.

Constraint: A database rule restricting valid data or relationships.

NULL: A marker representing an unknown, missing, or inapplicable value.

Index: A data structure that can accelerate data retrieval.

Composite index: An index containing more than one column.

View: A named query presented as a virtual table.

Transaction: A logical unit of database work.

Atomicity: The all-or-nothing property of a transaction.

Normalization: Structuring data to reduce redundancy and modification anomalies.

DECIMAL: An exact fixed-point numeric data type.

CRUD: Create, Read, Update, and Delete operations.

Collation: Rules governing character comparison and sorting.

Prepared statement: SQL compiled or parameterized separately from supplied values.

Referential integrity: Consistency between foreign-key values and referenced keys.

Review Questions

Short Answer Questions

1. What is the difference between DDL and DML?
2. Why is DECIMAL usually preferred to FLOAT for currency?
3. How should a query test whether a column contains NULL?
4. What purpose does a foreign key serve?
5. State two costs associated with adding indexes.

Long Answer Questions

1. Explain how primary keys, foreign keys, unique constraints, and check constraints contribute to data integrity.
2. Describe the stages and design considerations involved in creating a new MySQL table.
3. Compare inner joins and left joins, using a customer-and-orders example.
4. Explain ACID transactions and discuss why they are important in multi-step CRUD operations.
5. Discuss the security and governance practices required for a production MySQL database.

Analytical / Application Questions

1. A developer writes an UPDATE statement without a WHERE clause while intending to change one order. Identify the risk and propose a safe testing procedure.
2. A report must list every customer, including customers with no orders, and show their order count. Which join and aggregate expression should be used, and why?
3. Two users attempt to rent the same equipment item at the same time. Design a transaction-based strategy that prevents double rental and preserves accurate availability.

Multiple Choice Questions with Answers

Question 1

Which SQL statement defines a new table?

A. CREATE TABLE

B. INSERT TABLE

C. MAKE TABLE

D. BUILD TABLE

Correct Answer: A. CREATE TABLE

Question 2

Which data type is generally most appropriate for storing currency amounts exactly?

A. FLOAT

B. DOUBLE

C. DECIMAL

D. TEXT

Correct Answer: C. DECIMAL

Question 3

Which condition correctly identifies rows where phone is missing?

A. phone = NULL

B. phone IS NULL

C. phone == NULL

D. phone LIKE NULL

Correct Answer: B. phone IS NULL

Question 4

What is the primary purpose of a foreign key?

A. To sort rows automatically

B. To encrypt a column

C. To enforce a relationship with another table

D. To create duplicate records

Correct Answer: C. To enforce a relationship with another table

Question 5

Which clause filters grouped results after aggregation?

A. WHERE

B. ORDER BY

C. LIMIT

D. HAVING

Correct Answer: D. HAVING

Question 6

What does a left join preserve?

A. Only matching rows from both tables

B. Every row from the left table

C. Every row from the right table only

D. Only rows containing NULL values

Correct Answer: B. Every row from the left table

Question 7

Which command permanently confirms the successful work of a transaction?

A. SAVE

B. APPLY

C. COMMIT

D. CONFIRM

Correct Answer: C. COMMIT

Question 8

Which statement about indexes is correct?

A. They always improve every operation

B. They can speed reads but add storage and write costs

C. They replace primary keys

D. They prevent all invalid data

Correct Answer: B. They can speed reads but add storage and write costs

Question 9

Which practice most directly reduces SQL injection risk?

A. Concatenating user input into SQL

B. Using prepared statements

C. Removing all indexes

D. Using longer table names

Correct Answer: B. Using prepared statements

Question 10

In a customer left join orders query, why is COUNT(o.order_id) useful for customers with no orders?

A. It counts the unmatched left row as an order

B. It excludes NULL order identifiers and returns zero

C. It converts orders into customers

D. It automatically creates an index

Correct Answer: B. It excludes NULL order identifiers and returns zero