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：データベースを検索する

SELECT 名前 
FROM 購入リスト 
WHERE id=3;

↓
id=3に対応する名前が検索され、今回の場合の検索結果は”弁当”となります。

INSERT：データを挿入する

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

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

UPDATE：データを更新する

UPDATE 購入リスト
SET 価格=500 
WHERE id=1;

↓
id=1に対応する部分に対して、SETで指定した変更内容が更新されます。

DELETE：データを削除する

DELETE FROM 購入リスト 
WHERE id=1

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

データ定義言語（Data Definition Language　※略DDL）

オブジェクト同士の関係を定義する構文

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

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（登録された日時）

CREATE TABLE madeia.article

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

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

title VARCHAR(50)
- title:カラム名
- VARCHAR（50）:データ型

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

content TEXT
- content:カラム名
- TEXT：データ型

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMPM

• 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	山田

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

実行命令：

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	山田

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

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

実行命令：

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」を例に実行していきましょう。

実行命令：

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

DROP TABLE {テーブル名} CASCADE CONSTRAINTS;

↑完全削除はされず、ごみ箱に移動されます。

FLASHBACK TABLE {テーブル名} TO BEFORE DROP;

↑ごみ箱から削除したテーブルを復活させる

PURGE RECYCLEBIN;

↑ごみ箱を完全消去

DROP TABLE {テーブル名} PURGE;

↑ごみ箱を経由せずに完全にテーブルを削除する

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

ALTER TABLE emp add (
number  varchar2(13),
address varchar2(10)
)

↑表「emp」に列「number」と「address」を追加する

alter table emp modify (
emp_name varchar2(14)
)

↑表「emp」の列「emp_name」の項目長を１０バイトから１４バイトに増やす

update emp set emp_name = null;

ALTER TABLE emp modify (
emp_name varchar2(8)
)

↑表「emp」の列「emp_name」の項目長を１０バイトから８バイトに減らす

ALTER TABLE emp drop ( emp_name )

↑表「emp」の列「emp_name」を削除する

ALTER TABLE emp rename to emp01;

↑表「emp」の名前を 「emp01」に変更する。

ALTER TABLE emp rename column emp_name to e_name;

↑表「emp」の列名を 「emp_name」から「e_name」に変更する。

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

TRUNCATE TABLE (表名);

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

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

データ制御言語（Data Control Language　※略DCL）

データへのアクセスを制御する構文

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

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

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

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

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