Mysql学习笔记五

第八章 视图

视图是虚拟的表，不包含实际的物理数据，当查询的时候，可以直接查询视图，视图在去原来的表取数据，可以保护数据库数据的安全

创建视图

CREATE VIEW v2 AS SELECT bookName,price FROM t_book;

CREATE VIEW v3(b,p) AS SELECT bookName,price FROM t_book;

SELECT * FROM v1;

SELECT * FROM v2;

SELECT * FROM v3;

多表创建视图

CREATE VIEW v4 AS SELECT bookName,bookTypeName FROM t_book,t_booktype WHERE t_book.bookTypeId=t_booktype.id;

用别名

CREATE VIEW v5 AS SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_booktype tby WHERE tb.bookTypeId=tby.id;

SELECT * FROM v4;

SELECT * FROM v5;

查看视图

DESC v5;

SHOW TABLE STATUS LIKE 'v5';

SHOW TABLE STATUS LIKE 't_book';

查看详细信息

SHOW CREATE VIEW v5;

修改视图

就是修改视图的定义

create or replace view 语句修改视图（可以修改可以创建视图）

create or replace view v1(bookName,price) as select bookName,price from t_book;

alter语句修改视图（不可创建）

alter view v1 as select * from t_book;

更新视图

只能更新权限范围内的视图

插入

insert into v1 values(null,’java good’,120,’feng’,1);

更新

update v1 set bookName=’java very good’,price=200 where id=5;

删除

delete from v1 where id=5;

删除视图

删除视图只会删除视图，不会删除实际的数据

drop view if exists v4;