SQLの基礎を総まとめ

プログラミング初めて4ヶ月。
広く浅く勉強しています。

#SQLとは
SQLは、Structured Query Languageの略です。

プログラミングの技術があるひとも技術がない人も、リレーショナルデータベースからデータを参照、操作、変更するために作られた言語です。

#リレーショナルデータベース(関係データベース)とは
エクセルで作った表みたいな感じです。
関連する表の集合をあらわします。

各テーブルは、名前がついた列とデータが入っている行があります。
例はウィキペディアにも書いているので、みてみてください。

関係データベース(wikipedia)

#SELECT文
ここでは、SELECTとFROMを説明します。

SELECTは、列(レコード)を選択します。
FROMは、表(テーブル)を選択します

特定の列のクエリを選択する場合

SELECT name, age FROM consumer;

consumerっていうテーブルから、nameとageの列を選択しました。

全ての列を選択したい場合は、「*」この記号を使って
SELECT * としてあげればオッケーです。

SELECT DISTINCT name のように、DISTINCTを入れると重複しているやつも1つだけ表示してくれる。

#WHERE
先ほど行ったのは、「このテーブルのこの列を選択」でしたが、
次に行うのは、「このテーブルのこの列のこの条件に当てはまるものを選択」です。

これらの記号を使います。
= , !=, <, <=, >, >=

BETWEEN ... AND ...(ある数値とある数値の間)
IN ... (数字がリストに存在する)
NOT IN... (数字がリストに存在しない)
LIKE(大文字小文字を区別しない文字列)
NOT LIKE(大文字小文字を完全に区別する文字列)
%(0個以上の任意の文字 LIKEやNOT LINEの時だけ)　AN%なら、ANで始まるものならなんでも返してくれる
_(ある任意の一文字)

も使います。

例えば、顧客番号のデータがあれば、
「顧客情報」というテーブルの「顧客番号」という列にある、「1番から10番まで」を選択とすると、BETWEEN ANDが使えます。

例

SELECT number FROM consumer WHERE BETWEEN 1 AND 10;

#ソート(ORDER BY)

ASC...昇順
DESC...降順
何も指定しなかったら、勝手に昇順になります。

例

SELECT number FROM consumer ORDER BY number DEST;

次にLIMITとOFFSETを紹介します。
LIMITは、表示する表示する行数を指定するもので、
OFFSETは、表示を始める場所を決めることができます。

例

SELECT number FROM consumer ORDER BY number DEST
LIMIT 5 OFFSET 5;

#データベースの正規化
データベースの正規化を行うと、データの一貫性が保たれ、効率的にデータを管理できます

例えば、顧客情報を管理する際に、住所が変わっただけなのに、同じ名前、同じ年齢、違う住所の情報があったら、面倒臭い。

##内部結合と外部結合
内部結合は、2つのテーブルがあった時に、同じカラムがないと使えないみたいです。
その同じカラムを紐づけて、データを結合するみたいな、、、

外部結合は、同じカラムがなくても、ぴってくっつけることができるやつです。

詳しくはこちら
SQL素人でも分かるテーブル結合(inner joinとouter join)

#NULLに関して
nullは、データが入っていないという意味
NULLを探す場合は、WHERE カラム名 IS NULL
NULLが入っていないものを探す場合は、 WHERE カラム名 IS NOT NULL

#式を使ったクエリ
SELECT文には、ASが使える
AS...列の名前を変更します。

SELECT number AS consumer_number ...;

国内の売り上げ(d_sales)と海外の売り上げ(i_sales)のデータがある時に
合計して、a_salesというカラムを作りたいときは

SELECT d_sales + i_sales AS a_sales FROM ...

#集計関数
count(*)... 全てのレコード数を取得
count(列名)... 列名を指定すると、その列のNULL以外の要素数を取得
count(DISTINCT 列名)...その列の重複を含まない要素数を取得

その列の、特定のまとまりの数を知りたい場合は、SELECTで調整する
(従業員のうち20歳の人数をカウント)

SELECT age, COUNT(*) as age_of_employee
FROM employees
WHERE age = 20;

Min(列名)...その列の最小値を取得
Max(列名)...その列の最大値を取得
AVG(列名)...その列の平均値を取得
SUM(列名)...その列の合計を取得

仕事の役割ごとの従業員の勤務年数が知りたい場合、

SELECT role, AVG(years) as Ave_years
FROM employees
GROUP BY role;

GROUP BY　は集計する際に、このまとまりで集計してほしいと要求できるもので、この場合は、「仕事の役割ごと」っていう要求に対応して、GROUP BY roleとします。

HAVINGとは・・・

GroupByでグルーピングする前に抽出するのがWhere句で
GroupByでグルーピングした後に抽出するのがHaving句になります。

Where句でもHaving句でも同じ結果が出るけど何が違うんだろう？

HAVINGを知りたい方は、こちらのURLにとんでみて下さい。

WHERE ...GROPUBYより先にWHEREが実行される
HAVING...GROUPBYより先に　HAVINGが実行される

こんな感じの解釈かな？？

#実行順序

SELECTとか、WHEREとか色々あるけど、順序を一旦整理しましょう。

1. FROM, JOIN
   どのテーブルを参照するか、テーブルの結合を実行

2. WHERE
   選択する要素の条件を指定する

3. GROUP BY
   指定された列の共通に基づいてグループ化

4. HAVING
   GROUPBYがある場合、GROUPBYを行なってグループ化された後に、条件を指定して、条件に合わない要素を破棄する

5. SELECT
   SELECTで四則計算などの式で計算を行なった場合、それが実行される

6. DISTINCT
   重複するものを破棄する

7. ORDERBY
   昇順や降順でソートされる

8. LIMIT, OFFSET
   LIMITで指定した範囲の外の要素を破棄し、OFFSETで指定した範囲の外の要素を破棄する

#行(新しいデータ)の挿入 INSERT

INSERT INTO テーブル名(列名, 列名) VALUES (データ, データ, データ...)

これはもうSELECTとか関係なくて、これ単体で使います。

INSERT INTO mytable VALUES (5, 30, "こんにちは")

#行の更新 UPDATE
UPDATEはすでにテーブルにある行を修正して、更新します。
まあ、書き直しですね。

UPDATE　テーブル名
SET 列名=値, 列名=値...
WHERE 条件

UPDATE Mytable
SET age=20
WHERE id=5

これは、idが5の人のagemを20に修正せよっていう指令ですね。

#行の削除 DELETE
すでにテーブルの中にある行を削除します。

DELETE FROM テーブル名
WHERE 条件

DELETE FROM Mytable
WHERE age < 20

これは、Mytableから、20歳未満の人のデータ(行)を削除します。

#テーブルの作成 CREATE TABLE
CREATE TABLE Mytable2
列名 データ型　NULL/NOT NULL

データ型まとめ
INTEGER　...整数値
BOOLEAN... 真偽値(TRUE or FALSE)
FLOAT, REAL ,DOUBLE... 浮動小数点型
CHARACTER, VARCHAR, TEXT ...テキスト型
DATE, DATETIME ... 日付や時刻
BLOB ... 画像、音声、動画など、テキストでも数値でもないもの

テーブルの制約まとめ
PRIMARY KEY ... テーブルの中でレコード(行)を一つに特定できるカラム(列)のこと。普通のやつ
AUTOINCREMENT...カラムに値が指定されなかった場合に、自動的に値が振り分けられる。データ型は整数。IDみたいに、指定しなくても自動で割り振ってほしいやつに使う

UNIQUE...重複した値が入力されないように制約する
NOT NULL...NULLを格納することはできない
CHECK ... 格納されたデータをチェックして、格納された値が、指定された条件に当てはまるかどうかをテキストで返す。例えば、値が正であるか、特定の接頭辞で始まっているかなど

FOREIGN KEY...データを格納する際に、他のテーブルを参照したとすると、その他のテーブルが外部キーになる。他のテーブルとの整合性チェック

#列の追加

ALTER TABLE テーブル名　
ADD 列名　データ型
DEFAULT 初期値;

#列の削除

ALTER TABLE テーブル名　
DROP 列名　

#テーブル名の変更

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

#テーブルの削除

DROP TABLE テーブル名　

#(追記)正規表現をSQLで使う場合
HackerRankをしていて、SQLで正規表現の使い方が分からなかったので、まとめました。
これは、文字の終終わりが母音ではないものを抽出しています。
REGEXPを使えばいいんですね！！！

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP '[^aiueo]$'

#終わりに

だんだん後の方雑になってきたけど、こんな感じで終わりです！！