04 表的操作

表的操作

1. 创建表

1. 语法格式

CREATE TABLE 用于在数据库中创建一张新的表，可以定义字段名、数据类型、约束等信息。

1
2
3
4
5
6
CREATE TABLE [IF NOT EXISTS] 表名 (
    列名1 数据类型 [列级约束],
    列名2 数据类型 [列级约束],
    ...
    [表级约束]
) [ENGINE=存储引擎] [DEFAULT CHARSET=字符集] [COLLATE=校验规则];

参数说明：

参数说明
IF NOT EXISTS如果表已存在则报错，没有才创建
数据类型定义字段所能存储的数据类型，如 INT、CHAR、VARCHAR、DATE、FLOAT 等
列级约束针对单个列的约束，如 NOT NULL、DEFAULT、AUTO_INCREMENT、PRIMARY KEY
表级约束跨列的约束，如联合主键、外键等
ENGINE存储引擎，如 InnoDB、MyISAM
CHARSET字符集，用于指定表所采用的 编码格式，如果没有指定则以所在数据库的编码格式为准。
COLLATE字符比较/校验规则，用于指定表所采用的校验规则，如果没有指定则以所在数据库的校验规则为准。
常用字段数据类型简表说明
INT整数（4 字节），范围类比 C++的 int
BIGINT大整数（8 字节），范围类比 C++的 long long
VARCHAR(N)可变长字符串，N 为最大字符数（如 VARCHAR(50)）
CHAR(N)定长字符串（不足补空格）
TEXT长文本（最大 65535 字节）
DATETIME日期时间（格式：YYYY-MM-DD HH:MM:SS）
TIMESTAMP时间戳（自动记录修改时间，范围较小）
DECIMAL(M,D)精确小数，M 为总位数，D 为小数位数（如 DECIMAL(10,2)）

建议：字符串优先用 VARCHAR，文本内容多用 TEXT；时间用 DATETIME 更直观。

常见的列级约束作用示例
NOT NULL该列值不能为空name VARCHAR(50) NOT NULL
DEFAULT设置默认值age INT DEFAULT 18
AUTO_INCREMENT自动增长（仅整数列可用）id INT AUTO_INCREMENT
PRIMARY KEY指定该列为主键（唯一且非空）id INT PRIMARY KEY
UNIQUE唯一约束email VARCHAR(100) UNIQUE
COMMENT添加列注释grade VARCHAR(20) COMMENT '学生年级'

2. 创建表示例

在 CREATE TABLE 语句中，括号内需要定义表的列结构（至少要有一个列），比如 create table students(); 括号内是空的，没有指定任何列，这不符合 MySQL 的语法规则，会报错！

1
2
3
4
5
6
7
# 创建学生表（如果不存在）
CREATE TABLE IF NOT EXISTS students(
    id int comment '用户的ID',  # 学生的唯一标识ID
    name varchar(50) comment '用户的姓名',  # 学生的姓名信息
    age int default 18 comment '学生的年龄，默认值为18',  # 学生的年龄，未指定时默认为18
    enrollment_date DATE comment '学生的入学日期'  # 学生办理入学的日期
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci comment '存储学生基本信息的表';

说明： 创建的表会自动存储在当前数据库对应的目录中（如 /var/lib/mysql/test/students.ibd），如果忘记写 IF NOT EXISTS，当表名重复时会报错。

说明一下：

  • 采用不同的存储引擎，创建表时所产生的文件不一样。
  • 比如采用 InnoDB 存储引擎建表，会产生对应的 xxx.frm（表结构）和 xxx.ibd（表数据+表索引）文件。
  • 比如采用 MyISAM 存储引擎建表，会产生对应的 xxx.frm（表结构）、xxx.MYD（表数据）和 xxx.MYI（表索引）文件。

3. 插入数据

INSERT INTO 用于向表中添加新的数据行，其基本语法有两种形式：指定列插入 和 全列插入。

1
2
3
4
5
6
7
# 语法1：指定列插入（推荐写法）
INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...);

# 语法2：全列插入（列名可省略，但必须按顺序写全）
INSERT INTO 表名
VALUES (值1, 值2, 值3, ...);

注意： 插入数据时，列名与值的顺序和数量必须一一对应，如果某列有默认值或允许为 NULL，可在插入时省略，字符串和日期型值需用 单引号 ' ' 包裹。假设表结构：

1
2
3
4
5
6
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学号',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    age INT DEFAULT 18 COMMENT '年龄',
    enrollment_date DATE COMMENT '入学日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 插入所有列（标准写法）
insert into students (name, age, enrollment_date)
values ('张三', 20, '2025-01-01');

# 省略可默认列（自动填默认值），age 会默认填 18，id 自动递增
insert into students (name)
values ('李四');

# 同时插入多条数据
insert into students (name, age, enrollment_date)
values 
('王五', 19, '2024-09-01'),
('赵六', 21, '2023-09-01'),
('钱七', 22, '2022-09-01');

# 插入所有列（不写列名）：必须按照建表时字段的定义顺序填写所有列
#  如果主键为自增列，可使用 NULL 或 DEFAULT 占位
insert into students
values (NULL, '刘八', 23, '2022-09-01');

# ……

2. 查看表结构

1. 查看当前数据库中所有表

1
2
SHOW TABLES;
show tables;

2. 查看表结构定义

1
2
DESC 表名;
DESCRIBE 表名;

示例：

1
2
desc students;
describe students;

输出示例：

1
2
3
4
5
6
7
8
9
10
mysql> desc students;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int         | YES  |     | NULL    |       |
| name            | varchar(50) | YES  |     | NULL    |       |
| age             | int         | YES  |     | 18      |       |
| enrollment_date | date        | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
列名（表头）含义
Field列名（字段名），即表中每一列的名称
Type数据类型，例如 int、varchar(50)、date 等
Null是否允许为 NULL，YES 表示可为空，NO 表示必须有值
Key键类型（约束标识） ：
空：无约束
PRI：主键（Primary Key）
UNI：唯一键（Unique）
MUL：普通索引（Multiple，可重复）
Default默认值，如果未指定则为 NULL 或系统默认
Extra额外属性，如：
auto_increment：自动增长
on update CURRENT_TIMESTAMP：自动更新时间

3. 查看建表语句

还是一样，SHOW CREATE TABLE 表名; 大概率会有点乱码的感觉，不太美观，加上 \G 选项会好一点。

1
2
SHOW CREATE TABLE 表名;
show create table students;

输出示例：

1
2
3
4
5
6
7
8
9
10
mysql> show create table students\G;
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int DEFAULT NULL COMMENT '用户的ID',
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户的姓名',
  `age` int DEFAULT '18' COMMENT '学生的年龄，默认值为18',
  `enrollment_date` date DEFAULT NULL COMMENT '学生的入学日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='存储学生基本信息的表'
1 row in set (0.01 sec)

SHOW CREATE TABLE 可用于复制表结构、备份或迁移时查看完整建表 SQL。

4. 查看表中数据

1. 语法格式

SELECT 语句用于 从表中查询数据，是最常用的 SQL 语句之一。

1
2
3
4
5
SELECT [字段列表 | *]
FROM 表名
[WHERE 条件]
[ORDER BY 字段 [ASC|DESC]]
[LIMIT 偏移量, 行数];

说明： SELECT * 表示查询所有字段，查询结果不会修改数据，是 只读操作，查询时可以加上条件、排序、分页等功能。

2. 基本查询

查询所有数据： 查询整张表的所有字段和所有记录。

1
2
SELECT * FROM 表名;
select * from 表名

查询指定字段： 只查询所需的列，提高查询效率。

1
SELECT id, name, age FROM students;

为字段添加别名（方便阅读）：AS 可选，用于重命名列标题。

1
SELECT id AS 学号, name AS 姓名, age AS 年龄 FROM students;

3. 条件查询

用于 筛选 符合条件的数据行，支持运算符：=, !=, <, >, <=, >=, AND, OR, IN, LIKE, BETWEEN 等。

1
2
3
4
5
6
7
8
9
10
11
# 查询年龄大于 18 的学生
SELECT * FROM students WHERE age > 18;

# 查询年龄在 18 到 22 之间的学生
select * from students where age between 18 and 22;

# 查询名字为张三的学生
SELECT * FROM students WHERE name = '张三';

# 查询名字中包含“张”的学生（模糊匹配）
select * from students where name like '%张%';

4. 排序

用于按指定字段对结果进行 升序 或 降序 排序，默认排序为 ASC（升序），降序需显式写 DESC。

1
2
3
4
5
# 按年龄升序排列
SELECT * FROM students ORDER BY age ASC;

# 按入学日期降序排列
select * from students order by enrollment_date desc;

5. 限制返回行数

用于分页或仅查看前几条数据。格式：LIMIT 起始偏移量, 行数。

1
2
3
4
5
# 查看前 5 条记录
SELECT * FROM students LIMIT 5;

# 跳过前 5 条，显示接下来的 5 条（常用于分页）
SELECT * FROM students LIMIT 5, 5;

6. 扩展：去重查询

如果想去除重复值，可使用 DISTINCT：

1
SELECT DISTINCT age FROM students;		# 查询表中出现过的年龄，不重复显示。

7. 小结

功能关键字示例
查询所有字段SELECT * FROM 表名;查询整表数据
查询部分字段SELECT 列1,列2 FROM 表名;查询指定列
条件筛选WHEREWHERE age>18
排序ORDER BYORDER BY age DESC
限制行数LIMITLIMIT 10
去重DISTINCTSELECT DISTINCT age
列别名ASname AS 姓名

3. 修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 1. 修改表名
ALTER TABLE 旧表名 RENAME TO 新表名;
ALTER TABLE 旧表名 RENAME AS 新表名;

# 2. 新增字段
ALTER TABLE 表名 ADD [COLUMN] 新增列名 数据类型 [约束条件] [FIRST|AFTER 现有列名];

# 3. 删除字段
ALTER TABLE 表名 DROP [COLUMN] 列名;

# 4. 修改字段类型或属性
ALTER TABLE 表名 MODIFY [COLUMN] 列名 新数据类型 [新约束条件];

# 5. 修改字段名和类型
ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 新数据类型 [新约束条件];

# 6. 修改表的字符集
ALTER TABLE 表名 DEFAULT CHARACTER SET 字符集 COLLATE 校验规则;
ALTER TABLE 表名 CONVERT TO CHARACTER SET 字符集 COLLATE 校验规则;

1. 修改表名

1
alter table students rename to student;	# 将表名 students 改为 student

2. 新增字段

1
2
alter table student add column address varchar(20);
# 使用 add column 在表末尾新增字段，若要插入到指定位置，可加 AFTER 或 FIRST

3. 删除字段

1
2
alter table student drop address;		# 删除指定列
alter table student drop [column] address;

4. 修改字段类型或属性

1
2
3
alter table student modify id bigint;
alter table student modify [column] id bigint;
alter table student modify column id bigint default 18;

5. 修改字段名

1
2
alter table student change age student_age int;
alter table student change column student_age student_age bigint;

6. 修改表的字符集

1
2
alter table student default character set utf8mb4 collate utf8mb4_general_ci;
alter table student convert to character set utf8mb4 collate utf8mb4_general_ci;

说明：

  • ADD：新增列。
  • DROP：删除列。
  • MODIFY：修改列属性或类型。
  • CHANGE：修改列名和类型。
  • RENAME TO：重命名表。
  • DEFAULT CHARACTER SET / CONVERT TO：修改表字符集。

5. 删除表

语法格式：

1
2
DROP TABLE [IF EXISTS] 表名;
drop table students;

说明：

  • IF EXISTS 避免表不存在时报错。
  • 删除表会直接移除表结构和数据（对应 .ibd 文件会被删除）。
  • 删除不可恢复，请谨慎执行。

6. 小结

ALTER 改形状，INSERT 改内容，SELECT 只读不动。

层级操作目标示例命令修改对象是否改变数据内容是否改变表结构说明
库级操作数据库本身CREATE DATABASE db1;数据库目录（文件夹）❌ 否✅ 是创建/删除/修改数据库的元信息和配置
ALTER DATABASE db1 CHARACTER SET utf8mb4;数据库默认字符集❌✅仅影响默认字符集，不动数据
DROP DATABASE db1;数据库及其所有表✅（全删）✅删除整个数据库目录与数据文件
表级操作表定义（结构）CREATE TABLE students(...);表结构定义（列信息）❌✅新建表结构文件（如 .frm/.ibd）
ALTER TABLE students ADD COLUMN address VARCHAR(200);新增字段定义✅（为每行增加 NULL 值）✅改表结构，所有行增加新列空间
ALTER TABLE students DROP COLUMN age;删除字段定义✅（清除该列数据）✅删列即删该列所有数据
ALTER TABLE students MODIFY COLUMN name VARCHAR(100);修改字段属性❌（仅调整结构）✅不动已有数据内容
ALTER TABLE students CHANGE COLUMN name username VARCHAR(100);修改列名及类型❌✅元数据变化，数据保留
ALTER TABLE students RENAME TO student_info;修改表名❌✅表文件名变化，数据不动
DROP TABLE students;整个表✅（全删）✅删除表结构与数据文件
行级操作表中的记录（数据行）INSERT INTO students VALUES(...);新增数据行✅❌向表中插入实际数据
UPDATE students SET age=20 WHERE id=1;更新指定行字段值✅❌修改已有数据内容
DELETE FROM students WHERE id=1;删除行记录✅❌删除指定数据行
SELECT * FROM students;查询结果集❌❌只读操作，不改变任何内容