MySql数据库系统基础

课程目标

• 如何使用MySQL数据库（如何安装和基本语法，工具的使用）
• 如何设计数据库（一种策略、思想，与经验有关）
• SQL编程

我们要学四种语言：
1.标签语言：HTML & CSS（执行在浏览器端）
2.逻辑语言: Python,Java,Go,PHP...（执行在服务器端）
3.MySQL编程语言（执行在MySQL服务器端）
4.JS,TS逻辑语言（执行在浏览器端）

在今天的AI时代，实际上学习数据库是比以前容易了很多。

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

1.安装Typora打开markdown文档笔记
2.什么是数据库
3.在phpstudy（小皮中）安装和启动mysql8（重点）
4.mysql8的命令行（重点）
5.mysql是如何在osi模型中传递数据的原理剖析
6.Innodb和MyISAM的存储引擎
7.修改存储引擎(重点)
8.datagrip的安装和绿化（重点）
9.datagrip链接mysql（重点）
10.datagrip的图形化操作(重点)
11.SQL的经典分类和扩展分类(重点)
12.DDL数据库操作语句(重点)
13.通义灵码配置mysql插件
14.通过vibe coding生成DDL语句

1.数据库简介

简单来说，数据库就是存储数据的‘仓库（地方、方式、位置）’，比如我们平时使用的记事本、档案袋和生活便签等都是数据库，包括我们现在使用的word也是一种数据库！

很显然，少量的数据很容易管理，但是当数据量越来越大的时候，就需要引入专门管理数据的工具，我们就叫做数据库管理系统！

数据库系统 = 数据库管理系统 + 数据库 + 数据库管理员

DataBase System(DBS) = DataBase Management System + DataBase(DB) + DBA

对于mysql数据库系统来说，它是如下模式：

DBS = DBMS      + DB            + DBA
     (mysqld)    (库表文件)        (人)
      ↑ 服务端    ↑ 磁盘数据        ↑ root/运维

数据库：对大量的信息进行管理的高效的解决方案，按照数据结构来组织、存储和管理数据的“仓库”；

通常一个中小型web项目（网站）会使用一个数据库来存储其所有的动态数据！

数据库发展历程：层状型，网状型，目前主流的数据库是关系型的！

1.1.关系型数据库

1. 分类

大型：

Oracle：甲骨文

中型：

SQL Server：微软

MySQL：目前也是甲骨文公司的（最开始是瑞典的MySQL AB公司，08年被Sun公司收购了，09年Sun公司又被甲骨文收购了）

小型：

access（ASP + access，ASP.net+SQL Server），VF

Sqllite数据库

在web应用中，使用的最多的就是MySQL数据库，原因如下：

1， 开源、免费

2， 功能足够强大，足以应付web应用开发（最高支持千万级别的并发访问）

除了关系型数据库以外，还有nosql和向量数据库

https://db-engines.com/en/ranking

2. 关系型的定义

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

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

什么是实体和实体集？

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

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

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

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

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

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

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

1.2 总结

1. 什么是数据库？

• 通俗说：存数据的地方（如记事本、Word）。
• 专业说：按结构组织存储数据的“仓库”。
• 数据库系统 (DBS) = 管理软件 (DBMS) + 数据 (DB) + 管理员 (DBA)

2. 主流：关系型数据库

核心特点：用“二维表”存数据（像Excel）。

常见分类：

• 大型：Oracle
• 中型：SQL Server、MySQL（Web开发最常用，开源免费，性能好）
• 小型：Access

为什么叫“关系型”？

• 现实世界有实体（如：学生、老师）。
• 数据库里用表（如：学生表、教师表）来存储。
• 表与表之间是有联系的，所以叫“关系型”。

2.SQL语言

2.1 概念

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

2.2 SQL语言的分类

思考一下:

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

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

2， 再创建一张数据表

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

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

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

1.经典分类（4种,重点）

DDL DDL：Data Definition Language，数据定义语言用于创建或删除数据库、数据表、字段的SQL语句，包含以下几种指令：

DML DML：Data Manipulation Language，数据操作语言 主要就是对表中的记录进行增删改查的操作！

其中“查询”部分，又叫做DQL（Data Query Language）！

DCL DCL：Data Control Language，数据控制语言用于对控制数据库的操作权限的，包括用户权限以及数据操作权限。

2.扩展分类（6种，了解）

DDL：数据定义语言，进行数据库、表的管理等，如create、drop DQL：数据查询语言，用于对数据进行查询，如select DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback DCL：数据控制语言，进行授权与权限回收，如grant、commit,rollback,remove CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor

2.3 总结

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

4个单词搞定分类：

3.安装mysql

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.mysql8的命令行

4.1.打开mysql8命令行

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

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

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

然后输入cmd命令，回车

就能打开mysql的命令行

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

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

4.2 打开mysql控制台

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

# 方式一
mysql -uroot -proot [数据库名]
# 方式二
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 地址用于唯一标识网络中的每一台设备。它充当设备的“地址”，使得数据能够在网络中准确地找到目标设备。

IPV4：
    格式: x.x.x.x  x的取值范围（第一位x取值1-223，从第二位开始0-255） 
    IP可以分为公网/外网ip和私网/内网ip
    
    公网/外网IP:   IP 是全球唯一的，可以在任何地方直接访问到该地址
    私网/内网ip:   局域网内使用的地址，不能直接通过互联网访问,目的是为了内部网络的设备进行通信
    
IPV6：
    由8个16位的十六进制数组成，每组数字之间用冒号分隔。
    如：2001:0db8:85a3:0000:0000:8a2e:0370:7334

目前市场上，主要还是IPV4为主，IPV6为辅助. 
需要注意的是，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.命令行基本操作

5.1 show命令

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

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

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

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

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

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

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

5.2 \G显示方式

5.3 exit命令

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

6.存储引擎

6.1 修改存储引擎

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

注意：改完记得保存

在命令行中，输入show engines

6.2 Innodb和MyISAM引擎

6.2.1 简单理解Innodb和MyISAM引擎

白话理解innodb: 它是一个功能齐全的存储引擎且安全性高. 但查询性能稍差.

白话理解MyISAM: 它是一个功能简单的存储引擎,无安全能力. 但查询性能很强.

选用存储引擎示例:

登录:

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

结论: 选用MyISAM,原因是需要高速查询.

支付: 张三给李四转账100

1. 判断张三是否有100元(查询)
2. 在张三账户-100,在李四账户+100(修改,写,需要事务处理(保持原子操作).)
3. 返回李四的余额和张三的余额(查询)

结论: 选用Innodb: 第二步需要事务处理(原子操作),若出现问题,银行直接倒闭.

6.2.2 面试八股文(Leet Code)

1. 引擎的作用域

   所有的存储引擎是作用在表上,而不是数据库上.

   sql

   create database nice;
   
   use nice;
   
   create table users
   (
       id   int comment '用户编号',
       name varchar(20) comment '姓名'
   );
   
   # 在这里,可以发现 ENGINE=InnoDB
   show create table users;
   
   
   create table accounts
   (
       id   int comment '用户编号',
       name varchar(20) comment '姓名'
   ) engine = myisam;
   
   # 在这里,可以发现 ENGINE=MyISAM
   show create table accounts;

2. innodb和MyISAM的文件结构

   mysql8中

   Innodb是以单个文件形式存储的,它会把数据与结构存放在一起,扩展名为.ibd.

   MyISAM是以多个文件形式存储的.

   • .sdi: 数据结构文件,在mysql8中结构被mysql使用json格式优化了.
   • .myi: 索引文件.
   • .myd: 数据文件.

   在mysql5中

   Innodb是以两个文件形式存储的,会将数据结构独立出来.

   • .ibd: 数据与索引文件
   • .frm: 数据结构文件

   MyISAM是以多个文件形式存储的.

   • .frm: 数据结构文件.
   • .myi: 索引文件.
   • .myd: 数据文件.

3. MyISAM在mysql8之前的功能(了解)

   MyISAM是具有数据压缩功能和数据结构修复功能,但在mysql8中,功能被弃用.

   在mysql5中修复MyISAM表的命令

   • 在mysql5的bin目录下打开命令行

   powershell

   myisamchk <表.myi文件路径>

   在mysql5中压缩MyISAM表的命令

   • 在mysql5的bin目录下打开命令行

   powershell

   myisampack <表.myi文件路径>

   对于历史数据有用

   注意: 表在被修复和压缩过后,将会变为只读.

6.3 Arhive引擎

mysql8推荐用于取代MyISAM的方案

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

不支持删除,修改.

6.4 并发

行锁:

MyISAM不支持行锁

innodb支持行锁功能

7.DataGrip和通义灵码

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.DDL之数据库操作

8.1 创建数据库语法规则

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

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

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

create database ecmall;
create schema easycms;

推荐写法

create database if not exists itcast;

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

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

8.2 use语句

格式：

 use 数据库名字;

示例:

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

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

mysql -uroot -proot echop

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

8.4 查看当前数据库

格式：

select database();

8.5 删除数据库

格式：

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

示例:

drop schema ecmall;
drop database itcast;

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

8.6 修改数据库(了解)

ALTER DATABASE test_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

这个学不学都无所谓

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

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

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

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

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

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

9.Mysql中的数据类型

数据库里面的数据在保存时也要通过指定数据的类型来告诉数据库管理系统，这些数据有什么用途，所以也会有对应的数据类型。数据类型是为了节约内存,提高计算速度，尽量使用存储空间少的类型。常用数据类型有数值类型、字符串类型、时间日期类型、枚举类型。mysql的单表数据可以支持最多千万级（2000W是比较合适的）

9.1 数值类型

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

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

示例：

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

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

9.2 字符串类型

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

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

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

1.char与varchar的区别：

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

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

2.varchar字符串和text文本的区别

varchar可指定n，text不能指定 
text类型不能有默认值，注意json也不能有默认值。(但是在8.0.13版本后,支持常量默认值default 'textDefaultValue')
varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text。
索引：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的人查找出来

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

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

9.5 自增列

use nice;

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


insert into human values
    (null,'张三'),
    (null,'李四'),
    (null,'王五')

10.DDL之数据表操作

10.1 CREATE TABLE（创建表）

语法规则:

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

列定义规则：

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

示例：

# 设计一张会员表

10.2 复制表结构

语法规则:

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

示例:

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

10.3 ALTER TABLE（修改表）

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

语法规则：

ALTER TABLE <表名> 操作类型;

示例：

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

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

10.4 清空表

语法规则:

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

delete与truncate的区别

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

11. DML数据库表操作

DML 的核心操作无非“增删改”，而在应用开发中，这些操作大多已由 ORM 框架封装完成。至于查询（SELECT，隶属 DQL），在表关系简单的场景下，ORM 同样能胜任；但对于需要深入挖掘数据或性能优化的复杂查询，手写原生 SQL 仍是必修课。虽然 SELECT 语句的复杂度往往取决于业务场景与个人经验，但随着 AI 辅助编程工具的普及，即便是普通开发者，如今也能借助 AI 轻松驾驭高阶的查询逻辑。

11.1 insert语句（增）

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

语法

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

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

示例:

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

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

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


# 多行插入,使用","隔开.
insert into pet (pet_id, name, species, breed, intake_date, status)
values (1, '小白', '猫', '中华田园猫', '2025-02-01', '健康'),
       (2, '旺财', '狗', '中华田园犬', '2025-02-05', '需治疗'),
       (3, '咖啡', '猫', '英国短毛猫', '2025-02-10', '健康'),
       (4, '雪球', '狗', '萨摩耶', '2025-02-12', '健康');
       
# 复制表数据(当然,可以带条件)
create table pet_copy like pet;

insert into pet_copy
select *
from pet;

11.2 select语句（查）

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

示例:

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

在select中使用where子句

# 一般查询
select id, mobile
from ai_members
where id = 5;

11.3 update语句（改)

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

语法

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

11.4 delete语句(删)

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

示例

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

11.5 软删除操作

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

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

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

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

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

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

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

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


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

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


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

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


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

11.6 Mysql数据库的导出(备份)和导入(恢复)

这个技术是运维的工作.

# 导出
mysqldump -u root -p <数据库名> > <文件名>
# 导入
mysql -u root -p <数据库名> < <文件名>

12.字段约束

也叫完整性约束条件，主要是为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

12.1 默认值约束

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

示例:

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

12.2 非空约束

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

示例:

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

12.3 唯一性约束（索引）

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

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

示例:

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

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

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

12.4 主键约束（索引)

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

示例:

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. 索引和Explain关键字

13.1 索引的好处和缺点

• 好处：

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

• 提高排序和去重效率

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

• 缺点：

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

• 占用磁盘空间

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

13.2 使用Explain查看索引的情况

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

13.3

14.索引的创建和删除

在 MySQL 中，创建索引主要有以下几种常用方式，简单说明如下：

14.1 建表时直接创建

drop table if exists user;
create table user
(
    id       int unsigned primary key,
    username varchar(20),
    password varchar(20),
    email    varchar(55),
    # 创建普通索引
    # index <索引名> (<字段名>)
    index idx_username (username),
    # 创建唯一索引
    # unique index <索引名> (<字段名>)
    unique index uidx_email (email)

);
show indexes from user;

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

什么时候该使用索引?

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

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

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

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

14.3 使用 ALTER TABLE 创建索引

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

14.4 查看/删除索引

-- 查看
SHOW INDEX FROM user;
show index from <表名>;
# 或
show indexes from <表名>;

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

15.DQL单表查询（Select）

15.1 DQL 单表查询核心语法

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

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

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

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

15.2 数据准备

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

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

15.3 快速上手

案例1：查询全部 / 指定列

select * 
from students;

select name as cnname, score
from students

案例2：带条件查询

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

案例3：AND / OR / BETWEEN / IN

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

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

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

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


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


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

案例4： Like模糊查询

从mysql5.6开始才支持中文检索的.如果版本低于mysql5.6那么我们需要安装一个叫Sphinx

like这个search工具只适合做小型应用检索。现实当中Elasticseach,OpenSearch是工业级的，MeiliSearch是轻量级的

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

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

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

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

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

案例5：去重,排序

一般不会使用去重,因为去重量会导致索引失效,去重一般在程序中的set集合中去除.

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

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


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

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

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

案例6: 限制行数

表记录下标从0开始.

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

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

面试八股文: SQL注入攻击

原理: 从SQL层面,注入一个恒成立的条件,进面绕过字符串比对.

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

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

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

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


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

select * from login_members;


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

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

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


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

15.4 常用聚合函数

案例: 平均分、最高分、人数

-- 平均分、最高分、人数
SELECT
  AVG(score) as 平均分,
  MAX(score) as 最高分,
  COUNT(*) as 人数
FROM students;

扩展：count(1)是什么意思

如果没有where 子句count的性能很高,因为是获取的optimized中的内容.

有where子句时,有可能会使用不到索引.

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

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

案例：求总分和平均分，并且小数保留2位

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

15.5 GROUP BY 分组查询

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

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

实际业务中，往往需要：

“先分组，再分别统计”

例如：

按性别统计人数

按科目统计最高

语法规则：

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

案例 1：统计男女人数

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

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

案例 2：按性别统计平均成绩

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

案例 3：只统计成绩 ≥ 80 学生的平均分，再按性别分组

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

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

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

WHERE 作用于 分组前

15.6 HAVING 分组后筛选

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

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

基础案例：只显示平均成绩 ≥ 85 的性别组

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

优化代码后:

综合案例

SELECT
    gender AS 性别,
    COUNT(*) AS 人数,
    AVG(score) AS 平均成绩,
    MAX(score) AS 最高分,
    MIN(score) AS 最低分
FROM students
WHERE score IS NOT NULL
GROUP BY gender
HAVING AVG(score) >= 80
ORDER BY 平均成绩 DESC;

15.7 速记方法和思考题

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

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

WHERE：分组前过滤行

HAVING：分组后过滤组

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

🔹 想分组，GROUP BY

🔹 想统计，聚合函数

🔹 想筛行，用 WHERE

🔹 想筛组，用 HAVING

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

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

16.常用内置函数

数值函数

字符串函数

这里单独抽出一个GROUP_CONCAT()让大家关注一下,其作用如下：

将同一组中的多个值，拼接成一个字符串返回，常用于：“一对多”结果的单行展示

我个人是非常喜欢用这个函数的。

日期时间函数

其他函数

重点1：GROUP_CONCAT函数

数据准备:

CREATE TABLE stu_course(
  stu_name VARCHAR(10),
  course VARCHAR(10)
);

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

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

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

重点2： find_in_set

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

用于在集合中查询一个值

find_in_set始终无法利用索引.

语法规则:

find_in_set(关键字，字段名)

数据准备:

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


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

-- 不包含'地芬尼多'的数据（15条）
('刘洋',   '男', '奥卡西平'),
('孙婷',   '女', '普拉克索,左旋多巴'),
('周磊',   '男', '左旋多巴,奥卡西平'),
('吴芳',   '女', '奥卡西平,普拉克索'),
('郑凯',   '男', '普拉克索'),
('黄丽',   '女', '左旋多巴'),
('林峰',   '男', '奥卡西平,左旋多巴,普拉克索'),
('何雪',   '女', '普拉克索'),
('马超',   '男', '左旋多巴,奥卡西平'),
('罗琳',   '女', '奥卡西平,普拉克索'),
('谢鹏',   '男', '左旋多巴'),
('韩梅',   '女', '普拉克索,左旋多巴'),
('唐昊',   '男', '奥卡西平'),
('许静',   '女', '左旋多巴,奥卡西平,普拉克索'),
('邓飞',   '男', '普拉克索');

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

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

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

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

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

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

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

json_contains与member of

对于json类型的字段,使用json_contains与member of可以有效利用json字段的多值索引.

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


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

数据准备:

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

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

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