MySQL之事务&索引&函数

最新推荐文章于 2024-07-22 14:36:59 发布

Williamsgogogo

最新推荐文章于 2024-07-22 14:36:59 发布

阅读量101

点赞数 1

分类专栏：数据库文章标签： mysql 数据结构

本文链接：https://blog.csdn.net/tysearch/article/details/118659299

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一、事务

1.1 事务概述

事务:如果一个业务包含多个步骤的操作,那么这些操作要么同时成功,要么同时失败。

例如转账业务,包含扣钱操作和加钱操作。

1.2 事务原理

所有的操作先放在临时的事务日志中,当commit的时候,将日志中的信息写入数据库中;若rollback,会把事务日志中的信息相当于清空操作。
在这里插入图片描述
回滚点：当事务开启后,一部分sql执行成功,添加一个回滚点,后续操作报错,直接到回滚点,保证之前的操作可以成功提交

1.2 事务特性（ACID）

原子性（A）: 一组逻辑单元,不可再分割 , 最小的单位
一致性（C）: 事务的前后数据保持一致 , 要么同时成功要么同时失败
隔离性（I）：多事务之间应该产生一定的隔离 , 互不干扰
持久性（D）：事务不可逆 , 一旦事务提交了不可以再回滚

1.3 事务的隔离级别

多个事务之间应该相互隔离的,相互独立的。

不考虑隔离性产生的问题：

脏读：一个事务读到了另一个事务未提交的数据
不可重复读：在一个事务中,两次读取某些记录的结果不一致，即一个事务读到了另一个事务已经提交的update数据
幻读: 一个事务读到了另一个事务已经提交的insert或delete数据

数据库隔离级别：

名称	隔离级别	可解决的问题	备注
读未提交	READ UNCOMMITTED	脏读、不可重复读和幻读都不能解决
读已提交	READ COMMITTED	能解决脏读	Oracle和SQL Server默认隔离级别
可重复读	REPEATABLE READ	能解决脏读和不可重复读，有几率发生幻读	MySQL默认级别,mysql默认处理了幻读
串行化	SERIALIZABLE	脏读、不可重复读和幻读都能解决	单线程操作

设置设置隔离级别的方式：

set session transaction isolation level  隔离级别
 会话级（当前黑窗口有效，关闭之后再打开就失效）

小结：

读未提交安全性最低,效率是最高的
串行化安全性最高,效率是最低的
隔离级别越高，执行效率越低

1.4 操作事务

在Mysql数据库中，默认情况下，每条SQL语句自己独立一个事务。

如果想要把多条语句放在同一个事务中，则需要手动开启事务：

mysql中设置手动开启事务
start transaction;

手动提交：
commit;

手动回顾：
rollback;

结束标识：提交或回滚

二、索引

2.1 索引概述

随着数据库的数据的增加，查询速度会越来越慢，这个时候我们就需要按照一定的规则对数据进行整理和排序，这个整理排序的规则就是索引。

通过添加在数据表中某列或者某些列的检索规则，我们就可以建立索引，从而提交查询的效率。

2.2 索引的优势与劣势

优点：提高查询效率，降低数据库IO成本

缺点：

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
由于增删改的操作也需要维护索引，所以会降低增删改操作的效率

所以要根据实际需要使用索引。

2.3 索引的分类

普通索引：添加index来实现让字段有索引
唯一索引：添加唯一约束unique，自带唯一索引
主键索引：添加主键，自带主键索引，一张表只能有一个主键索引 primary key
组合（联合）索引：多列值组成一个索引 index，组成索引的字段是组合的

2.4 创建索引原则

经常用于查询条件的字段，建议添加索引
经常用于排序的字段，建议添加索引
辨识度大于70%以上的才建议使用索引

2.5 创建索引的语法

直接创建（普通、唯一）：

-- 创建普通索引
create index 索引名 on 表名(列名);
-- 创建唯一索引
create unique index 索引名 on 表名(列名);
-- 创建普通组合索引
create index 索引名 on 表名(列名1,列名2....);
-- 创建唯一组合索引
create unique index 索引名 on 表名(列名1,列名2...);

修改表时指定：

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(id);
-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(列名); -- 索引名就是列名
-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index(列名)；-- 索引名就是列名

创建表时指定：

create table student(
 id int,
 username varchar(32),
 age int,
 primary key(id), -- 主键
 unique(username), -- 唯一
 index(age) -- 普通
);

2.6 索引失效

模糊匹配时，如果条件没有一定辨识度，那么索引就会失效
当多条件查询时，如果使用or关键字，只要有一个条件没有索引就会失效
当查询条件字段有计算时，索引会失效
当条件有<>,is null ,is not null，!= 这些条件时，索引失效

2.7 索引数据结构

索引可以帮助Mysql高效获取排好序的数据结构，可以把索引理解为排序后的数据结构。
常见的数据结构：
二叉树
在这里插入图片描述
红黑树
红黑树又称为平衡二叉树，左旋和右旋实现自平衡，平衡整个树。

2.7.1 Hash散列

JDK1.7版本是数组+链表；
JDK1.8版本：

链表长度小于8是数组+链表
链表长度大于等于8则是数组+红黑树

2.7.2 B-Tree

多路搜索平衡树
在这里插入图片描述

2.7.3 B+Tree

Mysql使用的就是B+tree

叶子节点：最后一层子节点，数据存储在叶子节点
非叶子节点：中间结构，存储是索引+指针

在这里插入图片描述

2.8 数据库存储引擎

MySQL存储引擎的不同，那么索引文件保存的方式也有所不同，常见的有二种存储引擎MyISAM和InnoDB。

2.8.1 MyISAM

MySQL5.5版本之前默认的存储引擎，不支持事务。

CREATE TABLE myisam_tab(
  id INT,
  username VARCHAR(32)
)ENGINE = MYISAM;

它的索引文件和数据文件是分离的（非聚集索引）
在这里插入图片描述

2.8.2 InnoDB

MySQL5.5版本之后默认的存储引擎，支持事务。

CREATE TABLE innodb_tab(
   id INT,
  username VARCHAR(32)
)ENGINE = INNODB;

它的索引和数据在同一个文件中（聚集索引）
在这里插入图片描述
请添加图片描述
在实际开发中，设置不一样查询操作和增删改操作效率不一样：

myisam查询效率高
innodb增删改效率高

三、MySQL函数

为了简化操作，mysql提供了大量的函数给程序员使用。

函数是为了实现特定功能而编写的数据库代码，它不是一条SQL语句，而是多条SQL语句组成。

数据库函数一般指通用性的方法。

3.1 字符串函数

1.  函数：CONCAT(s1,s2...sn)
	描述：字符串 s1,s2 等多个字符串合并为一个字符串
	
2.  函数：CHAR_LENGTH(str)
	描述：返回字符串 str 的字符数（长度）
	
3.  函数：LENGTH(str)
	描述：返回字符串 s 的字节数（长度）
	
4.  函数：UCASE(s) | UPPER(s)
	描述：将字符串转换为大写
	
5.  函数：LCASE(s) | LOWER(s)
	描述：将字符串转换为小写
	
6.  函数：LOCATE(s1,s)
	描述：从字符串 s 中获取 s1 的开始位置
	
7.  函数：TRIM(str) | LTRIM(str) | RTRIM(str)
	描述：字符串去空格
	
8.  函数：REPLACE(s,s1,s2)
	描述：将字符串 s2 替代字符串 s 中的字符串 s1
	
9.  函数：SUBSTR(s, start, length)
	描述：从字符串 s 的 start 位置截取长度为 length 的子字符串
	
10. 函数：STRCMP(str1,str2)
	描述：比较字符串大小,左大于右时返回1，左等于右时返回0，，左小于于右时返回-1

3.2 日期函数

1.  函数：NOW() | CURDATE() | CURTIME()
	描述：获取系统当前日期时间、日期、时间
	实例：SELECT NOW();
	
2.  函数：YEAR(DATE) | MONTH(DATE) | DAY(DATE)
	描述：从日期中选择出年、月、日
	实例：SELECT YEAR(NOW()); 
	
3.  函数：LAST_DAY(DATE)
	描述：返回月份的最后一天
	实例：SELECT LAST_DAY(NOW());
	
4.  函数：ADDDATE(DATE,n) | SUBDATE(DATE,n)
	描述：计算起始日期 DATE 加（减） n 天的日期
	实例：SELECT ADDDATE(NOW(),10);
	
5.  函数：QUARTER(DATE)
	描述：返回日期 DATE 是第几季节，返回 1 到 4
	实例：SELECT QUARTER(NOW());
	
6.  函数：DATEDIFF(d1,d2)
	描述：计算日期 d1->d2 之间相隔的天数
	实例：SELECT DATEDIFF('2019-08-01','2019-07-01');
	
7.  函数：DATE_FORMAT(d,f)
	描述：按表达式 f的要求显示日期 d
	实例：SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

3.3 数字函数

1.  函数：ABS(x)
	描述：返回 x 的绝对值　　
	实例：SELECT ABS(-1);
	
2.  函数：CEIL(x) | FLOOR(x)
	描述：向上（下）取整
	实例：SELECT CEIL(1.5);
	
3.  函数：MOD(x,y)
	描述：返回x mod y的结果，取余
	实例：SELECT MOD(5,4);
	
4.  函数：RAND()
	描述：返回 0 到 1 的随机数
	实例：SELECT RAND();
	
5.  函数：ROUND(x)
	描述：四舍五入
	实例：SELECT ROUND(1.23456);
	
6.  函数：TRUNCATE(x,y)
	描述：返回数值 x 保留到小数点后 y 位的值
	实例：SELECT TRUNCATE(1.23456,3);

3.4 高级函数

3.4.1 case表达式

在查询代码的过程中，我们可能需要对查询的结果进行判断

-- 语法 
	SELECT 
		CASE [字段,值] 
			WHEN 判断条件1 
				THEN 希望的到的值1
			WHEN 判断条件2 
				THEN 希望的到的值2
			ELSE 前面条件都没有满足情况下得到的值 
		END
	FROM
		table_name;

3.4.2 if表达式

-- 语法
SELECT IF(1 > 0,'true','false') from table_name;

3.4.3 cast类型转换

显示转换

-- 语法
SELECT CAST(参数 AS 类型);

-- 类型
    字符型：CHAR
    日期 : DATE     
    时间: TIME     
    日期时间型 : DATETIME     
    浮点数 : DECIMAL      
    整数 : SIGNED

-- 字符串转为 日期
select cast('1999-1-1' as date);
-- 字符串转为 整型
select cast('19' as SIGNED);

四、数据库备份

4.1 使用数据库第三方工具

导出：
在这里插入图片描述
导入：

4.2 使用命令行

使用命令行必须登录到mysql中。

使用命令行导出的内容没有创建库和使用库的动作

mysqldump -u用户名 -p密码  需要备份的数据库 > 某个位置
mysqldump -uroot -proot mydata > d:/data.sql

导入操作需要创建数据库和使用数据库后才可以引入文件

source 源(数据的来源)
source data.sql

Williamsgogogo

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL之事务&索引&函数

MySQL之事务&索引&函数一、事务1.1 事务概述1.2 事务原理1.2 事务特性（ACID）一、事务1.1 事务概述事务:如果一个业务包含多个步骤的操作,那么这些操作要么同时成功,要么同时失败。例如转账业务,包含扣钱操作和加钱操作。1.2 事务原理所有的操作先放在临时的事务日志中,当commit的时候,将日志中的信息写入数据库中;若rollback,会把事务日志中的信息相当于清空操作。回滚点：当事务开启后,一部分sql执行成功,添加一个回滚点,后续操作报错,直接到回滚点,
复制链接

扫一扫