Common Operational Commands

Database Object Operations

Creating a Database

CREATE DATABASE [IF NOT EXISTS] inventory_db
[DEFAULT CHARACTER SET = 'UTF8MB4'];

• Defines a new database.
• The IF NOT EXISTS clause pervents errors if the database already exists.
• The DEFAULT CHARACTER SET specifies the encoding.

Viewing Databases

SHOW WARNINGS; -- Displays recent warnings.
SHOW DATABASES; -- Lists all existing databases.
SHOW CREATE DATABASE sales_db; -- Shows the creation statement for a specific database.

Modifying Database Encoding

ALTER DATABASE inventory_db
CHARACTER SET = 'LATIN1';

Selecting a Database

USE inventory_db; -- Switches the active database.
SELECT DATABASE(); -- Returns the name of the currently selected database.

Removing a Database

DROP DATABASE [IF EXISTS] archive_db;

Table Object Operations

Overview

• A table is a structured collection of data organized in rows and columns.

Creating a Table

CREATE TABLE IF NOT EXISTS products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    unit_price DECIMAL(10,2),
    in_stock BOOLEAN DEFAULT TRUE
)
ENGINE = InnoDB
CHARSET = UTF8MB4;

Numeric Data Types

• Integer Types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
• Boolean Types: BOOL, BOOLEAN (synonyms for TINYINT(1))

Floating-Point Types

FLOAT(7,2)  -- Approximate numeric type.
DOUBLE(15,4) -- Double-precision floating-point.
DECIMAL(10,2) -- Exact numeric type for financial data.

String and Text Types

CHAR(10)     -- Fixed-length string.
VARCHAR(255) -- Variable-length string.
TEXT         -- Large text data.
ENUM('Active', 'Inactive', 'Pending') -- Enumeration type.
SET('Red', 'Green', 'Blue') -- Set type allowing multiple values.

Date and Time Types

• TIME: Stores a time.
• DATE: Stores a date.
• DATETIME: Stores both date and time.
• TIMESTAMP: Stores a timestamp, often for row versioning.
• YEAR: Stores a year value.

Storage Engines Overview

Storage engines define how tables are stored and managed.

SHOW ENGINES; -- Lists available storage engines.
SHOW VARIABLES LIKE 'default_storage_engine'; -- Shows the default engine.

• InnoDB: Supports transactions, foreign keys, and row-level locking. Data is stored in .ibd files. Offers high reliability but uses more disk space.
• MyISAM: Stores data, indexes, and table structure in separate files (.MYD, .MYI, .frm). Provides fast read operations and compact storage but lacks transaction support.
• MEMORY: Stores table contents in RAM for extremely fast access, with a corresponding disk file for structure.

Character Sets and Collations

Core Concepts

Character sets define symbols and their encodings. Collations are rules for comparing characters within a set, influencing case sensitivity. By default, many collations (e.g., utf8mb4_general_ci) are case-insensitive (_ci). Case-sensitive collations end with _cs, and binary collations with _bin.

Collation names typically start with the character set name (e.g., utf8mb4_bin).

MySQL allows mixing character sets and collations at the server, database, table, and connection levels.

Server-Level Configuration

Set the default server collation in the configuration file (my.cnf or my.ini):

[mysqld]
collation_server = utf8mb4_bin

After restarting MySQL, new databases inherit this setting. Existing databases remain unchanged.

Alternatively, set it at server startup:

mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_bin

Database-Level Configuration

Specify collation when creating a database:

CREATE DATABASE app_data
CHARACTER SET utf8mb4
COLLATE utf8mb4_bin;

If only the character set is specified, its default collation is used.

Table-Level Configuration

Define collation at table creation:

CREATE TABLE users (
    username VARCHAR(50)
) CHARACTER SET latin1 COLLATE latin1_general_cs;

Connection-Level and Query-Level Control

Connection character set variables:

• character_set_client: Character set of statements sent by the client.
• character_set_connection: Character set used for parsing and converting statements.
• character_set_results: Character set for returning results to the client.

Use COLLATE within a query to override default collation for comparisons:

SELECT * FROM products
WHERE product_name COLLATE utf8mb4_bin = 'Widget';

The BINARY operator is a shorthand for the binary collation of the column's character set:

SELECT * FROM products WHERE BINARY product_code = 'ABC123';
-- Equivalent to:
SELECT * FROM products WHERE product_code COLLATE utf8mb4_bin = 'ABC123';

Query Execution Plan Analysis with EXPLAIN

The EXPLAIN command returns a table describing how MySQL executes a query.

EXPLAIN SELECT * FROM orders WHERE customer_id = 100;

Output columns include:

• id: A sequential identifier for each SELECT in the query. Higher values execute first in nested queries.
• select_type: The query type (e.g., SIMPLE, PRIMARY, SUBQUERY, DERIVED).
• table: The table being accessed.
• type: The join/access type, indicating efficiency:
  • ALL: Full table scan (least efficient).
  • index: Full index scan.
  • range: Index scan within a range.
  • ref: Non-unique index lookup.
  • eq_ref: Unique index lookup.
  • const: Single-row lookup via unique/primary key.
• possible_keys: Indexes MySQL considered using.
• key: The index actually used.
• key_len: Length of the used index key.
• ref: Columns or constants compared to the index.
• rows: Estimated number of rows to examine.
• Extra: Additional execution details:
  • Using filesort: An extra sort pass was required.
  • Using temporary: A temporary table was created.
  • Using index: The query used a covering index.
  • Using where: Rows were filtered after retrieval.

Analyzing the EXPLAIN output helps identify optimization opportunities, such as adding missing indexes or restructuring queries.