SQLとは？基本構文の書き方

SQLとは

SQLとは、データベース管理システム（DBMS）を操作するための言語のこと。
上述した通り、SQLには共通規格となっており、DBMSを利用しても共通の構文で操作できることが特徴のひとつ。

SQLでできること

・データの保管、取得、更新、削除
・テーブルの作成、更新、削除
・アプリ開発時にデータベースを操作できる。
・データ分析をしてビジネス活動を改善

用語

テーブル → 表
カラム → 縦の行
レコード → 横の列

主キー

テーブルに登録するレコード(データ行)の全体のうち、ひとつのデータに特定することをデータベースが保証する列のこと。
主キーは1つのみ。
Primary Key(プライマリーキー)やPKとも呼ばれる。

主キー設定のやり方

CREATE TABLE users (
 id int PRIMARY KEY,
 name varchar(10)
);

これで「usersテーブル」を作成、プライマリーキーは「id」に設定することができた。

候補キー

テーブル内で、すべてのレコード（組/行）を一意に識別することができる項目

複合キー

複数のキーを組み合わせたもの（主キーにもなり得る）

複合キーを主キーに設定するやり方

CREATE TABLE users (
 id int,
 name varchar(10),
 PRIMARY KEY (id,name)
);

これで「usersテーブル」を作成、プライマリーキーは「idとname両方」に設定することができた。

SQLの基本

SELECT文

SELECT文はデータベースから情報を出力するための構文のこと。
以下でSELECT文の使いかたを解説します。

  ー 特定の列を抽出する
SELECT 取得するカラム
FROM テーブル名;

 SELECT name, price  //カラム名 
FROM car; //テーブル名

これで「carテーブル」から「name」「price」の情報を取得できる。

 ー カラム名の変更
AS

 SELECT name AS '名前', feature AS '特徴' 
FROM car;

これで「carテーブル」から「name」「price」の情報を取得できる。

ー すべてのデータを出力
*

SELECTのあとの「*」はテーブルのすべてのデータを出力するという意味。

SELECT *
FROM car;

これで「carテーブル」のすべてのデータが出力できる。

ー 重複なしでデータを出力
DISTINCT

SELECT DISTINCT(price)
FROM car;

これで重複なしで「carテーブル」から「priceカラム」を取り出すことができる。

ー 条件で絞り込む
WHERE

SELECT name
FROM car
WHERE speed > 90;

これで「carテーブル」からスピードが90より速い車のみ指定して「nameカラム」を取得することができる。
つまり条件に一致したレコードのみを取得できる。

 ー WHEREとHAVINGの違い

WHEREとHAVINGはどちらも「抽出条件を指定」する同じ役割を持ちますが、その違いは呼び出しのタイミング。

実行順序　「WHERE」→「GROUP BY」→「HAVING」なので
「GROUP BY」を使ってグループ化するときに

「WHERE」・・・「GROUP BY」の影響の前に条件が適応される
「HAVING」・・・「GORUP BY」の影響のあとに条件が適応される

という違いがある。

SELECT name, SUM(speed) 
FROM car
GROUP BY speed
HAVING speed > 300

このように書くことで、「carテーブル」の「speedカラム」をグルーピングした後にHAVINGの条件（speed> 300）を実行してくれる。

WHERE句だと、先にspeedが300以下のものはすべて除外された後に
グルーピングされてしまう。

ー 条件を両方満たす
AND

SELECT *
FROM car
WHERE speed > 90 AND price < '100万円';

これで「carテーブル」からスピードが90より速く、料金が100万円未満のレコードのみを取得できる。

ー 条件をどちらか満たす
OR

SELECT *
FROM car
WHERE speed > 90 OR price < '100万円';

これで「carテーブル」からスピードが90より速い、または料金が100万円未満のレコードを取得できる。

ー ～の間のデータを取得する
BETWEEN AND

SELECT *
FROM car
WHERE speed BETWEEM 90 AND 120;

これで「carテーブル」からスピードが90～120のレコードを取得できる。
(この時、90と120も含まれる。)

ー 条件をまとめて指定
IN ( )

SELECT *
FROM car
WHERE speed IN ('90', '120');

これで「carテーブル」からスピードが90と120のレコードを取得できる。

まとめて否定したいときは「NOT IN」を使う。

 ー あいまい検索
LIKE  % / LIKE  _

SELECT *
FROM car
WHERE name LIKE 'ab%';

%(ワイルドカード文字)は0文字以上すべての文字という意味で、
上記の場合、「carテーブル」の「name」カラムから「ab」で始まる文字列のレコードを取得できる。（abのみの名前の含まれる。）

SELECT *
FROM car
WHERE name LIKE '%ab';

上記のように指定すると「carテーブル」の「name」カラムから「ab」で終わる文字列のレコードを取得できる。

また、「%」ではなく「_」をつけた場合は、
1文字という意味になる。

SELECT *
FROM car
WHERE name LIKE 'ab_';

この場合は、「carテーブル」の「name」カラムから「ab＋1文字」の文字列のレコードを取得できる。
※「_」を2つ付ければ2文字になる。

ー 空のレコードを取得
IS NULL

SELECT *
FROM car
WHERE speed IS NULL;

これで「carテーブル」からスピードが空のレコードを取得できる。

NULLじゃないカラムのみ指定したいときは「IS NOT NULL」を使用する。

 ー NULLの値を指定した値に置換する
IFNULL( )

SELECT IFNULL(id, 9999) 

この場合、「id」がNULLの場合、「9999」と出力される。
※NULLじゃない場合は、入っているデータが出力される。

ー レコードを任意の行のみ取得
LIMIT

SELECT *
FROM car
LIMIT 2;

これで「carテーブル」から2行のみ取得できる。

SELECT *
FROM car
LIMIT 5. 10;

少し応用的な使い方で、
LIMITの後に数字を二つ付けたこの場合だと
6行目から10個分抽出できる。
（ゼロが1行目のため、5は6行目）

ー データを並び替える
ORDER BY

SELECT *
FROM car
ORDER BY speed;

これで「carテーブル」の「speedカラム」を昇順で並び替えることができる。

降順させたい場合は最後に「DESC」をつける。

SELECT *
FROM car
ORDER BY speed DESC;

これで「carテーブル」の「speedカラム」を降順で並び替ることができる。

 ー 桁の切り捨て
TRUNCATE

TRUNCATEはテーブルのデータ削除にも使われるが、引数に指定した値を任にの桁で切り捨てることもできる。

SELECT TRUNCATE(data, 1)

この場合、「dataカラム」に入っている数値を小数点第一位で切り捨てることができる。
（例：513.25 → 513.2）

SELECT TRUNCATE(data, -1)

この場合、「dataカラム」に入っている数値を一桁目で切り捨てることができる。
（例：513.25 → 510）

 ー 文字を結合する
CONCAT( )

SELECT 'My' 'S' 'QL';

これで「MySQL」と出力される。

 ー 日付または時刻を指定したフォーマットで整形する
DATE_FORMAT( )

SELECT DATE_FORMAT(birth, '%Y年%m月%d日') 

この場合は、「birthカラム」が「Y年m月d日」の形に整形される。

Y、m、dとは？
SQLであらかじめ指定されている指定子のこと
Yの意味は、年、数字、4 桁
mの意味は、月、数字 (00..12)
dの意味は、日、数字 (00..31)

％とは？
%(ワイルドカード文字)は0文字以上すべての文字という意味。

 

ー 一部分を切り抜く
SUBSTRING( )

SELECT SUBSTRING(name, 1, 2)
FROM car;

この場合、「carテーブル」の「nameカラム」から「１文字目から２文字」切り抜く

 ー 検索結果(SELECT)を結合する(重複は除外)
UNION

SELECT  userId
FROM  football
UNION
SELECT  userId 
FROM  baseball;

この場合、取り出される情報は「footballテーブル」の「userId」と「baseballテーブル」の「userId」の両方になる。
※ただし、重複分は除外される。
注意点としては、2つのSELECTの列数を同じにしておく必要がある。

 ー 検索結果(SELECT)を結合する(重複も取得)
UNIONALL

SELECT  userId
FROM  football
UNIONALL
SELECT  userId 
FROM  baseball;

この場合、取り出される情報は「footballテーブル」の「userId」と「baseballテーブル」の「userId」の両方になる。
※重複分も取得される。
注意点としては、2つのSELECTの列数を同じにしておく必要がある

 ー 表を結合する
＝

SELECT * from car;
SELECT * from price;
の2テーブルを「同一のid」という条件で結合させたいとき、

SELECT *
FROM car, price
WHERE car.id = price.id;

これで「carテーブル」と「priceテーブル」をid合わせで結合する。

GROUP BY

ー グループ化する
GROUP BY

SELECT COUNT(name)
FROM car 
GROUP BY day;

「GROUP BY day」で同じ値の「day」をまとめる。
上記の場合、「carテーブル」の「day」ごとにまとめてグループを作り、個数を取得できる。

SELECT day, COUNT(name)
FROM car 
GROUP BY day;

この場合は、取得する値が、「dayカラム」と「nameカラム」の個数となる。

ー 複数をグループ化する
GROUP BY 対象のカラム1, 対象のカラム2;

SELECT day, size, COUNT(name)
FROM car 
GROUP BY day, size;

「GROUP BY day」で「day」「size」ごとにまとめる。

例）
・day→1/1, size→100
・day→1/1, size→200
・day→1/3, size→200
・day→1/3, size→200
がある場合、上記のコードでまとめると
・day→1/1, size→100　count→1
・day→1/1, size→200　count→1
・day→1/3, size→200　count→2
となる。

集計関数

・COUNT：レコード数
・AVG：平均
・MAX：最大値
・MIN：最小値
・SUM：合計

SELECT AVG(speed), MAX(speed),
FROM car 
GROUP BY day;

この場合「carテーブル」から平均速度(speed)と最大速度(speed)を取得できる。

JOIN

テーブル間で共通するデータを軸にテーブル同士を結合する。

ー テーブルを結合する(内部結合)
INNER JOIN  ON

SELECT *
FROM car AS c
INNER JOIN track AS t ON c.id = t.id;

「carテーブル」を「c」、「trackテーブル」を「t」とテーブル名を変え、
「cテーブル」に「tテーブル」を結合させる。
結合条件(ON以降)は「cのid」と「tのid」

取得データを両方のテーブルから取得したいときは、
「SELECT c.○○, t.○○」 と入れる。

ー データのないものも含めテーブルを結合する(外部結合)
LEFT OUTER JOIN  ON

例えばidがない車も含めて出力したくても、「INNER JOIN」を使うと、idがないレコードは出力されない。

そんなときは「LEFT OUTER JOIN」をつけると、データがない箇所は「NULL」をつけて出力してくれる。

SELECT *
FROM car AS c
LEFT OUTER JOIN track AS t ON c.id = t.id;

CREATE文

 ー 新しいデータベースの作成

CREATE DATABASE training;

この場合新規に「taraining」という名前のデータベースを作成することができる。

CASE式

 ー CASE

プログラミングでいうif構文。
条件を指定して、それに合致しているか、
条件1に合致して入れば処理完了、合致していなければ条件2へ、
それにも合致しなければelseへという式。

SELECT CASE WHEN 条件1 THEN 値
　　　　WHEN 条件2 THEN 値
       ELSE 値 END
FROM テーブル名

サブクエリ

 ー サブクエリ

クエリの中にクエリを書く。

例えば下記のような平均価格を出すSQL文があるとき

SELECT AVG(price)
FROM items;
//70,000とする

SELECT *
FROM items
WHERE price >= 70000;

これをSQL一本で書くときに用いるのがサブクエリ。

SELECT *
FROM items
WHERE price >= (SELECT AVG(price)
                  FROM items);

 ー 相関サブクエリ

例えば下記のような平均価格を出すSQL文があるとき、
categoryカラムに複数のカテゴリーが入っており、それぞれのカテゴリーから平均価格を求めたい。

SELECT *
FROM items AS i1
WHERE price >= (SELECT AVG(price)
                  FROM items AS i2
                 WHERE i1.category = i2.category
                 GROUP BY category);

こう書くことで、FROM itemsの「i1」とサブクエリ内の「i2」が同じcategoryだと認識されて、正しい値が返される。

INSERT文

 ー データベースにレコードを追加
INSERT INTO VALUES( )

INSERT INTO users(id,name)
VALUES(1, 'training');

この場合「usersテーブル」の（id,name）にそれぞれ（1,training）を追加する。
idに1、nameにtrainingが追加される。

UPDATE文

 ー レコードの情報を更新する。

UPDATE users
SET name = '田中',birthday = '2000－01－01'
WHERE id = 1;

この場合「usersテーブル」の「id = 1」の情報が「name = 田中、 birthday =  2000－01－01」に更新される。

DELETE / TRUNCATE文

 ー 削除
DELETE

テーブルからレコードを削除する際にはDELETE文を使う。

DELETE FROM car
WHERE id = 1;

この場合「carテーブル」から「id = 1」のレコードを削除する。

 ー 削除した後、再度作り直す
TRUNCATE

テーブル自体を一度削除した後、再度作り直す命令
DELETEのように条件を指定して特定のデータだけを削除することはできない。
テーブル内の全データが一括削除される。

TRUNCATE car;

これで、「carテーブル」を削除できる

DROP TABLE文

 ー テーブルを削除

テーブルを削除する際にはDROP文を使う。

DROP TABLE car;

この場合「carテーブル」を削除する。