一、事务
事务: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。