签到成功

知道了

CNDBA社区CNDBA社区

Mysql视图

2021-11-25 13:04 856 0 原创 mysql
作者: hbhe0316

创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一张表中,也可以建立在多张表中。
1.查看是否有创建视图的权限

http://www.cndba.cn/hbhe0316/article/22220

mysql> SELECT  Select_priv, Create_view_priv  FROM  mysql.user  WHERE  user='root';
+-------------+------------------+
| Select_priv | Create_view_priv |
+-------------+------------------+
| Y           | Y                |
+-------------+------------------+
1 row in set (0.00 sec)

2.创建视图
语法如下:
CREATE [ALGORITHM={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW view_name [( column_list )]
AS SELECT_statement
[ WITH | [ CASCADED | LOCAL ] CHECK OPTION];http://www.cndba.cn/hbhe0316/article/22220

ALGORITHM 是可选参数,表示视图选择的算法。ALGORTHIM参数的取值有3个,分别是UNDEFINED、MERGE和TEMPTABLE。UNDEFINED 表示 MySQL 将自动选择算法M;MERGE 表示将使用的视图语句与视图定义合并,使得视图定义的某一部分取代语句对应的部分; TEMPTABLE 表示将视图的结果存入临时表,然后用临时表来执行语句。

view_name 参数表示要创建的视图名称。http://www.cndba.cn/hbhe0316/article/22220http://www.cndba.cn/hbhe0316/article/22220

column_list 是可选参数,指定视图中各个属性的名词,默认情况下与 SELECT 语句中查询的属性相同。http://www.cndba.cn/hbhe0316/article/22220

SELECT_statement 参数是个完整的查询语句,表示从某个表中查出某些满足条件的记录,将这些记录导入视图中。http://www.cndba.cn/hbhe0316/article/22220

WITH CHECK OPTION 是可选参数,表示更新视图时要保证在该视图的权限范围之内。
CASCADED 与 LOCAL 为可选参数,CASCADED 为默认值。表示更新视图时要满足所有相关视图和表的条件;LOCAL 表示更新视图时满足该视图本身定义的条件即可。

3.在单表上创建视图

mysql> create table t3 (id int,name varchar(10) not null,sex varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3 values (1,'zhangsan','man');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values (2,'lisi','feman');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+----------+-------+
| id   | name     | sex   |
+------+----------+-------+
|    1 | zhangsan | man   |
|    2 | lisi     | feman |
+------+----------+-------+
2 rows in set (0.00 sec)

mysql> create view view_t3 as select id,name from t3;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_t3;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

4.在多表创建视图

http://www.cndba.cn/hbhe0316/article/22220

mysql> select * from t3;
+------+----------+-------+
| id   | name     | sex   |
+------+----------+-------+
|    1 | zhangsan | man   |
|    2 | lisi     | feman |
+------+----------+-------+
2 rows in set (0.00 sec)

mysql> select * from t4;
+------+---------+
| id   | menu    |
+------+---------+
|    1 | English |
|    2 | math    |
+------+---------+
2 rows in set (0.00 sec)


mysql> create view view_t5(id) as select t3.id from t3,t4 where t3.id=t4.id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_t5;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from view_t5;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

5.查看视图

http://www.cndba.cn/hbhe0316/article/22220

mysql> show create view view_t5 /G
*************************** 1. row ***************************
                View: view_t5
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t5` AS select `t3`.`id` AS `id` from (`t3` join `t4`) where (`t3`.`id` = `t4`.`id`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

6.更新视图
可以对视图进行增删改

mysql> update view_t3 set id=88 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_t5;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> select * from t3;
+------+----------+-------+
| id   | name     | sex   |
+------+----------+-------+
|   88 | zhangsan | man   |
|    2 | lisi     | feman |
+------+----------+-------+
2 rows in set (0.00 sec)

mysql> select * from t4;
+------+---------+
| id   | menu    |
+------+---------+
|    1 | English |
|    2 | math    |
+------+---------+
2 rows in set (0.00 sec)

7.删除视图

http://www.cndba.cn/hbhe0316/article/22220
http://www.cndba.cn/hbhe0316/article/22220

mysql> drop view view_t5;
Query OK, 0 rows affected (0.00 sec)

版权声明:本文为博主原创文章,未经博主允许不得转载。

MYSQL

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
hbhe0316

hbhe0316

关注

1.只有承认无知,才能装下新的东西; 2.进步来自一点点滴滴的积累; 3.广博让你更优秀,而专业让你无法替代; 4.挫折和失败能够转换为一种财富。

  • 889
    原创
  • 1
    翻译
  • 13
    转载
  • 24
    评论
  • 访问:1159896次
  • 积分:1523
  • 等级:核心会员
  • 排名:第6名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ