mysql 备份恢复后索引_关于MySQL 事务,视图,索引,数据库备份,恢复

/*创建数据库*/

CREATE DATABASE `mybank`;

/*创建表*/

USE mybank;

CREATE TABLE `bank`(

`customerName` CHAR(10),        #用户名

`currentMoney` DECIMAL(10,2)         #当前余额

);

/*插入数据*/

INSERT INTO `bank` (`customerName`,`currentMoney`) VALUES (‘张三‘,1000);

INSERT INTO `bank` (`customerName`,`currentMoney`) VALUES (‘李四‘,1);

2. --转账测试:张三希望通过转账,直接汇钱给李四500元

UPDATE `bank` SET `currentMoney` =`currentMoney`-500

WHERE `customerName` = ‘张三‘;

UPDATE `bank` SET `currentMoney` =`currentMoney`+500

WHERE `customerName` = ‘李四‘;

3. 执行事务 并提交事务

USE mybank;

/*设置结果集以gbk编码格式显示*/

SET NAMES gbk;

/*开始事务*/

BEGIN;

UPDATE `bank` SET `currentMoney` =`currentMoney`-500

WHERE `customerName` = ‘张三‘;

UPDATE `bank` SET `currentMoney` =`currentMoney`+500

WHERE `customerName` = ‘李四‘;

COMMIT;    　　　　#提交事务,写入硬盘

4. 开始事务  回滚事务

BEGIN;

UPDATE `bank` SET `currentMoney` = `currentMoney` -1000 WHERE `customerName` = ‘张三‘;

/*回滚*/

ROLLBACK;

5.设置自动提交关闭或开启

/*关闭事务自动提交*/

SET autocommit = 0;

/*转账*/

UPDATE `bank` SET `currentMoney` =`currentMoney`-500

WHERE `customerName` = ‘张三‘;

UPDATE `bank` SET `currentMoney` =`currentMoney`+500

WHERE `customerName` = ‘李四‘;

/*提交*/

COMMIT;

UPDATE `bank` SET `currentMoney` = `currentMoney` -1000 WHERE `customerName` = ‘张三‘;

/*回滚事务*/

ROLLBACK;

6. 创建视图

/*当前数据库*/

USE myschool;

DROP VIEW IF EXISTS `view_student_result`;

/*创建视图*/

CREATE VIEW `view_student_result`

AS

/*需要创建视图的数据*/

SELECT `studentName` AS 姓名,student.`studentNo` 学号,`studentResult` AS 成绩,`subjectName` AS 课程名称.`examDate` AS 考试日期

FROM `student`

INNER JOIN `result` ON `student`.`studentNo` = `result`.`studentNo`

INNER JOIN `subject` ON `result`.`subjectNo` = `subject`.`studentNo`

WHERE `subject`.`subjectNo`=(

SELECT `subjectNo` FROM `subject` WHERE `subjectName` = ‘Logic Java‘

)AND `examDate` =(

SELECT MAX(`examDate`) FROM `result`.`subject` WHERE `result`.`subjectNo` = `subject`.`subjectNo`

AND `subjectName` = ‘Logic Java‘

);

7. 创建索引

USE myschool;

/*创建索引*/

CREATE INDEX `index_student_studentName`

ON `student`(`studentName`);

8. 使用DOS命令备份数据库

mysqldump -u username -h -ppassword

dbname[tbname1 [,tbname2]........] > filename.sql

例:

mysqldump -u root -p myschool student > d:\backup\myschool.sql

EnterPassword:

9. 使用后DOS命令恢复数据库

1) .恢复数据库前先在MySQL服务器中创建新的数据库.

2). 执行 mysql -u username -p [dbname] 

例:

mysql -u root -p schoolDB

10. 使用source命令恢复数据库

语法:   source  filename;

例:

/*创建数据库*/

CREATE DATABASE myschoolDB2;

USE myschoolDB2;

/*恢复数据库*/

source ‘d:\backup\myschool.sql‘;

11. 将表数据导出到文本文件

语法: select 列名 from 表名 [where 条件,条件2......]

into outfile ‘filename‘ ;

例:

SELECT * FROM `result` WHERE `subjectNo` =

(SELECT  `subjectNo` FROM `subject`  WHERE `subjectName` = ‘Logic Java‘)

INTO OUTFILE ‘d: /backup/result.txt‘;

12. 将文本文件导入到数据表

语法: LOAD DATA INFILE ‘filename‘ INTO TABLE ‘tablename ‘;

1) . 先创建导入数据的表结构

USE myschoolDB2;

/*创建result表结构*/

CREATE TABLE `result`

(

`studentNo` INT(4) NOT NULL COMMENT ‘学号‘,

`subjectNo` INT(4) NOT NULL COMMENT ‘课程编号‘,

`examDate` DATETIME NOT NULL COMMENT ‘考试日期‘,

`studentResult` INT(4) NOT NULL COMMENT ‘考试成绩‘,

PRIMARY KEY (`studentNo`,`subjectNo`,`examDate`)

)ENGINE = INNODB DEFAULT CHARSET utf8;

2).导入数据

LOAD DATA INFILE ‘路径.txt‘ INTO TABLE result;

/*查看result表数据*/

SELECT * FROM result;

原文：http://www.cnblogs.com/ase0311/p/7601139.html