数据库-MySQL

数据库汇总： https://blog.csdn.net/qq_52681418/article/details/113881001

数据库-MySQL

• DDL：create、drop、alter
• DML：insert、delete、update
• DQL：select
• DCL：用户权限管理

什么是mysql？mysql就是一种数据库，数据库是用来存储数据的。举个例子，你注册一个账号，你的账户数据就存在数据库中。

mysql的存储实际上是表格形式的，以下表为例：

编号	用户名	密码
1	张三	123456
2	李四	666666

• 表：字面意思，就是一张表格，存放一组数据。
• 字段：一列就代表一个字段，编号字段、用户名字段…。
• 记录：一行就是一条记录，上面有2条。

实际上在你的主机上安装mysql后，你就可以对数据库进行存取操作了。也就是对数据的添加、删除、修改、查询。当一个新用户注册，这个表就会新增一行数据。

数据库中不可能只存放一张表，所以表可以创建多张，多张表之间也可以有关联性。

SQL语句

sql语句其实就是用来操作关系型数据的命令，你可以根据不同的命令来对数据库进行不同的操作。
如：

select *from user1;

常用语法CRUD: 库、表、字段、行

mysql服务

# 查看服务器版本
select version();
# 查看当前用户
select user();
# 查看服务器状态
show status;
# 查看配置变量
show variables;

库

# 查看全部库
show databases;
# 创建库
create database 库名;
# 使用库
use 库名;
# 删除库 [if exists]可选，防止报错
drop database [if exists] 库名;
# 查看当前库
select database();

表
如果不想字段数据发生重复，则为该字段添加主键或唯一索引即可。

# 查看全部表
show tables;
# 创建表
create table 表名(
	id int,
	name varchar(20),
	字段名 字段类型(长度),
	其它约束信息,
	...
);
# 删除表
drop table 表名;
# 复制表1
create table newtable select * from oldtable; 
# 复制表2
create table newtable like oldtable;
insert into newtable select * from oldtable;

字段
序列使用auto_increment关键字，即可以使值自增。
重置序列请看：https://www.runoob.com/mysql/mysql-using-sequences.html

# 查看表字段信息
desc 表名;		//desc 是describe简写，因此可以替换	
show columns from 表名;

# 添加字段
alter table 表名 add 字段名;
# 删除字段
alter table 表名 drop 字段名;

行
对记录基础的CRUD

# 增(2种)
insert into 表名 values (值1,值2...);	#值要和字段顺序对应
insert into 表名(字段1,字段2...) values (值1,值2...);
# 删
delete from 表名 where id=1;
# 改
update 表名 set 修改字段=新值 where id=1;
# 查
select *from 表;
select id,name from 表 where id=1;
select *from 表 a where a.id=1;	//a是表的别名

常用关键字：like、union等。

关键字	用法	作用
like	字段 like “％na”	搜索字符串
regexp	字段 regexp ‘.+?na.+?’	搜索字符串(正则表达式)
union	select… union select…	连接多个查询。默认去重，union all可带重
order by	order by 字段 排序方式	排序：ASC升、DESC降
group by	group by 字段	根据字段值分组，也可以去重查询。
with rollup	group by 字段 with rollup	统计分组成员数量
distinct	select distinct…	查询时去除重复数据
limit [开始记录] 条数	limit 5 或 limit 5,5	查询指定条记录,前者查询1-5，后者查询6-10

limit示例：

#查询前2条数据[1,2]
select *from user1 limit 2;
select *from user1 limit 0,2;
#查询[11-15],参数1为起始条-1，参数2为条数
select *from user1 limit 10,5;

重复数据管理示例：统计、过滤、删除

# 统计重复数据num
select count(*) as num,imgurl,url from qy_images group by imgUrl,url having num>1;

#查询时排除重复数据(2种)
select imgUrl,url from qy_images group (by imgUrl,url);
select distinct imgUrl,url from qy_images;

# 删除表中重复数据：创建新表的方式。
create table newtable select distinct *from qy_images;	#1.复制数据到新表
drop table qy_images;									#2.删除旧表
alter table newtable rename to qy_images;				#3.新表名改为旧表名

数据的导入、导出

1.数据库-文件

#数据库导出到文件：into outfile 文件名
select *from 表 into outfile '1.txt';
#文件到入到数据库: 添加local表示从主机获取、不加表示从服务器获取
load data local infile '1.txt' into table 表名;

2.数据-SQL文件
导出到SQL文件，使用了MySQL的 mysqldump 程序
参考：https://www.runoob.com/mysql/mysql-database-export.html

mysqldump -u root -p RUNOOB 表名 > 1.sql
password ******

从SQL文件导入

# mysql命令
mysql -u用户名    -p密码    < 1.sql
#source SQL语句
source /home/abc/1.sql

MySQL函数、运算符

常用函数

参考：https://www.runoob.com/mysql/mysql-functions.html

函数	用途
FORMAT ( 数字,小数位数)	数字a四舍五入，保存小数点后n位
LTRIM(字符串)	删首空
MID(字符串,起始位置,长读)	取文本中间=SUBSTRING()
DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数
DATE_FORMAT(d,f)	格式化日期

运算符

算术运算符

运算符	作用
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余

逻辑运算符

运算符号	作用
NOT 或 !	逻辑非
AND	逻辑与
OR	逻辑或
XOR	逻辑异或

位运算符

运算符号	作用
&	按位与
^	按位异或
!	取反
<<	左移
>>	右移

比较运算符

符号	描述	备注
=	等于
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间	>=min&&<=max
NOT	BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空

多表联查join:内连接、左连接、右连接

方式	用法	用途
内连接	t1 inner join t2 on t1字段=t2字段	查询两表都满足条件的数据
左连接	t1 left join t2	按照t1的满足的条件查询两张表
右连接	t1 right join t2	按照t1的满足的条件查询两张表

# 三种连接方式
select *from t1 a inner join t2 b on a.type=b.type;	//内连接
select *from t1 a left join t2 b on a.type=b.type;	//左连接
select *from t1 a right join t2 b on a.type=b.type;	//右连接

连接示例

现在有两张表
t1:

t2:

1.内连接
SELECT *FROM t1 a INNER JOIN t2 b ON a.type=b.type;

2.左连接
按照左表type为标准，没有的字段以null填充。
SELECT *FROM t1 a LEFT JOIN t2 b ON a.type=b.type;

3.右连接
按照右表type为标准，没有的字段以null填充。
SELECT *FROM t1 a RIGHT JOIN t2 b ON a.type=b.type;

4.3左连接示例：t1、t2、t3

select *from (t1 a left join t2 b on a.id=b.id)
left join t3 c on c.id=a.id;

行转列、列传行

1.行转列
方法一： if( , , )函数

//使用sum()、max()都行，主要是将数值输出
select stuid,name,
sum(if(subject="语文",score,0)) as 语文,
sum(if(subject="数学",score,0)) as 数学,
sum(if(subject="英语",score,0)) as 英语 
from stuscore group by stuid;

方法二：case when…then…else…

select stuid,name,
sum(case subject when "语文" then  score else 0 end) as 语文,
sum(case subject when "数学" then  score else 0 end) as 数学,
sum(case subject when "英语" then score else 0 end) as 英语 
from stuscore group by stuid;

2.列传行

select stuid,name,语文 as subject,max("语文") as score from student group by stuid  
union 
select stuid,name,数学 as subject,max("数学") as score from student group by stuid 
union  
select stuid,name,英语 as subject,max("英语") as score from student group by stuid;

可以发现在select后面，字段名代表当前列的值，即A as B 是将A列的值放到名为B的字段下。

类推

现在我们直接将下表进行行转列分析，推出第3种行转列的语句。

通过上面我们知道 A as B 中A代表对应列值，B代表新的列字段名。因此我们可以通过如下方式获得语文成绩并行转列。

现在我们可以对上面的表格进行行转列，因为stuid、name不需要转因此我们不用管这两个字段，我们可以使用score as 语文 + where subject =“语文” 的方式对语文进行行转列。于是通过同样方式我们可以得到如下结果：

我们可以得到3张表，通过左连接的方式就很容易得到结果了。

select  yy.stuid, yy.name, yy.英语, yw.语文, sx.数学 
from (
	(select stuid,name, score as 语文 from stuscore where subject="语文") as yw 
	left join
	(select stuid,name, score as 英语 from stuscore where subject="英语") as yy 
	on yw.stuid=yy.stuid
) 
left join
(select stuid,name, score as 数学 from stuscore where subject="数学") as sx  
on sx.stuid=yy.stuid;

结果：

其实很简单，分别查出各科成绩并组合即可。

同理可以使用视图实现

//创建语文、数学、英语视图
create view yw as select stuid,name,score as 语文 from stuscore where subject="语文";
create view sx as select stuid,name,score as 数学 from stuscore where subject="数学"; 
create view yy as select stuid,name,score as 英语 from stuscore where subject="英语";
//3个视图左连接
select yw.stuid,yw.name,yw.语文,sx.数学,yy.英语 from (yw left join sx on yw.stuid=sx.stuid) left join yy on yw.stuid=yy.stuid;

MySQL事务：ACID特性、隔离级别、传播机制、分布式XA事务

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务.

事务主要处理并发问题.
什么是事务?
事物是指应用程序中一套严密操作，要么干完要么不干。

ACID特性

原子性: 要么干完提交，要么报错回滚(撤销)。
一致性: 改数据库数据的时候一次性改完，报错就不改，不会改一半。
隔离性: 多个事务之间不能互相影响。
持续性:提交后改完就真改了，不能回滚啥的了。

四种隔离级别

读取未提交内容 ( Read Uncommitted ) :脏读。
读取已提交内容 ( Read Committed ) :有不可重复读问题。
可重读 ( Repeatable Read ) :有幻读问题(mysql默认)。
可串行化 ( Serializable ) :加锁影响性能。

//设置事务隔离级别
set [global|session] transaction isolation level 隔离级别;

三种常见问题
目标: 不读没提交的和新修改的,但读新添加的.

脏读: a改了数据未提交, b读到了这条数据, a回滚后, b再读就不一样了.
不可重复读: a修改数据并提交, b在a修改前读的数据和修改后读的数据不一样.
幻读:解决了b在a修改前后读取数据不一致的问题, 但a插入新记录后,b读不到新记录了.

七大传播机制
创建事务、加入事务、非事务方式执行、抛出异常

PROPAGATION_SUPPORTS加入当前事务，不存在则以非事务方式执行。
PROPAGATION_MANDATORY加入当前事务，不存在则抛异常。
PROPAGATION_NESTED 新键事务，当前事务存在时，在嵌套事务内执行。
PROPAGATION_REQUIRED 新建事务，当前事务存在时加入(mysql默认)。
PROPAGATION_REQUIRES_NEW 新建事务，当前事务存在时将其挂起。
PROPAGATION_NEVER 以非事务方式执行，当前事务存在时抛出异常。
PROPAGATION_NOT_SUPPORTED 以非事务方式执行，当前事务存在时将其挂起。

事务的操作

# 开启事务
begin;					//方法1
start transaction;		//方法2，不会自动提交

//回滚事务，撤销变更
rollback;

//提交事务,使变更永久变更
commit;

//设置自动提交状态：0关闭、1开启
set autocommit=0;

事务保存点

可以创建一个事务保存点，事务可以回滚到保存点而不是全部回滚。事务结束后保存点将全部被删除。

//设置一个保存点
savepoint 保存点名;
//回滚到保存点
rollback to savepoint 保存点名;
//删除保存点
release 保存点名;

XA事务

XA事务 SQL语句以XA开头，支持分布式、可以让多个主机的独立事务参与全局事务，这些独立事务为为事务分支或事务节点，对于这些节点来说，隔离等级必须为串行化。

角色

事务管理器: TM
全局事务协调者，所有分支事务向其汇报自身可执行状。
资源管理器：RM
分支、节点，自身管理自身的事务。

MySQL执行XA MySQL时,mysql相当于资源管理器，客户端相当于事务管理器，
全局事务：
全局事务要么所有分支全部提交，要么全部回滚，要考虑任意组件或连接可能出现故障。

1阶段提交：1PC
如果全局事务就一个分支，只需要一个提交阶段就行了。
2阶段提交：2PC
调节者接收全部分支的可提交状态，全都可提交，通知提交，否则通知回滚。
1.请求/表决阶段：事务发起者向事务调节者发送请求，事务调节者向所有分支发送事务预处理请求，分支则启动自身事务并执行但不会提交，执行完毕后向协调者报告执行状态：自己是否可以提交。如果所有分支都为可提交状态，则进入阶段2.
2.提交/执行阶段：事务调节者收到全部分支都可提交的状态后，向全部分支发送确认提交请求，所有分支立即提交自身事务，提交完毕后将成功消息返回给事务调节者，事务调节者收到全部成功消息后，将全局事务提交信息返回给发起者。

问题：

• 执行时，所有分支都处于阻塞阶段。
• 协调者挂了，全部分支都将无法结束事务。
• 网络异常或分支挂了，导致分支无法全部提交。

3阶段提交：3PC
1.CanCommit：分支自检，调节者收集事务是否可提交。
2.PreCommit：调节者预提交，分支执行，成功返回Ack，失败返回No。
3.DoCommit：调节者收集是否全为ack，是则分支提交，否则分支回滚。
参考:https://www.jianshu.com/p/0659417fb864

XA事务状态

• ACTIVE 活跃
• IDLE 闲置
• PREPARED 就绪

xid为事务唯一标识名，可以默认生成，成分有XA事务标识符、分支限定符、符格式ID(默认1)

# 语法
XA {start|begin} xid {join|resume};	//启动
XA end xid [suspend [for migrate]];	//闲置
XA prepare xid						//就绪
XA commit xid [on phase] 			//提交
XA rollback xid						//回滚
XA recover;	//查看已就绪的事务

# 示例
xa start 'myxa';	//启动
xa end 'myxa';		//闲置
xa prepare 'myxa';	//就绪
xa commit 'myxa';	//提交
xa rollback 'myxa';	//回滚
xa recover;			//查看

mysql锁

锁是为了仿效事务，在多个线程同时操作一张表时，某个线程锁定了表后，其它线程在锁被释放之前都无法操作这张表，这样保证了操作的原子性。
行锁效率比表锁高。使用索引时为行锁，不用索引时是表锁。其实是仿效事务。

表锁

• 锁定一个表，直到它释放锁后，其它线程才能允许访问。
• 当前线程再次锁一个表或连接断开时，锁将会被自动释放。
• 表锁只可以防止对表内部操作，比如删表等操作仍然可以。

//加锁：读锁、写锁
//read 和 read local区别：后者锁定后允许不冲突的插入，InnoDB中均等效read。
lock tables	表名 [别名] read [local];
lock tables 表名 write;
lock tables 表1 write, 表2 read; //锁定多个表，如果是同一张表，写在读前面

//释放锁
unlock tables;

write锁的优先级比read锁高，a线程获得了read锁后，b线程获取了write锁，则a线程需等待b线程释放锁。
其它：

• 事务不安全：锁定时，会自动提交所有活性事务，而且开启事务时，会自动释放锁。
• 使用表锁后，不能使用未加锁的表。
• 锁定时起了别名，sql中必须用此别名；锁定时没起别名，sql中不能用别名。

与事务一起造成死锁

1. 使用lock tables加锁时，mysql获取外部表锁、InnoDB获取内部表锁。
2. mysql释放表锁时使用unlock tables释放锁，但需要拥有内部表锁。
3. InnoDB调用commit提交事务时，内部表锁将被释放。
4. InnoDB事务默认自动提交，因此在事务未结束时加锁，事务自动提交并释放内部锁。

因此，加锁后事务自动提交并释放内部锁，mysql获取不到内部锁则无法释放外部锁，从而死锁。
解决办法：set autocommit=0 关闭事务自动提交。

---

行锁

innoDB实现了共享锁、排他锁：

• 共享锁（s）：又为读锁，获取该锁的事务对加锁的数据仅可读，同时其它事务只可加此锁，直到第一个获取此锁的事务释放。
• 排他锁（Ｘ）：允许取得此锁的事务更新数据，其它事务不能进行任何操作。

# 共享锁
select *from table lock in share mode;
# 排他锁：增删改 都默认加此锁
select *from table for update;
/**
*死锁发生：
*事务A、B的操作均为先获取共享锁再获取排他锁。
*并发情况下：A、B同时获得共享锁，共享锁部分执行完毕后，
*需要执行获取排他锁的内容，但想获取排他锁，必须得释放共享锁，
*由于两个事务都需要获取排他锁，才能结束事务并能释放锁，
*因此A、B都需要对方释放锁才能继续执行，即死锁。
*/

为了允许行锁和表锁共存，实现多粒度锁机制，innoDB两种意向锁：

• 意向共享锁（IS）：某行加了共享锁时，再为该表加共享锁可以成功，加排他锁失败。
• 意向排他锁（IX）：某行加了排他锁时，再为表加排他锁将失败；同样，事务给行加锁时，需要取得此锁。

除此之外，mysql还有其它锁：

• 间隙锁：本质上是只允许获取锁的事务允许在间隙插入事务。
• 临键锁：行锁+间隙锁。

MySQL索引 index：普通、组合、唯一、全文

合理使用mysql索引是非常重要的，它可以极大提高数据的检索速度。
索引实际上是内部创建了一个新表，保存索引信息，通过信息指向目标数据，因此更新操作会变慢，而且会提升占用空间，所以不要滥用索引。每张表最好不要超过6个。
查看一张表的索引

// \G格式化输出
show index from 表名; \G

---

普通索引

索引字段就一个。

创建索引（3种）：

# 1. 直接创建：index...on...
create index 索引名 on 表(索引字段);

# 2. 修改表结构：add...index...
alter table 表名 add index 索引名(索引字段);

# 3. 建表时创建：index...
create table 表名(
    字段1 类型1, 
    字段2 类型2,
    index [索引名] (索引字段(length));
); 

删除索引：

# 1.直接删除
drop index [索引名] on 表名

# 2.修改表结构删除
alter table 表名 drop index 索引名

---

组合索引

索引字段有多个，多个字段同时组合为一个索引。
组合索引的管理和普通索引的管理一样，只不过索引字段为多个罢了，多个字段用逗号隔开。

---

唯一索引 unique index

唯一索引是指索引值不同的索引，普通索引、组合索引的字段值相同时，组成的索引也是一样的，而唯一索引需要每条数据的索引字段值都无重复、或者多字段组合后的值不同。允许null值。

创建唯一索引：

和普通索引差不多，直接创建在index前添加unique即可，另外两种直接将index替换为unique。

# 1. 直接创建： unique index...on...
create unique index 索引名 on 表(索引字段);

# 2. 修改表结构：add...unique...
alter table 表名 add unique 索引名(索引字段);

# 3. 建表时创建：unique...
create table 表名(
    字段1 类型1, 
    字段2 类型2,
    unique [索引名] (索引字段(length));
); 

---

全文索引 fulltext index

MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。

全文索引的要求：

1、在MySQL5.6以下，只有MyISAM表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索。
2、表中数据最好超过4条。

创建全文索引：

和普通索引差不多，直接创建在index前添加fulltext即可，另外两种直接将index替换为fulltext。

# 1. 直接创建： fulltext index...on...
create fulltext index 索引名 on 表(索引字段);

# 2. 修改表结构：add...unique...
alter table 表名 add fulltext 索引名(索引字段);

# 3. 建表时创建：fulltext ...
create table 表名(
    字段1 类型1, 
    字段2 类型2,
    fulltext [索引名] (索引字段(length));
); 

全文索引的默认配置：

第一行：改变搜索词时，既不重启mysql也不重建索引。
第二行：索引最大长度，默认值为84，修改后必须重建索引文件
第三行：搜索词最小长度，默认为4，一般改为1。
第四行：查询括展时取最相关的几个值用作二次查询
第五行：过滤词文件，参考 http://ourmysql.com/archives/562

修改搜索词最小长度：
在mysql配置文件my.ini增加一行 ft_min_word_len = 1，改完后重启mysql即可。

windows重启mysql:
1.点击“开始”->“运行”(快捷键Win+R)。
2.启动：输入 net stop mysql
3.停止：输入 net start mysql
centos重启mysql：service mysqld restart

---

索引的使用

当你创建了索引之后，由数据库的查询优化器自动判断是否使用索引。当然你可以强制决定使用:

#强制使用
select * from 表名 force index (索引名);
#强制不用
select * from 表名 ignore index (索引名);

全局索引用法：match(索引字段) against(‘搜索词’)

# 全文索引的使用：索引字段为多个时，需要全部写出来
select * from 表名 where match(索引字段) against('搜索词');

更多全文索引内容：https://www.cnblogs.com/YangJiaXin/p/11153579.html

临时表 、视图

临时表temporary table

临时表只在当前连接可见，当连接关闭后临时表将会被销毁并释放空间。在关闭连接之前，你也可以手动销毁。

# 创建临时表
create temporary table 表名 (
    字段1 字段类型1,
    字段2 字段类型2,
    ...     
);
# 手动销毁临时表
drop table 表名;

视图view

视图类似于表存储在数据库服务器中，可为多张表的查询结果创建一个视图，通过视图直接查询，视图虽然可以查出这些数据，但实际上，这些数据依然在原来的表中，即视图不存储数据。

# 创建视图（视图和表同级别，因此不能和表同名）
create view 视图名 as select *from t1;
create or replace view 视图名 as select *from t1;	//覆盖(加or replace)

# 修改视图
alter view 视图名 as select *from t2;

视图数据来源可以是表，也可以是视图，但不能是临时表。
视图的select子句不允许引用参数或变量，也不允许有子查询，视图和触发程序不允许有关联。
在上面的sql语句中，在定义视图的时候，还有一个参数：
algorithm可选参数：有3个值，merge、temptable、undefined。

• merge：将视图语句替换为实际表语句。
• temptable：将视图结果存入临时表，释放锁更快。
• undefined：默认值，无定义。

# 创建、修改时均可指定
create view algorithm = undefined 视图名(vid,vname) as select id,name from t1; 

如上所示，视图可指定字段与查询字段对应。
merge演示：

# 当我创建一个视图，并且数据内容为t1中id>100的内容
create view algorithm=merge myview(vid,vname) as 
select id,name *from t1 where id>100;
# 引用视图
select *from myview vid<100;
#实际上执行的语句：
select id,name from t1 where id>100 and id<100;

可以看到，上面引用视图时，where条件实际上是原表查询，并且同时满足创建视图时的条件、以及视图引用语句的条件，而且vid和id是直接对应。

MySQL存储例程：存储过程、存储函数、触发器

存储过程、存储函数统称为存储例程，即在数据库服务器中存储的一组sql语句，可通过调用该过程名来执行这组sql。
项目大且复杂时，sql语句写在代码中难以维护，因此可以创建存储例程存放在数据库服务器中，代码中只需要调用例程名，即可实现对sql的使用。

MySQL的一些语法

在开始上手之前，我们需要了解一些知识:
存储例程主要由一个块组成，这个块为一个存储逻辑，就像方法的方法体{ } 一样，支持嵌套。

存储块：begin end

块中可以写变量、sql、条件语句等，是实际的执行逻辑，可以多层嵌套。

BEGIN
	...
END

mysql结束符切换：delimiter

由于存储例程中也会出现 ” ; “ 号，因此将系统的结束符暂时修改，防止sql代码误读。

DELIMITER //	#切换为//
DELIMITER ;		#切换为;

块变量

在块中的变量，作用域为当前块，子块也可以使用，变量类型为sql中的类型。

# 定义块变量name，此处设置默认值'zhangsan'
DECLARE 变量 类型;
DECLARE 变量 类型 DEFAULT 默认值;
# 修改变量值
SET 变量=值/表达式;

mysql变量

在正常的sql服务器内存中的变量，调用时直接 @变量 即可。

# 设置、修改
SET @变量=值/表达式;
# 删除变量
SET @变量:=NULL;
# 查看
SELECT @变量;
SELECT 'hello' into @变量;

条件语句

if-then-else 语句

delimiter //
create procedure test(in a int)
begin
	declare b int default 1;
	if b=a then
		update user1 set money=1;
	else
		update user1 set money=2;
	end if;
end //
delimiter ;
		

case语句

delimiter //
create procedure test2(in a int)
begin
	declare b int default 1;
	set b=b+a;
	# 1.传入值
	case b
		when 0 then 
			update user1 set money=100;
		when 1 then
			update user1 set money=200;
		when 2 then
			update user1 set money=300;
	end case;
	# 2.不传入值
	case
		when b=0 then
			update user1 set money=100;
		else
			update user1 set money=200;
	end case;
end //

while…do…

...
begin
	declare int b default 2;
	while b>100 do
		update user1 set money=100 where id=b;
		set b=b+1;
	end while;
end//
...

repeat…end repeat：循环后判断

...
begin
	declare int b default 2;
	repeat
		update user1 set money=100 where id=b;
	until b>100
	end repeat;
end //
...

loop…end loop：无条件循环、使用 leave 跳出循环。

...
begin
	declare int b default 2;
	myloop:loop
		update user1 set money=100 where id=b;
		set b=b+1;
		if b>100 then
			leave myloop;	# 跳出循环
	end loop;
end //

lables标号： 可以用在begin repeat while 或者loop 语句前，可以跳出循环，使运行指令达到复合语句的最后一步。
iterate：重新开始循环，即结束了本次循环。

...
myloop:loop
	set a=a+1;
	iterate myloop;	# 跳过循环
end loop;
...

可以直接使用：select 函数名(); 来调用函数。
此小节参考：https://www.jianshu.com/p/7b2d74701ccd

---

存储过程 procedure

存储过程存放一组sql逻辑。
无返回值，有三种参数：输入参数 ( IN ) 、输出参数 ( OUT ) 、输入输出参数( INOUT )

创建

CREATE PROCEDURE 过程名(IN 参数 类型)
BEGIN
	...
END

调用、修改、删除、查询

# 调用
CALL 过程名(@变量);
# 修改
ALTER PROCEDURE 过程名(变量);
# 删除
DROP PROCEDURE 过程名;

创建的完整步骤：

# 修改默认的结束符为 //
DELIMITER // 
# 存储过程
CREATE PROCEDURE 过程名(OUT 参数 int)
BEGIN
  SELECT COUNT(*) INTO 参数 FROM 表名;
END //
# 还原默认的结束符为 ;
DELIMITER ;

范例：

DELIMITER //  
CREATE PROCEDURE mytest (IN param1 INTEGER)   
BEGIN   
  DECLARE str CHAR(10);   	#声明变量
  IF param1 = 17 THEN   
      SET str = 'birds';   
      ELSE 
      SET str = 'beasts';   
  END IF;   
INSERT INTO table1 VALUES (str);  
END //  
DELIMITER ;  

查看：存储函数也适用。

# 查看存储过程列表(3种)
select name from mysql.proc where db='数据库名';
select routine_name from information_schema.routines where routine_schema='数据库名';
show procedure status where db='数据库名';
# 查看存储过程详细信息
show create procedure 数据库.存储过程名;

---

存储函数 function

存储函数与存储过程有些相似，但有有不同

delimiter //
create function myfun(a int)		#函数创建
returns int							#返回值类型
begin								#sql片段
	declare num int;
	select count(*) into num from user1 where age=a;
	return num;
end //
delimiter ;

调用、修改、删除：

# 调用
select 函数名(参数);
# 修改
alter function 函数名(参数);
# 删除
drop function 函数名;

存储过程、存储函数 比较

为了防止结束词冲突都需要使用delimiter 来改变mysql结束符。

delimiter //
...
delimiter ;

存储过程和存储函数

#存储过程
create procedure mypro(in a int,out b int,inout c int)
begin
	...
end

#存储函数
create function myfun(a int,b int,c int) returns int;
begin
	...
end

存储过程有输入、输出、输入输出参数，存储函数只有"输入"参数，但有返回值。

---

触发器 trigger

监听一个操作，监听到后在该操作前后执行一个另操作。（insert、delete、update 操作）
四个名词：要监听的表、监听的操作、触发的时机(after、before)、触发的操作。

触发器的创建：

# 语法
create trigger 触发器名 时机 监听的操作 on 监听的表 for each row 触发的操作; 

# 示例,语句很少时，可以不用begin
create trigger myTri after insert on user1 for each row 
begin
	insert into user2 values(1,'zhangsan');
end;

触发器的删除：

# 模式可省略，默认为当前模式
drop trigger [模式名] 触发器名

说明：有用，但尽量少用因为会提升数据库的维护难度。

SQL注入、SQL优化

SQL注入问题

sql注入即通过外部传入参数内容是sql片段时，改变了原本sql的执行逻辑。因此需要对用户的输入数据进行处理。
例如：

# 原本应该执行的sql：用户输入 zhangsan、123456
select *from usertable where name="zhangsan"  and  pwd="123456";
#输入name时不正常的输入如下：aaa" or 1=1; #  、bbb   此时sql语句就会变为：
select *from usertable where name="aaa" or 1=1; #"  and  pwd="bbb";
#在mysql中#代表注释，即不执行的内容，因此实际执行的sql变成了这样：
select *from usertable where name="aaa" or 1=1;
#从上面可以看出，sql语句被改变了，加了一个1=1值为true，始终执行。

除了这些，别有用心的人可能还会通过执行自己的sql来获取你的更多信息。

1.like语句注入

like的语法格式为：name like “ab%”，后面的字符串是匹配格式，_匹配任意一个字符，%匹配任意长度任意字符，当你未使用这些字符时，用户搜索带_或带%的内容时，会搜到多余的结果。
解决办法：对 _和%进行转义。

数据量小时一般不需要优化，但当数据有很多甚至上百万条时，就需要进行优化了。

---

SQL优化

sql优化不是解决注入问题的，只是为了不让文章目录过长，解决sql注入只需要过滤用户输入就行了。
sql优化是解决sql语句在有大量数据的情况下出现的性能问题。

1.查询优化

1.尽量使用索引、避免全表扫描
2.尽量避免模糊查询 select * 以及 like ‘%aa%’ ，如果需要使用可以使用如下解决办法：

• instr(str ,substr)
• fulltext全文索引、match against检索。
• 亿/s级搜索引擎ElasticSearch、solr
• 数据量小直接用like ‘%xx%’

3.尽量避免where子句null判断、使用 !=、<> 。

• 字段值尽量不要填充NULL、可用NOT NULL填充，或者int型用0填充。

4.尽量避免where子句使用or，替换如下：

#假设name有索引、age无索引 则索引失效
# 未优化，将放弃索引全局扫描
select id from user1 where name='zhangsan' or age=25;

# 优化，会使用索引 (联合查询)
select id from user1 where name='zhangsan'
union all
select id from user1 where age=25;

5.尽量避免where子句使用 in、not in

• 如果出现 in(1,2,3,4,5) 这种连续内容可以用函数 between 1 and 5 来代替。
• 用exists代替in：

# 优化前：tid在子句查询结果中
select id from user1 where tid in(select id from user2);
# 优化后
select id from user1 where exists(select id from user2 where id=a.tid);

6.尽量不要在where 子句中使用变量(@变量)、左边使用运算表达式、函数

• 如果一定要使用变量，可以强制使用索引： with( index( 索引名 ) )

select id from user1 with(index(索引名)) where num=@变量;

7.组合索引的第一个字段作为查询条件时才会使用索引，并且字段顺序尽量与索引字段顺序一致。
8.对于多张几百条以上的数据表join连接查询 ，先分页再join，否则逻辑读很高、性能很差。而且小表尽量放在前面，使用别名。

2.增、删、改 优化

1.insert操作记录很大时，应组合多条insert为一条，好处有:

• 减少解析为一次。
• 特定场景可以减少DB连接次数。
• sql语句整体较短，可减少网络传输的IO。

#优化前
insert into user1 values(1,2);
insett into user1 values(1,3);
insert into user1 values(1,4);
# 优化后
insert into user1 values(1,2),(1,3),(1,4);

2.适当使用commit可以释放事务占用的资源而减少消耗，如下：

• 释放事务占用的undo数据块；
• 释放事务在redo log中记录的数据块；
• 减少锁争用影响性能。使用delete删除大量数据时，分解删除量并定期commit。

3.避免重复查询更新的数据：可以使用sql变量，防止再次访问数据表

#一般写法
update user1 set time=now() where id=1;
select time from user1 where id=1;
# 使用变量优化
update user set time=now() where id=1 and @name:=now();
select @name;

4…进行delete、insert操作时会锁表，如果你的操作数据很多，锁的时间很久，而你的访问量很大，那么你操作这段时间，请求就会堆积，可能会导致服务器崩溃。
解决办法：使用limit拆分执行，每执行一部分后，暂停操作，缓解一下其它进程压力。
5.修改操作优先级，参考：https://zhuanlan.zhihu.com/p/265852739

3.其它

1.能用数值类型的就不要用字符类型。
2.尽量使用varchar、nvarchar代替char、nchar，因为可变长类型存储空间小。
3.尽量使用表变量代替临时表。
4.创建临时表时，数据量很大时使用select into，数据量小时，应使用create table和insert，
5.存储过程的最后应显示地删除所有临时表，先truncate table再droptable，可以避免系统表长时间锁定。
6.避免使用游标和临时表(小型数据集使用游标可能效果更好)，尽量使用集来解决问题。
7.在所有存储过程和触发器执行时关闭每个语句向客户端发送DONE_IN——PROC消息：
关闭、开启发送如下：

set nocount on;		# 关闭
set nocount off;	# 开启

MySQL连接 - JDBC

在mysql应用服务器外部使用mysql，使用DriverManager类来管理连接。也就是说在你的java代码中连接并操作数据库。

//在此之前请引入mysql驱动包
//此过程会有异常处理，为了在教程中步骤更直观，就省略了
public static void main(String[] args){
	//注册驱动 :根据类路径获取类并实例化(java反射)
	Class.forName("com.mysql.jdbc.Driver").newInstance();
	//获取连接
	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=monty&password=greatsqldb");
 	/*
 	 *
 	 *	CRUD操作
 	 *
 	 */
 	 //关闭连接
 	 conn.close();
}

insert 增

//使用?,是通过PreparedStatement 对象来防止sql注入。
String sql = "insert into user1 values(id=?,name=?)";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setObject(1,1);
ps.setObject(2,"zhangsan");
ps.executeUpdate();

delete 删

String sql="delete from user1 where id=? ";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setObject(1,1);
ps.executeUpdate();

update 改

String sql="update user1 set name=? where id=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setObject(1,1);
ps.setObject(2,"lisi");
ps.executeUpdate();

select 查

String sql="select id,name from user1";
PreparedStatement ps=conn.prepareStatement(sql);
ResultSet res=ps.executeQuery();

//结果不止一个，将结果放在list集合中
List<User> list=new ArrayList<>();
while(res.next()){
	User emp=new User();
	emp.setId(res.getInt("id"));
	emp.setName(res.getString("name"));
	list.add(emp);
}

由上面看，增删改都是一样的，查询略有不同，因为查询需要返回查询的记录信息，增删改返回影响的记录行数。

主从复制

为什么要用到主从复制?当你数据库服务压力很大时，你可以启用多个从服务（从节点）来缓解压力。并且主服务挂了可用从服务替代，达到高可用。
Replication 的原理：从节点拿到主节点二进制日志，然后根据日志来运行从而同步。

一主多从：某从节点拿到主服务日志，由它发送给其它从节点。
多主一从：将多个mysql服务器数据备份到一个较好的服务器上。
双主复制：两个主节点相互复制。
级联复制:部分从节点与从节点连接同步，缓解主节点连接压力。
参考：https://blog.csdn.net/cy973071263/article/details/104508211

配置Replication

主服务必须启用日志、配置唯一服务id、重启。(修改配置文件my.cnf)

[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1

创建日志目录并设置权限(shell)

mkdir /var/log/mysql
chown mysql.mysql /var/log/mysql
service mysqld restart

修改master my.cnf配置

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

关闭主服务的skip_networking：默认为关闭，查看的sql语句如下：

show variables like '%skip_networking%';

每个从服务都需要使用主服务的用户名、密码来连接。可以创建一个专门用来复制数据的mysql用户，只给读权限(SQL语句)。

//grant 权限 on 数据库.* to 用户名@主机 identified  by "密码";
grant replication save on *.*  to '用户名'@'%'  identified  by  "密码";

分区：分库、分表

为什么要进行数据拆分？数据量极大时，用一个叼炸天的数据库来存储很费>钱，而且数据再变得更大，也不易扩展。还不如直接用好几个比较水的数据>库一起存，而这种方式就需要拆分数据。

逻辑拆分：实际上没有分开，逻辑上分开；如为电脑分盘。

分区：MySql 支持Range，List，Hash，Key。
1.使用Range分区：

create table user1 (
   id int not null,
   name varchar(30),
   fqId int not null	//用来划分的字段
)
partition by range (fqId) (
   partition p0 values less than (6),
   partition p1 values less than (11),
   partition p2 values less than (16),
   partition p3 values less than (21)
);

2.使用List分区：

create table user1 (
   id int not null,
   name varchar(30),
   fqId int not null	//用来划分的字段
)
partition by list (fqId) (
   partition p0 values in (1,3,5,7,9),
   partition p1 values in (2,4,6,8,10),
   partition p2 values in (11,12,13,14,15),
   partition p3 values in (16,17,18,19,20)
);

3.使用Hash分区：

create table user1 (
   id int not null,
   name varchar(30),
   fqId int not null	//用来划分的字段
)
partition by hash(fqId) partitions 4;

4.使用Key分区：

create table user1 (
   id int not null,
   name varchar(30),
   fqId int not null	//用来划分的字段
)
partition by key() partitions 4;

分表：一个表数据太多，读写啥的都慢，分表后，查询子表就很快了。最常用算法为哈希算法。
实现方式：创建n个表，通过对id值运算来找到目标表，假设10张表，每张表10条数据，id=93时，93/10+1，第10张表；id=90时，90/10，第9张表。

物理拆分

读写分离：主从复制来实现，主节点写，从节点读，减少主节点压力。
垂直拆分(分库)：读写分离固然好，但写频繁时就不太好了，此时需要将写分离。即根据业务不同，将不同的数据表放在不同的库里，当然可以同时使用读写分离，进一步提高性能。写分离导致事务变得难以统一，因此需要分布式事务来处理。
水平拆分(分片):上面两个虽然能解决大部分问题，但体量巨大的数据库中，主库的写操依然多，因此需要将表水平拆分，并分布在不同的机器上。
水平拆分最简单的方式就是哈希算法。
1.简单哈希算法：https://xie.infoq.cn/article/200e90b980e8fec1ad6850878

MySQL常用复杂语法

1.查询的结果是一张表，可以发现查询一个表后返回的仍是一张表，因此我们可以：

select b.name from (select *from mytable where id>100) b where b.id<200

//上面相当于查询出一个结果，然后再根据结果查询，上面实际上相当于
select name from mytable where id>100 and id<200

2.起别名 ：字段、表都能起别名，可以使用as也可以不使用as

//此时结果表中，字段将显示为别名
select name as '姓名' from user as u where u.id>5
//等效
select name  '姓名' from user u where u.id>5