How to create a MySQL table? 10 Examples from Basic to Advanced.

A MySQL table is a structured data collection within a relational database, organized into rows and columns. It stores related information in a tabular format, enabling efficient querying, manipulation, and retrieval of data using SQL commands.

MySQL Table creation Examples

1. Basic Table with Auto-Increment Primary Key: The employees table maintains employee details, assigning each entry a unique ID automatically incremented upon insertion.

   CREATE TABLE employees (
       id INT AUTO_INCREMENT PRIMARY KEY,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       email VARCHAR(100)
   );

2. Table with Different Data Types: The products table stores product information, encompassing diverse data types like integers, decimals, dates, and a boolean indicating stock availability.

   CREATE TABLE products (
       product_id INT PRIMARY KEY,
       product_name VARCHAR(100),
       price DECIMAL(10, 2),
       release_date DATE,
       in_stock BOOLEAN
   );

3. Table with Unique Constraint: The customers table houses customer records, ensuring each email address is unique across entries.

   CREATE TABLE customers (
       customer_id INT PRIMARY KEY,
       email VARCHAR(100) UNIQUE,
       first_name VARCHAR(50),
       last_name VARCHAR(50)
   );

4. Table with Foreign Key Constraint: The orders table records order details, linked to customers through a foreign key relationship.

   CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       order_date DATE,
       FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   );

5. Table with NOT NULL Constraint: The students table catalogs student information, enforcing non-null values for first and last names.

   CREATE TABLE students (
       student_id INT PRIMARY KEY,
       first_name VARCHAR(50) NOT NULL,
       last_name VARCHAR(50) NOT NULL,
       birth_date DATE
   );

6. Table with DEFAULT Value: The messages table captures messages alongside sender, receiver, content, and a timestamp set to the current time by default.

   CREATE TABLE messages (
       message_id INT PRIMARY KEY,
       sender VARCHAR(50),
       receiver VARCHAR(50),
       content TEXT,
       creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );

7. Table with Composite Primary Key: The orders_details table stores quantities of products within orders, utilizing a composite primary key spanning order and product IDs.

   CREATE TABLE orders_details (
       order_id INT,
       product_id INT,
       quantity INT,
       PRIMARY KEY (order_id, product_id)
   );

8. Table with Enum Column: The colors table catalogues color options using an enumeration (enum) column, accepting predefined values.

   CREATE TABLE colors (
       color_id INT PRIMARY KEY,
       color_name ENUM('Red', 'Green', 'Blue', 'Yellow')
   );

9. Table with CHECK Constraint: The age_groups table defines age ranges with minimum and maximum ages, validating that the minimum is less than or equal to the maximum.

   CREATE TABLE age_groups (
       age_group_id INT PRIMARY KEY,
       age_range VARCHAR(50),
       min_age INT,
       max_age INT,
       CHECK (min_age <= max_age)
   );

10. Table with Auto-Generated UUID as Primary Key: The products_uuid table utilizes auto-generated UUIDs as primary keys to ensure record uniqueness without relying on auto-incrementing integers.

CREATE TABLE products_uuid ( product_uuid BINARY(16) PRIMARY KEY, product_name VARCHAR(100), price DECIMAL(10, 2), release_date DATE );

These examples showcase various MySQL table types designed to address specific data requirements and constraints, contributing to efficient data management and organization.