MySQL 视图之创建、查看、修改

视图是从数据库中一个或多个表中导出来的虚拟表，方便用户对数据的操作。

数据库中只有数据的定义，没有存放视图的数据，那些数据是在原来的表里。

视图具有这些作用：数据安全；数据表与应用程序独立；简化用户的操作。

前提：查询用户是否有select和create view的权限。

/*
我有两个mysql，当我将WampSever开启后，命令行直接进入了这个5.6.17版本，而另一个设置了环境变量的5.5.28直接忽略了。吓死我了，开始以为发生了
什么诡异事件。新建一个数据库mydb，并新建一张表，导入写好的数据（我懒得一条一条的插入了）。
*/
mysql> use mydb;
Database changed

mysql> select select_priv,create_view_priv from mysql.user where user='root';
+-------------+------------------+
| select_priv | create_view_priv |
+-------------+------------------+
| Y           | Y                |
| Y           | Y                |
| Y           | Y                |
+-------------+------------------+
3 rows in set (0.00 sec) 

mysql> create table pet (breed varchar(10), sum int);

mysql> create table person (name varchar(10), age int);

load data local infile "D:/c.csv" into table person character set 'utf8'  
fields terminated by ',' optionally enclosed by '"' escaped by '"'   
lines terminated by '\n';

mysql> create view person_view1(P_nm,P_ag) as select name,age from person;
Query OK, 0 rows affected (0.11 sec)

mysql> select * from person_view1;
+----------+------+
| P_nm     | P_ag |
+----------+------+
| ?Stephen |   19 |
| Elena    |   18 |
| Demon    |   19 |
| David    |   20 |
| jordan   |   25 |
| James    |   24 |
| Jane     |   23 |
| Dannis   |   23 |
| Rose     |   27 |
        | NULL |
+----------+------+
10 rows in set (0.00 sec)
/* 在MySql workBench里没有？乱码，为什么命令行会有。*/

/* 两张表的视图*/
mysql> create algorithm=merge view per_pet(name,age,breed,sum) as select name,age,breed,sum from person,p
et where person.age=pet.sum with local check option;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from per_pet;
+----------+------+--------+------+
| name     | age  | breed  | sum  |
+----------+------+--------+------+
| ?Stephen |   19 | ?dog   |   19 |
| Demon    |   19 | ?dog   |   19 |
| Elena    |   18 | cat    |   18 |
| ?Stephen |   19 | tiger  |   19 |
| Demon    |   19 | tiger  |   19 |
| David    |   20 | pig    |   20 |
| jordan   |   25 | beef   |   25 |
| James    |   24 | snake  |   24 |
| Jane     |   23 | monkey |   23 |
| Dannis   |   23 | monkey |   23 |
| Jane     |   23 | fish   |   23 |
| Dannis   |   23 | fish   |   23 |
| Rose     |   27 | cow    |   27 |
+----------+------+--------+------+
13 rows in set (0.00 sec)
/* 又一次刚刚那个问题。
可是在workbench上能正常显示：
# name, age, breed, sum
'Stephen', '19', 'dog', '19'
'Demon', '19', 'dog', '19'
'Elena', '18', 'cat', '18'
'Stephen', '19', 'tiger', '19'
'Demon', '19', 'tiger', '19'
'David', '20', 'pig', '20'
'jordan', '25', 'beef', '25'
'James', '24', 'snake', '24'
'Jane', '23', 'monkey', '23'
'Dannis', '23', 'monkey', '23'
'Jane', '23', 'fish', '23'
'Dannis', '23', 'fish', '23'
'Rose', '27', 'cow', '27'
cmd 窗口是ASCII gbk编码的，但是我set names gbk, utf8, latin1 全是？乱码。。
*/

查看视图：

重要的三种语句：

describe；

show table status like；

show create view

mysql> desc per_pet;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| breed | varchar(10) | YES  |     | NULL    |       |
| sum   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> show table status like 'per_pet'\G;
*************************** 1. row ***************************
           Name: per_pet
         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)

ERROR:
No query specified

/* 和下面的实表相比虚拟表的Engine，Rows等都是空值 */

mysql> show table status like 'person'\G;
*************************** 1. row ***************************
           Name: person
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 10
 Avg_row_length: 1638
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2016-02-05 20:00:11
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show create view per_pet\G;
*************************** 1. row ***************************
                View: per_pet
         Create View: CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `per_pe
t` AS select `person`.`name` AS `name`,`person`.`age` AS `age`,`pet`.`breed` AS `breed`,`pet`.`sum` AS `s
um` from (`person` join `pet`) where (`person`.`age` = `pet`.`sum`) WITH LOCAL CHECK OPTION
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

ERROR:
No query specified

修改视图：

create or replace view 此语句比较灵活，当视图不存在时可创建视图。

alter    如果在创建视图时使用了with check option，with encryption，view_metadata 等选项，如果想要保留这些功能，修改时需要将他们包括进去。

mysql> create or replace algorithm=temptable view per_pet (p1,p2,w1,w2) as select name,age,breed,sum from
 person,pet;
Query OK, 0 rows affected (0.06 sec)

mysql> desc per_pet;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| p1    | varchar(10) | YES  |     | NULL    |       |
| p2    | int(11)     | YES  |     | NULL    |       |
| w1    | varchar(10) | YES  |     | NULL    |       |
| w2    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> alter view per_pet(p1,w1,w2) as select name,breed,sum from person,pet where person.age=pet.sum;
Query OK, 0 rows affected (0.08 sec)

mysql> desc per_pet;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| p1    | varchar(10) | YES  |     | NULL    |       |
| w1    | varchar(10) | YES  |     | NULL    |       |
| w2    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)