1. Creating Tables

In MySQL, the CREATE TABLE statement is used to define the structure of a new table. The basic syntax includes specifying column names, data types, and various constraints or attributes.

CREATE TABLE [IF NOT EXISTS] table_name (
    column1_name data_type [constraints],
    column2_name data_type [constraints],
    ...
    PRIMARY KEY (column_name)
) [ENGINE=engine_type] [DEFAULT CHARSET=charset_name];

Key Column Attributes

• NOT NULL: Ensures that a column cannot have a NULL value.
• AUTO_INCREMENT: Automatically generates a unique identity for new rows, typically used for primary keys.
• DEFAULT: Provides a fallback value if no value is specified during insertion.
• PRIMARY KEY: Uniquely identifies each record in the table.
• UNIQUE: Guarantees that all values in a column (or a combination of columns) are distinct.
• COMMENT: Adds a descriptive note to the column for documentation purposes.

Example Implementation

CREATE TABLE `system_logs` (
    `log_id` INT(11) NOT NULL AUTO_INCREMENT,
    `module_name` VARCHAR(64) NOT NULL COMMENT 'Origin module',
    `severity` TINYINT(1) DEFAULT '0',
    `message` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`log_id`),
    UNIQUE KEY `uk_module_severity` (`module_name`, `severity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. Inserting Records

Data can be added to a table using the INSERT INTO statement. There are two primary methods for populating rows.

Explicit Column Mapping

Specifying the columns ensures that the values are mapped correctly, even if the table schema changes later.

INSERT INTO system_logs (module_name, severity, message) 
VALUES ('AUTH', 1, 'User login successful'),
       ('DB', 2, 'Connection timeout');

Implicit Positioning

If you provide values for every column in the order they were defined, you can omit the column names. Use NULL or DEFAULT for auto-incrementing or defaulted columns.

-- Assuming log_id is the first column and created_at is the last
INSERT INTO system_logs VALUES (NULL, 'API', 0, 'Endpoint called', DEFAULT);

3. Modifying Existing Data

The UPDATE statement allows you to modify existing records. It is critical to use a WHERE clause to avoid updating every row in the table.

-- Updating a specific value based on a condition
UPDATE system_logs SET severity = 3 WHERE log_id = 1;

-- Using functions within an update
UPDATE system_logs SET module_name = REPLACE(module_name, 'API', 'REST_API');

-- Performing arithmetic updates
UPDATE system_logs SET severity = severity + 1;

4. Altering Table Structure

The ALTER TABLE command is used to rename tables, modify columns, or change the table's internal configuration.

5. Data Deletion Strategies

There are three primary ways to remove data or tables, each with different implications for performence and structure.

6. Essential Utility Operations

Common administrative tasks for inspecting and cloning database objects:

• Examine Table Structure: DESCRIBE table_name; or DESC table_name;
• Retrieve Create Statement: SHOW CREATE TABLE table_name;
• Clone Structure Only: CREATE TABLE new_table LIKE old_table;
• Clone Structure and Data: CREATE TABLE new_table AS SELECT * FROM old_table;
• Check Supported Encodings: SHOW CHARACTER SET;
• Create Database with Specific Encoding: CREATE DATABASE app_db DEFAULT CHARACTER SET utf8mb4;