MySQL Table Structure and Data Types

Introduction

In this lab, we will explore the fundamentals of MySQL table structures and data types. Understanding how to create, modify, and manage tables is crucial for anyone working with databases. We'll learn about different data types in MySQL, how to choose the right data type for your needs, and how to perform basic table operations. By the end of this lab, you'll have hands-on experience with creating tables, defining columns with appropriate data types, and modifying table structures.

Objectives

By completing this lab, you will be able to:

• Understand MySQL's core data types and when to use them
• Create tables with appropriate column definitions
• Modify existing table structures
• Remove tables when they're no longer needed
• View and understand table metadata

Understanding MySQL Data Types

In this step, we'll explore the most commonly used MySQL data types. Understanding data types is crucial because choosing the right data type for your columns affects both data integrity and database performance.

Since many SQL commands are involved in this step, we recommend using the web terminal. Click on the "Terminal" tab to open it; it functions just like the desktop terminal.

Let's start by connecting to MySQL:

sudo mysql -u root

Now that we're connected, let's create a new database for our experiments:

CREATE DATABASE store;
USE store;

Let's examine the main categories of MySQL data types:

1. Numeric Types:

   • INT: For whole numbers
   • DECIMAL: For precise decimal numbers
   • FLOAT/DOUBLE: For approximate decimal numbers

2. String Types:

   • VARCHAR: For variable-length strings
   • CHAR: For fixed-length strings
   • TEXT: For long text

3. Date and Time Types:

   • DATE: For dates (YYYY-MM-DD)
   • TIME: For time (HH:MM:SS)
   • DATETIME: For both date and time

Let's create a simple table that demonstrates these different data types:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    weight FLOAT,
    in_stock BOOLEAN,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Let's break down this table structure:

• id: An auto-incrementing integer that serves as the primary key
• name: A variable-length string that cannot be NULL
• price: A precise decimal number with 10 total digits and 2 decimal places
• description: A text field for longer descriptions
• weight: A floating-point number for approximate decimal values
• in_stock: A boolean field (TRUE/FALSE)
• created_at: Automatically stores the creation timestamp
• last_updated: Automatically updates when the record changes

To see the structure of our table:

DESCRIBE products;

You should see output like this:

+--------------+---------------+------+-----+---------------------+-------------------------------+
| Field        | Type          | Null | Key | Default             | Extra                         |
+--------------+---------------+------+-----+---------------------+-------------------------------+
| id           | int(11)       | NO   | PRI | NULL                | auto_increment                |
| name         | varchar(100)  | NO   |     | NULL                |                               |
| price        | decimal(10,2) | NO   |     | NULL                |                               |
| description  | text          | YES  |     | NULL                |                               |
| weight       | float         | YES  |     | NULL                |                               |
| in_stock     | tinyint(1)    | YES  |     | NULL                |                               |
| created_at   | datetime      | YES  |     | current_timestamp() |                               |
| last_updated | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
+--------------+---------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.001 sec)

Creating Tables with Constraints

In this step, we'll learn about table constraints and how they help maintain data integrity. We'll create a more complex table structure that demonstrates various types of constraints.

Let's create two related tables to understand relationships and constraints:

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200),
    active BOOLEAN DEFAULT TRUE
);

CREATE TABLE inventory_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    sku VARCHAR(20) NOT NULL UNIQUE,
    item_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity >= 0),
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Let's examine the constraints we've used:

1. PRIMARY KEY: Ensures unique identification of each record
2. UNIQUE: Prevents duplicate values in a column
3. NOT NULL: Ensures a column cannot contain NULL values
4. CHECK: Validates data before it's inserted
5. FOREIGN KEY: Ensures referential integrity between tables
6. DEFAULT: Provides a default value if none is specified

To see the table structure with constraints:

SHOW CREATE TABLE inventory_items;

This will show the complete CREATE TABLE statement including all constraints:

MariaDB [store]> SHOW CREATE TABLE inventory_items;

<!-- Sample output -->

CREATE TABLE `inventory_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `sku` varchar(20) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL CHECK (`quantity` >= 0),
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `inventory_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

Modifying Table Structure

In this step, we'll learn how to modify existing tables using ALTER TABLE commands. This is a common task when your data requirements change over time.

Let's modify our products table to add some new columns and modify existing ones:

1. Add a new column:

ALTER TABLE products
ADD COLUMN manufacturer VARCHAR(100) AFTER name;

This command adds a new column named manufacturer after the name column.

2. Modify an existing column:

ALTER TABLE products
MODIFY COLUMN description VARCHAR(500) NOT NULL DEFAULT 'No description available';

This command changes the description column to a variable-length string with a maximum length of 500 characters. It also sets a default value of 'No description available' for new rows.

3. Rename a column:

ALTER TABLE products
CHANGE COLUMN weight product_weight DECIMAL(8,2);

This command renames the weight column to product_weight and changes its data type to a decimal number with 8 total digits and 2 decimal places.

4. Drop a column:

ALTER TABLE products
DROP COLUMN in_stock;

This command removes the in_stock column from the table.

Let's add a composite index to improve query performance:

ALTER TABLE products
ADD INDEX idx_name_manufacturer (name, manufacturer);

This command creates a composite index on the name and manufacturer columns.

To see all the changes we've made:

DESCRIBE products;
SHOW INDEX FROM products;

You should see the updated table structure and indexes:

MariaDB [store]> DESCRIBE products;
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| Field          | Type          | Null | Key | Default                  | Extra                         |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| id             | int(11)       | NO   | PRI | NULL                     | auto_increment                |
| name           | varchar(100)  | NO   | MUL | NULL                     |                               |
| manufacturer   | varchar(100)  | YES  |     | NULL                     |                               |
| price          | decimal(10,2) | NO   |     | NULL                     |                               |
| description    | varchar(500)  | NO   |     | No description available |                               |
| product_weight | decimal(8,2)  | YES  |     | NULL                     |                               |
| created_at     | datetime      | YES  |     | current_timestamp()      |                               |
| last_updated   | timestamp     | NO   |     | current_timestamp()      | on update current_timestamp() |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
8 rows in set (0.001 sec)

MariaDB [store]> SHOW INDEX FROM products;
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| products |          0 | PRIMARY               |            1 | id           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            1 | name         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            2 | manufacturer | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.000 sec)

Working with Table Information

In this step, we'll explore various ways to retrieve information about our tables and their structure.

First, let's look at table status information:

SHOW TABLE STATUS FROM store;

This command shows information about each table in the database, including:

• Storage engine
• Row format
• Number of rows
• Average row length
• Data length
• Index length

To see all tables in our database:

SHOW TABLES;

To see detailed information about a specific column:

SHOW FULL COLUMNS FROM products;

This provides additional information about each column, including:

• Column type
• Collation
• Privileges
• Comments

To see all indexes on a table:

SHOW INDEX FROM products;

We can also get information about our tables from the INFORMATION_SCHEMA database:

SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'store';

Dropping Tables and Cleanup

In this final step, we'll learn how to safely remove tables when they're no longer needed. This is an important skill, but it should be used with caution as dropping a table permanently deletes all its data.

Before dropping a table, it's good practice to:

1. Verify you're in the correct database
2. Check if the table exists
3. Ensure you have a backup if needed

Let's start by checking our current database and tables:

SELECT DATABASE();
SHOW TABLES;

To safely drop a table, we can use the IF EXISTS clause:

DROP TABLE IF EXISTS inventory_items;

Note that we need to drop tables with foreign key constraints first. If we tried to drop the categories table first, we would get an error because inventory_items references it.

Now we can drop the remaining tables:

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;

To verify the tables are gone:

SHOW TABLES;

You should see an empty set, indicating all tables have been dropped.

Summary

In this lab, we've covered the essential aspects of working with MySQL tables and data types:

1. Understanding and using different MySQL data types
2. Creating tables with appropriate constraints
3. Modifying table structures using ALTER TABLE
4. Retrieving table metadata and information
5. Safely dropping tables when they're no longer needed

These skills form the foundation for working with MySQL databases effectively.