MySQL数据库和表的基本操作（超详细图文版）

接触MySQL时，不少人会被“数据库”“数据表”“SQL语句”等概念困扰。其实MySQL的核心操作并不复杂，就像日常使用Excel一样——数据库相当于Excel文件，数据表相当于Excel里的工作表，掌握对它们的增删改查，就迈出了MySQL学习的第一步！

本文结合《MySQL数据库入门（第2版）》的核心知识点，用通俗的语言+实操案例，带大家吃透数据库和表的基本操作，看完可直接上手练习。

一、学习目标

开始学习前，明确核心目标，避免盲目学习：

• 掌握数据库的增删改查操作，能独立创建、查看、修改和删除数据库；

• 掌握数据表的增删改查操作，能创建数据表、查看表结构、修改表结构、删除数据表；

• 熟悉MySQL常见数据类型，明确不同数据的存储类型选择；

• 掌握表的4种核心约束（非空、唯一、主键、默认值），保证数据的完整性；

• 学会设置字段自动增长，解决手动输入唯一ID的麻烦。

二、核心概念类比（快速理解）

理解“数据库”和“数据表”的关系，是掌握后续操作的基础，用以下类比可快速入门：

• MySQL中的「数据库」= Excel文件（比如“员工信息.xlsx”）；

• MySQL中的「数据表」= Excel文件里的「工作表（Sheet）」（比如“销售部员工”“技术部员工”）；

• 数据表中的「字段」= Excel工作表的「列」（比如“姓名”“工号”“年龄”）；

• 数据表中的「记录」= Excel工作表的「行」（比如一行员工的完整信息）。

简单来说：先创建数据库（Excel文件），再在数据库里创建数据表（工作表），最后在数据表中存储数据（行和列），逻辑和用Excel存数据完全一致。

三、数据库的基本操作（重中之重）

MySQL安装完成后，首要操作就是数据库管理——数据表必须依赖数据库存在。所有操作均通过SQL语句实现，记住固定语法即可直接复制修改使用。

3.1 创建数据库（最常用）

创建数据库，即在MySQL中新建一个“数据存储容器”，语法格式如下（括号内为可选项，可先掌握基础用法）：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option];

关键说明：

• DATABASE 和 SCHEMA 功能完全一致，日常使用记 CREATE DATABASE 即可；

• IF NOT EXISTS：可选参数，作用是“若该数据库不存在，则创建”，避免重复创建导致报错；

• db_name：需创建的数据库名称，如“itcast”“bms”，不可与现有数据库重名。

实操案例（可直接复制执行）：

-- 创建名为itcast的数据库（基础用法）
CREATE DATABASE itcast;

-- 推荐用法：加上IF NOT EXISTS，避免报错
CREATE DATABASE IF NOT EXISTS itcast;

⚠️ 注意：若直接执行 CREATE DATABASE itcast; 且该数据库已存在，会报错：ERROR 1007 (HY000): Can’t create database itcast; database exists

3.2 查看数据库

创建数据库后，常需查看“当前MySQL中的所有数据库”或“某个数据库的创建配置”，以下是两种常用语句：

（1）查看所有数据库

SHOW {DATABASES | SCHEMAS};

实操结果示例（执行后会显示所有数据库）：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |  -- MySQL系统自带数据库
| itcast             |  -- 自定义创建的数据库
| mysql              |  -- MySQL系统自带数据库
| performance_schema |  -- MySQL系统自带数据库
| sys                |  -- MySQL系统自带数据库
+--------------------+
5 rows in set (0.03 sec)

（2）查看某个数据库的创建语句

用于验证数据库配置（如字符集），语法：

SHOW CREATE DATABASE db_name;

实操案例（查看itcast数据库的创建信息）：

mysql> SHOW CREATE DATABASE itcast;
+----------+-----------------------------------------------------------+
| Database | Create Database                                           |
+----------+-----------------------------------------------------------+
| itcast   | CREATE DATABASE `itcast` 
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ 
/*!80016 DEFAULT ENCRYPTION='N' */                                     |
+----------+-----------------------------------------------------------+
1 row in set (0.00 sec)

3.3 选择数据库

创建数据库后不会自动进入该数据库，如同新建Excel文件后需打开才能编辑工作表。因此，操作数据表前，必须先选择目标数据库：

USE 数据库名;

实操案例（选择itcast数据库）：

mysql> USE itcast;
Database changed  -- 提示“数据库已切换”，表示操作成功

⚠️ 注意：若不选择数据库直接创建数据表，会报错：No database selected

查看当前选择的数据库（可选）：

SELECT DATABASE();

3.4 修改数据库特征（少用，但需了解）

数据库创建后，可修改其字符集、校对集等特征，语法：

ALTER {DATABASE | SCHEMA} [db_name] alter_option;

最常用场景：修改数据库字符集（如将itcast的字符集改为gbk）：

ALTER DATABASE itcast DEFAULT CHARACTER SET gbk;

修改后，可通过 SHOW CREATE DATABASE itcast; 验证修改结果。

3.5 删除数据库（谨慎操作！）

删除数据库会彻底清除该数据库下的所有数据表及数据，且无法恢复，操作前需仔细确认。

DROP DATABASE [IF EXISTS] db_name;

实操案例（删除itcast数据库）：

-- 推荐用法：加上IF EXISTS，避免删除不存在的数据库报错
DROP DATABASE IF EXISTS itcast;

删除后，执行 SHOW DATABASES; 可确认itcast数据库已移除。

四、MySQL常见数据类型

创建数据表时，需为每个字段指定数据类型——如同Excel中设置单元格格式（文本、数字、日期），MySQL通过指定数据类型避免存储错误。

重点掌握3大类数据类型，可满足日常开发需求：

4.1 数值类型（存储数字）

用于存储工资、年龄、工号等数字类数据，核心类型：

• 整数类型：INT（最常用，适用于工号、年龄等整数场景）；

• 浮点类型：FLOAT（单精度）、DOUBLE（双精度，适用于工资 5000.50 等带小数的场景）；

• 定点数类型：DECIMAL（精度高，适合金额类数据，如 DECIMAL(7,2) 表示最多7位数字，保留2位小数）。

4.2 日期和时间类型（存储时间）

用于存储注册时间、上架时间等时间类数据，核心类型：

• DATE：存储日期（年月日，如 2026-04-01）；

• TIME：存储时间（时分秒，如 14:30:00）；

• DATETIME：存储日期+时间（最常用，如 2026-04-01 14:30:00）；

• YEAR：存储年份（如 2026）。

4.3 字符串类型（存储文本）

用于存储姓名、地址、邮箱等文本类数据，核心类型：

• CHAR：固定长度字符串（如手机号 11位，可用 CHAR(11)）；

• VARCHAR：可变长度字符串（最常用，适用于姓名、邮箱等，如 VARCHAR(20) 表示最多20个字符）；

• TEXT：大文本数据（适用于文章内容、备注等长度较长的文本）。

五、数据表的基本操作（核心中的核心）

选择目标数据库后，即可创建数据表——相当于在Excel文件中新建工作表，重点掌握“创建、查看、修改、删除”四大核心操作。

5.1 创建数据表

语法格式（基础版，约束部分后续详细说明）：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name(
    字段名1 数据类型1 [列级约束1],
    字段名2 数据类型2 [列级约束2],
    ...
    [,表级约束]
);

关键说明：

• tbl_name：数据表名称，如“tb_dept”（部门表）、“tb_emp”（员工表）；

• 字段名+数据类型：必填项，如“deptno INT”（部门编号，整数类型）；

• IF NOT EXISTS：可选参数，避免重复创建数据表报错。

实操案例（在itcast数据库中创建部门表tb_dept）：

-- 先选择数据库
USE itcast;

-- 创建部门表
CREATE TABLE IF NOT EXISTS tb_dept(
    deptno INT,  -- 部门编号
    dname VARCHAR(14),  -- 部门名称
    loc VARCHAR(13)  -- 部门地址
);

5.2 查看数据表

创建数据表后，需确认表是否创建成功、表结构是否符合预期，以下3种常用语句，重点掌握前2种：

（1）查看当前数据库中的所有数据表

SHOW TABLES;

实操结果示例：

mysql> SHOW TABLES;
+---------------+
| Tables_in_ems |
+---------------+
| tb_dept       |  -- 自定义创建的部门表
+---------------+
1 row in set (0.01 sec)

（2）查看数据表的创建语句

SHOW CREATE TABLE tbl_name;

实操案例（查看tb_dept的创建信息）：

mysql> SHOW CREATE TABLE tb_dept;
+---------+--------------------------------------------------------+
| Table   | Create Table                                           |
+---------+--------------------------------------------------------+
| tb_dept | CREATE TABLE `tb_dept` (
  `deptno` int DEFAULT NULL,
  `dname` varchar(14) DEFAULT NULL,
  `loc` varchar(13) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+--------------------------------------------------------+
1 row in set (0.00 sec)

（3）查看数据表结构（最常用）

用于查看数据表的字段、数据类型、约束等信息，语法：

DESCRIBE 数据表名;  -- 简写：DESC 数据表名;

实操案例（查看tb_dept的表结构）：

mysql> DESC tb_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int         | YES  |     | NULL    |       |  -- 字段名：部门编号，类型：INT，允许为空，无默认值
| dname  | varchar(14) | YES  |     | NULL    |       |  -- 字段名：部门名称，类型：VARCHAR(14)
| loc    | varchar(13) | YES  |     | NULL    |       |  -- 字段名：部门地址，类型：VARCHAR(13)
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

字段说明：

• Field：字段名称（列名）；

• Type：数据类型；

• Null：是否允许存储NULL值（YES=允许，NO=不允许）；

• Key：是否建立索引（后续章节讲解）；

• Default：字段的默认值；

• Extra：附加信息（如自动增长）。

5.3 修改数据表（常用操作）

数据表创建后，可根据需求修改表名、字段名、数据类型，或添加/删除字段，核心语法：

ALTER TABLE tbl_name [alter_option, ...];

以下是5种常用修改场景，可直接套用案例：

（1）修改数据表名

-- 将tb_dept改为dept
ALTER TABLE tb_dept RENAME TO dept;

（2）修改字段名

两种修改方式，优先使用第一种（仅修改字段名）：

-- 方式1：仅修改字段名（推荐）
ALTER TABLE dept RENAME COLUMN loc TO local_name;

-- 方式2：修改字段名+数据类型（灵活适配需求）
ALTER TABLE dept CHANGE local_name local_name CHAR(20);

（3）修改字段数据类型

-- 将dname的类型从VARCHAR(14)改为CHAR(16)
ALTER TABLE dept MODIFY dname CHAR(16);

（4）添加字段

-- 在dept表的第一列添加id字段（INT类型）
ALTER TABLE dept ADD id INT FIRST;

-- 在dname字段后面添加age字段（INT类型）
ALTER TABLE dept ADD age INT AFTER dname;

（5）删除字段

-- 删除dept表中的id字段
ALTER TABLE dept DROP id;

5.4 删除数据表（谨慎操作！）

删除数据表会彻底清除表结构及表中所有数据，且无法恢复，语法：

DROP TABLE [IF EXISTS] tbl_name;

实操案例（删除dept表）：

DROP TABLE IF EXISTS dept;

六、表的约束（保证数据正确性）

约束是MySQL中数据的“规则”，如“姓名不能为空”“工号不能重复”，目的是避免插入错误数据（如姓名留空、重复插入同一工号）。重点掌握4种核心约束，外键约束将在后续章节讲解。

6.1 非空约束（NOT NULL）

作用：指定字段不能存储NULL值（必须填写内容），适用于姓名、部门地址等必填字段。

用法（两种场景）：

-- 1. 创建表时设置非空约束
CREATE TABLE tb_dept01(
    deptno INT,
    dname VARCHAR(14),
    local VARCHAR(13) NOT NULL  -- local字段不能为NULL
);

-- 2. 修改表时添加非空约束
ALTER TABLE tb_dept01 MODIFY dname VARCHAR(14) NOT NULL;

-- 删除非空约束（重新定义字段，去掉NOT NULL）
ALTER TABLE tb_dept01 CHANGE dname dname VARCHAR(14);

6.2 唯一约束（UNIQUE）

作用：指定字段的值不能重复，适用于工号、邮箱等需唯一标识的字段。

用法（两种场景）：

-- 1. 创建表时设置唯一约束（列级+表级）
CREATE TABLE tb_emp01(
    empno INT UNIQUE,  -- 列级约束：工号唯一
    ename VARCHAR(16),
    deptname VARCHAR(16),
    UNIQUE (deptname, ename)  -- 表级约束：同一部门不能有相同姓名
);

-- 2. 修改表时添加唯一约束
ALTER TABLE tb_emp01 ADD UNIQUE(email);

-- 删除唯一约束（通过索引名删除，索引名默认和字段名一致）
ALTER TABLE tb_emp01 DROP INDEX empno;

6.3 主键约束（PRIMARY KEY）

作用：非空约束+唯一约束的组合，字段值既不能为NULL，也不能重复，用于唯一标识一条记录（如用户ID、图书ID）。

注意：一个数据表只能有一个主键。

用法（两种场景）：

-- 1. 创建表时设置主键约束
CREATE TABLE tb_dept02(
    id INT PRIMARY KEY,  -- 主键约束：id既不能空，也不能重复
    dname VARCHAR(14) UNIQUE,
    local VARCHAR(13) NOT NULL
);

-- 2. 修改表时添加主键约束
ALTER TABLE tb_dept01 ADD PRIMARY KEY(deptno);

-- 删除主键约束（无需指定字段名，因一个表仅一个主键）
ALTER TABLE tb_dept01 DROP PRIMARY KEY;

6.4 默认值约束（DEFAULT）

作用：指定字段的默认值，插入数据时若不填写该字段，MySQL会自动填入默认值（如用户状态默认启用）。

用法（两种场景）：

-- 1. 创建表时设置默认值约束
CREATE TABLE tb_emp02(
    id INT PRIMARY KEY,
    ename VARCHAR(16) UNIQUE,
    status INT DEFAULT 1  -- 默认值为1（如1表示启用）
);

-- 2. 修改表时添加默认值约束
ALTER TABLE tb_emp02 MODIFY sal DECIMAL(7,2) DEFAULT 0.00;

-- 删除默认值约束（重新定义字段，去掉DEFAULT）
ALTER TABLE tb_emp02 CHANGE sal sal DECIMAL(7,2);

七、字段自动增长（AUTO_INCREMENT，实用技巧）

手动输入唯一ID（如用户ID、图书ID）时，易出现重复或遗漏问题。

解决方案：给主键字段设置自动增长，MySQL会为每一条新记录自动生成唯一ID，从1开始依次递增。

用法及注意事项

-- 语法：字段名 数据类型 AUTO_INCREMENT;
CREATE TABLE tb_emp03(
    empno INT PRIMARY KEY AUTO_INCREMENT,  -- 主键+自动增长
    deptname VARCHAR(14) NOT NULL,
    job VARCHAR(13)
);

4个关键注意点：

• 一个数据表只能有一个自动增长字段；

• 自动增长字段必须是“键”（主键、唯一约束均可，通常搭配主键使用）；

• 插入数据时，自动增长字段可填NULL，MySQL会自动生成递增的值；

• 用DELETE删除记录后，自动增长的值不会“回退”（如删除ID=3的记录，下次插入仍为ID=4）。

八、上机实践（巩固知识点）

理论学习后，需通过实操巩固知识点。以下以“图书管理系统”为例，完成数据库和数据表的创建，掌握上述所有核心操作。

实践需求

1. 创建名称为bms的数据库，字符集设为utf8mb4；

2. 在bms数据库中创建3张表：user（用户表）、book（图书表）、record（借阅记录表）；

3. 查看book表的结构信息。

动手实操（可直接复制执行）

-- 1. 创建bms数据库（指定字符集）
CREATE DATABASE bms CHARACTER SET utf8mb4;

-- 2. 选择bms数据库
USE bms;

-- 3. 创建user表（用户表）
CREATE TABLE user(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键+自动增长（用户编号）
    name VARCHAR(20) NOT NULL UNIQUE,  -- 非空+唯一（用户名）
    state CHAR(1) NOT NULL DEFAULT '0'  -- 非空+默认值（0=启用，1=禁用）
);

-- 4. 创建book表（图书表）
CREATE TABLE book(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键+自动增长（图书编号）
    name VARCHAR(20) NOT NULL UNIQUE,  -- 非空+唯一（图书名称）
    price DECIMAL(6,2) NOT NULL,  -- 非空（图书价格）
    upload_time DATETIME NOT NULL,  -- 非空（上架时间）
    borrower_id INT,  -- 借阅人编号（可选）
    borrow_time DATETIME,  -- 借阅时间（可选）
    state CHAR(1) NOT NULL DEFAULT '0'  -- 非空+默认值（0=可借阅）
);

-- 5. 创建record表（借阅记录表）
CREATE TABLE record(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键+自动增长（记录编号）
    book_id INT NOT NULL,  -- 非空（图书编号）
    borrower_id INT NOT NULL,  -- 非空（借阅人编号）
    borrow_time DATETIME NOT NULL,  -- 非空（借阅时间）
    remand_time DATETIME NOT NULL  -- 非空（归还时间）
);

-- 6. 查看book表的结构
DESC book;

执行完以上语句，即可完成一个简单图书管理系统的数据库设计，建议实际操作验证效果。

九、总结

本章核心是数据库和数据表的基本操作，掌握以下3个核心逻辑，即可快速上手：

1. 先有数据库，再有数据表（先建Excel文件，再建工作表）；

2. 操作数据表前，必须先选择数据库（打开Excel文件才能编辑工作表）；

3. 约束和自动增长，用于规范数据格式、提升操作效率（避免错误数据，减少手动输入）。

学习时可先记住语法格式，复制案例执行验证，成功后再修改参数练习，熟练后即可灵活运用。下一章将讲解数据表中数据的增删改查操作，进一步提升MySQL使用能力。