MySQL: View

Why use View:

    1) Sometimes the result set of a query is frequently used. That is this result set is usually used as a sub-query for another query. So why don't we transform this result set into a table that can be directly stored and  used?

 

#pseudo-sql
#create table g2 like goods;
#insert into g2 select * from goods;

 

 

Definition of View:

     1) A view is a vitual table that derieves from the result set of a query.

 

#Normal SQL
select * from stu where stu_score >= 60;
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhangsan | Math       |        90 |
| zhaoliu  | Politic    |        99 |
+----------+------------+-----------+

#View
create view standard as select * from stu where stu_score > 60;
select * from standard;
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhangsan | Math       |        90 |
| zhaoliu  | Politic    |        99 |
+----------+------------+-----------+

 

 

Syntax of View:

      1) create [algorithm=merge/temptable/undefined] view view_name as select_statement

      2) drop view view_name

      3) alter view view_name as select_statement

 

Benefits of using View:

      1) Simplify SQL: Don't have to use many sub-sqls.

      2) Entitlement Control: Make entitlement control to a more fine grained level as into every column.

      3) Benefits Big Data Tabling.

Eg>Simplify SQL: Get the highest three student average score group by student name

#Get average score for each students
create view stu_avg as select stu_name, avg(stu_score) as avg_score from stu group by stu_name;
select * from stu_avg;
+----------+-----------+
| stu_name | avg_score |
+----------+-----------+
| lisi     | 50.0000   |
| wangwu   | 30.0000   |
| zhangsan | 60.0000   |
| zhaoliu  | 74.5000   |
+----------+-----------+

#Get highest three average score for students
select * from stu_avg order by avg_score desc limit 3;
+----------+-----------+
| stu_name | avg_score |
+----------+-----------+
| zhaoliu  | 74.5000   |
| zhangsan | 60.0000   |
| lisi     | 50.0000   |
+----------+-----------+
#Bingo!

 Eg>Entitlement Control:

     Grant normal user access to view but not to real table.

 Eg>Big Data Tabling:

     Once the row count of a table is larger than 2,000,000, it would be very slow when SQLing.

     Split the table into serval sub-tables.

     Table news (contains 2,000,000 rows)

              ->news1 + news2 + news3 + news4

              ->(news_id%4 + 1)== 1 into news1 table

              ->(news_id%4 + 1) == 2 into news2 table

              ->...

     use view to combine these three news table.

     create view news_view as select * from news1 union select * from news2 union select * from news3 union select * from news4;

 

Relationship between View and Table:

      1) View is derieved from table. So once table changed, view will be affected. View is the result set of table.

      2) What if view changed?

update stu_avg set avg_score = 68 where avg_score = 50;
ERROR 1288 : The target table stu_avg of the UPDATE is not updatable

           1) View is not always can CRUD. -> If a column of a view is simply derieved from table, then we can CRUD this column. And table will be affected.

                                                               -> If a column of a view is not simply derieved from table, then we cannot CRUD this column.

 

Algorithm of View:

      1) Algorithm = merge / temptable / undefined

      2) Merge>When using view, using statement combine with statement that defined this view.

      3) Temptable>When using view, create a temporary table besed on statement that defined this view.

      4) Undefined> Let system to choose algorithm.

      Comments: 

                1) Merge means view is just a rule. When using view, compiler will analyze the view statement and merge it into outer SQL.

                    <Simply merge two SQL together. Only ONE SQL executed>

                2) Temptable means view is a temporary table. Outer SQL will based on this temp table.

                    <Using real temporary table. TWO SQLs executed>

                3) Different algorithms affect different tables: Merge affects orginal table. Temptable affect temp table.

Eg> 

#Create view
create view standard as select * from stu where stu_score > 50;
select * from standard;
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhangsan | Math       |        90 |
| lisi     | Literature |        55 |
| zhaoliu  | Politic    |        99 |
+----------+------------+-----------+

#Using view
select * from standard where stu_score < 99;
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhangsan | Math       |        90 |
| lisi     | Literature |        55 |
+----------+------------+-----------+

#Analysis->The real process of execution
select * from stu where stu_score > 50 and stu_score < 99;
##########################################
#Get the profile of the students whose score is highest group by stu_course

#Normal approach
select * from stu order by stu_course asc, stu_score desc;
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhaoliu  | Geograph   |        50 |
| zhangsan | Geograph   |        40 |
| lisi     | Literature |        55 |
| zhangsan | Literature |        50 |
| zhangsan | Math       |        90 |
| zhaoliu  | Politic    |        99 |
| lisi     | Politic    |        45 |
| wangwu   | Politic    |        30 |
+----------+------------+-----------+
select * from (select * from stu order by stu_course asc, stu_score desc) as temp group by stu_course;
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhaoliu  | Geograph   |        50 |
| lisi     | Literature |        55 |
| zhangsan | Math       |        90 |
| zhaoliu  | Politic    |        99 |
+----------+------------+-----------+

#Using view
mysql> create view order_stu as select * from stu order by stu_course asc, stu_score desc;
Query OK, 0 rows affected

mysql> select * from order_stu;
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhaoliu  | Geograph   |        50 |
| zhangsan | Geograph   |        40 |
| lisi     | Literature |        55 |
| zhangsan | Literature |        50 |
| zhangsan | Math       |        90 |
| zhaoliu  | Politic    |        99 |
| lisi     | Politic    |        45 |
| wangwu   | Politic    |        30 |
+----------+------------+-----------+
8 rows in set

mysql> select * from order_stu group by stu_course;
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhangsan | Geograph   |        40 |
| zhangsan | Literature |        50 |
| zhangsan | Math       |        90 |
| lisi     | Politic    |        45 |
+----------+------------+-----------+
4 rows in set
#The result is wrong!!!! Why??
#Analysis: Real process of execution>>>>>>>>>>>
select * from stu group by stu_course order by stu_course asc, stu_score desc;
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhangsan | Geograph   |        40 |
| zhangsan | Literature |        50 |
| zhangsan | Math       |        90 |
| lisi     | Politic    |        45 |
+----------+------------+-----------+
4 rows in set
#It is the result of merge algorithm. ->Merge outer statement into view definition statement. Sometimes may incur problems!
#So we have to change the algorithm into TEMPTABLE!

#Remedy>
mysql> create algorithm = temptable view order_stu as select * from stu order by stu_course asc, stu_score desc;
Query OK, 0 rows affected

mysql> select * from order_stu;
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhaoliu  | Geograph   |        50 |
| zhangsan | Geograph   |        40 |
| lisi     | Literature |        55 |
| zhangsan | Literature |        50 |
| zhangsan | Math       |        90 |
| zhaoliu  | Politic    |        99 |
| lisi     | Politic    |        45 |
| wangwu   | Politic    |        30 |
+----------+------------+-----------+
8 rows in set

mysql> select * from order_stu group by stu_course;
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhaoliu  | Geograph   |        50 |
| lisi     | Literature |        55 |
| zhangsan | Math       |        90 |
| zhaoliu  | Politic    |        99 |
+----------+------------+-----------+
4 rows in set

 

Comments:

      1) The moment a view is created, it is regarded as a table. show tables command will show views as well.

show tables;
+-----------------+
| Tables_in_mugua |
+-----------------+
| goods           |
| standard        |
| stu             |
+-----------------+

     2) Knowing the meaning of algorithm for view. And knowing difference between different algorithms.