Hello World

目标

通过 MariaDB CLI 和 Python 驱动完成数据库创建、CRUD 操作，体验 MariaDB 与 MySQL 的命令兼容性。

💡 MariaDB 的客户端命令、SQL 语法与 MySQL 完全相同，所有 MySQL 工具（mysql、mysqldump 等）均适用于 MariaDB。

环境准备

mysql -u root -p
# 或 Docker：
docker exec -it mariadb-dev mysql -u root -proot123

第一步：CLI 操作

-- 创建数据库（MariaDB 独有：支持 OR REPLACE）
CREATE OR REPLACE DATABASE hello_mariadb CHARACTER SET utf8mb4;
USE hello_mariadb;

-- 创建表（体验 MariaDB CHECK 约束）
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2) CHECK(salary > 0),
    hire_date DATE DEFAULT (CURRENT_DATE),
    INDEX idx_dept (department)
);

-- 插入
INSERT INTO employees (name, department, salary) VALUES
    ('张三', '技术部', 15000.00),
    ('李四', '市场部', 12000.00),
    ('王五', '技术部', 18000.00),
    ('赵六', '人事部', 10000.00);

-- 查询
SELECT * FROM employees ORDER BY salary DESC;

-- MariaDB 独有：EXCEPT（差集）
SELECT department FROM employees WHERE department = '技术部';

-- 聚合
SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING cnt >= 1
ORDER BY avg_sal DESC;

-- 窗口函数（MariaDB 10.2+ 支持）
SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

第二步：Python 操作

pip install mariadb  # 推荐使用 MariaDB 官方 Connector

import mariadb
import sys

try:
    conn = mariadb.connect(
        host="localhost",
        user="root",
        password="root123",
        database="hello_mariadb"
    )
except mariadb.Error as e:
    print(f"连接错误: {e}")
    sys.exit(1)

cur = conn.cursor()

# 创建表
cur.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        price DECIMAL(8,2) CHECK(price >= 0),
        category VARCHAR(30),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
""")

# 批量插入
products = [
    ("机械键盘", 299.00, "外设"),
    ("无线鼠标", 89.00, "外设"),
    ("显示器", 1999.00, "显示"),
]
cur.executemany(
    "INSERT INTO products (name, price, category) VALUES (?, ?, ?)",
    products
)
conn.commit()

# 查询 + 参数化
cur.execute(
    "SELECT name, price FROM products WHERE price > ? ORDER BY price DESC",
    (100,)
)
for name, price in cur.fetchall():
    print(f"{name}: ¥{price}")

# 事务
try:
    cur.execute("UPDATE products SET price = price * 0.9 WHERE category = ?", ("外设",))
    cur.execute("SELECT ROW_COUNT()")
    updated = cur.fetchone()[0]
    print(f"打折影响 {updated} 行")
    conn.commit()
except:
    conn.rollback()

cur.close()
conn.close()

第三步：使用 RETURNING 子句（MariaDB 独有）

-- MariaDB 10.5+ 支持 RETURNING
INSERT INTO products (name, price, category)
VALUES ('USB Hub', 49.00, '外设')
RETURNING id, name, created_at;
-- 直接返回新插入行的数据，无需再 SELECT

预期输出

# CLI 窗口函数
name   | department | salary  | dept_rank
-------|------------|---------|-----------
王五   | 技术部     | 18000.00| 1
张三   | 技术部     | 15000.00| 2
李四   | 市场部     | 12000.00| 1
赵六   | 人事部     | 10000.00| 1

# Python
显示器: ¥1999.00
机械键盘: ¥299.00
打折影响 2 行