🚀 MariaDB 全面速查手册

1. 📊 数据库与表操作

🗃️ 数据库管理

-- 创建数据库
CREATE DATABASE company 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 显示所有数据库
SHOW DATABASES;

-- 显示数据库创建语句
SHOW CREATE DATABASE company;

-- 选择数据库
USE company;

-- 删除数据库
DROP DATABASE IF EXISTS old_company;

-- 修改数据库字符集
ALTER DATABASE company CHARACTER SET utf8mb4;

📋 表结构管理

-- 创建表（完整示例）
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INT,
    salary DECIMAL(10,2) DEFAULT 0,
    hire_date DATE NOT NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 索引
    INDEX idx_department (department_id),
    INDEX idx_name (first_name, last_name),
    INDEX idx_hire_date (hire_date),
    
    -- 外键约束
    CONSTRAINT fk_department 
        FOREIGN KEY (department_id) 
        REFERENCES departments(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001;

-- 显示所有表
SHOW TABLES;

-- 显示表结构
DESCRIBE employees;
DESC employees;  -- 简写
SHOW COLUMNS FROM employees;

-- 显示表创建语句
SHOW CREATE TABLE employees;

-- 修改表结构
ALTER TABLE employees 
ADD COLUMN phone VARCHAR(20) AFTER email;

ALTER TABLE employees 
MODIFY COLUMN salary DECIMAL(12,2);

ALTER TABLE employees 
DROP COLUMN phone;

ALTER TABLE employees 
RENAME TO staff;

-- 删除表
DROP TABLE IF EXISTS temp_employees;

2. 📝 数据操作语言 (DML)

➕ 数据插入

-- 单条插入
INSERT INTO employees (employee_id, first_name, last_name, email, salary, hire_date)
VALUES ('EMP001', '张', '三', 'zhangsan@company.com', 15000.00, '2023-01-15');

-- 批量插入
INSERT INTO employees (employee_id, first_name, last_name, email, salary, hire_date) VALUES
('EMP002', '李', '四', 'lisi@company.com', 12000.00, '2023-02-20'),
('EMP003', '王', '五', 'wangwu@company.com', 18000.00, '2023-03-10'),
('EMP004', '赵', '六', 'zhaoliu@company.com', 20000.00, '2023-04-05');

-- 插入查询结果
INSERT INTO employee_archive 
SELECT * FROM employees 
WHERE hire_date < '2020-01-01';

-- REPLACE（存在则替换，不存在则插入）
REPLACE INTO employees (id, employee_id, first_name, last_name, email)
VALUES (1, 'EMP001', '张', '三', 'new_email@company.com');

🔍 数据查询

-- 基础查询
SELECT * FROM employees;
SELECT id, first_name, last_name, salary FROM employees;

-- 条件查询
SELECT * FROM employees WHERE salary > 15000;
SELECT * FROM employees WHERE department_id IN (1, 2, 3);
SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-06-30';
SELECT * FROM employees WHERE last_name LIKE '张%';
SELECT * FROM employees WHERE email IS NOT NULL;

-- 排序和限制
SELECT * FROM employees 
ORDER BY salary DESC, last_name ASC 
LIMIT 10;

-- 分页查询
SELECT * FROM employees 
ORDER BY id 
LIMIT 20 OFFSET 0;  -- 第1页

SELECT * FROM employees 
ORDER BY id 
LIMIT 20 OFFSET 20; -- 第2页

-- 分组聚合
SELECT 
    department_id,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary,
    SUM(salary) as total_salary
FROM employees 
WHERE status = 'active'
GROUP BY department_id
HAVING avg_salary > 10000
ORDER BY avg_salary DESC;

-- 去重查询
SELECT DISTINCT department_id FROM employees;

✏️ 数据更新

-- 单表更新
UPDATE employees 
SET salary = salary * 1.1, 
    updated_at = CURRENT_TIMESTAMP
WHERE department_id = 1;

-- 多表更新
UPDATE employees e
JOIN departments d ON e.department_id = d.id
SET e.salary = e.salary * 1.05
WHERE d.name = '技术部';

-- 基于子查询更新
UPDATE employees 
SET salary = (
    SELECT AVG(salary) FROM employees WHERE department_id = 1
)
WHERE id = 100;

🗑️ 数据删除

-- 条件删除
DELETE FROM employees 
WHERE status = 'inactive' 
AND hire_date < '2020-01-01';

-- 清空表（重置自增ID）
TRUNCATE TABLE employee_archive;

-- 多表删除
DELETE e, s
FROM employees e
LEFT JOIN salaries s ON e.id = s.employee_id
WHERE e.hire_date < '2010-01-01';

3. 🔗 高级查询技巧

🔄 多表连接

-- 内连接
SELECT e.first_name, e.last_name, d.name as department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- 左连接
SELECT e.first_name, e.last_name, p.project_name
FROM employees e
LEFT JOIN projects p ON e.id = p.leader_id;

-- 右连接
SELECT d.name as department_name, COUNT(e.id) as employee_count
FROM departments d
RIGHT JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

-- 全外连接（MariaDB 10.3+）
SELECT e.first_name, d.name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

-- 自连接
SELECT e1.first_name as employee, e2.first_name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

📊 子查询

-- 标量子查询
SELECT first_name, last_name, salary,
    (SELECT AVG(salary) FROM employees) as company_avg_salary
FROM employees;

-- 列子查询
SELECT * FROM employees 
WHERE department_id IN (
    SELECT id FROM departments WHERE location = '北京'
);

-- 行子查询
SELECT * FROM employees 
WHERE (department_id, salary) = (
    SELECT department_id, MAX(salary) 
    FROM employees 
    GROUP BY department_id 
    LIMIT 1
);

-- EXISTS 子查询
SELECT d.name 
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e 
    WHERE e.department_id = d.id AND e.salary > 50000
);

-- 派生表
SELECT dept_stats.department_name, dept_stats.avg_salary
FROM (
    SELECT d.name as department_name, AVG(e.salary) as avg_salary
    FROM departments d
    JOIN employees e ON d.id = e.department_id
    GROUP BY d.id, d.name
) as dept_stats
WHERE dept_stats.avg_salary > 20000;

🎯 窗口函数

-- 排名函数
SELECT 
    first_name,
    last_name,
    salary,
    department_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dense_rank,
    NTILE(4) OVER (ORDER BY salary DESC) as quartile
FROM employees;

-- 聚合窗口函数
SELECT 
    first_name,
    last_name,
    salary,
    department_id,
    AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary,
    SUM(salary) OVER (PARTITION BY department_id) as dept_total_salary,
    salary - AVG(salary) OVER (PARTITION BY department_id) as diff_from_avg
FROM employees;

-- 前后行访问
SELECT 
    first_name,
    hire_date,
    salary,
    LAG(salary) OVER (ORDER BY hire_date) as prev_salary,
    LEAD(salary) OVER (ORDER BY hire_date) as next_salary,
    salary - LAG(salary) OVER (ORDER BY hire_date) as salary_change
FROM employees;

4. ⚡ 索引优化

📑 索引管理

-- 创建索引
CREATE INDEX idx_employee_email ON employees(email);
CREATE UNIQUE INDEX uk_employee_id ON employees(employee_id);
CREATE INDEX idx_name_composite ON employees(first_name, last_name);
CREATE INDEX idx_salary_department ON employees(salary DESC, department_id);
CREATE FULLTEXT INDEX ft_idx_employee_desc ON employees(description);

-- 显示索引
SHOW INDEX FROM employees;

-- 删除索引
DROP INDEX idx_employee_email ON employees;

-- 强制使用索引
SELECT * FROM employees FORCE INDEX (idx_salary_department) 
WHERE salary > 10000;

📈 查询优化

-- 分析查询执行计划
EXPLAIN FORMAT=JSON 
SELECT * FROM employees WHERE department_id = 1;

EXPLAIN ANALYZE
SELECT * FROM employees WHERE first_name LIKE '张%';

-- 优化器提示
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * 
FROM employees 
WHERE salary > 50000;

SELECT /*+ INDEX(employees idx_salary) */ *
FROM employees 
WHERE salary BETWEEN 10000 AND 20000;

5. 🔐 用户与权限管理

👥 用户账户管理

-- 创建用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password123';
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'readonly_pass';

-- 修改用户
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_password';
RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';

-- 删除用户
DROP USER IF EXISTS 'temp_user'@'localhost';

-- 显示用户
SELECT User, Host, authentication_string FROM mysql.user;

🛡️ 权限管理

-- 授予权限
GRANT SELECT, INSERT, UPDATE ON company.* TO 'app_user'@'localhost';
GRANT SELECT ON company.employees TO 'readonly_user'@'%';
GRANT ALL PRIVILEGES ON company.* TO 'admin_user'@'localhost';

-- 特定表权限
GRANT SELECT (id, first_name, last_name), UPDATE (email) 
ON company.employees TO 'hr_user'@'localhost';

-- 撤销权限
REVOKE DELETE ON company.* FROM 'app_user'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 立即生效
FLUSH PRIVILEGES;

6. 💾 存储引擎与事务

🏗️ 存储引擎

-- 显示支持的存储引擎
SHOW ENGINES;

-- 指定存储引擎创建表
CREATE TABLE innodb_table (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE aria_table (
    id INT PRIMARY KEY,
    log_data TEXT
) ENGINE=Aria TRANSACTIONAL=1;

-- 修改存储引擎
ALTER TABLE my_table ENGINE=InnoDB;

🔄 事务控制

-- 基本事务
START TRANSACTION;
INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE account_balance SET balance = balance - 100 WHERE user_id = 1;
COMMIT;
-- 或 ROLLBACK;

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ... 事务操作
COMMIT;

-- 保存点
START TRANSACTION;
INSERT INTO table1 VALUES (1);
SAVEPOINT sp1;
INSERT INTO table2 VALUES (2);
ROLLBACK TO SAVEPOINT sp1;  -- 只回滚到保存点
COMMIT;

7. 🛠️ 高级功能

📜 存储过程和函数

-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE IncreaseSalaries(
    IN dept_id INT, 
    IN increase_percent DECIMAL(5,2)
)
BEGIN
    DECLARE exit_handler BOOLEAN DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET exit_handler = TRUE;
    
    START TRANSACTION;
    
    UPDATE employees 
    SET salary = salary * (1 + increase_percent / 100)
    WHERE department_id = dept_id;
    
    IF exit_handler THEN
        ROLLBACK;
        SELECT 'Error: Operation failed' as result;
    ELSE
        COMMIT;
        SELECT CONCAT('Success: Updated salaries for department ', dept_id) as result;
    END IF;
END$$
DELIMITER ;

-- 调用存储过程
CALL IncreaseSalaries(1, 10.0);

✨ 触发器

-- 创建触发器
DELIMITER $$
CREATE TRIGGER before_employee_update
    BEFORE UPDATE ON employees
    FOR EACH ROW
BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
    
    IF NEW.salary < OLD.salary THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Salary cannot be decreased';
    END IF;
END$$
DELIMITER ;

👁️ 视图

-- 创建视图
CREATE VIEW active_employees AS
SELECT 
    e.id,
    CONCAT(e.first_name, ' ', e.last_name) as full_name,
    e.email,
    d.name as department_name,
    e.salary,
    e.hire_date
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.status = 'active';

-- 使用视图
SELECT * FROM active_employees WHERE department_name = '技术部';

8. 🗂️ 备份与维护

💽 数据备份

-- 导出数据
SELECT * INTO OUTFILE '/tmp/employees.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM employees;

-- 导入数据
LOAD DATA INFILE '/tmp/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

🛠️ 系统维护

-- 表维护
ANALYZE TABLE employees;
CHECK TABLE employees;
OPTIMIZE TABLE employees;
REPAIR TABLE employees;

-- 显示进程
SHOW PROCESSLIST;

-- 杀死进程
KILL 1234;

-- 显示状态
SHOW STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

9. 🎯 性能优化技巧

✅ 最佳实践

-- 1. 使用EXISTS代替IN
SELECT * FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.department_id = d.id
);

-- 2. 避免SELECT *
SELECT id, first_name, last_name FROM employees;

-- 3. 使用LIMIT限制结果集
SELECT * FROM employees LIMIT 100;

-- 4. 合理使用索引覆盖
SELECT department_id, COUNT(*) 
FROM employees 
WHERE hire_date > '2023-01-01'
GROUP BY department_id;

-- 5. 避免在WHERE子句中使用函数
-- 不好
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;
-- 好
SELECT * FROM employees WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';

📊 系统配置优化

-- 查看关键配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'query_cache%';

-- 临时修改配置
SET GLOBAL max_connections = 1000;
SET SESSION sort_buffer_size = 256000;