mysql--西北狼--第二周--view

xiaolilinux

0.基本内容：
  1) the  meaning and function of  view;
  2) how  to create  a  view
  3) how  to correct a view
  4) how  to read a view
  5) how  to delete a view

the  view  is virtual  table  in DB. the different  persons or departments only can 
         read the partedand  related  information.
the  view  makes  the information much safer in DB.
the  view  choose  the useful  information  for  you.

the   format  of  creating :

    create  [ algorithm = {undefined|merge|temptable}]
                  view   试图名   【(属性清单)】
         as  select   属性
    【with   [cascaded|local]   check  option】

Parameter  instruction :
     undefined 表示mysql自动选择要使用的算法  (zan bu tai shu)
     merge  表示使用试图的语句和试图定义结合，使得试图的定义的一部分取代语句的对应部分
     temptable 试图的结果插入临时表，然后使用临时表来执行语句

     cascaded(级联)  更新试图要考虑父试图的约束条件.有利于数据安全



本章用到的两个数据表：
mysql> show  tables;
+------------------+
| Tables_in_view   |
+------------------+
| department       |
| department_view1 |
| department_view2 |
| department_view3 |
| department_view4 |
| info_view        |
| info_view1       |
| work_info        |
| worker           |
| worker_view1     |
+------------------+
10 rows in set (0.00 sec)

mysql> desc   department;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id     | int(4)      | NO   | PRI | NULL    |       |
| d_name   | varchar(20) | NO   | UNI | NULL    |       |
| function | varchar(40) | YES  |     | NULL    |       |
| address  | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc    worker;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| num         | int(12)     | NO   | PRI | NULL    |       |
| d_id        | int(4)      | YES  | MUL | NULL    |       |
| name        | varchar(20) | NO   |     | NULL    |       |
| ***         | varchar(4)  | NO   |     | NULL    |       |
| birthday    | datetime    | NO   |     | NULL    |       |
| homeaddress | varchar(40) | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)







mysql> create   algorithm=merge  view
    -> info_view (id,name,***,address)
    -> as  select  id,name,***,address
    -> FROM   work_info  where  age>20
    -> with  local  check  option;
Query OK, 0 rows affected (0.05 sec)

mysql> desc  info_view;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(4)      | NO   |     | NULL    |       |
| name    | varchar(12) | NO   |     | NULL    |       |
| ***     | varchar(4)  | NO   |     | NULL    |       |
| address | varchar(40) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from info_view;
+------+---------+------+----------+
| id   | name    | ***  | address  |
+------+---------+------+----------+
| 1003 | wangwu  | male | xinjiang |
| 1004 | zhaoliu | fema | tunufan  |
+------+---------+------+----------+
2 rows in set (0.02 sec)

mysql> create   algorithm=merge  
view    info_view1 (id,name,***,address)
 as  select  id,name,***,address from   work_info  where age<20
 with  local   check  option;
Query OK, 0 rows affected (0.04 sec)

mysql> select  * from   info_view1;
+------+----------+------+---------+
| id   | name     | ***  | address |
+------+----------+------+---------+
| 1001 | zhangsan | male | beijing |
+------+----------+------+---------+
1 row in set (0.00 sec)

mysql> desc  work_info;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(4)      | NO   | PRI | NULL    |       |
| name    | varchar(12) | NO   |     | NULL    |       |
| ***     | varchar(4)  | NO   |     | NULL    |       |
| age     | int(2)      | YES  |     | NULL    |       |
| address | varchar(40) | YES  |     | NULL    |       |
| tel     | varchar(6)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> select  *  from    work_info;
+------+----------+------+------+----------+--------+
| id   | name     | ***  | age  | address  | tel    |
+------+----------+------+------+----------+--------+
| 1001 | zhangsan | male |   18 | beijing  | 123456 |
| 1002 | lisi     | fema |   20 | tianjing | 234567 |
| 1003 | wangwu   | male |   21 | xinjiang | 345678 |
| 1004 | zhaoliu  | fema |   21 | tunufan  | 456789 |
+------+----------+------+------+----------+--------+
4 rows in set (0.00 sec)


在多表上创建试图：

create  algorithm=merge   view
     worker_view1(name,department,***,age,address)
    as  select  name,department.d_name,***,2011-birthday,address
       from    worker,department  where  worker.d_id=department.d_id
       with   local   check   option;

mysql> desc   worker_view1;
+------------+--------------+------+-----+----------+-------+
| Field      | Type         | Null | Key | Default  | Extra |
+------------+--------------+------+-----+----------+-------+
| name       | varchar(20)  | NO   |     | NULL     |       |
| department | varchar(20)  | NO   |     | NULL     |       |
| ***        | varchar(4)   | NO   |     | NULL     |       |
| age        | double(23,6) | NO   |     | 0.000000 |       |
| address    | varchar(40)  | YES  |     | NULL     |       |
+------------+--------------+------+-----+----------+-------+
5 rows in set (0.00 sec)



用到数据插入 更新的语句：

insert  into   work_info   values (1001,'zhangdan','male','beijing','123456');

update   work_info   set  ***='fema'  where  id=1001;

修改试图：

alter   algorithm=merge  view
     info_view (id,name,***,address)
     as  select  id,name,***,address
     from   work_info   where  age < 20
      with  local   check  option;

删除试图：
  drop  view  info_view;