SQLの基礎とPostgreSQLのコマンドまとめ

はじめに

先日以下リプを頂いた。

気持ちはわかります
言語やFWよりGitやLinux、DBあたりだとどこでも使う(どこでもとは)のでそういう系に絞ると好印象な肌感です

— M2@世界一のカルネジスト/3年目個人事業主 (@M2_m_) April 28, 2020

言語やFWよりGitやLinux、DBあたりを勉強していると比較的好印象とのこと。

そこで、SQLに関して学んでみました。(たぶん教本は4~5か月くらい放置されてた。。。)

✅ゴール
SQLの理解を深める

✅環境に関して
Windows/PostgreSQL

✅参考教材
SQL 第2版 ゼロからはじめるデータベース操作　※主に4章まで

SQLの基本

✅SQL

リレーショナルデータベースを扱うための言語

✅データベース

大量の情報を保存し、コンピュータから効率よくアクセスできるように加工したデータの集まりのこと。
データベースをコンピュータで管理するシステムをDBMSと呼ぶ。

✅RDBMS

リレーショナルデータベース(=関係データベース)管理システム。DBMSの一種。データベースを読み書きするプログラム。
Excelのように列と行からなる2次元表の形式でデータ管理するため理解しやすく、最も広く利用されているデータベース。SQLで操作する。

テーブルと呼ばれる2次元表でデータを管理する。

テーブルは「列(カラム)」と1件のデータを表す「行(レコード)」からなる。読み書きはレコード単位で行われる。列と行が交わる1マスを「セル」と呼ぶ。

✅トランザクション

セットで実行されるべき1つ以上の更新処理の集まりのこと。

COMMIT;は処理の確定、ROLLBACK;は処理の取り消し

ACID特製：トランザクションにおいて、守るべき4つの約束事(標準規格)
①原子性②一貫性③独立性④永続性

✅ビュー

SQLの観点から見るとテーブルと同じもの。

テーブルは実際のデータが保存されているが、ビューはSELECT文が保存されていて、データ自体は持っていない。⇒仮想のテーブルを作る。

CREATE VIEW ビュー名(<ビューの列名>,~~) AS <SELECT文>

使い捨てのビューをサブクエリと呼ぶ。

PostgreSQLのコマンドまとめ

※インストールの参考

✅PostgreSQL

・PostgreSQLへの接続

psql -U postgres // デフォルトのDBへ接続
psql -U postgres -d shop // DBを指定

-Uでユーザーを指定してログイン。どうやらインストール時のパスワードはpostgresユーザーに紐づいているらしい。

✅データベース

・データベースの作成

# CREATE DATABASE <DB名>;

・データベース一覧の確認

psql -l -U postgres // コマンド上の場合
# \l // ログイン中の場合

✅テーブル

・テーブル作成

# CREATE TABLE <テーブル名> (<カラム名><データ型><制約>,~~);

・テーブル一覧表示

# \d;

・テーブル削除

# drop table <テーブル名>;

・テーブル構造の表示

# \d <テーブル名>;

・カラムの追加

ALTER TABLE <テーブル名> ADD COLUMN <カラム名> <型>;

・カラムの削除

ALTER TABLE <テーブル名> DROP COLUMN <カラム名> <型>;

・テーブル名の変更

ALTER TABLE <テーブル名> RENAME TO <新しいテーブル名>;

✅データ

・データ追加

INSERT INTO <テーブル名> VALUES ('0001', 'Tシャツ' ,'衣服', 1000, 500, '2009-09-20');

・データ一覧表示

カラム名に*(アスタリスク)を使うとすべての列を出力可能

# select <カラム名> from <テーブル名>;

SELECT文で必要なデータを検索し、取り出すことをクエリ(query)と呼ぶ。

・データ更新

# update <テーブル名> set <カラム名> = <カラム名*10> where <条件>; // where句を使うことで更新対象の行を制限できる。

・データ削除

# delete from <テーブル名>;

・重複を省いて表示

# SELECT DISTINCT <重複を省きたいカラム> FROM <テーブル名>;

・条件付きで表示

指定したカラムから指定した条件に当てはまるものを表示

# SELECT <カラム名> FROM <テーブル名> WHERE <条件式>;

✅関数

・COUNT関数：テーブルの行数を数える

SELECT COUNT(*) FROM <テーブル名>
SELECT COUNT(DISTINCT <カラム名>) FROM <テーブル名>

※NULLを除外したい場合はNULL列のあるカラム*の代わりに指定する。

※DISTINCTというキーワードを使うと超副業を除外してカウントしてくれる。

・SUM関数：合計を求める

・AVG関数：平均値を求める

・MAX/MIN関数：最大値/最小値を求める

✅句

・WHERE句：テーブルから指定した条件に合う行だけ表示する

SELECT <カラム名>,FROM <テーブル名> WHERE <条件式>;

※レコード(行)に対してのみ条件指定可能

・GROUP BY句：テーブルを複数のグループに分けて集約する

SELECT <カラム名> ,COUNT(*) FROM <テーブル名> GROUP BY <カラム名>; // GROUP BYを用いることでカラム内でグループ分けをすることができる。

・HAVING句：GROUP BY句でグループ分けしたものを更に条件指定

・ORDER BY句：検索結佐の並び替え

※昇順(ASC)は小さい数字から降順(DESC)は大きい数字から。NULLは比較できないため先頭または末尾に表示される。

躓いた点

✅PostgreSQLにログインできない

起因：パスワードエラー

解決策：psql -U postgresで起動させた。

✅PostgreSQLのポート変更ができない

起因：インストール時のポート番号に依存する。

解決策：再インストールする。

psql -U postgres -p 5433やったけどエラー。どうやらpostgresql.confだけの編集ではポート変更できないっぽい。

✅その他の注意

SQL文の最後は;を入れること。

逆にデータベースの確認時などは;が要らない。

# \l

おわりに

トランザクションやビューなどSQLの発展的な内容にはまだあまり手を付けていない。難しい。。。

が、個人開発ではなく実用レベルの開発ではデータ量も増えるなるはずなので、効率性などを考えてSQLは必然的に必要になる知識だろうなと感じた。

また、今までphpMyAdmin(GUI)に頼りっぱなしだったので、しばらくは敢えてコマンドラインで操作してSQL文を身に付けたい。

※PostgreSQLとMySQLではコマンドや仕組みも若干違うのでそこら辺の理解も徐々にしていきたい。

参考

psqlのオプションの使い方とオプション一覧

Windows向けPostgreSQL用クライアントアプリケーション３選

PostgreSQLの基本的なコマンド