MySQL: View

hao1nan

　　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.