← Back to Index

MySQL Reference

0. Connection & Basics

0.1 Client Connect

Connecting to MySQL via the mysql CLI.

# Connect to 'mydb' as 'user' on 'localhost'
mysql -h localhost -u user -p mydb

# Execute query and output as XML
mysql -u user -p -X -e "SELECT 1"

TERMINAL OUTPUT

<?xml version="1.0"?>
<resultset statement="SELECT 1">
  <row>
    <field name="1">1</field>
  </row>
</resultset>

0.2 CLI Meta-commands

Internal mysql shell commands.

\h          -- Show help
\u mydb     -- Switch to database 'mydb'
\s          -- Show server status
\c          -- Clear the current input statement
SOURCE f.sql-- Execute an external SQL file
\q          -- Quit mysql

TERMINAL OUTPUT

(Server status or command result)

0.3 Comments

Annotating SQL code.

# Single-line comment (MySQL style)
-- Single-line comment (Standard, requires space)
/* Multi-line
   comment block */
SELECT 1; -- Trailing comment

TERMINAL OUTPUT

+---+
| 1 |
+---+
| 1 |
+---+

1. Data Types

1.1 Numerics

Standard and unsigned integer types.

SELECT 
    127 AS tiny,         -- TINYINT (1 byte)
    2147483647 AS i4,    -- INT (4 bytes)
    CAST(1 AS UNSIGNED), -- Unsigned integer
    3.14 AS d_fixed,     -- DECIMAL(10,2)
    0.5f AS f_float;     -- FLOAT (4 bytes)

TERMINAL OUTPUT

+------+------------+--------------------+---------+---------+
| tiny | i4         | CAST(1 AS UNSIGNED)| d_fixed | f_float |
+------+------------+--------------------+---------+---------+
|  127 | 2147483647 |                  1 |    3.14 |     0.5 |
+------+------------+--------------------+---------+---------+

1.2 Strings & Binaries

Handling text and raw data.

SELECT 
    'fixed' AS c5,          -- CHAR(5)
    'var' AS vc10,          -- VARCHAR(10)
    'long text' AS txt,     -- TEXT (up to 64KB)
    X'DEADBEEF' AS bin;     -- BINARY/VARBINARY/BLOB

TERMINAL OUTPUT

+-------+------+-----------+------------------+
| c5    | vc10 | txt       | bin              |
+-------+------+-----------+------------------+
| fixed | var  | long text | [Binary Data]    |
+-------+------+-----------+------------------+

1.3 Date & Time

Temporal storage.

SELECT 
    CURDATE(),              -- YYYY-MM-DD
    NOW(),                  -- YYYY-MM-DD HH:MM:SS
    UTC_TIMESTAMP(),        -- Current UTC time
    INTERVAL 1 DAY + NOW(); -- Date arithmetic

TERMINAL OUTPUT

+------------+---------------------+---------------------+------------------------+
| CURDATE()  | NOW()               | UTC_TIMESTAMP()     | INTERVAL 1 DAY + NOW() |
+------------+---------------------+---------------------+------------------------+
| 2024-05-16 | 2024-05-16 10:00:00 | 2024-05-16 10:00:00 | 2024-05-17 10:00:00    |
+------------+---------------------+---------------------+------------------------+

1.4 Booleans & Enums

Logic and predefined sets.

SELECT 
    TRUE AS is_ok,          -- Alias for 1 (TINYINT)
    FALSE AS is_not,        -- Alias for 0
    CAST('active' AS CHAR) AS stat; -- ENUM('active', 'inactive')

TERMINAL OUTPUT

+-------+--------+--------+
| is_ok | is_not | stat   |
+-------+--------+--------+
|     1 |      0 | active |
+-------+--------+--------+

2. Basic Querying (DQL)

2.1 SELECT & Aliases

Result set projection.

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    price * 1.05 AS price_with_tax
FROM products;

TERMINAL OUTPUT

+---------------+----------------+
| full_name     | price_with_tax |
+---------------+----------------+
| Alice Smith   |         104.95 |
+---------------+----------------+

2.2 WHERE Filtering

Logical conditions.

SELECT * FROM users
WHERE age BETWEEN 18 AND 30
  AND email LIKE '%@gmail.com'
  AND last_login IS NOT NULL;

TERMINAL OUTPUT

(Filtered rows)

2.3 ORDER BY

Result sorting.

SELECT name, stock FROM inventory
ORDER BY stock DESC, name ASC;

TERMINAL OUTPUT

(Sorted inventory)

2.4 LIMIT / OFFSET

Pagination (MySQL style).

SELECT id FROM logs
ORDER BY id DESC
LIMIT 10 OFFSET 50; -- Skip 50, take 10
-- Shorthand: LIMIT 50, 10

TERMINAL OUTPUT

(10 rows)

3. Joins & Set Operations

3.1 Inner & Outer Joins

Horizontal table merging.

SELECT u.username, p.bio
FROM users u
INNER JOIN profiles p ON u.id = p.user_id; -- Standard inner join

TERMINAL OUTPUT

(Joined user profiles)

3.2 Cross & Self Joins

Cartesian products and recursive relationships.

-- Generate all combinations
SELECT colors.name, sizes.name
FROM colors CROSS JOIN sizes;

TERMINAL OUTPUT

(Matrix of color/size combinations)

3.3 UNION

Set-based row merging.

SELECT email FROM leads
UNION ALL -- Keeps duplicates
SELECT email FROM customers;

TERMINAL OUTPUT

(Combined email list)

3.4 JOIN with Subqueries

Derived table joins.

SELECT t.name, sub.total_sales
FROM teams t
JOIN (
    SELECT team_id, SUM(amount) AS total_sales 
    FROM sales GROUP BY team_id
) AS sub ON t.id = sub.team_id;

TERMINAL OUTPUT

(Teams with their total sales)

4. Aggregation & Grouping

4.1 GROUP BY

Vertical data reduction.

SELECT status, COUNT(*) AS count
FROM orders
GROUP BY status;

TERMINAL OUTPUT

+-----------+-------+
| status    | count |
+-----------+-------+
| pending   |    42 |
| shipped   |   150 |
+-----------+-------+

4.2 HAVING

Group-level filtering.

SELECT user_id, AVG(score)
FROM attempts
GROUP BY user_id
HAVING AVG(score) > 80;

TERMINAL OUTPUT

(Users with high average scores)

4.3 Aggregate Functions

Common reduction operations.

SELECT 
    COUNT(DISTINCT category) AS total_cats,
    GROUP_CONCAT(name SEPARATOR '|') AS name_list -- MySQL specific
FROM products;

TERMINAL OUTPUT

+------------+-------------------+
| total_cats | name_list         |
+------------+-------------------+
|          4 | Tool A|Tool B|C   |
+------------+-------------------+

5. Advanced Querying

5.1 Subqueries (IN, EXISTS)

Nested query logic.

SELECT name FROM products
WHERE id IN (SELECT product_id FROM promo_items);

TERMINAL OUTPUT

(Promotional products)

5.2 Common Table Expressions (WITH)

Readable subqueries (MySQL 8.0+).

WITH high_rev AS (
    SELECT shop_id FROM sales GROUP BY shop_id HAVING SUM(total) > 10000
)
SELECT * FROM shops WHERE id IN (SELECT shop_id FROM high_rev);

TERMINAL OUTPUT

(Successful shops)

5.3 Recursive CTEs

Graph traversal (MySQL 8.0+).

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 5
)
SELECT n FROM seq;

TERMINAL OUTPUT

+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+

6. Window Functions

6.1 OVER & PARTITION BY

Calculation over window (MySQL 8.0+).

SELECT 
    dept, 
    emp_name,
    salary,
    SUM(salary) OVER(PARTITION BY dept) AS dept_total
FROM employees;

TERMINAL OUTPUT

(Employees with their department's total salary)

6.2 Ranking

Sequence generation in windows.

SELECT 
    name, 
    RANK() OVER(ORDER BY score DESC) AS rnk
FROM leaderboard;

TERMINAL OUTPUT

(Leaderboard with ranks)

6.3 Analytics

Positional row access.

SELECT 
    val,
    LAG(val, 1) OVER(ORDER BY id) AS prev_val
FROM metrics;

TERMINAL OUTPUT

(Metrics with previous row's value)

7. Data Modification (DML)

7.1 INSERT

Adding new records.

INSERT INTO settings (key_name, value)
VALUES ('theme', 'dark'), ('lang', 'en'); -- Multi-row insert

TERMINAL OUTPUT

Query OK, 2 rows affected

7.2 UPDATE & DELETE

Data modification.

UPDATE inventory SET qty = qty - 1 WHERE sku = 'ABC';

DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

TERMINAL OUTPUT

Query OK, 1 row affected

7.3 Upsert (ON DUPLICATE KEY)

Insert or update existing.

INSERT INTO counters (id, val) VALUES (1, 1)
ON DUPLICATE KEY UPDATE val = val + 1;

TERMINAL OUTPUT

Query OK, 2 rows affected (1 inserted, 1 updated)

8. Schema Definition (DDL)

8.1 CREATE & ALTER Table

Structure management.

CREATE TABLE nodes (
    id INT AUTO_INCREMENT PRIMARY KEY, -- Auto-increment
    ip VARCHAR(45) NOT NULL,
    metadata JSON                      -- Native JSON support
) ENGINE=InnoDB;

ALTER TABLE nodes ADD COLUMN status TINYINT DEFAULT 1;

TERMINAL OUTPUT

Query OK, 0 rows affected

8.2 Constraints

Integrity rules.

CREATE TABLE refs (
    id INT PRIMARY KEY,
    node_id INT,
    FOREIGN KEY (node_id) REFERENCES nodes(id) ON DELETE CASCADE
);

TERMINAL OUTPUT

Query OK, 0 rows affected

8.3 Indexes & Performance

Search optimization.

CREATE UNIQUE INDEX idx_node_ip ON nodes (ip);
EXPLAIN SELECT * FROM nodes WHERE ip = '127.0.0.1';

TERMINAL OUTPUT

+----+-------------+-------+-------+---------------+...
| id | select_type | table | type  | possible_keys |...
+----+-------------+-------+-------+---------------+...

8.4 Views

Stored queries.

CREATE VIEW active_nodes AS 
SELECT * FROM nodes WHERE status = 1;

TERMINAL OUTPUT

Query OK, 0 rows affected

9. Logic & Administration

9.1 Transactions (ACID)

Atomic blocks.

START TRANSACTION;
UPDATE accounts SET bal = bal - 50 WHERE id = 10;
UPDATE accounts SET bal = bal + 50 WHERE id = 11;
COMMIT;

TERMINAL OUTPUT

Query OK, 0 rows affected

9.2 Triggers & Functions

Server-side logic.

DELIMITER // -- Change delimiter for multi-statement blocks
CREATE TRIGGER before_node_insert
BEFORE INSERT ON nodes
FOR EACH ROW
BEGIN
    SET NEW.ip = IFNULL(NEW.ip, '0.0.0.0');
END //
DELIMITER ;

TERMINAL OUTPUT

Query OK, 0 rows affected

9.3 Explain Plan

Query path analysis.

EXPLAIN FORMAT=JSON SELECT * FROM nodes WHERE id = 1\G

TERMINAL OUTPUT

{
  "query_block": {
    "select_id": 1,
    "cost_info": { "query_cost": "1.00" },
    "table": { "table_name": "nodes", "access_type": "const" }
  }
}

9.4 Roles & Permissions

Access control.

CREATE USER 'app'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON mydb.* TO 'app'@'localhost';
FLUSH PRIVILEGES;

TERMINAL OUTPUT

Query OK, 0 rows affected