MySQL及JavaWeb基础总结

查看完整笔记
百度网盘链接: https://pan.baidu.com/s/1AWPzCEtc7APvZenVaL5E-g
提取码: ljhc

MySQL

MySQL基础篇

数据库相关概念

数据库：存储数据的仓库，数据是有组织的进行存储，简称（DataBase）

数据库管理系统：操纵和管理数据库的大型软件，Database Management System（DBMS）

SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准，Struccctured Query Language（SQL）

MySQL数据库的准备

MySQL的安装

启动和停止

启动：services.msc进入window服务

输入指令：net start mysql80

停止：net stop mysql80

客户端连接

方式一：MySQl提供的客户端命令行工具

方式二：系统自带 的命令行工具执行指令

mysql -u root -p

MySQL数据模型

关系型数据库（RDBMS）

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7pSXGGi5-1658061692629)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220628110027932.png)]

SQL

SQL通用语法

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zijvDkRv-1658061692630)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220628110804107.png)]

SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库、表、字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

查询

查询所有的数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();

创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET字符集][COLLATE 排序规则];

IF NOT EXISTS:如果有则不创建，没有就创建。

DEFAULT CHARSET字符集：可指定，例如utf-8

删除

DROP DATABASE[IF EXISTS]数据库名;

IF EXISTS：如果存在则删除，可不写

使用

USE 数据库名；

DDL-表操作-查询

查询当前数据库所有的表

SHOW TABLES;

查询表结构

DESC表名;

查询指定表的建表语句

SHOW CREATE TABLE;

DDL-表操作-创建

CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
字段4 字段4类型[COMMENT 字段4注释],
字段5 字段5类型[COMMENT 字段5注释],
......
字段n 字段n类型[COMMENT 字段n注释]
)[COMMMENT 表注释];

DDL-表操作-数据类型

数值类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YJvEqRHY-1658061692631)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220628155906024.png)]

double(4,1)

第一个参数：表示数据的整体长度

第二个参数：有几位小数

字符串类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Co4k8m9c-1658061692631)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220628160644062.png)]

char：性能好

varchar：性能比char差一些

日期时间类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SknKQMHG-1658061692632)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220628161347116.png)]

DDL-表操作-修改

在表结构中添加字段

ALTER TABLEB表名ADD 字段名 类型（长度）[COMMENT注释][约束];

修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名  新字段名 类型（长度）[COMMENT 注释][约束];

删除字段

ALTER TABLE 表名 DROP 字段名;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

DDL-表操作删除

删除表

DROP TABLE [IF EXISTS] 表名;

删除指定表，并重新创建该表

TRUNCATE TABLE 表名;

在删除表时，表中的数据也会被删除。

MySQL图形化界面工具

DML

DML-添加数据

1.给指定字段添加数据

INSERT INTO 表名（字段名1，字段名2，....） VALUES(值1，值2，....);

2.给全部字段添加数据

INSERT INTO 表名 VALUES (值1，值2，....);

3.批量添加数据

INSERT INTO 表名 VALUES(值1，值2，...)，(值1，值2，...)，(值1，值2，...);

INSERT INTO 表名(字段名1，字段名2，...) VALUES(值1，值2，...)，(值1，值2，...)，(值1，值2，...);

DML-修改数据

UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,....[WHERE 条件];

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

DML-删除数据

DELETE FROM 表名 [WHERE 条件];

DQL(数据查询语言）

基本查询

1.查询多个字段

SELECT 字段1,字段2,...FROM 表名;

SELECT * FROM 表名;

尽量不要使用*去查询全部字段

2.设置别名

SELECT 字段1[AS 别名1]，字段2[AS 别名2]...FROM 表名;

作用：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vcxine4W-1658061692632)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220629142815681.png)]

原本的workaddress变成了工作地址

起别名as可省略。

3.去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5zr2eObF-1658061692633)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220629143649197.png)]

聚合函数

介绍

将一列数据作为一个整体，进行纵向计算。

常见聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法

SELECT 聚合函数(字段列表) FROM 表名;

所有的null值不参与聚合函数运算

分组查询

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名[HAVING 分组后过滤条件];

where与having区别

执行时机不同：where是分组之前进行过滤，不满足分组条件，不参与分组；而having是分组之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

执行顺序：where > 聚合函数 > having

分组之后：查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

排序方式

ASC:升序（默认值）

DESC:降序

如果是多字段排序，当第一个字段相同时，才会根据第二个字段进行排序

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注意：

1.起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。

2.分页查询是数据库的方言，不同的数据库有不同的实现

3.如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10；

DQL的执行顺序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-333hSbEg-1658061692633)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220629204905158.png)]

DCL

DCL：用户管理数据库、用户、控制数据库的访问、权限。

查询用户

USE mysql;
SELECT * FROM user;

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIDE WITH mysql_native_password BY '新密码'; 

删除用户

DROP USER '用户名'@'主机名';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lqeCzyUE-1658061692634)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220630094615447.png)]

DCL-权限控制

权限	说明
ALL、ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

查询权限

SHOW GRANTS FOR '用户名'@'主机名';

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

多个权限之间，使用逗号分隔

授权时，数据库名和表名可以使用*进行通配，代表所有。

函数

函数：是指一段可以直接被另一段程序调用的程序或代码。

字符串函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DxozTVnI-1658061692635)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220630104651945.png)]

substring()

空格也算一个字符

数值函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d0r8wjHH-1658061692635)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220630110530466.png)]

日期函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zi9V7RcN-1658061692635)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220630162045209.png)]

流程控制函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PFPvw59X-1658061692636)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220630163249732.png)]

约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据

目的：保证数据库中数据的正确、有效性和完整性

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MMUKSn9Q-1658061692636)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220630172539439.png)]

约束是作用于表中字段上的，可以在建表/修改表的时候添加约束。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mVZNyu2w-1658061692636)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220630204458862.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NtOmpDcg-1658061692637)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220630205312421.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b2064f5j-1658061692637)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220630205518289.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6pvmoXWS-1658061692637)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220630213557965.png)]

多表查询

指从多张表中进行查询数据

笛卡尔积

笛卡尔积：指在数学中，两个集合A集合和B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）。

清除笛卡尔积的实质就是找到他们之间的连接条件

多表查询的分类

连接查询

内连接：相当于查询A，B交集部分数据。

隐式内连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jX5ajBwx-1658061692638)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220701102950992.png)]

显式内连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E0kORNbJ-1658061692638)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220701103019261.png)]

外连接：

左外连接：查询左表所有数据，以及两张表交集部分数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XZqPZHJE-1658061692638)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220701163542889.png)]

相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据

右外连接：查询右表所有数据，以及两张表交集部分数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qFztXVFu-1658061692639)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220701165621820.png)]

自连接：当前表与自身的连接查询，自连接必须使用表别名

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u3eJE25h-1658061692639)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220701173513698.png)]

自连接查询，可以是内连接查询，也可以是外连接查询。

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...
UNION[ALL]
SELECT 字段列表 FROM 表B ...

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

子查询

SQL语句中嵌套SELECT语句，成为子查询。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iUJHSn5a-1658061692640)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220702141756436.png)]

根据查询结果不同，分为：

1.标量子查询（子查询结果为单个值）

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

常用的操作符：= <> > >= < <=

2.列子查询（子查询结果为一列）

常用操作符：IN, NOT IN,ANY,SOME,ALL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7APR6wxj-1658061692641)(E:\记笔记\图片\image-20220703202309328.png)]

3.行子查询（子查询结果为一行）

常用操作符：=、<>、IN、NOT IN

4.表子查询（子查询结果为多行多列）

常用操作符：IN

事务

事务：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务操作

查看/设置事务提交方式

SELECT @@autocommit;
SET @@autocommit=0;

提交事务

COMMIT;

回滚事务

ROLLBACK

开启事务

START TRANSACTION 或 BEGIN

提交事务

COMMIT;

回滚事务

ROLLBACK

事务的四大特性

原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性:事务完成时，必须使所有的数据都保持一致状态。

隔离性：数据库系统提供的隔离机制，保证事务在不受外部并法操作影响的独立环境下运行。

持久性：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的。

并发事务问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uApEnMR5-1658061692641)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220704081106275.png)]

事务的隔离级别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Og5SsHdA-1658061692642)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220704081915798.png)]

从上往下隔离级别越来越高

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZHUfFfS0-1658061692642)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220704082254655.png)]

进阶篇

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WCZuf0PV-1658061692642)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220704172053141.png)]

存储引擎

存储引擎就是存储数据，建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也被称为表类型。

默认存储引擎：InnDB

查询建表时的引擎

show  create table 表名;

查询当前数据库支持的存储引擎

show engines;

在创建表时指定存储引擎

在建表后使用engine指定

存储引擎的特点

InnoDB

DML操作遵循ACID模型，支持事务；

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

支持外键FOREIGN KEY约束，保证数据的完整性和正确性

表名.ibd，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

查看ibd文件

在黑窗口找到文件保存目录，然后ibd2sdi 表名.ibd；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jnn4FICN-1658061692643)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220704204838352.png)]

MyISM

特点

不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YEGrxgC5-1658061692643)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220704205151740.png)]

Memory

Memory引擎的表数据存储在内存中的，有于受到硬件问题、断电问题的影响，只能将这些表作为零时表或缓存表使用。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DQl3LquB-1658061692643)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220704205711795.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vtCSye9i-1658061692644)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220704205741516.png)]

存储引擎选择

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R4Nj1xqt-1658061692644)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220704205923246.png)]

索引

是帮助MySQL高效获取数据的数据结构。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bm2p6E5p-1658061692644)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705082540904.png)]

索引结构

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6tiQ7y12-1658061692645)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705082940993.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iNk2q9iA-1658061692645)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705111141321.png)]

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DeygHtgI-1658061692646)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705113522273.png)]

Hash索引

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

如果两个或多个键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称hash碰撞），可以通过链表来解决。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OmX3oXVH-1658061692646)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705140721901.png)]

在MySQL中，支持hash索引的是Memory引擎，而innoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

为什么InnoDB存储引擎选择使用B+Tree索引结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1lPk3AAr-1658061692647)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705141801386.png)]

索引的分类

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-anP5iXiX-1658061692647)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705141908693.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D0BFSoiJ-1658061692647)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705142119446.png)]

聚集索引选取规则

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

如果不存在主键，将使用第一个唯一索引作为聚集索引

如果表没有主键，或没有合适的唯一索引，则innoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询

先根据二级索引查询到主键，再根据主键进行查询。

InnoDB主键索引的B+Tree高度为多少

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xjo4UxWJ-1658061692648)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705143829282.png)]

高度为2：

n*8+(n+1)6 = 161024

n为键的个数

索引的操作语法

创建索引

CREATE [UNIQUE|FFULLTEXT] INDEX 索引名 ON 表名(字段名，...);

多个字段可以使用一个索引。

查看索引

SHOW INDEX FROM 表名

删除索引

DROP INDEX 索引名 ON 表名

SQL性能分析

SQL执行频率

MySQL客户端连接成功后，通过show[session|global] status命令可以提供服务器状态信息。通过如下指令，可查询当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

SHOW GLOBAL STATUS LIKE 'Com_'

慢查询日志

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jfEHeT64-1658061692648)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705151840118.png)]

show variables like 'slow_query_log';

查询慢查询日志是否开启

profie详情

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2NWqdmuv-1658061692649)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705152852429.png)]

select @@have_profiling;

查询当前系统是否支持profile

默认profiling是关闭的，

通过以下指令开启

SET profiling = 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6zXx8Swt-1658061692649)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705153333271.png)]

explain执行计划

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9L3XvG5L-1658061692649)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705155741569.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2WLQD4DH-1658061692650)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705163512999.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HYx0IZO7-1658061692650)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705163626225.png)]

索引使用

索引可以大幅度提升查询效率

使用原则

最左前缀法则

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kPl2WQi9-1658061692651)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705164817908.png)]

最左边的列不存在则索引全部失效

与位置无关

范围查询

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

可使用>=,<=来规避索引失效

索引列运算

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

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

模糊查询

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

只要头部加%，索引都会失效

or连接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

or两侧都有索引才会生效

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示

SQL提示是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index：

告诉数据库你用哪个索引，MySQL可以不接受你的建议。

ignore index：

忽略哪个索引

force index：：

强制使用哪个索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rWjAAkpB-1658061692651)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705174138913.png)]

覆盖索引

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EWAzCH6u-1658061692652)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705201239753.png)]

前缀索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BaCKFSIt-1658061692652)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705212147677.png)]

语法

create index idx_xxxx on 表名(column(n));

n代表指定几个字符。

前缀长度

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HoLNCClZ-1658061692653)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705212800705.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VZ2kXa8f-1658061692653)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705213628927.png)]

如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引

索引设计原则

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xU0r4xN0-1658061692654)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705215133160.png)]

SQL优化

插入数据优化

批量插入

手动事务提交

主键顺序插入

如果是大批量数据插入，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IhrNAewl-1658061692654)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705215957508.png)]

主键优化

数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种组织方式的表称为索引组织表。

页分裂

页可以为空，也可以填充100%。每个页包含2~N行数据(如果一行数据太大，会行溢出)，根据主键排列。

主键乱序插入，可能会发生页分裂

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EINx7Po0-1658061692655)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705221607594.png)]

页合并

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NwriLE0k-1658061692655)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705221711050.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-awisij0P-1658061692655)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220705221826276.png)]

主键设计原则

满足业务需求的情况下，尽量降低主键的长度

插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键

尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

order by优化

排序方式

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WRYf3tvT-1658061692655)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706082532427.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xji01O7H-1658061692656)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706082849236.png)]

group by优化

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9USxz9uc-1658061692656)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706083314762.png)]

limit优化

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EullH13U-1658061692657)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706083852412.png)]

count优化

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EaGH1ZjC-1658061692657)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706090826801.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GD3RPV8L-1658061692657)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706091122804.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3NMDe3nn-1658061692657)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706091324718.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-est8mYSL-1658061692658)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706091351436.png)]

update优化

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jeaHsVHb-1658061692658)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706091837677.png)]

视图

视图是一种虚拟存在的表。视图的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

视图只保存了查询的SQL逻辑，不保存查询结果。所引我们在创建视图时，主要的工作就落在创建这条SQL查询语句上。

创建

CREATE [OR REPACE] VIEW 视图名称[（视图列表）] AS SELECT语句 [WITH[CASCADED|LOCAL]CHECK OPTION]

查询

查看创建视图语句

SHOW CREATE VIEW 视图名称

查看视图数据

SELECT* FROM 视图名称......;

修改

CREATE[OR REPLACE] VIEW 视图名称[(列名列表)] AS SEECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION];

ALTER VIEW 	视图名称[(列名列表)] AS SELECT [WITH[CASCADED|LOCAL] CHECK OPTION]

删除

DROP VIEW [IF EXISTS] 视图名称[，视图名称]...

检查选项

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7GXJnG9c-1658061692658)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706103710558.png)]

主要是cascaded和local的区别查

视图的更新

视图中的行与基础表中的行之间必须存在一对一的关系

想要视图可以更新，在创建视图时不要出现以下关键字

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GzEI7tws-1658061692659)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706144423147.png)]

视图的作用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rcjV57Rx-1658061692659)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706144756741.png)]

存储过程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Lxih1StV-1658061692659)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706145603526.png)]

特点

封装，复用

可以接收参数、也可以返回数据

减少网络交互，效率提升

创建存储过程

CREATE PROCEDURE 存储过程名称([参数列表])

BEGIN
--SQL语句
END;

调用

CALL 名称([参数]);

查看

-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SSSCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'XXX';
-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

删除

DROP PROCEDURE [IF EXISTS] 存储过程名称;

在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符

变量

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZH3dYuVl-1658061692660)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706153536729.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xctrpOay-1658061692660)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706153549917.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JYoZAkcG-1658061692662)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706154519043.png)]

用户定义变量：是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用"@变量名"使用就可以。其作用域为当前连接。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TkaQO4Pm-1658061692662)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706161826329.png)]

赋值使用：=

比较使用=

局部变量：是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BECIN…END块。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uIm7ywBM-1658061692662)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706162938298.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dQ7hzeS6-1658061692663)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706162949389.png)]

if条件判断

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WOUOA4Lb-1658061692663)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706163234286.png)]

参数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MmcrVMZk-1658061692663)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706163747430.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yDJrc2ZV-1658061692664)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706163831421.png)]

case

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AMibFn0S-1658061692664)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706164910101.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CjEe9YWf-1658061692664)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706164946152.png)]

循环

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wctFLNDN-1658061692665)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706165413464.png)]

repeat

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EhHbYNeU-1658061692665)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706165947120.png)]

直到满足until条件为止

loop

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0IyYzXzs-1658061692665)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706170432332.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uA7N6ttV-1658061692666)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706170510145.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3g8fAdjU-1658061692666)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706170533628.png)]

游标（光标 cursor）

是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I9HtlU4g-1658061692667)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706201838957.png)]

要先声明普通变量，再申明游标

条件处理程序

可以用来定义在流程控制执行结构执行过程中遇到问题时相应的处理步。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-spmfPQjx-1658061692667)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706203702819.png)]

存储函数

是有返回值的存储过程，存储函数的参数只能是IN类型的。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4fjtrAxz-1658061692667)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706212254674.png)]

触发器

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C1dESldr-1658061692668)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706213647414.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kfdn5Uzs-1658061692668)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706214257902.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kjMO3uYh-1658061692668)(C:\Users\冷\AppData\Roaming\Typora\typora-user-images\image-20220706214525141.png)]