Interacting with the MySQL Server

MySQL client connections utilize various flags to manage administrative tasks:

• -u: Specifies the database user.
• -p: Prompts for the user password.
• -S: Defines the path to the local Unix socket file.
• -h: Targets a specific remote host IP address.
• -P: Specifies the network port.
• -e: Executes a command in non-interactive mode.

Common Connection Patterns

mysql -uadmin -p -S /var/run/mysqld/mysqld.sock
mysql -uadmin -p -h192.168.1.50 -P3306

Non-Interactive Execution and Restoration

# Run a command without logging into the shell
mysql -uadmin -p'secret' -e "SHOW DATABASES;"

# Restore from a backup file
mysql -uadmin -p < production_backup.sql

Internal Client Commands

• \c: Aborts the current command input.
• \q or exit: Terminates the session.
• \G: Displays result sets vertically for better readability.
• source: Imports and executes SQL commands from a file.

Core SQL Components

SQL (Structured Query Language) operates under the SQL92 standard, categorized into four primary types:

1. DDL (Data Definition Language): Defining structures (CREATE, ALTER, DROP).
2. DCL (Data Control Language): Managing permissions (GRANT, REVOKE).
3. DML (Data Manipulation Language): Modifying data (INSERT, UPDATE, DELETE).
4. DQL (Data Query Language): Fetching data (SELECT).

Structural Organization

Databases are containers for tables, defined by character sets (encoding) and collations (sorting/comparison rules). It is recommended to use utf8mb4 for comprehensive support, including emoji characters. Collations ending in _ci are case-insensitive, whereas _bin is case-sensitvie.

Data Type Selection

• Integers: Use tinyint for small ranges and int for standard numerical storage.
• Strings: char reserves fixed space, while varchar allocates storage dynamically. Use char for consistent, short lengths and varchar for varying, longer content.
• Enums: Useful for limiting a column to a fixed set of predefined strings.
• Temporal: datetime offers a wider date range compared to timestamp.

Implementing DDL

Managing Databases

CREATE DATABASE app_data CHARSET utf8mb4 COLLATE utf8mb4_bin;
SHOW CREATE DATABASE app_data;
DROP DATABASE app_data;

Schema Design Best Practices

• Ensure columns are defined as NOT NULL with appropriate DEFAULT values.
• Use AUTO_INCREMENT for surrogate primary keys.
• Apply COMMENT descriptors for schema documentation.
• Set storage engines explicitly (e.g., InnoDB).

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Unique ID',
    username VARCHAR(100) NOT NULL COMMENT 'Display Name',
    age TINYINT UNSIGNED NOT NULL DEFAULT 18,
    status ENUM('active', 'suspended', 'deleted') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8mb4;

Modifying Tables

-- Add a new column
ALTER TABLE user_profiles ADD phone_number VARCHAR(20) NOT NULL COMMENT 'Contact';

-- Position column after specific field
ALTER TABLE user_profiles ADD email VARCHAR(100) NOT NULL AFTER username;

-- Modify existing column structure
ALTER TABLE user_profiles CHANGE status account_status CHAR(10) NOT NULL;

Data Manipulation Operations

Inserting Records

INSERT INTO user_profiles (username, age) VALUES ('alice', 25), ('bob', 30);

Updating and Deleting Records

Always accompany UPDATE and DELETE statements with a WHERE clause to avoid accidental data loss across entire tables.

-- Conditional update
UPDATE user_profiles SET age = 26 WHERE user_id = 1;

-- Logical deletion (preferred over physical deletion)
ALTER TABLE user_profiles ADD is_removed TINYINT DEFAULT 0;
UPDATE user_profiles SET is_removed = 1 WHERE user_id = 1;