mysql视图

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