视图

视图是一种虚拟表 ，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。

视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

视图的创建和删除只影响视图本身，不影响对应的基表。

但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化。

视图操作

创建视图语法： CREATE VIEW 视图名 AS 查询语句

1
2
3
4
# 如 将教师与学生的查询结果存放为一张视图
# 其中 newview 就是视图，你可以将表的操作去使用
CREATE VIEW newview AS
SELECT a.name,a.age,b.name from student as a left join teacher as b on a.tea = b.stu;

当我们创建好一张视图之后，还可以在它的基础上继续创建视图

1
2
CREATE VIEW newtwoview AS
select name from newview where name = '罗小黑';

基本语法

  • 创建视图
1
CREATE VIEW 视图名 AS 查询语句
  • 查看视图结构
1
DESC/ DESCRIBE 视图名称;
  • 查看视图的属性信息
1
2
#查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G
  • 查看视图的详细定义信息
1
SHOW CREATE VIEW 视图名称
  • 修改视图
1
ALTER VIEW 视图名称 AS 查询语句
  • 删除视图
1
2
# 删除视图只是删除视图的定义，并不会删除基表的数据
DROP VIEW IF EXISTS 视图名称;

视图优点

1.操作简单

将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间

的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简

化了开发人员对数据库的操作。

2.减少数据冗余

视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语

句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

3.数据安全

MySQL将用户对数据的访问限制在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。

4.适应灵活多变的需求

当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较

大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。

5.能够分解复杂的查询逻辑

数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图

获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

事务

事务四大特性(简称ACID)

  • 原子性（atomicity）

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

  • 一致性（consistency）

数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

  • 隔离性（isolation）

通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

  • 持久性（durability）

一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

语法：

  • 查看表的创建语句

    表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎

1
show create table goods;
  • 开启事务
1
2
3
begin;
# or
start transaction;
  • 提交事务
1
commit;
  • 回滚事务
1
rollback;

索引

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。

  1. Primary Key（聚集索引）：InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。
  2. 单列索引：单列索引即一个索引只包含单个列
  3. 组合索引：组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合
  4. Unique（唯一索引）：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值
  5. Key（普通索引）：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值
  6. FULLTEXT（全文索引）：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建
  7. SPATIAL（空间索引）：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL

语法：

  • 查看索引
1
show index from 表名;
  • 创建事务
1
create index 索引名称 on 表名(字段名称(长度))
  • 删除索引
1
drop index 索引名称 on 表名;
  • 查看执行的时间：
1
show profiles;

要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

建立索引会占用磁盘空间