こんにちは。ECF Tech担当
Michiharu.Tです。
データベースを扱う開発やSQLを学習する際など、初期データの投入や既存データの移行に、SQLファイルを使ってデータを登録したい場面が多くあります。ですが、DBMSごとにコマンドや手順が微妙に異なり、戸惑うことも少なくありません。
本記事では、SQLファイルからデータベースにデータを投入するための方法をご紹介します。
手順は、PostgreSQL、MySQL、SQliteの３つのDBMSをそれぞれ説明しています。ご自身のお使いになられるDBMSに応じてご活用ください。DBMSごとの手順は、このページをスクロールするか下のリンクから移動できます。
また、本記事では各DBMSがすでにインストールされているものとして説明をしています。各DBMSのインストール手順が必要な方は、下のリンクからご参照ください。
下のリンクから、サンプルとなるSQLファイルをダウンロードできます。パソコンショップの販売管理を題材としたテーブル定義とそのデータです。データベースに投入後は、SQLの練習にもご活用いただけます。
サンプルデータ
※右クリックして、「名前をつけてリンク先を保存」をクリックしてください。
PostgreSQL
コマンドプロンプトを起動し、psqlコマンドでデータベースにアクセスします。
psql -U postgres
-Uオプションでユーザー名を指定します。上のコマンドではpostgresというユーザー名を指定しています。
適宜パスワードを入力して、ログインします。
sample.sqlのデータを保存するデータベースを作成しましょう。次のコマンドで、sampleという名前のデータベースを作成します。
create database sample;
作成したら、\l sampleと入力し、データベースがあること確認しましょう。
\qコマンドで、psqlからログアウトします。
次のコマンドを実行し、SQLファイル内のSQL文を実行します。
psql -U postgres -f ファイルパス -d sample
-Uはユーザー名を指定するオプションです。
-fはファイルパスを指定するオプションです。ご自身の環境で、sample.sqlを保存した場所を指定してください。
-dはデータベース名を指定するオプションです。
パスワードを聞かれた場合は入力します。
下は、ファイルパスとしてC:\Users\ecf_pc\Desktop\sample.sqlを設定して実行したものです。sample.sql内のSQL文が実行されます。
※ sample.sqlには、最初にテーブル削除の命令が入っているため、該当テーブルがない場合に上記画面のようにNOTICE:～のようなメッセージが出ますが、問題ありません。
SQLファイルの実行時に、次のようなエラーが発生するかもしれません。
psql:C:/Users/ecf_pc/Desktop/sample.sql:57: ERROR: 符号化方式"SJIS"に対する不正なバイト列です: 0x8e 0x27
これは、SQLファイル内の文字コードとPostgreSQLが読み込もうとしている文字コードが一致していないために起こります。
最も簡単な修正方法は、SQLファイルを、文字コードがSJIS形式のファイルとして保存しなおすことです。
メモ帳でSQLファイルを開き、次のように保存してください。
再度ログインし、次のコマンドでsampleデータベースにアクセスします。
\c sample;
次のSELECT文を実行してみましょう。
SELECT * FROM products;
正常に出力されれば成功です。
MySQL
はじめに、MySQLにログインします。コマンドプロンプトで次のように入力します。
mysql -u root -p
-uはユーザー名を指定するオプションです。
-pはパスワードを指定するオプションです。
パスワードを聞かれますので適宜入力します。ログインに成功すると、下のような画面になります。
sample.sqlのデータを投入するデータベースを作成しましょう。次のコマンドで、sampleという名前のデータベースを作成します。
create database sample;
正しく実行できると、次のメッセージが表示されます。
show databases;を実行すると、sampleというデータベースが作成されていることが確認できます。
データベースが作成できたら、exitのコマンドでmysqlをログアウトします。
コマンドプロンプトから、次のコマンドを実行します。
mysql -u root -p -D sample < ファイルパス
-Dは使用するデータベースを指定するオプションです。パスワードを適宜入力します。
ファイルパスの部分は、sample.sqlを保存した場所を指定してください。
下は、ファイルパスとしてC:\Users\ecf_pc\Desktop\sample.sqlを設定して実行したものです。正常に実行できた場合は何も表示されず、コマンドプロンプトの入力モードに戻ります。
再度ログインし、次のコマンドでsampleデータベースにアクセスします。
use sample;
次のSELECT文を実行してみましょう。
SELECT * FROM products;
正常に出力されれば成功です。
SQLite
任意のフォルダを準備し、sample.sqlを保存します。
sample.sqlを保存したフォルダでコマンドプロンプトを開きます。
次のコマンドを入力します。
sqlite3 sample.db < sample.sql
sample.sqlと同じフォルダ内にsample.dbという名前のファイルが作成されます。これでデータベースの作成完了です。
次のコマンドでデータベースにアクセスします。
sqlite3 sample.db
アクセスに成功すると次のように出力され、コマンドが入力できるようになります。
SQL文を実行し、結果が得られればOKです。
おわりに
最後までご覧頂き、ありがとうございました。皆様のSQLトレーニングの一助になれば幸いです。
