SQLについてまとめたみた

#概要

現在SQLを勉強しており、アウトプットとして記事をまとめます。

#SQLとは
データベースやデータを操作するための専門言語です。
SQLで書かれた命令をDBMSに送信することで、データの検索・更新・削除・追加などを行うことができる。

#DBMS(データベース管理システム)
コンピューター内で常に稼働してSQLを待つ。
届いたSQLの内容に従って、データバースファイルの内容を検索したり、
書き換えたりする。

DBMSにとって並び替えはとても負荷が高いためORDER BY, DISTINCT, UNIONを使用する際は注意が必要。

#データベース
検索や書き換え、分析などのデータ管理を目的として様々な情報を蓄積したもの。
表形式でデータ管理するものをRDB(リレーショナルデータベース)という。

#RDB(リレーショナルデータベース)
複数の表が入っており、ここの表をテーブル(table)という。
ここのテーブルには名前(テーブル名)がついている。
テーブルは列と行で構成されている。
一つの行が一件のデータに対応し、列はそのデータの要素に対応する。

リレーショナル型データベース >PostgreSQL MySQL SQLite Microsoft SQL server oracle db2

#SELECT文
テーブルから目的のデータを指定して取得する
SELECTの後に列名(カラム名)を入れると指定したカラムを取得する。

#INSERT文
テーブルに新しいデータを追加する

#UPDATE文
テーブルのデータを修正する

#DELETE文
テーブルのデータを削除する

#ASによる別名定義
SELECT文において列名やテーブル名の指定で、それぞれの記述の後ろに「AS+任意のキーワード」をつけることで別名を定義することができます。

#四大命令(SELECT、UPDATE、DELETE、INSERT)に共通すること

#WHERE文
・処理対象行の絞り込みに用いる
・SELECT,UPDATE,DELETEで使用可能
・WHEREの後ろには条件式を記述する
・条件式がTRUEとなる行だけが選ばれる(FALSE UNKNOWN)処理対象にならない

SELECT フィールド名
FROM テーブル名
WHERE 条件式

#NULLの判定

NULLとは
・そこに何も格納されていない、未定義であることを表す。
・数字の０や空白文字とも異なる。

/* NULLであることの判定 */
SELECT * FROM movies WHERE title IS NULL;
/* NULLでないことの判定 */
SELECT * FROM movies WHERE title IS NOT NULL;

#LIKE演算子
文字列があるパターンに合致しているかをチェックする時に使う。部分一致の検索。

パターン文字	意味
%	任意の0文字以上の文字列
_(アンダースコア)	任意の1文字

SELECT title, director FROM movies 
WHERE title LIKE "Toy Story%"

Toy Story%はToy Storyから始まる文字列、%Toy StoryはToy Storyで終わることを%Toy Story%は前後に文字がついていても良いと意味します。

#BETWEEN演算子
ある範囲内に値が収まっているか判定します。
yearの列が2000以上2010以下の範囲にある行のみを検索する

SELECT title, year FROM movies
WHERE year BETWEEN 2000 AND 2010;

#IN演算子/ NOT IN演算子
値がカッコ内に列挙した複数の値のいずれかに合致するかを判定する。
(=演算子では1つの値としか比較できませんが、IN演算子は一度にたくさんの値と比較できます。)

カッコ内に列挙した値のどれとも合致しないことを判定するためにNOT IN演算子を使う

#論理演算子(AND, OR, NOT)
1つの条件式でうまく行を絞り込めない場合、複数の条件式を組み合わせることが可能。
複数の論理演算子を使用する場合、
(1) NOT ( WHERE NOT title = 'Toy Story'という記述でToy Story以外の行を取り出す)
(2) AND ( 2つの条件式の両方が真の場合だけTRUE)
(3) OR ( 2つの条件式のどちらかが真の場合TRUE)

の優先順位に従って処理されますので注意が必要。
なので( )を使うことで優先順位を変えることができる(イメージとしては四則演算かな)

#DISTINCT
SELECT文に付加すると、結果表の中で内容が重複している行があれば、その重複を取り除く。

#ORDER BY
指定した列の値を基準として、検索結果を並び替えて取得する。

	並び順
ASC	昇順
DESC	降順

#表示が逆になる
SELECT DISTINCT director FROM movies
ORDER BY director DESC;

#複数の列で並び替えの場合

SELECT DISTINCT director FROM movies
ORDER BY director DESC, title DESC;

#LIMIT句
検索結果の全行ではなく、先頭の数件だけを取得したりに使う
ただし、Oracle DBには存在しない。

SELECT title FROM movies
ORDER BY title ASC
LIMIT 5 OFFSET 5  ←#OFF SETは先頭を除外するため　6番目から10番目まで取得する

#集合演算子(UNION, EXCEPT, INTERSECT)
構造がよく似た複数のテーブルにSELECT文をそれぞれ送り、その結果を組み合わせる
SELECT命令によって抽出した結果表を一つのデータの集合と捉え、足し合わせたり、共通部分を探したりをする。

UNION

2つのSELECT文をUNIONでつなぎ、検索結果を足し合わせた結果にする。 ※集合演算子を使うためには、それぞれのテーブルの列数とデータ型をぴったりと一致させる必要がある。 列数とデータ型さえ一致指定していれば、全く異なるデーブルや列もひとまとめにして抽出することができる。

SELECT title,director FROM movies UNION 
SELECT title,director FROM employees ORDER BY 2,1

EXCEPT

差集合。ある集合と別の集合の差。

SELECT title FROM movies EXCEPT
SELECT title FROM employees;

INTERSECT

積集合。2つのSELECT文に共通する行を集めた集合。

SELECT title FROM movies INTERSECT SELECT title FROM employees

#CASE演算子
列の値や条件式を評価し、その結果に応じて好きな値に変換する。

SELECT **, **
       CASE 評価する列や式　WHEN 値1 THEN 値1の時に返す値
　　　　　　　　　　　　　　  WHEN 値2 THEN 値2の時に返す値
　　　　　　　　　　　　　　　ELSE デフォルト値
       END

SELECT ***,***
       CASE WHEN 条件1 THEN 条件1の時に返す値
            WHEN 条件2 THEN 条件2の時に返す値
            ELSE デフォルト値
       END AS **
FROM ****
WHERE ** > 0

#JOIN
結合するキーとなる列名を指定することで、共通の値をもつ行同士を連結する
JOIN句によってMoviesテーブルにBoxOfficeテーブルが結合され
ON句の結合条件により下図のように結合する。

#LEFT JOIN(左外部結合)
左外部結合は左のテーブルを基準にして結合する。
※結合相手の行がない場合や左表の結合条件列がNULLの場合、選択列リストに抽出される右表の列は全てNULLとなる

#RIGHT JOIN(右外部結合)
右外部結合は右のテーブルを基準にして結合する。
NULLの行を生み出しても、左表の全行を必ず出力する処理。

SELECT DISTINCT building_name, role 
FROM buildings 
 RIGHT JOIN employees
    ON building_name = building

#FULL JOIN(完全外部結合)
完全外部結合は、左、右表の全行を必ず出力します。

SELECT ~
FROM 左テーブル名
 FULL JOIN 右テーブル名
    ON 結合条件

COUNT関数

検索結果の行数を数えてくれる集計関数。

COUNT(*) → 検索結果の行数(単純に行数をカウントする。NULLの行も含める)
COUNT(列) → 検索結果の指定列に関する行数(指定列の値がNULLである行を無視してカウントする)

MIN

各行の値の最小値を求める。

MAX

各行の値の最大値を求める。

SUM

各行の値の合計を求める

AVG

　各行の値の平均値を求める

GROUP BY(グループ化)

グループごとに行われ、グループごとの集計結果が結果表の形で得られます。

SELECT role
FROM employees
group by role

roleカラムをグループごとにまとめたもの。

HAVING

集計処理を行ったあとの結果表に対して絞り込みを行いたい場合に使う。

*SQLが行う順序があり、GROUPBY使用後にWHEREは使えない

FROM → WHERE → GROUPBY → HAVING → SELECT → ORDERBY

SELECT role, SUM(years_employed)
FROM employees
GROUP BY role
HAVING role = "Engineer";