视图的常用方法
查看是否有创建视图的权限:
SELECT SELECT_priv,create_view_priv from mysql.user WHERE user=‘root’
创建视图
CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];
参数说明:
(1)ALGORITHM:可选项,表示视图选择的算法。
(2)视图名:表示要创建的视图名称。
(3)属性清单:可选项,指定视图中各个属性的名词,默认情况下与SELECT语句中的查询的属性相同。
(4)SELECT语句:表示一个完整的查询语句,将查询记录导入视图中。
(5)WITH CHECK OPTION:可选项,表示更新视图时要保证在该视图的权限范围之内。
指定属性清单:
CREATE OR REPLACE VIEW view_user (a_id,a_name)
AS
SELECT id,name FROM tb_user;
列子:
CREATE VIEW V_VIEW2(ID, SNAME, SEX, AGE, CLASS, GRADE, HOBBY, TOTAL_SCORE, RANK)
AS SELECT a.ID, a.SNAME, a.SEX, a.AGE,a.CLASS, a.GRADE, a.HOBBY, b.TOTAL_SCORE, b.RANK
FROM student a,student_score b WHERE a.ID=b.SID;
修改视图:
ALTER VIEW 视图名称
AS
封装的查询条件
------------------------------------------------------------------
CREATE VIEW card
AS
SELECT * FROM card_sell_flow
SELECT * FROM card WHERE id=40288977211129301721115508
------------------------------------------------------------------
create or replace view 视图名 (视图存在就修改 不存就创建)
如果我们想修改上面的视图:
create or replace view card
AS
其他的封装的查询条件
删除视图:
drop view 视图名称,视图名称....;(可以删除多个视图)
DROP VIEW IF EXISTS view_user; (删除视图是指删除数据库中已存在的视图。删除视图时,只能删除视图的定义,不会删除数据。MySQL中,使用DROP VIEW语句来删除视图。但是,用户必须拥有DROP权限。 大多數我們都是有root权限的)
查看视图:
DESC 视图名称 查看视图解构
show create view 视图名称 查看视图解构
视图好处:
- 重用SQL语句
- 简化复杂的SQL操作,不必知道他的查询细节
- 保护数据,提高安全性
视图可以套视图:
CREATE VIEW card
AS
SELECT * FROM card_sell_flow
CREATE VIEW cardTwo
AS
select * from card(这是第一个视图)
MySQL视图中使用IF和CASE语句
在创建视图时,经常需要使用到MySQL的流程控制语句,如:IF语句和CASE语句。
-- 判断数据表是否存在,存在则删除
DROP TABLE IF EXISTS tb_staff;
-- 创建数据表
CREATE TABLE IF NOT EXISTS tb_staff
(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '编号',
NAME VARCHAR(50) NOT NULL COMMENT '姓名',
sex INT COMMENT '性别(1:男;2:女;)',
dept_code VARCHAR(10) COMMENT '部门编号',
is_post BIT COMMENT '是否在职(0:否;1:是)'
) COMMENT = '员工信息表';
添加员工数据:
-- 新增数据
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_01',1,'1001',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_02',2,'1002',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_03',1,'1003',0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_04',1,'1001',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_05',2,'1008',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_06',1,'1001',0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_07',2,'1002',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_08',1,'1003',0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_09',1,'1001',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_10',2,'1008',0);
-- 创建视图
CREATE OR REPLACE VIEW view_staff
AS
SELECT id
,NAME
,IF(sex=1,'男','女') AS sex_name
,CASE dept_code
WHEN '1001' THEN '研发部'
WHEN '1002' THEN '人事部'
WHEN '1003' THEN '财务部'
ELSE '其他'
END AS dept_name
,IF(is_post,'在职','离职') AS is_post_name
FROM tb_staff
;
视图操作限制:
创建视图时需要注意以下几点:
(2)select语句不能包含from子句中的子查询;
(3)select语句不能引用系统或用户变量;
(4)select语句不能引用预处理语句参数;
(5)在存储子程序内,定义不能引用子程序参数或局部变量;
(6)在定义中引用的表或视图必须存在。但是,创建了视图后,能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题,可使用check table语句;
(7)在定义中不能引用temporary表,不能创建temporary视图;
(8)在视图定义中命名的表必须已存在;
(9)不能将触发程序与视图关联在一起;
(10)在视图定义中允许使用order by,但是,如果从特定视图进行了选择,而该视图使用了具有自己order by的语句,它将被忽略。-
(11)数据库视图的可更新性和视图中的查询的定义有关系的,以下类型的视图是不可更新的。
有关键字sql语句:sum,min,max,count,distinct,group by
having,union,unionall 主要是指聚合函数
下面的视图是不可更新的。
更新不成功实例: 因为有 distinct
视图和表的联系和区别:
视图:只是保存了 sql 逻辑(视图主要应用了 查询使用 一般不要使用增删改 而且视图可以给他添加权限;只能读不能写)
表: 只是保存了 数据
1、视图是已经编译好的sql语句;而表不是。
2、视图没有实际的物理记录;而表有。
3、视图是窗口;表是内容
4、标是用物理空间而视图不占用物理空间,视图只是逻辑概念的存在;表可以修改,但视图只能有创建的语句来修改。
5、表是内模式,视图是外模式。
联系:视图实在基本表之上建立的表,它的结构(即所有定义列)和内容(即所有数据行)都来自基本表,它依据基本表存在而存在。一个视图可以对应一个基本表,也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。 一套接一套