宝山北京网站建设,海底捞口碑营销案例,php mysql网站开发,微网站免费开发平台视图是什么#xff1f; 一张虚表#xff0c;和真实的表一样。视图包含一系列带有名称的行和列数据。视图是从一个或多个表中导出来的#xff0c;我们可以通过insert#xff0c;update#xff0c;delete来操作视图。当通过视图看到的数据被修改时#xff0c;相应的原表的数…视图是什么 一张虚表和真实的表一样。视图包含一系列带有名称的行和列数据。视图是从一个或多个表中导出来的我们可以通过insertupdatedelete来操作视图。当通过视图看到的数据被修改时相应的原表的数据也会变化。同时原表发生变化则这种变化也可以自动反映到视图中。视图的优点简单化看到的就是需要的。视图不仅可以简化用户对数据的理解也可以简化操作。经常被使用的查询可以制作成一个视图安全性通过视图用户只能查询和修改所能见到的数据数据库中其他的数据既看不见也取不到。数据库授权命令可以让每个用户对数据库的检索限制到特定的数据库对象上但不能授权到数据库特定的行列上。逻辑数据独立性视图可帮助用户屏蔽真实表结构变化带来的影响创建视图语法格式ALTER[ALGORITHM {UNDEFINED | MERGE | TEMPTABLE}][DEFINER user]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]注释Undefined不常用。 merge 表示将使用的视图语句与视图定义合并起来使视图定义的某一部分取代语句对应的部分 temptable 表示将视图的结果存入临时表然后用临时表来执行语句with[cascaded|local] Cascaded 默认为cascaded表示更新视图时满足所有相关视图和表的条件Local 表示更新视图时满足该视图本身定义的条件即可创建单表视图mysql create table t(quantity int,price int);
Query OK, 0 rows affected (0.02 sec)
mysql insert into t values(3,50);
Query OK, 1 row affected (0.00 sec)
mysql create view view_t as select quantity,price,quantity*price from t; #创建view_t视图
Query OK, 0 rows affected (0.01 sec)查看mysql select * from view_t;
---------------------------------
| quantity | price | quantity*price |
---------------------------------
| 3 | 50 | 150 |
---------------------------------
1 row in set (0.00 sec)
mysql create view view_t2(qty,price,total) as - select quantity,price,quantity*price - from t;
Query OK, 0 rows affected (0.00 sec)
mysql select * from view_t2;
--------------------
| qty | price | total |
--------------------
| 3 | 50 | 150 |
--------------------
1 row in set (0.00 sec)创建多表视图mysql create table student(- s_id int primary key,- s_name varchar(30),- s_age int,- s_sex varchar(8));
Query OK, 0 rows affected (0.00 sec)
mysql create table stu_info(- s_id int,- class varchar(50),- addr varchar(100));
Query OK, 0 rows affected (0.00 sec)
mysql insert into stu_info(s_id,class,addr)- values- (1,erban,anhui),- (2,sanban,chongqing),- (3,yiban,shangdong);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql create view stu_class(id,name,class) as select student.s_id,student.s_name,stu_info.class from student,stu_info where student.s_idstu_info.s_id;
Query OK, 0 rows affected (0.00 sec)查看视图的结构信息 语法格式Describe 视图名mysql desc stu_class;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | NO | | NULL | |
| name | varchar(30) | YES | | NULL | |
| class | varchar(50) | YES | | NULL | |
-----------------------------------------------
3 rows in set (0.01 sec)查看视图的基本信息语法格式Show table status like ‘视图名’\G; mysql show table status like stu_class\G
*************************** 1. row ***************************Name: stu_classEngine: NULLVersion: NULLRow_format: NULLRows: NULLAvg_row_length: NULLData_length: NULL
Max_data_length: NULLIndex_length: NULLData_free: NULLAuto_increment: NULLCreate_time: NULLUpdate_time: NULLCheck_time: NULLCollation: NULLChecksum: NULLCreate_options: NULLComment: VIEW
1 row in set (0.01 sec)查看视图的详细信息语法格式Show create view 视图名或show create view 视图名\Gmysql show create view view_t\G
*************************** 1. row ***************************View: view_tCreate View: CREATE ALGORITHMUNDEFINED DEFINERrootlocalhost SQL SECURITY DEFINER VIEW view_t AS select t.quantity AS quantity,t.price AS price,(t.quantity * t.price) AS quantity*price from t
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec) 在mysql中information schema 数据库下的views表中存储了所有视图的定义。通过对views表的查询可以查看数据库中所有视图的详细信息。mysql select * from information_schema.views\G修改视图语法格式ALTERVIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]
mysql create or replace view view_t as select * from t;
Query OK, 0 rows affected (0.00 sec)mysql desc view_t;
----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------
| quantity | int(11) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
----------------------------------------------
2 rows in set (0.00 sec)修改视图mysql alter view view_t as select quantity from t;
Query OK, 0 rows affected (0.00 sec)mysql desc view_t;
----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------
| quantity | int(11) | YES | | NULL | |
----------------------------------------------
1 row in set (0.00 sec)更新视图updatemysql select *from t;
-----------------
| quantity | price |
-----------------
| 3 | 50 |
-----------------
1 row in set (0.00 sec)mysql select * from view_t;
----------
| quantity |
----------
| 3 |
----------
1 row in set (0.00 sec)mysql update view_t set quantity5; #这个操作是quantity列全部都为5加where可以指定某行
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select *from view_t;
----------
| quantity |
----------
| 5 |
----------
1 row in set (0.00 sec)mysql select * from t;
-----------------
| quantity | price |
-----------------
| 5 | 50 |
-----------------
1 row in set (0.00 sec)Insertmysql insert into t values(3,5);
Query OK, 1 row affected (0.00 sec)mysql select * from t;
-----------------
| quantity | price |
-----------------
| 5 | 50 |
| 3 | 5 |
-----------------
2 rows in set (0.00 sec)mysql select * from view_t2;
--------------------
| qty | price | total |
--------------------
| 5 | 50 | 250 |
| 3 | 5 | 15 |
--------------------
2 rows in set (0.00 sec)deletemysql select * from view_t2;
--------------------
| qty | price | total |
--------------------
| 5 | 50 | 250 |
| 3 | 5 | 15 |
--------------------
2 rows in set (0.00 sec)mysql delete from view_t2 where price5;
Query OK, 1 row affected (0.00 sec)mysql select * from view_t2;
--------------------
| qty | price | total |
--------------------
| 5 | 50 | 250 |
--------------------
1 row in set (0.00 sec) 视图存在以下情况时更新操作无法执行 视图中不包含基表中被定义为非空的列 在定义视图的select语句后的字段列表中使用了数学表达式 在定义视图的select 语句后字段列表中使用了聚合函数时不接受更新操作select中使用了union \top \group by 或having 无法接受删除视图语法格式DROP VIEW [IF EXISTS] view_name1 [, view_name2] ...mysql drop view if exists stu_class;
Query OK, 0 rows affected (0.00 sec)mysql drop view if exists stu_class;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql show warnings;
--------------------------------------------
| Level | Code | Message |
--------------------------------------------
| Note | 1051 | Unknown table bbs.stu_class |
--------------------------------------------
1 row in set (0.00 sec)mysql show create view stu_class;
ERROR 1146 (42S02): Table bbs.stu_class doesnt exist扩展 MySQL中视图和表的区别以及联系是什么 1、两者的区别 1视图是已经编译好的SQL语句是基于SQL语句的结果集的可视化的表而表不是。 2视图没有实际的物理记录而表有。 3表是内容视图窗口 4表和视图虽然都占用物理空间但是视图只是逻辑概念存在而表可以及时对数据进行修改但是视图只能用创建语句来修改 5视图是查看数据表的一种方法可以查询数据表中某些字段构成的数据只是一些SQL 语句的集合。从安全角度来说视图可以防止用户接触数据表因而不知道表结构 6表属于全局模式中的表是实表。而视图属于局部模式的表是虚表 7视图的建立和删除只影响视图本身而不影响对应表的基本表2、两者的联系 视图是在基本表之上建立的表它的结构和内容都来自于基本表它依赖基本表存在而存在。一个视图可以对应一个基本表也可以对应多个基本表。视图是基本的抽象和逻辑意义上建立的关系。
