Detailed views of the database, temporary table __ Database

detailed views, temporary tables in the database2013-10-27 20:33 1624 People read comments (0) Collection Report Category: Database (5) MySQL (27)

Copyright NOTICE: This article is the original article, without the owner's permission to reprint.

1, view, the concept of temporary table

2. Differences between views and temporary tables

3. Advantages and Disadvantages

One

1, view

A view is a logical window of data that is selected from the base table of a database, and unlike a basic table, a view is a virtual table. The database holds only the definition of the view, not the data that the view contains, which is still stored in the original base table. So if the data in the base table changes, the data that is queried from the view changes.

A view is a virtual table that is generated by executing a SQL query. The view is stored in the data dictionary by a select name. Whenever a SQL query contains a view name, the database management system executes the query contained in the view definition to establish its virtual results table. The result table can be used as the source table for the remainder of the query.

Views can be based on one or more tables, or on a view, but no matter how the view data is eventually converted to an operation on the underlying table, because the view is a virtual table and the data is actually stored in the base table.

2. Temporary table

A temporary table is a table that is built into the system Temp folder and, if used properly, can be performed in a variety of ways like a normal table, automatically released when VFP exits.
Temporary folders, the name of a temporary folder is installed as a temporary file. In a Windows XP system disk there are three temporary folder system temporary folder paths: C:\Windows\Temp This folder is where Windows systems use to dump temporary files.

A temporary table is a base table that is not stored in a database. In contrast, a temporary table exists only if the database session in which the temporary table was created is activated.

Temporary tables are stored in a database session, not in a database, and are not displayed when you use show tables, but can be queried when you close the database (quit,exit) and then open the query in the database.

Differences between views and temporary tables

1. The view exists only in a single query, and each time the view name is used, its virtual table is re-created based on existing data.
2. Temporary tables exist in the entire database session in which it was created.
3. The view automatically fills with the data retrieved by the query it defines.
4. You must use the Sqlinsert command to add data to a temporary table.
5, only those views that conform to the view updatable ability standard can be used for data modification. When you use a view for updating, the update is permanently passed to the underlying base table.
6. Because temporary tables are base tables, all temporary tables are updatable. However, these updates are temporary for these tables.
7. Since the contents of the view are generated every time the view is used, the data in the view is almost always up to date.
8. The data in the temporary table reflects the state of the database when the table is loaded into the database. If the data in the source table loaded by the temporary table changes after it has been retrieved from the temporary table, the contents of the temporary table may be out of sync with the data in the other parts of the database.

Iii. Advantages and Disadvantages

1, because the content of the view is generated every time the view is used, so the data and the data in the database is synchronized, but in each query to establish the view of the chart structure, so more waste of time.
2, temporary table only need to create once, so compared to view each time create a view structure, save a lot of time, but the temporary table data in the data source changes are not updated, there will be data synchronization phenomenon.
3. Simplify the data query statement--the definition view can hide the complex connection operation and search condition between the table and the table to the user, so the user simply queries a view.
4, users can look at the same data from a multiple perspective.
5, improve the security of the data ——— in the establishment of the view can be a sensitive data shadow hidden, and the need for the data exposed to the customer such words are more secure.
6, provides a certain degree of logical independence--the database is a three-level pattern mapping, three-level mode is the external mode, mode, internal mode and the view here is the outside mode, which provides the logical independence of the data.

* * Simple example:

To create a view:

CREATE VIEW ViewName

As select Sname, Sage,score

From table where sage<26//Here is a simple example, as described above, you can set up a view of multiple table joins and so on here no longer repeats.

with CHECK option; To satisfy a table's criteria when inserting data in a view

To create a temporary table:

Create temporary table tablename (//Creating a temporary table, if you want to create a local temporary table, temporary a local temporary table can be established before adding the If you set up a global temporary table, turn the local into global and OK

Sname Char (6) Primary key,

Sage smallint,

Score int

);

Comments: Data dictionary

An important part of the database is the data dictionary. It holds information about the database and is a set of read-only tables for the user. Data dictionary content includes:

tables, views, clusters, and indexes.  How much space is allocated, how much space is currently used, and so on.  The default value for the column. Constrain information integrity. User name, user and role granted permissions, audit information accessed or used by the user, and other resulting database information.