MySQL-视图
视图是什么?
一张虚表,和真实的表一样。视图包含一系列带有名称的行和列数据。视图是从一个或多个表中导出来的,我们可以通过insert,update,delete来操作视图。当通过视图看到的数据被修改时,相应的原表的数据也会变化。同时原表发生变化,则这种变化也可以自动反映到视图中。
视图的优点
简单化:看到的就是需要的。视图不仅可以简化用户对数据的理解,也可以简化操作。经常被使用的查询可以制作成一个视图
安全性:通过视图用户只能查询和修改所能见到的数据,数据库中其他的数据既看不见也取不到。数据库授权命令可以让每个用户对数据库的检索限制到特定的数据库对象上,但不能授权到数据库特定的行,列上。
逻辑数据独立性:视图可帮助用户屏蔽真实表结构变化带来的影响
创建视图
语法格式:
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_id=stu_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_class
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.01 sec)
查看视图的详细信息
语法格式:Show create view 视图名;
或show create view 视图名\G
mysql> show create view view_t\G
*************************** 1. row ***************************
View: view_t
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 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
修改视图
语法格式:
ALTER
VIEW 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)
更新视图
update
mysql> 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 quantity=5; #这个操作是quantity列全部都为5,加where可以指定某行
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> 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)
Insert
mysql> 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)
delete
mysql> 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 price=5;
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' doesn't exist
扩展
MySQL中视图和表的区别以及联系是什么?
1、两者的区别:
(1)视图是已经编译好的SQL语句,是基于SQL语句的结果集的可视化的表,而表不是。
(2)视图没有实际的物理记录,而表有。
(3)表是内容,视图窗口
(4)表和视图虽然都占用物理空间,但是视图只是逻辑概念存在,而表可以及时对数据进行修改,但是视图只能用创建语句来修改
(5)视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL 语句的集合。从安全角度来说,视图可以防止用户接触数据表,因而不知道表结构
(6)表属于全局模式中的表,是实表。而视图属于局部模式的表,是虚表
(7)视图的建立和删除只影响视图本身,而不影响对应表的基本表
2、两者的联系
视图是在基本表之上建立的表,它的结构和内容都来自于基本表,它依赖基本表存在而存在。一个视图可以对应一个基本表,也可以对应多个基本表。视图是基本的抽象和逻辑意义上建立的关系。
相关文章
- 怎么卸载电脑上的mysql_mysql installer
- MySQL Error number: MY-010092; Symbol: ER_CANT_CREATE_PID_FILE; SQLSTATE: HY000 报错 故障修复 远程处理
- mysql explain中key_len的作用详解数据库
- 乘除MySQL字段:四则运算实战(mysql字段加减)
- 在iOS上搭建MySQL数据库连接(ios连接mysql数据库)
- PostgreSQL vs. MySQL: 数据库对比(postgresql和mysql)
- MySQL数据库环境: 简单易用的搭建指南(mysql数据库环境说明)
- MySQL中利用多表创建视图的指南(mysql多表创建视图)
- 调整MySQL的隔离级别:必要的技巧(mysql修改隔离级别)
- 据库MySQL连接之路:连接数据库的步骤(mysql连接数)
- 利用MySQL存储过程实现返回值传递(mysql存储过程返回值)
- MySQL占用内存一图搞懂(mysql占内存)
- 如何在CMD中快速打开MySQL服务器(在cmd中打开mysql)
- MySQL C库:构建强大、高效的数据库系统(mysqlc库)
- 式处理MySQL实现数据分布式处理的技术(mysql数据分布)
- 如何高效设计MySQL用户表?(mysql用户表设计)
- MySQL存储过程的应用及优势(mysql存储过程适用)
- PHP和MySQL如何支持并发访问,提升网站性能?(phpmysql并发)
- 机制Java深入解析MySQL锁机制(javamysql锁)
- MySQL设计之外键使用实践(mysql设计外键)
- MySQL表性能优化:突破性能瓶颈(mysql表优化)
- MySQL执行顺序探索(mysql的执行顺序)
- 如何在MySQL中删除表的索引(mysql删除表的索引)
- 如何使用MySQL改变列的类型:简单易懂的步骤(mysql改变列的类型)
- MySQL实现一对多数据分页浅析(mysql 一对多 分页)
- MYSQL导入文件遭遇失败!(mysql导入文件失败)
- MySQL 5.0安装指南(mysql 5.0 安装)
- MySQL实现当前时间的获取(mysql获取当前时间)
- Mysql两表数据异同分析(mysql两表的不同数据)
- BMOB 与 MySQL完美结合提供稳定优质服务(bmob与mysql)
- 命令行下如何快速重启MySQL服务(cmd命令重启mysql)
- 2010连接MySQL数据库的新路径(2010连接mysql)
- 如何在MySQL中创建上午下午时间(mysql 上午下午)
- MySQL无法激活,如何解决(mysql不能激活码)
- MySQL 登录无需密码,安全问题需警惕(mysql不用密码就登录)
- mysql删除表中某一字段重复的记录