数据库学习总结

Honkers · 2025-8-1 11:06:37

引言

1 数据库管理系统 database

1.1 数据库概述

1.1.1 存储

1.1.2 数据库管理系统 DBMS

1.1.3 数据库管理系统分类

1.2 MySQL 核心操作

1.2.1 MySQL 命令（特有）

1.2.2 MySQL 服务

1.3 数据对象

1.4 表

2 SQL

2.1 数据库的数据类型

2.2 DDL 示例

2.3 DML 数据库操作语言

2.4 上传文件

2.4.1 mysql 导入 hr.sql

2.5 sql 查询

2.6 表连接

3 函数与子查询

4 函数和事务处理

4.1 函数

4.2 事务处理

4.2.1 事务

4.3 交集、并集、差集

4.4 事物操作

4.5 死锁

5 MySQL

5.1 安装客户端库

5.2 创建用户

5.3 MySQL API

5.4 索引

5.5 数据库备份与恢复

5.6 数据库调优

6 总结

引言

数据库索引是提升查询性能的关键技术之一，大多数现代数据库系统（如MySQL、Oracle、PostgreSQL等）的核心索引结构基于B+树实现。B+树通过其平衡的多层树形结构和有序的叶子节点链表，高效支持范围查询、等值查询及排序操作，同时减少了磁盘I/O次数。相比哈希索引的单一键值查找或B树的分散数据存储，B+树在范围扫描和顺序访问场景中表现更优，成为关系型数据库索引的主流选择。其设计权衡了查询效率、插入删除性能及存储开销，是数据库系统中平衡读写性能的重要基石。

1 数据库管理系统 database

1.1 数据库概述

1.1.1 存储

- 文件系统：FILE fopen fclose, 少量数据、配置信息

- 数据库管理系统：结构化、大量的数据

1.1.2 数据库管理系统 DBMS

是一套软件，用于创建、操作、管理 `数据库`

数据库：磁盘上的一组文件，包含：数据文件、日志文件、控制文件

数据库实例：由后台进程、内存结构、数据库构成

一个数据库，可以启动多个实例（集群），一个实例对应一个数据库，数据库实例是操作数据库的方式

1.1.3 数据库管理系统分类

- 关系型数据库：基于关系模型、使用SQL操作数据、传统的数据存储方式

- 商业产品

- Oracle #1

- Db2 IBM #2

- SQL Server

- 开源产品

- PostgreSQL 最先进的 C

- MySQL 最受欢迎 C++ #1

- SQLite 嵌入式数据库

- 国产的数据库产品：

- NoSQL：Not Only SQL，是在特定场景替代关系数据库

- 文档数据库：mongoDB 可以部分替代关系数据库

- 键值数据库：redis 缓存、数据结构存储和运算

- 列数据库：clickhouse/hbase 大数据场景的数据存储、

- 图数据库：neo2j 数据结构图相关的操作与算法

1.2 MySQL 核心操作

1.2.1 MySQL 命令（特有）

1. 连接服务器

mysql
mysql -u用户名 -p密码 -h主机名127.0.0.1 -P端口号3306

复制代码

2. 显示基本信息

\s 服务器状态信息
\h 帮助信息

复制代码

3. 显示数据库

show databases;

复制代码

4. 连接数据库

use 数据库名;

复制代码

5. 显示表

show tables;

复制代码

6. 显示表结构

desc 表名称;
显示表中的数据
select * from 表名称;

复制代码

7. 创建数据库

create database xyz;

复制代码

1.2.2 MySQL 服务

0. 查找服务是否存在

ps -ef | grep mysql
service mysql status
service mysql start

复制代码

1. 安装服务

sudo apt update
sudo apt upgrade
sudo apt install mysql-server

复制代码

1.3 数据对象

- 表：数据存储在表中，表由行和列构成的二维存储结构

- 索引：占空间，为提升搜索的速度

- 视图：虚拟的表，是查询语句，不占用存储空间

- 函数：

- 过程：存储过程，命名的 SQL 代码块，需要调用才执行

- 触发器：特殊的过程，在特定的时间或事件发生时自动执行

- 包：一个命名空间，把相关的数据对象封装起来

1.4 表

列：由多个‘字段’构成，每个字段存储单一的数据项，列的先后顺序对表没有影响

行：记录，一个表中不能存在完全相同的两行，行的顺序对表没有影响

主键：primary key 表中一列或多列组合起来唯一标识表中的一条记录

主键的选取：

- 业务主键：使用业务中有实际意义的一个字段做主键；身份证、手机号、学号、车牌号

- 代理主键：使用一个无意义的数值；自增长的序列、UUID

外键：foreign key ，用于连接另一张表，是连接表中的主键或唯一键

外键约束，数据的一致性。

关系范式：一套约束、确保表格式的设计符合关系模型

- 1NF：列的值是单一的数据项，不可拆分

- 2NF：主键依赖，表中的非键字段都直接依赖于主键

- 3NF：非键字段之间不存在依赖关系

意义：

- 降低数据冗余（一些数据，只存一次，而非存储多次）

- 完整性

- 一致性

反范式：满足了范式后，基于业务、性能的需求去打破或违反范式

- 业务

- 性能

2 SQL

- DDL 数据定义语言

- DML 数据操作语言

- TCL 事物控制语言

DDL 创建、修改、删除数据对象（表、视图、索引 ...）

- 'CREATE' 创建

- 'ALTER' 修改

- 'DROP' 删除

- 'TRUNCATE' 截断

- 'GRANT' 授权

DML 数据操作语言（CRUD CreateReadUpdateDelete 增删改查）

- INSERT 插入

- SELECT 查找

- UPDATE 更新

- DELETE 删除

TCL 事物控制语句 *

- BEGIN 开始

- COMMIT 提交

- ROLLBACK 回滚

-- 创建数据库
create database 数据库名称;
use 数据库名称;
-- 创建表
CREATE TABLE order
(
'id' INT PRIMARY KEY,
'name' VARCHAR(16),
'tel' CHAR(11),
''
);

复制代码

2.1 数据库的数据类型

- 数值

- bit

- tinyint 1

- smallint 2

- int 4 *

- bigint 8

- decimal *

- double

- 字符串（文本）

- CHAR 固定

- VARCHAR 可变长度 n+1

- BINARY 二进制

- BLOB 大二进制对象

- TEXT 比较长的字符串

- LONGTEXT

- ENUM 枚举

- SET 集合

- 日期与时间

- DATE

- TIME

- DATETIME

- timestamp 时间戳，精度高，秒之后六位

- YEAR

- JSON（非关系型数据库范畴，文档数据库的核心）

- 数组 []

- 对象 {}

- 空间数据

CREATE TABLE staff
(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(16) NOT NULL,
gender ENUM('M', 'F') DEFAULT 'M',
tel CHAR(11),
birthday DATE DEFAULT '2000-1-1'
);

复制代码

2.2 DDL 示例

create table tb1
(
id int primary key auto_increment,
info1 char(6) not null,
info2 char(6),
time datetime default now()
);
desc tb1; 查看表
insert into tb1 values(1, 'info 1', 'info 2', '2025-5-28');
insert into tb1(info1) values('hello');
select * from tb1;
select id, info1, info2, time from tb1;
-- 投影
select id, info1, 1+2 from tb1;
-- 修改表结构
alter table tb1 add age int;
alter table tb1 add tel char(11) default '10086';
alter table tb1 drop age;
alter table tb1 change tel phone char(14);
delete from tb1 where id=9;
-- 备份一张表，包括了结构与数据
create table tb2
select * from tb1;
-- 创建一张空表
create table tb3
select * from tb1 where 1>2;
create table tb4
select id, info1 from tb1;
-- 删除表：结构和数据
drop table tb1;
-- 删除表中的一行或多行数据
delete from tb1;
delete form tb1 where id=2;
-- 截断：删除数据，保留表结构，先drop再create（会释放空间，自动增长从头开始）
truncate table tb2;
-- 更新
update tb1 set info2='x1', time='1999-2-2', info1='aaa' where id=1;
-- 导入 SQL 脚本
source /opt/data.sql

复制代码

2.3 DML 数据库操作语言

- 'INSERT'

- 'SELECT' *

- 'UPDATE'

- 'DELETE'

-- 投影
select
col1,
col2 as col_xxx,
col3 nick
from
tb1
(LEFT | RIGHT | FULL) JOIN
tb2;
ON
tb1.pk = tb2.pk
WHERE
col1 > 42
GROUP BY
col2
HAVING
col < 3
ORDER BY
col1, col2 DESC
LIMIT
5
OFFSET
100;

复制代码

2.4 上传文件

ssh 安全 shell

scp 安全 copy

scp 源目标(root@地址ip:路径)

2.4.1 mysql 导入 hr.sql

source /home/jkh/mysql/hr.sql

复制代码

2.5 sql 查询

-- 查询员工的编号，姓名，工资
select id,name,salary
from staff
-- 按照工资降序排列
order by salary desc
-- 只要前三个
limit 3;
-- 查询 2000 年以后出生的员工信息，编号、姓名、生日、工资
select id,name,birth,salary
from staff
where birth >= '1990-01-01' and birth < '2000-01-01';
(where birth between '1990-01-01' and '2000-01-01';)
-- 工资在 5000-10000;
where salary between 5000 and 10000;
-- 查询2000年以后出生的员工，编号、姓名、生日、工资，按照工资降序排列，只要前三
select id,name,birth,salary
from staff
where birth >= '2000-01-01'
order by salary desc
limit 3;

复制代码

2.6 表连接

- 笛卡尔乘积

- 内连接(等值连接)

- 外连接

- 左外连接

- 右外连接

- 全连接

-- 笛卡尔乘积
select * from staff,dept;
-- 内连接(两张表中的交集部分)
select
*
from
staff
inner join
dept
on
staff.dept_id = dept.id;
select
*
from
staff,dept
where
staff.dept_id = dept.id;
-- 左外连接
select
*
from
staff s
left join
dept d
on
s.dept_id = d.id;
-- 右外连接
select
*
from
staff s
right join
dept d
on
s.dept_id = d.id;
-- 查询各个员工的编号、姓名、部门及工作地点
select
s.id,s.name,d.name dept,city
from
staff s
left join
dept d
on
s.dept_id = d.id;
-- 查询员工的编号、姓名、老板的名字
select
s.id,s.name,b.name
from
staff s
left join
staff b
on
s.mgr = b.id;

复制代码

3 函数与子查询

函数

- 内置函数

- 自定义函数

分类

- 多行（聚合）函数

- `max`

- `min`

- `avg`

- `sum`

- `count`

- 单行函数

- 数值

- 字符串

- 时间和日期

- 其他

--
select
min(salary),max(salary),avg(salary),sum(salary),count(salary),
sum(salary)/count(salary) avg
from
staff;
select
gender,
min(salary),
max(salary),
avg(salary),
count(gender)
from
staff
group by
gender;
select
level,
min(salary),
max(salary),
avg(salary),
count(id) n
from
staff
group by
level
having
n > 1;
select
count(id)
from
staff;
select
d.name dept,
min(s.salary) sal
from
staff s
right join
dept d
on
s.dept_id = d.id
group by
d.name
order by
min(s.salary);
select distinct gender from staff;
-- 各个部门有多少员工，部门编号、部门名称、员工人数
select
d.id,
d.name,
count(s.id)
from
staff s
right join
dept d
on
s.dept_id = d.id
group by
d.id, d.name;
-- 各个城市有多少个员工
select
d.city city,
count(s.id) n,
avg(salary) sal
from
staff s
right join
dept d
on
s.dept_id = d.id
group by
city
order by
n desc;
select
m.id,
m.name,
count(s.id)
from
staff s
left join
staff m
on
s.mgr = m.id
where
m.id is not null
group by
m.id;
-- 工资大于平均工资的员工
select
*
from
staff
where
salary > (
select
avg(salary)
from
staff
);
-- 获得与 jack 同一个部门的员工
select
*
from
staff
where
dept_id = (
select
dept_id
from
staff
where
name = 'jack'
);

复制代码

4 函数和事务处理

4.1 函数

- 聚合函数 group by having

- max

- min

- avg

- sum

- count

- 单行函数

- 数值 : round,floor,ceiling,abs,pow

- 字符串 : concat,trim,ltrim,rtrim,left,right,mid,char_length,replace,lower,upper,md5,sha1,sha2

- 日期和时间 : now,sysdate,curdate,curtime,timestamp,adddate,datediff,timediff,date_format

- json

4.2 事务处理

4.2.1 事务

一个业务操作包含多个数据操作，多个数据操作构成一个事物

特性

- A 原子性: 构成一个事物的多个数据操作是一个整体，不可切分，执行的结果要么都成功(提交)，要么都撤销(回滚)

- C 一致性: 事物执行前后，数据状态总体一致

- I 隔离性: 多个事物相互隔离，不能交织执行

- D 持久性: 事物结束，数据写入磁盘

隔离级别脏读不可重复读幻读

- 读未提交 + + +

- 读已提交 - + +

- 不可重复读 - - + *

- 串行化 - - -

-- 账户
create database bank;
use bank;
create table account
(
`id` int primary key auto_increment,
`name` varchar(16),
`balance` decimal(9,2)
);
insert into account values(1,"alice",10000);
insert into account values(2,"bob",10000);
insert into account values(3,"jack",10000),(4,"rose",10000);
-- 保存交易记录
create table log
(
`id` int primary key auto_increment,
`from` int,
`to` int,
`amount` decimal(9,2),
`time` datetime default now()
);

复制代码

ifnull(将null替换为其他值)(再SQL中任何数+null都为null)

select
name,ifnull(salary,0)+ifnull(bonus,0) income
from
staff;
select
name, bonus
from
staff
where
bonus is null;
select
name, bonus
from
staff
where
bonus is not null;
-- in, not in
select
name,dept_id
from
staff
where
dept_id in (1,2,3);
-- dept_id not in (1,2,3);
-- dept_id = 1 or dept_id = 2 or dept_id = 3;
select
avg(salary),
round(avg(salary),2),
floor(avg(salary)),
ceiling(avg(salary))
from
staff;
select
3*7,
abs(-9),
pow(2,3)
from
dual;
select
name,
level,
concat(name," is ",level)
from
staff;
select
quote(' hello '),
quote(ltrim(' hello ')),
quote(rtrim(' hello ')),
quote(trim(' hello '))
from
dual;
select
name,
left(name,1),
mid(name,2,5),
right(name,3)
from
staff;
select
name,
char_length(name)
from
staff;
select
name,
sha2(name,256)
from
staff;
-- 时间与日期
select
now(),
curdate(),
curtime(),
sysdate()
from
dual;
select
adddate(curdate(), 100),
datediff(curdate(), '2000-1-1'),
datediff(curdate(), '2025-12-31')
from
dual;
select
curtime(),
timediff(curtime(), '12:06')
from
dual;
select
birth,
date_format(birth, '%m/%d')
from
staff
where
date_format(birth, '%m') = '02';
-- like 模糊查询
-- _ 一个字符
-- % 任意字符
select
name
from
staff
where
name like '%a%';

复制代码

4.3 交集、并集、差集

create table t1(
id int,
info char(4)
);
insert into t1 values(1, 'a');
insert into t1 values(2, 'b');
insert into t1 values(3, 'c');
insert into t1 values(4, 'd');
create table t2(
id int,
info char(4)
);
insert into t2 values(1, 'c');
insert into t2 values(2, 'd');
insert into t2 values(3, 'e');
insert into t2 values(4, 'f');
-- 交集
select
t1.info
from
t1
inner join
t2
where
t1.info = t2.info;
-- 并集加 all 相同值会重复出现
select
info
from
t1
union all
select
info
from
t2;
-- 差集
-- 性能差
select
info
from
t1
where
info
not in(
select
info
from
t2
);
-- 推荐外连接再过滤
select t1.info
from t1
left join t2
on t1.info = t2.info
where t2.id is null;

复制代码

4.4 事物操作

默认为自动事物

手动事物:

- begin

- rollback

- commit

-- 1号给2号 1000 元
begin;
update account set balance = balance - 1000 where id = 1;
update account set balance = balance + 1000 where id = 2;
insert into log(`from`, `to`, `amount`) values(1,2,1000);
commit;

复制代码

4.5 死锁

-- 事物1
begin;
update account set balance = balance - 1000 where id = 1; -- 1
update account set balance = balance + 1000 where id = 2; -- 3
commit;
-- 事物2
begin;
update account set balance = balance - 10 where id = 2; -- 2
update account set balance = balance + 100 where id = 1; -- 4
commit;

复制代码

5 MySQL

MySQL C++

PostgreSQL C

SQLite C

数据库的**连接器**或**客户端**: C/C++, Python, Java, Go

5.1 安装客户端库

sudo apt-get install libmysqlclient-dev
安装头文件和库文件
/usr/include/mysql
/usr/lib/mysql
编译指令
gcc my.c -o my -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient
替代方案: makefile

复制代码

5.2 创建用户

sudo mysql
create user jkh@'%' identified by 'jkh';
授权 root 访问数据库 bank 中所有的数据对象
grank all on bank.* to root@'%'

复制代码

5.3 MySQL API

结构体

- MySQL 数据库连接，

- MySQL_RES result 结果集, 查询返回的多行数据构成的内存结构

- MySQL_ROW 一行记录，字符串数组，row[0],row[1],row[n-1]

- MySQL_ROWS 由 MYSQL_ROW 构成的链表

函数

- `mysql_init` 初始化数据库连接，没有连接

- `mysql_close` 释放数据库连接

- `mysql_error` 显示错误信息，字符串显示

- `mysql_real_connect` 建立数据库连接，TCP

- `mysql_query` 执行SQL语句: insert，update，delete，select

- `mysql_store_result` 加载select结果集, 使用缓存，

- `mysql_use_result` 加载select结果集

- `mysql_free_result` 释放结果集

- `mysql_num_rows` 结果集中的元数据：行数

- `mysql_num_fields` 结果集中的元数据：列数

- `mysql_fetch_row` 从结果集获得一行数据

- `mysql_insert_id` 获得插入记录的自动生成的id

5.4 索引

没有索引，默认全表扫描，性能比较差

- 主键字段自动创建索引

- where 过滤条件中的字段可以手动创建索引

优点：提升搜索的速度

缺点：占存储空间，在插入数据和删除数据后索引会重建，性能有损耗

具体实现：B+ 树

B树：分支节点和叶子节点中都存储了数据

B+树：叶子节点存储数据，叶子节点使用双向链表连接了起来

5.5 数据库备份与恢复

备份：导出

mysqldump -u root -p --databases hr > d:\hr.sql

复制代码

恢复：导入

mysql > source d:\hr.sql

复制代码

Oracle/DB2

- exp 导出

- imp 导入

5.6 数据库调优

- SQL优化：不使用 select * ，使用 join 替代子查询，不使用 IN \ NOT IN ，使用执行计划分析 SQL 语句的性能

- 索引，提升到原有的 1/10

- 分表：垂直分表（业务：），水平分表

- range

- list

- hash

- 分库：分布式的主从 Master/Worker 结构、做读/写分离

- 缓存：使用 redis 做缓存

-- 窗口函数
-- row_number()
-- union
select id,name,salary,
row_number() over(order by salary desc)
from staff;
select id,name,salary,
dense_rank() over(order by salary desc)
from staff;
create view v_staff
as
select id,name,salary,dept_id,
row_number() over(partition by dept_id order by salary desc) n
from staff;
select count(id) from dept
union all
select count(id) from staff;

复制代码

6 总结

总结来说，B+树因其高效的查询性能、优秀的范围查询支持以及较低的磁盘I/O开销，成为大多数数据库系统索引的首选数据结构。相较于哈希索引和B树，B+树在保持数据有序性的同时，通过多路平衡和叶子节点链表优化了顺序访问和范围扫描，使其特别适合数据库的大规模数据存储和高并发访问需求。尽管索引会占用额外存储空间并可能影响写入性能，但其在加速数据检索方面的优势使其成为现代数据库不可或缺的核心技术之一。

[数据库] 数据库学习总结

引言

1 数据库管理系统 database

1.1 数据库概述

1.1.1 存储

1.1.2 数据库管理系统 DBMS

1.1.3 数据库管理系统分类

1.2 MySQL 核心操作

1.2.1 MySQL 命令（特有）

1.2.2 MySQL 服务

1.3 数据对象

1.4 表

2 SQL

2.1 数据库的数据类型

2.2 DDL 示例

2.3 DML 数据库操作语言

2.4 上传文件

2.4.1 mysql 导入 hr.sql

2.5 sql 查询

2.6 表连接

3 函数与子查询

4 函数和事务处理

4.1 函数

4.2 事务处理

4.2.1 事务

4.3 交集、并集、差集

4.4 事物操作

4.5 死锁

5 MySQL

5.1 安装客户端库

5.2 创建用户

5.3 MySQL API

5.4 索引

5.5 数据库备份与恢复

5.6 数据库调优

6 总结

新人须知

常见问题

关于我们