SQLは必ず知っています.

8413 ワード

『SQLは必ず知っています.』読書ノート
この授業では、他の連結(それらの意味と使い方を含む)について説明します.
1.表の別名を使う
SQLは、列名と計算フィールドにエイリアスを使用するほか、表名にエイリアスを付けることができます.このようにするには二つの主な理由があります.
  • SQL文を短縮する.
  • は、同じ表を一つのSELECT文で複数回使用できるようにする.
  • 下のSELECT文を見てください.前の課の例で使った語句とほぼ同じですが、別名を使うようになりました.
    SELECT cust_name,cust_contact
    FROM Customers AS C, Orders AS O, OrderItems AS OI
    WHERE C.cust_id = O.cust_id
        AND OI.order_num = O.order_num
        AND prod_id = 'RGAN01';
    注意:OracleにASはありません.
    OracleはASキーワードに対応していません.Oracleで別名を使うなら、ASを使わずに簡単に列名を指定すればいいです.
    テーブルの列名はクエリー実行にのみ使用されます.列のエイリアスとは異なり、表のエイリアスはクライアントに戻りません.
    2.自己連結
    連結タイプ:内連結、自連結、自然連結、外連結
    まずJim Jonesが働いている会社を探して、その会社の仕事の顧客を探します.
    --        
    SELECT cust_id, cust_name, cust_contact
    FROM Customers
    WHERE cust_name = (
                        SELECT cust_name
                        FROM Customers
                        WHERE cust_contact = 'Jim Jones' 
                    );
    これは第一の解決策で、サブクエリを使っています.
    連結された同じクエリを使用してみます.
    SELECT c1.cust_id, c1.cust_name, c1.cust_contact
    FROM Customers AS c1, Customers AS c2
    WHERE c1.cust_name = c2.cust_name
        AND c2.cust_contact = 'Jim Jones';
    このクエリに必要な二つの表は実際には同じ表であるため、Custoomers表はFROMサブカルに二回現れました.これは完全に合法的ですが、Customersの引用には曖昧性があります.DBMSはどのCusstomers表を引用しているのか分かりません.この問題を解決するには、別の名前表を使う必要があります.
    ヒント:自己連結でサブクエリを使用しません.
    自己連結は、通常は外部文として用いられ、同じテーブルからデータを検索するためのサブクエリ文の代わりに使用される.最終的な結果は同じですが、多くのDBMS処理の連結は処理子クエリよりずっと速いです.
    3.自然連結
    標準的な連結(前の課で紹介した内部連結)は、すべてのデータを返します.同じ列は何度も現れます.自然連結排除は複数回出現して、一列に一回だけ戻るようにします.
    この仕事はどうやって完成しますか?自然連結にはそれらの唯一の列しか選択できません.普通は一つの表に対してワイルドカード(SELECT*)を使用して、他の表の列に対して明確なサブセットを使用して完成します.
    SELECT C.*, O.order_num, O.order_date,
            OI.prod_id, OI.quantity, OI.item_price
    FROM Customers AS C, Order AS O, OrderItems AS OI
    WHERE C.cust_id = O.cust_id
        AND OI.order_num = O.order_num
        AND prod_id = 'RGAN01';
    実際には、私たちがこれまでに作った各連結は自然連結であり、自然連結ではない内連結は永遠に使われないかもしれません.
    4.外連結
    多くの連結は、一つの表の行を他の表の行に関連付けるが、関連行がない行を含む必要がある場合がある.例えば、連結を使用して以下の作業を完了する必要があるかもしれない.
  • は、今まで注文していないお客様を含めて、各お客様の注文をカウントします.
  • はすべての商品と注文数量を並べて、注文していない商品を含みます.
  • 平均販売規模は、今まで注文していない顧客を含めて計算されます.
  • 上記の例では、連結は関連テーブルに関連していない行を含んでいる.このような連結は外連結といいます.
    次のSELECT文は簡単な内部連結を与えます.すべての顧客とその注文を検索します.
    SELECT Customers.cust_id, Orders.order_num
    FROM Customers INNER JOIN Orders
        ON Customers.cust_id = Orders.cust_id;
    外連結文法は似ています.注文していないお客様を含めた全てのお客様を検索するには、次のようにしてください.
    SELECT Customers.cust_id, Orders.order_num
    FROM Customers LEFT OUTER JOIN Orders
    ON Customers.cust_id = Orders.cust_id;
    前の課で述べたように、このSELECT文はキーワードOUTER JINを使って連結タイプを指定します.
    しかし、内部連結関連の2つのテーブルの行とは異なり、外部連結はまだ関連行がない行を含む.OUTER JIN文法を使用する場合は、RIGHTまたはLEFTキーワードを使用して、そのすべての行を含む表を指定しなければならない(RIGHTはOUTR JIN右側の表を指し、LEFTはOUT JIN左側の表を指摘する.)
    上記の例ではLEFT OUTER JINを使用して、FROM子文の左側の表(Cusstomers)からすべての行を選択します.右の表からすべての行を選択するために、RIGHT OUTER JINを使用する必要があります.以下の通りです.
    SELECT Customers.cust_id, Orders.order_num
    FROM Customers RIGHT OUTER JOIN Orders
    ON Orders.cust_id = Customers.cust_id;
    注意:SQLite外連結
    SQLiteはLEFT OUTER JINをサポートしていますが、RIGHT OUT OUTER JINはサポートされていません.
    ヒント:連結のタイプ
    覚えておきたいのですが、いつも二つの基本的な連結形式があります.それらの間の唯一の違いは、関連表の順序である.言い換えれば、FROMまたはWHERE子文の順を調整し、左連結を右連結に変換することができる.ですから、この二つの外連結は交換できます.どちらが便利ですか?
    もう一つの外連結があります.つまり、全外連結です.二つの表のすべての行を検索して、関連できる行に関連します.左連結または右連結が一つの表を含む関連しない行とは異なり、全連結は二つの表の関連しない行を含む.全外連結の文法は以下の通りです.
    SELECT Customers.cust_id, Orders.order_num
    FROM Orders FULL OUTER JOIN Customers
        ON Orders.cust_id = Customers.cust_id
    注意:FULL OUTER JOINのサポート
    Access、Marise DB、MySQL、Open Office BaseまたはSQLiteはFULL OUTER JIN文法をサポートしていません.
    5.集合関数付きの連結を使う
    すべてのお客様及びお客様の注文数を検索するには、以下のコードはCOUNT()関数を使ってこの仕事を完成します.
    SELECT Customers.cust_id,
           COUNT(Orders.order_num) AS num_ord
    FROM Customers INNER JOIN Orders
        ON Customers.cust_id = Orders.cust_id
    GROUP BY Customers.cust_id
    
    
    cust_id         num_ord
    10000001        2
    10000003        1
    10000004        1
    10000005        1
    凝集関数は他の連結とともに便利に使用できる.次の例を見てください.
    SELECT Customers.cust_id,
           COUNT(Orders.order_num) AS num_ord
    FROM Customers LEFT OUTER JOIN Orders
        ON Customers.cust_id = Orders.cust_id
    GROUP BY Customers.cust_id
    
    cust_id     num_ord
    10000001    2
    10000002    0
    10000003    1
    10000004    1
    10000005    1
    この例は左外部連結を使用して、すべてのお客様を含みます.注文がないお客様も含まれます.その結果、顧客100000 2も含まれています.彼は0個ずつ注文しています.
    6.連結と連結条件の使用
    連結と使用のポイントをまとめます.
  • は、使用される連結タイプに注意する.一般的には内連結を使いますが、外連結も有効です.
  • 適切な連結文法については、具体的な文書を見て、対応するDBMSがどのような文法をサポートしているかを確認します.
  • は、正しい紐付け条件を使用すると、不正なデータを返すことを保証しています.
  • は常に連結条件を提供しなければなりません.そうでなければ、デカルト積が出ます.
  • は、1つの連結に複数のテーブルを含み、各連結に対して異なる連結タイプを採用してもよい.このようにするのは合法的ですが、一般的にも有用ですが、一緒にテストする前にそれぞれの連結をテストします.これは故障の排除をもっと簡単にします.