mysql

轻量级数据库，免费，开源 数据库：单进程多线程多用户

基于C/S架构，客户端服务器端

开启数据库服务器命令：

service mysqld start
#mysqld: 服务器端进程名称

客户端连接服务器：

mysql -u(user) root -h(host) localhost -p(password)

use db_name; 选择库

数据存放在表结构，表的建立依赖于库

system clear; 清屏

quit 退出

select version(); 查看版本信息

status;查看数据库状态

SOL语句

结构化查询语言 ===》 操作数据库

DDL

数据定义语言（针对于结构的增删改查）

create增，drop删，alter修改，show查询

结构标识：

1. 库：database
2. 表：table
3. 视图：view
4. 索引：index
5. 触发器：trigger
6. 存储过程：procedure

database库

每个结构回收或申请的资源都是不同的，故应加上结构标识

1. 创建：

create database [if not exists] TL13;

if not exists 可加可不加，如果库已存在，不会报错

2. 删除：

drop database [if exists] TL13;

3. 查询：

查询数据库下的所有库：

show databases;

查看库的创建信息：

show create database db_name;

select database(); 显示当前在哪个数据库下

table表

数据类型

varchar(10) 可变长类型，'hello' 分配字节：6

char(10) 固长类型，"hello" 分配字节：10

• tinyint：小整数值，如状态，1

• smallint：大整数值，2

• mediumint：大整数值，3

• int：大整数值，4

• bigint：极大整数值，8

• float：单精度浮点数值，4

• double：双精度浮点数值，8

• decimal：含小数值，如金额，Max(D+,M+)（DECIMAL(P, D)，D<=P，P有效数字位数，D小数位数，DECIMAL(P）不包含小数，DECIMAL，P默认10，存储以4字节存储每9位数字的每个倍数）

---

日期时间

• date：YYYY-MM-DD，3

• time：HH:MM:SS，3

• year：YYYY，1

• datetime：YYYY-MM-DD HH:MM:SS，8

• timestamp：YYYYMMDDHHMMSS，8

---

• char：定长字符串，0-255

• varchar：变长字符串，0-65535

• text：长文本数据，0-65535

• blob：二进制形式的文本数据

---

1. 创建表

create [temporary] table [if not exists] table_name [(create_definition...)][table_options][select_statement];
#temporary:临时表，当前会话结束后自动消失
#table_options:配置选项，如表的默认存储引擎，字符集，eg:...engine=InnoDB default charset=utf8
#select_statement:通过select语句建表

create table stu
(
   id varchar(10),
   name varchar(20),
   age int,
   sex enum("man","woman")
);

2. 字段约束
   1. 主键：primary key（非空、唯一）
   2. 外键：foreign key （其他表的主键）foreign key(st1_no) references stu2(st2_no)
   3. 唯一：unique
   4. 非空：not null
   5. auto_increment：自动增长
   6. 默认：default 默认NULL（系统自带）

create table stu
(
       id varchar(10) primary key COMMENT "学生学号",
       name varchar(20) not null,
       age int default 18,
       sex enum("man","woman"),
       constraint name foreign key (name) references stu2(na)
);
#COMMENT "注释"
#字段名称+字段类型+字段约束+字段注释

3. 删除表：

   drop table stu;

4. 修改表结构：alter

   1. 修改字段类型 modify

alter table stu modify id varchar(20);

2. 修改字段名称 change

alter table stu change id sid varchar(15);

也可修改字段类型

alter table stu change sid sid varchar(20);

3. 添加新字段 add

alter table stu add id_card varchar(18) unique COMMENT "身份证";
...+after sid;
...+first;

4. 删除字段：drop

   alter table stu drop id_card;
   

5. 修改表名：rename

alter table stu rename student;

5. 查看表：

   查看当前库下所有表：

show tables;

查看表的创建信息：

show create table stu;

查看表的字段信息：

desc stu;

DML

数据操作语言（针对于数据的增删改查）

insert增，delete删，update修改，select查询

1. 添加数据：

insert into stu values("1",'zhangsan',18,"man");

insert into stu(id,name,sex) values("1","zhangsan","man");

小批量处理：

insert into stu values("3","wangwu",17,"man"),
                        ("4","zhaoqi",20,"wuman"),
                        ("5","kaixin",19,"woman");

大批量处理： load（可以通过加载将一个文件的数据全加载到数据库中）内部对每条数据的处理也是insert

2. 删除数据：delete

delete from stu;
#删除表中所有数据,可恢复
#truncate, 删除数据，不能恢复

delete from stu where id = 1;

3. 修改数据：update

update stu set age=20;
update stu set age=20 where id=2;

DQL

1. 数据查询语言，查询数据：select

   1. 普通查询：

select * from stu;
#*：通匹，所有，有弊端，可能信息泄露
#不等：<>或!=
#介于两个值之间：between and

select id,name,age,sex from stu [where age > 18];

2. in和like

#in 在where子句中过滤某字段的多个值
...where column_name in (value1, value2,...);
      
#like 模糊匹配，查询包含xxx字符串
like "%value%"
# %:匹配任意0或多个字符
# _:匹配任意单个字符
      
as
#取别名

3. 去重查询：distinct
   例：游戏用户表，1E个用户，查询用户年龄范围

select distinct age from game_user;

4. 排序查询：order by asc升序（默认） | desc降序

select distinct age from stu order by age asc;

5. 分页：limit

#limit 限制查询结果数量
limit [i,]n
#i下标（默认0开始）n数量
      
#分页公式，limit (page-1)*row_count, row_count

6. 分组查询：group by

select id,Sum(score) from result group by id [having sum(score) > 200];

分组后所有操作是针对于组内进行的

SOL：大小写不敏感

7. group_concat函数

#配合group by使用，将某列按指定分隔符进行拼接，默认,
select id, group_concat(name) ...
      
group_concat([distinct] column_name [order by column_name] [separator '分隔符'])

select id,group_concat(concat(name,'(',sore,')') separator ';') from stu group by id;

8. 连接查询（根据条件先缩小范围，再笛卡尔乘积处理）

9. 内连接查询

只需要匹配项，其余全过滤

#inner join 或 join
select name,score
from
(select id,name from stu where age < 20) a
inner join
(select id,score from result where score<60) b
on a.id=b.id;

10. 外连接查询：左外连接查询、右外连接查询、全外连接查询

a、左外连接查询

包含左表中的全部数据行

select name,score from
(select id,name from stu where age < 20) a
left join
(select id,score from result where score < 60) b
on (a.id=b.id);
#`on a.id=b.id` ：过滤条件，后面可加`where b.id is not null` 去除匹配失败
#左表中的数据和右表数据匹配：success右边补右表数据；fail右边补NULL

b、右外连接查询

包含右表中的全部数据行，不匹配成功左边补空

select name score from
(select id,name from stu where age<20) a
right join
(select id,score from result where score<60) b
on a.id=b.id;

c、全外连接查询 （mysql不支持）

包含左表、右表的全部数据行

select name,score
from
(select id,name from stu where age<20) a
full join
(select id,score from result where score<60) b
on a.id=b.id;

mysql数据库不作优化的话有些版本可能不支持全外连接查询

实际工作中应用最多的是左外连接查询

11. 自连接

#相互连接的表在物理上同为一张表，但逻辑上是多张表
select A.column, B.column
from tablename A, tablename B
where A.column=B.column;

12. 子查询in, exists

select id, name, addr
from students A
where id in (select id from score where pro = '数学');
#where exists (select * from score B where A.id=B.id and pro = '数学');
#可用exists替代，用于测试内部查询是否返回任何行的bool值
#not exists

13. 联合查询
    union 自带去重 | union all 不去重

查询所有师生信息

select * from teach union select * from stu;
select * from teach union all select * from stu;

DCL

数据控制语言（针对于权限的管理）

用户管理

create user '用户名'[@'主机名'] [IDENTIFIED by '密码'];
# 主机名（IP或机器名称）%，表示任何地址的主机地址
drop user '用户名'[@'主机名'];
alter user '用户名'[@'主机名'] IDENTIFIED by '新密码'; 
                
select * from user[\G];
#\G 格式化输出

grant授予权限，revoke回收权限 --all所有权限

授予TL13库中所存表的查看权限给tl13用户

grant select on TL13.* to tl13;

回收所有关于TL13库中的所有表的权限从tl13用户中

revoke all on TL13.* from tl13;

#刷新权限
flush privileges;
#查看权限
show grant for '用户名'[@'主机名'];

谁赋予的权限谁才能回收权限

权限：

• 数据类：insert，update，delete，selete，file

• 结构类：create，alter，drop，index，create temporary tables，show view，create routine，alter routine，

  execute，create view，event，trigger

• 管理类：usage，grant，super，process，reload，shutdown，show databases，lock tables，references，

  repucation cuent，repucation slave，create user

#忘记root密码
#关闭mysql服务--》重启时关闭权限验证--》修改密码--》正常启动
#刷新权限flush privileges
#关闭权限验证
mysqld --defaults-file="配置文件my.ini的完整路径" --console --skip-grant-tables --shared-memory
#--console：控制台，加上后控制台不会关闭
#--skip-grant-tables：跳过授权
#--shared-memory：共享内存的方式去启动
select variables like "%datadir%"
#查看数据目录在哪，my.ini在数据目录中

内置函数

聚合函数用having，时间函数可直接用where处理

• 数学函数： ABS（绝对值），SQRT（开平方），MOD（求余），SIN（正铉），COS（余铉），TAN（正切），COT（余切）

• 字符串函数： LENGTH（求长度），LOWER（小写转换），UPPER（大写转换），TRIM（取空格），SUBSTRING（字符串截取（LEFT，RIGHT）），CONCAT（拼接字符串）

• 时间函数： NOW，CURDATE，CURTIME，SYSDATE，DATE_FORMAT（日期时间格式转换），YEAR，MONTH，WEEK

  now(); 获取当前时间，返回值datetime 类型（2020-4-2 20:56:52）

  date_format(now(), '%Y/%m/%d %H:%i:%s')

• 聚合函数： COUNT（计数），忽略空值：SUM（求和），AVG（平均值），MIN（最小值），MAX（最大值）

  count(*)

• 条件判断函数： IF，IFNULL，CASE WHEN

  ifnull(v1,v2) vi不为空返回v1，否则返回v2

  case [col_name] when [value1] then [result1] when [value2] then [result2] else [default] end

  case when [expr] then [result1] else [default] end

case sore when "A" then "优秀" when "D" then "不及格" else "及格" end
#==
case when sore = "A" then "优秀" when sore = "D" then "不及格" else "及格" end

• 系统信息函数： VERSION，DATABASE，USER

• 加密函数： MD5，SHA1，SHA2

  md5("hhhhh")

索引

一种数据结构，提高查询效率，索引的建立基于字段来建立

建立索引选择区分度高的列，每次查询只能使用一个索引

慢查询日志

日志类型

• 重做日志(redo log)：是种物理格式的日志，记录物理数据页面的修改的信息
• 回滚日志(undo log)：逻辑格式，undo时，仅仅将数据从逻辑上恢复至事务之前的状态，而非物理页面上操作实现
• 二进制日志(binlog)：逻辑格式，记录数据库中的操作，但不记录查询
• 错误日志(errorlog)：记录mysqld启动、停止及服务器运行期间发生的错误相关信息
• 慢查询日志(slow query log)：记录执行时间过长和没有使用索引的查询语句
• 一般查询日志(general log)：记录服务器接收到的每个查询或命令，不论正确
• 中继日志(relay log)：类似二进制，可用于复制架构中，使服务器与主服务器的数据保持一致

日志全部打开会影响性能，根据需要选择

#不仅记录select，如insert\update\delete，只要响应时间超过设定阙值都会记录
#参数：
slow_query_log  #是否开启慢查询日志，1开启，0关闭
slow_query_log_file  #慢查询日志存储路径，可选，mysql5.6之前的版本用log_slow_queries
long_query_time  #阙值
log_queries_not_using_indexes #未使用索引的查询也会被记录
log_output #日志存储方式：'FILE'(日志存到文件，默认)，'table'(日志存入数据库)，'FILE,TABLE'(日志同时存入)

#通过命令行临时设置，修改配置文件永久设置
#查看是否开启慢查询日志
show variables like 'slow%';
#临时开启慢查询日志
set global slow_query_log='ON';
set long_query_time=1;
#慢查询日志文件所在位置
show variables like '%datadir';

查询分析器 explain

查看SQL语句的执行计划，即显示来自优化器的有关语句的执行计划

#除了select，同样可以分析update,insert,delete
explain select ...\G;

显示参数：

• id：执行select子句或操作表的顺序
• select_type：查询的类型，如SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION等
• table：当前行使用的表名
• partitions：匹配的分区
• type：连接类型，如（好==》）system，const，eq_ref，ref，range，index，all（==》差）等
• possible_keys：可能使用的索引
• key：实际使用的索引，NULL未使用
• key_len：查询中使用的索引长度
• ref：列与索引的比较
• rows：扫描的行数
• filtered：选取的行数占扫描的行数的百分比，理想结果100
• extra：其他额外信息

索引分类

• 普通索引：最基本，无限制，仅仅加速查询
• 唯一索引：索引列的值唯一，允许空值
• 主键索引：特殊的唯一索引，不允许空值，一般建表的同时自动创建主键索引
• 复合索引：两个或多个列上的索引
• 全文索引：对文本内容进行分词索引，非叶子结点索引值都是text数值，则数值太大，索引结构庞大，I/O效率低

#创建普通索引
create index index_1 on stu(id);
#创建唯一索引
create unique index index_1 on stu(id);
#创建复合索引
create index index_1 on stu(id，name);

#删除索引
drop index [index_1] on stu;

#查看索引
show index from stu;

#要避免对索引列进行计算，如like模糊匹配、对索引字段进行函数处理等情况，可能不会用到索引

#对字段添加索引
alter table tbname add index index_name on field_name;
#建表过程中
index index_name(field_name);

复合索引的前导列特性（最佳左前缀特性）：

若创建的复合索引（id1，id2，id3），则相当于建立了3个索引，（id1）（id1，id2）（id1，id2，id3）

覆盖索引：

即要查询的数据列只从索引中就能得到

覆盖索引必须要存储索引列的值，如哈希索引、全文索引、空间索引并不会真正存储索引列的值

哪些情况应建立索引：

1. 经常用于查询的字段
2. 主键的字段
3. 用于两个表连接的字段
4. 经常用于排序查询的字段
5. 范围查询
6. 经常用于条件过滤

哪些情况不应建立索引的字段：

1. 不经常用于查询的字段
2. 数值较小的字段
3. text、BLOB类型的字段，数值太大
4. 修改性能大于查询性能的字段

索引的注意事项：

1. 不会包含有NULL的列
2. 使用短索引
3. like模糊匹配不能使用索引查询；%在最前面不能使用索引查询（"li%“可；”%san"不可）
4. 不要在列上运算

事务

一组特定SQL语句的集合

需满足四个特性：A、C、I、D

A　原子性：　要么全部执行成功，要么全部执行失败

C　一致性：　保证事务执行前后的完整性约束（主键约束、外键约束．．．核心通过锁机制控制）

I　隔离性：　事务独立运行，消除事务间相互影响

Ｄ　持久性：　事务执行的结果在磁盘上永久保存

mysql中只有使用了innodb存储引擎的数据库或表才支持事务，用于维护数据库的完整性，用于管理insert、update和delete

#默认情况下mysql是自动提交事务的，即每执行一条insert、update、delete语句提交后会立即执行commit操作
#开启事务：
start transaction;
#或
begin;

#autocommit默认为1，自动提交事务，可设为0，手动去提交
select @@autocommit;
set autocommit=0;

#对于事务，要么回滚（rollback）全部失败、要么提交（commit）全部成功

I 隔离性

没有隔离性，事务执行过程中出现的问题

1. 脏读

   事务执行过程中获取到其他事务执行过程中（修改但尚未提交）的结果

2. 不可重复读

   事务B执行过程中获取到其他事务不同阶段的结果

   修改update导致的

3. 幻读

   事务执行过程中获取到了其他事务不同阶段的结果

   insert、delete导致的

隔离级别

未提交读：READ-UNCOMMITTED 存在问题：1、2、3

已提交读：READ-COMMITTED 存在问题：2、3

可重复读：REPEATABLE-READ （InnoDB默认）存在问题：3

可序列化（事务执行不并发，串行）：Serializable 无存在问题，但效率变低

查看隔离级别：

select @@tx_isolation;

设置隔离级别：

set tx_isolation="..."

全局隔离级别（默认REPEATABLE-READ）

会话隔离级别

如果事务执行过程中，其他事务开启，则该事务对其他事务来说透明（可重复读解决问题核心），解决了不可重复读

幻读===》间隙锁解决

日志系统：1、redo log重做日志；2、undo log未做日志

redo log记录事务将要执行的操作

日志先行，先刷新到磁盘上，再进行事务，保证事务全部执行成功 （一致性）

undo log 记录每个修改点的状态

用于回滚，保证事务全部执行失败 （一致性）

持久性通过日志先行保证

一致性的保证通过锁机制来保证

分区表

按照某种规则将同一张表的数据分段划分到多个位置存储

提高了数据库的性能，在物理上是多个文件，在逻辑上仍是一个表

mysql自5.1.3开始支持分区，8.0版本只有Innodb和NDB存储引擎支持分区

分区类型：

• Range分区（常用）：允许将数据划分不同范围，如将表数据通过年龄划分若干分区

create table...(
...
primary key(id,age)
#复合主键
)
partition by range(age)(
partition p1 values less than (20) data directory = 'C:/data/p1',
partition p2 values less than (50) data directory = 'C:/data/p2',
partition p3 values less than maxvalue data directory = 'C:/data/p3'
);
#主键必须包含分区字段

• List分区：允许通过预定义的列表值进行数据分割

create table...(
...
primary key(id,pri)
partition by list(pri)(
partition p1 values in (1, 3, 5),
partition p2 values in (2, 4, 6)
);

• Hash分区：允许通过对表的一个或多个列的hash key计算，最后通过hash码不同数值对应数据区域进行分区

create table...(
...
primary key(id,bir)
)
partition by hash(year(bir))
partitions 5;
#分区5个

• Key分区：对hash模式的延申，但hash key是系统产生的

create table...(
...
primary key(id,age)
)
partition by key(age)
partitions 5;
#分区5个

新增分区

alter table users add partition(partition p5 values less than maxvalue);

对已存在表分区

alter table users partition by range(age)(
    ...
);

删除分区（数据也会删除）

alter table users drop partition p5;

移除分区（数据不会被删除）

alter table users remove partition;

视图

一个虚拟表，内容由select定义，数据是在使用时视图动态生成，存储在基表

视图表的数据与基表数据变化会互相影响（基于多表关联而来的视图，不能进行数据变更操作）

#创建视图
create view view_name as select ...;

#修改
alter view view_name as select ...;

#查看
show create view view_name;
#查看有哪些视图
show table status where comment='view';

#删除
drop view view_name;

存储过程

为完成特定功能的SQL语句集，经编译创建保存在数据库中，可指定存储过程名并给定参数来调用执行

可移植性较差，无法使用explain对存储过程进行分析

#定义，delimiter:声明语句结束符
delimiter //
create procedure pro_name(in a int, out result int) #参数属性 参数名称 参数类型
begin
    #存储过程需要的执行操作,declare声明
    declare i int default 1;
    declare sums int default 0;
    
    while i <= n do
        set sums = sums + i;
        set i = i + 1;
    end while
    
    set result = sums;
end
//
delimiter ;

#调用
call pro_name(20, @result);
select @result;

#参数属性：IN，输入型参数，值传递；OUT，输出型参数，执行最后形参数据输出到实参；INOUT，输入输出型参数，引用
#定义存储过程内部的变量，set @mydata = select ...; set @name=0;变量的操作通过set操作,定义取值都用@
#删除
drop procedure pro_name;

触发器

监视某种情况并触发某些操作，是与表事件相关的特殊的存储过程，执行是由事件来触发

查询无法触发触发器

create trigger tri_name
tri_time      /*触发时间，before  after*/
tri_event     /*触发事件，insert  update  delete*/
on table_name  /*监视的表*/
for each row
tri_stmt;/*触发器执行操作*/
/*
begin
    ...;
    可使用OLD、NEW来引用变化前后的记录内容
    new.columnName:获取insert触发事件中新插入的数据
    old.columnName:获取update、delete触发事件中被更新删除的数据
    ...;
end
*/

#eg:
delimiter $
create trigger send_email
after
insert
on user_info
for each row
begin
    insert into email_info(email, content, send_time) values(new.email, "ok!", now());
end
$
delimiter ;

一张表上同类型（tri_time,tri_event）的触发器最多允许出现一个，一张表上最多能建立6个触发器

哪些操作能触发触发器：

1. insert：insert load replace（load批量插入，replace：有主键delete+insert；无主键insert）
2. delete：delete
3. update：update

预处理（绑定变量）

普通SQL语句（一次编译一次运行），预处理SQL（一次编译，多次运行），大大提高了客户端和服务器端数据传输的效率

创建预处理SQL，客户端向服务器发送SQL语句原型，服务器接收后解析并存储了该SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄，以后每次执行该SQL语句，客户端都指定使用该句柄

**优势：**高效执行重复SQL，减少网络开销（执行只需发送参数至服务器，而不是整个SQL语句），更安全（无须在应用程序中处理转义，也减少了SQL注入和攻击的风险）

#mysql支持sql接口的预处理，即不使用二进制传输协议，直接以sql的方式使用预处理
#定义
prepare stmt_name from preparable_stmt;

#执行
execute stmt_name [using @var_name [,@var_name]...];/*使用什么参数*/

#删除（释放）定义，会话级，会话关闭后消失
{deallocate | drop} prepare stmt_name;

#可在存储过程中使用

#eg：
prepare pre_employee from 'select * from employee where name=?';/*sql原型*/
set @name1='hhh1';
set @name2='hhh2';
execute pre_employee using @name1;

数据处理

查询缓存（mysql 8.0已删除查询缓存功能）

缓存查询的执行计划：对相同类型的sql语句可以跳过sql解析和执行计划分成阶段

查询缓存：缓存完成的select查询结果

将缓存存放在一个引用表中，类似于hashmap的数据结构，key是查询sql语句，value是查询结果，发起查询时，会使用SQL语句从缓存中查询，命中则返回缓存结果集，能控制的地方太少，更新缓存时有很大的性能损耗

show variables like '%query_cache%';
#查看跟查询缓存相关的变量
#可使用SQL_NO_CACHE在select中禁止缓存查询结果，select SQL_NO_CACHE ...;

表的复制

1. 只复制表结构（包括主键、索引、但不会复制数据）

create table ta_name like othertable_name;

2. 只关注表数据（复制表的大体结构及全部数据，不包括主键、索引）

create table ta_name select * from othertable_name;

3. 复制表结构及数据

create table ta_name like othertable_name;
insert into ta_name select * from othertable_name;

数据导出

#select...into outfile，导出纯文本数据
select * from students into outfile 'D:\\s.txt'
fields terminated by ','    /*指定字段分隔符*/
enclosed by '"'          /*值用双引号引起*/
lines terminated by '\r\n';    /*指定行间分隔符*/


#mysql命令重定向查询结果，-e选项，可执行指定SQL语句，结合dos重定向操作符‘>’，可将查询结果导出到文件，不需要连接数据库
mysql -h localhost -u root -p -D mydb -e "select * from students" > D:\s.txt


#mysqldump，是mysql专用于转存储数据库的实用程序，主要会产生一个SQL脚本，包含创建数据库、创建数据表、插入数据所必须的sql语句
#导出mydb数据库（含数据）
mysqldump -h localhost -u root -p mydb > D:/mydb.sql
#导出mydb数据库（不含数据）
mysqldump -h localhost -u root -p mydb --no-data > D:/mydb.sql
#导出mydb.students数据表
mysqldump -h localhost -u root -p mydb students > D:/students.sql
#导出mydb数据库、忽略contacts表
mysqldump -h localhost -u root -p mydb --ignore-table mydb.contacts > D:/students.sql

#secure_file_priv取值：null：不允许导入导出，空：没有任何限制，指定路径：导入导出只能在指定路径下完成
show variables like '%secure_file_priv%';

导入数据

#load data，将文本文件数据导入对应的数据库表中
load data infile 'D:\\s.txt' into table stus character set utf8   /*字符集*/
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n';


#source，可导入较大的sql文件
source D:/mydb.sql

字符集

显示支持的字符集

show character set;

设置字符集

#创建库、表、字段时都可指定
create database daname charset utf8 collate utf8_general_ci;/*collate utf8_general_ci用于排序*/
create table tbname(...) default charset=utf8;
create table tbname(...id int not null charset utf8,...);

SQL注入

指应用程序对用户输入数据合法性没有判断及过滤，攻击者在应用程序通过表单提交特殊字符串，该特殊字符串会改变sql运行结果，从而实现非法操作，以此欺骗数据库执行非授权的任意查询

#eg:登陆时，账号：abc，密码：' or '1'='1
select * from user where username='abc' and password='' or '1'='1';
/* or '1'='1' 是符合条件的 */ 

存储引擎

MySQL是插件式存储引擎的数据库

整体是C/S架构，s:单进程多线程结构来处理，服务器端在内存中，数据在磁盘上

服务管理模块：协调两个不同的模块，针对于配置文件之间的处理

SQL API模块核心是处理DML，DCL语句

当SQL优化器拿到解析完后的SQL语句，作一定优化，优化完后制定n多的执行计划，简单理解就是执行策略，一条SQL语句对应生成n条执行计划，最终选择最合适的（通过算法加权处理），效率服务器端目前无法判断

查看该版本下数据库自带有哪些存储引擎：

show engines;

存储引擎的设立基于表

创建表时，系统会根据

1. 主键 主键索引 主索引
2. 唯一键 唯一索引 主索引
3. 添加隐藏字段 6字节 主索引

MyISAM
不支持外键，支持全文索引，不支持事务，B+树，表锁
数据与索引分离（数据存储，随意存储，数据离散程度大，非聚集索引）

创建一个MyISAM的表时，会在底层生成三个文件

1. .frm 创建表的基本创建信息和对应的一些配置信息
2. .myi MyISAM表的索引
3. .myd 真实的数据

MyISAM：索引值不重复的都是主索引（可多个）；索引值允许重复的是辅助索引（存储数据的地址）

InnoDB
支持外键，不支持全文索引，支持事务（NDB 没 InnoDB 好），B+树，行锁
把索引当成数据的一部分存储（索引和数据结合在一起）（数据的存放，放在索引中，数据的存放依赖索引，数据离散程度小，聚集索引）

InnoDB一定要有索引存在

主索引：存放数据的索引（不允许索引值重复）

InnoDB中，若已有索引，基于其他字段再建索引，且放入完整数据，则：1、数据冗余，没办法同步；2、空间占用率太大，故其他索引（辅助索引）存储主索引的索引值

InnoDB中辅助索引查询2次 O(logxn)

1. 辅助索引找到主索引的索引值
2. 主索引找到数据

InnoDB中主索引仅有一个

MEMORY

把数据（临时数据）放在内存中

B+树，哈希索引（默认），把varchar()当成char()，不支持text（大文本类型）和BLOB类型（图片类型）的字段，太大了，如果有，会交给MyISAM进行处理，存在磁盘上，效率降低

ARCHIVE

归档存储引擎，数据存储会按照一定比例进行压缩，默认的是10：1，压缩后再进行存储（数据量大，访问少，日志数据log）

不支持update和delete操作，仅支持insert，select，不支持索引

临时表： 一般真实表拿几个字段拼接的一张新表

锁机制

MyISAM 表锁，粒度大

读锁 共享读锁

写锁 独占写锁

1. insert delete update 写锁，select 读锁
2. 读写锁不兼容；读锁和读锁兼容；写锁和写锁不兼容

MyISAM表锁锁定的是一张表，不会发生死锁

读锁锁表，阻塞其他连接修改表数据，不会阻塞其他连接查询数据，写锁锁表，阻塞其他连接查询和修改数据

InnoDB 行锁，粒度小

读锁 共享锁：一个连接对一行数据共享读锁

写锁 排它锁：一个连接对一行数据排斥读写锁

1. insert,delete,update 写锁；select 不加锁
2. 读锁和读锁兼容；读锁和写锁不兼容（有写锁可读，因为select不加锁，事务隔离性也保证读的正确性）；写锁和写锁不兼容

InnoDB行锁开销大，可能发生死锁，锁定的是一行数据

针对于MyISAM，一个连接叫会话

针对于InnoDB，一个连接叫事务

事务一旦完成会将所有锁机制释放

InnoDB下每条SQL语句相当于事务，InnoDB还支持表锁

InnoDB行锁的处理机制（在索引的索引项上加锁），数据在索引存放，主索引，锁定索引项

select * from text where id=1 for update; 索引查询，主索引

select * from text where name="ZS" for update; 非索引查询，不会用到行锁

InnoDB：

1. 索引查询：行锁
2. 非索引查询：表锁

表锁：并发效率低

行锁：并发效率高，开销大

悲观锁，概念性的锁，提前加锁，读锁和写锁

乐观锁，遇上问题时加锁，人为设计===》1、版本标识；2、时间戳 ===》程序人员人为处理：读：不动版本标识；写：修改版本标识，若相同，则处理过程无其他操作，不同，则处理过程中有其他操作

没有了加锁解锁的开销

意向锁（解决行锁与表锁产生冲突问题）==》针对于InnoDB而言

加锁前先加意向锁（系统自动）==》兼容情况下，允许加锁，不兼容，阻塞

间隙锁（锁定的是数据间隙）范围查询才加间隙锁，解决幻读问题

binlog日志

记录了所有DDL与DML语句（除了select），以事件形式记录，包含语句执行消耗时间，mysql的二进制日志是事务安全型的，若数据丢失，可使用binlog日志进行数据恢复（定时全备份+binlog日志恢复增量数据部分 ）

show variables like 'log_bin%';/*查看binlog日志是否开启*/

#查看所有二进制日志列表
show master logs;

#查看正在使用的二进制日志
show master status;

#刷新日志（重新开始新的binlog日志文件  ）
flush logs

#查询指定的binlog
show binlog events in 'WQ_20230227MDKU-bin.000045' [from 10706]\G;

#导出恢复数据用的sql
mysqlbinlog “D:\pro\mysql\data\WQ_20230227MDKU-bin.000043” --start-position 403 --stop-position 907 > D:\backup\t.sql
#--start-position：开始位置，--stop-position 1191：结束位置

审计功能

开启审计功能，mysql就能自动生成审计日志，能实时记录数据库的操作记录，有效的帮助对数据库异常行为进行分析审核，审计会详细记录谁、什么时间、进行什么操作（mysql社区版没有自带的审计功能或插件，可使用第三方审计插件）

任何DDL语句（包括原子性或其他的DDL）都会隐式地结束当前事务，类似执行语句之前执行commit操作，意味着DDL语句不能位于其他事务之中

mysql的文档存储的简单了解

文档存储更灵活，不需要事先定义数据结构、数据约束就可以直接存储数据，将mysql用作文档存储时，集合（collection）是容器，包含可以添加、删除、查找、更新的json文档（document）

#使用mysql shell连接数据库（支持文档存储）
mysqlsh root@localhost:33060/mydb  /*端口，mysql默认端口是3306*/

#查看当前数据库
db

#查看当前数据库有哪些集合
db.getCollections()

#创建集合
db.createCollection("mytest_doc")

#删除集合
db.dropCollection("mytest_doc")

#添加文档
db.mytest_doc.add({
"id":1,
"name":"t1"
})

#查询文档
db.mytest_doc.find("name='t2'")
db.mytest_doc.find()

#删除文档
db.mytest_doc.remove("name='t2'")

#删除所有文档
db.mytest_doc.remove("true")