数据库基本概念

（1）数据
所谓数据（Data）是指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的。它不仅仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。现在计算机存储和处理的数据范围十分广泛，而描述这些数据的符号也变得越来越复杂了。

（2）数据库
数据库（Database，DB）指的是以一定格式存放、能够实现多个用户共享、与应用程序彼此独立的数据集合。

（3）数据库管理系统
数据库管理系统（Database Management System，DBMS）是用来定义和管理数据的软件。如何科学的组织和存储数据，如何高效的获取和维护数据，如何保证数据的安全性和完整性，这些都需要靠数据库管理系统完成。目前，比较流行的数据库管理系统有：Oracle、MySQL、SQL Server、DB2等。

（4）数据库应用程序
数据库应用程序（Database Application System，DBAS）是在数据库管理系统基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序，如学生管理系统、人事管理系统、图书管理系统等。

（5）数据库管理员
数据库管理员（Database Administrator，DBA）是指对数据库管理系统进行操作的人员，其主要负责数据库的运营和维护。

（6）最终用户
最终用户（User）指的是数据库应用程序的使用者。用户面向的是数据库应用程序（通过应用程序操作数据），并不会直接与数据库打交道。

（7）数据库系统
数据库系统（Database System，DBS）一般是由数据库、数据库管理系统、数据库应用程序、数据库管理员和最终用户构成。其中DBMS是数据库系统的基础和核心。

数据库类型

【1】数据库类型
数据库经过几十年的发展，出现了多种类型。根据数据的组织结构不同，主要分为网状数据库、层次数据库、关系型数据库和非关系型数据库四种。目前最常见的数据库模型主要是：关系型数据库和非关系型数据库。

关系型数据库
关系型数据库模型是将复杂的数据结构用较为简单的二元关系（二维表）来表示，如图1-4所示。在该类型数据库中，对数据的操作基本上都建立在一个或多个表格上，我们可以采用结构化查询语言（SQL）对数据库进行操作。关系型数据库是目前主流的数据库技术，其中具有代表性的数据库管理系统有：Oracle、DB2、SQL Server、MySQL等。

PS：关系=二维表

非关系型数据库NOSQL
NOSQL（Not Only SQL）泛指非关系型数据库。关系型数据库在超大规模和高并发的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题。NOSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，尤其是大数据应用难题。常见的非关系型数据库管理系统有Memcached、MongoDB，redis，HBase等。

【2】常见的关系型数据库
虽然非关系型数据库的优点很多，但是由于其并不提供SQL支持、学习和使用成本较高并且无事务处理，所以本书的重点是关系型数据库。下面我们将介绍一下常用的关系型数据库管理系统。

Oracle
Oracle数据库是由美国的甲骨文（Oracle）公司开发的世界上第一款支持SQL语言的关系型数据库。经过多年的完善与发展，Oracle数据库已经成为世界上最流行的数据库，也是甲骨文公司的核心产品。
Oracle数据库具有很好的开放性，能在所有的主流平台上运行，并且性能高、安全性高、风险低；但是其对硬件的要求很高、管理维护和操作比较复杂而且价格昂贵，所以一般用在满足对银行、金融、保险等行业大型数据库的需求上。
DB2
DB2是IBM公司著名的关系型数据库产品。DB2无论稳定性，安全性，恢复性等等都无可挑剔，而且从小规模到大规模的应用都可以使用，但是用起来非常繁琐，比较适合大型的分布式应用系统。
SQL Server
SQL Server是由Microsoft开发和推广的关系型数据库，SQL Server的功能比较全面、效率高，可以作为中型企业或单位的数据库平台。SQL Server可以与Windows操作系统紧密继承，无论是应用程序开发速度还是系统事务处理运行速度，都能得到大幅度提升。但是，SQL Server只能在Windows系统下运行，毫无开放性可言。
MySQL
MySQL是一种开放源代码的轻量级关系型数据库，MySQL数据库使用最常用的结构化查询语言（SQL）对数据库进行管理。由于MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个人需要对其缺陷进行修改。
由于MySQL数据库体积小、速度快、成本低、开放源码等优点，现已被广泛应用于互联网上的中小型网站中，并且大型网站也开始使用MySQL数据库，如网易、新浪等。

MYSQL介绍

MySQL数据库最初是由瑞典MySQL AB公司开发，2008年1月16号被Sun公司收购。2009年，SUN又被Oracle收购。MySQL是目前IT行业最流行的开放源代码的数据库管理系统，同时它也是一个支持多线程高并发多用户的关系型数据库管理系统。MySQL之所以受到业界人士的青睐，主要是因为其具有以下几方面优点：
1. 开放源代码
  
  MySQL最强大的优势之一在于它是一个开放源代码的数据库管理系统。开源的特点是给予了用户根据自己需要修改DBMS的自由。MySQL采用了General Public License，这意味着授予用户阅读、修改和优化源代码的权利，这样即使是免费版的MySQL的功能也足够强大，这也是为什么MySQL越来越受欢迎的主要原因。
2. 跨平台
  
  MySQL可以在不同的操作系统下运行，简单地说，MySQL可以支持Windows系统、UNIX系统、Linux系统等多种操作系统平台。这意味着在一个操作系统中实现的应用程序可以很方便地移植到其他的操作系统下。
3. 轻量级
  
  MySQL的核心程序完全采用多线程编程，这些线程都是轻量级的进程，它在灵活地为用户提供服务的同时，又不会占用过多的系统资源。因此MySQL能够更快速、高效的处理数据。
4. 成本低
  
  MySQL分为社区版和企业版，社区版是完全免费的，而企业版是收费的。即使在开发中需要用到一些付费的附加功能，价格相对于昂贵的Oracle、DB2等也是有很大优势的。其实免费的社区版也支持多种数据类型和正规的SQL查询语言，能够对数据进行各种查询、增加、删除、修改等操作，所以一般情况下社区版就可以满足开发需求了，而对数据库可靠性要求比较高的企业可以选择企业版。
  另外，PHP中提供了一整套的MySQL函数，对MySQL进行了全方位的强力支持。
  总体来说，MySQL是一款开源的、免费的、轻量级的关系型数据库，其具有体积小、速度快、成本低、开放源码等优点，其发展前景是无可限量的。
PS：社区版与企业版主要的区别是：
1. 社区版包含所有MySQL的最新功能，而企业版只包含稳定之后的功能。换句话说，社区版可以理解为是企业版的测试版。
  
  2.MySQL官方的支持服务只是针对企业版，如果用户在使用社区版时出现了问题，MySQL官方是不负责任的。

MYSQL下载安装

【2】官方下载地址：
https://dev.mysql.com/downloads/windows/installer/8.0.html

【3】安装过程：
1.双击MySQL安装文件mysql-installer-community-8.0.18.0.msi，出现安装类型选项。
² Developer Default：开发者默认

² Server only：只安装服务器端

² Client only：只安装客户端

² Full：安装全部选项

² Custom：自定义安装

设置用户名和密码之后一直next就好了

检验安装成果

1)安装了Windows Service：MySQL80，并且已经启动。

2)安装了MySQL软件。安装位置为：C:\Program Files\MySQL。

MySQL文件下放的是软件的内容）
3)安装了MySQL数据文件夹，用来存放MySQL基础数据和以后新增的数据。安装位置为C:\ProgramData\MySQL\MySQL Server 8.0。

（ProgramData文件夹可能是隐藏的，显示出来即可）
（MySQL文件下的内容才是真正的MySQL中数据）
4)在MySQL数据文件夹中有MySQL的配置文件：my.ini。它是MySQL数据库中使用的配置文件，修改这个文件可以达到更新配置的目的。以下几个配置项需要大家特别理解。

port=3306：监听端口是3306

² basedir=”C:/Program Files/MySQL/MySQL Server 8.0/“：软件安装位置

² datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data：数据文件夹位置

² default_authentication_plugin=caching_sha2_password：默认验证插件

² default-storage-engine=INNODB：默认存储引擎

（这些内容在Linux下可能会手动更改）

MYSQL登录、访问、退出操作

需要配置环境变量path:

注意：控制命令台必须重启才会生效：

登录的命令：mysql -hlocalhost -uroot –p
² mysql：bin目录下的文件mysql.exe。mysql是MySQL的命令行工具，是一个客户端软件，可以对任何主机的mysql服务（即后台运行的mysqld）发起连接。

² -h：host主机名。后面跟要访问的数据库服务器的地址；如果是登录本机，可以省略

² -u：user 用户名。后面跟登录数据的用户名，第一次安装后以root用户来登录，是MySQL的管理员用户

² -p: password 密码。一般不直接输入，而是回车后以保密方式输入。

【2】访问数据库

显示MySQL中的数据库列表：show databases; 默认有四个自带的数据库，每个数据库中可以有多个数据库表、视图等对象。

切换当前数据库的命令：use mysql;
² MySQL下可以有多个数据库，如果要访问哪个数据库，需要将其置为当前数据库。

² 该命令的作用就是将数据库mysql（默认提供的四个数据库之一的名字）置为当前数据库

显示当前数据库的所有数据库表：show tables;

MySQL 层次：不同项目对应不同的数据库组成 - 每个数据库中有很多表 - 每个表中有很多数据

【3】退出数据库

退出数据库可以使用quit或者exit命令完成，也可以用\q; 完成退出操作

安装navicat连接MySQL

首先登录mysql然后进行设置

设置密码永不过期
alter user ‘root’@’localhost’ identified by ‘root’ password expire never;
设置加密规则为mysql_native_password
alter user ‘root’@’localhost’ identified with mysql_native_password by ‘root’;
重新访问navicat，提示连接成功。

SQL语言

SQL语言分为五个部分：

数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。
数据操作语言（Data Manipulation Language，DML）：DML主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：
1) INSERT：增加数据
2) UPDATE：修改数据
3) DELETE：删除数据
数据定义语言（Data Definition Language，DDL）：DDL主要用针对是数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改和删除操作。其主要包括：
1) CREATE：创建数据库对象
2) ALTER：修改数据库对象
3) DROP：删除数据库对象
数据控制语言（Data Control Language，DCL）：DCL用来授予或回收访问数据库的权限，其主要包括：
1) GRANT：授予用户某种权限

事务控制语言（Transaction Control Language，TCL）：TCL用于数据库的事务管理。其主要包括：
1) START TRANSACTION：开启事务
2) COMMIT：提交事务
3) ROLLBACK：回滚事务

DDLDML创建数据库表

【1】认识数据库表
表（Table）是数据库中数据存储最常见和最简单的一种形式，数据库可以将复杂的数据结构用较为简单的二维表来表示。二维表是由行和列组成的，分别都包含着数据，如表所示。

每个表都是由若干行和列组成的，在数据库中表中的行被称为记录，表中的列被称为是这些记录的字段。

记录也被称为一行数据，是表里的一行。在关系型数据库的表里，一行数据是指一条完整的记录。

字段是表里的一列，用于保存每条记录的特定信息。如上表所示的学生信息表中的字段包括“学号”、“姓名”、“性别”和“年龄”。数据表的一列包含了某个特定字段的全部信息。

【2】创建数据库表 t_student
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
学号是主键 = 不能为空 + 唯一
姓名不能为空
性别默认值是男
Email唯一

（1）创建数据库：

（2）新建查询：

（3）创建数据库表：

##这是一个单行注释
/*
多行注释
多行注释
多行注释
*/
/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
*/
-- 创建数据库表：
create table t_student(
        sno int(6), -- 6显示长度 
        sname varchar(5), -- 5个字符
        sex char(1),
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);
-- 查看表的结构：展示表的字段详细信息
desc t_student;
-- 查看表中数据：
select * from t_student;
-- 查看建表语句：
show create table t_student;
/*
CREATE TABLE `t_student` (
  `sno` int DEFAULT NULL,
  `sname` varchar(5) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `enterdate` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

数据库表列类型

1、整数类型

MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示
主键自增：不使用序列，通过auto_increment，要求是整数类型

2、浮点数类型

需要注意的是与整数类型不一样的是，浮点数类型的宽度不会自动扩充。 score double(4,1)
score double(4,1)—小数部分为1位，总宽度4位，并且不会自动扩充。

3.字符串类型

CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。

CHAR类型长度固定，VARCHAR类型的长度可变。

CHAR的长度固定，即使字符长度小于定义长度，也会在字符串末尾填充空格。

VARCHAR需要记录额外长度信息，占据1~2字节

UTF-8字符集，每个字符最大占用3字节，所以VARCHAR支持的最大长度是65535-2=65533，然后最大字符就是65533 / 3 = 21844

因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。
CHAR和VARCHAR表示的是字符的个数，而不是字节的个数

4.日期和时间类型

TIMESTEMP类型的数据指定方式与DATETIME基本相同，两者的不同之处在于以下几点：
(1) 数据的取值范围不同，TIMESTEMP类型的取值范围更小。
(2) 如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了NULL值，MySQL会自动将该字段赋值为系统当前的日期与时间。
(3) TIMESTEMP类型还可以使用CURRENT_TIMESTAMP来获取系统当前时间。
(4) TIMESTEMP类型有一个很大的特点，那就是时间是根据时区来显示的。例如，在东八区插入的TIMESTEMP数据为2017-07-11 16:43:25，在东七区显示时，时间部分就变成了15:43:25，在东九区显示时，时间部分就变成了17:43:25。

DML_添加数据

注意事项
int 宽度是显示宽度，如果超过，可以自动增大宽度 int底层都是4个字节
时间的方式多样 ‘1256-12-23’ “1256/12/23” “1256.12.23”
字符串不区分单引号和双引号
如何写入当前的时间 now() , sysdate() , CURRENT_DATE()
char varchar 是字符的个数，不是字节的个数，可以使用binary，varbinary表示定长和不定长的字节个数。
如果不是全字段插入数据的话，需要加入字段的名字

-- 查看表记录：
select * from t_student;
-- 在t_student数据库表中插入数据：
insert into t_student values (1,'张三','男',18,'2022-5-8','软件1班','123@126.com');
insert into t_student values (10010010,'张三','男',18,'2022-5-8','软件1班','123@126.com');
insert into t_student values (2,'张三','男',18,'2022.5.8','软件1班','123@126.com');
insert into t_student values (2,"张三",'男',18,'2022.5.8','软件1班','123@126.com');
insert into t_student values (7,"张三",'男',18,now(),'软件1班','123@126.com');
insert into t_student values (9,"易烊千玺",'男',18,now(),'软件1班','123@126.com');
insert into t_student (sno,sname,enterdate) values (10,'李四','2023-7-5');

DML_修改，删除数据

注意事项
1.关键字，表名，字段名不区分大小写
2.默认情况下，内容不区分大小写
3.删除操作from关键字不可缺少
4.修改，删除数据别忘记加限制条件

-- 修改表中数据
update t_student set sex = '女' ;
update t_student set sex = '男' where sno = 10 ;
UPDATE T_STUDENT SET AGE = 21 WHERE SNO = 10;
update t_student set CLASSNAME = 'java01' where sno = 10 ;
update t_student set CLASSNAME = 'JAVA01' where sno = 9 ;
update t_student set age = 29 where classname = 'java01';
-- 删除操作：
delete from t_student where sno = 2;

表的完整性约束

为防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束。

MySQL中主要支持以下几种种完整性约束，如表所示。其中Check约束是MySQL8中提供的支持。

非外键约束

【1】代码演示非外键约束：

/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
约束：
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
【1】学号是主键 = 不能为空 +  唯一 ，主键的作用：可以通过主键查到唯一的一条记录【2】如果主键是整数类型，那么需要自增
【3】姓名不能为空
【4】Email唯一
【5】性别默认值是男
【6】性别只能是男女
【7】年龄只能在18-50之间
*/
-- 创建数据库表：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男' check(sex='男' || sex='女'),
        age int(3) check(age>=18 and age<=50),
        enterdate date,
        classname varchar(10),
        email varchar(15) unique
);
-- 添加数据：
--  1048 - Column 'sname' cannot be null 不能为null
-- 3819 - Check constraint 't_student_chk_1' is violated. 违反检查约束
insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');
-- 1062 - Duplicate entry '1' for key 't_student.PRIMARY' 主键重复
-- > 1062 - Duplicate entry 'ls@126.com' for key 't_student.email' 违反唯一约束
insert into t_student values (2,'李四','男',21,'2023-9-1','java01班','ls@126.com');
insert into t_student values (3,'露露','男',21,'2023-9-1','java01班','ls@126.com');
-- 如果主键没有设定值，或者用null.default都可以完成主键自增的效果
insert into t_student (sname,enterdate) values ('菲菲','2029-4-5');
insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','xm@126.com');
insert into t_student values (default,'小刚','男',21,'2023-9-1','java01班','xg@126.com');
-- 如果sql报错，可能主键就浪费了，后续插入的主键是不连号的，我们主键也不要求连号的
insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','oo@126.com');
-- 查看数据：
select * from t_student;

【2】约束从作用上可以分为两类：
(1) 表级约束：可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称；

(2) 列级约束：包含在列定义中，直接跟在该列的其它定义之后，用空格分隔；不必指定列名；

-- 删除表：
drop table t_student;
-- 创建数据库表：
create table t_student(
        sno int(6) auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15),
        constraint pk_stu primary key (sno),  -- pk_stu 主键约束的名字
        constraint ck_stu_sex check (sex = '男' || sex = '女'),
        constraint ck_stu_age check (age >= 18 and age <= 50),
        constraint uq_stu_email unique (email)
);
-- 添加数据：
insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');
-- > 3819 - Check constraint 'ck_stu_sex' is violated.
-- > 3819 - Check constraint 'ck_stu_age' is violated.
-- > 1062 - Duplicate entry 'zs@126.com' for key 't_student.uq_stu_email'
insert into t_student values (3,'李四','男',21,'2023-9-1','java01班','zs@126.com');
-- 查看数据：
select * from t_student;

【3】在创建表以后添加约束：

-- 删除表：
drop table t_student;
-- 创建数据库表：
create table t_student(
        sno int(6), 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);
-- > 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
-- 错误的解决办法：就是auto_increment去掉
-- 在创建表以后添加约束：
alter table t_student add constraint pk_stu primary key (sno) ; -- 主键约束
alter table t_student modify sno int(6) auto_increment; -- 修改自增条件
alter table t_student add constraint ck_stu_sex check (sex = '男' || sex = '女');
alter table t_student add constraint ck_stu_age check (age >= 18 and age <= 50);
alter table t_student add constraint uq_stu_email unique (email);
-- 查看表结构：
desc t_student;

验证约束添加成功：查看表结构：

【4】总结：

1.主键约束

主键约束（PRIMARY KEY，缩写PK），是数据库中最重要的一种约束，其作用是约束表中的某个字段可以唯一标识一条记录。因此，使用主键约束可以快速查找表中的记录。就像人的身份证、学生的学号等等，设置为主键的字段取值不能重复（唯一），也不能为空（非空），否则无法唯一标识一条记录。

主键可以是单个字段，也可以是多个字段组合。对于单字段主键的添加可使用表级约束，也可以使用列级约束；而对于多字段主键的添加只能使用表级约束。

2.非空约束

非空约束（NOT NULL，缩写NK）规定了一张表中指定的某个字段的值不能为空（NULL）。设置了非空约束的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。

无论是单个字段还是多个字段非空约束的添加只能使用列级约束（非空约束无表级约束）

为已存在表中的字段添加非空约束

1	alter table student8 modify stu_sex varchar(1) not null;

使用ALTER TABLE语句删除非空约束

1	alter table student8 modify stu_sex varchar(1) null;

唯一约束

唯一约束（UNIQUE，缩写UK）比较简单，它规定了一张表中指定的某个字段的值不能重复，即这一字段的每个值都是唯一的。如果想要某个字段的值不重复，那么就可以为该字段添加为唯一约束。

无论单个字段还是多个字段唯一约束的添加均可使用列级约束和表级约束

检查约束

检查约束（CHECK）用来限制某个字段的取值范围，可以定义为列级约束，也可以定义为表级约束。MySQL8开始支持检查约束。

默认值约束

默认值约束（DEFAULT）用来规定字段的默认值。如果某个被设置为DEFAULT约束的字段没插入具体值，那么该字段的值将会被默认值填充。

 默认值约束的设置与非空约束一样，也只能使用列级约束。

字段值自动增加约束

自增约束（AUTO_INCREMENT）可以使表中某个字段的值自动增加。一张表中只能有一个自增长字段，并且该字段必须定义了约束（该约束可以是主键约束、唯一约束以及外键约束），如果自增字段没有定义约束，数据库则会提示“Incorrect table definition; there can be only one auto column and it must be defined as a key”错误。

由于自增约束会自动生成唯一的ID，所以自增约束通常会配合主键使用，并且只适用于整数类型。一般情况下，设置为自增约束字段的值会从1开始，每增加一条记录，该字段的值加1。

为已存在表中的字段添加自增约束

/*创建表student11*/

 create   table student11 (

       stu_id int(10) primary key,

       stu_name varchar(3),

       stu_sex varchar (1)

);

/*为student11表中的主键字段添加自增约束*/

alter   table student11 modify stu_id int(10) auto_increment;

使用ALTER TABLE语句删除自增约束

1	alter table studen11 modify stu_id int(10);

外键约束

【1】什么是外键约束？
外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。
外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

举个例子：如果想要表示学生和班级的关系，首先要有学生表和班级表两张表，然后学生表中有个字段为stu_clazz（该字段表示学生所在的班级），而该字段的取值范围由班级表中的主键cla_no字段（该字段表示班级编号）的取值决定。那么班级表为主表，学生表为从表，且stu_clazz字段是学生表的外键。通过stu_clazz字段就建立了学生表和班级表的关系。

主表（父表）：班级表 - 班级编号 - 主键
从表（子表）：学生表 - 班级编号 - 外键

-- 先创建父表：班级表：
create table t_class(
        cno int(4) primary key auto_increment,
        cname varchar(10) not null,
        room char(4)
)
-- 添加班级数据：
insert into t_class values (null,'java001','r803');
insert into t_class values (null,'java002','r416');
insert into t_class values (null,'大数据001','r103');
-- 可以一次性添加多条记录：
insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');
-- 查询班级表：
select * from t_class;
-- 学生表删除：
drop table t_student;
-- 创建子表,学生表：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4)  -- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
);
-- 添加学生信息：
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2);
-- 查看学生表：
select * from t_student;
-- 出现问题：
-- 1.添加一个学生对应的班级编码为4：
insert into t_student values (null,'丽丽',4
);
-- 2.删除班级2：
delete from t_class where cno = 2;
-- 出现问题的原因：12_main目录下配置文件打包问题
-- 因为你现在的外键约束，没用语法添加进去，现在只是逻辑上认为班级编号是外键，没有从语法上定义
-- 解决办法，添加外键约束：
-- 注意：外键约束只有表级约束，没有列级约束：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
        constraint fk_stu_classno foreign key (classno) references t_class (cno)
);
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4)
);
-- 在创建表以后添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno)
-- 上面的两个问题都解决了：
-- 添加学生信息：
-- > 1452 - Cannot add or update a child row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2);
-- 删除班级1：
-- 2.删除班级2：
insert into t_student values (null,'张三',3),(null,'李四',3),(null,'王五',3);
-- > 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
delete from t_class where cno = 3;

外键策略

外键策略用于维护数据库表之间的关联

-- 学生表删除：
drop table t_student;
-- 班级表删除：
drop table t_class;
-- 注意：先删除从表，再删除主表。
-- 先创建父表：班级表：
create table t_class(
        cno int(4) primary key auto_increment,
        cname varchar(10) not null,
        room char(4)
)
-- 可以一次性添加多条记录：
insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');
-- 添加学生表，添加外键约束：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
        constraint fk_stu_classno foreign key (classno) references t_class (cno)
);
-- 可以一次性添加多条记录：
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2),(null,'朱六',3);
-- 查看班级表和学生表：
select * from t_class;
select * from t_student;
-- 删除班级2：如果直接删除的话肯定不行因为有外键约束：
-- 加入外键策略：
-- 策略1：no action 不允许操作
-- 通过操作sql来完成：
-- 先把班级2的学生对应的班级 改为null 
update t_student set classno = null where classno = 2;
-- 然后再删除班级2：
delete from t_class where cno = 2;
-- 策略2：cascade 级联操作：操作主表的时候影响从表的外键信息：
-- 先删除之前的外键约束：
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;
-- 试试更新：
update t_class set cno = 5 where cno = 3;
-- 试试删除：
delete from t_class where cno = 5;
-- 策略3：set null  置空操作：
-- 先删除之前的外键约束：
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;
-- 试试更新：
update t_class set cno = 8 where cno = 1;
-- 注意：
-- 1. 策略2 级联操作  和  策略2 的  删除操作  可以混着使用：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete set null ;
-- 2.应用场合：
-- （1）朋友圈删除，点赞。留言都删除  --  级联操作
-- （2）解散班级，对应的学生 置为班级为null就可以了，-- set null

DDL和DML的补充

【1】sql展示：

-- 创建表：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男' check(sex='男' || sex='女'),
        age int(3) check(age>=18 and age<=50),
        enterdate date,
        classname varchar(10),
        email varchar(15) unique
);
-- 添加数据：
insert into t_student values (null,'张三','男',21,'2023-9-1','java01班','zs@126.com');
insert into t_student values (null,'李四','男',21,'2023-9-1','java01班','ls@126.com');
insert into t_student values (null,'露露','男',21,'2023-9-1','java01班','ll@126.com');
-- 查看学生表：
select * from t_student;
-- 添加一张表：快速添加：结构和数据跟t_student 都是一致的
create table t_student2
as
select * from t_student;
select * from t_student2;
-- 快速添加，结构跟t_student一致，数据没有：
create table t_student3
as
select * from t_student where 1=2;
select * from t_student3;
-- 快速添加：只要部分列，部分数据：
create table t_student4
as
select sno,sname,age from t_student where sno = 2;
select * from t_student4;
-- 删除数据操作 :清空数据
delete from t_student;
truncate table t_student;

【2】delete和truncate的区别：
从最终的结果来看，虽然使用TRUNCATE操作和使用DELETE操作都可以删除表中的全部记录，但是两者还是有很多区别的，其区别主要体现在以下几个方面：
(1)DELETE为数据操作语言DML；TRUNCATE为数据定义语言DDL。
(2) DELETE操作是将表中所有记录一条一条删除直到删除完；TRUNCATE操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，TRUNCATE操作的效率更高。
(3)DELETE操作可以回滚；TRUNCATE操作会导致隐式提交，因此不能回滚（在第十章中会讲解事务的提交和回滚）。
(4)DELETE操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“Affected rows：4”）；截断操作不会返回已删除的行量，结果通常是“Affected rows：0”。DELETE操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；TRUNCATE操作则会重新从1开始自增。

DQL-查询操作

表的准备

准备四张表：dept(部门表),emp(员工表),salgrade(薪资等级表),bonus(奖金表)

create table DEPT(  
  DEPTNO int(2) not null,  
  DNAME  VARCHAR(14),  
  LOC    VARCHAR(13)  
);  
alter table DEPT  
  add constraint PK_DEPT primary key (DEPTNO); 
        
create table EMP  
(  
  EMPNO    int(4) primary key,  
  ENAME    VARCHAR(10),  
  JOB      VARCHAR(9),  
  MGR      int(4),  
  HIREDATE DATE,  
  SAL      double(7,2),  
  COMM     double(7,2),  
  DEPTNO   int(2)  
);  
alter table EMP  
  add constraint FK_DEPTNO foreign key (DEPTNO)  
  references DEPT (DEPTNO);  
        
create table SALGRADE  
(  
  GRADE int primary key,  
  LOSAL double(7,2),  
  HISAL double(7,2)  
);  
create table BONUS  
(  
  ENAME VARCHAR(10),  
  JOB   VARCHAR(9),  
  SAL   double(7,2),  
  COMM  double(7,2)  
);  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (10, 'ACCOUNTING', 'NEW YORK');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (20, 'RESEARCH', 'DALLAS');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (30, 'SALES', 'CHICAGO');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (40, 'OPERATIONS', 'BOSTON');  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (1, 700, 1200);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (2, 1201, 1400);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (3, 1401, 2000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (4, 2001, 3000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (5, 3001, 9999);  
-- 查看表：
select * from dept; 
-- 部门表：dept:department 部分 ，loc - location 位置
select * from emp;
-- 员工表：emp:employee 员工   ,mgr :manager上级领导编号，hiredate 入职日期  firedate 解雇日期 ，common：补助
-- deptno 外键 参考  dept - deptno字段
-- mgr 外键  参考  自身表emp - empno  产生了自关联
select * from salgrade;
-- losal - lowsal
-- hisal - highsal
select * from bonus;

单表查询

最简单的sql查询

-- 对emp表查询：
select * from emp; -- *代表所有数据
-- 显示部分列：
select empno,ename,sal from emp;
-- 显示部分行：where子句
select * from emp where sal > 2000;
-- 显示部分列，部分行：
select empno,ename,job,mgr from emp where sal > 2000;
-- 起别名：
select empno 员工编号,ename 姓名,sal 工资 from emp; -- as 省略，''或者""省略了
-- as alias 别名
select empno as 员工编号,ename as 姓名,sal as 工资 from emp;
select empno as '员工编号',ename as "姓名",sal as 工资 from emp;
-- > 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '编号,ename as "姓 名",sal as 工资 from emp' at line 1
-- 错误原因：在别名中有特殊符号的时候，''或者""不可以省略不写
select empno as 员工 编号,ename as "姓 名",sal as 工资 from emp;
-- 算术运算符：
select empno,ename,sal,sal+1000 as '涨薪后',deptno from emp where sal < 2500;
select empno,ename,sal,comm,sal+comm from emp;  -- ？？？后面再说
-- 去重操作：
select job from emp;
select distinct job from emp;
select job,deptno from emp;
select distinct job,deptno from emp; -- 对后面的所有列组合 去重 ，而不是单独的某一列去重
-- 排序：
select * from emp order by sal; -- 默认情况下是按照升序排列的
select * from emp order by sal asc; -- asc 升序，可以默认不写
select * from emp order by sal desc; -- desc 降序
select * from emp order by sal asc ,deptno desc; -- 在工资升序的情况下，deptno按照降序排列

where子句

指定查询条件使用where子句，可以查询符合条件的部分记录。

-- 查看emp表：
select * from emp;
-- where子句：将过滤条件放在where子句的后面，可以筛选/过滤出我们想要的符合条件的数据：
-- where 子句 + 关系运算符
select * from emp where deptno = 10;
select * from emp where deptno > 10;
select * from emp where deptno >= 10;
select * from emp where deptno < 10;
select * from emp where deptno <= 10;
select * from emp where deptno <> 10;
select * from emp where deptno != 10;
select * from emp where job = 'CLERK'; 
select * from emp where job = 'clerk'; -- 默认情况下不区分大小写 
select * from emp where binary job = 'clerk'; -- binary区分大小写
select * from emp where hiredate < '1981-12-25';
-- where 子句 + 逻辑运算符：and 
select * from emp where sal > 1500 and sal < 3000;  -- (1500,3000)
select * from emp where sal > 1500 && sal < 3000; 
select * from emp where sal > 1500 and sal < 3000 order by sal;
select * from emp where sal between 1500 and 3000; -- [1500,3000]
-- where 子句 + 逻辑运算符：or
select * from emp where deptno = 10 or deptno = 20;
select * from emp where deptno = 10 || deptno = 20;
select * from emp where deptno in (10,20);
select * from emp where job in ('MANAGER','CLERK','ANALYST');
-- where子句 + 模糊查询：
-- 查询名字中带A的员工  -- %代表任意多个字符 0,1,2，.....
select * from emp where ename like '%A%' ;
-- -任意一个字符
select * from emp where ename like '__A%' ;
-- 关于null的判断：
select * from emp where comm is null;
select * from emp where comm is not null;
-- 小括号的使用  ：因为不同的运算符的优先级别不同，加括号为了可读性
select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500; -- 先and再or  and > or
select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500); 
select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500;

使用函数

MySQL中提供了大量函数来简化用户对数据库的操作，比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力，同时也给数据的转换和处理提供了方便。（在sql中使用函数）

函数只是对查询结果中的数据进行处理，不会改变数据库中数据表的值。MySQL中的函数主要分为单行函数和多行函数两大类，下面我们将详细讲解这两大类函数。

单行函数

单行函数是指对每一条记录输入值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。

多行函数

多行函数是指对一组数据进行运算，针对这一组数据（多行记录）只返回一个结果，也称为分组函数。

-- 函数举例：
select empno,ename,lower(ename),upper(ename),sal from emp;
-- 函数的功能：封装了特定的一些功能，我们直接拿过来使用，可以实现对应的功能
-- 函数作用：为了提高select的能力
-- 注意：函数没有改变数据自身的值，而是在真实数据的上面进行加工处理，展示新的结果而已。
select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp;
-- 函数的分类：
-- lower(ename),upper(ename) ：改变每一条结果，每一条数据对应一条结果  -- 单行函数
-- max(sal),min(sal),count(sal),sum(sal),avg(sal):多条数据，最终展示一个结果  -- 多行函数

PS：除了多行函数（max,min,count,sum,avg），都是单行函数

单行函数

1.字符串函数（String StringBuilder）

函数	描述
CONCAT(str1, str2, ···, strn)	将str1、str2···strn拼接成一个新的字符串
INSERT(str, index, n, newstr)	将字符串str从第index位置开始的n个字符替换成字符串newstr
LENGTH(str)	获取字符串str的长度
LOWER(str)	将字符串str中的每个字符转换为小写
UPPER(str)	将字符串str中的每个字符转换为大写
LEFT(str, n)	获取字符串str最左边的n个字符
RIGHT(str, n)	获取字符串str最右边的n个字符
LPAD(str, n, pad)	使用字符串pad在str的最左边进行填充，直到长度为n个字符为止
RPAD(str, n, pad)	使用字符串pad在str的最右边进行填充，直到长度为n个字符为止
LTRIM(str)	去除字符串str左侧的空格
RTRIM(str)	去除字符串str右侧的空格
TRIM(str)	去除字符串str左右两侧的空格
REPLACE(str,oldstr,newstr)	用字符串newstr替换字符串str中所有的子字符串oldstr
REVERSE(str)	将字符串str中的字符逆序
STRCMP(str1, str2)	比较字符串str1和str2的大小
SUBSTRING(str,index,n)	获取从字符串str的index位置开始的n个字符

数值函数（Math）

函数	描述
ABS(num)	返回num的绝对值
CEIL(num)	返回大于num的最小整数（向上取整）
FLOOR(num)	返回小于num的最大整数（向下取整）
MOD(num1, num2)	返回num1/num2的余数（取模）
PI()	返回圆周率的值
POW(num,n)/POWER(num, n)	返回num的n次方
RAND(num)	返回0~1之间的随机数
ROUND(num, n)	返回x四舍五入后的值，该值保留到小数点后n位
TRUNCATE(num, n)	返回num被舍去至小数点后n位的值

日期与时间函数

函数	描述
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
SYSDATE()	返回该函数执行时的日期和时间
DAYOFYEAR(date)	返回日期date为一年中的第几天
WEEK(date)/WEEKOFYEAR(date)	返回日期date为一年中的第几周
DATE_FORMAT(date, format)	返回按字符串format格式化后的日期date
DATE_ADD(date, INTERVAL expr unit) /ADDDATE(date, INTERVAL expr unit)	返回date加上一个时间间隔后的新时间值
DATE_SUB(date, INTERVAL expr unit) /SUBDATE(date, INTERVAL expr unit)	返回date减去一个时间间隔后的新时间值
DATEDIFF(date1, date2)	返回起始日期date1与结束日期date2之间的间隔天数

-- 单行函数包含：
-- 1.字符串函数
select ename,length(ename),substring(ename,2,3) from emp;
-- substring字符串截取，2:从字符下标为2开始，3：截取长度3    （下标从1开始）
-- 2.数值函数
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual; -- dual实际就是一个伪表 
select abs(-5) 绝对值,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.14) 四舍五入;  -- 如果没有where条件的话，from dual可以省略不写
select ceil(sal) from emp;
select 10/3,10%3,mod(10,3) ;
-- 3.日期与时间函数 
select * from emp;
select curdate(),curtime() ; -- curdate()年月日 curtime()时分秒
select now(),sysdate(),sleep(3),now(),sysdate() from dual; -- now(),sysdate() 年月日时分秒
insert into emp values (9999,'lili','SALASMAN',7698,now(),1000,null,30);
-- now()可以表示年月日时分秒，但是插入数据的时候还是要参照表的结构的
desc emp;

流程函数（ IF SWITCH）

间隔类型	描述
IF(condition, t, f)	如果条件condition为真，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为null，则返回value1，否则返回value2
NULLIF(value1, value2)	如果value1等于value2，则返回null，否则返回value1
CASE value WHEN [value1] THEN result1 [WHEN [value2] THEN result2 …] [ELSE result] END	如果value等于value1，则返回result1，···，否则返回result
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 …] [ELSE result] END	如果条件condition1为真，则返回result1，···，否则返回result

5.JSON函数

函数	描述
JSON_APPEND()	在JSON文档中追加数据
JSON_INSERT ()	在JSON文档中插入数据
JSON_REPLACE ()	替换JSON文档中的数据
JSON_REMOVE ()	从JSON文档的指定位置移除数据
JSON_CONTAINS()	判断JSON文档中是否包含某个数据
JSON_SEARCH()	查找JSON文档中给定字符串的路径

6.其他函数

函数	描述
DATABASE()	返回当前数据库名
VERSION()	返回当前MySQL的版本号
USER()	返回当前登录的用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA	返回数字代表的IP地址
PASSWORD(str)	实现对字符串str的加密操作
FORMAT(num, n)	实现对数字num的格式化操作，保留n位小数
CONVERT(data, type)	实现将数据data转换成type类型的操作

-- 4.流程函数
-- if相关
select empno,ename,sal,if(sal>=2500,'高薪','底薪') as '薪资等级' from emp; -- if-else 双分支结构
select empno,ename,sal,comm,sal+ifnull(comm,0) from emp; -- 如果comm是null，那么取值为0 -- 单分支
select nullif(1,1),nullif(1,2) from dual; --  如果value1等于value2，则返回null，否则返回value1  
-- case相关：
-- case等值判断
select empno,ename,job,
case job 
 when 'CLERK' then '店员'
 when 'SALESMAN'  then '销售'
 when 'MANAGER' then '经理'
 else '其他'
end '岗位',
sal from emp;
-- case区间判断:
select empno,ename,sal,
case 
 when sal<=1000 then 'A'
 when sal<=2000 then 'B'
 when sal<=3000 then 'C'
 else 'D'
end '工资等级',
deptno from emp;
from emp;
-- 5.JSON函数  
-- 6.其他函数
select database(),user(),version() from dual;

多行函数

对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称分组函数

多行函数包含： COUNT() ，SUM() ，AVG() ， MAX() ，MIN()

-- 多行函数：
select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp;
select * from emp;
-- 多行函数自动忽略null值
select max(comm),min(comm),count(comm),sum(comm),sum(comm)/count(comm),avg(comm) from emp;
-- max(),min(),count()针对所有类型   sum(),avg() 只针对数值型类型有效
select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;
-- count --计数   
-- 统计表的记录数：方式1：
select * from emp;
select count(ename) from emp;
select count(*) from emp;
-- 统计表的记录数：方式2
select 1 from dual;
select 1 from emp;
select count(1) from emp;

group_by函数

【1】group by : 用来进行分组

【2】sql展示：

select * from emp;
-- 统计各个部门的平均工资 
select deptno,avg(sal) from emp; -- 字段和多行函数不可以同时使用
select deptno,avg(sal) from emp group by deptno; -- 字段和多行函数不可以同时使用,除非这个字段属于分组
select deptno,avg(sal) from emp group by deptno order by deptno desc;
-- 统计各个岗位的平均工资
select job,avg(sal) from emp group by job;
select job,lower(job),avg(sal) from emp group by job;

having分组后筛选

-- 统计各个部门的平均工资 ,只显示平均工资2000以上的  - 分组以后进行二次筛选 having
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000 order by deptno desc;
-- 统计各个岗位的平均工资,除了MANAGER
-- 方法1：
select job,avg(sal) from emp where job != 'MANAGER' group by job;
-- 方法2：
select job,avg(sal) from emp group by job having job != 'MANAGER' ;
-- where在分组前进行过滤的，having在分组后进行后滤。

单表查询总结

【1】select语句总结
select column, group_function(column)
from table
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column];

注意：顺序固定，不可以改变顺序

【2】select语句的执行顺序
from—where — group by– select - having- order by

【3】单表查询练习：

-- 单表查询练习：
-- 列出工资最小值小于2000的职位
select job,min(sal)
from emp
group by job
having min(sal) < 2000 ;
-- 列出平均工资大于1200元的部门和工作搭配组合
select deptno,job,avg(sal)
from emp
group by deptno,job
having avg(sal) > 1200
order by deptno;
-- 统计[人数小于4的]部门的平均工资。 
select deptno,count(1),avg(sal)
from emp
group by deptno
having count(1) < 4
-- 统计各部门的最高工资，排除最高工资小于3000的部门。
select deptno,max(sal)
from emp 
group by deptno
having max(sal) < 3000;

多表查询

99语法：交叉连接，自然连接，内连接查询

【1】多表查询引入：
实际开发中往往需要针对两张甚至更多张数据表进行操作，而这多张表之间需要使用主键和外键关联在一起，然后使用连接查询来查询多张表中满足要求的数据记录。

一条SQL语句查询多个表，得到一个结果，包含多个表的数据。效率高。在SQL99中，连接查询需要使用join关键字实现。

提供了多种连接查询的类型： cross natural using on

交叉连接（CROSS JOIN）是对两个或者多个表进行笛卡儿积操作，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。比如：有两个表，左表有m条数据记录，x个字段，右表有n条数据记录，y个字段，则执行交叉连接后将返回m*n条数据记录，x+y个字段。笛卡儿积示意图如图所示。

【2】sql展示：

-- 查询员工的编号，姓名，部门编号：
select * from emp;
select empno,ename,deptno from emp;
-- 查询员工的编号，姓名，部门编号,部门名称：
select * from emp; -- 14条记录
select * from dept; -- 4条记录 
-- 多表查询 ：
-- 交叉连接：cross join
select * 
from emp
cross join dept; -- 14*4 = 56条 笛卡尔乘积 ： 没有实际意义，有理论意义
select * 
from emp
join dept; -- cross 可以省略不写，mysql中可以，oracle中不可以
-- 自然连接：natural join 
-- 优点：自动匹配所有的同名列 ,同名列只展示一次 ，简单
select * 
from emp
natural join dept;
select empno,ename,sal,dname,loc 
from emp
natural join dept;
-- 缺点： 查询字段的时候，没有指定字段所属的数据库表，效率低
-- 解决： 指定表名：
select emp.empno,emp.ename,emp.sal,dept.dname,dept.loc,dept.deptno
from emp
natural join dept;
-- 缺点：表名太长
-- 解决：表起别名
select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
from emp e
natural join dept d;
-- 自然连接 natural join 缺点：自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列：
-- 解决： 内连接 - using子句：
select * 
from emp e
inner join dept d -- inner可以不写
using(deptno) -- 这里不能写natural join了 ,这里是内连接
-- using缺点：关联的字段，必须是同名的 
-- 解决： 内连接 - on子句：
select * 
from emp e
inner join dept d
on (e.deptno = d.deptno);
-- 多表连接查询的类型： 1.交叉连接  cross join  2. 自然连接  natural join  
-- 3. 内连接 - using子句   4.内连接 - on子句
-- 综合看：内连接 - on子句
select * 
from emp e
inner join dept d
on (e.deptno = d.deptno)
where sal > 3500;
-- 条件：
-- 1.筛选条件  where  having
-- 2.连接条件 on,using,natural 
-- SQL99语法 ：筛选条件和连接条件是分开的

99语法：外连接查询

-- inner join - on子句： 显示的是所有匹配的信息
select * 
from emp e
inner join dept d
on e.deptno = d.deptno;
select * from emp;
select * from dept;
-- 问题：
-- 1.40号部分没有员工，没有显示在查询结果中
-- 2.员工scott没有部门，没有显示在查询结果中
-- 外连接：除了显示匹配的数据之外，还可以显示不匹配的数据
-- 左外连接： left outer join   -- 左面的那个表的信息，即使不匹配也可以查看出效果
select * 
from emp e
left outer join dept d
on e.deptno = d.deptno;
-- 右外连接： right outer join   -- 右面的那个表的信息，即使不匹配也可以查看出效果
select * 
from emp e
right outer join dept d
on e.deptno = d.deptno;
-- 全外连接  full outer join -- 这个语法在mysql中不支持，在oracle中支持 -- 展示左，右表全部不匹配的数据 
-- scott ，40号部门都可以看到
select * 
from emp e
full outer join dept d
on e.deptno = d.deptno;
-- 解决mysql中不支持全外连接的问题：
select * 
from emp e
left outer join dept d
on e.deptno = d.deptno
union -- 并集 去重 效率低
select * 
from emp e
right outer join dept d
on e.deptno = d.deptno;
select * 
from emp e
left outer join dept d
on e.deptno = d.deptno
union all-- 并集 不去重 效率高
select * 
from emp e
right outer join dept d
on e.deptno = d.deptno;
-- mysql中对集合操作支持比较弱，只支持并集操作，交集，差集不支持（oracle中支持）
-- outer可以省略不写

-- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级
select * from emp;
select * from dept;
select * from salgrade;
select e.ename,e.sal,e.empno,e.deptno,d.dname,s.* 
from emp e
right outer join dept d
on e.deptno = d.deptno
inner join salgrade s 
on e.sal between s.losal and s.hisal

内连接和外连接的区别

内连接只显示符合连接条件的记录，外连接除了显示符合条件的记录外，还显示表中的记录

99语法：自连接查询

-- 查询员工的编号、姓名、上级编号,上级的姓名
select * from emp;
select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名
from emp e1
inner join emp e2
on e1.mgr = e2.empno;
-- 左外连接：
select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名
from emp e1
left outer join emp e2
on e1.mgr = e2.empno;

92语法：多表查询

-- 查询员工的编号，员工姓名，薪水，员工部门编号，部门名称：
select e.empno,e.ename,e.sal,e.deptno,d.dname
from emp e,dept d
-- 相当于99语法中的cross join ,出现笛卡尔积，没有意义
select e.empno,e.ename,e.sal,e.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno;
-- 相当于99语法中的natural join 
-- 查询员工的编号，员工姓名，薪水，员工部门编号，部门名称，查询出工资大于2000的员工
select e.empno,e.ename,e.sal,e.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno and e.sal > 2000;
-- 查询员工的名字，岗位，上级编号，上级名称（自连接）：
select e1.ename,e1.job,e1.mgr ,e2.ename 
from emp e1,emp e2
where e1.mgr = e2.empno;
-- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级
select e.empno,e.ename,e.sal,e.deptno,d.dname,s.grade 
from emp e,dept d,salgrade s
where e.deptno = d.deptno and e.sal >= s.losal and e.sal <= s.hisal;
-- 总结：
-- 1.92语法麻烦 
-- 2.92语法中 表的连接条件 和  筛选条件  是放在一起的没有分开
-- 3.99语法中提供了更多的查询连接类型：cross,natural,inner,outer

子查询

不相关子查询

单行子查询

-- 单行子查询：
-- 查询工资高于平均工资的雇员名字和工资。
select ename,sal
from emp
where sal > (select avg(sal) from emp);
-- 查询和CLARK同一部门且比他工资低的雇员名字和工资。
select ename,sal
from emp
where deptno = (select deptno from emp where ename = 'CLARK') 
      and 
      sal < (select sal from emp where ename = 'CLARK')
-- 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息  
select * 
from emp
where job = (select job from emp where ename = 'SCOTT') 
      and 
      hiredate < (select hiredate from emp where ename = 'SCOTT')

多行子查询

-- 多行子查询：
-- 【1】查询【部门20中职务同部门10的雇员一样的】雇员信息。
-- 查询雇员信息
select * from emp;
-- 查询部门20中的雇员信息
select * from emp where deptno = 20;-- CLERK,MANAGER,ANALYST
-- 部门10的雇员的职务：
select job from emp where deptno = 10; -- MANAGER,PRESIDENT,CLERK
-- 查询部门20中职务同部门10的雇员一样的雇员信息。
select * from emp 
where deptno = 20 
and job in (select job from emp where deptno = 10)
-- > Subquery returns more than 1 row
select * from emp 
where deptno = 20 
and job = any(select job from emp where deptno = 10)
-- 【2】查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
-- 查询雇员的编号、名字和工资
select empno,ename,sal from emp
-- “SALESMAN”的工资：
select sal from emp where job = 'SALESMAN'
-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
-- 多行子查询：
select empno,ename,sal 
from emp 
where sal > all(select sal from emp where job = 'SALESMAN');
-- 单行子查询：
select empno,ename,sal 
from emp 
where sal > (select max(sal) from emp where job = 'SALESMAN');
-- 【3】查询工资低于任意一个“CLERK”的工资的雇员信息。  
-- 查询雇员信息
select * from emp;
-- 查询工资低于任意一个“CLERK”的工资的雇员信息
select * 
from emp
where sal < any(select sal from emp where job = 'CLERK')
and job != 'CLERK'
-- 单行子查询：
select * 
from emp
where sal < (select max(sal) from emp where job = 'CLERK')
and job != 'CLERK'

数据库对象

事务

事务及其特征

事务（Transaction）是用来维护数据库完整性的，它能够保证一系列的MySQL操作要么全部执行，要么全不执行。

例子1：
举一个例子来进行说明，例如转账操作：A账户要转账给B账户，那么A账户上减少的钱数和B账户上增加的钱数必须一致，也就是说A账户的转出操作和B账户的转入操作要么全部执行，要么全不执行；如果其中一个操作出现异常而没有执行的话，就会导致账户A和账户B的转入转出金额不一致的情况，为而事实上这种情况是不允许发生的，所以为了防止这种情况的发生，需要使用事务处理。
例子2：
在淘宝购物下订单的时候，商家库存要减少，订单增加记录，付款我的账号少100元…操作要么全部执行，要么全不执行

【1】事务的概念
事务（Transaction）指的是一个操作序列，该操作序列中的多个操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位，由DBMS（数据库管理系统）中的事务管理子系统负责事务的处理。
目前常用的存储引擎有InnoDB（MySQL5.5以后默认的存储引擎）和MyISAM（MySQL5.5之前默认的存储引擎），其中InnoDB支持事务处理机制，而MyISAM不支持。

【2】事务的特性
事务处理可以确保除非事务性序列内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的序列，可以简化错误恢复并使应用程序更加可靠。
但并不是所有的操作序列都可以称为事务，这是因为一个操作序列要成为事务，必须满足事务的原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这四个特性简称为ACID特性。

1)原子性
原子是自然界最小的颗粒，具有不可再分的特性。事务中的所有操作可以看做一个原子，事务是应用中不可再分的最小的逻辑执行体。
使用事务对数据进行修改的操作序列，要么全部执行，要么全不执行。通常，某个事务中的操作都具有共同的目标，并且是相互依赖的。如果数据库系统只执行这些操作中的一部分，则可能会破坏事务的总体目标，而原子性消除了系统只处理部分操作的可能性。

2) 一致性
一致性是指事务执行的结果必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性来保证的。
例如：在转账时，只有保证转出和转入的金额一致才能构成事务。也就是说事务发生前和发生后，数据的总额依然匹配。

3) 隔离性
隔离性是指各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都是隔离的。也就是说：并发执行的事务之间既不能看到对方的中间状态，也不能相互影响。
例如：在转账时，只有当A账户中的转出和B账户中转入操作都执行成功后才能看到A账户中的金额减少以及B账户中的金额增多。并且其他的事务对于转账操作的事务是不能产生任何影响的。

4)持久性
持久性指事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库，即使数据库出现故障，提交的数据也应该能够恢复。但如果是由于外部原因导致的数据库故障，如硬盘被损坏，那么之前提交的数据则有可能会丢失。

【3】sql展示：使用事务保证转账安全

-- 创建账户表：
create table account(
        id int primary key auto_increment,
        uname varchar(10) not null,
        balance double
);
-- 查看账户表：
select * from account;
-- 在表中插入数据：
insert into account values (null,'丽丽',2000),(null,'小刚',2000);
-- 丽丽给小刚 转200元：
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
-- 默认一个DML语句是一个事务，所以上面的操作执行了2个事务。
update account set balance = balance - 200 where id = 1;
update account set balance = balance2 + 200 where id = 2;
-- 必须让上面的两个操作控制在一个事务中：
-- 手动开启事务：用begin命令也可以
start transaction;
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
-- 手动回滚：刚才执行的操作全部取消：
rollback;
-- 手动提交：
commit;
-- 在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据

事务并发问题

脏读（Dirty read）

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

不可重复读

（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读

（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读

事务隔离级别

事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题。

事务的隔离级别从低到高依次为READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ以及SERIALIZABLE，隔离级别越低，越能支持高并发的数据库操作。

PS：√ 代表会出现问题， ×代表不会出现问题 = 解决问题

-- 查看默认的事务隔离级别  MySQL默认的是repeatable read  
select @@transaction_isolation;  
-- 设置事务的隔离级别   （设置当前会话的隔离级别）
set session transaction isolation level read uncommitted;  
set session transaction isolation level read committed;  
set session transaction isolation level repeatable read;  
set session transaction isolation level serializable;  
start transaction ;
select * from account where id = 1;

视图

【1】视图的概念

视图（view）是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表。同基础表一样，视图中也包含了一系列带有名称的列和行数据，但是数据库中只是存放视图的定义，也就是动态检索数据的查询语句，而并不存放视图中的数据，这些数据依旧存放于构建视图的基础表中，只有当用户使用视图时才去数据库请求相对应的数据，即视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表，如果基本表中的数据发生了变化，视图中相应的数据也会跟着改变。

PS:视图本质上就是：一个查询语句，是一个虚拟的表，不存在的表，你查看视图，其实就是查看视图对应的sql语句

【2】视图的好处

简化用户操作：视图可以使用户将注意力集中在所关心地数据上，而不需要关心数据表的结构、与其他表的关联条件以及查询条件等。

对机密数据提供安全保护：有了视图，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，避免机密数据（如，敏感字段“salary”）出现在不应该看到这些数据的用户视图上。这样视图就自动提供了对机密数据的安全保护功能

【3】SQL展示：

-- 创建/替换单表视图：
create or replace view myview01
as
select empno,ename,job,deptno 
from emp
where deptno = 20
with check option;
-- 查看视图：
select * from myview01;
-- 在视图中插入数据：
insert into myview01 (empno,ename,job,deptno) values (9999,'lili','CLERK',20);
insert into myview01 (empno,ename,job,deptno) values (8888,'nana','CLERK',30);
insert into myview01 (empno,ename,job,deptno) values (7777,'feifei','CLERK',30); 
-- > 1369 - CHECK OPTION failed 'mytestdb.myview01'
-- 创建/替换多表视图：
create or replace view myview02
as 
select e.empno,e.ename,e.sal,d.deptno,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where sal > 2000 ;
select * from myview02;
-- 创建统计视图：
create or replace view myview03
as
select e.deptno,d.dname,avg(sal),min(sal),count(1)
from emp e
join dept d
using(deptno)
group by e.deptno ;
select * from myview03;
-- 创建基于视图的视图：
create or replace view myview04
as
select * from myview03 where deptno = 20;
select * from myview04;

存储过程

【1】什么是存储过程(Stored Procedure)

通过前面章节的学习，我们已经知道SQL是一种非常便利的语言。从数据库抽取数据，或者对特定的数据集中更新时，都能通过简洁直观的代码实现。

但是这个所谓的“简洁”也是有限制，SQL基本是一个命令实现一个处理，是所谓的非程序语言。

在不能编写流程的情况下，所有的处理只能通过一个个命令来实现。当然，通过使用连接及子查询，即使使用SQL的单一命令也能实现一些高级的处理，但是，其局限性是显而易见的。例如，在SQL中就很难实现针对不同条件进行不同的处理以及循环等功能。

这个时候就出现了存储过程这个概念，简单地说，存储过程就是数据库中保存(Stored)的一系列SQL命令（Procedure）的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个小程序。

【2】存储过程的优点

1) 提高执行性能。存储过程执行效率之所高，在于普通的SQL语句，每次都会对语法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。

2) 可减轻网络负担。使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担。
3) 可将数据库的处理黑匣子化。应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了

【3】图解：

【4】展示存储过程：

-- 定义一个没有返回值 存储过程
-- 实现：模糊查询操作：
select * from emp where ename like '%A%';
create procedure mypro01(name varchar(10))
begin
        if name is null or name = "" then
                select * from emp;
        else
    select * from emp where ename like concat('%',name,'%');
        end if;	
end;
-- 删除存储过程：
drop procedure mypro01;
-- 调用存储过程：
call mypro01(null);
call mypro01('R');
-- 定义一个  有返回值的存储过程：
-- 实现：模糊查询操作：
-- in 参数前面的in可以省略不写
-- found_rows()mysql中定义的一个函数，作用返回查询结果的条数
create procedure mypro02(in name varchar(10),out num int(3))
begin
        if name is null or name = "" then
                select * from emp;
        else
    select * from emp where ename like concat('%',name,'%');
        end if;	
        select found_rows() into num;
end;
-- -- 调用存储过程：
call mypro02(null,@num);
select @num;
call mypro02('R',@aaa);
select @aaa;

MySQL高级

MySQL体系结构

第一层连接层

输入用户名和密码主要是完成授权认证，然后校验每一个客户端的权限

完成连接的处理，以及安全方案，检查是否超过最大连接数等等

第二层服务层

有SQL接口，解析器，查询优化器，缓存都是在服务层

第三层存储引擎层

有很多存储引擎供我们选择，可插拔式的存储引擎

索引是在存储引擎实现的，不同的存储引擎，索引结构式不一样的，默认InnoDB

第四层磁盘层

最后的数据是存储在磁盘中的，日志数据等等都存储在这

存储引擎

什么是存储引擎？

存储引擎是基于表的，不是基于库的，也就是说一个数据库的多张表可以用不同的存储引擎，在创建表的时候可以指定存储引擎，默认是InnoDB

存储引擎特点

事务、外键、行级锁

InnoDB 5.5版本之后是高可靠性和高性能的通用存储引擎。

特点 DML 数据的增删改语句遵循ACID模型，原子性，持久性，一致性，隔离性，支持事务

行级锁，提高并发访问性能。

支持外键约束

文件特点，用innodb存储的数据最终会变成xxx.ibd，xxx代表表名，这个文件存储该表的表结构（frm、sdi），数据和索引。

然后有一个参数innodb_file_per_table，然后8.0是默认打开的，也就是说每一张表对应一个文件。

然后如果想要查看ibd的文件内容的话直接打开是二进制文件，如果想要查看表格式的话可以用一个命令

1	ibd2sdi 表名.ibd

这样就能在cmd中看到表的结构了，是一堆json文件

InnoDB逻辑存储结构

分为了下面几块

TableSpace : 表空间

Segment: 段

Extent：区

Page: 页

Row: 行

这几类里面装了什么呢？

首先TableSpace表空间，装了很多个Segment段，然后每个Segement段里面装了很多Extent区，然后区里面装了很多Page页，页包含索引页和数据页，然后页里面包含Row行，就是一行一行的数据，一行中有存储了最后一次操作事务的id，一些指针，还有一列一列的字段。

页是磁盘操作的最小单元，一个页的大小是固定的16k，一个区的大小也是固定的1M，所以说一个区可以存64页

MyISAM

特点：不支持事务，不支持外键，支持表锁，不支持行锁。访问速度快。

MyISAM文件

xxx.sdi 存储表结构信息

xxx.MYD 存储数据

xxx.MYI 存储索引

Memory

这个引擎的表数据是存储在内存中的，由于收到硬件问题或者断点问题的影响，只能将这些表作为临时表或者缓存使用。

特点就是内存存放然后默认hash索引

这里的文件就一个文件xxx.sdi文件只存储表结构，数据都在内存中。

索引(index)

索引是包住MySQL高效获取数据的数据结构（有效）。

没有索引的时候就是全表扫描，性能很低

如果有索引需要对age来维护数据结构，如果需要对age建立索引就需要对age建立个b+树来维护索引。

下面先用二叉树来解释

索引的优缺点

索引的结构

索引是在存储引擎层实现的，所以不同的引擎有不同的存储结构

那么B+树是怎么样的呢？

二叉树的缺点：顺序插入时，会形成一个链表，查询性能大大降低，大数据量情况下，层级较深，检索速度慢。

那么这种在增删改的时候自动旋转调整的二叉树称为自平衡二叉排序树，AVL树也是一种例子，但是每次都要重复计算因子，这个因子是和父节点的高度差来维持的，红黑树则是维持平衡的规则不同，红黑树是根据颜色来维持平衡的

那么就用到了红黑树：大数据量情况下，层级较深，检索速度慢

多路那么为了解决层级较深的问题就有用到了B树：多路平衡查找树

B树是当插入的元素大于key的数量的时候，中间元素会向上分裂

B+树

以一个4阶的b+树为例

所有的元素都会出现在叶子节点，上面的非叶子节点主要起到索引的所用，所有的元素都会出现在叶子节点，叶子节点会组成一个单向链表。

中间元素会向上分裂，并且会形成一个链表，非叶子节点只是一个索引的作用。

在MySQL索引数据结构对经典的B+树进行了优化，在原有的基础上，增加了一个指向相邻叶子节点的链表指针，，就形成了带有顺序指针的B+树，这样提高了区间访问的性能。

Hash索引

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，庵后存储在hash表中。

hash索引的特点，hash索引只能用于对等比较(= ， in) ，不支持范围查询

无法利用索引完成排序操作

查询效率高，通常只需要一次检索就可以了，效率通常会高于b+树索引。前提是不出现哈希碰撞

InnoDB中会有自适应hash功能，会在B+树索引自动的构建为hash索引，具体在后面解释。

索引分类

InnoDB存储中又分两类

那我们在建表的时候没有指定哪个是聚集索引哪个是二级索引

聚集索引选取规则

如果存在主键，主键索引就是聚集索引

如果没有主见，那么会使用第一个唯一索引为聚集索引

如果没有之间也没有唯一索引，那么会自动生成一个rowid作为隐藏的聚集索引

存在主键的情况

那么如果我们要查询数据的时候，是怎么执行的呢？

1	select * from user where name = 'Arm'

会先走二级索引定位到id值，那么查询到10之后再到聚集索引中回表查询，找到10的叶子节点，找到了行数据

找到了对应的叶子节点后，因为一片叶子默认16KB大小，所以理论上可以存多条记录，那么在夜里面会根据二分找到页目录对应的槽位然后找到对应的分组，从而找到对应的记录

每个分组1-8条记录

例题：下面sql语句哪条执行效率高？

1
2
3

select * from user where id = 10;

select * from user where name = 'Arm';

备注：id是主键，name也建立了索引

第一条效率高，因为是直接从聚集索引查询，不需要像第二条一样需要回表查询

例题InnoDB主键索引的B+tree高度为多高呢？

假设：一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB 的指针占用6个字节的空间，主键即使是bigint，占用字节数为8（如果是Int就是4字节，主键占用的空间，由主键的类型决定）

那么高度为2：

指针式6字节的，这里的n指的是一个索引页中可以有的主键数量，暂时待定，算出n约为1170

n 8 + (n +1) 6 = 16 *1024

那么如果高度为2的时候可以存储的数量为1171 * 16 = 18736条记录

高度为3 可以存2000w条记录

总结

索引语法

创建索引

1	create [unique \| fulltext] index index_name on table_name(index_col_name,...);

如果一个索引之关联一个字段，叫单列索引，如果多个字段叫联合索引或者组合索引

如果不指定索引类型默认为普通索引

查看索引

1	show index from table_name

删除索引

1	drop index index_name on table_name

SQL性能分析

为什么需要学习SQL性能分析工具，因为我们需要了解需要知道每条SQL的性能，我们主要优化的是哪种SQL，主要优化的查询语句，那么一般是优化索引。

Sql的执行频率

MySQL客户端连接成功后，通过下面的指令看到数据库常用的操作是哪个，进而开始思考优化方向

1	show [global \| session ] status like 'Com_______';// 后面是模糊匹配Com7个下划线

慢查询日志

如果我们已经知道数据库常用的是select查询，那么我们再看要优化哪些sql语句呢？我们通过慢查询日志，这里面记录了所有执行时间超过制定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志，MySQL的慢查询日志默认没有开启，需要再MySQL的配置文件（/etc/my.cnf）中配置如下信息：

1	show variables like 'slow_query_log';//看到慢查询开关默认关闭

然后在配置文件中配置如下信息

然后重启Mysql服务就好了

1	systemctl restart mysql

然后在

1	tail -f localhost-slow.log

通过这个命令就能看到慢查询日志记录尾部的记录

profile详情

慢查询是超过了预设时间才会记录的，如果没超过但是依旧很长的就不会记录，那么如果业务简单但是时间依旧很长，那么我们就用profile，利用show profiles 能够在做SQL优化时帮助我们了解时间耗费到哪里了，通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

查看是否支持profile

1	select @@have_profiling

查看是否开启

1	select @@profiling

开启profile

1	set profiling = 1;

查看sql语句耗时情况

1	show profiles;

那看到了一个耗时比较长的，我们可以更加详尽的查看详尽信息

1	show profile for query [query id]

然后就能看到详细的sql信息了。

查看cpu的耗费情况

1	show profile cpu for query query_id

explain执行计划

通过在select 语句之前加上explain就可以看到这条sql语句的执行计划，可以清晰的看出来

id ：指代的事sql语句中的执行顺序，如果id相同，执行顺序从上到下，如果id不同，值越大约先执行。(在多表查询的时候就能看出来了)

select_type

表示select的类型，常见的取值有simple(简单表，即不使用表连接或者子查询)、PRIMARY（主查询，即外层查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等

type:

表示连接类型，性能由好到差的连接类型为null、system、const、eq_ref、range、index、all

possible_key

显示可能；应用在这张表的索引

key

实际使用的索引

key_len

表示索引中使用的字节数，该值为索引字段最大可能长度。

rows

MySQL认为必须要执行查询的行数，这是一个估计值

filtered

表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。读了多少返回多少

索引使用

在未建立索引之前，可以执行sql语句，查看sql的耗时

然后根据字段创建索引

1	create index index_name on table_name(col_name);

然后再查询sql然后就能发现索引提高性能的优势了。

索引使用原则

如果索引了多列（联合索引），要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）

如果拥有联合索引但是查询语句的where字段后面没有满足最左前缀法则，就会令索引失效然后全表扫描，跟放的位置没有关系，只要存在就行。

范围查询

联合索引中，出现范围查询（>，<）,范围查询右侧的列索引失效

1	select * from student where age > 30 and city = 'beijing';//那么这时候city的索引就会失效了

规避的话可以用>=就可以规避这个问题了

索引列运算

不要在索引列上进行运算操作，否则索引会失效

字符串不加引号

字符串类型字段，不加引号，索引就会失效

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引会失效。

or连接条件

用Or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么设计的索引都不会被用到

mysql使用索引的时候会先判断如果符合条件的<=30 % 就会走索引否则就会全表扫描

SQL提示

在explain执行查询语句的时候,mysql会自动选择最好的索引，但是也可以不用，在SQL语句中加入一些人为的提示来达到优化操作的目的

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到）减少select *

举个例子假设查询name和age这两个字段都有索引，然后如果select里面有这两个字段，这样InnoDB直接从二级索引中拿到所有需要的数据name和age了，不需要回主键索引查完整的行数据，但是如果用select * 那么当某些字段比如city 没有索引的时候，就需要回表查询性能就会变低。

前缀索引

当字段类型为字符串（varchar\text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

创建前缀索引

1	create index idx_xxx on table_name(column(n));

这个n就代表前缀长度了。那么这个n设置多长合适呢？

我们可以通过下面两个公式来求取

1 2	select count(distinct email) / count() from user; select count(distinct substring(email,1,5)) / count() from user;

单列索引联合索引

在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索时，建议建立联合索引。

索引下推

这个是对于非聚集索引的联合索引查询来说，如果是聚集索引这个机制不生效

原本没有索引下推这个机制是二级索引查到主键id后回表完成再where条件过滤

有了这个机制是直接查到数据后进行where过滤再进行回表，减少回表次数

SQL优化

插入数据优化

insert优化

1	insert into tb_name values(...);

批量插入

1	insert into tb_name values(...),(...)...;

手动提交事务：

1
2
3

start transaction;
insert into tb_name values(...),(...);
commit;

主键顺序插入

主键插入顺序插入从1、2、3。。。这样顺序插入

如果大批量插入数据用load指令

那么如何使用load命令呢？

#客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.sql' into table 'user' fields terminated by ',' lines terminated by '\n'
#用,分隔列，每一行代表一行数据,

主键优化

主键顺序插入的性能高于乱序插入的性能。

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的 也就是说是最后的链表是根据主键顺序存发放的，这种存储方式的表称为 索引组织表（IOT Index Organized Table）

页分裂

页可以为空，也可以一半也可以填充100%。每行包含了2 ~ N 行数据（如果一行数据过大，就会行溢出），根据主键排列。

页可以为空，但是不为空的页最少要有2行数据

主键如果乱序插入的时候，由于为了保证叶子节点链表的顺序，可能会出现页分裂的情况，重新设置链表的指针，导致性能降低。

页合并

在删除数据的时候，删除的时候当页中达到页的50%的时候，InnoDB会开始寻找最靠近的页看看是否可以将两个页合并以优化空间使用

MERGE_THRESHOLD: 合并页的阈值，可以自己设置，默认50%

主键设计原则

插入数据的时候，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键，尽量不要使用UUID做主键或者是其他自然主键，如身份证号。业务操作的时候避免操作主键

order by 优化

1、Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序

2、Using index: 通过有序索引顺序扫描直接返回有序数据，这种情况叫using index，不需要额外排序，操作效率高

简单理解就是需要排序的字段创建索引就会通过using index直接排序，而不需要全表扫描

并且这个时候也遵循最左前缀法则

并且如果一个用desc 一个用asc，这样查找的时候第一个用using index，第二个就会全表扫描了

那么为了解决这个问题可以创建索引的时候指定升序还是降序，如果不设置默认升序

也可以单独设一个单列索引

如果不可避免的出现filesort ，大量数据排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)

group by优化

也是通过覆盖索引来提升性能，满足最左前缀法则

limit优化

什么时候需要通过对limit的优化呢

一个常见的问题就是limit 200000,10,此时需要排序前2000010条记录，但是仅仅返回200000~2000010十条记录，其他记录丢弃，查询排序的代价极大。

这个时候也需要覆盖索引+子查询的形式进行优化

覆盖索引只是跳过了前200000条数据，但是跳过还是需要从第一个开始找到第200000条，只是没有对前面的进行排序了，那么如果再加上子查询，由于子查询直接定位到第200000条就不用。

详细解释一下，没加子查询的时候用limit的时候，需要利用索引然后找到主键然后回表，从第一条开始回表，然后一直到20000条回表，然后前面的200000条回表式不用的，这就造成了浪费。

那么用子查询的时候直接用id来进行子查询，那么就可以省略2000000条回表，因为id是聚集索引，那么就省略了200000条回表了，然后通过id直接定位到200000条数据，然后再利用索引然后回表10条数据就可以拿到10条数据的信息了

count优化

对于Innodb的时候执行count(*) 的时候，需要把数据一行一行的取出，然后累计计数，这个没有什么好的优化方法了，优化思路的时候只能自己计数，自己去维护计数了，用redis或者别的方式来计数了。

count的几种用法

count(*)、count(主键)，count(字段)、count(1)

count(1) 是每一行放1进去，直接进行累加

count(*)专门做了优化，不取值，直接进行累加

update优化

注意用例

-- 一个会话开启一个事务
begin;
update course set name = 'Java' where name = 'JavaEE'

-- 另一个会话开启事务也更新
begin;
update course set name = 'Spring' where id = 4

id和name 不是同一行数据也会更新失败为什么呢？

因为name没有索引，这时候就不是行级锁了，所以他把整张表都锁住了

行锁是针对索引加的锁，不是针对记录加的锁，并且索引不能失效，否则就会进化为表锁

触发器

触发器就是当某个事件发生的时候，数据库自动执行的一段代码

在数据库中的事件就是：INSERT、UPDATE、DELETE

然后时机，可以是BEFORE或ALTER事件发生前后

然后在哪张表上定义触发器

然后设置要执行的SQL语句可以是多条

使用别名old和new来引发触发器中发生变化的记录内容。举个例子原来数据库中有个id字段，然后如果创建了一个触发器，那么在begin和end中就能用old_id和new_id来获取更新前和更新后的id数据了，这个数据是在Undo Log中的具体如何来的可以看UndoLog

语法

create trigger trigger_name
before/after insert/update/delete
on tb_name for each row -- 行级触发器
begin
	trigger_stmt; -- 具体的sql逻辑
end;

show triggers;

drop trigger [schema_name.]trigger_name;-- 如果没有指定schema_name，默认当前数据库

锁

全局锁 锁定数据库中的所有表

经典的使用场景就是做全库的逻辑备份，从而获得一致性视图，保证数据的完整性。

加全局锁

flush tables with read lock;

-- 执行数据备份
mysqldump -uroot -p1234 tb_name>sql_name.sql

-- 解锁
unlock tables;

存在的问题：

1、如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆

2、如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制文件（binlog），会导致主从延迟。

在InnoDB引擎中，可以在备份时加上一个参数

1 2	-- 执行数据备份 mysqldump --single-transaction -uroot -p1234 tb_name>sql_name.sql

这样就可以不加锁来完成一致性数据备份

表级锁 锁定整张表

对于表级锁，主要又分为三类

1、表锁表锁又分为两类，读锁 read lock和写锁 write lock

语法

1
2
3

lock tables 表名  read/write

unlock tables / 客户端断开连接

读锁就是能读不能写，其他客户端是能读不能写

写锁是当前客户端能读也能写，但是其他客户端不能读和写

2、元数据锁

元数据锁加锁过程中是系统自动控制的，无需显示使用，在访问一张表的时候会自动加上。元数据锁主要作用是维护表元数据的数据一致性，在表上有活动实物的时候，不可以对元数据进行写入操作。

元数据就是表字段

在对一张表进行增删改查的时候，会自动加MDL读锁（共享锁），当对表结构进行变更操作的时候（alter table 的时候），加MDL写锁（排他锁）

当前已有锁 \ 请求的锁	`SHARED_READ` (SR)	`SHARED_WRITE` (SW)	`EXCLUSIVE` (X)
`SHARED_READ` (SR)	✅ 兼容	✅ 兼容	❌ 不兼容（等待）
`SHARED_WRITE` (SW)	✅ 兼容	✅ 兼容	❌ 不兼容（等待）
`EXCLUSIVE` (X)	❌ 不兼容（等待）	❌ 不兼容（等待）	❌ 不兼容（等待）

3、意向锁

为了解决行锁和表锁的冲突，加入了意向锁，在加表锁的时候不用检查每行是否加锁，使用意向锁来检查，是否可以添加表锁。因为如果已经加了行锁就不能加表锁了。

1、意向共享锁（IS）：由语句select … lock in share mode添加 ,与表锁共享锁read兼容，与表锁排他锁write互斥。

2、意向排他锁（IX）：由insert,update,delete,select … for update 添加，与表锁共享锁read和排他锁write都互斥。意向锁之间不会互斥。

可以通过下面这个语句查看意向锁的情况

1	select object_schema,object_name,index_name,locak_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁 锁定对应的行数据

行级锁，每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

行级锁分为

1、行锁（Record Lock），锁定单个行记录的锁，防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持。

隔离级别	脏读	不可重复读	幻读	说明
`READ UNCOMMITTED`	✅ 可能	✅ 可能	✅ 可能	最低级别，读未提交数据
`READ COMMITTED`	❌ 不可能	✅ 可能	✅ 可能	读已提交数据（Oracle 默认）
`REPEATABLE READ`	❌ 不可能	❌ 不可能	✅ 可能	可重复读（MySQL InnoDB 默认）
`SERIALIZABLE`	❌ 不可能	❌ 不可能	❌ 不可能	最高级别，完全串行执行

2、间隙锁（Gap Lock）锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事物在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

3、临建锁（Next-Key Lock）: 行锁和间隙锁的组合，同时锁住数据，并且锁住数据前面的所有间隙Gap，在RR隔离级别下支持。

行锁分为共享锁和排他锁

共享锁（S）：允许一个事务去读一行，防止其他事务获得相同数据集的排他锁

排他锁（X）：允许获取排他锁的事务更新数据，防止其他事务获得相同数据集的共享锁和排他锁。

那么在SQL语句，InnoDB会隐式帮我们加锁

默认情况下，InnoDB在RR事务隔离界别运行，InnoDB使用next-key 锁进行搜索和索引扫描，以防止幻读。

1、针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁

2、InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁

间隙锁/临键锁

默认情况下，InnoDB在Repeatable Read 事务隔离级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。

1、索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。

2、索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。

3、索引上的范围查询（唯一索引），会访问到不满足条件的第一个值位置

间隙锁唯一目的是防止其他事务插入间隙。间隙可以共存，一个事务采用的间隙锁不会阻止另一个事物在同一个间隙上采用间隙锁

如果MySQL出现死锁怎么办？

如果线上遇到死锁的情况，可以通过

1	show ENGINE INNODB STATUS;

来获取死锁的日志信息，从而定位到死锁发生的原因。

解决方法：

1、自动检测与回滚：MySQL自带死锁检测机制（innodb_deadlock_detect），当检测到死锁时，数据库会自动回滚其中一个事务，以解除死锁，通常回滚事务中持有最少资源的那个。

2、也有锁等待超时的参数（innodb_lock_timeout），当获取锁的等待时间超过阈值时，就释放锁进行回滚。

3、手动Kill发生死锁的语句

可以通过命令，手动快速的找出被阻塞的事务及其线程ID，然后手动kill他，及时释放资源。

MySQL中int(11) 的11表示什么

MySQL中，int(11)中的11表示显示宽度，并不影响存储大小或数值范围。

在8.0中，显示宽度被正式弃用

显示宽度 当使用ZEROFILL属性时，int(11)表示如果数值的位数少于11位，则会在前面填充-，如果不适用这个属性就会显示 42（前面有9个空格）

存储大小 int类型始终占4字节32位，可以表示的范围是-214783648 到 214783647

InnoDB引擎

逻辑存储结构

表空间（ibd文件），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

段分为数据段、索引段、回滚段，InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段就是B+树的非叶子节点。段用来管理多个Extent（区）

区，表示空间的单元结构，每个区的大小是1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页

页，是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB，为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区

行，InnoDB存储引擎数据是按行进行存放的。

然后行里面有Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列

Roll_pointer：每次对某条记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

架构

下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构

单看内存结构

有四个区域，缓冲区，更改缓冲区，自适应Hash索引，日志缓冲区。

Buffer Pool ：缓冲池，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲区以Page页为单位，底层采用链表数据结构，管理Page,根据状态，将Page分为三种状态

1、free page：空闲page，未被使用

2、clean page：被使用page，数据没有被修改过

3、dirty page：脏页，被使用page，数据被修改过，页中数据与磁盘的数据产生了不一致。

Change Buffer：更改缓冲区 针对于非唯一二级索引页，在执行DML（增删改）语句时，如果这些数据Page没有在缓冲池中，不会直接操作磁盘，而会将数据变更存在更改缓冲区中，在未来数据被读取时，再将数据合并恢复到缓冲池中，再将合并后的数据刷新到磁盘中。

Change Buffer的意义是什么？

和聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引，同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO，有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO

Adaptive Hash Index：自适应hash索引，用于优化对Buffer Pool 数据的查询。InnoDB存储引擎会监控对表上各个索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。

自适应哈希索引，无需人工干预，是系统根据情况自动完成

参数：adaptive_hash_index

Log Buffer：日志缓冲区 用来保存要写入到磁盘中的LOG日志数据（redo log\ undo log）

默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O

参数：

innodb_log_buffer_size: 缓冲区大小

innodb_flush_log_at_trx_commit: 日志刷新到磁盘的时机

磁盘结构

System Tablespace：系统表空间 是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。

参数：innodb_data_file_path

File-Per-Table Tablespaces：每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中。

参数innodb_file_per_table

Genenral Tablespaces：通用表空间，需要通过CREATE TABLESPACE语法创建通用表空间，在创建表时，可以指定该表空间。

1
2
3

CREATE TABLESPACE xxxx ADD
DATAFILE 'file_name'
ENGINE=engine_name;

创建完表空间之后（这个需要自己手动创建不然是不会有的），创建表的时候就可以自己指定表空间

1	CREATE TABLE xxx.... TABLESPACE ts_name;

Undo Tablespaces：撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log 日志

Temporary Tablespaces：InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据

Doublewrite Buffer Files：双写缓冲区，innoDB引擎将数据页从Buffer Pool 刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。

通常在系统表空间文件中，分为两个1MB的区域，合计2MB，每个区域可以存储64个16KB的页。

Redo Log：重做日志，用来实现事务的持久性。该日志文件由两部分组成，重做日志缓冲redo log buffer，以及重做日志文件redo log，前者是在内存中，后者是在磁盘中。当事务提交后会把所有修改信息都存放到该日志中，用于在刷新脏页到磁盘是，发生错误时，进行数据恢复使用。

后台线程

后台线程就是将InnoDB中缓冲区的数据在合适的时候刷新到磁盘文件当中。

分为4类线程

事务原理

事务，是一组操作的集合，是一个不可分割的工作单位，要么同时成功，要么同时失败。

事务的四个特性：ACID，原子性，一致性，隔离性，持久性

redo log 持久性

用来实现事务的持久性。该日志文件由两部分组成，重做日志缓冲redo log buffer，以及重做日志文件redo log，前者是在内存中，后者是在磁盘中。当事务提交后会把所有修改信息都存放到该日志中，用于在刷新脏页到磁盘是，发生错误时，进行数据恢复使用。

redo log执行流程：当事务中执行大量的操作数据的操作会先提交到buffer pool 然后把更改的操作记录在redolog buffer 中，当提交的时候，同时将redolog提供过后台线程刷新到磁盘中，如果刷新失败那么也会事务提交失败，所以就保证了一致性。（如果没有redolog的话他会不定时的把脏页刷新到磁盘中，中间可能会刷新失败从而保证不了一致性）

并且redolog不会把脏页刷新，先把redo log 文件异步刷新到磁盘中，而且日志是追加的，脏页是随机的，日志是顺序的，也就是先写日志，再刷新脏页。（WAL Write-Ahead Logging）

当脏页刷新完之后，redolog 就没用了，所以也会不定时的清理redolog文件

undo log 原子性

在真正修改数据之前，先记下“这个数据原来是什么”

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和 MVCC（多版本并发控制）

undo log 和 redo log 记录物理日志不一样，他是逻辑日志。可以认为当delete一条记录时，undo log 会记录一条对应的insert记录，反之亦然。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段中，内部包含1024个undo log segment。

MVCC

MVCC实现原理

三个隐式字段、Undo log 日志，readView

1	ibd2sdi xxx.ibd

可以查看ibd表结构

undo log 版本链

那么回滚的时候回滚到哪个版本呢？这个就取决于readview

readview

案例1

案例2

最终的隔离性和一致性

MySQL管理

系统数据库

MySQL数据库安装完成后，自带了一下四个数据库，具体作用如下：

常用工具

mysql的客户端工具

mysqladmin

mysqlbinlog

mysqlshow

mysqldump

mysqlimport/source

日志

错误日志

二进制日志

查询日志

更新update操作也会记录

慢查询日志

主从复制

概述

原理

搭建

分库分表

介绍

问题分析

拆分策略

垂直拆分

水平拆分

实现技术

Mycat入门

Mycat配置

Mycat分片

分片规则

首先同一个数hash之后是一个固定的值，那么再对这个值取模就能分片了

然后还有按照天分还能按照自然月分….

Mycat管理及监控

读写分离

单主单从

双主双从

常见问题

主从同步机制

MySQL的主从同步机制是一种数据复制技术，用于将主数据库的数据同步到一个或多个从数据库中，

主要通过二进制日志实现数据的复制。主数据库在执行写操作时，会将这些操作记录到Binlog中，然后异步推送给从数据库，从数据库重放对应的日志即可完成复制。

主从复制类型

异步复制：主库不需要等待从库的响应（性能高，一致性低）
同步复制：主库同步等待所有从库确认收到数据
半同步复制：主库等待至少一个从库确认收到数据
并行复制：多个SQL线程并发执行重放时间，提高复制效率。

主从同步延迟如何处理

首先明确，延迟是必然的，无论怎么优化都无法避免只能减少延迟时间

1、二次查询，如果从库查不到数据，就再去主库查一遍由API封装这个逻辑即可，算是一个兜底策略，比较简单。

2、强制将写之后立马读的操作转移到主库上。这属于代码写死了，比如一些写入之后立马查询的操作，就绑定在一起，写死都走主库，不推荐，比较死板。

3、关键业务读写都走主库，非关键还是读写分离。

4、使用缓存。主库写入后同步到缓存中，这样查询时可以先查询缓存，避免了延迟的问题，不过又引入了缓存数据一致性的问题。

如果组长要求你主导项目中的分库分表，大致的实施流程是？

1、第一步会先分析业务需求：确定数据量以及增长趋势，评估分库分表的必要性（需要一定的预判但是不要过度设计）

2、然后进行技术选型，设计分库分表方案，主要确认一下三点：

分片键：选好业务意义明确，查询高频，数据分布均匀的字段（比如user_id，order_id）
分库分表方式：rang，hash（通过hash取模），一致性hash(一样的hash放一起)。或者结合、确定分片数量。
路由逻辑：Client端（直接向不同的数据库发送请求），Proxy层（加一层类似Mycat），还是自研中间件。

3、考虑现有的数据迁移，需要将现有的数据迁移到新的分库分表结构中，可以通过批量导入，ETL工具或者脚本来完成。

4、部分 SQL的修改和业务逻辑的调整。比如有些业务中连表查询的修改或者本地事务变成分布式事务的情况都需要一一列举出来进行修改。

对数据库进行分库分表可能会引发哪些问题

1、首先是事务的问题

我们使用关系型数据库，有很大一点在于它保证事务的完整性。

而分库之后单机事务就用不上了，必须使用分布式事务来解决，而分布式事务相对而言就比较重了。而且大部分的分布式事务只能保证在最终一致性，所以 业务上会存在数据不一致的场景

2、连表join问题

在一个库中的时候我们还可以利用 JOIN 来连表查询，而跨库之后就无法使用 JOIN 了。

此时的解决方案就是在业务代码中进行关联，也就是先把一个表的数据查出来，然后通过得到的结果再去查另一张表，然后利用代码关联得到最终的结果。

这种方式实现起来比较复杂，不过也是可以接受的。

还可以适当的冗余一些字段，比如以前的表就存储一个关联ID，但是业务时长要求返回对应的Name或者其他字段。这时候就可以把这些字段冗余到当前表中，来去除需要关联的操作。（意思就是假设有个用户表和姓名表，之前需要关联的，现在直接在用户表里加上姓名，然后就不用连表查询姓名了。）

或者通过宽表的形式查询，比如将数据全量存储至ES来查询数据

宽表（Wide Table） 是指将多个业务实体的数据横向拼接成一张大表，包含很多字段，但每一行代表一个完整的业务对象。

ES（Elasticsearch） 一个高性能、分布式的搜索与分析引擎，适合做复杂查询和海量数据检索。

3、全局ID唯一性问题

以前单库单表直接使用数据库得到自增ID即可，但是分库分表之后，使用自增ID会导致重复主键的情况，此时需要利用雪花算法或者其他全局唯一ID发号器来生成唯一主键。

雪花算法：全局唯一，趋势递增，高性能本地生成，不依赖数据库，可解析能从中看出生成时间，机器信息等。

4、排序问题

单表直接通过order by 来进行排序，分库分表后直接利用数据库是无法实现排序的，要么利用分库分表中间件的能力进行汇总排序，要么在自己业务代码中进行排序，要么利用ES存储全量数据进行拍需要。

5、count问题

其实和排序类似，单表可以count，分库分表后无法支持，只能多表count然后业务代码中累加，或者单独搞一个地方来维护总数，要么还是利用ES

从MySQL获取数据，是从磁盘读取的吗？

不是，是从buffer pool 中获取的，MySQL使用缓存机制，比如Innodb存储引擎，将常用的数据和索引缓存在内存中，以提高读取性能。当查询数据时，系统会首先查询缓存，比如缓冲池，如果数据在内存中，则会直接池从内存中读取，如果不在则会从磁盘读取并加载到缓存中。

那mysql中的缓存

缓存池中存了数据页、索引页和其他相关信息。查询数据时，MySQL首先在缓冲池中查找，如果找到则直接返回数据，否则从磁盘读取数据页并将其缓存到缓冲池中。

Innodb存储引擎将表数据和索引页为单位存储，每页通常为16KB，当需要读取某条记录时，mysql会加载包含该记录的整个数据页到缓冲池中。从而减少频繁的I/O操作，所以要记住

mysql是以页为单位加载数据的，而不是记录行为单位

那么buffer pool的机制是什么呢？缓冲池的大小是有限的，如果内存放不下的数据，缓冲池也是需要淘汰机制的，淘汰那些不常被访问的数据页。

按照这个需求我们很容易想到LRU机制，最近最少使用的页面将被淘汰，即维护一个链表，被访问的页面移动到头部，新加的页面也加到头部，同时根据内存使用情况淘汰尾部的页面。

那么buffer pool中分为了老年代和新生代。

老年代默认占3/8 当然可以通过参数调整比例。

当有新页面加入 buffer pool时，插入的位置是老年代的头部，同时新页面在1s内再次被访问的话，不会移动到新生代，等1s后，如果该页面再次被访问才会移动到新生代。

这根我们了解的LRU不太一样，正常的LRU是将新页面插入到头部，且老页面只要被访问就会移动到头部，这样保证最近访问的数据都存在头部，淘汰的只会是尾部的数据。

为什么要有这样的改造呢？

因为innodb中有预读机制。简单理解就是读取连续的多个页面后，innodb认为后面的数据也会被读取，于是异步将这些数据加载到buffer pool中，但是这是一个预判，也就是说页面不一定会被访问，所以如果直接将页面都加入到新生代，可能会污染热点数据，但是如果是新页面加到老年代头部，就不会有这个问题。

那1s机制是为了什么呢

这个机制也是为了处理大量数据访问情况，因为及基本上大数据扫描之后，可能立马又再次范文，正常这时候需要把页面移动到新生代了，但等这波操作结束了，后面还有可能再也灭有请求访问这些页面了，但因为这波扫描把热点数据都淘汰了，就不太好了。

于是乎搞了个时间窗口，新页面在1s内的访问并不会将其移到新生代，这样就不会淘汰热点数据了，然后1s后如果这个页面再次被访问，才会被移动到新生代，这次访问大概率已经是业务的请求，也说明这个数据可能是热点数据。

经过这两个改造，innodb就解决了预读失效和一次性大批量数据访问的问题。

MySQL中的数据排序是怎么实现的

首先，排序过程中，如果排序字段命中索引，会直接用索引来排序，比如sort by id，这种，直接输出聚集索引即可，因为聚集索引本来就是有序的，这种排序性能最高。

如果没有命中的话会升级为filesort，那么这时候如果需要排序的数据比较少，则在sort_buffer中进行排序，如果数据量大，就需要利用磁盘临时文件来排序了，性能就比较差了，具体是通过sort_buffer_size来控制排序缓冲池的大小。

那么如果在内存中排序也就是在排序缓冲池中排序的话，一般是单路排序或者双路排序。

然后有一个叫max_length_for_sort_data参数，默认是4096字节，如果select列的数据长度超过他，则MySQL会采用row_id排序，即把主键和排序字段防止到sort_buffer中排序。

通过b排序完之后，在通过id回表查询得到其他需要查询的列，最终将结果集返回给客户端。

如果没有超过max_length_for_sort_data，就会进行单路排序，就是将select的字段都放入sort_buffer中，排序后直接返回结果集，减少了回表操作，效率更高。

那么如果比sort_buffer_size还要大就会需要磁盘文件临时排序了，一般会使用归并排序，就是先把数据分为多分文件，然后单独对文件进行排序，最后在合并成一个有序的大文件。

为什么Mysql中不推荐使用多表join

数据量小无所谓，但是当数据量大的时候，影响就被放大了，如果让数据库来承担这个复杂的关联操作，需要对连接的每个表进行扫描，匹配和组合，消耗大量的CPU和内存资源，让复杂的关联操作占用了大量的数据库资源，会影响其他查询修改操作。

数据库往往是我们系统的弱点，很多情况下性能瓶颈都在数据库，因此我们需要尽量避免把压力放在数据库上。

所以不推荐多表join。

join的时候注意点

尽量让小表做驱动表，因为驱动表需要全表扫描，而被驱动表是索引查询的。

举个例子 select * from user1 join user2 on(user1.a = user2.a)

让user1作为驱动表，user2是被驱动表

当小表（A）作为驱动表时，数据库可以将整个小表的数据加载到内存中（或至少大部分）。然后，它只需要扫描一次大表（B），在扫描过程中，对于B的每一行，都去内存中的小表A里查找匹配项。

这实际上将嵌套循环优化成了更高效的查找过程（如果小表有索引，则使用索引查找；即使没有，内存中的全表扫描也比磁盘I/O快得多）。

核心优势：大表（B）只被扫描一次，而小表（A）被完全加载到内存中，避免了重复的磁盘I/O。

mysql中delete drop truncate的区别是什么

Delete用于删除行数据，但保留表结构和相关的对象

drop用于完全删除数据库表，包括数据库和结构

truncate只删除数据，不会删除表结构和索引等其他结构

从性能看 drop > truncate > delete

Delete操作会生成binlog、redolog和undolog，所以如果删除全表使用delete的话，性能会比较差，但是他可以回滚，delete的本质上是给行数据大哥标记，并不实际删除，因此delete之后，空间大小不会变化。

在innodb中，每张表数据内容和索引都存储在一个以.ibd后缀的文件中，drop就是整个文件都给删除了，还有.frm后缀的文件也会被删除，这个文件包含的元数据和结构定义文件都删除了，并且无法回滚，表空间会被回收，

truncate会对整张表的数据进行删除，且不会记录回滚日志，所以无法被回滚，并且主键字段是自增的，使用truncate删除后自增重新从1开始。

MySQL中DATETIME和TIMESTAMP类型的区别是什么？

存储方式上：

1、DATETIME：以字符串形式存储，DATETIME(0) = 5字节，DATETIME(6) = 8字节

2、TIMESTAMP：以Unix时间戳形式存储，范围为1970-01-01UTC到2030-01-19UTC，TIMESTAMP(0)=4字节，TIMESTAMP(6) = 7字节。

这里的0和6表示的屎小数秒精度，也就是秒后面的小数位数。

时区处理上：

DATETIME：不受时区影响，存储的时间是具体的日期和时间，不会进行自动转换。

TIMESTAMP：受时区影响，存储是会转换为UTC，取出时会根据连接的时区进行转换，适合吃力跨时区的数据。

默认值DATETIME在MySQL5.6及更早的版本中，DATETIME列不能有自动更新的默认值。在之后的版本中，可以使用DEFAULT和ON UPDATE子句来制定自动初始化和更新行为，但不像TIMESTAMP那么直观。

TIMESTAMP：在高版本中，TIMESTAMP列可以有默认的当前时间戳CURRENT_TIMESTAMP，并且可以使用ON UPDATE CURRENT_TIMESTAMP使其在行更新时自动更新为当前时间戳。

MySQL中如何解决深度分页的问题

优化方式用覆盖索引+子查询

深度分页就是指数据量很大的时候，按照分页访问后面的数据，例如limit99999,10

数据库的三大范式

第一范式：确保每个列的值都是原子值，表中的每个字段只能包含单一的数据项，不允许重复的列和多值字段。

反例 ❌：

学生ID	姓名	课程
1	张三	数学, 英语, 物理

第二范式：在满足第一范式的基础上，确保表中的每个非主键字段完全依赖于之间，而且不是部分依赖，即 非主键字段必须依赖于整个主键

场景举例：

假设有一张表记录学生选课成绩：

学生ID	课程ID	课程名称	成绩
1	101	数学	85
1	102	英语	90
2	101	数学	78

主键是：(学生ID, 课程ID)（复合主键）
问题：课程名称 只依赖于 课程ID，而不依赖于学生ID → 这就是部分依赖

这违反了第二范式。

解决方法 ✅：拆分表

表1：课程表

课程ID	课程名称
101	数学
102	英语

表2：学生成绩表

学生ID	课程ID	成绩
1	101	85
1	102	90

现在：

成绩 完全依赖于 (学生ID, 课程ID)
课程名称 依赖于 课程ID（单独的主键）

第三范式：在满足第二范式的基础上，确保非主键字段之间不依赖，即消除传递依赖。所有非主键字段只能依赖于主键，不应相互依赖

反例 ❌：

学生ID	姓名	所在系	系主任
1	张三	计算机	李教授
2	李四	计算机	李教授
3	王五	数学	赵教授

分析：

主键是 学生ID
姓名、所在系 → 依赖于 学生ID ✅
系主任 → 实际上依赖于 所在系，而不是直接依赖于 学生ID ❌
（即：学生ID → 所在系 → 系主任，这是传递依赖）

违反第三范式。

解决方法 ✅：拆分表

在MySQL中，你使用过哪些函数

1、字符串函数

CONCAT: 连接字符串
SUBSTRING：提取子字符串
LENGTH：返回字符串的长度
REPLACE：替换字符串中的子字符串。
UPPER和LOWER：将字符串转换为大写或小写
TRIM：去除字符串两端的空格
LEFT和RIGHT：返回字符串左边或右边的字符

2、数学函数，用于处理数字运算：

ABS：返回绝对值
CEIL和FLOOR：返回大于或等于/小于或等于指定数的最小整数/最大整数
MOD：返回除法的余数
POWER：返回一个数的指定次幂

3、日期函数，用于处理日期和时间

NOW 返回当前日期和时间
DATE_ADD 和 DATE_SUB 日期加上或减去一个时间间隔
YEAR\MONTH\DAY 提取日提的年份、月份、日期
STR_TO_DATE 将字符串转换为日期

4、聚合函数用于汇总数据

COUNT 计算行数

SUM 计算总和

AVG 计算平均值

MAX 和 MIN 返回最大值和最小值

5、条件函数

IF 条件判断函数

IFNULL 返回第一个非NULL值

CASE 条件选择函数

MySQL中TEXT类型最大可以存储多长的文本

TEXT是非标准字符串类型，除了他还有TINYTEXT \ MEDIUMTEXT \ LONGTEXT

255字节、65535字节（text）、16MB 、 4GB

MySQL中AUTO_INCREMENT列达到最大值会发生什么

如果表的自定义自增ID到达上线限后，再申请下一个ID，得到的值不变！因此会导致报重复值的错误

如果InnoDB表没有配置主键，会创建一个看不见长度为6个字节的row_id，然后如果这个值达到上线后，又会从0开始，然后继续循环。如果插入的新数据的row_id在表中已经存在，那么老数据会被这个新数据覆盖，不会产生任何报错。

对于TINYINT（8位），最大值是127（有符号）或255（无符号）

对于SMALLINT(16位)，最大值是32767有符号，无符号65535

对于MEDIUMINT（24位）

对于INT 32位，最大值是21亿或400亿

对于BIGINT 64位

在MySQL中存储金额数据，应该使用什么数据类型

在数据库中业界常用两种类型来存储金额：bigint和decimal

decimal是一种精确数值类型，用于存储固定精度和小数位的十进制数

他不会出现浮点数float和double那样的精度丢失问题

DECIMAL(M,D) M总位数，D小数位数

bigint代码中用long，decimal代码中使用BigDecimal,如果用bigint来存储的话追求的是高性能，然后单位是分来避免小数的出现。如果是极致追求精确用decimal。

什么是数据库的游标

数据库游标是一个数据库对象，使得应用程序可以朱行访问查询结果集，而不仅仅是一次性处理所有结果，它允许开发者在结果集中的行之间移动，方便进行复杂的数据操作，比如更新或删除特定的行。

为什么需要游标？SQL是集合导向的语言，比如select返回的是一个结果集，大多数操作都是批量处理的。但是在某些场景下，需要逐行处理数据。

这时候，就需要使用游标来实现过程化的数据处理，首先声明游标（DECLARE），定义一个游标，绑定一个SELECT查询，然后打开游标（OPEN）执行查询，生成结果集，游标指向第一行之前。最后提取数据（Fetch）逐行读取数据，游标向下移动。最后关闭游标CLOSE，释放资源。

MySQL中EXISTS和IN的区别是什么

EXISTS用于判断子查询是否返回任何行，通常用于检查某个条件是否满足

IN用于检查某个值是否在指定的集合中，可以是一个子查询或静态值列表

性能差异

EXISTS一旦找到匹配行就会立即返回，通常在子查询返回较大数据集时性能更好。

IN通常会评估整个子查询并构建一个值列表，然后进行匹配，在处理较大数据集时可能性能较差。

在RC隔离级别下，执行更新操作，如果当前行已经被其他事物锁定，数据库会进行什么操作

InnoDB会进行半一致性读操作，来判断当前行的最新版本数据是否影响当前更新操作。

SQL中select \ from \ join \ where \ group by \ having \ order by \ limit的执行顺序是什么？