MySQL事务、索引、数据恢复和备份

最新推荐文章于 2025-06-09 21:59:06 发布

DragonGod__

最新推荐文章于 2025-06-09 21:59:06 发布

阅读量689

点赞数

文章标签： mysql sql 数据库

本文链接：https://blog.youkuaiyun.com/DragonGod__/article/details/121758945

版权

#第一个会话

#调用库关键词:use
USE kj11;
#改变事务之自动提交   关键词:set autocommit 0:关闭/1:打开
SET autocommit = 1;
#查询表   关键词:show tables
SHOW TABLES;
#查询表信息   关键词:select * from
SELECT * FROM grade;
#添加
INSERT INTO grade VALUES
(5,'大五');
#删除
DELETE FROM grade WHERE GradeID = '5';
#手动添加数据   关键词:commit
COMMIT;

#MySQL事务、索引、数据恢复和备份
/*
事务就是将一组SQL语句放在同一批次内去执行
如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行
事务必须具备以下四个属性，简称ACID 属性
1)原子性（Atomicity）

2)一致性（Consistency）

3)隔离性（Isolation）

4)持久性（Durability）

MySQL的事务实现方法
SET AUTOCOMMIT
使用SET语句来改变自动提交模式

1) SET AUTOCOMMIT = 0; # 关闭自动提交模式
2) SET AUTOCOMMIT = 1; # 开启自动提交模式
注意!
1) MySQL中默认是自动提交
2) 使用事务时应先关闭自动提交

MySQL的事务实现方法
1) START TRANSACTION
开始一个事务,标记事务的起始点
2) COMMIT
提交一个事务给数据库
3) ROLLBACK
将事务回滚，数据回到本次事务的初始状态
4) SET AUTOCOMMIT = 1;
开启MySQL数据库的自动提交

数据库索引
   作用
       1)提高查询速度
       2)确保数据的唯一性
       3)可以加速表和表之间的连接，实现表与表之间的参照完整性
       4)使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
       5)全文检索字段进行搜索优化
   分类
       1)主键索引（PRIMARY KEY）
       2)唯一索引（UNIQUE）
       3)常规索引（INDEX）
       4)全文索引（FULLTEXT）

主键索引
   某一个属性或属性的组合能唯一标识一条记录
       如：学生表（学号，姓名，班级，性别等），学号就是唯一标识的，可作为主键
   特点
       最常见的索引类型
       确保数据记录的唯一性
       确定特定数据记录在数据库中的位置
   代码如下:
   CREATE TABLE `Grade` (
       `GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
       #省略代码……
       #主键索引也可在字段字义之后，如
       # PRIMARY KEY(`GradeID`)
   )
   唯一索引（UNIQUE）
       作用
           避免同一个表中某数据列中的值重复
       与主键索引的区别
           主键索引只能有一个
           唯一索引可有多个
   代码如下:
   CREATE TABLE `Grade` (
       `GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
       `GradeName` VARCHAR(32) NOT NULL UNIQUE
       #或 UNIQUE KEY `GradeID` (`GradeID`)
   )

   常规索引（INDEX）
       作用：快速定位特定数据
       注意
           index和key关键字都可设置常规索引
           应加在查找条件的字段
           不宜添加太多常规索引，影响数据的插入、删除和修改操作
   代码如下:
   CREATE TABLE `result` (
       //省略一些代码
       INDEX/KEY `ind` (`studentNo`, `subjectNo`)   #创建表时添加
   )
   ALTER TABLE `result` ADD INDEX `ind`    #创建后添加
   (`studentNo`, `subjectNo`);

   全文索引（FULLTEXT）
       作用：快速定位特定数据
       注意
           只能用于MyISAM类型的数据表
           只能用于 CHAR 、 VARCHAR、TEXT数据列类型
           适合大型数据集
   代码如下:
   CREATE TABLE `student` (
       #省略一些SQL语句
       FULLTEXT (`StudentName`) #创建表时添加
   )ENGINE=MYISAM;
   ALTER TABLE employee ADD FULLTEXT (`first_name`);#创建后添加

   管理索引
       创建索引
           创建表时添加
           建表后追加
           ALERT TABLE 表名 ADD 索引类型（数据列名）
       删除索引
           DROP INDEX 索引名 ON 表名
           ALTER TABLE 表名 DROP INDEX 索引名
           ALTER TABLE 表名 DROP PRIMARY KEY
       查看索引
           SHOW INDEX(或KEYS) FROM 表名

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

#删除表关键词:drop
DROP TABLE IF EXISTS account;
#创建新表:account
CREATE TABLE IF NOT EXISTS account(
id INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
cash DECIMAL(9,2) NOT NULL,
PRIMARY KEY (id)
)ENGINE = INNODB CHARSET=utf8 COLLATE=utf8_bin;

#清空表关键词:delete
DELETE FROM account;
#添加记录关键词:insert into ...values....
INSERT INTO `account` VALUES
(1001,'A',2000),
(1002,'B',10000);
#查询表关键词:account
SELECT * FROM account;

#开启事务 start transaction
START TRANSACTION;
#a向b转500元 update...set...where...; savepoint A:事务结果保存到点A
UPDATE account SET cash = (cash-500) WHERE `name` = 'A';
SAVEPOINT A;
UPDATE account SET cash = (cash+500) WHERE `name` = 'B';
SAVEPOINT B;
UPDATE account SET cash = (cash+500) WHERE `name` = 'B';
SAVEPOINT c;
/*
#没有发生错误 commit:自动提交
注意事务提交后就不可以进行回滚
COMMIT;
*/
#发生错误回滚到A保存点关键词:rollback to A:回滚返回到指定A点
ROLLBACK TO A;
#发生错误回滚到事务开始状态关键词:rollback:回到初始点;
ROLLBACK;
COMMIT;
/*#发生错误回滚到B保存点
rollback to B;
select * from account;
rollback to A;#报错!原因回滚后不可以回头
*/
#视图
#查询学生姓名,年纪名称,学习的科目名称
SELECT a.`StudentName`,b.`GradeName`,c.`SubjectName` FROM student a,grade b,SUBJECT c
WHERE a.`GradeID` = b.`GradeID`
AND b.`GradeID` = c.`GradeID`;

#删除视图关键词:drop view if exists
DROP VIEW IF EXISTS student_grade_subject_view;
#创建视图思路:将上表的查询结果做成视图提供给下面的需求会变得很简单
#关键词:create view 视图名 as 创建视图依据
CREATE VIEW `student_grade_subject_view` AS
SELECT a.`StudentName`,b.`GradeName`,c.`SubjectName` FROM student a,grade b,SUBJECT c
WHERE a.`GradeID` = b.`GradeID`
AND b.`GradeID` = c.`GradeID`;

SELECT * FROM `student_grade_subject_view`;
#查询大一学生的信息
SELECT StudentName FROM `student_grade_subject_view`
WHERE GradeName = '大一';

#查询大一学生郭靖的科目信息
SELECT SubjectName FROM `student_grade_subject_view`
WHERE StudentName = '郭靖';

#删除索引关键词 alter table 索引所在表名 drop index 索引名;
ALTER TABLE student DROP INDEX student_index;
#创建索引关键词:create index...on...
CREATE INDEX student_index
ON student(StudentName);

SELECT * FROM student
WHERE StudentName = '黄蓉';

#B树 Btree:数据库底层索引
SHOW INDEX FROM student;
#解释表信息
EXPLAIN SELECT * FROM student
WHERE StudentName = '黄蓉';