MySQL笔记

-- 注释 使用时候必须要加空格， 或者使用#

-- 第一步，连接连接数据库
mysql -h localhost -P 3306 -u root -p123456


-- 第二步，创建数据库 语句：Create database 数据库名称[库选项]
-- 库选项：1、 字符集设定：charset/character set 具体字符集（字符编码格式）如UTF8，GBK
-- 2、校对级设定：collate 具体校对级（数据比较的规则）校对级依赖字符集

-- 注意：1、数据库名称命名方式与其他与变量相同，不能以数字开头等，不能使用关键字和保留字，
-- 如果非要使用关键字或者保留字就就必须使用``反引号。
-- 2、中文数据库名称是可以的但是需要声明使用中文字符集如下：
set names gbk;
Create database 中国 charset utf8;

Create database users charset utf8;


-- 第三步，查看数据库；
-- 1.查看全部数据库
show databases;

-- 2.查看指定数据库
show databases like 'pattern' -- pattern是匹配模式
%：表示匹配多个字符串
_: 表示匹配单个字符串
show databases like 'user\_%'; -- 如果查看的是具有下划线的名称的数据库需要加转义字符 \;

-- 3查看数据库的创建语句
show create database users;
-- 注意：创建数据库之前mysql会对sql语句进行优化；

四、更新数据库
数据库名字不可以修改

-- 1.数据库的修改仅限库选项：字符集和校对集（校对集依赖字符集）
--Alter database 数据库名称[库选项]
--Charset/character set[=]字符集
--Collate [=]校对集
Alter database users charset gbk；


-- 2.删除数据库
Drop database 数据库名称



五、新增数据表
Create table[if not exists]表名(
字段名称 数据类型,
字段名称 数据类型 -- 最后一行不需要逗号
)[表选项]
--if not exists :如果表存在就不再创建，检查功能
表选项：控制表的表现
字符集：charset/character set具体字符集；保证表中存储时的字符集
校对集：collate 具体校对集；
存储引擎：engine 具体的存储引擎(innodb(免费的)和myisam(默认的));

创建数据表（两种方法）：
-- 1、指定创建的数据库
create table if not exists userlist.student(
name varchar(12),
gender varchar(12),
age int
)charset utf8;

-- 2、进入对应的数据库然后再创建数据表
use userlist;
create table if not exists student(
name varchar(12),
gender varchar(12),
age int
)charset utf8;

六、查看数据表
-- 1.查看所有数据表
show tables；
-- 2.查看指定的数据表
show tables like 'pattern'; -- 匹配模式

-- 3.查看数据表创建语句
show Create table student;

-- 查询到的数据表可以使用 \G 可以使查到的结果竖向显示
show tables student\G

-- 4.查看数据表结构
Desc/describe/(show columns) 数据表名称;



七、修改表

1、修改表名称
rename table 老表名 to 新表名；
rename table student to my_students；

2、修改表选项：字符集、校对集、存储引擎
Alter table 表名称 表选项
alter table my_students charset utf8;

3、修改字段
-- 包括 新增、修改、重命名、删除

1.新增字段
alter table 表名 add[column] 字段名 数据类型[列属性][位置]；
位置：first:第一个位置
after：在什么位置之后

eg：alter table my_students
add id int
first；


2.修改字段属性
alter table 表名 modify 字段名 数据类型 [属性][位置]；
eg：alter table my_students modify number char(10) after id;


3.字段重命名
alter table 表名 change 旧字段 新字段名 数据类型 [属性][位置]；
eg：alter table my_students change gender sex cahr(10) after name;

4.删除字段
alter table 表名 drop 字段名；
eg: alter table my_students drop age;


八、删除数据表

drop table 表名1,表名2...;






对表内数据的操作


一、插入数据
insert into 表名 (字段列表) values(值列表)[,(值列表)];

insert into my_students (id,number,name,sex) values (1,'itcast001','xiaoming','nan');

二、查询数据
select * from 表名; -- 查询所有数据
select id,name,sex,age from 表名 [where id=1]; -- 查询指定条件的数据
select id,name,sex,age from 表名 order by 字段名[asc/desc]; -- asc升序排列(默认)，desc降序排列

三、更新数据
update 表名 set 字段名=值 [where条件];

四、删除数据
delete from 表名 where 。。。;

五、中文乱码解决
乱码原因是：客户端数据只能是GBK, 而服务器认为是UTF8，矛盾产生；

解决办法1：
1.查看服务器对外处理的字符集为gbk
show variables like 'character_set%';
2.改变服务器默认接受字符集
set character_set_client=gbk;
3.修改服务器给客户端的字符集为gbk
set character_set_results=gbk;

解决办法2：（快捷方式设置字符集为GBK）
set names gbk; --相当于character_set_client,character_set_results,character_set_connection;
--connection连接层: 是字符集转变的中间者,如果统一了效率更高,不统一也没问题


六、乱码问题

header("content-type:text/html;charset=utf-8");
<meta http-equiv="content-type" content="text/html;charset=utf-8">




数据类型

一、mysql中数据类型分为：数值型，字符串形，时间日期类型；

1、数值型：整数形，小数形

1.整数型：Tinyint: 迷你整型,使用一个字节存储, 表示的状态最多为256种(常用)
Smallint: 小整型,使用2个字节存储,表示的状态最多为65536种
Mediumint: 中整型, 使用3个字节存储
Int: 标准整型, 使用4个字节存储(常用)
Bigint: 大整型,使用8个字节存储

eg: create table my_int(
int_1 tinyint,
int_2 smallint,
int_3 int,
int_4 bigint,
)charset utf8;

注意：sql中默认是有符号类型的，如果不需要可以使用 int unsigned(无符号类型) 来限定；


2.int(4) 括号内是显示位数

意义：当数据长度不够显示宽度时，会自动使用前导0使得数据变成显示宽度（不会改变值得大小）。从而保持格式一致；
zerofill(零填充)；当使用此属性时，会自动选择无符号类型。（负数前填充0是一种运算）
eg：alter table student add int_6 tinyint(2) zerofill;



3.小数型：浮点型，定点型
float(M,D)
M:表示总长度
D:表示小数部分的长度
注意：插入数据时，整数部分不能超出长度，但是小数部分是可以超出长度的（因为小数部分会自动四舍五入）；


定点型（decimal）：绝对的保证整数部分不会被四舍五入（不会丢失精度），小数部分有可能（理论小数部分是不会丢精度的）。


浮点型跟定点型的区别：
浮点型小数部分超可以进位自动四舍五入，定点型不能；

eg：create table my_decimal(
f1 float(10,2),
d2 decimal(10,2)
)charset utf8;

insert into my_decimal values(11111111.11,99999999.99);



2、时间日期类型
datetime：时间日期 格式：YYYY-mm-HH:ii:ss,表示1000到9999年，有0值；
date：日期，就是datetime中的date部分；
time：时间段，指定的某个区间的时间，-时间到+时间
timestamp:时间戳，从1970年开始，

3、字符串类型
在SQL中，字符串分为6类：char，varchar，text，blob，enum和set

定长字符串（char）
磁盘（二维表）在定义结构的时候，就已经确定了最终数据的存储长度。
格式：Char（L）L代表length，可以存储的长度，单位是字符，最大长度是255

变长字符串：varchar，在分配空间的时候，按照最大的空间分配，但是最终使用多少，是根据具体的数据来确定的。
Varchar（L）：L代表字符长度。理论长度是65536个，但是会多出1到2个字节来确定实际长度。
超过255的使用两个字节来表示长度，长度小于255使用1个字节来表示长度

在utf8格式下：10个汉字占用 10*3+1=31(bytes)
如何选择使用定长还是变长字符串？
定长对磁盘空间比较浪费，但是效率高，如果存储的数据长度是确定的那么就使用定长，
变长的磁盘空间比较节省，但是效率不高，如果不能确定长度，或者长度有变化选择变长。

4、枚举
定义：num, 事先将所有可能出现的结果都设计好, 实际上存储的数据必须是规定好的数据中的一个.
使用方法：enum('结果1','结果2'....)；

证明字段存储的数据是数值: 将数据取出来 + 0 就可以判断出原来的数据存的到底是字符串还是数值: 如果是字符串最终结果永远为0, 否则就是其他值.
select sex + 0,sex from student;





5、集合
集合跟枚举很类似: 实际存储的是数值,而不是字符串(集合是多选)
集合使用方式:
定义: Set(元素列表)
使用: 可以使用元素列表中的元素(多个), 使用逗号分隔





6、记录长度
（1）Mysql中规定: 任何一条记录最长不能超过65535个字节.(varchar永远达不到理论值)

（2）Varchar的实际存储长度能达到多少呢? 看字符集编码.
Utf8 下varchar的实际顶配: 21844字符 -- 21844 * 3 + 2 = 65534（剩下一个字节为空）
GBK下的varchar的实际顶配: 32766字符 -- 32766 * 2 + 2 = 65534
想用完整个65535个字节长度: 增加一个tinyint字段即可，

（3）Mysql记录中: 如果有任何一个字段允许为空,那么系统会自动从
整个记录中保留一个字节来存储NULL(若想释放NULL所占用的字节:
必须保证所有的字段都不允许为空)
因此，在增加字段时就要声明 非空 （not null）
eg：create table my_length(
m_char varchar(21844) not null,
m_tinyint tinyint not null
)charset utf8;


7、text
Mysql中text文本字符串,不占用记录长度: 额外存储.
但是text文本字符串也是属于记录的一部分:
一定需要占据记录中的部分长度: 10个字节(保存数据的地址以及长度).





列属性

一、列属性
列属性有很多: NULL/NOT NULL, default, Primary key, unique key, auto_increment,comment

1、空属性
NULL(默认的)和NOT NULL(不为空)

create table m_class(
name varchar(20);
age int(3) not null
)charset utf8;


2、列描述（comment）
没有实际含义，只是对字段的描述。用来给程序员了解的。

create table m_class(
name varchar(20) not null comment '姓名';
age int(3) not null
)charset utf8;


3、默认值（default）

create table m_default(
sex enum('男','女','保密') unsigned not null default '男' comment '性别'

)charset utf8;


主键
主键：primary key，一张表只能有一个字段使用对应的键，用来唯一约束该字段
里面的数据，不能重复，这种称之为主键。

一张表最多只能有一个主键

一、增加主键
SQL中增加主键的方式有很多种，常用的有三种

1、在创建表的时候给表增加主键，使用primary key关键字（主键本身不能为空）

create table my_prim1(
name varchar(20) not null comment '姓名',
number char(10) primary key comment '学号'
)charset utf8;

2、在创建表的时候在所有的字段之后，使用primary key（主键字段列表）来创建主键
如果是多个字段作为主键，可以是复合主键。

create table my_prim2(
number char(10) comment '学号',
course char(10) comment '学科编号',
score tinyint unsigned default 60 comment '成绩',
primary key(number,course)
)charset utf8;

3、在把表创建完成后，额外追加主键,
可以通过修改字段属性，
也可以直接追加。
alter table 表名 modify 字段名 int primary key;
alter table 表名 add primary key(字段列表);

create table my_prim3(
course char(10) not null comment '课程编号',
name varchar(10) not null comment '课程名字'
)charset utf8;


主键约束

主键对应的字段中数据是不可以重复的，一旦重复，数据操作失败；



二、更新主键 & 删除主键

没有办法更新主键：主键必须先删除，才能增加；

alter table 表名 drop primary key;


三、主键的分类
业务主键(很少使用)：使用真实项目中的业务数据作为主键字段（如学号，课程号）
逻辑主键：使用逻辑字段，没有实际业务意义。一般是 id,并且设置自动增长属性。

create table my_student(
id int primary key auto_increment comment '逻辑主键：自增长',
number char(10) unsigned not null comment '学号',
name varchar(10) not null
)charset utf8;


四、自动增长
对应的字段不给值或者是给默认值，或者是给NULL的时候，会自动触发，系统会从
字段已有的最大值+1操作。通常跟主键搭配

auto_increment
1、任何字段自增长前提本身必须是一个索引（key有值）
2、自动增长的字段必须是数字（整型）
3、一张表最多只能有一个自动增长


4、修改自增长
自增长如果是涉及到字段改变: 必须先删除自增长,后增加(一张表只能有一个自增长)

修改当前自增长已经存在的值: 修改只能比当前已有的自增长的最大值大,不能小(小不生效)
Alter table 表名 auto_increment = 值;

查看自增长对应的变量: show variables like ‘auto_increment%’;


5、删除自动增长
alter table 表名 modify 字段 类型;

唯一键
一张表往往有很多字段需要具有唯一性，数据不能重复，但是一张表中只能有一个主键，
唯一键（unique key）就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质与主键差不多：
唯一键默认是可以为空的，
而且可以多个字段为空（空字段不参与唯一性比较）

一、增加唯一键

1、在创建表的时候,字段之后直接跟unique/ unique key
create table my_unique1(
number char(10) uniuqe comment '学号',
name varchar(10) not null
)charset utf8;


2、在所有的字段之后增加unique key(字段列表); -- 复合唯一键
create table my_unique2(
number char(10) not null comment'学号',
name varchar(10) not null ,
-- 增加唯一键
unique key(number)
)charset utf8;

注意：如果给增加 唯一键 时，同时字段又是不为空的（not null），
这个字段显示为主键，
原因是：不为空的唯一键（与主键的性质一样）
并且该表没有主键，系统默认此字段为主键


3、 在创建表之后增加唯一键
create table my_unique3(
id int unsigned primary key auto_increment,
number char(10) not null,
name varchar(10) not null
)charset utf8;
-- 增加唯一键
alter table my_unique add unique key(number);

二、唯一键的约束
唯一键与主键本质相同，唯一的区别是默认允许为空，而且是多个为空。

三、更新唯一键 & 删除唯一键

删除唯一键
-- alter table 表名 drop unique key; -- 错误的，唯一键有多个；
alter table 表名 drop index 索引名称，-- 唯一键默认的使用字段名作为索引名称

索引

几乎所有的索引都是建立在字段之上。

索引：系统根据某种算法，将已有的数据（未来可能新增的数据），单独建立一个文件
文件能够实现快速匹配数据，并且能够快速的找到对应表中的记录

索引的意义：
1.提升查询数据的效率
2.约束数据的有效性（唯一性等）

增加索引的前提条件：索引本身会产生索引文件（有时候可能比数据还大），非常耗费磁盘空间

某个字段需要作为查询条件经常使用，那么可以使用索引
某个字段需要进行数据的有效性约束，也可以使用索引（主键，唯一键）


MySQL中提供可多种索引：
1.主键索引:primary key
2.唯一键索引：unique key
3.全文索引：fulltext index
4.普通索引：index

全文索引: 针对文章内部的关键字进行索引
全文索引最大的问题: 在于如何确定关键字

关系

将实体与实体的关系，反应到最终数据库表的设计上来：将关系分成三种：
一对一，一对多（多对一），多对多。

范式
范式：normal format，是为了解决数据存储和优化问题：保存数据存储后，凡是能通过关系找到的数据
坚决不再重复存储：这样做的目的是为了减少数据冗余

范式：是一种分层结构的规范，分为六层，每一层都比前一层更加严格。
六层范式：1NF，2NF，3NF...6NF

MySQL属于关系型数据库：存在空间浪费（数据冗余），因此可以使用范式
来解决，但是为了提高数据库查询效率，一般要求数据库设计时满足三层范式即可，
过高的范式会使得数据查询效率大大降低。

范式在数据库的设计当中是有指导意义: 但是不是强制规范.


一、1NF
第一范式：再设计数据表的时候，如果表中的数据取出来使用之前还需要额外的处理（拆分）
那么这个数据表就不满足第一范式，第一范式要求数据具有原子性：不可再分


二、2NF
第二范式：在数据表的设计的过程中，如果有复合主键（多字段主键），且表中有字段并不是
完全由整个主键来确定，而是依赖主键的某个字段（主键的部分）：存在字段依赖部分主键的问题
称之为部分依赖。如果数据表存在部分依赖那么就不满足第二范式
因此，第二范式的条件是：不存在部分依赖

在设计数据表的时候不要使用复合主键，使用逻辑主键和复合唯一键


三、3NF
第三范式：应该一张表中的所有字段都应该直接依赖主键(逻辑主键: 代表的是业务主键),
如果表设计中存在一个字段, 并不直接依赖主键,而是通过某个非主键字段依赖,
最终实现依赖主键: 把这种不是直接依赖主键,而是依赖非主键字段的依赖关系称之为传递依赖.
第三范式就是要解决传递依赖的问题.


逆规范化

有时候, 在设计表的时候,如果一张表中有几个字段是需要从另外的表中去获取信息. 理论上讲,
的确可以获取到想要的数据, 但是就是效率低一点. 会刻意的在某些表中,不去保存另外表的主键(逻辑主键),
而是直接保存想要的数据信息: 这样一来,在查询数据的时候, 一张表可以直接提供数据, 而不需要多表查询(效率低), 但是会导致数据冗余增加.

数据高级操作

数据操作: 增删改查


一、新增数据
1、基本语法：insert into 表名 [(字段列表)] values(值列表);

insert into student (id,name,gender,age,my_float) values('','小明','男',20,'');

在插入数据的时候，假设主键对应的值已经存在：插入一定会失败！
2、主键冲突解决

1.更新操作(on duplicate key update)
insert into 表名[(字段列表，包含主键)] values(值列表)
on duplicate key update
字段=新值;

2.替换操作（replace into）
replace into 表名[(字段列表:包含主键)] values(值列表);
eg:replace into student (id,name,gender,age,height) values(2,'张赫','男',23,188);


二、蠕虫复制
蠕虫复制：从已有数据中去获取数据，然后将数据进行新增操作：数据成倍的增加，

表创建高级操作：从已有表创建新表（复制表结构）
create table 表名 like 数据库.表名;


蠕虫复制：先查出数据，然后将查出的数据新增一遍
insert into 表名[(字段名称)] select (字段列表)/ * from 数据表名;
注意：蠕虫复制往本表复制的时候不能复制主键，因为主键是唯一的

蠕虫复制的意义：
1.从已有表中拷贝数据到新表中
2.可以迅速的让表中的的数据膨胀到一定数量级：用于测试表的压力以及效率


三、更新数据
基本语法
update 表名 set 字段 = 值[where条件];

高级新增语法
update 表名 set 字段=值[where条件][limit更新数量];


四、删除数据
与更新类似：可以通过limit来限制数量
delete from 表名[where条件][limit数量];

删除：如果表中存在主键自增长，那么当删除之后，自增长不会还原。
思路：删除是不会改变表结构，只能删除表后重建表

重置表：
truncate 表名； -- 先删除改变，后新增改变。





查询数据

一、基本语法：
select 字段名称/ * from 表名[where条件];


二、完整语法
select[select选项] 字段列表[字段别名]/ * from 数据源[where条件子句]
[group by子句][having子句][order by子句][limit子句];

1、select选项
select选项：对select查询出来的结果处理方式
ALL：默认的，保留所有的结果
distinct:去重，查出来的结果把所有字段都相同的数据去除；


2、字段别名

字段别名：当数据进行查询出来的时候，有时候字段名称并不能满足需求（多表查询的时候，会有同名字段），需要对重名字段进行重命名：别名
语法：
字段名 [as] 别名;


3、数据源
数据源:数据的来源,关系型数据库的来源都是数据表：本质上只要保证数据类似二维表，最终都可以作为数据源。

数据源分为多种：单表数据源，多表数据源，查询语句

（1）单表数据源
select * from 表名;

（2）多表数据源
select * from 表名1,表名2...;

从一张表中取出一条记录,去另外一张表中匹配所有记录,而且全部保留:(记录数和字段数),将这种结果成为: 笛卡尔积(交叉连接): 笛卡尔积没什么卵用, 所以应该尽量避免.

（3）子查询：数据的来源是一条查询语句（查询语句的结果是二维表）
select * from(select语句) as 表名;


4、where子句
where子句：用于判断数据，筛选数据；
where子句返回结果为0(false)或1(ture);

（1）判断条件：
比较运算符：>, <, >=, <= ,!= ,<>, =, like, between and, in/not in
逻辑运算符：&&（and），||（or），!（not）；

（2）Where原理: where是唯一一个直接从磁盘获取数据的时候就开始判断的条件:
从磁盘取出一条记录, 开始进行where判断:
判断的结果如果成立保存到内存;如果失败直接放弃.

5、group by子句
group by:分组的意思，根据某个字段进行分组（相同的放一组，不同的分到不同的组中）

（1）基本语法 ：group by 字段名;
分组的意思是为了统计数据

（2）SQL中提供了一系列的统计函数
count():统计分组后的记录数，每一组有多少天记录
max():统计每组中的最大值
min():统计每组中的最小值
avg():统计平均值
sum():统计和
group_concat(字段)：可以将分组结果中的字符串进行连接

（3）Count函数: 里面可以使用两种参数: *代表统计记录,字段名代表统计对应的字段(NULL不统计)

eg:select gender,count(*),max(height),min(height),avg(age),sum(age) from m_2 group by gender;

（4）group 可以将分组的字段进行排序
group by 字段名[asc(默认升序)|desc(降序排列)]

（5）回溯统计: with rollup: 任何一个分组后都会有一个小组, 最后都需要向上级分组进行汇报统计: 根据当前分组的字段.
这就是回溯统计: 回溯统计的时候会将分组字段置空.

6、having子句
（1）having子句：与where子句都是用来进行条件判断的

（2）区别：where是针对磁盘数据进行判断：
进入到内存后会进行分组操作，
分组结果判断就需要having来处理
having能做where能做的所有事情，但是where不能做having能做的很多事情；

（3）1.分组统计的结果或者说统计函数都只有having能够使用.
select gender,count(*) from student group by gender having count(*)>2;

（4）2.Having能够使用字段别名: where不能: where是从磁盘取数据,而名字只可能是字段名: 别名是在字段进入到内存后才会产生.
select gender,count(*) as total from student group by gender having total>2;

7、order by 子句
(1)使用基本语法
Order by 字段名 [asc|desc]; -- asc是升序(默认的),desc是降序






8、limit子句
limit子句是一种限制结果的语句：限制数量


limit使用方法有两种：
（1）只限制查询长度（数据量）
limit 数据量（数字）

（2）限制起始位置，限制查询长度
limit 起始位置，长度；

方式（2）常用于实现查询数据分页：为用户节省时间，提高服务器响应效率，减少资源浪费


对于用户来说：可以点击按钮：1,2,3,4
对于服务器来说：根据用户选择的页码去数据库获取不同的数据 limit offset,length

offset（起始位置）：offset=（页码-1）*每一页的显示量
length:每页显示额数据量，基本不变





连接查询

连接查询: 将多张表(可以大于2张)进行记录的连接(按照某个指定的条件进行数据拼接):
最终结果是: 记录数有可能变化, 字段数一定会增加(至少两张表的合并)

连接查询的意义: 在用户查看数据的时候,需要显示的数据来自多张表.

连接查询: join, 使用方式: 左表 join 右表
左表: 在join关键字左边的表
右表: 在join关键字右边的表


一、连接查询的分类
内连接
外连接
自然连接
交叉连接

1、交叉连接：cross join，从一张表中循环取出每一条记录, 每条记录都去另外一张表进行匹配: 匹配一定保留(没有条件匹配),
而连接本身字段就会增加(保留),最终形成的结果叫做: 笛卡尔积. （没有实际意义 不使用）

基本语法：
左表 cross join 右表;===========from 左表，右表
不建议使用交叉连接


2、内连接：[inner] join, 从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留.

（1）基本语法
左表[inner]join 右表 on 左表.字段 = 右表.字段;
on 表示连接条件，件字段就是代表相同的业务含义(如my_student.c_id和my_class.id)

（2）字段别名以及表别名

（3）内连接可以没有连接条件

3、外连接
外连接: outer join, 以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL.

外连接分类：
（1）左外连接（left join）：以左表为主
（2）右外连接（right join）：以右表为主

基本语法：左表 left/right join 右表 on 左表.字段=右表.字段;

虽然左连接和右连接有主表差异, 但是显示的结果:
左表的数据在左边,右表数据在右边.
左连接和右连接可以互转.


4、自然连接
自然连接：natural join，自然连接，就是自动匹配连接条件：系统以字段名字作为匹配模式
（同名字段就作为条件，多个同名字段都作为条件）

分类：自然内连接和自然外连接.


（1）自然内连接：左表 natural join 右表；
注意：自然连接使用同名字段作为连接条件，连接之后会合并同名字段

（2）自然外连接：左表natural left/right join 右表；

（3）其实, 内连接和外连接都可以模拟自然连接: 使用同名字段,合并字段
左表 left/right/inner join 右表 using(字段名); -- 使用同名字段作为连接条件: 自动合并条件


5、多表连接
多表连接: A表 inner join B表 on 条件 left join C表 on条件 ...
执行顺序: A表内连接B表,得到一个二维表, 左连接C表形成二维表...






外键
一、外键：foreign key，外面的键（键不在自己表中）：如果有一个表中有一个字段（非主键）指向另外一张表，那么该字段称之为外键；


二、增加主键

外键可以在创建表时候或者创建表之后增加（但是要考虑数据问题）。
一张表可以有多个外键

1、创建表的时候增加外键：在所有的字段之后，使用
foreign key（外键字段）references 外部表（主键字段）

eg：-- 创建外键
create table my_foreign1(
id int primary key auto_increment comment 'ID',
name varchar(10) not null comment '姓名',
c_id int comment '班级ID',
-- 增加外键
foreign key(c_id) references my_class(id)
)charset utf8;

-- 创建class班级表
create table my_class(
id int primary key auto_increment,
class varchar(10) comment'班级'
)charset utf8;

-- 插入数据
insert into my_foreign1 values(null,'张三',null);
insert into my_foreign1 values(null,'李四',null);
insert into my_foreign1 values(null,'王五',null);

-- 插入数据
insert into my_class values(null,'itcast001');
insert into my_class values(null,'itcast002');
insert into my_class values(null,'itcast003');

2、在创建表完成后添加外键:修改表结构
alter table 表名 add[constraint 外键名称] foreign key(外键字段)references 父表(主键字段);

eg:alter table my_foreign1 add constraint foreign_cid foreign key(c_id) references my_class(id);

三、修改外键&删除外键
外键不可以直接修改：只能先删除后新增

1、删除外键语法
alter table 表名 drop foreign key 外键名；

2、注意：一张表中可以有多个外键，但是外键名称是唯一的
外键不能通过查询表结构体现，可以通过查询表创建语句获得；

四、外键作用

外键默认的作用有两点：一个是对父表，一个是对子表（外键字段所在的表）

1、对子表的约束：
子表数据进行写（增和改）操作时，如果对应的外键字段在父表中找不到对应的匹配时，操作会失败；

2、对父表约束：父表数据进行写操作（删和改），如果对应的主键在子表中已经被引用，那么不允许被操作；


五、外键条件

1、外键要存在：首先必须保证存储引擎是innodb（默认的存储引擎）：如果不是innodb存储引擎，外键可以创建成功，但是没有约束效果
2、外键字段的字段类型（列类型）必须与父表的主键类型完全一致
3、一张表中的外键名称不能重复
4、增加外键字段（数据已经存在），必须保证数据要与父表对应

六、外键约束
所谓的外键约束：就是指外键的作用
之前所用的是外键的默认作用，其实可以通过对外键的需求，进行定制操作；

1、外键的约束有三种模式：（都是针对父表的约束）
district：严格模式（默认的）：不能删除或者更新一个已经被子表数据引用的记录；
cascade：级联模式：父表的操作，对子表关联的数据也跟着更新；
set null：置空模式：对父表操作之后，子表对应的数据（外键字段）被置空；


2、通常采用一个比较合理的做法（约束模式）：删除置空，更新级联。
语法：
foreign key(外键字段名) references 父表(主键字段) on delete set null on update cascade;

3、删除置空的前提条件: 外键字段允许为空(如果不满足条件,外键无法创建)

4、外键虽然很强大, 能够进行各种约束: 但是对于PHP来讲, 外键的约束降低了PHP对数据的可控性: 通常在实际开发中, 很少使用外键来处理.

联合查询

一、联合查询：将多次查询（多条select语句），在记录上进行拼接（字段不会增加）

二、基本语法
多条select语句构成：每一条语句获取的字段必须严格一致（但是字段类型无关）

1、语句格式：
select 语句1
union[union选项]
select 语句2...

union选项：与select选项一样有两个
all：保留所有的结果
distinct：去重（整个重复）默认的

eg：-- 联合查询
select * from my_class
union
select * from my_class;

2、什么时候使用联合查询
1.查询同一张表，但是需求不同：如查询学生信息，男生身高升序，女生身高降序；
2.多表查询：多张表的结构完全一样的，保存的数据（结构）也是一样的。

3、order by使用
联合查询中：order by不能直接使用，需要对查询语句使用括号才行，

eg: -- 需求：男生升序，女生降序
(select * from student where gender='男' order by age asc)
union
(select * from student where gender='女' order by age desc);

查询出来的结果发现order by 并没有生效

4、limit的使用
要想order by 生效，就必须使用limit限制最大数量才行；

(select * from student where gender='男' order by age asc limit 9999999)
union
(select * from student where gender='女' order by age desc limit 9999999);

子查询

一、子查询：sub query，查询实在某个查询结果之上进行的（一条select语句内部包含了另外一条select语句）

二、子查询的分类

1、按照位置分类：
（1）from子查询：子查询跟在from之后
（2）where子查询：子查询出现在where条件中
（3）exists子查询：子查询出现在exists里面

2、按照结果分类：
（1）标量子查询：子查询得到的结果是一行一列
（2）列子查询：子查询得到的结果是一列多行
（3）行子查询：子查询得到的结果是多列一行（多列多行）
上面三种位置都是出现在where之后
（4）表子查询：子查询得到的结果是多列多行（出现位置是在from之后）



1、标量子查询实现
需求：知道班级名称为itcast002，想获取该班所有学生

（1）确定数据源：获取所有的学生
select * from my_foreign1 where c_id = ?;
（2）获取班级ID：可以通过班级名字确定
select id from my_class where class='itcast002';

-- 实现标量子查询
select * from my_foreign1 where c_id = (select id from my_class where class='itcast002');

2、列子查询
需求：查询所有在读班级学生（班级表中存在的班级）

（1）确定数据源：学生
select * from my_foreign1 where c_id in(?);
（2）确定有效班级的ID：所有班级id
select id from my_class;

eg：-- 列子查询：学生
select * from my_foreign1 where c_id in(select id from my_class);

列子查询返回的结果会比较: 一列多行, 需要使用in作为条件匹配: 其实在mysql中有还有几个类似的条件:
all, some, any

=Any ==== in; -- 其中一个即可
Any ====== some; -- any跟some是一样
=all ==== 为全部


3、行子查询：返回结果可以是多行多列（一行多列）
需求：要求查询整个学生中，年龄最大且身高最高的学生

（1）确定数据源
select * from my_foreign1 where age=？ and height=？;
（2）确定最大年龄和最高身高；
select max(age),max(height) from my_foreign1;

（3）行子查询：需要构造行元素：行元素有多个字段构成
eg：-- 行子查询
select * from my_student where (age,height) = (select max(age),max(height) from my_student);


4、表子查询
表子查询：子查询返回结果是多行多列的二维表：子查询返回的结果是当做二维表来使用

需求：找出每一个班最高的学生。

（1）确定数据源：现将学生的身高进行降序排列
select * from my_student order by height desc;
（2）从每个班选出第一个学生
select * from my_student group by c_id;

eg: -- 表子查询
select * from (select * from my_student order by height desc) as student group by c_id;


5、exists子查询

exists：是否存在的意思，exists子查询就是用来判断某些条件是否满足（跨表）
exists是接在where之后：exists返回结果是0和1;


需求：查询所有学生：前提是班级存在

（1）确定数据源
select * from student where ?;

（2）确定查询条件是否满足
exists(select * from my_class); -- 是否成立

eg：-- exists子查询
select * from student where exists(select * from my_class);







视图

一、视图：view，是一种有结构（有行有列）但是没有结果（结构中不真实存放数据）
的虚拟表，虚拟表的结构来源不是自己定义的，而是从对应 基表 中产生的（视图的数据来源）；

二、创建视图

基本语法
create view 视图名称 as select语句；-- select语句可以使普通的查询；可以使连接查询，可以使联合查询，可以使子查询

（1）创建单表视图：基表只有一个
（2）创建多表视图：基表来源至少两个
注意：如果视图是多张表的情况下字段不能重复

eg：-- 创建单表视图
create view my_v1 as select * from student;

-- 创建多表视图
create view my_v2 as
select s.*, c.class from student as s
left join my_class as c
on s.c_id = c.id;

三、查看视图

（1）查看视图的结构
视图是一张虚拟表：表，表的所有查看方式都适用于视图：
show tables [like]
desc 视图名称
show create view 视图名称；

（2）视图一旦创建：系统会在对应的数据库文件下创建一个对应的结构文件：frm文件；



四、使用视图

使用视图主要是为了查询：将视图当做表查询即可。

视图的执行：其实本质就是执行封装好的select语句。


五、修改视图
视图本身不可以修改，但是视图的来源可以修改。

修改视图：修改视图来源的语句（SELECT语句）
alter view 视图名称 as 新的select语句;

eg：-- 修改视图名称
alter view my_v1 as select * from my_class;

六、删除视图

drop view 视图名称



七、视图的意义

1、视图可以节省SQL语句：将一些复杂的查询语句使用视图进行保存，
以后直接对视图进行操作即可

2、数据安全：视图操作是主要针对查询的，如果对视图结构进行处理（删除）

3、视图往往是在大项目中使用，而且是多系统使用：可以对外提供有用数据，
但是隐藏关键（无用）数据：数据安全

4、视图可以对外提供友好型：不同的视图提供不同的数据，对外好像专门设计

5、视图可以更好（容易）的进行权限控制



八、视图数据操作
视图的确可以进行数据读写操作：但是有很多限制
将数据直接在视图上进行操作。

1、新增数据
数据新增就是直接对视图进行数据新增

（1）多表视图不能新增数据

（2）可以向单表视图插入数据：但是视图中包含的字段必须有基表中所有不能为空（或者没有默认值的字段）的字段

（3）视图可以向基表中插入（更新）数据
eg：-- 向单表视图中插入数据
insert into my_v1 values(6,'明明',22,188,2);


2、删除数据

（1）多表视图不能删除数据

（2）单表视图可以删除数据
eg：-- 单表视图删除数据
delete from my_v1 where id=6;


3、更新数据

（1）无论是多表视图还是单表视图都可以是更新数据
eg：-- 单表视图更新数据
update my_v1 set name='hh' where id=6;
-- 多表视图更新数据
update my_v2 set c_id=4 where id=1;

（2）更新限制
with chech option ，如果对视图进行新增时，限定了某个字段有限制；
那么对视图进行数据更新操作时，系统会进行验证：要保证更新之后，数据依然能够被实体查询出来，
否则不让修改；

eg：-- 限制age字段数据必须大于20，age>20
create view my_v3 as
select * from student where age>20 with check option;
-- age字段的数据将被限制为大于20；


4、视图算法
（1）视图算法：系统对视图以及外部查询视图的select语句的一种解析方式


（2）视图算法分为三种

（1）undefined：未定义的（默认的），这不是一种实际使用算法，是一种推卸责任的算法: 告诉系统,视图没有定义算法, 系统自己看着办
（2）temptable：临时表算法：系统应该先执行视图的select语句，后执行外部查询语句
（3）merge：合并算法：系统应该现将视图对应的select语句与外部查询语句的select语句进行合并，然后执行（效率高），常态；


（3）算法指定：在创建视图的时候
create algorithm = 制定算法 view 视图名字 as select 语句；

eg：需求：查出每个班身高最高的学生
-- 创建表时指定视图算法
create algorithm = temptable view my_v5 as select * from student order by height desc;
-- 分组查询结果
select * from my_v5 group by c_id order by height;

（4）视图算法选择：如果视图的select语句中会包含一个条件查询子句（五子句），而且
很可能顺序比外部的查询语句要靠后，一定要使用算法temptable，其他可以不指定；




九、数据备份与还原
备份：将当前已有的数据或者记录保留
还原：将已经保留的数据恢复到对应的表中

为什么要进行数据备份？
1、防止数据丢失：被盗，误操作
2、保护数据记录

数据备份还原的方式有很多种：数据表备份，单表数据备份，SQL备份，增量备份

1、数据表备份
不需要通过SQL来备份：直接进入到数据库文件夹复制对应的表结构以及数据文件，以后还原的时候直接将备份的内容放进去即可；
数据表备份有前提条件：根据不同的存储引擎有不同的区别。

存储引擎：mysql进行数据存储的方式：主要是两种：innodb，myisam(默认的)

两种存储引擎的区别
（1）innodb：只有表结构，数据全部存储到ibdata1中（不能通过直接复制文件来备份）
（2）myisam：表，数据和索引全部单独分开存储
一般会有三个文件：.frm(表结构文件)，.MYD(数据文件)，.MYI(索引文件)
在数据备份时可通过直接复制三个文件到对应数据库即可使用


2、单表数据备份
每次只能备份一张表；只能备份数据表（表结构不能备份）

通常使用：将表中的数据进行导出到文件

（1）备份：从表选出一部分数据保存到外部的文件中（outfile）
语法结构：select */字段列表 into outfile 文件备份路径 from 数据源；-- 前提是外部文件不存在

eg：-- 单表备份
select * into outfile 'G:/wamp64/tmp/student.txt' from student;

（2）高级备份：自己制定字段和行处理方式
select */字段列表 into outfile 文件备份路径 fields 字段处理 lines 行处理 from 数据源;

fiels:字段处理
enclosed by：字段使用什么内容包裹，默认是空字符串
terminated by：字段以什么结束，默认是“\t”，tab键
escaped by：特殊符号用什么方式处理，默认是‘\\’,使用反斜杠转义
lines：行处理
starting by：每行以什么开始，默认是' ',空字符串
terminated by：每行以什么结束，默认是“\r\n”,换行符

eg: -- 单表备份-高级备份
select * into outfile 'G:/wamp64/tmp/student1.txt'
-- fields字段处理
fields
enclosed by '"' -- 字段使用双引号包裹
terminated by ' | ' -- 使用竖线分隔
-- lines行处理
lines
starting by 'START:'
from student;


（3）数据还原：将一个在外部保存的数据重新恢复到表中（如果表结构不存在则不行）
load data infile 文件路径 into table 表名[(字段名称)] fields 字段处理 lines 行处理;-- 怎么备份怎么还原
注意：fields和lines要与备份的一致

eg：-- 数据还原
load data infile 'G:/wamp64/tmp/student1.txt'
into table student
fields
enclosed by '"' -- 字段使用双引号包裹
terminated by ' | ' -- 使用竖线分隔
-- lines行处理
lines
starting by 'START:';


3、SQL备份
备份的是SQL语句：系统会对表结构以及数据进行处理,变成对应的SQL语句,
然后进行备份: 还原的时候只要执行SQL指令即可.(主要就是针对表结构)

（1）备份：MySQL没有提供备份的指令：需要利用MySQL提供的软件：MySQLdump.exe
mysqldump.exe也是一种客户点操作软件，需要进行连接认证

语法(不需要使用分号结束)：
mysqldump/mysqldump.exe -h(服务器) -P(端口) -u(用户名) -p(密码)
数据库名称[表名1[表名2]......] > 备份文件目录（建议使用.sql）

1.单表备份
eg：-- sql备份
mysqldump -h localhost -u root -p123456 userlist student > G:/wamp64/tmp/student.sql

2.整库备份
mysqldump -h localhost -u root -p123456 userlist > G:/wamp64/tmp/userlist_database.sql


（2）SQL还原数据：两种方式还原

方案一：使用mysql.exe客户端还原
mysql.exe/mysql -hPup 数据库名称 < 备份文件目录


方案二：使用SQL指令还原（必须进入对应的数据库才行）
source 备份文件所在路径;


（3）SQL备份的优缺点
1、优点：可以备份结构
2、缺点：会浪费空间（额外的增加SQL指令）


4、增量备份
增量备份是针对mysql日志文件进行的备份

增量备份：指定时间段开始进行备份，备份数据不会重复，而且所有操作都会备份（大项目使用增量备份）





事务安全

一、事务（transaction）：一系列要发生的连续操作
事务安全：一种保护连续操作同时满足的一种机制

事务安全的意义：保证数据操作的完整性


二、事务操作
事务操作分为两种：自动事务（默认的），手动事务
手动事务：操作流程

1、开启事务：告诉系统以下所有的操作(写)不要直接写入到数据表, 先存放到事务日志
Start transaction;或者 BEGIN;

2、进行事务操作：一系列操作
a) 李四账户减少
b）张三账户增加

3、关闭事务：选择性的将日志文件中操作结果保存到数据表（同步）或者说直接清空事务日志
a）提交事务：同步数据表（操作成功）：commit;
b）回滚事务：直接清空日志表（操作失败）：rollback;


eg：-- 创建账户表
create table my_money(
id int primary key auto_increment,
name varchar(10) comment '用户名',
money decimal comment '薪资'
)engine innodb charset utf8;

-- 插入数据
insert into my_money values(null,'张三',1000),(null,'李四',2000);

-- 开启事务
start transaction;
-- 执行事务操作，张三账户减少，李四账户增加
update my_money set money = money - 100 where id=1;
update my_money set money = money + 100 where id=2;

-- 结束事务：提交事务结果cmmit，或者回滚rollback
-- commit
roolback;


三、事务原理
实务操作原理：事务开启之后，所有操作都会临时保存到事务日志，事务日志只有在得到commit命令时
才会同步到数据表，其他任何情况都会清空（rollback，断电，断开连接）



四、回滚点：在某个成功的操作之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，
可以在当前位置设置一个点：可以供后续失败操作返回该位置，而不是返回所有操作，这个点称之为回滚点。


设置回滚点语法：
savepoint 回滚点名称;

回到回滚点
rollback to 回滚点名称;



五、自动事务处理
在mysql中：默认都是自动事务处理，用户操作会立即同步到数据表。

（1）自动事务：系统通过autocommit 变量控制
show variables like 'autocommit';

（2）关闭自动提交：
set autocommit=off/0;

（3）关闭自动提交后，需要手动提交处理：commit提交，rollback回滚；

六、事务特性

1、事务有四大特性：ACID
（1）A: Atomic原子性, 事务的整个操作是一个整体, 不可分割,要么全部成功,要么全部失败;
（2）C: Consistency, 一致性, 事务操作的前后, 数据表中的数据没有变化
（3）I: Isolation, 隔离性, 事务操作是相互隔离不受影响的.
（4）D: Durability, 持久性, 数据一旦提交, 不可改变,永久的改变数据表数据


2、锁机制：innodb 默认是行锁，但是如果在十五操作过程中，没有使用到索引，那么
系统自动全表检索数据，自动升级为表锁

（1）行锁：只有当前行被锁住，别的用户不能进行操作
（2）表锁：整张表被锁住，别的用户不能进行操作



七、变量
变量分为两种：系统变量和自定义变量

1、系统变量
（1）系统定义好的变量: 大部分的时候用户根本不需要使用系统变量: 系统变量是用来控制服务器的表现的: 如autocommit, auto_increment_increment等
（2）查看系统变量：show variables;
（3）查看具体的变量值：任何一个有数据返回的内容都是由select查看Select @@变量名;

（4）修改系统变量
修改系统变量分为两种方式: 会话级别和全局级别

会话级别: 临时修改, 当前客户端当次连接有效
Set 变量名 = 值;/Set @@变量名 = 值;

全局级别: 一次修改,永久生效(对所有客户端都生效)
Set global 变量名 = 值;


2、自定义变量
（1）系统为了区分系统变量, 规定用户自定义变量必须使用一个@符号
Set @变量名 = 值;

（2）在mysql中, “=”会默认的当做比较符号处理(很多地方), mysql为了区分比较和赋值的概念: 重新定义了一个新的的赋值符号: :=
eg： set @name := '张三';

（3）MySQL允许从数据表中获取数据，然后赋值给变量：两种方式

方案一：边赋值边查看结果
select @变量名 := 字段名 from 数据源; -- 从字段中取值赋给变量名，如果是用等号会变成比较
eg -- 边查询边赋值
select @name := name from my_money;

方案二：只赋值不看结果：要求严格：数据记录最多只允许获取一条：mysql不支持数组
select 字段列表 from 表名 into 变量列表;
eg: select name,money from my_money where id=2 into @name,@money;

（4）所有自定义的变量都是会话级别: 当前客户端当次连接有效
所有自定义变量不区分数据库(用户级别)





触发器
需求: 有两张表, 一张订单表,一张商品表, 每生成一个订单,意味着商品的库存要减少.

1、触发器：trigger，事先为某张表绑定好一段代码，当表中的某些内容发生改变的时候（增删改），系统会自动触发代码执行。

2、触发器：事件类型，触发时间，触发对象
（1）触发时间：前后：before，after
（2）事件类型：增删改，insert，delete，update
（3）触发对象：表中的每一条记录（行）

注意：一张表中只能拥有一种触发时间的一种类型的触发器：最多一张表能有6个触发器

一、创建触发器
1、在MySQL高级结构中：没有大括号，都是用对应的字符符号代替

2、触发器基本语法
-- 临时修改语句结束符
delimiter 自定义符号：后续代码中只有碰到自定义符号才算结束
create trigger 触发器名称 触发时间 事件类型 on 表名 for each row
begin -- 代表左大括号：开始
--- 里面是触发器的内容：每行必须使用结束符：分号
end -- 代表右大括号：结束
-- 语句结束符
自定义符号

-- 将临时修改修正过来(注意加空格)
delimiter ;


eg： -- 创建订单表
create table my_order(
id int primary key auto_increment,
g_id int not null comment '商品ID',
g_number int comment '商品数量'
)engine innodb charset utf8;

-- 创建商品表
create table my_goods(
id int primary key auto_increment,
name varchar(20) not null comment '商品名称',
price decimal(10,2) default 1,
inv int comment '库存数量'
)engine innodb charset utf8;
-- 插入商品数据
insert into my_goods values(null,'huawei',5000,100),(null,'honor',3000,200);


delimiter $$
-- 创建触发器 需求：当用户产生订单时，商品表中的总数会减少
create trigger after_order after insert on my_order for each row
begin
-- 触发器内容
update my_goods set inv=inv-1 where id=2;
end
-- 语句结束
$$
delimiter ;

二、查看触发器
1、查看所有触发器或者模糊匹配
Show triggers [like ‘pattern’];

2、可以查看触发器创建语句
Show create trigger 触发器名字;

3、所有的触发器都会保存一张表中: Information_schema.triggers

三、使用触发器
触发器：不需要手动调用, 而是当某种情况发生时会自动触发.(订单里面插入记录之后)


四、修改触发器&删除触发器
触发器不能修改，只能先删除后修改；
语法：
drop trigger 触发器名称;

五、触发器记录

1、触发器记录：不管触发器是否触发了,只要当某种操作准备执行,
系统就会将当前要操作的记录的当前状态和即将执行之后新的状态给分别保留下来,
供触发器使用: 其中, 要操作的当前状态保存到old中, 操作之后的可能形态保存给new.

（1）old 代表旧记录
（2）new代表新纪录
删除的时候是没有new的; 插入的时候是没有old

2、Old和new都是代表记录本身: 任何一条记录除了有数据, 还有字段名字.
使用方式: old.字段名 / new.字段名(new代表的是假设发生之后的结果)

eg：-- 使用触发器记录
delimiter $$
create trigger order_after after insert on my_order for each row
begin
update my_goods set inv=inv-new.g_number where id=new.g_id;
end
$$
delimiter ;

3、如果触发器内部只有一条要执行的SQL指令, 可以省略大括号(begin和end)
Create trigger 触发器名字 触发时间 事件类型 on 表名 for each row
SQL指令;

4、触发器: 可以很好的协调表内部的数据处理顺序和关系. 但是从PHP角度出发, 触发器会增加数据库维护的难度, 所以较少使用触发器.


六、代码执行结构
代码执行结构分为三种：顺序结构，分支结构，循环结构。

1、分支结构：实现准备多个代码块, 按照条件选择性执行某段代码.

2、MySQL中if分支的语法
if 条件判断 then
-- 满足条件执行的代码；
else
-- 不满足条件执行的代码；
end if;

触发器结合if分支：判断库存是否满足，不够就不能生成订单
eg：-- 触发器结合if分支
delimiter $$
create trigger before_order before insert on my_order for each row
begin
-- if判断商品库存是否足够
-- 获取商品库存并赋值给变量 @inv
select inv from my_goods where id=new.g_id into @inv;
-- 比较库存跟订单需求
if @inv < new.g_number
then
-- 库存不够，触发器没有提供阻止时间发生的能力（暴力报错）
insert into XXX values(XXX);

end if;

end
$$
delimiter ;


3、循环结构：某段代码在指定条件下重复执行

（1）while 循环语法结构（没有for循环）
while 条件判断 do
-- 满足条件要执行的代码
-- 变更循环条件
end while;

（2）循环控制：在循环内部进行循环判断和控制
MySQL中没有对应的continue和break。但是有替代品。
iterate：迭代，类似continue，后面的代码不执行，循环重新来过
leave：离开，类似break，整个循环结束

使用方法：iterate/leave 循环名字；

-- 定义循环名字
循环名字：while 条件 do
-- 循环体
-- 循环控制
leave/iterate 循环名字;
end while;


七、函数
1、函数：将一段代码块封装到一个结构中，在需要执行代码快的时候，调用结构执行即可（代码复用）

2、函数分为两类：系统函数和自定义函数

3、MySQL中，字符串是基本的操作单位（最常见的是字节）
（1）substring：字符串截取（字符为单位）
eg：set @name := '张三';
select substring(@name,1,1); -- 从1开始截取1个字符串

（2）char_length：字符长度
（3）length：字节长度
eg：select char_length(@name),length(@name);

（4）instr：判断字符串是否在某个具体的字符串存在，存在返回位置
eg：select instr(@name,'三'),instr(@name,'王');

（5）lpad：左填充，将字符串按照某个指定的填充方式，填充到指定长度（字符）
eg：select lpad(@name,20,'李四');

（6）insert：替换，找到目标位置，指定长度的字符串，替换成目标字符串
eg：set @str := 'aabbbb';
select insert(@str,3,3,'XXX');

（7）strcmp：compare，字符串比较
eg： set @h1 := 'hello';
set @h2 := 'HELLO';
select strcmp(@h1,@h2);



4、自定义函数
函数要素：函数名，参数列表（形参和实参），返回值，函数体（作用域）

（1）创建函数
创建语法
create function 函数名([形参列表]) returns 数据类型 -- 规定要返回的数据类型
begin
-- 函数体
-- 返回值：return类型（指定数据类型）；
end

eg：-- 自定函数
create function display() returns int
begin
return 100;
end


（2）自定义函数与系统函数的调用方式一样：
select 函数名([实参列表])；

eg:-- 调用自定义函数
select display();

（3）查看函数
查看所有函数：show function status[like 'pattern'];
查看函数的创建语句: show create function 函数名;


（4）修改函数&删除函数
函数只能先删除再新增，不能修改。
drop function 函数名;
eg: -- 删除函数
drop function display;

（5）函数参数
参数分为两种: 定义时的参数叫形参, 调用时的参数叫实参(实参可以是数值也可以是变量)
形参: 要求必须指定数据类型
create function 函数名(形参名字 字段类型) returns 数据类型

eg：-- 函数形参
delimiter $$
create function display(int_1 int ) returns int
begin
-- 定义条件变量
set @i :=1; -- @定义的是全局变量，没有的可以理解为局部变量
set @result :=0; -- 用于保存相加结果

-- 循环求和
while @i < = int_1 do
-- 求和：任何变量要修改必须使用set关键字
-- mysql中没有 +=，++
set @result := @result + @i;

-- 修改循环变量
set @i =@i +1;

end while;

return @result;
end
$$ -- 结束函数
delimiter ;

在函数内部使用@定义的变量在函数外部也可以访问

（6）作用域
Mysql中的作用域与js中的作用域完全一样
全局变量可以在任何地方使用; 局部变量只能在函数内部使用.

全局变量: 使用set关键字定义, 使用@符号标志
局部变量: 使用declare关键字声明, 没有@符号: 所有的局部变量的声明,必须在函数体开始之前


（7）存储过程
1.存储过程简称过程，procedure ，是一种用来处理数据的方式
存储过程是一种没有返回值的函数。


2.创建过程
create procedure 过程名称([参数列表])
begin
-- 过程体
end

eg：-- 创建存储过程
create procedure Pro1() -- 假设过程中有需要显示的数据：使用select
select * from my_student;


3.查看过程
函数的查看方式完全适用于过程：关键字换成procedure

查看所有过程：
show procedure status [like 'pattern'];

查看过程创建语句
show create procedure 过程名;


4.调用过程
过程没有返回值：select不能进行访问
过程有一个专门的调用关键字：call

语法：call 过程名称;

5.修改过程&删除过程
过程只能先删除再新增

语法：drop procedure 过程名称; -- 注意过程名不需要加（）


6.过程参数
函数的参数需要数据类型指定，过程比函数更加严格

过程还有自己的类型限定：三种类型
in：数据只能从外部出入给内部使用（值传递）：可以是数值变量
out：只允许过程内部使用
inout：外部可以在内部使用，内部修改也可以给外部使用；典型的值传递：只能传递变量。

基本使用：
crate procedure 过程名(in 形参名称 数据类型,out 形参名称 数据类型,inout 形参名字 数据类型)