文章目录
MySQL事务、索引、数据恢复和备份
一、事务
MySQL的事务处理
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行
- MySQL事务处理只支持InnoDB和BDB数据表类型
- 事务必须具备以下四个属性，简称ACID属性：
- 原子性（Atomicity)： 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生要么都不发生
- 一致性（Consistency)：事务的执行的前后数据的完整性保持一致
- 隔离性（Isolation)：一个事务执行的过程中,不应该受到其他事务的干扰
- 持久性（Durability)： 永久写入硬盘，永久保存 。事务一旦结束,数据就持久到数据库
事务实现方法
-
set autocommit
- 使用SET语句来改变自动提交模式
SET AUTOCOMMIT = 0; # 关闭自动提交模式 SET AUTOCOMMIT = 1; # 开启自动提交模式- MySQL中默认是自动提交
- 使用事务时应先关闭自动提交
-
start transaction
- 开始一个事务，标记事务的起始点
-
commit
- 提交一个事务给数据库
-
rollback
- 将事务回滚，数据回到本次事务的初始状态
-
set autocommit = 1;
- 开启MySQL数据库的自动提交
-- 使用事务控制逻辑
-- mysql数据库自动开启了事务提交，所以我们应该先关闭自动提交，改为手动提交
SET autocommit = 0; #关闭自动提交
START TRANSACTION ; # 开启事务
-- sql代码：
UPDATE user set money = money+100 where id=1;
UPDATE user set money = money-'abc' where id=2;
ROLLBACK; #回滚
SET autocommit = 1; #开启事务提交，以便接下来使用
-- 以上一直在内存储存中,没有交,是控制台的问题。 ------>类似脏读
-- 在java中回释放，不会出现再执行commit后在改变
COMMIT; #提交
脏读
脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的，值得注意的是，脏读一般是针对于update操作的。
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
MySQL事务处理步骤
https://zhuanlan.zhihu.com/p/345617565
二、视图
-
视图是由查询结果形成的一张虚拟表。
-
如果某个查询结果出现的非常频繁，也就是，要经常拿这个查询结果来做子查询的时候会用到视图
-- 视图的创建语法：
create view 视图名 as select 语句;
-- 创建视图
CREATE VIEW res_stu_sub as
-- sql语句
select a.StudentNo, b.StudentName, a.SubjectNo, c.SubjectName, a.StudentResult
from result a
LEFT JOIN student b ON a.StudentNo = b.StudentNo
LEFT JOIN `subject` c ON c.SubjectNo = a.SubjectNo ;
-- 获得李文才的考试信息
select * from res_stu_sub as rss
where rss.StudentName = "李文才"
- 如果原表的内容进行了修改删除，视图也会改变。
- 性能变低，以前修改删除原表即可，现在视图也要进行，相当于多了一张表
三、索引
索引是优化数据库的唯一手段
什么叫数据库索引？
索引是对数据库中一列或者多列的值进行排序的一种数据结构。重点：对列的值进行排序的数据结构。使用索引可以快速访问数据库中的记录。
索引的主要用途是什么？
执行select查询语句时候会使用索引，索引主要用来提高查询性能。由于索引是经过某种算法优化过的，因而查找次数要少的多。
不使用索引情况下，sql查询语句是怎么执行的？
如果没有索引，必须遍历整个表。例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的。
作用：
- 提高查询的速度
- 确保数据的唯一性
- 可以加速表和表之间的连接，实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化
分类：
- 主键索引（PRIMARY KEY）
- 唯一索引（UNIQUE）
- 常规索引（INDEX）
- 全文索引（FULLTEXT）
#修改主键索引
ALTER table student add PRIMARY Key(StudentNo);
#修改唯一索引
ALTER table student add UNIQUE(StudentName);
#修改普通索引
ALTER table student add INDEX(IdentityCard);
#全文索引
ALTER table student add FULLTEXT(Phone)
explain select .....可以看到执行效率
1.主键索引（PRIMARY KEY）：
某一个属性或属性的组合能唯一标识一条记录
如：学生表（学号，姓名，班级，性别等） ，学号就是唯一标识的，可作为主键
特点
-
最常见的索引类型
-
确保数据记录的唯一性
-
确定特定数据记录在数据库中的位置
CREATE TABLE `GradeID`(
`GraderID` INT(11) AUTO_INCREMENT PRIMARY KEY,
# 主键索引也可以在字段字义之后，如：PRIMARY KEY(`GraderID`)
)
2.唯一索引（UNIQUE）：
- 作用：避免同一个表中某数据列中的值重复
- 与主键索引的区别：主键索引只能有一个；唯一索引可有多个
CREATE TABLE `Grade` (
`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
#或 UNIQUE KEY `GradeID` (`GradeID`)
)
很多时候使用唯一索引不是为提高查询速度而是为了避免同一个表中某数据列中的值重复（null可以重复 )
3.常规索引（INDEX）：
- 作用：快速定位特定数据
- 注意：
- index和key 关键字都可以设置常规索引
- 应加在查找条件的字段
- 不宜添加太多常规索引，影响数据的插入、删除和修改操作
- 组合索引，越常做条件的越要放在前面
-- 创建表时添加
CREATE TABLE `result`(
INDEX/KEY `ind` (`studentNo`,`subjectNo`)
)
-- 创建后追加
ALERT TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`)
4.全文索引（FULLTEXT）：
- 作用：快速定位特定数据
- 注意：
- 只能用于MyISAM类型的数据表
- 只能用于CHAR、VARCHAR、TEXT数据类型
- 适合大型数据集
-- 创建表时添加
CREATE TABLE `student`(
FULLTEXT(`StudentName`)
)ENGINE = MYISAM;
-- 创建后追加
ALTER TABLE employee ADD FULLTEXT(`first_name`);
全文索引使用方法：
select StudentNo, Address from student where MATCH(StudentName) AGAINST('张')
看性能的方法：
-
EXPLAIN后直接跟sql语句，可以观察是否使用索引，以及查了几行
-
直接在sql后面输入 show PROFILES;
管理索引
哪些情况需要创建索引？
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系建立索引
- 频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引
- WHERE条件里用不到的字段不创建索引
- 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
- 查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
哪些情况不要创建索引？
- 表记录太少
- 经常增删改的表
（ 提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和 DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。）- 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
建立索引的时机
一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：
SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city=‘郑州’
此时就需要对city和age建立索引，由于mytable表的 userame也出现在了JOIN子句中，也有对它建立索引的必要。
刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。
SELECT * FROM mytable WHERE username like'admin%'
-- 但是'%xx%'和'%xx'不会使用索引
SELECT * FROM mytable WHEREt Name like'%admin'
给数据库表添加索引:
-
创建索引
-
创建表时追加：
如果是 CHAR，VARCHAR类型，length可以小于字段实际长度;如果是BLOB和TEXT类型，必须指定 length
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );- 建表后追加（修改表结构）：
ALERT TABLE 表名 ADD 索引类型(数据列明) # 或者 右键-设计表-索引，去设置 -
-
删除索引
DROP INDEX 索引名 ON 表名 ALTER TABLE 表名 DROP INDEX 索引名 ALTER TABLE 表名 DROP PRIMARY KEY -
查看索引
SHOW INDEX(或KEYS) FROM 表名
组合索引：
为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将多个列建到一个索引里。
建表：
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
city VARCHAR(50) NOT NULL,
age INT NOT NULL
);
例如： 将 name, city, age建到一个索引里
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
最左原则：
例如组合索引（a,b,c），组合索引的生效原则是
-
从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的 引没有起作用；
-
对于组合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
两个或更多个列上的索引被称作复合索引。
添加正确的索引：
-
在WHERE、ORDER BY 子句中经常使用的字段
-
字段的值是多个（例如性别字段则不适合）
-
字段内容不是经常变化的
经常变化的字段，添加索引反而降低性能
-
不宜过多添加索引
每添加一条索引都会占用磁盘空间
四、MySQL的备份
作用：保证重要数据不丢失、数据转移
MySQL数据库备份方法：
- mysqldump备份工具
- 数据库管理工具，如SQLyog
- 直接拷贝数据库文件和相关配置文件
mysqldump数据库备份：
转储数据库、搜集数据库进行备份、将数据转移到另一个SQL服务器(不一定是MySQL服务器)
-- 预存文件目录，须有该目录读写权限
mysqldump -h 主机名 –u 用户名 –p [options] 数据库名
[ table1 table2 table3 ] > path/filename.sql
-
mysqdump与mysql是同级别的命令。都是在bin目录下的exe执行文件。
-
强调mysqldump 不是 在mysql命令里执行的。
-- 备份myschool数据库如：
> mysqldump -u root -p myschool > d:/myschool.sql
EnterPassword:****
常用选项：
| 符号名称 | 描述 |
|---|---|
| –add-drop-table | 导出sql脚本会加上 DROP TABLE IF EXISTS 语句 默认是打开的，可以用 --skip-add-drop-table 来取消 |
| –add-locks | 该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句 好处：防止记录被再次导入时，其他用户对表进行的操作，默认是打开的 |
| -t 或 –no-create-info | 忽略不写重新创建每个转储表的CREATE TABLE语句 |
| -c 或 –complete-insert | 在每个INERT语句的列上加上字段名 在数据库导入另一个数据库时非常有用 |
| -d 或 –no-data | 不写表的任何行信息。对于只想转储表的结构很有用 |
| –where “where-condition”, -w “where-condition” | 只转储给定的WHERE条件选择的记录 |
| –opt | 该选项是速记；等同于指定 –add-drop-tables –add-locking –create-option –disable-keys–extended-insert –lock-tables –quick –set-charset |
五、存储过程
概念：
SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。
注意：
-
MySQL默认以";“为分隔符。如果没有分隔符，要事先用“ DELIMITER // ”声明当前段分隔符，让编译器把两个”//"之间的内容当做存储过程的代码，不会执行这些代码；
“DELIMITER ;” 的意为把分隔符还原
DROP PROCEDURE IF EXISTS xxx; #如果存在删除这个存储过程 DELIMITER// #声明当前段分隔符 CREATE PROCEDURE xxx( ) BEGIN END;// DELIMITER; #分隔符还原 call xxx();
语法：
CREATE PROCEDURE 过程名(
[[IN|OUT|INOUT] 参数名 数据类型,
[IN|OUT|INOUT] 参数名 数据类型…]
) [特性 ...] 过程体
-- ----------------------MySQL存储过程-------------------------
DROP PROCEDURE IF EXISTS addresult; #如果存在删除这个存储过程
CREATE PROCEDURE addresult(
-- 参数，与表格字段名不可重复，不区分大小写。类型指定长度
[IN/OUT] stuname VARCHAR(255),
subname VARCHAR(255)
)
BEGIN
-- 查询语句
SELECT * from student where StudentName = stuname;
-- 输出
SELECT stuname, subname;
-- 修改
set stuname = "王五";
END
# 存储过程的调用用call
set @stuname = "郭靖";
call addresult(@stuname,"线性代数");
# 修改王五后查询：
select @stuname;
关键字IN/OUT/INOUT：
- IN：存储过程修改的值不返回，默认为IN。能查能改，但返回相当于void无值
- OUT: 返回修改结果值，查询结果为null
- INOUT：调用时指定，并且可被改变和返回
-- -------------------------------------------
DROP PROCEDURE IF EXISTS addresult1;
CREATE PROCEDURE addresult1(
stuname VARCHAR(255),
subname VARCHAR(255)
)
BEGIN
-- 接收StudentNo的值用into,用@定义系统变量或者自己定义变量
SELECT StudentNo into @stuid
from student where StudentName = stuname;
#SELECT @stuid;
SELECT SubjectNo into @subid
from `subject` where SubjectName = subname;
INSERT INTO result(StudentNo, SubjectNo, StudentResult)
VALUES (@stuid,@subid,99);
END;
call addresult1("三百七","概率论");
变量：
**语法：**DECLARE 变量名1[,变量名2…] 数据类型 [默认值];
**变量赋值：**SET 变量名 = 变量值 [,变量名= 变量值 …]
**用户变量：**用户变量一般以@开头。滥用用户变量会导致程序难以理解及管理
-- --------------------定变量--------------------------------
DROP PROCEDURE IF EXISTS addresult2;
# 存储
CREATE PROCEDURE addresult2(
stuname VARCHAR(255),
subname VARCHAR(255)
)
BEGIN
# 变量
DECLARE aa INT(11) DEFAULT 0;
set aa = aa+1;
SELECT aa;
END;
# 存储过程的调用
call addresult2("三百七","概率论");
控制语句：
-
if-then-else语句：
BEGIN # 变量 DECLARE aa INT(11) DEFAULT 0; IF aa = 0 THEN # 在sql里面条件相等一个"="号， set aa = aa+1; SELECT aa; ELSE xxxx END IF; #相当于括号的另一半 END; -
while
BEGIN
DECLARE i INT(11) DEFAULT 0;
-- 接收StudentNo的值用into,用@定义系统变量或者自己定义变量
SELECT StudentNo into @stuid
from student where StudentName = stuname;
SELECT SubjectNo into @subid
from `subject` where SubjectName = subname;
while i<3 do
INSERT INTO result(StudentNo, SubjectNo, StudentResult)
VALUES (@stuid,@subid,99);
set i = i+1;
end while;
END;
真正作用：
- 定义变量记录程序是否报错
- 捕获异常
DROP PROCEDURE IF EXISTS upUser;
DELIMITER //
CREATE PROCEDURE upUser()
BEGIN
DECLARE t_error INTEGER DEFAULT 0; #定义变量
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; #异常时触发
START TRANSACTION; #开启事务
UPDATE `user` set money = money+100 where id = 1;
UPDATE `user` set money1 = money-"xxx" where id=2;
IF t_error = 1 THEN #判断
ROLLBACK; #回滚
ELSE
COMMIT; #提交
END IF;
SELECT t_error ; #返回状态
END;//
DELIMITER;
call upUser();
六、触发器
old删除得时候用，new修改时候用.
并且自己 不能触发自己
268
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
