View is a kind of table expression, so it is also a virtual table. When operating on the view, it will dynamically obtain data from the table temporarily through statements.
1. Create and modify views
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [IF NOT EXISTS] view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]When using or replace, if the view exists, this statement is equivalent to alter view. If the view does not exist, it is equivalent to create view.
The algorithm will be described in detail later.
with [local|cascaded] check option: its object is the updatable view (i.e. the view of the merge algorithm). For updatable views, the WITH CHECK OPTION clause can be given to prevent illegal records from being inserted or updated, except for select acting on rows_ The WHERE clause in the statement is "true". Local means that the view can be inserted or updated as long as the filter conditions of this view are met, and cascaded means that the filter conditions of all views must be met. The default is with cascaded check option.
For example, the following statement defines three views, of which the last two views are created with the first view as the base table. When inserting records into view2 and view3, if the field a=10 in the record: because view2 uses the cascaded option by default, a=10 does not meet the conditions of view1, so the insertion fails; View3 uses the local option, which only needs to meet the conditions of view3, so if a=10 meets the conditions, it can be successfully inserted.
create view view1 as select * from t where a<10; create view view2 as select * from view1 where a>5; create view view3 as select * from view1 where a>5 with local check option;
After the view in MySQL/MariaDB is created, the definition of the column is "solidified". In other words, if an asterisk "*" is used in the select statement in the view definition statement to represent all columns, it will be converted into the corresponding column name and stored in the view definition statement when creating the view. Therefore, if a column is added in the base table, it will not be retrieved by the SQL statement of the view.
For example:
create or replace view v_city as select * from world.city where id>200;
View the definition statement of the view: you can see that the asterisk in the select statement is represented by the corresponding column name.
mysql> mysql> show create view v_city\G
*************************** 1. row ***************************
View: v_city
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`192.168.100.%` SQL SECURITY DEFINER VIEW `v_city` AS select `city`.`ID` AS `ID`,`city`.`Name` AS `Name`,`city`.`CountryCode` AS `CountryCode`,`city`.`District` AS `District`,`city`.`Population` AS `Population` from `city` where (`city`.`ID` > 200)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)In the select part of the view definition statement in MySQL/MariaDB, the from cannot be followed by a subquery. At this point, MySQL/MariaDB is somewhat different from other types of databases. If, under certain conditions, the definition statement of a view, the from sentence, just needs a subquery, you can define the subquery as a view first, and then put the view in the from sentence. When updating the view, you are actually going to the corresponding base table for updating.
2. About the order by clause in the view
According to the rules of standard SQL, the order by clause is not allowed in the select statement of the view definition statement unless TOP(limit) is used, but the order by only selects the rows that meet the number for top. Because view is a kind of table expression, since it is a table expression, it is a kind of table, although it is a virtual table. The table is not allowed to be ordered (in the view of the relationship engine, the table is always disordered, and in the view of the optimizer, the table can be ordered). In SQL Server, if order by is used in the view definition statement but the top clause is not used, an error will be reported directly.
However, order by is allowed in the view definition statement in MySQL/MairaDB (which is also a violation of standards). It believes that the order by in the view will act directly on the base table when referring to the view. If the order by clause is also used when referencing the view, the order by in the View reference statement overrides the order by in the view definition statement. For example:
CREATE OR REPLACE VIEW my_view AS SELECT * FROM t ORDER BY id DESC ; SELECT * FROM my_view ORDER BY id ASC;
3. View algorithm merge and temptable
algorithm={undefined|merge|temptable} is the view selection algorithm. The algorithm of views will affect the way MySQL/MariaDB processes Views:
- merge will combine the statements that refer to the view with the view definition statements, so that a part of the view definition replaces the corresponding part of the statement. For example, when referencing a view, the view name will be replaced with the base table name, and the columns involved in the query will be replaced with the column names in the base table.
- temptable puts the results of the view into a temporary table, and then uses the data of the table to perform corresponding statement operations.
- undefined is to let MySQL/MariaDB choose merge or temptable. It prefers merge. This is the default value when algorithm is not specified.
For example, the following is a special example of merge, which can well illustrate the merge algorithm:
MariaDB [test]> create or replace table t (id int auto_increment, name char(20), age int, primary key(id));
MariaDB [test]> insert into t(name,age) values ('chenyi',21), ('huanger',22), ('zhangsan',23), ('lisi',24), ('wangwu',25), ('zhaoliu',26);
MariaDB [test]> select * from t;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | chenyi | 21 |
| 2 | huanger | 22 |
| 3 | zhangsan | 23 |
| 4 | lisi | 24 |
| 5 | wangwu | 25 |
| 6 | zhaoliu | 26 |
+----+----------+------+
# Create a view with ID < 5_ view
MariaDB [test]> create or replace algorithm=merge view my_view(vf1,vf2) as select id,name from t where age<24;
MariaDB [test]> select * from my_view;
+-----+----------+
| vf1 | vf2 |
+-----+----------+
| 1 | chenyi |
| 2 | huanger |
| 3 | zhangsan |
+-----+----------+The result returned is 3 lines of records.
Because it is a view of the merge algorithm, when referring to the view (here is the query operation), the items in the view will be replaced with those in the base table t. include:
- The "*" sign is replaced by vf1 and vf2, which in turn are replaced by id and name in the t table.
- My in the from clause_ Replace view with table t.
- Add the where clause in the view definition statement.
Therefore, select * from my_view; During execution, it will be converted to the following query statement:
select id,name from t where age<24;
If you query my_view, use the following statement:
MariaDB [test]> select * from my_view where vf1<2; +-----+--------+ | vf1 | vf2 | +-----+--------+ | 1 | chenyi | +-----+--------+
When executing, the statement will be replaced with the following statement:
select id,name from t where id<2 and age<24;
The view is updatable only when the merge algorithm is used, because the temptable algorithm operates on the data filled in the temporary table and cannot be updated in combination with the base table.
Because the merge algorithm combines the base table, it has some limitations. The merge algorithm cannot be used in the following cases:
- HAVING
- LIMIT
- GROUP BY
- DISTINCT
- UNION
- UNION ALL
- Aggregate functions are used, such as MAX(), MIN(), SUM() or COUNT()
- There are subqueries in the select list
- For example, select as may not have a base value of VA.
The reason for the above restrictions is that after using them, the structure of the view is inconsistent with the structure of the base table, and cannot correspond to the base table one by one, so it cannot be used as an updatable view.
4. Delete and view views
You can delete multiple views at once.
DROP VIEW [IF EXISTS] view_name [, view_name] ...
There is no show view status statement in MySQL/MariaDB. You can use show table status to display the status information of tables and views, and show tables to display the tables and views in the database.
SHOW TABLE STATUS LIKE 'v_city';
View view definition statement:
show create view view_name;
You can also use information_ schema. View relevant information in the views table, but note that the field where the view name is located in the views table is called table_name instead of view_name. As follows:
select * from information_schema.views where table_name='view_name';
5. Check invalid view
When creating a view, its base table must already exist, otherwise an error will be reported. However, after the view is created successfully, the base table of the view may be deleted or the reference fields in the base table may be updated. At this time, the view is invalid.
How do I detect these invalid views?
You can start with information Find out which views exist in the schema, and then use the check table statement to detect.
For example:
check table my_view,my_view2
The following are the invalid view check results:
MariaDB [test]> check table my_view\G *************************** 1. row *************************** Table: test.my_view Op: check Msg_type: Error Msg_text: Table 'test.t' doesn't exist *************************** 2. row *************************** Table: test.my_view Op: check Msg_type: Error Msg_text: View 'test.my_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them *************************** 3. row *************************** Table: test.my_view Op: check Msg_type: error Msg_text: Corrupt 3 rows in set (0.000 sec)