MySQL数据库

一、SQL 结构化查询语言

1.1、SQL概述

SQL（Structured Query Language）结构化查询语⾔，用于存取、查询、更新数据以及管理关系型数据库系统

SQL是在1981年由IBM公司推出，⼀经推出基于其简洁的语法在数据库中得到了广泛的应用，成为主流数据库的通用规范。

• SQL由ANSI组织确定规范
• 在不同的数据库产品中遵守SQL的通用规范，但是也对SQL有⼀些不同的改进，形成了⼀些数据库的专有指令
  • MySQL：limit
  • SQLServer ： top
  • Oracle：rownum

根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类

• DDL(Data Definition Language)数据定义语⾔
  用于完成对数据库对象（数据库、数据表、视图、索引等）的创建、删除、修改
• DML(Data Manipulation Language)数据操作/操纵语⾔
  用于完成对数据表中的数据的添加、删除、修改操作
  添加：将数据存储到数据表
  删除：将数据从数据表移除
  修改：对数据表中的数据进⾏修改
• DQL(Data Query Language)数据查询语⾔
  用于将数据表中的数据查询出来
• DCL(Data Control Language)数据控制语⾔
  用于完成事务管理等控制性操作

1.2、SQL基本语法

在MySQL Command Line Client 或者Navicat等工具中都可以编写SQL指令

• SQL指令不区分大小写
• 每条SQL表达式结束之后都以;结束
• SQL关键字之间以空格进行分隔
• SQL之间可以不限制换行（可以有空格的地⽅就可以有换行）

二、DDL 数据定义语言

2.1、DDL-数据库操作

使⽤DDL语句可以创建数据库、查询数据库、修改数据库、删除数据库

## 查询数据库
# 显示当前mysql中的数据库列表
show databases;

# 显示指定名称的数据的创建的SQL指令
show create database <dbName>;


## 创建数据库
# 创建数据库 dbName表示创建的数据库名称，可以⾃定义
create database <dbName>;

# 创建数据库，当指定名称的数据库不存在时执行创建
create database if not exists <dbName>;

# 在创建数据库的同时指定数据库的字符集（字符集：数据存储在数据库中采⽤的编码格式utf8 gbk）
create database <dbName> character set utf8;


## 修改数据库 修改数据库字符集
alter database <dbName> character set utf8; # utf8 gbk


## 删除数据库 删除数据库时会删除当前数据库中所有的数据表以及数据表中的数据
# 删除数据库
drop database <dbName>;

# 如果数据库存在则删除数据库
drop database if exists <dbName>;


## 使⽤/切换数据库
use <dbName>

2.2、DDL-数据表操作

数据表实际就是⼀个⼆维的表格，⼀个表格是由多列组成，表格中的每⼀类称之为表格的⼀个字段。

## 创建数据表
create table user(
	 id int not null unique,
	 name varchar(20) not null,
	 gender char(2) not null,
	 age int not null,
	 tel char(11) not null unique,
	 address varchar(30) unique
);

## 查询数据表
show tables; 

## 查询表结构
desc <tableName>; 

## 删除数据表
drop table <tableName>;

## 当数据表存在时删除数据表
drop table if exists <tableName>;


## 修改数据表
# 修改表名
alter table <tableName> rename to <newTableName>;

# 数据表也是有字符集的，默认字符集和数据库⼀致
alter table <tableName> character set utf8;

# 添加列（字段）
alter table <tableName> add <columnName> varchar(200);

# 修改列（字段）的列表和类型
alter table <tableName> change <oldColumnName> <newCloumnName> <type>;

# 只修改列（字段）类型
alter table <tableName> modify <columnName> <newType>;

# 删除列（字段）
alter table <tableName> drop <columnName>;

三、MySQL数据类型

数据类型，指的是数据表中的列中⽀持存放的数据的类型

3.1、数值类型

在mysql中有多种数据类型可以存放数值，不同的类型存放的数值的范围或者形式是不同的

类型	内存空间大小	范围	说明
tinyint	1byte	有符号 -128~127 ⽆符号 0~255	特小型整数（年龄）
smallint	2byte	有符号 -32768 ~ 32767 ⽆符号 0~65535	小型整数
mediumint	3byte	有符号 -2^31 ~ 2^31 - 1 ⽆符号 0~2^32-1	中型整数
int/integer	4byte		整数
bigint	8byte		大型整数
float	4byte		单精度
double	8byte		双精度
decimal	第⼀参数+2		decimal(10,2) 表示数值⼀共有10位 ⼩数位有2位

3.2、字符串类型

存储字符序列的类型

类型	字符长度	说明
char	0~255字节	定⻓字符串，最多可以存储255个字符 ;当我们指定数据表字段为char(n) 此列中的数据最⻓为n个字符，如果添加的数据少于n，则补’\u0000’⾄n⻓度
varchar	0~65536字节	可变⻓度字符串，此类型的类最⼤⻓度为65535
tinyblob	0~255 字节	存储⼆进制字符串
blob	0~65535	存储⼆进制字符串
mediumblob	0~1677215字节	存储⼆进制字符串
longblob	0~4294967295字节	存储⼆进制字符串
tinytext	0~255字节	⽂本数据（字符串）
text	0~65535字节	⽂本数据（字符串）
mediumtext	0~1677215字节	⽂本数据（字符串）
longtext	0~4294967295字节	⽂本数据（字符串）

3.3、日期类型

在MySQL数据库中，我们可以使用字符串来存储时间，但是如果我们需要基于时间字段进行查询操作（查询在某个时间段内的数据）就不便于查询实现

类型	格式	说明
date	2021-09-13	日期，只存储年月日
time	11:12:13	时间，只存储时分秒
year	2021	年份
datetime	2021-09-13 11:12:13	日期+时间，存储年⽉日时分秒
timestamp	20210913 111213	日期+时间 （时间戳）

四、字段约束

4.1、约束介绍

在创建数据表的时候，指定的对数据表的列的数据限制性的要求（约束实际上就是表中数据的限制条件）

为什么要给表中的列添加约束

• 保证数据的有效性
• 保证数据的完整性
• 保证数据的正确性

分类

• 主键约束(primary key) PK
• 自增长约束(auto_increment)
• 非空约束(not null)
• 唯一性约束(unique)
• 默认约束(default)
• 零填充约束(zerofill)
• 外键约束(foreign key) FK

4.2、主键约束

• MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。
• 主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
• 每个表最多只允许一个主键
• 主键约束的关键字是：primary key
• 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

## 方式一：创建表时添加主键约束
create table books(
    id int primary key,
    name varchar(10) not null,
    author varchar(20)
);

## 方式二：创建表时添加主键约束
create table books(
    id int ,
    name varchar(10) not null,
    author varchar(20),
		primary key(id)
);

## 删除数据表主键约束
alter table books drop primary key; 

## 创建表时没有添加主键约束
create table books(
    id int ,
    name varchar(10) not null,
    author varchar(20)
);

## 创建表之后添加主键约束
alter table books modify id int primary key;

## 联合主键 联合主键——将数据表中的多列组合在⼀起设置为表的主键
create table grades(
    user_id int,
    course_id int,
    score int,
    primary key(user_id, course_id)
);

注意：在实际企业项⽬的数据库设计中，联合主键使⽤频率并不⾼；当⼀个张数据表中没有明确的字段可以作为主键时，我们可以额外添加⼀个ID字段作为主键。

4.3、主键自动增长

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加 auto_increment 属性来实现主键自增长

特点

• 默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。
• 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
• auto_increment约束的字段必须具备 NOT NULL 属性。
• auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等。
• auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，- auto_increment就会失效。

## 定义主键⾃动增⻓
## 定义int类型字段⾃动增⻓： auto_increment
create table types(
    type_id int primary key auto_increment,
    type_name varchar(20) not null,
    type_remark varchar(100)
);

注意：⾃动增⻓从1开始，每添加⼀条记录，⾃动的增⻓的列会⾃定+1，当我们把某条记录删除之后再添加数据，⾃动增⻓的数据也不会重复⽣成（⾃动增⻓只保证唯⼀性、不保证连续性）

指定自增字段初始值

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加

-- 方式1，创建表时指定
create table t_user2 ( 
  id int primary key auto_increment, 
  name varchar(20)
)auto_increment=100;


-- 方式2，创建表之后指定
create table t_user3 ( 
  id int primary key auto_increment, 
  name varchar(20)
);
alter table t_user2 auto_increment=100;

delete和truncate在删除后自增列的变化

• delete数据之后自动增长从断点开始
• truncate数据之后自动增长从默认起始值开始

4.4、非空约束

MySQL 非空约束（not null）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

创建表：设置图书表的 name not null

create table books(
    id int,
    name varchar(10) not null,
    author varchar(20)
);

添加数据：id和author 不给值，可以默认为null，如果name不给值则提示添加错误

如果添加中文报错，在创建数据库时选择编码为utf8或utf8mb4

4.5、唯⼀约束

唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。

创建表：设置图书表的name为 unique

create table books(
    id int unique,
    name varchar(10) not null,
    author varchar(20)
);

4.6、默认约束(default)

MySQL 默认值约束用来指定某列的默认值。
语法

• <字段名> <数据类型> default <默认值>;
• alter table 表名 modify 列名 类型 default 默认值;

-- 方式1
create table t_user10 ( 
  id int , 
  name varchar(20) , 
  address varchar(20) default ‘北京’ -- 指定默认约束 
);

-- 方式2
-- alter table 表名 modify 列名 类型 default 默认值; 
create table t_user11 ( 
  id int , 
  name varchar(20) , 
  address varchar(20)  
);
alter table t_user11 modify address varchar(20) default '北京';

--删除默认约束
-- alter table <表名> modify column <字段名> <类型> default null; 

alter table t_user11 modify column address varchar(20) default null;

4.7、零填充约束(zerofill)

• 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
• zerofill默认为int(10)
• 当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128 ~ +127，无符号为0~256。

-- 操作
create table t_user12 ( 
  id int zerofill , -- 零填充约束
  name varchar(20)   
);

-- 删除
alter table t_user12 modify id int;

五、DML 数据操纵语言

用于完成对数据表中数据的插入、删除、修改操作

## 插入数据
insert into <tableName>(columnName,columnName....)
values(value1,value2....);

## 删除数据--从数据表中删除满⾜特定条件（所有）的记录
delete from <tableName> [where conditions]; 

## 删除数据--一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。
##			并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
truncate table user;

### 修改数据--对数据表中已经添加的记录进⾏修改
update <tableName> set columnName=value [where conditions] 

六、DQL 数据查询语言

从数据表中提取满足特定条件的记录

• 单表查询
• 多表联合查询

6.1、查询基础语法

## 语法格式
select 
  [all|distinct]
  <目标列的表达式1> [别名],
  <目标列的表达式2> [别名]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名> 
[having <条件表达式>]]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

# 简化版语法
select *|列名 from 表 where 条件

## select 关键字后指定要显示查询到的记录的哪些列
select colnumName1[,colnumName2,colnumName3...] from <tableName> [where conditions];

## 如果要显示查询到的记录的所有列，则可以使⽤ * 替代字段名列表 （在项⽬开发中不建议使⽤*）
select * from user;

6.2、where子句

在删除、修改及查询的语句后都可以添加where子句（条件），用于筛选满⾜特定的添加的数据进⾏删除、修改和查询操作。

数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。

MySQL支持4种运算符

• 算术运算符
• 比较运算符
• 逻辑运算符
• 位运算符

算数运算符

算术运算符	说明
+	加法运算
-	减法运算
*	乘法运算
/ 或DIV	除法运算，返回商
%或MOD	求余运算，返回余数

比较运算符

比较运算符	说明
=	等于
<和<=	小于和小于等于
>和>=	大于和大于等于
<=>	安全的等于，两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
<>或!=	不等于
IS NULL或ISNUL	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

逻辑运算符

逻辑运算符	说明
NOT或者!	逻辑非
AND 或者 &&	逻辑与
OR或者||	逻辑或
XOR	逻辑异或

位运算符

位运算符	说明
|	按位或
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反，反转所有比特

6.3、LIKE子句

在where子句的条件中，我们可以使⽤like关键字来实现模糊查询

在like关键字后的reg表达式中

• % 表示任意多个字符 【 %o% 包含字⺟o】
• _ 表示任意⼀个字符 【 _o% 第⼆个字⺟为o】

6.4、对查询结果的处理

• select colnumName1, columnName2,...
  声明显示查询结果的指定列
• select distinct colnumName1, columnName2...
  消除重复行
• select colnumName1 + columnName2...
  对从数据表中查询的记录的列进行⼀定的运算之后显示出来
• select colnumName as newColnumName...
  字段取别名

6.5、排序 - order by

将查询到的满足条件的记录按照指定的列的值升序/降序排列

特点

• asc代表升序，desc代表降序，如果不写默认升序
• order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名
• order by子句，放在查询语句的最后面。LIMIT子句除外

多字段排序: 先满⾜第⼀个排序规则，当第⼀个排序的列的值相同时再按照第⼆个列的规则排序

6.6、聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

聚合函数	作用
count()	统计指定列不为NULL的记录行数；
sum()	计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
max()	计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min()	计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg()	计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

聚合查询NULL值的处理

1. count函数对null值的处理
   如果count函数的参数为星号（*），则统计所有记录的个数。而如果参数为某字段，不统计含null值的记录个数。
2. sum和avg函数对null值的处理
   这两个函数忽略null值的存在，就好象该条记录不存在一样。
3. max和min函数对null值的处理
   max和min两个函数同样忽略null值的存在。

操作

-- 创建表
create table test_null( 
 c1 varchar(20), 
 c2 int 
);

-- 插入数据
insert into test_null values('aaa',3);
insert into test_null values('bbb',3);
insert into test_null values('ccc',null);
insert into test_null values('ddd',6);
 
-- 测试
select count(*), count(1), count(c2) from test_null;
select sum(c2),max(c2),min(c2),avg(c2) from test_null;

6.7、日期函数和字符串函数

日期函数

当我们向日期类型的列添加数据时，可以通过字符串类型赋值（字符串的格式必须为yyyy-MM-dd hh:mm:ss），如果我们想要获取当前系统时间添加到日期类型的列，可以使⽤now()或者sysdate()。

• 通过字符串类型 给日期类型的列赋值
• 通过now()获取当前时间
• 通过sysdate()获取当前时间

字符串函数

• concat(colnum1, colunm2,…)
  拼接多列
• upper(column)
  将字段的值转换成大写
• lower(column)
  将指定列的值转换成小写
• substring(column, start, len)
  从指定列中截取部分显示 start从1开始

6.8、分组查询 - group by

分组——就是将数据表中的记录按照指定的类进行分组

语法

select 分组字段/聚合函数
from 表名
[where 条件]
group by 分组字段 [having 分组条件]
[order by 排序字段]

select 后使⽤ * 显示对查询的结果进行分组之后，显示每组的第⼀条记录（这种显示通常是⽆意义的）
select 后通常显示分组字段和聚合函数(对分组后的数据进⾏统计、求和、平均值等)

语句执行顺序：

1. 先根据where条件从数据库查询记录
2. group by对查询记录进行分组
3. 执行having对分组后的数据进行筛选

6.9、分页查询 - limit

当数据表中的记录比较多的时候，如果⼀次性全部查询出来显示给⽤户，用户的可读性体验性就不太好，因此我们可以将这些数据分页进行展示。

## 语法
select ...
from ...
where ...
limit param1,param2

• param1 int , 表示获取查询语句的结果中的第⼀条数据的索引（索引从0开始）
• param2 int, 表示获取的查询记录的条数（如果剩下的数据条数<param2，则返回剩下的所有记录）

6.10、INSERT INTO SELECT语句

将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句 。
格式

insert into Table2(field1,field2,…) select value1,value2,… from Table1 或者：
insert into Table2 select * from Table1

要求目标表Table2必须存在

6.11、SELECT INTO FROM语句

将一张表的数据导入到另一张表中，有两种选择 SELECT INTO 和 INSERT INTO SELECT。

-- 格式
SELECT vale1, value2 into Table2 from Table1

要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。

6.12、DQL-正则表达式

正则表达式(regular expression)描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、用来定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。

MySQL通过REGEXP关键字支持正则表达式进行字符串匹配

模式	描述
^	匹配输入字符串的开始位置。
$	匹配输入字符串的结束位置。
.	匹配除 “\n” 之外的任何单个字符。
[…]	字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…]	负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1|p2|p3	匹配 p1 或 p2 或 p3。例如，‘z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，‘zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

操作

SELECT  'abc' REGEXP '.c';
SELECT  'abc' REGEXP 'a.';
 
-- [...] 匹配括号内的任意单个字符
SELECT  'abc' REGEXP '[xyz]';
SELECT  'abc' REGEXP '[xaz]';

-- [^...] 注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
SELECT  'a' REGEXP '[^abc]';
SELECT  'x' REGEXP '[^abc]';
SELECT  'abc' REGEXP '[^a]';
 
-- a* 匹配0个或多个a,包括空字符串。 可以作为占位符使用.有没有指定字符都可以匹配到数据
 
SELECT 'stab' REGEXP '.ta*b';
SELECT 'stb' REGEXP '.ta*b';
SELECT '' REGEXP 'a*';
 
-- a+  匹配1个或者多个a,但是不包括空字符
SELECT 'stab' REGEXP '.ta+b';
SELECT 'stb' REGEXP '.ta+b';

-- a?  匹配0个或者1个a
SELECT 'stb' REGEXP '.ta?b';
SELECT 'stab' REGEXP '.ta?b';
SELECT 'staab' REGEXP '.ta?b';
 
-- a1|a2  匹配a1或者a2，
SELECT 'a' REGEXP 'a|b';
SELECT 'b' REGEXP 'a|b';
SELECT 'b' REGEXP '^(a|b)';
SELECT 'a' REGEXP '^(a|b)';
SELECT 'c' REGEXP '^(a|b)';
 
-- a{m} 匹配m个a
 
SELECT 'auuuuc' REGEXP 'au{4}c';
SELECT 'auuuuc' REGEXP 'au{3}c';

 -- a{m,n} 匹配m到n个a,包含m和n
 
SELECT 'auuuuc' REGEXP 'au{3,5}c';
SELECT 'auuuuc' REGEXP 'au{4,5}c';
SELECT 'auuuuc' REGEXP 'au{5,10}c';
 
-- (abc) abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，
-- 如果要把多个字符作为一个整体去匹配就需要用到括号，所以括号适合上面的所有情况。
SELECT 'xababy' REGEXP 'x(abab)y';
SELECT 'xababy' REGEXP 'x(ab)*y';
SELECT 'xababy' REGEXP 'x(ab){1,2}y';

七、数据表的关联关系

7.1、关联关系介绍

MySQL是⼀个关系型数据库，不仅可以存储数据，还可以维护数据与数据之间的关系 ——通过在数据表中添加字段建⽴外键约束

数据与数据之间的 关联关系 分为四种：

• ⼀对⼀关联
• ⼀对多关联
• 多对⼀关联
• 多对多关联

7.2、⼀对⼀关联

• 人 — 身份证 ⼀个人只有⼀个身份证、⼀个身份证只对应⼀个人
• 学⽣ — 学籍 ⼀个学生只有⼀个学籍、⼀个学籍也对应唯⼀的⼀个学生
• 用户 — 用户详情 ⼀个用户只有⼀个详情、⼀个详情也只对应⼀个用户

⽅法1：
主键关联——两张数据表中主键相同的数据为相互对应的数据

⽅法2：
唯⼀外键 —— 在任意⼀张表中添加⼀个字段添加外键约束与另⼀张表主键关联，并 且将外键列添加唯⼀约束

7.3、一对多与多对一

• 班级 — 学生 （一对多） 一个班级包含多个学生
• 学生 — 班级 （多对一） 多个学生可以属于同一个班级
• 图书 — 分类
• 商品 ---- 商品类别

⽅法： 在多的⼀端添加外键 ，与少的一端主键进行关联

7.4、多对多关联

• 学⽣ — 课程 ⼀个学生可以选择多门课、⼀门课程也可以由多个学生选择
• 会员 — 社团 ⼀个会员可以参加多个社团、⼀个社团也可以招纳多个会员

⽅法： 额外创建⼀张关系表来维护多对多关联——在关系表中定义两个外键，分别与两个数据表的主键进行关联

7.5、外键约束

定义一个外键时，需要遵守下列规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这 个外键的内容就是正确的。
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键中列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

案例：学⽣表 与 班级表

1.先创建班级表

CREATE TABLE classes ( 
	class_id INT PRIMARY KEY auto_increment, 
	class_name VARCHAR ( 40 ) NOT NULL UNIQUE, 
	class_remark VARCHAR ( 200 ) 
);

2.创建学⽣表（在学⽣表中添加外键与班级表的主键进行关联）

## 【⽅式⼀】在创建表的时候，定义cid字段，并添加外键约束
#  由于cid 列 要与classes表的class_id进⾏关联，因此cid字段类型和⻓度要与class_id⼀致
CREATE TABLE students (
	stu_num CHAR ( 8 ) PRIMARY KEY,
	stu_name VARCHAR ( 20 ) NOT NULL,
	stu_gender CHAR ( 2 ) NOT NULL,
	stu_age INT NOT NULL,
	cid INT,
	CONSTRAINT FK_STUDENTS_CLASSES FOREIGN KEY ( cid )REFERENCES classes ( class_id )
);

## 【⽅式⼆】先创建表，再添加外键约束
CREATE TABLE students (
	stu_num CHAR ( 8 ) PRIMARY KEY,
	stu_name VARCHAR ( 20 ) NOT NULL,
	stu_gender CHAR ( 2 ) NOT NULL,
	stu_age INT NOT NULL,
	cid INT 
);
# 在创建表之后，为cid添加外键约束
ALTER TABLE students ADD CONSTRAINT FK_STUDENTS_CLASSES FOREIGN KEY ( cid ) REFERENCES classes ( class_id );
# 删除外键约束
ALTER TABLE students DROP FOREIGN KEY FK_STUDENTS_CLASSES;

7.6、外键约束-级联

多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候,需要显示的数据来自多张表。

7.6.1、多表查询有以下分类

• 交叉连接查询 [产生笛卡尔积，了解]
  语法：select * from A,B;
• 内连接查询(使用的关键字 inner join – inner可以省略)
  隐式内连接（SQL92标准）：select * from A,B where 条件;
  显示内连接（SQL99标准）：select * from A inner join B on 条件;
• 外连接查询(使用的关键字 outer join – outer可以省略)
  • 左外连接：left outer join
    select * from A left outer join B on 条件;
  • 右外连接：right outer join
    select * from A right outer join B on 条件;
  • 满外连接: full outer join
    select * from A full outer join B on 条件;
• 子查询
  select的嵌套
• 表自关联：
  将一张表当成多张表来用

准备多表查询需要数据，注意，外键约束对多表查询并无影响。

use mydb3;

-- 创建部门表
create table if not exists dept3(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);
 
-- 创建员工表
create table if not exists emp3(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
);

准备查询数据

-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');

-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');

7.6.2、交叉连接查询

• 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
• 笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
• 假如A表有m行数据，B表有n行数据，则返回m*n行数据
• 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

-- 格式
select * from 表1,表2,表3….; 

-- 交叉连接查询
select * from dept3,emp3;

7.6.3、内连接查询

内连接查询求多张表的交集

格式

• 隐式内连接（SQL92标准）：select * from A,B where 条件;
• 显示内连接（SQL99标准）：select * from A inner join B on 条件;

-- 查询每个部门的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
-- 查询研发部和销售部的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
 
-- 查询每个部门的员工数,并升序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
 
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
-- 查询人数大于等于3的部门，并按照人数降序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
 
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;

7.6.4、外连接查询

外连接分为左外连接（left outer join）、右外连接(right outer join)，满外连接(full outer join)。

注意：oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用union来达到目的。

格式

• 左外连接：left outer join
  select * from A left outer join B on 条件;
• 右外连接：right outer join
  select * from A right outer join B on 条件;
• 满外连接: full outer join
  select * from A full outer join B on 条件;

-- 外连接查询
-- 查询哪些部门有员工，哪些部门没有员工
use mydb3;
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
 
-- 查询哪些员工有对应的部门，哪些没有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
 
-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union 
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id

7.6.5、子查询

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。

子查询可以返回的数据类型一共分为四种

• 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；
• 单行多列：返回一行数据中多个列的内容；
• 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；
• 多行多列：查询返回的结果是一张临时表

-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄
select eid,ename,age from emp3 where age = (select max(age) from emp3);
  
-- 查询年研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno,name from dept3 where name = '研发部' or name = '销售部') ;
 
-- 查询研发部20岁以下的员工信息,包括员工号、员工名字，部门名字
select eid,age,ename,name from (select * from dept where name = '研发部 ')t1,(select * from emp3 where age <20)t2

在子查询中，有一些常用的逻辑关键字，这些关键字可以给我们提供更丰富的查询功能，

主要关键字如下:

1. ALL关键字
2. ANY关键字
3. SOME关键字
4. IN关键字
5. EXISTS关键字

子查询关键字-ALL

-- 格式
select …from …where c > all(查询语句)

-- 等价于：
select ...from ... where c > result1 and c > result2 and c > result3

特点

• ALL: 与子查询返回的所有值比较为true 则返回true
• ALL可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
• ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);

-- 查询不属于任何一个部门的员工信息 
select * from emp3 where dept_id != all(select deptno from dept3); 

子查询关键字-ANY和SOME

- 格式
select …from …where c > any(查询语句)

-- 等价于：
select ...from ... where c > result1 or c > result2 or c > result3

特点

• ANY:与子查询返回的任何值比较为true 则返回true
• ANY可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一个数据。
• 表示制定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。
• SOME和ANY的作用一样，SOME可以理解为ANY的别名

-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);

子查询关键字-IN

-- 格式
select …from …where c in(查询语句)

-- 等价于：
select ...from ... where c = result1 or c = result2 or c = result3

特点

• IN关键字，用于判断某个记录的值，是否在指定的集合中
• 在IN关键字前边加上not可以将条件反过来

-- 查询研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename,t.name
from emp3 
where dept_id in
	 (select deptno from dept3 where name = '研发部' or name = '销售部') ;

子查询关键字-EXISTS

-- 格式
select …from …where exists(查询语句)

特点

• 该子查询如果“有数据结果”(至少返回一行数据)， 则该EXISTS() 的结果为“true”，外层查询执行
• 该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS()的结果为“false”，外层查询不执行
• EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where条件成立
• 注意，EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字

-- 查询公司是否有大于60岁的员工，有则输出
select * from emp3 a where exists(select * from emp3 b where a.age > 60);
 
-- 查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);

7.6.6、自关联查询

MySQL有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。注意自关联时表必须给表起别名。

-- 格式
select 字段列表 from 表1 a , 表1 b where 条件;
--或者 
select 字段列表 from 表1 a [left] join 表1 b on 条件;

操作

-- 创建表,并建立自关联约束
create table t_sanguo(
    eid int primary key ,
    ename varchar(20),
    manager_id int,
 foreign key (manager_id) references t_sanguo (eid)  -- 添加自关联约束
);

-- 添加数据 
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);
 
-- 进行关联查询
-- 查询每个三国人物及他的上级信息，如:  关羽  刘备 
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;

八、MySQL的函数

在MySQL中，为了提高代码重用性和隐藏实现细节，MySQL提供了很多函数。函数可以理解为别人封装好的模板代码。

在MySQL中，函数非常多，主要可以分为以下几类:

• 聚合函数
• 数学函数
• 字符串函数
• 日期函数
• 控制流函数
• 窗口函数

8.1、聚合函数

在MySQL中，聚合函数主要由：count,sum,min,max,avg,这些聚合函数我们之前都学过，不再重复。这里我们学习另外一个函数:group_concat(），该函数用户实现行的合并

group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。

-- 格式
group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])

说明：

• 使用distinct可以排除重复值；
• 如果需要对结果中的值进行排序，可以使用order by子句；
• separator是一个字符串值，默认为逗号。

操作

create database mydb4;
use mydb4;
 
create table emp(
    emp_id int primary key auto_increment comment '编号',
    emp_name char(20) not null default '' comment '姓名',
    salary decimal(10,2) not null default 0 comment '工资',
    department char(20) not null default '' comment '部门'
);
 
insert into emp(emp_name,salary,department) 
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),
('刘云鹏',7800,'销售部');

-- 将所有员工的名字合并成一行 
select group_concat(emp_name) from emp;

-- 指定分隔符合并 
select department,group_concat(emp_name separator ';' ) from emp group by department; 

-- 指定排序方式和分隔符 
select department,group_concat(emp_name order by salary desc separator ';' ) from emp group by department;

8.2、数学函数

函数名	描述	实例
ABS(x)	返回 x 的绝对值	返回 -1 的绝对值： SELECT ABS(-1) – 返回1
CEIL(x)	返回大于或等于 x 的最小整数	SELECT CEIL(1.5) – 返回2
FLOOR(x)	返回小于或等于 x 的最大整数	小于或等于 1.5 的整数： SELECT FLOOR(1.5) – 返回1
GREATEST(expr1, expr2, expr3, …)	返回列表中的最大值	返回以下数字列表中的最大值： SELECT GREATEST(3, 12, 34, 8, 25); – 34 返回以下字符串列表中的最大值： SELECT GREATEST(“Google”, “Runoob”, “Apple”); – Runoob
LEAST(expr1, expr2, expr3, …)	返回列表中的最小值	返回以下数字列表中的最小值： SELECT LEAST(3, 12, 34, 8, 25); – 3 返回以下字符串列表中的最小值： SELECT LEAST(“Google”, “Runoob”, “Apple”); – Apple
MAX(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值： SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)	返回字段 expression 中的最小值	返回数据表 Products 中字段 Price 的最小值： SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y)	返回 x 除以 y 以后的余数	5 除于 2 的余数： SELECT MOD(5,2) – 1
PI()	返回圆周率(3.141593）	SELECT PI() --3.141593
POW(x,y)	返回 x 的 y 次方	2 的 3 次方： SELECT POW(2,3) – 8
RAND()	返回 0 到 1 的随机数	SELECT RAND() --0.93099315644334
ROUND(x)	返回离 x 最近的整数（遵循四舍五入）	SELECT ROUND(1.23456) --1
ROUND(x,y)	返回指定位数的小数（遵循四舍五入）	SELECT ROUND(1.23456,3) –1.235
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) – 1.234

8.3、字符串函数

函数	描述	实例
CHAR_LENGTH(s)	返回字符串 s 的字符数	返回字符串 RUNOOB 的字符数 SELECT CHAR_LENGTH(“RUNOOB”) AS LengthOfString;
CHARACTER_LENGTH(s)	返回字符串 s 的字符数	返回字符串 RUNOOB 的字符数 SELECT CHARACTER_LENGTH(“RUNOOB”) AS LengthOfString;
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串 SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", “Facebook”) AS ConcatenatedString;
CONCAT_WS(x, s1,s2…sn)	同 CONCAT(s1,s2,…) 函数，但是每个字符串之间要加上 x，x 可以是分隔符	合并多个字符串，并添加分隔符： SELECT CONCAT_WS("-", “SQL”, “Tutorial”, “is”, “fun!”)AS ConcatenatedString;
FIELD(s,s1,s2…)	返回第一个字符串 s 在字符串列表(s1,s2…)中的位置	返回字符串 c 在列表值中的位置： SELECT FIELD(“c”, “a”, “b”, “c”, “d”, “e”);
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 RUNOOB开始处的空格： SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB
MID(s,n,len)	从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)	从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT MID(“RUNOOB”, 2, 3) AS ExtractString; – UNO
POSITION(s1 IN s)	从字符串 s 中获取 s1 的开始位置	返回字符串 abc 中 b 的位置： SELECT POSITION(‘b’ in ‘abc’) – 2
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 abc 中的字符 a 替换为字符 x： SELECT REPLACE(‘abc’,‘a’,‘x’) --xbc
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来： SELECT REVERSE(‘abc’) – cba
RIGHT(s,n)	返回字符串 s 的后 n 个字符	返回字符串 runoob 的后两个字符： SELECT RIGHT(‘runoob’,2) – ob
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 RUNOOB 的末尾空格： SELECT RTRIM("RUNOOB ") AS RightTrimmedString; – RUNOOB
STRCMP(s1,s2)	比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1	比较字符串： SELECT STRCMP(“runoob”, “runoob”); – 0
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTR(“RUNOOB”, 2, 3) AS ExtractString; – UNO
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTRING(“RUNOOB”, 2, 3) AS ExtractString; – UNO
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 RUNOOB 的首尾空格： SELECT TRIM(’ RUNOOB ') AS TrimmedString;
UCASE(s)	将字符串转换为大写	将字符串 runoob 转换为大写： SELECT UCASE(“runoob”); – RUNOOB
UPPER(s)	将字符串转换为大写	将字符串 runoob 转换为大写： SELECT UPPER(“runoob”); – RUNOOB
LCASE(s)	将字符串 s 的所有字母变成小写字母	字符串 RUNOOB 转换为小写： SELECT LCASE(‘RUNOOB’) – runoob
LOWER(s)	将字符串 s 的所有字母变成小写字母	字符串 RUNOOB 转换为小写： SELECT LOWER(‘RUNOOB’) – runoob

8.4、日期函数

函数名	描述	实例
UNIX_TIMESTAMP()	返回从1970-01-01 00:00:00到当前毫秒值	SELECT UNIX_TIMESTAMP();
UNIX_TIMESTAMP(DATE_STRING)	将指定日期转为毫秒值时间戳	SELECT UNIX_TIMESTAMP(‘2021-12-17 13:43:03’);
FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])	将毫秒值时间戳转为指定格式日期	SELECT FROM_UNIXTIME(1639719882,’%Y-%m-%d %H:%i:%s’);
CURDATE()	返回当前日期	SELECT CURDATE();
CURRENT_DATE()	返回当前日期	SELECT CURRENT_DATE();
CURRENT_TIME	返回当前时间	SELECT CURRENT_TIME();
CURTIME()	返回当前时间	SELECT CURTIME();
CURRENT_TIMESTAMP()	返回当前日期和时间	SELECT CURRENT_TIMESTAMP();
DATE()	从日期或日期时间表达式中提取日期值	SELECT DATE(“2021-12-15”);
DATEDIFF(d1,d2)	计算日期 d1-d2 之间相隔的天数	SELECT DATEDIFF(‘2021-12-17’,‘2021-12-01’);
TIMEDIFF(time1, time2)	计算时间差值	SELECT TIMEDIFF(“13:05:11”, “13:10:10”);
DATE_FORMAT(d,f)	按表达式 f的要求显示日期 d	SELECT DATE_FORMAT(‘2021-12-17 13:50:11’,’%Y-%m-%d %r’);
STR_TO_DATE(string, format_mask)	将字符串转变为日期	SELECT STR_TO_DATE(“August 10 2021”, “%M %d %Y”);
DATE_SUB(date,INTERVAL expr type)	函数从日期减去指定的时间间隔。	SELECT DATE_SUB(‘2021-12-17 13:50:11’, INTERVAL 2 DAY);
ADDDATE/DATE_ADD(d，INTERVAL expr type)	计算起始日期 d 加上一个时间段后的日期，type 值可以是： MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR DAY_MINUTE DAY_HOUR YEAR_MONTH	SELECT DATE_ADD(“2021-12-17”, INTERVAL 10 DAY); SELECT DATE_ADD(“2021-12-17 14:34:21”, INTERVAL 15 MINUTE); SELECT DATE_ADD(“2021-12-17 14:34:21”, INTERVAL -3 HOUR); SELECT DATE_ADD(“2021-12-17 14:34:21”, INTERVAL -3 HOUR);
EXTRACT(type FROM d)	从日期 d 中获取指定的值，type指定返回的值。 type可取值为 MICROSECOND SECOND MINUTE HOUR ……	SELECT EXTRACT(MINUTE FROM ‘2021-12-17 14:34:21’);
LAST_DAY(d)	返回给定日期的那一月份的最后一天	SELECT LAST_DAY(“2021-12-17”);
MAKEDATE(year, day-of-year)	基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期	SELECT MAKEDATE(2021, 125);
YEAR(d)	返回年份	SELECT YEAR(“2021-12-17”);
MONTH(d)	返回日期d中的月份值，1 到 12	SELECT MONTH(‘2021-12-17 14:15:41’);
DAY(d)	返回日期值 d 的日期部分	SELECT DAY(“2021-12-17”);
HOUR(t)	返回 t 中的小时值	SELECT HOUR(‘14:15:41’);
MINUTE(t)	返回 t 中的分钟值	SELECT MINUTE(‘14:15:41’);
SECOND(t)	返回 t 中的秒钟值	SELECT SECOND(‘14:15:41’);
QUARTER(d)	返回日期d是第几季节，返回 1 到 4	SELECT QUARTER(‘2021-12-17 14:15:41’);
MONTHNAME(d)	返回日期当中的月份名称，如 November	SELECT MONTHNAME(‘2021-12-17 14:15:41’);
MONTH(d)	返回日期d中的月份值，1 到 12	SELECT MONTH(‘2021-12-17 14:15:41’);
DAYNAME(d)	返回日期 d 是星期几，如 Monday,Tuesday	SELECT DAYNAME(‘2021-12-17 14:15:41’);
DAYOFMONTH(d)	计算日期 d 是本月的第几天	SELECT DAYOFMONTH(‘2021-12-17 14:15:41’);
DAYOFWEEK(d)	日期 d 今天是星期几，1 星期日，2 星期一，以此类推	SELECT DAYOFWEEK(‘2021-12-17 14:15:41’);
DAYOFYEAR(d)	计算日期 d 是本年的第几天	SELECT DAYOFYEAR(‘2021-12-17 14:15:41’);
WEEK(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEK(‘2021-12-17 14:15:41’);
WEEKDAY(d)	日期 d 是星期几，0 表示星期一，1 表示星期二	SELECT WEEKDAY(‘2021-12-17 14:15:41’);
WEEKOFYEAR(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEKOFYEAR(‘2021-12-17 14:15:41’);
YEARWEEK(date, mode)	返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推	SELECT YEARWEEK(‘2021-12-17 14:15:41’);
NOW()	返回当前日期和时间	SELECT NOW();

8.5、控制流函数

if逻辑判断语句

格式	解释	案例
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。	SELECT IF(1 > 0,‘正确’,‘错误’)
IFNULL(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。	SELECT IFNULL(null,‘Hello Word’)
ISNULL(expression)	判断表达式是否为 NULL	SELECT ISNULL(NULL);
NULLIF(expr1, expr2)	比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1	SELECT NULLIF(25, 25);

case when语句

-- CASE 表示函数开始，END  表示函数结束。
-- 如果  condition1 成立，则返回  result1, 如果  condition2 成立，则返回  result2，
-- 当全部不成立则返回  result，而当有一个成立之后，后面的就不执行了。

-- 格式
CASE expression
	WHEN condition1 THEN  result1
	WHEN condition2 THEN  result2
	...    
	WHEN conditionN THEN  resultN
	ELSE result
END

-- 操作
SELECT
CASE 100 
	WHEN 50 THEN 'tom' 
    WHEN 100 THEN 'mary'
    ELSE 'tim' 
END;
	
SELECT
CASE		
	WHEN 1 = 2 THEN 'tom' 
	WHEN 2 = 2 THEN 'mary' 
ELSE 'tim' 
END;

-- case when语句

use mydb4; 

-- 创建订单表
create table orders(
 oid int primary key, -- 订单id
 price double, -- 订单价格
 payType int -- 支付类型(1:微信支付 2:支付宝支付 3:银行卡支付 4：其他)
);
 
insert into orders values(1,1200,1);
insert into orders values(2,1000,2);
insert into orders values(3,200,3);
insert into orders values(4,3000,1);
insert into orders values(5,1500,2);
 
-- 方式1
select 
*  ,
case 
  when payType=1 then '微信支付' 
    when payType=2 then '支付宝支付' 
    when payType=3 then '银行卡支付' 
    else '其他支付方式' 
end  as payTypeStr
from orders;
-- 方式2
select 
*  ,
case payType
  when 1 then '微信支付' 
    when 2 then '支付宝支付' 
    when 3 then '银行卡支付' 
    else '其他支付方式' 
end  as payTypeStr
from orders;

8.6、窗口函数

• MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点。
• 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。
  
  分类
  另外还有开窗聚合函数: SUM,AVG,MIN,MAX

语法结构

window_function ( expr ) OVER ( 
  PARTITION BY ... 
  ORDER BY ... 
  frame_clause 
)

其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；OVER子句包含三个选项：

• 分区（PARTITION BY）
  PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算
• 排序（ORDER BY）
  OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似
• 以及窗口大小（frame_clause）。
  frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

序号函数
序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号。

-- 格式
row_number()|rank()|dense_rank() over ( 
  partition by ... 
  order by ... 
) 

-- 操作
use mydb4; 
create table employee( 
   dname varchar(20), -- 部门名 
   eid varchar(20), 
   ename varchar(20), 
   hiredate date, -- 入职日期 
   salary double -- 薪资
); 

insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
 
insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

-- 对每个部门的员工按照薪资排序，并给出排名
select dname, ename, salary,
row_number() over(partition by dname order by salary desc) as rn 
from employee;

-- 对每个部门的员工按照薪资排序，并给出排名 rank
select dname, ename, salary,
rank() over(partition by dname order by salary desc) as rn 
from employee;

-- 对每个部门的员工按照薪资排序，并给出排名 dense-rank
select dname, ename, salary,
dense_rank() over(partition by dname order by salary desc) as rn 
from employee;

-- 求出每个部门薪资排在前三名的员工- 分组求TOPN
select * 
from (select dname, ename, salary, dense_rank() over(partition by dname order by salary desc) as rn from employee
) t
where t.rn <= 3

-- 对所有员工进行全局排序（不分组）
-- 不加partition by表示全局排序
select dname, ename, salary,
     dense_rank() over( order by salary desc)  as rn
from employee;

开窗聚合函数- SUM,AVG,MIN,MAX
在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()），可以动态计算在指定的窗口内的各种聚合函数值。

select dname, ename, salary,
	sum(salary) over(partition by dname order by hiredate) as pv1 
from employee;
 
select cookieid,createtime,pv,
	sum(pv) over(partition by cookieid) as pv3
from itcast_t1;  -- 如果没有order  by排序语句  默认把分组内的所有数据进行sum操作

select dname, ename, salary,
     sum(salary) over(partition by dname order by hiredate  rows between unbounded preceding and current row) as c1 
from employee;
 
select dname, ename, salary,
     sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and current row) as c1 
from employee;

select dname, ename, salary,
     sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and 1 following) as c1 
from employee;

select dname, ename, salary,
     sum(salary) over(partition by dname order by hiredate   rows between current row and unbounded following) as c1 
from employee;

分布函数- CUME_DIST和PERCENT_RANK

介绍-CUME_DIST

• 用途：分组内小于、等于当前rank值的行数 / 分组内总行数
• 应用场景：查询小于等于当前薪资（salary）的比例

/*
rn1: 没有partition,所有数据均为1组，总行数为12，
     第一行：小于等于3000的行数为3，因此，3/12=0.25
     第二行：小于等于4000的行数为5，因此，5/12=0.4166666666666667
rn2: 按照部门分组，dname='研发部'的行数为6,
     第一行：研发部小于等于3000的行数为1，因此，1/6=0.16666666666666666
*/
select dname, ename, salary,
     cume_dist() over(order by salary) as rn1, -- 没有partition语句 所有的数据位于一组
     cume_dist() over(partition by dept order by salary) as rn2 
from employee;
/*
rn1: 没有partition,所有数据均为1组，总行数为12，
     第一行：小于等于3000的行数为3，因此，3/12=0.25
     第二行：小于等于4000的行数为5，因此，5/12=0.4166666666666667
rn2: 按照部门分组，dname='研发部'的行数为6,
     第一行：研发部小于等于3000的行数为1，因此，1/6=0.16666666666666666
*/

介绍-PERCENT_RANK

• 用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
• 应用场景：不常用

select dname, ename, salary,
     rank() over(partition by dname order by salary desc ) as rn,
     percent_rank() over(partition by dname order by salary desc ) as rn2
from employee;
/*
 rn2:
  第一行: (1 - 1) / (6 - 1) = 0
  第二行: (1 - 1) / (6 - 1) = 0
  第三行: (3 - 1) / (6 - 1) = 0.4
*/

前后函数-LAG和LEAD

• 用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
• 应用场景：查询前1名同学的成绩和当前同学成绩的差值

-- lag的用法
select dname, ename, hiredate, salary,
	lag(hiredate,1,'2000-01-01')over(partition by dname order by hiredate) as last_1_time,
	lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
from employee;
/*
last_1_time: 指定了往上第1行的值，default为'2000-01-01'  
                         第一行，往上1行为null,因此取默认值 '2000-01-01'
                         第二行，往上1行值为第一行值，2021-11-01 
                         第三行，往上1行值为第二行值，2021-11-02 
last_2_time: 指定了往上第2行的值，为指定默认值
                         第一行，往上2行为null
                         第二行，往上2行为null
                         第四行，往上2行为第二行值，2021-11-01 
                         第七行，往上2行为第五行值，2021-11-02 
*/

-- lead的用法
select dname, ename, hiredate, salary,
     lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
     lead(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
from employee;

头尾函数-FIRST_VALUE和LAST_VALUE

• 用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值
• 应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

-- 注意,  如果不指定ORDER BY，则进行排序混乱，会出现错误的结果
select dname, ename, hiredate, salary,
      first_value(salary) over(partition by dname order by hiredate) as first,
      last_value(salary) over(partition by dname order by  hiredate) as last 
from  employee;

其他函数-NTH_VALUE(expr, n)、NTILE(n)

介绍-NTH_VALUE(expr,n)

• 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
• 应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资

-- 查询每个部门截止目前薪资排在第二和第三的员工信息
select dname, ename, hiredate, salary,
      nth_value(salary,2) over(partition by dname order by hiredate) as second_score,
      nth_value(salary,3) over(partition by dname order by hiredate) as third_score
from employee

介绍-NTILE

• 用途：将分区中的有序数据分为n个等级，记录等级数
• 应用场景：将每个部门员工按照入职日期分成3组

-- 根据入职日期将每个部门的员工分成3组
select dname, ename, hiredate, salary,
	ntile(3) over(partition by dname order by  hiredate  ) as rn 
from employee;

-- 取出每个部门的第一组员工
select *
from
(
    SELECT dname, ename, hiredate, salary,
    NTILE(3) OVER(PARTITION BY dname ORDER BY  hiredate  ) AS rn 
    FROM employee
)t
where t.rn = 1;

九、视图

9.1、视图的概念

• 视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。
• 数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
• 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

9.2、视图的作用

• 安全性
  如果我们直接将数据表授权给用户操作，那么用户可以CRUD数据表中所有数据，加入我们想要对数据表中的部分数据进行保护，可以将公开的数据⽣成视图，授权用户访问视图；用户通过查询视图可以获取数据表中公开的数据，从而达到将数据表中的部分数据对用户隐藏。
• 简单性
  如果我们需要查询的数据来源于多张数据表，可以使用多表连接查询来实现；我们通过视图将这些连表查询的结果对用户开放，用户则可以直接通过查询视图获取多表数据，操作更便捷。

9.3、创建视图

## 创建视图
CREATE VIEW <view_name> AS <tableName>

9.4、视图数据的特性

视图是虚拟表，查询视图的数据是来源于数据表的。当对视图进行操作时，对原数据表中的数据是否由影响呢？

• 查询操作
  如果在数据表中添加了新的数据，而且这个数据满⾜创建视图时查询语句的条件，通过查询视图也可以查询出新增的数据；当删除原表中满足查询条件的数据时，也会从视图中删除。
• 新增数据
  如果在视图中添加数据，数据会被添加到原数据表
• 删除数据
  如果从视图删除数据，数据也将从原表中删除
• 修改操作
  如果通过修改数据，则也将修改原数据表中的数据视图的使用建议 : 对复杂查询简化操作，并且不会对数据进行修改的情况下可以使用视图。

9.5、查询视图结构

-- 查询视图结构
desc <view_name>

9.6、修改视图

-- ⽅式1
create OR REPLACE view <view_name>
AS
select * from students where stu_gender='⼥';

-- ⽅式2
alter view  <view_name>
AS 
select * from students where stu_gender='男';

9.7、更新视图

某些视图是可更新的。也就是说，可以在UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种，那么它就是不可更新的：

• 聚合函数（SUM(), MIN(), MAX(), COUNT()等）
• DISTINCT
• GROUP BY
• HAVING
• UNION或UNION ALL
• 位于选择列表中的子查询
• JOIN
• FROM子句中的不可更新视图
• WHERE子句中的子查询，引用FROM子句中的表。
• 仅引用文字值（在该情况下，没有要更新的基本表）

create or replace view <view_name>
AS
select * from students where stu_gender='男';

9.8、删除视图

删除数据表时会同时删除数据表中的数据，删除视图时不会影响原数据表中的数据

-- 删除视图
drop view <view_name>;

十、存储过程

10.1、存储过程介绍

什么是存储过程

• MySQL 5.0 版本开始支持存储过程。
• 简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；
• 存储过就是数据库 SQL 语言层面的代码封装与重用。

特性

• 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
• 函数的普遍特性：模块化，封装，代码复用；
• 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

10.1.1、SQL指令执行过程

从SQL执行的流程中我们分析存在的问题：

1. 如果我们需要重复多次执行相同的SQL，SQL执行都需要通过连接传递到MySQL，并且 需要经过编译和执行的步骤；
2. 如果我们需要连续执行多个SQL指令，并且第⼆个SQL指令需要使用第⼀个SQL指令执行的结果作为参数；

10.1.2、存储过程的介绍

存储过程： 将能够完成特定功能的SQL指令进行封装（SQL指令集），编译之后存储在数据库服务器上，并且为之取⼀个名字，客户端可以通过名字直接调用这个SQL指令集，获取执行结果。

存储过程优点

• SQL指令⽆需客户端编写，通过⽹络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意篡改保证安全性；
• 存储过程经过编译创建并保存在数据库中的，执行过程⽆需重复的进行编译操作，对SQL 指令的执行过程进行了性能提升；
• 存储过程中多个SQL指令之间存在逻辑关系，⽀持流程控制语句（分⽀、循环），可以实现更为复杂的业务;

存储过程的缺点

• 存储过程是根据不同的数据库进行编译、创建并存储在数据库中；当我们需要切换到其他的数据库产品时，需要重写编写针对于新数据库的存储过程；
• 存储过程受限于数据库产品，如果需要高性能的优化会成为⼀个问题；
• 在互联网项目中，如果需要数据库的高（连接）并发访问，使用存储过程会增加数据库的连接执行时间（因为我们将复杂的业务交给了数据库进行处理）

10.2、创建存储过程

10.2.1、存储过程创建语法

-- 语法:
delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
  sql语句
end 自定义的结束符合
delimiter ;

10.2.2、示例

## 创建⼀个存储过程实现加法运算: Java语法中，⽅法是有参数和返回值的
## 存储过程中，是有输⼊参数 和 输出参数的
create procedure proc_test1(IN a int,IN b int,OUT c int)
begin
 SET c = a+b;
end;

10.3、调用存储过程

## 调⽤存储过程
## 定义变量@m
SET @m=0; 
## 调用存储过程，将3传递给a，将2传递给b，将@m传递给c
CALL proc_test1 (3,2,@m);
## 显示变量值
SELECT @m FROM DUAL;

10.4、存储过程中变量的使用

存储过程中的变量分为两种：

• 局部变量
• ⽤户变量

10.4.1、定义局部变量

局部变量：定义在存储过程中的变量，只能在存储过程内部使用局部变量定义语法

## 局部变量要定义在存储过程中，⽽且必须定义在存储过程开始
declare <attr_name> <type> [default value];

## 局部变量定义示例：
CREATE PROCEDURE proc_test2 ( IN a INT, OUT r INT ) BEGIN
	## 定义x int类型，默认值为0
	DECLARE x INT DEFAULT 0;
	## 定义y
	DECLARE y INT DEFAULT 1;
	
	SET x = a * a;
	
	SET y = a / 2;
	
	SET r = x + y;
END;

10.4.2、定义用户变量

用户变量：相当于全局变量，定义的⽤户变量可以通过 select @attrName from dual 进行查询

## 用户变量会存储在mysql数据库的数据字典中（dual）
## 用户变量定义使⽤set关键字直接定义，变量名要以@开头
SET@n=1;

10.4.3、给变量设置值

⽆论是局部变量还是用户变量，都是使用 set 关键字修改值

SET @n=1; 
CALL proc_test2 (6,@n); 
SELECT @n FROM DUAL;

用户变量使用注意事项
因为用户变量相当于全局变量，可以在SQL指令以及多个存储过程中共享，在开发中建议尽量少使用用户变量，用户变量过多会导致程序不易理解、难以维护。

10.5、存储过程的参数

MySQL存储过程的参数⼀共有三种：IN \ OUT \ INOUT

• IN 输入参数
  输入参数——在调⽤存储过程中传递数据给存储过程的参数（在调用的过程必须为具有实际值的变量 或者 字面值）
• OUT 输出参数
  输出参数——将存储过程中产⽣的数据返回给过程调用者，相当于Java⽅法的返回值，但不同的是⼀个存储过程可以有多个输出参数
• INOUT 输入输出参数

10.6、存储过程中流程控制

在存储过程中⽀持流程控制语句用于实现逻辑的控制

10.6.1 分支语句

if-then-else

## 单分支：如果条件成立，则执行SQL
IF conditions THEN
	## SQL
END IF;

## 双分支：如果条件成立则执行SQL1，否则执行SQL2
IF conditions THEN
	## SQL1
ELSE
	## SQL2
END IF;

case

CREATE PROCEDURE proc_test3 (IN a INT) 
BEGIN 
	CASE a WHEN 1 THEN
		## SQL1 如果a的值为1 则执行SQL1
	WHEN 2 THEN
		## SQL2 如果a的值为2 则执行SQL2
	ELSE
		## SQL (如果变量的值和所有when的值都不匹配，则执行else中的这个SQL)
 END CASE;
END;

10.6.2、循环语句

while

CREATE PROCEDURE proc_test4 (IN num INT) 
BEGIN 
	DECLARE i INT; 
	SET i=0; 
	WHILE i< num DO
		-- SQL
		SET i=i+1; 
	END WHILE; 
END;
CALL proc_test4(4);

repeat

CREATE PROCEDURE proc_test5 (IN num INT) 
BEGIN 
	DECLARE i INT; 
	SET i=1; 
	REPEAT
		-- SQL
		SET i=i+1; 
		UNTIL i> num 
	END REPEAT; 
END; 
CALL proc_test5 (4);

loop

CREATE PROCEDURE proc_test6 (IN num INT) 
BEGIN 
	DECLARE i INT; 
	SET i=0; 
	myloop : LOOP-- SQL
			INSERT INTO classes (class_name) VALUES (CONCAT('HTML',i			)); 
			SET i=i+1; 
			IF i=num THEN 
				LEAVE myloop; 
			END IF; 
	END LOOP; 
END; 
CALL proc_test6 (5);

10.7、存储过程管理

10.7.1、查询存储过程

存储过程是属于某个数据库的，也就是说当我们将存储过程创建在某个数据库之后，只能在当前数据库中调用此存储过程。

查询存储过程：查询某个数据库中有哪些存储过程

## 根据数据库名，查询当前数据库中的存储过程
SHOW PROCEDURE STATUS WHERE db='mysql_test';
-- 查询存储过程的创建细节
SHOW CREATE PROCEDURE mysql_test.proc_test1;

10.7.2、修改存储过程

修改存储过程指的是修改存储过程的特征/特性

ALTER PROCEDURE<proc_name> 特征1 [特征2 特征3 ....] 

存储过程的特征参数：

• CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句
• NO SQL 表示子程序中不包含 SQL 语句
• READS SQL DATA 表示子程序中包含读数据的语句
• MODIFIES SQL DATA 表示子程序中包含写数据的语句
• SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行
  • DEFINER 表示只有定义者自己才能够执行
  • INVOKER 表示调用者可以执行
• COMMENT ‘string’ 表示注释信息

ALTER PROCEDURE proc_test1 READS SQL DATA;

10.7.3、删除存储过程

## 删除存储过程
## drop 删除数据库中的对象 数据库、数据表、列、存储过程、视图、触发器、索引....
## delete 删除数据表中的数据
DROP PROCEDURE proc_test1;

10.8、游标

游标可以用来依次取出查询结果集中的每⼀条数据——逐条读取查询结果集中的记录

游标的使用步骤

## 1、声明游标
DECLARE cursor_nanme CURSOR FOR select_statement;

## 2、打开游标
open mycursor; 

## 3、使用游标，使用游标：提取游标当前指向的记录（提取之后，游标⾃动下移）
FETCH mycursor INTO bname,bauthor,bprice;

## 4、关闭游标
CLOSE mycursor; 

十一、触发器

11.1、触发器操作

• 触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动条用。
• 在MySQL中，只有执行insert,delete,update操作时才能触发触发器的执行
• 触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。
• 使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器的特性

1. 什么条件会触发：I、D、U
2. 什么时候触发：在增删改前或者后
3. 触发频率：针对每一行执行
4. 触发器定义在表上，附着在表上

## 创建触发器
create trigger tri_name
<before|after> -- 定义触发时机
<insert|delete|update> -- 定义DML类型
ON <table_name>
for each row -- 声明为⾏级触发器（只要操作⼀条记录就触发触发器执⾏⼀
次）
sql_statement -- 触发器操作


## 查看触发器
SHOW TRIGGERS;

## 删除触发器
DROP TRIGGER tri_test1;

11.2、NEW与OLD

触发器用于监听对数据表中数据的insert、delete、update操作，在触发器中通常处理⼀些DML的关联操作；我们可以使用 NEW 和 OLD 关键字在触发器中获取触发这个触发器的DML操作的数据

• NEW
  在触发器中用于获取insert操作添加的数据、update操作修改后的记录
• OLD
  在触发器中用于获取delete操作删除前的数据、update操作修改前的数据

11.3、触发器使用总结

优点

• 触发器是⾃动执行的，当对触发器相关的表执行响应的DML操作时立即执行；
• 触发器可以实现表中的数据的级联操作（关联操作），有利于保证数据的完整性；
• 触发器可以对DML操作的数据进行更为复杂的合法性校验

缺点

• 使用触发器实现的业务逻辑如果出现问题将难以定位，后期维护困难；
• ⼤量使用触发器容易导致代码结构杂乱，增加了程序的复杂性；
• 当触发器操作的数据量比较⼤时，执行效率会大大降低。

使用建议

• 在互联网项目中，应避免适应触发器；
• 对于并发量不大的项目可以选择使用存储过程，但是在互联网引用中不提倡使用存储过程
  原因：存储过程时将实现业务的逻辑交给数据库处理，
  • ⼀则增减了数据库的负载，
  • ⼆则不利于数据库的迁移

十二、索引

数据库是用来存储数据，在互联网应用中数据库中存储的数据可能会很多(大数据)， 数据表中数据的查询速度会随着数据量的增长逐渐变慢 ，从而导致响应用户请求的速度变慢——用户体验差，我们如何提⾼数据库的查询效率呢？

12.1、索引的介绍

索引，就是用来提⾼数据表中数据的查询效率的。

索引，就是将数据表中某⼀列\某几列的值取出来构造成便于查找的结构进行存储，生成数据表的目录

当我们进行数据查询的时候，则先在目录中进行查找得到对应的数据的地址，然后再到数据表中根据地址快速的获取数据记录，避免全表扫描。

12.2、索引的分类

MySQL中的索引，根据创建索引的列的不同，可以分为：

• 主键索引
  在数据表的主键字段创建的索引，这个字段必须被primary key修饰，每张表只能有⼀个主键
• 唯⼀索引
  在数据表中的唯⼀列创建的索引(unique)，此列的所有值只能出现⼀次，可以为NULL
• 普通索引
  在普通字段上创建的索引，没有唯⼀性的限制
• 组合索引
  两个及以上字段联合起来创建的索引

在创建数据表时，将字段声明为主键（添加主键约束），会⾃动在主键字段创建主键索引；
在创建数据表时，将字段声明为唯⼀键（添加唯⼀约束），会⾃动在唯⼀字段创建唯⼀索引；

12.3、创建索引

## 创建唯⼀索引: 创建唯⼀索引的列的值不能重复
-- create unique index <index_name> on 表名(列名);
create unique index idx_name on user(name);

## 创建普通索引: 不要求创建索引的列的值的唯⼀性
-- create index <index_name> on 表名(列名);
create index idx_age on user(age);

## 创建组合索引
-- create index <index_name> on 表名(列名1, 列名2...);
create index idx_gender_tel on user(gender, tel);

## 全⽂索引
-- MySQL 5.6 版本新增的索引，可以通过此索引进⾏全⽂检索操作，因为MySQL全⽂检索不⽀持中⽂，
-- 因此这个全⽂索引不被开发者关注，在应⽤开发中通常是通过搜索引擎（数据库中间件）实现全⽂检索
-- create fulltext index <index_name> on 表名(字段名); 
create fulltext index idx_address on user(address); 

12.4、索引使用

索引创建完成之后⽆需调用，当根据创建索引的列进行数据查询的时候，会⾃动使用索引；

组合索引需要根据创建索引的所有字段进行查询时触发。

## 查看查询语句的查询规划
-- explain 查询sql语句
explain select * from user where id='10001';

12.5、查看索引

-- show create table <tableName>;
show create table user;

-- 查询数据表的索引
show indexes from user;

-- 查询索引
show keys from user;

12.6、删除索引

-- 删除索引：索引是建⽴在表的字段上的，不同的表中可能会出现相同名称的索引
-- 因此删除索引时需要指定表名
-- drop index <indexName> on <tableName>;
drop index idx_name on user;

12.7、索引的使用总结

优点

• 索引⼤⼤降低了数据库服务器在执行查询操作时扫描的数据，提高查询效率
• 索引可以避免服务器排序、将随机IO编程顺序IO

缺点

• 索引是根据数据表列的创建的，当数据表中数据发⽣DML操作时，索引页需要更新；
• 索引⽂件也会占用磁盘空间；

注意事项
1.数据表中数据不多时，全表扫⾯可能更快吗，不要使⽤索引；
2.数据量⼤但是DML操作很频繁时，不建议使用索引；
3.不要在数据重复读高的列上创建索引（性别）；
4.创建索引之后，要注意查询SQL语句的编写，避免索引失效。

十三、MySQL的存储引擎

• 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。
• 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
• 用户可以根据不同的需求为数据表选择不同的存储引擎
• 可以使用 SHOW ENGINES 命令 可以查看Mysql的所有执行引擎我们 可以到 默认的执行引擎是innoDB 支持事务，行级锁定和外键。

分类

• MyISAM：Mysql 5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务
• InnoDB：事务型速记的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5成为默认数据库引擎
• Memory： 所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MYSQL重新启动是会丢失。
• Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度，但其对查询的支持相对较差
• Federated ：将不同的 MySQL 服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用
• CSV ：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .csv 文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV 存储引擎不支持索引。
• BlackHole： 黑洞引擎，写入的任何数据都会消失，一般用于记录 binlog 做复制的中继
• ERFORMANCE_SCHEMA存储引擎该引擎主要用于收集数据库服务器性能参数。
• Mrg_Myisam Merge存储引擎，是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。
  

-- 查询当前数据库支持的存储引擎：
show engines;
 
-- 查看当前的默认存储引擎：
show variables like ‘%storage_engine%’;

-- 查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): 
show create table student; 
 
-- 创建新表时指定存储引擎：
create table(...) engine=MyISAM;
 
-- 修改数据库引擎
alter table student engine = INNODB;
alter table student engine = MyISAM;

修改MySQL默认存储引擎方法

1. 关闭mysql服务
2. 找到mysql安装目录下的my.ini文件：
3. 找到default-storage-engine=INNODB 改为目标引擎，
   如：default-storage-engine=MYISAM
4. 启动mysql服务

十四、MySQL的事务

14.1、事务的概念

• 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。
• 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
• 用户可以根据不同的需求为数据表选择不同的存储引擎
• 可以使用 SHOW ENGINES 命令 可以查看Mysql的所有执行引擎我们 可以到 默认的执行引擎是innoDB 支持事务，行级锁定和外键。

14.2、什么是事务

• 在MySQL中的事务（Transaction）是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 DDL、DML、DCL操作，比如 insert,update,delete 语句，默认是自动提交的。

14.3、MySQL的事务操作

MySQL的事务操作主要有以下三种：
1、开启事务：Start Transaction
任何一条DML语句(insert、update、delete)执行，标志事务的开启
命令：BEGIN 或 START TRANSACTION

2、提交事务：Commit Transaction
成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
命令：COMMIT

3、回滚事务：Rollback Transaction
失败的结束，将所有的DML语句操作历史记录全部清空
命令：ROLLBACK

之前的所有SQL操作其实也有事务，只是MySQL自动帮我们完成的，每执行一条SQL时MySQL就帮我们自动提交事务，因此如果想要手动控制事务，则必须关闭MySQL的事务自动提交。

在MySQL中直接用 SET 来改变 MySQL 的自动提交模式:

-- 禁止自动提交 
set autocommit=0 
-- 开启自动提交 
set autocommit=1 

create database if not exists mydb12_transcation;
use mydb12_transcation;
-- 创建账户表
create table account(
    id int primary key, -- 账户id
    name varchar(20), -- 账户名
    money double -- 金额
);
 

--  插入数据
insert into account values(1,'zhangsan',1000);
insert into account values(2,'lisi',1000);
 
 -- 设置MySQL的事务为手动提交(关闭自动提交)
select @@autocommit;
set autocommit = 0;
 
-- 模拟账户转账
-- 开启事务 
begin;
update account set money = money - 200 where name = 'zhangsan';
update account set money = money + 200 where name = 'lisi';
-- 提交事务
commit;
 
-- 如果转账中的任何一条出现问题，则回滚事务
rollback;

14.4、事务的特性

14.5、事务的隔离级别

事务的并发问题

• 脏读
  读取了另一个事务未提交的数据
• 不可重复度
  在一个事务内，读取表中的某一行数据，多次读取结果不同，不一定是错误，只是场合不对
• 虚读 (不可重复读)
  一个事务内，读取到了别的事务插入的数据，导致前后读取不一致

• 读未提交(Read uncommitted)
  一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证,会造成脏读。
• 读已提交(Read committed)
  一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读。
• 可重复读(Repeatable read)
  就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造成幻读。
• 串行(Serializable)
  是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

Mysql的默认隔离级别是Repeatable read。

事务的隔离级别-操作

-- 查看隔离级别 
show variables like '%isolation%’; 

-- 设置隔离级别
/*
set session transaction isolation level 级别字符串
级别字符串：read uncommitted、read committed、repeatable read、serializable
*/
-- 设置read uncommitted
set session transaction isolation level read uncommitted;
 
-- 设置read committed
set session transaction isolation level read committed;
 
-- 设置repeatable read
set session transaction isolation level repeatable read;
 
-- 设置serializable
set session transaction isolation level serializable;

十五、MySQL的锁机制

15.1、概述

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。

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

从对数据操作的粒度分 ：

• 表锁：操作时，会锁定整个表。
• 行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：
MySQL锁的特性可大致归纳如下 ：

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；

而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

15.2、MyISAM 表锁

MyISAM 存储引擎只支持表锁

如何加表锁
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

-- 加读锁 
lock table table_name read; 

-- 加写锁
 lock table table_name write；

表锁特点

1. 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

2. 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

 
-- MySQL的锁机制
drop database if exists  mydb14_lock;
create database mydb14_lock ;
 
use mydb14_lock;
  
create table `tb_book` (
  `id` int(11) auto_increment,
  `name` varchar(50) default null,
  `publish_time` date default null,
  `status` char(1) default null,
  primary key (`id`)
) engine=myisam default charset=utf8 ;
 
insert into tb_book (id, name, publish_time, status) values(null,'java编程思想','2088-08-01','1');
insert into tb_book (id, name, publish_time, status) values(null,'solr编程思想','2088-08-08','0');
 
create table `tb_user` (
  `id` int(11) auto_increment,
  `name` varchar(50) default null,
  primary key (`id`)
) engine=myisam default charset=utf8 ;
 
insert into tb_user (id, name) values(null,'令狐冲');
insert into tb_user (id, name) values(null,'田伯光');

15.3、InnoDB行锁

行锁特点
偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

InnoDB 与 MyISAM 的最大不同有两点

• 一是支持事务
• 二是 采用了行级锁。

行锁模式
InnoDB 实现了以下两种类型的行锁。

• 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
• 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

-- 可以通过以下语句显示给记录集加共享锁或排他锁 。
-- 共享锁（S）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 

-- 排他锁（X)
SELECT * FROM table_name WHERE ... FOR UPDATE

-- 行锁 
drop table if exists test_innodb_lock;
create table test_innodb_lock(
    id int(11),
    name varchar(16),
    sex varchar(1)
)engine = innodb ;
 
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');
 
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

十六、MySQL的日志

在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件。MySQL 也不例外。

日志分类

• 错误日志
• 二进制日志
• 查询日志
• 慢查询日志

16.1、错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

该日志是默认开启的 ， 默认存放目录为 mysql 的数据目录, 默认的日志文件名为 hostname.err（hostname是主机名）。

-- 查看日志位置指令 ： 
show variables like 'log_error%';

16.2、二进制日志-binlog

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。

二进制日志，MySQl8.0默认已经开启，低版本的MySQL的需要通过配置文件开启，并配置MySQL日志的格式。
Windows系统：my.ini Linux系统:my.cnf

# 配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 、
# 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin

# 配置二进制日志的格式
binlog_format=STATEMENT

日志格式

• STATEMENT
  该日志格式在日志文件中记录的都是SQL语句（statement），每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。
• ROW
  该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句 ： update tb_book set status=‘1’ , 如果是STATEMENT 日志格式，在日志中会记录一行SQL文件； 如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更。
• MIXED
  混合了STATEMENT 和 ROW两种格式。

-- 查看MySQL是否开启了binlog日志
show variables like 'log_bin';

-- 查看binlog日志的格式
show variables like 'binlog_format';
 
-- 查看所有日志
show binlog events;
 
-- 查看最新的日志
show master status;
 
-- 查询指定的binlog日志
show binlog events in 'binlog.000010';
select * from mydb1.emp2;
select count(*) from mydb1.emp2;
update mydb1.emp2 set salary = 8000;

-- 从指定的位置开始,查看指定的Binlog日志
show binlog events in 'binlog.000010' from 156;

-- 从指定的位置开始,查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 156 limit 2;
--从指定的位置开始，带有偏移，查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 666 limit 1, 2;
 
-- 清空所有的 binlog 日志文件
reset master

16.3、查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。

默认情况下， 查询日志是未开启的。如果需要开启查询日志，可以设置以下配置

# 该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启 
general_log=1

# 设置日志的文件名 ， 如果没有指定， 默认的文件名为 host_name.log 
general_log_file=file_name

-- 查看MySQL是否开启了查询日志
show variables like 'general_log';
 
-- 开启查询日志
set global  general_log=1;
 
select * from mydb1.emp2;
select * from mydb6_view.emp;
 
select count(*) from mydb1.emp2;
select count(*) from mydb6_view.emp;
update mydb1.emp2 set salary = 9000;

-- 查看MySQL是否开启了查询日志
show variables like 'general_log';
 
-- 开启查询日志
set global  general_log=1;
 
select * from mydb1.emp2;
select * from mydb6_view.emp;
 
select count(*) from mydb1.emp2;
select count(*) from mydb6_view.emp;
update mydb1.emp2 set salary = 9000;

16.4、慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒。

# 该参数用来控制慢查询日志是否开启， 可取值： 1 和 0 ， 1 代表开启， 0 代表关闭
slow_query_log=1

 # 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log

# 该选项用来配置查询的时间限制， 超过这个时间将认为值慢查询， 将需要进行日志记录， 默认10s
long_query_time=10

十七、MySQL的优化

17.1、概念

在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化.

MySQL的优化方式有很多，大致我们可以从以下几点来优化MySQL:

• 从设计上优化
• 从查询上优化
• 从索引上优化
• 从存储上优化

17.2、查看SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型。

--下面的命令显示了当前 session 中所有统计参数的值
show session status like 'Com_______';  -- 查看当前会话统计结果
show global  status  like 'Com_______';  -- 查看自数据库上次启动至今统计结果
 
show status like 'Innodb_rows_%’;       -- 查看针对Innodb引擎的统计结果

17.3、定位低效率执行SQL

• 可以通过以下两种方式定位执行效率较低的 SQL 语句。
  慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句。
• show processlist：该命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

定位低效率执行SQL-慢查询日志

-- 查看慢日志配置信息 
show variables like '%slow_query_log%’; 

-- 开启慢日志查询 
set global slow_query_log=1; 

-- 查看慢日志记录SQL的最低阈值时间 
show variables like 'long_query_time%’; 

-- 修改慢日志记录SQL的最低阈值时间 
set global long_query_time=4;

定位低效率执行SQL-show processlist

show processlist; 

1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4） db列，显示这个进程目前连接的是哪个数据库
5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
6） time列，显示这个状态持续的时间，单位是秒
7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8） info列，显示这个sql语句，是判断问题语句的一个重要依据

17.4、explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

-- 准备测试数据 
create database mydb13_optimize; 
use mydb13_optimize; 

执行sql脚本sql_optimize.sql添加数据 

explain select * from user where uid = 1;

explain select * from user where uname = '张飞';

17.4.1、Explain分析执行计划-Explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种:

• id 相同表示加载表的顺序是从上到下。
• id 不同id值越大，优先级越高，越先被执行。
• id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

17.4.2、Explain分析执行计划-Explain 之 select_type

表示 SELECT 的类型，常见的取值，如下表所示：

17.4.3、Explain分析执行计划-Explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

结果值从最好到最坏以此是：system > const > eq_ref > ref > range > index > ALL

17.4.4、Explain分析执行计划-其他指标字段

• Explain 之 table
  显示这一步所访问数据库中表名称有时不是真实的表名字，可能是简称，
• explain 之 rows
  扫描行的数量。
• Explain 之 key
  possible_keys : 显示可能应用在这张表的索引， 一个或多个。
  key ： 实际使用的索引， 如果为NULL， 则没有使用索引。
  key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
• Explain之 extra
  其他的额外的执行计划信息，在该列展示 。
  

17.5、show profile分析SQL

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。。
通过 have_profiling 参数，能够看到当前MySQL是否支持profile：

select @@have_profiling; 
set profiling=1; -- 开启profiling 开关； 

通过profile，我们能够更清楚地了解SQL执行的过程。首先，我们可以执行一系列的操作

show databases;
 
use mydb13_optimize;
 
show tables;
 
select * from user where id < 2;
 
select count(*) from user;

执行完上述命令之后，再执行show profiles 指令， 来查看SQL语句执行的耗时：

show profiles;

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

show profile for query 8;

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间
在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：

show profile cpu for query 133;  

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：

17.6、trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划

打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

SET optimizer_trace="enabled=on",end_markers_in_json=on; 
set optimizer_trace_max_mem_size=1000000;

执行SQL语句 ：

select * from user where uid < 2;

最后， 检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的 ：

select * from information_schema.optimizer_trace\G;

17.7、使用索引优化

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

数据准备

create table `tb_seller` (
    `sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1),
    `address` varchar (100),
    `createtime` datetime,
    primary key(`sellerid`)
); 

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

数据准备

-- 创建组合索引 
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

避免索引失效应用-全值匹配

-- 创建组合索引 
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

避免索引失效应用-最左前缀法则

-- 最左前缀法则
 -- 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
explain select * from tb_seller where name='小米科技'; -- 403
 
explain select * from tb_seller where name='小米科技' and status='1'; -- 410
explain select * from tb_seller where  status='1' and name='小米科技'; -- 410
-- 违法最左前缀法则 ， 索引失效：
explain select * from tb_seller where status='1'; -- nulll
 
-- 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
explain select * from tb_seller where name='小米科技'  and address='北京市'; -- 403

避免索引失效应用-其他匹配原则

-- 范围查询右边的列，不能使用索引 。 
explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市’; 

-- 不要在索引列上进行运算操作， 索引将失效。 
explain select * from tb_seller where substring(name,3,2)='科技’ 

-- 字符串不加单引号，造成索引失效。 
explain select * from tb_seller where name='小米科技' and status = 1 ;

-- 1、范围查询右边的列，不能使用索引 。
-- 根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。
explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市';
 
-- 2、不要在索引列上进行运算操作， 索引将失效。
explain select * from tb_seller where substring(name,3,2)='科技'
 
-- 3、字符串不加单引号，造成索引失效。 
explain select * from tb_seller where name='小米科技' and status = 1 ;
 
-- 4、尽量使用覆盖索引，避免select *
-- 需要从原表及磁盘上读取数据
explain select * from tb_seller where name='小米科技'  and address='北京市';  -- 效率低
 
-- 从索引树中就可以查询到所有数据
explain select name from tb_seller where name='小米科技'  and address='北京市';  -- 效率高
explain select name,status,address from tb_seller where name='小米科技'  and address='北京市';  -- 效率高
-- 如果查询列，超出索引列，也会降低性能。
explain select name,status,address,password from tb_seller where name='小米科技'  and address='北京市';  -- 效率低

-- 尽量使用覆盖索引，避免select *
-- 需要从原表及磁盘上读取数据
explain select * from tb_seller where name='小米科技'  and address='北京市';  -- 效率低
 
-- 从索引树中就可以查询到所有数据
explain select name from tb_seller where name='小米科技'  and address='北京市';  -- 效率高
explain select name,status,address from tb_seller where name='小米科技'  and address='北京市';  -- 效率高
-- 如果查询列，超出索引列，也会降低性能。
explain select name,status,address,password from tb_seller where name='小米科技'  and address='北京市';  -- 效率低

-- 用or分割开的条件， 那么涉及的索引都不会被用到。
explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'; 
explain select * from tb_seller where name='黑马程序员' or address = '西安市';  
explain select * from tb_seller where name='黑马程序员' or status = '1';   
 
-- 以%开头的Like模糊查询，索引失效。
explain select * from tb_seller where name like '科技%'; -- 用索引
explain select * from tb_seller where name like '%科技'; -- 不用索引
explain select * from tb_seller where name like '%科技%';-- 不用索引
-- 弥补不足,不用*，使用索引列
explain select name from tb_seller where name like '%科技%';
 
--  1、如果MySQL评估使用索引比全表更慢，则不使用索引。
  -- 这种情况是由数据本身的特点来决定的
create index index_address on tb_seller(address);
 
explain select * from tb_seller where address = '北京市'; -- 没有使用索引
explain select * from tb_seller where address = '西安市'; -- 没有使用索引
 
 
--  2、is  NULL ， is NOT NULL  有时有效，有时索引失效。
create index index_address on tb_seller(nickname);
explain select * from tb_seller where nickname is NULL;  -- 索引有效
explain select * from tb_seller where nickname is not NULL; -- 无效

17.8、SQL优化

17.8.1、大批量插入数据

create table `tb_user` (
  `id` int(11) not null auto_increment,
  `username` varchar(45) not null,
  `password` varchar(96) not null,
  `name` varchar(45) not null,
  `birthday` datetime default null,
  `sex` char(1) default null,
  `email` varchar(45) default null,
  `phone` varchar(45) default null,
  `qq` varchar(32) default null,
  `status` varchar(32) not null comment '用户状态',
  `create_time` datetime not null,
  `update_time` datetime default null,
  primary key (`id`),
  unique key `unique_user_username` (`username`)
);

当使用load 命令导入数据的时候，适当的设置可以提高导入的效率。对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

1） 主键顺序插入
因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

-- 1、首先，检查一个全局系统变量 'local_infile' 的状态， 如果得到如下显示 Value=OFF，则说明这是不可用的
show global variables like 'local_infile';
 
-- 2、修改local_infile值为on，开启local_infile
set global local_infile=1;
 
-- 3、加载数据 
/*
脚本文件介绍 :
    sql1.log  ----> 主键有序
    sql2.log  ----> 主键无序
*/
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';

2 )、关闭唯一性校验
在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

-- 关闭唯一性校验
SET UNIQUE_CHECKS=0;
 
truncate table tb_user;
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
 
SET UNIQUE_CHECKS=1;

17.8.2、优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案:

-- 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

-- 原始方式为：
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
 
-- 优化后的方案为 ： 
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

-- 在事务中进行数据插入。
begin;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

-- 数据有序插入
insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');
 
-- 优化后
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

17.8.3、优化order by语句

1、环境准备

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
 
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
 
create index idx_emp_age_salary on emp(age,salary);

2、两种排序方式
第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
3、Filesort 的优化
通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。对于Filesort ， MySQL 有两种排序算法：

1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ：

drop index idx_emp_age_salary on emp; 

explain select age,count(*) from emp group by age;

explain select age,count(*) from emp group by age order by null;

create index idx_emp_age_salary on emp(age,salary)；

17.8.4、优化子查询

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

explain select * from user where uid in (select uid from user_role ); 

explain select * from user u , user_role ur where u.uid = ur.uid;

system>const>eq_ref>ref>range>index>ALL
连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

17.8.5、优化limit查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 900000,10 ，此时需要MySQL排序前900010 记录，仅仅返回900000 - 900010 的记录，其他记录丢弃，查询排序的代价非常大 。

1、优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
2、优化思路二
该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

十八、MySQL的JDBC操作

18.1、JDBC概述

JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API。JDBC是Java访问数据库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和类组成。

JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。

18.2、JDBC入门案例

JDBC核心类和接口

• DriverManager:用于注册驱动
• Connection: 表示与数据库创建的连接
• Statement/PrepareStatement: 操作数据库sql语句的对象
• ResultSet: 结果集或一张虚拟表

执行流程

代码编写

public class JdbcDemo1 {
    public static void main(String[] args) throws Exception {
        // 注意：使用JDBC规范，采用都是 java.sql包下的内容
        //1 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2 获得连接
        String url = "jdbc:mysql://localhost:3306/mydb16_jdbc";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        //3获得执行sql语句的对象
        Statement stmt = conn.createStatement();
        //4执行SQL语句
        ResultSet rs = stmt.executeQuery("select * from student");
 
        //5处理结果集
        while(rs.next()){
            // 获得一行数据
            Integer cid = rs.getInt("sid");
            String cname = rs.getString("sname");
            Integer age = rs.getInt("age");
            System.out.println(cid + " , " + cname);
        }
        //6释放资源
        rs.close();
        stmt.close();
        conn.close();
	} 
}

18.3、JDBC的SQL注入

SQL注入：用户输入的内容作为了SQL语句语法的一部分，改变了原有SQL真正的意义。