MySQL事务、视图、索引、恢复、备份、导入导出的学习

------------------------------------------------------------------------

事务的特点：

  1.原子性：事务(多条sql)是一个完成的操作，事务的各部分，不能进行分割（要么全部执行成功，要么全部执行失败）
  2.一致性：当事务完成时，必须保证完成前后是一致的。
    指在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。
  3.持久性：事务提交完成之后，对数据库的操作是永久保存下来
  4.隔离性：并发事务之间，彼此隔离，互不干涉
同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

mysql的事务（将一组sql语句放在同一批次内去执行...）
     如果一个sql语句出现问题啦，一个批次内的所有sql语句，都会被取消
     只支持：innodb数据表类型 与 bob数据表类型

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

CREATE TABLE `bank` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `money` double DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO bank VALUES(NULL,1000,'小红');
INSERT INTO bank VALUES(NULL,1000,'小李');

----------2账户能改变--------------
SET autocommit =0;  #关闭自动提交模式
START TRANSACTION ;  #开启事务

UPDATE bank SET money = money - 500 WHERE id=1;
UPDATE bank SET money = money + 500 WHERE id=2;

COMMIT;  #提交事务
SET autocommit =1;  #打开自动提交模式（默认）
----------2账户不改变--------------
SET autocommit =0;  #关闭自动提交模式
START TRANSACTION ;  #开启事务

UPDATE bank SET money = money - 500 WHERE id=1;
UPDATE bank SET money = money + 500 WHERE id=2;

ROLLBACK; #回滚事务
SET autocommit =1;  #打开自动提交模式（默认）

------------------------------------------视图-------------------------------------------

视图：

视图可以说是一种虚拟表，建立在基本表的基础上，通过关联一个表或者多个表来获取多个表中需要的字段，
      视图只是用来查询数据并不能用来存储数据信息。
   作用：筛选表中的行
         保护数据的安全，通过不提供表中敏感数据列，来实现
         降低数据库的复杂程度
         
  创建视图
     CREATE VIEW 视图名
     SELECT  列中的字段....
     FROM  查询的表     
     WHERE  添加条件

  #创建视图
      CREATE VIEW v_stu_class
      AS
      SELECT stu.stuname AS stuname ,stu.stuage AS stuage ,c.classname AS stuclassname
      FROM student stu,class c
      WHERE stu.stuclassno = c.classid;

  #查看视图
    SELECT  stuname , stuage , stuclassname FROM v_stu_class;

  #删除视图
    DROP VIEW v_stu_class;

  #查看所有视图
    SHOW TABLE STATUS WHERE COMMENT='view';

  #视图数据来自2张表以上是不能对数据进行增删改
  #视图的sql查询出来的列不能有同名的！

------------------------------------------------------------------------------------

索引：

   索引是一种有效组合数据的方式，为其快速查找到指定的记录
 作用：
   大大提升数据库的检索速度，改善数据库性能
   确保数据的唯一性
   全文检索字段进行搜索优化

 分类：
   主键索引(PRIMARY KEY)
      唯一标识一条记录
      特点：
        最常见的索引类型
        确保数据记录的唯一性
        确保特定数据记录在数据库中的位置
   唯一索引(UNIQUE)
      避免同一张表某数据列中的值重复
      与主键的区别：
         主键索引只能有一个 （不能为NULL）
         唯一索引可以有很多 (可以为NULL)
   常规索引(INDEX)
      快速定位特定数据
      注意事项
         index 和 key 关键字都可设置索引
         应添加在查找条件的字段
         不宜添加太多的常规索引，影响数据插入，删除和修改操作
   全文索引(FULLTEXT)
      快速定位特定数据
      注意：
         只能用于MyISAM类型的数据表
         只能用于char  varchar  text 数据列的类型
         适合于大型数据集

 CREATE TABLE `test` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `testno` VARCHAR(10) DEFAULT NULL,
  `c` VARCHAR(50) DEFAULT NULL,
  `d` VARCHAR(20) DEFAULT NULL,
  `e` TEXT,
  PRIMARY KEY (id),
  UNIQUE KEY(testno),
  INDEX(c),
  FULLTEXT(e)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

 CREATE TABLE `test2` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `testno` varchar(10) DEFAULT NULL,
  `c` varchar(50) DEFAULT NULL,
  `d` varchar(20) DEFAULT NULL,
  `e` text,
  PRIMARY KEY (id)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

#alter table test add PRIMARY KEY (id);
alter table test2 add UNIQUE KEY(testno);
alter table test2 add INDEX(c);
alter table test2 add FULLTEXT(e);
 

#显示所有索引
SHOW INDEX FROM test;

#插入数据    
INSERT INTO test VALUES(1,'101','abc','def','dfjdljfldjflsjdlfsdfs');
INSERT INTO test VALUES(2,'102','aabbc','deef','dfjdl哈sjdlfsdfs');
INSERT INTO test VALUES(3,'103','aaabbbc','deeef','dfjdljfld呵呵呵lsjdlfsdfs');
INSERT INTO test VALUES(4,'104','aaaabbbbc','defff','dfjdlj快快快jdlfsdfs');

 
#查看使用索引情况 ->关键字 EXPLAIN
EXPLAIN SELECT * FROM test WHERE id = 1;
EXPLAIN SELECT * FROM test WHERE testno ='101';
EXPLAIN SELECT * FROM test WHERE c ='aabbc';
#全文索引 错误！！！EXPLAIN SELECT * FROM test WHERE e ='dfjdljfld呵呵呵lsjdlfsdf';
EXPLAIN SELECT * FROM test WHERE MATCH(e) AGAINST('dfjdljfld呵呵呵lsjdlfsdf');
EXPLAIN SELECT * FROM test WHERE id = 1 AND testno ='101';    #只显示，应用主键索引

#删除索引
alter table test drop index  testno ;
alter table test drop index  c ;
alter table test drop index  e ;

注意：
   索引不是越多越好！
   不要对经常变动的数据添加索引
   小数据量的表建议不要添加索引
   索引一般应添加在查询条件的字段
 
   小数据量，每次查询时，会进行索引值的遍历，查询记录比索引遍历时间都短，没有意义，增加数据库压力
   索引过多，会增大磁盘空间，影响增删改，在数据修改的时候，索引也会改变

   添加索引的原则：
     频繁搜索的列
     经常用作查询条件的列
     经常排序分组的列
     经常用作连接的列（主外键）
     列（性别->男或女）= 不适合添加索引

/*创建索引*/
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

/*删除索引*/
drop index tableName.index_name;
   

----------------------------------数据库恢复和备份----------------------------------

#数据备份与恢复

#方式一：mysql命令方式

#备份DOS：
#备份myschool数据库的student及result表
mysqldump -uroot -pbdqn myschool student result>h:/myschool.sql

#恢复1:
mysql -uroot -pbdqn;
USE test;
source h:/myschool.sql;

#恢复2:
mysql -uroot -pbdqn test<h:/myschool.sql;

#方式二：工具方式
选择需要备份的数据库，右键-》备份/导出-》以SQL转储文件备份数据库

选择需要恢复的数据库，右键-》导入-》从sql转储文件导入数据库

数据的导出和导入

----------------------------------数据的导出和导入----------------------------------
#备份数据库myschool中的student表中的studentno及studentname列到文件myschool3.sql中
USE myschool;
SELECT  studentno,  studentname  INTO  OUTFILE  'e:/myschool3.sql'  FROM  student;

#恢复文件myschool3.sql中的数据到test数据库的t2表中来
USE test;
CREATE TABLE t2(
    id INT(4),
    sname VARCHAR(20)
)
LOAD  DATA  INFILE  'e:/myschool3.sql'  INTO  TABLE  t2(id, sname);

SELECT * FROM t2;