黑马程序员MySQL学习笔记

1 数据库基本介绍

1.1 数据库基本知识

数据库：database（DB），是一种存储数据的仓库

数据库是根据数据结构组织、存储和管理数据
数据库能够长期、高效的管理和存储数据
数据库的目的就是能够存储（写）和提供（读）数据

1.2 数据库分类

数据库分类：根据数据库的架构和数据组织原理进行分类

1、早期根据数据库的组织数据的存储模型分类

层次数据库：基于层次的数据结构（数据分层）——树
网状数据库：基于网状的数据结构（数据网络）——图
关系数据库：基于关系模型的数据结构（二维表）

2、现在较多根据实际数据管理模型分类（存储介质）

关系型数据库：基于关系模型的数据结构（二维表）通常存储在磁盘（比较稳定）
非关系型数据库：没有具体模型的数据结构（键值对）通常存储在内存（效率高）

1.3 关系型数据库

关系型数据库：是一种建立在关系模型上的数据库

关系模型
- 关系数据结构（存储）
- 关系操作集合（操作）
- 关系完整性约束（约束）
关系型数据库存储在磁盘中（永久性存储）
关系型数据库系统（DBS）模型有四层结构
- 数据库管理系统（DBMS）：管理系统运行（DataBase Management System）
- 数据库（DB）：数据存储的管理者（小管理，受DBMS管理）
- 数据表（Table）：数据关系管理者
- 数据字段（Field）：依赖于数据表，实际数据存储者
关系型数据库产品
- 大型：Oracle、DB2
- 中型：MySQL、SqlServer
- 小型：Sybase、Access

1.4 非关系型数据库

非关系型数据库：NoSQL（Not only SQL），不仅仅是关系型数据库

所有不是关系型数据库的统称
数据存储模型不是二维表，而是键值对（key->value）
存储的位置通常是内存（效率高）
不能永久性存储（需要定时存到关系型数据库中）
常见的非关系型数据库产品
- MongoDB
- Redis
- Memcached

对比

NoSQL通常是与关系型数据库配合使用的，他们彼此是一种互补关系

NoSQL运行在内存，解决效率问题
- I/O问题
- 效率问题
MySQL运行在磁盘，解决稳定问题
- 安全问题（永久存储）
- 稳定

小结

1、NoSQL是对非关系型数据库的一类统称

NoSQL是不仅仅只是关系型数据库的意思

2、NoSQL通常运行在内存

读取效率高
并发访问高
稳定性不高（断电即丢失）

3、NoSQL通常是键值对存储数据，访问也比较简单

1.5 SQL基本介绍

SQL：Structured Query Language，结构化查询语言，是一种针对关系型数据库特殊标准化的编程语言

SQL是一种编程语言
能够实现用户数据库查询和程序设计
SQL根据操作不同，分为几类
- DQL：Data Query Language，数据查询语言，用于查询和检索数据
- DML：Data Manipulation Language，数据操作语言，用于数据的写操作（增删改）
- DDL：Data Definition Language，数据定义语言，用于创建数据结构
- DCL：Data Control Language，数据控制语言，用于用户权限管理
- TPL：Transaction Process Language，事务处理语言，辅助DML进行事务操作（因此也归属于DML）

小结

1、SQL虽然是编程语言，但是目前数据库通常只用来进行数据管理（逻辑部分给其他编程语言）

2、SQL虽然是针对关系型数据库的通用语言，但是不同的产品操作指令不完全通用

1.6 MySQL基本介绍

MySQL：是瑞典AB公司下的一款关系型数据库

MySQL当前属于甲骨文公司（AB->Sun->Oracle）
MySQL开源免费（部分存储引擎收费，源码开源，Oracle收费）
MySQL是一种C/S结构软件，因此需要MySQL的客户端来访问服务端（数据管理）
- mysqld.exe：服务端
- mysql.exe：客户端
MySQL使用SQL指令对数据库进行操作

访问原理

graph LR
A(MySQL客户端)-->B[寻找服务端<br>host寻找]
B-->C[寻找服务端<br>Port寻找]
C-->D[验证身份<br>username判定<br>password判定]
D-->E((服务端))

小结

1、MySQL是一款流行的关系型数据库

2、MySQL是一款C/S结构的软件，需要客户端访问服务端

3、MySQL是基于SQL指令进行管理操作

1.7 MySQL访问

MySQL访问：就是客户端连接上服务端，然后实现数据操作的过程

客户端访问服务端
- 利用Windows控制台访问（MySQL客户端）
- 利用数据库管理工具（Navicat）
客户端需要连接认证
- -h：主机地址（本机localhost可以省略）
- -P：端口号（默认3306可以省略）
- -u：用户名
- -p：用户密码
客户端连接上服务端就表示占用了一个资源，可以进行对应权限的操作
- MySQL数据库连接资源有限：单个服务器最多16384个
- 连接资源不够了其他访问就需要排队等待
- 用完尽可能释放资源
客户端退出服务端
- \q
- quit
- exit

步骤

1、打开客户端（CMD控制台）：mysql -uroot -p密码

2、输入服务器信息（连接）和用户信息（验证）

3、执行SQL操作

4、断开连接

小结

1、MySQL需要通过客户端来进行服务端访问

自带客户端mysql.exe：Windows下借助CMD
数据库管理工具：Navicat，图形化管理
支持MySQL扩展的编程语言：PHP、Java等

2、数据库操作需要进行连接认证

主机地址：-h，默认localhost可省略
端口：-P（大写字母），默认3306可省略
用户名：-u
密码：-p（小写字母）

3、数据库连接资源有限，用完即关闭

1.8 总结

1、数据库的作用要清楚：高效的存储和管理数据，为编程语言提供数据支撑

2、当前市面上数据库的分类主要为两类

关系型数据库：注重结构和数据存储的持久性
非关系型数据库：注重数据读取的效率

3、关系型数据库是几乎所有网站都会使用到的，必须掌握其概念

4、所有关系型数据库都是基于SQL进行数据的操作，MySQL数据库也是（不同的数据库产品对应的SQL指令可能有细微差别）

5、MySQL是一种C/S结构的软件，所以访问者必须通过客户端进行访问

客户端与服务端通常不会在一台电脑上
客户端访问服务端需要寻址、授权（-hPup）
MySQL服务端的连接数是有限的，时刻注意用完就销毁（减少资源无效占用）

2 MySQL基本操作

2.1 SQL语法规则

SQL语法规则：SQL是一种结构化编程语言

基础SQL指令通常是以行为单位
SQL指令需要语句结束符，默认是英文分号：;、\g、\G
- \G：主要用于查询数据，立体展示结果
SQL指令类似自然语言
编写的SQL中如果用到了关键字或者保留字，需要使用反引号``来包裹，让系统忽略

示例

1、结构创建

1	create 结构类型结构名结构描述;

2、显示结构

#显示结构
show 结构类型（复数）;

#显示结构创建详情
show create 结构类型 结构名;

3、数据操作（数据表）

#新增数据
insert into 表名 values

#查看数据
select from 表名

#更新数据
update 表名 set 

#删除数据
delete from 表名

小结

1、SQL是一种类似于自然语言的编程语言

基本SQL指令以行为单位
SQL指令需要语句结束符

2、根据数据库的对象层级，可以将基础SQL操作分为三类

库操作：数据库相关操作
表操作：数据表（字段）相关操作
数据操作：数据相关操作

2.2 SQL库操作

1、创建数据库

创建数据库：根据项目需求创建一个存储数据的仓库

使用create database 数据库名字创建
- 数据库层面可以指定字符集：charset / character set
- 数据库层面可以指定校对集：collate
创建数据库会在磁盘指定存放处产生一个文件夹
创建语法

1	create database 数据库名字 [数据库选项];

示例

1、创建一个指定名字的数据库

1	create database db_1;

2、创建一个指定字符集的数据库

1	create database db_2 charset utf8MB4;

3、创建一个指定校对集的数据库

1	create database db_3 charset utf8MB4 collate utf8mb4_general_ci;

小结

1、数据库的创建是存储数据的基础，数据库的创建通常是一次性的

2、创建数据库的语法包含几个部分

关键字： create database
数据库名字：自定义名字
- 数字、字母和下划线组成
- 不区分大小写
- 数字不能开头
- 使用下划线法创建复杂数据库名字（很少使用驼峰命名法）
数据库选项：非必须的规定
- 字符集：charset /character set 字符集。非必须，默认继承DBMS
- 校对集：collate 校对集。非必须，依赖字符集

3、创建好的数据库可以在数据存储指定地点（安装时指定）看到

一个数据库对应一个文件夹
每个数据库下有个对应的文件，里面有字符集和校对集信息（Mysql7以前）

2、显示数据库

显示数据库：通过客户端指令来查看已有数据库

数据库的查看是根据用户权限限定的
数据库的查看分为两种查看方式：
- 查看全部数据库
- 查看数据库创建指令

示例

1、显示所有数据库

1	show databases;

2、显示数据库创建指令

1	show create database db_1;

小结

1、查看数据库分为两种方式

查看全部：show databases;
查看具体创建指令：show create database 数据库名字;

2、查看数据库的目的和应用

开发人员确认数据库是否存在
数据库管理员维护

3、使用数据库

使用数据库：指在进行具体SQL指令之前，让系统知道操作针对的是哪个数据库

数据库的操作通常是针对数据表或者数据
通过使用数据库可以让后续指令默认针对具体数据库环境
使用数据库语法：use 数据库名字;

示例

use db_1;

小结

1、使用数据库的指令是：use 数据库名字;

2、使用数据库的目标

让系统知道后续SQL指令都是针对当前选择的数据库
简化后续SQL指令的复杂度（如果不指定数据库，那么所有的SQL操作都必须强制指定数据库名字）

4、修改数据库

修改数据库：修改数据库的相关库选项

数据库名字不可修改（老版本可以）
- 先新增
- 后迁移
- 最后删除
数据库修改分为两个部分（库选项）
- 字符集
- 校对集
数据库修改指令（与创建指令差不多）

1	alter database 数据库名字库选项

示例

1、修改数据库字符集

1	alter database db_2 charset gbk;

2、修改数据库校对集（如果校对集修改必须同时改变字符集）

1	alter database db_3 charset gbk collate gbk_chinese_ci;

小结

1、数据库的修改只能修改库选项，不能修改名字

2、字符集的修改指令使用alter，其他跟创建指令一致

3、数据库修改通常有两部分

字符集修改
校对集修改（校对集必须对应字符集）

4、一般我们都不会使用数据库修改（一般要改也是删除后新增）

5、删除数据库

删除数据库：将当前已有数据库删除

删除数据库会删除数据库内所有的表和数据
删除数据库操作要慎重（删前备份）
删除数据库后，对应的存储文件夹就会消失
删除语法

1	drop database 数据库名字;

示例

1	drop database db_1;

小结

1、删除数据库使用指令：drop database 数据库名字

2、数据库的删除不可逆

删除会清空当前数据库内的所有数据表（表里数据一并删除）
删除数据库会将对应的文件夹从磁盘抹掉
数据库删除要谨慎（一般不建议删除）

6、总结

1、数据库的操作通常是一次性的，即在进行业务代码开展之前将数据库维护好

2、数据库的删除需要非常慎重，尤其是生产环境，数据库的删除是不可逆（会将数据库中的所有数据全部删除）

2.3 SQL表（字段）操作

1、创建数据表

创建数据表：根据业务需求，确定数据表的字段信息，然后创建表结构

表与字段不分家，相辅相成
表的创建需要指定存储的数据库
- 明确指定数据库：数据库.表名
- 先使用数据库：use 数据库名字
字段至少需要指定名字、类型
数据库表不限定字段数量
- 每个字段间使用逗号,分隔
- 最后一个字段不需要逗号
表可以指定表选项（都有默认值）
- 存储引擎：engine [=] 具体存储引擎
- 字符集：[default] charset 具体字符集（继承数据库）
- 校对集：collate（继承数据库）
表创建语法

create table [数据库名.]表名(
    字段名 字段类型,
    ...
    字段名 字段类型
)表选项;

示例

1、创建简单数据表（指定数据库创建数据表）

1
2
3

create table db_2.t_1(
    name varchar(50)
);

2、创建数据表——多字段

# 使用数据库（进入数据库环境）
use db_2;
create table t_2(
    name varchar(50),
    age int,
    gender varchar(10)
);

3、创建数据表——表选项

1
2
3

create table t_3(
    name varchar(50)
)engine Innodb charset utf8MB4;

小结

1、创建数据库表是为了存储具体数据

2、数据表的创建与字段是同时存在的

3、数据表的创建需要指定数据库

在表名字前指定数据库：数据库名.表名
进入数据库环境（常用）

4、一张数据表用来存一组相关数据

5、扩展：存储引擎是指数据存储和管理的方式，MySQL中提供了多种存储引擎，一般使用默认存储引擎

InnoDB（常用）
- 默认存储引擎
- 支持事务处理和外键
- 数据统一管理
MyIsam
- 不支持事务和外键
- 数据、表结构、索引独立管理
- MySQL5.6以后不再维护

6、扩展：如果想创建一个与已有表一样的数据表，MySQL提供了一种便捷的复制模式

create table 表名 like 数据库名字.表名

create table t_3_like like t_3;

2、显示数据表

显示数据表：客户端通过指令显示已有的数据表

数据表的显示跟用户权限有关
显示数据表有两种方式
- 显示所有数据表
- 显示具体数据表的创建指令

示例

1、显示所有数据表——当前数据库下

1	show tables;

2、显示所有数据表——指定数据库

1	show tables from db_3;

3、显示部分关联数据表——匹配

1	show tables like '%like'; # _表示匹配一个字符（固定位置），%表示匹配N个字符

4、显示数据表的创建指令

1	show create table t_1; # 看到的结果未必一定是真实创建的指令（系统会加工）

小结

1、显示数据表有两种形式

显示所有数据表：show tables [from 指定数据库];
- 显示部分：show tables like ‘pattern’; 匹配模式：_匹配单个字符，%匹配不限量字符
显示数据表创建指令：show create table 表名;

2、显示数据表通常是为了验证数据表是否存在或者验证数据表的创建指令是否正确

3、在显示数据的时候可以使用不同的语句结束符

\g：与普通分号无区别
\G：纵向显示列数据

3、查看数据表

查看数据表：指查看数据表中的具体结构

通常是查看字段信息
详细的显示字段的各项信息
查看语法有三种（效果一样）

1
2
3

desc 表名；
describe 表名;
show columns from 表名;

示例

desc t_1;

小结

1、数据表的查看是为了查看表中具体字段的信息

2、查看数据表的指令有多个，效果都一样

desc 表名;（常用）
describe 表名;
show columns from 表名;

3、查看表结构的原因通常是在开发过程中为了更清晰的了解数据的存储形式和要求

4、更改数据表

更改数据表：修改表名字和表选项

修改表名：rename table 表名 to 新表名
修改表选项：alter table 表名

示例

1、修改表名

1	rename table t_1 to t1;

注意：如果有时候想要跨库修改的话，需要使用数据库名.表名（一般不建议）

2、修改表选项

1	alter table t1 charset utf8;

小结

1、更改数据表分为两个部分

更改表名：rename table 原表名 to 新表名；
更改表选项：alter table 表名表选项

2、通常我们较少使用更改数据表，数据表应该在创建时就定义好

5、更改字段

更改字段：指针对表创建好后，里面字段的增删改

字段操作包含字段名字、类型和属性的操作
字段操作分为四类
- 新增字段：add [column]
- 更改字段名：change
- 修改类型：modify
- 删除字段：drop
字段操作还有位置处理
字段操作通常是在表已经存在数据后进行

（1）新增字段

新增字段：在表创建好后往里面增加其他字段

字段的新增必须同时存在字段类型
新增语法

1	alter table 表名 add [column] 字段名字段类型 [字段属性] [字段位置]

示例

1、给已经存在的t_3表增加一个字段age

1	alter table t_3 add age int;

2、给已经存在的t_3表增加一个字段nickname

1	alter table t_3 add column nickname varchar(10);

小结

1、新增字段就是给已有表追加一个字段（较少）（因为，增加的数据从何处来）

2、字段新增必须指定字段类型

3、字段新增语法为：alter table 表名 add [column] 字段名字段类型;

4、字段的追加默认是在所有字段之后

（2）字段位置

字段位置：指字段放到某个指定字段之后

字段位置分为两种
- 第一个字段：first
- 某个字段后：after 已经存在字段名
字段位置适用于追加字段、修改字段、更改字段名
字段位置语法

1	alter table 表名字段操作字段位置;

示例

1、为t_3表增加一个id字段，放到最前面

1	alter table t_3 add id int first;

2、在t_3表name字段后增加一个身份证字段card

1	alter table t_3 add card varchar(18) after name;

小结

1、字段位置是配合字段操作的（新增、修改）

2、字段位置分两种

最前面（第一个字段）：first
字段后面：after 已存在字段名

（3）更改字段名

更改字段名：指对已经存在的字段名进行修改

字段名的修改也必须跟上字段类型
字段名修改语法

1	alter table 表名 change 原字段名新字段名字段类型 [字段属性] [位置]

示例

修改字段名card为sfz

1	alter table t_3 change card sfz varchar(18);

小结

1、字段名更改通常只是修改字段名字，但是也必须跟随类型

2、字段名修改语法：alter table 表名 change 原字段名新字段名字段类型

3、字段名修改change其实也可以修改字段类型、属性和位置，但是通常不使用（专人专事）

（4）修改字段

修改字段：指修改字段的相关信息

修改字段类型、字段属性和位置
修改字段语法

1	alter table 表名 modify 字段名字段类型 [字段属性] [位置]；

示例

修改身份证的类型为char(18)并且位置放到id后面

1	alter table t_3 modify sfz char(18) after id;

小结

1、修改字段包含多个操作

字段类型修改
字段属性修改
字段位置修改

2、修改字段语法：alter table 表名 modify 字段名字段类型 [字段属性] [位置];

6、删除字段

删除字段：即将某个不要的字段从表中剔除

删除字段会将数据也删除（不可逆）
删除字段语法

1	alter table 表名 drop 字段名;

示例

删除年龄字段

1	alter table t_3 drop age;

小结

1、字段删除在删除字段名的同时会删除字段对应的数据，而且不可逆

2、字段删除语法：alter table 表名 drop 字段名

7、总结

1、数据表结构的操作是数据操作的基础

2、一般情况下新手都不会接触数据表的设计，但是作为一名新手一定要在使用数据表之前查看数据表的结构信息

3、不要轻易的修改或者删除数据表结构（数据会一并被处理掉）

4、数据表结构的维护通常是一次性的，在业务开展前尽可能好的设计好数据表，而不要后期再进行其他维护

2.4 SQL数据操作

1、新增数据

新增数据：将数据插入到数据表永久存储

新增数据是根据表的字段顺序和数据类型要求将数据存放到数据表中
数据表中的数据以行（row）为存储单位，实际存储属于字段（field）存储数据
数据插入分两种方式
- 全字段插入：insert into 表名 values(字段列表顺序对应的所有值);
- 部分字段插入：insert into 表名 (字段列表) values(字段列表对应的值顺序列表);

示例

1、给t_3表插入一条完整数据

1	insert into t_3 values(1,'440111200011111101','Jim','Green');

2、根据字段插入数据

1	insert into t_3 (id,sfz,name) values(2,'441000200011111211','Tom');

小结

1、数据插入是利用insert指令将数据永久存储到数据表中

2、数据存储以行为单位，字段为最小实际存储单位

3、数据插入分为两种方式插入数据

全字段插入
- insert into 表名 values(值列表)
- 值列表必须与字段列表顺序一致
- 值列表的每个数据类型必须与字段类型一致
部分字段插入
- insert into 表名 (字段列表) values(值列表)
- 字段列表可以顺序随意
- 值列表必须与指定的字段列表顺序一致
- 值列表元素的类型必须与字段列表的类型一致

2、查看数据

查看数据：将表中已经存在的数据按照指定的要求显示出来

查到的数据显示出来是一张二维表
数据显示包含字段名和数据本身
数据查看分两种方式
- 查看全部字段：使用*代替所有字段
- 查看部分字段：明确字段名，使用逗号分隔
查看数据很多时候也是根据条件查询部分数据
查看语法

1	select *\|字段列表 from 表名;

示例

1、查看t_3表中所有数据

1	select * from t_3;

2、查看t_3表中的name和身份证信息

1	select name,sfz from t_3;

3、查看t_3表中id值为1的信息

1	select * from t_3 where id = 1;

小结

1、数据查看是数据库中最常用的操作（99%）

2、数据查看分两种情况

查看全部：select （叫做通配符）
查看部分：select 字段列表（建议）

3、实际使用时通常会带where条件进行数据筛选

3、更新数据

更新数据：即更新某个已有字段的值

更新数据通常是根据条件更新某些数据，而不是全部记录都更新
更新数据语法

1	update 表名 set 字段 = 新值[,字段 = 新值] [where条件筛选];

示例

1、更新所有记录的身份证信息

1	update t_3 set sfz = '440100200010100001';

2、更新某个记录的多个字段数据

1	update t_3 set name = 'Lily',sfz = '440100200010100002' where id = 1;

小结

1、更新数据的针对记录的字段数据进行更新

2、更新通常是限定条件更新（一般不会更新全部）

4、删除数据

删除数据：将数据从已有数据表中清除（针对的是记录record）

删除数据是一种不可逆操作
数据删除通常都是有条件删除
数据删除语法

1	delete from 表名 [where条件];

示例

删除t_3表中id为2的数据

1	delete from t_3 where id = 2;

小结

1、数据删除是不可逆的操作

2、数据删除通常都匹配条件部分删除

5、总结

1、数据操作不论是初级开发者还是高级开发者都频繁接触的操作

2、数据操作中读操作，往往占据了整个操作的99%以上

3、基本的增删改查是作为进阶的必要知识，必须熟练掌握和频繁练习（尽可能使用原码，集成工具后期开发时使用）

3 MySQL数据库字符集

3.1 字符集

1、字符集概念

字符集：charset或者character set，是各种文字和符号在计算机中的总称

字符集根据不同国家的符号不同，有不同的字符集
不同的字符集占用的存储空间不一样，存储的底层也不一样
不同字符集之间可以进行转换
常见字符集
- ASCII：美国信息交换标准码，一般英文符号，一个字节存储
- latin1：拉丁文字符集，一个字节存储，ISO-8859-1的别名，能够向下兼容ASCII
- GB2312：信息交换用汉字编码字符集，是中国1981年的一套国标规范，2个字节存储
- GBK：汉字内码扩展规范（1995年），两个字节表示表示（汉字很多超过5000个）
- Unicode：万国码（统一码），使用统一的编码方式来解决传统的局限，1994年出现
- UTF-8：8-bit Unicode Transformation Format（万国码），针对Unicode的可变长度字符编码，采用1-6个字节编码Unicode字符（目前通用编码规则）。建议使用UTF-8字符集进行数据存储（MySQL8中建议使用utf8mb4）

原理流程图

1、数据存储

graph LR
A(输入符号<br>A)-->B[计算机存储]
B-->C{字符集选择}
C-->|ASCII|D[转码<br>01000001]
C-->|GBK|E[转码<br>0000000001000001]
C-->|ASCII 存储中文|G[转码<br>乱码]
D-->F((存储))
E-->F
G-->F

2、数据读取

graph LR
A(数据读取)-->B{字符集选择}
B-->|ASCII|C[读1个字节<br>01000001]
B-->|GBK|D[读2个字节<br>0000000001000001]
B-->|UTF-8|G[读3个字节<br>错误读取]
G-->H[解析<br>乱码]
C-->E[解析<br>A]
D-->E
E-->F((输出))
H-->F

小结

1、字符集是一套符号的总称

2、不同国家地区的符号有区别，所以有自己的字符集

3、常见的字符集目前是三个

ASCII：美国信息交换标准码
GBK：汉字内码扩展规范（国标），兼容ASCII
UTF-8：8字节万国码，兼容GBK和ASCII

4、目前基本都统一使用UTF-8开发和数据存储

5、字符集是指定字符的存储和读取的规范

指定的字符集存储需要使用对应的字符集读取
错误的字符集存储或者读取都会产生乱码

2、MySQL字符集

MySQL字符集：MySQL内部对于数据实际存储的字符集（服务器端）

MySQL内部对象可以在各个层级设置字符集
MySQL内部对象存在字符集继承：字段 -> 表 -> 数据库 -> DBMS
MySQL内部内嵌支持几乎所有主流字符集
数据存储的最终字符集由字段控制
客户端与服务器进行交互时，需要明确告知服务器客户端自己的字符集（数据格式）

示例

查看MySQL支持的所有字符集

1	show charset;

原理图

1、数据库内部对象字符集原理

graph TB
A(服务器数据管理)-->B[DBMS系统<br>安装配置字符集]
B-->C[数据库<br>设定字符集]
C-->|继承DBMS字符集|B
C-->D[数据表<br>设定字符集]
D-->|继承数据库字符集|C
D-->E[数据字段<br>设定字符集]
E-->|继承数据表字符集|D

1.1、服务端存储的数据最终字符集由字段确定

1.2、字段通常不会设置字符集，继承表的字符集（统一性，真正一般都与数据库一致）

1.3、数据存储的字符集与客户端的字符集没有直接关系，是由表（字段）决定

2、客户端存储数据原理

graph TB
A(客户端录入数据<br>数据自带字符集)-->B[发送服务端]
A-->|提前告知服务端客户端数据字符集<br>如不告知-服务器使用默认设置|C
B-->C
C[服务端接收数据]-->D[执行存储操作]
D-->E[转码为目标字符集]
E-->F((存储))

3、客户端读取数据原理

graph TB
A(客户端录入获取指令)-->B[发送服务端]
A-->|提前告知服务端客户端数据字符集<br>如不告知-服务器使用默认设置|C
B-->C[服务器接收请求]
C-->D[执行获取指令<br>按照数据存储本身字符集获取]
D-->E[转成客户端字符集数据]
E-->F[返回客户端]
F-->G((客户端解析<br>只能按照自己的字符集解析))

小结

1、MySQL服务端数据存储的字符集依赖各个对象设置

DBMS：设置最广，一旦设置所有对象都可以依赖，但是优先级最低
DB：针对数据库内的所有表，优先级高于DBMS，可以继承DBMS（一般在数据库层设置）
Table：针对当前表的设置，优先级高于DB，可以继承DB
Field：针对当前字段设置，优先级高于Table，可以继承Table，优先级最高

2、通常字符集的设置都是围绕数据表（现在都在数据库层），不会到具体字段

3、建议使用UTF8字符集存储数据（MySQL8以后建议使用UTF8MB4）

4、MySQL服务端支持各种字符集，并且能够进行各种字符集转换

5、客户端存储数据到服务端原理

客户端告知服务端客户端的字符集
服务端按照客户端指定的字符集接收数据（如果没有指定，使用默认，可能出现乱码）
服务端按照实际存储表对应的字符集进行转换
服务端存储数据

6、客户端读取服务端数据原理

客户端告知服务端客户端的字符集
服务端按照客户指定的指令从数据库读取原始字符集数据
服务端按照客户端的需求将数据进行字符转换
服务端发送目标数据给客户端
客户端按照自己的字符集进行解析

3、乱码问题解决

乱码：指数据不能按照正确的字符集进行存储或者解析

乱码原因1：数据在存储的时候已经变成乱码
- 客户端字符集与服务端解析字符集不一致
- 读取时想转成其他字符集均会错误
乱码原因2：数据存储时正确，但是读取时解析成错误字符集
- 客户端能解析的字符集与服务器提供的字符集不一致
乱码解决方案：不论存储还是读取，都提前告知服务器当前客户端的字符集

1	set names 客户端字符集;

示例

1、MySQL客户端（CMD打开），客户端字符集是固定的GBK

1	set names gbk;

流程图

graph TB
A(客户端连接认证服务端)-->B[设定字符集<br>告知服务端当前客户端的字符集]
B-->C[其他操作<br>内部字符集转换不用管]
C-->D((结束))

小结

1、乱码的本质原因就是客户端与服务端的字符集不一致导致

客户端存储数据的时候服务端没有正确理解（服务端按照默认的存储，存储的就是乱码）
客户端读取的时候没有正确告知服务端（服务端按照默认的提供）

2、解决乱码问题的方案：保证服务端正确理解客户端的字符集

set names 客户端字符集
在任何数据操作之前（尤其是写数据，包括结构）

4、字符集设置原理

字符集设置原理：服务器端正确保障对客户端的数据识别

MySQL服务端提供了变量来记录客户端的字符集
MySQL对应的存储字符集的变量可以修改
set names 字符集就是对变量的修改，总共有三个
- character_set_client：客户端提供的数据的字符集
- character_set_results：客户端需要服务端提供的数据的字符集
- character_set_connection：连接使用的字符集，内部数据操作

示例

1、查看系统内部存储这些记录字符集的信息

1	show variables like 'character_set%'; #%表示通配符，匹配后续所有不确定的数据

2、修改客户端字符集变量，保证数据正常存进服务端

1	set character_set_client = gbk;

3、修改客户端解析字符集变量，保证数据正常被客户端查看

1	set character_set_results = gbk;

4、使用set names 字符集批量修改，保证客户端被服务端正确理解，同时客户端也能正确解析

1	set names gbk;

小结

1、MySQL字符集控制是在服务端内部通过变量连接（针对每个独立的客户端）

2、set names字符集是一种快捷方式，本质有三个变量被修改

character_set_client：服务端接收客户端数据
character_set_connection：服务端内部连接使用
character_set_results：服务端提供数据给客户端

3、通常我们都是使用set names字符集来进行统一设置，而且是在建立连接之后操作数据之前就设置

5、总结

1、字符集是所有编程语言里都必须面对的首要问题，必须在一开始就选择好字符集（去到企业后先问清楚）

业务针对的符号
业务针对的范围

2、乱码是编程中最基础要解决的问题，一旦数据产生了乱码，通常是不可逆操作

3、解决乱码问题其实本质就是统一字符集问题

客户端字符集：character_set_client
连接层字符集：character_set_connection
结果集字符集：character_set_results
简单统一方式：set names 客户端字符集
存储字符集：不用考虑，因为数据库有强大的字符集转换能力，只要在开始设置好，保证后续数据不会超出字符集即可

3.2 校对集

1、校对集概念

校对集：collate/collation，即数据比较时对应的规则

校对集依赖字符集
校对集的校对方式分为三种
- 大小写不敏感：_ci，case insensitive（不区分大小写）
- 大小写敏感：_cs，case sensitive（区分大小写）
- 二进制比较：_bin，binary（区分大小写）
校对集是在进行数据比较的时候触发

示例

1、_ci，大小写不敏感

1	A 与 a 是相同的，不存在谁大谁小（系统会转换成一种）

2、_cs，大小写敏感

1	A 与 a 有大小关系，所以不同（存储数值）

3、_bin，二进制比较

1
2
3

A的二进制是01000001
a的二进制是01100001
二进制按位比较，所以不同

小结

1、校对集是数据比较的规则

校对集依赖字符集存在
每个字符集有多种校对规则

2、校对规则一共有三种

_ci：大小写不敏感，不区分大小写
_cs：大小写敏感，区分大小写
_bin：二进制比较（区分大小写）

2、校对集设置

校对集设置：在创建数据表的时候创建校对规则

校对规则可以在MySQL四层对象设计
- DBMS：系统配置
- DB：数据库指定（库选项）
- Table：表指定（表选项）
- Field：字段指定（字段选项，一般不用）
校对集从Field到DBMS继承；优先级Field最高
每个校对集都有字符集对应的默认规则
校对集设置语法

1	collate 校对集规则;

示例

1、查看MySQL支持的所有校对集

1	show collation;

2、在数据库层设计校对集（常见）

1	create database db_4 charset utf8mb4 collate utf8mb4_bin;

3、在数据表层设计校对集

create table t_4(
    id int,
    name varchar(10)
)charset utf8mb4 collate utf8mb4_bin;

4、在字段层设计校对集（一般不常用）

create table t_5(
    id int,
    name varchar(10) collate utf8mb4_bin
)charset utf8mb4;

小结

1、MySQL中四层对象都可以设置校对集

DBMS：配置文件
DB：创建数据库时限定（设置）
Table：创建表示限定
Field：创建字段时限定

2、校对集从Field到DBMS实现继承

3、校对集依赖字符集，且每个字符集都有默认的校对集（一般情况不需要设置）

3、校对集应用

校对集应用：触发校对规则的使用

校对集的应用通常是通过数据比较触发：order by 字段
数据表中数据一旦产生，校对集的修改就无效

示例

1、创建校对规则数据表并插入数据

# 创建默认校对规则表（不区分大小写）
create table t_4(
    name varchar(1)
)charset utf8mb4;
insert into t_4 values('B');
insert into t_4 values('A');
insert into t_4 values('b');
insert into t_4 values('a');

# 创建二进制校对规则（区分大小写）
create table t_5(
    name varchar(1)
)charset utf8mb4 collate utf8mb4_bin;
insert into t_5 values('B');
insert into t_5 values('A');
insert into t_5 values('b');
insert into t_5 values('a');

2、触发校对：排序 order by

1 2	select * from t_4 order by name; # 升序 select * from t_5 order by name;

3、数据已经存在的表重新修改校对规则无效

1	alter table t_5 collate utf8mb4_general_ci;

小结

1、校对集的应用不是主动触发，而是通过数据比较自动触发

2、校对集对应的数据一旦产生，那么就不可以修改数据表的校对规则

3、校对集通常使用字符集默认校对集，如果需要进行额外的比较应用（通常是区分大小写），那么需要在建表的时候设定好目标校对规则

4、总结

1、校对集是数据比较的标准

2、校对集的校对规则都是依赖字符集存在的，不外乎三种规则

_ci：不区分大小写
_cs：区分大小写
_bin：二进制比较（区分大小写）

3、校对集的触发是自动的，只要数据在进行比较的时候就会自动触发设定的校对规则

校对集的维护要在数据产生之前
数据产生之后校对集的修改将无效

4、在进行数据表设计之前，要提前了解数据后续可能产生的比较形态，选择好合适的校对规则（一般都默认不区分大小写）

4 MySQL数据库字段详解

4.1 字段类型

1、字段类型作用

字段类型：MySQL中用来规定实际存储的数据格式

字段类型在定义表结构时设定
设定好字段类型后，插入数据时必须与字段类型对应，否则数据错误
MySQL有四大数据类型
- 整数类型
- 小数类型
- 字符串类型
- 时间日期类型

示例

规定类型的字段只能插入相应的数据格式

# 正确数据类型插入
insert into t_6 values(3,'444111222211110101','Lily','Lee');

# 错误数据类型插入
insert into t_6 values('hello','444444222211110102','Lucy','Lu');

小结

1、字段类型的作用就是强制规范录入的数据格式

规范数据的格式
保证数据的有效性

2、MySQL中有四种数据类型规范

整数类型：只能存储整数
小数类型：可以存储有效数值
字符串类型：存储字符串数据
时间日类类型：存储时间日期格式数据

2、整数类型

整数类型：有效的整数数据

MySQL中为了数据空间的有效使用，设定了五种整数类型
- 迷你整型：tinyint，使用1个字节存储整数，最多存储256个整数（-128~127）
- 短整型：smallint，使用2个字节存储整数
- 中整型：mediumint，使用3个字节存储整数
- 标准整型：int，使用4个字节存储整数
- 大整型：bigint，使用8个字节存储
数值型存储在MySQL中分为有符号（有负数）和无符号（纯正数）

步骤

1、确定数据的格式是存储整数

2、预估整数的范围，选择合适的整数类型

3、确定整数是否需要符号（负数区间）

示例

1、设计一个表记录个人信息：年龄、头发数量

# 年龄：没有负数，正常年龄也不超过200岁，迷你整型无符号即可
# 头发数量：没有负数，大概在几百万根，所以标准整型无符号即可
create table t_7(
    age tinyint unsigned, # unsigned修饰整数，表示无符号（从0开始）
    haircount int unsigned
)charset utf8;

2、设计一个表记录4S店的汽车销量信息：库存数量、销量、采购量

# 4S店经常是先卖后进货，所以库存可能为负数，一个店铺的库存数通常不会太多，那么小整型即可
# 销量通常全国一个月也就几万台，所以基本上小整型就够了，但是为了保证后续可能存在的爆发，那么中整型也是绝对够了的，而且不会是负数
# 采购通常是正向采购，所以不会出现负数，而采购数量与销量持平就好

create table t_8(
    stock smallint,
    sales mediumint unsigned,
    purchase mediumint unsigned
)charset utf8;

小结

1、整型是用来存储整数数据的

2、整数数据也需要根据业务大小来选择合适的存储方式

迷你整型：存储数量不超过1个字节表示范围
小整型：存储数量不超过2个字节表示范围
中整型：存储数量不超过3个字节表示范围
整型：存储数量不超过4个字节表示范围
大整型：存储数量不超过8个字节表示范围

3、数值型类型在MySQL中默认是有符号的，即有正负

无符号需要使用unsigned修饰整型，即纯正数

4、一般开发中不会太计较一个或者两个字节（不愿意算），所以tinyint和int居多，其他较少

3、显示宽度

显示宽度：int(L)，整数在数据库中显示的符号（数字+符号）个数

显示宽度一般是类型能表示的最大值对应的数字个数（通过desc查看表字段显示）
显示宽度包含符号（如果允许为负数，-负号会增加一个宽度）
显示宽度可以主动控制：创建字段时加括号确定
显示宽度不会影响类型能表示的最大数值
可以通过zerofill让不够宽度的数值补充到对应宽度：在字段类型后使用zerofill

示例

1、有符号和无符号对应的宽度不一样

create table t_9(
    a tinyint,
    b tinyint unsigned
)charset utf8;

2、可以主动控制显示宽度

1	alter table t_9 add c tinyint(2) unsigned;

3、显示宽度不影响数据的大小

1 2	insert into t_9 values(1,1,1); #小于显示宽度 insert into t_9 values(100,100,100); #大于显示宽度

4、可以通过erofill让小于显示宽度的数值前置补充0到显示宽度

1 2	alter table t_9 add d tinyint(2) zerofill; # 0填充只能针对正数 insert into t_9 values(1,1,1,1);

小结

1、显示宽度是显示整型能表示的最多符号数量

2、显示宽度能主动设置，但是绝对不会改变类型本身能表示的数据大小

3、可以通过zerofill来强制让不够宽度的数据补充前置0来达到显示宽度

zerofill默认要求整型为无符号
zerofill通常用来制作一些规范宽度的数据

4、小数类型（浮点型）

浮点数：float/double，存储不是特别精确的数值数据

浮点数又称之为精度数据，分为两种
- 单精度：float，使用4个字节存储，精度范围为6-7位有效数字
- 双精度：double，使用8个字节存储，精度范围为14-15位有效数字
浮点数超过精度范围会自动进行四舍五入
精度可以指定整数和小数部分
- 默认不指定，整数部分不超过最大值，小数部分保留2位
- 可以指定：float/double(总长度,小数部分长度)
可以使用科学计数法插入数据：AEB，A * 10 ^ B

步骤

1、确定当前设计的字段的数据为不精确型数据（或者小数）

2、确定数据的大小或者精度的要求范围

6-7位有效数字使用float
14-15位有效数字使用double

3、确定精度的分布：整数部分和小数部分

示例

1、记录宇宙中恒星、行星的数量

# 数量属于不确定量级，所以精确的数据是无意义的，只能是个大概（绝大部分时候float就可以）
create table t_10(
    h_star float unsigned,
    x_star float(20,0) unsigned
)charset utf8;

insert into t_10 values(1234567890,1.2E10);

2、记录商品的价格

# 商品名字字符串
# 商品价格一般都允许带小数
create table t_11(
    goods_name varchar(20),
    goods_price float
)charset utf8;
insert into t_11 values('Nokia3310',199.99);
insert into t_11 values('Nokia6100',1999.9999);

小结

1、浮点数是用来记录一些不需要特别精确的数值或者小数数值的

float：单精度，6-7位有效数字
double：双精度，14-15位有效数字

2、浮点数能够表示很大的数值

3、浮点数可以指定整数部分和小数部分的有效数值区间

float/double
- 默认是整数不超过最大范围即可
- 小数部分保留2位有效数字
float/double(有效数位,小数部分有效位)
- 整数部分为有效数位 - 小数部分
- 数值如果超过整数部分就不让插入

4、因为浮点数会自动四舍五入，所以不要使用浮点数来存储对精度要求较高的数值

5、小数类型（定点型）

定点型：decimal，能够保证精度的小数

不固定存储空间存储
每9个数字使用4个字节存储
定点型可以指定整数部分长度和小数部分长度
- 默认不指定，10位有效整数，0位小数
- 可以指定：decimal(有效数位,小数部分数位)
- 有效数位不超过65个
数据规范
- 整数部分超出报错
- 小数部分超出四舍五入

步骤

1、确定小数是否需要保证精度

2、确定有效数位长度

示例

记录个人资产情况：资产和负债

# 资产和负债应该都是精确的，小数部分可以到分
create table t_12(
    money decimal(14,2),
    bet decimal(10,2)
)charset utf8;

insert into t_12 values(1111111111.12,1111111.999);
insert into t_12 values(1111111111.12,99999999.999); # 错误：进位导致正数部分超过指定范围

小结

1、定点数是用来存储精确的小数的

2、定点数可以指定长度

decimal：默认
- 整数部分为10位
- 小数部分为0
decimal(有效位数,小数位数)
- 整数部分为：有效位数 - 小数位数
- 有效数位不超过65个

3、定点数的存储模式不是固定长度，所以数据越大占用的存储空间越长

6、字符串类型（定长型）

定长型：char(L)，指定固定长度的存储空间存储字符串

定长是指定存储长度
定长的长度是字符而不是字节
- L的最大值是255
- 实际存储空间：L字符数 * 字符集对应字节数
定长里存储的数据不能超过指定长度，但是可以小于指定长度
字符串数据使用单引号或者双引号包裹

步骤

1、确定数据类型为字符串（或不能用整数存储的超长数字符号）

2、确定数据长度基本一致（定长占用固定空间）

3、确定具体长度

示例

记录个人信息：身份证信息和手机号码

# 身份证为固定长度18位（数字）
# 手机号码是11位固定长度（数字）
create table t_13(
    id_number char(18),
    phone_number char(11)
)charset utf8;
insert into t_13 values('440111999912120304','13512345678');

小结

1、定长是固定存储空间

实际存储空间：L字符 * 字符集对应字节数

2、定长对应的是字符长度，而不是字节长度

3、字符串数据需要使用引号包裹具体数据

4、定长的访问效率较高，但是空间利用率不高

固定长度的数据使用定长
定长最大数据长度指定不超过255字符

7、字符串类型（变长型）

变长型：varchar(L)，根据实际存储的数据变化存储空间

变长型的存储空间是由实际存储数据决定的
变长型的L也是指字符而不是字节
- L指定的是最大存储的数据长度
- L最大值理论是65535
- 变长需要额外产生1-2个字节，用来记录实际数据的长度
  - 数据长度小于256个，多1个字节
  - 数据长度大于256个，多2个字节
- 实际存储空间：实际字符数 * 字符集对应字节数 + 记录长度
变长数据不能超过定义的最大长度

步骤

1、确定数据类型为字符串

2、确定数据是不规则的数据

3、确定最大长度

示例

记录个人信息：用户名、密码、姓名、身份证

# 用户名不确定长度，最长不超过50个字符
# 密码不确定长度，最潮超过15个字符
# 姓名不确定长度，最长不超过10个字符
# 身份证固定长度，18个字符
create table t_14(
    `username` varchar(50),
    `password` varchar(15),
    `name` varchar(10),
    `id_number` char(18)
)charset utf8;

insert into t_14 values('username','password','name','444111999912121111');

小结

1、变长varchar是根据数据的长度实际计算存储空间

2、变长需要规定数据的最大长度，理论长度为65535个字符

3、变长字符串能够更好的利用存储空间

4、变长字符串需要有额外1-2个字节存储数据长度

不超过256个字符：1个字节
超过256个字符：2个字节

5、变长字符串在读取时需要进行长度计算，所以效率没有定长字符串高

8、字符串类型（文本字符串）

文本字符串：text/blob，专门用来存储较长的文本

文本字符串通常在超过255个字符时使用
文本字符串包含两大类
- text：普通字符
  - tinytext：迷你文本，不超过2 ^ 8 -1个字符
  - text：普通文本，不超过 2 ^ 16 - 1个字符
  - mediumtext：中型文本，不超过 2 ^ 24 - 1 个字符
  - longtext：长文本，不超过 2 ^ 32 - 1 个字符（4G）
- blob：二进制字符（与text类似）
  - tinyblob
  - blob
  - mediumblob
  - longblob
文本字符串会自动根据文本长度选择适合的具体类型
一般在文本超过255个字符时，都会使用text（blob现在极少使用）

步骤

1、确定类型为文本类型

2、确定数据长度可能超过255个字符

3、使用text

示例

记录新闻信息：标题、作者和内容

# 标题一般不会超过50个字符，varchar
# 作者一般不会超过10个字符：varchar
# 内容通常都很长，使用text
create table t_15(
    author varchar(10),
    title varchar(50),
    content text
)charset utf8;

insert into t_15 values('佚名','给联合国的一封信','给联合国的一封信...');

小结

1、文本类型是专门用来存储长文本的

text：普通文本字符
blob：二进制文本字符

2、一般文本长度超过255的（较长）都使用text

3、text/blob根据数据存储长度有很多种，但是一般使用text/blob，因为文本会根据数据长度自适应选择

9、字符串类型（枚举）

枚举：一种映射存储方式，以较小的空间存储较多的数据

枚举是在定义时确定可能出现的可能
枚举在定义后数据只能出现定义时其中的一种
枚举类似一种单选框
枚举使用1-2个字节存储，最多可以设计65535个选项
枚举实际存储是使用数值，映射对应的元素数据，从1开始
枚举语法：enum(元素1,元素2,…元素N)

步骤

1、确定数据是固定的几种数据之一

2、使用枚举穷举相应的元素

3、数据存储只能选择穷举中的元素之一

示例

1、记录人群类型：小朋友、少年、青年、中年、老年，每个人实际只属于一种类别

# 要保证未来数据只能出现在某种可能中，所以要先列出来，可以使用enum
create table t_16(
    type enum('小朋友','少年','青年','中年','老年')
)charset utf8;

insert into t_16 values('少年');
insert into t_16 values('仙人');	# 不存在的数据不能插入

2、enum是建立映射关系，然后实际存储是数字，数值是按照元素顺序从1开始

1
2
3

# 可以使用字段 + 0来判定数据具体的效果（字符串转数值为0）
select type,type + 0 from t_16;
insert into t_16 values(5);

流程原理

1、枚举定义原理

枚举数据	映射值
数据1	1
数据2	2
…	…
数据N	N（小于65535）

2、数据存储（读取反过来）

graph LR
A(指令开始)-->B[插入数据]
B-->C[读取映射关系<br>元素==数值<br>数值==数值]
C-->D[数值存储到字段]
D-->E((结束))

小结

1、枚举是在定义时确定可能出现的元素，而后数据只能出现规定的元素之一的数据类型

2、枚举的存储是一种映射关系，对元素进行顺序编号，实际存储的是编号

3、使用枚举的作用

规范数据模型
优化存储空间

10、字符串类型（集合）

集合：set，一种映射存储方式，以较小的空间存储较多的数据

集合是在定义时确定可能出现的元素进行穷举
集合在定义后数据只能出现定义时其中的元素（可以是多个）
集合类似一种多选框
集合使用1-8个字节存储数据，最多可以设计64个元素
集合实际存储是使用数值（二进制位），映射对应的元素数据，每个元素对应一个比特位
集合语法：set(元素1,元素2,…元素N)

步骤

1、确定数据是固定的几种数据组合

2、使用集合穷举相应的元素

3、数据存储只能选择穷举中的元素组合（多个使用逗号分隔）

示例

1、记录个人的球类爱好，有篮球、足球、羽毛球、网球、乒乓球、排球、台球、冰球

# 爱好可以是多种，并非固定的，但是只能从规定的类型中选择
create table t_17(
    hobby set('足球','篮球','羽毛球','网球','乒乓球','排球','台球','冰球')
)charset utf8;

insert into t_17 values('足球');
insert into t_17 values('冰球,台球,篮球');

2、集合建立的也是映射关系，映射方式是每个元素对应一个字节的比特位，从左边开始第一个对应字节从右边开始的第一位

# 可以通过字段 + 0的方式查看存储的具体数值
select hobby,hobby + 0 from t_17;

# 可以通过插入数值来组合元素，但是需要确定对应的十进制转换成二进制有对应的元素对应
insert into t_17 values(8);

/*
 8:00001000
 
 1:00000001
 
 冰球、台球、篮球
 11000010 => 1 + 2 ^ 1 + 1 * 2 ^ 6 + 1 * 2 ^ 7 = 2 + 64 + 128 = 194
*/

流程原理

1、集合定义原理

集合数据	映射位
数据1	00000001
数据2	00000010
…	…
数据8	10000000

2、数据存储（读取反过来）

graph LR
A(指令开始)-->B[插入数据]
B-->C[读取映射关系<br>元素选中==位值为1<br>元素未选中==位值为0]
C-->D[转化成十进制存储]
D-->E((结束))

小结

1、集合是在定义时确定可能出现的元素，而后数据只能出现规定的元素数据类型

2、集合的存储是一种映射关系，每个元素对应字节中的一个位，实际存储的是编号

数据存在：对应位为1
数据不存在：对应位为0

3、使用集合的作用

规范数据模型
优化存储空间

11、时间日期类型（年）

年：year，MySQL中用来存储年份的类型

MySQL中使用1个字节存储年份
year能够表示的范围是1901-2155年（256年）
- year的特殊值是：0000
year允许用户使用两种方式设计（效果一样）
- year
- year(4)

步骤

1、确定存储的数据是年份

2、确定年份的区间在1901-2155之间

3、使用year类型

示例

1、记录个人的出生年份

create table t_18(
    y1 year,
    y2 year(4)
)charset utf8;
insert into t_18 values(1901,2155);

2、Year类型允许使用2位数来插入，系统会自动匹配对应的年份

69以前：系统加上2000
69以后：系统加上1900

1	insert into t_18 values(69,70);

3、Year类型的特殊值是0000，可以使用00或者0000插入

1	insert into t_18 values(00,0000);

小结

1、year类型是MySQL用来存储年份信息的

2、year使用1个字节，所以只能表示256个年号，表示区间为1901-2155年

3、year数据可以用两种方式插入

直接插入4位年，1901-2155之间都可以
插入2位年，0-99之间，系统会自动以69和70为界限

4、因为year字段表示的范围有限，所以通常会使用字符串来存储（牺牲空间换安全）

12、时间日期类型（时间戳）

时间戳：timestamp，基于格林威治时间的时间记录

MySQL中时间戳表现形式不是秒数，而是年月日时分秒格式
- YYYY-MM-DD HH:II::SS
- YYYYMMDDHHIISS
timestamp使用4个字节存储
timestamp的特点是所对应的记录不论哪个字段被更新，该字段都会更新到当前时间

步骤

1、确定类型需要使用年月日时分秒格式

2、确定当前字段需要记录数据的最近更新时间

3、使用timestamp时间戳

示例

1、记录商品库存的最后更新时间

create table t_19(
    goods_name varchar(10),
    goods_inventory int unsigned,
    change_time timestamp
)charset utf8;

insert into t_19 values('Nokia3110',100,'1971-01-01 00:00:00');
insert into t_19 values('Nokia7100',100,'19710101000000');

2、timestamp会在自己所在的记录任何位置被修改时自动更新时间

1	update t_19 set goods_inventory = 90;

注意：在MySQL8以后，取消了timestamp的默认自动更新，如果需要使用，需要额外使用属性： on update current_timestamp

1
2
3

alter table t_19 add c_time timestamp on update current_timestamp;

update t_19 set goods_inventory = 80;

小结

1、timestamp是用以时间戳的形式来保存时间的

时间戳算法是从格林威治时间开始
MySQL中存储的是年月日时分秒格式

2、timestamp使用4个字节存储数据

表示范围是1971年1月1日0时0分0秒-2155年12月31日23是59分59秒
timestamp可以使用0000-00-00 00:00:00

3、timestamp一般用来记录数据变化的，其他时候通常用整型保存真正的时间戳

timestamp在MySQL8中需要主动使用on update current_timestamp才会自动更新

13、时间日期类型（日期）

日期：date，用来记录年月日信息

使用3个字节存储数据
存储日期的格式为：YYYY-MM-DD
存储的范围是：1001-01-01~9999-12-31

步骤

1、确定存储的数据格式为日期类格式

2、确定数据格式为YYYY-MM-DD

3、使用date类型

示例

记录个人生日

create table t_20(
    name varchar(10),
    birth date
)charset utf8;

insert into t_20 values('Jim','2000-12-12');
insert into t_20 values('Tom','10011212');

小结

1、日期date是用来存储YYYY-MM-DD格式的日期的

2、date用3个字节存储，存储区间是1000 - 9999年，跨度很大

3、date可以在需要存储日期的地方放心使用

14、时间日期类型（日期时间）

日期时间：datetime，用来综合存储日期和时间

使用8个字节存储数据
存储格式为：YYYY-MM-DD HH:II:SS
存储区间为：1000-01-01 00:00:00 到9999-12-31 23:59:59

步骤

1、确定要存储的时间格式包含日期

2、确定存储格式为：YYYY-MM-DD HH:II:SS

3、使用datetime

示例

记录个人具体的出生时间

create table t_21(
    name varchar(10),
    birth datetime
)charset utf8;

insert into t_21 values('Jim','2000-12-12 12:12:12');
insert into t_21 values('Tom','10011212182323');

小结

1、日期时间是综合存储日期和时间两部分的

2、日期时间datetime使用8个字节存储

3、datetime的存储区间为：1000-01-01 00:00:00 到 9999-12-12 23:59:59

4、实际开发中因为编程语言（PHP）的强大，实际存储的时候通常不会使用这种类型

占用较大存储空间
处理不够灵活（固定格式）
使用int unsigned存储时间戳然后利用PHPdate进行格式处理

15、时间日期类型（时间）

时间：time，用来记录时间或者时间段

使用3个字节存储数据
数据范围是 -838:59:59 - 838:59:59
数据插入的格式分为两种
- 时间格式：[H]HH:II:SS（[]表示可以没有）
- 时间段格式：D HH:II:SS（D表示天）

步骤

1、确定要存储的类型是时间格式

2、确定格式类型为time能表示的格式

3、使用time存储

示例

记录用户登录的具体时间

# 具体登录时间可以使用时间戳（包含年月日时分秒信息）
# 也可以时间datetime格式，或者date+time双字段格式（具体后面学习范式时会知道该怎么用）
create table t_22(
    login_time1 int unsigned,
    login_time2 datetime,
    login_date date,
    login_time3 time
)charset utf8;

insert into t_22 values(12345678,'2000-12-12 12:12:12','2000-12-12','12:12:12');
insert into t_22 values(1234567,'2000-12-12 12:12:12','2000-12-12','3 12:12:12');

小结

1、时间格式time主要用来记录时间点或者时间段

2、time类型通常被用来做时间段计算：如多少天后的什么时间点（可以理解为过期检查）

16、总结

1、字段类型是用来规范数据的格式的

2、MySQL中有很多类型用来规范数据格式

整数类型（常用）
- 常用类型：tinyint、int
小数类型（常用）
- 常用类型：decimal、float
字符串类型（常用）
- 常用类型：char、varchar、text
时间日期类型（不常用：通常使用真正时间戳存储数据，然后PHP进行灵活解读）

3、实际开发的时候，一定要仔细了解需求，根据需求判定好具体选用那种数据类型

最原始的维护能够具有最大的通用性（选中类型）
最小的消耗能够解决全部的问题（巧妙利用存储空间）

4.2 属性

1、属性作用

属性：建立在字段类型之后，对字段除类型之外的其他约束

属性是在定义表字段的时候针对每个字段进行属性设定
设定好的属性可以通过查看表字段desc进行查看
数据在进行增删改（写）操作时需要在满足字段的要求同时还要满足属性的要求

示例

查看表属性：desc 表名

desc t_1;

# Field：字段名字
# Type：数据类型
# Null：是否为空（属性）
# Key：索引类型（属性）
# Default：默认值（属性）
# Extra：额外属性

小结

1、属性是用来MySQL用来增加字段规范和约束的

2、数据的写操作必须严格满足字段类型和属性要求

3、用好属性能够提升数据的有效性，方便未来进行数据操作和数据分析（数据真实性和有效性）

2、NULL属性

NULL：数据是否允许为空

默认情况下数据是允许为空的
不为空设计：Not Null
数据为空一般不具备运算和分析价值，所以通常数据都需要设定为Not Null（不区分大小写）

步骤

1、数据类型确定

2、数据是否为空确定

允许为空：不用考虑Null属性（默认为Null）
不允许为空：Not Null

示例

1、用户信息表：用户名、密码、姓名、年龄、注册时间

create table t_23(
    username varchar(50) not null,
    password char(32) not null,
    name varchar(20),
    age tinyint unsigned,
    reg_time int unsigned not null
)charset utf8;

2、如果字段不能为空（Not Null），那么数据就必须满足条件：插入时不能为空的字段就需要主动提供值

insert into t_23 values('username','password','Jim',20,123456789);

# 错误操作：reg_time不能为空，而默认为空，所以系统报错
insert into t_23 (username,password) values('username','password');

小结

1、Null/Not Null属性是用来限定数据是否为Null值的

默认是允许为Null值
不允许为空：Not Null

2、一般有效的数据都必须设定为Not Null来保证数据的有效性

3、Default属性

默认值：default，在设计表字段的时候给定默认数据，在后续字段操作（数据新增）的时候系统没有检测到字段有数据的时候自动使用的值

默认值在字段设计的时候使用（默认值需要满足数据类型规范）
默认值通常设计的是字段容易出现的数据
- 一般字段的默认值默认是Null
默认值触发
- 在系统进行数据插入时自动检测触发
- 主动使用default关键字触发默认值

步骤

1、确定字段的数据类型

2、确定字段可能出现的数据会是某个经常出现的值

3、设定默认值

4、触发默认值

自动触发：数据插入时不给字段赋值
手动触发：数据插入时主动使用default关键字

示例

1、用户开户：银行卡账号、身份证号码、姓名、账户余额

create table t_24(
    account varchar(19) not null,
    id_card char(18) not null,
    name varchar(20) not null,
    money decimal(16,2) default 0.00 not null
)charset utf8;

2、默认值触发

# 默认触发
insert into t_24 (account,id_card,name) values('6226000000000001','44011120001212000x','Lily');

# 主动触发
insert into t_24 values('6226000000000002','440111200012120011','Tom',default);
insert into t_24 values('6226000000000003','440111200012120022','Jim',100);

小结

1、每个字段都有默认值

默认情况下基本为Null
主动设置后，默认值改变

2、默认值是在设置表字段的时候添加

3、默认值触发

自动触发
关键字手动触发

4、默认值通常用于一些不重要的字段，但是会出现常用值（或者初始值一样）

4、主键

主键：primary key，用来保证整张表中对应的字段永远不会出现重复数据

主键在一张表中只能有一个
主键的另外一个特性是能够提升主键字段作为查询条件的效率（索引）
主键不能为空：Not Null（默认）
逻辑主键：数据没有具体业务意义，纯粹是一种数值数据
- 逻辑主键通常是整数：int
- 逻辑主键目的是方便检索和数据安全（不暴露数据真实信息）
复合主键：多个字段共同组成不能重复的数据
- primary key(字段1,字段2,…字段N)
- 联合主键使用不多，一般也不会超过2个字段

步骤

1、确定字段数据具有唯一性

2、确定数据不允许为空

3、确定数据会经常用于数据检索（条件查询）

4、使用主键primary key

5、一般每张表都会使用一个逻辑主键（id）

示例

1、银行账户信息：账户、姓名、余额

# 银行账户具有唯一性，不能重复，也不允许为空
create table t_25(
    account varchar(17) primary key,
    name varchar(20) not null,
    money decimal(16,2) not null default 0.00
)charset utf8;

# 复合主键
create table t_26(
    account varchar(17),
    name varchar(20),
    money decimal(16,2) not null default 0.00,
    primary key(account,name)
)charset utf8;

# 一般使用逻辑主键
create table t_27(
    id int unsigned primary key,
    account varchar(17) not null,
    name varchar(20) not null,
    money decimal(16,2) not null default 0.00
)charset utf8;

2、主键数据不允许重复

insert into t_27 values(1,'6226000000000001','Lily',default);

# 错误：主键1已经存在
insert into t_27 values(1,'6226000000000002','Tom',default);

# 联合主键就是联合字段加起来不重复即可
insert into t_26 values('6226000000000001','Tom',default);
insert into t_26 values('6226000000000002','Tom',default);

小结

1、主键的作用就是控制对应字段的数据具有唯一性（不能重复）

2、一张表只能有一个主键

3、虽然主键可以用来保证数据的唯一性，但是一般都是使用逻辑主键作为主键字段（保证唯一性还有其他方式，如唯一键）

4、通常也不怎么使用复合主键

5、主键管理

主键管理：在创建表并且已经有数据后的维护

删除主键
追加主键
修改主键（先删除后新增）

示例

1、删除主键：主键只有一个，所以删除语法也比较特殊

1	alter table t_26 drop primary key;

2、后期新增主键：如果是针对业务主键需要保证字段数据没有Null数据且没有数据重复（一般主键都会在表创建时维护好）

1	alter table t_26 add primary key(account,name);

小结

1、主键的使用通常是在创建表的时候就会指定好

2、主键的维护实际使用较少，主要涉及的操作是删除和新增

6、自增长属性

自增长：auto_increment，被修饰的字段在新增时，自动增长数据

自增长只能是整数类型，而且对应的字段必须是一个索引（通常逻辑主键）
一张表只能有一个自动增长
自增长数据可以理解为一种默认值，如果主动给值，那么自动增长不会触发
自增长由两个变量控制
- 初始值：auto_increment_offset，默认是1
- 步长：auto_increment_increment，默认值也是1
- 查看自增长控制：show variables like 'auto_increment%';

步骤

1、确定数据类型为整型

2、确定数据需要有规则的变化

从1开始
每次增长1
可以调整，但是通常有固定规则（一般不调整）

3、必须是一个索引字段（逻辑主键）

4、使用auto_increment

示例

1、记录学生信息：学号和姓名

# 学生信息：学号自动增长
create table t_28(
    id int primary key auto_increment,
    stu_no int(8) zerofill not null,
    stu_name varchar(20) not null
)charset utf8;

2、触发自增长

# 使用自增长（可以使用NULL或者default来触发）
insert into t_28 values(null,1,'Jim');
insert into t_28 values(default,2,'Tom');

# 主动控制：自增长的值会从当前最大的值开始自动增长
insert into t_28 values(10,3,'Lily');
insert into t_28 values(null,4,'Lucy');

小结

1、自增长auto_increment一般是配合逻辑主键实现自动增长

整型字段
存在索引（主键）

2、自增长的触发是通过不给值（默认值）实现自动计算

3、自增长是根据当前表中自增长列最大值运算

4、一张表中只能有一个自增长

7、自增长管理

自增长管理：在某些特殊使用下，需要自增长按照需求实现

修改表中自增长的值：让下次自增长按照指定值开始
修改自增长控制：调整自增长的变化

示例

1、修改表中自增长的值，跳过一些值，直接从下次开始按照新的目标值出现

1	alter table t_28 auto_increment = 50;

注意：奇数会保留原值，偶数会自动加1（可能出现的情况）

2、修改自增长控制：步长和起始值（修改针对的是整个数据库，而非单张表）

1 2	set auto_increment_increment = 2; # 当前用户当前连接有效（局部） set @@auto_increment_increment = 2; # 所有用户一直有效（全局）

小结

1、自增长通常不会修改，如果有规则要求必须修改，通常也会在数据库运行前修改好

2、如果碰到要修改操作的，通常会选择全局修改而不是局部修改

8、唯一键

唯一键：unique key，用来维护数据的唯一性

一个表中可以有多个唯一键
唯一键与主键的区别在于唯一键允许数据为Null（而且Null的数量不限）
唯一键与主键一样，可以提升字段数据当做条件查询的效率（索引）
复合唯一键：多个字段共同组成
- unique key(字段1,字段2,…字段N)
- 一般不会出现，最多2个字段组成

步骤

1、确定数据列具有唯一特性

2、确定数据列不用作为主键

3、确定数据会经常用于检索条件

4、唯一键字段是否允许数据为空

为空：普通唯一键
不为空：not null（唯一键与主键效果一样）

5、使用唯一键

示例

1、用户表：用户名唯一，而且经常作为查询条件

create table t_29(
    id int primary key auto_increment,
    username varchar(50) unique comment '唯一键，可以直接在字段之后增加，使用unique关键字',
    password char(32) not null
)charset utf8;

insert into t_29 values(null,'username','password');
insert into t_29 values(null,Null,'password');
insert into t_29 values(null,Null,'password');

# 错误
insert into t_29 values(null,'username','password1');

2、学生成绩表：一个学生只能有一个学科成绩，但是可以有多个学科

# 学号和学科编号共同组成唯一
create table t_30(
    id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2),
    unique key(stu_name,course)
)charset utf8;

insert into t_30 values(null,'Jim','Math',50);
insert into t_30 values(null,'Jim','English',80);

小结

1、唯一键的目标是保证对应字段数据的唯一性

唯一键不限定数据是否为Null（Null不参与唯一判定）
复合唯一键：允许多个字段共同组成唯一性

2、唯一键能够弥补主键只有一个的特性（不限定数据量）

3、唯一键使用的位置应该要确保该字段数据会用作数据检索条件

9、唯一键管理

唯一键管理：在表创建后对唯一键的管理

删除唯一键：一张表中不止一个唯一键，所以删除方式是相对麻烦：alter table 表名 drop index 唯一键名字；
新增唯一键：alter table 表名 add unique key(字段列表)；

示例

1、删除表中已有的唯一键

1	alter table t_30 drop index `stu_name`;

2、追加唯一键

1	alter table t_30 add unique key `stu_course` (stu_name,course);

追加唯一键要保证字段里的数据具有唯一性

小结

1、唯一键一般也会在前期架构（创建表）时就会设置好

2、如果在后期数据庞大后进行数据库优化，可能会涉及到后期维护唯一键

3、唯一键的删除不能像主键那样删除，原因就是唯一键在表中不是唯一存在的，必须指定唯一键名字

10、comment属性

描述：comment，是用文字描述字段的作用的

comment代表的内容是对字段的描述
- 方便以后自己了解字段的作用
- 方便团队了解字段的作用
描述如果涉及到字符集（中文）一定要在创建表之前设置好客户端字符集（否则会出现描述乱码）

步骤

1、字段命名不是特别简单（见名知意）

2、使用comment增加简易描述

示例

学生成绩表

# 学生成绩表中通常是存储学生学号
# 学科通常也是学科代码
create table t_31(
    id int primary key auto_increment,
    stu_no varchar(10) not null comment '学号',
    course_no varchar(10) not null comment '课程号',
    score decimal(5,2) comment '考试成绩',
    unique key `stu_course` (stu_no,course_no) comment '学号和课程号组成唯一键'
)charset utf8;

如果要查看描述信息，需要使用show create table查看

小结

1、养成描述的良好习惯，为自己也为其他同事提供方便

2、并非所有字段都需要描述，但是对于自定义名字或者关联关系，都应该使用描述说清楚

11、总结

1、字段属性是基于字段类型对数据控制后，再进行其他控制（辅助加强字段类型对数据的控制）

2、巧妙的利用好字段属性，能够帮助我们更好的保证数据的有效性、安全性

3、字段属性与字段类型通常是搭配使用，常见的属性有

主键（逻辑）+自增长
唯一键+Not Null（Null数据不参与运算，没有价值）
默认值以及default关键字的使用

4.3 数据库记录长度

数据库记录长度：MySQL中规定一条记录所占用的存储长度最长不超过65535个字节

记录长度为表中所有字段预计占用的长度之和
所有字段只有允许Null存在，系统就会预留一个字节存储Null（多个Null也只要一个就好）
因为MySQL记录长度的存在，varchar永远达不到理论长度
- GBK存储：65535（字符） * 2 + 2 = 131072（字节）
- UTF8存储：65535（字符） * 3 + 2 = 196607（字节）
一般数据长度超过255个字符都会使用text/blob进行存储（数据存储不占用记录长度）

步骤

1、字符串字段如果超过长度255字符，使用text/blob替代

2、所有字段是否有允许为Null的，如果有要占用1个字节

3、计算字段所占用的预计总长度不要超过65535个字节

示例

1、GBK表能存储的最大varchar字符串长度

create table t_32(
    content varchar(65535)
)charset gbk;	# 错误

create table t_32(
    content varchar(32767)
)charset gbk;	# 错误

create table t_32(
    content varchar(32766)
)charset gbk;

2、UTF8表能存储的最大varchar字符串长度

create table t_33(
    content varchar(65535)
)charset utf8;	# 错误

create table t_33(
    content varchar(21844)
)charset utf8;

3、Null也要占用一个字节

create table t_34(
    id tinyint,
    content varchar(21844)
)charset utf8;	# 错误

create table t_34(
    id tinyint not null,
    content varchar(21844) not null
)charset utf8;

小结

1、MySQL的记录长度是从设定表的时候就会检查所有字段加起来的预占用长度是否超过65535个字节

超过：创建失败
不超过：创建成功

2、创建表字段的时候要使用text/blob来避免长字符串出现，超过MySQL记录长度

3、Null是个细节，一条记录只要允许出现Null就会占用记录长度里的一个字节

5 MySQL数据库设计规范

5.1 关系型数据库设计范式

范式：Normal Format，符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度

范式是离散数学里的概念
范式目标是在满足组织和存储的前提下使数据结构冗余最小化
范式级别越高，表的级别就越标准
目前数据库应用到的范式有以下几层
- 第一范式：1NF
- 第二范式：2NF
- 第三范式：3NF
- 逆规范化

示例

1、一张员工表

工号	姓名	部门	入职时间
0001	杨戬	武装部	0001-01-01
0002	李白	书院部	1500-12-12

2、每个员工都是与部门挂钩的，但是部门不可能很多，所以上述表中会有很多数据重复，此时应该将部门单独维护出来，减少数据冗余

部门编号	部门名称
1	武装部
2	书院部

工号	姓名	部门编号	入职时间
0001	杨戬	1	0001-01-01
0002	李白	2	1500-12-12

N个1和N个武装部占用的磁盘空间肯定是不一样的

小结

1、范式是一种数学理论，在关系型数据库上用来减少数据冗余

2、满足的范式越多，越符合高标准表设计

3、范式一共有6层，但是数据库的设计通常只要求满足3层即可

1、第一范式1NF

第一范式：1NF，数据字段设计时必须满足原子性

1NF要求字段数据是不需要拆分就可以直接应用
如果数据使用的时候需要进行拆分那么就违背1NF

步骤

1、设计的字段是否在使用的时候还需要再拆分？

2、将数据拆分到最小单位（使用），然后设计成字段

3、满足1NF

示例

1、设计一张学生选修课成绩表

学生	性别	课程	教室	成绩	学习时间
张三	男	PHP	101	100	2月1日,2月28日
李四	女	Java	102	90	3月1日,3月31日
张三	男	Java	102	95	3月1日,3月31日

当前表的学习时间在使用的时候肯定是基于开始时间和结束时间的，而这种设计就会存在使用时的数据拆分，不满足原子性也就是1NF

2、满足1NF的设计：字段颗粒度应用层最小（不需要拆分）

学生	性别	课程	教室	成绩	开始时间	结束时间
张三	男	PHP	101	100	2月1日	2月28日
李四	女	Java	102	90	3月1日	3月31日
张三	男	Java	102	95	3月1日	3月31日

小结

1、1NF就是要字段数据颗粒度最小，保证数据取出来使用的时候不用再拆分

2、1NF是满足数据表设计的最基础规范

2、第二范式2NF

第二范式：2NF，字段设计不能存在部分依赖

部分依赖：首先表存在复合主键，其次有的字段不是依赖整个主键，而只是依赖主键中的一部分
部分依赖解决：让所有非主属性都依赖一个候选关键字
- 最简单方式：取消复合主键（一般选用逻辑主键替代，但是本质依然是复合主键做主），所有非主属性都依赖主属性（逻辑主键）
- 正确方式：将部分依赖关系独立成表

步骤

1、表中是否存在复合主键？

2、其他字段是否存在依赖主键中的一部分？

3、如果存在部分依赖，将部分依赖的关系独立拆分成表

4、满足2NF

示例

1、学生成绩表中学生和课程应该是决定性关系，因此属于主属性（主键）

学生（P）	性别	课程（P）	教室	成绩	开始时间	结束时间
张三	男	PHP	101	100	2月1日	2月28日
李四	女	Java	102	90	3月1日	3月31日
张三	男	Java	102	95	3月1日	3月31日

成绩是由学生和课程决定的，是完全依赖主属性
性别只依赖学生（部分依赖）
教室、开始时间和结束时间依赖课程（部分依赖）

2、解决方案：将学生信息维护到一张表，课程信息维护到一张表，成绩表取两个表的主属性即可

学生表

Stu_id（P）	姓名	性别
1	张三	男
2	李四	女

Stu_id是姓名的代指属性（逻辑主键，本质主键是姓名）
性别只依赖主属性

课程表

Class_id（P）	课程	教室	开始时间	结束时间
1	PHP	101	2月1日	2月28日
2	Java	102	3月1日	3月31日

Class_id是课程的代指属性（逻辑主键）
教室、开始时间和结束时间都依赖课程（主属性）

成绩表

Stu_id（P）	Class_id（P）	成绩
1	1	100
2	2	90
1	2	95

Stu_id和Class_id共同组成主属性（复合主键）
成绩依赖Stu_id和Class_id本身，不存在部分依赖

小结

1、2NF是在满足1NF的前提之上的

2、2NF的目标是取消表中存在的部分依赖

主属性（主键）为复合主键才有可能存在
解决方案就是让部分依赖存在的关系独立成表（学生表和课程表），不存在部分依赖关系的独立成表（学生成绩表）

3、2NF可以实现很大程度的数据冗余减少

3、第三范式3NF

第三范式：3NF，字段设计不能存在传递依赖

传递依赖：字段某个非主属性不直接依赖主属性，而是通过依赖某个其他非主属性而传递到主属性之上
传递依赖解决：让依赖非主属性的字段与依赖字段独立成表

步骤

1、确定表中的所有字段都是依赖主属性的

2、如果存在不直接依赖主属性，而是通过依赖其他属性产生依赖的，形成独立的表

3、满足3NF

示例

1、学生表：包括所在系信息

学号（P）	姓名	专业编号	专业名字
1	张三	0001001	软件工程
2	李四	0001002	土木工程

姓名和专业编号都依赖于学号（为学号提供信息支持）
专业名字依赖专业编号（为编号提供信息支持）
专业名字间接依赖学号：传递依赖
随着学生增加，专业名字会出现大量数据冗余

2、解决方案：将存储传递依赖部分的字段（非主属性）独立成表，然后在需要使用相关信息的时候，引入即可

专业表

专业编号（P）	专业名字
0001001	软件工程
0001002	土木工程

即使有更多的信息为专业提供支持也不存在传递关系

学生表

学号（P）	姓名	专业编号
1	张三	0001001
2	李四	0001002

姓名和专业编号都依赖学号（为学号提供信息支持）
没有其他字段是通过非主属性（专业编号）来依赖主属性的：没有传递依赖
学生再多，专业名字信息只需要维护一次，减少数据冗余

小结

1、3NF目的是为了解决非主属性对主属性的传递依赖

2、让有关联关系的表独立成表就可以消除传递依赖，满足3NF

4、逆规范化

逆规范化：为了提升数据查询的效率而刻意违背范式的规则

逆规范化的目标是为了提升数据访问效率
所谓逆规范化就是减少表之间的关联查询（效率降低），刻意增加数据冗余

步骤

1、表中部分数据来源于其他表（通常只需要其他表的某个简单数据）

2、当前表会被高频次查询

3、数据表数据量很大

4、考虑使用逆规范化

示例

1、学生成绩表需要经常查询，而且数据量很大，但是：

成绩表中只有学号，显示的时候需要学生姓名（去学生表中连表查询）
成表表中只有课程号，显示的时候需要显示课程名（去课程表中连表查询）
逆规范化：将学生姓名和课程名在表中冗余维护（不满足2NF）

学号（P）	学生姓名	课程号（P）	课程名字	成绩
1	张三	1	PHP	100
1	张三	2	Java	90

学生姓名部分依赖学号（主属性）：不满足2NF
学生姓名和课程名字会有大量数据冗余存在（不满足2NF导致）

小结

1、逆规范化只有在数据量大，查询效率低下的时候为了提升查询效率而牺牲磁盘空间的一种做法

2、逆规范化后数据表的设计必然是不完全符合范式要求的（2NF/3NF）

5、总结

1、范式是关系型数据库设计借鉴用来减少数据冗余的

1NF：数据字段的原子性，增强数据的可用性
2NF：取消字段的部分依赖，建立数据的关联性，减少数据冗余
3NF：取消字段的传递依赖，将相关实体独立划分，减少数据冗余
逆规范化：为了提升数据访问效率，刻意增加数据冗余（磁盘空间利用率与访问效率的矛盾）

2、在进行数据表设计的时候，需要严格遵循范式规范

基于规范设计数据表
在设计表中深入认知范式规范
熟练的基于业务设计数据表

5.2 表关系

表关系：一个表代表一个实体，实体之间都有关联关系的

根据范式的要求来设计表关系，减少数据冗余
根据实际需求来设计表关系，提升访问效率

示例

设计一个简单新闻管理系统的数据库

新闻信息表：id、标题、内容、发布时间、作者id（作者表主属性）、分类id（分类表主属性）、阅读量、推荐数
作者表：id、作者名字、作者来源id（来源表）
来源表：id、来源名字、来源描述
分类表：id、分类名字、分类级别（父分类id）
评论表：id、评论人id（评论表）、评论时间、评论内容（不回复）

小结

1、表关系是体现数据实际联系的方式

2、表关系的设计好坏直接关联数据维护的准确性、有效性

3、良好的数据库设计有助于后期程序开发

1、一对一关系

一对一关系：一张表中的一条记录与另外一张表中有且仅有一条记录有关系

一对一关系通常是用来将一张原本就是一体的表拆分成两张表
- 频繁使用部分：常用字段
- 不常使用部分：生僻字段
- 使用相同的主键对应
一对一关系设计较多使用在优化方面

步骤

1、一张表的数据字段较多且数据量较大

2、表中有部分字段使用频次较高，而另一部分较少使用

3、将常用字段和不常用字段拆分成两张表，使用同样的主键对应

示例

1、学生信息表

学号（P）	姓名	性别	年龄	身高	体重	籍贯	政治面貌
1	张飞	男	20	178	160	蜀	农民
2	武则天	女	21	168	110	唐	党员

以上数据表信息字段较多
姓名、性别、年龄属于常用字段，频繁查询

2、一对一关系设计

将常用字段取出，与学号组合成一张常用表
将不常用字段取出，与学号组合成一张不常用表
表与表数据对应关系：基于学号（唯一）是一对一关系

常用表

学号（P）	姓名	性别	年龄
1	张飞	男	20
2	武则天	女	21

不常用表

学号（P）	身高	体重	籍贯	政治面貌
1	178	160	蜀	农民
2	168	110	唐	党员

小结

1、一对一关系的核心是两张表中记录匹配有且仅有一条匹配

2、一对一关系常用来进行分表，实现优化操作

3、因为一对一关系表通常有相同信息作为匹配条件，所以查询方式也比较方便

连表操作：利用共有信息进行匹配，一并查出一条完整信息
多次查询：利用共有信息进行多表查询，利用程序组合成一条完整信息

2、一对多关系

一对多关系：也叫多对一关系，一张表中的一条记录与另外一张表的多条记录对应，反过来另外一张表的多条记录只能对应当前表的一条记录

一对多关系是实体中非常常见的一种关系，实体设计时也应用非常多
一对多关系的核心解决方案是如何让记录能够正确匹配到另外表中的数据
- 一表设计：一表记录在另外一张表中有多条记录，所以无法记录多个字段（违背1NF）
- 多表设计：多表记录在另外一张表中只有一条记录，可以设置字段记录对应的主属性（通常主键）

步骤

1、确定实体间的关系为一对多（多对一）关系

2、在多表中增加一个字段记录一表中对应的主属性

示例

1、老师与学科间的关系：一个老师只能教一个学科，但是一个学科有多个老师教授，学科与老师形成的关系就是一对多（反过来老师与学科的关系就是多对一关系）

老师表（多表）

老师ID（P）	姓名	年龄	性别
1	张老师	35	男
2	李老师	34	女
3	王老师	30	男

学科表（一表）

学科ID（P）	名字	课时长度
1	PHP	600
2	Java	800

以上两个实体没有体现彼此之间的关联关系
实际上讲师与学科肯定是有关联的

2、在多表（讲师）中增加字段维护一表（学科）的关系型，形成多对一关系

老师ID（P）	姓名	年龄	性别	学科ID
1	张三	35	男	1
2	李四	34	女	1
3	王五	30	男	2

基于新的讲师表与学科表产生了关联关系（多对一）
基于讲师表可以知道讲师所属学科
基于学科ID可以统计出不同学科的讲师数量

小结

1、一对多关系设计是将实体的关系在表结构层进行强制关联（没有关系程序层也可以控制，但是会非常麻烦）

便于连表操作
便于数据分析统计（数据库层）

2、一对多关系的核心在于分析出表与表之间的关系

3、多对多关系

多对多关系：一张表中的一条记录对应另外一个表中多条记录，反过来一样

多对多关系在实体中是最常见的关系
多对多关系是无法在自身表中维护对应表关系的（违背1NF），需要通过第三方表来实现将多对多关系变成多个多对一关系
- 设计一个中间表：记录两张表之间的对应关系（主属性）
- 中间表与其他表都是多对一的关系

步骤

1、确定实体间的关系为多对多关系

2、设计中间表，记录两张表的对应关系

示例

1、老师与学生之间的关系：一个老师会教授多个学生，一个学生也会听多个老师的课，所以实体关系是多对多关系

老师表

老师ID（P）	姓名	年龄	性别
1	张老师	35	男
2	李老师	34	女
3	王老师	30	男

学生表

学生ID（P）	姓名	年龄	性别
1	小明	15	男
2	小红	14	女
3	小萌	14	女

以上实体没有从结构上体现表之间的关系

2、设计一个中间表：老师与学生关系表，将老师与学生的对应关系对应上（多对一）

中间表

ID（P）	学生ID	老师ID
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2
6	2	3
7	3	1
8	3	3

中间表与老师表的对应关系是多对一：通过老师ID可以找到每一个上过课的老师
中间表与学生表的对应关系是多对一：通过学生ID可以找到每一个听过课的学生
老师找学生：老师表—》中间表（找出老师对应的学生ID）—》学生表（找出学生ID对应的学生信息）
学生找老师：学生表—》中间表（找出学生对应的老师ID）—》老师表（找出老师ID对应的老师信息）

小结

1、多对多关系在表上不能直接维护（字段设计违背1NF）

2、多对多关系是将关系抽离形成中间关系表，形成多个多对一的关系

3、多对多关系是否建立主要看业务上是否存在数据要求，如果不存在数据需求，那么就没必要刻意设计

4、总结

1、表关系的设计是要遵循范式规范作为前提

2、表关系的设计是根据实体关系以及业务需求进行设计

一对一关系：主要在于优化访问效率、传输效率
一对多关系：在于如何让实体间的联系在结构中体现（后期可以使用外键进行相关约束保证数据的有效性）
多对多关系：与一对多关系一样，清晰明了的体现实体间的结构联系

3、在设计数据库的时候，要严格使用表关系来进行实体关联设计

基于表关系来实现实体间的关联控制
在设计和应用表的时候提炼对表关系的认知
能够熟练的基于业务控制数据库的关系

6 MySQL高级SQL操作

高级SQL操作：利用SQL指令的变化实现一些复杂业务的数据操作

示例

1、统计不同班级的人数信息

按照现有SQL操作，即便数据表已经存在数据，但是我们也无法通过SQL实现，只能取出来后通过其他代码对数据进行加工实现
通过高级SQL学习，我们就可以通过一条SQL指令完成操作

1	select count(*),班级ID from 学生表 group by 班级ID;

小结

1、高级SQL操作能够帮助我们解决复杂的需求问题

2、在实际开发过程中，高级SQL操作占据的比例相当高

6.1 数据新增

1、批量插入

批量插入：是一种优化数据逐条插入的方式

批量插入数据的语法与简单数据插入的语法差不多

批量插入分为两种

全字段批量插入

1	insert into 表名 values(值列表1),(值列表2),...(值列表N);

部分字段批量插入（注意字段默认值）

1	insert into 表名 (字段列表) values (值列表1),(值列表2),...(值列表N);

步骤

1、用户的操作涉及到多记录同时插入（通常数据批量导入）

2、组装成批量插入SQL指令

字段为全字段（逻辑主键不存在没问题）：全字段批量插入SQL
部分字段：组织字段列表并检查其他字段是否允许默认值

3、执行批量插入

示例

1、批量插入学生成绩（t_30全字段）

1 2	insert into t_30 values(null,'Tom','Computer',90), (null,'Lily','Computer',92);

2、批量插入学生考试信息（t_30不包含成绩）

1	insert into t_30 (stu_name,course) values('Tony','English'),('Ray','Math');

小结

1、批量插入可以针对性解决数据的批量导入之类的业务

2、批量插入可以一次性解决多条数据插入，能够有效降低客户端占用问题，提升数据操作效率

MySQL8以后默认有事务安全，即批量要么都成功要么都失败，不会出现部分问题

2、蠕虫复制

蠕虫复制：从已有表中复制数据直接插入到另外一张表（同一张表）

蠕虫复制的目标是快速增加表中的数据
- 实现表中数据复制（用于数据备份或者迁移）
- 实现数据的指数级递增（多用于测试）
蠕虫复制语法

1	insert into 表名 [(字段列表)] select 字段列表 from 表名;

注意事项
- 字段列表必须对应上
- 字段类型必须匹配上
- 数据冲突需要事先考虑

步骤

1、确定一张需要插入数据的表（一般与要蠕虫复制数据的表结构一致）

2、确定数据的来源表

字段数量匹配
字段类型匹配
所选字段不存在冲突（数据可能是重复数据）

3、使用蠕虫复制

示例

1、创建一张新表，将t_30表中的数据迁移到新表中

create table t_35(
    id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2)
)charset utf8;

insert into t_35 select * from t_30;

2、快速让t_35表中的数据达到超过100条（重复执行）

1	insert into t_35 (stu_name,course,score) select stu_name,course,score from t_35;

小结

1、蠕虫复制的目标就是批量利用已有数据来丰富表数据

数据迁移：一次性复制表数据
数据增长：重复执行自我复制增长（数据测试使用）

3、主键冲突

主键冲突：在数据进行插入时包含主键指定，而主键在数据表已经存在

主键冲突的业务通常是发生在业务主键上（业务主键本身有业务意义）

主键冲突的解决方案

忽略冲突：保留原始记录

1	insert ignore into 表名 [(字段列表)] values(值列表);

冲突更新：冲突后部分字段变成更新

1
2
3

insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值[,字段=新值...];
# 1、尝试新增
# 2、更新

冲突替换：先删除原有记录，后新增记录

1	replace into 表名 [(字段列表)] values(值列表); # 效率没有insert高（需要检查是否冲突）

步骤

1、确定当前业务可能存在主键冲突

2、选择主键冲突的解决方案

示例

1、用户名作为主键的用户注册（冲突不能覆盖）：username，password，regtime

create table t_36(
    username varchar(50) primary key,
    password char(32) not null,
    regtime int unsigned not null
)charset utf8;

insert into t_36 values('username','password',12345678);
# 冲突忽略
insert ignore into t_36 values('username','12345678',12345678);

2、用户名作为主键的记录用户使用信息（不存在新增、存在则更新时间）：username，logintime

create table t_37(
    username varchar(50) primary key,
    logintime int unsigned
)charset utf8;

insert into t_37 values('username',12345678);	# 当前时间戳

# 冲突更新（替换部分字段数据）
insert into t_37 values('username',12345678) on duplicate key update logintime = unix_timestamp();	# 当前时间戳

如果主键不冲突：新增
如果主键冲突：更新指定字段
上述方式适用于字段较多，但是可能冲突时数据变化的字段较少

3、用户名作为主键的记录用户使用信息（不存在新增、存在则更新全部）：username，logintime、clientinfo

create table t_38(
    username varchar(50) primary key,
    logintime int unsigned,
    clientinfo varchar(255) not null
)charset utf8;

insert into t_38 values('username',unix_timestamp(),'{PC:chrome}');

# 替换插入
replace into t_38 values('username',unix_timestamp(),'{phone:uc}');

replace遇到主键重复就会先删除、后新增
如果有较多字段需要更新：建议使用替换

小结

1、主键冲突的解决方案有三种，但是需要根据具体的业务来选择合适的方式

忽略新数据：insert ignore
更新部分数据：insert ... on duplicate key update
全部替换：replace into

2、从效率上来讲，insert into不考虑冲突的效率最高，三种解决冲突的方式都会有效率下降（需要检索），其中三种本身的效率依次是：忽略新数据 > 更新部分数据 > 替换全部

6.2 数据查询

小结

1、数量掌握高级数据查询后，以前需要多次操作的业务基本都可以通过一些复杂SQL的编写实现一次性进行数据筛选提炼，从而达到一次性解决问题的要求

1、查询选项

查询选项：用于对查询结果进行简单数据筛选

查询选项是在select关键字之后，有两个互斥值
- all：默认，表示保留所有记录
- distinct：去重，重复的记录（所有字段都重复）

步骤

1、查询的结果需要去除重复记录

2、使用distinct查询选项去重（默认就是all保留全部）

示例

查看商品表中所有品类的商品信息：重复的商品只保留一次（名字、价格、属性都一致）

create table t_39(
    id int primary key auto_increment,
    goods_name varchar(50) not null,
    goods_price decimal(10,2) default 0.00,
    goods_color varchar(20),
    goods_weight int unsigned comment '重量，单位克'
)charset utf8;

insert into t_39 values(null,'mate10',5499.00,'blue',320),
(null,'mate10',5499.00,'gray',320),
(null,'nokia3301',1299,'black',420);

# 考虑所有字段的去重（不含逻辑主键）
select distinct goods_name,goods_price,goods_color,goods_weight from t_39;
select goods_name,goods_price,goods_color,goods_weight from t_39; # 保留所有

# 不考虑颜色去重
select distinct goods_name,goods_price,goods_weight from t_39;
select all goods_name,goods_price,goods_weight from t_39;

小结

1、select选项主要是用来进行数据全保留或者去重选择的

all：默认，保留全部（关键字可以没有）
distinct：手动选择，去重（针对所选字段构成的记录，而不是某个字段）

2、distinct选项一般用于制作数据报表时使用

2、字段选择&别名

字段选择：根据实际需求选择的要获取数据的字段信息

根据实际需求，明确所需要的字段名字，使用英文逗号,分隔
获取所有字段，使用星号*通配所有字段
字段数据可以不一定是来自数据源（select只要有结果即可）
- 数据常量：select 1
- 函数或者变量：select unix_timestamp(),@@version （@@是系统变量的前缀，后面跟变量名）

字段别名：给字段取的临时名字

字段别名使用as语法实现
- 字段名 as 别名
- 字段名别名
字段别名的目的通常为了保护数据
- 字段冲突：多张表同时操作有同名字段（系统默认覆盖），想保留全部
- 数据安全：对外提供数据不使用真实字段名字

步骤

1、明确需要查询的字段信息

全部：*
部分：确定字段列表

2、确定存在数据冲突或者需要数据保护（通常可以理解为对外提供给别的系统访问）

使用别名

示例

1、查询商品信息

# 全部查询
select * from t_39;

# 需求为商品名字和价格
select goods_name,goods_price from t_39;

# 别名使用
select goods_name as gn,goods_price gp from t_39;

2、不需要数据源的数据获取：select的表达式本身能算出结果）

1 2	# 获取当前时间戳和版本号 select unix_timestamp() as now,@@version as version,@@version;

小结

1、字段的选择只要在保证数据需求能实现的情况下，尽可能少使用*代替（MySQL优化）

减少服务器的数据读取压力
减少网络传输压力
让客户端能够精确解析数据（不用大海捞针）

2、字段别名的灵活使用一方面可以保证原始数据的安全，也可以为数据使用者提供便利

同名字段覆盖问题（连表操作学习时会遇到）
原始字段保护
数据字段的简化

3、select是SQL中用于取出数据的一种指令，这种指令未必一定需要从数据表取出数据，只要是本身能够有数据的表达式，都可以使用select获取

3、数据源

数据源：from关键字之后，数据的来源。只要最终结果是一个二维表，都可以当做数据源

单表数据源：数据源就是一张表 from 表名
多表数据源：数据来源是多张表（逗号分隔） from 表名1,表名2,...表名N
子查询数据源：数据来源是一个查询结果 from (select 字段列表 from 表名) as 别名
- 数据源要求必须是一个表
- 如果是查询结果必须给起一个表别名
数据表也可以指定别名
- 表名 as 别名
- 表名别名

示例

1、单表数据源：最简单的数据源，直接从一个数据表获取

1	select * from t_27;

2、多表数据源：利用一张表的一条数据匹配另外一张表的所有记录，记录结果为：记录数 = 表1记录数 * 表2记录数；字段数 = 表1字段数 + 表2字段数（笛卡尔积）

1	select * from t_27,t_30;

3、子查询数据源：数据来源是一个select对应的查询结果

查询语句需要使用括号包裹
查询结果需要指定别名

1 2	select * from (select * from t_27,t_30) t; # 数据有冲突查不出来 select * from (select * from t_27) as t;

4、如果有时候名字较长或者使用不方便，可以利用表别名

1
2
3

select * from t_30 as t;

select t1.*,t2.stu_name from t_27 as t1,t_30 t2;

一般情况下别名设置是为了后续条件中可以直接使用别名
如果多表操作下，可以使用表别名来明确提取表字段

小结

1、数据源是为查询、检索提供数据支持的，使用时需要明确指定

2、通常情况下数据源不会使用简单的多表数据源（笛卡尔积）

3、数据表的别名在负责SQL查询操作时非常有用，而且有些地方是必须使用（如子查询数据源）

4、where子句

where子句：跟在from数据源之后，对数据进行条件匹配

where是在磁盘读取后，进入内存之前进行筛选
- 不符合条件的数据不会进入内存
where筛选的内容因为还没进入内存，所以数据是没有被加工过的
- 字段别名不能在where中使用

步骤

1、确定要查询的数据需要进行条件筛选

2、使用where进行数据筛选

示例

1、查询t_35表中学生为lily的成绩信息

1	select * from t_35 where stu_name = 'Lily';

2、因为where是在磁盘取数据时进行条件筛选，此时数据没有进入内存，所以字段别名是无效的

1 2	# 错误 select stu_name name,score from t_35 where name = 'Lily';

小结

1、where是用来匹配条件筛选数据的

2、where是在数据从磁盘取出，还没进入内存前进行筛选：筛选过后合适的才会进入到内存（后续才能显示）

3、成熟的项目中几乎每次执行查询都是会使用条件查询的

5、运算符

运算符：用于进行运算的符号

比较运算符
- >（大于）、\<（小于）、=（等于）、>\=（大于等于）、\<\=（小于等于）、\<>（不等于）
- between A and B：A和B之间（A小于B），包括A和B本身（数值比较）
- in (数据1,数据2,…数据N)：在列举的数据之中
- like ‘pattern’：像上面样的，用于字符串比较
  - _：单下划线，匹配对应位置的一个任意字符（ab_：ab开头+一个字符，匹配abc，ab1，但不能匹配abcd）
  - %：匹配当前位置（往后）任意数量任意字符（ab%：ab开头+任意数量任意字符，匹配abc，ab1，abcd）
逻辑运算符
- and（逻辑与）、or（逻辑或）、not（逻辑非）
null运算符
- is null（为空）、is not null（不为空）

步骤

1、确定需要使用运算符进行运算

2、根据数据要求使用准确的运算符

示例

1、查询成绩不及格的所有学生信息

1 2	# 成绩条件：成绩是数值，又是比大小，可以直接使用比较运算符 select * from t_35 where score < 60;

2、查询成绩在60-90间的学生信息

# 成绩条件：区间60到90，可以有两种解决方案

select * from t_35 where score between 60 and 90;
select * from t_35 where score >= 60 and score <= 90;

3、查询还没有成绩的学生

1 2	# 成绩条件：成绩为null，所以不能用比较符号查，只能使用is null实现 select * from t_35 where score is null;

小结

1、运算符可以用来进行字段数据运算，配合where进行条件筛选

2、基本运算符与其他编程语言都差不多，SQL中有几个特殊的要了解一下

between and：一种>= and \<=的便捷写法
in：用来做具体选择，类似于switch里的case
is null/is not null：字段值为Null的判定方式

3、熟练应用的前提是不断熟练的使用，掌握每个运算符带来的结果和效果

6、group by子句

group by子句：分组统计，根据某个字段将所有的结果分类，并进行数据统计分析

分组的目的不是为了显示数据，一定是为了统计数据
group by子句一定是出现在where子句之后（如果同时存在）
分组统计可以进行统计细分：先分大组，然后大组分小组
分组统计需要使用统计函数
- group_concat()：将组里的某个字段全部保留
- any_value()：不属于分组字段的任意一个组里的值
- count()：求对应分组的记录数量
  - count(字段名)：统计某个字段值的数量（NULL不统计）
  - count(*)：统计整个记录的数量（较多）
- sum()：求对应分组中某个字段是和
- max()/min()：求对应分组中某个字段的最大/最小值
- avg()：求对应分组中某个字段的平均值

步骤

1、确定要进行数据统计

2、确定统计对象：分组字段（可以多个）

3、确定要统计的数据形式：选择对应统计函数

4、分组统计

示例

1、创建一张表，存储学生信息

create table t_40(
id int primary key auto_increment,
name varchar(10) not null,
gender enum('男','女','保密'),
age tinyint unsigned not null,
class_name varchar(10) not null comment '班级名称'
)charset utf8;

insert into t_40 values(null,'鸣人','男',18,'木叶1班'),
(null,'佐助','男',18,'木叶1班'),
(null,'佐井','男',19,'木叶2班'),
(null,'大蛇丸','男',28,'木叶0班'),
(null,'卡卡西','男',29,'木叶0班'),
(null,'小樱','女',18,'木叶1班'),
(null,'雏田','女',18,'木叶1班'),
(null,'我爱罗','男',19,'木叶1班'),
(null,'向日葵','女',6,'木叶10班'),
(null,'博人','男',8,'木叶10班'),
(null,'鼬','男',28,'木叶0班');

2、统计每个班的人数

1	select count(*),class_name from t_40 group by class_name;

3、多分组：统计每个班的男女学生数量

1	select count(*),class_name,gender from t_40 group by class_name,gender;

4、统计每个班里的人数，并记录班级学生的名字

1 2	select count(),group_concat(name),class_name from t_40 group by class_name; select count(),any_value(name),class_name from t_40 group by class_name;

分组原理

以统计班级学生为例

graph TB
A(获取数据后分组开始)-->B[匹配班级名字分组]
B-->|木叶1班|C[木叶1班组<br>鸣人<br>佐助<br>小樱<br>雏田<br>我爱罗]
B-->|木叶2班|D[木叶2班组<br>佐井]
B-->|木叶0班|E[木叶0班组<br>大蛇丸<br>卡卡西<br>鼬]
B-->|木叶10班|F[木叶10班组<br>博人<br>向日葵]
C-->G[统计结果<br>只对结果负责<br>结果就是函数,而函数只对小组工作<br>木叶1班组5人 木叶2班组1人 木叶0班组3人 木叶10班组2人]
D-->G
E-->G
F-->G
G-->H[返回结果]
H-->I((分组结束))

小结

1、分组与统计是不分离的，分组必然要用到统计，而统计一旦使用实际上就进行了分组

2、分组统计使用数据数据的查询只能依赖统计函数和被分组字段，而不能是其他字段（MySQL7以前可以，不过数据没意义：因为系统只保留组里的第一个）

3、group by子句有自己明确的位置：在where之后（where可以没有）

7、回溯统计

回溯统计：在进行分组时（通常是多分组），每一次结果的回溯都进行一次汇总统计

回溯统计语法：在统计之后使用 with rollup

步骤

1、确定要进行分组统计

2、确定是多分组统计

3、需要对每次分组结果进行汇总

4、使用回溯统计

示例

统计每个班的男女同学数量，同时要知道班级人数总数

# 只统计每个班的男女同学数量，没有班级汇总
select count(*),class_name,gender,group_concat(name) from t_40 group by class_name,gender;

# 汇总统计：回溯
select count(*),class_name,gender,group_concat(name) from t_40 group by class_name,gender with rollup;

回溯统计原理

graph TB
A(统计开始)-->B[大分组:班级名字分组]
B-->|木叶1班|C[木叶1班组<br>鸣人<br>佐助<br>小樱<br>雏田<br>我爱罗]
B-->|木叶2班|D[木叶2班组<br>佐井]
B-->|木叶0班|E[木叶0班组<br>大蛇丸<br>卡卡西<br>鼬]
B-->|木叶10班|F[木叶10班组<br>博人<br>向日葵]
C-->G[小分组:性别分组]
G-->|男|H[木叶1班组:男<br>鸣人<br>佐助<br>我爱罗]
G-->|女|I[木叶1班组:女<br>小樱<br>雏田]
D-->J[小分组:性别分组]
J-->|男|K[木叶2班组:男<br>佐井]
E-->L[小分组:性别分组]
L-->|男|M[木叶0班组:男<br>大蛇丸<br>卡卡西<br>鼬]
F-->N[小分组:性别分组]
N-->|男|O[木叶10班组:男<br>博人]
N-->|女|P[木叶10班组:女<br>向日葵]
H-->Q[性别回溯<br>木叶1班组:NULL<br>鸣人<br>佐助<br>小樱<br>雏田<br>我爱罗]
I-->Q
K-->R[性别回溯<br>木叶2班组:NULL<br>佐井]
M-->S[性别回溯<br>木叶0班组:NULL<br>大蛇丸<br>卡卡西<br>鼬]
O-->T[性别回溯<br>木叶10班组:NULL<br>博人<br>向日葵]
P-->T
Q-->U[班级名字回溯<br>NULL:NULL<br>全部人]
R-->U
S-->U
T-->U
U-->V((回溯统计结束))

小结+

1、回溯统计一般用在多字段分组中，用来统计各级分组的汇总数据

2、因为回溯统计会将对应的分组字段置空（不置空无法合并），所以回溯的数据还需要经过其他程序语言加工处理才能取出数据来

8、分组排序

分组排序：在分组后统计结果时可以根据分组字段进行升序或者降序显示数据

默认的系统就会自动对分组结果根据分组字段进行升序排序
可以设定分组结果的排序方式
- group by 字段名 [ASC]：升序排序（默认）
- group by 字段名 DESC：降序排序

步骤

1、确定使用分组统计

2、需要对结果进行降序排序（升序不用管）

3、使用分组降序排序

示例

对分组结果女性优先显示：gender为枚举，男值为1，女值为2

1	select count(*),class_name,gender,group_concat(name),any_value(name) from t_40 group by class_name,gender desc;

小结

1、分组排序是针对分组的结果，通过分组字段进行排序

2、一般情况下较少使用分组排序

9、having子句

having子句：类似于where子句，是用来进行条件筛选数据的

having子句本身是针对分组统计结果进行条件筛选的
having子句必须出现在group by子句之后（如果同时存在）
having针对的数据是在内存里已经加载的数据
having几乎能做where能做的所有事，但是where却不一定
- 字段别名（where针对磁盘数据，那时还没有）
- 统计结果（where在group by之前）
- 分组统计函数（having通常是针对group by存在的）

步骤

1、前面有分组统计

2、需要针对分组统计后的结果进行数据筛选

3、使用having组织条件进行筛选

示例

1、获取班级人数小于3的班级

select count(*) as `count`,class_name,group_concat(name) from t_40 group by class_name having `count` < 3;

select count(*) as `count`,class_name,group_concat(name) from t_40 group by class_name having count(*) < 3; # 多用了一次函数（效率降低）

select class_name,group_concat(name) from t_40 group by class_name having count(*) < 3; # 没办法，前面没统计，只能自己统计

小结

1、having也是用于数据筛选的，但是本质是针对分组统计，如果没有分组统计，不要使用having进行数据筛选

2、能用where解决问题的地方绝不使用having

where针对磁盘读取数据，源头解决问题
where能够限制无效数据进入内存，内存利用率较高，而having是针对内存数据筛选

10、order by子句

order by子句：排序，根据某个指定的字段进行升序或者降序排序

排序的参照物是校对集
order by子句在having子句字后（如果同时存在）
排序分为升序和降序：默认是升序
- order by 字段 [ASC]：升序
- order by 字段 DESC：降序
多字段排序：在根据某个字段排序好后，可以再细分排序

步骤

1、确定需要排序的字段

2、确定排序的方式：升序 or 降序

3、使用排序

示例

1、单字段排序：给所有学生按照年纪大小升序排序

1 2	select * from t_40 order by age; select * from t_40 order by age asc;

2、多字段排序：先性别降序排序，然后按年龄升序排序

1 2	select * from t_40 order by gender desc,age; select * from t_40 order by gender desc,age asc;

小结

1、排序是针对前面所得到的结果进行排序（已经进入到内存的数据）

2、多字段排序是在第一个字段排好序的情况下，不改变原来排序的基调后，再小范围排序（类似分组）

3、实际开发中排序的使用非常常见，尤其是在数值、时间上多见

11、limit子句

limit子句：限制数据的获取数量（记录数）

limit子句必须在order by子句之后（如果同时存在）
limit限制数量的方式有两种
- limit 数量：限制获取的数量（不保证一定能获取到指定数量）
- limit 起始位置,数量：限制数据获取的位置以及数量（分页）

步骤

1、确定要对记录数进行限制

2、确定限制的方式：限定数量 or 限定位置+限定数量

示例

1、获取t_40表中前3条数据

1	select * from t_40 limit 3;

2、获取t_40表中第3条以后的3条数据

1 2	select * from t_40 limit 3,3; select * from t_40 limit 6,3;

小结

1、limit限制数量可以有效的减少服务器的压力和传输压力

2、常利用limit来实现分页获取数据

12、总结

1、查询操作是所有操作里使用的最多也是最终的操作

2、查询操作的完整语法

1	select select选项字段列表[别名]\|* from 数据源[别名] where子句 group by子句 having子句 order by子句 limit 子句;

各个位置的顺序不能调换
五子句（where、group by、having、order by、limit）可以没有，但是出现后一定要保证顺序
group by到最后都是针对已经加载带内存中的数据进行加工处理

3、很多结构的组合其实可以达到同一效果，但是可能过程和效率会不同

6.3 数据更新

限制更新：即更新时对更新的记录数进行限制

限制更新通过limit来实现
限制更新其实是局部更新的一种手段，一般更多情况下是依据条件精确更新

步骤

1、确定要进行批量更新：符合条件的部分

2、确定更新的数量

3、使用limit限制更新数量

示例

对会员选3个发送10元红包（添加到账户）

create table t_41(
    id int primary key auto_increment,
    username varchar(50) not null unique,
    password char(32) not null,
    account decimal(10,2) default 0.00
)charset utf8;

insert into t_41 values(null,'username1','password',default),
(null,'username2','password',default),
(null,'username3','password',default),(null,'username4','password',default),(null,'username5','password',default);


update t_41 set account = account + 10 limit 3;

小结

1、限制更新可以实现批量小范围操作

2、实际开发当中，极少出现这类操作，一般都愿意精准操作（利用where条件明确更新条件）

3、更新操作不可逆

6.4 数据删除

学习目标：了解数据删除的其他规则，理解数据删除的危害

限制删除
清空数据

1、限制删除

限制删除：限制要删除的记录数

使用limit限制删除数量
一般很少使用限制删除，通常是通过where条件精确删除

步骤

1、确定要进行数据批量删除

2、确定通过where条件限定后无法完全满足删除条件

3、使用limit限制删除数量完成目标

示例

删除没有账户余额的一个用户（当前用户量少，一般数量会大些）

1	delete from t_41 where account = 0 limit 1;

小结

1、限制删除本质也是删除，操作不可逆，谨慎使用

2、更愿意使用精确删除

2、清空数据

清空数据：将表中的所有数据清除，并且将表的所有状态回到原始状态

清空数据的本质是先删除表，后创建表
清空数据能够让表的一些变化状态回到原始状态
- 自增长重新回到初始值
清空语法： truncate 表名

步骤

1、确定要删除表中所有数据

2、确定需要让表状态回到原始

3、truncate清空数据

示例

清空用户数据表

1	truncate t_41;

小结

1、清空数据表是一种比delete更彻底的数据删除方式，所以使用之前必须要慎重

2、一般只会在开发阶段才会使用这种数据删除操作，如表数据发生错乱，或者业务发生变化

7 MySQL多表操作

示例

不管是大型还是小型项目，一个数据库里都会有N张表，表之间也通过一对一、多对一或者多对多关系进行关联：如新闻管理系统

作者表：id、用户名、密码

新闻表：id、标题、内容、发布时间、作者id

显示新闻的时候是肯定需要显示作者姓名的

原始方式：查出新闻—>查出作者ID—>查出作者
- 步骤多
- 如果是批量显示新闻就更麻烦
多表操作：使用连接查询一条SQL搞定

小结

1、多表操作是实际开发时经常遇到的解决问题的方案

2、多表操作能够在数据库层就实现大量数据的组合或者筛选

7.1 联合查询

1、联合查询

联合查询：union，是指将多个查询结果合并成一个结果显示

联合查询是针对查询结果的合并（多条select语句合并）
联合查询语法

select 查询【决定字段名字】
    union 查询选项
select 查询
...

联合查询要求：联合查询是结果联合显示
- 多个联合查询的字段结果数量一致
- 联合查询的字段来源于第一个查询语句的字段
查询选项：与select选项雷士
- all：保留所有记录
- distinct：保留去重记录（默认）

步骤

1、确定要进行多个表数据的联合操作

表结构一致
数据汇总

2、确定数据的要求：全部保留 or 去重

3、使用联合查询

示例

1、创建一个表与t_40，并插入数据

create table t_42 like t_40;

insert into t_42 values(null,'犬夜叉','男',200,'神妖1班'),
(null,'日暮戈薇','女',16,'现代1班'),
(null,'桔梗','女',88,'法师1班'),
(null,'弥勒','男',28,'法师2班'),
(null,'珊瑚','女',20,'法师2班'),
(null,'七宝','保密',5,'宠物1班'),
(null,'杀生丸','男',220,'神妖1班'),
(null,'铃','女',4,'现代1班'),
(null,'钢牙','男',68,'神妖1班'),
(null,'奈落','男',255,'神妖1班'),
(null,'神乐','女',15,'神妖2班');

t_42与t_40结构一样，可以理解为因为数据量大拆分到了两个表中

2、使用联合查询将两张表的数据拼接到一起显示

1
2
3

select * from t_40 
union
select * from t_42;

3、联合查询选项默认是distinct

select * from t_40
union 
select * from t_40;

select * from t_40
union all
select * from t_40;

4、联合查询不要求字段类型一致，只对数量要求一致，而且字段与第一条查询语句相关

1
2
3

select name from t_40
union all
select age from t_40;

注意：如果数据不能对应，那么查询没有意义

5、如果使用where对数据进行筛选，where针对的是select指令，而不是针对union结果

select * from t_40 
union all
select * from t_42
where gender = '女';

where只针对第二条select有效
若要全部有效，需要select都使用where

小结

1、union是负责将多次查询的结果统一拼凑显示

记录数增加
字段数不变（第一条SQL指令决定）

2、union常用方式

因为数据量大分表存储，然后统一查看或者统计
根据不同维度对数据进行筛选，然后统一查看或者统计

3、union默认是去重的，想要保留全部查询结果，需要使用union all

2、联合查询排序

联合查询排序：针对联合查询的结果进行排序

order by本身是对内存结果进行排序，union的优先级高于order by，所以order by默认是对union结果进行排序
如果想要对单独select的结果进行排序，需要两个步骤
- 将需要排序的select指令进行括号包裹（括号里使用order by）
- order by必须配合limit才能生效（limit一个足够大的数值即可）

步骤

1、确定需要对联合查询进行排序

2、确定排序内容

针对union结果排序
针对union前的select结果进行排序

3、选择合适的排序方式

示例

1、将t_40和t_42表的结果使用年龄降序排序

select * from t_40
union all
select * from t_42
order by age desc; #针对的是整个union之后的结果

2、t_40表按年龄降序排序，t_42表按年龄升序排序

# 无效方式
(select * from t_40 order by age desc)
union 
(select * from t_42 order by age);

# 正确方式
(select * from t_40 order by age desc limit 99999)
union 
(select * from t_42 order by age desc limit 99999);

小结

1、联合排序需要区分排序的内容是select结果还是union结果

union结果：在最后使用排序即可
select结构：需要针对select使用排序
- select必须使用括号包裹
- select里的排序必须配合limit才会生效

7.2 连接查询

连接查询：join，将两张表依据某个条件进行数据拼接

join左右各一张表：join关键字左边的表叫左表，右边的表叫右表
连接查询的结果都是记录会保留左右表的所有字段（字段拼接）
- 具体字段数据依据查询需求确定
- 表字段冲突需要使用表别名和字段别名区分
不同的连表有不同的连接方式，对于结果的处理也不尽相同
连接查询不限定表的数量，可以进行多表连接，只是表的连接需要一个一个的连（A join B join C …）

小结

1、连接查询就是通过字段拼接，把两张表的记录变成一条记录：字段数量增加

2、连接查询的目的是将分散在不同表的数据组合到一起，方便外部使用数据

1、交叉连接

交叉连接：cross join，不需要连接条件的连接

交叉连接产生的结果就是笛卡尔积
- 左表的每一条记录都会与右表的所有记录连接并保留
交叉连接没有实际数据价值，只是丰富了连接查询的完整性

示例

交叉连接t_41和t_42表

1	select * from t_41 cross join t_42; # t_41,t_42

小结

1、笛卡尔积无意义，尽量避免出现

2、内连接

内连接：[inner] join，将两张表根据指定的条件连接起来，严格连接

内连接是将一张表的每一条记录去另外一张表根据条件匹配
- 匹配成功：保留连接的数据
- 匹配失败：都不保留
内连接语法： 左表 join 右表 on 连接条件

步骤

1、确定需要从多张表中获取数据组成记录

2、确定连接的要求是保留连接成功的，不成功的数据不要

3、使用内连接

示例

1、设计学生表和专业表：学生对专业多对一关系

# 学生表
create table t_43(
    id int primary key auto_increment,
    name varchar(50) not null,
    course_no int
)charset utf8;
insert into t_43 values(null,'Student1',1),
(null,'Student2',1),
(null,'Student3',2),
(null,'Student4',3),
(null,'Student5',1),
(null,'Student6',default);

# 专业表
create table t_44(
    id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;
insert into t_44 values(null,'Computer'),(null,'Software'),(null,'Network');

2、获取已经选择了专业的学生信息，包括所选专业

# 学生和专业在两个表中，所以需要连表
# 学生必须有专业，而专业也必须存在，所以是内连接
# 连接条件：专业编号
# 两张表有两个字段冲突：id、name，所以需要使用别名
select t_43.*,t_44.name as course_name from t_43 inner join t_44 on t_43.course_no = t_44.id;

# 表名的使用也可以使用别名
select s.*,c.name as c_name from t_43 as s inner join t_44 c on s.course_no = c.id;

字段冲突的话在MySQL里倒是不影响，只是会同时存在，但是后续其他地方使用就不方便了

原理分析

graph TB
A(连接开始<br>A inner join B on a.aid = b.id)-->B[连接条件循环匹配匹配<br>A表逐条取出记录与B表的每条记录匹配]
B-->|a.aid = b.id|C[保留匹配结果<br>根据字段要求保留]
C-->|继续下一个|B
B-->E[全部匹配完毕]
B-->|a.aid <> b.id|D[放弃]
D-->|继续下一个|B
E-->F((取出所有匹配成功结果:结束))

小结

1、内连接匹配规则就是必须保证左表和右表同时存储连接关系，这样的数据才会保留

2、扩展：内连接可以没有on条件，那么得到的结果就是交叉连接（笛卡尔积），无意义

3、扩展：内连接的on关键字可以换成where，结果是一样（但是不建议使用）

3、外连接

外连接：outer join，是一种不严格的连接方式

外连接分为两种
- 左外连接（左连接）：left join
- 右外连接（右连接）：right join
外连接有主表和从表之分
- 左连接：左表为主表
- 右连接：右表为主表
外连接是将主表的记录去匹配从表的记录
- 匹配成功保留
- 匹配失败（全表）：也保留，只是从表字段置空

步骤

1、确定进行连表操作

2、确定要有数据保护，即表中数据匹配失败也要保留

3、确定主从表

4、选择对应外连接

示例

1、查出所有的学生信息，包括所在班级（左连接）

1 2	# 主要数据是学生，而且是全部学生：外连接、且学生表是主表 select s.*,c.name c_name from t_43 s left join t_44 c on s.course_no = c.id;

2、查出所有班级里的所有学生（右连接）

1 2	# 主表是班级 select s.*,c.name c_name from t_43 s right join t_44 c on s.course_no = c.id;

小结

1、外连接与内连接的区别在于数据匹配失败的时候，外连接会保留一条记录

主表数据保留
从表数据置空

2、外连接不论是左连接还是右连接，字段的顺序不影响，都是先显示左表数据，后显示右表数据

3、外连接必须使用on作为连接条件（不能没有或者使用where替代）

4、自然连接

自然连接：natural join，是一种自动寻找连接条件的连接查询

自然连接不是一种特殊的连接方式，而是一种自动匹配条件的连接
自然连接包含自然内连接和自然外连接
- 自然内连接：natural join
- 自然外连接：natural left/right join
自然连接条件匹配模式：自动寻找相同字段名作为连接条件（字段名相同）

步骤

1、需要进行连表查询结果

2、连表查询的表字段能够直接关联（字段名字相同：非常高的表结构设计）

3、选择合适的连接方式：内连接 or 外连接

4、使用自然连接

示例

1、自然连接t_43和t_44表

1	select * from t_43 natural join t_44;

2、自然连接是不管字段是否有关系的，只管名字是否相同：如果想要自然连接成功，那么字段的设计就必须非常规范

create table t_45(
    s_id int primary key auto_increment,
    s_name varchar(50) not null,
    c_id int comment '课程id'
)charset utf8;
insert into t_45 select * from t_43;

create table t_46(
    c_id int primary key auto_increment,
    c_name varchar(50) not null unique
)charset utf8;
insert into t_46 select * from t_44;

# 自然连接：条件只有一个相同的c_id
select * from t_45 natural join t_46;

自然连接会将同名条件合并成一个字段（数据一样）

小结

1、自然连接本身不是一种特别连接，是基于内连接、外连接和交叉连接实现自动条件匹配而已

没有条件（没有同名字段）：交叉连接
有条件：内连接/外连接（看关键字使用）

2、自然连接使用较少，因为一般情况下表的设计很难做到完全标准或者不会出现无关同名字段

5、using关键字

using关键字：连接查询时如果是同名字段作为连接条件，using可以代替on出现（比on更好）

using是针对同名字段（using(id) === A.id = B.id）
using关键字使用后会自动合并对应字段为一个
using可以同时使用多个字段作为条件

步骤

1、需要进行连表进行数据查询

2、两个表的连接条件字段同名

3、使用using关键字作为连接条件

示例

查询t_45中所有的学生信息，包括所在班级名字

1 2	select s.,c.c_name from t_45 s left join t_46 c using(c_id); select from t_45 s left join t_46 c using(c_id);

小结

1、using关键字用来简化同名条件字段的连接条件行为

2、using关键字与自然连接相似，但是比自然连接灵活，可以指定有效的同名连接条件，忽略无效的同名字段

6、总结

1、连接查询是实际开发过程中应用最多的查询方式

很少出现单表查询操作
实体（表）间或多或少都是有关联的

2、连接查询的效率肯定没有单表查询高

逆规范化可以适当的运用来提升效率

3、连接查询中使用的较多的就是内连接和外连接

7.3 子查询

子查询：sub query，通过select查询结果当做另外一条select查询的条件或者数据源

示例

想查出某个专业的所有学生信息

查询的目标是学生表
查询的条件在专业表

按照以前的知识，可以产生两种解决方案：

1、分开查询

从专业表通过名字查出专业id

1	select c_id from t_46 where c_name = '专业名字';

从学生表通过专业id取出学生信息

1	select * from t_45 where c_id = '查出来的专业id';

2、连表查询

将学生表与专业表通过专业id相连
对整个连表结果通过where条件进行筛选

1	select s.* from t_45 s right join t_46 c using(c_id) where c.c_name = '专业名字';

从解决方案分析

1、分开查询数据量小，但是麻烦

2、连接查询方便，但是效率不高（先连后筛选）

如果能够将方案1变成一个简单的方式就好了

1	select * from t_45 where c_id = (select c_id from t_46 where c_name = '专业名字');

以上就是子查询

小结

1、子查询就是能够将一些具有先后顺序的查询组装到一个查询语句中，从而节省操作的过程，降低复杂程度

1、子查询分类

子查询分类：根据子查询出现的位置或者产生的数据效果分类

位置分类
- from子查询：子查询出现在from后做数据源
- where子查询：子查询出现在where后做数据条件
按子查询得到的结果分类
- 标量子查询：子查询返回的结果是一行一列（一个数据）
- 列子查询：子查询返回的结果是一列多行（一列数据）
- 行子查询：子查询返回的结果是一行多列
- 表子查询：子查询返回的结果是一个二维表
- exists子查询：子查询返回的结果是布尔结果（验证型）
子查询都需要使用括号 () 进行包裹，必要时需要对子查询结果进行别名处理（from子查询）

小结

1、通常我们使用子查询结果定义分类

2、位置划分是包含子查询结果的

from子查询对应表子查询（表子查询）
where子查询

2、标量子查询

标量子查询：子查询返回的结果是一行一列，一个值

标量子查询是用来做其他查询的条件的

步骤

1、确定要从一张表中获取数据（可以是多张）

2、确定查询条件在当前查询表中无法实现但是可以从其他表中精确获得（只有一个）

3、使用标量子查询

示例

获取Computer专业的所有学生

# 数据目标：学生表t_45
# 条件：专业名字，不在t_45中，但是t_45中的专业id可以通过专业名字在另外一张表精确获得（一个值）

select * from t_45 where c_id = (select c_id from t_46 where c_name = 'Computer');

小结

1、标量子查询通常用简单比较符号来制作条件的

3、列子查询

列子查询：子查询返回的结果是一列多行

列子查询通常是用来做查询条件的

步骤

1、确定要从一张表中获取数据（可以是多张）

2、确定查询条件在当前查询表中无法实现但是可以从其他表中精确获得（一个字段多个数据）

3、使用列子查询

示例

1、获取所有有学生的班级信息

# 数据获取目标是班级信息
# 数据获取条件是在学生表中的班级id，是多个

select * from t_46 where c_id in (select distinct c_id from t_45 where c_id is not null);

小结

1、列子查询通常是作为外部主查询的条件，而且是使用in来进行判定

4、行子查询

行子查询：子查询返回的结果是一行多列

行子查询需要条件中构造行元素（多个字段组成查询匹配条件）
- (元素1,元素2,..元素N)
行子查询通常也是用来作为主查询的结果条件

步骤

1、确定获取数据的条件不只是一个字段

2、确定数据条件的来源不在当前表中（也可以在当前表），但是可以通过条件精确获取到（一行多列）

3、使用行子查询

示例

获取学生表中性别和年龄都与弥勒相同的学生信息

# 查询条件有多个：性别和年龄
# 数据的条件的来源在另外一张表中

# 解决思路：两个标量子查询
select * from t_40 where gender = (select gender from t_42 where name = '弥勒') and age = (select age from t_42 where name = '弥勒');

问题分析：以上查询解决了问题但是用到了两次子查询（效率降低），而且查询语句是一样的，只是字段不一样，可以使用行子查询解决

1
2
3

# 构建条件行元素(gender,age)

select * from t_40 where (gender,age) = (select gender,age from t_42 where name = '弥勒');

小结

1、行子查询是可以使用多个标量子查询替代解决问题的，但是行子查询的效率会比多个标量要高。需要使用到行子查询的时候不会使用标量子查询来解决的

如果数据来源不在一张表可以考虑使用多个标量子查询实现

5、表子查询

表子查询：子查询返回的结果是多行多列（二维表）

表子查询多出现在from之后当做数据源（from子查询）
表子查询通常是为了想对数据进行一次加工处理，然后再交给外部进行二次加工处理

步骤

1、需要查询的数据通过一次SQL查询不能直接搞定（可能顺序关系导致）

2、如果先把结果加工后（多行多列），外部再来一层结果查询加工可以完成目标

3、使用表子查询

示例

获取学生表中每个班级里年龄最大的学生信息（姓名、年龄、班级名字），然后按年龄降序排序显示

1 2	# 尝试直接解决 select any_value(name),max(age) m_age,class_name from t_42 group by class_name order by m_age desc;

分组统计中any_value取的是分组后的第一条记录数据（犬夜叉），而我们要的是最大

解决方案：要是在分组之前将所有班级里的学生本身是降序排序，那么分组的第一条数据就是满足条件的数据。但是问题是：order by必须出现在 group by之后，如何解决？

# order by必须在group by之前解决：就要想办法让order by在group by之前而且不在同一条select指令中（同一条无解）
# 必须使用子查询解决在不用SQL中的问题，而子查询的结果应该是全部记录信息，所以应该是表子查询，而且是数据源

select any_value(name),max(age),class_name from 
(select name,age,class_name from t_42 order by age desc) as t
group by class_name;

依然无效：原因是MySQL7以后若要子查询中的order by生效，需要像联合查询那样，让子查询带上limit

1
2
3

select any_value(name),max(age),class_name from 
(select name,age,class_name from t_42 order by age desc limit 99999) as t
group by class_name;

因为order by在子查询的时候已经对结果进行过排序了，所以分组统计后最终结果也就不用再进行排序了，如果需要再进行排序，只要在最终结果后排序即可
- 如果要用到字段排序，建议在外部查询select字段里使用别名（否则又要统计）

1
2
3

select any_value(name),max(age) m_age,class_name from 
(select name,age,class_name from t_42 order by age desc limit 99999) as t
group by class_name order by m_age;

小结

1、表子查询通常解决的问题是提供数据源

2、表子查询出现的业务

一条select指令中所用到的子句顺序不能满足查询条件
数据的来源可能是多张数据表

3、特别注意：在MySQL7以后，子查询中使用的order by子句需要配合limit才会生效

6、exists子查询

exists子查询：代入查询，将主表（外部查询）的每一行代入到子表（子查询表）进行校验

子查询返回的结果是布尔结果
- 成功返回true
- 失败返回false
exists子查询通常是作为where条件使用
- where exists(子查询)

步骤

1、确定查询的数据来自主表

2、确定条件是需要去子表（其他表）进行验证：不需要去子表获取数据之类的

3、使用exists子查询

示例

获取所有有学生的班级信息t_46

# 获取的数据是班级表t_46
# 班级是否有学生需要在t_45中确认，并不需要t_45提供任何数据显示

select * from t_46 c where exists(select c_id from t_45 where c.c_id = c_id);

小结

1、exists子查询通常用来解决那种不需要数据但是需要去表中确认关系的查询问题

在exists子查询中尽量少的选择字段（不建议使用*），因为都是无价值的

7、比较方式

比较方式：在子查询中可以使用一些特定的比较方式

特定的比较方式都是基于比较符号一起使用
all：满足后面全部条件
- > all(结果集)：数据要大于结果集中的全部数据
any：满足任意条件
- \= any(结果集)：数据只要与结果集中的任何一个元素相等
some：满足任意条件（与any完全一样）
结果集：可以是直接的数据也可以是子查询结果（通常是列子查询）

示例

1、找出t_40表中与t_42表中年龄相同的信息

# 数据获取在t_40表
# 数据条件在t_42表

# 解决方案1：使用in列子查询
select * from t_40 where age in (select distinct age from t_42);

# 解决方案2：使用exists子查询
select * from t_40 t1 where exists(select id from t_42 where t1.age = age);

# 解决方案3：使用any或者some匹配（列子查询）
select * from t_40 where age = some(select age from t_42);

小结

1、比较方式其实很多都可以实现替代，越精准的数据匹配方式效率就越高

8、总结

1、子查询通常使用较多的是标量子查询、列子查询和exists子查询

2、子查询的效率是比连接查询的效率要低的，要适当选择使用

子查询是在主表的每一次记录匹配时都会执行一次（where子查询）
- 主表数据大，子表数据小：影响较小
- 主表数据小，子表数据大：影响较大
from子查询因为只执行一次，影响不大

3、理论上来讲，不限制子查询的嵌套，但是考虑到效率的降低，不建议使用子查询嵌套

8 MySQL安全管理

安全管理：用各种方式来确保数据库的安全和数据的安全

示例

携程的数据库被程序员删库跑路…

如果有用户管理，那么可以通过权限限制其没有权限删除
如果有数据备份，即便数据删除，也可以很快的实现数据还原，减小损失
…

小结

1、安全管理是每一个接触数据库的人都应该考虑的问题，尤其是DBA（数据库管理员）

2、数据库安全的维度有很多

管理安全：用户、权限、备份还原等
结构安全：外键、视图、事务等
执行层：预处理

8.1 外键约束

1、外键

外键：foreign key，表中指向外部表主键的字段定义成外键

外键必须要通过语法指定才能称之为外键
- [constraint外键名] foreign key(当前表字段名) references 外部表(主键字段)
外键构成条件
- 外键字段必须与对应表的主键字段类型一致
- 外键字段本身要求是一个索引（创建外键会自动生成一个索引）

步骤

1、确定表中字段与另外一张表存在关联关系

2、使用外键明确关联外表

3、外键约束成功

示例

1、创建专业表和学生表，学生表中的专业id指向专业表id

create table t_47(
    id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;

create table t_48(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int comment '指向t_46表中的id主键',
    constraint `c_id` foreign key(c_id) references t_47(id)
)charset utf8;

2、外键可以不指定名字，系统会自动生成

create table t_49(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int,
    foreign key(c_id) references t_47(id)
)charset utf8;

小结

1、外键是需要保证字段与外部连接的主键字段一致的

2、一张表可以有多个外键，但是一个字段只能产生一个外键

2、外键约束

外键约束：当表建立外键关系后，外键就会对主表（外键指向的表）和子表（外键所在的表）里的数据产生约束效果

外键约束的是写操作（默认操作）
- 新增：子表插入的数据对应的外键必须在主表存在
- 修改：主表的记录如果在子表存在，那么主表的主键不能修改（主键不能修改）
- 删除：主表的记录如果在子表存在，那么主表的主键不能删除
- 删除：主表的记录如果在子表存在，那么主表的主键不能删除
外键约束控制：外键可以在定义时控制外键的约束作用
- 控制类型
  - on update：父表更新时子表的表现
  - on delete：父表删除时子表的表现
- 控制方式
  - cascade：级联操作，父表操作后子表跟随操作
  - set null：置空操作，父表操作后，子表关联的外键字段置空
  - restrict：严格模式，不允许父表操作（默认的）
  - no action：子表不管

步骤

1、确定表的外键关联关系

2、确定主表的约束控制

3、明确使用相应的约束控制

4、系统自动约束

示例

1、子表不能插入主表不存在的数据

insert into t_48 values(null,'Tony',2);	# 错误

insert into t_47 values(null,'English');
insert into t_48 values(null,'Peny',1);

2、默认的外键产生后，主键不能更新被关联的主键字段或者删除被关联的主键记录

1
2
3

# 错误
update t_47 set id = 2;
delete from t_47 where id = 1;

3、限制外键约束，一般使用更新级联，删除置空

on update cascade：更新级联
on delete set null：删除置空

create table t_50(
    id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;

create table t_51(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int, # 如果要允许置空，就不能not null
    foreign key(c_id) references t_50(id) on update cascade on delete set null
)charset utf8;

insert into t_50 values(null,'Chinese'),(null,'Computer');
insert into t_51 values(null,'Tony',1),(null,'Petter',2);

子表依然不允许插入父表不存在的外键
但是可以插入外键为Null的数据

# 错误
insert into t_51 values(null,'Lilei',3);

insert into t_51 values(null,'Lilei',NULL); # OK

父表的更新（主键）会让关联的外键自动级联更新

1	update t_50 set id = 3 where id = 1;

父表的删除会让关联的外键自动自动置空

1	delete from t_50 where id = 3;

小结

1、外键约束对子表和父表都有约束

子表约束：子表不能插入父表不存在的外键
父表约束
- 更新约束（默认不允许）
- 删除约束（默认不允许）
一般约束
- 级联更新
- 删除置空

2、外键约束增强了数据的安全性和可靠性，但是会增加程序对于数据的不可控性，所以是实际开发中一般会通过程序逻辑控制来保证数据的完整性和安全性，外间使用较少

3、外键管理

外键管理：在表创建后期维护外键

新增外键

1	alter table 表名 add [constraint `外建名`] foreign key(外键字段) references 表名(主键) [on 外键约束]

删除外键

1	alter table 表名 drop foreign key 外键名;

更新外键：先删除后新增

示例

1、删除外键

1	alter table t_51 drop foreign key t_51_ibfk_1; # 系统生成的外键

2、追加外键

1	alter table t_51 add constraint `t_51_50` foreign key(c_id) references t_50(id);

注意：追加外键需要保证外键字段里的值要么为Null，要么在父表中都能找到

小结

1、外键的使用最好的创建表结构的时候就维护好，后期的维护对子表数据有要求

8.2 事务安全

1、事务

事务：要做的某个事情

计算机中的事务是指某个程序执行单元（写操作）
事务安全：当事务执行后，保障事务的执行是有效的，而不会导致数据错乱
事务安全通常针对的是一连串操作（多个事务）而产生的统一结果

MySQL中默认的写操作是直接写入的
- 执行写操作SQL
- 同步到数据表

示例

银行转账：从A账户转账到B账户

创建数据表

create table t_52(
    id int primary key auto_increment,
    name varchar(50) not null,
    account decimal(10,2) default 0.00
)charset utf8;

insert into t_52 values(null,'Tom',10000),(null,'Lucy',100);

转账：Tom向Lucy转账，一定是分为两步

# Tom扣钱
update t_52 set account = account - 1000 where id = 1;

# Lucy收钱
update t_52 set account = account + 1000 where id = 2;

以上两步必须都成功转账才能叫成功
两步操作无法确保哪一步会出问题（尤其是第二步）
为了保障两步都成功才能叫事务安全

事务安全原理

事务安全是在操作前告知系统，接下来所有的操作都暂不同步到数据表，而是记录到事务日志，指导后续所有操作都成功，再进行同步；否则取消所有操作

以上述转账为例

graph TB
A(转账开始)-->B[开启事务]
B-->C{事务1:Tom转出1000}
C-->|成功|D[记录到事务日志]
C-->|失败|G
D-->E{事务2:Lucy转入1000}
D-->|失败|G
E-->|成功|F[记录到事务日志]
F-->G[关闭事务<br>成功:提交事务 同步到数据表\清除事务日志<br>失败:回滚事务 清除事务日志]
G-->H((结束))

小结

1、事务的目的就是为了保障连续操作的一致性，保证结果的完整性

2、事务的原理是通过将操作结果暂时保存在事务日志中，等所有操作的结果都是成功的，然后一并同步到数据表

2、事务处理

事务处理：利用自动或者手动方式实现事务管理

自动事务处理：系统默认，操作结束直接同步到数据表（事务关闭状态）
- 系统控制：变量 autocommit（值为ON，自动提交）
手动事务处理
- 开启事务： start transaction
- 关闭事务
  - 提交事务：commit（同步到数据表同时清空日志数据）
  - 回滚事务：rollback（清空日志数据）
事务回滚：在长事务执行中，可以在某个已经成功的节点处设置回滚点，后续回滚的话可以回到某个成功点
- 设置回滚点：savepoint 回滚点名字
- 回滚到回滚点：rollback to 回滚点名字

步骤

1、确定操作需要使用到事务操作

2、开启事务

3、执行事务

如果需要回滚点设置：设置回滚点
如果需要回滚：回滚到回滚点

4、结束事务

成功提交事务：同步到数据表，清空事务日志
失败回滚事务：清空事务日志

示例

1、手动事务：启用事务转账，成功提交事务

# 开启事务
start transaction;

# Tom扣钱
update t_52 set account = account - 1000 where id  = 1;

# Lucy收钱
update t_52 set account = account + 1000 where id  = 2;

# 提交事务
commit;

2、手动事务：启用事务转账，成功提交事务（回滚点）

# 开启事务
start transaction;

# Tom扣钱
update t_52 set account = account - 1000 where id= 1;

# 设置回滚点
savepoint sp1;

# Lucy收钱
update t_52 set account = account + 10000 where id= 2;

# 操作失败回到回滚点
rollback to sp1;

# Lucy收钱
update t_52 set account = account + 1000 where id= 2;

# 提交事务
commit;

3、自动事务

Mysql默认是自动提交事务的：所以事务一旦发生就会立即写入到数据表（不能多个事务一起完成任务）

1	show variables like 'autocommit';

关闭自动提交事务（当前设置级别用户级：当前用户档次连接有效）

1	set autocommit = 0;

手动提交事务

1 2	insert into t_52 values(null,'Liu',1000); commit;

小结

1、事务处理要应用到多次写操作组成的大事务中，如金融安全等

2、事务处理通常都会使用手动控制事务，没必要去修改原本的自动提交的机制，开启所有事务

3、扩展：事务处理的支持是有条件的

存储引擎需要为InnoDB

3、事务特点

事务特点：事务处理具有ACID四大特性

原子性（Atomicity ）：一个事务操作是一个整体，不可拆分，要么都成功，要么都失败
一致性（Consistency）：事务执行之前和执行之后都必须处于一致性状态，数据的完整性没有被破坏（事务逻辑的准确性）
隔离性（Isolation ）：事务操作过程中，其他事务不可见
持久性（Durability ）：事务一旦提交，结果不可改变

小结

1、事务特点需要在对应事务操作时，结合多个用户来看才能看的完整和亲切

2、扩展

事务锁：当一个事务开启时，另外一个事务是不能对当前事务锁占用的数据进行操作的
- 行所：当前事务只占用了一行（id精确检索数据），那么其他事务可以操作其他行数据
- 表所：当前事务占用了整张表（like扫码整个表），那么其他事务对整张表都不能操作
脏读：一个事务在对某个数据进行操作但尚未提交，而另外一个事务读到了这个“历史”数据其实已经被修改

8.3 预处理

1、预处理

预处理：prepare statement，一种预先编译SQL指令的方式（然后命令执行）

预处理不同于直接处理，是将要执行的SQL指令先发送给服务器编译，然后通过指令执行
- 发送预处理：prepare 预处理名字 from '要执行的SQL指令'
- 执行预处理：execute 预处理名字
预处理管理
- 预处理属于会话级别：即当前用户当次连接有效（断开会被服务器清理掉）
- 删除预处理：deallocate | drop prepare 预处理名字

步骤

1、要执行的SQL指令想使用预处理

重复执行的指令
涉及数据安全的指令

2、发送预处理指令

3、执行预处理

示例

1、查询学生的SQL指令需要重复执行很多次

# 普通操作
select * from t_42;

# 预处理操作：发送预处理
prepare p1 from 'select * from t_42';

# 预处理操作：执行预处理
execute p1;

# 删除预处理
deallocate  prepare p1;

预处理原理

普通处理和预处理对比

graph TB
A(普通处理)-->B[接收SQL指令]
B-->C[编译SQL]
C-->D[执行SQL]
D-->E((返回结果))

A1(预处理)-->B1[接收预处理指令]
B1-->C1[编译预处理指令]
C1-->D1{是否执行}
D1-->|execute<br>以后都不需要执行接收SQL和编译SQL|E1[执行SQL<br>可重复执行]
D1-->|不执行|F1
E1-->F1((返回结果))

小结

1、预处理就是把要执行的结构（SQL指令）提前发送给服务器端，服务器进行编译但不执行，等待执行指令后才执行

2、预处理的作用

性能优化
- 效率优化：同样的SQL不用每次都进行编译（编译耗时）
  - 普通处理：每次都需要编译
  - 预处理：编译一次
- 网络传输优化：复杂的SQL指令只需要传输一次
  - 普通处理：每次都需要网络传输SQL指令
  - 预处理：传输一次SQL指令，以后都是执行指令
安全：有效防止SQL注入（外部通过数据的特殊使用使得SQL的执行方式改变）
- 普通处理：直接发送给服务器执行（容易出现SQL注入）
- 预处理：发送的是结构，数据是后期执行传入（传入协议不一样，数据安全性高）

2、预处理传参

预处理传参：在执行预处理的时候传入预处理需要的可变数据

一般预处理都不会是固定死的SQL指令，而是具有一些数据可变的执行（条件）
- 可变数据的位置使用占位符 ? 占位
1
prepare 预处理名字 from `预处理指令变化部分使用?替代`
在执行预处理的时候将实际数据传进去代替占位符执行SQL
- 数据存储到变量（预处理传入的值必须是变量保存的）
1
set @变量名 = 值
- 使用using关键字传参
1
execute 预处理名字 using @变量名
- 数据传入的顺序与预处理中占位符的顺序一致

步骤

1、同样的SQL指令要执行N次，但是条件不一致

2、使用预处理占位符发送预处理指令

3、设定变量保存要传入的数据

4、执行预处理，携带变量参数

示例

向t_40表中插入数据

# 准备预处理：涉及参数
prepare t_40_insert from 'insert into t_40 values(null,?,?,?,?)';

# 设置变量并传入参数
set @name = '药师兜';
set @gender = '男';
set @age = 23;
set @class_name = '木叶1班';

# 执行预处理
execute t_40_insert using @name,@gender,@age,@class_name;

小结

1、预处理传参是实际应用预处理时最常见的方式

2、预处理指令可以适用于增删改查各种指令

3、如果预处理的指令不是在一次连接中重复使用，那么预处理反而会降低效率。所以预处理的执行如果不是考虑到安全因素，那么一定是SQL需要重复执行

8.4 视图

1、视图

视图：view，一种由select指令组成的虚拟表

视图是虚拟表，可以使用表管理（结构管理）
- 为视图提供数据的表叫做基表

# 创建视图
create view 视图名字 as select指令;

# 访问视图：一般都是查询
select */字段名 from 视图名字;

视图有结构，但不存储数据
- 结构：select选择的字段
- 数据：访问视图时执行的select指令

步骤

1、确定需要使用视图提供数据

数据来源是多张表
对外部系统提供数据支撑（保护基表数据）

2、使用视图

示例

1、需要对外提供一个学生详情的数据，经常使用：可以利用视图实现

# 对外提供数据，要保护数据本身的安全
# 需要长期使用

# 创建视图
create view v_student_info as select * from t_45 left join t_46 using(c_id);
# 使用视图：像表一样使用
select * from v_student_info;

2、有些复杂的SQL又是经常用到的，如多张表的连表操作：可以利用视图实现

# 院系表
create table t_53(
    id int primary key auto_increment,
    name varchar(50) not null
)charset utf8;
insert into t_53 values(null,'语言系'),(null,'考古系');

# 专业表
create table t_54(
    id int primary key auto_increment,
    name varchar(50) not null,
    s_id int not null comment '学院id'
)charset utf8;
insert into t_54 values(null,'English',1),(null,'Chinese',1);

# 学生表
create table t_55(
    id int primary key auto_increment,
    name varchar(50) not null,
    s_id int not null comment '专业Id'
)charset utf8;
insert into t_55 values(null,'Lilei',2),(null,'Mark',2),(null,'Tony',1);

# 获取所有学生的明细信息
select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_55 as stu left join t_54 sub on stu.s_id = sub.id left join t_53 sch on sub.s_id = sch.id;

# 以视图保存这类复杂指令，后续可以直接访问视图
create view v_student_detail as select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_55 as stu left join t_54 sub on stu.s_id = sub.id left join t_53 sch on sub.s_id = sch.id;

select * from v_student_detail;

小结

1、视图是用来提供数据支持的，是由select指令组成的结构

存在结构
不存在数据（数据是使用时调用select指令动态获取数据）

2、视图的目的

方便提供全面数据：可以根据需求组织数据，而实际上不会在数据库产生数据冗余
数据安全：视图本质是来源于数据基表，但是对外可以保护基本的数据结构

2、视图管理

视图管理：对视图结构的管理

视图查看：显示视图结构和具体视图信息

1
2
3

show tables;	# 查看全部视图
show create table/view 视图名字;	# 查看视图创建指令
desc 视图名字;	 # 查看视图结构

视图修改：更改视图逻辑

1
2
3

# 更改视图
alter view 视图名 as 新的查询指令;
create or replace view 视图名 as 新的查询指令;	# 创建新的或者替换新的

视图删除

1	drop view 视图名;

示例

1、查看全部视图和视图明细

1
2
3

show tables;	# 查看全部表，包括视图
desc v_student_detail;	# 查看视图结构
show create view v_student_detail;	# 查看视图创建明细

2、修改视图：重置视图数据逻辑

1
2

alter view v_student_info as select t1.s_name,t2.c_name from t_45 t1 left join t_46 t2 using(c_id);
create or replace view v_student_info as select t1.s_name,t2.c_name from t_45 t1 left join t_46 t2 using(c_id);

3、删除视图

1	drop view v_student_info;

小结

1、视图操作与表操作类似，通常情况下不会经常的去修改维护，而是会在一开始就维护好

2、视图管理可以与表一样对结构进行管理

3、视图数据操作

视图数据操作：直接对视图进行写操作（增删改）然后实现基表数据的变化

视图所有的数据操作都是最终对基表的数据操作
视图操作条件
- 多基表视图：不允许操作（增删改都不行）
- 单基表视图：允许增删改
  - 新增条件：视图的字段必须包含基表中所有不允许为空的字段
- with check option：操作检查规则
  - 默认不需要这个规则（创建视图时指定）：视图操作只要满足前面上述条件即可
  - 增加此规则：视图的数据操作后，必须要保证该视图还能把通过视图操作的数据查出来（否则失败）

步骤

1、根据需求确定需要使用视图

2、确定允许视图进行数据操作（通常用户权限设定，且是单基表视图）

3、确定视图数据的操作是否需要操作检查（有where条件筛选，且只对新增和更新有影响）

需要：增加with check option
不需要

4、使用视图进行数据操作（最终数据写落点是基表）

示例

1、增加一个单表视图和多表视图

1
2
3

create view v_student_1 as select s_id,s_name from t_45;
create view v_student_2 as select s.*,c.c_name from t_45 s left join t_46 c using(c_id);
create or replace view v_student_3 as select * from t_45 where c_id is not null with check option;

2、新增数据

insert into v_student_1 values(null,'student7');	# 正确：视图包含所有必有字段
insert into v_student_2 values(null,'student8',null,null); # 错误：不可插入
insert into v_student_3 values(null,'student8',null);	# 错误：check option，因为第三个字段c_id为NULL，不符合视图筛选条件，查不出来
insert into v_student_3 values(null,'Student9',1);	# 正确

3、更新数据

update v_student_1 set s_name = 'boy' where s_id = 8;
update v_student_2 set s_name = 'boy' where s_id = 7; # 错误：不可修改
update v_student_3 set c_id = null where s_id = 1;	  # 错误：check option，修改后c_id为null，变得不符合视图筛选条件了
update v_student_3 set s_name = 'boy' where s_id = 1; # 正确

4、删除数据

1
2
3

delete from v_student_1 where s_id = 2;
delete from v_student_2 where s_id = 3;	# 错误：不可删除
delete from v_student_3 where s_id = 1;	# 可以删除，说明with check option不影响删除操作

小结

1、视图数据操作一般情况下是不允许的，通常之所以对外提供视图就提供数据的只读操作

2、视图数据操作与视图的基表数量和字段有关

多基表视图不允许任何写操作
单基表视图允许更新和删除、根据情况允许新增（视图包含基表中所有不允许为空字段）

3、with check option是针对有where条件的视图组成有效，需要手动选择是否增加该选项

视图数据的新增、修改后，必须与原来的查询结果是一致的（新增一定要能在视图中看到）
视图数据的删除不受with check option影响
视图数据的新增、修改都是针对当前视图能查出来的，否则既不报错也不生效
with check option还可以更复杂，如果有兴趣可以深入的了解一下

4、视图算法

视图算法：指视图在执行过程中对于内部的select指令的处理方式

视图算法在创建视图时指定

1	create ALGORITHM = 算法 view 视图名字 as select指令;

视图算法一共有三种
- undefined：默认的，未定义算法，即系统自动选择算法
- merge：合并算法，就是将视图外部查询语句跟视图内部select语句合并后执行，效率高（系统优先选择）
- temptable：临时表算法，即系统将视图的select语句查出来先得出一张临时表，然后外部再查询（temptable算法视图不允许写操作）

步骤

1、确定使用视图

2、确定视图算法：考虑视图内部SQL指令中的子句使用情况

3、创建视图并使用视图

示例

1、创建三种不同算法视图

1
2
3

create algorithm = undefined view v_student_4 as select * from t_42 order by age desc;
create algorithm = merge view v_student_5 as select * from t_42 order by age desc;
create algorithm = temptable view v_student_6 as select * from t_42 order by age desc;

2、使用视图：为了体现算法效果，给视图增加分组效果

1
2
3

select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_4 group by class_name;
select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_5 group by class_name;
select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_6 group by class_name;

3、临时表算法的视图不能进行数据插入操作

1	insert into v_student_6 values(null,'冥加','男',100,'神妖1班'); # 错误：不可插入

小结

1、视图算法是用来结合外部外的查询指令的优化思路，主要的优化方式有两种

merge：合并算法，将视图的select与外部select合并成一条，然后执行一次（效率高）
temptable：临时表算法，视图的指令单独执行得到一个二维表，然后外部select再执行（安全）
undefined：未定义算法是一种系统自动选择的算法，系统偏向于选择merge算法

2、一般在设计视图的时候要考虑到视图算法的可行性，通常视图中如果出现了order by排序的话，就要考虑使用temptable算法

只要merge以后，不会导致数据因为子句的先后顺序而混乱（order by与group by的顺序混乱容易出问题）

8.5 数据备份与还原

备份：backup，将数据或者结构按照一定的格式存储到另外一个文件中，以保障阶段数据的完整性和安全性

将当前正确数据进行数据保存
备份通常是有固定的时间节点

还原：restore，在当前数据出问题的情况下，将之前备份的数据替换掉当前数据，保证系统的持续、正确的运行

基于备份进行数据还原
备份还原不一定能够保证所有损失挽回

小结

1、数据的备份与还原是作为一个正常运行的数据库必须做的事情

确保数据的安全
将数据出错的风险降低到最小

2、数据库的备份与还原是作为一个DBA最基本的技术要求（开发者也要会）

1、表数据备份

表数据备份：单独针对表里的数据部分进行备份（数据导出）

将数据从表中查出，按照一定格式存储到外部文件
- 字段格式化：fields
  - terminated by：字段数据结束后使用的符号，默认是空格
  - enclosed by：字段数据包裹，默认什么都没有
  - escaped by：特殊字符的处理，默认是转义
- 行格式化：lines
  - terminated by：行结束符号，默认是\n，自动换行
  - starting by：行开始符号，默认没有

select 字段列表|*  into outfile 外部文件路径 
    [fields terminated by 格式 enclosed by 格式]
    [lines terminated by 格式 starting by 格式]
from 数据表;

表数据备份不限定数据的来源是一张表还是多张表（可以连表）

步骤

1、确定需要对表数据进行导出处理（备份），而且不需要考虑字段名字

2、确定导出的数据的处理

字段处理（可以默认）
行处理（可以默认）

3、执行表数据导出

示例

1、将t_40表的数据按照默认的方式导出到文件

1	select * into outfile 'D:/t_40.csv' from t_40;

如果系统提示：secure-file-priv问题，说明配置没有允许进行文件的导入导出。需要在配置文件里（my.ini）配置好这个配置项：secure-file-priv = 数据导入导出路径/不指定值（重启MySQL生效）

2、将t_40表的数据按照指定格式导出到文件

select name,gender,age,class_name into outfile 'D:/t_40_self.csv'
    fields terminated by '-' enclosed by '"'
    lines starting by 'GO:'
from t_40;

3、多表数据导出：t_45连接t_46表

1	select * into outfile 'D:/t_45_46.csv' from t_45 left join t_46 using(c_id);

小结

1、表数据备份是一种将表中的数据按照一定的格式导出到外部文件进行保存

数据取出后方便进行加工管理
SQL有不同的语法，但是数据的识别是一致的，所以方便进行数据库间的切换

2、表数据备份通常是为了进行数据加工后存入回表中，或者到其他表

3、目前比较少用这种方式进行数据备份

2、表数据还原

表数据还原：将符合数据表结构的数据导入到数据表中（数据导入）

将一定格式的数据按照一定的解析方式解析成符合表字段格式的数据导入到数据表
- 字段处理
- 行处理

load data infile '数据文件所在路径' into table 表名
    [fields terminated by 格式 enclosed by 格式]
    [lines terminated by 格式 starting by 格式]
    [(字段列表)];	# 如果是部分表字段，那么必须将字段列表放到最后

数据文件来源
- 表数据备份的数据文件
- 外部获取或者制作的符合格式的数据

步骤

1、数据文件里的数据满足数据表的字段要求

数据类型
字段对应数（自增长id、可以为空字段除外）

2、数据文件里的数据可以通过字段加工、行加工处理满足表字段要求

3、使用数据导入

示例

1、将t_40.csv数据导入到db_3数据库中的一个与t_40表结构一致的表中

create table t_40 like db_2.t_40;

load data infile 'D:/t_40.csv' into table t_40; # 有可能因为字符集出现问题           
load data infile 'D:/t_40.csv' into table t_40 charset utf8;

注意：数据加载的时候需要注意外部数据的字符集，在加载的时候需要指定字符集为外部文件数据格式，在表后增加字符集charset 外部文件数据字符集

2、将t_40_self文件里的数据导入到db_3.t_40表中

1	load data infile 'D:/t_40_self.csv' into table t_40 charset utf8 fields terminated by '-' enclosed by '"' lines starting by 'GO:' (name,gender,age,class_name) ;

小结

1、表数据还原其实是将外部符合条件的数据，按照一定的格式要求导入到数据表中

2、数据导入可以解决不同格式数据或者不同数据库产品间的数据互相导入到对应数据库产品的问题

3、目前较少使用这种方式进行数据导入：数据的生成应该是业务产生，而不是人工参与（破坏数据的客观有效性，使得数据不真实）

3、文件备份

文件备份：直接对数据表进行文件保留，属于物理备份

文件备份操作简单，直接将数据表（或者数据库文件夹）进行保存迁移
MySQL中不同表存储引擎产生的文件不一致，保存手段也不一致
- InnoDB：表结构文件在ibd文件中，数据和索引存储在外部统一的ibdata文件中(Mysql7以前话是frm后缀)
- MyIsam：每张表的数据、结构和索引都是独立文件，直接找到三个文件迁移即可

步骤

1、设定备份时间节点

2、设定备份文件存储位置

3、确定备份表的存储引擎

4、根据节点进行文件备份：将文件转移（复制）到其他存储位置

示例

1、MyIsam表的文件备份：找到三个文件，复制迁移

sdi：表结构文件
MYI：索引文件
MYD：数据文件

2、InnoDB表的文件备份：找到两个文件，复制迁移

ibd：表结构文件
ibdata：所有InnoDB数据文件

小结

1、文件备份是一种简单粗暴的数据备份方式，是直接将数据文件打包管理的方式

MyIsam存储引擎相对比较适合文件备份，因为MyIsam存储引擎表文件独立，不关联其他表
InnoDB不适合文件备份，因为不管是备份一张表还是全部数据表，都需要备份整个数据存储文件ibdata（适合整库迁移）

2、文件备份方式非常占用磁盘空间

4、文件还原

文件还原：利用备份的文件，替换出现问题的文件，还原到备份前的良好状态

直接将备份的文件放到对应的位置即可
文件还原影响
- MyIsam存储引擎：单表备份，单表还原，不影响其他任何数据
- InnoDB存储引擎：单表结构，整库数据，只适合整库备份还原，否则会影响其他InnoDB存储表

步骤

1、找到出问题的数据文件

MyIsam：表结构、表数据、表索引三个文件（删掉即可）
InnoDB：表结构、整库数据表ibdata（删掉）

2、将备份数据放到相应删除的文件位置

示例

1、MyIsam数据备份表的数据迁移：单表迁移到不同数据库

2、InnoDB数据备份完成整个数据库的迁移（包括数据库用户信息）

小结

1、文件备份的还原通常使用较少

数据备份占用空间大，这种备份方式就少
InnoDB的备份是针对整个数据库里所有InnoDB表，还原会覆盖掉所有不需要还原的表

2、文件备份与还原通常可以在数据迁移的情况下使用

MyIsam：独立表的迁移（现在很少用，myisam很少用）
InnoDB：整个数据库的迁移

5、SQL备份

SQL备份：将数据库的数据以SQL指令的形式保存到文件当中，属于逻辑备份

SQL备份是利用Mysqldump.exe客户端实现备份
SQL备份是将备份目标（数据表）以SQL指令形式，从表的结构、数据和其他信息保存到文件

mysqldump.exe -h -P -u -p [备份选项] 数据库名字 [数据表列表] > SQL文件路径
备份选项很多，常见的主要是数据库的备份多少
- 全库备份：--all-databases 所有数据库的所有表，也不需要指定数据库名字
- 单库备份：[--databases] 数据库 指定数据库里的所有表（后面不要给表名）
- 部分表（单表）备份：数据库名字表1[ 表2...表N]

步骤

1、确定备份的时间：通常是有规则的时间备份

2、确定备份的目标级别：全库、单库、数据表

3、使用mysqldump实现备份

示例

1、全库备份（借助于Windows下的cmd访问mysqldump.exe，当前用户使用root账号）

mysqldump.exe -uroot -proot --all-databases > D:/mysql.sql

2、单库备份

mysqldump -uroot -proot --databases db_2 > D:/db_2.sql

3、单表备份（没有创建数据库的指令）

mysqldump -uroot -proot db_2 t_40 t_42 > D:/t_40_42.sql

小结

1、SQL备份是一般更新频次不高的数据库的常用备份方式

2、SQL备份是将数据表（库）以SQL指令形式进行备份

结构指令：表创建（库创建）
数据指令：insert数据

3、SQL备份能够完成的备份结构和数据，而结构和数据又是独立的，所以比较方便用作备份和还原

SQL备份比较耗费时间和占用性能，建议在闲时进行备份（用户不活跃时）
SQL备份可以根据数据表的重要性进行频次区分备份

6、SQL还原

SQL还原：在需要用到SQL备份数据时，想办法让SQL执行，从而实现备份数据的还原

SQL还原可以使用Mysql.exe进行操作

mysql.exe -h -P -u -p [数据库名字] < SQL文件路径

SQL还原可以在进入到数据库之后利用SQL指令还原

1	source SQL文件路径;

步骤

1、确定数据库（表）需要进行数据还原

数据错乱
数据不完整

2、找到对应节点的SQL备份文件

3、SQL还原

示例

1、使用mysql客户端对db_2的数据文件进行单库还原（通常针对数据库）

mysql.exe -uroot -p < D:/db_2.sql

注意：如果不是库备份，那么需要指定数据库才能执行的

mysql.exe -uroot -p db_2 < D:/t_40_42.sql

2、在进入数据库之后，使用source指令还原SQL备份（通常针对表）

1	source D:/t_40_42.sql;

小结

1、SQL还原是利用SQL备份文件，触发SQL指令执行，从而恢复到指定时间点的结构和数据

2、SQL还原不能百分百保证数据库的数据不受影响

SQL备份通常不具有实时性（一般都会有时间间断）

7、总结

1、数据的备份与还原是作为数据库管理者和使用者都应该掌握的一项技能

保障数据安全
保证系统正常运行
保障公司和客户的利益

2、数据库备份与还原的方式很多，每一种都有自己的特点和适用点，需要我们熟练区分和选择

表数据备份与还原：适用于数据导出和导入，数据具有结构，但是不包含字段和类型
文件备份与还原：简洁方便，但是需要区分存储引擎InnoDB和MyIsam（InnoDB不适合进行文件备份）
SQL备份与还原：不限定存储引擎，随时随地可以备份，不过备份和还原的效率都比较低（完整备份）

3、数据库的备份与还原是一门学问，所以不同的企业、业务都会选择不同的备份策略，也有可能使用交叉策略备份来确保数据的安全，而且一般会将备份文件与运行环境分离开来以确保数据真正的隔离和安全。

8.6 用户管理

1、账号管理

账号管理：根据项目的需求设置和管理账号

账号是权限依赖的对象，先有账号才有权限
MySQL中账号的组成分为两个部分：用户名 @ 主机地址（root@localhost）
- 用户名为用户登录时的名字
- 主机地址：是允许账号所在客户端的访问的客户端IP（如上述root只能在服务器本机通过客户端访问）
账号管理
- 创建账号：create user 用户名@主机地址 identified by '明文密码';
- 删除账号：drop user 用户名@主机地址

步骤

1、根据项目要求创建用户

2、根据项目要求删除用户

示例

1、根据项目情况，跟不同的项目组创建不同的账号

# A团队只允许在公司访问服务器，公司IP为163.177.151.110
create user `admin`@`163.177.151.110` identified by 'admin123';

# B团队不限定负责数据库管理，不限定工作地点
create user `admin` identified by 'admin321';

2、开发任务结束，A团队的任务已经完成，不需要进行数据库操作

1	drop user `admin`@`163.177.151.110`;

小结

1、账号管理是用户管理的基础，但是账号管理也只是用户管理的一部分

账号管理是要考虑数据安全因素划分
账号管理单独应用较少，一般都要配合权限控制
账号管理也是DBA对于数据库管理的一种重要手段：根据项目划分账号
大的项目或者大的数据库服务器上几乎不会给开发使用root账号（权限太大）

2、权限管理

权限管理：对账号进行权限的支持与回收

账号创建之初除了登录是没有其他操作权限的
账号的管理通常需要配合权限的使用
- 赋权：给账号绑定相应的权限 grant 权限列表 on 数据库|*.数据表|* to 用户名@主机地址
- 回收：将账号已有的权限回收 revoke 权限列表 on 数据库|*.数据表|* from 用户名@主机地址
- 刷新权限：flush privileges
- 查看权限：show grants for 用户名@主机地址
MySQL提供的权限列表

Privilege	Grant Table Column	Context
`ALL [PRIVILEGES]`	Synonym for “all privileges”	Server administration
`ALTER`	`Alter_priv`	Tables
`ALTER ROUTINE`	`Alter_routine_priv`	Stored routines
`CREATE`	`Create_priv`	Databases, tables, or indexes
`CREATE ROLE`	`Create_role_priv`	Server administration
`CREATE ROUTINE`	`Create_routine_priv`	Stored routines
`CREATE TABLESPACE`	`Create_tablespace_priv`	Server administration
`CREATE TEMPORARY TABLES`	`Create_tmp_table_priv`	Tables
`CREATE USER`	`Create_user_priv`	Server administration
`CREATE VIEW`	`Create_view_priv`	Views
`DELETE`	`Delete_priv`	Tables
`DROP`	`Drop_priv`	Databases, tables, or views
`DROP ROLE`	`Drop_role_priv`	Server administration
`EVENT`	`Event_priv`	Databases
`EXECUTE`	`Execute_priv`	Stored routines
`FILE`	`File_priv`	File access on server host
`GRANT OPTION`	`Grant_priv`	Databases, tables, or stored routines
`INDEX`	`Index_priv`	Tables
`INSERT`	`Insert_priv`	Tables or columns
`LOCK TABLES`	`Lock_tables_priv`	Databases
`PROCESS`	`Process_priv`	Server administration
`PROXY`	See `proxies_priv` table	Server administration
`REFERENCES`	`References_priv`	Databases or tables
`RELOAD`	`Reload_priv`	Server administration
`REPLICATION CLIENT`	`Repl_client_priv`	Server administration
`REPLICATION SLAVE`	`Repl_slave_priv`	Server administration
`SELECT`	`Select_priv`	Tables or columns
`SHOW DATABASES`	`Show_db_priv`	Server administration
`SHOW VIEW`	`Show_view_priv`	Views
`SHUTDOWN`	`Shutdown_priv`	Server administration
`SUPER`	`Super_priv`	Server administration
`TRIGGER`	`Trigger_priv`	Tables
`UPDATE`	`Update_priv`	Tables or columns
`USAGE`	Synonym for “no privileges”	Server administration

步骤

1、创建新的用户账号

2、根据需求赋予/回收指定数据库（一般整库）或者指定数据表的操作权限

3、刷新权限

示例

1、给用户admin@localhost分配权限：db_2下所有表的所有权限

1 2	create user `admin`@`localhost` identified by '123456'; grant all privileges on db_2.* to `admin`@`localhost`;

2、给用户admin分配权限：db_2下的查看视图权限

grant select on db_2.v_student_1 to `admin1`;
grant select on db_2.v_student_2 to `admin1`;
grant select on db_2.v_student_3 to `admin1`;
grant select on db_2.v_student_4 to `admin1`;
grant select on db_2.v_student_5 to `admin1`;
grant select on db_2.v_student_6 to `admin1`;

3、回收权限

# 如果用户不要了，可以直接删除用户，保留用户不给权限，就回收全部权限
revoke all on db_2.* from `admin`@`localhost`;

# 针对单独授权表的权限回收：只能针对表进行操作
revoke select on db_2.v_student_1 from `admin1`;

小结

1、权限管理是整个用户管理的核心：账号只能让用户能够连接服务器，而权限管理才能给用户提供各类操作

2、权限的操作是根据使用账号的用户需要出发的

DBA用户通常可以分配整个数据库所有库的权限：all on *.*
项目管理级别的用户可以针对所负责项目的权限：all on 数据库.*（多个项目分配多次）
项目开发者用户可以针对所负责项目模块的权限：权限列表 on 数据库.表名/*（如果是跨项目分配多次）
常用的开发者权限有：
- create、alter、drop：库、表结构操作
- insert、select、update、delete：数据操作
- references：外键权限
- index：索引

3、扩展：可以直接使用赋权创建新用户（MySQL7以上不允许这么操作）

1	grant select on db_2.* to `user`@`localhost` with grant option;

3、角色管理

角色管理：role，即根据角色来分配权限，然后用户只需要关联角色即可（分配角色）：Mysql8以后才有的

角色的存在可以更方便的用户维护多个具有相同权限的用户（核心价值）
角色相关操作和语法
- 创建角色：create role 角色名字1[,角色名字2,...角色名字N]（可批量创建）
- 分配权限：grant 权限列表 on 数据库|*.数据表|* to 角色名字
- 绑定角色：grant 角色名字 to 用户名@主机地址
- 撤销角色：revoke 角色名字 from 用户名@主机地址
- 回收角色权限：revoke 权限列表 on 数据库|*.数据表|* from 角色名字
- 删除角色：drop role 角色名字1[,角色名字2,...角色名字N]

步骤

关联角色

1、创建角色

2、确定角色的权限：给角色分配权限

3、将角色分配给用户（和第2步可以没有先后关系）

取关角色

1、权限过大：回收角色权限

2、放弃角色：删除角色

示例

1、创建用户角色，分配给具有同样权限的用户

# 创建角色（角色与用户名很相似）
create role developer,app_read,app_write;

# 给角色分配权限
grant all on db_2.* to developer;
grant select on db_2.* to app_read;
grant insert,update,delete on db_2.* to app_write;

# 创建用户，并分配角色给用户
create user 'admin1'@'%' identified by '1234';
create user 'admin2'@'%' identified by '1234';
create user 'admin3'@'%' identified by '1234';

grant developer to 'admin1'@'%';
grant app_read to 'admin2'@'%','admin1'@'%'; # 允许批量给用户分配角色
grant app_write to 'admin3'@'%';

注意：虽然权限已经最终关联到用户，但是用户并不能真正使用权限，还需要权限分配者每次登陆服务器时激活角色：set default role all to 用户名@主机地址（一次只能激活一个角色）

激活之后对应的用户需要退出之后重新登录才行

2、回收角色权限或者角色

# 回收角色权限
revoke insert,delete on db_2.* from app_write;

# 回收角色
revoke app_read from 'admin2'@'%';

# 删除角色
drop role developer;

小结

1、角色管理是利用角色与权限关联，实现角色批量关联用户

方便权限的重复利用
方便相同权限用户的批量维护

2、角色的使用需要角色创建者（有权限的就行）激活角色才能使用（关联角色的用户需要重新登录才会生效）

9 PHP操作MySQL数据库

9.1 PHP扩展

PHP扩展：PHP中提供了一些PHP本身做不到，但是通过更底层的实现可以帮助PHP解决需求的外部支持

PHP的外部支持在PHP安装目录下的ext文件夹下（extension扩展）
PHP的外部支持通常以dll（Windows下，动态链接库）结尾
PHP需要应用扩展就需要在配置文件中（php.ini）加载对应的动态链接库
- 扩展路径：extension_dir
- 扩展名称：extension

步骤

1、确定需要使用的外部扩展的名字

2、确定外部扩展所在的路径（通常是统一放到ext目录下）

3、修改配置文件

扩展路径（一次性修改）
扩展名称（按需加载）

示例

在PHP中开启MySQLi数据库扩展，允许PHP实现数据库操作

;配置路径
extension_dir = "D:/server/php7/ext"

;开启mysqli扩展，将mysqli前面的注释去掉
extension=php_mysqli

PHP的配置文件已经加载到Apache，要生效，需要重启Apache

小结

1、扩展是PHP用来实现一些复杂功能时所要用到的其他技术体系

扩展在必要时才加载
扩展加载分为两个部分
- 扩展路径
- 扩展名字

2、PHP配置文件的修改要及时的重启服务器才会生效

9.2 MySQLi扩展

MySQLi扩展：PHP提供的一套包含面向过程和面向对象两种方式实现的MySQL数据库操作

PHP加载了MySQLi扩展后，PHP就可以充当MySQL的客户端
- 连接认证服务器
- 发送SQL指令
- 接收SQL执行结果
- 解析结果

步骤

1、测试PHP加载MySQLi是否成功（PHPinfo()函数）

2、使用MySQLi提供的连接认证函数连接认证（mysqli_connect()函数）

3、检查连接信息（mysqli_connect_error()函数）

4、执行SQL操作

新增操作
删除操作
查询操作
修改操作

5、关闭连接（mysqli_close()函数）

示例

PHP操作MySQLi实现数据库的连接认证和关闭

phpinfo();	# 查看PHP是否加载mysqli成功（测试使用，非正式代码）

# 1、连接认证
$conn = @mysqli_connect('localhost:3306','root','root');

# 2、错误检查
if(!$conn) die(mysqli_connect_error());
# iconv函数可以实现字符集转换

# 3、其他操作

# 4、关闭连接
mysqli_close($conn);

小结

1、mysqli提供的扩展里有很多函数可以帮助我们解决相应问题

2、PHP充当了mysql的客户端来操作服务器

连接认证：错误检查
数据操作：错误检查
关闭连接

9.3 MySQLi常用函数

mysqli_connect：连接认证，正确返回连接对象，失败返回false
mysqli_connect_error：连接时错误获取错误信息
mysqli_select_db：选择数据库，失败返回false，成功返回true
mysqli_set_charset：设置客户端字符集
mysqli_query：执行SQL指令，第一个参数为连接对象，第二个参数为SQL指令
- 失败返回false
- 成功
  - 写操作返回true
  - 读操作返回一个结果集对象
mysqli_insert_id：上一步新增操作产生的自增长id
mysqli_affected_rows：上一个操作（写）受影响的行数
mysqli_num_rows：当前结果集对象（读）中记录数
mysqli_fetch_assoc：从当前结果集中查询出一条记录，返回一个关联数组
- 结果集指针与数组指针一样移动
mysqli_fetch_row：从当前结果集中查询出一条记录，返回一个索引数组
mysqli_fetch_all：从结果集中取出所有记录，返回二维数组
mysqli_errno：上述所有操作（读写）出现错误的编号
mysqli_error：上述所有操作出现错误的信息
mysqli_free_result：释放当前查询得到的结果集
mysqli_close：断开当前连接

9.4 新增数据

新增数据：利用mysqli扩展将数据写入到数据库

步骤

1、收集用户数据和新增需求

2、连接认证数据库

3、设置字符集

4、选择数据库

5、组织新增SQL指令，确认是否需要获取自增长id

6、发送给数据库（mysqli_query函数）

7、判定执行指令的执行情况

mysqli_errno：错误编号
mysqli_error：错误信息

8、获取自增长id（确定需要获取），否则返回受影响的行数

9、新增完成，关闭连接

示例

新增一个学生信息入库到db_2表中的t_40表中

# 1、接收数据（通常外部传入，需要验证）
$name = '自来也';
$gender = '男';
$age = 30;
$class_name = '木叶0班';

# 2、连接认证数据库并处理可能出现的错误信息
$conn = @mysqli_connect('localhost','root','root') or die(iconv('gbk','utf-8',mysqli_connect_error()));

# 3、设置字符集
mysqli_set_charset($conn,'utf8') or die(mysqli_error($conn));

# 4、选择数据库
mysqli_select_db($conn,'db_2') or die(mysqli_error($conn));

# 5、组织SQL指令
$sql = "insert into t_40 values(null,'$name','$gender',$age,'$class_name')";
# 确定是否返回自增长id
$auto_id = true;

# 6、执行SQL写入数据库
$res = mysqli_query($conn,$sql) or die(mysqli_error($conn));
# if($res === false) die(mysqli_error($conn));

# 7、返回结果
if(isset($auto_id)) echo mysqli_insert_id($conn);	# 自增长ID
else echo mysqli_affected_rows($conn);				# 受影响的行数

# 8、关闭连接
mysqli_close($conn);

小结

1、新增数据主要是利用mysqli_query执行写操作，然后得出受影响行数，必要时进行自增长id获取（在执行mysqli_query之后）

2、无论何时，我们都需要对进行的操作进行结果验证（凡是涉及到的数据存在外部来源时）

9.5 更新数据

更新数据：利用MySQLi扩展，将已有数据进行更新入库

步骤

1、收集用户更新的数据信息并验证

2、连接认证数据库

3、设置字符集

4、选择数据库

5、组织更新SQL指令

6、发送给数据库（mysqli_query函数）

7、判定执行指令的执行情况

mysqli_errno：错误编号
mysqli_error：错误信息

8、返回受影响的行数

9、更新完成，关闭连接

示例

将所有db_2库中t_40表中所有人的年龄都+1

# 1、接收数据（通常外部传入，需要验证）
# 当前需求来自内部，不需要外部数据

# 2、连接认证数据库并处理可能出现的错误信息
$conn = @mysqli_connect('localhost','root','root') or die(iconv('gbk','utf-8',mysqli_connect_error()));

# 3、设置字符集
mysqli_set_charset($conn,'utf8') or die(mysqli_error($conn));

# 4、选择数据库
mysqli_select_db($conn,'db_2') or dir(mysqli_error($conn));

# 5、组织SQL指令
$sql = "update t_40 set age = age + 1";

# 6、执行SQL写入数据库
$res = mysqli_query($conn,$sql);
if($res === false) die(mysqli_error($conn));

# 7、返回结果
echo mysqli_affected_rows($conn);

# 8、关闭连接
mysqli_close($conn);

小结

1、更新操作通常是用户已有数据上的编辑后提交实现

2、更新操作较少出现全部更新，一般都是个别数据更新或者批量更新，都是需要进行where条件指定的

9.6 删除数据

删除数据：利用MySQLi扩展，将已有数据从数据表删除

步骤

1、收集用户删除的数据信息并验证（通常有权限验证）

2、连接认证数据库

3、设置字符集

4、选择数据库

5、如果有必要，需要验证要删除的数据在数据库是否存在

6、组织删除SQL指令

7、发送给数据库（mysqli_query函数）

8、判定执行指令的执行情况

mysqli_errno：错误编号
mysqli_error：错误信息

9、返回受影响的行数

10、删除完成，关闭连接

示例

删除db_2库里t_45表中没有班级的学生

# 1、接收数据（通常外部传入，需要验证）
# 当前需求来自内部，不需要外部数据

# 2、连接认证数据库并处理可能出现的错误信息
$conn = @mysqli_connect('localhost','root','root') or die(iconv('gbk','utf-8',mysqli_connect_error()));

# 3、设置字符集
mysqli_set_charset($conn,'utf8') or die(mysqli_error($conn));

# 4、选择数据库
mysqli_select_db($conn,'db_2') or dir(mysqli_error($conn));

# 5、组织SQL指令
$sql = "delete from t_45 where c_id is null";

# 6、执行SQL写入数据库
$res = mysqli_query($conn,$sql) or die(mysqli_error($conn));

# 7、返回结果
echo mysqli_affected_rows($conn);;

# 8、关闭连接
mysqli_close($conn);

小结

1、删除操作与更新操作本质是一样的，都是写操作（包括新增），只是业务层面上有一些区别

新增：用户需要提交全部数据（数据最多，无主键，自增长）
更新：用户提交部分更新数据（数据较少，有主键，更新条件）
删除：用户一般是点击或者选择执行（只有主键）

2、很多时候，重要的业务数据一般对用户提供删除操作，但是实际并不删除：设置一个字段，用来记录是否删除（最终删除演变成更新操作）

9.7 查询数据

查询数据：利用MySQLi扩展，从数据库查询出数据，并进行浏览器显示（变成浏览器能够识别的格式）

数据查询逻辑
- mysqli_query将数据查询出来，此时是一个结果集对象（PHP和浏览器都不可识别）
- 利用结果集查询mysqli_fetch系列函数将结果集翻译出来，此时是一个数组（PHP能识别）
- PHP将数组按照指定格式解析到HTML中（浏览器能识别）

步骤

1、根据需求确定要获取的数据来源

2、连接认证数据库

3、设置字符集

4、选择数据库

5、组织查询SQL指令

6、发送给数据库（mysqli_query函数）

7、判定执行指令的执行情况

mysqli_errno：错误编号
mysqli_error：错误信息

8、解析查询结果集

索引数组：mysqli_fetch_row()：不包含字段名
关联数组：mysqli_fetch_assoc()：包含字段名（数组下标：使用较多）

9、释放结果集资源：mysqli_free_result()

10、实现数据输出

11、查询完成，关闭连接

示例

获取db_2库里t_40表中所有学生信息并显示在表格里

# 1、接收数据：查询条件，当前没有条件

# 2、连接认证数据库并处理可能出现的错误信息
$conn = @mysqli_connect('localhost','root','root') or die(iconv('gbk','utf-8',mysqli_connect_error()));

# 3、设置字符集
mysqli_set_charset($conn,'utf8') or die(mysqli_error($conn));

# 4、选择数据库
mysqli_select_db($conn,'db_2') or dir(mysqli_error($conn));

# 5、组织SQL指令
$sql = "select * from t_40";

# 6、执行SQL查出所有数据
$res = mysqli_query($conn,$sql);
if($res === false) die(mysqli_error($conn));

# 7、解析结果
$one = mysqli_fetch_assoc($res);	# 取出一条记录
                                                          
# 取出全部：循环
$lists = [];
while($one = mysqli_fetch_assoc($res)){
    $lists[] = $one;
}

# 8、释放结果集资源
mysqli_free_result($res);
                                                          
# 9、关闭连接
mysqli_close($conn);
                                                          
# 10、输出表格
echo '<table>';
echo '<tr><td>id</td><td>姓名</td><td>性别</td><td>年龄</td><td>班级</td></tr>';
foreach($lists as $one){
    echo "<tr><td>{$one['id']}</td><td>{$one['name']}</td><td>{$one['gender']}</td><td>{$one['age']}</td><td>{$one['class_name']}</td></tr>";
}
echo '</table>';

小结

1、查询操作是数据库操作最常见的操作

查询得到的结果不能直接被PHP接续，是一种结果集
结果集需要通过MySQLi提供的函数进行解析，取出里面的数据变成PHP可以理解的数据

9.8 总结

1、PHP要操作数据库是通过扩展实现的，MySQLi扩展是目前通用的一种面向过程的MySQL扩展（也支持面向对象）

2、扩展的使用本质是使用扩展里提供的函数来帮助我们解决需求问题

连接认证数据库：mysqli_connect
设置字符集：mysqli_set_charset
选择数据库：mysqli_select_db
执行SQL指令：mysqli_query
判定执行结果，获取错误信息：mysqli_errno/mysqli_error
新增获取自增长id：mysqli_insert_id
写操作获取受影响的行数：mysqli_affected_rows
解析查询结果：mysqli_fetch_assoc/mysqli_fetch_row
释放结果集资源：mysqli_free_result
关闭连接资源：mysqli_close

3、不管是写操作还是读操作，每一次SQL操作有一些过程是相同的，应该进行封装实现代码复用

数据库的连接认证以及错误处理（包括字符集和数据库选择）

# 成功返回连接，失败返回false
function connect(&$error,$username,$password,$dbname,$host = 'localhost',$port = '3306',$charset = 'utf8'){
    # 连接认证
    $conn = @mysqli_connect($host,$username,$password,$dbname,$port);
    
    # 判定：连接失败，将错误信息记录下来，并返回false
    if(!$conn) {
        $error = iconv('gbk','utf-8',mysqli_connect_error());
        return false;
    }
    
    # 设置字符集
    if(!mysqli_set_charset($conn,$charset)){
        $error = mysqli_error($conn);
        return false;
    }
    
    # 返回正确结果
    return $conn;
}

SQL的执行和错误检测，以及错误处理

# SQL执行，返回执行结果，执行失败返回false，并记录错误信息
function execute($conn,$sql,&$error){
    $res = mysqli_query($conn,$sql);
    
    # 执行失败
    if(!$res)
    {
        $error = mysqli_error($conn);
        return false;
    }
    
    # 返回执行结果
    return $res;
}

数据的查询操作（一条记录和多条记录）

# 查询：分为一条记录查询或者多条记录查询
function read($conn,$sql,&$error,$all = false){
    # 调用执行函数
    $res = execute($conn,$sql,$error);
    
    # 判定结果
    if(!$res){
        return false;
    }
    
    # 解析结果
    if($all){
        # 获取全部结果
        $lists = [];
        
        while($row = mysqli_fetch_assoc($res)){
            $lists[] = $res;
        }
        return $lists;
    }else{
        # 获取一条结果
        return mysqli_fetch_assoc($res);
    }  
}

写操作封装

# 写操作：考虑是否需要获取自增长id
function write($conn,$sql,&$error,$insert = false){
    # 默认操作为删改操作（不需要自增长ID的操作）
    $res = execute($conn,$sql,$error);
    
    # 判定结果
    if(!$res){
        return false;
    }
    
    # 针对用户需求判定
    if($insert) return mysqli_insert_id($conn);
    else return mysqli_affected_rows($conn);
}

10 新闻管理系统

10.1 表单传值

1、表单传值

表单传值：通过HTML表单，实现数据在浏览器端录入，并传递给后端语言

表单传值是一种数据的传递方式
表单传值有多种，但是在网站应用里通常使用两种
- GET传值
- POST传值
表单传值给服务器做动态数据支撑提供了必要条件
- 数据可以从浏览器端存储到服务器（数据新增）
- 数据可以通过浏览器让服务器进行数据筛选（数据查询）
表单传值需要用到表单元素
- form表单：用来包裹多个表单元素一并提交
- input表单元素
  - text：文本数据
  - password：密文数据
  - textarea：长文本数据
  - hidden：隐藏数据
  - submit：提交按钮
- 下拉框表单：select
- 按钮选择
  - radio：单选框（互斥选框）
  - CheckBox：多选框
- url传值
  - a标签：直接在请求链接后使用 ? + 数据

步骤

1、确定浏览器传值给服务器

2、选择传值的方式

url传值：a标签
form表单传值：表单元素

示例

1、url传值

<html>
    <body>
        <a href="login.php?username=XXX">登录</a>
    </body>
</html>

2、表单传值

<html>
   	<body>
        <form action="#提交对象：login.php" method="GET/POST">
            用户名：<input type="text" name="username">
            密码：<input type="password" name="password">
            <input type="submit" value="登录">
        </form>
    </body>
</html>

小结

1、表单传值是利用HTML里的表单元素收集用户数据，或者是提供url绑定数据

form表单：通常是用户提交写入的数据
url绑定：通常是系统提供的数据，然后让用户点击操作

2、表单传值打通了用户与服务器的数据关系，允许用户将自己的数据提交给服务器，从而有了服务器的可操作空间

2、POST传值

POST传值：通过form表单使用post方式，将数据从浏览器以肉眼不可见形式传输给服务器

POST传值必须使用form表单
- form表单的属性method的值为post（不区分大小写）
- 所有需要提交给服务器的数据元素都必须在form表单内部
要提交的表单元素本身必须拥有name属性（无name属性无法提交）

步骤

1、form表单method属性使用post

2、根据需要提交的数据的数量和类型选择合适的form表单元素

示例

1、提交用户登录信息：用户名和密码信息，提交给login.php处理

<html>
   	<body>
        <form action="login.php" method="post">
            用户名：<input type="text" name="username">
            密码：<input type="password" name="password">
            <input type="submit" name="submit" value="登录">
        </form>
    </body>
</html>

2、提交新闻信息入库：标题、作者、内容

<html>
   	<body>
        <form action="insert.php" method="post">
            标题：<input type="text" name="title">
            作者：<input type="text" name="author">
            内容：<textarea name="content"></textarea>
            <input type="submit" value="提交">
        </form>
    </body>
</html>

小结

1、post提交数据是基于form表单，只能通过form表单实现

2、post提交的数据不会在浏览器的地址栏出现，是为了让数据的安全性增加

3、post的原始意义在于数据的提交入库：也就是说通过post提交的数据通常是要进行数据库的写操作的（安全要求除外）

3、GET传值

GET传值：将浏览器的数据以肉眼可见的形式提交给服务器

GET传值可以通过form表单实现也可以通过url直接实现
- form表单：method属性使用GET（要提交的元素必须有name属性）
- URL：直接在a标签href属性里增加要传递的数据
GET传值的数据可以在浏览器地址栏里看到

步骤

1、确定使用GET方式传递数据

2、选择合适的传值方式

form表单method为GET提交多个数据
a标签href属性提交数据：index.php?name=value&name=value…

示例

1、使用form表单提交检索数据

<html>
    <body>
        <form action="check.php" action="get">
            商品名字：<input type="text" name="goods_name">
            商品颜色：<select name="goods_color">
                <option value=1>红色</option>
                <option value=2>绿色</option>
                <option value=3>蓝色</option>
            </select>
        	<input type="submit" value="查询">
        </form>
    </body>
</html>

2、使用url传值：要删除的数据（新闻）

<html>
    <body>
        <ul>
            <li><a href="delete.php?id=1">太平洋竟然漏水</a></li>
            <li><a href="delete.php?id=2">珠穆朗玛峰长高了</a></li>
            <li><a href="delete.php?id=3">白令海峡结冰了</a></li>
            <li><a href="delete.php?id=4">北极熊去南极找企鹅</a></li>
            <li><a href="delete.php?id=5">百慕大又双叕沉船</a></li>
        </ul>
    </body>
</html>

小结

1、GET传值是会让用户能够直接在地址栏里看到具体的数据信息的

2、GET传值的目的，通常是为了将数据提交给服务器用户数据的查询操作（提交的数据通常是查询条件）

3、GET不会传输特别重要的数据信息

4、PHP接收表单数据

PHP接收表单数据：PHP将用户通过表单传递的数据变成PHP可识别的数据和可操作的方式

PHP接收数据的系统行为，自动接收（PHP语言为了开发者使用方便）
PHP接收数据有三种方式，每一种方式都是表单元素的name属性的值作为数组元素下标，具体数据作为数组元素的值
- $_GET：接收浏览器GET方式传递的数据
- $_POST：接收浏览器POST方式传递的数据
- $_REQUEST：接收浏览器GET和POST提交的数据
  - POST与GET如果有同名表单（名字），POST会覆盖GET

步骤

1、明确浏览器数据提交的方式：开发者后台定义后，不受浏览器端用户控制

2、选择合适的PHP接收方式

3、在浏览器请求的PHP脚本中使用PHP接收指令

4、通过数组下标（表单元素名字）访问数据（提交的值）

示例

1、接收用户登录提交的信息：login.php

# 用户是POST提交，所以应该使用$_POST或者$_REQUEST接收
var_dump($_GET,$_POST,$_REQUEST);

# 取出数据加工
$username = $_POST['username'];
$password = $_POST['password'];

2、接收用户的删除信息：delete.php

$id = $_GET['id'];

# 安全接收方式
$id = isset($_GET['id']) ? intval($_GET['id']) : 0;
# isset判定是为了让脚本在没有真正接到数据时不会报错，从而不友好
# intval是因为id通常是逻辑的，需要加工成整型，这样可以防止外部进行SQL攻击
# 默认值0是为了方便后续进行判定，因为逻辑主键不会存在0的

小结

1、PHP提供了三种方式接收用户提交的普通表单数据

$_GET：接收浏览器GET方式传递的数据
$_POST：接收浏览器POST方式传递的数据
$_REQUEST：接收浏览器GET和POST提交的数据（因为覆盖所以不安全）

2、接收数据时要考虑到数据的安全性

恶意访问：isset进行存在性判定
数据乱写：类型转换保证数据安全
数据判定：保证数据的可靠性再进行服务器其他操作（提升服务器的有效工作效率）

10.2 新闻管理系统

1、数据库设计

数据库设计：根据要做的系统分析出内部存在的实体以及实体间的联系，然后创建对应的表来实现需求里复杂的关系

步骤

1、分析找出需求中存在的实体（确定表数量）

2、找出实体本身应该存在的信息（确定表内在属性）

3、找出实体间的关联关系（确定表关系）

4、必要时确定表关系的约束性（外键、唯一键等）

5、创建数据库

示例

1、一个新闻管理系统应该包含的信息如下

新闻发布者：作者author
新闻本身：news
新闻分类：category
发布者来源：新闻平台platform
评论信息：comment
用户管理：user

简易新闻管理系统：作者、新闻

2、确定试题内在联系（简易新闻管理系统）

作者：作者id、名字、所属平台
新闻：新闻id，标题、内容、发布时间

3、确定实体间的关联关系（简易新闻管理系统）

新闻：作者id

4、创建数据库

# 数据库
create database news charset utf8;

# 创建数据表
create table news.author(
    id int primary key auto_increment,
    name varchar(50) not null,
    platform varchar(50) comment '发布平台'
)charset utf8;
insert into news.author values(null,'佚名','佚名自媒体'),
(null,'留白','海上自媒体'),(null,'花田错','音乐自媒体');

create table news.news(
    id int primary key auto_increment,
    title varchar(50) not null comment '新闻标题',
    content text not null comment '新闻内容',
    a_id int unsigned not null comment '作者id',
    publish int unsigned not null comment '发布时间'
)charset utf8;

小结

1、根据需求设计数据库实际上是一个经验活，需要做的多之后才知道具体该怎么划分和设计，但是总的步骤是不变的

确定实体数量（表）
确定实体内在关联（字段）
确定实体间关系（外联）
设置数据库

2、数据库的设计就是看上去平台可能不大，但是实际涉及的实体数量和字段却很多。大型项目的实体数量和字段数量是非常多的

2、新增新闻

新增新闻：用户通过新增表单实现新闻入库

步骤

1、确定访问新增表单的url：用户直接访问（add.php）

获取作者信息
PHP加载HTML文件进行表单展示

2、确定表单的提交方式和提交对象

method：post提交
action：将来处理新闻新增的PHP文件：insert.php
显示作者信息

3、PHP接收用户提交的数据

安全接收
数据逻辑验证

4、PHP操作数据库

连接认证（选择数据库）
字符集设置
安全验证

5、组织SQL入库

组织SQL指令
执行SQL指令
安全验证SQL执行

6、提示操作结果

正确：跳转到列表页
错误：回到新增页面

实现

1、创建add.php，然后加载添加新闻的表单

1 2	# 包含模板文件 include 'news_add.html';

2、修改资源：静态资源（js、css）和表单资源

<form action="insert.php" method="post">
    <div class="form-group">
        <label for="news_title">新闻标题</label>
        <input type="text" name="title" class="form-control" id='news_title'>
    </div>
    
    <div class="form-group">
        <label for="news_author">作者</label>
        <select name="author">
            <option value="a_id">作者名字</option>
        </select>
    </div>

    <div class="form-group">
        <label for="news_content">新闻内容</label>
        <textarea class="form-control" name="content" rows="12"></textarea>
    </div>

    <button class="btn btn-primary btn-block">添加</button>
</form>

3、在add.php中，加载新增表单之前要获取所有作者信息（假设作者是注册的）

# 连接认证
$conn = @mysqli_connect('localhost','root','root','news','3306') or die(mysqli_connect_error());

# 设置字符集
mysqli_set_charset($conn,'utf8') or die(mysqli_error($conn));

# 获取所有数据
$res = mysqli_query($conn,'select id,name from author') or die(mysqli_error($conn));

# 取出数据
$authors = [];
while($row = mysqli_fetch_assoc($res)){
    $authors[] = $row;
}

# 包含模板文件
include 'news_add.html';

4、在模板中显示所有的作者信息

<form action="insert.php" method="post">
    <div class="form-group">
        <label for="news_title">新闻标题</label>
        <input type="text" name="title" class="form-control" id='news_title'>
    </div>
    
    <!--正常不用增加这一栏，一般需要用户登录才能发表-->
    <div class="form-group">
        <label for="news_author">作者</label>
        <input type="text" name="author" class="form-control" id='news_author'>
        <select name="author">
            <?php foreach($authors as $author):?>
            <option value="<?php echo $author['id'];?>"><?php echo $author['name'];?></option>
            <?php endforeach;?>
        </select>
    </div>

    <div class="form-group">
        <label for="news_content">新闻内容</label>
        <textarea class="form-control" name="content" rows="12"></textarea>
    </div>

    <button class="btn btn-primary btn-block">添加</button>
</form>

5、创建insert.php接收数据

# 接收数据
$title = $_POST['title'] ?? '';
$content = $_POST['content'] ?? '';
$a_id = $_POST['author'] ?? 0;

# 安全判定（简单判定）
if(empty($title) || empty($content)){
    # 错误跳转重来
    header('refresh:3;url=add.php');
    echo '新闻标题和内容都不能为空！';
    exit;
}

6、连接认证数据库

# 连接认证
$conn = @mysqli_connect('localhost','root','root','news','3306') or die(mysqli_connect_error());

# 设置字符集
mysqli_set_charset($conn,'utf8') or die(mysqli_error($conn));

7、组织数据入库

# 数据入库：时间戳可以使用mysql自动生成，也可以使用PHP生成好放进去（建议生成好）
$publish = time();
$sql = "insert into news values(null,'{$title}','{$content}',{$a_id},{$publish})";
$res = mysqli_query($conn,$sql);

8、判定结果，然后处理

正确跳转到列表页（首页）：index.php
失败回到新增页面：add.php

# 判定数据
if($res){
    header('refresh:2;url=index.php');
    echo '新闻：' . $title . ' 新增成功！';
}else{
    header('refresh:3;url=add.php');
    echo '新闻：' . $title . '新增失败！';
}

# 最后可以不用使用exit终止脚本了

小结

1、新增的核心逻辑是分成两个部分的

提供新增表单：让用户可以提供数据
提供新增处理：接收用户数据后安全入库

2、表单页面通常属于HTML文件，一般情况下，我们都不会让浏览器直接访问HTML文件，而是访问对应的PHP文件，由PHP去选择是加载HTML文件还是进行其他逻辑处理

3、新增要考虑成功后的逻辑

成功后去到列表：提示成功即可，也不需要获取成功操作的其他数据
成功后去到查看详情页：需要获取到当前操作成功的id，然后带着id去请求另外一个实现详情页的文件（PHP文件）

3、封装

封装：将需要重复使用的代码或者某些特定的功能使用函数进行管理

根据业务需求对操作进行封装，后续只需要直接对文件进行包含，然后对函数调用即可
业务封装主要是根据数据库的操作需求实现各类封装
- 初始化
- 数据新增
- 数据编辑（更新和删除）
- 数据查询

步骤

1、初始化封装：连接认证、数据库选择、字符集设置

2、SQL指令执行封装：错误处理

3、自动更新封装：提供数据自动构造更新指令，实现更新

4、自动查询操作：提供查询条件，自动构造查询指令，实现查询

5、普通查询：用户自己组装SQL指令

实现

1、初始化封装（Sql.php）

# 初始化功能：连接认证、选择数据库、设定字符集
# 成功返回连接对象，失败返回false，错误记录在错误参数中
function connect($user,$pass,$dbname,&$error,$host = 'localhost',$port = '3306',$charset = 'utf8'){
    # 连接认证
    $conn = @mysqli_connect($host,$user,$pass,$dbname,$port);
    
    # 验证错误
    if(!$conn){
        $error = iconv('gbk','utf-8',mysqli_connect_error());
        return false;
    }
    
    # 设置字符集
    if(!mysqli_set_charset($conn,$charset)){
        $error = mysqli_error($conn);
        return false;
    }
    
    # 返回对象
    return $conn;
}

2、SQL指令执行封装

# 外部传入SQL，负责执行也验证SQL语法问题，成功返回结果，失败返回false，错误记录在错误参数中
function query($conn,$sql,&$error){
    # 执行SQL
    $res = mysqli_query($conn,$sql);
    
    # 判定
    if($res === false){
        $error = mysqli_error($conn);
        return false;
    }
    
    # 返回执行的正确结果
    return $res;
}

以上操作也是普通的写操作

3、简易自动更新

# 用户提供要更新的数据和主键id，自动组装SQL
# 成功返回受影响的行数，失败返回false（0表示没有更新）
function auto_update($conn,$data,$table,&$error,$id = 0){
    # 组织更新部分数据：字段名 = 值
    $set = '';
    foreach($data as $k => $v){
        $set .= $k . "='{$v}',";
    } # title = 'title',content = 'content',
    
  
    # 清除多余的右侧逗号
    $set = rtrim($set,',');	# $set : title = 'title',content = 'content'
    
    # 组织更新指令
    $sql = "update {$table} set {$set} ";
    
    # 组装where条件（id不为0才组织）：要求主键字段名字必须为ID
    if($id) $sql .= ' where id = ' . $id;	
    # update 表名 set title = 'title',content = 'content' where id = 1
    
    # 执行
    if(query($conn,$sql,$error))
        return mysqli_affected_rows($conn);
    else return false;
}

4、简易自动查询

# 系统提供查询条件（只能是=比较和and逻辑运算），可以查询一条记录或者多条记录
# 成功返回数组（多条二维数组，一条一维数组）失败返回false，错误记录在参数中
function auto_read($conn,$table,&$error,$where = [],$all = false){
    # 组装查询条件：默认永远为真
    $where_clause = ' where 1 ';	# where 1
    if($where){		# 空数组自动转换成布尔false
        # 解析条件
        foreach($where as $k => $v){
            $where_clause .= ' and ' . $k . " = '$v' ";
        }  # where 1 and title = 'news' ...      
    }
    
    # 组织完整SQL
    $sql = "select * from {$table} {$where_clause}";
    $res = query($conn,$sql,$error);
    
    # 判定执行结果
    if($res === false) return $res;
    
    # 判定获取一条还是多条
    $lists = [];
    if($all){
        # 获取多条，二维数组存储
        while($row = mysqli_fetch_assoc($res)){
            $lists[] = $row;
        }
    }else{
        # 获取一条，一维数组存储
        $lists = mysqli_fetch_assoc($res);
    }
    
    # 释放资源，返回结果
    mysqli_free_result($res);
    return $lists;
}

5、普通查询

# 用户提供SQL指令，可以查询一条或者多条记录
function read($conn,$sql,&$error,$all = false){
    # 执行SQL，并判定结果
    $res = query($conn,$sql,$error);
    if($res === false) return false;
    
    # 解析结果集
    $lists = [];
    if($all){
        # 多条数据：二维数组
        while($row = mysqli_fetch_assoc($res)){
            $lists[] = $row;
        }
    }else{
        # 一条数据：一维数组
        $lists = mysqli_fetch_assoc($res);
    }
    
    # 释放资源，返回结果
    mysqli_free_result($res);
    return $lists;;
}

小结

1、封装是为了代码能够更好的实现复用

复用性
灵活性

2、封装需要考虑封装事务的独立性，不要试图用一个函数去解决一个很大的问题

4、新闻列表

新闻列表：将新闻信息从数据库取出，然后在模板中显示

步骤

1、新增列表处理文件：index.php

2、在PHP中根据模板显示的要求，获取相应的数据

3、PHP加载显示数据的模板

4、在模板中使用PHP输出相应的数据

示例

1、创建首页（列表页）处理脚本index.php

# 显示新闻列表

2、根据模板显示要求获取数据信息：新闻id、标题、内容、作者名字、发布时间

# 加载封装的数据库操作文件
include 'sql.php';

# 完成初始化操作
$conn = connect('root','root','news',$error);
# 判定结果
if(!$conn) die($error);

# 组织数据SQL，获取数据（手动组织：需要连表）
$sql = "select n.*,a.name from news n left join author a on n.a_id = a.id";
$news = read($conn,$sql,$error,true);

3、加载模板

1 2	# 加载首页模板 include 'news_index.html'; # 注意路径

4、在模板中对应位置显示对应的数据

<?php foreach($news as $n):?>
    <!-- 列表项开始 -->
    <div class="news-list-item">
        <!-- 作者以及发布时间 -->
        <div class="author-time"> <span><?php echo $n['name'];?></span> 发表于 <span><?php echo date('Y-m-d H:i:s',$n['publish']);?></span> </div>
        <!-- 新闻详情 -->
        <div class="news-des">
        	<!-- 新闻标题 -->
            <h3 class="news-title" style='float: left;'>
            	<i></i><a href="02-新闻详情页.html"><?php echo $n['title'];?></a>
            </h3>

         	<!-- 编辑,删除 -->
            <div class="operate">
            	<a href="04-新闻修改页.html">编辑</a>&nbsp;&nbsp;<a href="#" onclick="return confirm('你确认要删除本条新闻吗?')">删除</a>
            </div>

            <div style='clear:both'></div>

            <!-- 新闻内容 -->
            <div class="news-content-des ellipsis"><?php echo $n['content'];?></div>
        </div>
    </div>
    <!-- 列表项结束 -->
<?php endforeach;?>

小结

1、显示数据的核心逻辑是获取数据、显示数据

5、编辑新闻

编辑新闻：将已有的新闻取出来，然后再次进行加工操作，并提交更新到服务器

步骤

1、提供操作链接：编辑通常是针对单条记录，需要在列表页针对每一条记录制作对应的编辑链接

2、后台新增接收编辑的PHP脚本：edit.php

接收要编辑的新闻信息
从数据库取出要编辑的新闻
加载编辑模板（表单）
显示编辑取出来的新闻

3、用户编辑数据后提交到update.php

接收用户提交的信息
- 被编辑的内容（要入库）
- 不可编辑的内容（条件匹配）
数据安全性验证
组织更新指令更新入库（自动更新）

4、根据执行结果提示用户操作信息并进行跳转

示例

1、修改列表页，增加编辑链接：需要传入要编辑的新闻id

<!-- 编辑,删除 -->
<div class="operate">
    <a href="edit.php?id=<?php echo $n['id'];?>">编辑</a>&nbsp;&nbsp;<a href="#" onclick="return confirm('你确认要删除本条新闻吗?')">删除</a>
</div>

2、增加edit.php脚本，实现新闻的获取

# 接收要获取的新闻id
$id = $_GET['id'] ?? 0;
if(!$id) {
    header("Refresh:3;url={$_SERVER['HTTP_REFERER']}");
    echo '当前要编辑的新闻不存在！';
    exit;
}

# 获取新闻数据
include 'sql.php';

$conn = connect('root','root','news',$error);
# 判定结果
if(!$conn) {
    header("Refresh:3;url=index.php"); 
    echo $error;
    exit;
}

# 自动查询
$news = auto_read($conn,'news',$error,['id' => $id]);
if(!$news){
    header("Refresh:3;url=index.php");
    echo '当前要编辑的新闻不存在！';
    exit;
}

3、加载模板

1 2	# 加载模板 include 'news_edit.html';

4、在模板中显示要编辑的数据：id必须传，但是需要隐藏

<form action="update.php" method="post">
    <input type="hidden" name="id" value="<?php echo $news['id'];?>">
    <div class="form-group">
                 <label for="news_title">新闻标题</label>
                 <input type="text" name="title" class="form-control" id='news_title' value='<?php echo $news['title'];?>'>
             </div>

             <div class="form-group">
                 <label for="news_content">新闻内容</label>
                 <textarea class="form-control" name="content" rows="12"><?php echo $news['content']?></textarea>
             </div>
             <button class="btn btn-danger btn-block">修改</button>
</form>

5、后台增加update.php 用于处理用户提交的表单

# 接收数据：数组接收可能修改的数据（下标与表字段名一致）
$id = $_POST['id'];
$data['title'] 	= $_POST['title'] ?? '';
$data['content']= $_POST['content'] ?? '';

# 判定
if(empty($data['title']) || empty($data['content'])){
    header('Refresh:3;url=edit.php?id=' . $id);
    echo '标题和内容都不能为空！';
    exit;
}

# 自动更新
include 'sql.php';
$conn = connect('root','root','news',$error);
# 判定结果
if(!$conn) {
    header("Refresh:3;url=index.php"); 
    echo $error;
    exit;
}

$res = auto_update($conn,$data,'news',$error,$id);

6、根据更新结果进行跳转

# 结果判定
if($res){
    header('Refresh:3;url=detail.php?id='.$id);
    echo '更新成功！';
    exit;
}else{
    # 是没有要更新的数据
    header('Refresh:3;url=index.php');
    echo '没有要更新的数据！';
    exit;
}

小结

1、编辑操作是所有增删改查里步骤最复杂的一个

6、删除新闻

删除新闻：对已有新闻从数据库指定移出

步骤

1、提供操作链接：删除通常是针对单条记录，需要在列表页针对每一条记录制作对应的删除链接（需要明确要删除的数据的参数）

2、后台新增实现删除的PHP脚本：delete.php

接收要删除的记录参数（通常是主键）
必要时进行数据安全验证
组织SQL指令执行删除操作

3、根据删除结果进行跳转提示

示例

1、修改列表页，增加删除链接：需要传入要删除的新闻id

<!-- 编辑,删除 -->
<div class="operate">
    <a href="edit.php?id=<?php echo $n['id'];?>">编辑</a>&nbsp;&nbsp;<a href="delete.php?id=<?php echo $n['id'];?>" onclick="return confirm('你确认要删除本条新闻吗?')">删除</a>
</div>

一般删除会做一个前端安全限定：js的确认验证

2、后台增加一个delete.php实现数据的删除操作

$id = $_GET['id'] ?? 0;
if(!$id){
    header('Refresh:3;url=index.php');
    echo '当前要删除的新闻不存在！';
    exit;
}

# 实现数据库操作
include 'sql.php';
$conn = connect('root','root','news',$error);
# 判定结果
if(!$conn) {
    header("Refresh:3;url=index.php"); 
    echo $error;
    exit;
}

# 删除操作
$sql = "delete from news where id = {$id}";
$res = query($conn,$sql,$error);

# 判定下执行结果
if($res)
    $res = mysqli_affected_rows($connn);
else{
    header("Refresh:3;url=index.php"); 
    echo $error;
    exit;
}

if($res){
    header("Refresh:3;url=index.php"); 
    echo '删除成功！';
    exit;
}else{
    header("Refresh:3;url=index.php"); 
    echo '删除失败！';
    exit;
}

小结

1、删除操作是增删改查里本质最简单的一种业务，但是实际开发中删除操作却是非常谨慎的

删除不可逆
数据的删除不利于大数据分析

2、实际实现中，一般的业务数据可以删除，但是比较重要的数据虽然给用户的感觉是删除，但是实际上数据库里并不会删除

数据表增加字段记录状态
数据查询时根据状态来进行数据筛选
数据删除时只要改变状态不让普通查询可查即可

7、查看新闻

查看新闻：用户通过点击新闻标题查看新闻详细信息

步骤

1、找到列表页中出现新闻标题的位置

2、给每一个标题增加一个访问链接：后台处理文件detail.php

3、给每个请求追加具体新闻信息id

4、后台detail.php接收要查看的新闻数据：id

5、从数据库查询新闻信息：按模板显示需求

6、加载显示新闻详情的模板

7、显示数据

示例

1、显示新闻详情

在新闻列表页增加标题点击链接，请求detail.php

<!-- 新闻标题 -->
<h3 class="news-title" style='float: left;'>
    <i></i><a href="detail.php?id=<?php echo $n['id'];?>"><?php echo $n['title'];?></a>
</h3>

在后台增加detail.php获取新闻信息，加载新闻详情模板

# 接收新闻id
$id = $_GET['id'] ?? 0;
if(!$id){
    header('Refresh:3;url=index.php');
    echo '非法访问！';
    exit;
}

# 获取新闻数据
include 'Sql.php';

$conn = connect('root','root','news',$error);
# 判定结果
if(!$conn){
    header('Refresh:3;url=index.php');
    echo $error;
    exit;
}

# 查询数据
$sql = "select n.*,a.name from news n left join author a on n.a_id = a.id where n.id = {$id}";
$news = read($conn,$sql,$error);
if(!$news){
    header('Refresh:3;url=index.php');
    echo '您所访问的新闻不存在！';
    exit;
}

# 加载模板
include 'news_detail.html';

在模板中显示新闻信息

<div class="news-list-item" style='border-bottom: none;'>
    <!-- 新闻标题 -->
    <h3 class="news-title"><?php echo $news['title'];?></h3> 
    <!-- 作者以及发布时间 -->
    <div class="author-time"> <span><?php echo $news['name']?></span> 发表于 <span><?php echo date('Y-m-d H:i:s',$news['publish']);?></span> </div<!-- 新闻详情 -->
    <div class="news-des">
                    	
        <!-- 新闻内容 -->
        <div class="news-content-des"><?php echo $news['content']?></div>
    </div>
</div>

小结

1、显示详情的话通常是通过列表进入，然后给出点击链接实现

2、数据显示往往比较简单：保证数据查询是到位的

8、分页

分页：将数据按照一种类似于页码的逻辑呈现，用户通过不同页码看到的数据不同

分页里有几个核心需要关注的数据
- 总记录数：决定页码数量的有效记录数
- 每页显示量：每一个页面里要显示的记录数
- 总页数：总记录数 / 每页显示量
- 当前页码：进行数据筛选的最关键标志
分页的逻辑有两大类
- 服务器数据分页：服务器在获取数据时，只获取当前用户需要页面的数据
  - 减少服务器压力
  - 减少网络传输延迟
- JS脚本分页：服务器不管分页，一次性提交所有数据给浏览器，JS通过脚本控制每次显示的数据量
  - 减少请求次数
  - 减少用户分页点击时等待时间
服务器端实现分页，通常是通过数据库的limit来实现

1 2	limit 0,3; # 显示前3条 limit 3,6; # 显示第二个3条

分页的效果通常是浏览器上给用户提供一排页码点击链接，用户点击哪个就访问对应页面的数据

<div class="page">
    <a href="#">首页</a>
    <a href="#">上一页</a>
    <a href="#">1</a>
    <a href="#">2</a>
    <a href="#" class='current'>3</a>
    <a href="#">4</a>
    <a href="#">5</a>
    <a href="#">下一页</a>
    <a href="#">末页</a>
</div>

步骤

1、确定分页模式（通常使用数据库分页）

2、确定分页效果：参照京东分页逻辑

有上一页和下一页
- 如果已经是第一页：那么上一页不可用（或者没有）
- 如果已经是最后一页：那么下一页不可用（或者没有）
一共最多显示7个可点击数字页面
- 如果总页数小于等于7页：显示所有页码：1，2，3，4，5，6，7
- 如果总页数大于7页，显示当前页码左右连贯5页（左右各两页）
  - 当前页码小于等于5：显示前7页，最后增加一个...表示有更多页码：1，2，3，4，5，6，7，…
  - 当前页码大于5页：显示前2页（1和2），然后跟...，然后显示当前页连贯5页：1，2，…
    - 如果当前页码已经属于最后3页内，显示最后5页数据：1，2，…，6，7，8，9，10
    - 如果当前页码小于最后3页，那么最后增加一个...表示有更多页码：1，2，…，5，6，7，8，9，…

3、确定分页链接方式

前端设计者已经设计好，针对设计好的进行动态化即可
前端没有提供，一般提供一个ul列表+a标签实现

4、在后台实现分页点击的字符串逻辑

5、在前端页面分页处显示后台设计的分页逻辑

示例

1、使用京东分页逻辑实现分页，使用前端设计好的a标签形式设计分页字符串

# 在所有数据分页都是请求index.php，所以需要在index.php开始处增加页码获取信息（包含每页访问的数量定义）
$page = $_GET['page'] ?? 1;
$pagecount = 4;


# 获取满足条件的数据的总记录数（在获取列表之前：因为不需要获取全部数据了）
$count_sql = 'select count(*) as total from news';
$res = read($conn,$count_sql,$error);		# 执行
$count = $res['total'] ?? 0;				# 取出记录（如果没有那就是0）
$pages = ceil($count / $pagecount);			# 总页数


# 计算页码数据：使用limit限制
$offset = ($page - 1) * $pagecount;
$limit = " limit $offset,$pagecount";

# 拼凑原来获取数据的SQL，使得数据分页效果实现
# 连接查询之后，系统会默认按照连接条件进行升序排序，需要调整为新闻id升序排序
$sql = "select n.*,a.name from news n left join author a on n.a_id = a.id order by n.id " . $limit;

# 产生分页链接信息
$pageinfo = '';

# 首页
$pageinfo .= "<a href='index.php?page=1'>首页</a>";

# 判定是否需要上一页
if($page != 1) {
    $prev = $page - 1;
    $pageinfo .= "<a href='index.php?page={$prev}'>上一页</a>";
}

# 拼凑数字逻辑部分：以页码总数先划分
if($pages <= 7){
    # 显示所有页码，也不需要...
    for($i = 1;$i <= $pages;$i++){
        # 判定当前页码是否被选中：增加样式
        if($page == $i) $pageinfo .= "<a class='current' href='index.php?page={$i}'>{$i}</a>";        
        else $pageinfo .= "<a href='index.php?page={$i}'>{$i}</a>";
    }
}else{
    # 当前页码在前5页：显示前7页，外加...
    if($page <= 5){
        for($i = 1;$i <= 7;$i++){
            # 判定当前页是否被选中：选中需要增加css样式
            if($page == $i) $pageinfo .= "<a class='current' href='index.php?page={$i}'>{$i}</a>";
            else $pageinfo .= "<a href='index.php?page={$i}'>{$i}</a>";
        }
        
        # 追加...
        $pageinfo .= "<a href ='javascript:return false;' onclick='return false;'>...</a>";
    }else{
        # 当前页码大于5：显示前2页和...
        $pageinfo .= "<a href='index.php?page=1'>1</a>";
    	$pageinfo .= "<a href='index.php?page=2'>2</a>";
        $pageinfo .= "<a href ='javascript:return false;' onclick='return false;'>...</a>";
        
        # 判定当前页码是否已经到达最后三页：不需要后序...
        if($page > $pages - 3){
            # 显示最后5页
            for($i = $pages - 4;$i <= $pages;$i++){
                # 判定当前页是否被选中：选中需要增加css样式
            	if($page == $i) $pageinfo .= "<a class='current' href='index.php?page={$i}'>{$i}</a>";
            	else $pageinfo .= "<a href='index.php?page={$i}'>{$i}</a>";
            }
        }else{
            # 显示中间5页，并追加...
            for($i = $page - 2;$i <= $page + 2;$i++){
                # 判定当前页是否被选中：选中需要增加css样式
            	if($page == $i) $pageinfo .= "<a class='current' href='index.php?page={$i}'>{$i}</a>";
            	else $pageinfo .= "<a href='index.php?page={$i}'>{$i}</a>";
            }
            # 追加...
            $pageinfo .= "<a href ='javascript:return false;' onclick='return false;'>...</a>";
        }    
    }
}

# 判定是否需要增加下一页
if($page != $pages){
    $next = $page + 1;
    $pageinfo .= "<a href='index.php?page={$next}'>下一页</a>";
} 

# 末页
$pageinfo .= "<a href='index.php?page={$pages}'>末页</a>";

2、在模板上显示计算好的分页链接，代替原来的a标签分页

<!-- 分页 -->
<div class="page">
    <?php echo $pageinfo;?>
</div>

小结

1、分页后台数据筛选分为两个核心部分

数据分页：利用limit实现数据的分页获取
逻辑分页：利用逻辑部分实现分页链接，让用户可以访问不同分页数据

9、项目完善和总结

1、项目完善：各类链接地址修改

2、项目总结

前端提供好静态页面
根据静态页设计好数据库
PHP进行逻辑处理
- 数据加工
- 数据库操作（增删改查）
- 数据显示

1 数据库基本介绍

1.1 数据库基本知识

1.2 数据库分类

1.3 关系型数据库

1.4 非关系型数据库

1.5 SQL基本介绍

1.6 MySQL基本介绍

1.7 MySQL访问

1.8 总结

2 MySQL基本操作

2.1 SQL语法规则

2.2 SQL库操作

1、 创建数据库

2、显示数据库

3、使用数据库

4、修改数据库

5、删除数据库

6、总结

2.3 SQL表（字段）操作

1、创建数据表

2、显示数据表

3、查看数据表

4、更改数据表

5、更改字段

（1）新增字段

（2）字段位置

（3）更改字段名

（4）修改字段

6、删除字段

7、总结

2.4 SQL数据操作

1、新增数据

2、查看数据

3、更新数据

4、删除数据

5、总结

3 MySQL数据库字符集

3.1 字符集

1、字符集概念

2、MySQL字符集

3、乱码问题解决

4、字符集设置原理

5、总结

3.2 校对集

1、校对集概念

2、校对集设置

3、校对集应用

4、总结

4 MySQL数据库字段详解

4.1 字段类型

1、字段类型作用

2、整数类型

3、显示宽度

4、小数类型（浮点型）

5、小数类型（定点型）

6、字符串类型（定长型）

7、字符串类型（变长型）

8、字符串类型（文本字符串）

9、字符串类型（枚举）

10、字符串类型（集合）

11、时间日期类型（年）

12、时间日期类型（时间戳）

13、时间日期类型（日期）

14、时间日期类型（日期时间）

15、时间日期类型（时间）

16、总结

4.2 属性

1、属性作用

2、NULL属性

3、Default属性

4、主键

5、主键管理

6、自增长属性

7、自增长管理

8、唯一键

9、唯一键管理

10、comment属性

11、总结

4.3 数据库记录长度

5 MySQL数据库设计规范

1、创建数据库