Java工程师的进阶之路
目录
❄️1.数据库三大范式是什么？❄️2.mysql有关权限的表都有哪几个?❄️3.SQL中聚合函数有哪些？❄️4.SQL之连接查询（左连接和右连接的区别）？❄️5.SQL之sql注入是什么？❄️6.防止sql注入的方式？❄️7.MySQL性能优化有哪些？❄️8.事务的四大特征是什么？❄️9.MySQL中四种隔离级别分别是什么？❄️10.视图有哪些特点？❄️11.SQL的生命周期？❄️12.什么是脏读？幻读？不可重复读？❄️13.MySQL 数据库架构图了解吗？❄️14.MySQL架构器中各个模块都是什么？❄️15.MySQL事务介绍？❄️16.MySQL怎么创建存储过程?❄️17.MySQL触发器怎么写？
❄️1.数据库三大范式是什么？
第一范式：每个列都不可以再拆分。第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由，比如性能。事实上我们经常会为了性能而妥协数据库的设计。
❄️2.mysql有关权限的表都有哪几个?
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，
由mysql_install_db脚本初始化。
这些权限表分别user，db，table_priv，columns_priv和host。
user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。db权限表：记录各个帐号在各个数据库上的操作权限。table_priv权限表：记录数据表级的操作权限。columns_priv权限表：记录数据列级的操作权限。host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。
这个权限表不受GRANT和REVOKE语句的影响。
❄️3.SQL中聚合函数有哪些？
聚合函数是对一组值进行计算并返回单一的值的函数，它经常与select语句中的group by子句一同使用。
avg()：返回的是指定组中的平均值，空值被忽略。count()：返回的是指定组中的项目个数。max()：返回指定数据中的最大值。min()：返回指定数据中的最小值。sum()：返回指定数据的和，只能用于数字列，空值忽略。
❄️4.SQL之连接查询（左连接和右连接的区别）？
左连接（左外连接）：以左表作为基准进行查询，左表数据会全部显示出来，右表如果和左表匹配的数据则显示相应字段的数据，如果不匹配则显示为null。右连接（右外连接）：以右表作为基准进行查询，右表数据会全部显示出来，左表如果和右表匹配的数据则显示相应字段的数据，如果不匹配则显示为null。全连接：先以左表进行左外连接，再以右表进行右外连接。内连接：显示表之间有连接匹配的所有行。
❄️5.SQL之sql注入是什么？
通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。举例：当执行的sql为select * from user where username = “admin” or “a” = “a”时，sql语句恒成立，参数username毫无意义。
❄️6.防止sql注入的方式？
预编译语句：如，select * from user where username = ？，sql语句语义不会发生改变，sql语句中变量用?表示，即使传递参数时为“admin or ‘a’ = ‘a’”，也会把这整体当做一个字符创去查询。
Mybatis框架中的mapper方式中的#也能很大程度的防止sql注入（$无法防止sql注入）。
❄️7.MySQL性能优化有哪些？
1、当只要一行数据时使用limit 1
查询时如果已知会得到一条数据，这种情况下加上limit 1会增加性能。因为MySQL数据库引擎会在找到一条结果停止搜索，而不是继续查询下一条是否符合标准直到所有记录查询完毕。
2、选择正确的数据库引擎
MySQL中有两个引擎MyISAM和InnoDB，每个引擎有利有弊。MyISAM适用于一些大量查询的应用，但对于有大量写功能的应用不是很好。甚至你只需要update一个字段整个表都会被锁起来。而别的进程就算是读操作也不行要等到当前update操作完成之后才能继续进行。另外，MyISAM对于select count(*)这类操作是超级快的。InnoDB的趋势会是一个非常复杂的存储引擎，对于一些小的应用会比MyISAM还慢，但是支持“行锁”，所以在写操作比较多的时候会比较优秀。并且，它支持很多的高级应用，例如：事务。
3、用not exists代替not in
not exists用到了连接能够发挥已经建立好的索引的作用，not in不能使用索引。not in是最慢的方式要同每条记录比较，在数据量比较大的操作红不建议使用这种方式。
4、对操作符的优化，尽量不采用不利于索引的操作符
如：in、not in、is null、is not null 、<> 等某个字段总要拿来搜索，为其建立索引：MySQL中可以利用alter table语句来为表中的字段添加索引，语法为：alter table表名add index(字段名)
❄️8.事务的四大特征是什么？
数据库事务transanction正确执行的四个基本要素。ACID，原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
❄️9.MySQL中四种隔离级别分别是什么？
读未提交（READ UNCOMMITTED）：未提交读隔离级别也叫读脏，就是事务可以读取其它事务未提交的数据。
读已提交（READ COMMITTED）：在其它数据库系统比如SQL Server默认的隔离级别就是提交读，已提交读隔离级别就是在事务未提交之前所做的修改其它事务是不可见的。
可重复读（REPEATABLE READ）：保证同一个事务中的多次相同的查询的结果是一致的，比如一个事务一开始查询了一条记录然后过了几秒钟又执行了相同的查询，保证两次查询的结果是相同的，可重复读也是MySQL的默认隔离级别。
可串行化（SERIALIZABLE）：可串行化就是保证读取的范围内没有新的数据插入，
❄️10.视图有哪些特点？
视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。视图是由基本表(实表)产生的表(虚表)。 视图的建立和删除不影响基本表。对视图内容的更新(添加，删除和修改)直接影响基本表。当视图来自多个基本表时，不允许添加和删除数据。视图的操作包括创建视图，查看视图，删除视图和修改视图。
❄️11.SQL的生命周期？
- 应用服务器与数据库服务器建立一个连接数据库进程拿到请求sql解析并生成执行计划，执行读取数据到内存并进行逻辑处理通过步骤一的连接，发送结果到客户端关掉连接，释放资源
❄️12.什么是脏读？幻读？不可重复读？
脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。不可重复读(Non-repeatable read)：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。幻读(Phantom Read)：在一个事务的两次查询中数据笔数不一致，
例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
❄️13.MySQL 数据库架构图了解吗？
MyISAM和InnoDB是最常见的两种存储引擎，特点如下。
1、MyISAM存储引擎
MyISAM是MySQL官方提供默认的存储引擎，其特点是不支持事务、表锁和全文索引，对于一些OLAP（联机分析处理）系统，操作速度快。
每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)。这里特别要注意的是MyISAM不缓存数据文件，只缓存索引文件。
2、InnoDB存储引擎
InnoDB存储引擎支持事务，主要面向OLTP（联机事务处理过程）方面的应用，其特点是行锁设置、支持外键，并支持类似于Oracle的非锁定读，即默认情况下读不产生锁。InnoDB将数据放在一个逻辑表空间中（类似Oracle）。
InnoDB通过多版本并发控制来获得高并发性，实现了ANSI标准的4种隔离级别，默认为Repeatable，使用一种被称为next-keylocking的策略避免幻读。
对于表中数据的存储，InnoDB采用类似Oracle索引组织表Clustered的方式进行存储。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。以下是InnoDB体系架构：
❄️14.MySQL架构器中各个模块都是什么？
1、连接管理与安全验证是什么
每个客户端都会建立一个与服务器连接的线程，服务器会有一个线程池来管理这些连接；如果客户端需要连接到MYSQL数据库还需要进行验证，包括用户名、密码、主机信息等。
2、解析器是什么
解析器的作用主要是分析查询语句，最终生成解析树；首先解析器会对查询语句的语法进行分析，分析语法是否有问题。还有解析器会查询缓存，如果在缓存中有对应的语句，就返回查询结果不进行接下来的优化执行操作。前提是缓存中的数据没有被修改，当然如果被修改了也会被清出缓存。
3、优化器怎么用
优化器的作用主要是对查询语句进行优化操作，包括选择合适的索引，数据的读取方式，包括获取查询的开销信息，统计信息等，这也是为什么图中会有优化器指向存储引擎的箭头。之前在别的文章没有看到优化器跟存储引擎之间的关系，在这里我个人的理解是因为优化器需要通过存储引擎获取查询的大致数据和统计信息。
4、执行器是什么
执行器包括执行查询语句，返回查询结果，生成执行计划包括与存储引擎的一些处理操作。
❄️15.MySQL事务介绍？
MySQL和其它的数据库产品有一个很大的不同就是事务由存储引擎所决定，例如MYISAM，MEMORY，ARCHIVE都不支持事务，事务就是为了解决一组查询要么全部执行成功，要么全部执行失败。MySQL事务默认是采取自动提交的模式，除非显示开始一个事务。
SHOW VARIABLES LIKE 'AUTOCOMMIT';
修改自动提交模式，0=OFF，1=ON，注意：修改自动提交对非事务类型的表是无效的，因为它们本身就没有提交和回滚的概念，还有一些命令是会强制自动提交的，比如DLL命令、locktables等。
SET AUTOCOMMIT = 0;
❄️16.MySQL怎么创建存储过程?
MySQL存储过程是从MySQL5.0开始增加的新功能。存储过程的优点有一箩筐。不过最主要的还是执行效率和SQL代码封装。特别是SQL代码封装功能，如果没有存储过程，在外部程序访问数据库时，要组织很多SQL语句。特别是业务逻辑复杂的时候，一大堆的SQL和条件夹杂在代码中，让人不寒而栗。现在有了MySQL存储过程，业务逻辑可以封装存储过程中，这样不仅容易维护，而且执行效率也高。
1、创建MySQL存储过程（procedure）
创建一个叫 add 的存储过程，有两个int类型的输入参数，返回这两个参数的和。
# 如果存在pr_add的存储过程，则先删掉 drop procedure if exists pr_add; create procedure add (a int,b int) begin declare c int; if a is null then set a = 0; end if; if b is null then set b = 0; end if; set c = a + b; select c as sum;
2、调用 MySQL 存储过程
call add(10， 20);
❄️17.MySQL触发器怎么写？
MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
在MySQL中，创建触发器语法如下：
CREATE TRIGGER name time event ON tbl_name FOR EACH ROW trigger_stmt
name：标识触发器名称，用户自行指定；time：标识触发时机，取值为BEFORE或AFTER；event：标识触发事件，取值为INSERT、UPDATE或DELETE；tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；trigger_stmt：触发器程序体，可以是一句SQL语句，或者用BEGIN和END包含的多条语句。
有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个。
BEFOREINSERT、BEFOREUPDATE、BEFOREDELETE、AFTERINSERT、AFTERUPDATE、AFTERDELETE
假设系统中有两个表：
● 班级表class(班级号c_id，班内学生数stu_count)
● 学生表student(学号stu_id，所属班级号c_id)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下
create trigger tri_stu_insert after insert on student for each row begin declare c int; set c = (select stu_count from class where c_id = new.c_id); update class set stu_count = c + 1 where c_id = new.c_id; end
查看触发器
SHOW TRIGGERS FROM [db_name];
删除触发器
DROP TRIGGER [IF EXISTS] [db_name] trigger_name;
