What is the difference between database views and tables

Difference: 1. Views are compiled SQL statements, tables are not; 2. Views do not have actual physical records, tables do; 3. Tables are content, views are windows; 4. Tables occupy physical space , the view does not occupy physical space; 5. The table is a conceptual schema, the view is an external schema; 6. The table belongs to the table in the global schema, the view belongs to the table in the local schema, etc.

The operating environment of this tutorial: windows7 system, mysql8, Dell G3 computer.

The difference between views and tables in the database

• Views are compiled sql statements, but tables are not;

• The view does not have actual physical records, but the table does;

• The table is the content, the view is the window;

• The table Occupies physical space but the view does not occupy physical space. The view is just a logical concept. The table can be modified in time, but the view can only be modified with the created statement;

• The table is In the conceptual schema in the three-level schema structure, the view is the external schema;

  

• The view is a way to view the data table and can query the data table The data composed of certain fields is just a collection of SQL statements. From a security perspective, the view does not allow users to access the data table, so they do not know the table structure;

• The table belongs to the global Tables in the schema are real tables, and views belonging to the local schema are virtual tables;

• The creation and deletion of views only affects the view itself, not the corresponding basic table;

• The view cannot be updated or inserted into.

The relationship between views and tables in the database

1. Views (views) are established on basic tables table, its structure (that is, the defined columns) and content (that is, all data rows) come from the basic table, and it exists based on the existence of the basic table;
2. A view can correspond to a basic table, or it can correspond to Multiple basic tables;
3. Views are abstractions of basic tables and new relationships established in a logical sense.
Summary:
A view is a subquery, and its performance will definitely be lower than that of a direct query (despite the optimization within sql), so one thing you must pay attention to when using views is not to use nested queries, especially Complex queries.

What are the uses of views?

1. When a query needs to be used frequently as a subquery, the view can simplify the code and call it directly instead of repeating it every time. Write this thing.
2. The database administrator of the system needs to provide others with certain two columns of data in a table, but does not want them to see any other data. In this case, they can build a view with only these two columns of data, and then change the view announced to him.

Solution to performance loss

Optimize the query statement of the view.
Generally speaking, there is no difference between direct query and query view (sql itself will be optimized), unless the view is nested in the view, or the subquery is very complex and needs to be calculated.
Special instructions:
Every time you select a view, the view will recalculate the rules for creating it (sql algorithm). If the algorithm is complex and the amount of data is large, it will be slower, so it will be very slow every time.
Moreover, the index of the table is invalid for the view, which is a full table scan.

Example

The database is Oracle, and the view tool used is the free version of Navicat.

Locate the specified data, click View, and all views of the current database will be displayed on the right. Double-click to open the view window, which looks no different from the table:

Ctrl D can enter the SQL statement of the view to view, as follows:

Related free learning recommendations: mysql video tutorial

The above is the detailed content of What is the difference between database views and tables. For more information, please follow other related articles on the PHP Chinese website!