MySQL(五)ビューの操作

24078 ワード
mysql
1.ビューの使用理由:複雑なSQL文の多重化とテーブル操作のセキュリティを向上させるため(たとえば、「給与」フィールドはクエリーの結果を表示できるすべての人には表示されたくない)、MySQLはビュー機能を提供します.ビューとは本質的に仮想テーブルで、名前付きの列と行のデータを含む一連の内容が実際のテーブルと似ています.ただし、ビューはデータベースにデータ値を格納する形で存在しません.行と列のデータはビューのデータをカスタマイズします参照される基本テーブルをクエリーし、ビューを具体的に使用するときに動的に生成します.ビューには次のような特徴があります.      1. ビューのカラムは、テーブルの抽象的および論理的な意味で確立された新しい関係である異なるテーブルから使用できます.      2. ビューは、基本テーブル(実テーブル)によって生成されるテーブル(ダミーテーブル)です.3.ビューの作成と削除は基本テーブルに影響しません.4.ビューの内容の更新(追加、削除、変更)基本表に直接影響します.5.ビューが複数の基本表から来た場合、データの追加と削除は許可されません.2.ビューの作成:ビューの作成時に、まずCREATE VIEWの権限を確保し、同時にビューの作成に参照されたテーブルにも適切な権限があることを確認します.2.1ビューの構文の作成:ビューは仮想テーブルと見なすことができるが、データベース管理システムにはビューにデータを格納する専用の場所がないという物理的に存在しない.ビューの概念に基づいて、クエリー文に由来するデータが検出されたため、ビューを作成する構文は次のとおりです.
CREATE VIEW view_name AS     
//  ：      ，        、           。                   。
  ：
use zhaojd_test;    //          
create table t_product(  //   
    id int primary key,
    pname varchar(20),
    price decimal(8,2)
);  
insert into t_product values(1,'apple',6.5); //       
insert into t_product values(2,'orange',3);  //       
create view view_product as select id,name from t_product; //    
select * from view_product;
   ：
+------+--------+
| id   | name   |
+------+--------+
| 1    | apple  |
| 2    | orange |
+------+--------+
//                       ，                      ，        。
//  ，                   ，         。
//  ： SQL        ，     view_xxx  v_xxx      。                。

2.2様々なビューを作成する:ビューの機能は実際にはクエリー文をカプセル化するため、どの形式のクエリー文もビューにカプセル化できるのではないでしょうか.    2.2.1クエリ定数文を実装するビュー(定数ビュー)をカプセル化します.
  ：
mysql> create view view_test1 as select 3.1415926;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from view_test1;
+-----------+
| 3.1415926 |
+-----------+
| 3.1415926 |
+-----------+
1 row in set (0.00 sec)

    2.2.2集約関数(SUM、MIN、MAX、COUNTなど)を使用して文をクエリーするビューをカプセル化します.
  ：
                   ;
CREATE TABLE t_group(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
CREATE TABLE t_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    sex CHAR(1),
    group_id INT,
    FOREIGN KEY (group_id) REFERENCES t_group (id)
);
//t_group      
INSERT INTO t_group (NAME) VALUES('group_1');
INSERT INTO t_group (NAME) VALUES('group_2');
INSERT INTO t_group (NAME) VALUES('group_3');
INSERT INTO t_group (NAME) VALUES('group_4');
INSERT INTO t_group (NAME) VALUES('group_5');
//t_student      
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_1','M',1);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_2','M',1);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_3','M',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_4','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_5','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_6','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_7','M',3);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_8','W',4);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_9','W',4);
================================================================
mysql> create view view_test2 as select count(name) from t_student;
Query OK, 0 rows affected (0.71 sec)
mysql> select * from view_test2;
+-------------+
| count(name) |
+-------------+
|           9 |
+-------------+
1 row in set (0.01 sec)

    2.2.3ソート機能(ORDER BY)クエリー文を実装するビューをカプセル化します.
  ：
mysql> create view view_test3 as select name from t_student order by id desc;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from view_test3;
+-------+
| name  |
+-------+
| zjd_9 |
| zjd_8 |
| zjd_7 |
| zjd_6 |
| zjd_5 |
| zjd_4 |
| zjd_3 |
| zjd_2 |
| zjd_1 |
+-------+
9 rows in set (0.00 sec)

    2.2.4テーブル内の接続クエリ文を実装するビューをカプセル化します.
  ：（        ）
mysql> create view view_test5 as select s.name from t_student s,t_group g where s.group_id=g.id and g.id=2;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from view_test5;
+-------+
| name  |
+-------+
| zjd_3 |
| zjd_4 |
| zjd_5 |
| zjd_6 |
+-------+
4 rows in set (0.00 sec)

    2.2.5表外接続(LEFTJOINとRIGHT JOIN)を実現するクエリ文をカプセル化した図:
  ：（       ）
mysql> create view view_test6 as select s.name from t_student s left join t_group g on s.group_id=g.id where g.id=2;
Query OK, 0 rows affected (0.09 sec)
mysql> select * from view_test6;
+-------+
| name  |
+-------+
| zjd_3 |
| zjd_4 |
| zjd_5 |
| zjd_6 |
+-------+
4 rows in set (0.01 sec)

    2.2.6サブクエリ関連クエリ文を実装するビューをカプセル化します.
  ：
mysql> create view view_test7 as select s.name from t_student s where s.id in(select id from t_group);
Query OK, 0 rows affected (0.08 sec)
mysql> select * from view_test7;
+-------+
| name  |
+-------+
| zjd_1 |
| zjd_2 |
| zjd_3 |
| zjd_4 |
| zjd_5 |
+-------+
5 rows in set (0.00 sec)

    2.2.7レコード連携(UNIONとUNIONALL)クエリ文を実装するビューをカプセル化する:
mysql> create view view_test8 as select id,name from t_student union all select id,name from t_group;
Query OK, 0 rows affected (0.08 sec)
mysql> select * from view_test8;
+----+---------+
| id | name    |
+----+---------+
|  1 | zjd_1   |
|  2 | zjd_2   |
|  3 | zjd_3   |
|  4 | zjd_4   |
|  5 | zjd_5   |
|  6 | zjd_6   |
|  7 | zjd_7   |
|  8 | zjd_8   |
|  9 | zjd_9   |
|  1 | group_1 |
|  2 | group_2 |
|  3 | group_3 |
|  4 | group_4 |
|  5 | group_5 |
+----+---------+
14 rows in set (0.01 sec)

3.ビュー表示:3.1 SHOW TABLES文ビュー名:SHOW TABLES文を実行すると、表の名前だけでなく、ビューの名前も表示されます.
  ：
mysql> show tables;
+------------------+
| Tables_in_zhaojd |
+------------------+
| t_group          |
| t_product        |
| t_student        |
| v_product        |
| view_test1       |
| view_test2       |
| view_test3       |
| view_test4       |
| view_test5       |
| view_test6       |
| view_test8       |
+------------------+
11 rows in set (0.00 sec)

3.2 SHOW TABLE STATUS文ビュー詳細:SHOW TABLES文と同様に、SHOW TABLE STATUS文は表の詳細だけでなく、ビューの詳細も表示されます.
    ：
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
//  db_name       ，SHOW TABLES STATUS                   。
//  LIKE   ，                  。  ： SHOW TABLE STATUS FROM zhaojd LIKE 't_group' \G
  ：
mysql> show table status from zhaojd \G
*************************** 1. row ***************************
           Name: t_group
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 7340032
 Auto_increment: 6
    Create_time: 2016-08-19 16:26:06
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
============================================================= 
           Name: view_test8
         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

3.3 SHOW CREATE VIEW文ビュー定義情報を表示する:
   ：
 SHOW CREATE VIEW viewname;
   ：
 mysql> show create view view_test8 \G
*************************** 1. row ***************************
View: view_test8
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test8` AS select `t_student`.`id` AS `id`,
`t_student`.`NAME` AS `name` from `t_student` union all select `t_group`.`id` AS `id`,`t_group`.`NAME` AS `name` from `t_group`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
//          ，SHOW CREATE VIEW        ，         View          Create view  。

3.4 DESCRIBE|DESC文ビュー定義情報を表示する:
   ：
    DESCRIBE | DESC viewname;
  ：
mysql> desc view_test8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

3.5システム表によるビュー情報の表示:MySQLのインストールが成功すると、システムデータベースinfomation_が自動的に作成される.schema.このデータベースには、ビュー情報を含むテーブルがあり、テーブルviewsを表示することで、すべてのビューに関する情報を表示できます.
  ：
mysql> use information_schema;
Database changed
mysql> select * from views where table_name='view_test8' \G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: zhaojd
          TABLE_NAME: view_test8
     VIEW_DEFINITION: select `zhaojd`.`t_student`.`id`AS`id`,`zhaojd`.`t_student`.`NAME` AS `name` from `zhaojd`.`t_student` union all select `zhaojd`.`t_group`.`id` AS`id`,`zhaojd`.`t_group`.`NAME` AS `name` from `zhaojd`.`t_group`
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.01 sec)

4.ビューの削除:ビューを削除するときは、まずビューを削除する権限があることを確認します.
   ：
DROP VIEW view_name [,view_name] ......
//       ，DROP VIEW          
  ：
mysql> use zhaojd;
Database changed
mysql> show tables;
+------------------+
| Tables_in_zhaojd |
+------------------+
| t_group          |
| t_product        |
| t_student        |
| v_product        |
| view_test1       |
| view_test2       |
| view_test3       |
| view_test4       |
| view_test5       |
| view_test6       |
| view_test8       |
+------------------+
11 rows in set (0.00 sec)
mysql> drop view view_test1, view_test2;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_zhaojd |
+------------------+
| t_group          |
| t_product        |
| t_student        |
| v_product        |
| view_test3       |
| view_test4       |
| view_test5       |
| view_test6       |
| view_test8       |
+------------------+
9 rows in set (0.00 sec)

5.修正ビュー:5.1 CREATE OR REPLACE VIEW文修正ビュー:作成したテーブル、特に大量のデータがあるテーブルについては、先に削除してから、新しいテーブル定義に従ってテーブルを再構築することによってテーブルを修正し、データのリロードなど、多くの追加作業を行う必要がある.しかし、ビューでは「虚表」であり、データが格納されていないため、このようにビューを変更することは完全に可能である.
      ：        ，                 。
DROP VIEW view_name; 
CREATE VIEW view_name as     ;

しかし、ビューを変更するたびに、ビューを削除してから同じ名前のビューを再作成するのは面倒です.そこでMySQLは、代替ビューの作成を容易にする構文を提供します.完全な構文は次のとおりです.
CREATE OR REPLACE VIEW view_name as     ；

5.2 ALTER文修正ビュー:
   ：
ALTER VIEW view_name as     ;

6.ビューを使用して基本テーブルを操作する:MySQLでは通常ビューで基本テーブルデータを取得することができ、これはビューの最も基本的な応用であり、それ以外にビューで基本テーブルのデータを修正することもできる.6.1データの検索(クエリー):ビューを通じてデータを検索する.テーブルを通じてクエリーを行うのと全く同じである.ただ、ビューを通じてテーブルを検索するのはもっと安全で、もっと簡単で実用的である.テーブル名をビュー名に変えるだけでよい.6.2ビューを利用して基本テーブルデータを操作する:ビューが「虚表」であるためのため、ビューデータの更新操作は、実際にはその基本テーブルデータの更新操作である.ビューデータを具体的に更新する際には、以下の2点に注意する必要があります.    1. ビューデータの追加、削除、変更は、基本テーブルに直接影響します.    2. ビューが複数の基本テーブルから来た場合、データの追加、削除は許可されません.ビュー内のデータの追加操作、データの削除操作、データの更新操作の構文は、テーブルとまったく同じです.テーブル名をビュー名に変更するだけです.
electronメモ
Electronで設定ファイルを導入