MySQLで更新可能なビューを作成する方法の詳細な説明

この記事では、例を使用して、MySQL で更新可能なビューを作成する方法について説明します。ご参考までに、詳細は以下の通りです。

MySQL では、ビューはクエリ可能であるだけでなく、更新可能であることもわかっています。つまり、INSERT または UPDATE ステートメントを使用して、更新可能なビューを通じて基本テーブルの行を挿入または更新できます。さらに、delete ステートメントを使用して、ビューを通じて基になるテーブルの行を削除することもできます。ただし、更新可能なビューを作成するには、ビューを定義する SELECT ステートメントに次の要素が含まれていてはなりません。

min、max、sum、avg、count などの集計関数。
DISTINCT 句
GROUP BY 句
HAVING 節
左結合または外部結合。
UNION または UNION ALL 句
テーブルを参照する SELECT 句内のサブクエリまたは where 句内のサブクエリが FROM 句に表示されます。
FROM句で更新不可能なビューを参照する
リテラル値のみを引用符で囲む
ベーステーブルの任意の列への複数の参照

temptable アルゴリズムを使用してビューを作成する場合、ビューを更新することはできませんが、内部結合を使用して複数のテーブルに基づいて更新可能なビューを作成できる場合があります。では、早速、更新可能なビューを作成する方法を見てみましょう。まず、offices テーブルに基づいて officeInfo というビューを作成してみましょう。このビューは、offices テーブルの 3 つの列 (officeCode、phone、city) を参照します。

ビューの作成 officeInfo
 として
  オフィスコード、電話番号、都市を選択
  オフィスから;

次に、次のステートメントを使用して、officeInfo ビューからデータをクエリします。

選択
  *
から
  オフィス情報;

上記のクエリステートメントを実行すると、次の結果が得られます。

mysql> officeInfo から * を選択;
+------------+------------------+----------------+
| オフィスコード | 電話番号 | 都市名 |
+------------+------------------+----------------+
| 1 | +1 650 219 4782 | サンフランシスコ |
| 2 | +1 215 837 0825 | ボストン |
| 3 | +1 212 555 3000 | ニューヨーク |
| 4 | +33 14 723 4404 | パリ |
| 5 | +86 33 224 5000 | 北京 |
| 6 | +61 2 9264 2451 | シドニー |
| 7 | +44 20 7877 2041 | ロンドン |
+------------+------------------+----------------+
7行セット

次に、officeInfo ビューを通じて次の更新ステートメントを使用して、officeCode の値をオフィスの電話番号 4 に変更します。

オフィス情報を更新
セット
  電話番号 = '+86 089866668888'
どこ
  オフィスコード = 4;

最後に、officeInfo ビューでデータをクエリして変更を確認します。

mysql> 選択
  *
から
  オフィス情報
どこ
  オフィスコード = 4;

+------------+------------------+--------+
| オフィスコード | 電話番号 | 都市名 |
+------------+------------------+--------+
| 4 | +86 089866668888 | パリ |
+------------+------------------+--------+
セット内の1行

information_schema データベースの views テーブルから is_updatable 列をクエリすることで、データベース内のビューが更新可能かどうかを確認できます。たとえば、luyaran データベースをクエリしてすべてのビューを取得し、どのビューが更新可能かを表示します。

選択
  テーブル名、更新可能
から
  情報スキーマビュー
どこ
  table_schema = 'luyaran';

上記のクエリステートメントを実行すると、次の結果が得られます。

+------------------+--------------+
| テーブル名 | 更新可能 |
+------------------+--------------+
| 上記の平均製品 | はい |
| bigsalesorder | はい |
| 顧客注文 | いいえ |
| オフィス情報 | はい |
| 注文ごとの販売 | いいえ |
+------------------+--------------+
5行セット

ビューを通じて行を削除してみましょう。まず、items というテーブルを作成し、items テーブルにいくつかの行を挿入して、価格が 700 を超えるアイテムを照会するビューを作成します。

testdbを使用します。
-- items という名前の新しいテーブルを作成します
CREATE TABLE アイテム (
  id INT AUTO_INCREMENT 主キー、
  名前 VARCHAR(100) NOT NULL,
  価格 DECIMAL(11, 2) NOT NULL
);
-- アイテムテーブルにデータを挿入する
アイテムに挿入(名前、価格)
VALUES('ラップトップ',700.56),('デスクトップ',699.99),('iPad',700.50);
-- アイテムテーブルに基づいてビューを作成する
CREATE VIEW LuxuryItems AS
  選択
    *
  から
    アイテム
  どこ
    価格 > 700;
-- LuxuryItems ビューからデータをクエリする
選択
  *
から
  高級品;

上記のクエリステートメントを実行すると、次の結果が得られます。

+----+--------+--------+
| ID | 名前 | 価格 |
+----+--------+--------+
| 1 | ラップトップ | 700.56 |
| 3 | iPad | 700.5 |
+----+--------+--------+
2行セット

その後、DELETE ステートメントを使用して、ID 3 の行を削除します。

LuxuryItemsから削除
どこ
  id = 3;

mysql は、1 行が影響を受けたことを示すメッセージを返します。

クエリは正常、1 行が影響を受けました

ビューを通じてデータをもう一度確認してみましょう。

mysql> LuxuryItems から * を選択します。
+----+--------+--------+
| ID | 名前 | 価格 |
+----+--------+--------+
| 1 | ラップトップ | 700.56 |
+----+--------+--------+
セット内の1行

また、ベーステーブル items からデータをクエリして、DELETE ステートメントによって実際に行が削除されたことを確認することもできます。

mysql> SELECT * FROM items;
+----+---------+--------+
| ID | 名前 | 価格 |
+----+---------+--------+
| 1 | ラップトップ | 700.56 |
| 2 | デスクトップ | 699.99 |
+----+---------+--------+
2行セット

ベーステーブルで ID 3 の行が削除されていることがわかります。

さて、この記録についてはこれですべてです。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL トランザクション操作スキル」、「MySQL ストアドプロシージャスキル」、「MySQL データベースロック関連スキルの概要」、および「MySQL 共通関数の概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: