CREATE DATABASE and CREATE TABLE

Before You Create Anything

A database design should begin with requirements, not commands. Decide what one row represents, which values are mandatory, what uniquely identifies a row and how this table will relate to others. In this lesson, one row represents one student.

The MySQL account must have the required CREATE privilege. On managed hosting, the control panel may create the database and user for you; in that case, use the database name assigned by the host.

CREATE DATABASE Step by Step

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

USE coding_school;

IF NOT EXISTS avoids an error when the name already exists, but it does not compare schemas. utf8mb4 supports complete Unicode. The chosen collation controls comparison and sorting rules; utf8mb4_0900_ai_ci is available in MySQL 8.x and performs accent-insensitive, case-insensitive comparisons.

To confirm the active database and its definition:

SELECT DATABASE() AS current_database;
SHOW CREATE DATABASE coding_school;

Create the Students Table

CREATE TABLE IF NOT EXISTS students (
  student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  full_name  VARCHAR(80) NOT NULL,
  class_name VARCHAR(10) NOT NULL,
  marks      DECIMAL(5,2) DEFAULT NULL,
  status     VARCHAR(10) NOT NULL DEFAULT 'Active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id),
  CONSTRAINT chk_students_marks
    CHECK (marks IS NULL OR marks BETWEEN 0 AND 100),
  CONSTRAINT chk_students_status
    CHECK (status IN ('Active', 'Inactive'))
) ENGINE = InnoDB;

The statement defines both storage and data-quality rules. AUTO_INCREMENT generates the next numeric identifier. DECIMAL(5,2) stores marks such as 88.50 exactly. A NULL mark means “not recorded”; it is different from zero. Check constraints reject values outside the declared rules in current MySQL versions.

Understand Every Column and Constraint

A constraint belongs in the database when invalid data should never be accepted, regardless of which application sends it. Application validation improves the user experience; database constraints protect the stored truth.

Verify the Structure

SHOW TABLES;
DESCRIBE students;
SHOW CREATE TABLE students;

SHOW TABLES confirms the object exists. DESCRIBE gives a compact list of columns, types, nullability, keys and defaults. SHOW CREATE TABLE is the authoritative reconstruction statement stored by MySQL.

Exact display can vary slightly by MySQL release. The important check is that each type, NULL rule, default and key matches the design.

Common Design Mistakes

• No primary key: duplicate-looking rows become hard to update safely and relationships become unreliable.
• Using VARCHAR for everything: dates and numbers lose appropriate validation, sorting and arithmetic behavior.
• Using FLOAT for exact marks or money: binary floating-point can introduce representation surprises; use DECIMAL for exact fixed-point values.
• Confusing NULL with zero or an empty string: NULL means missing or unknown, not a numeric zero.
• Overusing IF NOT EXISTS: it can hide that an old table has the wrong structure. Inspect the object after creation.
• Choosing names such as order or group: reserved words require quoting and create avoidable friction. Prefer clear names.
• Changing a live schema without a backup: rehearse migrations, protect data and use version-controlled migration scripts.

Practice Task

1. Create a separate database named library_lab using utf8mb4.
2. Create a books table with an auto-increment key, required title, optional price, status default and created timestamp.
3. Add a CHECK rule so price cannot be negative.
4. Use DESCRIBE and SHOW CREATE TABLE to verify every decision.
5. Explain why an ISBN is better stored as text than as an arithmetic number.

Continue with INSERT INTO to add consistent sample rows to the students table.

Quick Summary

• CREATE DATABASE creates a database namespace; USE selects it for the session.
• CREATE TABLE defines columns, types, defaults, keys and constraints.
• Use utf8mb4, a primary key and meaningful data types for new designs.
• Verify with DESCRIBE and SHOW CREATE TABLE.
• Privileges, hosting versions and migration safety matter in real projects.

Official References

• CREATE DATABASE Statement — MySQL 8.4
• CREATE TABLE Statement — MySQL 8.4
• SHOW CREATE TABLE Statement
• The utf8mb4 Character Set
• Introduction to InnoDB

References reviewed on 14 August 2026. Run destructive schema changes only on a backup or practice database.