第５章　テーブルの操作（後編）

# **テーブルの操作（後編）**

***

## **1.データの挿入**

テーブルにデータを追加するには```INSERT文```を使います。基本となる書式は次の通りです。

```
INSERT INTO データベース名.テーブル名(列名1, 列名2, ...) VALUES (値1, 値2, ...);
```

先に**USE**でデータベースを指定したら、データベース名を省略できます。

```
INSERT INTO テーブル名(列名1, 列名2, ...)　VALUES (値1, 値2, ...);
```

テーブルに含まれている全てのカラムに値を指定する場合に限ってカラムの記述を省略することができます

```
INSERT INTO テーブル名 VALUES (値1, 値2, ...);
```

**・実例**[sample5-1.sql]

作ったテーブルにデータを挿入します。

テーブルcountryを作成する時、countryNameにNOT NULLを設定しなかったので、値入れなくでもエラーになりません。

```
use companydata; -- companydataデータベースを使用

-- テーブルcountryにデータを挿入 
INSERT INTO country ( countryCd, countryName )
 VALUES ( '001', '中国' ),( '002', '日本' ),( '003', 'アメリカ' ),( '004', 'カナダ' );

INSERT INTO country ( countryCd, countryName ) VALUES ( '005', 'イギリス' );
INSERT INTO country ( countryCd ) VALUES ( '006' ); -- エラーなし
```

文字型の値を挿入する際に値を""と''で囲む必要があります。
```

-- テーブルsexにデータを挿入 
INSERT INTO sex ( sexCd, sexName ) VALUES ( '01', '男性' );
INSERT INTO sex ( sexCd, sexName ) VALUES ( '02', '女性' );
INSERT INTO sex ( sexCd, sexName ) VALUES ( '03', '不明' );

-- テーブルempinfoにデータを挿入
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100011', '山田もも', '1990-09-09', '001', '02', 2 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100012', '上田太郎', '1993-09-06', '001', '01', 2 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100013', '高橋梨', '1990-05-09', '002', '02', 4 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100014', '高橋アリア', '1992-09-09', '002', '01', 1 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100015', '山田ユリ', '1990-04-09', '001', '02', 1 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100016', '五反田二', '1992-09-11', '004', '02', 2 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100017', '臼杵一', '1991-09-09', '001', '02', 3 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100018', '坂本剣', '1991-09-04', '003', '01', 2 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100019', '坂本健一', '1990-09-09', '002', '01', 3 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100020', '山本孝之', '1990-03-09', '003', '01', 5 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100021', '上野もも', '1990-09-07', '001', '02', 4 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100022', '山田奈々', '1990-09-08', '003', '02', 1 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100023', '山田健一', '1990-07-04', '002', '01', 2 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100024', '鈴木勝田', '1992-09-09', '001', '02', 2 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100025', '山田奈々', '1990-09-08', '006', '02', 1 );
INSERT INTO empinfo ( empCd, name, birthday, countryCd, sexCd, year ) VALUES( '100026', '山田健一', '1990-07-04', '002', '01', 2 );
```

テーブルempinfoの全てのカラムに値を挿入するので、カラム名の記述が省略できますが、推薦しない。
```
INSERT INTO empinfo VALUES ( '100027', 'Lily', '1992-09-09', '001', '02', 2 );
INSERT INTO empinfo VALUES ( '100028', 'Joy', '1990-09-08', '003', '02', 1 );
```

結果の確認

<a href="https://i.loli.net/2019/04/23/5cbeac6211f03.png" target="_blank"><img src="https://i.loli.net/2019/04/23/5cbeac6211f03.png" width="40%"></a>

## **2.データの取得**

挿入されたデータの内容を確認、取得には```SELECT文```を使います。

```
SELECT 列名1,列名2,… FROM テーブル名 [条件];
```

列名の部分に　*　を入れると指定したテーブルのすべての列が列挙されます。

```
SELECT * FROM テーブル名;
```

**・実例**[sample5-2.sql]

<a href="https://i.loli.net/2019/04/23/5cbeb04ab2e7c.png" target="_blank"><img src="https://i.loli.net/2019/04/23/5cbeb04ab2e7c.png" width="40%"></a>

<a href="https://i.loli.net/2019/04/23/5cbeb0ec4b5f0.png" target="_blank">
<img src="https://i.loli.net/2019/04/23/5cbeb0ec4b5f0.png" width="40%"></a>

## **3.WHEREによる検索**

SELECT文を発行し、更に検索条件をつけるには```WHERE句```を使用します。

```
SELECT …　FROM テーブル名　WHERE 条件式
```

**・実例**[sample5-3.sql]

year（入社年数）が2のレコードを選択して、empCdとnameの内容を表示します。

<a href="https://i.loli.net/2019/04/23/5cbeb4b383ebc.png" target="_blank"><img src="https://i.loli.net/2019/04/23/5cbeb4b383ebc.png" width="40%"></a>

### **3-1.WHERE句の条件式**

（１）WHERE句の比較に使える演算子
<table border="1">
<tr style="width:200px ;background-color:#C6E0B4 ;text-align:center;">
	<th align="center">演算子</th>
 <th align="center">使用例</th>
	<th align="center">意味</th>
</tr>
<tr>
	<td>=</td>
 <td>a = b</td>
	<td>a と b は等しい</td>
</tr>
<tr>
	<td><></td>
 <td>a <> b</td>
	<td>a と b は等しくない</td>
</tr>
<tr>
	<td>!=</td>
 <td>a != b</td>
	<td>a と b は等しくない</td>
</tr>
<tr>
	<td><</td>
 <td>a < b</td>
	<td>a は b よりも小さい</td>
</tr>
<tr>
	<td><=</td>
 <td>a <= b</td>
	<td>a は b よりも小さいか等しい</td>
</tr>
<tr>
	<td>></td>
 <td>a > b</td>
	<td>a は b より大きい</td>
</tr>
<tr>
	<td>></td>
 <td>a >= b</td>
	<td>a は b よりも大きいか等しい</td>
</tr>
</table>

（２）また下記のような演算子も用意されています。

<table border="1">
<tr style="width:200px ;background-color:#C6E0B4 ;text-align:center;">
	<th align="center">演算子</th>
 <th align="center">使用例</th>
	<th align="center">意味</th>
</tr>
<tr>
	<td>IS NULL</td>
 <td>a IS NULL</td>
	<td>a はNULL</td>
</tr>
<tr>
	<td>IS NOT NULL</td>
 <td>a IS NOT NULL</td>
	<td>a はNULLではない</td>
</tr>
<tr>
	<td>IS</td>
 <td>a IS boolean_value</td>
	<td>a はboolean_value</td>
</tr>
<tr>
	<td>IS NOT</td>
 <td>a IS NOT boolean_value < b</td>
	<td>a はboolean_valueではない</td>
</tr>
<tr>
	<td>BETWEEN AND</td>
 <td>a BETWEEN min AND max</td>
	<td>a は minより大きいか等しくmaxより小さいか等しい</td>
</tr>
<tr>
	<td>>IN/td>
 <td>a IN (value,...)</td>
	<td>a は 括弧内のいずれかの値に等しい</td>
</tr>
<tr>
	<td>NOT IN</td>
 <td>a NOT IN (value,...)</td>
	<td>a は 括弧内のいずれの値とも等しくない</td>
</tr>
<tr>
	<td>LIKE</td>
 <td>a LIKE pat</td>
	<td>SQL の単純な正規表現比較を使用したパターンマッチング</td>
</tr>
<tr>
	<td>NOT LIKE</td>
 <td>a NOT LIKE pat	</td>
	<td>LIKEの結果を反転させたもの</td>
</tr>
</table>

（３）WHERE句で **「AND」「&&」**（かつ）や **「OR」「||」**（か）演算子を使って、複数の条件でデータを検索できます。

**・実例**[sample5-4.sql]

入社年数(year)が、2以上、4未満の社員番号(empCd)と名前(name)が表示されます。

<a href="https://i.loli.net/2019/04/23/5cbeb82e4d483.png" target="_blank"><img src="https://i.loli.net/2019/04/23/5cbeb82e4d483.png" width="40%"></a>

### **3-2.WHERE句のオプション**

条件検索に用いることができるのは、WHERE句には、さまざまなオプションを追加することができます。

**（１）BETWEEN**

BETWEENは、数値がある値の範囲にあることを示す句です。

```
SELECT … FROM テーブル名 WHERE 列名 BETWEEN 値1 AND 値2
```

**・実例**[sample5-5.sql]

empCdが100013から100023の間の行を検索し、そのempCdとnameを表示します。

<a href="https://i.loli.net/2019/04/23/5cbebdc551302.png" target="_blank"><img src="https://i.loli.net/2019/04/23/5cbebdc551302.png" width="40%"></a>

**（２）IN**

IN条件は値が指定した値のリストの中にあるかを比較します。

```
SELECT … FROM テーブル名 WHERE 列名 IN(値1,値2)
```

これは、列が、値1か値2のどちらかであった場合、その値を取得するというものです。

**・実例**[sample5-6.sql]

INが「2」か「4」の行のempCd,およびnameを取得します。

<a href="https://i.loli.net/2019/04/23/5cbec03f32327.png" target="_blank">
<img src="https://i.loli.net/2019/04/23/5cbec03f32327.png" width="40%"></a>

**（３）LIKE**

LIKEは、文字の検索条件を指定します。ここで、％と＿（アンダースコア）は特殊な意味が割り当てられており、%は「任意の文字数の任意の文字」、_は「1文字の任意の文字」を表します。

```
SELECT … FROM テーブル名 WHERE 列名 LIKE 検索条件
```

**・実例**[sample5-7.sql]

birthdayが「1990」から始まる行のempCd,name,birthdayを取得します。

<a href="https://i.loli.net/2019/04/23/5cbec17e67e26.png" target="_blank">
<img src="https://i.loli.net/2019/04/23/5cbec17e67e26.png" width="40%"></a>

## **4.テーブル結合**

**（１）内部結合**

データベースでは、複数のテーブルを結合して検索するという処理を行うことがよくあります。

内部結合はそれぞれのテーブルの指定した列の```値が一致する```データだけを取得します、```INNER JOIN文```を使います。

```
SELECT テーブル名.カラム名, ... FROM テーブル名1 INNER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;
```

**・実例**[sample5-8.sql]

内部結合を行うには、2つのテーブルで結合の対象となるカラムを指定します。

<a href="https://i.loli.net/2019/04/24/5cbfb25031b55.png" target="_blank"><img src="https://i.loli.net/2019/04/24/5cbfb25031b55.png" width="40%"/></a>
二つのテーブルを結合します。

<a href="https://i.loli.net/2019/04/24/5cbfb4606b2cc.png" target="_blank">
<img src="https://i.loli.net/2019/04/24/5cbfb4606b2cc.png" width="40%"/></a>
 
もし、結合用の二つのカラムが同じ名前であれば、```on文```の代わりに```using文```が使えます。

```
SELECT * FROM テーブル１ INNER JOIN テーブル２ USING ( 列名 );
```

**・実例**[sample5-9.sql]

<a href="https://i.loli.net/2019/04/24/5cbfb930a8ee0.png" target="_blank">
<img src="https://i.loli.net/2019/04/24/5cbfb930a8ee0.png" width="40%"/></a>

**（２）交差結合**

交差結合（こうさけつごう）とは、二つのテーブルの組み合わせのすべての組み合わせを作る結合です。```CROSS JOIN文```を使います。

```
SELECT * FROM テーブル１ CROSS JOIN テーブル２;
```

**・実例**[sample5-10.sql]

<a href="https://i.loli.net/2019/04/24/5cbfbd36883e1.png" target="_blank">
<img src="https://i.loli.net/2019/04/24/5cbfbd36883e1.png" width="40%"/></a>

**（３）外部結合**

内部結合が、データの構造が一致する部分のみの接続であったのに対し、外部結合は、それぞれのテーブルの指定した列の値が一致するデータに加えて```どちらか```のテーブルにしか存在しないデータについても取得します。

構文①

```
SELECT テーブル名.カラム名, ... FROM テーブル名1 LEFT OUTER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;
```

構文②

```
SELECT テーブル名.カラム名, ... FROM テーブル名1 RIGHT OUTER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2
```

**・実例**[sample5-11.sql]

<a href="https://i.loli.net/2019/04/24/5cbfc2dc7991f.png" target="_blank">
<img src="https://i.loli.net/2019/04/24/5cbfc2dc7991f.png" width="40%"/></a>

<a href="https://i.loli.net/2019/04/24/5cbfc354136dc.png" target="_blank">
<img src="https://i.loli.net/2019/04/24/5cbfc354136dc.png" width="40%"/></a>

**（４）複数の結合**

テーブルの結合は3つ以上のテーブルを結合することも可能です。

**・実例**[sample5-12.sql]

empinfo、country、sex三つテーブルの結合。

<a href="https://i.loli.net/2019/04/24/5cbfc62ccf74c.png" target="_blank">
<img src="https://i.loli.net/2019/04/24/5cbfc62ccf74c.png" width="40%"/></a>

## **4.データの更新**

テーブルデータの更新には、```UPDATE文```を利用します。

```
UPDATE テーブル名　SET 列名1 = 値1,列名2 = 値2,… WHERE [条件];
```

**・実例**[sample5-13.sql]

<a href="https://i.loli.net/2019/04/24/5cbfacc6eddf4.png" target="_blank">
<img src="https://i.loli.net/2019/04/24/5cbfacc6eddf4.png" width="40%"/></a>

## **5.データの削除**

テーブルデータの削除には、```DELETE文```を利用します。

```
DELETE FROM テーブル名 WHERE [条件];
```

**・実例**[sample5-14.sql]

<a href="https://i.loli.net/2020/03/11/7ZhIuoROwJKAa6r.png" target="_blank">
<img src="https://i.loli.net/2020/03/11/7ZhIuoROwJKAa6r.png" width="60%"></a>

***
## **6.1 集約 GROUP BY**
SQLで、列の値を集計する場合、集計はグループ単位で行われます。グループとは特定のカラムに格納されている同じ値を持つデータをまとめたもので、どの列を対象にグループ化するのかを指定するにはGROUP BY句を使用します。
書式は次の通りです。

```
SELECT 列名, ... FROM テーブル名GROUP BY列名, ...; 
```  
GROUP BY句の後に列名を指定すると、そのカラム名に格納されている値が同じデータをグループとしてまとめます。

例えば、次のような、重複するデータが存在する検索結果があったとします。
```
SELECT countryCd FROM empinfo;
```
この実行結果は、以下のようになります。

GROUP BYによって集約します。
```
SELECT countryCd FROM empinfo GROUP BY countryCd;
```
実行結果から国籍の重複が回避されたことがわかります。重複する値が、GROUP BYにより、一つに集約された結果です。 
<a href="https://i.loli.net/2020/03/24/GRMO38djfiEcZAT.png" target="_blank"><img src="https://i.loli.net/2020/03/24/GRMO38djfiEcZAT.png" width="40%"></a>

このような重複の回避は、GROUP BYだけではなく、distinctを用いることでも実現できます。 
```
SELECT distinct countryCd FROM empinfo;
```
<a href="https://i.loli.net/2020/03/24/97OULuARdhJjaY5.png" target="_blank"><img src="https://i.loli.net/2020/03/24/97OULuARdhJjaY5.png" width="40%"></a>

## **6.2 集計処理 集計と関数**

GROUP BYを使う利点は、重複の回避だけではありません。合計値や、平均値の算出などといった、集計処理にも利用できます。書式は以下の通りになります。

### **集計処理**
```
SELECT 列名, ... ,集計関数（集計をとる列名）FROM テーブル名GROUP BY列名, ...; 
```
empinfoテーブルで、以下の通りの検索を行ってみてください。  
```
SELECT countryCd,AVG(year) FROM empinfo GROUP BY countryCd;
```

このSQL文では、 empinfoテーブルをcountryCdでグループ化し、社員の勤務年数の平均値をとっています。AVGは、平均値をとる集計関数です。
<a href="https://i.loli.net/2020/03/24/Krngf7kXRBIomTa.png" target="_blank"><img src="https://i.loli.net/2020/03/24/Krngf7kXRBIomTa.png" width="40%"></a>

GROUP BYで用いられる集計関数には、以下のようなものがあります。

<table border="1">
<tr style="background-color:#C6E0B4 ;text-align:center;"><th>関数名</th><th>意味</th></tr>
<tr><td>MAX</td><td>最大値</td></tr>
<tr><td>MIN</td><td>最小値</td></tr>
<tr><td>SUM</td><td>合計</td></tr>
<tr><td>AVG</td><td>平均</td></tr>
<tr><td>COUNT</td><td>個数のカウント</td></tr>
</table>

HAVING句

集計処理には、条件を付けることも可能です。その際使用するのが、HAVING句です。使用方法は以下の通りです。

``` 
SELECT 列名, ... ,集計関数（集計をとる列名）FROM テーブル名GROUP BY列名, ... HAVING 条件式; 
``` 
中国以外の社員の勤務年数の平均値をとっています。　　
```
SELECT countryCd,AVG(year) FROM empinfo GROUP BY countryCd HAVING countryCd != "001";;

```
実行結果は、以下のようになります。
<a href="https://i.loli.net/2020/03/24/Kr4mTMQUGCkAW7d.png" target="_blank"><img src="https://i.loli.net/2020/03/24/Kr4mTMQUGCkAW7d.png" width="40%"></a>

>**練習**
>>**問題1**[ex5-1.sql]

データの挿入は複数をまとめて挿入できます。
下記データをまとめてcountry と　sex に挿入してください。

>>**問題**[ex5-2.sql]

国別の入社平均年数を求めて、国名を表示します。

<!--
>>**答え**

```select AVG(year),countryName from empinfo INNER JOIN country USING(countryCd) group by countryCd;```

-->

content

第５章 テーブルの操作（後編）

第５章　テーブルの操作（後編）