LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@yamashun38

【Oracle SQL】基礎

Last updated at Posted at 2024-12-25

概要

  • SQLの基礎について
  • 一部Oracle SQLのみのものもあるかも

テーブル作成

CREATE TABLE テーブル名 (
  カラム名 データ型 (制約),
  カラム名 データ型 (制約),
  ...
);
  • 指定したデータ型のカラムを持つテーブルを作成
  • 必要に応じて制約を付与

データ型

データ型 意味
CHAR 固定長の文字列
VARCHAR2 可変長の文字列
NUMBER 整数
BINARY_FLOAT 32ビットの浮動小数点
DATE 年月日
yyyy-mm-ddの形式で記述
BLOB バイナリデータ
  • CHARやVARCHAR2、NUMBERの後には()で長さを指定する
  • CHAR(10)の場合は10バイトの文字列を扱う
  • CHARとVARCHAR2は、固定長か可変長かが異なる
    • 固定長の場合は、()で指定したバイト数を常に確保する
    • 可変長の場合は、実際の文字列分のバイト数を確保する
      ※()で指定したバイト数以上は確保不可
  • OracleでDATE型のカラムの値をINSERTする場合は、VALUES(値1, 値2, DATE 'yyyy-mm-dd', 値4, ...)という形で記述する
    (dateを先頭につける必要がある)

制約

制約 意味
NOT NULL 値が空欄はNG
PRIMARY KEY 主キー
FOREIGN KEY (カラム名) REFERENCES テーブル名(カラム名) 外部キー

テーブル定義の確認

DESC テーブル名;
  • テーブルに登録されているのカラムの定義を表示
  • DESCはDESCRIBEでも可

テーブル削除

DROP TABLE テーブル名;
  • テーブル名で指定したテーブルを削除する
  • 制約があって削除できない場合はCASCADE CONSTRAINTSを付けて制約ごと削除可能
  • 削除するとリサイクル・ビンというゴミ箱に入る
    • リサイクル・ビンからも削除したい場合は、テーブル名の後にPURGEを付ける

レコード登録

INSERT INTO テーブル名(カラム1, カラム2, ...) VALUES(値1, 値2, ...);
  • 新規レコードを作成
  • カラムに対応する値をそれぞれ指定する
  • NOT NULLがついているカラムは必ず指定する
  • 指定をしていないカラムはNULLになる
  • カラム名はそのまま記述するかダブルクォーテーションで囲む
    ※シングルクォーテーションでは囲まない
  • 値は、型がvarcharなどの文字列の場合はシングルクォーテーション囲み、numberなどの数値の場合はそのまま記述
  • テーブル名(カラム1, カラム2, ...)を省略する場合は全てのカラムの値を指定する必要あり

レコード更新

UPDATE テーブル名 SET カラム1 = 値1, カラム2 = 値2, ... WHERE 条件;
  • 条件に当てはまるレコードのカラムを、それぞれ指定した値に更新する

レコード削除

DELETE FROM テーブル名 WHERE 条件;
  • 条件に当てはまるレコードを削除
  • WHERE句を指定しないと全件削除となるため注意

データ抽出

SELECT * FROM テーブル名;
  • テーブルにある全てのカラムを抽出
SELECT カラム名, カラム名 FROM テーブル名;
  • 指定したカラムのみを抽出
  • 指定したカラムに対して+、-、*、/などの演算子を使用することも可能
    • 日付に対して演算を行うことも可能
  • NULL値に対して演算を行うと結果はNULLになる
  • テーブル名にDUALを指定すると、ダミーのテーブルで結果を得ることが可能
SELECT カラム名 AS 別カラム名 FROM テーブル名;
  • カラム名に別名を付けて抽出
  • ASは省略可能
  • 別カラム名にスペースや記号を入れたい場合は別カラム名全体を""で囲む
    ※''ではないので注意 
SELECT カラム名1 || カラム名2 || カラム名3 FROM テーブル名;
  • カラムを結合して表示
  • ||でカラムではなく文字列を結合する場合は''で囲む
SELECT DISTINCT カラム名　FROM テーブル名;
  • カラムの中で重複した値を持つ行を削除して抽出
  • DISTINCTはNULLに対しても有効（NULLが1行にまとめられる）

抽出条件

SELECT カラム名 FROM テーブル名 WHERE 条件式
  • 条件式に従ってテーブルからカラムを抽出
  • 条件式は下記のような形式で記述する
条件式 意味
カラム名 = 100 カラムの値が100に等しいデータ
文字列の場合は''で囲む
カラム名 >= 1000 カラムの値が1000以上
カラム名 <= 1000 カラムの値が1000以下
カラム名 <> 1000
カラム名 != 1000		 カラムの値が1000ではない
カラム名 BETWEEEN 1000 AND 2000 カラムの値が1000以上、2000以下
カラム名 IN (100, 200, 300) カラムの値が100, 200, 300
カラム名 LIKE 'J%' カラムの値がJで始まる
%は何文字でも可(ワイルドカード)
データが数字の場合でも''で囲む必要あり
カラム名 LIKE '_LEAK' カラムの値の1文字目が不明で、2文字目以降がLEAKとなっているデータ
_は不明な文字数分必要
データが数字の場合でも''で囲む必要あり
カラム名 IS NULL カラムの値がNULL
カラム名 IS NOT NULL カラムの値がNULLでない
条件1 AND 条件2 条件1と条件2どちらも満たす
条件1 OR 条件2 条件1か条件2のいずれかを満たす
  • ANDとOR両方を条件式に記述している場合、ANDが優先して評価される

置換変数

WHERE カラム名 = &変数名;
  • 条件を変数化し都度置き換えることが可能
  • SQL文を実行すると、変数名にあたる部分の値を選択するよう画面に表示される
  • 入力した値が&変数名に置き換わり、SQLが実行される

image.png

ソート（並び替え）

ORDER BY カラム1, カラム2 ASC;
  • カラム1、カラム2の順でソート
  • ASCを付けると昇順にソートされるが、標準で昇順となっているため省略可
  • NULLは一番最後に表示される
    • NULLS FIRSTを付けるとNULLを最初に表示可能
ORDER BY カラム1, カラム2 DESC;
  • カラム2、カラム1の順でソート（降順）
  • NULLは一番最初に表示される
    • NULLS LASTを付けるとNULLを最後に表示可能

文字列操作関数

SQL文 実行結果 例
LOWER(カラム名) 大文字を小文字に変換 ORACLE ⇒ oracle
UPPER(カラム名) 小文字を大文字に変換 oracle ⇒ ORACLE
INITCAP(カラム名) 先頭文字を大文字、以降を小文字に変換 ORACLE MASTER ⇒ Oracle Master
SUBSTR(カラム名, 値1, 値2)
※値2は省略可		 文字列の値1番目から値2個分までを抽出 SUBSTR('ORACLE', 2, 4)⇒ RACL
LENGTH(文字列) 文字数を返す LENGTH('おらくる') ⇒ 4
LENGTHB(文字列) バイト数を返す LENGTHB('おらくる') ⇒ 12
INSTR(カラム名, 文字列, 値1, 値2)
※値1, 値2は省略可		 文字列が出現する場所を返す。値1は開始場所、値2は文字列が出てくる回数 INSTR('ABCDEABC', 'C', 3,2) ⇒ 8
INSTR('ABCDEABC', 'C') ⇒ 3
LPAD(カラム名, 値1, 文字) 値1の長さになるまで文字を左に埋める LPAD('ORACLE', 10 '*') ⇒ ****ORACLE
RPAD(カラム名, 値1, 文字) 値1の長さになるまで文字を右に埋める RPAD('ORACLE', 10 '') ⇒ ORACLE***
TRIM (文字列) 前後の空白を除く TRIM(' ORA CLE ') ⇒ORA CLE
REPLACE(文字列, 文字1, 文字2) 文字1を文字2に置換する REPLACE('AABBCC','B', 'D') ⇒ AADDCC

数値関数

SQL文 実行結果 例
ROUND(数字1, 数字2)
※数字2は省略可		 数字1の小数点以下を数字2で指定した桁で四捨五入
数字2を0にすると整数になる		 ROUND(55.56,1)⇒ 55.6
ROUND(55.56) ⇒ 56
TRUNC(数字1, 数字2)
※数字2は省略可		 数字1の小数点以下を数字2で指定した桁で切捨て TRUNC(55.56,1) ⇒55.5
TRUNC(55.56) ⇒ 55
MOD(数字1, 数字2) 数字1を数字2で割った余り MOD(55,3) ⇒ 1
CEIL(数字) 数字の小数点以下を切上 CEIL(55.56) ⇒ 56
FLOOR(数字) 数字の小数点以下を切捨 FLOOR(55.56)⇒ 55
POWER(数字1, 数字2) 数字1を数字2で累乗した値を返す POWER(3, 2) ⇒3の2乗⇒ 9
SQRT(数字) 数字の平方根を返す SQRT(4) ⇒ 4の平方根⇒ 2

集約関数 / 集計関数

集約関数

SQL文 実行結果
GROUP BY カラム1, カラム2, カラム3, ... 指定したカラム内で重複する値ごとに集約を行う
NULLも1つのグループとして集約される
GROUP BYを使用した場合、SELECT句ではGROUP BYで指定したカラムのみを使用可能
HAVING フィルタリング条件 GROUP BY句によって集約されたデータに対してフィルタリングをかける
基本的にWHERE句と同じ書き方が可能

集計関数

SQL文 実行結果
COUNT(カラム) 指定したカラムに一致するカラムの件数を出力
SUM(カラム) 指定したカラムに一致するカラムの値の合計を出力
AVG(カラム) 指定したカラムに一致するカラムの値の平均を出力
MAX(カラム), MIN(カラム) 指定したカラムの中の最大値、最小値を出力
  • 集計関数は集約関数を使っている場合のみに使用可能
  • 集計関数はWHERE句では使用不可（詳細は関数の実行順序を参照）

NULL関連

NVL関数

SELECT VNL(カラム, 値) FROM テーブル名 WHERE 条件式;
  • 第1引数のカラムがNULLだった場合、第2引数の値を返す
  • 第1引数のカラムがNULLでない場合、そのままカラムの値を返す
  • 第1引数にはカラムの値や演算処理などを指定可能
  • Oracleのみで使用可能
    ※MySQLやPostgreSQLなど他のDBMSでは後述のCOALESCE関数を使用

例

  • 下記のようなテーブルで入社日(JOIN_DAY)がNULLの場合は2000/1/1と表示する
ID NAME AGE JOIN_DAY JOB_NAME
0001 Johnny 23 2020/8/25 salesman
0002 Tom 15 2019/4/1 engineer
0003 Jack 24 salesman
0004 Andrew 32 2015/6/28 president 
SELECT ID, NAME, VNL(JOIN_DAY, '2000/1/1') AS 入社日 FROM EMPLOYEE;
  • 上記を実行するとJackの入社日が2000/1/1となる

COALESCE関数

SELECT COALESCE(式1, 式2, ...) FROM テーブル名 WHERE 条件式;
  • 引数の式を左から順に評価し、最初に見つかったNULL以外の値を返す

日付の計算, 日付関数

SQL文 実行結果
SYSDATE 実行した日付
日付 + 数値 日付に数値分の日数が足される
日付 - 数値 日付から数値分の日数が引かれる
日付 + 日付 不可

SQL文 実行結果
ADD_MONTHS(日付, 値) 値の月だけ日付を加算
値が負の数の場合は減算
LAST_MANTHS(日付) 日付の月末の日を返す
NEXT_DAY(日付, 曜日) 日付で指定した日の次の曜日の日を返す
MONTHS_BETWEEN(日付1, 日付2) 日付1と日付2の間の月数を返す
  • 日付についてはYY-MM-DDという形式で記述する

型変換関数

SQL文 実行結果
TO_CAHR(数値) 数値を文字列に変換
TO_CAHR(数値, 'L999') 数値を文字列に変換し、先頭に¥を付ける
TO_CAHR(数値, '999.0') 数値を文字列に変換し、数値が整数の場合は小数点以下を表示する
TO_CAHR(数値, '999,999,999') 数値を文字列に変換し、3桁区切りに,を追加する
TO_NUMBER(文字) 文字もしくは文字列を数値に変換
TO_DATE(文字列) 文字列を日付(YY-MM-DD)に変換
'YYYY-MM-DD' 年を西暦で表示
'HH24:MI:SS' 時刻を表示
HH24は24時間表記で、HHは12時間表記

表の結合

内部結合

  • 2つの表のうち、結合条件に一致するもののみを抽出する結合
SELECT カラム1, カラム2, ... 
FROM テーブル1, テーブル2
WHERE テーブル1.カラム1 = テーブル2.カラム1;
  • テーブル1のカラム1の値とテーブル2のカラム1の値を比較
  • 等しいフィールドがある場合、テーブル1と2を結合しカラム1、カラム2を抽出する
  • 結合するテーブルにおいてカラム名の重複がある場合は、テーブル名.カラム名と表記する
  • WHERE句を記述せずに表を結合してしまう(結合条件を記述しない)と、データベースに負荷をかけてしまうため避ける（直積）
SELECT カラム1, カラム2, ...
FROM テーブル1 (INNER) JOIN テーブル2
ON テーブル1.カラム1 = テーブル2.カラム1;
  • 上記のWHERE句を用いた結合と同義
  • INNERは省略可能
  • ON句にはほかの表から値を参照する際に、参照する列と参照される列の関係を記述（結合条件）
  • 参照する列と参照される列はE-R図から判断する

(補足)テーブルに別名を付ける

FROM テーブル1 A, テーブル2 B;
  • テーブルに別名を付ける
  • 上記ではテーブル1にA、テーブル2にBという別名を付けている
  • A.カラム名と表記できるだけでテーブル名を変更しているわけではない

外部結合（左側一致）

  • 2つの表のうち結合条件の左側一致するもののみを抽出する結合
  • 割と使う
SELECT カラム1, カラム2, ...
FROM テーブル1 LEFT JOIN テーブル2
ON テーブル1.カラム1 = テーブル2.カラム1;

外部結合（右側一致）

  • 2つの表のうち結合条件の右側一致するもののみを抽出する結合
  • あまり使うことはない
SELECT カラム1, カラム2, ...
FROM テーブル1 RIGHT JOIN テーブル2
ON テーブル1.カラム1 = テーブル2.カラム1;

外部結合（全て）

  • 2つの表のうち結合条件の一致するもの全てのみを抽出する結合
  • 左側一致と右側一致を合わせた結合
SELECT カラム1, カラム2, ...
FROM テーブル1 FULL OUTER JOIN テーブル2
ON テーブル1.カラム1 = テーブル2.カラム1;

(参考) 左側一致と右側一致の違いについて

条件分岐

  • 条件分岐を行う
  • 単純CASE式と検索CASE式の2種類がある

単純CASE式

CASE 条件分岐判断対象のカラム(複数可)
  WHEN '値1' THEN '結果1'
  WHEN '値2' THEN '結果2'
  ...
  ELSE '結果n'
END
-- もしくは
CASE 
  WHEN 条件分岐判断対象のカラム IN '値1' THEN '結果1'
  WHEN 条件分岐判断対象のカラム IN '値2' THEN '結果2'
  ...
  ELSE '結果n'
END
  • 等価式がTRUEかFALSEかを判断する際に使用
  • どの条件にも当てはまらない場合はELSEに記載した処理を実行

検索CASE式

CASE
  WHEN 条件分岐判断対象のカラム 条件式 THEN '結果1'
  WHEN 条件分岐判断対象のカラム 条件式 THEN '結果2'
  ...
  ELSE '結果n'
END
  • 単純にTRUEかFALSEでは判断できない条件の際に使用
  • どの条件にも当てはまらない場合はELSEに記載した処理を実行
  • 条件式の部分では比較条件など(=, BETWEEN, NULL, NOT, AND)を使用可能

CASEとSUMの特殊な使用

  • CASEとSUMを併用することで、複数行のレコードを1行で集計することが可能
SELECT 
    SUM (CASE WHEN カラム = 値1 THEN 1 END), 
    SUM (CASE WHEN カラム = 値2 THEN 1 END), 
    SUM (CASE WHEN カラム = 値3 THEN 1 END), 
    ...
FROM テーブル;
  • 集計対象のカラムが値1の場合、値2の場合、値3の場合、のように値ごとにカラムを作成
  • 各SUM関数の中では、当てはまる値が見つかった場合はビットフラグを立て、最終的にそのフラグの数をSUM関数でカウントしている

例

  • 下記のテーブルにおいて各血液型の人数を1行で集計したい場合を考える
    ※A, B, O, AB, 不明の4列を作成するイメージ
ID NAME BLOODTYPE
1 大山　加奈子
2 栗原　江見 B
3 中田　秀樹 A
4 斧　真一 O
5 中村　瞬次 A
6 末長　信吾 A
7 高橋　直美 O
8 渋江　洋子 AB
9 高橋　幸雄 O
  • 下記の通りSQL文を記述
  • A型のユーザが見つかったら2行目のSUM関数内のビットを立てる
  • B型以降も同様
select
	sum (case when bloodtype = 'A' then 1 end) A型, 
	sum (case when bloodtype = 'B' then 1 end) B型, 
	sum (case when bloodtype = 'O' then 1 end) O型, 
	sum (case when bloodtype = 'AB' then 1 end) AB型, 
	sum (case when bloodtype is null then 1 end) 血液型不明
from テーブル;
  • 実行結果は下記の通り
  • 各血液型の人数を1行にまとめることができた
A型 B型 O型 AB型 血液型不明
3 1 3 1 1

副問い合わせ（サブクエリ）

SELECT カラム1 FROM テーブル2 WHERE (SELECT カラム2 FROM テーブル2 WHERE 条件式)
  • SELECT文の中にさらにSELECT文を使用したSQL
  • 前者のSELECT文を主問い合わせ、後者を副問い合わせ(サブクエリ)という
  • 副問い合わせは主問い合わせより先に実行される
  • FROM句で副問い合わせを実施することも可能

EXISTS

SELECT カラム FROM テーブル WHERE EXISTS (副問い合わせ)
  • 表に特定の行が存在するかを確認する副問い合わせ
  • WHERE句の中で使用し、EXISTSやNOT EXISTSの後ろに副問い合わせがくる

集合演算子

  • 2つ以上のSQLの抽出結果を1つの結果として抽出する処理
    (ANDやORのようなイメージ)
SELECT カラム1 FROM テーブル1
UNION
SELECT カラム2 FROM テーブル2
SQL文 実行結果
UNION 2つ以上のSQLの抽出結果を連結し重複した行を削除して結合した結果を出力
(＝同じ行が2つ以上あっても1行しか抽出されない)
UNION ALL 2つ以上のSQLの抽出結果を連結し重複した行も含めて結合した結果を出力
(＝同じ行が2つ以上あった場合は全て抽出される)
  • 抽出結果のカラム名は1つ目のSQL文で指定したカラム名となる
  • ORDER BY句を使用する場合は一番最後に記述し、1つ目のSQL文のカラム名を指定する必要がある
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?