MySQL基础（一）：数据库、库表操作、数据类型与约束

1. 数据库基础

1.1 什么是数据库

数据库（Database）就是按照一定的数据结构组织、存储和管理数据的仓库。

那普通文件同样可以保存数据啊，为啥不用文件非得费个二遍事呢?

如果直接使用文件管理大量数据，会逐渐出现一些问题：

• 安全性差：误操作后很难进行恢复。
• 查询和管理不方便：文件中的数据没有按照合适的数据结构组织起来。
• 控制不方便：各种数据管理逻辑都需要程序员自己实现。
• 不适合管理海量数据：数据量越大，操作成本越高。

因此，我们需要数据库来统一完成数据的组织、存储和管理。

1.2 常见数据库

目前常见的数据库有：

• MySQL
• Oracle
• SQL Server
• PostgreSQL
• SQLite
• Redis

其中按照数据主要存储的位置，又可以简单分为：

磁盘数据库：MySQL、Oracle、SQLite …
内存数据库：Redis …

本文主要学习 MySQL。

1.3 MySQL本质是一个网络服务

使用MySQL时，实际上存在两个角色：

客户端 服务端
mysql —————-> mysqld
SQL语句

我们平时在终端中使用的：

mysql

本质上是 MySQL客户端程序。

而真正负责管理数据库的是：

mysqld

也就是MySQL服务器。

MySQL服务器本质上是一个网络服务，客户端通过网络连接服务器，然后将SQL语句发送给服务器执行。

MySQL默认端口号为：

3306

因此整体关系可以理解为：

Client
|
| SQL
v
MySQL Server
|
+– database
| |
| +– table
| +– table
|
+– database
|
+– table

2. MySQL基本使用

2.1 启动、关闭和重启MySQL

在Ubuntu中可以通过 systemctl 管理MySQL服务：
启动MySQL:

sudo systemctl start mysql

关闭MySQL：

sudo systemctl stop mysql

重启MySQL：

sudo systemctl restart mysql

查看MySQL状态：

sudo systemctl status mysql

部分Linux发行版中服务名可能是 mysqld：

systemctl start mysqld
systemctl stop mysqld
systemctl restart mysqld

2.2 连接MySQL服务器

完整连接方式：

mysql -h127.0.0.1 -P3306 -uroot -p

其中：

-h MySQL服务器IP
-P MySQL服务器端口
-u 用户名
-p 输入用户密码

如果连接的是本机MySQL，一般可以直接：

mysql -uroot -p

输入密码后即可进入MySQL。

退出MySQL：

exit

也可以使用：

quit

或者：

\\q

3. 数据库、表、行和列

安装MySQL服务器，本质上就是安装了一个数据库管理系统。

一个MySQL服务器可以管理多个数据库，一个数据库中又可以存在多张表：

MySQL Server
│
├── database
│ │
│ ├── table
│ └── table
│
└── database
│
└── table

而表本身又由行和列组成。

例如：

其中：

列（column）：表中的属性，例如 id、name、gender
行（row）：一条完整的数据，也称为一条记录

因此MySQL中的基本逻辑结构就是：

MySQL服务器
↓
数据库 database
↓
表 table
↓
行 row + 列 column

有了这个基本认识，下面正式开始操作数据库。

4. 数据库的基本操作

数据库的基本操作可以按照CRUD的思路理解：

Create 创建
Retrieve 查看
Update 修改
Delete 删除

4.1 增：创建数据库

创建数据库的基本语法：

create database [if not exists] db_name[[default] charset=charset_name][[default] collate=collation_name];

其中：

[] 可选，不选则默认
if not exists 数据库不存在时才创建
charset 指定字符集
collate 指定字符集对应的校验规则

最简单的创建方式：

create database helloworld;

4.1.1 创建数据库本质上做了什么

先查看MySQL的数据存储目录：

show variables like 'datadir';

得到数据存储目录：

/var/lib/mysql/

在Linux中查看：

sudo ls /var/lib/mysql/

可以看到对应的：

helloworld

目录

也就是说：

创建数据库，本质上会在MySQL的数据存储目录下创建一个对应的数据库目录。

删除数据库后，这个目录也会随之消失。

关于db.opt文件

在MySQL 5.7等旧版本中，新建数据库后，数据库目录中通常存在一个：

db.opt

文件，其中记录该数据库默认使用的：

字符集
校验规则

需要注意：MySQL 8.0已经取消了 db.opt 等文件式元数据，因此不同MySQL版本看到的目录内容可能不同。

重点理解：

数据库是逻辑概念
↓
最终仍然需要落到磁盘进行存储

4.1.2 字符集和校验规则

字符集决定：

数据应该使用什么编码方式进行存储。

校验规则决定：

字符串应该按照什么规则进行比较。

创建数据库时指定字符集：

create database db1 charset=utf8mb4;

同时指定校验规则：

create database db2 charset=utf8mb4 collate=utf8mb4_general_ci;

如果创建数据库时没有显式指定，则使用MySQL的默认配置。

查看数据库默认字符集：

show variables like 'character_set_database';

查看数据库默认校验规则：

show variables like 'collation_database';

查看MySQL支持的字符集：

show charset;

查看支持的校验规则：

show collation;

不同的校验规则会直接影响字符串比较。

例如：

_ci 一般表示大小写不敏感
_bin 按二进制方式比较，通常区分大小写

因此同样查询：

select * from person where name='alice';

在不同校验规则下，得到的结果可能不同。

4.2 查：查看数据库

查看当前MySQL服务器中的所有数据库：

show databases;

查看数据库的创建语句：

show create database helloworld;

4.2.1 使用数据库

进入指定数据库：

use helloworld;

可以将它简单理解成：

Linux：cd进入目录

MySQL：use进入数据库

查看当前正在使用哪个数据库：

select database();

4.2.2 查看MySQL连接

查看当前MySQL连接情况：

show processlist;

如果希望查看完整SQL：

show full processlist;

其中常见字段：

Id 当前连接的编号
User 用户
Host 客户端来源
db 当前使用的数据库
Command 当前状态
Time 状态持续时间
Info 当前执行的SQL

如果需要终止某个连接：

kill id;

4.3 改：修改数据库

数据库的修改主要是修改：

字符集
校验规则

基本语法：

alter database db_name [[default] charset=charset_name] [[default] collate=collation_name];

例如：

alter database helloworld charset=utf8mb4 collate=utf8mb4_general_ci;

修改后可以再次查看：

show create database helloworld;

4.4 删：删除数据库

基本语法：

drop database [if exists] db_name;

例如：

drop database helloworld;

为了避免数据库不存在时报错：

drop database if exists helloworld;

数据库删除以后：

数据库目录被删除
数据库中的表也全部被删除
表中的数据自然也全部消失

因此 drop database 是一个非常危险的操作。

5. 表的基本操作

数据库创建完成以后，真正的数据最终需要放到表中。

表结构相关操作属于：

DDL：Data Definition Language

也就是数据定义语言。

这里同样按照：

增 → 查 → 改 → 删

的顺序进行。

5.1 增：创建表

创建表之前首先需要进入一个数据库：

use helloworld;

创建表的基本语法：

create table [if not exists] table_name(
field1 datatype1 [comment '描述'],
field2 datatype2 [comment '描述'],
field3 datatype3 [comment '描述']
)[charset=charset_name] [collate=collation_name] [engine=engine_name];

其中：

field 列名
datatype 数据类型
charset 表的字符集
collate 表的校验规则
engine 存储引擎
comment 字段说明

例如：

create table stu(
id int,
name varchar(30),
gender varchar(2)
);

5.1.1 存储引擎

查看MySQL支持的存储引擎：

show engines;

目前最常用的存储引擎是：

InnoDB

创建表时也可以显式指定：

create table stu(
id int,
name varchar(30)
) engine=InnoDB;

如果不指定，则使用MySQL默认存储引擎。

5.1.2 创建表本质上做了什么

创建表以后，可以再次查看数据库对应的物理目录：

sudo ls /var/lib/mysql/helloworld/

表是逻辑结构，但最终依然需要由存储引擎将其数据落到磁盘。

旧版MySQL中：

InnoDB：
stu.frm 表结构
stu.ibd 表数据和索引

而MySQL 8.0已经取消 .frm 文件，将表结构等元数据统一放入数据字典（.ibd）。

因此实际截图时以当前MySQL版本为准。

5.2 查：查看表

查看当前数据库中的所有表：

show tables;

查看表结构：

desc stu;

也可以写成：

describe stu;

常见字段：

Field 字段名
Type 数据类型
Null 是否允许NULL
Key 是否为索引字段
Default 默认值
Extra 额外属性

查看完整建表语句：

show create table student;

5.3 改：修改表

修改表结构统一使用：

alter table

新增字段

alter table stu add age tinyint unsigned;

如果希望添加到某个字段后面：

alter table stu add age tinyint unsigned after name;

添加到第一列：

alter table stu add id int first;

修改字段属性

alter table stu modify name varchar(50);

modify 修改的是字段属性，不改变字段名。

修改字段名

alter table stu change name student_name varchar(50);

change 可以同时修改：

字段名
字段属性

删除字段

alter table stu drop age;

删除字段以后，该字段原有的数据也会一起消失。

修改表名

alter table stu rename to student;

5.4 删：删除表

基本语法：

drop table [if exists] table_name;

例如：

drop table student;

删除表意味着：

表结构消失
+
表中的数据全部消失

6. 数据库和表的备份与恢复

前面已经把库和表的基本操作介绍完毕，下面再来看数据的备份和恢复。

MySQL的逻辑备份，本质上可以简单理解为：

把创建数据库、创建表、插入数据等SQL语句保存到一个SQL文件中。

恢复时再把这些SQL重新执行一遍。

6.1 数据库备份

数据库备份使用：

mysqldump -h127.0.0.1 -P3306 -uroot -p -B 数据库名 > 备份文件.sql

例如：

mysqldump -h127.0.0.1 -P3306 -uroot -p -B helloworld > helloworld.sql

注意：

mysqldump 是Linux命令，需要在普通终端中执行，而不是在 mysql> 中执行。

查看生成的SQL文件：

less helloworld.sql

可以发现其中保存的实际上就是大量SQL语句。

6.2 数据库恢复

进入MySQL：

mysql -uroot -p

然后执行：

source /完整路径/helloworld.sql;

MySQL会按照顺序重新执行备份文件中的SQL，数据库、表以及表中的数据就会被恢复出来。

6.3 表备份

如果不希望备份整个数据库，只想备份其中几张表：

mysqldump -h127.0.0.1 -P3306 -uroot -p 数据库名 表1 表2 > 备份文件.sql

例如：

mysqldump -h127.0.0.1 -P3306 -uroot -p helloworld student > table.sql

6.4 表恢复

表备份文件中通常没有：

create database
use database

因此恢复表之前，需要先准备一个数据库：

create database restore_test;
use restore_test;

再执行：

source /完整路径/table.sql;

恢复完成后：

show tables;

即可看到恢复出来的表。

7. MySQL数据类型

表由一个个字段组成，而每个字段都必须具有自己的数据类型。

数据类型主要决定三件事：

数据应该占用多少空间
数据应该如何解释
数据允许取什么值

MySQL常见数据类型可以分为：

7.1 BOOL

MySQL中的：

bool

实际上是：

tinyint(1)

的同义写法。

例如：

create table test_bool(
flag bool
);

查看：

desc test_bool;

可以看到实际类型会表现为 tinyint。

在布尔语义中：

0 false
非0 true

需要注意，tinyint(1) 并不会真的把数据限制成只能存 0 和 1。

7.2 TINYINT

tinyint 占用1字节。

有符号范围：

-128 ~ 127

无符号范围：

0 ~ 255

例如：

create table test(
num tinyint
);

插入：

insert into test values(127);

可以成功。

继续插入：

insert into test values(128);

将会超出取值范围。

无符号：

create table test_unsigned
(
num tinyint unsigned
);

此时范围变成：

0 ~ 255

数据类型本身其实就已经是一种约束：

tinyint规定了数据必须是整数
同时规定了整数的取值范围

这也是后面表约束的基础。

7.3 BIT

bit 用于保存位数据：

bit(M)

其中：

M表示位数
范围为1~64

例如：

create table test_bit(
num bit(8)
);

底层保存的是二进制位，因此直接查询时显示结果有时并不直观。

可以配合：

select num + 0 from test_bit;

将其按照数字查看。

7.4 FLOAT和DOUBLE

float 用于保存单精度浮点数：

float

double 用于保存双精度浮点数：

double

浮点数最大的问题是：

存在精度损失。

因此如果只是普通小数，可以使用 float / double。

如果要求数据绝对精确，例如：

金额
财务数据

则更适合使用 decimal。

7.5 DECIMAL

decimal 是定点数：

decimal(M, D)

其中：

M 总位数
D 小数位数

例如：

price decimal(8, 2);

表示：

总共最多8位
其中2位为小数

decimal 相比 float 最大的特点就是能够避免常见的浮点精度损失。

精度要求不高 float / double
精度要求很高 decimal

精度比较如下:

8. 字符串类型

8.1 CHAR

char 是定长字符串：

char(N)

例如：

gender char(1);

所谓定长，就是字段定义以后，其最大字符长度是固定的。

适合长度比较稳定的数据。

8.2 VARCHAR

varchar 是变长字符串：

varchar(N)

例如：

name varchar(30);

它会根据实际保存的数据长度使用空间，因此更适合长度变化较大的字符串。

8.3 CHAR和VARCHAR

简单来说：

char 定长
varchar 变长

如果数据长度基本固定：

char更合适

如果数据长度变化很大：

varchar更合适

例如：

性别、固定编号 char

姓名、地址、标题 varchar

9. 日期时间类型

9.1 DATE

date 用于保存日期：

YYYY-MM-DD

例如：

birthday date;

数据：

2026-09-06

9.2 DATETIME

datetime 保存日期和时间：

YYYY-MM-DD HH:MM:SS

例如：

create_time datetime;

数据：

2026-09-06 10:30:00

9.3 TIMESTAMP

timestamp 时间戳同样可以记录时间。

例如：

create_time timestamp;

通常用于：

创建时间
更新时间

等时间信息。
无需插入，自动获取

10. ENUM和SET

10.1 ENUM

enum 是枚举类型：

只能从提前给定的多个值中选择一个。

例如：

gender enum('男', '女');

此时：

男 可以
女 可以
其他 不符合枚举定义

因此 enum 可以理解为：

单选

10.2 SET

set 同样需要提前给出允许的取值：

hobby set('篮球', '足球', '音乐', '电影');

但与 enum 不同：

enum 只能选择一个
set 可以选择多个

例如：

insert into student values('张三', '篮球,音乐');

11. 表的约束

前面学习的数据类型，本身已经能够对数据进行一定的约束。

例如：

age tinyint unsigned

就已经规定：

必须是整数
不能是负数
还不能超过tinyint unsigned的范围

但是数据类型提供的约束仍然比较单一。

实际业务中还会出现：

姓名不能为空
学号不能重复
年龄不给时使用默认值
id自动增长
学生必须属于一个真实存在的班级

因此MySQL还提供了各种额外的表约束。

主要包括：

null / not null
default
comment
zerofill
primary key
auto_increment
unique
foreign key

11.1 NULL和NOT NULL

MySQL中的字段默认通常允许为：

NULL

NULL 表示：

当前这个值未知或者不存在。

例如：

select null;

并且：

select null + 1;

结果仍然是：

NULL

因为一个未知的值参与运算以后，结果仍然无法确定。//这块是个坑，下篇解决它

如果一个字段不允许为空，可以使用：

not null

例如：

create table student(
name varchar(30) not null
);

此时插入数据时必须提供 name。

11.2 DEFAULT

如果某个字段经常使用同一个值，可以给它设置默认值：

default

例如：

create table student
(
name varchar(30),
age tinyint unsigned default 18
);

插入：

insert into student(name) values('张三');

由于没有给 age，MySQL会使用：

18

作为默认值。

需要注意：

default 解决“不提供值时用什么”
not null 解决“能不能显式出现NULL”

二者不是一回事。

11.3 COMMENT

comment 用于给字段添加说明：

create table student
(
id int comment '学号',
name varchar(30) comment '姓名'
);

查看：

show create table student;

即可看到这些字段描述。

comment 本质上类似代码中的注释，主要方便：

程序员
DBA
维护人员

理解表结构。

11.4 ZEROFILL

zerofill 用于在数值显示宽度不足时，在前面补 0。

例如旧版MySQL中：

create table test
(
num int(5) zerofill
);

插入：

insert into test values(12);

显示：

00012

但底层保存的数据仍然是：

12

也就是说：

zerofill主要改变显示效果，并没有改变真实数值。

该特性在新版MySQL中已经逐渐废弃，实际开发了解即可。

11.5 PRIMARY KEY

主键：

primary key

用于唯一标识表中的一条记录。

例如：

create table student
(
id int unsigned primary key,
name varchar(30)
);

主键具有两个最基本的要求：

不能重复
不能为空

因此：

id = 1
id = 2
id = 3

都可以。

但再次插入：

id = 1

就会产生主键冲突。

一张表只能有一个主键。

添加和删除主键

已经存在的表也可以添加主键：

alter table student add primary key(id);

删除主键：

alter table student drop primary key;

复合主键

一张表只能有一个主键，但：

一个主键可以由多个字段共同组成。

例如一个网络进程可以由：

IP + PORT

共同唯一确定。

create table process
(
ip varchar(30),
port int unsigned,
info varchar(100),

primary key(ip, port)
);

此时：

ip可以重复
port也可以重复

但(ip, port)不能同时重复

例如：

192.168.1.1 8080
192.168.1.1 8081
192.168.1.2 8080

都没有问题。

但：

192.168.1.1 8080
192.168.1.1 8080

会发生主键冲突。

11.6 AUTO_INCREMENT

auto_increment 表示字段自动增长。

例如：

create table student
(
id int unsigned primary key auto_increment,
name varchar(30)
);

插入数据时不提供 id：

insert into student(name) values('张三');
insert into student(name) values('李四');
insert into student(name) values('王五');

MySQL会自动得到：

1 张三
2 李四
3 王五

自增长字段通常需要满足：

必须是数值类型
必须是一个Key(不止主键，后面详谈)
一张表只能有一个auto_increment字段

最常见的组合就是：

id int primary key auto_increment

11.7 UNIQUE

唯一键：

unique

用于保证某个字段的数据不能重复。

例如：

create table teacher
(
id int unsigned primary key auto_increment,
sn int unsigned unique,
name varchar(30)
);

这里：

id 使用主键保证唯一
sn 使用unique保证唯一

主键和唯一键的区别可以简单理解为：

primary key
唯一
非空
一张表只有一个

unique
唯一
一张表可以存在多个

11.8 FOREIGN KEY

外键用于建立两张表之间的约束关系。

例如现在有：

班级表
学生表

一个学生所属的班级，应该是真实存在的班级。

先创建班级表：

create table class_table
(
class_id int primary key,
class_name varchar(30)
);

再创建学生表：

create table student
(
id int primary key,
name varchar(30),
class_id int,

foreign key(class_id) references class_table(class_id)
);

其中：

foreign key(class_id) references class_table(class_id)

表示：

student.class_id
|
| 引用
v
class_table.class_id

关系可以表示为：

class_table
———————-
class_id class_name
1 一班
2 二班
3 三班
^
|
| foreign key
|
student
———————-
id name class_id
1 张三 1
2 李四 2
3 王五 1

如果班级表中不存在：

class_id = 10

那么学生表就不能随意插入一个属于10班的学生。

这就是外键约束：

保证两张存在关联关系的表，其数据关系也是合法的。

没有99班被阻止

删除有学生的班级被阻止

12. 小结

到这里，MySQL最基础的一条主线就已经建立起来了：

MySQL服务器
↓
数据库
↓
表
↓
字段
↓
数据类型
↓
表约束

这一篇主要解决的是：

数据放在哪里
↓
表应该怎么建立
↓
字段应该存什么
↓
字段应该遵守什么规则

下一篇正式开始操作表中的数据。

也就是：

Create 新增
Retrieve 查询
Update 修改
Delete 删除

简称：

CRUD