SQL Shell(psql)を使用してでpostgreSQLにアクセスし、コマンドで操作をする方法 その2
目次
はじめに
今回はオープンソースソフトウェアでリレーショナルデータベース管理システム(RDMS)であるPostgreSQLにコマンドラインによってアクセスし、データを取得したり、追加したりしてみたいと思います。SQLとはStructured Query Languageの略語です。PostgreSQLの概要やインストールは省略しますが気になる方はこちらを参照して下さい。PostgreSQLの超入門。
postgresのインストールを完了された方向けの記事となりますが興味のある方もぜひご覧ください。まだインストールしてない方もmacユーザー向けのインストール設定参考記事が上記にありますので参考にされてください。
この様な方々を対象としています。
- postgresのインストールを完了された方
- コマンドからPostgreSQLを操作したい
- まずはSTEP BY STEPでpostgreSQLの扱いを覚えたい
- 直接queryを扱いたい
- GUIを使って操作したい
- macをお使いの方
実行環境
macOS (Catalina) version 10.15.2
macbook Pro(15-inch, 2017)
brew –version : 2.2.2
nodebrew use (current: 12.13.1)
npm version (6.12.1)
LaunchpadからSQL Shellを立ち上げる
インストールしたSQL ShellからpostgreSQLを操作する事ができます。Launchpadから立ち上げましょう。もしお使いのターミナルやIterm2がありましたらパスを通している場合そちらで操作が可能となります。パスを通す方法を解説しておりますのでよかったらまだお済みで無い方は実行してみて下さい。
もちろんSQL Shellで操作可能ですので今回はSQL Shellを用いて解説していきたいと思います。
Iterm2でのpsqlコマンドのPATHの通し方はこちら。
SQL Shellの使い方を解説
SQL Shellを立ち上げたらこのようなターミナルが出現します。
この画面は認証を行う画面になっていて、インストールした時の設定が必要になります。
順番に聞かれる事がこちらです。
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (11.6)
Type “help” for help.
順番に聞かれることにエンターを押して、パスワードの時にインストール設定時のパスワードを入力しましょう。その他についてはデフォルトの値が localhost となっていますし、postgres データベースは自動で作成されており、 postgres ロールで接続した場合は postgres データベースに接続します。ほとんどがデフォルト設定されています。
接続されましたらこのような出力になります。
データベースがあると仮定して間違ってみる
もしデータベースが作成されている場合、記述のデーターベースと接続する事ができますが間違った場合はエントリー出来ません。
一度試してみましょう。
左上の🔴をクリックして閉じるかexitコマンドで終了して、再度立ち上げ直してみて下さい。
画像の時にmydbとしてみます。
先に進めてパスワードを打ち込んでください。
このようにmydbは存在しませんとpsqlに怒られますね。
わざわざ怒られるようなことをしてすいません。
CREATE DATABASEでデータベースを作成し、DROP DATABASEで削除しよう
では早速databaseを作成してみましょう。コマンドはこちらです。
CREATE DATABASE mytestdb;
画像のように出力されましたでしょうか。
データベースが作成されました。では表示してみましょう。
postgres=# \lと入力して下さい。バックスラッシュとアルファベットのエルです。リスト表示させる意味になります。
画像のようにmytestdbが作成されているでしょうか。これでデータベースの作成は以上になります。終了コマンドは:qです。
DROP DATABASE で削除してみよう
では先ほど作成したデータベースを削除します。
DROP DATABASE mytestdb;
これでデータベースを削除出来ました。アプリケーションの作成途中で消してしまわないよう注意しましょう。
では再度データーベースを作成してみて下さい。CREATE DATABASE mytestdb;でデータベースを作成する事が出来ます。
カレントデータベースをmytestdbにしよう
では作成したデーターベースに接続してみましょう。今現在はカレントデーターベースがpostgresとなっていますが作成したdbに移動し、テーブルを作成してみましょう。
postgres=# \c mytestdb
バックスラッシュとcで移動する事が出来ます。もちろん同じように先ほどまでのpostgresにも戻る事が出来ます。
CREATE TABLEでテーブルを作成しよう
テーブルを作成するコマンドには型があります。実際に作成するときは + 記号は必要ありませんが、このような形式で作成します。
画像にあるconstrainsts if anyとは制約がある場合という意味になります。
ここから制約をつけない場合とある場合で作成してみましょう。
ではターミナルがmytestdb=# になっていることを確認して下さい。
コマンドはこちらです。
personテーブルを作成する際にデータタイプのあるカラムも作成しています。
preson( でエンターを押して改行して大丈夫です。INT,のパートでも同じように改行して下さい。
出来ましたらこのように出力されます。
データタイプに関してはこちらの記事がございますのでご覧ください。
作成したデータベースを表示させてみましょう。
mytestdb=# \d
表示させる事が出来たでしょうか。
テーブルが作成されていればカレントデーターベースからアクセスし一覧を表示させる事が出来ます。
では次にpersonデーブルを表示させてみましょう。
コマンドはこちらです。
mytestdb=# \d person
personテーブルのカラムやデータタイプを表示させる事が出来ました。しかしここで制約をつけたカラムを作成してみましょう。一度作成したperson テーブルを削除します。
コマンドはこちらです。
mytestdb=# DROP TABLE person;
テーブルが無い事を確認したらもう一度作成しなおしてみましょう。
画像にあるBIGSERIAL NOT NULL PRIMARY KEYなどの記述が追加されています。
bigserialは自動採番型で、idの最大数が最も多いタイプになります。not nullは列が NULL 値をとらないことを指定しています。primary keyはユニークなもの、つまり同じものはとらないという意味です。
このようにデータベースに挿入するデータの型を厳格にする事が重要のためどんなデータを格納したいかを決める際は意識しておきましょう。
ではコマンドはこちらです。
打ち間違いがあると何度もやり直さなければならないので大変ですよね。
では personデーブルを表示させてみましょう。
mytestdb=# \d person
Nullableにnot nullが追加されました。次はこのコマンドで確かめましょう。
mytestdb=# \d
mytestdbデータベースがもつテーブルが表示されています。
ここでperson_id_seqが追加されていますが、BIGSERIALにすることで作成されたのがperson_id_seqです。連番で割り当てられるserial型と紐付けて作成されます。
では次はテーブルのみを表示させてみましょう。
コマンドはこちらです。
mytestdb=# \dt
レコードを挿入する
作成したテーブルにレコードを挿入してみましょう。まずemailデータが抜けたデータを入れてみましょう。
画像のようにコマンドを入れます。
date_of_birth ) まで打ち込むと mytestdb= から mytestdb- と表記が変わります。セミコロンを打たずに、エンターを押して、valuesを記述しエンターを押します。
INSERT 01というメッセージを受け取りました。一度確認してみましょう。
コマンドはこちらです。
mytestdb=# select * from person;
確かに先ほど作成したカラムの通りレコードが入っています。次はemailも含んだデータを入れてみましょう。
このようにコマンドから指定したカラムと挿入するデータを一致させることで完了させる事ができます。仮にカラムは用意したけど、データは抜けていたり、その逆の場合でもinsert into は完了させる事ができませんので注意して記述して下さい。
データが挿入されました。ではここでもっと多くのデータを挿入してみましょう。
MOCKデータを提供するサービス、Mackarooでデータをダウンロードする事が出来ます。
URLはこちらになります。
このサイトで1000件のsqlを取得する事ができ、ターミナルで読み込ませた後にデーターベースに挿入する事で学習をする事ができます。
Mackarooからデータを取得しよう
今回はip_addressを削除します。右側にある❌をクリックして下さい。
そしてAdd another fieldをクリックしてfiledを追加します。
代わりに date_of_birth と country_of_birthを追加して下さい。
date_of_birthのTypeをDateへ、それから日付をyyyy-mm-ddにへ変更し, country_of_birthをcountryに変更します。
formatをSQLに変更し、Table Nameをperson, include create tableにチェックを入れて下さい。
変更箇所
previewをクリックするとダウンロードするsqlファイルを確認する事ができます。
ではダウンロードして、エディターで開いてみて下さい。少し編集を加えます。
僕の場合はVScodeで開きます。
VScodeで開くとこのようなファイルになってます。上部にcreate tableのコードがあり、insertでデータが連なっています。
ここでtableの変更を加えます。
emailとcountry_of_birth以外、NOT NULLを追加します。idにBIGSERIALとNOT NULL PRIMARY KEYを与え、
さらにemailを150へ、genderを7へ変更します。そしてVScodeの編集したperson.sqlを保存しましょう。
変更後このファイルをすべてコピーしてターミナルを開いて下さい。macの方はcommand + aで全選択して、command + s でコピーできます。
ターミナルでコマンドを確認します。
mytestdb=# \?
コマンドのhelpを表示させました。下まで見ていくと次のようなコマンドを見つける事ができます。
\i FILE execute commands from file
このコマンドでファイルを読み込ませる事ができる事がわかりました。ではターミナルで貼り付けて見ましょう。それにはperson.sqlのパスが必要です。ファイルのあるディレクトリ を見てみましょう。僕の場合はDesktopですがDownloadsの方が多いと思います。そのディレクトリ で pwdコマンドでパスを取得してみましょう。おそらく/Usesrs/{username}/Downloadsでは無いでしょうか。
mytestdb=# \i /Users/{username}/Downloads/person.sql;
しかしこのままでは新しいカラムがある事でErrorが起きてしまいます。
カラムとデータの整合性が取れませんね。
drop tableでテーブルを削除しよう
コマンドはこちらです。
mytestdb=# DROP TABLE person;
コマンドで確かめます。
mytestdb=# \d
コマンドはこちらです。
もう一度person.sqlを読みこもう
もうお気づきかもしれませんが、コマンドは矢印の上キーをクリックする度に使用した履歴を追う事ができます。上キーで素早くコマンドを探し当てる事ができるのです。では次のコマンドはこちらです。上キーで探してみて下さい。
mytestdb=# \i /Users/{username}/Downloads/person.sql;
多くのデータが読み込まれました。テーブル表記で確認しましょう。
mytestdb=# select * from person;
このように表示させる事ができました。さらに確認でpersonテーブルを表示させましょう。
SELECT FROMコマンドを使ってみよう
先ほどから使ってきたselect fromコマンドですが、他にも多様な使い方があります。ここからはそのほかのselect fromコマンドを使ってみましょう。
行(rows)の個数 SELECT FROM
このコマンドで行の個数を出す事ができます。
mytestdb=# SELECT FROM person;
(1000 rows)
SELECT first_name FROM person;
mytestdb=# SELECT first_name FROM person;
カラムをつないで出力させよう
mytestdb=# SELECT first_name, last_name FROM person;
ORDER BYで並べ替えよう
出力結果を並べ替える事ができます。
mytestdb=# select * from person ORDER BY country_of_birth DESC;
mytestdb=# select * from person ORDER BY country_of_birth ASC;
ASCは並びが1,2,3,4と並びます。DESCは5,4,3,2,1となります。
DISTINCTで重複を外して表示させよう
mytestdb=# SELECT DISTINCT country_of_birth FROM person ORDER BY country_of_birth;
このように重複したデータを省いたデータのフィルタリングができます。country_of_birthは重複があるデータです。このようにすれば何カ国かわかりやすくする事ができます。
WHEREで特定する
mytestdb=# SELECT * FROM person WHERE gender = ‘Female’;
genderがfemalだけに絞る事ができます。さらにANDを使って絞る事ができます。
mytestdb=# SELECT * FROM person WHERE gender = ‘Male’AND country_of_birth=’Brazil’;
次にgenderがMALEでかつcountry_of_birthがロシアとフランスで絞ってみましょう。
mytestdb=# SELECT * FROM person WHERE gender = ‘Male’AND(country_of_birth=’France’ OR country_of_birth=’Russia’);
まとめ
今回はSQL Shellでのpsqlコマンドを実践しました。
データベースの作成や削除からperson tableを作成してinsert intoでデータを挿入しました。デモデータをダウンロードしてpsqlコマンドでデータベースに登録し、select fromコマンドでいろんな情報、データを出力してみました。ここまで長い時間お付き合いいただきありがとうございました。
Twitterアカウントを公開しておりますので、何かご不明な点がありましたらコメントいただけたら幸いです。お疲れ様でした。
