Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the differences between database views and tables, I believe that most people still do not understand, so share this article for your reference, I hope you can learn a lot after reading this article. Let's learn about it!

Differences: 1, the view is a compiled sql statement, the table is not; 2, the view has no actual physical records, the table has; 3, the table is the content, the view is the window; 4, the table occupies physical space, the view does not take up physical space; 5, the table is the conceptual schema, the view is outside the schema; 6, the table belongs to the global schema table, the view belongs to the local schema table and so on.

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

The difference between views and tables in a database

The view is a compiled sql statement, while the table is not

The view has no actual physical record, while the table has

Tables are contents, views are windows.

The table occupies physical space while the view does not. The view is only the existence of a logical concept, and the table can modify it in time, but the view can only be modified with the statement created.

The table is the conceptual schema in the three-level schema structure, and the view is the external schema.

View is a way to view a data table. You can query the data made up of some fields in the data table, but it is just a collection of SQL statements. From a security point of view, the view can not give users access to the data table and therefore do not know the structure of the table.

The table belongs to the table in the global schema, which is the real table, and the view belongs to the table in the local schema, which is a virtual table.

The creation and deletion of the view only affects the view itself, not the corresponding basic table.

You cannot update or insert into a view.

The relationship between views and tables in the database

1. A view is a table built on top of a basic table. Its structure (that is, the defined columns) and its contents (that is, all data rows) come from the basic table, and it exists according to the existence of the basic table.

2. A view can correspond to either one basic table or multiple basic tables

3. The view is the abstraction of the basic table and the new relationship established in the logical sense.

Summary:

A view is a subquery, and its performance is bound to be lower than that of a direct query (although there are optimizations within sql), so one important thing to pay attention to when using a view is not to use queries nested, especially complex queries.

What's the use of views?

1. When a query needs to be frequently used as a subquery, the view can simplify the code and call it directly instead of repeating it every time.

2. The database administrator of the system needs to provide others with some two columns of data in a table, but does not want him to see any other data, so he can build a view with only these two columns of data, and then publish the view to him.

Performance loss solution

Optimize the query statement of the view.

In general, there is no difference between a direct query and a query view (sql itself optimizes), unless the view is nested or the subquery is complex to calculate.

Special note:

Every time a SELECT view is made, the view will recalculate the rules that created it (the sql algorithm). If the algorithm is complex and the amount of data is large, it will be slow, so it will be slow each time.

Also, the index of the table is not valid for the view view, which is scanned throughout the table.

Example

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

Navigate to the specified data, click the view, and on the right show all the views of the current database. Double-click and open the view window, which looks no different from the table:

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

These are all the contents of the article "what's the difference between database views and tables?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.