`
DavyJones2010
  • 浏览: 153819 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论
阅读更多

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.

分享到:
评论

相关推荐

    MySQL:grant_语法详解(MySQL_5.X)

    另一类是数据库开发人员和管理员需要使用的权限,包括对数据库结构的操作权限,如创建表(CREATE)、修改表(ALTER)、删除表(DROP)、创建索引(INDEX)、创建视图(CREATE VIEW)、显示视图源代码(SHOW VIEW)、...

    IDEA连接mysql数据库:使用idea软件与mysql数据库的简单的连接

    - 在IDEA中,打开“Database”视图(如果未显示,可以通过View -&gt; Tool Windows -&gt; Database菜单项打开)。 - 点击右上角的"+"图标,选择"Data Source",然后选择"MySQL"。 2. 配置数据源: - 输入数据源名称,...

    MySQL数据库:视图的使用.pptx

    MySQL 数据库视图的使用 MySQL 数据库中的视图是一种虚拟表,基于基本表的数据,它可以简化复杂的查询操作,提高数据的可读性和可维护性。本文将详细介绍 MySQL 数据库中的视图的使用,包括可更新视图的条件、通过...

    jmeter连接MySQL数据库的驱动mysql-connector-java-5.1.38-bin

    6. **结果处理**:根据需求,可以添加监听器,如"View Results Tree"或"Summary Report",以便查看查询结果或分析性能指标。 7. **运行测试计划**:最后,保存并运行测试计划,JMeter将按照配置与MySQL数据库进行...

    mysql教程:入门教程。

    3. **视图(View)**:虚拟表,提供对数据的另一种访问方式,可简化复杂的查询。 4. **索引(Index)**:提高查询速度,通过创建主键索引、唯一索引和普通索引优化查询性能。 5. **存储过程(Stored Procedures)和...

    MyEclipse连接MySQL数据库报错解决办法

    1、在MyEclipse中连接MySQL数据库:依次点击window–&gt;show view–&gt;other–&gt;MyEclipse Database–&gt;DB Browser 图一:找到数据库连接选项 2、选中DB Browser,右键单击,并选择New一个数据连接的菜单,新建一个MySQL...

    Mysql基础:数据库数据文件

    - 视图(VIEW):视图是虚拟表,基于一个或多个表的查询结果。它们可以在代码中提供更简洁的接口,且可以隐藏复杂查询逻辑。 - 函数(FUNCTIONS):MySQL提供了许多内置函数,例如数学函数、字符串函数、日期时间...

    jmeter链接mysql用到的jar包

    3. **结果处理**:为了查看和分析测试结果,可以添加 "View Results Tree" 或其他监听器,以便查看 SQL 请求的响应时间和结果。 4. **添加依赖**:将 `mysql-connector-java-5.1.46-bin.jar` 添加到 JMeter 的 lib ...

    康师傅mysql基础pdf

    CREATE VIEW view_name AS SELECT column1, column2 FROM table_name; ``` 7. **事务处理**: 事务是一系列操作的集合,这些操作要么全部成功,要么全部回滚。ACID(原子性、一致性、隔离性和持久性)是事务处理的...

    mysql 5.1.59

    appendix) or view the online documentation at &lt;http://dev.mysql.com/doc/&gt;. GPLv2 Disclaimer For the avoidance of doubt, except that if any license choice other than GPL or LGPL is available it will ...

    MySQL数据库:使用NAVICAT工具创建和管理视图.pptx

    例如,如果你想要创建一个包含`employees`表中`name`和`salary`字段的视图,你可以写`CREATE VIEW view_name AS SELECT name, salary FROM employees;` 4. 完成后,点击“运行”或“保存”,视图就会被创建并在视图...

    MySQL 官方文档.

    MySQL是一个流行的开源关系型数据库管理系统(RDBMS),它使用结构化查询语言(SQL)进行数据库管理。MySQL由瑞典的MySQL AB公司开发,后被Sun Microsystems公司收购,而Sun Microsystems又在2010年被甲骨文公司...

    MySQL中文参考手册

    MySQL是一种广泛使用的开源关系型数据库管理系统(RDBMS),它以其高效、稳定和易用性在众多企业级应用中占据重要地位。这份“MySQL中文参考手册”是学习和理解MySQL数据库系统的重要资源,尤其对于初学者和开发者来...

    如何利用Debugview方式来调试MySQL UDF

    总的来说,使用Debugview调试MySQL UDF是一种灵活且实用的方法,特别适合那些暂时无法使用特定开发工具的内置调试器,或者需要进行远程调试的情况。通过输出关键信息和返回值,开发者可以更有效地定位问题,提升开发...

    mysql 学习用例(console程序)

    MySQL是一种广泛使用的开源关系型数据库管理系统(RDBMS),它基于结构化查询语言(SQL)进行数据操作。作为初学者,了解并掌握MySQL对于数据库管理至关重要。以下是对MySQL的一些核心概念和常见操作的详细说明: 1...

    MySQL初级与高级教程

    ### MySQL初级与高级教程知识点概览 #### 一、MySQL基础知识 1. **数据库系统概述** - 数据库系统的组成:数据库(Data)、数据库管理系统(DBMS)、数据库管理员(DBA)。 - 关系型数据库特点:通过表格形式来...

    JBuilder连接MySQL的驱动配置

    - 在`Database Pilot`窗口中选择`View -&gt; Options -&gt; Drivers`。 - 点击`Add`按钮,在`Driver class`中输入:`com.mysql.jdbc.Driver`。 - 在`Sample URL`中输入:`jdbc:mysql://localhost:3306/DataBaseName`。 ...

    mysql 基本命令,及java中访问mysql

    CREATE VIEW 视图名称 AS SELECT * FROM 表名 WHERE 条件; ``` 15. **数据导出与导入**: - **导出整个数据库**: ``` mysqldump -uroot -p 数据库名称 &gt; 文件保存位置 ``` - **导出特定表**: ``` ...

    mysql使用实例book-MySQL

    通过`CREATE VIEW view_name AS SELECT column1, column2 FROM table_name;`可以创建视图,简化复杂的查询。 触发器是预定义的数据库操作,当特定事件发生时自动执行。这在实现业务规则和数据验证时非常有用。例如...

    mysql.rar_MYSQL数据库_MySQL软件_SQL软件

    - 视图(View):虚拟表,基于一个或多个表的查询结果。 - 存储过程(Stored Procedure):预编译的SQL语句集合,可以提高性能并简化复杂操作。 - 触发器(Trigger):自动执行的数据库操作,基于特定的事件(如...

Global site tag (gtag.js) - Google Analytics