数据库圣经——CRUD(三)

Han.miracle

发布于 2025-12-22 15:04:26

1110

文章被收录于专栏：Java核心技术图谱：原理·对比·避坑Java核心技术图谱：原理·对比·避坑

数据库的理解

数据库的数据层级：

层级 1：数据库服务器是运行在电脑上的软件 / 硬件载体，负责管理多个数据库，是数据管理的 “大管家”。
层级 2：数据库（数据的集）由服务器管理，是多个数据表的集合，用于分类存储不同业务领域的数据（比如电商系统中，用户数据库、商品数据库是分开的）。
层级 3：数据表是保存真实数据的容器，结构类似 Excel 表格，是数据存储的直接载体（比如用户表存储姓名、手机号等用户信息）。
层级 4：数据行（记录）是数据表中的每一行，代表一条完整的信息记录（比如用户表中，每一行对应一个用户的所有信息）。
层级 5：列（字段）是数据表中的每一列，代表数据的一个属性（比如 “姓名”“年龄” 列，分别对应用户的不同属性）。

据库操作核心概念 “CRUD”:

C（Create）：新增指在数据库表中添加一条新的记录，例如在用户表中新增一个用户的信息。
R（Retrieve）：检索、查询是从数据库中获取所需数据的操作，比如查询某一时间段内的订单信息。
U（Update）：更新用于修改数据库中已有的记录，像更新用户的手机号码。
D（Delete）：删除即移除数据库中的记录，例如删除失效的商品信息。

Create 新增

INSERT [INTO] table_name
 [(column [, column] ...)]
 VALUES 
(value_list) [, (value_list)] ...
 value_list: value, [, value] ...

1.INSERT INTO

SQL 插入数据语句（INSERT INTO），用于在数据库表中新增一条数据行，分点解读如下：

语法结构格式为;。其中insert into是关键字，指定要操作的表名，[字段1, 字段2]可选，用于指定要插入数据的列，values后紧跟要插入的具体数值。

insert into 表名 [字段1, 字段2] values (值, 值, 值);
//简写的全列 直接把字段省略，然后所有列的值都列出来

insert into：SQL 关键字，标识 “插入数据” 操作。
[字段1, 字段2]：可选，指定要插入数据的表字段（即表的列名），数量可单个或多个，需与表定义时的字段名一致。
values (值, 值)：指定要插入的具体数据，值的顺序必须与前面字段名的顺序严格对应。

字段名的列表与values后的数值列表是一一映射的关系，即 “字段 1 对应第一个值，字段 2 对应第二个值”，以此确保数据插入到正确的列中。

注意：字符串类型是用的单引号，跟Java不太一样

插入数据的时候，一定要数据类型符合还有数据列的个数一样

这是关于 MySQL 中INSERT INTO语句两种插入数据方式的示例，分点提取如下：

1. 单行数据 + 全列插入

语法特点：不指定具体字段，直接插入所有列的数据。
示例语句

INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');

注意事项：value_list的数量、顺序必须与表定义的列完全一致。

2. 多行数据 + 指定列插入

语法特点：指定要插入的字段（如id, sn, name），同时插入多条数据（用逗号分隔多个value_list）。

示例语句：

INSERT INTO student (id, sn, name) VALUES
  (102, 20001, '曹孟德'),
  (103, 20002, '孙仲谋');

3.数据库插入数据效率对比的分析，结论是 “一次插入多条数据效率更高”，原因分三点：

网络开销：执行每条 SQL 都会产生网络通信成本，一次插入多条可减少通信次数，降低总开销。

磁盘 IO 开销：写入数据时的磁盘读写操作，批量插入可减少 IO 次数，提升效率。

事务开销：每条 SQL 默认开启一个事务，事务的开启和关闭会消耗系统资源，批量插入可减少事务操作次数，节约资源。

4.重现字符集设置错误导致插入数据异常？

改默认配置即可

4.1 点击此电脑的管理

把这个MySQL 的运行停止

4.3 找到my.ini 配置文件

这是我的地址：D:\ProgramData\MySQL\MySQL Server 8.0

4.4 修改my.ini 配置即可假设改成 latin1 (latin1 是一种字符集（Character Set），主要用于存储西欧语言（如英语、德语、法语等）的字符,不支持中文、日文、韩文等多字节字符。)

5.重启MySQL服务，检查是否改变

已经改变了我们可以看到

6.那如果我在这和默认的不想用，想改 character_set_database 改成latin1呢

创建表时指定：

CREATE TABLE test (...) CHARACTER SET latin1;

手动设置新的数据库编码集

修改字段字符集：

ALTER TABLE test MODIFY name VARCHAR(50) CHARACTER SET latin1;

创建数据库指定：

CREATE DATABASE test_db CHARACTER SET latin1;

4.字码集错误的提示？

因数据库、表或字段的字符集不支持中文（如使用latin1字符集），导致插入中文时编码不兼容，出现 “Incorrect string value” 报错。

5.如果确认了是字符集的问题，按我们该怎么办呢？

如果数据库未存储重要数据：删除原库并重新创建指定正确编码集（如utf8mb4）是可行的，步骤为：
1. 删除原库：DROP DATABASE 原库名;
2. 新建库并指定编码：CREATE DATABASE 新库名 CHARACTER SET utf8mb4;
如果数据库已有重要数据：直接删除不现实，建议通过修改数据库、表、字段的字符集来解决，步骤可参考之前的 “解决建议”（如ALTER DATABASE、ALTER TABLE等语句），无需删除库即可修复编码问题。

查询（Retrieve）

select

1.全列查询（select * from 表名）

语法：

select * from 表名

注意:在生产过程中，不要进行这样的操作

生产环境中，数据库表可能存在极大的数据量（甚至达到 TB 级），这种情况下，一些操作会带来严重的资源消耗风险。

一方面，查询操作会产生磁盘开销和网络开销。而磁盘与网络在生产环境中属于非常紧缺的资源，一旦被过量消耗，会直接导致系统性能大幅下降。

另一方面，高危 SQL 语句的执行可能耗尽服务器的 CPU、内存等资源，使得其他程序或数据库操作被迫处于等待状态，进而引发业务阻塞。

注意：在生产过程中不要不加任何的限制条件的查询

2.指定列查询

对特定的列选择性查询

语法：

SELECT column [AS] alias_name [, ...] FROM table_name

格式：

select 列名，列名，列名 from 表名

3.表达式查询

1.2 查询结果表达式列于列之间也可以实现

计算成总分我们可以给他起一个别名用 as（as 后面的中文加单引号也可以）这个as 也是可以省略的

别名中间有空格 ‘总分 ’，就要用单引号给他包裹起来

注意：

当表中原本没有 “总分” 这一列时，通过表达式查询生成的结果集会借助临时表返回，临时表仅在查询执行期间存在，执行完毕后会被自动删除。

MySQL所有的查询结果都会通过临时表来返回给用户

4.查询去重（distinct）

select distinct 列名，列名，列名 from 表名

在 MySQL 中，distinct关键字用于处理重复数据，其核心规则是：

使用distinct后，重复的记录仅保留一条。
MySQL 判定重复数据的标准是查询结果中每一列都完全相同，即两条记录的所有字段值都一致时，才会被认定为重复数据。

也就是我选择的列再加上 id 那就是行不一样了，列一样，不符合去重

5.查询加别名

select 列名  as 新列名 from 表名；

最好在别名上加的单引号，这是比较规范的操作

6.排序（order by）简单提一下

select 列名 from 表名 order by 以什么排序的列名 [ASC | DESC];

ORDER BY用于对查询结果按指定列排序。
ASC表示升序（默认，可省略），DESC表示降序。
默认的是升序，建议明确排序规则（desc/asc）

注意：desc

在表查询的时候，查看表的结构 desc describle

在order 排序的时候，desc descend

6.1.MySQL 无 ORDER BY 子句时结果集排序规则：

核心结论：当 SQL 语句中未指定ORDER BY子句时，MySQL 返回结果集的默认排序是不确定的。
开发建议：永远不要依赖这种默认排序来处理业务逻辑，若需明确的结果顺序，必须显式添加ORDER BY子句指定排序字段及升降序（ASC升序、DESC降序）。

1.null 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

对英语成绩排序

6.2.表达式以及别名排序

1. 表达式排序

若查询中使用了计算表达式（如字段运算、函数处理等），可直接在 ORDER BY 后写该表达式进行排序。

SELECT 
    sname, 
    math + english AS total  -- 表达式：math + english（计算总分）
FROM 
    student
ORDER BY 
    math + english DESC;  -- 直接用表达式排序（降序）

2. 别名排序

若为表达式或字段起了别名（AS 别名），ORDER BY 子句中可直接使用别名排序，更简洁。

SELECT 
    sname, 
    math + english AS total  -- 为表达式起别名 total
FROM 
    student
ORDER BY 
    total DESC;  -- 直接用别名排序（降序）

MySQL 中 NULL 特性的说明，分点解读如下：

运算特性：NULL 与任何值进行运算，结果都为 NULL。
逻辑判定：在逻辑判断中，NULL 始终被判定为 FALSE。
值的本质：MySQL 中的 NULL 不是其他编程语言中的 0，它是一种表示 “无值”“未知” 的特殊状态。

在 MySQL 中，可对多个字段进行排序，其规则如下：

排序优先级：与ORDER BY子句中字段的书写顺序相关，先按第一个字段排序，第一个字段值相同的行再按第二个字段排序，以此类推。

排序规则指定：可为每个字段单独指定ASC（升序，默认）或DESC（降序）的排序规则。

语法格式：order by 列名 [asc|desc], 列名 [asc|desc], 列名 [asc|desc] ...

order by 列名 [asc|desc], 列名 [asc|desc], 列名 [asc|desc] ...

示例：如select id, name, chinese, math, english from exam order by math desc, chinese asc, english; 表示先按math字段降序排列，math值相同的行再按chinese字段升序排列，chinese值仍相同的行最后按english字段升序（默认）排列。

3.多字段排序时的优先级叠加逻辑

优先按第一个字段排序系统先根据ORDER BY中第一个字段的规则（升序 / 降序）对所有数据进行排序，确定初步的排列顺序。
前序字段相同的行，再按第二个字段排序若存在多条记录在第一个字段上的值完全相同，这些记录会被视为 “同一组”，系统会对这一组内的记录，按照第二个字段的规则（升序 / 降序）再次排序，进一步区分顺序。
以此类推，逐层细化若第二个字段仍有相同值的记录，继续用第三个字段的规则排序，直到所有记录的顺序都能被唯一确定（或所有指定字段都参与排序）。

7.条件查询 where

7.1语法

 SELECT
       select_expr [, select_expr] ... [FROM table_references]
 WHERE  where_condition

7.2 比较运算符

MySQL 比较是用 = ，赋值也是 =
= 与 <=> 的区别
判断是不是等于 null ，不可以用 = 要用 is null ，或者 is not null

in 条件中的值在选项列表中存在则返回 true, 否则返回 false
SQL 通配符 %：用于匹配任意多个字符（包括 0 个），在 LIKE 语句中实现模糊查询，如查询以 “a” 开头的记录
_匹配严格的一个任意字符

7.3逻辑运算符

根据返回的结果可以得出一个结论：

AND 的优先级是大于 OR

的整体的优先级顺序和 JAVA 中一样

NOT > AND > OR

7.4 演示注意

在一行数据中的两个列是可以进行比较的但是不能跨行进行比较，比如一位同学的语文成绩和数学成绩
where 语句不能用别名当作过滤条件

出现这种现象是和 MYSQL 内部的实现有关，换句话说就是和 MYSQL 执行 SQL 语句的顺序有关

如果要在数据中查某些数据，首先要确定表，先执行 from
在查询的过程中要根据指定的查询条件把符合条件的数据过滤出来，这时执行的就是 where 子句
执行 select 后面的指定的列，这些列是需要加入到最终的结果集中
排序操作，根据 order by 子句中指定的列名和排序规则进行最后的排序

注意事项：

若查询中无ORDER BY子句，返回顺序未定义，切勿依赖该顺序。
ORDER BY子句中可使用列的别名进行排序。
对NULL进行排序时，其视为比任何值都小，升序时出现在最上面，降序时出现在最下面。

8.分页查询 Limit

作用：限制查询结果集中的条数

-- 起始下标为 0
-- 从 0 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num;
-- 从 start 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT start, num;
-- 从 start 开始，筛选 num 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num OFFSET start;