MySQL Database Management

Chapter 4: Creating Tables and Data Types

4.1 Creating Tables

Tables are the foundation of MySQL databases, storing structured data.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

4.2 Data Types

Choosing the right data types optimizes storage and performance.

Numeric Types:

• INT, BIGINT, SMALLINT, TINYINT
• DECIMAL, FLOAT, DOUBLE

String Types:

• VARCHAR(n) - Variable length
• CHAR(n) - Fixed length
• TEXT - Large text data

Date/Time Types:

• DATE, TIME, DATETIME
• TIMESTAMP - Auto-updating

4.3 Constraints

Constraints enforce data integrity rules.

Constraint Types:

• PRIMARY KEY - Unique identifier
• FOREIGN KEY - Referential integrity
• UNIQUE - No duplicate values
• NOT NULL - Required field
• CHECK - Custom validation