数据库视图与索引经典题

红太狼 2023-10-13 12:21 3阅读 0赞

> https://www.jianshu.com/p/d0aa8ac94f40

#### 文章目录 ####

*  视图与索引
 *  视图
 *   *  定义视图
     *  创建视图
     *   *  创建单源表视图
         *  创建多源表视图
         *  创建基于视图的视图
         *  创建带表达式的视图
         *  创建含统计信息的视图
     *  删除视图
     *  查询视图
     *  视图的作用
 *  索引
 *   *  索引的概念
     *  索引使用原则
     *  索引的类型
     *  设计索引
     *  复合索引(A,B,C)

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

## 视图与索引 ##

## 视图 ##

可以通过视图对某些用户查询的数据进行限制  
可以把一些常用的、复杂的统计类的做成视图

**使用视图**：当做一张虚拟的表来使用

**视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。**  
数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。

所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。  
从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。  
视图只供查询，数据不可更改

### 定义视图 ###

用CREATE TABEL语句创建的表叫基本表（Base Table）。

视图（View）是从一个或多个基本表或视图中导出的表，视图的结构和数据都是建立在对基本表的查询基础上的。

视图不是真实存在的表，而是一个虚拟表，数据库中只存储视图的定义，而没有存储视图对应的数据，视图中的数据是从基本表中选取出来的，这些数据并不实际的按视图结构存储在数据库中，而是存储在原来的基本表中。

### 创建视图 ###

创建视图的SQL语句一般格式为：

create view 视图名 as 查询语句;

查看当前数据库中的所有视图：

SHOW FULL TABLES IN 数据库名 WHERE TABLE_TYPE LIKE 'VIEW';

**创建视图注意事项**

（1）视图名必须遵循标识符命名规则，且对每类用户视图名必须是唯一的，即对不同用户定义相同的视图，也必须使用不同的名字。

（2） SELECT查询子句的查询内容就是视图的内容。SELECT语句通常不允许含有ORDER BY子句和DISTINCT子句。

SELECT语句中查询的表和视图即新创建的视图所参照的表和视图。

（3）视图列名列表是视图中所包含的列。若使用与基本表中相同的列名，则可以省略。若指定列名列表，则需全部指定，不能只给出一部分。以下情况要求必须指定视图的全部列名：

①由算术表达式、系统内置函数或者常量得到的列；

②多表连接查询时选出的同名列；

③希望视图中的列名与基表中的列名不同的时候。

（4）WITH CHECK OPTION子句表示在视图上执行UPDATE，INSERT或DELETE操作时要保证所修改的行满足视图定义中的谓词条件（即SELECT查询子句中的限定条件，如Where条件），这样可以确保数据修改后，仍可通过视图看到修改的数据。

**数据库**

-- 创建图书表Book
    CREATE TABLE Book(
      book_id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(50) NOT NULL,
      author VARCHAR(50) NOT NULL,
      publisher VARCHAR(50) NOT NULL,
      publish_date DATE NOT NULL,
      price DECIMAL(10,2) NOT NULL
    );
    
    -- 创建读者表Reader
    CREATE TABLE Reader(
      reader_id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(50) NOT NULL,
      gender CHAR(1) NOT NULL,
      birthdate DATE NOT NULL,
      tel VARCHAR(20) NOT NULL
    );
    
    -- 创建借阅表Borrow
    CREATE TABLE Borrow(
      borrow_id INT AUTO_INCREMENT PRIMARY KEY,
      reader_id INT NOT NULL,
      book_id INT NOT NULL,
      borrow_date DATE NOT NULL,
      return_date DATE DEFAULT NULL,
      FOREIGN KEY (reader_id) REFERENCES Reader(reader_id),
      FOREIGN KEY (book_id) REFERENCES Book(book_id)
    );
    
    -- 向图书表Book中插入数据
    INSERT INTO Book (name, author, publisher, publish_date, price)
    VALUES 
    ('MySQL从入门到精通', '张三', '清华大学出版社', '2020-03-01', 59.00),
    ('Java编程思想', 'Bruce Eckel', '机械工业出版社', '2018-08-01', 88.00),
    ('Python数据分析与挖掘实战', '李笑来', '人民邮电出版社', '2019-06-01', 45.00),
    ('计算机网络', '谢希仁', '清华大学出版社', '2017-03-01', 69.00),
    ('算法导论', 'Thomas H. Cormen', '机械工业出版社', '2020-05-01', 99.00);
    
    -- 向读者表Reader中插入数据
    INSERT INTO Reader (name, gender, birthdate, tel)
    VALUES 
    ('张三', '男', '1990-01-01', '13812345678'),
    ('李四', '女', '1992-05-05', '13987654321'),
    ('王五', '男', '1995-12-18', '13666666666'),
    ('赵六', '女', '1991-07-23', '13333333333'),
    ('周七', '男', '1994-11-11', '13588888888');
    
    -- 向借阅表Borrow中插入数据
    INSERT INTO Borrow (reader_id, book_id, borrow_date, return_date)
    VALUES 
    (1, 1, '2020-02-01', NULL),
    (2, 2, '2020-03-01', NULL),
    (3, 3, '2020-04-01', NULL),
    (1, 2, '2020-05-01', '2020-06-01'),
    (4, 4, '2020-06-01', NULL),
    (5, 5, '2020-07-01', NULL);

#### 创建单源表视图 ####

一个视图可以从单个或多个基本表导出数据，即单源表视图或多源表视图。但是，一个单源表视图不一定保留该基本表的主键或唯一键，而可能只是一个简单的行列子集视图

【例】创建价格高于30元的图书视图BookView1。

CREATE VIEW BookView1
    AS
    SELECT * FROM Book
    WHERE price > 30

![在这里插入图片描述][225bee23658a4147b1fc805e6cfcabad.png]  
**使用视图**  
![在这里插入图片描述][9314fb71217e4d0a9f91c93d0fe2f760.png]

【例】创建价格高于30元的图书视图BookView2。并要保证对该视图的修改都要符合价格高于30元这个条件。

CREATE VIEW BookView2
    AS
    SELECT book_id, name, author, publisher, price 
    FROM Book
    WHERE price > 30
    WITH CHECK OPTION;

![在这里插入图片描述][32fe43b95d79434bb15621519cd61302.png]

#### 创建多源表视图 ####

多源表视图是指创建视图时的子查询中用了多个源表。

多源表视图一般只用于查询，不用于修改数据。

【例】创建R\_B\_Book视图，查询所有读者借阅图书的读者编号、姓名、图书编号、书名、出版社、价格、借阅日期信息。

CREATE VIEW R_B_Book (reader_id, name, book_id,book_name, publisher, price, borrow_date)
    AS 
    SELECT R.reader_id, R.name, B.book_id,B.name AS book_name, B.publisher, B.price, W.borrow_date AS borrow_date
    FROM Reader AS R 
    JOIN Borrow AS W ON R.reader_id = W.reader_id
    JOIN Book AS B ON B.book_id = W.book_id;

![在这里插入图片描述][a069afc9c2514222b1f4fd1eede9ec96.png]

#### 创建基于视图的视图 ####

视图可以建立在其它已经创建好的视图上，即创建基于视图的视图。

【例】基于上例中的视图R\_B\_Book，创建”张三”的读者借阅的图书书名和出版社信息和借阅日期的视图BorrowV

CREATE VIEW BorrowV
    AS
       SELECT Book.name AS 书名, Book.publisher AS 出版社, Borrow.borrow_date AS 借阅日期 
       FROM Book 
       JOIN Borrow ON Book.book_id = Borrow.book_id 
       JOIN Reader ON Borrow.reader_id = Reader.reader_id 
       WHERE Reader.name = '张三';

![在这里插入图片描述][4377129defe54dc78fb33107e0be500e.png]

#### 创建带表达式的视图 ####

在定义视图时可以根据实际需要设置一些派生属性列，在这些派生属性列中保存经过计算的值。称它们为虚拟列。带虚拟列的视图也称带表达式的视图。

【例】创建读者信息的视图ReaderInfo,包括读者编号、姓名和年龄，在视图中的列名分别为ID，Name和Age。

CREATE VIEW ReaderInfo (ID, Name, Age)
    AS
      SELECT reader_id, name, TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS Age
      FROM Reader;

![在这里插入图片描述][da04b8c3699c4496a4923e7824962944.png]

#### 创建含统计信息的视图 ####

还可以用带有集合函数和GROUP BY子句的查询来创建视图，这种视图称为分组视图。

【例】创建每个出版社出版图书的平均价格的视图PerPublish\_AVG。

CREATE VIEW PerPublish_AVG(Publish，AVG_Price)
    AS
      SELECT publish, AVG(price) 
      FROM Book
      GROUP BY publish

![在这里插入图片描述][8e5728a9cf2f4477bf239ffb5e43e3eb.png]

### 删除视图 ###

删除视图的SQL语句的格式为：

DROP VIEW 视图名

删除视图时注意：按照参照的逆序删除

【例】删除图书视图BookView

DROP VIEW BookView

### 查询视图 ###

视图是一张虚表，可以同基本表一样进行查询，但需要注意查询视图时应使用视图定义时的列名。

【例】基于视图RB\_Book查询借阅《数据库原理》一书的读者ID和name

SELECT 读者ID,name FROM RB_Book
    WHERE 书名='数据库原理'

【例】基于视图ReaderInfo查询年龄高于20岁的读者ID和name

SELECT id,name FROM ReaderInfo WHERE AGE>=20

### 视图的作用 ###

1、视图能够简化用户的操作

2、视图使用户能以多种角度看待同一数据。

3、视图对重构数据库提供了一定程度的逻辑独立性。

4、视图能够对机密数据提供安全保护。

5、适当的利用视图可以更清晰的表达查询。

## 索引 ##

> 索引的作用提升查询语句的执行速度，索引本身的维护也需要时间和空间，所以索引要适当使用  
> 索引中常见的数据结构有以下几种：Hash表、二叉树、红黑树、B-Tree、B+Tree

没有索引：逐行扫描（顺序查找）

有索引：快速定位数据行(索引查找)

### 索引的概念 ###

索引实质上是一个单独的、物理的数据库结构，它是表中一个或多个列（称为搜索关键字）的值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。  
![在这里插入图片描述][24b0af31bc6b4661a70d4974ec544dae.png]

### 索引使用原则 ###

并不是每个字段都设置索引就好，也不是索引越多越好，而是需要合理的使用。

1.  对经常更新的表就避免对其进行过多的索引。
2.  对经常用于查询的字段应该创建索引，但是这列数据不能经常修改
3.  数据量小的表最好不要使用索引（一般是少于十万行的，大表才需要）。
4.  相同值较多的字段上不要建立索引（比如"性别"字段）。相反的，在不同值较多的字段上可以建立索引（比如姓名）。

### 索引的类型 ###

根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型。

一种是数据表的物理顺序与索引顺序相同的索引，称为聚集索引；

另一种是数据表的物理顺序与索引顺序不相同的索引，称为非聚集索引；

**聚集索引在使用中具有如下特点：**  
(1)、每一个表只能有一个聚集索引；

(2)、在创建任何非聚集索引前创建聚集索引；

(3)、聚集索引的平均大小大约是数据表的百分之五。

**每一个表只能有一个聚集索引，因为表中数据的物理顺序只有一个；**

聚集索引的平均大小大约是数据表的百分之五，但是，实际聚集索引的大小常常根据索引列的大小变化而变化。

这大大减少了磁盘读写的次数。

**非聚集索引在使用中具有如下特点：**  
(1)、非聚集索引具有与表的数据完全分离的结构。

(2)、非聚集索引表示行的逻辑顺序。

(3)、在非聚集索引中，数据存放在一个地方，索引存放在另一个地方，并用指针指出数据的存储位置。索引中项目是按照关键值的顺序存放，但是表中的数据则是按照不同的顺序存放。

**缺省情况下创建的索引是非聚集索引。**

**索引的优缺点**  
**索引的主要优点如下：**

①可以大大加快数据的检索速度。

②通过创建唯一性索引，可以确保表中每一行数据的唯一性。

③可以加速表与表之间的连接，特别有利于实现数据的参照完整性。

④在使用分组子句和排序子句进行数据检索时，可以显著提高查询中分组和排序的效率。

**可以大大加快数据的检索速度，这也是创建索引的最主要原因**

**索引的主要缺点如下：**

①创建索引要耗费时间。

②索引要占据数据库的物理空间。

③维护索引要花费很多的时间。

创建索引和维护索引要耗费时间，而且这种时间会随着数据量的增加而增加。

索引要占据数据库的物理空间，索引越多，占据的空间越多。

维护索引要花费很多的时间，尤其是在增加、删除和修改表中数据的时候。

### 设计索引 ###

**对于以下这些列不适合创建索引：**

①对于那些在查询中很少使用或很少参考的列上不适合创建索引。

②对于那些重复值太多的列也不适合建索引。

③当在某列做的修改远大于在其上做的查询操作时，也不适合在该列创建索引。

④对小型表一般也无须创建索引，即便进行索引可能不会产生明显优化效果，而且往往得不偿失。

**一般来说，适合在以下的这些列上创建索引：**

①在经常检索的列上创建索引，可以加快检索速度。

②在主码列上创建索引，可以强制该列的唯一性并组织表中数据的排列结构。

③在经常使用在WHERE子句中的列上创建索引，加快条件判断速度。

④在经常需要排序的列上创建索引，加快排序查询的时间。

⑤在经常用在多表连接的列上创建索引，可以加快连接的速度。

### 复合索引(A,B,C) ###

下列三种查询组合都执行索引吗？  
A,B、B,C、A,C、A、ABC  
B,C不执行 复合索引只要查询条件中使用了最左侧的列，就会执行索引

[225bee23658a4147b1fc805e6cfcabad.png]: https://img-blog.csdnimg.cn/225bee23658a4147b1fc805e6cfcabad.png
[9314fb71217e4d0a9f91c93d0fe2f760.png]: https://img-blog.csdnimg.cn/9314fb71217e4d0a9f91c93d0fe2f760.png
[32fe43b95d79434bb15621519cd61302.png]: https://img-blog.csdnimg.cn/32fe43b95d79434bb15621519cd61302.png
[a069afc9c2514222b1f4fd1eede9ec96.png]: https://img-blog.csdnimg.cn/a069afc9c2514222b1f4fd1eede9ec96.png
[4377129defe54dc78fb33107e0be500e.png]: https://img-blog.csdnimg.cn/4377129defe54dc78fb33107e0be500e.png
[da04b8c3699c4496a4923e7824962944.png]: https://img-blog.csdnimg.cn/da04b8c3699c4496a4923e7824962944.png
[8e5728a9cf2f4477bf239ffb5e43e3eb.png]: https://img-blog.csdnimg.cn/8e5728a9cf2f4477bf239ffb5e43e3eb.png
[24b0af31bc6b4661a70d4974ec544dae.png]: https://img-blog.csdnimg.cn/24b0af31bc6b4661a70d4974ec544dae.png