12a. ProgateでSQL Ⅰ～Ⅲ(備忘録)

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が先に行われます。

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は複数回使用可能 */