MySQL参考手册

-- 枚举类型：只能选择预定义值之一
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    status ENUM('active', 'inactive', 'pending')
);

-- 集合类型：可以选择多个预定义值
CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    tags SET('sale', 'new', 'featured', 'limited')
);

-- 创建数据库
CREATE DATABASE company;

-- 创建表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

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

-- 插入数据
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('张三', '技术部', 8000.00, '2023-01-15');

-- 更新数据
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = '技术部';

-- 删除数据
DELETE FROM employees 
WHERE id = 5;

-- 创建用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password123';

-- 授予权限
GRANT SELECT, INSERT, UPDATE ON company.* TO 'app_user'@'localhost';

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

-- 开始事务
START TRANSACTION;

-- 执行多个操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- IF函数
SELECT name, salary, 
       IF(salary > 5000, '高薪', '普通') AS salary_level
FROM employees;

-- CASE语句
SELECT name, salary,
    CASE
        WHEN salary > 10000 THEN '高薪'
        WHEN salary > 5000 THEN '中等'
        ELSE '一般'
    END AS salary_level
FROM employees;

-- COALESCE函数（返回第一个非NULL值）
SELECT name, COALESCE(phone, email, '无联系方式') AS contact
FROM employees;

-- 查看所有系统变量
SHOW VARIABLES;

-- 查看特定变量
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 查看全局变量
SHOW GLOBAL VARIABLES;

-- 查看会话变量
SHOW SESSION VARIABLES;

-- 使用SELECT查看变量值
SELECT @@global.innodb_buffer_pool_size;
SELECT @@session.autocommit;

-- 设置会话变量（当前连接有效）
SET sort_buffer_size = 256000;
SET SESSION sort_buffer_size = 256000;

-- 设置全局变量（所有新连接有效）
SET GLOBAL max_connections = 200;
SET @@global.max_connections = 200;

-- 永久设置（需要修改配置文件）
# my.cnf
[mysqld]
max_connections = 200
innodb_buffer_pool_size = 1G
query_cache_size = 64M