数据库视图和索引

辣椒日记

于 2024-09-12 00:08:59 发布

阅读量438

点赞数 23

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/qq_46091002/article/details/142143195

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

参考链接：

数据库的视图和索引的概念和区别_索引和视图的区别-CSDN博客

MySQL 数据库--索引（理论详解及实例演示）_数据库索引-CSDN博客

1.视图

视图是从一个或多个表中导出来的表，是一种不是一种真正存在的概念。这样，用户可以不用看到整个数据库中的数据，而之关心对自己有用的数据。

视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。

视图是基于 SQL 语句的结果集的可视化表现。它是一个虚拟表，其内容由查询定义。视图可以包含来自一个或多个表的列，甚至可以包含常量和计算字段。

1.1创建视图

语法

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

举例

单个表

假设你有一个名为 employees 的表，包含 employee_id, name, department, 和 salary 列。你想要创建一个视图，只显示每个部门的平均工资：

CREATE VIEW department_average_salary AS
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

多个表

假设我们有两个表：employees 和 departments。employees 表包含员工的详细信息，而 departments 表包含部门的信息。现在，我们想要创建一个视图，该视图显示每个员工的姓名、所在部门的名称以及部门的预算。

首先，这里是两个表的示例结构：

employees 表:

employee_id (员工ID)
name (姓名)
department_id (部门ID)
position (职位)

departments 表:

department_id (部门ID)
department_name (部门名称)
budget (预算)

我们想要创建一个视图，这个视图将包含员工的姓名、所在部门的名称以及部门的预算。以下是创建这个视图的 SQL 语句：

CREATE VIEW employee_departments AS
SELECT e.name AS employee_name, d.department_name, d.budget
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

总结

就是 create view 视图名 as 后面就是正常的sql语句。

1.2 修改视图

ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

1.3 删除视图

DROP VIEW view_name;

1.4 视图特点

视图不存储数据，它们是动态生成的，每次查询视图时都会执行定义视图的 SQL 语句。
可以有权限控制，允许用户访问视图而不必直接访问底层表。【增加安全性】
可以用于实现复杂的查询逻辑，如连接（JOIN）多个表。

1.5用途

提供数据的逻辑表示，简化复杂的 SQL 操作。
限制用户访问特定数据，增强数据的安全性。
作为数据的抽象层，使得应用程序不必关心底层数据结构的变化。

2.索引

索引，可以理解为是一本书的目录，它记录了数据在数据库中存放的位置，就像一本书的目录，记录了，某一页在这本书的那个位置。相同地，索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据库表里所有记录的引用指针。
MySQL中索引的存储类型有两种：BTREE（树）和 HASH（哈希），具体和表的存储引擎有关。MyISAM和InnoDB存储引擎只支持BTREE索引。

重新举一个例子：

想象一下，你有一书架的书，每本书都有一个独特的标题，你可以很容易地通过书名找到任何一本书。但是，如果你想要找到所有关于特定主题的书，比如“烹饪”，你可能需要一本索引书，它列出了关于烹饪的所有书籍的标题和它们在书架上的位置。这样，你就不用一本一本地检查每一本书的封面了，你可以直接去索引书中标记的位置找到所有烹饪书籍。

在数据库中，索引的作用类似。数据库中的数据就像是你书架上的书籍，而索引就是那本帮助你快速找到特定信息的索引书。

加快搜索速度：就像索引书让你快速找到烹饪书籍一样，数据库索引可以让你快速检索到特定的数据记录，而不需要扫描整个数据库。
组织数据：索引可以帮助数据库以特定的顺序存储和检索数据，比如按照日期或字母顺序。
提高效率：当你需要执行搜索、排序或分组等操作时，索引可以显著提高这些操作的效率

创建索引就像是在数据库中创建了一个快速通道，它允许数据库管理系统（DBMS）更快地找到数据，而不必检查数据库中的每一条记录。但是，就像索引书需要占用额外的空间一样，数据库索引也会占用额外的存储空间，并且在数据更新时（如插入、删除或修改记录）需要维护索引，这可能会稍微减慢这些操作的速度。

2.1 索引的分类

2.1.1 普通索引

这是最基本的索引类型
而且它没有唯一性之类的限制

2.1.2唯一性索引

与普通索引类似，区别是唯一索引列的所有值都只能出现一次，即必须唯一
当现有数据库中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存
数据库还可能防止添加将在表中创建重复键值的新数据
例如，如果在员工信息统计表中对员工的姓（name）上创建了唯一索引，则任何两个员工都不能同姓

2.1.2 主键索引

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”
在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型
该索引要求主键中每个值都唯

2.1.3 全文索引

索引类型为 FULLTEXT
适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息
全文索引可以在 CHAR、VARCHAR 或 TEXT 类型的列上创建
每个表只允许有一个全文索引
主要用于搜索大型文本或者二进制列中的关键词

2.1.4 组合索引（单列索引与多列索引）

索引可以是单列上创建的索引，也可以是在多列上创建的索引
多列索引可以区分其中一列可能有相同值的行
如果经常同时搜索两列、多列、按两列或多列排序时，索引也很有帮助
例如，如果经常在同一查询中为姓和名两列设置查询条件，那么这两列上创建多列索引将很有意义

2.2 索引的创建

2.2.1 普通索引的创建

CREATE INDEX 索引名 ON 表名 (列名[(length)]);

#(列名(length))：length是可选项，下同
#如果忽略 length 的值，则使用整个列的值作为索引
#如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小
#索引名建议以“_index”结尾


例：
create index phone_index on member (phone);
#直接创建索引

select phone from member

2.2.2 唯一索引的创建

CREATE UNIQUE INDEX 索引名 ON 表名(列名);


例：
create unique index address_index on member (address);
#注意，创建唯一索引必须得满足每个值都是唯一的，否则无法创建

show create table member;

2.2.3 创建主键索引

CREATE TABLE 表名 ([...],PRIMARY KEY (列名));


例：
create table test1 (id int primary key,name varchar(20));
#这里用了两种方式创建，一种是在字段中直接指定，第二种是在字段结束后指定
create table test2 (id int,name varchar(20),primary key (id));

show create table test1;
show create table test2;

2.2.4 全文索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);


例：
create fulltext index remark_index on member (remark);
#全文索引 fulltext，全文只可在 char、varchar、text 类型上创建，且每个表只允许有一个
show create table member;

2.2.5 组合索引

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';


例:
create table test001 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));

show create table test001;

insert into test001 values(1,'wangsansan','123123');

select * from test001 where name='wangsansan' and id=1;