MySQL数据库基础操作：增删查改、表克隆与外键约束

山间有风

发布时间：2026-10-16| 2人浏览过

原创

本文详细总结了MySQL数据库的核心操作，涵盖常用字段类型、数据库与表的创建删除。通过实例演示了使用INSERT、SELECT、UPDATE、DELETE进行数据的增删查改，并介绍了ALTER命令修改表结构。此外，还解析了LIKE法与SELECT克隆表的差异、临时表特性以及主从表的外键约束建立与维护方法。

准备环境与远程连接授权

在进行数据库操作前，需确保能够连接到 MySQL 服务。通常可使用 Navicat 等可视化工具或 Linux 命令行终端。

Linux 本地登录：在终端输入 mysql -u root -p密码 即可进入交互环境。

开启远程访问：若需通过 Navicat 连接 Linux 服务器上的 MySQL，需在数据库中执行授权命令。例如授予 root 用户在任意终端以密码 123456 登录所有库和表：

grant all privileges on *.* to 'root'@'%' identified by '123456';
flush privileges; -- 刷新权限生效

查看数据库基本信息

登录成功后，可按以下步骤查看当前环境信息：

# 1. 查看所有数据库
SHOW DATABASES;

# 2. 切换至指定数据库（注意大小写）
USE 数据库名;

# 3. 查看当前库下的表
SHOW TABLES;

# 4. 查看表结构
DESCRIBE 表名;  # 简写为 DESC 表名

# 5. 查看建表语句
SHOW CREATE TABLE 表名;

# 6. 查询结果垂直展示（适合宽表）
SELECT * FROM USER_INFO\G;

# 7. 查看当前所在库
SELECT database();

SQL 语言分类与字段类型

SQL 分为四大类：DDL（定义）、DML（操纵）、DQL（查询）和 DCL（控制），分别对应创建对象、管理数据、查找记录和设置权限等功能。

常用数据类型

整型： int 默认为 11 位显示宽度。
浮点型： float（单精度，32位）和 double（双精度，64位）。精确度可用 decimal(总位数,小数位) 指定，如 decimal(6,2)。
字符型：
- char(n)：定长，读取快但耗空间。不足补空格，超出截取（低版本）或报错（高版本），最大 255 字符。
- varchar(n)：变长，节省空间。MySQL 5.0+ 中括号内参数代表字符数；4.0- 版本代表字节数。
其他： text 用于大文本，image 常用于图片存储。

第1步：创建与删除数据库及表

涉及删除操作时，请务必提前备份数据。

创建数据库：

CREATE DATABASE SCHOOL;

创建表：包含字段定义、类型及主键声明。

CREATE TABLE STUDENT (
  ID int(10) NOT NULL,
  NAME varchar(20),
  AGE int(3),
  SEX char(2),
  PRIMARY KEY (ID)
);

删除库或表：

DROP TABLE [数据库名.]表名;
DROP DATABASE 数据库名;

第2步：数据表内容增删查改（CRUD）

以下操作基于新建的 STARBUCKS 库中的 USER_INFO 表进行演示。

CREATE DATABASE STARBUCKS;
USE STARBUCKS;

CREATE TABLE `USER_INFO` (
  `ID` int(32) NOT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  `TYPE` varchar(32) DEFAULT NULL,
  `ADD_TIME` varchar(20) DEFAULT NULL,
  `IS_USED` char(5) DEFAULT NULL,
  PRIMARY KEY (`ID`)
);

添加记录（Insert）

支持逐条插入或多行合并插入：

-- 逐条全字段插入
INSERT INTO USER_INFO(ID,NAME,TYPE,ADD_TIME,IS_USED) VALUES (1001, '咖啡机', '机器齐全', '2021-06-22', 'D0001');
INSERT INTO USER_INFO VALUES (1002, '奶茶机', '设备', '2021-06-23', 'D0001');

-- 批量插入
INSERT INTO USER_INFO (ID,NAME,TYPE,ADD_TIME,IS_USED) 
VALUES (1003, '牛奶', '食品', '2021-06-24', 'D0002'),
       (1004, '包装机', '设备', '2021-06-20', 'D0001'),
       (1005, '糖块', '食品', '2021-06-25', 'D0001');

查询记录（Select）

使用 WHERE 限制条件，使用 LIMIT 分页：

-- 获取前N行
SELECT * FROM USER_INFO LIMIT 3;
-- 跳过前N行，取M行
SELECT * FROM USER_INFO LIMIT 3, 5;

更新记录（Update）

注意：未加 WHERE 条件会更新表中所有数据。

UPDATE USER_INFO SET TYPE='仪器' WHERE ID=1001;

删除记录（Delete）

注意：同样需要 WHERE 锁定范围，否则清空全表。

DELETE FROM USER_INFO WHERE ID=1001;

第3步：修改表结构与约束

利用 ALTER TABLE 命令进行元数据层面的修改。

重命名与新增字段

-- 表名重命名
ALTER TABLE USER_INFO RENAME USER_MESG;

-- 新增字段并设默认值
ALTER TABLE USER_MESG ADD STATUS VARCHAR(20) DEFAULT '在用';

删除字段与列名修改

-- 删除某列
ALTER TABLE USER_MESG DROP STATUS;

-- 修改列名并增加唯一键约束
ALTER TABLE USER_MESG CHANGE NAME DEV_NAME VARCHAR(30) UNIQUE KEY;

唯一键约束允许有空值，但不能出现重复值。

第4步：索引管理与自增特性

索引查看与管理

可以通过 SHOW INDEXES FROM 表名; 查看索引详情。

若需手动控制唯一索引的增删：

-- 增加唯一索引
ALTER TABLE USER_MESG ADD UNIQUE INDEX (DEV_NAME);

-- 删除唯一索引（需先知道索引名Key_name）
ALTER TABLE USER_MESG DROP INDEX 索引名;

自增与零填充

在建表时可结合 AUTO_INCREMENT 实现主键自增，配合 ZEROFILL 实现不足位数的补零效果：

CREATE TABLE AREA_INFO (
  AREA_ID int(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,
  AREA_NAME VARCHAR(20) NOT NULL UNIQUE KEY,
  IS_USED CHAR(5) NOT NULL
);

第5步：表克隆技巧

当需要将现有表的数据结构或数据复制到新表时，有两种方式：

复制结构+数据（推荐）：使用 LIKE 克隆表头，再用 INSERT INTO ... SELECT 导入数据。此法能保留主键、索引和约束。

CREATE TABLE AREA_CLONE1 LIKE AREA_INFO;
INSERT INTO AREA_CLONE1 SELECT * FROM AREA_INFO;

仅克隆数据：直接根据查询结果建表，语法简洁但丢失原表结构配置。

CREATE TABLE 克隆表名(SELECT * FROM 源表名);

清空表与临时表

清空表对比：
- DELETE FROM 表名：逐行删除，返回受影响行数。若有自增字段，再次插入 ID 会从断点处继续。
- TRUNCATE TABLE 表名：重置表结构，速度更快。自增 ID 重新从 1 开始。

临时表：使用 CREATE TEMPORARY TABLE 创建的表仅在会话期间存在，断开连接后自动销毁，且对同库其他用户不可见。

第6步：外键约束维护

为保证数据一致性，建立主表与从表的关联。

建表：从表关联字段需与主表主键类型一致。
添加外键：使用 FOREIGN KEY ... REFERENCES 绑定。
插入逻辑：必须先往主表插数据，再从表插数据。
删除逻辑：必须先删从表，再删主表；或使用外键级联操作。

-- 建主表
CREATE TABLE HOBBY (HOBID int(4), HOBNAME VARCHAR(50));
-- 建从表
CREATE TABLE HOBBY_DETAIL (ID int(4) PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), AGE int(3), HOBID int(4));

-- 设主键
ALTER TABLE HOBBY ADD CONSTRAINT PK_HOBID PRIMARY KEY (HOBID);
-- 设外键
ALTER TABLE HOBBY_DETAIL ADD CONSTRAINT FK_HOB FOREIGN KEY (HOBID) REFERENCES HOBBY (HOBID);

-- 插入顺序
INSERT INTO HOBBY VALUES(1,'Football');
INSERT INTO HOBBY_DETAIL VALUES(1,'Messi',32,1);