MySQLにおけるビューの作成(CREATE VIEW)と使用制限の詳しい説明

MySQLにおけるビューの作成(CREATE VIEW)と使用制限の詳しい説明

この記事では、例を使用して、MySQL ビューの作成 (CREATE VIEW) と使用上の制限について説明します。ご参考までに、詳細は以下の通りです。

MySQL 5.x 以降のバージョンでは、データベース ビューがサポートされています。MySQL では、ビューのほぼすべての機能が SQL: 2003 標準に準拠しています。 MySQL は、ビューに対するクエリを 2 つの方法で処理します。

  • 最初の方法では、MySQL はビュー定義ステートメントに基づいて一時テーブルを作成し、この一時テーブルに対して受信クエリを実行します。
  • 2 番目の方法では、MySQL は受信したクエリとクエリ定義を 1 つのクエリに結合し、結合されたクエリを実行します。

MySQL はビューのバージョン管理システムをサポートしています。ビューが変更または置換されるたびに、ビューのコピーが特定のデータベース フォルダにある arc (アーカイブ) フォルダにバックアップされます。バックアップ ファイルの名前は view_name.frm-00001 です。ビューを再度変更すると、mysql は view_name.frm-00002 という名前の新しいバックアップ ファイルを作成します。 MySQL では、他のビューに基づいてビューを作成できます。つまり、ビュー定義の SELECT ステートメントで、別のビューを参照できます。

まあ、これ以上詳しく説明することはしません。次に、CREATE VIEW ステートメントを使用してビューを作成してみましょう。まずは構文構造を見てみましょう。

作成する 
  [アルゴリズム = {MERGE | TEMPTABLE | 未定義}]
VIEW [データベース名].[ビュー名] 
として
[SELECT文]

それでは、上記の SQL 内のさまざまな単語の意味を詳しく見てみましょう。まず、最初の括弧はビューを作成するためのアルゴリズム属性を表します。これにより、ビューを作成するときに MySQL が使用するメカニズムを制御できます。MySQL では、MERGE、TEMPTABLE、および UNDEFINED の 3 つのアルゴリズムが提供されています。それぞれを個別に見てみましょう。

  • MySQL は MERGE アルゴリズムを使用して、最初に入力クエリとビューを定義する SELECT ステートメントを 1 つのクエリに結合します。 次に、MySQL は結合されたクエリを実行して結果セットを返します。 選択ステートメントに集計関数 (min、max、sum、count、avg など) または distinctive、group by、having、limit、union、union all、subquery が含まれている場合、MERGE アルゴリズムは許可されません。 選択ステートメントがテーブルを参照しない場合、MERGE アルゴリズムは許可されません。 MERGE アルゴリズムが許可されていない場合、MySQL はアルゴリズムを UNDEFINED に変更します。入力クエリとビュー定義内のクエリを 1 つのクエリに結合することをビュー解決と呼びます。
  • TEMPTABLE アルゴリズムを使用すると、MySQL は最初にビューを定義する SELECT ステートメントに基づいて一時テーブルを作成し、次に一時テーブルに対して入力クエリを実行します。 MySQL は結果セットを保存するために一時テーブルを作成し、データを基本テーブルから一時テーブルに移動する必要があるため、TEMPTABLE アルゴリズムは MERGE アルゴリズムよりも効率が低くなります。 さらに、TEMPTABLE アルゴリズムを使用するビューは更新できません。
  • 明示的なアルゴリズムを指定せずにビューを作成する場合、UNDEFINED がデフォルトのアルゴリズムになります。 UNDEFINED アルゴリズムにより、MySQL は MERGE アルゴリズムと TEMPTABLE アルゴリズムのどちらを使用するかを選択できます。 MySQL では、MERGE アルゴリズムの方が効率的であるため、TEMPTABLE アルゴリズムよりも MERGE アルゴリズムが優先されます。

次に、view の後に続くフレーズは名前を意味します。データベースでは、ビューとテーブルは同じ名前空間を共有するため、ビューとテーブルに同じ名前を付けることはできません。 さらに、ビューの名前はテーブルの命名規則に従う必要があります。

最後のステートメントは SELECT ステートメントです。 SELECT ステートメントでは、データベース内の任意のテーブルまたはビューからデータをクエリできます。同時に、SELECT ステートメントは次のルールに従う必要があります。

  • SELECT ステートメントでは、where 句にサブクエリを含めることができますが、FROM 句にサブクエリを含めることはできません。
  • SELECT ステートメントは、ローカル変数、ユーザー変数、セッション変数などの変数を参照できません。
  • SELECT ステートメントは準備されたステートメントのパラメータを参照できません。

ここで注意すべき点は、SELECT ステートメントではテーブルを参照する必要がないことです。最後に、orderDetails テーブルに基づいて各注文の合計売上を表すビューを作成してみます。

VIEW SalePerOrder AS を作成する
  選択 
    注文番号、SUM(注文数量 * 各価格) 合計
  から
    注文詳細
  注文番号によるグループ化
  ORDER BY total DESC;

SHOW TABLES コマンドを使用してサンプル データベース (yiibaidb) 内のすべてのテーブルを表示すると、テーブルのリストに SalesPerOrder ビューも表示されることがわかります。

mysql> テーブルを表示;
+--------------------+
| yiibaidb 内のテーブル |
+--------------------+
| 記事タグ |
| 連絡先 |
| 顧客 |
| 部門 |
|従業員|
| オフィス |
|オフィス_bk|
| 米国オフィス |
|注文詳細|
| 注文 |
| お支払い |
| 製品ライン |
| 製品 |
|販売注文|
+--------------------+
14行セット

これは、ビューとテーブルが同じ名前空間を共有するためです。どのオブジェクトがビューかテーブルかを確認するには、次のように SHOW FULL TABLES コマンドを使用します。

mysql> テーブル全体を表示します。
+--------------------+-------------+
| yiibaidb 内のテーブル | テーブルタイプ |
+--------------------+-------------+
| article_tags | ベーステーブル |
| 連絡先 | ベーステーブル |
| 顧客 | ベース テーブル |
| 部門 | ベース テーブル |
| 従業員 | ベース テーブル |
| オフィス | ベーステーブル |
| offices_bk | ベーステーブル |
| offices_usa | ベース テーブル |
| 注文詳細 | 基本テーブル |
| 注文 | ベース テーブル |
| 支払い | ベース テーブル |
| 製品ライン | ベーステーブル |
| 製品 | ベーステーブル |
| セールパーオーダー | 表示 |
+--------------------+-------------+
14行セット

結果セットの table_type 列は、どのオブジェクトがビューで、どのオブジェクトがテーブル (基本テーブル) であるかを指定します。上記のように、saleperorder に対応する table_type 列の値は VIEW です。ただし、各販売注文の合計売上額を照会する場合は、次に示すように、SalePerOrder ビューで単純な SELECT ステートメントを実行するだけで済みます。

選択 
  *
から
  注文ごとの販売;

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

+-------------+-----------+
| 注文数 | 合計 |
+-------------+-----------+
| 10165 | 67392.85 |
| 10287 | 61402.00 |
| 10310 | 61234.67 |
| 10212 | 59830.55 |
|-- ここでは多くのデータが省略されています-- |
| 10116 | 1627.56 |
| 10158 | 1491.38 |
| 10144 | 1128.20 |
| 10408 | 615.45 |
+-------------+-----------+
セット内の行数は 327 行です

別のビューに基づいてビューを作成しましょう。たとえば、次に示すように、SalesPerOrder ビューに基づいて BigSalesOrder というビューを作成し、合計が 60,000 を超える各販売注文を表示できます。

VIEW BigSalesOrder AS を作成する
  選択 
    orderNumber、ROUND(total,2) を合計として計算
  から
    注文あたりの販売数
  どこ
    合計 > 60000;

これで、次のように BigSalesOrder ビューからデータをクエリできます。

選択 
  注文数、合計
から
  ビッグセールスオーダー;

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

+-------------+-----------+
| 注文数 | 合計 |
+-------------+-----------+
| 10165 | 67392.85 |
| 10287 | 61402.00 |
| 10310 | 61234.67 |
+-------------+-----------+
3行セット

次に、内部結合を使用して、顧客番号と顧客が支払った合計金額を含むビューを次のように作成します。

CREATE VIEW customerOrders AS
  選択 
    c.顧客番号、
    金額
  から
    顧客
      内部結合
    支払い p ON p.customerNumber = c.customerNumber
  GROUP BY c.顧客番号
  ORDER BY p.amount DESC;

次の SQL を使用して、customerOrders ビューのデータをクエリします。

+----------------+-----------+
| 顧客番号 | 金額 |
+----------------+-----------+
| 124 | 101244.59 |
| 321 | 85559.12 |
| 239 | 80375.24 |
| **** ここでは多くのデータが省略されています ***|
| 219 | 3452.75 |
| 216 | 3101.4 |
| 161 | 2434.25 |
| 172 | 1960.8 |
+----------------+-----------+
セット内の行数は 98 行

ここで、次のようにサブクエリを使用して、すべての製品の平均価格よりも高い価格の製品を含むビューを作成してみます。

上記のAvgProducts ASのビューを作成
  選択 
    製品コード、製品名、購入価格
  から
    製品
  どこ
    購入価格 > 
 (選択 
        AVG(購入価格)
      から
        製品)
  ORDER BY buyPrice DESC;

上記のAvgProductsビューのデータをクエリしてみましょう。

選択 
  *
から
  上記の平均製品;

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

+-------------+------------------------------------------+----------+
| 製品コード | 製品名 | 購入価格 |
+-------------+------------------------------------------+----------+
| S10_4962 | 1962 ランチアA デルタ 16V | 103.42 |
| S18_2238 | 1998 クライスラー プリマス プラウラー | 101.51 |
| S10_1949 | 1952 アルピーヌ ルノー 1300 | 98.58 |
|**************** ここでは多くのデータが省略されています************************************|
| S18_3320 | 1917 マクスウェル ツーリングカー | 57.54 |
| S24_4258 | 1936 クライスラー エアフロー | 57.46 |
| S18_3233 | 1985 トヨタ スープラ | 57.01 |
| S18_2870 | 1999 インディ 500 モンテカルロ SS | 56.76 |
| S32_4485 | 1974 ドゥカティ 350 Mk3 デスモ | 56.13 |
| S12_4473 | 1957 シボレー ピックアップ | 55.7 |
| S700_3167 | F/A 18 ホーネット 1/72 | 54.4 |
+-------------+------------------------------------------+----------+
セット内の行数は54行

さて、ここではビューの作成と使用についてほぼ説明しました。しかし、ビューの使用に制限はないのでしょうか?答えはもちろん「はい」です。一つずつ見ていきましょう。

まず、ビューにインデックスを作成することはできません。次に、マージ アルゴリズムを使用するビューを使用してデータをクエリする場合、MySQL は基になるテーブルのインデックスを使用します。また、テンプテーション アルゴリズムを使用するビューの場合、ビューに対してデータをクエリすると、インデックスは使用されません。

また、MySQL 5.7.7 より前のバージョンでは、SELECT ステートメントの FROM 句でサブクエリを使用してビューを定義することはできないことに注意してください。

ビューの基になるテーブルを削除したり名前を変更したりしても、MySQL はエラーを発行しません。ただし、mysql はビューを無効にするため、CHECK TABLE ステートメントを使用してビューが有効かどうかを確認できます。

単純なビューではテーブル内のデータを更新できますが、結合やサブクエリなどを含む複雑な選択ステートメントに基づいて作成されたビューは更新できません。

MySQL は、Oracle や PostgreSQL などの他のデータベース システムのような物理ビューをサポートしていません。MySQL は物理ビューをサポートしていません。

さて、今回はビューについて言うことはこれだけです。

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

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

以下もご興味があるかもしれません:
  • MySQLでビューを作成する方法
  • MySQL ビューの原理と使用法の詳細な分析
  • MySQLのビューとインデックスの使い方と違いの詳細な説明
  • MySql ビュー、トリガー、ストアド プロシージャに関する簡単な説明
  • MySql ビュー トリガー ストアド プロシージャの詳細な説明
  • MySQLビューの原理と使用法の詳細な説明
  • MySQL ビュー管理ビューの例の詳細説明 [追加、削除、変更、クエリ操作]
  • MySQLで更新可能なビューを作成する方法の詳細な説明
  • MySQL における単一テーブルと複数テーブル、およびビューと一時テーブルに対する Update と Select の違い
  • mysql 3つのテーブルを接続してビューを作成する
  • MySQL ビューの原則分析

<<:  nginx-ingress-controller ログ永続化ソリューションのソリューション

>>:  Vue 日付時刻ピッカーコンポーネントの使い方の詳細な説明

推薦する

Vue のライフサイクルとフック関数の詳細な説明と典型的な面接の質問

目次1. Vue ライフサイクル2. フック機能2.1 4つの段階と8つの方法に分かれています。 2...

Vue の動的メニュー、動的ルートの読み込みと更新の落とし穴

目次必要:アイデア:レッスン:テキストを共有する:要約する必要:インターフェイスからサブメニュー デ...

CSS を使用して、画像に 3D の凸型と凹型のエフェクト (フレーム外に凸型、またはフレーム内に凹型) を実現します。

Ⅰ. 問題の説明: CSS を使用して画像の 3D 凸凹効果を実現します。 Ⅱ実施手順は以下のとお...

MySQL で重複を削除するには、distinct または group by を使用する必要がありますか?

序文group by と distinctive のパフォーマンス比較について: インターネット上の...

MySQLの左結合と内部結合について簡単に説明します

序文最近、X 省のコールド チェーン トレーサビリティ システムの開発で忙しくしています。毎日午後 ...

VueはTeleportをベースにModalコンポーネントを実装します

目次1. テレポートについて知る2. テレポートの基本的な使い方3. 最初のステップの最適化4. 第...

怖いハロウィーン Linux コマンド

ハロウィーンではありませんが、Linux の不気味な側面に注目する価値はあります。幽霊、魔女、ゾンビ...

Docker Swarmを使用してWordPressを構築する方法

原因かつて私は Vultr に WordPress を設定しましたが、よく知られている理由により、こ...

Windows 10 で Ubuntu 20.04 LTS をアップデートする方法

2020 年 4 月 23 日、本日、Windows 上の Ubuntu 20.04 では、Ubun...

Reactプロジェクトの新規作成からデプロイまでの実装例

新しいプロジェクトを始めるこの記事では主に、新規プロジェクトを0から1まで取り組むプロセスを記録し、...

Linuxダイナミックリンクライブラリの使用

通常のプログラムと比較すると、ダイナミック リンク ライブラリにはメイン関数がなく、一連の関数の実装...

And キーワードを使用した MySQL の複数条件クエリ ステートメント

AND キーワードを使用した MySQL 複数条件クエリ。MySQL では、AND キーワードを使用...

入力スクリプトなしでタイプ拡張を使用する方法

序文JS の型付けが弱く、記述基準が緩く、開発ツールのサポートが弱いため、前任者のコードをメンテナン...

MySQL における tinyint と int の違いの詳細な説明

質問: int(1) と tinyint(1) の違いは何ですか?このような設計では、いずれにしても...

Eclipse は Tomcat を構成しますが、Tomcat には無効なポート解決策があります

目次1. EclipseがTomcatを構成する2. Tomcat の無効なポートの解決方法方法1:...