数据库MYSQL第四章MySQL事务、索引、数据恢复和备份

mysql

MySQL的事务处理

事务就是将一组SQL语句放在同- -批次内去执行如果一个SQL语句出错，则该批次内的所有SQL都将
被取消执行
##注意MySQL事务处理只支持InnoDB和BDB数据表类型

事务的特性

事务必须具备以下四个属性，简称ACID属性
原子性 (Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)

MySQL的事务实现方法

#SET AUTOCOM MIT「
使用SET语句来改变自动提交模式
SET AUTOCOMMIT = 0;#关闭自动提交模式
SET AUTOCOMMIT = 1;#开启自动提交模式
注意
MySQL中默认是自动提交
使用事务时应先关闭自动提交

MySQL的事务实现方法

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

王老师讲课实录

#转账 尤俊杰给李嘉易转1000
# 提交  回滚
set autocommit = 0; #关闭自动提交
START TRANSACTION ;#开启事务
update user set money=money-1000
where name='尤俊杰';
update user set money=money+1000
where name='李嘉易';
COMMIT; #提交事务
#ROLLBACK;#回滚事务
set autocommit = 1;#开启自动提交

在整个流程图结束之后把SET AUTOCOMMIT = 1;还原MySQL数据库的自动提交

数据库索引

作用

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

分类

主键索引 (PRIMARY KEY)
唯一索引(UNIQUE)
常规索弓| (INDEX)
全文索引(FULLTEXT)

主键索引

某一个属性或属性的组合能唯一标识-条记录
如:学生表(学号，姓名，班级，性别等) ,学号就是唯一-标识的 ,可作为主键
特点
最常见的索引|类型
确保数据记录的唯一-性
确定特定数据记录在数据库中的位置
示例
CREATE TABLE Grade' (
'GradelD` INT(11) AUTO_ INCREMENT PRIMARY KEY,
#省略代码....
#主键索引也可在字段字义之后，如
# PRIMARY KEY(`GradelD`)
)

唯一索引(UNIQUE)

作用
避免同一个表中某数据列中的值重复
与主键索引的区别
主键索弓|只能有一个
唯- -索引可有多个
示例
CREATE TABLE‘Grade^ (
`GradelD' INT(11) AUTO_ INCREMENT PRIMARY
KEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
#或UNIQUE KEY GradelD ( `GradelD`)
)

常规索引(INDEX)

作用:快速定位特定数据
注意
index和key关键字都可设置常规索引
应加在查找条件的字段
不宜添加太多常规索引，影响数据的插入、删除和修改操作
CREATE TABLE `result` (
#省略-些代码
INDEXIKEY `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表名

索引准则

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

分析SQL语句的执行性能

EXPLAIN表名(DESC 表名)
EXPLAIN SELECT语句

添加正确的索引

在WHERE、
ORDER BY子句中经常使用的
字段的值是多个(例如性别字段则不适合)
字段内容不是经常变化的
经常变化的字段，添加索引反而降低性能
不宜过多添加索引
每添加一条索引|都会占用磁盘空间

MySQL的备份

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

mysgldump数据库备份

作用
转储数据库
搜集数据库进行备份
将数据转移到另一个SQL服务器(不一 定是MySQL服务器)
mysqldump -h 主机名-u用户名-p [options] 数据库名 (预存文件目录，须有该目录读写权限)
[ table1 table2 table3] > path/filename.sql
示例

备份myschool数据库如:

mysqldump -u root -p myschool > d:/myschool.sql
EnterPassword:*

#mysqIdump常用选项2-1
|符号名称|描述|
|-----|-----|
|-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语句|
#mysqldump常用选项2-2
|符号名称|描述|
|----|----|
|-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|

MySQL数据库的恢复

方法一
用SOURCE语法
/path/是一个绝对路径，并且必须是mysql运行用户有权限读取的文件
SOURCE在MySQL命令行里执行
SOURCE /path/db_ name.sql;#都要先创建或选择数据库
方法二
用mysql客户端
mysql -u root -p dbname < /path/db_ name.sql;

利用SQL语句导出、导入数据

SELECT * INTO OUTFILE 'file_ name' 导出数据
FROM tbl_ name
输出的文件不能先存在，否则报错
LOAD DATA INFILE 'file_ name 导入数据
INTO TABLE tbl_ name[FIELDS]

视图

  视图就是把好几个表组合几个到一起起个别名带时候可以直接点别名比较的方便缺点就是如果更改一个剩余的几张表都被修改

create view result_student_subject
as 
select a.SubjectNo,
select a.SubjectNo,
b.StudentName,c.SubjectName,
a.ExamDate,a.StudentResult 
 from result a
LEFT JOIN student b
on a.StudentNo=b.StudentNo  
LEFT JOIN `subject` c 
on a.SubjectNo=c.SubjectNo;