【SQL入門】SELECT INTO構文でデータを移行・加工・分析！用途別使い方とサンプルコード集
MySQLにおけるSELECT INTO構文：詳細解説
構文
SELECT ...
INTO [destination]
destinationは以下の3種類から選択できます。
- 既存のテーブル: 抽出したデータを既存のテーブルに挿入します。
- 新しいテーブル: 抽出したデータを使って新しいテーブルを作成します。
- 変数: 抽出したデータを変数に格納します。
- ファイル: 抽出したデータをファイルに出力します。
...は、SELECT句で指定する列名、式、または集計関数です。- 既存のテーブルに挿入する場合、列名とデータ型が一致する必要があります。
- 新しいテーブルを作成する場合、CREATE TABLE構文と同じオプションを指定できます。
- 変数は、@記号で始まる必要があります。
- ファイル形式は、CSV、テキスト、JSONなど、様々な形式を指定できます。
用途
SELECT INTO構文は、様々な用途に使用できます。以下はその例です。
- データの移行: あるテーブルから別のテーブルにデータを移行する。
- データの加工: 特定の条件に合致するデータのみを抽出し、新しいテーブルを作成する。
- データ分析: 集計関数を使用して、データの統計情報を新しいテーブルに格納する。
- データのバックアップ: 抽出したデータをファイルに出力し、バックアップを取る。
例
例1：既存のテーブルにデータを挿入
既存の customers テーブルから、city が "Seattle" である顧客のみを抽出し、seattle_customers という新しいテーブルに挿入します。
SELECT *
INTO seattle_customers
FROM customers
WHERE city = 'Seattle';
例2：新しいテーブルを作成
orders テーブルから、注文金額が100ドルを超える注文のみを抽出し、high_orders という新しいテーブルを作成します。新しいテーブルには、order_id、customer_id、order_amount の列が含まれます。
SELECT order_id, customer_id, order_amount
INTO high_orders
FROM orders
WHERE order_amount > 100;
例3：変数にデータを格納
employees テーブルから、平均給与を計算し、avg_salary という変数に格納します。
SELECT AVG(salary)
INTO avg_salary
FROM employees;
例4：ファイルを生成
products テーブルの内容をCSVファイルに出力します。
SELECT *
INTO OUTFILE '/path/to/products.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM products;
SELECT *
INTO seattle_customers
FROM customers
WHERE city = 'Seattle';
解説:
- このコードは、
SELECT句を使用して、customersテーブルからすべての列のデータを選択します。 INTO句を使用して、抽出されたデータをseattle_customersという新しいテーブルに挿入します。WHERE句を使用して、cityが "Seattle" である顧客のみを抽出します。
SELECT order_id, customer_id, order_amount
INTO high_orders
FROM orders
WHERE order_amount > 100;
- このコードは、
SELECT句を使用して、ordersテーブルからorder_id、customer_id、order_amountの列のデータを選択します。 WHERE句を使用して、注文金額が100ドルを超える注文のみを抽出します。- 新しいテーブル
high_ordersは、order_id、customer_id、order_amountの3つの列を持つことになります。
SELECT AVG(salary)
INTO avg_salary
FROM employees;
- このコードは、
SELECT句を使用して、employeesテーブルのsalary列の平均値を計算します。 INTO句を使用して、計算結果をavg_salaryという変数に格納します。
SELECT *
INTO OUTFILE '/path/to/products.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM products;
INTO OUTFILE句を使用して、抽出されたデータを/path/to/products.csvというCSVファイルに出力します。FIELDS TERMINATED BY ','オプションは、CSVファイルの各フィールドをカンマで区切ります。
- 上記のコードはほんの一例です。SELECT INTO構文は、様々な目的に使用できます。
INSERT INTO ... SELECT構文は、SELECT INTO構文とほぼ同じ機能を備えていますが、より汎用性が高く、柔軟性に優れています。SELECT INTOではできない以下の操作が可能です。
- 複数のテーブルからデータを結合して挿入する。
- サブクエリを使用して、挿入するデータを選択する。
- INSERT ステートメントのオプションを指定して、挿入処理を制御する。
以下の例は、INSERT INTO ... SELECTを使用して、customers テーブルと orders テーブルのデータを結合し、orders_with_customer_info という新しいテーブルに挿入する方法を示しています。
INSERT INTO orders_with_customer_info
SELECT o.order_id, o.customer_id, c.name, o.order_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id;
CREATE TABLE ... SELECT
CREATE TABLE ... SELECT構文は、既存のテーブルのデータに基づいて新しいテーブルを作成する場合に便利です。SELECT INTO構文よりも簡潔で、読みやすいコードになります。
CREATE TABLE seattle_customers
SELECT *
FROM customers
WHERE city = 'Seattle';
UNION ALL
UNION ALL構文は、複数のSELECT結果を結合して新しい結果セットを作成する場合に便利です。SELECT INTO構文とは異なり、重複する行も含めて結合します。
以下の例は、products テーブルと categories テーブルのデータを結合し、すべての製品とカテゴリを表示する方法を示しています。
SELECT *
FROM products
UNION ALL
SELECT *
FROM categories;
TEMPORARY TABLE
TEMPORARY TABLEは、処理中にのみ存在する一時的なテーブルを作成する場合に便利です。SELECT INTO構文よりも効率的で、メモリ使用量が少ない場合がある。
以下の例は、orders テーブルから、注文金額が100ドルを超える注文のみを含む一時テーブル high_orders を作成する方法を示しています。
CREATE TEMPORARY TABLE high_orders
SELECT *
FROM orders
WHERE order_amount > 100;
クライアントライブラリ
MySQLクライアントライブラリを使用すると、SELECT INTO構文よりも柔軟で強力な方法でデータを操作できます。ライブラリは、結果セットをメモリに格納したり、CSVファイルなどの形式で保存したりするなどの機能を提供します。
以下の例は、PythonのMySQLライブラリを使用して、products テーブルの内容をCSVファイルに保存する方法を示しています。
import mysql.connector
db = mysql.connector.connect(
host="localhost",
user="username",
password="password",
database="mydatabase"
)
cursor = db.cursor()
cursor.execute("SELECT * FROM products")
results = cursor.fetchall()
with open('products.csv', 'w') as csvfile:
writer = csv.writer(csvfile)
writer.writerow([col for col in cursor.description])
writer.writerows(results)
db.close()
mysql sql
