【MySQL_06】表的相关操作

一、表的基本操作

1.1 创建表

使用 CREATE TABLE 语句定义表结构，包含列名、数据类型和约束条件。

语法：

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

示例：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2)
);

1.2 修改表结构

通过 ALTER TABLE 调整表结构，支持添加/删除/修改列、重命名列、添加约束等操作。

1.2.1 添加列

ALTER TABLE employees ADD department VARCHAR(50);

1.2.2 删除列

ALTER TABLE employees DROP COLUMN department;

1.2.3 修改列

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

1.2.4 重命名列

ALTER TABLE employees CHANGE hire_date start_date DATE;

1.2.5 添加约束

ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

1.3 删除表

使用 DROP TABLE 永久删除表及数据：

DROP TABLE employees;  -- 不可恢复！

1.4 查询表结构

查看表结构信息：

DESCRIBE employees;  -- 或 SHOW COLUMNS FROM employees;

1.5 重命名表

修改表名：

RENAME TABLE employees TO staff;

1.6 复制表

1.6.1 仅复制结构

CREATE TABLE employees_copy LIKE employees;

1.6.2 复制结构及数据

CREATE TABLE employees_copy AS SELECT * FROM employees;

1.7 清空表数据

快速清空数据并重置自增列：

TRUNCATE TABLE employees;  -- 不可逆操作！

二、数据完整性约束

2.1 主键约束

唯一标识记录：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    ...
);

2.2 唯一约束

确保列值唯一：

ALTER TABLE employees ADD UNIQUE (email);

2.3 非空约束

禁止空值：

CREATE TABLE users (
    username VARCHAR(50) NOT NULL,
    ...
);

2.4 外键约束

跨表数据一致性：

ALTER TABLE orders 
ADD FOREIGN KEY (user_id) REFERENCES users(id);

2.5 检查约束

自定义数据条件：

ALTER TABLE products 
ADD CHECK (stock_quantity >= 0);

三、索引管理

3.1 创建索引

加速查询：

CREATE INDEX idx_last_name ON employees(last_name);

3.2 删除索引

释放存储空间：

DROP INDEX idx_last_name ON employees;

四、高级表功能

4.1 分区表

4.1.1 按范围分区

CREATE TABLE sales (...) 
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

4.1.2 管理分区

动态调整分区：

ALTER TABLE sales ADD PARTITION (PARTITION p2022 VALUES LESS THAN (2023));

4.2 视图

4.2.1 创建虚拟表

CREATE VIEW high_salary_view AS
SELECT * FROM employees WHERE salary > 10000;

4.2.2 删除视图

DROP VIEW high_salary_view;

4.3 触发器

4.3.1 自动执行业务逻辑

CREATE TRIGGER update_timestamp 
BEFORE UPDATE ON orders 
FOR EACH ROW SET NEW.updated_at = NOW();

4.3.2 删除触发器

DROP TRIGGER update_timestamp;

4.4 存储过程与函数

4.4.1 存储过程示例

CREATE PROCEDURE RaiseSalary(IN emp_id INT, IN amount DECIMAL)
BEGIN
    UPDATE employees SET salary = salary + amount WHERE id = emp_id;
END;

4.4.2 函数示例

CREATE FUNCTION GetEmployeeName(emp_id INT) 
RETURNS VARCHAR(100)
BEGIN
    RETURN (SELECT CONCAT(first_name, ' ', last_name) FROM employees WHERE id = emp_id);
END;

4.5 定时任务

4.5.1 创建每日备份事件

CREATE EVENT daily_backup
ON SCHEDULE EVERY 1 DAY
DO BEGIN
    -- 备份逻辑
END;

4.5.2 删除事件

DROP EVENT daily_backup;

五、事务管理

5.1 事务控制

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 或 ROLLBACK;

六、备份与恢复

6.1 使用mysqldump备份

mysqldump -u root -p mydb employees > backup.sql

6.2 恢复数据

mysql -u root -p mydb < backup.sql

七、性能优化

7.1 优化存储空间

OPTIMIZE TABLE employees;

7.2 查询分析

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

八、存储引擎与字符集

8.1 切换存储引擎

ALTER TABLE employees ENGINE = InnoDB;

8.2 设置UTF-8编码

ALTER TABLE employees CONVERT TO CHARACTER SET utf8mb4;

九、权限管理

9.1 授权访问

GRANT SELECT, INSERT ON employees TO 'user1'@'localhost';

9.2 撤销权限

REVOKE DELETE ON employees FROM 'user1'@'localhost';

十、日志管理

10.1 启用二进制日志

SET GLOBAL log_bin = ON;

10.2 监控慢查询

SHOW VARIABLES LIKE 'slow_query%';