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
- 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) );
- 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 );
- 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) );
- 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) );
- 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 );
- 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 );
- 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) );
- 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') );
- 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) );
- 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.