SQL Guide Chapter

Lesson 11B – Introduction to Data Definition Language in MySQL (or: building the skeleton your data hangs on)

And look, you can write the world’s fanciest queries, but if your tables are mis‑shapen nightmares—with missing columns, wrong types, and no constraints—your database is basically a very expensive junk drawer. MySQL’s D...

oracle 19c mysql notes study chapter

And look, you can write the world’s fanciest queries, but if your tables are mis‑shapen nightmares—with missing columns, wrong types, and no constraints—your database is basically a very expensive junk drawer. MySQL’s DDL is where you decide what the data is allowed to look like before people start shoving values into it.

This lesson is about defining and changing schema structure in MySQL.

You will learn to:

1. Databases, Tables, and Naming Rules

In MySQL you typically work with:

1.1 Creating a database

General syntax:

CREATE DATABASE dbname;
    
    -- or
    CREATE DATABASE IF NOT EXISTS dbname;
    
    -- equivalent
    CREATE SCHEMA dbname;

Switch to the database:

USE dbname;

1.2 Naming rules (databases, tables, columns)

Allowed characters:

Constraints:

Example:

CREATE DATABASE my database;         -- ERROR (space)
    CREATE DATABASE `my database`;       -- works, but you’ll regret it later

Backticks are MySQL’s “fine, but you’re responsible for this” feature.

2. Data Types in MySQL

MySQL supports several families of data types.

2.1 Numeric types

  age INT UNSIGNED;
  price DECIMAL(8,2);   -- e.g., 999,999.99 max

Use DECIMAL for anything involving money; floating‑point is for scientific/numeric approximations, not payroll.

2.2 Date and time types

Example:

hire_date DATE,
    last_login TIMESTAMP;

2.3 String types

Examples:

code   CHAR(5),
    name   VARCHAR(50),
    notes  TEXT,
    photo  BLOB;

2.4 Other types

In day‑to‑day tables you’ll mostly combine INT, DECIMAL, VARCHAR, DATE/DATETIME, and the occasional TEXT or BLOB.

3. CREATE TABLE – Building the Structure

General pattern:

CREATE TABLE employees (
      employee_id   INT          NOT NULL,
      last_name     VARCHAR(25)  NOT NULL,
      first_name    VARCHAR(25),
      hire_date     DATE         NOT NULL,
      salary        DECIMAL(8,2),
      department_id INT,
    
      PRIMARY KEY (employee_id)
    );

Key points:

3.1 Column options

Common column attributes:

Example:

CREATE TABLE departments (
      department_id   INT          NOT NULL AUTO_INCREMENT,
      department_name VARCHAR(30)  NOT NULL,
      manager_id      INT,
      location_id     INT,
    
      PRIMARY KEY (department_id)
    );

MySQL will auto‑increment department_id for each new row if you omit a value.

3.2 Creating a table with existing data (CTAS)

You can create a table and populate it from a SELECT in one go:

CREATE TABLE dept80 AS
    SELECT employee_id,
           last_name,
           salary * 12 AS annsal,
           hire_date
    FROM   employees
    WHERE  department_id = 80;

Notes:

4. Keys, Indexes, and Constraints

4.1 Indexes and keys

In MySQL, keys are usually just indexes with special semantics:

Indexes:

4.2 Primary keys

Define a primary key at table creation:

CREATE TABLE jobs (
      job_id   INT         NOT NULL,
      job_title VARCHAR(25) NOT NULL,
    
      PRIMARY KEY (job_id)
    );

Rules:

4.3 Unique keys

Require values to be distinct, but allow NULLs:

CREATE TABLE employees6 (
      employee_id INT         NOT NULL,
      email       VARCHAR(50) NOT NULL,
    
      PRIMARY KEY (employee_id),
      UNIQUE KEY emp6_email_uk (email)
    );

4.4 Foreign keys

Maintain relationships between parent and child tables.

Example – classic departments/employees relationship:

CREATE TABLE departments (
      department_id   INT         NOT NULL,
      department_name VARCHAR(30) NOT NULL,
      PRIMARY KEY (department_id)
    );
    
    CREATE TABLE employees6 (
      employee_id   INT         NOT NULL,
      last_name     VARCHAR(25) NOT NULL,
      department_id INT,
    
      PRIMARY KEY (employee_id),
      CONSTRAINT emp6_dept_fk
        FOREIGN KEY (department_id)
        REFERENCES departments (department_id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
    );

You can also define the FK in a later ALTER TABLE:

ALTER TABLE employees6
    ADD CONSTRAINT emp6_manager_fk
    FOREIGN KEY (manager_id)
    REFERENCES employees6 (employee_id);

Referential actions for ON DELETE / ON UPDATE:

Useful for keeping relationships valid; dangerous if you forget you added CASCADE.

4.5 Secondary indexes

Non‑constraint indexes for performance:

CREATE INDEX idx_emp6_lastname
    ON employees6 (last_name);

You can also create indexes via ALTER TABLE as part of constraint definitions.

5. SHOW CREATE TABLE – Reverse‑Engineering a Table

SHOW CREATE TABLE reveals the full DDL MySQL would use to recreate a table.

SHOW CREATE TABLE employees6\G

You’ll see:

This is extremely useful when you inherit a schema and want to see how it was built without reading someone else’s migration scripts.

6. ALTER TABLE – Changing Existing Structure

Use ALTER TABLE to modify a table’s definition.

6.1 ADD columns

ALTER TABLE dept80
    ADD COLUMN job_id VARCHAR(9) NOT NULL DEFAULT 'ST_CLERK'
    AFTER last_name;

6.2 MODIFY columns

ALTER TABLE dept80
    MODIFY COLUMN last_name VARCHAR(30) NOT NULL FIRST;

6.3 DROP columns

ALTER TABLE dept80
    DROP COLUMN job_id;

6.4 Adding indexes or constraints

You can attach indexes/constraints after creation:

ALTER TABLE employees6
    ADD CONSTRAINT emp6_manager_fk
    FOREIGN KEY (manager_id)
    REFERENCES employees6 (employee_id);

Or create a separate index:

CREATE INDEX idx_emp6_job
    ON employees6 (job_id);

7. DROP TABLE – Removing Tables

DROP TABLE removes a table and its data.

DROP TABLE dept80;
    
    -- or safer in scripts:
    DROP TABLE IF EXISTS dept80;

8. Putting It Together – Example: Adding JOB_TITLE to JOBS

Back to Ben’s complaint: the JOBS table is missing JOB_TITLE.

Assuming a simple existing JOBS table, you could add the column with:

ALTER TABLE jobs
    ADD COLUMN job_title VARCHAR(25) NOT NULL;

Then populate it with appropriate titles via UPDATE statements.

9. What You Should Now Be Able to Do

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

You’ve now graduated from “I use whatever tables exist” to “I design the tables”—which is both a promotion and an exciting new way to break production if you’re not careful.

← previous view source markdown next →
// sql-guide generated from markdown notes