Databases and Tables

Concept

A database (synonym: schema) is a namespace that contains tables, views, stored routines, triggers, and events. On disk, each database is a directory in the data directory (datadir).

A table stores rows. Each InnoDB table is normally stored in its own tablespace file table_name.ibd (file-per-table, the default).

Every table uses a storage engine (default InnoDB), a character set, and a collation.

MySQL 8.0 stores all metadata in a transactional data dictionary (the old .frm files are gone), and DDL statements are atomic: a failed CREATE/ALTER/DROP does not leave half-finished objects.

Why It Matters

Correct database and table definitions determine data integrity, performance, storage size, and the ability to support international text.

Syntax

CREATE DATABASE [IF NOT EXISTS] db_name
    [CHARACTER SET charset] [COLLATE collation];
USE db_name;
SHOW DATABASES;
SHOW CREATE DATABASE db_name;
ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
DROP DATABASE [IF EXISTS] db_name;

CREATE TABLE [IF NOT EXISTS] tbl_name (
    col_name data_type [NOT NULL | NULL] [DEFAULT value] [AUTO_INCREMENT]
             [UNIQUE] [PRIMARY KEY] [COMMENT 'text'],
    ...
    [PRIMARY KEY (cols)],
    [UNIQUE KEY name (cols)],
    [KEY name (cols)],
    [CONSTRAINT name FOREIGN KEY (cols) REFERENCES parent (cols)],
    [CONSTRAINT name CHECK (expr)]
) [ENGINE = InnoDB] [DEFAULT CHARSET = utf8mb4] [COLLATE = utf8mb4_0900_ai_ci] [COMMENT = 'text'];

Example

4.1 Creating and inspecting a database Identifier rules:

Up to 64 characters.

Letters, digits, _, $ are safe unquoted; anything else (spaces, hyphens, reserved words) needs backticks: ` order-lines , order `.

Case sensitivity of database and table names depends on the operating system (lower_case_table_names): case-sensitive on Linux by default, case-insensitive on Windows and macOS. Always use lowercase names to stay portable. Column names are case-insensitive.

4.2 Creating a table MySQL-specific features shown:

UNSIGNED integers (no negative values, doubled positive range).

AUTO_INCREMENT for generated IDs.

ON UPDATE CURRENT_TIMESTAMP automatically maintains updated_at.

KEY (synonym INDEX) creates a secondary index inline.

COMMENT documents columns and tables (visible in SHOW CREATE TABLE and information_schema).

4.3 Other ways to create tables 4.4 ALTER TABLE MODIFY and CHANGE replace the whole column definition: if you omit NOT NULL, DEFAULT, or COMMENT, they are lost.

Online DDL. InnoDB can perform many ALTERs without blocking writes:

Only metadata changes Yes (brief metadata lock) Rebuild or index build inside InnoDB Usually yes with LOCK=NONE New table copied row by row No (writes blocked)

Specifying the algorithm makes MySQL raise an error instead of silently choosing a slower, blocking method. For very large production tables, tools like gh-ost or pt-online-schema-change are also used.

4.5 TRUNCATE, DROP, RENAME TRUNCATE fails if another table's foreign key references the table (drop the FK or use DELETE).

4.6 Inspecting metadata with information_schema (table_rows is an estimate for InnoDB; use COUNT(*) for exact counts.)

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

SHOW CREATE DATABASE vehicle_test;
USE vehicle_test;
SELECT DATABASE();

CREATE TABLE vehicles (
    vehicle_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    vin           CHAR(17)     NOT NULL COMMENT 'Vehicle Identification Number',
    model         VARCHAR(40)  NOT NULL,
    model_year    YEAR         NOT NULL,
    market        CHAR(2)      NOT NULL DEFAULT 'IN',
    is_prototype  BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (vehicle_id),
    UNIQUE KEY uq_vehicles_vin (vin),
    KEY ix_vehicles_model_year (model, model_year)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci
  COMMENT = 'Vehicles under test';

-- Copy structure only, INCLUDING indexes and column attributes (not foreign keys, not data)
CREATE TABLE vehicles_archive LIKE vehicles;

-- Copy structure and data from a query (no indexes/keys except what you declare)
CREATE TABLE india_vehicles AS
SELECT vehicle_id, vin, model FROM vehicles WHERE market = 'IN';

-- Temporary table (visible only to this connection; dropped when it ends)
CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY);

ALTER TABLE vehicles ADD COLUMN color VARCHAR(20) NULL AFTER model;      -- AFTER / FIRST control position
ALTER TABLE vehicles ADD COLUMN battery_kwh DECIMAL(5,1) NULL,
                     ADD COLUMN notes TEXT NULL;                         -- several changes in one statement
ALTER TABLE vehicles MODIFY COLUMN color VARCHAR(30) NULL;               -- change definition (restate everything!)
ALTER TABLE vehicles CHANGE COLUMN color paint_color VARCHAR(30) NULL;   -- rename + redefine
ALTER TABLE vehicles RENAME COLUMN paint_color TO body_color;            -- rename only (8.0+)
ALTER TABLE vehicles ALTER COLUMN market SET DEFAULT 'EU';               -- change default only
ALTER TABLE vehicles DROP COLUMN notes;
ALTER TABLE vehicles ADD INDEX ix_vehicles_market (market);
ALTER TABLE vehicles DROP INDEX ix_vehicles_market;
ALTER TABLE vehicles RENAME TO test_vehicles;                             -- or: RENAME TABLE a TO b;
ALTER TABLE test_vehicles COMMENT = 'Vehicles in the test fleet';
ALTER TABLE test_vehicles ENGINE = InnoDB;                                -- rebuild / convert engine

ALTER TABLE test_vehicles ADD COLUMN trim_level VARCHAR(20) NULL, ALGORITHM = INSTANT;   -- metadata-only (8.0.12+; any position 8.0.29+)
ALTER TABLE test_vehicles ADD INDEX ix_tv_year (model_year), ALGORITHM = INPLACE, LOCK = NONE;
ALTER TABLE test_vehicles MODIFY COLUMN vin CHAR(20) NOT NULL, ALGORITHM = COPY;          -- full rebuild

TRUNCATE TABLE india_vehicles;          -- remove all rows fast, reset AUTO_INCREMENT (implicit commit, not undoable)
DROP TABLE IF EXISTS india_vehicles;    -- remove table
DROP TEMPORARY TABLE IF EXISTS tmp_ids;
RENAME TABLE test_vehicles TO vehicles, vehicles_archive TO vehicles_old;   -- atomic multi-rename
DROP DATABASE IF EXISTS scratch_db;     -- removes ALL objects in the database

SELECT table_name, engine, table_rows, data_length, index_length, create_time
FROM information_schema.tables
WHERE table_schema = 'eh_company'
ORDER BY table_name;

SELECT column_name, column_type, is_nullable, column_default, column_key, extra
FROM information_schema.columns
WHERE table_schema = 'eh_company' AND table_name = 'orders'
ORDER BY ordinal_position;

Expected Result

(MUL = the column is the first column of a non-unique index.)

SELECT column_name, column_type, is_nullable, column_key
FROM information_schema.columns
WHERE table_schema = 'eh_company' AND table_name = 'orders'
ORDER BY ordinal_position;

+--------------+---------------+-------------+------------+
| COLUMN_NAME  | COLUMN_TYPE   | IS_NULLABLE | COLUMN_KEY |
+--------------+---------------+-------------+------------+
| order_id     | int unsigned  | NO          | PRI        |
| customer_id  | int unsigned  | NO          | MUL        |
| order_date   | date          | NO          |            |
| status       | varchar(20)   | NO          |            |
| total_amount | decimal(12,2) | NO          |            |
+--------------+---------------+-------------+------------+

Common Mistakes

Using MODIFY and losing NOT NULL/DEFAULT.

Mixed-case table names that work on Windows but fail on Linux.

Reserved words as names (order, group, key, desc) — must be backticked everywhere.

Assuming DDL can be rolled back: every DDL statement implicitly commits the current transaction.

Running a blocking ALTER on a large table during business hours.

Forgetting ENGINE = InnoDB on very old servers (5.1 and earlier defaulted to MyISAM).

Best Practices

Lowercase snake_case names; plural or singular consistently.

Always define a primary key (InnoDB clusters data by it).

Specify charset/collation at database level (utf8mb4).

Request ALGORITHM=INSTANT/INPLACE, LOCK=NONE explicitly for production DDL.

Use COMMENT to document units (speed_kmh, 'km/h').

Interview Relevance

Medium: "Difference between MODIFY and CHANGE?", "CREATE TABLE LIKE vs CREATE TABLE AS SELECT?", "What is online DDL / ALGORITHM=INSTANT?", "Can you roll back DDL in MySQL?" (no — it causes an implicit commit).

Practice Questions

Create a table ecus with an auto-increment ID, a vehicle_id, ecu_code (up to 10 characters), serial_no (unique), and created_at defaulting to now.

Add a column supplier VARCHAR(40) after ecu_code using ALGORITHM=INSTANT.

Rename serial_no to serial_number without changing its definition.

Show the size of every table in eh_company using information_schema.tables.

Explain why ALTER TABLE t MODIFY c VARCHAR(50); might make a NOT NULL column nullable.