mysql数据库的基本操作-表与数据类型

作者

MySQL 基本操作——表与数据类型

本文档专门讲 表（Table）的创建、查看、修改、删除 以及 数据类型 的用法，每一步都配有详细说明和大量示例，适合零基础新手跟着做。

目录

  1. “表”与“数据类型”指什么
  2. 操作前：先选择数据库
  3. 创建表的基本语法
  4. 数值类型详解
  5. 字符串类型详解
  6. 日期与时间类型详解
  7. 其他类型简介
  8. 如何选择合适的数据类型
  9. 查看表
  10. 修改表结构
  11. 删除表
  12. 表与列命名建议
  13. 完整示例：从建表到改表
  14. 常见错误与注意点
  15. 命令与类型速查

1. “表”与“数据类型”指什么

1.1 表（Table）

  • 表是数据库里真正存放数据的地方，可以理解成一张 有表头的表格。
  • 表头 = 列名（字段名）；每一行 = 一条记录。
  • 建表时要先定好：有哪些列、每列叫什么、每列是什么类型，之后插入的数据必须符合这些列和类型。

1.2 数据类型（Data Type）

  • 数据类型规定这一列可以存什么：整数、小数、文字、日期等。
  • 例如：年龄用整数（INT），姓名用字符串（VARCHAR），生日用日期（DATE）。
  • 选对类型既能正确存数据，又能节省空间、方便查询和比较。

2. 操作前：先选择数据库

建表、查表、改表都要在 某个数据库 里进行，所以要先 USE 数据库名。

USE school;

然后再执行 CREATE TABLE ...，表就会建在 school 库里。若没选库就建表，会报错：

ERROR 1046 (3D000): No database selected

3. 创建表的基本语法

3.1 基本格式

CREATE TABLE 表名 (
    列名1  数据类型  [可选约束],
    列名2  数据类型  [可选约束],
    ...
);
  • 表名：自己取，如 students、orders。
  • 列名：如 id、name、age。
  • 数据类型：如 INT、VARCHAR(50)、DATE。
  • 可选约束：如 PRIMARY KEY、NOT NULL、DEFAULT（后面会提到）。

示例：最简单的学生表

USE school;

CREATE TABLE students (
    id    INT,
    name  VARCHAR(50),
    age   INT
);

表示创建一张表 students，有三列：id（整数）、name（最多 50 个字符的字符串）、age（整数）。

3.2 带主键和自增（推荐）

通常用一列 id 作为主键，并设为 自增，插入时不用手写 id：

CREATE TABLE students (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(50),
    age   INT
);
  • PRIMARY KEY：该列为主键，唯一、非空。
  • AUTO_INCREMENT：插入时不写 id，自动从 1 递增。

3.3 若表已存在：IF NOT EXISTS

若表已经存在，再执行 CREATE TABLE students (...) 会报错。可以加上 IF NOT EXISTS，存在则跳过：

CREATE TABLE IF NOT EXISTS students (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(50),
    age   INT
);

3.4 指定字符集（可选）

若建库时已用 utf8mb4，表一般会继承；也可在表上单独指定：

CREATE TABLE students (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(50),
    age   INT
) DEFAULT CHARSET=utf8mb4;

4. 数值类型详解

4.1 整数类型

类型 字节 有符号范围（约） 无符号范围（约） 常见用途
TINYINT 1 -128 ~ 127 0 ~ 255 小范围整数
SMALLINT 2 -3万 ~ 3万 0 ~ 6万多 较小整数
MEDIUMINT 3 -800万 ~ 800万 0 ~ 1600多万 中等整数
INT 4 -21亿 ~ 21亿 0 ~ 42亿 最常用整数
BIGINT 8 很大 0 ~ 很大 超大 ID、计数

示例：

-- 年龄：小范围，用 TINYINT 或 INT 都可以
age TINYINT

-- 学号、数量、ID：用 INT
id INT

-- 自增主键、雪花 ID：用 BIGINT
id BIGINT PRIMARY KEY AUTO_INCREMENT

无符号（UNSIGNED）：只存非负整数，范围会变成“0 ~ 最大值”：

age TINYINT UNSIGNED        -- 0~255
stock INT UNSIGNED         -- 库存不为负

显示宽度与 ZEROFILL（了解）：如 INT(5) ZEROFILL 表示不足 5 位前面补 0，MySQL 8.0 已不推荐，知道即可。

4.2 小数类型

DECIMAL(M,D) —— 精确小数，适合金额、分数。

  • M：总位数（整数+小数）。
  • D：小数位数。

示例：

price DECIMAL(10, 2)   -- 共 10 位，小数点后 2 位，如 99999999.99
score DECIMAL(5, 2)    -- 如 100.00、99.50

FLOAT / DOUBLE —— 近似小数，有精度误差，适合科学计算、比例，不适合金额：

rate FLOAT             -- 比例
ratio DOUBLE           -- 高精度近似

建议：金额、分数用 DECIMAL；一般比例用 FLOAT/DOUBLE 即可。

4.3 数值类型示例汇总

CREATE TABLE product (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    price       DECIMAL(10, 2),    -- 价格
    stock       INT UNSIGNED,      -- 库存
    score       DECIMAL(3, 1),     -- 评分 0.0~9.9
    discount    FLOAT              -- 折扣率 0.0~1.0
);

5. 字符串类型详解

5.1 CHAR 与 VARCHAR

类型 特点 长度 适用场景
CHAR(n) 定长，不足补空格 n 个字符 长度固定的编码、性别
VARCHAR(n) 变长，按实际存 最多 n 个字符 姓名、地址、标题等
  • VARCHAR(n)：n 是最大字符数（utf8mb4 下中文、英文都算 1 个字符），超过则报错或截断（看模式）。
  • CHAR(n)：固定占 n 个字符，存不满会补空格，取出来时一般会去掉尾部空格。

示例：

name    VARCHAR(50)     -- 姓名，最多 50 个字符
gender  CHAR(1)         -- 性别：'男'/'女'
code    CHAR(6)         -- 固定 6 位验证码
address VARCHAR(200)    -- 地址

5.2 TEXT 系列（长文本）

类型 最大长度（约） 说明
TINYTEXT 255 字节 很短文本
TEXT 64KB 文章摘要、备注
MEDIUMTEXT 16MB 长文章
LONGTEXT 4GB 超长内容

示例：

intro   TEXT            -- 商品简介、个人简介
content LONGTEXT        -- 文章正文

注意：TEXT 列不能设默认值（除 NULL），一般不能建索引（MySQL 可对前若干字符建前缀索引，进阶再用）。

5.3 字符串类型示例汇总

CREATE TABLE article (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    title   VARCHAR(200),    -- 标题
    author  VARCHAR(50),     -- 作者
    summary TINYTEXT,       -- 摘要
    body    TEXT            -- 正文
);

6. 日期与时间类型详解

6.1 常见类型

类型 格式示例 说明
DATE 2025-02-26 只有日期
TIME 14:30:00 只有时间
DATETIME 2025-02-26 14:30:00 日期+时间，不涉及时区
TIMESTAMP 2025-02-26 14:30:00 日期+时间，存的是 UTC，显示按会话时区
YEAR 2025 只有年份（1 字节或 2 字节）

示例：

birth_date   DATE           -- 生日
login_time   DATETIME       -- 登录时间
created_at   TIMESTAMP      -- 创建时间（常用来“自动填当前时间”）
updated_at   TIMESTAMP      -- 更新时间

6.2 默认当前时间

DATETIME / TIMESTAMP 可以设置“插入时自动填当前时间”：

created_at DATETIME DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  • DEFAULT CURRENT_TIMESTAMP：插入时不写该列，自动用当前时间。
  • ON UPDATE CURRENT_TIMESTAMP：更新该行时，自动把该列改为当前时间（仅 TIMESTAMP 或部分版本 DATETIME 支持）。

示例表：

CREATE TABLE user_log (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    user_id    INT,
    action     VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

7. 其他类型简介

7.1 ENUM（枚举）

列的值只能是给定列表里的一个：

gender ENUM('男', '女', '其他')
status ENUM('待支付', '已支付', '已取消')

插入时只能写 '男'、'女'、'其他' 之一，否则报错或存成空（看严格模式）。

7.2 SET（集合）

列的值可以是给定列表里的多个，用逗号分隔存成一个集合（少用，了解即可）：

tags SET('科技', '教育', '娱乐')

7.3 JSON（MySQL 5.7+）

存 JSON 格式数据，可做简单查询（进阶用）：

extra JSON

7.4 BLOB（二进制）

存二进制数据（如图片、文件流），一般更推荐把文件放磁盘、数据库只存路径。了解即可。

8. 如何选择合适的数据类型

8.1 按业务含义选

含义 推荐类型 示例定义
主键 ID INT 或 BIGINT id INT PRIMARY KEY AUTO_INCREMENT
姓名 VARCHAR(50) name VARCHAR(50)
年龄 TINYINT 或 INT age TINYINT UNSIGNED
金额/价格 DECIMAL(10,2) price DECIMAL(10,2)
数量/库存 INT UNSIGNED stock INT UNSIGNED
标题 VARCHAR(200) title VARCHAR(200)
长文章 TEXT / LONGTEXT content TEXT
生日 DATE birth_date DATE
注册/创建时间 DATETIME 或 TIMESTAMP created_at DATETIME DEFAULT CURRENT_TIMESTAMP
是否、状态 TINYINT(1) 或 ENUM status ENUM('开','关')

8.2 长度与范围

  • 整数：能小就小（省空间），但要留够范围（如年龄 TINYINT 够用，ID 用 INT/BIGINT）。
  • 字符串：按“最大可能长度”设 VARCHAR(n)，不要一律 255。
  • 金额：用 DECIMAL，不要用 FLOAT/DOUBLE。

9. 查看表

9.1 查看当前库下所有表

SHOW TABLES;

示例输出：

+------------------+
| Tables_in_school |
+------------------+
| students         |
| scores           |
| classes          |
+------------------+

9.2 查看表结构（列名、类型、约束等）

DESC 表名;

或：

DESCRIBE 表名;

示例：

DESC students;

示例输出：

+----+-------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+----+-------------+------+-----+---------+----------------+
| id   | int        | NO   | PRI | NULL    | auto_increment |
| name | varchar(50)| YES |     | NULL    |                |
| age  | int        | YES |     | NULL    |                |
+----+-------------+------+-----+---------+----------------+
  • Field：列名。
  • Type：数据类型。
  • Null：是否允许 NULL。
  • Key：是否主键(PRI)、唯一(UNI)等。
  • Default：默认值。
  • Extra：如 auto_increment。

9.3 查看建表语句（完整 SQL）

SHOW CREATE TABLE 表名;

会显示创建该表时的完整 SQL（包括引擎、字符集等），便于复制或对照。

10. 修改表结构

用 ALTER TABLE 在已有表上增加、修改、删除列或改表名。

10.1 添加一列

语法：

ALTER TABLE 表名 ADD [COLUMN] 列名 数据类型 [约束] [位置];

示例：在 students 表末尾加一列 phone

ALTER TABLE students ADD COLUMN phone VARCHAR(20);

在指定位置加列（了解）：

-- 放在第一列
ALTER TABLE students ADD COLUMN no INT FIRST;

-- 放在 name 后面
ALTER TABLE students ADD COLUMN email VARCHAR(100) AFTER name;

10.2 修改列的类型或属性

语法：

ALTER TABLE 表名 MODIFY [COLUMN] 列名 新数据类型 [约束];

示例：把 name 长度改为 100

ALTER TABLE students MODIFY COLUMN name VARCHAR(100);

示例：给 age 设默认值 0

ALTER TABLE students MODIFY COLUMN age INT DEFAULT 0;

10.3 修改列名（或同时改类型）

语法：

ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 数据类型 [约束];

示例：把 phone 改名为 mobile

ALTER TABLE students CHANGE COLUMN phone mobile VARCHAR(20);

注意：CHANGE 必须写新列名和数据类型，即使只改名字也要写一遍类型。

10.4 删除一列

语法：

ALTER TABLE 表名 DROP [COLUMN] 列名;

示例：

ALTER TABLE students DROP COLUMN phone;

删除后该列数据无法恢复，慎用。

10.5 重命名表

语法：

ALTER TABLE 旧表名 RENAME [TO] 新表名;

或：

RENAME TABLE 旧表名 TO 新表名;

示例：

RENAME TABLE students TO student_info;

10.6 修改表字符集

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

11. 删除表

11.1 基本语法

DROP TABLE 表名;

示例：

DROP TABLE demo_table;

会删除整张表（结构和数据），且无法恢复。

11.2 若表不存在：IF EXISTS

DROP TABLE IF EXISTS demo_table;

表不存在也不会报错，适合脚本。

11.3 一次删多张表

DROP TABLE IF EXISTS t1, t2, t3;

12. 表与列命名建议

12.1 表名

  • 小写英文，多个词用下划线：student_info、order_detail。
  • 表名常用复数表示“多行”：students、orders。
  • 见名知意：users、products、scores。

12.2 列名

  • 小写英文，下划线分隔：user_name、created_at。
  • 主键常用 id；创建时间常用 created_at，更新时间 updated_at。

12.3 避免使用的

  • 用 MySQL 保留字做表名/列名（如 order、group），若要用需加反引号：`order`。
  • 中文、空格、横线（除非反引号包起来，不推荐）。

13. 完整示例：从建表到改表

下面在同一库里：建表 → 查看 → 改结构 → 再查看 → 删表，方便你整段复制练习。

USE school;

-- 1. 创建表
CREATE TABLE IF NOT EXISTS product (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL,
    price       DECIMAL(10, 2) NOT NULL,
    stock       INT UNSIGNED DEFAULT 0,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. 查看表列表
SHOW TABLES;

-- 3. 查看表结构
DESC product;

-- 4. 添加一列
ALTER TABLE product ADD COLUMN category VARCHAR(50);

-- 5. 修改列
ALTER TABLE product MODIFY COLUMN name VARCHAR(200);

-- 6. 查看结构确认
DESC product;

-- 7. 删除列（慎用）
ALTER TABLE product DROP COLUMN category;

-- 8. 重命名表
RENAME TABLE product TO product_info;

SHOW TABLES;

-- 9. 不需要时删除表（慎用）
-- DROP TABLE IF EXISTS product_info;

14. 常见错误与注意点

14.1 未选择数据库

报错：No database selected。
解决：先 USE 数据库名; 再建表。

14.2 表已存在

报错：Table 'xxx' already exists。
解决：换表名，或用 CREATE TABLE IF NOT EXISTS ...。

14.3 列名或表名用了保留字

如用 order 做表名会报错，可改为 orders，或加反引号：`order`。

14.4 字符串、日期没加引号

插入时字符串和日期要用单引号，数字不用：

-- 正确
INSERT INTO students (name, age) VALUES ('张三', 18);

-- 错误（姓名会被当成列名或报错）
INSERT INTO students (name, age) VALUES (张三, 18);

14.5 修改/删除列前先确认

ALTER TABLE ... DROP COLUMN 和 MODIFY 会立刻生效，数据可能丢失或类型不兼容，建议先在测试库试或备份。

15. 命令与类型速查

15.1 表操作命令

操作 命令
创建表 CREATE TABLE 表名 (列名 类型, ...);
查看所有表 SHOW TABLES;
查看表结构 DESC 表名; 或 DESCRIBE 表名;
查看建表语句 SHOW CREATE TABLE 表名;
添加列 ALTER TABLE 表名 ADD COLUMN 列名 类型;
修改列类型 ALTER TABLE 表名 MODIFY COLUMN 列名 新类型;
改列名 ALTER TABLE 表名 CHANGE COLUMN 旧名 新名 类型;
删除列 ALTER TABLE 表名 DROP COLUMN 列名;
重命名表 RENAME TABLE 旧名 TO 新名;
删除表 DROP TABLE 表名; 或 DROP TABLE IF EXISTS 表名;

15.2 常用数据类型速查

用途 类型示例
主键 ID INT / BIGINT + AUTO_INCREMENT
短整数 TINYINT、SMALLINT、INT
金额/分数 DECIMAL(10,2)、DECIMAL(5,2)
姓名/标题 VARCHAR(50)、VARCHAR(200)
长文本 TEXT、LONGTEXT
日期 DATE
日期时间 DATETIME、TIMESTAMP
默认当前时间 DATETIME DEFAULT CURRENT_TIMESTAMP

把「表」和「数据类型」练熟后，就可以在表里做增删改查（INSERT、SELECT、UPDATE、DELETE）了。建议在本地按第 13 节完整跑一遍，再自己设计一两张表（如订单表、用户表）练手。

访问数: 11

发表评论