MySQL视图

MySQL视图

一、什么是视图
    视图是存放数据的一个接口，也可以说是虚拟表，这些数据可以是从一个或几个基表（视图）的数据，也可是用户自己定义的数据，其实视图里面不存放数据，数据据还是存在基表里面，基表数据发生变化，视图里的数据也随之变量，视图里数据变化，基表也会变化。

二、视图的作用
    1. 视图可以让查询变得很清楚（复杂的SQL语句变得很简单）
    
    2. 保护数据库的重要数据， 给不同的人看不同的数据
    

三、创建视图
    create [or replace] [algorithm={merge|temptable|undefined}]
    view view_name [(column_list)]
    as select_statement
    [with [cascaded|local] check option]

----------------------创建视图实例------------------------------------
    --学生表
    create table student (
        id int primary key auto_increment,
        name varchar(20) not null,
        age int(3) not null,
        email varchar(50) not null
    );
    
    insert into student values(null,'张三',30,'zhangsan@aa.com');
    insert into student values(null,'李四',30,'lisi@aa.com');
    insert into student values(null,'王五',50,'wangwu@aa.com');
    insert into student values(null,'李岩',35,'liyan@aa.com');
    insert into student values(null,'赵六',90,'zhaoliu@aa.com');
    insert into student values(null,'孙七',15,'sunqi@aa.com');
    
mysql> select * from student;
+----+------+-----+-----------------+
| id | name | age | email           |
+----+------+-----+-----------------+
|  1 | 张三 |  30 | zhangsan@aa.com |
|  2 | 李四 |  30 | lisi@aa.com     |
|  3 | 王五 |  50 | wangwu@aa.com   |
|  4 | 李岩 |  35 | liyan@aa.com    |
|  5 | 赵六 |  90 | zhaoliu@aa.com  |
|  6 | 孙七 |  15 | sunqi@aa.com    |
+----+------+-----+-----------------+
6 rows in set (0.00 sec)

    --成绩表
    create table score (
        id int primary key auto_increment,
        cid int not null,  --学生id
        english int not null,
        computer int not null,
        math int not null,
        constraint fk_id foreign key(cid) references student(id)   --建立外键
    );
    
    insert into score values(null,2,80,70,50);
    insert into score values(null,3,100,50,80);
    insert into score values(null,4,90,30,55);
    insert into score values(null,5,90,77,40);
    insert into score values(null,6,66,72,89);
    insert into score values(null,1,81,71,31);
    
mysql> select * from score;
+----+-----+---------+----------+------+
| id | cid | english | computer | math |
+----+-----+---------+----------+------+
|  1 |   2 |      80 |       70 |   50 |
|  2 |   3 |     100 |       50 |   80 |
|  3 |   4 |      90 |       30 |   55 |
|  4 |   5 |      90 |       77 |   40 |
|  5 |   6 |      66 |       72 |   89 |
|  6 |   1 |      81 |       71 |   31 |
+----+-----+---------+----------+------+
6 rows in set (0.00 sec)

--多表联合查询

mysql> select s1.name,s1.age,s2.english,s2.computer,s2.math from student s1 join score s2 on s1.id = s2.id;
+------+-----+---------+----------+------+
| name | age | english | computer | math |
+------+-----+---------+----------+------+
| 张三 |  30 |      80 |       70 |   50 |
| 李四 |  30 |     100 |       50 |   80 |
| 王五 |  50 |      90 |       30 |   55 |
| 李岩 |  35 |      90 |       77 |   40 |
| 赵六 |  90 |      66 |       72 |   89 |
| 孙七 |  15 |      81 |       71 |   31 |
+------+-----+---------+----------+------+
6 rows in set (0.00 sec)

--创建视图
create view stusc_view as  select s1.name,s1.age,s2.english,s2.computer,s2.math from student s1 join score s2 on s1.id = s2.id;

--查询视图
mysql> select * from stusc_view;
+------+-----+---------+----------+------+
| name | age | english | computer | math |
+------+-----+---------+----------+------+
| 张三 |  30 |      80 |       70 |   50 |
| 李四 |  30 |     100 |       50 |   80 |
| 王五 |  50 |      90 |       30 |   55 |
| 李岩 |  35 |      90 |       77 |   40 |
| 赵六 |  90 |      66 |       72 |   89 |
| 孙七 |  15 |      81 |       71 |   31 |
+------+-----+---------+----------+------+
6 rows in set (0.00 sec)

    

视图有三种类型
    Merge: 会将引用视图的语句的文本与视图定义结合起来，使得视图定义的某一部分取代语句的对应部分

    Temptable: 不可更新，只有与基表一一对应才可以更新。

    undefined ： 默认选项，一般倾向于选择 Megre

    algorithm=merge
-----例子-------
    
mysql> create view stu3_view as select * from student where age > 30;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from stu3_view where age < 50;
+----+------+-----+--------------+
| id | name | age | email        |
+----+------+-----+--------------+
|  4 | 李岩 |  35 | liyan@aa.com |
+----+------+-----+--------------+
1 row in set (0.00 sec)

----说明----
    merge:查询stu3_view视图，相当于查询基表select * from student where age > 30 and age < 50;
    
    

    with local check option     [local]只要满足本视图的条件就可以更新
    with cascaded check option  [cascaded]则是必须满足所有针对视图的条件，才可以更新
    ------说明----------
    ---创建学生1视图
    create view stu1_view as select * from student where age > 30;
    --在学生1视图基础上创建学生2视图
    create view stu2_view as select * from stu1_view where age < 90;

  插入或更新stu2_view视图，如果是local只需满足age<90即可。如果是cascaded则需要满足age>30 and age <90;
    
四、视图的操作
    
    

五、更改视图

    alter [or replace] [algorithm={merge|temptable|undefined}]
    view view_name [(column_list)]
    as select_statement
    [with [cascaded|local] check option]
    
    

六、删除视图

    drop view 视图名;
    
mysql> drop view stu3_view;
Query OK, 0 rows affected (0.00 sec)

七、查看视图

        show tables;
        --查看建视图语句
        mysql> show create view stu1_view \G;
*************************** 1. row ***************************
       View: stu1_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu1_view` AS select `student`.`id` AS
ame` AS `name`,`student`.`age` AS `age`,`student`.`email` AS `email` from `student` where (`student`.`age` > 30)

        ---查看表是否是视图，看最后一行
        mysql> show table status like 'stu1_view' \G;
*************************** 1. row ***************************
           Name: stu1_view
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

八、视图在WEB开发中的应用（PHP中使用视图）

<?php

    
    $mysqli = new mysqli('localhost','root','root','xsphp');
    if(mysqli_connect_errno()) {
        printf('连接数据库出错，出错原因：%s',mysqli_connect_error());
        exit;
    }
    $mysqli->set_charset('gbk');
    $sql = 'select * from stusc_view';
    $result = $mysqli->query($sql);
    echo '<table border="1" align="center">';
    //输出列名
    echo '<tr>';
    while($field = $result->fetch_field()) {
        echo "<th>{$field->name}</th>";
    }
    echo '</tr>';
    //输出数据
    while($row = $result->fetch_assoc()) {
        echo '<tr>';
        foreach($row as $value) {
            echo "<td>{$value}</td>";
        }
        echo '</tr>';
    }
    echo '</table>';

    $mysqli->close();