SQL篇-MySQL数据库

1.数据库的基本概念：

名称	全称	简称
数据库	存储数据的仓库，数据是有组织的进行存储	DataBase( DB )
数据库管理系统	操作和管理数据库的大型软件	DataBase Management System( DBMS )
SQL	操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准	Structured Query Language( SQL )

什么是数据库：
- 数据库是“按照数据结构来组织、存储和管理数据的仓库
- 是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合
数据库的特点：
1. 持久化存储数据。数据库也是一个文件系统
2. 方便存储和管理数据
3. 使用了统一的方式操作数据库—SQL
关系型数据库（RDBMS）
- 概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库
- 特点：
  - 使用表存储数据，格式统一，便于维护
  - 使用SQL语言操作，标准统一，使用方便
非关系型数据库（Not Only SQL）：
- 概念：是一种轻量、开源、不兼容 SQL 功能的数据库；
- 特点：
  - 强调 Key-Value 存储和文档数据库的优点

2.MySQL数据库的安装配置：

仓库安装：

[root@localhost ~]# rpm -ivh https://repo.mysql.com//mysql80-community-release-el9-1.noarch.rpm
[root@localhost ~]# dnf install mysql-server -y
[root@localhost ~]# rpm -qa | grep mysql
[root@localhost ~]# systemctl restart mysqld
# 分支一：
[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log     # 输出的信息后面即为临时密码
[root@localhost ~]# mysql -uroot -p
# 分支二：
[root@localhost ~]# vi /etc/my.cnf      # 在配置文件中添加skip-grant-tables，奕斯伟跳过登录权限直接登录
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql               # 直接登录mysql数据库，无需密码
# 先修改root先修改权限
# 再修改root密码
# 再修改root的host字段为%，意味为允许所有主机登录

网络下载安装：

[root@localhost mysql]# wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.32-1.el8.x86_64.rpm-bundle.tar
[root@localhost mysql]# tar xvf mysql-8.0.32-1.el8.x86_64.rpm-bundle.tar
[root@localhost mysql]# dnf localinstall mysql-community-server-8.0.32-1.el8.x86_64.rpm \
> mysql-community-client-8.0.32-1.el8.x86_64.rpm \
> mysql-community-common-8.0.32-1.el8.x86_64.rpm \
> mysql-community-icu-data-files-8.0.32-1.el8.x86_64.rpm \
> mysql-community-client-plugins-8.0.32-1.el8.x86_64.rpm \
> mysql-community-libs-8.0.32-1.el8.x86_64.rpm
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# grep 'temporary password' /var/log/mysqld.log
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '上面文件中的临时密码';
mysql> show databases;
mysql> exit

docker安装MySQL8.0.32：

docker run -id \
-p 3307:3306 \
--name=b_mysql \
-v $PWD/conf:/etc/mysql/mysql.conf.d \       
-v $PWD/logs:/var/logs \
-v $PWD/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=$Rui123456 \
mysql:8.0.32

（可选）MySQL8.0.32版本的数据库进行远程登录需要授权和更改加密规则：

grant all on *.* to '用户名'@'%' with grant option;      -- 给予root用户对所有数据库的所有权限
flush privileges;      -- 刷新权限
update mysql.user set host='%' where user='qv123';      -- 允许远程登录
alter user 'root'@'%' identified with mysql_native_password by '123456';      -- 更新root用户密码
-- 查看用户信息表，plugin值为mysql_native_password的用户才可以被登录；即caching_sha2_password不行
select user,host,plugin from mysql.user;               
# 重启数据库

3.MySQL数据库的服务操作：

启动MySQL服务：（管理员）
1
net start mysql
停止MySQL服务：（管理员）
1
net stop mysql
登录MySQL数据库
1
mysql -u用户名
MySQL数据库的退出：
1
exit

查看最大连接数

1	show variables like '%max_connections%';

查询缓存配置情况
1
show variables like '%query_cache%';

4.数据库的数据类型:

数据类型	说明
int	4字节整数类型
bigint	8字节长整数类型
float	4字节单精度浮点型
double	8字节双精度浮点型（默认）
date	日期，yyyy-MM-dd
datetime	日期，yyyy-MM-dd HH:mm ss
timestamp	时间错类型 yyyy-MM-dd HH:mm ss
char	字符
varchar	字符串 varchar(20):姓名最大20个字符，此数据类型最大存储255字符
text	字符串，最多可容纳64KB
mediumtext	字符串，最多可容纳16MB
longtext	字符串，最多可容纳4GB

5.SQL语言：

概念：
- 结构化查询语言(Structured Query Language)，在关系型数据库上执行数据操作、数据检索以及数据维护的标准语言。
- SQL语言是非过程化的语言
- 使用SQL语句，程序员和数据库管理员可以完成如下的任务
  - 改变数据库的结构
  - 更改系统的安全设置
  - 增加用户对数据库或表的许可权限
  - 在数据库中检索需要的信息
  - 对数据库的信息进行更新

分类：

分类	说明
(DDL)数据定义语言：	定义数据库对象：数据库，表，列等。关键字：创建表/数据库，删除表/数据库，修改表/数据库等
(DML)数据库操作语言：	修改数据：insert,delete,updata等
(DQL)数据查询语言：	查询数据:select,where等
(DCL)数据控制语言：	定义数据的访问权限，及创建用户.关键字：GRANT,REVOKE等
(TCL)事务控制语言	代表关键字为commit、rollback

书写规范：
- 在数据库系统中，SQL语句不区分大小写(建议用大写) 。
- 但字符串常量区分大小写。
- SQL语句可单行或多行书写，以“;”结尾。
- 关键词不能跨多行或简写。
- 用空格和缩进来提高语句的可读性。
- 子句通常位于独立行，便于编辑，提高可读性。
- 单行注释：“–”，多行注释：/* text */

6.操作数据库：CRUD:

1.C(Create):创建:

创建自己的数据库：
1
create database 数据库名;

创建自己的数据库（先判断）：

1	create database if not exists 数据库名;

创建自己的数据库,使用gbk的字符集:

1	create database 数据名 character set gbk;

创建自己的数据库,判断是否存在，使用gbk的字符集:

1	create database if not exists 数据库名 character set gbk;

2.R(show):查询:

查询所有数据库的名称：
1
show databases;
查询某个数据库的创建语句:
1
show create database 数据库名称;

模糊查询数据库：

1
2
3

show databases like '__s%';
# %表示匹配任意个数的任意字符。
# _表示单个任意字符。

3.U(alter):修改:

修改数据库库的字符集:

1	alter database 数据库名 character set 字符集名;

4.D（drop）:删除

删除数据库:
1
drop database 数据库名;
删除数据库,如果存在就删除:
1
drop database if exists 数据库名;

5.使用数据库:

查询当期正在使用的数据库名:
1
select database();
使用数据库:
1
use 数据库名;

6.设置提示符显示当前数据库:

[root@mysql8-0-30 mysql]# vim /etc/my.cnf.d/mysql-server.cnf
[mysql]
prompt=mysql8.0 [\\d]>
[root@mysql8-0-30 mysql]# systemctl restart mysqld

7.数据库的字符集：

mysql字符集：

- latin1支持西欧字符、希腊字符等
- gbk支持中文简体字符
- big5支持中文繁体字符
- utf8几乎支持世界所有国家的字符。
- utf8mb4是真正意义上的utf-8

查看所有的字符集：
1
show variables like 'character%';

修改mysql默认字符集

# 1.在[mysqld]下添加
character-set-server=utf8
init_connect = 'SET NAMES utf8'
# 2.在[client]下添加
default-character-set=utf8

MySQL5.8开始，官方建议使用utf8mb4。

8.MySQL系统数据库：

数据库	含义
mysql	存储MySQL服务器正常运行所需要的各种信息（时区，主从，用户，权限等）
information_schema	提供了访问数据库元数据的各种表和视图，包含数据库，表，字段类型及访问权限等
performance_schema	为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
sys	包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优的诊断的视图

由information_schema数据库负责维护：

分类：

字典	说明
tables	存放数据库里所有的数据表、以及每个表所在数据库
schemata	存放数据库里所有的数据库信息
views	存放数据库里所有的视图信息。
columns	存放数据库里所有的列信息。
triggers	存放数据库里所有的触发器。
routines	存放数据库里所有存储过程和函数
key_column_usage	存放数据库所有的主外键
table_constraints	存放各个表的约束
statistics	存放了数据表的索引

7.表的基本操作:

1.C(Create):创建

创建一个表:

create table 表名(
		列名1 数据类型1,
		列名2 数据类型2,
		列名3 数据类型3,
		........
		列名n 数据类型n
);

创建表并带有注解

create table 表名(
		列名1 数据类型1 comment '...',
		列名2 数据类型2 comment '...',
		列名3 数据类型3 comment '...',
		........
		列名n 数据类型n comment '...'
) comment '...';

复制表结构:

1 2	create table 表名 like 被复制的表名 -- 注意：最后一列不加逗号

复制表结构和表数据：

1	create table 新表名 select * from 原表

2.R(show):查询

查询某个数据库中所有的表名称:
1
2
show tables;
shoW tables from mysql;
查询表结构：
1
desc 表名

3.U(alter):修改

修改表名:

1	alter table 表名 rename to 新的表名;

查询表的字符集:
1
show create database 表名;
修改表的字符集：
1
alter table 表名 character set utf8;

添加一列：

1	alter table 表名 add 列名数据类型;

修改列名称，类型:

1 2	alter table 表名 change 列名新列名新数据类型; alter table 表名 modify 列名新数据类型;

删除列：
1
alter table 表名 drop 列名;

4.D（drop）:删除

1 2	drop table 表名; drop table if exists 表名;

5.表的约束：

约束是在表上强制执行的数据校验规则。约束主要用于保证数据库的完整性。当表中数据有相互依赖性时，可以保护相关的数据不被删除。
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。
根据约束数据列的限制，约束可分为：
- 单列约束：每个约束只约束一列。
- 多列约束：每个约束可约束多列数据。
根据约束的作用范围，约束可分为：
- 列级约束：只能作用在一个列上，跟在列的定义后面，语法：列定义约束类型
- 表级约束：可以作用在多个列上，不与列一起，而是单独定义

列级约束类型：

约束关键字	说明
primary key	主键(非空且唯一)约束，一张表只能有一个字段为主键（实体完整性）
foreign key	外键约束（参照完整性）
unique	唯一约束，规定某个字段在整个表中是唯一的（用户定义的完整性）
check	检查约束，检查列值是否满足一个条件表达式（用户定义的完整性）（8.0.1版本后出现）
not null	非空约束，规定某个字段不能为空（用户定义的完整性）
default	默认值约束
auto_increment	自动增长

创建表时添加列级约束：

create table 表名( 
	id INT primary key auto_increment,
	stuname varchar(20) not null,
    age int default 18,
    seat int unque,
    age int check(age > 18),
    dept_id int,
    foreign key (dept_id) references [外表名](外表列名)
);

修改表的列级约束：

1	alter table [表名] modify [字段名] [字段类型] [约束字段]

删除表的列级约束：

-- 删除NOT NULL约束
alter table [表名] modify [列名] [字段类型];
-- 删除UNIQUE约束
alter table [表名] drop index [唯一约束名]
-- 删除PRIMARY KEY约束
alter table [表名] drop primary key;
-- 删除FOREIGN KEY约束
alter table [表名] drop foreign key [外键名]

表级约束类型有四种：主键、外键、唯一、检查

约束关键字说明

primary key 主键(非空且唯一)约束

foreign key 外键约束

unique 唯一约束，规定某个字段在整个表中是唯一的

check 检查约束

约束关键字	说明
primary key	主键(非空且唯一)约束
foreign key	外键约束
unique	唯一约束，规定某个字段在整个表中是唯一的
check	检查约束

创建表时添加表级约束：

create table tb_employee (
	.......
	constraint 'tb_employee_ibfk_1' FOREIGN KEY (dept_id) REFERENCES 'tb_dept'
(`dept_id`)

修改表的表级约束：

1	alter table 表名 add constraint 约束名约束类型(列名)

删除表级约束：

-- 删除NOT NULL约束
alter table [表名] modify [列名] [字段类型];
-- 删除UNIQUE约束
alter table [表名] drop index [唯一约束名]
-- 删除PRIMARY KEY约束
alter table [表名] drop primary key;
-- 删除FOREIGN KEY约束
alter table [表名] drop foreign key [外键名]

8.MySQL的存储引擎：

1.存储引擎的概念：

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

2.InnoDB，MyISAM，Memory的区别：

InnoDB存储引擎：支持事务安全表（ACID），支持行锁定和外键
MyISAM存储引擎：拥有较高的插入、查询速度，但不支持事务
Memory存储引擎：将表中的数据存储到内存中，为查询和引用其他数据提供快速访问

4.存储引擎的操作：

查询表的存储引擎：
1
show create table 表名;

建表时指定存储引擎

1
2
3

create table 表名(
    ...
) engine=InnoDB;

查看当前数据库支持的存储引擎
1
show engines;

8.DCL(管理用户和授权)：

1.管理用户：

MySql8有新的安全要求，不能像之前的版本那样一次性创建用户并授权。需要先创建用户，再进行授权操作

添加用户：

create user '用户名'@'登录地址' identified by '密码';
-- 允许用户对所有数据库的权限，以及赋权权限
grant all on *.* to '用户名'@'%' with grant option;
-- 撤销用户的所有权限(不包含赋权权限)
revoke all on *.* from '用户名'@'主机名';
-- 收回赋权权限
revoke grant option on *.* '用户名'@'主机名';
-- 刷新权限
flush privileges;

删除用户：
1
drop user '用户名'@'主机名';

修改用户

1	update mysql.user set host='%' where user='qv123'; -- 允许远程登录

修改用户密码：

1
2

-- 查看用户信息表，plugin值为mysql_native_password的用户才可以被登录；即caching_sha2_password不行
alter user 'root'@'%' identified with mysql_native_password by '123456';      # 更新root用户密码

查询用户：

-- 1.切换到mysql数据库
user mysql;
-- 2.查询user表
select * from user;

2.授权：

权限分类：

权限	说明
usage	只能登录
select	插入数据
update	修改数据
delete	删除数据
select	只能查询指定表
alter	修改表
drop	删除数据库 / 表 / 视图
create	创建数据库 / 表
all	能对指定数据库进行任意操作

查询权限:

1	show grants for '用户名'@'主机名';

授予权限：grant

grant [权限1,....] on [数据库名.[表名]] to '用户名'@'主机名' [identified by '密码'] [with option 参数];
# 例：
-- grant usage on *.* to'qv123'@'localhost';  -- 只能登录
-- grant select on springboot.book to 'qv123'@'localhost';  -- 只能查询指定表

with option参数：

选项	说明
MAX_QUERIES_PER_HOUR	定义每小时允许执行的查询数
MAX_UPDATES_PER_HOUR	定义每小时允许执行的更新数
MAX_CONNECTIONS_PER_HOUR	定义每小时可以建立的连接数
MAX_USER_CONNECTIONS	定义单个用户同时可以建立的连接数

撤销权限：revoke

1
2
3

revoke [权限1,....] on [数据库名.[表名]] from '用户名'@'主机名';
# 例：
-- revoke all on *.* from dba@localhost;    -- 移除所有权限

刷新权限：
1
flush privileges;

3.密码策略：

查看临时密码：

1
2
3

awk '/temporary password/ {print $NF}' /var/log/mysqld.log
# or
grep 'password' /var/log/mysqld.log

密码策略：

Policy	Tests Performed
0 or LOW	Length
1 or MEDIUM	Length; numeric, lowercase/uppercase, and special characters
2 or STRONG	Length; numeric, lowercase/uppercase, and special characters; dictionary file

查看密码插件：

show variables like 'validate_password%';
# 内容如下
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

如果没有插件可以通过安装的形式：

1	install component 'file://component_validate_password';

更改密码策略：

方法一：临时修改：

-- 更改密码策略为LOW，改为LOW或0
set global validate_password.policy=LOW;
-- 更改密码长度
set global validate_password.length=0;
-- 设置大小写、数字和特殊字符均不要求。
set global validate_password.special_char_count=0;     -- 特殊字符
set global validate_password.mixed_case_count=0;       -- 大小写
set global validate_password.number_count=0;           -- 数字

方法二：初始化时不启用，只需要在初始化时指定 –initialize-insecure 即可
1
mysqld --initialize-insecure --datadir=/var/lib/mysql --basedir=/usr --user=mysql

方法三：修改配置文件

vim /etc/my.cnf
# 增加内容
[mysqld]
validate_password=off

修改完毕，重启服务

查看数据库当前密码策略

show variables like "%password%";
# 内容如下：
+----------------------------------------------+-----------------+
| Variable_name                                | Value           |
+----------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys | ON              |
| caching_sha2_password_digest_rounds          | 5000            |
| caching_sha2_password_private_key_path       | private_key.pem |
| caching_sha2_password_public_key_path        | public_key.pem  |
| default_password_lifetime                    | 0               |
| disconnect_on_expired_password               | ON              |
| generated_random_password_length             | 20              |
| mysql_native_password_proxy_users            | OFF             |
| password_history                             | 0               |
| password_require_current                     | OFF             |
| password_reuse_interval                      | 0               |
| report_password                              |                 |
| sha256_password_auto_generate_rsa_keys       | ON              |
| sha256_password_private_key_path             | private_key.pem |
| sha256_password_proxy_users                  | OFF             |
| sha256_password_public_key_path              | public_key.pem  |
| validate_password.check_user_name            | ON              |
| validate_password.dictionary_file            |                 |
| validate_password.length                     | 8               |
| validate_password.mixed_case_count           | 1               |
| validate_password.number_count               | 1               |
| validate_password.policy                     | LOW             |
| validate_password.special_char_count         | 1               |
+----------------------------------------------+-----------------+

9.MySQL的DML语句

1. insert/ replace语句：添加数据

1	[insert/replace] into 表名 (列名1,列名2,...列名n) values (值1,值2,...值n);

1	[insert/replace] into 表名 set 字段1=值1,字段2=值2;

1 2	# 复制数据，已经存在一张结构一致的表 [insert/replace] into 新表 select * from 原表

1
2
3

# REPLACE与INSERT语句区别：
# 使用replace语句向表插入新记录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录先被删除，然后再插入新记录。
# 使用replace的最大好处就是可以将delete和insert合二为一

2.delete / truncate/drop语句：删除数据：

1
2
3

delete from 表名 where 条件;       -- 删除数据，保留表结构，可以回滚，如果数据量大，很慢
truncate table 表名 where 条件;    -- 删除所有数据，保留表结构，不可以回滚，速度相对很快,然后在创建一个一模一样的空表
drop table 表名 where 条件;        -- 删除数据和表结构,删除速度最快。

3.update语句：修改数据：

1
2
3

update 表名 set 列名1=值1，列名2=值2,...where 条件;
-- 注意:
--     1.如果不加任何条件，则会将表中所有记录全部删除

4.select语句：查询数据

1.基础查询

查询表中某些记录
1
select 列名，列名 from 表名;
查询表中所有记录
1
select * from 表名;
查询表中不重复的记录
1
select distinct 列名 from 表名;

查询并计算表中某些记录之和

1	select 列名,列名,列名+列名 from 表名;

查询并计算表中某些记录之和（如果值为NULL，值替换为0）
1
select 列名,列名,列名+ifnull(列名,0) from 表名;

查询并计算表中某些记录之和（如果值为NULL），改为0,起别名

1	select 列名 as 列名,列名 as 列名,列名+ifnull(列名,0) as 列名 from 表名;

2.条件查询

where子句后跟条件

运算符	说明
< <= >= = <>	大于小于等于
between…and	之间
like	模糊查询
_ 单个任意字符 %任意多个字符	占位符
is null is not null	判断是否为null
and 或者 &&	和
or 或者	或
not 或者 !	非
regexp	正则表达式

1	SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;

SELECT * FROM student WHERE NAME LIKE "王%";---第一个字为“王”的记录
SELECT * FROM student WHERE NAME LIKE "_麻%";---第二个字为“麻”的记录
SELECT * FROM student WHERE NAME LIKE "__";---名为两个字的记录
SELECT * FROM student WHERE NAME LIKE "%马%";---名中含“马”的记录

1	SELECT NAME FROM worker WHERE FLOOR(salary) BETWEEN 1000 AND 2000;

3.排序查询

排序方式	说明
asc	升序（默认）
desc	降序

1
2
3

select * from 表名 order by 列名;---默认排序（从小到大）
select * from 表名 order by 列名 排序方式;
select * from 表名 order by 列名1 排序方式1,列名2 排序方式2;---如果列名1值相同，列名1相同记录按列名2，排序方式2排序

4.分组查询

select 列名1,avg(math) from 表名 group by 列名1;
select 列名1,avg(math) from 表名 where 条件 group by 列名1;
select 列名1,avg(math) from 表名 where 条件 group by 列名1 having count(id)>2;
-- 注意:
--     where 与having：where在分组之前进行限定，having在分组之后进行限定
--     where后不能跟聚合函数，having可以跟聚合函数的判断

5.分页查询

-- 公式:开始的索引=(当前的页码-1)*每页显示的条数
-- 公式:当前的页码=开始的索引/每页显示的条数 + 1
-- select * from 表名 limit 开始的索引,条数; --第一页，三条记录
SELECT * FROM book LIMIT 0,3;
-- select * from 表名 limit 开始的索引,条数; --第二页，三条记录
SELECT * FROM book LIMIT 3,3;

SELECT * FROM book  WHERE NAME LIKE "%张%" LIMIT 0,3;

6.多表查询:

笛卡尔积:

有两个集合A,B 取这两个集合的所有组成情况；要完成多表查询,需要消除无用的数据

内连接查询

隐式内连接：(自连接查询)

1	select 表1.列名2,表2.列名2 from 表1,表2 where 表1.'列名1'=表2.'列名1'

显式内连接：

1	select 表1.列名2,表2.列名2 from 表1 join 表2 on 表1.'列名1'=表2.'列名1'

外连接查询

左外连接

-- 查询的是左表所有数据以及交集部分
select 字段列表 from 表1 left join 表2 on 条件;

-- 查询所有学生的选课情况（包括没有选课的学生）
SELECT sname,sc.cno FROM student LEFT JOIN sc ON sc.sno = student.sno;

右外连接

1 2	-- 查询的是右表所有数据以及交集部分 select 字段列表 from 表1 right join 表2 on 条件;

7.嵌套查询：

操作符	描述
IN	在指定的集合范围内，多选一
NOT IN	不在指定的集合范围内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

概念：

查询中嵌套查询，称嵌套查询为子查询

带IN的子查询：(列子查询)

1 2	-- IN 和 NOT IN select sno,sname from student where sno in (select sno from sc where cno=1);

带有比较运算符的子查询：（标量子查询）

1 2	select sno,cno from sc x where grade >=(select avg(grade) from sc y where y.sno=x.sno); select * from emp where dept_id=(select id from dept where name='销售部');

带有ANY或All的子查询：(列子查询)

-- >ANY 大于子查询结果中的某个值
-- >ALL 大于子查询结果中的所有值
select sname,sage from student where sage<any(select sage from student where sdept='CS');
select sname,sage from student where sage<all(select sage from student where sdept='CS');

（行子查询）

1 2	-- 通过查询张无忌的薪资和直属领导，查询张无忌的员工信息 select * from emp where (salary,managerid)=(select salary,managerid from emp where name='张无忌');

（表子查询）

1 2	-- 通过查询鹿杖容或者宋远桥的职位和薪资，查询员工信息 select * from emp where (job,salary) in (select job,salary from emp where name ='鹿杖容' or name='宋远桥');

带有EXISTS的谓语查询：

1
2

-- EXISTS的谓语查询不返回任何数据，只产生逻辑真值“true”，或逻辑假值“false”
select sname from student where exists(select * from sc where sno=student.sno and cno='1');

8.集合查询：

关键字	说明
union	并操作（对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序）
union all	并操作（对两个结果集进行并集操作，包括重复行，不进行排序）
intersect	交操作
except	差操作

并操作：union

1	select * from student where sdept='CS' union select * from student where sage<=19;

交操作：intersect

1	select * from student where sdept='CS' intersect select * from student where sage<=19;

差操作：except

1	select * from student where sdept='CS' except select * from student where sage<=19;

9.算术表达式：

1 2	# 对数值型数据列、变量、常量可以使用算数操作符创建表达式（+ - * /） # 对日期型数据列、变量、常量可以使用部分算数操作符创建表达式（+ -）

1 2	-- 运算符不仅可以在列和常量之间进行运算，也可以在多列之间进行运算。 select last_name, salary, salary*12 from employees;

-- 补充：+说明
-- MySQL的+默认只有一个功能：运算符
SELECT 100+80;      -- 结果为180
SELECT '123'+80;    -- 只要其中一个为数值，则试图将字符型转换成数值，转换成功做预算，结果为203
SELECT 'abc'+80;    -- 转换不成功，则字符型数值为0，结果为80
SELECT 'This'+'is'; -- 转换不成功，结果为0
SELECT NULL+80;     -- 只要其中一个为NULL，则结果为NULL

安全等于<=>
1.可作为普通运算符的=
2.也可以用于判断是否是NULL 
where salary is NULL/(is not NULL) ->where salary<=>NULL
示例1：查询emp表奖金为空的员工信息。
select * from emp where comm <=> NULL;
示例2：查询emp表奖金为50000的员工信息
select * from emp where comm <=> 50000;
3.用于判断表达式是否相等，相等返回1，不相等返回0
mysql> select 1+1 <=> 3;
+-----------+
| 1+1 <=> 3 |
+-----------+
|         0 |
+-----------+

10.DQL编写顺序：

10.SQL函数：

概念：是指一段可以直接被另一段程序调用的程序或代码
这里的函数是mysql的内置函数

1.聚合函数

聚合函数需要和group by一起使用

函数	说明
count	计算个数
max	计算最大值
min	计算最小值
sum	计算和
avg	计算平均值

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

1
2
3

select count(列名) from 表名;
select count(ifnull(列名,0)) from 表名;---null替换为0
select count(*) from 表名;

注意：聚合函数排除null值

2.字符串函数：

函数	说明
concat( s1,s2…sn )	字符串拼接，将s1, s2, …, sn拼接成一个字符串
lower( str )	将字符串全部转为小写
upper( str )	将字符串全部转为大写
lpad( str,n,pad )	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad( str,n,pad )	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim( str )	去掉字符串头部和尾部的空格
substring( str,start,len )	返回从字符串str从start位置起的len个长度的字符串

使用示例：

-- 拼接
SELECT CONCAT('Hello', 'World');
-- 小写
SELECT LOWER('Hello');
-- 大写
SELECT UPPER('Hello');
-- 左填充
SELECT LPAD('01', 5, '-');
-- 右填充
SELECT RPAD('01', 5, '-');
-- 去除空格
SELECT TRIM(' Hello World ');
-- 切片（起始索引为1）
SELECT SUBSTRING('Hello World', 1, 5);

3.数值函数：

函数	功能
ceil( x )	向上取整
floor( x )	向下取整
mod( x,y )	返回x/y的模 (x%y)
rand( )	返回0~1内的随机数
round( x,y )	求参数x的四舍五入值，保留y位小数
abs(x)	求绝对值
sqrt	求平方根
pow / power	两个函数的功能相同，返回参数的幂次方
mod	求余数

使用示例：

-- 向上取整
SELECT CEIL(2.5);
-- 返回0~1内的随机数
SELECT rand();
-- 返回x/y的模
SELECT MOD(5,3);

4.日期函数：

函数	功能
curdate( )	返回当前日期（2022-05-28）
curtime( )	返回当前时间（16:17:46）
now( )	返回当前日期和时间（2022-05-28 16:19:09）
year( date )	获取指定date的年份（2022）
month( date )	获取指定date的月份
day( date )	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值+上一个时间间隔expr后的时间值
datediff( date1,date2 )	返回起始时间date1和结束时间date2之间的天数（date1-date2）

使用示例：

-- 返回当前日期
SELECT CURDATE();
-- 返回当前时间
SELECT CURTIME();
-- 返回当前日期和时间
SELECT NOW();
-- 获取指定date的年份
SELECT YEAR(NOW());
-- 返回一个日期/时间值加上一个时间间隔expr后的时间值（ 2092-05-28 16:21:49 ）
SELECT DATE_ADD(NOW(), INTERVAL 70 YEAR);
-- 返回起始时间date1和结束时间date2之间的天数（ -70 ）
SELECT DATEDIFF(NOW(),DATE_ADD(NOW(), INTERVAL 70 DAY));

5.流程函数：

函数	功能
if( value,t,f )	如果value为true，则返回t，否则返回f
ifnull(value1, value2)	如果value1不为空，返回value1，否则返回value2
case when [ val1 ] then [ res1 ] … else [ default ] end	如果val1为true，返回res1，否则返回default默认值
case [ expr ] when [ val1 ] then [ res1 ] … else [ default ] end	如果expr的值等于val1，返回res1，否则返回default默认值

例子：

-- 如果value为true，则返回t，否则返回f
SELECT IF(TRUE,'hello','yes');
-- 如果val1为true，返回res1，否则返回default默认值
select
    name,
    (case when age > 30 then '中年' else '青年' end)
from employee;
-- 如果expr的值等于val1，返回res1， 否则返回default默认值
select
    name,
    (case workaddress when '北京市' then '一线城市' when '上海市' then '一线城市' else '二线城市' end) as '工作地址'
from employee;

11.级联操作

行为	说明
no action	当在父表中删除/更新时，首先检查是否有对应外键，如果有则不允许删除/更新（与restrict一致）
restrict	当在父表中删除/更新时，首先检查否有对应外键，如果有则不允许删除/更新（与no action一致）
cascade	当在父表中删除/更新时，首先检查是否有对应外键，如果有则也删除/更新外键在子表中的记录
set null	当在父表中删除/更新时，首先检查是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为null）
set default	父表有变更时，子表将外键设为一个默认值（Innodb不支持）

概念：修改数据（外键和连接的主键）

添加外键时，添加级联操作或删除级联操作：

1
2

alter table 表名 add constraint 外键名 foreign key(外键列名) references 表名2（主键2) on update cascade;
alter table 表名 add constraint 外键名 foreign key(外键列名) references 表名2（主键2) on delete cascade;

级联更新：
1
on update cascade
级联删除：
1
on delete cascade

12.数据库的备份和还原：

1.MySQL日志管理：

在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这样情况下，我们必须保证数据的安全性和完整性，就需要使用日志来查看或者恢复数据了。
数据库中数据丢失或被破坏可能原因：
- 误删除数据库
- 数据库工作时，意外断电或程序意外终止
- 由于病毒造成的数据库损坏或丢失
- 文件系统损坏后，系统进行自检操作
- 升级数据库时，命令语句不严格
- 设备故障等等
- 自然灾害
- 盗窃

2.MySQL日志类型：

MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：

日志类型	记入文件中的信息类型
错误日志	记录启动、运行或停止时出现的问题
查询日志	记录建立的客户端连接和执行的语句
二进制日志	记录所有更改数据的语句。主要用于复制和即时点恢复
慢日志	记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
事务日志	记录InnoDB等支持事务的存储引擎执行事务时产生的日志

在mysql中，执行SQL语句：
1
mysql> flush logs
在shell中，通过mysqladmin命令执行日志刷新：
1
2
mysqladmin flush-logs
mysqladmin refresh

3.错误日志：

错误日志主要记录如下几种日志：
- 服务器启动和关闭过程中的信息
- 服务器运行过程中的错误信息
- 事件调度器运行一个时间是产生的信息
- 在从服务器上启动从服务器进程是产生的信息

查看当前错误日志配置：

1 2	show global variables like '%log_error%'; show global variables like '%log_warnings%';

4.通用查询日志：

字段说明：

- 启动开关：general_log={ON|OFF}
- 日志文件变量：general_log_file[=/PATH/TO/file]
- 全局日志开关：log={ON|OFF} 该开关打开后，所有日志都会被启用
- 记录类型：log_output={TABLE|FILE|NONE}：

因此，要启用通用查询日志，需要至少配置general_log=ON，log_output={TABLE|FILE}。而general_log_file如果没有指定，默认名是host_name.log。

看看上述几个值的默认配置：

1 2	show global variables like '%general_log%'; show global variables like '%log_output%';

修改：
1
set global general_log=NO;

5.慢查询日志：

MySQL如果启用了 slow_query_log=ON 选项，就会记录执行时间超过long_query_time的查询（初使表锁定的时间不算作执行时间）。日志记录文件为slow_query_log_file[=file_name]，如果没有给出file_name值，默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。

查看慢查询日志：

1 2	# 默认没有启用慢查询，为了服务器调优，建议开启 show global variables like '%slow_query_log%';

1 2	# 开启方法,当前生效，永久有效配置文件中设置 set global slow_query_log=ON;

开启满查询日志：
1
set global slow_query_log=ON;
查看慢查询查询时长超时值：（默认10秒）
1
show global variables like 'long_query_time';

6.二进制日志：

二进制日志（BINLOG）记录了所有的DDL（数据定义语言，对表的操作）语言和DML（数操纵语言，对数据的增删改查）语言，但不包括数据查询（select，show）语句。
作用：
1. 灾难时的数据恢复；2.MySQL的主从复制
默认二进制日志时开启着的，参看二进制文件的存放目录：
1
show variables like '%log_bin%';
开启日志：
- 二进制日志启动开关：（默认开启）
  1
  set global log_bin=NO;
- 注意：在5.6及以上版本一定要手动指定。5.6以下版本默认file_name为$datadir/mysqld-binlog。
- 查看二进制日志的工具为：
  1
  mysqlbinlog
- 二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。
二进制日志的管理：
- 日志滚动。在my.cnf中设定max_binlog_size = 200M，表示限制二进制日志最大尺寸为200M，超过200M后进行滚动。
- MySQL的滚动方式与其他日志不太一样，滚动时会创建一个新的编号大1的日志用于记录最新的日志，而原日志名字不会被改变。每次重启MySQL服务，日志都会自动滚动一次。
- 另外如果需要手动滚动，则使用命令
  1
  flush logs

日志查看：

查看有哪些二进制日志文件：
1
show binary logs;
查看当前正在使用的是哪一个二进制日志文件：
1
show master status;

查看二进制日志内容：

1
2
3

show binlog events in 'binlog.000002';
show binlog events in 'binlog.000002'\G;    -- 键值对显示
show binlog events in 'binlog.000002' from 203;    -- 指定显示从哪个Position（位置）开始

使用命令mysqlbinlog查看二进制日志内容：
1
mysqlbinlog [options] log-files

日志删除：
清空所有的binlog日志：
1
reset master;

删除指定日志的之前日志：

1	purge [master/binary] logs to 'binlog.000004';

二进制日志还原数据：

语法：

1	mysqlbinlog [option] filename \| mysql -uuser -ppassword

option	说明
–start-datetime、–stop-datetime	能够指定恢复数据库的起始时间点和结束时间点
–start-position、–stop–position	能够指定恢复数据的开始位置和结束位置

使用：

1	mysqlbinlog --stop-position=425 binlog.000010 \| mysql-uroot -pAdmin123!

使用mysqlbinlog读取需要的日志内容，使用标准输入重定向到一个sql文件，然后在mysql服务器上导入即可，如下：
1
mysqlbinlog binlog.000001 > /usr/qv123/temp_date.sql

7.MySQL的备份：

备份类型：
- 根据服务器状态，可以分为热备份、温备份、冷备份
  - 热备份：读、写不受影响；
  - 温备份：仅可以执行读操作；
  - 冷备份：离线备份；读、写操作均中止；
- 注：为保证所备份数据的完整性，在停止mysql数据库之前，需要先执行flush tables语句将所有数据写入到数据文件的文本文件里。
- 从对象来分，可以分为物理备份与逻辑备份
  - 物理备份：复制数据文件；
  - 逻辑备份：将数据导出至文本文件中，内容是一条条sql语句，或者是表内实际数据组成；
- 从数据收集来分，可以完全备份、增量备份、差异备份
  - 完全备份：备份全部数据；
  - 增量备份：仅备份上次完全备份或增量备份以后变化的数据；
  - 差异备份：仅备份上次完全备份以来变化的数据；
逻辑备份优缺点：
- 逻辑备份的优点：
  - 在备份速度上两种备份要取决于不同的存储引擎。
  - 物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表。
  - 逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理。
  - 逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高。
  - 逻辑备份也对保持数据的安全性有保证。
- 逻辑备份的缺点：
  - 逻辑备份要对RDBMS产生额外的压力，而裸备份无压力。
  - 逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩
  - 逻辑备份可能会丢失浮点数的精度信息。

备份内容：

数据文件
日志文件（比如事务日志，二进制日志）
存储过程，存储函数，触发器
配置文件（十分重要，各个配置文件都要备份）
用于实现数据库备份的脚本，数据库自身清理的Crontab等……

MySQL自带的备份工具：

mysqldump，是mysql数据库管理系统自带的逻辑备份工具，支持所有引擎，MyISAM引擎是温备，InnoDB引擎是热备，备份速度中速，还原速度非常非常慢。但是在实现还原的时候，具有很大的操作余地，具有很好的弹性。

备份某库的某表，如果有多张表，用空格隔开

1	[root@mysql8-0-30 mysql]# mysqldump -u[用户] -p[密码] [数据库] [表] > [路径] # 备份到指定文件中

备份某库的里面的所有表

1	[root@mysql8-0-30 mysql]# mysqldump -u[用户] -p[密码] [数据库] > [路径]

还原备份

1	[root@mysql8-0-30 mysql]# mysql -u[用户] -p[密码] [数据库] < [路径] # 还原指定文件中的备份

1	mysql > source [路径] # 先使用数据库，还原指定文件中的备份

备份数据库和该库里面所有的表，指定多个库时用空格隔开

1	[root@mysql8-0-30 mysql]# mysqldump -u[用户] -p[密码] --databases [数据库] > [路径]

备份所有数据库

1	[root@mysql8-0-30 mysql]# mysqldump -u[用户] -p[密码] --all-databases > [路径]

mysqlhotcopy 物理备份工具
- 只支持MyISAM引擎。基本上属于冷备的范畴，物理备份，速度比较快。
- 多用于mysql5.5之前。mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库或单个表，属于裸文件备份（物理备份）
- 本质是使用锁表语句，然后cp或scp。
文件系统备份工具：
- cp命令，冷备份，支持所有引擎，复制命令，只能实现冷备，物理备份。使用归档工具，cp命令对其进行备份的备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。
- lvm 几乎是热备份，支持所有引擎，基于快照(LVM，ZFS)的物理备份，速度非常快，恢复速度也比较快，几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用。而且LVM的限制：不能对多个逻辑卷同一时间进行备份，所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。
其它工具：
- ibbackup：商业工具 MyISAM是温备份，InnoDB是热备份，备份和还原速度都很快，这个软件它的每服务器授权版本是5000美元。
- xtrabackup：开源工具 MyISAM是温备份，InnoDB是热备份，是ibbackup商业工具的替代工具。
- mysqlbackup：ORACLE公司也提供了针对企业的备份软件MySQL Enterprise Backup简称：

13.MySQL视图：

1.视图的概念：

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中真实存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动他生成的。
在SQL语言中的视图View是数据库的外模式
视图是一种常用的数据库对象，它是提供查看和存放数据的另一种途径，可以简化数据库的操作
视图通过以定制的方式显示来自一个或多个表的数据
视图是一种数据库对象，用户可以像查询普通表一样查询视图
视图内其实没有存储任何数据，它只是对表的一个查询
视图的定义保存在数据字典内，创建视图所基于的表称为“基表”
如果修改原视图或者原表数据，视图会同步数据；如果修改视图数据，原视图或者原表不会发生变化
视图的作用和优点：
- 作用：
  - 控制安全
  - 保存查询数据
- 优点：
  - 提供了灵活一致级别安全性。
  - 隐藏了数据的复杂性
  - 简化了用户的SQL指令
  - 通过重命名列，从另一个角度提供数据
视图使用规则：
- 视图必须有唯一命名
- 在mysql中视图的数量没有限制
- 创建视图必须从管理员那里获得必要的权限
- 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
- 在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY。
- 视图不能索引，也不能关联触发器或默认值
- 视图可以和表同时使用

2.建立视图：

语法：

create view [or replace] [视图名] as select [列名...] from 表名 where [条件] [with cascaded check option]
例：
create view s_address as select * from t_address;
create view s_address(id,name,time) as select * from t_address;
-- or replace（表示替换，可以省略）
create or replace view 视图名 as select 列名... from 表名 where 条件    
-- with check option视图的检查选项,MySQL会通过视图检查正在更改的每个行,插入，更新，删除，以使其符合视图的定义
create or replace view 视图名 as select 列名... from 表名 where 条件 with check option

MySQL基于另一个视图创建视图，，他会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：cascaded和local，默认为cascaded

1
2
3

create or replace view 视图名2 as select 列名... from 表名 where 条件 with cascaded check option
-- 因为视图1依据视图2而生，又因为视图2添加了检查选项，所以视图1也默认添加
create or replace view 视图名1 as select 列名... from 视图名2 where 条件

1
2
3

create or replace view 视图名2 as select 列名... from 表名 where 条件 with local check option
--  不检查视图1
create or replace view 视图名1 as select 列名... from 视图名2 where 条件

2.更新视图：

方式一：

1 2	create view s_address(id,name,time) as select * from t_address; create view 视图名 as select 列名... from 表名 where 条件

方式二：

1 2	alter view s_address(id,name,time) as select * from t_address; alter view 视图名 as select 列名... from 表名 where 条件

4.删除视图：

1
2
3

drop view 视图名 
-- cascade删除视图1和由它所导出的所有视图
drop view 视图名 cascade

5.查询视图：

-- 查看创建视图的语句
show create view 视图名称;
-- 查看视图数据
select * from 视图......;

6.更新数据：

概述：由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新
要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
1. 聚合函数或窗口函数（SUM()，MIN()，MAX()，COUNT()等）
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION 或者 UNION ALL

插入insert

1	insert into 视图名 values(值1,....);

删除delete
1
delete from 视图名 where 条件

修改update

1	update 视图名 set 视图列1=新值 where 视图列2=值;

7.视图的作用：

视图能简化用户的操作
视图使用户能以多种角度看待同一数据
视图对重构数据库提供了一定程度的逻辑独立性
视图能够对机密数据提供安全保护
适当利用视图可以清晰地表达查询

14.MySQL索引：

1.索引的概述：

索引是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查询算法，这种数据结构就是索引。
优缺点：
- 优点：
  - 提高数据检索效率，降低数据库的IO成本
  - 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
- 缺点：
  - 索引列也是要占用空间的
  - 索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE

2.B树：

B树是一种树状数据结构，是一种二叉查找树,也称多路并衡二叉树；这种数据结构中，一个结点允许多于两个key的存在。
它能够存储数据、对其进行排序并允许以O(logn)的时间复杂度进行查找、顺序读取、插入和删除等操作。
B树的特性：
- 每个结点最多有M-1个key，并且以升序排列；
- 每个结点最多能有M个子结点；
- 根结点至少有两个子结点；
在实际应用中B树的阶数一般都比较大（通常大于100），所以，即使存储大量的数据，B树的高度仍然比较小，这

样在某些应用场景下，就可以体现出它的优势。
B树存储数据：
B树在磁盘文件中的应用：
- 在我们的程序中，不可避免的需要通过IO操作文件，而我们的文件是存储在磁盘上的。计算机操作磁盘上的文件是
  
  通过文件系统进行操作的，在文件系统中就使用到了B树这种数据结构。

3.B+树：

B+树是对B树的一种变形树，它与B树的差异在于：
- 非叶结点仅具有索引作用，也就是说，非叶子结点只存储key，不存储value；
- 树的所有叶子结点构成一个有序链表，可以按照key排序的次序遍历全部数据。
B+树存储数据：
- 若参数M选择为5，那么每个结点最多包含4个键值对，我们以5阶B+树为例，看看B+树的数据存储。
B树和B+树的对比：
- B树的优点：
  
  由于B树的每一个节点都包含key和value，因此我们根据key查找value时，只需要找到key所在的位置，就能找到value，但B+树只有叶子结点存储数据，索引每一次查找，都必须一次一次，一直找到树的最大深度处，也就是叶子结点的深度，才能找到value。
- B+树的优点：
  
  1.由于B+树在非叶子结点上不包含真正的数据，只当做索引使用，因此在内存相同的情况下，能够存放更多的
  
  key。 2.B+树的叶子结点都是相连的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序
  
  排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历
B+树在数据库中的应用：
- 在数据库的操作中，查询操作可以说是最频繁的一种操作，因此在设计数据库时，必须要考虑到查询的效率问题，
  
  在很多数据库中，都是用到了B+树来提高查询的效率；
- 未建立主键索引查询：
- 建立主键索引查询：
- 区间查询：
  - 执行 select * from user where id>=12 and id<=18 ,如果有了索引，由于B+树的叶子结点形成了一个有序链表，所以我们只需要找到id为12的叶子结点，按照遍历链表的方式顺序往后查即可，效率非常高。

4.索引的结构：

索引结构	描述
B+Tree	最常见的索引类型，大部分引擎都支持B+树索引
Hash	底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree(空间索引)	空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-Text(全文索引)	是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene, Solr, ES

索引	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-Tree索引	不支持	支持	不支持
Full-text	5.6版本后支持	支持	不支持

二叉树的缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢
为了解决上述问题，可以使用 B-Tree 结构。
B-Tree (多路平衡查找树) 以一棵最大度数（max-degree，指一个节点的子节点个数）为5（5阶）的 b-tree 为例（每个节点最多存储4个key，5个指针）
MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。
- 特点：
  - Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、…）
  - 无法利用索引完成排序操作
  - 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引

5.为什么InnoDB存储引擎使用B+tree索引结构：

相对于二叉树，层级更少，搜索效率高；
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
相对Hash索引，B+tree支持范围匹配及排序操作

6.索引的分类：

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	primary
唯一索引	避免同一个表中某数据列中的值重复	可以有一个	unique
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	fulltext

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

二级索引选取：

除去主键，其他字段（name，gender）创建的索引就是二级索引

回表查询：

如：select * from user where name=’ Arm ‘; 会在二级索引中拿到id值，到聚集索引中查询

7.多列索引：

使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。
如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。
也就是说多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

8.索引的语法：

创建索引：

create [unique | funlltext | spatial] index 索引名 on 表名(字段名[(length)] [asc | desc])      
例：
-- name字段为姓名字段，该字段的值可能会重复，为该字段创建（常规索引）
create index idx_user_name on tb_user(name);
-- phone手机号字段的值非空，且唯一，为该字段创建（唯一索引）
create unique index idx_user_phone on tb_user (phone);
-- 为profession, age, status创建（联合索引）
create index idx_user_pro_age_stat on tb_user(profession, age, status);

create table index1(
    id int,
    Name varchar(20),
    Sex boolean,
    index index4_st(id)
);

查看索引：
1
show index from 表名

修改索引：

1	alter table [表名] add index [索引名] on (列名)

删除索引：

1
2
3

drop index 索引名 on 表名
例：
drop index idx_user_email on tb_user;

9.MySQL使用索引的场景：

快速查找符合where条件的记录
快速确定候选集。若where条件使用了多个索引字段，则MySQL会优先使用能使候选记录集规模最小的那个索引，以便尽快淘汰不符合条件的记录。
如果表中存在几个字段构成的联合索引，则查找记录时，这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找。
多表做join操作时会使用索引（如果参与join的字段在这些表中均建立了索引的话）。
若某字段已建立索引，求该字段的min()或max()时，MySQL会使用索引
对建立了索引的字段做sort或group操作时，MySQL会使用索引

10.MySQL索引的优化：

针对于数据量较大，且查询比较频繁的表建立索引
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

15.MySQL的锁：

1.锁的概述：

锁是计算机协调多个进程或线程并访问某一资源的机制。
在数据库中，除传统的计算机资源（CPU，RAM，I/O）的争用以外，数据也是一种供许多用户共享资源。如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个因素。从这个角度来说，锁对数据库而言显得尤为重要，也更为复杂

分类：MySQL中的锁，按照锁的粒度分，分为以下三类

MySQL锁的分类	说明
全局锁	锁定数据库中的所有表，锁的粒度小
表级锁	每次操作锁住整张表，锁定粒度大，锁冲突的概念最高，并发度最低
行级锁	每次操作锁住对应的行，锁定粒度最小，锁冲突的概念最低，并发度最高

2.全局锁：

全局锁就是对整个数据库实例加锁，加锁后整个实例处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被堵塞
锁的粒度小
其典型的使用场景：做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性

一致性数据备份：

给数据库加上全局锁（加锁后所有的更新操作都不能执行）：
1
flush tables with read lock;

进行数据备份（命令行）：

1
2
3

mysqldump -u用户名 -p密码 -h地址 数据库名 > 路径+文件名.sql
例：
mysqldump -uroot -p -h192.168.2.25 springboot > D:/user.sql

取消全局锁：
1
unlock tables;

数据库中加全局锁，是一个比较重的操作，存在以下问题
- 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
- 如果在从表上备份，那么可以在备份期间从库不能执行主库同步过来地二进制日志（binlog），会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数–single-transaction参数来完成不加锁的一致性数据备份（命令行）。

1
2
3

mysqldump --single-transaction -u用户名 -p密码 -h地址 数据库名 > 路径+文件名.sql
例：
mysqldump --single-transaction -uroot -p -h192.168.2.25 springboot > D:/user.sql

3.表级锁：

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概念最高，并发度最低。应用在MyISAM，InnoDB，BDB等存储引擎中。

对于表级锁，主要分为以下三类：

表锁的分类	说明
表锁	手动控制，分为：表共享读锁和表独占写锁
元数据锁	自动控制，为了避免DML(增删改)与DDL冲突，保证读写的正确性
意向锁	为了避免DML在执行时，加行锁与表锁的冲突

表锁：

表共享读锁（read lock）：会阻塞客户端和其他客户端对表的写操作
表独占写锁（write lock）：会阻塞其他客户端对表的读操作和写操作

语法：

1
2
3

加锁：lock tables 表名 read/write;   -- read表示加表共享读锁，write表示加表独占写锁
例：
lock tables ssm read;

1
2
3

释放锁：unlock tables   /  客户端断开;
例:
unlock tables;

元数据锁（meta data lock,MDL）
- MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。
- 为了避免DML(增删改)与DDL冲突，保证读写的正确性。
- 在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）。
- 查询元数据锁：
  1
  select object_type,object_schema,object_name,lock_duration from performance_schema.metadata_locks;
意向锁
- 为了避免DML在执行时，加行锁与表锁的冲突
- 在InnoDB中引入意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
- 意向共享锁（IS）：与表锁共享锁(read)兼容，与表锁排它锁(write)互斥
- 意向排他锁（IX）：与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不会互斥
- 添加意向锁：
  1
  意向共享锁(IS)：查询语句 lock in share mode;
  1
  意向排他锁(IX)：增删改查语句 for update;
- 查看意向锁及行锁的加锁情况：
  1
  select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

4.行级锁：

行级锁，每次操作锁住对应的行数组。锁定粒度最小，发生锁冲突的概念最低，并发度最高，应用在InnoDB存储引擎中。
InnoDB的数据时基于索引组织的，行锁是通过对索引上的索引项来实现，而不是对记录加的锁。
对于行级锁，主要分为以下三类：
- 行锁(Reord Lock)：锁定单个记录的锁，防止其他事务对此行进行update和delete。在RC，RR隔离级别下都支持。
- 间隔锁(Gap Lock)：锁定索引记录间隔(不含该记录)，确定索引间隔不变，防止其他事务在这个间隔进行insert，产生幻读。在RR隔离级别都支持。
- 临时锁(Next-Key Lock)：行锁和间隔组合，同时锁住数据，并锁住数据前面的间隔Gap，在RR隔离级别下支持。
行锁：InnoDB实现了以下两种类型的行锁：
- 共享锁(S)：允许一个事务去读一行，阻止其他事务获取的相同数据集的排它锁。
- 排它锁(X)：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁个排他锁。
- 不同语句加锁的类型：
- 默认情况下，InnoDB在repeatable read事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读
  1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
  2. InnoDB的行锁是针对于索引的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。
- 可以通过以下SQL，查看意向锁及行锁的加锁情况：
  1
  select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
间隔锁/临键锁：
- 默认情况下，InnoDB在repeatable read事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读
  1. 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隔锁
  2. 索引的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隔锁
  3. 索引上范围查询（唯一索引），会访问到不满足条件的第一只为止
- 注意：间隔锁唯一的目的是防止其他事务插入间隔。间隔锁可以共存，一个事务采用的间隔锁不会阻止另一个事务在同已间隔上采用间隔锁。

16.MySQL事务：

1.事务的基本介绍:

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败

如果开启事务，而未提交事务；看到的数据是临时的并没有生效

事务提交的两种形式：	说明
自动提交	MySQL就是自动提交的，一条DML(增删改)语句会自动提交一次事务
手动提交	Oracle数据库默认是手动提交事务，需要先开启事务，在提交

修改事务默认提交方式：
- 查找事务的默认提交方式：1：代表自动提交 0：代表手动提交
  1
  select @@autocommit;
- 修改默认提交方式：
  1
  set @@autocommit=1;
操作：
1. 开启事务：
  1
  [start transaction/begin];
2. 回滚：—发现出问题了，回滚事务
  1
  rollback;
3. 提交: —发现执行没有问题，提交事务
  1
  commit;
  ——开启事务—-（多个操作）—-提交事务—-回滚事务

2.事务的四大特征:

四大特征	说明	保证方式
原子性	Atomicity，是不可分割的最小操作单位，要么同时成功，要么同时失败	undo log日志
持久性	Consistency，当事务提交或回滚后，数据库会持久化的保存数据	redo log日志
隔离性	Isolution，多个事务之间，相互独立	锁机制和MVCC
一致性	Durability，事务操作前后，数据库的完整性约束没有被破坏，都是合法的数据状态。	前三种

3.InnoDB事务的ACID如何保证：

InnoDB存储引擎还提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)
redo log(重做日志)：
- 作用：记录的是对数据的操作，用于保证事务持久性
- 为什么出现：
  - InnoDB提供了缓存（Buffer Pool）来提高数据读写效率；如：需要读取数据时，会先从缓存读取，再从磁盘中读取；需要写入数据时，先写入缓存，再定期刷入磁盘
  - 但如果MySQL宕机，但缓存修改的数据没有刷入磁盘就会导致数据的丢失，事务的持久性就无法保证；所以出现了redo log日志
- 如何解决持久性问题：
  - 当数据修改时，除了修改缓存中的数据，（先）还会在redolog记录这次操作
  - 当事务成功提交后时，会对redo log进行刷新
  - 如果MySQL宕机，重启后可以读取redo log的数据进行恢复
undo log(回滚日志)：
- 作用：记录的是sql执行相关的信息，是事务原子性和隔离性实现的基础
- 如何解决原子性问题：
  - 当事务对数据库进行修改时，InnoDB会生成对应的undo log；
  - 当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作；如对于每个insert，回滚时会执行delete；对于每个update，会记录修改的行，修改前后的值，回滚时会update回去。
- 如何解决隔离性问题：
  - 锁机制保证隔离性：(一个事务)写操作对(另一个事务)写操作的影响
  - MVCC保证隔离性：(一个事务)写操作对(另一个事务)读操作的影响
一致性（Durability）：
- 一致性是事务追求的最终目标
- 通过保证事务的原子性，持久性，隔离性来保证事务的一致性。

4.InnoDB引擎的MVCC：

概述：
- 全称Multi-version Concurrency Controller（多版本并发控制），指维护一个数据的多个版本，使得读写操作没有冲突
- 是一种用来解决读-写冲突的无锁并发控制机制
- 在并发读写数据库时,可以做到在读操作时不用阻塞写操作,写操作也不用阻塞读操作
作用：
- 提高数据库并发性能
- 解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题

5.并发情况下，读操作可能存在的三类问题：

脏读：
- 概述：事务 A 读取事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。（侧重于新增或删除）
- 解决方法：加表锁
不可重复读（虚读）：
- 概述：在同一事务中，事务 A 多次读取数据，事务B在事务A读取过程中对数据更新且提交，导致事务A前后读取到的数据不一样（侧重于修改）
- 解决方法：加行锁
幻读：
- 概述：事务A操作（DML）数据表中的所有记录，事务B添加了一条数据，则事务A查询不到自己的修改
- 幻读产生的原因：行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录
- 解决方法：要通过next-key lock。next-key lock是行锁的一种，实现相当于record lock(记录锁)+ gap lock(间隙锁)；

6.事务的隔离级别:

概念：
- 多个事务之间隔离的，相互独立的，但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题
- 隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差

隔离级别:

隔离级别	说明
read uncommitted	读未提交，产生问题：脏读，不可重复读，幻读
read committed	读已提交，产生问题：不可重复读，幻读
repeatable read	可重复读，产生的问题:幻读（默认隔离级别）
serializable	串行化，可以解决所有的问题

注意:隔离级别从小到大安全性越来越大，但是效率越来越低

数据库查询隔离级别
1
select @@tx_isolation;

数据库设置隔离级别:

1 2	-- set global transaction isolation level 级别字符串 set global transaction isolation level serializable

12.数据库设计的范式：

1.概述：

概念：数据库范式是数据库的设计规范，遵守不同的规范要求，可以设计出合理的关系型数据库；范式越高，数据冗余越小
作用：使用数据结构更加合理，使用冗余（重复数据）尽量小，便于增删改

2.依赖：

函数依赖：通过唯一的X一定能查找到Y，即为”Y依赖于X”（X —> Y）