数据库初阶笔记

一、数据库基础

1. 数据库的基本作用

• 数据库是一类软件，用于组织和管理大量数据，方便后续的增删改查（CRUD）
• 常见数据库操作：创建、读取、更新、删除数据

2. 数据库的基础原理

• 数据库底层用到了各种数据结构（如B+树、哈希表等）来实现高效的数据管理
• 数据库系统不仅仅存储数据，还负责数据的安全、完整性、并发控制等

3. 数据库编程

• 通过SQL等语言与数据库进行交互，实现数据的管理和应用开发

---

二、数据库与数据结构的区别

项目	数据库	数据结构
定义	一类组织和管理数据的软件	一门组织数据的学科
目的	实现数据的高效管理	提供数据组织方法
关系	用数据结构实现底层功能	为数据库等提供理论基础

---

三、主流数据库软件介绍

1. 关系型数据库（RDBMS）

Oracle

• 商业收费，功能强大，适用于数据极其重要、预算充足的大型企业
• 提供完善的技术支持和服务，价格昂贵

MySQL

• 开源免费，广泛应用于互联网企业
• 后被Oracle收购，阿里等国内公司有改进版本

MariaDB

• MySQL原开发者分支出来的新版本，兼容MySQL

SQL Server

• 微软出品，常用于Windows生态，销售策略不佳

SQLite

• 轻量级数据库，体积小（约1MB），适合嵌入式场景

关系型数据库特点

• 数据以"表格"形式组织，结构严格
• 每个表有多行（记录）、多列（字段），每列有特定类型和含义
• 支持复杂的数据操作和完整性校验

2. 非关系型数据库（NoSQL）

• 代表产品：Redis、MongoDB、HBase 等
• 数据组织方式灵活，如键值对、文档型等
• 适合分布式、高并发、大数据量场景，效率高，但功能和校验能力相对较弱

---

四、数据库的客户端与服务器

• 数据库系统通常采用客户端-服务器架构：
  • 客户端：主动发起请求
  • 服务器：被动响应请求，7×24小时运行，支持多客户端并发
• 客户端与服务器可以在同一台主机，也可以分开，通过网络通信

---

五、数据库存储介质

• 内存：速度快，容量小，成本高，断电数据丢失
• 硬盘：容量大，速度慢，成本低，数据持久保存
• 数据库一般选择硬盘存储数据，实现数据的持久化

---

六、SQL基础

1. SQL与其他编程语言的区别

• C、Java等编程语言：强调"如何做"，即通过具体的逻辑步骤实现功能
• SQL语言：强调"要什么结果"，即描述你需要的数据结果，至于如何实现由数据库服务器自动完成

2. SQL的使用方式

• 通过mysql命令行客户端输入SQL语句
• 主流数据库（如MySQL、Oracle等）都支持SQL，虽然各自有细节差异，但核心思想一致

3. 数据库的概念

• 数据库软件：如MySQL、Oracle，指的是管理数据的软件系统
• 数据库（逻辑上的）：在数据库软件中，数据被组织为一个个数据库，每个数据库下有多个表，表内有行和列
• 数据组织结构：服务器硬盘 → 多个数据库 → 多个表 → 多行多列

---

七、数据库操作

1. 查看当前有哪些数据库

SHOW DATABASES;

2. 创建数据库

CREATE DATABASE 数据库名称 CHARSET utf8;

字符集说明：

• MySQL 5 默认字符集为拉丁文，插入中文可能失败，需指定为utf8
• MySQL 8 默认支持utf8，也可不指定
• MySQL的utf8并非完整UTF-8，推荐使用utf8mb4以支持更多字符

防止重复创建：

CREATE DATABASE IF NOT EXISTS 数据库名称 CHARSET utf8mb4;

查看警告：

SHOW WARNINGS;

3. 选中数据库

USE 数据库名;

4. 删除数据库

DROP DATABASE 数据库名;

⚠️ 注意：删除数据库风险极高，操作需谨慎！

5. 防止误删数据库的措施

1. 权限控制：限制数据库操作权限，仅授权给少数人
2. 定期备份：遵循"一二三"原则——
   • 一份数据
   • 至少保存到两台机器
   • 至少有三个副本
3. 危险操作双人协作：进行如删库等高危操作时，建议两人一起操作，一人执行，一人监督，防止失误

---

八、MySQL数据类型

1. 数字类型

数据类型	大小/范围	说明	对应Java类型
BIT(M)	M指定位数，1~64，默认1	二进制数，存储范围0~2^M-1，常用作布尔值	Boolean/byte[]
TINYINT	1字节，-128~127（有符号）	很小的整数	byte/short
SMALLINT	2字节，-32768~32767	小整数	short
MEDIUMINT	3字节，-8388608~8388607	中等整数	int
INT/INTEGER	4字节，-2147483648~2147483647	标准整数	int
BIGINT	8字节，-263~263-1	大整数	long
FLOAT	4字节，单精度浮点数	近似值，精度约7位	float
DOUBLE	8字节，双精度浮点数	近似值，精度约15位	double
DECIMAL(M,D)	M为总位数，D为小数位	高精度定点数，常用于金额存储	java.math.BigDecimal

2. 字符串类型

数据类型	大小/范围	说明	对应Java类型
CHAR(n)	定长，最多255个字符（实际存储字节数=字符数×每字符字节数，最大255字节）	定长字符串，空白补齐，n为字符数	String
VARCHAR(n)	变长，最多65535字节（n为字符数，最大实际存储受字符集影响）	变长字符串，常用，n为字符数	String
TINYTEXT	最多255字节	短文本字符串	String
TEXT	最多65535字节	长文本字符串	String
MEDIUMTEXT	最多16MB（16777215字节）	中等长度文本	String
LONGTEXT	最多4GB（4294967295字节）	超长文本	String
BINARY(n)	定长，最多255个字节（n为字节数）	定长二进制数据，n为字节数	byte[]
VARBINARY(n)	变长，最多65535字节（n为字节数）	变长二进制数据，n为字节数	byte[]

补充说明：

• CHAR(n)、VARCHAR(n)的n单位均为"字符数"，但最大实际存储空间以字节计，受字符集影响（如utf8mb4下每字符最多4字节）
• BINARY(n)、VARBINARY(n)的n单位为"字节数"
• TEXT、TINYTEXT、MEDIUMTEXT、LONGTEXT的上限均为字节数

3. 日期和时间类型

数据类型	大小/范围	说明	对应Java类型
DATE	‘1000-01-01’~‘9999-12-31’	日期（年-月-日）	java.sql.Date
TIME	‘-838:59:59’~‘838:59:59’	时间（时:分:秒）	java.sql.Time
DATETIME	‘1000-01-01 00:00:00’~‘9999-12-31 23:59:59’	日期+时间	java.sql.Timestamp
TIMESTAMP	‘1970-01-01 00:00:01’~‘2038-01-19 03:14:07’	时间戳，常用于记录修改时间	java.sql.Timestamp
YEAR	‘1901’~‘2155’	年份	java.sql.Date/int

4. 数据类型相关说明

无符号类型（Unsigned）

• MySQL不推荐使用无符号类型，其他开发中也不推荐
• 主要原因：无符号类型容易带来兼容性和数据溢出等问题

TEXT与VARCHAR区别

• 两者都是可变长字符串类型
• 实际开发中更倾向于使用VARCHAR，因为：
  • VARCHAR可以指定最大长度（如VARCHAR(255)），便于约束和优化
  • TEXT不能指定最大长度，适合存储大文本，但限制较多，不适合普通字符串字段

---

九、表操作

1. 基础操作

• 选择数据库：操作表前，需先USE选择数据库

• 查看所有表：

  SHOW TABLES;
  

2. 创建表

CREATE TABLE 表名 (列名 类型,列名 类型,...
);

3. 注释

• 单行注释：#或--（注意后面要有空格）

4. 查看表结构

DESC 表名;

说明：int(11)的11是显示宽度，仅影响控制台显示，不影响实际存储

5. 删除表

DROP TABLE 表名;

⚠️ 注意：删表往往比删库危险性更大，一定要谨慎！

---

十、数据操作

1. 插入数据（INSERT）

基本插入语法

INSERT INTO 表名 VALUES (值1, 值2, ...);

• 列的个数和类型要与表结构一致
• SQL类型系统与Java差异较大，通常会尝试自动类型转换
  • 如：‘100’（字符串）可以自动转为int 100
  • 200（整数）可以自动转为’200’（字符串）

指定列插入

INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);

一次插入多行

INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), ...;

插入当前时间

• 可以用now()作为时间字段的值

2. 查询数据（SELECT）

全列查询

SELECT * FROM 表名;

⚠️ 注意：大表上慎用，容易造成硬盘/网络IO瓶颈

指定列查询

SELECT 列1, 列2 FROM 表名;

表达式查询

• 支持列之间的加减乘除等运算，仅影响临时查询结果，不改变原表数据

• 可以给表达式结果取别名（AS可省略）：

  SELECT 列1 + 20 AS 新列名 FROM 表名;
  

去重查询

SELECT DISTINCT 列名 FROM 表名;

排序查询

SELECT * FROM 表名 ORDER BY 列1 ASC, 列2 DESC;

• 默认升序（ASC），降序用DESC
• 不加ORDER BY时，结果顺序不可预测

条件查询（WHERE）

SELECT 列名 FROM 表名 WHERE 条件;

3. 修改数据（UPDATE）

UPDATE 表名 SET 列名 = 值... WHERE 条件;

• 可搭配WHERE/ORDER BY/LIMIT

4. 删除数据（DELETE）

DELETE FROM 表名 WHERE 条件;

⚠️ 注意：DELETE是危险操作，可搭配WHERE/ORDER BY/LIMIT

---

十一、SQL运算符

1. 比较运算符

运算符	说明
>, >=, <, <=	大于、大于等于、小于、小于等于
=	等于，NULL不安全，如NULL = NULL的结果是NULL
<=>	等于，NULL安全，如NULL <=> NULL的结果是TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果a0 <= value <= a1，返回TRUE(1)
IN (option, …)	如果是option中的任意一个，返回TRUE(1)
IS NULL	是NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配。%表示任意多个（包括0个）任意字符；_表示任意一个字符

2. 逻辑运算符

运算符	说明
AND	多个条件必须都为TRUE(1)，结果才是TRUE(1)
OR	任意一个条件为TRUE(1)，结果为TRUE(1)
NOT	条件为TRUE(1)，结果为FALSE(0)

注意：

1. WHERE条件可以使用表达式，但不能使用别名
2. AND的优先级高于OR，若同时使用，需用小括号()包裹优先执行的部分

3. SQL执行顺序

1. 遍历表，取出每一行
2. 要把当前行，带入到WHERE条件，根据条件的真假决定这一行是否要查询出来
3. 再根据SELECT后面写的列名/表达式，进行选择列/进行计算
4. ORDER BY进行排序

4. LIKE模糊匹配

• 不需要完全相等，只要满足一定条件就可以了
• 搭配通配符：
  • %：匹配0个或者任意个字符
  • _：匹配一个特定的字符
• 在SQL中，LIKE不能支持正则，只能简单用%或者_来匹配
• LIKE模糊查询是一个比较低效的操作，实际开发中使用LIKE要能够节制

---

十二、分页查询

1. 基本语法

SELECT * FROM 表名 LIMIT N;

2. 带偏移量的分页

SELECT * FROM 表名 LIMIT N OFFSET M;

• OFFSET表示从下标为几的记录开始计算
• 针对一个行数非常多的表，使用LIMIT仍然是一个高效的操作

3. 分页查询的重要性

• SELECT * 容易查出太多的数据，使机器挂了
• 通过指定列查询，虽然查到的结果变少了很多，但是如果行数足够多的话，仍然是有可能会把机器搞出问题的
• 此时更稳妥的做法，就是’分页查询’，限制一次查询，最多能查到多少个记录

---

十三、数据库约束

1. 约束的作用

• 对于数据库的存储，做出一些"限制"“要求”
• 约束就是数据库程序对于保存的数据进行合法性检查的机制
• 一旦发现问题，数据库程序就能及时给出反馈

2. 常见约束类型

约束类型	说明
NOT NULL	指示某列不能存储NULL值
UNIQUE	保证某列的每行必须有唯一的值
DEFAULT	规定没有给列赋值时的默认值
PRIMARY KEY	NOT NULL和UNIQUE的结合，确保某列有唯一标识
FOREIGN KEY	保证一个表中的数据匹配另一个表中的值的参照完整性
CHECK	保证列中的值符合指定的条件（MySQL对CHECK子句进行分析，但是忽略CHECK子句）

注意：CHECK约束，MySQL不支持。但写了CHECK不会报错，检查条件不会生效！实际开发中，也想进行类似的校验，只能通过搭配的C++/Java这样的代码，在这样的代码插入数据之前，先判断就行了。

3. 主键相关

• 不允许一个表出现多个主键
• 数据库允许把多个列共同作为一个主键（联合主键）
• 主键需要分配一个唯一值，MySQL提供了一个自动分配主键值的方式：“自增主键”
• 能手动指定其他值
• 在MySQL中，给每个表都记录了一个"自增主键的最大值"，后续继续插入数据，无论之前的最大值是否存在都是根据之前保存的最大值，继续往后分配的
• 自增主键只能针对，像int/bigint整数
• 也可能会使用字符串作为主键的
• 大部分情况下，都是用数字作为主键
• 主键本身也是有唯一性要求的，频繁触发查询，此处数据库就会对主键约束，给出特殊处理，加快查询速度
• 数据库会针对主键创建索引

4. 逻辑删除

• 考虑一个情景，商品下架
• 直接删除，不行
• 不用外键，针对其他插入数据，缺少了校验过程，不合适
• 此处真正的解决方案：“逻辑删除”

---

十四、表的设计

1. 设计思路

• 表的设计比较抽象，依赖一定的经验
• 如何创建表，根据实际需求创建，梳理清楚，应该创建几个表，每个表要有哪些列，以及多个表之间存在什么样的联系

2. 基本切入点

• 梳理清楚需求中的"实体"和"关系"
• 这里的实体类似于编程中的对象→进一步的再去梳理出，这里需要包含哪些关键信息

3. 如何"找对象"

• 分析你的需求场景，提取出一些"关键的名词"
• 还需要确定对象的属性方法
• 对象有哪些信息需要保存，以及需要针对这些信息进行哪些"加工操作"
• 找对象的过程，本身也就是把现实中的东西，使用代码的方式"抽象的"表示出来
• 一个东西，越具体，此时就越不抽象；越不具体，就越抽象

4. 确定实体

• 找到关键名词，提取出一些需要用到的信息
• 有几个实体，一般就会有几个表。每个实体使用一个表来表示

5. 实体间的关系

• 梳理实体之间关系，根据这里的关系，需要对于表做出一些特殊的设定

1. 一对一
2. 一对多
3. 多对多
4. 没关系

---

十五、复杂查询操作

1. 基础查询类型

1. 全列查询
2. 指定列查询
3. 带表达式查询
4. 带有别名的查询
5. 去重查询
6. 排序查询
7. 条件查询
8. 分页查询

2. 查询和新增结合

• 把表1的查询结果，插入到表2中

3. 聚合查询

• 针对行和行之间进行"聚合操作"
• 进行聚合查询，搭配聚合函数
• 如果数据中包含NULL，可能对上述COUNT产生影响
• 如果是SELECT 列名的方式，遇到空值，不会进行计数

4. 分组查询（GROUP BY）

SELECT 列名, 聚合函数() FROM 表名 GROUP BY 列名;

• 使用GROUP BY，指定一个列
• 就会把列的值相同的一行，归到一组中
• 分完组之后，还可以针对每个组，分别进行聚合查询

5. 示例数据

创建班级表

INSERT INTO classes(name,description) VALUES
('旭山高等学校','五等分的新娘'),
('丰之崎学校','路人女主的养成方法'),
('月之森女子学园','mygo!!!!!');

创建学生表

INSERT INTO student(serial_number,name,email,class_id) VALUES
('A01','中野一花','n.ichika0505@socomo.ne.jp',1),
('A02','中野二乃','n.nino0505@socomo.ne.jp',1),
('A03','中野三玖','n.miku0505@socomo.ne.jp',1),
('A04','中野四叶','n.yotsuba0505@socomo.ne.jp',1),
('A05','中野五月','n.itsuki0505@socomo.ne.jp',1);INSERT INTO student(serial_number,name,email,class_id) VALUES
('C01','长崎爽世','null',3),
('C02','若叶睦','null',3);

创建课程表

INSERT INTO course(name) VALUES
('国'),('数'),('理'),('社'),('英');

创建成绩表

INSERT INTO score(score,student_id,course_id) VALUES
-- 中野一花
(38,1,1),(63,1,2),(52,1,3),(40,1,4),(47,1,5),
-- 中野二乃
(32,2,1),(33,2,2),(40,2,3),(48,2,4),(56,2,5),
-- 中野三玖
(43,3,1),(48,3,2),(41,3,3),(72,3,4),(34,3,5),
-- 中野四叶
(51,4,1),(33,4,2),(32,4,3),(36,4,4),(32,4,5),
-- 中野五月
(47,5,1),(35,5,2),(70,5,3),(32,5,4),(40,5,5);

6. 多表查询

基本步骤

1. 明确要查询的信息来自于哪些表
2. 针对这些表进行笛卡尔积（前提是确实能进行笛卡尔积）
3. 指定连接条件，把有意义的数据筛选出来
4. 根据需要，进一步指定条件，对数据进行筛选。新加的条件往往通过AND方式，和连接条件放在一起
5. 进一步的针对查询的列，也去进行精简

JOIN语法

• 多表查询，还可以通过另一个写法，JOIN
• 指定连接条件，通过ON
• 一步步来，不要一口气写完
• JOIN能更清楚的体现出，联合查询的过程

注意：

• 虽然笛卡尔积，是可以拿任意个表进行的
• 但是由于笛卡尔积可能会产出大量的"中间结果"，此时就会对于性能影响很大，甚至严重的，会把数据库搞挂了
• 因此多表联合查询，可以用，用的时候，要慎重，需要先对当前这样的多表查询，大概会涉及到多少数据量进行操作，有一个预估

7. 外连接

内连接与外连接的区别

• 如果两个表的数据不一一对应，内连接和外连接结果不同

内连接（INNER JOIN）

• 最基本的多表联合查询是内连接
• INNER JOIN 就是内连接

外连接

• 外连接，也是 JOIN ON 这样的写法（不支持FROM 多个表）
• 给 JOIN 前面加上 LEFT/RIGHT 就是表示外连接

左外连接（LEFT JOIN）

• 就是以左表为基准
• 能够确保左表中每个记录都出现在最终结果里
• 如果左表中的记录，在右表中没有对应的记录，此时就会把右表中的相关字段，填成NULL

右外连接（RIGHT JOIN）

• 以右表为基准进行连接

全外连接

• MySQL不支持全外连接

8. 自连接

• 自己和自己进行笛卡尔积
• 需要取别名

9. 子查询

⚠️ 原则上不推荐使用

• 套娃
• 本来一个需求，需要通过多个SQL来完成
• 但是偏偏要把多个SQL合并成一个
• 这种做法和软件开发背道而驰

多行子查询

• 返回多行记录的子查询
• 此时不能使用=><这样的运算符直接比较了，但是可以用IN
• 案例：查询"语文"或"英文"课程的成绩信息

EXISTS子查询

⚠️ 更不推荐使用

• 非常消耗时间，背后会触发大量的硬盘IO操作
• 代码理解起来也比较复杂

10. 合并查询

UNION

• 把多个查询结果合并成一个
• 会自动去重

UNION ALL

• 把多个查询结果合并成一个
• 不去重

---

总结

SQL语句的四大基本操作

1. INSERT INTO - 插入数据
2. SELECT FROM - 查询数据
3. UPDATE - 修改数据
4. DELETE FROM - 删除数据

注意：这四种操作有三种风格，不太科学的设定，接口风格不统一，使使用者学习成本增加。

重要提醒

1. 安全性

   • 删库删表等危险操作要谨慎
   • ALTER操作不推荐使用（开发中用的不多，面试不考，还很复杂）

2. 性能考虑

   • 大表查询、多表联合查询要注意性能影响
   • 数据库本身就慢，需要合理使用
   • LIKE模糊查询是比较低效的操作，要节制使用

3. 规范性

   • 遵循数据库设计规范，合理使用约束
   • 在修改、插入时会先触发查询

4. 实用性

   • 优先使用简单高效的查询方式
   • 避免过于复杂的子查询
   • 可能数据量特别大的时候，不是一个MySQL数据库，而是多个MySQL构成的分布式"集群"

数据库设计原则

• 主键本身也是有唯一性要求的
• 频繁触发查询，此处数据库就会对主键约束，给出特殊处理，加快查询速度
• 数据库会针对主键创建索引
• 如果不指定主键，是一个UNIQUE，也能达成类似的效果
• 都是要自动建立索引，以便于后续的快速查询

校验规则

• 校验规则，决定如何比较字符串
• 在修改、插入时会先触发查询
• 不允许一个表出现多个主键
• 但数据库允许把多个列共同作为一个主键（联合主键）