SQLの基礎まとめ

## ■ はじめに
今回はSQLの基礎について、まとめていきます。

【対象とする読者の方】
・SQLの基礎を学びたい、復習したい方
<br>
## 1. テーブルの作成
データの表を「テーブル」と呼びます。
ここでは、emp（社員）テーブルを作成していきます。

まず最初に、列名を決めます。

・id（番号）
・name（名前）
・age（年齢）
・gender（性別）
・dep_id（部署番号）

次に、下記の中からデータ型を指定します。

・integer：整数
・float：浮動小数点数
・boolean：真偽値
・date：日付
・text：文字

そして、列制約を選択します。

・not null：null（値なし）を許さない
・unique：重複した値を許さない
・primary key：主キー（not null + unique）

下記の構文で、テーブルの枠組みを作成します。

・**create table テーブル名（列名　データ型　列制約）;**

```sql

create table emp(

id integer primary key,
name text not null,
age integer,
gender text,
dep_id integer

);
```
※実際には表示されませんが、下記の状態になっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/7ceba376-0e9a-eab0-2de8-c68a06f9ab2a.png)
<br>
## 2. データの挿入
枠組みができたので、データを挿入していきます。

・**insert into テーブル名（列名のリスト）values（挿入する値のリスト）;**


```sql

insert into emp(id, name, age, gender, dep_id) values(1, '鈴木', 25, 'M', 10);
insert into emp(id, name, age, gender, dep_id) values(2, '佐藤', 30, 'F', 20);
insert into emp(id, name, age, gender, dep_id) values(3, '田中', 35, 'M', 20);
insert into emp(id, name, age, gender, dep_id) values(4, '渡辺', 25, 'F', 30);
insert into emp(id, name, age, gender, dep_id) values(5, '島田', 40, 'M', 40);
```
全列に挿入する場合は、下記のように列名のリストを省略できます。

```sql

insert into emp values(1, '鈴木', 25, 'M', 10);
insert into emp values(2, '佐藤', 30, 'F', 20);
insert into emp values(3, '田中', 35, 'M', 20);
insert into emp values(4, '渡辺', 25, 'F', 30);
insert into emp values(5, '島田', 40, 'M', 40);
```
また、まとめてデータを挿入することも可能です。

```sql

insert into emp values(1, '鈴木', 25, 'M', 10),
                      (2, '佐藤', 30, 'F', 20),
                      (3, '田中', 35, 'M', 20),
                      (4, '渡辺', 25, 'F', 30),
                      (5, '島田', 40, 'M', 40);
```
※実際には表示されませんが、下記の状態になっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/37c77810-825b-54a2-31ba-13c991f9bb32.png)

同じ流れで、dep（部署）テーブルも作成しておきます。

・dep_id：部署番号
・dep_name：部署名

```sql
create table dep(

dep_id integer primary key,
dep_name text

);
```
```sql

insert into dep values(10, '営業'), (20, '開発'), (30, '経理'), (40, '人事');
```
※実際には表示されませんが、下記の状態になっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/b3f31373-eb61-71be-9df3-259f89141072.png)
<br>
## 3. データの検索
先ほど作成した、社員テーブルを表示します。

・**select 列名 from テーブル名;**

列名に「*」を使用することで、全ての列を指定できます。

```sql

select * from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/37c77810-825b-54a2-31ba-13c991f9bb32.png)

次に、部署テーブルを表示します。

```sql

select * from dep;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/b3f31373-eb61-71be-9df3-259f89141072.png)

また下記のように、「where 検索条件」で、取り出すデータを絞ることができます。

・**select 列名 from テーブル名 where 検索条件;**

年齢が30歳以上に該当するデータの、名前と年齢の列を表示します。

```sql

select name, age from emp where age >= 30;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/da3763fa-a823-8cda-1ad2-97c137cdb85f.png)

検索条件は、複数指定することも可能です。

```sql

select * from emp where age >= 30 and gender = 'M';
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/6c3a16e0-4851-ab58-aeb2-fda1273abda0.png)
<br>
## 4. データの並べ替え
empテーブルの名前と年齢の列を取り出してから
年齢について昇順（下に行くにつれて、小→大）に並べ替えます。

・**select 列名 from テーブル名 where 検索条件 order by 並べ替える列;**

```sql

select name, age from emp order by age;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/8ce88960-97b1-7ecb-8be2-f5d49c55e206.png)

「desc」を用いると、降順（下に行くにつれて、大→小）でも並べ替えることもできます。

・**select 列名 from テーブル名 where 検索条件 order by 並べ替える列 desc;**

```sql

select name, age from emp order by age desc;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/0b8acd2c-a959-1c2f-319e-025177a7cf79.png)

## 5. データの更新
まず、empテーブルを表示します。

```sql

select * from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/37c77810-825b-54a2-31ba-13c991f9bb32.png)

ここで、渡辺さんの年齢を25→45歳に変更（更新）してみます。

・**update テーブル名 set 列名 = 更新データ where 条件;**

```sql

update emp set age = 45 where name = '渡辺';
```
empテーブルを表示して確認します。

```sql

select * from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/17b8e73c-05db-9535-c484-5b5304ac4752.png)
また、下記の方法でも年齢を更新することができます。

```sql
update emp set age = age + 5 where name = '島田';
select * from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/1f932cd0-b6be-aff6-7b94-4494e34651d5.png)
<br>
## 6. データの削除
empテーブルの島田さん（id = 5）の行を削除します。

・**delete from  テーブル名 where 条件;**

```sql

delete from emp where id = 5;
select * from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/bcfbe0e4-f007-6a20-3703-0e891b603840.png)
また、下記のように条件を指定しないと、empテーブルの全データが削除されるので、注意が必要です。

```sql

delete from emp;
```
<br>
## 7. データの集計
集計には、下記のような方法があり、それぞれを集約関数と呼びます。

・count（行数）
・sum（合計）
・avg（平均）
・max（最大値）
・min（最小値）

実際に、empテーブルの行数をカウントしてみます。

・**select 集約関数（列名） from テーブル名 where 検索条件;**

```sql

select count(*) from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/a1570671-1902-8277-1d61-743f4f9e5280.png)
年齢が35歳以下の行数もカウントします。

```sql

select count(age) from emp where age <= 35;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/0956bc4d-7acc-fdf4-044f-e86ce9539546.png)
他の集約関数も使用して、まとめて抽出してみます。

```sql

select sum(age), avg(age), max(age), min(age) from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/f8e004d2-0762-c518-8bb9-d6e4137b1751.png)
<br>
## 7. テーブルの結合
テーブル同士の結合方法のうち、今回はinner joinを扱います。

実際にempテーブルとdepテーブルを結合して、確認していきます。

その前に、empテーブルの内容を変更しておきます。

```sql

update emp set dep_id = 20 where name = '鈴木';
update emp set dep_id = 30 where name = '佐藤';
update emp set dep_id = 40 where name = '田中';
update emp set dep_id = 50 where name = '渡辺';

select * from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/2ec46768-1f8b-2af1-564c-31b9f11fd4a8.png)
depテーブルの内容も確認しておきます。

```sql

select * from dep;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/0038dda2-9bad-cd82-40fe-f98b4c13bf4b.png)
これら2つのテーブルを使っていきます。

inner joinは、結合に使用する「両テーブルに共通した列」において、データが一致したものだけを結合します。

・**select 列名 from テーブル名1 inner join テーブル名2;**

```sql

select * 
from emp inner join dep
using(dep_id);
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/c336ed5b-ca5b-bee5-ed21-03f7de57e47e.png)
コード3行目より、結合に使用する「両テーブルに共通した列（dep_id）」を選択します。
そして2行目で、empとdepテーブルを結合したものをfromで指定し、1行目で全てのデータを取り出しています。

すると、dep_idにおいて、両テーブルに存在するデータ（20, 30, 40）を持つ行のみが結合されています。

また、結合に使用する列については、下記のように「on」で指定することも可能です。

```sql

select *
from emp inner join dep
on emp.dep_id = dep.dep_id;
```
emp.dep_idは「empテーブルのdep_id」、dep.dep_idは「depテーブルのdep_id」という意味です。
<br>
## 8. 列の別名付与
「as」を使用することで、列に別名を付けることができます。

・**select 列名 as 別名 from テーブル名;**

```sql

select name as 名前, age as 年齢, dep_id as 部署番号 from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/2eb563d2-fd11-7257-b79f-7a010ed53e05.png)
別名の付与は、検索結果のみ有効となります。

```sql

select * from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/b98e0709-99d1-e3d6-9073-0cacc439497e.png)
そのため、例えばテーブルの結合などをした際、似たような列名があれば
区別するために別名を付与することが望ましいです。
<br>
## 9. null データの検索
テーブル内のnull（値なし）を持つ行を検索することができます。

・**select 列名 from テーブル名 where 列名 is null;**

```sql
select * from emp where name is null;
```
逆に、nullを持たない行を検索することも可能です。

```sql

select * from emp where name is not null;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/53d894dc-05f8-72c3-858c-c2d3ec30a336.png)
<br>
## 10. 文字列データの曖昧検索
「like」を用いて、文字列の並びからデータを検索することができます。

例えば、名前が「佐」で始まる人を調べます。

・**select 列名 from テーブル名 where 列名 like '条件';**

```sql

select * from emp where name like '佐%';
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/525fad69-ccb4-1ea9-b5f3-9de00dda4be9.png)
次に、名前が「辺」で終わる人を検索します。

```sql

select * from emp where name like '%辺';
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/52bab61c-fffe-0fcb-4c37-829d7ceef7bf.png)
今度は、名前に「木」を含む人を調べます。

```sql

select * from emp where name like '%木%';
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/81bca548-0eab-39a9-b4c8-3bfad8c71e4b.png)
このように、文字列の情報からデータを検索することが可能です。
<br>
## 11. 検索データの絞り込み
「limit」を使用すると、検索したデータから絞り込むことができます。
例えば、次のようにすると、最初の1行のみを取り出します。

・**select 列名 from テーブル名 limit 件数;**

```sql

select * from emp limit 1;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/1ce06f80-a24b-b241-522d-e9da0819869b.png)

次は、最初の3行を表示します。

```sql

select * from emp limit 3;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/8ab49a98-a0b5-1f0a-a25a-19087e129eb9.png)
また、「offset」を一緒に使うことで、始まりの行を指定することができます。

・**select 列名 from テーブル名 limit 件数 offset 位置;**

```sql

select * from emp limit 3 offset 1;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/e474f968-3658-9dd0-f557-09d63ba402da.png)
尚、行は0番目から数えたものとなります。（1行目：0番、2行目：1番、3行目：2番　など）
そのため、上記のように「offset」で1を指定した場合には、2行目からの開始となります。
<br>
## 12. 時刻型データ
日付や時間など、時刻に関するデータを扱う際は、「timestamp」というデータ型を使用します。

テーブルを作成し、時刻型データを挿入していきます。

```sql

create table time_tb(t timestamp);
```

```sql
insert into time_tb values('2020-3-15');
insert into time_tb values('2020-10-25');
insert into time_tb values('yesterday');
insert into time_tb values('today');
insert into time_tb values('tomorrow');
insert into time_tb values('now');
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/4e5add28-56a8-f1d0-0771-8b887ed1f450.png)
時刻型データは、文字列データと同様に、シングルクォート（''）で囲います。

「where」で条件指定をして、検索をすることも可能です。

```sql

select * from time_tb where t > '2020-12-14';
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/5d5915f2-bbfc-c5c9-0119-4c6e0eb7acef.png)
日時の演算も行ってみます。

それぞれの日付に1日を足して、取り出します。

```sql

select t+'1 day' as plus from time_tb;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/2cf80207-30ec-748c-bc5f-4b6014c753d5.png)

```sql

select t+'-1 day' as plus from time_tb;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/34dd072f-e7c4-9cf7-bcfb-8788209af86d.png)
「update」を使用しても、計算することが可能です。

```sql

update time_tb set t = t+'3day';
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/f5674d3f-491e-8c5a-8209-36e6d55aa192.png)
ただし「update」では、元データそのものが変更されます。
<br>
## 13. 副問い合わせ（サブセレクト）
select文（問い合わせ）の中に、さらにselect文（副問い合わせ）を入れることができます。

まずは、emp・depテーブルを確認しておきます。

```sql

select * from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/0f86c013-c20d-274f-cc81-6fabb55c2a43.png)


```sql

select * from dep;
````
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/62f06fa7-75da-af1f-d2d7-65d74d5680a8.png)
副問い合わせを使って、データを取り出してみます。

```sql

select * from emp
where dep_id = (select dep_id from dep where dep_id = 30);
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/2c8c4455-0e32-758d-c1b6-f8eed62b4582.png)
まず、depテーブルから部署番号が30であるdep_idを取り出して
それをwhere以下のdep_idとし、empテーブルからデータを取り出しました。

```sql
select * from emp
where dep_id = (select dep_id from dep where dep_name = '開発');
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/75f3473c-93b1-b11d-3299-c0ebc30811e2.png)
depテーブルから部署名が'開発'であるdep_idを取り出して
それをwhere以下のdep_idとし、empテーブルからデータを取り出しました。
<br>
## 14. グループ化
特定の列をグループ化したい場合には、「group by」を使用することができます。

まず、empテーブルにデータを追加しておきます。

```sql

insert into emp values(5, '松本', 35, 'M', 30),
                      (6, '小林', 30, 'F', 40),
                      (7, '岡本', 25, 'M', 20),
                      (8, '市川', 30, 'F', 30);
```

```sql

select * from emp;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/362d741f-a3a6-787e-94cb-a68772e5a270.png)
「group by」では、指定した列のうち、同じ値を持つものについてはまとめて扱うことができます。
次のSQLを実行すると、年齢の一覧が出力されます。

・**select 列名 from テーブル名 where 検索条件 group by 対象列名;**

```sql

select age from emp
group by age;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/c7e53d3f-72bf-b6eb-c6cd-1fd7ef4ac7cb.png)
「group by」は主に、集約関数と一緒に使用することが多いです。
「count」を使用することで、それぞれの年齢の数をカウントすることができます。

```sql

select age, count(age) from emp
group by age;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/a9d71574-2420-5483-9baa-f5fb6fa7e4bb.png)
年齢の合計値も求めてみます。

```sql

select age, sum(age) from emp
group by age;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/07e2889e-ef95-2734-3e64-62ea141020f1.png)
「having」を使用することで、取り出すデータを絞り込むことができます。

・**select 列名 from テーブル名 where 検索条件 group by 対象列名 having 条件;**

```sql

select age, sum(age) from emp
group by age
having age >= 35;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/c10f2bed-047e-8387-b9be-a1e3aaa4d69e.png)
「group by」でグループ化した結果に対して、ageが35歳以上のデータを取り出しています。

「having」は、「where」と合わせて使用されることもあります。

```sql

select age, sum(age) from emp
where age < 45
group by age
having age >= 35;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/725082/bee397c9-0ddc-7510-9d5a-d4bacc6ce279.png)
処理の順番は、where → group by → having となります。

「where」でageを45歳未満に絞ってから、「group by」でグループ化を行い
その後、「having」によって35歳以上のデータを取り出しています。
<br>
## ■ おわりに
今回はSQLの基礎について、まとめさせていただきました。
少しでも多くの方にとって、お役に立ちましたら幸いです。