RDBMS:検索結果の集約と並び替え

下記のようなテーブルからデータを選択したい時に使用するのが、SELECT文だった。 image.png

今回は、さらにテーブルデータを集約したり、並び替えなどをする方法について学んだ。

集約関数

テーブルのデータを集計したりする際に用いるのが集約関数(または集合関数)。とりあえず、COUNT,SUM,AVG,MAX,MINの5つを覚えておけば、ひとまず良いみたい。

COUNT:テーブルのレコード数(行数)を数える

  • コマンド:SELECT COUNT(引数) FROM テーブル名;
  • 実行例:image.png 引数が*の場合はNULLもカウントされるが、列を限定するとNULLは除外してカウントされる。

SUM:合計値を求める

  • コマンド:SELECT SUM(引数) FROM テーブル名;
  • 実行例:image.png SUMの対象列にNULLがあった場合は、当該データを除外した上で結果が返される。

AVG:平均値を求める

  • コマンド:SELECT AVG(引数) FROM テーブル名;
  • 実行例:image.png SUMの対象列にNULLがあった場合は、当該データを除外した上で結果が返される。 具体的には、hanbai_tankaを例にすると、下記の計算がされているということ。 sql SUM(shiire_tanka)/COUNT(shiire_tanka) -- 2035.0(12210/6)

    NULLを値0とみなして、計算させる方法もあるが、第六章で学ぶ。

MAX,MIN:最大値・最小値を求める

  • コマンド(MAX):SELECT MAX(引数) FROM テーブル名;
  • コマンド(MIN):SELECT MIN(引数) FROM テーブル名;
  • 実行例:image.png

重複値を除外して集約関数を用いる

  • コマンド:SELECT 集約関数(DISTINCT キーワード) FROM テーブル名
  • 実行例:image.png

GROUP BY:グループに分ける

  • コマンド:SELECT カラム名 FROM テーブル名 GROUP BY 集約キー(グループ化列)
  • 実行例1:image.png
  • 実行例2:WHERE句を使う image.png

HAVING:集約結果に条件を指定する

WHEREとの違いは下記。

句名 説明
WHERE 行に対する条件指定
HAVING グループに対する条件指定

要はグループ分けした後のものに対して条件指定をしたい場合は、HAVINGを用いると覚えておけば良さそう。よってHAVINGGROUP BYの後ろに記述する。

  • コマンド:SELECT カラム名 FROM テーブル名 GROUP BY カラム名 HAVING 条件
  • 実行例:image.png

ORDER BY:並び替え

ORDER BY句はSELECT文の最後に書く。昇降については、キーワードを設定する。昇順はASC、降順はDESCというキーワードを設定する。省略した場合は、実務的には昇順の使用率が高いと言う理由から、暗黙的に昇順になる。

  • コマンド:SELECT カラム名 FROM テーブル名 ORDER BY 基準列
  • 実行例1:降順image.png
  • 実行例2:複数ソートキーを設定image.png
  • 実行例3:別名を使うimage.png

参考

RDBMS:SELECT文を理解する

SELECT文の基本

下記のようなテーブルからデータを選択したい時に使用するのが、SELECT文。 image.png

全ての列を出力

  • コマンド:select * from テーブル名;
  • 実行例:image.png

特定の列を出力

  • コマンド:select カラム名 from テーブル名;
  • 実行例:image

列に別名をつける

  • コマンド:select カラム名 as 別名 from テーブル名
  • 実行例:image.png

重複行を省く

  • コマンド:select distinct 対象カラム名 from テーブル名
  • 実行例:image.png
  • 備考:NULLもデータとして扱われる

コメント

  • 1行コメント:--の後ろに記述
  • 複数行コメント:/**/で囲む

行を選択する

  • コマンド:select カラム名 from テーブル名 where 条件式
  • 実行例:image.png

算術演算

SQL文で使える四則演算は、足し算(+)、引き算(-)、掛け算(*)、割り算(/)で、これらをまとめて算術演算子と呼ぶ。

算術演算子を用いる時の注意点として、演算するセルがNULLの場合、演算結果もNULLになるという点。ただ5 + NULL = 0のようにNULLを0とみなしてほしいというときの対象方法もある。それは六章で。

image.png

比較演算

両辺に記述した列や値を比較する記号を比較演算子と呼ぶ。使える主な演算子=,<>,>=,>,<=,<で、<>は等しくないという意味を指す。

基本的な使い方は下記のような感じ。

  • コマンド:where句と組み合わせて使う
  • 実行例:image.png

NULLで分ける

  • コマンド:where句と組み合わせて使う
  • 実行例:image.png

論理演算子

SQL文でもAND演算子とOR演算子が使える。いずれも複数の条件式を組み合わせる際に使う。

  • コマンド:where句と組み合わせて使う
  • 実行例:image.png

参考

データベースとSQL

データベースの構成

RDBMS(Relational DataBase Management System)のシステム構成は、一般的にクライアント/サーバ型になっている。データベースに読み書きにするために、クライアントはサーバに対してSQL文を送信する。

image.png

項目 説明
クライアント データベースを利用するプログラム
サーバ データベースを読み書きするプログラム
データベース ハードディスクなどに保存されているデータ
SQL 「どんなデータが欲しいか」や「どこのデータを書き換えて欲しい」などのSQLで書いた文

テーブルの構成

RDBMSでは、2次元の表でデータを管理していて、この表のことをテーブルと呼び、下記のような単語が使われている。 image.png

RDBMSでは、レコード単位でデータを読み書きする。

SQLの概要

SQL(Structured Query Language)はデータベースを操作するために開発された言語で、最近はISOによって定められた標準規格に準拠した標準SQLのサポートが進んでいる。ただ、RDMBSごとに微妙な差異がある部分もあるため、一部は方言に則った書き方をする必要がある。

SQLの文は大きく3種類に分類される。

  • DDL(Data Definition Language):データベースやテーブルなどの作成や削除を行う。DDLに分類される命令は次の通り。

    命令名 説明
    CREATE データベースやテーブルなどの作成
    DROP データベースやテーブルなどの削除
    ALTER データベースやテーブルなどの構成変更
  • DML(Data Manipulation Language):テーブルの行を検索、変更を行う。DMLに分類される命令は次の通り。

    命令名 説明
    SELECT テーブルからレコードを検索
    INSERT テーブルに新規レコードを登録
    UPDATE テーブルのレコードを更新
    DELETE テーブルのレコードを削除
  • DCL(Data Control Language):データベースに対して行なった変更を確定したり取り消したり、ユーザの権限変更なども行う。DCLに分類される命令は次の通り。

    命令名 説明
    COMMIT データベースに対して行なった変更の確定
    ROLLBACK データベースに対して行なった変更の取り消し
    GRANT ユーザに操作権限を与える
    REVOKE ユーザから操作権限を奪う

何はともあれ、テーブルを作ってみる。

docker exec -it postgres bash # postgreコンテナに入る
psql -U postgres -d shop # postgreを起動してshopデータベースに接続する

下記コマンドを実行してテーブルを作る。

CREATE TABLE Shohin
(shohin_id CHAR(4) NOT NULL,
shohin_mei VARCHAR(100) NOT NULL,
shohin_bunrui VARCHAR(32) NOT NULL,
hanbai_tanka INTEGER  ,
shiire_tanka INTEGER  ,
torokubi DATE   ,
PRIMARY KEY (shohin_id));

作ったテーブルの構成を確認する。

\d shohin;
                          Table "public.shohin"
    Column     |          Type          | Collation | Nullable | Default 
---------------+------------------------+-----------+----------+---------
 shohin_id     | character(4)           |           | not null | 
 shohin_mei    | character varying(100) |           | not null | 
 shohin_bunrui | character varying(32)  |           | not null | 
 hanbai_tanka  | integer                |           |          | 
 shiire_tanka  | integer                |           |          | 
 torokubi      | date                   |           |          | 
Indexes:
    "shohin_pkey" PRIMARY KEY, btree (shohin_id)

テーブルにレコードを登録する。

BEGIN TRANSACTION;
INSERT INTO shohin VALUES ('0001', 'Tシャツ','衣服',1000,500,'2009-09-20');
COMMIT;

テーブルデータを確認する。

select * from shohin;
 shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi  
-----------+------------+---------------+--------------+--------------+------------
 0001      | Tシャツ    | 衣服          |         1000 |          500 | 2009-09-20
(1 row)

参考

PostgreSQLをコンテナで立てる

PostgreSQLの環境構築

ゼロからはじめるデータベース操作を進めるためにPostgreSQLの環境構築を進めた。本の中ではWindowsの説明があったけど、せっかくなのでさくらVPSで立てたサーバで環境を構築してみました。

最近はDockerで環境構築するのがめっちゃ楽と思っているので、debianサーバにDockerを入れて、コンテナとしてPostgreSQLの環境構築をしました。

Docker Engine インストール(Debian 向け)

公式を参考に、Dockerをインストールする。

  1. リポジトリのセットアップ

    1. HTTPS経由でリポジトリにアクセスしパッケージをインストールできるようにする

      sudo apt-get update
      sudo apt-get install \
      ca-certificates \
      curl \
      gnupg \
      lsb-release
      
    2. Docker の公式 GPG 鍵を追加

      curl -fsSL https://download.docker.com/linux/debian/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg
      
    3. 安定版(stable)リポジトリをセットアップ

      echo \
      "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/debian \
      $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
      
  2. Docker Engine のインストール

    1. 最新版の Docker Engine、containerd、Docker Compose をインストール

      sudo apt-get update
      sudo apt-get install docker-ce docker-ce-cli containerd.io docker-compose-plugin
      
    2. Docker Engine が正しくインストールされているかの確認

      sudo docker run hello-world
      

Docker Composeを入れる

Docker Composeでコンテナを作るためにdocker-composeを使えるようにする。

  1. Docker Compose 最新版をダウンロード

     sudo curl -L https://github.com/docker/compose/releases/download/1.16.1/docker-compose-`uname -s`-`uname -m` -o /usr/local/bin/docker-compose
    
  2. 実行権限を付与

     sudo chmod +x /usr/local/bin/docker-compose
    
  3. インストールを確認

     docker-compose --version
    

コンテナを立てる

【Docker】postgresqlの構築を参考に、下記のようにdocker-compose.ymlを記述。

version: '3'

services:
  db:
    image: postgres:latest
    container_name: postgres
    ports:
      - 5432:5432
    volumes:
      - db-store:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=password
volumes:
  db-store:

コンテナを立てる。

sudo docker-compose up -d

コンテナを起動して、接続確認をする。

sudo docker exec -it postgres bash #コンテナへ接続
psql -h localhost -U postgres #psqlで接続確認

nginxで複数サーバを稼働する

nginxの設定ファイルにserverディレクティブを複数立ててあげる。それぞれに設定を書いてあげればよい。

server {
    listen       443 ssl;
    server_name  goruchan.net;
    ssl_certificate ***/fullchain.pem; # managed by Certbot
    ssl_certificate_key ***/privkey.pem; # managed by Certbot

    access_log  ***/host.access.log  main;
    error_log ***/error.log debug;

    location / {
        root   ***/public;
        index  index.html index.htm;
    }

    #error_page  404              /404.html;

    # redirect server error pages to the static page /50x.html
    #
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   /usr/share/nginx/html;
    }
}
server {
    listen       443 ssl;
    server_name  goruchan.shop;
    ssl_certificate     ***/goruchan.shop.crt;
    ssl_certificate_key ***/goruchan.shop.key;

    access_log  ***/host.access.log  main;
    error_log ***/error.log debug;

    location / {
        root   ***/public;
        index  index.html index.htm;
    }

    #error_page  404              /404.html;

    # redirect server error pages to the static page /50x.html
    #
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   /usr/share/nginx/html;
    }
}

上記を書いたら、下記を実行して設定を反映する。

sudo nginx -s reload

サーバ証明書の種類

証明書は、サイトを証明するための認証方法の違いによって種類が異なり、3種類ある。 暗号強度に関しては違いはない

名称 認証方法 費用
DV ドメイン所有のみ証明 0〜数万円
OV ドメイン所有、会社実在性を証明 数万円〜
EV ドメイン所有、会社実在性、
電話認証(在籍確認、会社運用状況等)を証明
十数万円〜

それぞれのサイトの違いは下記で確認できる。

  • DV:Domain Validation
    ドメイン所有のみなので、証明書の情報は薄い。 DV.png
  • OV:Organization Validation
    ドメイン所有に加えて、会社実在性が必要なため会社情報がある。 OV_1.png OV_2.png
  • EV:Extended Validation
    ドメイン所有に加えて、会社実在性必要なため会社情報がある。さらに発行先情報に会社名が表示されている。 EV_1.png EV_2.png

上記の通り、OVとEVを区別するには発行先部分に会社名があるかどうかで判断できる。ちょっと前にはURLに緑色マークでEVであることがわかったようだけど、非表示になったらしい。 green.png

理由としては、ユーザはEVであるかを気にしていないし、逆に緑色部分を間違ってクリックしてしまうことなどもあった。 また、EV証明書を取得するには、登記簿の提出など組織の実在を証明する厳しい手続きと高い費用が必要だが、証明書を取得する組織が、法的に問題ない業務を行っているかどうかは認証局チェックの範囲外のため、条件さえ揃えば悪意あるサイトも取得できてしまう。 そういった理由から、過度の信頼を植え付けないように非表示になったという話もあるようです。

Google Chrome EV表示の終焉

Let’s Encryptでサーバ証明書を作って、nginxのサイトをHTTPS対応する

snapdのインストール

Let’s Encryptを使うにはACME プロトコルというルールを使用するソフトウェアを使う。Certbot という ACME クライアントを使うことを公式では推奨している。このCertbotを使うためにsnapdをインストールする。 Installing snap on Debian

sudo apt update
sudo apt install snapd
exit # 一旦ログアウトしてPATHを更新する
ssh -p xxx username@xxx.yyy.zzz.*** #再ログイン
sudo snap install core

snapがインストールできたかの確認する

sudo snap install hello-world
hello-world #=> Hello World!

Certbotのインストール

snapdの最新版を取得する。

sudo snap install core; sudo snap refresh core

パッケージインストーラCertbotをインストールしてしまっている場合は、パッケージを削除し、snapdでCertbotが実行されるようにする。

sudo snap install --classic certbot 

Certbotのコマンドを実行できるようにリンクを貼る

sudo ln -s /snap/bin/certbot /usr/bin/certbot

Let's Encryptに証明書申請する

下記コマンドを叩くだけで、証明書が発行され、Certbot が nginx の設定を自動的に編集して証明書を提供し、ワンステップで HTTPS アクセスを有効にすることができる。めちゃ簡単😲

sudo certbot --nginx

上記コマンドを実行すると、メールアドレスやら利用規約の同意を求められるので回答する。『メール送っても良いか?』みたいなものはNoにしておきました。

サイトにアクセスしてみると無事に鍵マーク付きのサイトになりました😄 https.png