1. Why Use a Relational Database?

---

A relational database provides durable, structured storage and offers simple, declarative statements to insert, update, delete, and retrieve data. MySQL is popular because it is free, performs well, and integrates smoothly with Java and many other runtimes.

2. Core Terminology

---

• Data – symbols that describe facts; meaning comes from context (metadata).
• Database (DB) – an organized, shared collection of data stored on disk.
• DBMS – software that sits between the operating system and the user to manage the database.
• DBS – the entire ecosystem: hardware, software, data, and the people who maintain it.

3. SQL Dialects in MySQL

---

Category	Purpose	Common Statements
DDL	Define structures	CREATE, ALTER, DROP
DML	Manipulate rows	INSERT, UPDATE, DELETE, SELECT
DQL	Query rows	SELECT
DCL	Control access	GRANT, REVOKE

4. Working with Databases

---

-- list all databases
SHOW DATABASES;

-- create a new database with UTF-8
CREATE DATABASE IF NOT EXISTS bookstore CHARACTER SET utf8mb4;

-- switch context
USE bookstore;

-- inspect definition
SHOW CREATE DATABASE bookstore;

-- change character set
ALTER DATABASE bookstore CHARACTER SET gbk;

-- remove
DROP DATABASE IF EXISTS bookstore;

5. Working with Tables

---

5.1 Common Data Types

Type	Usage
INT	whole numbers
DOUBLE	floating point
VARCHAR(50)	variable-length strings
DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS

5.2 Creating a Table

CREATE TABLE IF NOT EXISTS book (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    title       VARCHAR(100) NOT NULL,
    author      VARCHAR(50),
    price       DECIMAL(8,2),
    stock       INT DEFAULT 0
);

5.3 Inspecting and Changing Structure

-- structure
DESC book;

-- full definition
SHOW CREATE TABLE book\G

-- rename
ALTER TABLE book RENAME TO books;

-- change column type
ALTER TABLE books MODIFY price DECIMAL(10,2);

-- rename column
ALTER TABLE books CHANGE price list_price DECIMAL(10,2);

-- add column
ALTER TABLE books ADD published DATE AFTER author;

-- drop column
ALTER TABLE books DROP published;

-- remove table
DROP TABLE IF EXISTS books;

6. Data Manipulation (DML)

---

6.1 Inserting Rows

-- all columns
INSERT INTO books VALUES (NULL, 'Clean Code', 'Robert C. Martin', 42.50, 10);

-- explicit columns
INSERT INTO books (title, author, price) VALUES
('Refactoring', 'Martin Fowler', 45.00),
('Design Patterns', 'Gang of Four', 54.99);

6.2 Updating Rows

-- single row
UPDATE books SET stock = stock - 1 WHERE id = 1;

-- multiple rows
UPDATE books SET price = price * 0.9 WHERE stock > 20;

6.3 Deleting Rows

-- conditional
DELETE FROM books WHERE stock = 0;

-- wipe table (keeps auto-increment)
DELETE FROM books;

-- wipe table (resets auto-increment)
TRUNCATE books;

7. Querying Data (DQL)

---

7.1 Basic Syntax

SELECT [ALL|DISTINCT] select_list
FROM   table_name
[WHERE condition]
[GROUP BY col_list [HAVING condition]]
[ORDER BY col_list [ASC|DESC]]
[LIMIT [offset,] row_count];

7.2 Filtering

-- comparison
SELECT * FROM books WHERE price > 30;

-- range
SELECT * FROM books WHERE price BETWEEN 20 AND 40;

-- set membership
SELECT * FROM books WHERE author IN ('Martin Fowler', 'Robert C. Martin');

-- pattern
SELECT * FROM books WHERE title LIKE '%Design%';

-- null check
SELECT * FROM books WHERE published IS NULL;

7.3 Sorting and Paging

-- multi-level sort
SELECT * FROM books ORDER BY author ASC, price DESC;

-- pagination
SELECT * FROM books ORDER BY id LIMIT 10 OFFSET 20;

7.4 Aggregation

SELECT
    author,
    COUNT(*)            AS total_books,
    AVG(price)          AS avg_price,
    MAX(price)          AS max_price
FROM books
GROUP BY author
HAVING avg_price > 40;

8. Constraints

---

• PRIMARY KEY – unique, non-null identifier.
• FOREIGN KEY – enforces referential integrity between tables.
• UNIQUE – disallows duplicate values (NULLs allowed).
• NOT NULL – forbids NULL values.
• DEFAULT – supplies a value when none is provided.
• AUTO_INCREMENT – generates sequential integers for a primary key.

CREATE TABLE publisher (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE book (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    title       VARCHAR(100) NOT NULL,
    publisher_id INT,
    CONSTRAINT fk_book_publisher
        FOREIGN KEY (publisher_id) REFERENCES publisher(id)
);

9. Multi-Table Queries

---

9.1 Inner Join

SELECT b.id, b.title, p.name AS publisher
FROM book b
JOIN publisher p ON b.publisher_id = p.id;

9.2 Left & Right Joins

-- all books, even those without a publisher
SELECT b.id, b.title, p.name AS publisher
FROM book b
LEFT JOIN publisher p ON b.publisher_id = p.id;

-- all publishers, even those without books
SELECT p.id, p.name, b.title
FROM publisher p
RIGHT JOIN book b ON p.id = b.publisher_id;

10. Subqueries

---

-- books from the same publisher as 'Clean Code'
SELECT *
FROM book
WHERE publisher_id = (
    SELECT publisher_id FROM book WHERE title = 'Clean Code'
);

-- authors whose average book price exceeds the global average
SELECT author
FROM books
GROUP BY author
HAVING AVG(price) > (SELECT AVG(price) FROM books);

11. Transactions

---

A transaction is a logical unit of work that must either complete entirely or not at all. MySQL satisfies ACID properties:

• Atomicity – all-or-nothing execution.
• Consistency – database remains in a valid state.
• Isolation – concurrent transactions do not interfere.
• Durability – committed changes survive crashes.

START TRANSACTION;

UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

COMMIT;  -- or ROLLBACK;

12. Modeling & Design Worklfow

---

1. Gather requirements.
2. Draw an E-R diagram (entities, attributes, relationships).
3. Translate the E-R model into relational tables:
   • Entity → table
   • Attribute → column
   • 1:1 → foreign key in either table
   • 1:N → foreign key on the "many" side
   • M:N → junction tible with two foreign keys
4. Add constraints and indexes.