What are the concepts and operating functions of MySQL views?

Common database objects

Object	Description
Table( TABLE)	Table is a logical unit for storing data, which exists in the form of rows and columns. Columns are fields and rows are records
Data Dictionary	is the system table, a table that stores database-related information. The data of system tables is usually maintained by the database system. Programmers usually should not modify it. They can only view the
Constraints (CONSTRAINT)	rules for performing data verification. Use Rules for ensuring data integrity
View (VIEW)	The logical display of data in one or more data tables, the view does not store the data
Index (INDEX)	is used to improve query performance, equivalent to the directory of the book
Stored procedure (PROCEDURE)	Used to complete a complete business process, there is no return value, but multiple values ​​can be passed to the calling environment through outgoing parameters
Storage function (FUNCTION)	Used to complete a specific calculation, with a return value
Trigger (TRIGGER)	is equivalent to an event listener, when a specific event occurs in the database After that, the trigger is triggered and the corresponding processing is completed

The concept of view

A view is a kind of virtual table, which itself does not have data and occupies very little memory space. It is an important concept in SQL.

Views are built on existing tables, and these tables on which views are built are called base tables.

The creation and deletion of views only affects the view itself and does not affect the corresponding base table. When add, delete, modify (DML) operations are performed on the view, the data in the view will be updated accordingly, and vice versa, the data in the data table will also change.

The statement that the view provides the data content is the SELECT statement. The view can be understood as the stored SELECT statement

In the database, the view does not save the data, the data is actually saved in the data table. If you add, delete, or modify data in the data table, the data in the view will change accordingly. vice versa.

View is another form of expression that provides users with base table data. Under normal circumstances, the database of small projects does not need to use views, but in large projects and when the data tables are relatively complex, the value of views becomes prominent. It can help us put frequently queried result sets into virtual tables. , improve usage efficiency. It is very easy to understand and use.

Create a view

The alias of the field in the query statement will appear as the alias of the view

CREATE VIEW vu_emps
AS 
SELECT employee_id,last_name,salary
FROM emps;

CREATE VIEW vu_emps2(emp_id,name,monthly_sal)
AS 
SELECT employee_id,last_name,salary
FROM emps;

Create a view for multiple tables

CREATE VIEW vu_emp_dept
AS
SELECT employee_id,e.department_id,department_name
FROM emps e JOIN depts d
ON e.department_id = d.department_id;
SELECT * FROM vu_emp_dept;

Use the view to manipulate the data Formatting

CREATE VIEW vu_emp_dept1
AS
SELECT CONCAT(e.last_name,'(',d.department_name,')') emp_info
FROM emps e JOIN depts d
ON e.department_id = d.department_id;

Create a view based on a view

CREATE VIEW vu_emp4
AS 
SELECT department_id,department_name FROM vu_emp_dept;
SELECT * FROM vu_emp4;

View the view

View the table object of the database, view object

SHOW TABLES;

View the database structure

DESC vu_emp4;

View the attribute information of the data

mysql> SHOW TABLE STATUS LIKE 'vu_emp4'\G;
*************************** 1. row ***************************
           Name: vu_emp4
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)
ERROR: 
No query specified

View the detailed definition information of the view

mysql> SHOW CREATE VIEW vu_emp4\G;
*************************** 1. row ***************************
                View: vu_emp4
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `vu_emp4` AS select `vu_emp_dept`.`department_id` AS `department_id`,`vu_emp_dept`.`department_name` AS `department_name` from `vu_emp_dept`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
ERROR: 
No query specified

Update the view data

Updating the data in the view will result in the data in the base table Modification

Updating the data in the table will also result in modification of the data in the view

In order for the view to be updatable, each row in the view must correspond to a row in the base table And there is a one-to-one relationship between the two. In addition, when the following conditions occur in the view definition, the view does not support update operations:

• If "ALGORITHM = TEMPTABLE" is specified when defining the view, the view will not support INSERT and DELETE operations;

• The view does not contain all columns in the base table that are defined as non-empty and have no default value specified, and the view will not support the INSERT operation;

• If a JOIN joint query is used in the SELECT statement that defines the view, the view will not support INSERT and DELETE operations;

• Mathematical expressions are used in the field list after the SELECT statement that defines the view formula or subquery, the view will not support INSERT, nor does it support UPDATE field values ​​that use mathematical expressions or subqueries;

• is in the field list after the SELECT statement that defines the view Using DISTINCT, aggregate functions, GROUP BY, HAVING, UNION, etc., the view will not support INSERT, UPDATE, DELETE;

• The SELECT statement that defines the view contains a subquery, and the subquery The table after FROM is referenced in the query, and the view will not support INSERT, UPDATE, and DELETE;

• The view definition is based on a non-updatable view; Constant view.

Although views can update data, in general, views as virtual tables are mainly used to facilitate queries, and it is not recommended to update view data. Changes to the view data are completed by operating the data in the actual data table.

Modify the view

Method 1: Use the CREATE OR REPLACE VIEW clause to modify the view

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

CREATE OR REPLACE VIEW vu_emp4
AS 
SELECT CONCAT(e.last_name,'(',d.department_name,')') emp_info
FROM emps e JOIN depts d
ON e.department_id = d.department_id;

Note: The aliases of each column in the CREATE VIEW clause should be the same as each column in the subquery Corresponding.

Method 2: ALTER VIEW

The syntax to modify the view is:

ALTER VIEW view name
AS
Query statement

ALTER VIEW vu_emp4
AS 
SELECT CONCAT(e.last_name,'(',d.department_name,')') emp_info
FROM emps e JOIN depts d
ON e.department_id = d.department_id;

Delete View

DROP VIEW vu_emp4;
DROP VIEW IF EXISTS vu_emp1;

Advantages and Disadvantages of View

Advantages:

• Simple operation

• Reduce data redundancy

• Data security

• Adapt to flexible and changing needs

• Able to decompose complex query logic

Disadvantages:

• High maintenance cost

• Readability not good

The above is the detailed content of What are the concepts and operating functions of MySQL views?. For more information, please follow other related articles on the PHP Chinese website!