mysql视图
mysql视图
五大三粗 发表于3年前
mysql视图
  • 发表于 3年前
  • 阅读 6
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

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$$

共有 人打赏支持
粉丝 152
博文 890
码字总数 4484662
×
五大三粗
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: