db2视图入门一

 

以下简单的示例所使用的数据库都是建立在db2 9.5版本下，示例代码运行是在quest4.8版本运行

 

建立二张表： employees员工表，department部门表

  create table administrator.employees ( --员工表
  id integer ,                        --员工编号
  name varchar(50),         --员工姓名
  age integer,                    --员工年龄
  departmentid integer) ;--员工所在部门的编号
  
  insert into administrator.employees(id,name,age,department)values(1,"zhangsan",23,1);
  insert into administrator.employees(id,name,age,department)values(2,"lisi",24,2);
  insert into administrator.employees(id,name,age,department)values(3,"wangwu",22,2);
  insert into administrator.employees(id,name,age,department)values(4,"wangwu",26,3);
  
  create table administrator.department (
  id integer ,                     --部门编号
  name varchar(50),      --部门名称
  fileid integer) ;             --文件名称
  
  insert into administrator.department(id,name,fileid)values(1,'技术部',1);
  insert into administrator.department(id,name,fileid)values(2,'文化部',21);
  insert into administrator.department(id,name,fileid)values(3,'组织部',31);

//建立视图，语法是create view 模式.表名 as select....（sql语句）

create view readfile as
  select e.id as eid,e.name as ename,e.age as eage,
         d.id as did,d.name as dname,d.fileid as dfileid,
  from  administrator.department as d,administrator.employees as e
  where d.id = e.departmentid

执行查询：

select * from readfile01

查询结果：

1 'zhangsan' 23 1 '技术部' 1
2 'lisi' 24 2 '文化部' 21
3 'wangwu' 22 2 '文化部' 21
4 'liuliu' 26 3 '组织部' 31