MySQL基础详解

MySQL基础详解

一、数据库基础操作详解

1.1 数据库的概念与重要性

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。在现代应用开发中，数据库作为数据存储的核心，承载着系统的所有业务数据，是企业的核心资产。MySQL作为目前最流行的关系型数据库管理系统之一，其轻量、高效、开源的特性使其广泛应用于各类应用场景，从个人博客到大型企业系统均能见到其身影。

1.2 查看数据库相关操作

查看所有数据库

查看当前MySQL服务中存在的所有数据库，使用如下命令：

SHOW DATABASES;

该命令会返回一个包含所有数据库名称的列表，包括MySQL自带的系统数据库（如information_schema、mysql、performance_schema等）。

查看当前使用的数据库

在操作过程中，经常需要确认当前正在操作的数据库，可通过以下命令实现：

SELECT DATABASE();

这是MySQL提供的内置函数，若未选择任何数据库，会返回NULL。

查看数据库编码集配置

数据库的编码集直接影响数据存储的准确性（尤其是中文等特殊字符），查看当前数据库服务的默认编码集配置：

SHOW VARIABLES LIKE '%character%';

该命令会显示与字符集相关的所有系统变量，包括客户端编码、连接编码、数据库编码等。

1.3 数据库的创建

特殊数据库名的处理

若数据库名称为MySQL关键字（如create、database、user等），直接使用会导致语法错误。此时需用反引号（`）包裹名称：

CREATE DATABASE `create`; -- 正确写法

反引号的作用是告诉MySQL解释器，内部的内容作为标识符处理而非关键字。

1.4 数据库的使用与切换

切换数据库

对数据库进行操作前，需先指定要操作的数据库，使用USE命令：

USE db_name; -- 分号可省略

例如，切换到my_db数据库：

USE my_db

在这里插入图片描述

验证切换结果

切换后，可通过SELECT DATABASE();命令验证当前数据库是否正确切换。

在这里插入图片描述

1.5 数据库的删除

删除语法

删除数据库的语法如下：

DROP DATABASE [IF EXISTS] db_name;

• IF EXISTS：可选参数，避免删除不存在的数据库时出现错误

危险提示与操作规范

删除数据库是极高风险的操作，执行后数据库内的所有表和数据将被彻底删除，且难以恢复。在实际开发中需遵循以下规范：

1. 执行前必须确认操作的必要性，并获得相关负责人审批（有领导背书）
2. 操作前务必做好数据备份
3. 生产环境中，建议多人在场共同确认操作
4. 避免在高峰期执行删除操作

数据恢复机制

虽然删除操作风险高，但MySQL提供了一定的恢复手段：

• 日志恢复：MySQL的二进制日志（binlog）记录了所有数据修改操作，可通过重放日志恢复数据
• 定期备份：建议采用"增量备份+全量备份"结合的策略（如每日增量备份，每周全量备份） 数据库也是定期备份，每天备份一次（增量备份，记录是今天比昨天新增的数据），每周备份一次，全量备份
• 集群部署：生产环境通常采用集群架构，单节点数据丢失可从其他节点恢复 在公司中的生产环境中，一般都是以集群的形式部署的，如果说只删了其中一台机器，其他机器上的日志还是可以用的

1.6 数据库客户端辅助操作

查看帮助信息

在MySQL客户端中，可通过以下命令查看帮助：

\?

该命令会显示MySQL客户端支持的所有命令及说明。

在这里插入图片描述

退出客户端

退出MySQL客户端的命令：

\q

在这里插入图片描述

也可使用exit或quit这两个命令实现相同的退出功能。

二、MySQL数据类型详解

数据类型是数据库表设计的基础，合理选择数据类型不仅能节省存储空间，还能提高查询效率和数据准确性。MySQL提供了丰富的数据类型，可分为数值类型、字符串类型、日期类型三大类。

2.1 数值类型

整型类型

整型类型适用于存储整数数据，MySQL提供了多种不同长度的整型，以满足不同范围的存储需求：

使用建议：

• 尽量不使用unsigned（无符号）属性，当数据可能超出有符号范围时，应升级数据类型（如INT不够用则换BIGINT）
• TINYINT可用于表示bool类型（0表示false，1表示true），等同于BIT(1)

浮点类型

浮点类型用于存储带小数的数据，分为精确型和近似型两类：

关键特性：

• M表示总位数（不包含小数点和符号），D表示小数位数
• 若省略D，默认D=0；若省略M，默认M=10
• DECIMAL通过字符串存储实现精确计算，无精度丢失，是金额存储的首选（如订单金额、账户余额）decimal是真正意义上的双精度

decimal的注意： 在有些系统中表示金额，一般把元为单位换成以分为单位，然后用int类型去表示金额 decimal == numeric，两者都等于Java中的BigDecimal M中不计算小数点和负数的，如果D为0，则具体的值不保存小数点和小数部分（也就是说保存的是一个整数） decimal的最大位数（M）为65，支持最大的小数（D）为30 如果省略D，则默认为0，如果省略M，则默认为10

总结：我们只要知道decimal可以保存很大的数就可以了，根据自己项目中的要求去设置小数精度

示例：

-- 存储最大9999.99的金额（总长度6，小数2位）
price DECIMAL(6,2)

2.2 字符串类型

字符串类型用于存储文本数据，MySQL提供了多种灵活的类型选择：

VARCHAR深度解析：

• SIZE表示字符数（而非字节），在utf8mb4编码下，每个字符占1~4字节
• 最大可存储字符数计算：65535字节 ÷ 4字节/字符 = 16383字符
• 实际存储时，会额外占用1字节（长度≤255）或2字节（长度>255）记录数据长度
• 建议根据实际需求设置合理长度（如用户名设为VARCHAR(50)，邮箱设为VARCHAR(100)）

使用建议：

• 避免使用TEXT存储频繁查询的短文本（影响性能）
• 数据库中不建议存储大文件（如图片、视频），应将文件存储在磁盘，表中仅保存文件路径
• BLOB适用于存储小型二进制数据（如验证码图片），其中存放的是byte[]数组，也就是一个文件的二进制形式，但需注意数据库容量增长

txt：是Windows系统中文本文件的默认后缀名 文本文件：人类可以直接看懂的文件，比如记事本里面写的日记，配置文件，Java源码 二进制文件：我们用文本编辑器打开的文件，里面是一堆乱码，这些文件只有机器可以读得懂，一般称之为二进制文件

注意： 一般在数据库中，不建议存文件，而是把文件存在磁盘上，在数据库相应的字段中保存文件路径，在查询文件的时候，先从数据库中找到文件路径，再去根据文件路径在磁盘上加载或是返回具体的真实文件

2.3 日期与时间类型

日期时间类型用于存储时间相关数据，MySQL提供了多种精度的类型：

关键对比：

• DATETIME与TIMESTAMP：前者范围更大，后者受时区影响（适合跨国应用）
• TIMESTAMP的2038年问题：由于其基于32位整数存储，代表从1970年开始，而且MySQL 是以毫秒为单位去计时的，超4字节就范围会取值，所以那么在2038年后会溢出，长期系统建议使用DATETIME
• 日期存储格式：MySQL默认以字符串形式显示（如2023-10-01 15:30:00），但内部以二进制存储

使用场景：

• 生日：DATE（仅需年月日）
• 订单创建时间：DATETIME（需完整时间且不受时区影响）
• 日志记录：TIMESTAMP（自动记录当前时区时间）

三、数据表操作全指南

数据表是数据库中存储数据的基本单位，由行（记录）和列（字段）组成。合理的表结构设计是高效存储和查询数据的前提；数据库服务里面包括数据库，数据库包括了多张表，表中包括了多条记录，每条记录包括了多个列。

3.1 数据表的创建

创建流程与语法

创建表需先指定所属数据库，再定义表结构：

1. 切换到目标数据库：

USE db_name;

在这里插入图片描述

1. 创建表的基本语法：

CREATE TABLE [IF NOT EXISTS] table_name (
  field1 data_type [comment '字段说明'],
  field2 data_type [comment '字段说明'],
  ...
  -- 最后一个字段后无逗号
) [comment '表说明'];

table_name为自定义数据表的名字 field1 datatype：字段名 数据类型

基础示例

创建学生表（包含ID和姓名）：

CREATE TABLE IF NOT EXISTS student (
  id BIGINT COMMENT '学生编号',
  name VARCHAR(50) COMMENT '学生姓名'
) COMMENT '学生信息表';

注意事项

• 字段名和表名避免使用中文和关键字（如需使用关键字，用反引号包裹）
• 每个字段必须指定数据类型，字符串类型需明确长度（如VARCHAR(50)而非VARCHAR）
• 建议为每个字段和表添加COMMENT说明，提高可维护性
• IF NOT EXISTS可避免重复创建错误（生产环境推荐使用），只需要查看warnings即可
• 强烈建议每张表中都要设计一个编号字段，数据类型为bigint
• 尤其是varchar的长度要记得指定，在实际工作中要根据详细设计文档的描述转换为代码

3.2 数据表的查看

查看数据库中的所有表

SHOW TABLES;

该命令会返回当前数据库中所有表的列表。

在这里插入图片描述

查看表结构

查看表的字段定义、类型、约束等信息：

DESC table_name; -- 简写形式
-- 或
DESCRIBE table_name;
-- 或
SHOW COLUMNS FROM table_name;

输出结果解析：

• Field：字段名称
• Type：数据类型
• Null：是否允许为空（YES/NO）
• Key：索引类型（如PRI表示主键）
• Default：默认值，若某些数据为保密，那么我们就要去写默认值） 没有任何指定的时候就是null
• Extra：额外信息（如自增、自动更新等）

示例

查看student表结构：

DESC student;

输出：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

3.3 数据表的删除

删除语法

DROP TABLE [IF EXISTS] table_name [, table_name2 ...];

• 可同时删除多个表，用逗号分隔表名
• TEMPORARY关键字可用于删除临时表：DROP TEMPORARY TABLE table_name;

在这里插入图片描述

危险提示

删除表会导致表中所有数据永久丢失，操作前必须：

1. 确认是否有必要删除（是否可通过逻辑删除替代）
2. 完整备份表数据
3. 获得相关负责人审批
4. 在非业务高峰期执行

如果删除表的不存在：那么就会存在这条警告

在这里插入图片描述

而上述图片警告这种表示的是：库名.表名 来指定一张表在MySQL服务中的完全路径

3.4 执行外部SQL文件

命令行工具，只执行一些简单的查询或指令，如果一两句sql可以搞定就在这里面去执行，如果说要编复杂的SQL，比如复杂的表设计，复杂查询，建议大家还是去可视化工具中编写

那么对于复杂的表结构设计，通常会在可视化工具中编写SQL脚本，保存为一个本地的.SQL文件 然后在命令窗口执行文件，先把写好的文件的路径进行复制一下，再通过命令行执行：

1. 将SQL语句保存为.sql文件（如create_tables.sql）
2. 在MySQL客户端中执行：

SOURCE /path/to/create_tables.sql; -- 绝对路径或相对路径

在这里插入图片描述

示例：

SOURCE D:/projects/sql/create_user_table.sql;

四、综合示例：简单的电商数据库设计

4.1 需求分析

设计一个简单的电商数据库（bit_mall），包含商品、客户、订单三个核心表：

• 商品表（goods）：存储商品信息
• 客户表（customer）：存储客户信息
• 订单表（purchase）：存储购买记录

4.2 数据库创建

-- 创建数据库并指定编码
CREATE DATABASE IF NOT EXISTS bit_mall 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 切换到该数据库
USE bit_mall;

4.3 表结构设计与创建

商品表（goods）

drop table if exists goods;
create table goods(
	goods_id bigint comment '商品编号',
    goods_name varchar(50) comment '商品名称',
    unitprice decimal(10, 2) comment '单价，单位分', 
    category varchar(20) comment '商品分类',
    provider varchar(50) comment '供应商名称'
);

客户表（customer）

create table if not exists customer(
    customer_id int comment '客户编号',
    name varchar(32) comment '客户姓名',
    address varchar(256) comment '客户地址',
    email varchar(64) comment '电子邮箱',
    sex bit comment '性别',
    card_id varchar(18) comment '身份证'
);

购买记录表（purchase）

create table if not exists purchase(
    order_id int comment '订单号',
    customer_id int comment '客户编号',
    goods_id int  comment '商品编号',  
    nums int comment '购买数量'  
);

五、数据库操作注意

5.1 命名规范

数据库名、表名、字段名使用小写字母，单词间用下划线分隔（如user_info而非UserInfo） 避免使用MySQL关键字（如order、table），必须使用时加反引号 命名需体现业务含义（如goods_category表示商品分类，而非table1）

5.2 性能优化建议

数值类型选择：根据数据范围选择最小合适类型（如存储年龄用TINYINT而非INT） 字符串类型：短文本用VARCHAR，长文本用TEXT，避免过度设置长度（如VARCHAR(255)够用就不设VARCHAR(1000)） 日期类型：避免用字符串存储日期（如'2023-10-01'），应使用DATE或DATETIME，便于日期计算和索引

5.3 安全操作规范

1. 权限控制：生产环境中，普通用户仅授予SELECT、INSERT、UPDATE权限，DROP、ALTER等高危权限仅授予管理员（一般数据库运维才有这种权限）
2. 操作审计：开启MySQL日志（binlog），记录所有数据修改操作，便于追溯
3. 备份策略：
   • 全量备份：每周一次，备份整个数据库
   • 增量备份：每天一次，备份新增数据
   • 备份文件异地存储，定期验证备份有效性

5.4 常见问题解决方案

1. 中文乱码：确保数据库、表、字段的字符集均为utf8mb4，连接字符串指定characterEncoding=utf8mb4
2. 数据溢出：设计时预留足够长度，如用户ID使用BIGINT，避免INT在用户量增长后溢出
3. 时间戳2038问题：新系统优先使用DATETIME存储时间，旧系统逐步迁移

六、总结

核心知识点回顾

• 数据库操作：创建（CREATE DATABASE）、查看（SHOW DATABASES）、使用（USE）、删除（DROP DATABASE）
• 数据类型：根据业务选择合适的数值、字符串、日期类型，重点掌握DECIMAL（金额）、VARCHAR（字符串）、DATETIME（日期）
• 表操作：创建（CREATE TABLE）、查看（DESC）、删除（DROP TABLE），理解逻辑删除的优势

希望通过本文的学习，大家已经掌握MySQL数据库的基础操作和表设计原则。如果有什么不足，欢迎大家在评论区中指出！这节内容就先到这里了。