文档章节

mysql视图

五大三粗
 五大三粗
发布于 2015/05/18 12:48
字数 2571
阅读 9
收藏 0
点赞 0
评论 0

1) mysql> show table status where comment='view'; 
(说明:Mysql5.1支持视图,视图被看作一种抽象表,因此显示视图状态的语句与显示表状态的语句相同,只是在comment列中以‘view’区分)

2) mysql> select * from information_schema.tables where table_schema='yourDatabaseName' and table_type='view';
(说明:这种方法通过系统表查找,效果同上,显示信息更详细。如果不能正确显示结果,可能是大小写的问题,Mysql在不同系统平台不同配置参数下的显示结果可能不同,注意这点。)

 

 

 

 

 

 

 

 

 

----------------------

 

 

一. 视图概述
视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列 数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。

对其中所引用的基础表来说,视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表,或者其它视图。通过视图进行查询没有任 何限制,通过它们进行数据修改时的限制也很少。

视图是存储在数据库中的查询的SQL 语句,它主要出于两种原因:安全原因, 视图可以隐藏一些数据,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等,另一原因是可使复杂的查询易于理解和使用。

  视图:查看图形或文档的方式。

  视图是从一个或多个表或视图中导出的表,其结构和数据是建立在对表的查询基础上的。和表一样,视图也是包括几个被定义的数据列和多个数据行,但 就本质而言这些数据列和数据行来源于其所引用的表。

  所以视图不是真实存在的基础表而是一张虚表,视图所对应的数据并不实际地以视图结构存储在数据库中,而是存储在视图所引用的表中。

  视图一经定义便存储在数据库中,与其相对应的数据并没有像表那样又在数据库中再存储一份,通过视图看到的数据只是存放在基本表中的数据。对视图 的操作与对表的操作一样,可以对其进行查询、修改(有一定的限制)、删除。

  当对通过视图看到的数据进行修改时,相应的基本表的数据也要发生变化,同时,若基本表的数据发生变化,则这种变化也可以自动地反映到视图中。

  视图有很多优点,主要表现在:
  •视点集中
  •简化操作
  •定制数据
  •合并分割数据
  •安全性
当然视图也存在一些缺点,最大的缺点就是视图带来的更新负担,比如源数据改了,那么视图中要做相应更新,视图中数据改了源数据也要做同步,这和MySQL 的Cache差不多。

二. 创建视图——CREATE VIEW

1. 语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]通过该语句可以创建视图,若给定了[OR REPLACE],则表示当已具有同名的视图时,将覆盖原视图。select_statement是一个查询语句,这个查询语句可从表或其它的视图中查 询。视图属于数据库,因此需要指定数据库的名称,若未指定时,表示在当前的数据库创建新视图。

表和数据库共享数据库中相同的名称空间,因此,数据库不能包含相同名称的表和视图,并且,视图的列名也不能重复。

2. 使用举例

Eg. 本例创建一个产品表(product)和一个购买记录表(purchase),再通过视图purchase_detail查询出购买的详细信息。

CREATE TABLE product

(

product_id INT NOT NULL,

name VARCHAR(50) NOT NULL,

price DOUBLE NOT NULL

);

INSERT INTO product VALUES(1, ‘apple ‘, 5.5);

CREATE TABLE purchase

(

id INT NOT NULL,

product_id INT NOT NULL,

qty INT NOT NULL DEFAULT 0,

gen_time DATETIME NOT NULL

);

INSERT INTO purchase VALUES(1, 1, 10, NOW());

CREATE VIEW purchase_detail AS SELECT product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

创建成功后,输入:SELECT * FROM purchase_detail;

运行效果如下:

+——-+——-+—–+————-+

| name | price | qty | total_value |

+——-+——-+—–+————-+

| apple | 5.5 | 10 | 55 |

+——-+——-+—–+————-+

1 row in set (0.01 sec)

3. 注意事项

创建视图存在如下注意事项:

(1) 运行创建视图的语句需要用户具有创建视图(CRATE VIEW)的权限,若加了[OR REPLACE]时,还需要用户具有删除视图(DROP VIEW)的权限;

(2) SELECT语句不能包含FROM子句中的子查询;

(3) SELECT语句不能引用系统或用户变量;

(4) SELECT语句不能引用预处理语句参数;

(5) 在存储子程序内,定义不能引用子程序参数或局部变量;

(6) 在定义中引用的表或视图必须存在。但是,创建了视图后,能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题,可使用CHECK TABLE语句;

(7) 在定义中不能引用TEMPORARY表,不能创建TEMPORARY视图;

(8) 在视图定义中命名的表必须已存在;

(9) 不能将触发程序与视图关联在一起;

(10) 在视图定义中允许使用ORDER BY,但是,如果从特定视图进行了选择,而该视图使用了具有自己ORDER BY的语句,它将被忽略。

三. 修改视图——ALTER VIEW

1. 语法

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。

2. 使用举例

Eg. 将上一小节中中创建的视purchase_detail进行修改,去掉qty列,语句如下:

ALTER VIEW purchase_detail AS SELECT product.name as name, product .price as price, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

此时通过语句:select * from purchase_detail;对视图进行查询时,结果如下:

+——-+——-+————-+
| name | price | total_value |

+——-+——-+————-+

| apple | 5.5 | 55 |

+——-+——-+————-+

3. 注意事项

修改视图的注意事项除了第一条外跟创建视图的注意事项是一样的。第(1)条应改为:

该语句需要具有针对视图的CREATE VIEW和DROP权限,也需要针对SELECT语句中引用的每一列的某些权限。

四. 删除视图——DROP VIEW

1. 语法

DROP VIEW [IF EXISTS] view_name [, view_name] … [RESTRICT | CASCADE]该语句用户删除视图,可一次删除多个视图。[IF EXISTS]选项确保语句正确运行。若没有该子句,当指定的视图不存在时,将发生错误。

2. 使用举例

Eg1. 删除在前面的小节中创建的视图purchase_detail:DROP VIEW purchase_detail;

Eg2. 删除一个未知的视图:DROP VIEW IF EXISTS test_view;

Eg3. 删除多个视图:DROP VIEW IF EXISTS test_view1, test_view2;

3. 注意事项

必须对要删除的一个或多个视图拥有DROP VIEW的权限。

 

 

 

 

 

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
这个命令可以帮助你找到匹配的表格和视图
例如
SHOW TABLE STATUS from tarena能找到tarena数据库实例的所有表格和视图

 

 

 

-------------------------------------实例

 

CREATE DEFINER=`root`@`%` PROCEDURE `upload_timecard`()
    COMMENT 'sdf'
BEGIN
/*
  功能: HRMS从EXCEL导入数据到系统中
  存储过程名称:upload_timecard
  创建人:Terry o r
  创建日期:2008-1-4
*/
insert into timecard_detail
(card_id,name,card_time,locaiton,kinds,dept,create_date) (select
card_id,name,card_time,locaiton,kinds,dept,create_date
from timecard_temp);


/*Create temp table tmp_timecard*/
drop table if exists tmp_timecard;
CREATE TEMPORARY TABLE `tmp_timecard` (
  `card_id` varchar(50) default NULL,
  `name` varchar(50) default NULL,
  `card_date` date default NULL,
  `first_time` datetime default NULL,
  `last_time` datetime default NULL,
  KEY `AK_Identifier_2` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Insert  data into temp table and update record*/
insert into tmp_timecard (card_id,name,card_date,first_time,last_time)
select card_id,name,date(card_time) as card_time,min(card_time) as first_time,max(card_time) as last_time
from timecard_temp  where name<>'' group by  card_id,name,date(card_time) order by name,card_time;

update tmp_timecard set first_time=if(hour(first_time)<12,first_time,null),last_time=if(hour(last_time)>12,last_time,null) where first_time=last_time;

/*insert into timecard*/
insert into timecard (card_id,name,card_date,first_time,last_time)
select card_id,name,card_date,first_time,last_time from tmp_timecard;
call update_timecard();
END$$

--
DELIMITER ;
--

 

 

 

-- 
-- Procedures
-- 
DELIMITER $$
-- 
CREATE DEFINER=`test`@`%` PROCEDURE `count_timecard`(IN tYear INTEGER(11), IN tMonth INTEGER(11))
BEGIN
     declare i_error int default 0;
     declare p_emp_no varchar(50);
     declare p_emp_id int;
     declare p_full_name varchar(100);
     declare p_dept_id int default 0;
     declare p_card_date varchar(10);
     declare workdays int;           
     declare p_need_work_days int;     
     declare i int default 0;
     declare monthdays int;         
     declare holidaynum int default 0;  
     declare daysofWeek int default 0;
     declare maxWorkDate date;        
     declare tDate date;
     declare newDate date;
     
     declare rs_sur cursor for (
     select e.emp_no,e.id,r.name,e.dept_id,r.workdays from (
     select name,count(*) as workdays from timecard
     where year(card_date)=tYear and month(card_date)=tMonth
     and (not isnull(first_time) or not isnull(last_time)) and name
     in(select name_cn from employee)
     and (WEEKDAY(card_date)<5 or date(card_date) in (select spec_date from spec_workdate))
     group by name) as  r,employee e where r.name=e.name_cn
     );
     declare continue handler for sqlstate '02000' set i_error=1;
     open rs_sur;
     if tMonth<10 then
        set p_card_date=concat(concat(tYear,'-0'),tMonth);
     else
        set p_card_date=concat(concat(tYear,'-'),tMonth);
     end if;
     
     set monthdays=day(last_day(concat(p_card_date,'-1')));
     
     drop TEMPORARY  table if exists tmp_workdate;
     CREATE  TEMPORARY TABLE `tmp_workdate` (
     `work_date` date default NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    select max(card_date) from  timecard where year(card_date)=tYear and month(card_date)=tMonth and isnull(status) and (first_time<>'' or last_time<>'')   into maxWorkDate;
     
     set tDate =concat(tYear,'-',tMonth,'-1');
     while i<monthdays do
         set newDate =date_add(tDate,interval i day);
         if (WEEKDAY(newDate)=5 or WEEKDAY(newDate)=6) then
            if not exists(select id from spec_workdate where spec_date=newDate) then
               set daysofWeek=daysofWeek+1;
            else
                if newDate<=maxWorkDate then
                   insert into tmp_workdate values(newDate);
                end if;
            end if;
         else
            if not exists(select id from holiday_set where spec_date=newDate) then
               if newDate<=maxWorkDate then
                  insert into tmp_workdate values(newDate);
               end if;
            end if;
         end if;
         set i=i+1;
     end while;
     select count(*) from holiday_set where year(spec_date)=tYear and month(spec_date)=tMonth into holidaynum;
     set holidaynum= daysofWeek+holidaynum;
     set p_need_work_days =monthdays-holidaynum;
     START  TRANSACTION;
     while i_error=0 do
           fetch rs_sur into p_emp_no,p_emp_id,p_full_name,p_dept_id,workdays;
           if i_error=0 then
                  if exists(select * from timecard_rpt where full_name=p_full_name and cur_date=p_card_date) THEN
                     update timecard_rpt set cur_m_days=monthdays,holiday_days=holidaynum,need_work_days=p_need_work_days,fact_work_days=workdays where full_name=p_full_name and cur_date=p_card_date;
                  else
                      insert into timecard_rpt (dept_id,emp_no,emp_id,full_name,cur_date,cur_m_days,holiday_days,need_work_days,fact_work_days)
                      values (p_dept_id,p_emp_no,p_emp_id,p_full_name,p_card_date,monthdays,holidaynum,p_need_work_days,workdays);
                  end if;
          end if;
     end while;
     close rs_sur;
     
     
     update timecard_rpt a inner join (SELECT name,count(*) as times FROM `view_later_early` where  (first_time>set_come_time ) and  year(card_date)=tYear and month(card_date)=tMonth group by name)  b
     on a.full_name=b.name and a.cur_date=p_card_date
     set a.late_times=b.times;
      update timecard set status=31 where id in (
      SELECT id  FROM `view_later_early` where  (first_time>set_come_time) and  year(card_date)=tYear and month(card_date)=tMonth
      );
     
    update timecard_rpt a inner join (SELECT name,count(*) as times FROM `view_later_early` where  (last_time<set_go_time ) and  year(card_date)=tYear and month(card_date)=tMonth group by name)  b
     on a.full_name=b.name and a.cur_date=p_card_date
     set a.leave_early=b.times;
      update timecard set status=32 where id in (
      SELECT id  FROM `view_later_early` where  (last_time<set_go_time  or f_time<w_time) and  year(card_date)=tYear and month(card_date)=tMonth
      );
     COMMIT;
 END$$

© 著作权归作者所有

共有 人打赏支持
五大三粗
粉丝 155
博文 890
码字总数 4537901
作品 0
广州
程序员
mysql之视图详解

视图就是一个存在于数据库中的虚拟表。 视图本身没有数据,只是通过执行相应的select语句完成获得相应的数据。 目录 创建视图: 删除视图: 修改视图: 视图缩减业务逻辑 视图的执行算法: ...

mac_zhao ⋅ 2015/01/22 ⋅ 0

[转] mysql 视图性能 算法选择

MySQL在处理视图时有两种算法,分别称为MERGE和TEMPTABLE。在执行"CREATE VIEW"语句时可以指定使用哪种算法。所谓MERGE是指在处理涉及到视图的操作时,将对视图的操作根据视图的定义进行展开...

红薯 ⋅ 2010/01/07 ⋅ 0

MySQL View 视图分析

为什么MySQL中很少使用视图功能? 首先MySQL使用视图有什么好处: 清晰简单,可以让简单的语句逻辑更清晰 可复用,可以让部分复杂的sql逻辑多次复用,统一更新 安全,可以隐藏掉一些私密的表...

懒懒惰惰 ⋅ 05/11 ⋅ 0

mysql视图学习总结

一、使用视图的理由是什么? 1.安全性。一般是这样做的:创建一个视图,定义好该视图所操作的数据。之后将用户权限与视图绑定。这样的方式是使用到 了一个特性:grant语句可以针对视图进行授予...

mifans ⋅ 2016/10/19 ⋅ 0

《MySQL必知必会》读书笔记(四) 22~30章 完结篇

1、视图 视图不包含表中应该有的任何列或数据,它包含的是一个SQL查询。在视图创建之后,可以用于表基本相同的方式利用它们。可以对视图执行SELECT操作,过滤和排序数据,将视图联结到其他视...

一万 ⋅ 2016/02/08 ⋅ 0

mysql 1449错误

一、问题 为了更好地管理mysql数据库,重新创建了一个新账号:accout 并给予all privileges, 但是开发人员在使用视图的时候出现了mysql 1449的错误,网上说要给账号所有权限。 最后找到一篇文...

famiover ⋅ 2016/04/20 ⋅ 0

There is no 'root'@'%' registered

把别人机器上的MYSQL中的一个数据库导出来,生成了一个.sql的文件 在我的机器上导入这个.sql文件之后,在数据库连接时出现了如下错误: “There is no 'root'@'%' registered” 解决办法: ...

liangtee ⋅ 2013/02/27 ⋅ 0

【数据库优化专题】MySQL视图优化(一)

本期数据库优化专题分享,为大家带来的是DBA+社群MySQL领域原创专家——李海翔所著的MySQL视图优化系列文章。以下是第一部分的内容,未完部分敬请关注后续更新。 专家简介 李海翔 网名:那海...

李海翔 ⋅ 2015/11/06 ⋅ 0

BeX5开发中MySQL视图使用的一个小问题

问题 数据操作中的查询是极端重要的,使用频繁。MySQL数据库中视图概念的存在十分类似于微软Access小型关系数据库中的查询(二级考试重点)。视图的存在极大减小了数据表的创建数量,而且还有...

朱先忠老师 ⋅ 2017/06/30 ⋅ 0

MySQL重命名表(RENAME TABLE语句)

在本教程中,您将学习如何使用MySQL RENAME TABLE语句和ALTER TABLE语句来重命名表。 MySQL RENAME TABLE语句简介 由于业务需求的变化,我们需要将当前表重命名为一个新的名称,以更好地反映...

易百教程 ⋅ 2016/09/27 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Android JNI 读写Bitmap的方法

Java层创建Bitmap,通过JNI将Bitmap传到C/C++进行处理 Java部分 public static native boolean greenBitmap(Bitmap bitmap); C/C++部分 JNIEXPORT jboolean JNICALL Java_com_test_Test_gree......

国仔饼 ⋅ 16分钟前 ⋅ 0

一次性让你懂async/await,解决回调地狱

什么是async? 欢迎留言讨论 async 函数是 Generator 函数的语法糖。使用 关键字 async 来表示,在函数内部使用 await 来表示异步。相较于 Generator,async 函数的改进在于下面四点: 内置执...

阿K1225 ⋅ 17分钟前 ⋅ 0

angular常用命令

.下载更新操作 1.利用npm下载angular的命令行工具AngularCLI: npm install -g @angular/cli 2.下载jquery: npm install --save jquery 3.更新npm: npm i -g npm 4.更新angular: ng update ......

消散了的诗意 ⋅ 19分钟前 ⋅ 0

window.print 页面打印

定义和用法 print() 方法用于打印当前窗口的内容。 语法 window.print(); window.print() 实际上,是浏览器打印功能菜单的一种程序调用。与点击打印功能菜单一样,不能精确分页,不能设置纸型...

初学者的优化 ⋅ 20分钟前 ⋅ 0

魔兽世界 7.0版本上 PVE装备全攻略

  T套 因为大家应该都会打穿副本的所以具体是哪个boss我就不说了。   T1: 所有套装都在【熔火之心】出   T2: 头原来是在【奥妮克希亚的巢穴】改到黑翼之巢的奈法利安了,腿是在【熔火之...

wangchen1999 ⋅ 20分钟前 ⋅ 0

java.math.BigDecimal使用小结

原文地址 java.math.BigDecimal使用小结 divide方法 使用BigDecimal.divide方法时一定要考虑: 除数是否为0 商是否是无限小数 正确的使用方式 判断除数是否为0,是0做另外的处理逻辑 调用除法...

666B ⋅ 23分钟前 ⋅ 0

关于qstring转char乱码问题。

if (OpenClipboard(NULL)) { HGLOBAL hgClip; EmptyClipboard(); QByteArray byay = FValue.toLocal8Bit(); //转latin编码 char *bochsrc_line = byay.data(); hgClip = GlobalAlloc(GMEM_DD......

backtrackx ⋅ 23分钟前 ⋅ 0

了解SSH加密和连接过程

介绍 SSH或安全shell是安全协议,也是安全管理远程服务器的最常用方式。通过使用多种加密技术,SSH提供了一种机制,用于在双方之间建立加密安全连接,对彼此进行身份验证,以及来回传递命令和...

吴伟祥 ⋅ 30分钟前 ⋅ 0

微信小程序

小程序的全局配置app.json 微信小程序的全局配置保存在app.json文件中。开发者通过使用app.json来配置页面文件(pages)的路径、窗口(window)表现、设定网络超时时间值(networkTimeout)以...

上官清偌 ⋅ 33分钟前 ⋅ 0

【转】百度坐标坐标系之间的转换(JS版代码)

/** * Created by Wandergis on 2015/7/8. * 提供了百度坐标(BD09)、国测局坐标(火星坐标,GCJ02)、和WGS84坐标系之间的转换 *///定义一些常量var x_PI = 3.1415926535897932...

HAVENT ⋅ 34分钟前 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部