MySQL 数据库

数据库基础

数据库的分类

关系型数据库使用表保存数据，并通过主键、外键等机制描述表之间的关系。常见关系型数据库包括 MySQL、Oracle、DB2 和 SQL Server。

非关系型数据库通常不使用固定的二维表模型，常见类型包括键值数据库、文档数据库和列式数据库。Redis 是常见的键值数据库。

“非关系型”并不表示数据之间一定没有任何关系，而是表示它不以传统关系模型作为主要组织方式。

基本概念

概念	说明
数据	数字、文本、图片、音频和视频等信息
数据库	按一定结构组织和保存数据的集合，简称 DB
数据库管理系统	管理数据库的软件，简称 DBMS
数据库系统	数据库、DBMS、应用程序和相关人员等组成的整体，简称 DBS

一个项目可以使用一个或多个数据库，同一个数据库也可以服务于多个业务模块，具体划分取决于系统架构。

MySQL 安装与目录

在 Windows 环境安装 MySQL 时，应避免使用包含特殊字符的计算机名和安装路径。不同版本、安装方式和操作系统的目录可能不同。

常见目录包括：

程序安装目录：保存 MySQL Server、客户端和相关工具。
数据目录：保存数据库文件、日志和配置数据。
配置文件：Windows 常见为 my.ini，Linux 常见为 my.cnf。

卸载旧版本时，应先备份重要数据，再停止并删除对应服务。不要在不确认数据用途的情况下直接删除数据目录。

SQL 语句分类

SQL 是操作关系型数据库的结构化查询语言。

分类	作用	常见关键字
DDL	定义数据库对象	`CREATE`、`ALTER`、`DROP`
DML	操作表中的数据	`INSERT`、`UPDATE`、`DELETE`
DQL	查询数据	`SELECT`
DCL	控制用户和权限	`GRANT`、`REVOKE`
TCL	控制事务	`COMMIT`、`ROLLBACK`、`SAVEPOINT`

MySQL 常用数据类型

整数类型

常见整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT。选择类型时应根据业务范围确定，避免无意义地使用过大类型。

定点数和浮点数

金额等需要精确计算的数据应优先使用 DECIMAL。

salary decimal(9, 2)

DECIMAL(9, 2) 表示总共最多九位十进制数字，其中两位是小数。

FLOAT 和 DOUBLE 属于近似数值类型，适合可以接受浮点误差的场景，不适合直接保存需要精确计算的金额。

字符串类型

CHAR(n)：定长字符串，适合长度基本固定的数据。
VARCHAR(n)：变长字符串，适合长度变化较大的文本。
TEXT：用于保存较长文本。

字符串字面量通常使用单引号。

日期和时间类型

DATE：保存日期。
TIME：保存时间。
DATETIME：保存日期和时间。
TIMESTAMP：保存时间戳，范围和时区转换行为与 DATETIME 不同。

二进制类型

BLOB 用于保存二进制数据。大型图片、音频和视频通常更适合保存在对象存储或文件系统中，数据库保存文件地址和元数据。

DDL 数据定义语句

创建表

基本语法

create table 表名 (
    列名 数据类型 列属性,
    列名 数据类型 列属性
);

表名和列名应清晰表达业务含义，并保持统一命名风格。

创建学生表

create table student (
    sno int,
    sname varchar(16),
    birthday date,
    height decimal(3, 2),
    tel char(11)
);

每条 SQL 建议使用分号结束。

列属性

默认值

status tinyint not null default 0

插入数据时省略该列，数据库会使用默认值。

自增属性

AUTO_INCREMENT 常用于整数主键。插入数据时省略该列或传入 NULL，数据库会生成下一个序号。

create table student (
    sno int auto_increment,
    sname varchar(16),
    birthday date,
    height decimal(3, 2),
    tel char(11),
    classno int,
    constraint pk_student primary key (sno)
);

自增列必须建立索引，并且一个表只能有一个自增列。它通常与主键配合使用。

约束

约束用于保证数据完整性和一致性。违反约束的数据不能成功写入。

主键约束

主键用于唯一标识一条记录，主键值必须唯一且不能为 NULL。一个表只能有一个主键，但主键可以包含多个列。

列级写法：

create table student (
    sno int primary key,
    sname varchar(16),
    birthday date
);

表级写法：

create table student (
    sno int,
    sname varchar(16),
    birthday date,
    constraint pk_student primary key (sno)
);

外键约束

外键用于保证引用完整性。外键列中的非空值必须能够在被引用表的候选键中找到。

应先创建被引用表，再创建引用表。

drop table if exists student;
drop table if exists classes;

create table classes (
    classno int,
    classname varchar(32),
    constraint pk_classes primary key (classno)
);

create table student (
    sno int,
    sname varchar(16),
    birthday date,
    height decimal(3, 2),
    tel char(11),
    classno int,
    constraint pk_student primary key (sno),
    constraint fk_student_classno
        foreign key (classno)
        references classes(classno)
);

插入数据时，应先插入被引用表，再插入引用表。删除数据时，应先处理引用记录，再删除被引用记录。

外键可以设置引用动作：

constraint fk_student_classno
    foreign key (classno)
    references classes(classno)
    on delete set null
    on update cascade

CASCADE 和 SET NULL 会自动影响关联数据，使用前应确认业务语义。逻辑删除则通常通过状态字段标记记录，而不是执行物理删除。

唯一约束

唯一约束用于限制列或列组合不能出现重复值。

constraint uk_student_tel unique (tel)

在 MySQL 中，唯一索引通常允许出现多个 NULL。如果业务要求该列必须有值，还需要同时添加 NOT NULL。

非空约束

sname varchar(16) not null

检查约束

constraint ck_student_birthday
    check (birthday < '2026-02-05')

MySQL 8 会执行 CHECK 约束。较早版本可能解析但不执行，因此应注意数据库版本。

修改表结构

ALTER TABLE 用于修改表名、列和约束。

修改表名

alter table student rename to student2;
alter table student2 rename to student;

添加列

alter table student
add address varchar(255);

修改列名和类型

alter table student
change address addr varchar(255);

alter table student
modify addr varchar(32);

CHANGE 可以同时修改列名和类型，MODIFY 只修改列定义。

删除列

alter table student
drop column addr;

添加约束

alter table student
add constraint pk_student primary key (sno);

alter table student
add constraint fk_student
foreign key (classno)
references classes(classno);

alter table student
add constraint uk_student_tel unique (tel);

删除表

drop table if exists student;

删除表会同时删除表结构和数据，操作前应确认备份。

DML 数据操作语句

插入数据

基本语法

insert into 表名 (列名, 列名)
values (值, 值);

列名数量、顺序和对应值必须匹配。推荐明确写出列名，避免表结构变化影响代码。

插入一条记录

insert into student(sname, birthday)
values('Jack', '2026-01-01');

使用默认值

insert into student
values(default, 'Rose', '2025-01-01', 1.70, '13312345678', null);

插入多条记录

insert into student(sname, birthday)
values
    ('Rose1', '2025-01-01'),
    ('Rose2', '2025-01-02');

不要依赖危险或不明确的隐式类型转换。例如无效日期字符串不应当作合法日期插入。

修改数据

update student
set height = 1.77,
    tel = '13312345678'
where sno = 3;

字段可以基于原值更新：

update student
set height = height + 0.03
where sno = 3;

执行 UPDATE 前应先确认 WHERE 条件。省略条件会修改整张表。

删除数据

delete from student
where sno = 5;

省略 WHERE 条件会删除表中全部记录。DELETE 删除数据但保留表结构。

SELECT 查询语句

简单查询

select *
from student;

推荐只查询业务需要的列：

select sno, sname, birthday
from student;

查询结果称为结果集。

表达式与空值处理

NULL 表示未知或缺失值。它与普通数值运算后，结果通常仍是 NULL。

select ifnull(lowest_sal, 0) + 100,
       highest_sal + 500
from job_grades;

连接字符串可以使用 CONCAT()：

select concat('86', tel)
from student;

列别名

select ifnull(lowest_sal, 0) + 100 as 最低工资,
       highest_sal + 500 as 最高工资
from job_grades;

AS 可以省略，但明确写出更易读。

去除重复记录

select distinct sname, birthday
from student;

DISTINCT 会对所选列的组合去重。

条件表达式

select case
           when lowest_sal < 3000 then '低工资'
           when lowest_sal between 3000 and 5000 then '中等工资'
           else '高工资'
       end as 工资等级,
       highest_sal
from job_grades;

条件查询

比较条件

select *
from student
where sname = 'Rose';

select *
from student
where sname <> 'Rose';

select *
from student
where height >= 1.70;

逻辑条件

select *
from student
where height >= 1.70
  and birthday < '2025-01-03';

select *
from student
where height >= 1.70
   or birthday > '2025-01-01';

AND 的优先级高于 OR。复杂条件应使用括号明确逻辑。

范围条件

BETWEEN 包含两端边界。

select *
from student
where height between 1.70 and 1.72;

模糊匹配

LIKE 中的 % 表示零个或多个任意字符，_ 表示一个任意字符。

select *
from student
where sname like 'J%';

select *
from student
where sname like 'J_c%';

select *
from student
where sname like '%a%';

集合条件

select *
from student
where sname in ('Rose', 'Jack', 'Tom');

空值条件

判断空值必须使用 IS NULL 或 IS NOT NULL。

select *
from student
where tel is null;

select *
from student
where tel is not null;

不能使用 tel = null 判断空值。

排序

select *
from student
order by height asc;

select *
from student
order by birthday desc,
         height desc;

ASC 表示升序，DESC 表示降序。ORDER BY 可以使用查询结果中的列别名，WHERE 通常不能使用同层查询定义的别名。

聚合函数

常用聚合函数包括 SUM()、AVG()、MAX()、MIN() 和 COUNT()。

select sum(salary),
       avg(ifnull(salary, 0)),
       max(salary),
       min(salary)
from employee;

除 COUNT(*) 外，大多数聚合函数会忽略 NULL。

select count(*)
from employee;

COUNT(*) 会统计结果集行数，并不是不推荐写法。是否使用 COUNT(*)、COUNT(1) 或 COUNT(非空列)，应以语义和执行计划为依据。

分组查询

`GROUP BY`

select department_id,
       avg(salary) as avg_salary,
       count(*) as employee_count
from employee
group by department_id;

查询列表中未参与聚合的列，应出现在 GROUP BY 中。启用 ONLY_FULL_GROUP_BY 时，MySQL 会严格检查该规则。

`HAVING`

WHERE 在分组前过滤行，HAVING 在分组后过滤分组结果。

select department_id,
       avg(salary) as avg_salary
from employee
where department_id in (5001, 5002)
  and salary > 5000
group by department_id
having avg(salary) > 6000
order by avg_salary;

分页查询

MySQL 使用 LIMIT 限制结果行数。

select *
from employee
limit 5;

select *
from employee
limit 5, 5;

LIMIT offset, row_count 中的偏移量从 0 开始。

第 pageNo 页、每页 pageSize 条记录时，偏移量为：

(pageNo - 1) * pageSize

不能直接把上述变量表达式写入普通静态 SQL，应用程序应先计算偏移量，再通过参数传入。

多表查询

笛卡尔积与连接条件

多个表直接写在 FROM 后，会形成笛卡尔积。应通过连接条件保留正确组合。

select *
from employee e,
     departments d
where e.department_id = d.department_id;

现代 SQL 更推荐显式 JOIN 语法。

内连接

select e.first_name,
       e.department_id,
       d.department_name
from employee e
inner join departments d
    on e.department_id = d.department_id
where e.department_id = 5001;

连接关系应写在 ON 中，针对最终结果的普通筛选条件通常写在 WHERE 中。

多表内连接

查询在北京工作的员工姓名、部门名称和城市：

select e.first_name,
       d.department_name,
       loc.city
from employee e
inner join departments d
    on e.department_id = d.department_id
inner join locations loc
    on d.location_id = loc.location_id
where loc.city = '北京';

不等值连接

select e.first_name,
       e.salary,
       j.grade_level
from employee e
inner join job_grades j
    on e.salary >= j.lowest_sal
   and e.salary < j.highest_sal;

外连接

左外连接保留左表全部记录。右表没有匹配记录时，右表列返回 NULL。

select e.first_name,
       e.department_id,
       d.department_name
from employee e
left join departments d
    on e.department_id = d.department_id;

查询所有程序员及其工作城市：

select e.first_name,
       loc.city
from employee e
left join departments d
    on e.department_id = d.department_id
left join locations loc
    on d.location_id = loc.location_id
where e.job_id = '程序员';

需要注意，若在 WHERE 中对右表列设置非空条件，可能会使左连接效果接近内连接。

自连接

自连接是同一张表以不同别名参与连接。

select e.first_name as employee_name,
       m.first_name as manager_name
from employee e
left join employee m
    on e.manager_id = m.employee_id;

查询员工及管理者的工作城市：

select e.first_name as employee_name,
       employee_location.city as employee_city,
       m.first_name as manager_name,
       manager_location.city as manager_city
from employee e
left join employee m
    on e.manager_id = m.employee_id
left join departments employee_department
    on e.department_id = employee_department.department_id
left join locations employee_location
    on employee_department.location_id = employee_location.location_id
left join departments manager_department
    on m.department_id = manager_department.department_id
left join locations manager_location
    on manager_department.location_id = manager_location.location_id;

全连接

MySQL 不直接支持 FULL OUTER JOIN。可以根据业务需要使用左连接、右连接和 UNION 模拟，但必须处理重复行。

多表更新

MySQL 支持在更新语句中使用连接。

update employee e
left join departments d
    on e.department_id = d.department_id
left join locations loc
    on d.location_id = loc.location_id
set e.salary = e.salary + 5
where loc.city = '北京';

MySQL 也支持多表删除，但语法与单表 DELETE 不同。执行前应谨慎确认影响范围。

子查询

子查询是嵌套在其他 SQL 中的查询。它可以出现在 WHERE、FROM、SELECT 等位置。

单行子查询

子查询只返回一个值时，可以配合普通比较运算符。

select first_name, salary
from employee
where salary > (
    select salary
    from employee
    where first_name = 'Rose'
);

若子查询返回多行，上述写法会报错。

多行子查询

多行子查询常与 IN、ANY、ALL 或 EXISTS 配合使用。

`IN`

select *
from employee
where department_id in (
    select department_id
    from departments
    where manager_id = 100
);

`EXISTS`

EXISTS 只判断子查询是否至少返回一行。

select *
from employee e
where exists (
    select 1
    from departments d
    where d.department_id = e.department_id
      and d.manager_id = 100
);

EXISTS 不一定总比 IN 快。优化器可能重写查询，应结合索引、数据分布和执行计划判断。

`ANY`

select first_name, salary
from employee
where salary > any (
    select salary
    from employee
    where department_id = 5001
);

表示工资大于子查询结果中的至少一个值。

`ALL`

select first_name, salary
from employee
where salary > all (
    select salary
    from employee
    where department_id = 5001
);

表示工资大于子查询结果中的每一个值。

`FROM` 子查询

FROM 后的子查询会形成派生表，并且必须设置别名。

select max(department_avg_salary)
from (
    select avg(salary) as department_avg_salary
    from employee
    group by department_id
) department_salary;

优化器可能合并或物化派生表，不能简单认定它永远“只执行一次”。

结果集联合

`UNION` 与 `UNION ALL`

UNION 和 UNION ALL 用于纵向合并结果集。各查询的列数必须相同，对应列的数据类型应兼容。

UNION 会去重，UNION ALL 不去重，后者通常开销更小。

select first_name as name
from employee
union
select city as name
from locations;

select first_name
from employee
union all
select first_name
from employee;

添加汇总行

select first_name,
       salary
from employee
union all
select '总计',
       sum(salary)
from employee;

行列结构转换示例

旧表以科目作为列：

create table score (
    sname varchar(16),
    shuxue int,
    yuwen int,
    yingyu int
);

新表以科目作为行：

create table score2 (
    sname varchar(16),
    kemu varchar(32),
    chengji int
);

可以使用 UNION ALL 转换指定学生的数据：

insert into score2(sname, kemu, chengji)
select sname, 'shuxue', shuxue
from score
where sname = 'Tom'
union all
select sname, 'yuwen', yuwen
from score
where sname = 'Tom'
union all
select sname, 'yingyu', yingyu
from score
where sname = 'Tom';

数据库备份

冷备份

冷备份是在数据库服务停止后复制数据文件。操作简单，但会造成停机，并且不能随意复制正在使用的数据目录。

热备份

热备份是在数据库服务运行期间完成备份。常见方式包括逻辑导出和使用支持在线备份的工具。

备份是否有效，应通过恢复演练验证。只创建备份文件而不验证恢复流程是不够的。

数据库事务

事务概念

事务是一组作为一个逻辑单元执行的操作。事务中的操作要么全部成功提交，要么失败后全部回滚。

本地事务中的操作通常在同一个数据库资源中完成。分布式事务涉及多个数据库或其他资源，需要额外的协调机制。

ACID 特性

特性	说明
原子性	事务中的操作要么全部完成，要么全部撤销
一致性	事务执行前后，数据满足既定完整性规则
隔离性	并发事务之间按照隔离级别控制可见性和影响
持久性	事务提交后，修改应被可靠保存

SQL 事务控制

MySQL 默认开启自动提交。关闭自动提交后，可以手动控制事务。

set autocommit = 0;

insert into departments(department_id, department_name)
values(5005, '行政部');

insert into employee(first_name, phone_number, department_id)
values('Jack', '13312346788', 5005);

commit;

发生错误时可以回滚：

rollback;

保存点

set autocommit = 0;

insert into departments(department_id, department_name)
values(5019, '行政部2');

savepoint after_department;

insert into employee(first_name, phone_number, department_id)
values('Jack', '13312346788', 5019);

rollback to after_department;
commit;

ROLLBACK TO 会回滚保存点之后的操作，不会自动结束事务。

日志与事务恢复

InnoDB 事务处理中常涉及以下日志：

undo log：保存旧版本信息，用于回滚和多版本并发控制。
redo log：记录页修改，用于崩溃恢复并支持持久性。
binlog：MySQL Server 层的逻辑日志，用于复制和时间点恢复。

不能简单理解为提交后才把所有数据一次性写入数据库文件。数据页、日志缓冲和磁盘刷写由数据库按照 WAL 等机制协调。提交成功通常要求相关日志达到配置要求，而数据页可以稍后由后台线程写回磁盘。

并发读取问题

脏读

一个事务读到了另一个事务尚未提交的数据。如果后者回滚，前者读取的数据就无效。

不可重复读

同一事务中多次读取同一行，结果不同，原因通常是其他事务提交了修改。

幻读

同一事务中按相同条件多次查询，返回的记录集合发生变化，原因通常是其他事务插入或删除了符合条件的行。

事务隔离级别

SQL 标准定义了四种隔离级别：

隔离级别	说明
`READ UNCOMMITTED`	允许读取未提交数据
`READ COMMITTED`	只能读取已提交数据
`REPEATABLE READ`	同一事务内重复读取通常保持一致
`SERIALIZABLE`	以更严格方式串行化并发访问

InnoDB 默认隔离级别通常是 REPEATABLE READ。它通过 MVCC 和锁机制处理一致性读与当前读。避免幻读并不等同于“始终给整张表加表锁”。InnoDB 可能使用记录锁、间隙锁和临键锁等机制。

锁的基本概念

共享锁：允许其他事务继续读取，但限制冲突写入。
排他锁：用于修改数据，限制其他事务对相同资源的冲突访问。
行级锁：锁定索引记录或范围，并发度通常较高。
表级锁：锁定整张表，并发度通常较低。

锁的实际范围与索引、SQL 条件和隔离级别有关。缺少合适索引可能扩大扫描和加锁范围。

视图

视图是基于查询定义的虚拟表。普通视图通常不单独保存一份结果数据，查询视图时由数据库处理其定义。

create view v_emp as
select e.employee_id,
       e.first_name,
       e.salary,
       e.department_id
from employee e;

select *
from v_emp;

简单单表视图在满足条件时可以更新：

update v_emp
set salary = 8100
where employee_id = 100;

包含聚合、分组、DISTINCT、联合等结构的视图通常不可直接更新。

视图的作用

封装复杂查询。
对外提供稳定的数据访问结构。
限制用户只能访问部分行和列。
简化上层程序的查询代码。

视图不能完全替代表结构版本管理。基础表发生不兼容变化时，视图本身也可能需要修改。

`WITH CHECK OPTION`

create view v_beijing_employee as
select *
from employee
where department_id = 5001
with check option;

通过该视图新增或修改数据时，结果必须仍满足视图条件。

存储过程

存储过程可以封装一组 SQL 和流程控制语句，并通过参数接收输入或返回输出。

drop procedure if exists proc_get_user_info;

delimiter //

create procedure proc_get_user_info(
    in p_user_id int,
    in p_include_address boolean,
    out p_result_code int
)
begin
    declare v_error int default 0;
    declare continue handler for sqlexception set v_error = 1;

    set p_result_code = 0;

    if p_include_address then
        select id,
               username,
               age,
               address,
               create_time
        from t_user
        where id = p_user_id;
    else
        select id,
               username,
               age,
               create_time
        from t_user
        where id = p_user_id;
    end if;

    if v_error = 1 then
        set p_result_code = 1;
        select '查询用户信息失败' as error_msg;
    end if;
end //

delimiter ;

调用示例：

call proc_get_user_info(1, true, @result_code);
select @result_code;

自定义函数

自定义函数接收参数并返回一个值，适合封装可复用计算逻辑。

drop function if exists get_salary_level;

delimiter //

create function get_salary_level(p_salary decimal(10, 2))
returns varchar(20)
deterministic
begin
    if p_salary < 3000 then
        return '低工资';
    elseif p_salary <= 5000 then
        return '中等工资';
    else
        return '高工资';
    end if;
end //

delimiter ;

select first_name,
       get_salary_level(salary)
from employee;

触发器

触发器会在指定表发生 INSERT、UPDATE 或 DELETE 事件时自动执行。

下面的示例限制数据只能在每天八点到十七点之间写入：

drop trigger if exists trg_employee_insert_time;

delimiter //

create trigger trg_employee_insert_time
before insert on employee
for each row
begin
    if current_time() < '08:00:00'
       or current_time() > '17:00:00' then
        signal sqlstate '45000'
            set message_text = '当前时间不允许新增员工数据';
    end if;
end //

delimiter ;

触发器会隐式执行，使用过多会增加排查难度。适合数据库级审计或强制约束的场景，不应把全部业务逻辑都放入触发器。

MySQL数据库

MySQL 数据库

数据库基础

数据库的分类

基本概念

MySQL 安装与目录

SQL 语句分类

MySQL 常用数据类型

整数类型

定点数和浮点数

字符串类型

日期和时间类型

二进制类型

DDL 数据定义语句

创建表

基本语法

创建学生表

列属性

默认值

自增属性

约束

主键约束

外键约束

唯一约束

非空约束

检查约束

修改表结构

修改表名

添加列

修改列名和类型

删除列

添加约束

删除表

DML 数据操作语句

插入数据

基本语法

插入一条记录

使用默认值

插入多条记录

修改数据

删除数据

SELECT 查询语句

简单查询

表达式与空值处理

列别名

去除重复记录

条件表达式

条件查询

比较条件

逻辑条件

范围条件

模糊匹配

集合条件

空值条件

排序

聚合函数

分组查询

GROUP BY

HAVING

分页查询

多表查询

笛卡尔积与连接条件

内连接

多表内连接

不等值连接

外连接

自连接

全连接

多表更新

子查询

单行子查询

多行子查询

IN

EXISTS

ANY

ALL

FROM 子查询

结果集联合

UNION 与 UNION ALL

添加汇总行

`GROUP BY`

`HAVING`

`IN`

`EXISTS`

`ANY`

`ALL`

`FROM` 子查询

`UNION` 与 `UNION ALL`

`WITH CHECK OPTION`