欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql学习【第5篇】:事务索引备份视图

程序员文章站 2022-03-09 14:33:49
狂神声明 : 文章均为自己的学习笔记 , 转载一定注明出处 ; 编辑不易 , 防君子不防小人~共勉 ! mysql学习【第5篇】:事务索引备份视图 MySQL事务 事务就是将一组SQL语句放在同一批次内去执行 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行 MySQL事务处理只支持In ......

狂神声明 : 文章均为自己的学习笔记 , 转载一定注明出处 ; 编辑不易 , 防君子不防小人~共勉 ! 

mysql学习【第5篇】:事务索引备份视图

 mysql事务

  • 事务就是将一组sql语句放在同一批次内去执行
  • 如果一个sql语句出错,则该批次内的所有sql都将被取消执行
  • mysql事务处理只支持innodb和bdb数据表类型

事务的acid原则

  • 原子性(atomic)
    •  整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
  • 一致性(consist)
    •  一个事务可以封装状态改变(除非它是一个只读的)。事务必须始终保持系统处于一致的状态,不管在任何给定的时间并发事务有多少。
              也就是说:如果事务是并发多个,系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(preserving an invariant),以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时发生多个转账,无论并发多少个,比如在a与b账户之间转账5元,在c与d账户之间转账10元,在b与e之间转账15元,五个账户总额也应该还是500元,这就是保护性和不变性。

  • 隔离性(isolated)
    • 隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务,运行在相同的时间内,执行相同的功能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混淆,必须串行化或序列化请求,使得在同一时间仅有一个请求用于同一数据。
  • 持久性(durable)
    • 在事务完成以后,该事务对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。

mysql事务实现方法

/* 使用set语句来改变自动提交模式 */
set autocommit = 0;   /*关闭*/
set autocommit = 1;   /*开启*/

/* 注意
    1.mysql中默认是自动提交
    2.使用事务时应先关闭自动提交
*/

/*开始一个事务,标记事务的起始点*/
start transaction   

/*提交一个事务给数据库*/
commit

/*将事务回滚,数据回到本次事务的初始状态*/
rollback

/*还原mysql数据库的自动提交*/
set autocommit =1;

-- 保存点
    savepoint 保存点名称 -- 设置一个事务保存点
    rollback to savepoint 保存点名称 -- 回滚到保存点
    release savepoint 保存点名称 -- 删除保存点

 

mysql事务处理步骤

mysql学习【第5篇】:事务索引备份视图

/*
课堂测试题目

a在线买一款价格为500元商品,网上银行转账.
a的银行卡余额为2000,然后给商家b支付500.
商家b一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/

create database `shop`character set utf8 collate utf8_general_ci; 
use `shop`; 

create table `account` (
  `id` int(11) not null auto_increment,
  `name` varchar(32) not null,
  `cash` decimal(9,2) not null,
  primary key (`id`)
) engine=innodb default charset=utf8

insert into account (`name`,`cash`)
values('a',2000.00),('b',10000.00)

# 转账实现
set autocommit = 0;
start transaction;
update account set cash=cash-500 where `name`='a';
update account set cash=cash+500 where `name`='b';
commit;
# rollback;
set autocommit = 1;

 数据库索引

作用 :

  • 提高查询速度
  • 确保数据的唯一性
  • 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
  • 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
  • 全文检索字段进行搜索优化.

分类 :

  • 主键索引  (primary key)
  • 唯一索引  (unique)
  • 常规索引  (index)
  • 全文索引  (fulltext)

主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 : 

  • 最常见的索引类型
  • 确保数据记录的唯一性
  • 确定特定数据记录在数据库中的位置

 唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

  • 主键索引只能有一个
  • 唯一索引可能有多个

     mysql学习【第5篇】:事务索引备份视图

常规索引

作用 : 快速定位特定数据

注意 : 

  • index 和 key 关键字都可以设置常规索引
  • 应加在查询找条件的字段
  • 不宜添加太多常规索引,影响数据的插入,删除和修改操作

    mysql学习【第5篇】:事务索引备份视图

全文索引

作用 : 快速定位特定数据

注意 :

  • 只能用于myisam类型的数据表
  • 只能用于char , varchar , text数据列类型
  • 适合大型数据集

创建/删除索引

/*
#方法一:创建表时
      create table 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [unique | fulltext | spatial ]   index | key
                [索引名]  (字段名[(长度)]  [asc |desc]) 
                );


#方法二:create在已存在的表上创建索引
        create  [unique | fulltext | spatial ]  index  索引名 
                     on 表名 (字段名[(长度)]  [asc |desc]) ;


#方法三:alter table在已存在的表上创建索引
        alter table 表名 add  [unique | fulltext | spatial ] index
                             索引名 (字段名[(长度)]  [asc |desc]) ;
                             
                             
#删除索引:drop index 索引名 on 表名字;
#删除主键索引: alter table 表名 drop primary key;


#显示索引信息: show index from student;
*/

/*增加全文索引*/
alter table `school`.`student` add fulltext index `studentname` (`studentname`); 

/*explain : 分析sql语句执行性能*/
explain select * from student where studentno='1000';

/*使用全文索引*/
explain select *from student where match(studentname) against('love');

索引的两大类型hash与btree

#我们可以在创建上述索引的时候,为其指定索引类型,分两类
hash类型的索引:查询单条快,范围查询慢
btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)

#不同的存储引擎支持的索引类型也不一样
innodb 支持事务,支持行级别锁定,支持 b-tree、full-text 等索引,不支持 hash 索引;
myisam 不支持事务,支持表级别锁定,支持 b-tree、full-text 等索引,不支持 hash 索引;
memory 不支持事务,支持表级别锁定,支持 b-tree、hash 等索引,不支持 full-text 索引;
ndb 支持事务,支持行级别锁定,支持 hash 索引,不支持 b-tree、full-text 等索引;
archive 不支持事务,支持表级别锁定,不支持 b-tree、hash、full-text 等索引;

索引准则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表建议不要加索引
  • 索引一般应加在查找条件的字段

 mysql备份

  • 数据库备份必要性
    • 保证重要数据不丢失
    • 数据转移
  • mysql数据库备份方法
    • mysqldump备份工具
    • 数据库管理工具,如sqlyog
    • 直接拷贝数据库文件和相关配置文件

mysqldump客户端

作用 :

  • 转储数据库
  • 搜集数据库进行备份
  • 将数据转移到另一个sql服务器,不一定是mysql服务器

语法 :

mysql学习【第5篇】:事务索引备份视图

-- 导出
1. 导出一张表
  mysqldump -u用户名 -p密码 库名 表名 > 文件名(d:/a.sql)
2. 导出多张表
  mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(d:/a.sql)
3. 导出所有表
  mysqldump -u用户名 -p密码 库名 > 文件名(d:/a.sql)
4. 导出一个库 
  mysqldump -u用户名 -p密码 -b 库名 > 文件名(d:/a.sql)

可以-w携带备份条件

-- 导入
1. 在登录mysql的情况下:
  source  备份文件
2. 在不登录的情况下
  mysql -u用户名 -p密码 库名 < 备份文件

 

 视图

/* 视图 */ ------------------
什么是视图:
    视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来*定义视图的查询所引用的表,并且在引用视图时动态生成。
    视图具有表结构文件,但不存在数据文件。
    对其中所引用的基础表来说,视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表,或者其它视图。通过视图进行查询没有任何限制,通过它们进行数据修改时的限制也很少。
    视图是存储在数据库中的查询的sql语句,它主要出于两种原因:安全原因,视图可以隐藏一些数据,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等,另一原因是可使复杂的查询易于理解和使用。

-- 创建视图
create [or replace] [algorithm = {undefined | merge | temptable}] view view_name [(column_list)] as select_statement
    - 视图名必须唯一,同时不能与表重名。
    - 视图可以使用select语句查询到的列名,也可以自己指定相应的列名。
    - 可以指定视图执行的算法,通过algorithm指定。
    - column_list如果存在,则数目必须等于select语句检索的列数

-- 查看结构
    show create view view_name 

-- 删除视图
    - 删除视图后,数据依然存在。
    - 可同时删除多个视图。
    drop view [if exists] view_name ...

-- 修改视图结构
    - 一般不修改视图,因为不是所有的更新视图都会映射到表上。
    alter view view_name [(column_list)] as select_statement

-- 视图作用
    1. 简化业务逻辑
    2. 对客户端隐藏真实的表结构

-- 视图算法(algorithm)
    merge        合并
        将视图的查询语句,与外部查询需要先合并再执行!
    temptable    临时表
        将视图执行完毕后,形成临时表,再做外层查询!
    undefined    未定义(默认),指的是mysql自主去选择相应的算法。

 

触发器

/* 锁表 */
表锁定只用于防止其它客户端进行不正当地读取和写入
myisam 支持表锁,innodb 支持行锁
-- 锁定
    lock tables tbl_name [as alias]
-- 解锁
    unlock tables


/* 触发器 */ ------------------
    触发程序是与表有关的命名数据库对象,当该表出现特定事件时,将激活该对象
    监听:记录的增加、修改、删除。

-- 创建触发器
create trigger trigger_name trigger_time trigger_event on tbl_name for each row trigger_stmt
    参数:
    trigger_time是触发程序的动作时间。它可以是 before 或 after,以指明触发程序是在激活它的语句之前或之后触发。
    trigger_event指明了激活触发程序的语句的类型
        insert:将新行插入表时激活触发程序
        update:更改某一行时激活触发程序
        delete:从表中删除某一行时激活触发程序
    tbl_name:监听的表,必须是永久性的表,不能将触发程序与temporary表或视图关联起来。
    trigger_stmt:当触发程序激活时执行的语句。执行多个语句,可使用begin...end复合语句结构

-- 删除
drop trigger [schema_name.]trigger_name

可以使用old和new代替旧的和新的数据
    更新操作,更新前是old,更新后是new.
    删除操作,只有old.
    增加操作,只有new.

-- 注意
    1. 对于具有相同触发程序动作时间和事件的给定表,不能有两个触发程序。


-- 字符连接函数
concat(str1[, str2,...])

-- 分支语句
if 条件 then
    执行语句
elseif 条件 then
    执行语句
else
    执行语句
end if;

-- 修改最外层语句结束符
delimiter 自定义结束符号
    sql语句
自定义结束符号

delimiter ;        -- 修改回原来的分号

-- 语句块包裹
begin
    语句块
end

-- 特殊的执行
1. 只要添加记录,就会触发程序。
2. insert into on duplicate key update 语法会触发:
    如果没有重复记录,会触发 before insert, after insert;
    如果有重复记录并更新,会触发 before insert, before update, after update;
    如果有重复记录但是没有发生更新,则触发 before insert, before update
3. replace 语法 如果有记录,则执行 before insert, before delete, after delete, after insert