12a. ProgateでSQL Ⅰ～Ⅲ

ProgateでSQLⅠ(基本操作)
ProgateでSQLⅡ(集計関数とグループ化)
ProgateでSQLⅢ(サブクエリとJOIN)

ProgateでSQLⅠ(基本操作)

SQLとは
データベースに送る命令をSQLと呼びます。

SQL文

/* SELECT文を用いて、データベースからデータを取得 */
select カラム名 from テーブル名;
select name from purcharse;
 
select name, price from purcharse;  /* 複数のカラムからデータを取得 */
select * from purcharse;            /* 全てのカラムからデータを取得 */
 
/* where句 */
select * from purchases where category = "食費";              /* 「category」が「食費」のデータを検索 */
select * from purchases where price = 1000;                 /* 数値のデータを検索(クォーテーションで囲まない) */
select * from purchases where purchased_at = "2017-07-01";  /* 日付のデータを検索 */
select * from purchases where price >= 1000;                 /* priceカラムのデータが「1000以上」のレコードを検索(比較演算子) */
select * from purchases where purchased_at <= "2017-08-01";  /* purchased_atカラムのデータが「2017-08-01以前」のレコードを検索 */
select * from purchases where name like "%プリン%";            /* 「プリン」という文字を含むレコードを検索(LIKE演算子) */
 
/* NOT演算子 */
select * from purchases where not price > 1000;          /* 条件を満たさないレコードを検索 */
select * from purchases where not name like "%プリン%";    /* 「プリン」という文字を含まないレコードを検索 */
 
/* NULL演算子 */
select * from purchases where price is null;        /* 指定されたカラムのデータが「NULL」であるレコードを検索 */
select * from purchases where price is not null;    /* 指定されたカラムのデータが「NULL」ででないレコードを検索 */
 
/* AND演算子とOR演算子 */
select * from purchases where character_name = "ひつじ仙人" and category = "食費";                 /* 条件1と条件2を共に満たす */
select * from purchases where character_name = "ひつじ仙人" or character_name = "にんじゃわんこ";   /* 条件1または条件2のどちらかを満たす */
 
select * from purchases order by price desc;            /* データを並び替える */
select * from purchases where 条件 order by price desc;   /* order by句は末尾に記述する */
select * from purchases where 条件 limit 5;               /* limit句は末尾に記述する */

ProgateでSQLⅡ(集計関数とグループ化)

select name, price * 1.08 from purchases;   /* 四則演算 */
select distinct(name) from purchases;       /* nameカラムの重複したレコードを省く */
 
/* 集計関数 */
select sum(price) from purchases;           /* SUM関数(合計) */
select avg(price) from purchases;           /* AVG関数(平均) */
select count(*) from purchases;             /* COUNT関数 */
select max(price) from purchases;           /* MAX関数 */
select min(price) from purchases;           /* MIN関数 */
 
/* グループ化 */
select sum(price), purchased_at from purchases group by purchased_at;   /* 購入日でグループ化して、金額を集計 */
select sum(price), purchased_at character_name from purchases           /* 購入日とキャラクターでグループ化して、金額を集計 */
group by purchased_at, character_name;
 
/* カテゴリが食費のものを検索し、購入日とキャラクターでグループ化して、金額を集計 */
select sum(price), purchased_at character_name from purchases where category = "食費"
group by purchased_at, character_name;
 
/* having句(GROUP BYによってグループ化されたデータを検索) */
select sum(price) from purchases group by purchased_at having sum(price) > 1000;

ProgateでSQLⅢ(サブクエリとJOIN)

サブクエリとは
SQLでは、SQL文の中に他のSQL文を入れることができます。この他のSQL文をサブクエリと言います。

select name from players where goals > (
    select goals from players where name = "ウィル"    /* サブクエリ(ウィルの得点数を取得) */
);

ASの使い方
「カラム名 AS “名前”」で、カラム名などに別名を定義できます。

select goals as "ウィルの得点数" from players where name = "ウィル";
 
/* 実行結果 */
ウィルの得点数
14

JOIN(テーブルの結合)
ONで条件を指定して、playersテーブルにcountriesテーブルを結合します。

select * from players join countries on players.country_id(外部キー) = countries.id(主キー);

複数テーブルでのカラムの指定
複数のテーブルに同じカラム名が存在する時は、「テーブル名.カラム名」で指定する必要があります。

select players.name, countries.name     /* 同じカラム名なので、テーブル名の指定も必要 */
from players join countries
on players.country_id = countries.id;

実行順序
SQLは取得するテーブルを形成してから検索を行うので、FROM・JOINが先に行われます。

1	テーブルの指定	FROM
2	結合	ON・JOIN
3	取得条件	WHERE
4	グループ化	GROUP BY
5	関数	COUNT・SUM・AVG・MIN
6	HAVING	HAVING
7	検索	SELECT・DISTINCT
8	順序	ORDER BY
9	LIMIT	LIMIT

スクロールできます

LEFT JOIN(外部キーがNULLの場合も含めてレコードを全て取得)

select * from players left join teams on players.previous_team_id(外部キー) = team.id(主キー);

複数テーブルのJOIN

select * from players
join countries on players.country_id = countries.id
left join teams on players.previous_team_id = teams.id;     /* JOINは複数回使用可能 */