Is there space for mysql view?

Mysql view has no space. A MySQL view is a virtual table that does not actually exist in the database and does not allocate space. The MySQL database only stores the definition of the view and does not store the data in the view. These data are stored in the real table referenced by the query that defines the view.

(Recommended tutorial: mysql video tutorial)

MySQL view (View) is a virtual table , like a real table, a view is composed of columns and rows, but the view does not actually exist in the database. The row and column data come from the tables used in the query that defines the view, and are dynamically generated when the view is used.

The database only stores the definition of the view, and does not store the data in the view. These data are stored in the real table referenced by the query that defines the view. When using a view to query data, the database will retrieve the corresponding data from the real table. Therefore, the data in the view depends on the data in the real table. Once the data in the real table changes, the data displayed in the view will also change.

The view can select information that is useful to the user from the original table. Information that is not useful to the user or that the user does not have permission to know can be directly blocked, which is similar to filtering. This not only simplifies the application, but also ensures the security of the system.

For example, the following database has a company department table department. The table includes department number (d_id), department name (d_name), function (function) and office address (address). The structure of the department table is as follows:

mysql> DESC department;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id     | int(4)      | NO   | PRI | NULL    |       |
| d_name   | varchar(20) | NO   | UNI    | NULL    |       |
| function | varchar(50) | YES  |     | NULL    |       |
| address  | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

There is also an employee table worker. The table contains the employee's job number (num), department number (d_id), name (name), gender (sex), date of birth (birthday) and home address (homeaddress). The structure of the worker table is as follows:

mysql> DESC worker;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| num         | int(10)     | NO   | PRI | NULL    |       |
| d_id        | int(4)      | YES  |MUL     | NULL    |       |
| name        | varchar(20) | NO   |     | NULL    |       |
| sex         | varchar(4)  | NO   |     | NULL    |       |
| birthday    | datetime    | YES  |     | NULL    |       |
| homeaddress | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

Since the scope of authority of each department leader is different, the leader of each department can only see the employee information of that department; moreover, the leader may not care about the employee's birthday and home address . In order to achieve this purpose, a view can be established for the leaders of each department. Through this view, the leaders can only see the specified information of employees in this department.

For example, create a view named product _view for the production department. Through the view product_view, the leader of the production department can only see the job number, name, gender and other information of the employees in the production department. The information of these department tables and worker tables still exist in their respective tables, and no data information is saved in the view product_view. When the information in the department table and worker table changes, the information displayed in the view product_view will also change accordingly.

Tips: If you often need to query data of specified fields from multiple tables, you can create a view on these tables and display the data of these fields through this view.

MySQL views do not support the function of input parameters, so there is still a lack of interactivity. But for operations that don’t change much, using views can simplify the user’s operations to a great extent.

Views are not different from data tables. Their differences lie in the following points:

• Views are not real tables in the database, but a A virtual table whose structure and data are based on the query of the real table in the data.

• The query operation SQL statement stored in the database defines the content of the view. The column data and row data come from the actual table referenced by the view query. These data are dynamically generated when the view is referenced.

• The view has no actual physical records and is not stored in the database in the form of a data set. Its corresponding data is actually stored in the real table referenced by the view.

• The view is the window of data, and the table is the content. The table is the storage unit of actual data, while the view only displays the data in different display methods, and its data source is still the actual table.

• View is a way to view a data table. It can query data composed of certain fields in the data table. It is just a collection of SQL statements. From a security perspective, the data security of views is higher. Users using views do not touch the data tables and do not know the table structure.

• The creation and deletion of a view only affects the view itself and does not affect the corresponding basic table.

The above is the detailed content of Is there space for mysql view?. For more information, please follow other related articles on the PHP Chinese website!