Views and Indexes

Creating and Viewing Views#

A view is a special type of table that abstracts the querying or manipulation of data from a data table into a separate database operation. In reality, it does not exist in the database.
Only the definition of the view is stored in the database, and the actual data of the view is obtained through querying the database. Therefore, changes in the database content will affect the data of the view.

Syntax:

CREATE VIEW [view_name] AS [SELECT_statement]

Tips:

The name of the view cannot be the same as other table names or views in the database. And the name of the view cannot start with a number.
The SELECT_statement is a SELECT statement. It can query data from multiple tables. There are the following limitations for this statement:
1. The user needs to have the CREATE VIEW permission, as well as the SELECT permission on the relevant tables and views.
2. The SELECT_statement cannot reference system or user variables.
3. The SELECT_statement cannot contain subqueries in the FROM clause.
4. The SELECT_statement cannot reference parameters in prepared statements.
Prepared statements: Create SQL statement templates and send them to the database. Reserved values are marked with the parameter "?" For example:
```
INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)
```
The tables or views that the view needs to reference must exist, but they can be non-existent after the view is created (but the data will definitely be problematic). You can use CHECK TABLE to check if the table exists.
The view definition allows the use of the ORDER BY clause. However, if the ORDER BY clause is used in the view, the data in the view will also be sorted according to the ORDER BY clause when querying the view.
The view definition cannot reference temporary tables and cannot create temporary table views.
The WITH CHECK OPTION keyword allows the view to check if the data in the view is valid.

Examples#

Creating from a single table

CREATE VIEW MyView AS
SELECT * FROM MyTable

Creating from multiple tables

CREATE VIEW MyView AS
SELECT * FROM MyTable1, MyTable2

Creating from multiple tables and specifying columns

CREATE VIEW MyView AS
SELECT MyTable1.id, MyTable1.name, MyTable2.age
FROM MyTable1, MyTable2

Creating from multiple tables, specifying columns, and specifying the order of columns

CREATE VIEW MyView AS
SELECT MyTable1.id, MyTable1.name, MyTable2.age
FROM MyTable1, MyTable2
ORDER BY MyTable1.id, MyTable2.age

Specifying the correspondence between column names in the view

CREATE VIEW MyView 
(id,name,number)    AS
SELECT MyTable1.id, MyTable1.name, MyTable2.age
FROM MyTable1, MyTable2

Querying Views#

Views can be used for querying, but not recommended for updating data