九、MySQL 数据库

1. delete，truncate 和 drop之间的区别

详情参考： https://mp.weixin.qq.com/s/6YmWM2fqpYlmD46CH4eL8A
相同点：truncate、delete 和 drop 都可以实现表删除；
不同点：

truncate、drop 和 delete 的区别主要有以下 6 点：
1.执行速度不同：drop > truncate > detele。
2.删除对象不同：delete 和 truncate 只删除表数据，而 drop 把表结构和表数据都删除了（会删除表数据和表结构以及表的索引、约束和触发器）；
3.删除条件支持不同：delete 可以加 where 条件实现部分数据删除，而 truncate 和 drop 不能加 where 条件是整体删除。
4.回滚支持不同：truncate 和 drop 是立即执行，且不能恢复；而 delete 会走事务，可以撤回和恢复。delete 属于 DML 支持事务回滚操作，而 truncate 和 drop 属于 DDL，执行之后立马生效，且数据是不可恢复的（delete 之后是可以进行恢复（回滚）的，而 truncate 和 drop 之后是不能回滚的）；
5.自增初始化不同：truncate 会重置自增列为 1，而 delete 不会重置自增列。delete 不会重置自增字段的初始值，而 truncate 会重置自增字段的初始值；
6.命令分类不同：truncate 和 drop 是 DDL 语句，而 delete 是 DML 语句。truncate、delete 和 drop 所属 SQL 分类不同，其中 delete 属于 DML，而 truncate 和 drop 属于 DDL。SQL 分为以下 3 类：
① DDL【Data Definition Language】数据定义语言，用来维护存储数据的结构；代表指令: create、drop、alter、truncate。
② DML【Data Manipulation Language】数据操纵语言，用来对数据进行操作；代表指令：insert，delete，update，DML 中又单独分了一个 DQL，数据查询语言，代表指令是 select。
③ DCL【Data Control Language】数据控制语言，主要负责权限管理和事务；代表指令：grant，revoke，commit。
PS：truncate 是先复制一个新的表结构，再把原有旧表结构和数据一起删除，所以它属于数据定义语言 DDL，而非数据操纵语言 DML。

3. SQL优化

详情参考： https://blog.csdn.net/qq_41029923/article/details/120532942

详情参考： https://mp.weixin.qq.com/s/ZosZWVeME3V652uO1yrdoA

1.创建索引
要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引；在经常需要进行检索的字段上创建索引；
避免在索引上使用表达式操作
2.选择正确的存储引擎
4.(查询select语句优化)select 语句列出需要查询的字段，不用*
5.①尽量避免在where子句中使用or来连接条件；
②尽量避免在where子句中使用 !=(<>) not in not exists；
③尽量避免在where子句中进行null值判断；is null. is not null；
④不能使用前导模糊查询，也就是 ‘%XX’ 或 ‘%XX%’，由于前导模糊不能利用索引的顺序，必须一个个去找，看是否满足条件，这样会导致全索引扫描或者全表扫描；
⑤ 不要在where 子句中对字段进行表达式操作；
⑥用union all替换union，使用exists代替in；
6.调整where子句连接顺序，表连接最好写在其他where条件之前，那些可以过滤掉最大数量记录数的条件必须写在where子句的末尾(因为DBMS一般采用自下而上的顺序解析where字句)
7.使用表的别名
8.尽量将多条SQL语句压缩到一句SQL中
9.使用预编译查询。

1. MySql和Oracle区别？

参考：MySql和Oracle区别 & Oracle中常见函数： https://blog.csdn.net/qq_41029923/article/details/120532942

1.数据类型区别：参考 https://www.cnblogs.com/yangxia-test/p/4710250.html
2. oracle中NUMBER(10,2)表示什么：参考 https://zhidao.baidu.com/question/1536396442757275827.html
3. Oracle/MySql下分别对应的日期与字符串转换函数？
参考： https://blog.csdn.net/J080624/article/details/52753726?utm_source=blogxgwz5
Oracle：to_char、to_date；
MySql：str_to_date、date_format；

两者区别具体参考：
https://blog.csdn.net/it_you_know/article/details/83617761
1.单引号的处理（字符串单引号都可以，双引号Oracle不可以）
单引号：


双引号：


2.自动增长的数据类型处理
oracle不支持自动增长的数据类型，通过建立一个自动增长的序列号来完成自动增长：
①代码中使用到sequence：


②Sequence接口：

③OracleSequence类实现Sequence接口：

public class OracleSequence implements Sequence {
    private final String sequenceName;
    private final String sql;
    private final DataSource dataSource;

    public OracleSequence(String sequenceName,DataSource dataSource){
        this.sequenceName=sequenceName;
        this.sql="SELECT"+sequenceName+".nextval FROM DUAL CONNECT BY LEVEL<=?";
        this.dataSource=dataSource;
    }

    public Long nextValue(){
        return this.nextValues(1).get(0);//当前条记录的"nextValues(1)"下面所有记录集的第1行记录的"get(0)"第一个字段值
    }
    
    public List<Long> nextValues(int n){
        if (n<1){
            throw new IllegalArgumentException("Input parameter must be greater than zero");
        }else {
            Connection con= DataSourceUtils.getConnection(this.dataSource);
            PreparedStatement stmt=null;
            try {
                stmt=con.prepareStatement(this.sql);
                stmt.setInt(1,n);
                ResultSet resultSet=stmt.executeQuery();
                ArrayList seqNumbers=new ArrayList();
                while (resultSet.next()){
                    seqNumbers.add(resultSet.getLong(1));
                }
                ArrayList var6=seqNumbers;
                return var6;
            } catch (SQLException e) {
                JdbcUtils.closeStatement(stmt);
                stmt=null;
                DataSourceUtils.releaseConnection(con,this.dataSource);
                con=null;
                throw new RuntimeException("Failed to generate sequence",e);
            } finally {
                JdbcUtils.closeStatement(stmt);
                DataSourceUtils.releaseConnection(con,this.dataSource);
            }
        }
    }
    
    public String toString(){
        return "sequence:"+this.sequenceName;
    }
}

④SequenceConfig中：

②Oracle自增实现：第一步：创建一个表；第二步：创建一个自增序列以此提供调用函数；第三步：我们通过创建一个触发器，使调用的方式更加简单；
参考： https://js.aizhan.com/data/oracle/7236.html
第一步：创建一个表：

create table Test_Table	(
ID number(11) primary key,
Name varchar(50) not null,
Value1 varchar(50) not null	)

第二步：创建一个自增序列以此提供调用函数：

create sequence AutoID
start with 1 //开始值；根据需要自己可修改该数值
increment by 1 //步长值
minvalue 1
nomaxvalue
Nocycle	//Cycle循环、Nocycle不循环; 
nocache //Cache缓存 nocache不缓存

其实到此步骤，已经可以实现字段自增了。新增记录时看如下代码：
Insert into Test_Table(ID,Name,Value1) values (AutoID.nextval,‘xiyang’,‘blog’)
commit;
select * from Test_Table;

第三步：我们通过创建一个触发器，使调用的方式更加简单：

create trigger AutoID_Trigger

before insert on Test_Table for each row
begin
select AutoID.nextval into :new.ID from dual;
end AutoID_Trigger;

关于序列 sequence：
参考： https://blog.csdn.net/qq_29832217/article/details/87938177
序列: 是oacle提供的用于产生一系列唯一数字的数据库对象。
（例子：select xx_seq.nextval from dual
dual ：是oracle 数据库中的虚拟表，并不是真实存在的；
xx_seq：这个是我们创建序列时自定义的一个序列名称；
xx_seq.nextval：这个是取出序列的下一个值；currval：存放序列的当前值；nextval 应在 currval 之前指定 ，二者应同时有效）
一、创建序列：Create sequence
1.要有创建序列的权限 create sequence 或 create any sequence
2.创建序列的语法:

CREATE SEQUENCE sequence  //创建序列名称
  [INCREMENT BY n]  //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1
  [START WITH n]    //开始的值,递增默认是minvalue 递减是maxvalue
  [{MAXVALUE n | NOMAXVALUE}] //最大值
  [{MINVALUE n | NOMINVALUE}] //最小值
  [{CYCLE | NOCYCLE}] //循环/不循环
  [{CACHE n | NOCACHE}];//分配并存入到内存中

例子：

Create sequence seqEmp increment by 1 start with 1 maxvalue 3 minvalue 1
Cycle cache 2; //Cycle循环、nocycle不循环; Cache缓存 
//先nextval 后 currval
Select seqEmp.nextval  from dual; //nextval下一个值
Select seqEmp.currval  from dual; //currval当前值
Cache<max-min/increment //一般不采用缓存

二、修改序列：Alter sequence：

alter SEQUENCE sequence  //创建序列名称
  [INCREMENT BY n]  //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1
  [START WITH n]    //开始的值,递增默认是minvalue 递减是maxvalue
  [{MAXVALUE n | NOMAXVALUE}] //最大值
  [{MINVALUE n | NOMINVALUE}] //最小值
  [{CYCLE | NOCYCLE}] //循环/不循环
  [{CACHE n | NOCACHE}];//分配并存入到内存中

Alter sequence seqEmp maxvalue 5;
Select seqEmp.nextval from dual;

修改序列的注意事项：
1.必须是序列的拥有者或对序列有 ALTER 权限；
2. 只有将来的序列值会被改变；
3. 改变序列的初始值只能通过删除序列之后重建序列的方法实现；
三、删除序列：Drop sequence
使用DROP SEQUENCE 语句删除序列；
删除之后，序列不能再次被引用；

③Oracle分页：
https://blog.csdn.net/YAn_2018/article/details/86620619

0. 对事务了解多少？

四种事务特性、四种隔离级别、七种传播行为

【1】四种事务特性：ACID
① 原子性：强调事务的不可分割；
② 一致性：事务执行前后数据的完整性保持一致；
③ 隔离性：一个事务执行过程中，不应该受到其他事务的干扰；
④ 持久性：事务一旦结束，数据就持久到数据库中。
（若不考虑隔离性，回引发安全性问题：
① 脏读：一个事务读到另一个事务未提交的数据；
② 不可重复读：一个事务读到另一个事务已提交的 update的数据导致多次查询结果不一致；
③ 幻读：一个事务读到另一个事务已提交的 insert的数据导致多次查询结果不一致）
【2】解决读的问题：设置隔离级别_四种
① 读未提交：脏读、不可重复读、幻读都可能发生；
② 读已提交：避免脏读，但不可重复读、幻读有可能发生；
③ 可重复读：避免脏读、不可重复读，但幻读有可能发生；
④ 串行：避免脏读、不可重复读、幻读；
MySQL 默认的隔离级别是 可重复读，Oracle默认的隔离级别是 读已提交；

DEFAULT：是一个PlatformTransactionManager默认的隔离级别，使用数据库默认的事务隔离级别；
（读未提交 是最低的隔离级别，它允许另一个事务可看到这个事务未提交的数据；读已提交 保证一个事务提交后才能被另一个事务读取，另一个事务不能读取该事务未提交的数据；可重复读 这种隔离级别可防止脏读、不可重复读，但可能出现幻读，它保证了一个事务不能被另一个事务读取未提交的数据外还避免了不可重复读；串行 这种花费最高但最可靠的事务隔离级别，事务被处理为顺序执行，防止了脏读、不可重复读、幻读。 ）
【3】事务的七种传播行为
PROPAGION_XXX：事务的传播行为
一、保证在同一事务中：
PROPAGION_REQUIRED：支持当前事务，如果不存在，就新建一个（默认）；
PROPAGION_SUPPORTS：支持当前事务，如果不存在，就不使用事务；
PROPAGION_MANDATORY：支持当前事务，如果不存在，抛出异常；
二、保证没有在同一事务中：
PROPAGION_REQUIRES_NEW：如果有事务存在，挂起当前事务，创建一个新的事务；
PROPAGION_NOT_SUPPORTED：以非事务运行，如果事务存在，挂起当前事务；
PROPAGION_NEVER：以非事务运行，如果有事务存在，抛出异常；
PROPAGION_NESTED：如果当前事务存在，则嵌套事务执行。

1. 数据库的三范式？

【1】第一范式：
第一范式的目标是确保每列的原子性，每列都是不可再分的最小数据单元；

【2】第二范式：
如果一个关系满足第 1NF，并且除了主键以外的其他列，都完全依赖于该主键，则满足第二范式；第二范式要求每个表只描述一件事情；

【3】第三范式：
如果一个关系满足 2NF，且除了逐渐以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）；第三范式要求表中不存在冗余字段；

2. 说一下 ACID 是什么？

3. char和varchar、float和double的区别？

【1】Char和 Varchar在指定时必须要指定后面的数字（VARCHAR类型的实际长度是它的值的实际长度+1，为什么“+1”呢？这一个字节用于保存实际使用了多大的长度）；
【2】Char固定长度 ，Varchar可变长度；
【3】Char比 Varchar效率高，Varchar比 Char更节省空间；
【4】Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度 ，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错;；Varchar类型如果存入的数据的实际长度比指定的长度小，会缩短到实际长度，如果存入数据的实际长度大于指定长度，低版本会被截取 高版本会报错；
（目前 VARCHAR是 VARCHAR2的同义词。工业标准的 VARCHAR类型可以存储空字符串，但是 oracle不这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型 VARCHAR2，这个类型不是一个标准的VARCHAR，它将在数据库中 varchar列可以存储空字符串的特性改为存储NULL值。如果你想有向后兼容的能力，Oracle建议使用 VARCHAR2而不是VARCHAR）

4. MySQL 的 内连接、左连接、右连接 有什么区别？

4_1. 连接查询、条件查询、联合查询

【一、连接查询】
内连接：
① 相连接两张表地位平等；
② 若一张表在另一张表中不存在对应数据，则不作连接；

select colList from table1 [inner] join table2 on table1.col=table2.col;

//(隐式内连接)
select orders.*,name,age from orders,users where users.id=orders.user_id;

外连接：
① 作连接的两张表地位不平等，其中一张为基础表；
② 基础表中每条数据必须出现，即使另一表中无数据与之匹配，也要用 null补充；
③ 左（右）连接左（右）表是基础表；

select colList from table1 left/right [outer] join table2 on table1.col=table2.col;

【二、子查询】：in / exists

select * from scores where gno in(select gno from game) where name="tom";
select * from scores where exists(select * from users where name="tom");
//查询工资<平均工资的员工号、名字
select id,name from employee where salary<(select avg(salary) from employee);

【三、联合查询】：union / unionall
① 把多条查询语句产生的结果纵向连接为一体；
② 有 all关键字会显示全部数据，重复的也显示；无 all，重复的只显示一次；
③ 列的数量和类型要一致；

select * from users where sex="女" union select *from users where age is null;
结果：
 女 1995
 女 1996
 男 null

【union / unionall 区别】
同： 都是合并结果集；
异： ① union去除两个结果集的重复记录，unionall不能去除重复记录；
② union效率低，unionall效率高。
【limit 的使用】
查询31~40条记录的数据：select * from users limit 30,10;
从表中第一条数据开始，查询三条记录：select * from users limit 3;

5. MySQL 索引是怎么实现的？

MySQL数据库四种类型的索引：普通索引、唯一索引、主键索引、组合索引；
【1】普通索引：是最基本的 Mysql索引，无任何限制；
① 创建索引：（create index on）
create index indexName on mytable(username(length));；
② 修改表结构：（alter mytable add index on）
alter mytable add index [indexName] on(username(length));；
③ 创建表时直接指定：（create table index）

create table mytable(id int not null,
	                 username varchar(16) not null,
	                 index [indexName] (username(length)));

④ 删除索引：（drop index on）
drop index [indexName] on mytable;；
【2】唯一索引：与普通索引类似，不同的是：mysql数据库索引列的值必须唯一，但允许有空值；（若是组合索引，则列值的组合必须唯一）
① 创建索引：（create unique index on）
create unique index indexName on mytable(username(length));；
② 修改表结构：（alter mytable add unique index on）
alter mytable add unique [indexName] on(username(length));；
③ 创建表时直接指定：（create table unique index）

create table mytable(id int not null,
	                 username varchar(16) not null,
	                 unique index[indexName] (username(length)));

【3】主键索引：它是一种特殊的唯一索引，不允许有空值。
①一般在建表的同时创建主键索引：（create table primary key）。也可用 alter命令，一个表只能一个主键。

create table mytable(id int not null,
	                 username varchar(16) not null,
	                 primary key(id));

【4】组合索引
总结： 通常说的索引是B+Tree索引，树状的，搜索需要从根节点出发，上层节点对应靠左的值，如果不从根节点出发，后面的节点对应下层的值，依然是乱序的，需要遍历，所以索引就失效了，所以有最左原则。

组合索引的使用：（例如组合索引（a,b,c），组合索引的生效原则是 ）
① 从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
② abc只要用上了就行，跟写的顺序无关；
③ 范围值也算断点，只不过自身用到了索引；
④ order by排序也用到了索引的效果（但在 order by之前有断点不会发挥排序、索引效果了）；

组合索引详细参考： https://blog.csdn.net/u014590757/article/details/79590561
为形象的对比单列索引和组合索引，为表添加多个字段，例如：

create table mytable(id int not null,
	                 username varchar(16) not null,
	                 city varchar(50) not null,
	                 age int not null);

为进一步榨取 mysql的效率，要考虑建立组合索引，即将 name、city、age建到一个索引中：
aletr table mytable add index name_city_age(name(10),city,age);
建表时，username长度为 16，这里用 10，因为通常名字长度 <10，这样会加速索引查询速度，还会减少索引文件大小，提高 insert的更新速度。若分别在 username、city、age上建立单列索引，让该表有3个单列索引，查询时效率会远低于组合索引，虽然有3个索引，但 mysql只能用到其中那个它认为似乎最有效的单列索引。建立这样的组合索引，其实相当于分别建立了下面三组组合 mysql数据库索引：
username city、age username、city username；为何没有 city age的组合索引呢？：因为 mysql组合索引“最左前缀”的结果，即只从最左面开始组合，并非只要包含这三列的查询都会用到该索引；

下面的几个 sql语句就会用到这个组合 mysql数据库索引：

select * from mytable where username="admin" and city="shanghai";
select * from mytable where username="admin";

而下面的几个则不会用到：

select * from mytable where age=20 and city="shanghai";
select * from mytable where city="shanghai";

B+树（特点）：

总结： ①所有数据都保存在叶子节点且叶子结点本身依关键字的大小自小而大顺序链接，非叶子结点作为叶子结点的索引，搜索总是到叶子结点结束，不可能在非叶子结点命中；  ②有n棵子树的非叶子结点中含有n个关键字(即非叶子结点的子树指针与关键字个数相同)，这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点；  ③同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素；
1. 有n棵子树的非叶子结点中含有n个关键字（b树是n-1个）(即非叶子结点的子树指针与关键字个数相同)，这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。
2. 所有的叶子结点中包含了全部关键字的信息及指向含这些关键字记录的指针(即所有关键字都在叶子结点出现)，且叶子结点本身依关键字的大小自小而大顺序链接（叶子节点组成一个链表）。
3. 同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素。
4. 在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中,不可能在非叶子结点命中；


B-树（B树）（特点）：
（B-tree树即B树，B即Balanced，平衡的意思。因为B树的原英文名称为B-tree，而国内很多人喜欢把B-tree译作B-树，其实，这是个非常不好的直译，很容易让人产生误解。如人们可能会以为B-树是一种树，而B树又是另一种树。而事实上是，B-tree就是指的B树。特此说明。）

关键字分布在整棵树中，任何一个关键字出现且只出现在一个节点中；搜索可能在非叶子节点结束；

1. 关键字分布在整颗树中；
2. 任何一个关键字出现且只出现在一个结点中；
3. 搜索有可能在非叶子结点结束；
4. 所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；

详细参考：
面试题：B（B-）树，B+树，B树和B+树的区别，B树和B+树的优点；
https://blog.csdn.net/yu876876/article/details/84896789
https://blog.csdn.net/qq_35008624/article/details/81947773

6. 怎么验证 MySQL 的索引是否满足需求？

根据查询需求决定配置的索引类型，确定后，在 select语句前加上 explain，使用 explain 来查看执行计划。
（执行结果中主要关注的是，type 和 key：
type = ALL ：表示全表扫描；type = const ：表示通过索引一次就找到了。
key = NULL：表示没有使用索引；key = primary ：表示使用了主键；
key一般=使用了主键/索引的名字）

详细参考： https://blog.csdn.net/u014453898/article/details/55004193
https://www.cnblogs.com/pengyunjing/p/9197430.html

6_1. 索引的优缺点

优点：
提高查询速度；
主键索引或唯一索引能保证数据唯一性；
对于有依赖关系的子表和父表之间的联合查询时，提高表连接速度，提高查询速度；
使用分组和排序子句进行数据查询时，提高分组和排序效率。

缺点：
索引会占用物理空间；
增加、删除和修改数据时，要动态的维护索引，会消耗性能；
创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；

6_2. 索引何时生效、何时不生效

【1】索引何时失效：

（1）组合索引未使用最左前缀；例如组合索引（A，B），where B=b不会使用索引；
（2）模糊查询时 like通配符前置；where A like ‘%China’；
（3）搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引 ；
（4）or会使索引失效（即使用 or且查询字段不同）；如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）
（5）存在 NULL值条件：is null；（字段值存在 null）如：select * from users where id is not null;
（6）NOT条件：查询条件为非时，索引定位就困难了，执行计划此时更倾向于全表扫描，这类的查询条件有：！=(<>)、NOT in、NOT exists；

（7）如果列类型是字符串（即字符串类型的列没使用引号）；要使用引号。例如where A=‘China’，否则索引失效（会进行类型转换）；
（8）在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；

【2】何时使用索引：
MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”，倒不如说，和全表扫描比起来，去分析两个索引 B+树更加耗费时间。所以where A=a and B=b这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。
（1）主键，unique字段；
（2）和其他表做连接的字段需要加索引；
（3）在 where里使用＞，≥，＝，＜，≤，is null和 between等字段；
（4）使用不以通配符开始的 like，where A like ‘China%’；
（5）聚集函数 MIN()，MAX()中的字段；
（6）order by和 group by字段
【3】何时不使用索引：
（1）表记录太少；
（2）数据重复且分布平均的字段（只有很少数据值的列）；
（3）经常插入、删除、修改的表要减少索引；
（4）text，image等类型不应该建立索引，这些列的数据量大（假如text前10个字符唯一，也可以对text前10个字符建立索引）；
（5）MySQL能估计出全表扫描比使用索引更快时，不使用索引；

详细参考：
参考一： https://blog.csdn.net/weixin_39420024/article/details/80040549
参考二： https://blog.csdn.net/weixin_39420024/article/details/80040549

6_3. 索引碎片

① 查询所有带有碎片语句：show table status like 'aa'；（aa表示带有索引的表名）；查询结果中 Data_free值大于0，表示有索引碎片产生；
② 可以使用如下方式清理碎片：
方法一：通过一个 nop操作（不产生对数据实质影响的操作）来修改表，如：表的引擎为 innodb，可以 alter table xx engine innodb；，修改表的引擎为默认引擎会重新调整数据，但不会影响数据；
方法二：使用 optimize table xx，来进行修复；
③ 再去查索引碎片：show table status like 'aa'，Data_free变为0；

参考： https://www.cnblogs.com/YasinXiao/p/7736400.html
https://www.cnblogs.com/qlqwjy/p/8594859.html

7. MySQL 的常用引擎

8. MySQL 的行锁和表锁

表锁：开销小，锁表快，高并发下性能低；
行锁：开销大，锁表慢，高并发下性能高；

9. 乐观锁和悲观锁

乐观锁： 十分乐观，总是认为不会出现问题，无论干什么都不去上锁，如果出现问题，在此更新测试；
悲观锁： 十分悲观，认为总是出现问题，无论干什么都会上锁，在去操作；
乐观锁的实现方式：
1.取出记录时，获取当前版本；
2.更新时，带上这个version；
3.执行更新时，set version=newVersion where version=oldversion
4.如果version不对，就更新失败;
eg：
(1.先查询获取版本号为version=1;2.更新时,id条件后带上这个version;3.执行更新时把version由原来的版本改为最新的版本,一般会自动+1;4.如果version不对，就更新失败)
-----A(线程)
update user set name=‘lala’,version=version+1
where id=2 and version=1

-----B(线程)抢先于A线程完成，此时version=2了，会导致A线程修改失败！
update user set name='lala',version=version+1
where id=2 and version=1

测试MP(mybatis plus)的乐观锁插件
1.表中新增version字段,并设置默认值都为1;
2.实体类加对应的字段version,并在字段上添加@version注解(mybatis plus的乐观锁的version注解);
3.注册组件：新建config包,创建MyBatisPlusConfig配置类：
①在类上加@Confguration注解表示这是一个配置类;(有事务的控制还可以加上一个@EnableTransactionManagement注解自动管理事务);
若在此配置类上管理mybatis plus,则可以将启动类上的包扫描(是交给mybatis做的),不放在启动类上了,放在此配置类上;
②类中注册乐观锁插件;
此时乐观锁插件配置完毕;
4.测试类中进行测试;

乐观锁： 每次拿数据的时候都认为别人不会修改数据，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据；
乐观锁需要自己实现，在表中添加一个 version字段，每次修改成功值加 1，这样每次修改的时候先对比一下自己的 version跟数据库现在的 version是否一致，如果不一致就不修改，这样就实现了乐观锁；
悲观锁： 每次拿数据的时候都认为别人会修改，所以每次拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放；悲观锁的特点是先获取锁，再进行业务操作，通常所说的“一锁二查三更新”即指的是使用悲观锁，通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁，当数据库执行select for update时会获取被select中的数据行的行锁，因此其他并发执行的select for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果；select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。
这里需要注意的一点是不同的数据库对select for update的实现和支持都是有所区别的，例如oracle支持select for update no wait，表示如果拿不到锁立刻报错，而不是等待，mysql就没有no wait这个选项。另外mysql还有个问题是select for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在mysql中用悲观锁务必要确定走了索引，而不是全表扫描；

总结： 乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能；（乐观锁还适用于一些比较特殊的场景，例如在业务操作过程中无法和数据库保持连接等悲观锁无法适用的地方）

详细参考： https://blog.csdn.net/qq_27469747/article/details/79741490

10. 如何做 MySQL 的性能优化？

（1）创建索引； 选择正确的存储引擎；
（2）日志和数据分开存储，减小 IO压力；
（3）模糊查询%通配符后置，前置导致全表扫描：
select id from t where name like ‘%abc%’ 或者
select id from t where name like ‘%abc’ 或者
若要提高效率，可以考虑全文检索。
而select id from t where name like ‘abc%’ 才用到索引；
（4）应尽量避免在 where 子句中使用 or 来连接条件，可以 使用 union合并查询结果集： select id from t where num=10 union all select id from t where num=20；
（5） 应尽量避免在 where 子句中对字段进行 null 值判断（is null，is notnull），否则将导致引擎放弃使用索引而进行全表扫描；
（6）应尽量避免在 where 子句中使用 ！=(<>)、in、NOT in操作符，否则引擎将放弃使用索引而进行全表扫描；
（7）很多时候用 exists 代替 in 是一个好的选择：
select * from sys_user s where exists (select 1 from runoob_tbl rb where runoob_id = s.id)；
（8）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描；
（9）避免使用 select *，列出需要查询的字段；

11. 如何获取当前数据库版本？

12. MySQL 问题排查都有哪些手段？

12_1.说一些数据库优化方面的经验？

① 程序优化，用 PrepareStatement 进行增删改查；
② 查询结果不要用 *，要明确指明结果字段；
③ 减少多表连接数，使用尽量少的表进行连接；表连接时，尽量用主键连接或用唯一索引；

1.创建索引
要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引；在经常需要进行检索的字段上创建索引；
避免在索引上使用表达式操作
2.选择正确的存储引擎
4.(查询select语句优化)select 语句列出需要查询的字段，不用*
5.调整where子句连接顺序，表连接最好写在其他where条件之前，那些可以过滤掉最大数量记录数的条件必须写在where子句的末尾(因为DBMS一般采用自下而上的顺序解析where字句)
6.尽量避免在where子句中使用or来连接条件；
尽量避免在where子句中进行null值判断；
尽量避免在where子句中使用 !=(<>) not in not exists；
用union all替换union，使用exists代替in；
模糊查询 %后置；
7.使用表的别名
8.尽量将多条SQL语句压缩到一句SQL中
9.使用预编译查询

12_2.web响应优化？

1.使用CDN，cdn可以优化网站访问速度
2.合并静态资源(减少HTTP请求)
3.把多个css合并为一个css，把图片组合成雪碧图
4.把css放页面头部，js放底部(让浏览器尽快下载CSS渲染页面，不会让页面出现长时间的空白)
5.对于较大的文本资源，必须开启gzip压缩
6.延长静态资源缓存时间
CDN（contentdistribute network，内容分发网络）的本质仍然是一个缓存，而且将数据缓存在离用户最近的地方，使用户以最快速度获取数据，即所谓网络访问第一跳，如下图。
由于CDN部署在网络运营商的机房，这些运营商又是终端用户的网络服务提供商，因此用户请求路由的第一跳就到达了CDN服务器，当CDN中存在浏览器请求的资源时，从CDN直接返回给浏览器，最短路径返回响应，加快用户访问速度，减少数据中心负载压力。
CDN缓存的一般是静态资源，如图片、文件、CSS、script脚本、静态网页等，但是这些文件访问频度很高，将其缓存在CDN可极大改善网页的打开速度。

13. MySQL 中表删除数据时，id值变化情况？

14. MySQL的存储过程

① 创建存储过程：CREATE PROCEDURE 过程名（参数）

② 执行存储过程：CALL 过程名（参数）

③ 删除存储过程：DROP PROCEDURE 过程名

④ 显示某个存储过程信息 ：SHOW CREATE PROCEDURE 过程名

⑤ 查看数据库中存在的所有存储过程：SHOW PROCEDURE STATUS

_在创建存储过程时，必须具有 CREATE ROUTINE 权限

_使用 DELIMITER 命令将结束命令修改为其他字符；

【注释】：MySQL存储过程可使用两种风格的注释：
双杠：–，该风格一般用于单行注释
C风格： 一般用于多行注释

1.存储过程是什么？

简单来说就是为方便以后的使用而保存的一条或者多条MySQL语句的集合；
MySQL的存储过程：存储过程是数据库的一个重要的功能，MySQL 5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

2.为什么使用存储过程？

（1）存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
（2）提高性能，因为存储过程比单独执行的SQL语句执行的要快。高性能
较快的执行速度，如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
（3）简化对变动的管理，如果表名，列名或者其他的内容需要更改直接改存储过程就可以了，别人甚至不需要直到里面的内容。方便

3.存储过程的缺陷？

（1）普通用户没有存储过程的安全访问权限，需要管理员进行设置创建存储过程的权限，允许用户使用存储过程，而不是创建存储过程。
（2）一般来说，存储过程的编写更加复杂，需要更高的技能。更丰富的经验。

4.如何执行存储过程？（CALL 过程名（参数））

执行的语句是CALL，示例：
CALL productpricing(@pricelow,@pricehigh,@priceaverage);
其中，执行的是名为producpricing的存储过程；计算并返回价格的最高，最低和平均值。可以显示结果，也可以不显示结果。

5.如何创建存储过程？（CREATE PROCEDURE 过程名（参数））语法：CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 …] 过程体

示例：

CREATE PROCEDURE productpricing()      //括号里如果有参数可以列出；
BEGIN     //这是存储过程的主体，由一个select语句构成；
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;

mysql> DELIMITER //
mysql> CREATE PROCEDURE ShowStuScore()
    -> BEGIN
    -> SELECT * FROM tb_students_score;
    -> END //
Query OK， 0 rows affected (0.09 sec)

DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

DELIMITER $
  CREATE PROCEDURE pro_test()
    BEGIN
      SELECT * FROM student WHERE id = 1;
      SELECT * FROM student WHERE id = 3;
    END $

【过程名】：存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。
【分隔符】：MySQL默认以";“为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个”//“之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用”,"分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:
【过程参数】：存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数；
      MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。需要注意的是，参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。
     IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值；OUT：该值可在存储过程内部被改变，并可返回；INOUT：调用时指定，并且可被改变和返回
【过程体】：过程体的开始与结束使用BEGIN与END进行标识。存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。
      在存储过程的创建中，经常会用到一个十分重要的 MySQL 命令，即 DELIMITER 命令，特别是对于通过命令行的方式来操作 MySQL 数据库的使用者，更是要学会使用该命令。
      在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。为解决这个问题，通常可使用 DELIMITER 命令将结束命令修改为其他字符。语法格式：DELIMITER $$；语法说明：$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。
      在 MySQL 命令行客户端输入如下SQL语句：mysql > DELIMITER ??；成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了。
      若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可：mysql > DELIMITER ;；注意：DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时，必须具有 CREATE ROUTINE 权限。可以使用 SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程，若要查看某个存储过程的具体信息，则可以使用 SHOW CREATE PROCEDURE <存储过程名>。
【① IN参数例子】

-- (1)带有输入参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_test1(IN sid INT)
BEGIN
    SELECT * FROM student WHERE id=sid;
END $
-- 定义会话变量
SET @sid = 2;
-- 调用存储过程
CALL pro_test1(@sid);

DELIMITER //
  CREATE PROCEDURE in_param(IN p_in int)
    BEGIN
    SELECT p_in;
    SET p_in=2;
    SELECT p_in;
    END;
    //
DELIMITER ;

#调用
SET @p_in=1;
CALL in_param(@p_in);

SELECT @p_in;

执行结果可以看出，p_in虽然在存储过程中被修改，但并不影响@p_in的值

【② OUT参数例子】

-- (2)带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_test2(OUT str VARCHAR(20))
BEGIN
    -- 修改str的值
    SET str = '输出参数';
END $
-- 定义会话变量
-- set @s='';
-- 调用存储过程
CALL pro_test2(@s);
-- 查询会话变量的值
SELECT @s;

#存储过程OUT参数
DELIMITER //
  CREATE PROCEDURE out_param(OUT p_out int)
    BEGIN
      SELECT p_out;
      SET p_out=2;
      SELECT p_out;
    END;
    //
DELIMITER ;
#调用
SET @p_out=1;
CALL out_param(@p_out);

SELECT @p_out;

【③ INOUT参数例子】

-- (3)带有输入输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_test3(INOUT m INT)
BEGIN
      -- 查看变量的值
      SELECT m;
      -- 修改变量的值
      SET m = 999;
END $
-- 定义会话变量
SET @money = 100;
-- 调用存储过程
CALL pro_test3(@money);
-- 查询变量的值
SELECT @money;

#存储过程INOUT参数
DELIMITER //
  CREATE PROCEDURE inout_param(INOUT p_inout int)
    BEGIN
      SELECT p_inout;
      SET p_inout=2;
      SELECT p_inout;
    END;
    //
DELIMITER ;
#调用
SET @p_inout=1;
CALL inout_param(@p_inout) ;
SELECT @p_inout;

-- (4)将查询的结果赋值给输出参数
DELIMITER $
CREATE PROCEDURE pro_test4(IN sid INT,OUT sname VARCHAR(10))
BEGIN
    SELECT NAME INTO sname FROM student WHERE id=sid;
END $
-- 定义会话变量
SET @sid = 4;
-- 调用存储过程
CALL pro_test4(@sid,@sname);
-- 查询会话变量的值
SELECT @sname;

-- (5)带有条件判断的存储过程
-- 需求:编写一个存储过程，当n=1，输出'星期一'，
-- 当n=2，输出'星期二'
-- 当n=3，输出'星期三'，其他情况输出'其他情况'
DELIMITER $
CREATE PROCEDURE pro_test5(IN n INT,OUT str VARCHAR(20))
BEGIN
    IF n=1 THEN
        SET str = '星期一';
    ELSEIF n=2 THEN
        SET str = '星期二';
    ELSEIF n=3 THEN
        SET str = '星期三';
    ELSE 
        SET str = '其他情况';
    END IF;
END $
-- 定义会话变量
SET @n = 7;
-- 调用存储过程
CALL pro_test5(@n,@str);
-- 查询变量的值
SELECT @str;

6.如何删除存储过程？（DROP PROCEDURE 存储名）

注意 ：所有的MySQL变量都以@开头。

执行下列语句：DROP PROCEDURE productpricing;

7.显示所有的存储过程信息 （SHOW CREATE PROCEDURE 存储名）

SHOW CREATE PROCEDURE ordertotal;

14. where、gruop by、having、order by

where、having： 都是查询条件；但 having子句是统计结果（大小、平均数、总数、个数）做条件；
group by、order by： group by 把查询结果分组，order by 把查询结果排序；

14_1. Mysql常用函数

聚合函数：
sum（）：求和；
avg（）：求平均值；
min（）：最小值；
max（）：最大值；
count（）：统计记录行的数量；
数学函数：（Mysql、Oracle都常用这些）
ceil（）：向上取整；
floor（）：向下取整；
abs（）：取绝对值；
round（）：四舍五入；… …等等

select sum(score) as '总分' from scores where name='tom';
select avg(score) as '平均分' from scores where name='tom';
select min(score) as '最低分' from scores where gno=1;

select count(name) from users;//具体某一列的行数,如果name列中有一行值为空,则值为总行数-1
select count(*) from users;//查询总的行数(记录数),包括某列中值为空的一行

14. 数据库题目

一、用两种方式根据部门号降序，工资升序列出每个员工的信息：
select * from employee order by dep_id desc,salary(asc);
二、列出各个部门中工资高于本部门平均工资的员工数、部门号，并按部门号排序：

select count(*),a.dep_id from employee a a.salary>
                         (select avg(b.salary) from employee b where b.dep_id="本部门")
                         group by a.dep_id order by a.dep_id;

三、用一条 sql语句查询每门课分数都 >80分的学生姓名：

name  kechneg  fenshu
tom   语文     81
tom   数学     75
jay   语文     76
jay   数学     90

select name from student group by name having min(score)>80;

四、所有部门之间的比赛组合：一个表中只有一个 name字段，一共四条记录，分别对应 a、b、c、d 四个球队，对四个球队进行比赛，用一条 sql语句显示所有可能的比赛内容：

    name
1   a
2   b
3   c
4   d

    a.name   b.name
1   d        c
2   b        b
3   d        a
4   c        b
5   c        d
6   c        a

select a.name,b.name from team a,team b where a.name>b.name;

五、统计每年每月信息，将表1内容换为表2内容显示：

year   month   amount
1991   1       1.1
1991   2       1.2
1991   3       1.3
1991   4       1.4
1992   1       2.1
1992   2       2.2
1993   3       2.3
1994   4       2.4

year   m1   m2   m3   m4
1991   1.1  1.2  1.3  1.4
1992   2.1  2.2  2.3  2.4

select s.year,
       (select t.amount from sales t where t.month='1' and t.year=s.year)m1,
       (select t.amount from sales t where t.month='2' and t.year=s.year)m2,
       (select t.amount from sales t where t.month='3' and t.year=s.year)m3,
       (select t.amount from sales t where t.month='4' and t.year=s.year)m4
from sales s group by s.year;