MySQLビューの作成、表示、変更

ビューは、データベース内の1つ以上のテーブルから導出された仮想テーブルであり、ユーザーのデータ操作を容易にします.
データベースにはデータの定義しかなく、ビューのデータが格納されていません.それらのデータは元のテーブルにあります.
ビューには、データセキュリティ、データセキュリティ、データテーブルはアプリケーションとは独立しています.ユーザーの操作を簡略化します.
前提:ユーザーに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 '
';

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   ？  。。
*/

表示:
重要な3つの文:
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)