HTTPの歴史と概要をまとめてみた

HTTPは、Webでクライアントとサーバが通信するときに使用される
アプリケーション層のプロトコル。


Webの普及によってより多くの機能がHTTPに求められるようになってきた。

現在ではHTTP/1.1が主流。
HTTP/2やHTTP/3も実装が進んでいる。

【 HTTP/0.9 】

HTTP/0.9はサーバからデータを取得する機能しかなく、非常に簡素なプロトコルでした。
現在ではほとんど使われていない。

【 HTTP/1.0 】

HTTP/0.9に多少の機能が追加され、HTTP/1.0からRFCで正式に定義されるようになりました。
HTTP/1.0からは、HTTP/0.9と区別するためプロトコルにバージョンを含めるようになりました。

まだ機能が不足していたのと、そのあとに発表されたHTTP/1.1が主流となって
現在ではほとんど利用されていない。

【 HTTP/1.1 】

HTTP/1.0発表からしばらく経って、HTTP/1.1が発表されました。
その後2回の改定を経て今に至ります。
HTTPの現在の主流のプロトコルで、通常HTTPといえばHTTP/1.1を指すことが多いです。

【 HTTP/2 】

HTTP/2はHTTP/1.1と後方互換性を維持したまま、高速な通信ができるプロトコルになっている。
クライアントが対応していればHTTP/2による通信をして、
クライアントが対応していなければHTTP/1.1の通信をするので共存ができます。

ヘッダの圧縮や、パイプラインと呼ばれる通信の多重化技術を使って、
HTTP/1.1よりも高速な通信を実現しています。

【 HTTP/3 】

HTTP/3はHTTP/2からさらに高速化を実現するために実装されるプロトコルです。
従来のHTTPはトランスポート層のプロトコルとしてTCPを使用していましたが、
HTTP/3では新たにQUICを使用しています。

QUICを使用することでTCPでは限界のあった通信速度改善が見込まれ、
HTTP/2よりも高速な通信が期待できます。

集合演算

UNION レコードの足し算

-- 二つのselectの結果が合わせて出力される
select id, price from soft
union 
select id, price from soft2;

-- ※注意点
--  列の数を揃えること
-- 列のデータ型を揃えること
-- order byは全体で一つだけ

-- where と order byを使う
select id, price from soft
where price > 200
union 
select id, price from soft2
where id <> '4'
order by price;


-- ALL で重複行を残す
-- union の後にallをつけると各テーブルのデータが重複しても取得する
select id, price from soft
union all
select id, price from soft2;

INTERSECT テーブルの共通部分

-- 各テーブルの一致したデータのみ出力
select id, price from soft
intersect 
select id, price from soft2
order by price;

EXCEPT レコードの引き算

-- 前者のテーブルから後者でも一致するレコード以外取得する。(前者レコード - 後者レコード = 結果)
select id, price from soft
except 
select id, price from soft2
order by price;

-- 注意点
-- 引き算のためどちらのテーブルを先に書くかで結果が変わるので注意

JOIN 結合

テーブルの列を連結する

-- INNERJOIN 内部結合
select s.name, s.price, c.name 
from soft as s 
inner join category as c 
on s.category = c.category_id;

-- where を使う
select s.name, s.price, c.name 
from soft as s 
inner join category as c 
on s.category = c.category_id
where s.id = '2';


-- OUTER JOIN 外部結合
-- outer join の前にleft  またはrightをつける
select s.name, s.price, c.name 
from soft as s 
left outer join category as c 
on s.category = c.category_id;


-- 注意点

-- INNER JOIN(内部結合) と OUTER JOIN(外部結合)の違い
-- 内部結合では２つのテーブルに存在しているデータを取得する
-- 外部結合では片方のテーブルのデータを全て取得する
-- 例えば外部結合ではデータがNULLでも取得する

-- LEFT と RIGHT の違い (OUTER JOIN)
-- LEFT か RIGHT のテーブル指定でどちらのテーブルをマスタにするか決める。
-- 機能的な差はないのでどちらでも良い

クロス結合

-- データ数  ✖️ データ数 が結果として取得する
-- 2つのテーブルのレコード全ての組み合わせを作る
select s.name, s.price, c.name 
from soft as s 
cross join category as c ;

SQL 使いやすい関数とCASE式

文字列連結

# || で文字列連結
select name, category, name || category as name_category from soft;

日付系

# 現在の日付を取得
select current_date;

# 現在の時間
select current_time;

# 現在の日時
select current_timestamp;

# extract
# 日付要素の切り出し 戻り値数値なので注意
select current_timestamp as now, 
extract(year from current_timestamp) as year,
extract(month from current_timestamp) as month,
extract(day from current_timestamp) as day,
extract(hour from current_timestamp) as hour,
extract(minute from current_timestamp) as minute,
extract(second from current_timestamp) as second;

COALESCE (コアレス)

左から順に引数を見て、最初にNULLでない値を返す。

select coalesce (null, 1) as col_1, coalesce (null, 'abc', null) as col_2, coalesce (null, null, '12345678') as col_3;

LIKE (あいまい検索)

# 前方一致
select * from soft where name like '%a';


# 中間一致
select * from soft where name like '%a%';


# 後方一致
select * from soft where name like 'a%';

BETWEEN (範囲検索)

select * from soft where price between 200 and 1000;

# 以下と同じ
select * from soft where price >= 200 and price <= 1000;

IN (ORの省略形)

select * from soft where price in (200, 400, 800);

# 以下と同じ
select * from soft where price = 200 or  where price = 400,  where price = 800;

# NOTにする
select * from soft where price not in (200, 400, 800);

CASE式

条件分岐をする

# 構文

CASE 
WHEN <評価式> THEN <式> 
WHEN <評価式> THEN <式>
WHEN <評価式> THEN <式> 
WHEN <評価式> THEN <式> 
ELSE <式>
END

使う

select * , 
case 
when category = 'Editor'
then 'Eから始まる' || category || 'ですよ'
when category = 'IDE'
then 'Iから始まる' || category || 'ですよ'
else null
end as if_category
from soft;

※endを忘れないように。

SQL ビューとサブクエリ

ビューとは仮装のテーブル よく使うSELECT文を保存できる。 そして、仮想的なテーブルを作る。

ビューを作る

# as の後にselect文を記載する
create view soft_max (category, price_max)
as select category, MAX(price) from soft group by category; 

作成したビューを取得する

# fromのテーブルの名前の位置にビューの名前を指定する。
select * from soft_max;

# ビューに条件式を使う。
select * from soft_max where category = 'IDE';

ビューを一覧表示する

select table_name from INFORMATION_SCHEMA.views WHERE table_schema = ANY (current_schemas(false));

ビューを削除する

drop view soft_max;

※ビューはテーブルの最新データを取得する。逆にビューを更新するとテーブルのデータも更新される。 ビュー定義にはorderbyは使用できない。

サブクエリ

# fromにselect文をいれる
select  category, max_price
from (select category, MAX(price) as max_price from soft group by category) as soft_max; 


# 階層が深いサブクエリ
select  category, max_price
from (select * from (select category, MAX(price) as max_price from soft group by category) 
as soft_max where max_price > 1000) as soft_max_filter; 

スカラサブクエリ

戻り値が単一になる

# where句によく使う
# (select ~)をスカラサブクエリという
select id, name, price from soft 
where price > (select avg(price) from soft);

# 定数としても使える
select id, name , price , (select min(price) from soft) as min_price from soft;

※複数行を返さないようにする

相関サブクエリ

スカラサブクエリでは一行しか返せないが、複数行返すことができる

# 各categoryごとの平均を超えるpriceをもつ行を返す
select id, name, price, category from soft as s1 
where price > (select avg(price) from soft 
as s2 where s1.category = s2.category group by category);

SQL CRUDとトランザクション

INSERT データを挿入する

# 1行のINSERT
insert into soft (id, name, price, category) values ('8', 'chrome', 800, null);

# カラムの指定なしでも可能
insert into soft  values ('9', 'phpstorm', 1000, 'IDE');

# 複数行のINSERT 
#  values につなげて記述
insert into soft  values ('10', 'webstorm', 1100, 'IDE'), ('11', 'memo', 1200, 'Editor');

# DEFAULTの値を明示的にする
insert into soft  values ('10', 'webstorm', 1100, DEFAULT);

他のテーブルからデータをコピーする

# 事前にテーブルを作成する必要あり
# soft_secondというテーブルを作成したと仮定 
# select の結果が挿入される
insert into soft_second (second_id, second_name, second_price, second_category)
select id, name, price, category from soft;

DELETE データを削除する

# テーブルを残したまま、全ての行を削除するDELETE文
DELETE FROM soft;

# 該当の行だけ削除するDELETE
# idが11だけ削除する
delete from soft where id = '11';

UPDATE データを更新する

# 全て更新
# 全てのデータのpriceが1000に更新
update soft set price = 1000;

# 該当のみ更新
# priceが500以上のデータのpriceを2倍にする。
update soft set price = price * 2 where price > 500;

#複数の更新
# カンマで区切る
update soft set price = price * 2, category = 'Editor'  where price > 500;

トランザクション

# COMMMITする (確定)
BEGIN TRANSACTION;

UPDATE soft set price = 1000;

COMMIT;


# ROLLBACKする (取り消し)
BEGIN TRANSACTION:

UPDATE soft set price = 1000;

ROLLBACK;

SQL 集約関数とGROUPBY

INSERTサンプル

BEGIN TRANSACTION;

INSERT INTO soft VALUES ('1', 'eclipse', '100');
INSERT INTO soft VALUES ('2', 'vscode', '200');
INSERT INTO soft VALUES ('3', 'visual studio', '300');
INSERT INTO soft VALUES ('4', 'Atom', '400');
INSERT INTO soft VALUES ('5', 'pycharm', '500');
INSERT INTO soft VALUES ('6', 'sublime text', '600');
INSERT INTO soft VALUES ('7', 'DBeaver', '700');
 
COMMIT;

SELECT

基本形

# 全て
select * from soft;

# 列を指定 ※idとpriceが列名
select id, price from soft;

ASで列に別名をつける

# idにsoft_idという名前を付けている
# 日本語を使用する場合は、""(ダブルクオーテーション)で囲む。
select id as soft_id, price as "値段" from soft;

# 定数の出力なども可能
select 'おまけ' as omake,  id as soft_id from soft;

値の重複を省く

# 出力結果で重複が省かれる
select distinct name from soft;

条件式

# idを1に絞って出力
select * from soft where id = '1';

# NULLはis null となる
select price from soft where price is null;

算術と比較演算子

# price を300以上に絞って出力
select * from soft where price > 300;

# price を2倍で出力
select id, price * 2 as price_x2 from soft;

# 否定は<>を使う
# 300以外を取得
select *  from soft where price <> 300;

論理演算子

# NOT演算子 
# priceが400以上ではないという条件
select *  from soft where not price  > 400;

# AND
select * from soft where price > 300 and id <> '7';

# OR 
select * from soft where price > 300 or id = '1';

テーブルを集約

# COUNT 行数を数える
select count(*) from soft;
# NULLを除外するには、列を指定
select count(price) from soft;


# SUM 合計を取得
# priceの合計を取得する
select sum(price) from soft;


# AVG 平均値を取得
SELECT AVG (price) from soft;

# MAX 最大値を取得
# MIN 最小値を取得
SELECT MAX(price) , MIN (price) from soft;

# 重複値を省きつつ、集約する
select max(distinct price ) from soft;

説明用にカラムを追加

# 値は適当です。
alter table soft add column category char(10);

BEGIN TRANSACTION;

update soft set category = 'IDE' where id = '1';
update soft set category = 'Editor' where id = '2';
update soft set category = 'IDE' where id = '3';
update soft set category = 'Editor' where id = '4';
update soft set category = 'IDE' where id = '5';
update soft set category = 'Editor' where id = '6';
update soft set category = 'IDE' where id = '7';

COMMIT;

グループに分ける

# GROUP BY 
# 値が等しい行をグループ分けすることができる。
# この例では、categoryのIDEとEditorをグループ分けして、priceの最大値を取得している。
select category, MAX(price) from soft group by category;

# whereで条件式を使う場合は、条件で一致した値に対してGROUP BYが実行される。
select category, MIN(price) from soft where price >= 200 group by category;

HAVING

GROUP BYでグループ化した結果に条件を指定することができる。

# group byの後に記述する。
# categoryでgroup by したpriceに最大値が600で絞る
select category, MAX(price) from soft group by category having MAX(price) > 600;

並べ替え

order by

# asc 昇順 
#何も付けないと昇順
select * from soft order by price;

# desc 降順
select * from soft order by price desc;


#集約関数も使用可能
select category, MAX(price) from soft group by category order by MAX(price) desc;

SQL スキーマとかテーブルとか

PostgresをDockerで動かす

docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

docker exec -it some-postgres bin/bash

psql -U postgres

データベース一覧

SELECT* FROM pg_database;

現在のデータベースを確認

select current_database();

データベース変更

\c test;

データベース削除

drop database test;

バージョン確認

select version();

スキーマ一覧

select schema_name from information_schema.schemata;

現在のスキーマを選択

select current_schema();

作成

 create schema test;

スキーマを変更

SET search_path = test;

スキーマ削除

drop schema test;

テーブル操作

テーブル一覧

select  relname as TABLE_NAME  from  pg_stat_user_tables;
# または
SELECT * FROM pg_catalog.pg_tables;

テーブル作成

create table soft();
# カラム指定
create table soft(id char(4) NOT NULL, name char(30) NOT NULL, price INTEGER , PRIMARY KEY (id));

テーブル列を追加する ※softはサンプルのテーブル名

alter table soft add column created_at date;

テーブルの列を削除する

alter table soft drop column created_at;

テーブル削除

drop table soft;

カラム一覧取得

SELECT * FROM information_schema.columns where table_name='soft' order by ordinal_position;

※命名ルール データベースやテーブル、列といった名前に使える文字は、半角文字のアルファベット 数字、アンダーバーに限られる。 また、名前の最初は半角のアルファベットで始める。

コメントの書き方

# 一行のみ
-- これはコメント

# 複数行
/*
これはコメント
これはコメント
*/