MariaDB表操作 #

表结构
├── 表名（Table Name）
├── 列（Column）
│   ├── 列名
│   ├── 数据类型
│   └── 约束
├── 行（Row）
│   └── 数据记录
└── 约束（Constraint）
    ├── 主键
    ├── 外键
    ├── 唯一
    └── 检查

-- 查看支持的存储引擎
SHOW ENGINES;

-- 常用引擎
-- InnoDB：默认，支持事务、外键
-- Aria：MariaDB增强的MyISAM
-- MyISAM：旧版默认，不支持事务
-- Memory：内存表
-- CSV：CSV格式
-- Archive：归档存储

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
) [ENGINE=engine_name] [DEFAULT CHARSET=charset_name];

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 如果不存在则创建
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- 创建表并复制数据
CREATE TABLE users_backup AS SELECT * FROM users;

-- 只复制结构
CREATE TABLE users_copy LIKE users;

-- 复制部分结构和数据
CREATE TABLE users_active AS 
SELECT id, name, email FROM users WHERE status = 1;

-- 创建临时表（会话结束后自动删除）
CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(50)
);

-- 从查询创建临时表
CREATE TEMPORARY TABLE temp_results AS
SELECT * FROM users WHERE created_at > '2024-01-01';

CREATE TABLE data_types_example (
    -- 整数类型
    tiny_col TINYINT,
    small_col SMALLINT,
    int_col INT,
    bigint_col BIGINT,
    
    -- 浮点类型
    float_col FLOAT,
    double_col DOUBLE,
    decimal_col DECIMAL(10,2),
    
    -- 字符串类型
    char_col CHAR(10),
    varchar_col VARCHAR(255),
    text_col TEXT,
    
    -- 日期时间类型
    date_col DATE,
    time_col TIME,
    datetime_col DATETIME,
    timestamp_col TIMESTAMP,
    
    -- 其他类型
    bool_col BOOLEAN,
    json_col JSON,
    enum_col ENUM('a', 'b', 'c')
);

CREATE TABLE column_attributes (
    -- 默认值
    status INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- 非空约束
    name VARCHAR(50) NOT NULL,
    
    -- 自动递增
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- 无符号
    age TINYINT UNSIGNED,
    
    -- 零填充
    code INT(5) ZEROFILL,
    
    -- 注释
    description VARCHAR(200) COMMENT '描述信息'
);

-- MariaDB 10.3+ 隐藏列
CREATE TABLE invisible_column (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    secret_col VARCHAR(100) INVISIBLE
);

-- 单列主键
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 自增主键
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

-- 复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 使用UUID主键（MariaDB 10.7+）
CREATE TABLE users (
    id UUID DEFAULT UUID() PRIMARY KEY,
    name VARCHAR(50)
);

-- 创建主表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- 创建从表并添加外键
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 外键选项
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    CONSTRAINT fk_department 
        FOREIGN KEY (department_id) 
        REFERENCES departments(id)
        ON DELETE CASCADE      -- 删除时级联删除
        ON UPDATE CASCADE      -- 更新时级联更新
);

-- 外键选项说明
-- ON DELETE/UPDATE:
--   CASCADE: 级联操作
--   SET NULL: 设置为NULL
--   SET DEFAULT: 设置为默认值
--   RESTRICT: 限制（默认）
--   NO ACTION: 无操作

-- 列级唯一约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- 表级唯一约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    UNIQUE KEY uk_email (email)
);

-- 复合唯一约束
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE KEY uk_user_role (user_id, role_id)
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT  -- 可以为NULL
);

-- MariaDB 10.2+ 支持CHECK约束
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    CHECK (price > 0)
);

-- 命名CHECK约束
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    salary DECIMAL(10,2),
    CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65),
    CONSTRAINT chk_salary CHECK (salary > 0)
);

-- 复杂CHECK约束
CREATE TABLE orders (
    id INT PRIMARY KEY,
    status ENUM('pending', 'confirmed', 'shipped', 'delivered'),
    created_at DATETIME,
    shipped_at DATETIME,
    CHECK (
        (status = 'pending' AND shipped_at IS NULL) OR
        (status IN ('shipped', 'delivered') AND shipped_at IS NOT NULL)
    )
);

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 使用表达式作为默认值（MariaDB 10.2+）
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    code VARCHAR(20) DEFAULT (UUID())
);

-- 查看当前数据库的表
SHOW TABLES;

-- 模糊匹配
SHOW TABLES LIKE 'user%';

-- 查看特定数据库的表
SHOW TABLES FROM mydb;

-- 查看表结构
DESCRIBE users;
DESC users;

-- 查看创建语句
SHOW CREATE TABLE users;

-- 从information_schema查询
SELECT 
    column_name,
    column_type,
    is_nullable,
    column_key,
    column_default,
    extra
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;

-- 查看表状态
SHOW TABLE STATUS LIKE 'users';

-- 查看表大小
SELECT 
    table_name,
    table_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = DATABASE();

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 添加列到指定位置
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER name;

-- 添加列到第一位
ALTER TABLE users ADD COLUMN id INT FIRST;

-- 添加多列
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20) AFTER name,
ADD COLUMN address VARCHAR(200) AFTER phone;

-- MariaDB 10.3+ 即时添加列（无锁表）
ALTER TABLE users ADD COLUMN nickname VARCHAR(50), ALGORITHM=INSTANT;

-- 修改列类型
ALTER TABLE users MODIFY COLUMN name VARCHAR(100);

-- 修改列名和类型
ALTER TABLE users CHANGE COLUMN name username VARCHAR(100);

-- 修改列属性
ALTER TABLE users MODIFY COLUMN name VARCHAR(100) NOT NULL;

-- 修改默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 0;

-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 删除多列
ALTER TABLE users 
DROP COLUMN phone,
DROP COLUMN address;

-- 重命名表
RENAME TABLE users TO members;

-- 重命名多个表
RENAME TABLE 
    users TO members,
    orders TO sales;

-- 使用ALTER TABLE
ALTER TABLE users RENAME TO members;

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

-- 查看修改进度
ALTER TABLE users ENGINE = InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

-- 修改表字符集
ALTER TABLE users CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 转换所有列的字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 删除表
DROP TABLE users;

-- 如果存在则删除
DROP TABLE IF EXISTS users;

-- 删除多个表
DROP TABLE users, orders, products;

-- 清空表数据（保留结构）
TRUNCATE TABLE users;

-- TRUNCATE vs DELETE
-- TRUNCATE: 
--   - 更快
--   - 重置AUTO_INCREMENT
--   - 不触发触发器
--   - 不能回滚
-- DELETE:
--   - 可以WHERE条件
--   - 触发触发器
--   - 可以回滚
--   - 不重置AUTO_INCREMENT

-- 临时表会话结束后自动删除
-- 也可以手动删除
DROP TEMPORARY TABLE IF EXISTS temp_users;

-- 创建普通索引
CREATE INDEX idx_name ON users(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_name_status ON users(name, status);

-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 创建前缀索引
CREATE INDEX idx_name_prefix ON users(name(10));

-- 使用ALTER TABLE创建索引
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 删除索引
DROP INDEX idx_name ON users;

-- 使用ALTER TABLE删除索引
ALTER TABLE users DROP INDEX idx_name;

-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;

-- 查看表索引
SHOW INDEX FROM users;

-- 从information_schema查询
SELECT 
    index_name,
    column_name,
    non_unique,
    seq_in_index
FROM information_schema.statistics
WHERE table_name = 'users';

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE employees 
ADD CONSTRAINT fk_department 
FOREIGN KEY (department_id) REFERENCES departments(id);

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE KEY uk_email (email);

-- 添加CHECK约束
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);

-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;

-- 删除外键
ALTER TABLE employees DROP FOREIGN KEY fk_department;

-- 删除唯一约束
ALTER TABLE users DROP INDEX uk_email;

-- 删除CHECK约束
ALTER TABLE products DROP CONSTRAINT chk_price;
-- 或
ALTER TABLE products DROP CHECK chk_price;

-- RANGE分区
CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- LIST分区
CREATE TABLE users (
    id INT,
    region_id INT
) PARTITION BY LIST (region_id) (
    PARTITION p_north VALUES IN (1, 2, 3),
    PARTITION p_south VALUES IN (4, 5, 6),
    PARTITION p_east VALUES IN (7, 8, 9),
    PARTITION p_west VALUES IN (10, 11, 12)
);

-- HASH分区
CREATE TABLE logs (
    id INT,
    created_at DATETIME
) PARTITION BY HASH (id) PARTITIONS 4;

-- 添加分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- 删除分区
ALTER TABLE orders DROP PARTITION p2022;

-- 合并分区
ALTER TABLE orders REORGANIZE PARTITION p2023, p2024 INTO (
    PARTITION p2023_2024 VALUES LESS THAN (2025)
);

-- 查看分区信息
SELECT * FROM information_schema.partitions 
WHERE table_name = 'orders';

-- 复制表结构（包含索引）
CREATE TABLE users_copy LIKE users;

-- 只复制表结构（不含索引）
CREATE TABLE users_structure AS SELECT * FROM users WHERE 1=0;

-- 复制数据到新表
CREATE TABLE users_backup AS SELECT * FROM users;

-- 复制数据到已有表
INSERT INTO users_copy SELECT * FROM users;

-- 条件复制
INSERT INTO users_active SELECT * FROM users WHERE status = 1;

-- 分析表（更新统计信息）
ANALYZE TABLE users;

-- 检查表
CHECK TABLE users;

-- 修复表
REPAIR TABLE users;

-- 优化表
OPTIMIZE TABLE users;

-- 校验表数据
CHECKSUM TABLE users;

-- 校验多个表
CHECKSUM TABLE users, orders, products;

-- 表名：小写，下划线分隔，复数形式
CREATE TABLE users (...);
CREATE TABLE order_items (...);

-- 列名：小写，下划线分隔
CREATE TABLE users (
    user_id INT,
    first_name VARCHAR(50),
    created_at DATETIME
);

-- 主键：id 或 表名_id
CREATE TABLE users (
    id INT PRIMARY KEY
);

-- 外键：关联表名_id
CREATE TABLE orders (
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 索引：idx_表名_列名
CREATE INDEX idx_users_email ON users(email);

-- 唯一索引：uk_表名_列名
CREATE UNIQUE INDEX uk_users_email ON users(email);

-- 1. 选择合适的存储引擎
CREATE TABLE users (...) ENGINE=InnoDB;

-- 2. 使用合适的字符集
CREATE TABLE users (...) DEFAULT CHARSET=utf8mb4;

-- 3. 为大表创建分区
CREATE TABLE logs (...) PARTITION BY RANGE (...);

-- 4. 合理使用索引
CREATE INDEX idx_users_status_created ON users(status, created_at);

-- 5. 使用覆盖索引
SELECT id, name FROM users WHERE status = 1;  -- 如果有(status, name)索引