数据库临时表和视图的区别、和用法

灵动的水

浏览: 194526 次
性别:
来自: 济南

最近访客更多访客>>

liufengfan666

delin_OneZero

IT_Dodo

xklc

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

mysql sql sql server

临时表
    临时表与永久表相似，但临时表存储在 tempdb 中，当不再使用时会自动删除。
    临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。
    例如，如果创建了 employees 表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表 #employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表，则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该表，则 SQL Server 将在您断开连接并且所有其他会话不再使用该表时将其删除。

视图视图
    可以被看成是虚拟表或存储查询。除非是索引视图，否则视图的数据不会作为非重复对象存储在数据库中。数据库中存储的是 SELECT 语句。SELECT 语句的结果集构成视图所返回的虚拟表。用户可以采用引用表时所使用的方法，在 Transact-SQL 语句中引用视图名称来使用此虚拟表
    视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图在数据库中并不是以数据值存储集形式存在，除非是索引视图。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。分布式查询也可用于定义使用多个异类源数据的视图。例如，如果有多台不同的服务器分别存储您的单位在不同地区的数据，而您需要将这些服务器上结构相似的数据组合起来，这种方式就很有用。
    通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
    视图种类：索引视图和索引视图
    SQL Server 2005 查询处理器对索引视图和非索引视图将区别对待：索引视图的行以表的格式存储在数据库中。如果查询优化器决定使用查询计划的索引视图，则索引视图将按照基表的处理方式进行处理。只有非索引视图的定义才存储，而不存储视图的行。查询优化器将视图定义中的逻辑纳入执行计划，而该执行计划是它为引用非索引视图的 SQL 语句生成的。
    SQL Server 查询优化器用于决定何时使用索引视图的逻辑与用于决定何时对表使用索引的逻辑相似。如果索引视图中的数据包括所有或部分 SQL 语句，而且查询优化器确定视图的某个索引是低成本的访问路径，则不论查询中是否引用了该视图的名称，查询优化器都将选择此索引。当 SQL 语句引用非索引视图时，分析器和查询优化器将分析 SQL 语句的源和视图的源，然后将它们解析为单个执行计划。

=========================================
临时表

当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后在这些表运行查询。

创建临时表很容易,给正常的CREATE TABLE语句加上TEMPORARY关键字:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

)

临时表将在你连接MySQL期间存在。当你断开时,MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。

DROP TABLE tmp_table

如果在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在,临时表将有必要屏蔽(隐藏)非临时表tmp_table。

如果你声明临时表是一个HEAP表,MySQL也允许你指定在内存中创建它:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

) TYPE = HEAP

因为HEAP表存储在内存中,你对它运行的查询可能比磁盘上的临时表快些。然而,HEAP表与一般的表有些不同,且有自身的限制。详见MySQL参考手册。

正如前面的建议,你应该测试临时表看看它们是否真的比对大量数据库运行查询快。如果数据很好地索引,临时表可能一点不快。

1. 临时表再断开于mysql的连接后系统会自动删除临时表中的数据,但是这只限于用下面语句建立的表:

定义字段:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

)

2)直接将查询结果导入临时表

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

2. 另外mysql也允许你在内存中直接创建临时表,因为是在内存中所有速度会很快,语法如下:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

) TYPE = HEAP

3. 从上面的分析可以看出临时表的数据是会被清空的,你断开了连接就会被自动清空,但是你程序中不可能每发行一次sql就连接一次数据库吧(如果是这样的话,那就会出现你担心的问题,如果不是就没有问题),因为只有断开数据库连接才会被清空数据,在一个数据库连接里面发行多次sql的话系统是不会自动清空临时表数据的。
=========================

视图

视图是一种虚拟的数据表，它们的行为和数据表一样，但并不真正包含数据。它们是用底层（真正的）数据表或其他视图定义出来的“假”数据表，用来提供查看数据表数据的另一种方法，这通常可以简化应用程序。

本节重点介绍视图的一些应用。这里没有讨论 DEFINER 子句，这个子句是存储程序和视图都使用的，它可以用来从信息安防的角度对视图数据的访问情况进行控制。如果要选取某给定数据表的数据列的一个子集，把它定义为一个简单的视图是最方便的做法。比如说，假设你经常需要从 president 数据表选取 last_name、first_name、city 和 state 等几个数据列，但不想每次都必须写出所有这些数据列，如下所示：

SELECT last_name, first_name, city, state FROM president;

你也不想使用 SELECT *，这虽然简单，但用*检索出来的数据列不都是你想要的。解决这个矛盾的办法是定义一个视图，让它只包括你想要的数据列：

CREATE VIEW vpres AS

SELECT last_name, first_name, city, state FROM president;

这个视图就像一个“窗口”，从中只能看到你想看的数据列。这意味着你可以在这个视图上使用SELECT *，而你看到的将是你在视图定义里给出的那些数据列：

mysql> SELECT * FROM vpres;

+------------+---------------+---------------------+-------+

| last_name | first_name | city | state |

+------------+---------------+---------------------+-------+

| Washington

| George

| Wakefield

| VA

|

| Adams

| John

| Braintree

| MA

|

| Jefferson

| Thomas

| Albemarle County

| VA

|

| Madison

| James

| Port Conway

| VA

|

| Monroe

| James

| Westmoreland Count

y | VA

|

...

如果你在查询某个视图时还使用了一个 WHERE 子句，MySQL 将在执行该查询时把它添加到那个视图的定义上以进一步限制其检索结果：

mysql> SELECT * FROM vpres WHERE last_name = 'Adams';

+-----------+-------------+-----------+-------+

| last_name | first_name | city | state |

+-----------+-------------+-----------+-------+

| Adams | John | Braintree | MA |

| Adams | John Quincy | Braintree | MA |

+-----------+-------------+-----------+-------+

在查询视图时还可以使用 ORDER BY、LIMIT 等子句，其效果与查询一个真正的数据表时的情况一样。在使用视图时，你只能引用在该视图的定义里列出的数据列。也就是说，如果底层数据表里的某个数据列没在视图的定义里，你在使用视图的时候就不能引用它：

mysql> SELECT * FROM vpres WHERE suffix <> '';

ERROR 1054 (42S22): Unknown column 'suffix' in 'where clause'

在默认的情况下，视图里的数据列的名字与 SELECT 语句里列出的输出数据列相同。如果你想明确地改用另外的数据列名字，需要在定义视图时在视图名字的后面用括号列出那些新名字：

mysql> CREATE VIEW vpres2 (ln, fn) AS

-> SELECT last_name, first_name FROM president;

此后，当你使用这个视图时，必须使用在括号里给出的数据列名字，而非 SELECT 语句里的名字：

mysql> SELECT last_name, first_name FROM vpres2;

ERROR 1054 (42S22) at line 1: Unknown column 'last_name' in 'field list' mysql> SELECT ln, fn FROM vpres2;

+------------+---------------+

| ln | fn |

+------------+---------------+

| Washington

| George

|

| Adams

| John

|

| Jefferson

| Thomas

|

| Madison

| James

|

| Monroe | James |

...
视图可以用来自动完成必要数学运算。

mysql> CREATE VIEW pres_age AS

-> SELECT last_name, first_name, birth, death,

-> TIMESTAMPDIFF(YEAR, birth, death) AS age

-> FROM president;

这个视图包含一个 age 数据列，它被定义成一个运算，从这个视图选取该数据列将检索出这个运算的结果：

mysql> SELECT * FROM pres_age;

+------------+---------------+------------+------------+------+

| last_name | first_name | birth | death | age |

+------------+---------------+------------+------------+------+

| Washington

| George

| 1732-02-22 | 1799-12-14 |

67 |

| Adams

| John

| 1735-10-30 | 1826-07-04 |

90 |

| Jefferson

| Thomas

| 1743-04-13 | 1826-07-04 |

83 |

| Madison

| James

| 1751-03-16 | 1836-06-28 |

85 |

| Monroe

| James

| 1758-04-28 | 1831-07-04 |

73 |

...

通过把年龄计算工作放到视图定义里完成，我们就用不着再在查询年龄值时写出那个公式了。有关的细节都隐藏在了视图里。同一个视图可以涉及多个数据表，这使得联结查询的编写和运行变得更容易。下面定义的视图对score、student 和 grade_event 数据表进行了联结查询：

mysql> CREATE VIEW vstudent AS

-> SELECT student.student_id, name, date, score, category

-> FROM grade_event INNER JOIN score INNER JOIN student

-> ON grade_event.event_id = score.event_id

-> AND score.student_id = student.student_id;

当你从这个视图选取数据时，MySQL 将执行相应的联结查询并从多个数据表返回信息：

mysql> SELECT * FROM vstudent;

+------------+-----------+------------+-------+----------+

| student_id | name | date | score | category |

+------------+-----------+------------+-------+----------+

|

1 | Megan

| 2008-09-03 |

20 | Q

|

|

3 | Kyle

| 2008-09-03 |

20 | Q

|

|

4 | Katie

| 2008-09-03 |

18 | Q

|

|

5 | Abby

| 2008-09-03 |

13 | Q

|

|

6 | Nathan

| 2008-09-03 |

18 | Q

|

|

7 | Liesl

| 2008-09-03 |

14 | Q

|

|

8 | Ian

| 2008-09-03 |

14 | Q

|

...

这个视图可以让我们轻而易举地根据名字检索出某个学生的考试成绩：

mysql> SELECT * FROM vstudent WHERE name = 'emily';

+------------+-------+------------+-------+----------+

| student_id | name | date | score | category |

分享到：