1.1.关系型数据库#

2. 关系型的定义#

所谓的关系型数据库，就是基于关系模型的数据库，一个关系模型其实就是一张二维表！

而一张二维表往往对应着现实世界中的一个实体集！

什么是实体和实体集？

实体是人类观念世界中描述客观事物的一种概念，可以是具体的事物，比如一本书，一个人，一个手机，一条街等，也可以是抽象的事物，比如一种感受，一个电子订单！

同一类实体的所有实例就构成了一个实体集，实体集就是实体的集合，每一个实体都是该实体集的一个实例，实体与实体集之间的关系有点类似于数学上的元素与集合的关系！

实体集反映到数据库中，就是一张一张的二维表！

比如，我们现在的有2种实体集：

学生实体集，教师实体集，也就分别对应着数据库中的2张数据表：学生表，教师表！

在现实世界中，实体与实体之间肯定是有关系的，所以在数据库中，表与表之间也肯定是有关系的，所以叫做“关系型”数据库！

思考：假如为一个酒店管理系统设计一个数据库，需要设计哪些表？

1.2 总结#

2.1 概念#

SQL：Structured Query language，结构化查询语言！ 是一种基于关系模型数据库的操作语言，也是一种数据库编程语言！ SQL最初由IBM公司在70年代开发出来的，在80年代的时候被国际标准化组织ISO定义为关系型数据库的标准语言！

2.2 SQL语言的分类#

思考一下:

如果我们现在准备往一个数据库里面存放一些数据，需要有哪些准备工作？

1， 先创建一个新的数据库

2， 再创建一张数据表

3， 定义这张表的结构（有哪些字段，字段是什么数据类型，有没有主键索引等）

所以，数据库的第一种操作语言就是DDL！

根据对数据库不同的操作对象或操作层次，SQL又可以分成不同的操作语言.

2.3 总结#

SQL是操作数据库的标准语言

4个单词搞定分类：

3.1 mysql版本#

为什么我们会选择mysql8呢？原因如下:

窗口函数 + CTE

不用再写嵌套子查询算排名了，ROW_NUMBER()、WITH递归一键搞定

默认 utf8mb4

建库不用再手动 CHARSET=utf8mb4，emoji 直接存

原子 DDL

以前 DROP TABLE a,b;中途崩了可能 a 删了 b 还在；8.0 要么全成要么全回滚

隐藏索引 / 降序索引

索引可以先 INVISIBLE 试水再删，降序排序真能用上索引了

角色（Role）

权限从”挨个给用户授权”升级成”角色模板”，DBA 狂喜

自增 ID 持久化

5.7 重启可能主键回退，8.0 写 redo 里，根治

干掉 .frm 文件

元数据全进 InnoDB 事务表，information_schema查询快 N 倍

3.2 使用phpstudy安装mysql8#

…现场演示…

3.3 启动mysql8#

在phpstudy中，启动mysql8是非常简单，如下图所示:

启动成功后，如下图所示：

3.4 mysql的配置文件#

当mysql启动的时候，它会自动加载mysql的配置文件，我们可以通过这个方式找到该配置文件：

4.1.打开mysql8命令行#

先找到你安装phpstudy的地方，例如我的安装目录是：D:\tools\phpstudy_pro

点击进入Extensions目录，找到Mysql8的安装目录

在找到该目录下的bin目录：

然后输入cmd命令，回车

就能打开mysql的命令行

注意： 务必先启动mysql8,否则无法后面的操作

注意：phpstudy还是最好不要加mysql8的环境变量

4.2 打开mysql控制台#

在phpstudy中，安装了mysql8后，默认的用户名和密码都是root,我们可以通过如下命令打开控制台:

1
# 方式一
2
mysql -uroot -proot [数据库名]
3
# 方式二
4
mysql -u <用户名> -p [数据库名]

回车就可以进入控制台：

4.3 mysql的架构#

MySQL基于C/S模型的，安装之后里面有两个部分：

服务器软件（mysqld）

客户端软件 (mysql)

要想正常的使用MySQL服务器，首先要完成两个步骤：

1， 开启MySQL服务器(就是通过mysqld启动的)

2， 通过客户端连接服务器（mysql，python/java/go/php…，datagrip）

4.4 网络连接3要素#

• IP（Internet Protocol） IP 地址用于唯一标识网络中的每一台设备。它充当设备的“地址”，使得数据能够在网络中准确地找到目标设备。

1
IPV4：
2
    格式: x.x.x.x  x的取值范围（第一位x取值1-223，从第二位开始0-255）
3
    IP可以分为公网/外网ip和私网/内网ip
4

5
    公网/外网IP:   IP 是全球唯一的，可以在任何地方直接访问到该地址
6
    私网/内网ip:   局域网内使用的地址，不能直接通过互联网访问,目的是为了内部网络的设备进行通信
7

8
IPV6：
9
    由8个16位的十六进制数组成，每组数字之间用冒号分隔。
10
    如：2001:0db8:85a3:0000:0000:8a2e:0370:7334
11

12
目前市场上，主要还是IPV4为主，IPV6为辅助.
13
需要注意的是，IP需要确保在对应网络范围内唯一

• 端口（Port） 端口用于区分同一台设备上不同的应用程序或服务。在计算机网络中，一个 IP 地址可以对应多个服务，每个服务通过不同的端口进行通信。端口号是通信中识别应用程序的方式。
  • 端口的范围：
  • 0-1023：这些端口称为 知名端口，通常被系统或服务使用（例如，HTTP 使用端口 80，HTTPS 使用端口 443，FTP使用端口 21）。
  • 1024-49151：这些端口称为 注册端口，通常用于用户和应用程序之间的通信（mysql的端口默认是3306）。
  • 49152-65535：这些端口是 动态或私有端口，用于临时连接或客户端通信。
• 协议（Protocol） 协议定义了数据在网络中传输时的规则和格式。常见的协议有 TCP/IP、Socket、UDP、HTTP、FTP 等，它们规定了数据如何被分割、传输、接收和重组。

在计算机当中127.0.0.1这个是一个默认的本地IP地址，也可以用localhost来表示。

Mysql是采用了Tcp/IP协议，它自定义了应用层.

MySQL 使用的是基于 TCP 的自定义应用层协议

✅ 本地连接可以用 Unix Domain Socket（常被误叫“Socket 协议”）

5.1 show命令#

1
-- 展示所有的数据库有哪些
2
show databases;

1
-- 展示引擎有哪些
2
show engines;

在开发中我们一般使用InnoDB比较多，你简单理解就是这个引擎的功能更完整

1
-- 展示数据库创建的语句
2
show create database demo;
3

4
# 额外补充
5
# 还有一种显示表定义的语句,只不过不是DDL,而是结构化输出,是给人看的
6
desc <表名>
7
describe <表名>

1
-- 展示当前数据库使用默认编码字符集
2
SHOW VARIABLES LIKE 'character_set%';
3
SHOW VARIABLES LIKE '%char%';

这种命令你有个印象就差不多，在AI时代的今天，你是可以vibe coding的（比如：通义灵码）或者你问豆包/元宝也可以。

5.2 \G显示方式#

5.3 exit命令#

退出mysql控制台（ctrl+c快捷键也可以退出，不过windows可能不太支持，mac和linux是没问题的）。

6.1 修改存储引擎#

我们可以通过修改配置文件的让mysql8的引擎默认为Innodb

注意：改完记得保存

在命令行中，输入show engines

6.2 Innodb和MyISAM引擎#

1
1. 输入手机号+密码.
2
1. 检验手机号是否存在(查询).
3
1. 判断密码是否正确(查询)

6.3 Arhive引擎#

mysql8推荐用于取代MyISAM的方案

archive引擎同时具有安全写,高性能写入,压缩的功能.

不支持删除,修改.

7.1 安装和绿化#

请参考如下资料和观看视频进行实操：

7.2 使用DataGrip链接Mysql#

首先复制mysql-connector-java-8.0.25.jar到DataGrip的安装目录下，建议创建driver目录进行存放

这个目录不一定要交driver，但必须是英文或者数字，不能包括任何空格和中文字符串和特殊符号

7.4 通义灵码配置和连接Mysql#

最新的2026版本的DataGrip是可以集成通义灵码的，如果有兴趣的同学可以去闲鱼找找

但DataGrip是不是最新版的其实无所谓，所以这里就使用通义灵码的IDEA辅助就可以了。

在Python阶段，可以在Pycharm中安装最新版本。

7.3 MySQL注释符#

单行注释：

# 注释内容

— 注释内容，这里的—与注释内容之间必须有一个空格！

多行注释

/* 注释内容 */

8.1 创建数据库语法规则#

1
CREATE <DATABASE | SCHEMA> [IF NOT EXISTS] db_name
2
    [CHARACTER SET <charset_name>]
3
    [COLLATE <collation_name>];

DATABASE和 SCHEMA完全等价，可互换使用。

需求场景：创建名为ecmall的数据库，创建名为easycms的数据库

1
create database ecmall;
2
create schema easycms;

推荐写法

1
create database if not exists itcast;

完整写法（了解，不常用）:

1
CREATE DATABASE IF NOT EXISTS test_db
2
  CHARACTER SET utf8mb4
3
  COLLATE utf8mb4_unicode_ci;

8.2 use语句#

格式：

1
 use 数据库名字;

示例:

1
use demo;  # 使用或者切换数据库为demo

有时候我们会在登录控制台的时候就使用某一个数据库了：

1
mysql -uroot -proot echop

加上数据库不存在，那么mysql客户端会报错 ERROR 1049 (42000): Unknown database ‘aaaaabbbbcccc’

8.4 查看当前数据库#

格式：

1
select database();

8.5 删除数据库#

格式：

1
drop database [if exists]  数据库名称
2
drop schema [if exists]  数据库名称

示例:

1
drop schema ecmall;
2
drop database itcast;

删除数据库尽量不要使用if exists ,最好让数据库有报错的可能性

8.6 修改数据库(了解)#

1
ALTER DATABASE test_db
2
  CHARACTER SET utf8mb4
3
  COLLATE utf8mb4_unicode_ci;

这个学不学都无所谓

注意： 请不要忘记前面学过的show databases;命令

mysql中的数据库存储位置可以在my.ini中配置

1
# 若缺省,则使用运行目录下的./data文件夹
2
datadir=D:/phpstudy_pro/Extensions/MySQL8.0.12/data/

每个数据库在data文件夹中都有一个单独的同名文件夹

今天目标(2026-07-02):

1
0.回顾昨天
2
1.Mysql中的数据类型
3
2.DDL数据表操作
4
3.DML增删改查操作
5
4.软删除

9.1 数值类型#

MySQL中的数值类型提供了整型、浮点型、定点数，与python类似。

对于小数的表示，MYSQL分为两种方式：浮点数（float）和定点数（Decimal）。浮点数包括float(单精度)和double(双精度), 而定点数只有decimal一种，在mysql中底层以字符串的形式存放，比浮点数更精确，适合用来表示货币等精度要求高的数据。

示例：

1
格式: 字段名 数据类型 [unsigned]
2

3
age tinyint # 有符号
4
ate tinyint unsigned # 无符号

9.2 字符串类型#

MySQL中针对文本内容的存储类型提供了字符串与文本两种格式，其中按存储方式不同，又分为固定长度（定长）与可变长度（变长）两种，按存储格式不同，又分为普通字符格式与二进制格式两种。

SQL 语句中（单|双）引号都能表示字符串或文本。

这里有两个经典的面试八股文

1
存入 'abc'：
2
CHAR(10)→ 存 'abc       '（补 7 个空格）
3
char若超过指定的长度,直接截断,不报错
4

5
VARCHAR(10)→ 存 'abc'（只占 3 个字符 + 长度标记）

1
varchar可指定n，text不能指定
2
text类型不能有默认值，注意json也不能有默认值。(但是在8.0.13版本后,支持常量默认值default 'textDefaultValue')
3
varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text。
4
索引：index，主要为了加快查询数据的数据的一种技术，类似书籍的目录。

9.3 日期类型#

表示时间值的日期和时间类型为 DATETIME、DATE、TIMESTAMP、TIME 和 YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的 MySQL 不能表示的值时使用”零”值。

9.4 枚举与集合#

enum中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值，而不能一次取多个值。SET和ENUM非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。

枚举是单选的

集合是多选的

集合的特殊用途

name, hobbies

张三 1,2

李四 1,3

王五 1,2,3

将所有值有1的人查找出来

1
select * from users where find_in_set('1',hobbies)>0;
2

3
# find_in_set('<值>',<字段名>) # 返回结果: 位置索引(下标从1开始).

9.5 自增列#

1
use nice;
2

3
create table human(
4
    num int primary key auto_increment comment '自增长列,因为它是自动增长的所以不需要手动维护',
5
    name varchar(20) not null
6
);
7

8

9
insert into human values
10
    (null,'张三'),
11
    (null,'李四'),
12
    (null,'王五')

10.1 CREATE TABLE（创建表）#

语法规则:

1
CREATE TABLE [IF NOT EXISTS] 表名 (
2
    列定义1,
3
    列定义2,
4
    ...
5
    [表级约束]
6
) [ENGINE=InnoDB] [DEFAULT CHARSET=utf8mb4] [COMMENT='表注释'] [auto_increment = <值>];

列定义规则：

1
列名 数据类型 [NOT NULL | NULL] [DEFAULT 默认值]
2
[AUTO_INCREMENT]
3
[UNIQUE [KEY]]
4
[PRIMARY KEY]
5
[COMMENT '列注释']

示例：

1
# 设计一张会员表

10.2 复制表结构#

语法规则:

1
create table <表名> like <源表名>;

示例:

1
# 创建store_members表.
2
# 除了爱好字段以外,其它字段都与ai_members相同.
3
# 1. 复制表结构
4
create table store_members like ai_members;

10.3 ALTER TABLE（修改表）#

一旦涉及到修改表，你当初设计表的时候肯定多少存在一些不太合理的地方。

语法规则：

1
ALTER TABLE <表名> 操作类型;

示例：

1
# 2. 删除爱好字段
2
alter table store_members
3
    drop column hobbies;
4
# 3. 添加字段
5
alter table store_members
6
    add column login_time datetime    default now() comment '登录时间',
7
    add column email      varchar(36) default 'yourname@kulve.tech' comment '邮箱地址' after mobile;
8
# 4. 修改字段名
9
alter table store_members
10
    change column gender sex enum ('男','女') default '男' comment '性别', # 修改字段名 gender -> sex
11
    modify column email varchar(32) default 'nickname@kulve.tech' comment 'kulve.tech邮箱地址'; # 修改列属性
12

13
# 修改自增长列的值
14
alter table <表名>
15
    auto_increment = 10;

10.4 清空表#

语法规则:

1
delete from 表名; # 删除所有记录,保留表状态
2
truncate table 表名; # 截断表,不保留表状态

delete与truncate的区别#

两个命令都会把数据清空，但是delete会删除数据，保留表的历史状态，而truncate会清空数据并清楚表的历史状态。

11.1 insert语句（增）#

insert语句就是对表进行记录添加操作.

语法

1
# 单行插入与多行插入
2
insert into <表名>[(<字段列表>)] values (<值列表>)[,(<值列表>)][,(...)];
3
insert into <表名>[(<字段列表>)] values (<值列表>)[,(<值列表>)][,(...)];
4

5
# 复制表数据
6
# 当然,可以只复制某几列,或者给列指定一个计算列或者只复制某些行,只需要修改select就可以了.
7
# 若列名不同,则需要指定字段列表.
8
insert into <表名>[(<字段列表>)] select * from <表名> [where <条件>]

示例:

1
# 单行插入
2
insert into ai_members(mobile, password, gender, hobbies)
3
values ('13800001111', 'password', '女', 'cat');
4

5
# set集合会自动去重
6
insert into ai_members(mobile, password, hobbies)
7
values ('13800001112', 'password', 'foot,cat,foot');
8

9
insert into ai_members
10
values (null, '13833334444', '78956456', '男', 'music,foot', now());
11

12

13
# 多行插入,使用","隔开.
14
insert into pet (pet_id, name, species, breed, intake_date, status)
15
values (1, '小白', '猫', '中华田园猫', '2025-02-01', '健康'),
16
       (2, '旺财', '狗', '中华田园犬', '2025-02-05', '需治疗'),
17
       (3, '咖啡', '猫', '英国短毛猫', '2025-02-10', '健康'),
18
       (4, '雪球', '狗', '萨摩耶', '2025-02-12', '健康');
19

20
# 复制表数据(当然,可以带条件)
21
create table pet_copy like pet;
22

23
insert into pet_copy
24
select *
25
from pet;

11.2 select语句（查）#

select语句就是对表进行查的操作.

示例:

1
# 查询所有字段
2
select *
3
from ai_members;

在select中使用where子句

1
# 一般查询
2
select id, mobile
3
from ai_members
4
where id = 5;

11.3 update语句（改)#

update语句就是对表进行记录修改的操作.一般都需要配合where子句进行

语法

1
update <表名> set <字段名> = <值> [where <条件>];

11.4 delete语句(删)#

update语句就是对表进行记录删除的操作（是物理删除，直接在磁盘中把记录删除了，一般是无法恢复的），在实际开发中，有90%以上的需求是软删除

示例

1
delete from <表名> [where <条件>]

11.5 软删除操作#

在基础课程中，我可以先跳过这个实操，后面可以配合Python的一些ORM做这个操作。

这里大家可以了解一下原理：

通过在表中添加支持null的字段delete_at来支持软删除.

当为null时,代表数据没有被删除,当该字段被赋予值时,代表已经被删除了.

当删除时,使用update 更新delete_at的值来代表软删除.

查询时,使用is null查询未删除的数据,使用is not null查询已删除的数据.

恢复数据时,只需要使用update将delete_at设置为null即可.

1
create table ai15_Users (
2
  id int unsigned not null primary key comment '主键，非增长列',
3
  name varchar(30) not null comment '姓名',
4
  mobile char(11) not null comment '手机号码',
5
  # default null是可以省略不写，不写就是default null
6
  deleteAt datetime default null comment '软删除标识'
7
)comment '软删除表';
8

9

10
-- 插入数据
11
insert into ai15_Users(id,name,mobile)values(1,'武二郎','12345678901');
12
insert into ai15_Users(id,name,mobile)values(2,'武大郎','12345678902');
13
insert into ai15_Users(id,name,mobile)values(3,'潘金莲','12345678903');
14

15
-- 如果在软删除中，我们要删除数据，不是调用delete删除
16
-- 而是调用update去更新deleteAt字段为当前时间
17
update ai15_Users set deleteAt=NOW() where id=2;
18

19

20
-- 如果我们要查询当前没有被删除的数据，我们通过IS NULL来查询
21
select * from ai15_Users where deleteAt Is NULL ;
22

23
-- 如果我们要被删除的数据,我们通过IS NOT NULL来查询
24
select * from ai15_Users where deleteAt Is NOT NULL ;
25

26

27
-- 如果我们要恢复武大郎是未被删除的，我们只需把deleteAt重新修改为Null就可以了
28
update ai15_Users set deleteAt=NULL where id=2;

12.1 默认值约束#

default 的应用场景： 年龄默认值，性别默认值

示例:

1
age tinyint unsigned default 18 # 年龄
2
gender enum('男','女','保密') dfault '保密' #性别

12.2 非空约束#

not null的应用场景：账号，手机号码

示例:

1
username varchar(16) not null # 用户名
2
phone char(11) not null # 手机
3
idcard varchar(22) not null # 大陆身份证号码

12.3 唯一性约束（索引）#

unique的应用场景： 手机号码，身份证号码

当表的相关列中已经有重复值时,无法对这些列创建唯一索引.

示例:

1
# 创建方式一:
2
alter table <表名>
3
    add unique index <索引名> (id_card);
4

5
# 创建方式二: 注意这个是有on关键字的
6
create unique index <索引名> on <表名> (<单个字段>);
7

8
# 额外: 联合索引,两个索引使用同一个名字
9
create unique index <索引名> on <表名> (<字段名列表>);

当为某一字段创建了唯一性约束，同时也为这个字段标记为索引字段

12.4 主键约束（索引)#

primary key的应用场景：id，username. 主键也是唯一的，同样起到了唯一性的作用。同时它具有not null的作用

示例:

1
id int unsigned primary key auto_increment

12.5 unique和主键有什么不同#

口诀： PRIMARY KEY 一张表只能有一个且不允许 NULL，而 UNIQUE 可以有多个并允许 NULL（多数数据库仅允许一个 NULL）。

primary key: 主键,每张表只允许一个,不允许null,键不允许重复.

unique: 唯一索引,每张表可以有多个,允许null,键不允许重复,允许多个null.(原因是mysql8中,null 是不可比较的,所以允许多个为空,但如果是mysql5,则不允许重复.)

13.1 索引的好处和缺点#

• 好处：

• 加快查询速度（尤其是 WHERE、JOIN、ORDER BY、GROUP BY）

• 提高排序和去重效率

• 约束数据唯一性（如唯一索引）

• 缺点：

• 降低增删改性能（索引也要同步维护）

• 占用磁盘空间

• 过多索引会让优化器更难选、甚至选错

13.2 使用Explain查看索引的情况#

1
# 如果有and,or,in,between,like等运算符,就是证明where子句具有逻辑
2
# 如果逻辑不当,可能索引会失效.
3
# 如果逻辑合理,但逻辑非常复杂,也有可能会导致索引失效.
4
# mysql8是拥有查询缓存的.
5
# 用上索引最好的结果是const,次结果是range,最差的结果是all.
6
explain
7
select * from user where id = 1 and username = 'sj';

13.3#

14.1 建表时直接创建#

1
drop table if exists user;
2
create table user
3
(
4
    id       int unsigned primary key,
5
    username varchar(20),
6
    password varchar(20),
7
    email    varchar(55),
8
    # 创建普通索引
9
    # index <索引名> (<字段名>)
10
    index idx_username (username),
11
    # 创建唯一索引
12
    # unique index <索引名> (<字段名>)
13
    unique index uidx_email (email)
14

15
);
16
show indexes from user;

mobile varchar(11) unique not null comment ‘手机号码’ 这种方式创建的索引名称是mysql创建的

什么时候该使用索引?

• 主键必须为索引.
• 如果一个字段在查询中经常被where使用,那么就考虑设计为索引字段.

14.2 已存在的表添加索引（最常用）#

1
-- 普通索引
2
create index <索引名称> ON <表名>(<字段名>);
3

4
-- 唯一索引
5
create unique index <索引名称> ON <表名>(<字段名>);
6
CREATE UNIQUE INDEX uk_email ON user(email);

14.3 使用 ALTER TABLE 创建索引#

1
# alter table <表名> add [unique] index <索引名>(<字段名>)
2
ALTER TABLE user ADD INDEX idx_name (name);
3
ALTER TABLE user ADD UNIQUE INDEX uk_email (email);

14.4 查看/删除索引#

1
-- 查看
2
SHOW INDEX FROM user;
3
show index from <表名>;
4
# 或
5
show indexes from <表名>;
6

7
-- 删除索引,风险很高,很容易破坏数据结构,产生大碎片
8
# 删除索引方式一:
9
drop index <索引名> on <表名>
10
DROP INDEX idx_name ON user;
11
-- 删除索引方式二:
12
alter table <表名> drop index <索引名>
13
ALTER TABLE user DROP INDEX idx_name;

15.1 DQL 单表查询核心语法#

1
select [distinct] <[*]|<字段列表>|<表达式>>
2
from <表名>
3
    [where <条件>]
4
    [order by <字段> [asc|desc]]
5
    [limit [<偏移>,] <行数>];
6

7
# 关于limit关键字的解释
8
# limit 1: 从第0行开始,返回1条记录,即返回第1条记录. 省略第一个参数,默认为0.
9
# limit 1,1: 从第1行开始,返回1条记录,即返回第2条记录
10
# limit 3,3: 从第3行开始,返回3条记录,即返回第4-6条记录

✅ MySQL 8 仍沿用传统的标准结构，执行顺序：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

15.2 数据准备#

1
CREATE TABLE students(
2
    id INT unsigned primary key auto_increment comment '主键,pk',
3
    name varchar(20) not null comment '姓名',
4
    gender char(1) not null comment '性别',
5
    score DECIMAL(5,2) not null comment '分数'
6
);
7

8
INSERT INTO students VALUES
9
(1,'张三','男',88),
10
(2,'李四','女',95),
11
(3,'王五','男',88),
12
(4,'赵六','女',72),
13
(5,'张无忌','男',80),
14
(6,'张三丰','男',76),
15
(7,'黎明','男',83),
16
(8,'王明辉','男',97);

15.3 快速上手#

1
select *
2
from students;
3

4
select name as cnname, score
5
from students

1
-- 成绩 ≥ 80 的学生
2
select name as cnname, score
3
from students
4
where score >=80;

1
-- 男生且成绩80~90
2
# between是包括两个边界的,两个边界都会被返回.
3
select * from students
4
where gender='男'
5
  and score between 80 and 90;
6

7
-- 成绩是88或95
8
SELECT * FROM students
9
WHERE score IN (88,95);
10

11
-- 查id是1,10,13的同学
12
select * from student where id=1 or id=10 or id=13;
13

14
-- 用in来确定范围 in（1,10，13）
15
-- in在mysql8之前用上索引的几率不高
16
-- in不要大范围查询
17
select * from student where id in(1,10,13);
18

19

20
-- in一般用在批量软删除比较多,这个几率高于select语句使用in
21
-- 批量软删除
22
update student set deleteAt=NOW() where id in(1,11,13);
23
-- 查出批量软删除的用户
24
select * from student where deleteAt IS NOT NULL;
25

26

27
--- in还可以字符，尤其是时间字符
28
select * from students where name='张三' or name='张三丰';
29
select * from students where name in('张三','张三丰');

1
-- 查询姓“张”的学生
2
SELECT * FROM students
3
WHERE name LIKE '张%';
4

5
-- 查询名字中包含“明”的学生
6
SELECT * FROM students
7
WHERE name LIKE '%明%';
8

9
-- 查询名字以“丰”结尾的学生
10
SELECT * FROM students
11
WHERE name LIKE '%丰';
12

13
-- 查询姓名为两个字的学生
14
SELECT * FROM students
15
WHERE name LIKE '__';
16

17
-- 查询姓“张”且名字为三个字的学生
18
SELECT * FROM students
19
WHERE name LIKE '张__';

1
-- 去除重复的成绩
2
select distinct score from students;
3

4
-- asc表示从小到大（升序），desc表示从大到小(降序)
5
-- 按照分数的升序进行排序
6
select * from students order by score asc;
7
-- 如果省略asc或者desc，那么默认就是asc
8
select * from students order by score;
9
-- 按照分数的降序进行排序
10
select * from students order by score desc;
11

12

13
-- 按名字排列,如果是中文，其实utf8编码
14
select * from students order by name asc;
15

16
-- age的升序来排,如果年龄是一样的，就按id的降序来排
17
select * from student order by age asc,id desc;
18

19
-- mysql8是做优化的，如果比mysql5.6低，order by id desc是用不上索引的
20
select * from student order by id desc;

1
select *
2
from <表名>
3
limit [<起始行下标>,] <行数>
4

5
# 关于limit关键字的解释
6
# limit 1: 从第0行开始,返回1条记录,即返回第1条记录. 省略第一个参数,默认为0.
7
# limit 1,1: 从第1行开始,返回1条记录,即返回第2条记录
8
# limit 3,3: 从第3行开始,返回3条记录,即返回第4-6条记录

1
-- 赋值逻辑的功能
2
select 1=1 as a;
3
select ''='' as b;
4

5
-- 创建一张表是会员登录表login_members,字段有id,username,password
6
create table login_members(
7
  id int unsigned primary key auto_increment,
8
  username varchar(16) unique comment '登录名',
9
  password char(32) comment '密码md5-32的,5e64fe04bfd8363b6c74ea86f5c867f1'
10
);
11

12
-- 插入数据
13
insert into login_members(username,password)
14
values ('zhangsan','5e64fe04bfd8363b6c74ea86f5c867f1');
15

16
insert into login_members(username,password)
17
values ('lisi','5e64fe04bfd8363b6c74ea86f5c867f1');
18

19

20
replace into login_members(username,password)
21
values ('pengjin','5e64fe04bfd8363b6c74ea86f5c867f1');
22

23
select * from login_members;
24

25

26
-- 如果我们让lisi登录成功,输入正确的用户名和密码
27
select * from login_members where
28
    username='lisi' and password='5e64fe04bfd8363b6c74ea86f5c867f1';
29

30
-- 注入攻击（原理）
31
select * from login_members where 1=1 or ''='';
32

33
-- 网页中'' or ''=''  或者 ' or 1=1
34
-- 可以同postman 或者 apifox去抓包输入的
35
-- 要控制它最好是预处理，或者使用优秀知名的orm框架
36
select * from login_members where
37
    username='' or ''='' and password='' or 1=1;
38

39

40
-- 在sql的层面操作，那么把select的and
41
# 输入用户的时候查出用户名和密码
42
select username,password from login_members where username='' or ''='';
43
# 密码判断'' or ''=''
44
# 做密码的逻辑判断是在java/python中做的,对于这个取出的用户,使用or条件进行比对是不正确的.
45
# '' or ''='' == '5e64fe04bfd8363b6c74ea86f5c867f1'

15.4 常用聚合函数#

1
-- 平均分、最高分、人数
2
SELECT
3
  AVG(score) as 平均分,
4
  MAX(score) as 最高分,
5
  COUNT(*) as 人数
6
FROM students;

1
# 获取表中行计数
2
select count(1) from <表名>;
3

4
-- count()是一个函数
5
-- * 是一个参数,count(*)统计表有多条记录
6
-- count并不是一定要用*来做占位的
7
select count(*) as rsCount from students;
8
select count('a') rstotal from students;

1
# sum求总分
2
select sum(score) as 总分 from students;
3
# round一般会配合sum,avg使用，保留多少位小数
4
select round(avg(score),2) as 平均分 from students;

15.5 GROUP BY 分组查询#

思考: 为什么需要分组(Group By) ?

前面学的聚合函数是 “整张表算一次”

实际业务中，往往需要：

“先分组，再分别统计”

例如：

按性别统计人数

按科目统计最高

语法规则：

1
SELECT 分组列, 聚合函数()
2
FROM 表名
3
[WHERE 条件]
4
GROUP BY 分组列
5
[ORDER BY 列];

1
-- 如果没有分组，select语句统计男女各占多少，那么就需要2条select语句
2
select count(1) as 总数男 from students where gender='男';
3
select count(1) as 总数女 from students where gender='女';
4

5
-- 分组：你要对什么类型的数据进行分组，你就需要把group by 作用在谁的身上
6
-- 分组语句一般是跟聚合函数一起使用
7
-- 如果有分组/窗口，那么聚合函数就可能是多行的数据
8
select gender as 性别,count(1) as 总数 from students
9
group by gender;

1
select gender as 性别,round(avg(score),2) as 平均分 from students
2
group by gender;

1
-- 步骤1： 只统计成绩 ≥ 80 学生的平均分
2
select round(avg(score),2) as 平均分 from students where score>=80;
3

4
-- 步骤2: 只统计成绩 ≥ 80 男学生的平均分
5
select round(avg(score),2) as 平均分 from students
6
where score>=80 and gender='男'; #85.28
7
-- 步骤3: 只统计成绩 ≥ 80 女学生的平均分
8
select round(avg(score),2) as 平均分 from students
9
where score>=80 and gender='女'; #95.80
10

11
-- 只统计成绩 ≥ 80 学生的平均分，再按性别分组
12
select gender 性别,round(avg(score),2) 平均分 from students
13
where score>=80 group by gender;

15.6 HAVING 分组后筛选#

where用于筛选记录,having用于筛选分组.

1
SELECT 分组列, 聚合函数()
2
FROM 表名
3
GROUP BY 分组列
4
HAVING 聚合条件;

1
select
2
    gender,
3
    count(1) as 人数,
4
    avg(score) as 平均分
5
from students
6
group by gender
7
having avg(score)>=85;

1
SELECT
2
    gender AS 性别,
3
    COUNT(*) AS 人数,
4
    AVG(score) AS 平均成绩,
5
    MAX(score) AS 最高分,
6
    MIN(score) AS 最低分
7
FROM students
8
WHERE score IS NOT NULL
9
GROUP BY gender
10
HAVING AVG(score) >= 80
11
ORDER BY 平均成绩 DESC;

15.7 速记方法和思考题#

GROUP BY：按某一列或多列“分组”

聚合函数：对每一组分别计算

WHERE：分组前过滤行

HAVING：分组后过滤组

SELECT 中只能是：分组列 + 聚合函数

🔹 想分组，GROUP BY

🔹 想统计，聚合函数

🔹 想筛行，用 WHERE

🔹 想筛组，用 HAVING

思考： group by 某个字段，在个字段必须出现在select的字段列表中，对吗？

语法中不是必须的,语义是是必须的.

重点1：GROUP_CONCAT函数#

数据准备:

1
CREATE TABLE stu_course(
2
  stu_name VARCHAR(10),
3
  course VARCHAR(10)
4
);
5

6
INSERT INTO stu_course VALUES
7
('张三','语文'),
8
('张三','数学'),
9
('李四','英语'),
10
('李四','物理');

需求场景：一条记录显示每个学生的姓名和对应的课程

1
select stu_name,GROUP_CONCAT(course) as 选课列表 from stu_course group by stu_name;

重点2： find_in_set#

功能强大，但性能一般，适合做内部系统场景

用于在集合中查询一个值

find_in_set始终无法利用索引.

语法规则:

1
find_in_set(关键字，字段名)

数据准备:

1
CREATE TABLE `bjxz_sickers` (
2
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '患者id',
3
  `name` varchar(11) COLLATE utf8_unicode_ci NOT NULL COMMENT '患者名称',
4
  `gender` enum('男','女') COLLATE utf8_unicode_ci DEFAULT '男' COMMENT '性别',
5
  `drug` set('奥卡西平','普拉克索','左旋多巴','地芬尼多') COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用药情况',
6
  `reg_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
7
  PRIMARY KEY (`id`)
8
) comment '患者表';
9

10

11
INSERT INTO `bjxz_sickers` (`name`, `gender`, `drug`) VALUES
12
-- 包含'地芬尼多'的数据（5条）
13
('张伟', '男', '奥卡西平,地芬尼多'),
14
('李娜', '女', '普拉克索,左旋多巴,地芬尼多'),
15
('王强', '男', '奥卡西平,普拉克索,地芬尼多'),
16
('赵敏', '女', '左旋多巴,地芬尼多'),
17
('陈浩', '男', '奥卡西平,地芬尼多'),
18

19
-- 不包含'地芬尼多'的数据（15条）
20
('刘洋',   '男', '奥卡西平'),
21
('孙婷',   '女', '普拉克索,左旋多巴'),
22
('周磊',   '男', '左旋多巴,奥卡西平'),
23
('吴芳',   '女', '奥卡西平,普拉克索'),
24
('郑凯',   '男', '普拉克索'),
25
('黄丽',   '女', '左旋多巴'),
26
('林峰',   '男', '奥卡西平,左旋多巴,普拉克索'),
27
('何雪',   '女', '普拉克索'),
28
('马超',   '男', '左旋多巴,奥卡西平'),
29
('罗琳',   '女', '奥卡西平,普拉克索'),
30
('谢鹏',   '男', '左旋多巴'),
31
('韩梅',   '女', '普拉克索,左旋多巴'),
32
('唐昊',   '男', '奥卡西平'),
33
('许静',   '女', '左旋多巴,奥卡西平,普拉克索'),
34
('邓飞',   '男', '普拉克索');
35

36
# 其实如果是like和find_in_set这个索引是没有用的
37
alter table bjxz_sickers add index `ik_durg`(`drug`);

1
# 其实这样也是是可以查出来的
2
select * from bjxz_sickers where drug like '%地芬尼多%';
3
# 这样就可读性就比较高
4
select * from bjxz_sickers where find_in_set('地芬尼多',drug);

这个函数的真实场景：广州市第八人民医院神经内科与北京修正药业进行合作（是一个药物研发,关于耳水平衡的），北京修正要求医院提供一份使用”xxx药物”的病人清单给他们做专访。而我也是人生第一次接触到了find_in_set这个函数，当时因为用不上这个索引，通不过测试。

另外这里还有一个很有意思的约定，你会发觉很多医院的项目的药物字段都是drug这个单词.

最后，你可能会问，医院的药物那么它的集合是真的这样写得死死的吗，当然不是。

不过合作方的数据库表一般是新增的子模块功能，还真的是这样写得死死的，因为这张表当时就是我自己建立的，合作方它能进入医院的药物我遇到的最多也就16个，而医院的药物数据是会动态加一张合作方数据表的。

上述这些需求，如果你换着社区医院，那是绝对够用的。

1
# json_contains函数语法
2
json_contains(<json列名>,<查找目标>[,<路径>])
3
# 要注意,json_contains的语法较为复杂,有以下几点
4
#  - 如果查找目标是字符串,以"kulve"为例,那么,在写查找目标时,需要使用另外一种引号包裹,即 '"kulve"'.
5
#  - 如果查找目标是数字,那么需要写成'1'
6
#  - 如果查找目标是多个值,那么需要写成 '["kulve1", "kulve2"]'
7
# path: 用于指定要搜索的json路径
8
# return: 1(true) / 0(false)
9

10

11
# member of函数语法
12
<查找目标> member of(<json列名 | json函数返回的结果>)

1
CREATE TABLE `bjxz_sickers_json` (
2
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '患者id',
3
  `name` varchar(11) COLLATE utf8_unicode_ci NOT NULL COMMENT '患者名称',
4
  `gender` enum('男','女') COLLATE utf8_unicode_ci DEFAULT '男' COMMENT '性别',
5
  `drugs` json COMMENT '用药情况',
6
  PRIMARY KEY (`id`)
7
) comment '患者表,json形式的';
8

9
INSERT INTO `bjxz_sickers_json` (`name`, `gender`, `drugs`) VALUES
10
-- ✅ 包含'地芬尼多'的数据（5条）
11
('张伟', '男', '["奥卡西平","地芬尼多"]'),
12
('李娜', '女', '["普拉克索","左旋多巴","地芬尼多"]'),
13
('王强', '男', '["奥卡西平","普拉克索","地芬尼多"]'),
14
('赵敏', '女', '["左旋多巴","地芬尼多"]'),
15
('陈浩', '男', '["奥卡西平","地芬尼多"]'),
16

17
-- ❌ 不包含'地芬尼多'的数据（15条）
18
('刘洋', '男', '["奥卡西平"]'),
19
('孙婷', '女', '["普拉克索","左旋多巴"]'),
20
('周磊', '男', '["左旋多巴","奥卡西平"]'),
21
('吴芳', '女', '["奥卡西平","普拉克索"]'),
22
('郑凯', '男', '["普拉克索"]'),
23
('黄丽', '女', '["左旋多巴"]'),
24
('林峰', '男', '["奥卡西平","左旋多巴","普拉克索"]'),
25
('何雪', '女', '["普拉克索"]'),
26
('马超', '男', '["左旋多巴","奥卡西平"]'),
27
('罗琳', '女', '["奥卡西平","普拉克索"]'),
28
('谢鹏', '男', '["左旋多巴"]'),
29
('韩梅', '女', '["普拉克索","左旋多巴"]'),
30
('唐昊', '男', '["奥卡西平"]'),
31
('许静', '女', '["左旋多巴","奥卡西平","普拉克索"]'),
32
('邓飞', '男', '["普拉克索"]');