MySQL数据库基础（一）库操作｜表操作｜数据类型｜表约束详解

1. 数据库基础

1.1 什么是数据库

文件保存数据的缺点：

1) 文件的安全性问题

2) 文件不利于数据查询和管理

3) 文件不利于存储海量数据

4) 文件在程序中控制不方便

数据库：解决文件存储的缺陷，更加高效管理数据；数据库水平是衡量程序员水平的重要指标。
数据库存储介质：磁盘、内存

1.2 主流数据库

1. SQL Server：微软产品，.NET程序员常用，适合中大型项目

2. Oracle：甲骨文，适合大型项目、复杂业务逻辑；并发强，闭源收费

3. MySQL：世界最受欢迎开源数据库，中小型互联网项目；并发性能好

4. PostgreSQL：加州大学伯克利开发，开源免费，商用、科研均可

5. SQLite：轻量级嵌入式数据库，不需要服务进程，占用资源极小，多用于嵌入式设备、移动端

6. H2：Java开发嵌入式数据库，是一个类库，可以直接嵌入Java应用

1.3 MySQL基本使用

1.3.1 MySQL安装

CentOS6.5编译安装MySQL5.6.14

CentOS7 yum安装MariaDB

Windows安装MySQL5.7

1.3.2 连接服务器

mysql -h 127.0.0.1 -P 3306 -u root -p

-h：主机地址，不写默认127.0.0.1本地

-P：端口号，不写默认3306

-u：用户名

-p：密码，回车后输入密码

成功登录提示：Welcome to the MySQL monitor. Commands end with ; or \g.

1.3.3 Windows服务器管理

win+r输入services.msc打开服务管理器，可以停止、暂停、重启MySQL服务。

1.3.4 服务器、数据库、表关系

1. 数据库服务器：安装MySQL，是一套管理程序，一台服务器可以管理多个数据库。

2. 数据库（DB）：一个项目一般对应一个数据库。

3. 表（Table）：一个数据库里面有多张表，保存实体数据。

4. 层级：Client客户端 → MySQL服务 → 多个数据库DB → 每个DB多张表

5. 表：行（记录）、列（字段）。

1.3.5 使用案例

-- 创建数据库 create database helloworld; -- 使用数据库 use helloworld; -- 创建表 create table student( id int, name varchar(32), gender varchar(2) ); -- 插入数据 insert into student (id,name,gender) values (1,'张三','男'); insert into student (id,name,gender) values (2,'李四','女'); insert into student (id,name,gender) values (3,'王五','男'); -- 查询全部数据 select * from student;

1.3.6 数据逻辑存储

行row：一条完整记录

列column：字段，代表属性

1.4 MySQL架构

MySQL跨平台：支持Linux、Windows、MacOS。

分层：

1. Client Connectors：各种语言驱动（JDBC、PHP、Python等）

2. Connection Pool：连接池、权限认证、安全

3. SQL Interface：SQL接口接收语句

4. Parser：语法解析器，词法语法分析

5. Optimizer：查询优化器，生成最优执行计划

6. Caches：查询缓存

7. Pluggable Storage Engines 可插拔存储引擎：真正负责读写数据

InnoDB、MyISAM、Memory、Archive等

8. File System：底层磁盘文件系统；日志文件（redo、undo、binary log等）

1.5 SQL语句分类

1.6 存储引擎

1.6.1 概念

存储引擎：MySQL如何存储数据、建立索引、更新查询数据的底层实现方式。MySQL支持可插拔多种存储引擎。

1.6.2 查看存储引擎

show engines;

1.6.3 常用引擎对比

1. InnoDB（MySQL8.0默认）

✅支持事务、行锁、外键、MVCC

适合增删改频繁业务，互联网项目首选

2. MyISAM

❌不支持事务；表锁；查询速度快；支持全文索引

适合大量查询，很少修改场景，崩溃丢失数据

3. Memory

全部数据放内存，断电丢失，速度极快

4. Archive：只支持插入查询，压缩存储

5. NDB：集群引擎

2. 库的操作

2.1 创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT CHARACTER SET charset_name] [DEFAULT COLLATE collation_name];

IF NOT EXISTS：数据库不存在才创建，防止报错

CHARACTER SET（charset）：字符集

COLLATE：排序/校验规则

2.2 案例

--最简创建 create database db1; --指定字符集 create database db2 charset=utf8; --字符集+collate create database db3 charset=utf8 collate utf8_general_ci;

不指定字符集collate，使用MySQL服务器默认。

2.3 字符集和校验规则collate

2.3.1 查看数据库字符集、排序规则

show variables like 'character_set_database'; show variables like 'collation_database';

2.3.2 查看全部支持字符集

show charset;

2.3.3 查看全部collate排序规则

show collation;

2.3.4 collate对查询、排序的影响

1. utf8_general_ci：ci=case insensitive大小写不敏感

create database test1 collate utf8_general_ci; use test1; create table person(name varchar(20)); insert into person values('a'),('A'),('b'),('B'); select * from person where name='a'; -- 结果：a 和 A 两条都会查出来，大小写视为相等

2. utf8_bin：二进制比较，区分大小写

create database test2 collate utf8_bin; use test2; create table person(name varchar(20)); insert into person values('a'),('A'),('b'),('B'); select * from person where name='a'; --只会匹配'a'，不会匹配'A'

order by排序也会受collate影响，ci不区分大小写排序，bin严格二进制排序。

2.4 操纵数据库

2.4.1 查看服务器所有数据库

show databases;

2.4.2 查看数据库创建语句

show create database 数据库名;

反引号 ` 包裹库名，防止库名和关键字冲突。

/*!40100 ... */：版本条件注释，高版本MySQL才执行。

2.4.3 修改数据库

只能修改字符集、collate；不能修改数据库名字

ALTER DATABASE db_name [DEFAULT CHARACTER SET charset_name] [DEFAULT COLLATE collation_name];

示例：alter database mytest charset=gbk;
⚠️只修改数据库设置，不会自动修改已经存在的表。

2.4.4 删除数据库

DROP DATABASE [IF EXISTS] db_name;

IF EXISTS：存在才删除，避免报错

删除效果：数据库消失；对应磁盘文件夹被删除；库里面所有表全部级联删除
⚠️禁止随意删除数据库！

2.4.5 备份与恢复（mysqldump）

mysqldump是外部命令，退出mysql终端执行，不是sql语句。

备份整个数据库

mysqldump -P3306 -u root -p -B 数据库名 > 备份文件.sql

示例：mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql
导出的.sql里面保存全部建库、建表、插入数据SQL。

恢复（source命令，mysql内部执行）

source D:/mysql-5.7.22/mytest.sql;

其他备份用法

1. 只备份库中几张表，不带-B

mysqldump -u root -p 库名 表1 表2 > xxx.sql

2. 同时备份多个数据库

mysqldump -u root -p -B db1 db2 > all.sql

不带-B参数备份：恢复前要手动先create database，use数据库再source。

2.4.6 查看数据库连接

show processlist;

作用：

1. 查看当前哪些用户正在连接MySQL

2. 发现陌生连接，判断是否被入侵

3. 数据库慢的时候，可以看连接状态定位问题

输出字段：Id、User、Host、db、Command、Time、State、Info

考试高频易错总结

1. charset字符集：管文字怎么存；collate排序规则：管字符串比较、where匹配、order by排序。

2. ci大小写不敏感；bin二进制区分大小写。

3. 修改数据库charset/collate不会更新已有表。

4. InnoDB支持事务、行锁；MyISAM表锁，不支持事务。

5. mysqldump是shell命令，不是mysql内部sql；source是mysql内部恢复命令。

6. DDL定义结构(create/drop/alter)，DML操作数据(insert/update/delete)，DQL查询select。

7. 删除数据库drop database级联删除全部表，谨慎操作。

8. MySQL的utf8不是完整utf‑8，最多3字节，不能存emoji，生产优先utf8mb4。

3. 表的操作

3.1 创建表

语法

CREATE TABLE table_name ( field1 datatype, field2 datatype, field3 datatype ) character set 字符集 collate 校验规则 engine 存储引擎;

参数说明

field：表的列名

datatype：列的数据类型

character set：字符集，不指定则继承数据库字符集

collate：校验规则，不指定则继承数据库校验规则

engine：指定存储引擎

3.2 创建表案例

create table users ( id int, name varchar(20) comment '用户名', password char(32) comment '密码是32位的md5值', birthday date comment '生日' ) character set utf8 engine MyISAM;

💡 MyISAM存储引擎文件说明：
使用MyISAM引擎建表，磁盘会生成3个文件：

1） users.frm：表结构文件

2） users.MYD：表数据文件

3） users.MYI：表索引文件

对比：InnoDB引擎只有 .frm 和 .ibd 文件，数据和索引放在ibd文件中。

3.3 查看表结构

语法

desc 表名;

示例

desc users;

输出字段含义：

3.4 修改表 ALTER TABLE

开发中经常需要新增字段、修改字段类型、删除字段、重命名表、重命名字段。

核心语法

-- 添加字段 ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...); -- 修改字段类型/长度 ALTER TABLE tablename MODIFY (column datatype [DEFAULT expr][,column datatype]...); -- 删除字段 ALTER TABLE tablename DROP (column); -- 修改表名 ALTER TABLE old_table RENAME [TO] new_table; -- 修改列名（必须完整重写类型） ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型;

实操案例

1. 插入测试数据

insert into users values(1,'a','b','1982-01-04'),(2,'b','c','1984-01-04');

2. 新增字段，在birthday后面增加图片路径字段

alter table users add assets varchar(100) comment '图片路径' after birthday;

新增字段不会影响原有数据，旧数据新增字段处值为NULL。

3. 修改字段长度，把name长度改为60

alter table users modify name varchar(60);

4. 删除字段 ⚠️危险，字段和对应数据全部丢失

alter table users drop password;

5. 修改表名

alter table users rename to employee;

to关键字可以省略。

6. 修改列名

CHANGE语法：新字段必须完整定义，不能只写名字

alter table employee change name xingming varchar(60);

3.5 删除表

语法

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

IF EXISTS：如果表不存在不会报错，推荐写在生产脚本

TEMPORARY：只删除临时表

示例：

drop table if exists t1;

📌面试重点总结

1. MyISAM 3个文件：.frm结构、.MYD数据、.MYI索引；InnoDB：.frm、.ibd

2. modify：改字段类型长度；change：改列名（必须重写类型）

3. add ... after 列名：控制新增字段位置

4. drop删除字段，数据直接丢失，不可恢复

5. 删除表建议带上if exists，避免脚本执行报错

4. 数据类型

4.1 数据类型总览

4.2 数值类型

整数范围表

默认是有符号；加上UNSIGNED变成无符号，只能存非负数。
⚠️生产建议：尽量少用UNSIGNED，数据存不下直接升级为BIGINT。

TINYINT越界测试

create table tt1(num tinyint); insert into tt1 values(1); insert into tt1 values(128); --越界报错 Out of range

无符号示例

create table tt2(num tinyint unsigned); insert into tt2 values(-1); --报错，不能负数 insert into tt2 values(255); --合法

4.2.1 BIT位类型

语法：bit(M)，M范围1‑64，默认M=1
select查询bit，默认显示ASCII字符，不直接显示数字。适合存储0/1状态。

create table tt4(id int, a bit(8)); insert into tt4 values(10,10); select * from tt4; --bit字段显示字符，看不到数字10 --bit(1)只存0或1，节省空间，性别、开关状态 create table tt5(gender bit(1)); insert into tt5 values(0); insert into tt5 values(1); insert into tt5 values(2); --越界报错

4.2.2 小数类型

FLOAT

语法：float(M,D) [unsigned]

M总显示长度，D小数位数，占用4字节；会四舍五入，精度大约7位，不适合财务。

float(4,2)：范围 -99.99 ~ 99.99；unsigned则0‑99.99

create table tt6(id int, salary float(4,2)); insert into tt6 values(100,-99.99); insert into tt6 values(101,-99.991); --四舍五入保存-99.99

DECIMAL（定点数，财务必用）

语法：decimal(M,D) [unsigned]

高精度，不会丢失精度，金额、账单必须用decimal。

decimal(5,2)：总长度5位，小数占2位，范围 -999.99 ~999.99

decimal 整数最大位数M为65，支持小数最大位数D为30。如果D被省略，默认为0。如果M被省略，默认为10。

create table tt8( id int, salary float(10,8), salary2 decimal(10,8) ); insert into tt8 values(100,23.12345612, 23.12345612); --float会丢失精度，decimal保持准确

float：近似存储；decimal：精确存储。涉及钱一定用decimal！

4.3 字符串类型 char vs varchar

CHAR(L) 定长字符串

• 固定长度，L最多255个字符

• 数据不足L长度，内存仍然占满L；查询速度快，浪费空间。

适合：身份证、手机号、md5密码，长度固定数据。

create table tt9(id int,name char(2)); insert into tt9 values(100,'ab'); insert into tt9 values(101,'中国');

VARCHAR(L) 可变长字符串

• L：最多字符数，实际字节受字符集限制，最大长度65535个字节，utf8一个汉字占3字节。

• 按需占用空间，节省存储，性能略低于char。

适合：姓名、地址，长度变化的数据。

create table tt10(id int,name varchar(6)); insert into tt10 values(100,'hello'); insert into tt10 values(100,'我爱你，中国');

char与varchar对比总结

✅选型：

1. 长度固定 → char（手机号、md5）效率高，浪费空间无所谓

2. 长度变化大 → varchar，节省磁盘

3. char最大255字符；varchar最大受行大小限制。

4.4 日期时间类型

create table birthday(t1 date, t2 datetime, t3 timestamp); insert into birthday(t1,t2) values('1997‑7‑1','2008‑8‑8 12:1:1'); --t3 timestamp 不赋值，自动填入当前时间 update birthday set t1='2000‑1‑1'; --更新行，timestamp会自动刷新为当前时间

业务小提示：只需要日期用date；完整时间用datetime；timestamp会自动更新，适合记录修改时间。

4.5 ENUM 与 SET

ENUM 单选枚举：只能选给定列表其中一个值，底层存储数字。

enum('男','女');

SET 多选集合：可以选列表中0个、1个或者多个，底层位图存储，最多64个选项。

set('登山','游泳','篮球','武术');

案例：

create table votes( username varchar(30), hobby set('登山','游泳','篮球','武术'), gender enum('男','女') ); insert into votes values('雷锋','登山,武术','男'); insert into votes values('Juse','登山,武术',2); --enum数字2代表女

⚠️注意：where hobby='登山' 只能匹配只选登山的记录；同时选登山+武术查不出来。

查询集合包含某一项，使用find_in_set()函数！

--查询爱好包含登山的所有记录 select * from votes where find_in_set('登山', hobby);

find_in_set(sub,str_list)：找到返回下标，找不到返回0。

select find_in_set('a','a,b,c'); --返回1 select find_in_set('a,b','a,b,c'); --返回0,只能查找一项 select find_in_set('d','a,b,c'); --返回0

📌面试重点总结

1. 整数类型：tinyint(1字节) ~ bigint(8字节)，unsigned无符号，不推荐滥用。

2. bit类型查询显示ASCII字符，适合0/1开关。

3. 金额绝对不能用float/double，必须用decimal定点数！

4. char定长，varchar变长；char上限255字符。

5. timestamp会自动更新时间；datetime不会自动。

6. enum单选，set多选；set查询包含某一项要用find_in_set()。

5. 表的约束

作用：数据类型约束比较单一，约束是额外校验规则，从业务逻辑层面保证存入数据库的数据合法、正确。
常见约束：null/not null、default、comment、zerofill、primary key、auto_increment、unique key、foreign key

5.1 空属性 NULL / NOT NULL

知识点

1. NULL：允许为空（系统默认），该字段可以不填数据

2. NOT NULL：不为空，该字段必须填入数据，不能是NULL

3. 运算大坑：NULL参与任何数学运算，结果永远为NULL

select 1+null; -- 结果为NULL，得不到1

4. 开发规范：业务中尽量设置 NOT NULL

原因：空值无法正常参与运算、索引效率差，业务上很多字段本来就不应该为空（班级名、姓名）

示例代码

-- 创建班级表，班级名称、教室不能为空 create table myclass( class_name varchar(20) not null, class_room varchar(10) not null ); -- 查看表结构 desc myclass; -- 报错！缺少class_room，字段不允许为空 insert into myclass(class_name) values('class1'); -- ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

5.2 默认值 DEFAULT

知识点

1. 默认值：插入数据不给该字段传值时，自动填入预设的默认数据

2. 只有设置了default的字段，插入语句才可以省略该列

3. 如果手动传入数值，优先使用传入的值，不会触发默认值

示例代码

create table tt10 ( name varchar(20) not null, age tinyint unsigned default 0, sex char(2) default '男' ); desc tt10; -- 只插入name，age、sex自动使用默认值 0、男 insert into tt10(name) values('zhangsan'); select * from tt10;

查询结果：

注意：not null 和 default一般不同时写。有默认值，就算不传，字段也不会是空，不需要not null。

5.3 列注释 COMMENT

知识点

1. comment 不影响任何表逻辑，仅用来给字段写中文说明，给开发/DBA阅读

2. desc 表名 看不到注释

3. 使用show create table 表名\G才能完整查看 建表语句+注释

示例代码

create table tt12 ( name varchar(20) not null comment '姓名', age tinyint unsigned default 0 comment '年龄', sex char(2) default '男' comment '性别' ); -- 完整查看建表语句，显示注释 show create table tt12\G

5.4 zerofill 零填充

知识点

1. 只作用于数字类型

2. int(5)：括号内数字本身没有意义，只有搭配zerofill才生效

3. 功能：查询展示的时候，数字前面补0，补齐到设定长度

⚠重点：只是显示效果！数据库底层存储仍然是原始数字，不会改变存储的值

4. 添加zerofill，字段会自动带上unsigned无符号属性，不能存负数

示例代码

-- 修改a字段，5位长度，零填充 alter table tt3 change a int(5) unsigned zerofill; insert into tt3 values(1,2); select * from tt3; -- 查询输出：00001 , 2 -- 底层存储依旧是数字1，hex(a)验证存储值不变 select a,hex(a) from tt3;

5.5 主键 primary key（PRI）

知识点

1. 主键约束2条硬性规则

✅值不能重复（唯一） ✅不能为NULL（非空）

2. 一张表最多只能有1个主键

3. 主键字段业务首选整数类型，查询、关联性能更好

4. 分类：单字段主键、复合主键（多字段联合主键）

复合主键：多个字段合在一起作为主键，组合整体不能重复，单个字段可以重复

①单主键示例

-- 创建时直接指定主键 create table tt13 ( id int unsigned primary key comment '学号不能为空', name varchar(20) not null ); desc tt13; -- 重复主键插入直接报错 insert into tt13 values(1,'aaa'); insert into tt13 values(1,'aaa'); -- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' -- 表建好之后追加主键 alter table 表名 add primary key(字段列表); -- 删除主键（不需要写字段名，一张表只有一个主键） alter table tt13 drop primary key;

②复合主键示例

create table tt14( id int unsigned, course char(10) comment '课程代码', score tinyint unsigned default 60 comment '成绩', primary key(id,course) -- id+课程 联合复合主键 ); desc tt14; insert into tt14 (id,course)values(1,'123'); -- 组合完全一样，主键冲突报错 insert into tt14 (id,course)values(1,'123');

5.6 自增长 auto_increment

知识点

1. 作用：插入数据不给值，数据库自动生成一个+1递增的整数

2. 强制前提：字段本身必须是索引（一般搭配primary key主键）；字段类型必须是整数；一张表最多只能设置1个自增长列

3. 自增规则：从当前表里已有最大ID+1生成新ID

4. 获取刚刚插入的自增ID：select last_insert_id();

批量插入时，返回第一条生成的自增id

示例代码

create table tt21( id int unsigned primary key auto_increment, name varchar(10) not null default '' ); -- 不给id，自动自增 insert into tt21(name) values('a'); insert into tt21(name) values('b'); select * from tt21; -- id自动变成1，2 -- 获取上一次自增id select last_insert_id();

5.7 唯一键 unique key（UNI）

知识点

1. 作用：保证字段业务不重复（手机号、邮箱、身份证）

2. 和主键对比核心区别

业务经验：主键用无业务含义自增ID；唯一键用来约束业务字段不能重复（邮箱、身份证）

示例代码

create table student ( id char(10) unique comment '学号，不能重复，但可以为空', name varchar(10) ); insert into student(id,name) values('01','aaa'); insert into student(id,name) values('01','bbb'); -- 重复报错 insert into student(id,name) values(null,'bbb'); -- NULL可以多次插入 select * from student;

5.8 外键 foreign key

知识点

1. 作用：约束两张表的数据关联性，保证从表数据一定在主表存在，杜绝脏数据

主表：被引用的表（班级表）

从表：设置外键的表（学生表）

2. 语法

foreign key(从表字段) references 主表名(主表主键字段)

3. 约束规则
1）从表外键的值，要么等于主表已经存在的值
2）从表外键的值，要么直接为NULL
3）主表被从表引用的数据，不能随意删除
4）主表被引用列，必须是主键或者唯一键

开发提醒：MySQL外键是数据库层校验；大型互联网项目一般不在数据库建立外键，业务代码层面做逻辑校验

示例代码

-- 1.先建【主表】班级表 create table myclass ( id int primary key, name varchar(30) not null comment '班级名' ); -- 2.再建【从表】学生表，设置外键关联班级id create table stu ( id int primary key, name varchar(30) not null comment '学生名', class_id int, foreign key (class_id) references myclass(id) ); -- 主表插入班级 insert into myclass values(10,'C++大牛班'),(20,'java大神班'); -- 合法，班级10、20主表里存在 insert into stu values(100,'张三',10),(101,'李四',20); -- ❌报错：班级30不存在，外键约束拦截 insert into stu values(102,'wangwu',30); -- ✅合法：外键给NULL，学生暂时没有分配班级 insert into stu values(102,'wangwu',null);

5.9 综合建表案例（商店业务三张表）

业务说明：商品表、客户表、购买订单表，主外键关联约束

需求清单
1.每张表设置主键、自增
2.客户姓名不能为空
3.邮箱不能重复（unique唯一键）
4.性别只能：男 / 女（enum枚举）

-- 创建数据库 create database if not exists bit32mall default character set utf8 ; use bit32mall; -- 商品表 goods create table if not exists goods ( goods_id int primary key auto_increment comment '商品编号', goods_name varchar(32) not null comment '商品名称', unitprice int not null default 0 comment '单价，单位分', category varchar(12) comment '商品分类', provider varchar(64) not null comment '供应商名称' ); -- 客户表 customer create table if not exists customer ( customer_id int primary key auto_increment comment '客户编号', name varchar(32) not null comment '客户姓名', address varchar(256) comment '客户地址', email varchar(64) unique key comment '电子邮箱', sex enum('男','女') not null comment '性别', card_id char(18) unique key comment '身份证' ); -- 购买订单 purchase（从表，双外键） create table if not exists purchase ( order_id int primary key auto_increment comment '订单号', customer_id int comment '客户编号', goods_id int comment '商品编号', nums int default 0 comment '购买数量', foreign key (customer_id) references customer(customer_id), foreign key (goods_id) references goods(goods_id) );

📌约束面试重点总结

1. not null：字段禁止为空；default不传值自动填充预设内容

2. zerofill：仅查询显示补零，存储数值不变，自动unsigned无符号

3. primary key：主键，非空+唯一，一张表只能1个主键，支持复合主键

4. auto_increment：自增，必须绑定整数索引（主键），单表只能1个

5. unique key：唯一键，可以多个，可以存NULL，只约束业务字段不重复

6. foreign key外键：关联两张表，从表数据必须在主表存在或者NULL，管控数据完整性

7. comment注释仅文档作用，不参与任何校验逻辑