数据库-MySQL拓展

0 前言

       数据库拓展是指数据库的进阶和高级内容，它是在数据库基础的基础上，进一步提高和优化数据库的性能和功能。数据库拓展中包括了事务、视图、索引等内容，它们是进行数据管理和分析的重要技术。

事务是指一组逻辑上相关的数据库操作，它们要么全部成功，要么全部失败，不会出现中间状态。事务可以实现数据的一致性和完整性，例如转账、购物等。

视图是指一种虚拟的数据表，它是基于一个或多个数据表的查询结果，不会占用额外的存储空间。视图可以实现数据的抽象和封装，例如隐藏敏感信息、简化复杂查询等。

索引是指一种用于加速数据检索的数据结构，它是对数据表中的一个或多个字段进行排序和存储的副本，可以减少数据扫描的次数。索引可以实现数据的快速查询和排序，例如模糊查询、范围查询等。

本文将介绍数据库拓展中事务、视图、索引的介绍，包括了它们的概念、特点、分类和应用，希望能够对读者有所帮助。

1 事务

1.1 事务的定义

事务就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内的所有sql都将被取消执行。

1.2 事务的ACID原则

(1)原子性
一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性
(2)一致性
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。[例如两账户的和不变]
(3)隔离性
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
(4)持久性
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

1.3 事务的常用命令

开始事务 begin或start transaction
提交事务 commit
回滚事务 rollback

1.4 事务提交

--必须创建InnoDB数据库，MyISAM数据库不支持事务
--创建表
create table account(
  id int(4) primary key auto_increment,
  username varchar(32) not null,
  cash int(32) not null
)
--添加数据
insert into account(username,cash) values('A',2000);
insert into account(username,cash) values('B',1000);
--关闭MySQL自动提交事务
set autocommit = 0;
--开始事务
start TRANSACTION;
--事务操作
update account set cash = cash -500 where username = 'A';
update account set cash = cash +500 where username = 'B';
--提交事务
COMMIT;
--开启自动提交
set autocommit = 1;
--查看表数据
select * from account;

1.5 事务回滚

--创建表
create table account_new(
  id int(4) primary key auto_increment,
  username varchar(32) not null,
  cash int(32) not null
)
--添加数据
insert into account_new(username,cash) values('A',2000);
insert into account_new(username,cash) values('B',1000);
--关闭MySQL自动提交事务
set autocommit = 0;
--开始事务
start TRANSACTION;
--事务操作
update account_new set cash = cash -500 where username = 'A';
update account_new set cash = cash +500 where username = 'B';
--回滚
rollback;
--提交事务
COMMIT;
--开启自动提交
set autocommit = 1;
--查看表数据
select * from account;

2 视图

2.1 为什么需要视图

不同的维护人员关注不同列的字段
保证信息的安全

2.2 视图的定义

1.视图是一张虚拟表
   表示一张表的部分数据或多张表的综合数据
   其结构和数据是建立在对表的查询基础上
2.视图中不存放数据
   数据存放在视图所引用的原始表中
3.一个原始表，根据不同用户的不同需求，可以创建不同的视图

2.3 视图的作用

1）筛选表中的行
2）防止未经许可的用户访问敏感数据
3）降低数据库的复杂程度
4）将多个物理数据库抽象为一个逻辑数据库

2.4 视图操作命令

--创建表
CREATE TABLE t_user (
	id INT NOT NULL auto_increment,
	NAME VARCHAR ( 255 ),
	pwd VARCHAR ( 255 ),
	weight DOUBLE,
	height DOUBLE,
PRIMARY KEY ( id ) 
)
--添加数据
insert into t_user() values(1,'zs','123',80.0,80.0);
insert into t_user() values(2,'ls','789',60.0,60.0);
--创建视图
CREATE VIEW v_user01 as select name,pwd from t_user;
--修改视图（修改视图数据的同时会修改表数据）
update v_user01 set name = 'xxx' where id = 1;
--查看视图数据
SELECT * from v_user01;
--查看表数据
select * from t_user;
--删除视图
DROP VIEW v_user01;
--查看所有表和视图
show tables;
--查看所有视图
show table status where comment = 'view';

3 索引

3.1 索引的作用

大大提高数据库的检索速度
改善数据库性能
拓展：可以提高查询效率，但会降低增删改的效率

3.2 索引种类

索引种类	索引特点
普通索引	没什么限制，允许插入空值和重复值
唯一索引	不允许重复值，允许空值
主键索引	不允许空值，不允许重复值
全文索引	用来查找文本中的关键字，而不是直接与字段值进行比较
组合索引	遵循最左前缀原则，(a,b,c)创建索引，共有(a)，(a,b)，(a,b,c)三种索引
空间索引	对空间数据类型的字段建立的索引

3.3 索引操作

--创建表
CREATE TABLE t_person (
	id INT,
	id_number int,
	NAME VARCHAR ( 255 ),
	age INT,
sex VARCHAR ( 255 ) 
)
--添加主键索引
alter table t_person add PRIMARY key(id);
--添加唯一索引
alter table t_person add unique(id_number);
--添加普通索引
alter table t_person add index(name);
--添加组合索引
alter table t_person add index(age,sex);
--查询索引
show index from t_person;
--删除索引
drop index age on t_person;
--删除主键索引
alter table t_person drop PRIMARY KEY;

4 拓展

mysql备份数据库：
命令行输入mysqldump -uroot -p 库名 表明>文件名.sql
mysql恢复数据库：
命令行输入mysql -uroot -p 库名<文件名.sql