MySQL表操作 #

一、表概述 #

1.1 什么是表 #

表（Table）是数据库中存储数据的基本单位，由行（记录）和列（字段）组成。

text

┌─────────────────────────────────────────────┐
│                  users 表                    │
├─────────┬──────────┬─────────┬─────────────┤
│ id      │ name     │ age     │ email       │ ← 列（字段）
├─────────┼──────────┼─────────┼─────────────┤
│ 1       │ John     │ 25      │ john@ex.com │ ← 行（记录）
│ 2       │ Jane     │ 30      │ jane@ex.com │
│ 3       │ Bob      │ 28      │ bob@ex.com  │
└─────────┴──────────┴─────────┴─────────────┘

1.2 表的组成 #

组成部分	说明
表名	标识表的名称
列名	标识字段的名称
数据类型	字段存储的数据类型
约束	字段的限制条件

二、创建表 #

2.1 基本语法 #

sql

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

2.2 创建简单表 #

sql

-- 创建用户表
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

2.3 创建带约束的表 #

sql

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT DEFAULT 0,
    email VARCHAR(100) UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2.4 完整示例 #

sql

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password VARCHAR(100) NOT NULL COMMENT '密码',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    phone CHAR(11) COMMENT '手机号',
    age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
    gender ENUM('male', 'female', 'other') DEFAULT 'male' COMMENT '性别',
    status TINYINT DEFAULT 1 COMMENT '状态：1启用 0禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

三、约束 #

3.1 约束类型 #

约束	说明	关键字
主键约束	唯一标识每行记录	PRIMARY KEY
外键约束	建立表之间的关联	FOREIGN KEY
唯一约束	字段值唯一	UNIQUE
非空约束	字段值不能为空	NOT NULL
默认约束	设置默认值	DEFAULT
自增约束	自动递增	AUTO_INCREMENT
检查约束	自定义条件	CHECK

3.2 主键约束 #

sql

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

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

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

-- 添加主键约束名
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    CONSTRAINT pk_users PRIMARY KEY (id)
);

3.3 外键约束 #

sql

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

-- 创建从表，添加外键
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    CONSTRAINT fk_emp_dept 
    FOREIGN KEY (department_id) 
    REFERENCES departments(id)
);

-- 外键级联操作
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) 
    REFERENCES departments(id)
    ON DELETE CASCADE    -- 删除主表记录时删除从表记录
    ON UPDATE CASCADE    -- 更新主表记录时更新从表记录
);

3.4 唯一约束 #

sql

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

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

-- 组合唯一
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE (user_id, role_id)
);

3.5 非空约束 #

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- 插入时必须提供非空字段的值
INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com');

3.6 默认约束 #

sql

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

-- 插入时可以省略有默认值的字段
INSERT INTO users (id, name) VALUES (1, 'John');
-- status 自动设为 1，created_at 自动设为当前时间

3.7 自增约束 #

sql

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 自增从1开始
INSERT INTO users (name) VALUES ('John');  -- id = 1
INSERT INTO users (name) VALUES ('Jane');  -- id = 2

-- 指定起始值
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
) AUTO_INCREMENT = 1000;

-- 设置自增值
ALTER TABLE users AUTO_INCREMENT = 100;

3.8 检查约束 #

sql

-- MySQL 8.0+ 支持
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10,2),
    CHECK (price > 0)
);

-- 命名检查约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    CONSTRAINT chk_users_age CHECK (age >= 0 AND age <= 150)
);

四、查看表 #

4.1 查看表列表 #

sql

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

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

-- 模糊查询
SHOW TABLES LIKE 'user%';

4.2 查看表结构 #

sql

-- 方式1：DESCRIBE（推荐）
DESC users;
DESCRIBE users;

+-------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type         | Null | Key | Default           | Extra                       |
+-------+--------------+------+-----+-------------------+-----------------------------+
| id    | int          | NO   | PRI | NULL              | auto_increment              |
| name  | varchar(50)  | YES  |     | NULL              |                             |
| age   | int          | YES  |     | NULL              |                             |
+-------+--------------+------+-----+-------------------+-----------------------------+

-- 方式2：SHOW COLUMNS
SHOW COLUMNS FROM users;

-- 方式3：SHOW CREATE TABLE
SHOW CREATE TABLE users;

4.3 查看表详细信息 #

sql

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

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

-- 从information_schema查询
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default,
    column_comment
FROM information_schema.columns
WHERE table_name = 'users' AND table_schema = 'mydb';

五、修改表 #

5.1 修改表名 #

sql

-- 方式1
RENAME TABLE users TO user_info;

-- 方式2
ALTER TABLE users RENAME TO user_info;

5.2 添加列 #

sql

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

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

-- 指定位置
ALTER TABLE users ADD COLUMN age INT AFTER name;  -- 在name之后
ALTER TABLE users ADD COLUMN id INT FIRST;        -- 在第一列

5.3 修改列 #

sql

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

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

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

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

5.4 删除列 #

sql

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

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

5.5 添加约束 #

sql

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

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

-- 添加外键
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_users 
FOREIGN KEY (user_id) REFERENCES users(id);

-- 添加检查约束
ALTER TABLE users ADD CHECK (age >= 0);

5.6 删除约束 #

sql

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

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

-- 删除外键
ALTER TABLE orders DROP FOREIGN KEY fk_orders_users;

-- 删除检查约束
ALTER TABLE users DROP CHECK chk_users_age;

5.7 修改存储引擎 #

sql

ALTER TABLE users ENGINE = InnoDB;

5.8 修改字符集 #

sql

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

六、删除表 #

6.1 删除表 #

sql

-- 删除表
DROP TABLE users;

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

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

6.2 清空表 #

sql

-- 方式1：TRUNCATE（推荐，更快）
TRUNCATE TABLE users;

-- 方式2：DELETE
DELETE FROM users;

-- 区别：
-- TRUNCATE：重置自增值，不记录日志，更快
-- DELETE：保留自增值，记录日志，可以带条件

6.3 删除注意事项 #

sql

-- 删除前检查
-- 1. 确认表名
SHOW TABLES LIKE 'users';

-- 2. 查看表内容
SELECT COUNT(*) FROM users;

-- 3. 备份（如果需要）
-- mysqldump -u root -p mydb users > users_backup.sql

-- 4. 执行删除
DROP TABLE IF EXISTS users;

七、临时表 #

7.1 创建临时表 #

sql

-- 创建临时表
CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(50)
);

-- 从查询结果创建临时表
CREATE TEMPORARY TABLE temp_orders AS
SELECT * FROM orders WHERE created_at > '2024-01-01';

7.2 临时表特点 #

sql

-- 临时表特点：
-- 1. 只在当前会话可见
-- 2. 会话结束自动删除
-- 3. 可以与普通表同名（临时表优先）

-- 查看临时表
SHOW TABLES;  -- 不显示临时表

-- 删除临时表
DROP TEMPORARY TABLE temp_users;

八、表复制 #

8.1 复制表结构 #

sql

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

-- 查看结构
DESC users_copy;

8.2 复制结构和数据 #

sql

-- 复制结构和数据
CREATE TABLE users_backup AS SELECT * FROM users;

-- 注意：这种方式不会复制约束和索引

8.3 复制部分数据 #

sql

-- 复制部分数据
CREATE TABLE users_2024 AS
SELECT * FROM users WHERE YEAR(created_at) = 2024;

九、表设计最佳实践 #

9.1 命名规范 #

sql

-- 表名：小写，下划线分隔，使用复数
users
order_items
product_categories

-- 字段名：小写，下划线分隔
user_name
created_at
is_active

-- 主键：id 或 表名_id
id
user_id

-- 外键：关联表名_id
department_id
order_id

9.2 字段设计 #

sql

CREATE TABLE users (
    -- 主键
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    
    -- 字符串字段
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    
    -- 状态字段
    status TINYINT UNSIGNED DEFAULT 1,
    
    -- 布尔字段
    is_active BOOLEAN DEFAULT TRUE,
    
    -- 时间字段
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 索引
    INDEX idx_username (username),
    UNIQUE INDEX uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

9.3 表设计原则 #

原则	说明
范式设计	避免数据冗余
适当反范式	提高查询性能
选择合适类型	节省存储空间
添加必要索引	提高查询速度
添加注释	提高可维护性

十、总结 #

表操作要点：

操作	语法
创建	CREATE TABLE table_name (…)
查看	DESC table_name
修改	ALTER TABLE table_name …
删除	DROP TABLE table_name
清空	TRUNCATE TABLE table_name

约束要点：

约束	说明
PRIMARY KEY	主键，唯一标识
FOREIGN KEY	外键，关联表
UNIQUE	唯一约束
NOT NULL	非空约束
DEFAULT	默认值
AUTO_INCREMENT	自增

下一步，让我们学习数据操作！