MySQL 数据库 事务、视图、索引、备份和恢复
如何执行事务
支持事务的存储引擎有InnoDB 和BDB.InnoDB存储引擎事务主要通过UNDO日志和REDO日志实现.
- 开始事务
语法: BEGIN 或 START TRAINSACTION;
- 提交事务
语法: COMMIT;
自事务开始至提交语句之间执行的所有数据更新将永久地保存在数据库数据文件中,并释放连接时占用的资源.
- 回滚事务
语法: ROLLBACK;
清除自事务起始点至该语句所做的所有数据更新操作,将数据状态回滚到事务开始前,并释放有事务控制的资源
- 设置自动提交关闭或开启
MySQL中默认开始自动提交模式,
语法: SET autocommit=0|1;
值0: 关闭自动提交
值1:开启自动提交
编写事务需遵循:
1.事务尽可能简短
2.事务中访问的数据量尽量最少
3.查询数据时尽量不要使用事务
4.在事务处理过程中尽量不要出现等待用户输入的操作
如何创建和使用视图
1.使用SQL语句创建视图
语法： CREATE VIEW 视图名
AS
<SELECT 语句>;
注意:在SQL语句命名规范中,视图一般以view_xxx或v_xxx的样式来命名.
使用SQL语句删除视图
语法:DROP VIEW [IF EXISTS] 视图名;
2.使用SQL语句查看视图数据
语法:SELECT 字段1, 字段2,…..FROM view_name;
3.使用视图的注意
- 每个视图中可以使用多个表
- 与查询相似一个视图可以嵌套另一个视图,但最好不要超过三层
- 对视图数据进行添加,更新和删除操作直接引用表中的数据
- 当视图数据来自多个表时,不允许添加和删除数据
索引
什么事索引
根据索引的存储类型,可以将索引分为B-树索引(BTREE)和哈希索引(HASH)
索引的作用是通过使用索引,大大提高数据库的检索速度,改善数据库性能.
索引分类
MySQL中,常用的索引有以下六类:
- 普通索引
普通索引时MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值.它的唯一任务是加快对数据的访问速度.
- 唯一索引
唯一索引不允许两行具有相同的索引值.创建唯一索引的列允许有空值.
- 主键索引
主键索引要求主键中的每个值时非空,唯一的.
- 复合索引
可以将多个列组合作为索引,这种索引称为复合索引.
- 全文索引
全文索引的作用是在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值.
- 空间索引
空间索引是对空间数据类型的列建立的索引.创建空间索引的列,必须将其声明为NOT NULL,
创建索引
语法: CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (column_name[length].....);
1.UNIQUE | FULLTEXT | SPATIAL: 分别表示唯一索引,全文索引和空间索引,为可选参数
2.Index_name :指定索引名
3.Table_name:指定创建索引的表名
4.column_name:指定需要创建索引的列
5.Length:指定索引长度,
删除索引
语法: DROP INDEX index_name ON table_name;
删除索引时注意:
1.删除表时,该表的所有索引将同时被删除
2.删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除.
建立索引标准:
1.频繁搜索的列
2.经常用作查询选择的列
3.经常排序,分组的列
4.经常用作哟连接的列
查看索引
语法: SHOW INDEX FROM table_name;
在查询语句后加”\G”,表示将结果集按列表示.
数据库的备份和恢复
使用mysqldump命令备份数据库
1.mysqldump命令格式
语法:mysqldump -u username -h host -ppassword dbname[tbname1[,tbname2.....]]>filename.sql
Username:表示用户名
Host表示登录用户的主机名称,如本机为主机可省略
Password表示登录密码
Dbname为需要备份的数据库
Tbname为需要备份的数据表,可指定多张表,为可选项.如备份整个数据库则此项省略
Filename.sql表示备份文件的名称
使用mysql命令恢复数据库
语法: mysql -u username -p [dbname]<filename.sql
username 表示用户
Dbname 数据库名
filename.sql 为数据库备份后的文件
注意:在执行该语句之前,必须在MySQL服务器中创建新数据库,如果不存在新数据库恢复数据库过程将会出错.
Mysql命令在DOS环境下的恢复数据库命令:
语法:source filename;
通过复制文件实现数据备份和恢复
使用此种方法备份数据库时,为了保证所备份数据的完整性,在停止MySQL数据库服务器之前,需要先执行FLUSH TABLES 语句将所有写入到数据文件中
表数据导出到文本文件
语法: SELECT columnlist FROM tablename
[WHERE contion]
INTO OUTFILE ‘filename` [OPTION];
文本文件导入到数据表]
语法: LOAD DATA INFILE filename INTO TABLE tablename [OPTION];
注意:在导入数据到数据表时,先创建表.
