《JavaEE》第八周day2学习笔记-数据库高级

一、事务

事务：Transaction，是逻辑上的一组操作，在SQL中为一组语句。
事务相关语句：START TARNSACTION |BEGIN（启动事务）、COMMIT（提交事务）、ROLLBACK（回滚事务）。

（一）事务的特点

---

原子性（Atomicity）：事务中的操作只有‘全部完成’，‘都未完成’两种状态，如果执行过程中出现Error将回滚至事务执行前的状态；
一致性（Consistency）：事务操作完成后数据库状态应与预期目标一致（由原子性、业务逻辑和不可预知错误共同影响）；
隔离性（Isolation）：防止多个事务的并发执行导致数据的不一致，包括读未提交、读提交、可重复读和串行化四种级别；
持久性（Durability）：事务操作结束后，对数据的修改是永久的，系统故障也不会对其造成影响。

（二）事务隔离级别

---

（三）事务常见异常

---

1.脏读
A事务中读取到B事务未提交的变更信息

2.不可重复读
A事务中读取到B事务已提交的变更信息（更新）

3.幻读（例子可能不太准确）
A事务受到B事务已提交信息的影响（插入、修改）

（四）JDBC相关操作

---

conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);//设置隔离级别为可重复读
connection.setAutoCommit(false);//关闭自动提交功能（相当于启动事务）
connection.commit(); //提交
connection.rollback(); //回滚
执行过程：将数据库操作语句置于启动事务语句及提交语句之间，捕捉到异常则调用回滚语句。

二、视图

（一）视图概念

---

视图，即数据库虚拟表，方便用户对多张表联动操作。视图具有以下优势：
1.简化操作；
2.增加数据的安全性，视图只能查询和修改给定的数据；
3.提升表独立性，原有表格的增加删除不会影响视图。

（二）视图操作

---

1.创建视图

create or replace [algorithm = {undefined|merge|temptable}] view '视图名称' as '查询语句' [with check option];

algorithm：视图创建方式
undefined：未定义（MySQL倾向于merge方式）
merge：替换方式，将视图构建语句与视图查询语句进行整合替换
temptable：临时表方式，将产生的视图结果生成临时表
with check option：更新视图时必须在视图观察权限范围内
2.select * from ‘视图名称’;-- 查询视图
3.update ‘视图名称’ set 字段名1=值1 where 字段名2=值2;-- 更新视图
4.drop view ‘视图名称’;-- 删除视图

三、触发器

（一）触发器概念

---

Trigger，由事件（非系统调用及DBA操作）触发的数据库操作，MySQL5.x触发器每张表只能存在一个。
触发器的特点
1.可维护数据库的安全性、一致性和完整性；
2.可在写入数据前，检查或转换数据；
3.触发器错误时，异常的结果会被撤销；

（二）创建触发器

---

create trigger '触发器名称' after|before insert|update|delete on 数据表名称（触发点）
for each row
begin
    SQL（触发后执行的SQL语句）;
end;

注意，SQL语句中可以通过new、old关键字调用触发点触发后、前的值。

（三）删除触发器

---

drop trigger ‘触发器名称’;

（四）查询触发器

---

方法1：select * from information_schema.`TRIGGERS`;
方法2：show triggers;

四、索引

索引是对数据库表格的一列或多列值排序的数据结构，类似于图书目录，是通过牺牲空间换取时间的方式，本质是BTree（一个表的索引建议不超过6个）。
优点：提高数据的查询（检索）速度；
缺点：创建维护索引消耗时间，减慢写入速度。

（一）索引类型

---

普通索引（单列索引）
复合索引（多列组合索引）
唯一索引
主键索引
全文索引

（二）创建索引

---

CREATE TABLE table_name [col_name data type] [unique|fulltext] 
[index|key] [index_name] (col_name[length]) [asc|desc]

unique | fulltext：唯一索引、全文索引
index | key：同义词
col_name：索引字段名
index_name：索引名称，默认为col_name
length：索引长度（仅字符串类型适用）
asc | desc：升序、降序存储索引

查看索引：show indexes from ‘表名’；show keys from ‘表名’；
删除索引：alter table ‘表名’ frop index ‘索引名’；
1.普通索引（单列索引）
创建索引1：CREATE INDEX index_name ON table_name(col_name);
创建索引2：ALTER TABLE table_name ADD INDEX index_name(col_name);
创建索引3：

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL,
    PRIMARY KEY (`id`),
    INDEX index_name (title(255))
);

删除索引1：DROP INDEX index_name ON table_name;
删除索引2：alter table 表名 drop index 索引名;
2.复合索引（多列组合索引）
复合索引遵循“最左前缀”原则，即查询条件中存在第一个字段时，索引才会启用。
创建索引：create index index_name on table_name(col_name1,col_name2,…);
3.唯一索引
唯一索引限制索引字段内容必须唯一（不存在重复），允许且只允许存在一个null；
创建索引1：CREATE UNIQUE INDEX index_name ON table_name(col_name);
创建索引2：CREATE UNIQUE INDEX index_name ON table_name(col_name,...);
创建索引3：

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    UNIQUE index_name_unique(title)
)

4.主键索引
主键索引与表的主键相关，一般在创建数据库表的同步创建。
创建索引：

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`)
)

5.全文索引
全文索引使用FULLTEXT关键字，全表查询匹配的数据（通常使用like进行模糊查询，因此数据量大的表容易造成性能问题）
注意，“先写数据再加索引”比“先加索引再写数据”快！
创建索引1：

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` text  NOT NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
     PRIMARY KEY (`id`),
    FULLTEXT (content)
)

创建索引2：

CREATE FULLTEXT INDEX index_fulltext_content ON table_name(col_name)

注意，MySQL不支持中文全文索引，通常使用全文搜索引擎ElasticSearch或Solr。

五、存储过程

存储过程是可编程的函数组，可以封装隐藏复杂的业务逻辑，可以接受参数并返回值；但存储过程与数据库系统相关，如果切换至其他数据库系统需要重写存储过程，存储过程支持递归调用。

（一）创建存储过程

---

CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体
DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

注意，DELIMITER //…// DELIMITER修饰的代码将被编译器解释为存储过程代码。
IN参数：调用存储过程时指定，仅供内部使用
OUT参数：内部使用后，可以返回值至存储过程外部
INOUT参数：调用存储过程时指定，可以返回值至存储过程外部

（二）调用存储过程

---

call ‘存储过程名称’(实参列表);

（三）查询存储过程

---

查询方式1：select name from mysql.proc where db=’数据库名’;
查询方式2：show procedure status where db=’数据库名’;
查询方式3（创建语句）：SHOW CREATE PROCEDURE 数据库.存储过程名;

六、变量及语句

（一）变量

---

变量声明：DECLARE variable_name [,variable_name…] datatype [DEFAULT value];
datatype 为MySQL默认类型。
变量赋值：SET 变量名 = 表达式值 [,variable_name = expression …]
@变量名←用户变量，无需声明即可使用。

（二）条件语句

---

1.if-then-else语句

if parameter=0 then 
update t set s1=s1+1;  
else 
update t set s1=s1+2;  
end if;

2.case语句

case var  
when 0 then   
insert into t values(17);  
when 1 then   
insert into t values(18);  
else   
insert into t values(19);  
end case; 

（三）循环语句

---

1.while-end while

 while var<6 do  
insert into t values(var);  
set var=var+1;  
end while;  

2.repeat-end repeat

repeat  
insert into t values(v);  
set v=v+1;  
until v>=5  
end repeat;  

3.loop-end loop

LOOP_LABLE:loop  
insert into t values(v);  
set v=v+1;  
if v >=5 then 
leave LOOP_LABLE;  
end if;  
end loop; 

4.lables标识
LOOP_LABLE:←为一种lables标识，通常用来作为leave的靶目标以离开循环。

七、函数

MySQL提供内置函数功能，支持创建自定义函数，注意，MySQL5.x版本自定义函数不支持递归调用！

（一）创建自定义函数

---

创建函数1：CREATE FUNCTION ‘函数名’ ( [func_parameter] ) RETURNS type [ characteristic …] routine_body
创建函数2：
create function ‘函数名’()
returns varchar (255)
begin
return ‘一个简单的mysql函数’;
end;

（二）操作函数

---

删除函数：drop function if exists ‘函数名’;
调用函数：select ‘函数名’();

（三）内置函数

---

参考博客：https://blog.csdn.net/Mrtwofly/article/details/53939400

八、三大范式

• 第一范式（1NF）：在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。
• 第二范式（2NF）：满足1NF，且数据库表中的每个实例或记录必须可以被唯一地区分，实体的属性完全依赖于主关键字。
• 第三范式（3NF）：满足2NF，且一个关系中不包含已在其它关系已包含的非主关键字信息（消除依赖传递）。
• 其他：BCNF、4NF、5NF。

附：客户端与数据库交互过程示意图