当前位置：首页 > news >正文

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

news 来源：原创 2024/9/25 11:06:30

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

事务的特点：

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;