Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
0
Help us understand the problem. What is going on with this article?
@hacoq

SQLまとめ(DMLとDDL)

More than 1 year has passed since last update.

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

クエリ

データベースに対する命令そのものを指します。そのクエリをデータベースが認識出来るように言語化したものが"SQL"です。

テーブル

データベースは表として管理されており、この表をテーブルと言います。
この表の縦の列を「カラム」、横の列のことを「レコード」と言うことも覚えておいた方が良いでしょう。

SQLの記述

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

<購入リスト>

id 名前 価格 購入日
300 2019-04-02
ペン 300 2019-03-01
弁当 500 2019-04-02

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

データ操作言語(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文では、下記の4つのカラムをもつテーブルを作成しています。

・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種類存在します。今回は以下の二つのテーブルをこの3種類の方法で結合させてみましょう。

・teamテーブル

team_id team
01 チームA
02 チームB
03 チームC

・userテーブル

id team_id name
01 佐藤
02 鈴木
02 山田

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

実行命令:

SELECT * 
FROM team 
CROSS JOIN user;

実行結果:

team_id team id team_id name
01 チームA 1 01 佐藤
02 チームB 1 01 佐藤
03 チームC 1 01 佐藤
01 チームA 2 02 鈴木
02 チームB 2 02 鈴木
03 チームC 2 02 鈴木
01 チームA 3 02 山田
02 チーム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
01 チームA 1 01 佐藤
02 チームB 2 02 鈴木
02 チームB 3 02 山田

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

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

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

実行命令:

SELECT * 
FROM team 
LEFT OUTER JOIN user ON team.team_id = user.team_id;

実行結果:

team_id team id team_id name
01 チームA 1 01 佐藤
02 チームB 2 02 鈴木
02 チームB 3 02 山田
03 チームC NULL NULL NULL

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

〜さらに詳しい3種類の「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」の項目長を10バイトから14バイトに増やす

update emp set emp_name = null;

ALTER TABLE emp modify (
emp_name varchar2(8)
)

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

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:トランザクションを取り消す

0
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
0
Help us understand the problem. What is going on with this article?