1)什么是视图?
一个拥有独立名称的在数据库中一直保持的衍生表。
2)什么是衍生表?
在MySQL和MyISAM存储引擎下,表的内容(列的值)将会存储在磁盘的.MYD
和.MYI文件中。这类型的表文件是基础表和基本,因此叫做基本表。在基本表之上
是衍生表,这种表的列的值来自基本表和文本,捉着来自系统变量比如
current_time.当我们执行"FROM table1, table2" or "GROUP BY x"或者"SELECT
..."时就产生了衍生表,上面这些语句都是从一个或多个中获得数据从而产生另外
一个表,执行select语句后产生的是结果集,但如果将这个结果集命名后存储就得
到一个查看过的表。也就是视图。
CREATE VIEW语法:
CREATE [OR REPLACE]
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW view-name
[(column-list)]
AS select-statement
[WITH[CASCADED|LOCAL|CHECK OPTION]
示例如下:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db5 |
| mybatis |
| mysql |
| test |
+--------------------+
5 rows in set (0.44 sec)
mysql> use db5;
Database changed
mysql> show tables;
+---------------+
| Tables_in_db5 |
+---------------+
| error_log |
| t |
| t2 |
| t3 |
+---------------+
4 rows in set (0.00 sec)
mysql> create view v1 as select * from t;
Query OK, 0 rows affected (0.28 sec)
mysql> insert into v values(22);
ERROR 1146 (42S02): Table 'db5.v' doesn't exist
mysql> insert into v1 values(22);
Query OK, 1 row affected (0.19 sec)
mysql> select * from v;
ERROR 1146 (42S02): Table 'db5.v' doesn't exist
mysql> select * from v1;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 22 |
+------+
6 rows in set (0.01 sec)
mysql> select * from t;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 22 |
+------+
6 rows in set (0.00 sec)
3)衍生表可以进行什么操作?
操作有增删查改,就像操作表一样。但前提是这种视图的结构足够简单。
示例:
mysql> delete from t from v1 where v1.s1=22;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax
to use near 'from v1 where v1.s1=22' at line 1
mysql> delete from v1 where v1.s1=22;
Query OK, 1 row affected (0.11 sec)
mysql> select * from v1;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
5 rows in set (0.00 sec)
mysql> select * from t;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
5 rows in set (0.00 sec)
mysql> update v1 set s1=222 where v1.s1=4;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from v1;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
mysql> select * from s1;
ERROR 1146 (42S02): Table 'db5.s1' doesn't exist
mysql> select * from t;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
4)衍生表跟基本表的区别,为什么有了基本表还要使用衍生表?
a.衍生表可以提高基本表的使用效率。
如果视图除了对单个表执行select外不包括其他定义的话,那么这个视图
就是该表的一个同义词,就上上面的实例一样。
b.视图中可以包括列以及表达式。
c.在视图中你可以使用任意的子查询或子句。
d.视图的select定义中可以包括表达式。
mysql> create view v2 as select s1+10 from t;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from v;
ERROR 1146 (42S02): Table 'db5.v' doesn't exist
mysql> select * from v2;
+-------+
| s1+10 |
+-------+
| 10 |
| 11 |
| 12 |
| 13 |
| 232 |
+-------+
5 rows in set (0.00 sec)
mysql> select * from t;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
e.视图中可以包含视图,即视图可以作为其他视图的基本表
mysql> create view v4 as select * from v3;
Query OK, 0 rows affected (0.02 sec)
mysql> create view v5 as select * from v4;
Query OK, 0 rows affected (0.05 sec)
mysql> select * frm v5;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax
to use near 'frm v5' at line 1
mysql> select * from v5;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
mysql> select * from v4;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
mysql> select * from v3;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
自己的理解:视图就是你想要某种结果,所以你写了一堆的sql语句来实现,并
且定义为视图。
因为视图是衍生表,更新的结果会使原来的基本表相关数据被更新,但是应该不是
绝对的?就想上面的d特征一样。
一个拥有独立名称的在数据库中一直保持的衍生表。
2)什么是衍生表?
在MySQL和MyISAM存储引擎下,表的内容(列的值)将会存储在磁盘的.MYD
和.MYI文件中。这类型的表文件是基础表和基本,因此叫做基本表。在基本表之上
是衍生表,这种表的列的值来自基本表和文本,捉着来自系统变量比如
current_time.当我们执行"FROM table1, table2" or "GROUP BY x"或者"SELECT
..."时就产生了衍生表,上面这些语句都是从一个或多个中获得数据从而产生另外
一个表,执行select语句后产生的是结果集,但如果将这个结果集命名后存储就得
到一个查看过的表。也就是视图。
CREATE VIEW语法:
CREATE [OR REPLACE]
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW view-name
[(column-list)]
AS select-statement
[WITH[CASCADED|LOCAL|CHECK OPTION]
示例如下:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db5 |
| mybatis |
| mysql |
| test |
+--------------------+
5 rows in set (0.44 sec)
mysql> use db5;
Database changed
mysql> show tables;
+---------------+
| Tables_in_db5 |
+---------------+
| error_log |
| t |
| t2 |
| t3 |
+---------------+
4 rows in set (0.00 sec)
mysql> create view v1 as select * from t;
Query OK, 0 rows affected (0.28 sec)
mysql> insert into v values(22);
ERROR 1146 (42S02): Table 'db5.v' doesn't exist
mysql> insert into v1 values(22);
Query OK, 1 row affected (0.19 sec)
mysql> select * from v;
ERROR 1146 (42S02): Table 'db5.v' doesn't exist
mysql> select * from v1;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 22 |
+------+
6 rows in set (0.01 sec)
mysql> select * from t;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 22 |
+------+
6 rows in set (0.00 sec)
3)衍生表可以进行什么操作?
操作有增删查改,就像操作表一样。但前提是这种视图的结构足够简单。
示例:
mysql> delete from t from v1 where v1.s1=22;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax
to use near 'from v1 where v1.s1=22' at line 1
mysql> delete from v1 where v1.s1=22;
Query OK, 1 row affected (0.11 sec)
mysql> select * from v1;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
5 rows in set (0.00 sec)
mysql> select * from t;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
5 rows in set (0.00 sec)
mysql> update v1 set s1=222 where v1.s1=4;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from v1;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
mysql> select * from s1;
ERROR 1146 (42S02): Table 'db5.s1' doesn't exist
mysql> select * from t;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
4)衍生表跟基本表的区别,为什么有了基本表还要使用衍生表?
a.衍生表可以提高基本表的使用效率。
如果视图除了对单个表执行select外不包括其他定义的话,那么这个视图
就是该表的一个同义词,就上上面的实例一样。
b.视图中可以包括列以及表达式。
c.在视图中你可以使用任意的子查询或子句。
d.视图的select定义中可以包括表达式。
mysql> create view v2 as select s1+10 from t;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from v;
ERROR 1146 (42S02): Table 'db5.v' doesn't exist
mysql> select * from v2;
+-------+
| s1+10 |
+-------+
| 10 |
| 11 |
| 12 |
| 13 |
| 232 |
+-------+
5 rows in set (0.00 sec)
mysql> select * from t;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
e.视图中可以包含视图,即视图可以作为其他视图的基本表
mysql> create view v4 as select * from v3;
Query OK, 0 rows affected (0.02 sec)
mysql> create view v5 as select * from v4;
Query OK, 0 rows affected (0.05 sec)
mysql> select * frm v5;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax
to use near 'frm v5' at line 1
mysql> select * from v5;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
mysql> select * from v4;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
mysql> select * from v3;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 222 |
+------+
5 rows in set (0.00 sec)
自己的理解:视图就是你想要某种结果,所以你写了一堆的sql语句来实现,并
且定义为视图。
因为视图是衍生表,更新的结果会使原来的基本表相关数据被更新,但是应该不是
绝对的?就想上面的d特征一样。