SQLまとめ(DMLとDDL)

**SQLとはデータベースを扱うための言語です。**
ここではSQLにおける基礎知識を以下にまとめていきます。

##クエリ
データベースに対する命令そのものを指します。そのクエリをデータベースが認識出来るように言語化したものが"SQL"です。
##テーブル
データベースは表として管理されており、この表をテーブルと言います。
この表の縦の列を「カラム」、横の列のことを「レコード」と言うことも覚えておいた方が良いでしょう。

##SQLの記述
クエリとテーブルを理解したら実際に以下のようなデータベースを扱ってみましょう。

＜購入リスト＞

| id|名前|価格 |購入日|
|:-:|:-:|:-:|:-:|:-:|
|  １ |  本 |  300 |2019-04-02|
|  ２ |  ペン |  300 |2019-03-01|
|  ３|  弁当 |  500 |2019-04-02|


**SQLは以下の２種類の言語から大きく構成されています。ここではそれぞれの内容と使い方をまとめています。（厳密に分類すれば他にも言語はありますが今回は２つ紹介します。）**

##データ操作言語（Data Manipulation Language ※略DML）
データベースを操作するための構文



**SELECT：データベースを検索する**

```sql
SELECT 名前 
FROM 購入リスト 
WHERE id=3;
```
↓
id=3に対応する名前が検索され、今回の場合の検索結果は”弁当”となります。


**INSERT：データを挿入する**

```sql
INSERT INTO  購入リスト
VALUES ('4', 'プリン' , '300' , '2019-03-01' );
```

↓
VALUESで指定したデータが追加されます。

**UPDATE：データを更新する**

```sql
UPDATE 購入リスト
SET 価格=500 
WHERE id=1;
```
↓
id=1に対応する部分に対して、SETで指定した変更内容が更新されます。

**DELETE：データを削除する**

```sql
DELETE FROM 購入リスト 
WHERE id=1
```
↓
id=1に対応するレコードが購入リストから削除されます。


##データ定義言語（Data Definition Language　※略DDL）
オブジェクト同士の関係を定義する構文

**CREATE：新しいデータベースやテーブルなどのオブジェクトを定義する**

```sql
CREATE TABLE  madeia.article （
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(50),
content TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
）;
```
↓
上記のSQL文では、下記の４つのカラムをもつテーブルを作成しています。

・id（ID）
・title（タイトル）
・content（本文）
・created_at（登録された日時）


<font color="red">CREATE TABLE  madeia.article</font>

"media"というデータベースの中に"article"というテーブルを作成する。

<font color="red">id INT AUTO_INCREMENT NOT NULL PRIMARY KEY</font>
- `id`：カラム名
- `INT`：データ型
- `AUTO_INCREMENT`：自動的に値が1ずつ増えていく設定
- `NOT NULL`：空データを許さない設定
- `PRIMARY KEY`：データ（レコード）を一意に特定するためのカラムを決めるもので、1つのテーブルに必ず1つは必要

<font color="red">title VARCHAR(50)</font>
- `title`:カラム名
- `VARCHAR（50）`:データ型

VARCHAR型は文字列であり、(50)は50文字と制限するためのもの

<font color="red">content TEXT</font>
- `content`:カラム名
- `TEXT`：データ型

<font color="red">created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMPM</font>

- `created_at`:カラム名
- `TIMESTAMP`：データ型
- `NOT NULL`：空データを許さない設定
- `DEFAULT CURRENT_TIMESTAMP`：現在日時を保存すると言う意味です。

**JOIN：テーブル同士を結合する**

複数テーブルの結合を行いたいときに使用する命令です。「JOIN」の種類は大きく分けると、クロス結合、内部結合、外部結合の3種類存在します。今回は以下の二つのテーブルをこの３種類の方法で結合させてみましょう。

・teamテーブル

| team_id|team|
|:-:|:-:|
|  0１ |  チームA | 
|  0２ |  チームB |
|  0３ |  チームC |


・userテーブル 

|id| team_id|name|
|:-:|:-:|:-:|
|１|  0１ |  佐藤 | 
|２|  0２ |  鈴木 |  
|３|  02 |  山田 | 


**クロス結合**
-すべての組み合わせ作成する結合方式

実行命令：

```sql
SELECT * 
FROM team 
CROSS JOIN user;
```
実行結果：

| team_id|team|id| team_id|name|
|:-:|:-:|:-:|:-:|:-:|
|  0１ |  チームA |1|01|佐藤|
|  0２ |  チームB |1|01|佐藤|
|  03 |  チームC |1|01|佐藤|
|  0１ |  チームA |2|02|鈴木|
|  0２ |  チームB |2|02|鈴木|
|  03 |  チームC |2|02|鈴木|
|  0１ |  チームA |3|02|山田|
|  0２ |  チームB |3|02|山田|
|  03 |  チームC |3|02|山田|

↑存在しえる、すべての組み合わせを作ることができています。

**・内部結合**
-指定した関連性のある要素を軸にして、組み合わせてくれる命令

実行命令：

```sql
SELECT * 
FROM team
INNER JOIN user ON team.team_id = user.team_id;
```
実行結果：

| team_id|team|id| team_id|name|
|:-:|:-:|:-:|:-:|:-:|
|  0１ |  チームA |1|01|佐藤|
|  0２ |  チームB |2|02|鈴木|
|  0２ |  チームB |3|02|山田|

↑team_idを軸にして、関連している要素で結合できています。

**・外部結合**
-外部結合とは「内部結合 + α」の情報を表示する方式です。
「+α」部分として、「LEFT OUTER JOIN」「RIGHT OUTER JOIN」「FULL OUTER JOIN」 ３種類の結合方式が存在します。

今回は「LEFT OUTER JOIN」を例に実行していきましょう。



実行命令：

```sql
SELECT * 
FROM team 
LEFT OUTER JOIN user ON team.team_id = user.team_id;
```
実行結果:

| team_id|team|id| team_id|name|
|:-:|:-:|:-:|:-:|:-:|
|  0１ |  チームA |1|01|佐藤|
|  0２ |  チームB |2|02|鈴木|
|  0２ |  チームB |3|02|山田|
|  03 |  チームC |NULL|NULL|NULL|

↑関連性はなくとも、FROM側のテーブルに、レコードとして存在する内容も合わせて表示してくれています。


〜さらに詳しい３種類の「JOIN」の説明〜
LEFTとは左側、つまりFROMで指定した側のテーブルを指します。このテーブル側を軸に情報を表示するのが「LEFT JOIN」です。それとは逆にJOINしている側のテーブルを軸に情報を表示するのが「REIGHT JOIN」。両方のテーブルを対象とするのが「FULL OUTER JOIN」です。

**DROP：定義したオブジェクトを削除する**
ーテーブルのデータではなく、テーブル自体を削除します。
以下に具体的な使い方をまとめていきます。


```sql
DROP TABLE {テーブル名} CASCADE CONSTRAINTS;
```
↑完全削除はされず、ごみ箱に移動されます。


```sql
FLASHBACK TABLE {テーブル名} TO BEFORE DROP;
```
↑ごみ箱から削除したテーブルを復活させる

```sql
PURGE RECYCLEBIN;
```
↑ごみ箱を完全消去

```sql
DROP TABLE {テーブル名} PURGE;
```
↑ごみ箱を経由せずに完全にテーブルを削除する

**ALTER：定義したオブジェクトの内容を変更する**
-テーブルに関する様々な事に対して変更を行うことができます。
以下に具体例を挙げていきます。

```sql
ALTER TABLE emp add (
number  varchar2(13),
address varchar2(10)
)
```
↑表「emp」に列「number」と「address」を追加する

```sql
alter table emp modify (
emp_name varchar2(14)
)
```
↑表「emp」の列「emp_name」の項目長を１０バイトから１４バイトに増やす

```sql
update emp set emp_name = null;

ALTER TABLE emp modify (
emp_name varchar2(8)
)
```
↑表「emp」の列「emp_name」の項目長を１０バイトから８バイトに減らす

```sql
ALTER TABLE emp drop ( emp_name )
```
↑表「emp」の列「emp_name」を削除する

```sql
ALTER TABLE emp rename to emp01;
```
↑表「emp」の名前を 「emp01」に変更する。


```sql
ALTER TABLE emp rename column emp_name to e_name;
```
↑表「emp」の列名を 「emp_name」から「e_name」に変更する。


**TRUNCATE：データをすべて削除する**

```sql
TRUNCATE TABLE (表名);
```
↑表内のデータを全削除します
TRUNCATE文はWHERE句で指定できませんのでテーブルのデータを全て削除します。
テーブルごと削除してから再作成するのでDELETE文よりも高速です。


**DML、DDL以外の言語として挙げらるものとしてDCLがあります。以下に簡単にまとめておきます。**


##データ制御言語（Data Control Language　※略DCL）
データへのアクセスを制御する構文

GRANT：ユーザー権限を付与する

REVOKE：ユーザー権限を削除する

BEGIN：トランザクションを開始する

COMMIT：トランザクションを確定する

ROLLBACK：トランザクションを取り消す