Mysqlサブクエリ-select文ネスト-複数のテーブルの取得
4244 ワード
Mysqlのサブクエリは、複数のテーブルからデータを取得する場合に使用されるSelect文のネストです.
WHERE文にサブクエリを埋め込む
TNT 2という商品を注文したすべてのお客様の情報(お客様の名前、連絡先など)を検索し、データベースを知ることで、ここで与えられた条件と検索結果が同じ表ではなく2つの表に分かれていることがわかり、単一の検索ではできません.この2つのテーブルの関係を見つける必要があります.この2つのテーブルはordersテーブルで関連付けることができることが分かった.
mysql> DESCRIBE orderitems; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | order_num | int(11) | NO | PRI | NULL | | | order_item | int(11) | NO | PRI | NULL | | | prod_id | char(10) | NO | MUL | NULL | | | quantity | int(11) | NO | | NULL | | | item_price | decimal(8,2) | NO | | NULL | | +------------+--------------+------+-----+---------+-------+ 5 rows in set (0.76 sec)
mysql> DESCRIBE orders; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | order_num | int(11) | NO | PRI | NULL | auto_increment | | order_date | datetime | NO | | NULL | | | cust_id | int(11) | NO | MUL | NULL | | +------------+----------+------+-----+---------+----------------+ 3 rows in set (0.13 sec)
mysql> DESCRIBE customers; +--------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_address | char(50) | YES | | NULL | | | cust_city | char(50) | YES | | NULL | | | cust_state | char(5) | YES | | NULL | | | cust_zip | char(10) | YES | | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +--------------+-----------+------+-----+---------+----------------+ 9 rows in set (0.09 sec)
上の表の構造を研究することによって、構想を得た.
1、TNT 2を含む注文番号を検索し、orderitems表でSELECT order_num FROM orderitems WHERE prod_id='TNT2';
2、注文番号によって顧客IDを見つけ、orders表でSELECT cust_id FROM orders WHERE order_num IN (20005,20007);
3、顧客IDによって顧客の名前と連絡先を検索し、customers表の中でSELECT cust_name,cust_contact FROM customers WHERE cust_id IN (10001,10004);
サブクエリのメソッド:
つまり、上の3つのSELECT文が1つのSELECT文に統合されています.
+----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +----------------+--------------+ 2 rows in set (0.01 sec)
SELECT文にサブクエリを埋め込む
上の例は,WHERE句にSELECT文を用いる.
もう1つのケースはSELECT文にSELECT文を使用することです.
次の例を見てください.
customersテーブルの各顧客の受注数を取得します.
分析では、顧客の情報はcustomersテーブルに保存され、顧客の注文はordersテーブルに保存されています.2つの注文はcust_を通じてidが関連付けられています.
顧客ごとの注文IDライン数を算出すると、彼の注文数がわかります.
+----------------+--------+ | cust_name | orders | +----------------+--------+ | Coyote Inc. | 2 | | E Fudd | 1 | | Mouse House | 0 | | Wascals | 1 | | Yosemite Place | 1 | +----------------+--------+ 5 rows in set (0.07 sec)
サブクエリのテクニック:
サブクエリでクエリを作成する最も信頼できる方法は、徐々に実行することです.
まず、最内層クエリーを確立し、ハードコーディングされたデータで外層クエリーを確立し、サブクエリーが正常であることを確認してからのみ埋め込まれます.このとき、再度テストし、追加するサブクエリごとにこれらの手順を繰り返します.
WHERE文にサブクエリを埋め込む
TNT 2という商品を注文したすべてのお客様の情報(お客様の名前、連絡先など)を検索し、データベースを知ることで、ここで与えられた条件と検索結果が同じ表ではなく2つの表に分かれていることがわかり、単一の検索ではできません.この2つのテーブルの関係を見つける必要があります.この2つのテーブルはordersテーブルで関連付けることができることが分かった.
mysql> DESCRIBE orderitems; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | order_num | int(11) | NO | PRI | NULL | | | order_item | int(11) | NO | PRI | NULL | | | prod_id | char(10) | NO | MUL | NULL | | | quantity | int(11) | NO | | NULL | | | item_price | decimal(8,2) | NO | | NULL | | +------------+--------------+------+-----+---------+-------+ 5 rows in set (0.76 sec)
mysql> DESCRIBE orders; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | order_num | int(11) | NO | PRI | NULL | auto_increment | | order_date | datetime | NO | | NULL | | | cust_id | int(11) | NO | MUL | NULL | | +------------+----------+------+-----+---------+----------------+ 3 rows in set (0.13 sec)
mysql> DESCRIBE customers; +--------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_address | char(50) | YES | | NULL | | | cust_city | char(50) | YES | | NULL | | | cust_state | char(5) | YES | | NULL | | | cust_zip | char(10) | YES | | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +--------------+-----------+------+-----+---------+----------------+ 9 rows in set (0.09 sec)
上の表の構造を研究することによって、構想を得た.
1、TNT 2を含む注文番号を検索し、orderitems表でSELECT order_num FROM orderitems WHERE prod_id='TNT2';
2、注文番号によって顧客IDを見つけ、orders表でSELECT cust_id FROM orders WHERE order_num IN (20005,20007);
3、顧客IDによって顧客の名前と連絡先を検索し、customers表の中でSELECT cust_name,cust_contact FROM customers WHERE cust_id IN (10001,10004);
サブクエリのメソッド:
つまり、上の3つのSELECT文が1つのSELECT文に統合されています.
mysql> SELECT cust_name,cust_contact
-> FROM customers
-> WHERE cust_id IN(SELECT cust_id
-> FROM orders
-> WHERE order_num IN (SELECT order_num
-> FROM orderitems
-> WHERE prod_id = 'TNT2'));
結果:+----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +----------------+--------------+ 2 rows in set (0.01 sec)
SELECT文にサブクエリを埋め込む
上の例は,WHERE句にSELECT文を用いる.
もう1つのケースはSELECT文にSELECT文を使用することです.
次の例を見てください.
customersテーブルの各顧客の受注数を取得します.
分析では、顧客の情報はcustomersテーブルに保存され、顧客の注文はordersテーブルに保存されています.2つの注文はcust_を通じてidが関連付けられています.
顧客ごとの注文IDライン数を算出すると、彼の注文数がわかります.
mysql> SELECT cust_name,(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS orders FROM customers ORDER BY cust_name;
結果:+----------------+--------+ | cust_name | orders | +----------------+--------+ | Coyote Inc. | 2 | | E Fudd | 1 | | Mouse House | 0 | | Wascals | 1 | | Yosemite Place | 1 | +----------------+--------+ 5 rows in set (0.07 sec)
サブクエリのテクニック:
サブクエリでクエリを作成する最も信頼できる方法は、徐々に実行することです.
まず、最内層クエリーを確立し、ハードコーディングされたデータで外層クエリーを確立し、サブクエリーが正常であることを確認してからのみ埋め込まれます.このとき、再度テストし、追加するサブクエリごとにこれらの手順を繰り返します.