Master MySQL: Essential Commands, Indexes, Stored Procedures & Triggers

MySQL Basic Operations

MySQL operations can be divided into two major groups: common CRUD statements (CREATE, READ, UPDATE, DELETE) and advanced features such as stored procedures, triggers, and transaction handling.

1. Common SQL Statements

Database‑level commands: SHOW DATABASES, USE db_name, SHOW STATUS, SHOW GRANTS, SHOW ERRORS, SHOW WARNINGS, SHOW CREATE DATABASE db_name, SHOW CREATE TABLE tbl_name, SHOW TABLES, SHOW COLUMNS FROM tbl_name (or DESCRIBE tbl_name).

Table‑level commands: CREATE DATABASE db_name, CREATE TABLE tbl_name ( ... ) ENGINE=INNODB with column definitions, NULL / NOT NULL, DEFAULT, PRIMARY KEY, and ENGINE options (InnoDB, MyISAM, MEMORY).

Foreign key definition using FOREIGN KEY (col) REFERENCES other_tbl(col).

Dropping objects: DROP DATABASE db_name, DROP TABLE tbl_name.

Altering tables: ALTER TABLE tbl_name ADD column_def, ALTER TABLE tbl_name RENAME TO new_name.

2. Data Querying

Basic SELECT: SELECT col1, col2 FROM tbl WHERE condition.

Distinct rows: SELECT DISTINCT col FROM tbl.

Limiting rows: SELECT col FROM tbl LIMIT 5 (zero‑based offset).

Sorting: ORDER BY col DESC.

Set operators: IN (value1, value2), NOT IN, LIKE '%pattern%', BETWEEN a AND b.

Grouping: GROUP BY col HAVING aggregate_condition with notes on nested GROUP BY, NULL handling, and column requirements.

Subqueries: SELECT col FROM tbl WHERE col IN (SELECT col FROM other_tbl).

Join types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN with examples.

Union queries: UNION (removes duplicates) and UNION ALL (keeps all rows).

Functions: string concatenation ( CONCAT()), arithmetic expressions, text functions ( UPPER(), LTRIM(), RTRIM()), date functions ( DATE(), DAY()), numeric functions ( ABS(), COS()), and aggregate functions ( AVG(), COUNT(), MAX(), MIN(), SUM()).

3. Data Modification

Insert rows: INSERT INTO tbl (col1, col2) VALUES ('val1','val2') (multiple rows separated by commas). Omitting column list is allowed only when omitted columns accept NULL or have DEFAULT values.

Update rows: UPDATE tbl SET col='new' WHERE id=1001. Warning: omitting WHERE updates every row.

Delete rows: DELETE FROM tbl WHERE id=10086. Warning: without WHERE all rows are removed; the table structure remains.

Truncate table (faster bulk delete): TRUNCATE tbl.

4. SQL Execution Order

MySQL processes keywords in a fixed sequence, generating intermediate virtual tables at each step. The typical order is:

(1) FROM
(2) ON
(3) JOIN
(4) WHERE
(5) GROUP BY
(6) WITH CUBE|ROLLUP
(7) HAVING
(8) SELECT
(9) DISTINCT
(10) ORDER BY
(11) LIMIT

Understanding this order helps predict how complex queries are evaluated.

5. Index Management

Indexes accelerate data retrieval. Types include single‑column and composite indexes. Creation syntax:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX idx_name ON tbl_name (col1, col2) USING BTREE;

Key points: UNIQUE, FULLTEXT, SPATIAL specify index type; default is a regular index. INDEX_TYPE can be BTREE (default for InnoDB/MyISAM) or HASH (available for MEMORY tables).

Prefix indexing on CHAR/VARCHAR columns: CREATE INDEX idx_name ON tbl(col(6)); Altering an existing table to add an index:

ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX idx_name (col1, col2) USING BTREE;

Dropping an index: ALTER TABLE tbl_name DROP INDEX idx_name; Viewing indexes:

SHOW INDEX FROM tbl_name [FROM db_name];

6. Stored Procedures

A stored procedure groups one or more SQL statements for reuse and encapsulation.

Creation syntax (example that calculates an order total with optional tax):

DELIMITER //
CREATE PROCEDURE ordertotal(
    IN custid INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
) COMMENT 'obtain total order price'
BEGIN
    DECLARE total DECIMAL(8,2);
    DECLARE taxrate INT DEFAULT 6;
    SELECT SUM(item_price*item_quantity) INTO total
    FROM orders
    WHERE cust_id = custid;
    IF taxable THEN
        SELECT total + (total/100*taxrate) INTO total;
    END IF;
    SELECT total INTO ototal;
END //
DELIMITER ;

Parameter directions: IN: input value. OUT: output value (accessed via a user variable prefixed with @). INOUT: both input and output.

Execute a procedure:

CALL ordertotal(1, TRUE, @total);
SELECT @total;

Delete a procedure:

DROP PROCEDURE ordertotal;

7. Transaction Control

Transactions guarantee atomicity: either all statements succeed or none are applied.

START TRANSACTION;
INSERT INTO customers (cust_name, item_price, item_quantity) VALUES ('1',5,18);
SAVEPOINT sp1;
INSERT INTO customers (cust_name, item_price, item_quantity) VALUES ('2',5,18);
ROLLBACK TO sp1;  -- only the second insert is undone
COMMIT;            -- makes the first insert permanent

Key keywords: START TRANSACTION begins a transaction block. SAVEPOINT defines a rollback point. ROLLBACK TO reverts to a savepoint. COMMIT permanently writes changes.

Implicit commits occur outside a transaction block.

8. Triggers

Triggers automatically run predefined SQL when INSERT, UPDATE, or DELETE occurs on a table.

Common elements when creating a trigger:

Unique trigger name.

Associated table.

Timing: BEFORE or AFTER.

Event: INSERT, UPDATE, or DELETE.

Example – INSERT trigger that captures the new row’s primary key:

DELIMITER //
CREATE TRIGGER after_insert_customers AFTER INSERT ON customers
FOR EACH ROW
BEGIN
    SELECT NEW.cust_id INTO @new_insert_id;
END //
DELIMITER ;

Example – DELETE trigger that stores deleted row values:

DELIMITER //
CREATE TRIGGER before_delete_customers BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
    SELECT OLD.cust_id INTO @deleted_id;
    SELECT OLD.cust_name INTO @deleted_name;
END //
DELIMITER ;

Example – BEFORE UPDATE trigger that modifies the incoming value:

DELIMITER //
CREATE TRIGGER before_update_customers BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
    SELECT NEW.cust_name INTO @before_update;
    SET NEW.cust_name = 'reset_name';
    SELECT OLD.cust_name INTO @after_update;
END //
DELIMITER ;

Delete a trigger: DROP TRIGGER after_insert_customers; These examples illustrate how triggers can enforce business rules, audit changes, or maintain derived data.