Grundlegende Operationen von MySQL-Ansichten (5)

1. Warum Ansichten verwenden:
Um die Wiederverwendbarkeit komplexer SQL-Anweisungen und die Sicherheit von Tabellenoperationen zu verbessern (z. B. möchte das Gehaltsfeld nicht jedem angezeigt werden, der die Abfrageergebnisse sehen kann), stellt MySQL Ansichtsfunktionen bereit. Die sogenannte Ansicht ist im Wesentlichen eine virtuelle Tabelle, deren Inhalt einer realen Tabelle ähnelt und eine Reihe benannter Spalten- und Zeilendaten enthält. Ansichten sind jedoch nicht als gespeicherte Datenwerte in der Datenbank vorhanden. Die Zeilen- und Spaltendaten stammen aus der Basistabelle, auf die die Abfrage der benutzerdefinierten Ansicht verweist, und werden bei Verwendung der Ansicht dynamisch generiert.
Ansichten haben die folgenden Eigenschaften:
1. Die Spalten der Ansicht können aus verschiedenen Tabellen stammen. Dies stellt eine neue Beziehung dar, die im abstrakten und logischen Sinne der Tabelle hergestellt wird.
2. Eine Ansicht ist eine Tabelle (virtuelle Tabelle), die aus einer Basistabelle (realen Tabelle) generiert wird.
3. Das Erstellen und Löschen von Ansichten hat keine Auswirkungen auf die Basistabellen.
4. Aktualisierungen zum Anzeigen von Inhalten (Hinzufügen, Löschen und Ändern) wirken sich direkt auf die Basistabelle aus.
5. Wenn die Ansicht aus mehreren Basistabellen stammt, ist das Hinzufügen und Löschen von Daten nicht zulässig.

2. Erstellen Sie eine Ansicht:
Beim Erstellen einer Ansicht müssen Sie zunächst sicherstellen, dass Sie über die CREATE VIEW-Berechtigungen verfügen, und stellen Sie außerdem sicher, dass Sie über die entsprechenden Berechtigungen für die Tabelle verfügen, auf die die erstellte Ansicht verweist.
2.1 Die Syntaxform zum Erstellen einer Ansicht:
Obwohl eine Ansicht als virtuelle Tabelle betrachtet werden kann, existiert sie nicht physisch, das heißt, das Datenbankverwaltungssystem verfügt nicht über einen speziellen Ort zum Speichern von Daten für die Ansicht. Gemäß dem Ansichtskonzept wird festgestellt, dass die Daten aus der Abfrageanweisung stammen. Die Syntax zum Erstellen einer Ansicht lautet daher:

CREATE VIEW view_name AS-Abfrageanweisung
//Hinweis: Genau wie beim Erstellen einer Tabelle darf der Ansichtsname nicht mit dem Tabellennamen oder anderen Ansichtsnamen identisch sein. Die Funktion der Ansicht besteht eigentlich darin, komplexe Abfrageanweisungen zu kapseln.

Beispiel:

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;

Nach dem Login kopieren

Das Ergebnis ist:
------ --------
|. id |. name |
------ --------
|. 1 |. Apfel |
|. 2 |. orange |
------ --------
//Tatsächlich wird beim Erstellen der Ansicht tatsächlich eine Tabellenabfrageanweisung in den Code geschrieben, aber die Abfrageanweisung wird gekapselt und mit einem neuen Namen versehen, damit sie wiederverwendet werden kann.
//Außerdem können Sie aus Sicherheitsgründen einige Felder ausblenden, die Sie nicht sehen möchten, wie zum Beispiel das Preisfeld hier.
//Hinweis: In der Namenskonvention von SQL-Anweisungen werden Ansichten im Allgemeinen im Stil von view_xxx oder v_xxx benannt. Die Abfrageanweisung der Ansicht ist dieselbe wie die Abfrageanweisung der Tabelle.

2.2 Verschiedene Ansichten erstellen:
Kann jede Form einer Abfrageanweisung in der Ansicht gekapselt werden, da die Funktion der Ansicht die Abfrageanweisung tatsächlich in Echtzeit kapselt?

2.2.1 Kapseln Sie die Ansicht, die die Abfragekonstantenanweisung implementiert (konstante Ansicht):

Beispiel:

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)

Nach dem Login kopieren

2.2.2 Ansichten, die Abfrageanweisungen mithilfe von Aggregatfunktionen (SUMME, MIN, MAX, COUNT usw.) kapseln:

Beispiel:
Bereiten Sie zunächst die beiden Tabellen und ihre Initialisierungsdaten vor, die verwendet werden müssen;

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)

Nach dem Login kopieren

2.2.3 Kapselt die Ansicht, die die Abfrageanweisung der Sortierfunktion (ORDER BY) implementiert:

Beispiel:

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)

Nach dem Login kopieren

2.2.4 Kapselt die Ansicht, die die Join-Abfrageanweisung implementiert, in der Tabelle:

Beispiel: (Name der zweiten Studierendengruppe)

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)

Nach dem Login kopieren

2.2.5 Kapselt die Ansicht, die Abfrageanweisungen für Out-of-Table-Joins (LEFT JOIN und RIGHT JOIN) implementiert:

Beispiel: (Zweite Gruppe von Studentennamen)

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)

Nach dem Login kopieren

2.2.6 kapselt die Ansicht, die unterabfragebezogene Abfrageanweisungen implementiert:

Beispiel:

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)

Nach dem Login kopieren

2.2.7 Kapselt die Ansicht, die Datensatz-Union-Abfrageanweisungen (UNION und UNION ALL) implementiert:

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)

Nach dem Login kopieren

3. Ansicht anzeigen:
3.1 SHOW TABLES-Anweisung zum Anzeigen des Ansichtsnamens:
Beim Ausführen der SHOW TABLES-Anweisung kann nicht nur der Name der Tabelle, sondern auch der Name der Ansicht angezeigt werden.

Beispiel:

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)

Nach dem Login kopieren

3.2 SHOW TABLE STATUS-Anweisung zum Anzeigen von Ansichtsdetails:

Wie die SHOW TABLES-Anweisung zeigt die SHOW TABLE STATUS-Anweisung nicht nur detaillierte Informationen zur Tabelle, sondern auch detaillierte Informationen zur Ansicht an.

Die Syntax lautet wie folgt:
TABELLENSTATUS ANZEIGEN [FROM db_name] [LIKE 'pattern']
//Der Parameter db_name wird zum Festlegen des Datenbankstatus verwendet. SHOW TABLES STATUS bedeutet, dass die detaillierten Informationen der Tabellen und Ansichten der eingestellten Bibliothek angezeigt werden.
//Legen Sie das Schlüsselwort LIKE fest, um detaillierte Informationen zu einer bestimmten Tabelle oder Ansicht anzuzeigen. Zum Beispiel: SHOW TABLE STATUS FROM zhaojd LIKE 't_group' G
Beispiel:

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

Nach dem Login kopieren

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字段。

Nach dem Login kopieren

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)

Nach dem Login kopieren

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)

Nach dem Login kopieren

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)

Nach dem Login kopieren

5.修改视图：
5.1 CREATE OR REPLACE VIEW语句修改视图：
对于已经创建好的表，尤其是已经有大量数据的表，通过先删除，然后再按照新的表定义重新建表的方式来修改表，需要做很多额外的工作，例如数据的重载等。可是对于视图来说，由于是“虚表”，并没有存储数据，所以完全可以通过该方式来修改视图。

实现思路就是：先删除同名的视图，然后再根据新的需求创建新的视图即可。

DROP VIEW view_name; 
CREATE VIEW view_name as 查询语句;

Nach dem Login kopieren

但是如果每次修改视图，都是先删除视图，然后再次创建一个同名的视图，则显得非常麻烦。于是MySQL提供了更方便的实现替换的创建视图的语法，完整语法为：

CREATE OR REPLACE VIEW view_name as 查询语句；

5.2 ALTER语句修改视图：

语法为：

ALTER VIEW view_name as 查询语句;

6.利用视图操作基本表：
再MySQL中可以通常视图检索基本表数据，这是视图最基本的应用，除此之后还可以通过视图修改基本表中的数据。
6.1检索（查询）数据：
    通过视图查询数据，与通过表进行查询完全相同，只不过通过视图查询表更安全，更简单实用。只需要把表名换成视图名即可。
6.2利用视图操作基本表数据：
    由于视图是“虚表”，所以对视图数据进行的更新操作，实际上是对其基本表数据进行的更新操作。在具体更新视图数据时，需要注意以下两点;
    1. 对视图数据进行添加、删除直接影响基本表。
    2. 视图来自于多个基本表时，不允许添加、删除数据。
    视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。

Grundlegende Operationen von MySQL-Ansichten (5)_MySQL