MySQL 表operation and management

1. 表 creation

in MySQLin, usingCREATE TABLE语句creation new 表. creation表时需要指定表名, 字段名称, dataclass型 and 约束条件etc..

1.1 basiccreation语法

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

1.2 creation表example

-- creationuser表
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- creation文章表
CREATE TABLE IF NOT EXISTS articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- creation评论表
CREATE TABLE IF NOT EXISTS comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT NOT NULL,
    user_id INT NOT NULL,
    article_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

1.3 creation表 Notes

• usingIF NOT EXISTS可以避免表已存 in 时 error
• for 表指定合适 store引擎, 通常usingInnoDB (supporttransaction and out 键)
• for 表指定合适 字符集, 通常usingutf8mb4 (support更 many 字符)
• for 字段添加适当 约束, 确保data integrity

2. 表structure modify

in MySQLin, usingALTER TABLE语句modify表structure. 可以添加, modify or delete字段, 以及modify表 otherproperty.

2.1 添加字段

-- 向user表添加 new 字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 向user表添加 new 字段 to 开头
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT TRUE FIRST;

-- 向user表添加 many 个 new 字段
ALTER TABLE users ADD COLUMN (
    address VARCHAR(255),
    birthday DATE
);

2.2 modify字段

-- modify字段dataclass型
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);

-- modify字段名称 and dataclass型
ALTER TABLE users CHANGE COLUMN phone phone_number VARCHAR(30);

-- modify字段默认值
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT FALSE;

-- modify字段位置
ALTER TABLE users MODIFY COLUMN address VARCHAR(255) AFTER phone_number;

2.3 delete字段

-- delete字段
ALTER TABLE users DROP COLUMN birthday;

-- delete many 个字段
ALTER TABLE users DROP COLUMN address, DROP COLUMN phone_number;

2.4 modify表property

-- modify表名
ALTER TABLE users RENAME TO members;

-- modify表字符集
ALTER TABLE members CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- modify表store引擎
ALTER TABLE members ENGINE=InnoDB;

3. 表 delete

in MySQLin, usingDROP TABLE语句delete表. delete表会同时delete表structure and 表in 所 has data, operation不可逆, 需谨慎执行.

3.1 delete表语法

DROP TABLE [IF EXISTS] table_name [, table_name2, ...];

3.2 delete表example

-- delete单个表
DROP TABLE IF EXISTS comments;

-- delete many 个表
DROP TABLE IF EXISTS articles, members;

3.3 delete表 Notes

• usingIF EXISTS可以避免表不存 in 时 error
• delete表会同时delete表in 所 has data, operation before 请backup important data
• such as果表之间存 in out 键relationships, 需要按照依赖relationships 相反顺序delete表

4. 表 rename

in MySQLin, 可以usingRENAME TABLE语句rename表.

4.1 rename表语法

RENAME TABLE old_table_name TO new_table_name;

4.2 rename表example

-- rename表
RENAME TABLE users TO members;

-- 同时rename many 个表
RENAME TABLE 
    members TO users,
    articles TO posts;

5. 表 copy

in MySQLin, 可以usingCREATE TABLE ... LIKE语句copy表structure, or usingCREATE TABLE ... SELECT语句copy表structure and data.

5.1 copy表structure

-- copy表structure (不copydata) 
CREATE TABLE users_backup LIKE users;

-- copy表structure and 部分data
CREATE TABLE users_active AS
SELECT * FROM users WHERE is_active = TRUE;

-- copy表structure and 所 has data
CREATE TABLE users_full_backup AS
SELECT * FROM users;

5.2 copy表 Notes

• CREATE TABLE ... LIKE会copy表structure, index and 约束, 但不会copydata
• CREATE TABLE ... SELECT会copy表structure and data, 但不会copyindex and 约束
• copy big 表时可能会占用 big 量resource, 建议 in low 峰期执行

6. 表 截断

in MySQLin, usingTRUNCATE TABLE语句截断表. 截断表会delete表in 所 has data, 但保留表structure, 比DELETE FROM更 high 效.

6.1 截断表语法

TRUNCATE TABLE table_name;

6.2 截断表example

-- 截断表
TRUNCATE TABLE users_backup;

-- 截断表并重设自增计数器
TRUNCATE TABLE users;

6.3 截断表 and delete表 区别

• TRUNCATE TABLE只deletedata, 保留表structure, 速度更 fast
• DROP TABLEdelete整个表structure and data, operation不可逆
• TRUNCATE TABLE会reset自增字段 计数器
• TRUNCATE TABLE不会触发DELETE触发器

7. 表 informationquery

in MySQLin, 可以using many 种语句query表 information, including表structure, 表statusetc..

7.1 query表structure

-- usingDESCRIBEquery表structure
DESCRIBE users;

-- usingSHOW CREATE TABLEquerycreation表 语句
SHOW CREATE TABLE users;

-- usingSHOW COLUMNSquery列information
SHOW COLUMNS FROM users;

7.2 query表status

-- query表status
SHOW TABLE STATUS LIKE 'users';

-- querydatalibraryin 所 has 表
SHOW TABLES;

8. 完整example

-- creationexampledatalibrary
CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

-- 1. creation部门表
CREATE TABLE IF NOT EXISTS departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. creation员工表
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    hire_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. 向员工表添加 new 字段
ALTER TABLE employees ADD COLUMN email VARCHAR(100) AFTER name;

-- 4. modify员工表字段
ALTER TABLE employees MODIFY COLUMN email VARCHAR(150) NOT NULL;

-- 5. rename员工表
ALTER TABLE employees RENAME TO staff;

-- 6. copy员工表
CREATE TABLE IF NOT EXISTS staff_backup LIKE staff;
INSERT INTO staff_backup SELECT * FROM staff;

-- 7. 截断backup表
TRUNCATE TABLE staff_backup;

-- 8. deletebackup表
DROP TABLE IF EXISTS staff_backup;

-- 9. query表structure
DESCRIBE staff;
SHOW CREATE TABLE staff;

-- 10. querydatalibraryin 表
SHOW TABLES;