14 MySQL 视图

2019-02-12 本文已影响23人 Kokoronashi

MySQL 视图

[toc]

视图概述

视图介绍

什么是视图

虚拟表
内容与真实的表相似,有字段有记录
视图并不在数据库中以存储的数据的形式存在
行和列的数据来自定义视图时查询所引用的基表,并且在具体引用视图时动态生成
更新视图的数据,就是更新基表的数据
更新基表的数据,仕途的数据也会跟着改变

视图优点

简单

用户不需关心视图中的数据如何查询获得
视图中的数据已经是过滤好的符合条件的结果集

安全

用户只能看到视图中的数据

数据独立

一旦视图结构确定,可以屏蔽表结构对用户的影响

视图使用限制

不能在视图上创建索引
在视图的 FROM 子句中不能使用子查询
以下情形中的视图是不可更新的
- 包含以下关键字的 SQL 语句: 聚合函数( SUM, MIN, MAX,COUNT等), DISTINCT, GROUP BY, HAVING, UNION 或 UNION ALL
- 常量视图, JOIN, FROM 一个不能更新的视图
- WHERE 子句的子查询引用了 FROM 子句中的表
- 使用了临时表

视图基本使用

创建视图

create view 视图名称 as SQL查询;

create view 试图名称(字段名列表) as SQL查询;

注意: 在视图表中不定义字段名的话,默认使用基表的字段名,若定义字段名,视图表中的字段必须和基表的字段个数相等.

示例

#准备基表
mysql> create database db9;
mysql> use db9;
mysql> create table user(
    -> name char(20),
    -> password char(1),
    -> uid int(2),
    -> gid int(2),
    -> comment char(150),
    -> homedir char(150),
    -> shell char(30)
    -> );
mysql> system cp /etc/passwd /var/lib/mysql-files
mysql> load data infile "/var/lib/mysql-files/passwd" into table user fields terminated by ":" lines terminated by "\n";
mysql> alter table user add id int(2) primary key auto_increment first

#创建视图 不指定视图字段名称
mysql> create view t1 as select name,shell from user where uid<=20;
Query OK, 0 rows affected (0.32 sec)

mysql> show tables;
+---------------+
| Tables_in_db9 |
+---------------+
| t1            |
| user          |
+---------------+
2 rows in set (0.01 sec)

mysql> create view t3 as select name,uid,gid from user limit 3;
mysql> select * from t3;
+--------+------+------+
| name   | uid  | gid  |
+--------+------+------+
| root   |    0 |    0 |
| bin    |    1 |    1 |
| daemon |    2 |    2 |
+--------+------+------+

#创建视图 指定视图字段名称
#视图表里字段名和SQL查询里字段名必须一致
mysql> create view t4(user,stu_uid,stu_gid) as select name,uid,gid from user limit 3;
mysql> select * from t4;
+--------+---------+---------+
| user   | stu_uid | stu_gid |
+--------+---------+---------+
| root   |       0 |       0 |
| bin    |       1 |       1 |
| daemon |       2 |       2 |
+--------+---------+---------+


#数据目录下 视图 只有表结构,数据来源自基础表
[root@test6 db9]# ls
db.opt  t1.frm  user.frm  user.ibd

mysql> select * from t1;
+----------+----------------+
| name     | shell          |
+----------+----------------+
| root     | /bin/bash      |
| bin      | /sbin/nologin  |
| daemon   | /sbin/nologin  |
| adm      | /sbin/nologin  |
| lp       | /sbin/nologin  |
| sync     | /bin/sync      |
| shutdown | /sbin/shutdown |
| halt     | /sbin/halt     |
| mail     | /sbin/nologin  |
| operator | /sbin/nologin  |
| games    | /sbin/nologin  |
| ftp      | /sbin/nologin  |
+----------+----------------+
12 rows in set (0.00 sec)

查看视图

show table status;

show table status where comment="view"\G

查看创建视图具体命令

show create view 视图名;

示例

# 查看哪些表是视图
mysql> show table status where comment="view"\G;
*************************** 1. row ***************************
           Name: t1
         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)

#查看视图的数据基于哪个物理表
mysql>  show create view t1\G;
*************************** 1. row ***************************
                View: t1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t1` AS select `user`.`name` AS `name`,`user`.`shell` AS `shell` from `user` where (`user`.`uid` <= 20)
character_set_client: utf8
collation_connection: utf8_general_ci

使用视图

查询记录

select 字段名列表 from 视图名 where 条件;

插入记录

insert into 视图名(字段名列表) values(字段值列表);

更新记录

update 视图名 set 字段名=值 where 条件;

删除记录

Delete from 视图名 where 条件;

对视图操作即是对基表操作,反之依然 !!!

示例

修改

修改基表会影响视图,修改视图会影响基表


mysql> create view v5 as select id,name,shell,password from user;
mysql> select * from v5;
+----+-----------------+----------------+----------+
| id | name            | shell          | password |
+----+-----------------+----------------+----------+
|  1 | root            | /bin/bash      | x        |


mysql> update v5 set name="tom" where id=1;

mysql> select * from v5;
+----+-----------------+----------------+----------+
| id | name            | shell          | password |
+----+-----------------+----------------+----------+
|  1 | tom             | /bin/bash      | x        |

mysql> select id,name,shell,password from user;
+----+-----------------+----------------+----------+
| id | name            | shell          | password |
+----+-----------------+----------------+----------+
|  1 | tom             | /bin/bash      | x        |

增加

增加视图会增加基表

mysql> insert into v5(name,shell,password) values("leo","shelltest","x");

mysql> select * from v5 where name="leo";
+----+------+-----------+----------+
| id | name | shell     | password |
+----+------+-----------+----------+
| 24 | leo  | shelltest | x        |
+----+------+-----------+----------+

mysql> select id,name,shell from user where name="leo";
+----+------+-----------+
| id | name | shell     |
+----+------+-----------+
| 24 | leo  | shelltest |
+----+------+-----------+

删除视图

drop view 视图名;

删除视图表不会影响基表

视图进阶

创建视图的完全格式

命令格式

CREATE
[OR REPLACE]
[ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER}]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[ WITH { CASCADED | LOCAL } CHECK OPTION]

设置字段别名

视图中的字段名不可以重复,所以要定义别名

create view 视图名

as

select 表别名.源字段名 as 字段别名 from 源表名表别名 left join 源表名表别名 on 条件;

create view v2
as
select a.name as aname, b.name as bname,a.uid as auid,b.uid as buid from user a left join info b on a.uid=b.uid;

重要选项说明

OR REPLACE

create or replace view 视图名 as select 查询;

创建时,若视图已存在,会替换已有视图

mysql> create view t1 as select * from info;
Query OK, 0 rows affected (0.01 sec)

#提示已存在
mysql> create view t1 as select * from info;
ERROR 1050 (42S01): Table 't1' already exists

#无提示,已覆盖
mysql> create or replace view t1 as select * from info;
Query OK, 0 rows affected (0.01 sec)

ALGORITHM

ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }

参数	意义
MERAGE	替换方式
TEMPTABLE	具体化方式
UNDEFINED	未定义

LOCAL 和 CASCADED

LOCAL 和 CASCADED关键字决定检查的范围

关键字	检查范围
LOCAL	仅检查当前视图的限制
CASCADED	同时要满足基表的限制 ( 默认值 )

示例

# 创建 info 表
mysql> create table info select name,uid,shell from user limit 5;

# 创建视图 v2 ,user表 别名a,字段重命名,
mysql> create view v2 as select a.name as aname, b.name as bname, a.uid as auid, b.uid as buid from user a left join info b on a.uid=b.uid;

mysql> select * from v2;
+-----------------+--------+-------+------+
| aname           | bname  | auid  | buid |
+-----------------+--------+-------+------+
| tom             | tom    |     0 |    0 |
| bin             | bin    |     1 |    1 |
| daemon          | daemon |     2 |    2 |
| adm             | adm    |     3 |    3 |
| lp              | lp     |     4 |    4 |
| sync            | NULL   |     5 | NULL |
| shutdown        | NULL   |     6 | NULL |
| halt            | NULL   |     7 | NULL |
| mail            | NULL   |     8 | NULL |
| operator        | NULL   |    11 | NULL |
| games           | NULL   |    12 | NULL |
| ftp             | NULL   |    14 | NULL |
| nobody          | NULL   |    99 | NULL |
| systemd-network | NULL   |   192 | NULL |
| dbus            | NULL   |    81 | NULL |
| polkitd         | NULL   |   999 | NULL |
| sshd            | NULL   |    74 | NULL |
| postfix         | NULL   |    89 | NULL |
| ntp             | NULL   |    38 | NULL |
| mysql           | NULL   |    27 | NULL |
| rpc             | NULL   |    32 | NULL |
| rpcuser         | NULL   |    29 | NULL |
| nfsnobody       | NULL   | 65534 | NULL |
+-----------------+--------+-------+------+

14 MySQL 视图

MySQL 视图

视图概述

视图介绍

视图优点

简单

安全

数据独立

视图使用限制

视图基本使用

创建视图

示例

查看视图

示例

使用视图

查询记录

插入记录

更新记录

删除记录

示例

删除视图

视图进阶

创建视图的完全格式

设置字段别名

重要选项说明

OR REPLACE

ALGORITHM

LOCAL 和 CASCADED

示例

猜你喜欢

热点阅读

14 MySQL 视图

MySQL 视图

视图概述

视图介绍

视图优点

简单

安全

数据独立

视图使用限制

视图 基本使用

创建视图

示例

查看视图

示例

使用视图

查询记录

插入记录

更新记录

删除记录

示例

删除视图

视图 进阶

创建视图的完全格式

设置字段别名

重要选项说明

OR REPLACE

ALGORITHM

LOCAL 和 CASCADED

示例

猜你喜欢

热点阅读

视图基本使用

视图进阶