概要

データベースの一時表（一時テーブル、temporary table）は、使いこなすととても便利な機能なのですが、唯一扱いづらいところは、別セッションからレコードが覗けないというところです。

この難点のためだけに一時表を使わないのは勿体無いのでどうにかしたいと思います。

ビューは使わない

タイトルにある「定義を変更せず」というのは、一時表の代わりに実テーブルとビューを組み合わせて一時表モドキにするという手法は用いないということです。

やり方

それでは手順を説明します。サンプルのSQLはOracleで実行できる形式とします。
（MySQLやPostgreSQLでも応用可能です）

サンプルテーブル

今回の説明用のサンプルテーブル定義は以下の通りです。

CREATE GLOBAL TEMPORARY TABLE USER1.TMPTBL (
  "ID"   NUMBER(3,0) NOT NULL ENABLE,
  "NAME" CHAR(128)   NOT NULL ENABLE,
   CONSTRAINT TMPTBL_IDX_00 PRIMARY KEY ("ID")
) ON COMMIT PRESERVE ROWS

ログテーブルの作成

一時表の内容は当然他のセッションでは見れませんので、その内容のコピーを記録するログテーブルを作成します。

CREATE TABLE USER1.TMPTBL_LOG (
  "SESSIONID" NUMBER DEFAULT SYS_CONTEXT('USERENV', 'SESSIONID') NOT NULL ENABLE,
  "ID"        CHAR(3)   NOT NULL ENABLE,
  "NAME"      CHAR(128) NOT NULL ENABLE,
  CONSTRAINT TMPTBL_LOG_IDX_00 PRIMARY KEY ("SESSIONID", "ID")
);

一時表と同じテーブル定義にSESSIONID列を追加します。
名前からわかる通りセッションIDを格納します。セッションIDは同時には同じものが存在しない事が保証されますが、時間があくと同じIDを振られる可能性があるので、長時間保存しておく必要がある場合はさらに更新時刻も主キーに追加する必要があります。
（特にPostgreSQLはセッションIDの代わりにサーバプロセスIDを使用するので更新時刻は必須です）

トリガーの追加

一時表（USER1.TMPTBL）にトリガーを追加します。

CREATE OR REPLACE TRIGGER USER1.TMPTBL_TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON USER1.TMPTBL FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO USER1.TMPTBL_LOG(ID,NAME) VALUES(:NEW.ID,:NEW.NAME);
  ELSIF UPDATING THEN
    UPDATE USER1.TMPTBL_LOG SET
      ID = :NEW.ID
      , NAME = :NEW.NAME
    WHERE
      SESSIONID = SYS_CONTEXT('USERENV', 'SESSIONID')
      AND ID = :OLD.ID;
  ELSE
    DELETE FROM
      USER1.TMPTBL_LOG
    WHERE
      SESSIONID = SYS_CONTEXT('USERENV', 'SESSIONID')
      AND ID = :NEW.ID;
  END IF;
END;

これで一時表にINSERT、UPDATE、DELETEが発生した際にログテーブルに内容が書き込まれるので、別セッションから内容を確認できる様になりました。

他のデータベースの場合

MySQL

MySQLの場合は、SYS_CONTEXT('USERENV', 'SESSIONID')をCONNECTION_ID()に変更してセッションIDを取得します。

PostgreSQL

PostgreSQLの場合は、SYS_CONTEXT('USERENV', 'SESSIONID')をpg_backend_pid()に変更してサーバプロセスIDを取得します。
前述の注意通りサーバプロセスIDはそこそこ同じIDが割り当てられる可能性があるので更新時刻も主キーに含めて一意制約違反が発生しない様に注意が必要です。

対象者

ProgateでSQLを学習した方。
情報を整理するためにまとめました。

念頭に置くべきポイント

select * from (テーブル名) where (カラム名) = "テスト";

• selectで対象としているのはカラム(列)
• whereで対象としているのはレコード(行)

完全に正しい考えではないが、このように整理すると理解が進む。

# 基本フォーマット。慣れるまではこのフォーマットを使い、下記の流れに沿ってSQL文を書いていく。
select
from (テーブル名)

;

下記では情報をまとめるために、サブクエリの箇所までは一行にまとめてます。

基本の基本の基本

select * from goods where name like "%商品A%" order by price desc limit 5;

1.goodsテーブルの全てのレコードを対象。
2.nameカラムから「商品A」の文字列を含むものを取得し、そのレコードをpriceカラムを基準に降順で並べる。
3.取得したレコードから、priceが安いトップ５のレコードを取得し、値を返す。

---

• nullにはisを使う(=は使えない)

select * from goods where price is null;

---

• whereを使い。複数のカラムを指定する場合は、「and」[or]を使う。

select name = "商品A" and created_at <= "2020-02-02";

基本の基本

データ加工(指定カラムの合計値や平均値を出すなど)の流れ。
1.データをwhereで検索し、レコードを取得
2.取得したデータを、集計関数を用いて、selectで処理を実行。
※集計関数は、基本selectで指定してあげる。

select max(price) from goods where name = "にんじゃわんこ"

---

カラム名でグループ化した後、データ加工の流れ。
1.データをgroup byでグループ化。（同じカラム名は同じグループになる）
2.取得したデータを、集計関数を用いて、selectで処理を実行。
3.何のカラムをグループ化したか分かりやすい検索結果(レコード)を出すために、グループ化したカラムも、selectしてもらう。
※group byを使う場合、selectで指定できるのは、group byで指定したカラム名と、集計関数のみ。

※group byを用いると、データをグループ化できる。具体的には、指定したカラムで、完全に同一のデータを持つレコードどうしが同じグループとなる！。

select sum(price), created_at, updated_at from goods group by created_at, updated_at;

 # ¥年齢毎の人数と、その年齢を取得
select age, count(*) as "年齢毎の人数"
from users
group by age;
;

select sales_records.purchased_at, SUM(items.price) AS "売上額"
from sales_records
join items
on sales_records.item_id = items.id
group by purchased_at
order by purchased_at ASC;

 # 10個以上購入したユーザーIDとユーザー名、購入した商品の数を取得してください
select users.id, users.name, count(*) as "購入数"
from sales_records
join users
on sales_records.user_id = users.id
group by users.id, users.name
having count(*) >= 10;

 # 「グラタン」を購入したユーザーのidと名前を取得してください
select users.id, users.name
from sales_records
join users
on sales_records.user_id = users.id
where sales_records.item_id = (
  select id
  from items
  where name = "グラタン"
);

# グラタンより売上額が高い商品の指定されたデータを取得してください
select items.id, items.name, items.price * count(*) as "売上額"
from sales_records
join items
on sales_records.item_id = items.id
group by items.id, items.name, items.price
having (count(*) * items.price) > (
  select count(*) * items.price
  from sales_records
  join items
  on sales_records.item_id = items.id
  where items.name = "グラタン"
);

whereで細かい条件を指定してグループ化したデータ加工する場合の流れ
1.whereで検索しレコードを取得。
2.group byでグループ化。
3.selectで集計関数を使い、検索結果を出力。
4.havingで指定した条件があれば、その条件を元に検索結果を出力。(グループ化された後で実行される)

• where:全データ(レコード)を対象。
• having：group byでグループされたレコードを対象。

select sum(price),created_at from goods where name = "商品A" group by created_at having sum(price) > 1000

基本

サブクエリは()で囲む。※()の中には;はいらない。
サブクエリが実行された後で、外側のクエリが実行される。

# 意味が分からなくなるから、先にasで取得したいカラム名を命名する。
select name as "調べたかった商品名"
from goods
where purchased_at > (
  select purchased_at
  from goods
  where price= 1000
)
;

外部キーを使って、テーブルを分けるメリット
→データの管理・更新がしやすい

---

joinは複数のテーブルを1つに結合できる。ONで条件を指定して、結合したテーブルは1つのテーブルとして扱うことができる。
→最初にjoinが実行されてから、selectが実行される。

select *
from users
join blogs
on users.id = blogs.user_id
group by blogs.title
;

• joinしたテーブル間で、同じカラム名があれば、「テーブル名.カラム名」で指定してあげる。
• joinは複数回できる。※fromにかかるテーブルがjoinにかかるテーブルの外部キーを持っている必要あり。
• 外部キーがnullのレコードは、joinに反映されない。 →こんな時はleft joinを使う！！！！ from側に、外部キーを持っているテーブルを指定する！

# 外部キーがnullのレコードも、結合後反映される。
select *
from users
left join blogs
on users.id = blogs.user_id
;

大学の授業でD-Oceanを利用しました。ログイン/登録ページはこちらです。
D-Oceanは一般の人がアクセスできるデータベースのようなものらしいです。公開されている表形式のデータの他に、グループにjoinできれば利用できるデータもあります。今回はとある社員食堂の購買データを分析するべく、必要な分だけのデータを取り出し、ダウンロードしました。
まずデータを選択して現れるポップアップでSQLタグをクリックすると、そのデータを操作するためのSQL分を入力する欄が表示されます。

私が使用したコマンドは以下のようなものです。

sql_command

SELECT COLUMN_2,COLUMN_5 FROM `$group_name.data_file_name`where COLUMN_4="value1" and COLUMN_8="value2" LIMIT 1000

その欄でこのコマンドを実行することは、「group_nameという名前のグループで共有されているdata_file_nameというファイルの格納された表形式のデータから、データ項目COLUMN_4, COLUMN_8がそれぞれvalue1,value2であるデータのうち、COLUMN_2とCOLUMN_5の値を取り出す」ことを意味しています。

このようにして必要な分だけを取り出したあと、クリックひとつでダウンロードできます。

記事は以上です、ありがとうございました。

DBを利用するだけなのに、DAO、entity、XMLを３セット書かなくちゃ！
フレームワークは面倒くさいと思いつつ、今回のライブラリを作成した。

１．メリット

・XML不要（sql文を記述する設定ファイル）
・DAO不要（sqlを実行するクラス）
・フレーム不要

・・・・・・・

Q：なにも不要？ちゃんとしている機能はないじゃない？？
A：いえいえ、sql及びentityによるDB操作はしっかりサポートしているし、かつ一つクラスで完結！

２．ライブラリ紹介

sqlの作成を手助ける

早速サンプルコードを見ましょう！

    SqlWritter writter = new SqlWritter();
    writter.select("*")
            .from("speech_data")
            .where("name")
            .like("新垣結衣 ニンゲン観察バラエティモニタリング");

    System.out.println(writter);

?コンソール出力

select
*
from
speech_data
where
name LIKE '%新垣結衣 ニンゲン観察バラエティモニタリング%'

Q： ('ω') うわあああ！sql文を生成してくれたねといいたかったところ、どういうメリットがあるか？
A：確かにこれだけだと、メリットはあんまり実感できなさそうだね。しかし、以上の機能をコアとして、さらに便利な機能を提供できるんだって！

sqlによる検索

生成したsqlを用いて、DAO不要のメリットを見てみよう。

    List<SpeechData> entities;
    try (Accessor accessor = new Accessor()) {
        entities= accessor.selectBySql(writter, SpeechData.class);
    }

上記のコードを簡単に説明してみると、上記はsql文による検索操作だ。
　SpeechDataは事前作成したspeech_dataというテーブルのエンティティクラス
　entitiesは結果を格納するためのリスト
　Accessorはデータベースへアクセスするためのクラス
　writterは先ほど生成したSQL

Q：うんーーー、よくわからない！
A：大丈夫、覚えておいてほしいのはAccessorクラスはすべてのエンティティクラスに対応できるため、DAOはもう不要だ！

下記のコードを追加して、実行結果を見てみよう。

    for (SpeechData entity : entities) {
        System.out.println(String.format("ファイル：%sの分析結果：", entity.getName()));
        System.out.print(String.format("喜び：%s,", entity.getJoy()));
        System.out.print(String.format("悲しみ：%s,", entity.getSorrow()));
        System.out.print(String.format("怒り：%s,", entity.getAnger()));
        System.out.print(String.format("エネルギー：%s,", entity.getEnergy()));
        System.out.println(String.format("穏やかさ：%s", entity.getCalm()));
        System.out.println();
    }

?コンソール出力

ファイル：新垣結衣 ニンゲン観察バラエティモニタリング_00.wavの分析結果：
喜び：0,悲しみ：9,怒り：0,エネルギー：0,穏やかさ：40

ファイル：新垣結衣 ニンゲン観察バラエティモニタリング_01.wavの分析結果：
喜び：18,悲しみ：2,怒り：0,エネルギー：16,穏やかさ：29

ファイル：新垣結衣 ニンゲン観察バラエティモニタリング_02.wavの分析結果：
喜び：11,悲しみ：0,怒り：0,エネルギー：11,穏やかさ：38

・
・
・

Q：怪しいデータが出てきた！
A：ごめんごめん、音声による感情識別データだ。データの中身はとりあえず無視して、実行したsqlは無事に結果を戻しれくれた。

エンティティによる検索

フレームのように、エンティティによる検索も可能！

    SpeechData speechData = new SpeechData();
    speechData.setName("新垣結衣 ニンゲン観察バラエティモニタリング_02.wav");

    List<SpeechData> entities;
    try (Accessor accessor = new Accessor()) {
        entities = accessor.selectByEntity(speechData);
    }

    for (SpeechData entity : entities) {
        System.out.println(String.format("ファイル：%sの分析結果：", entity.getName()));
        System.out.print(String.format("喜び：%s,", entity.getJoy()));
        System.out.print(String.format("悲しみ：%s,", entity.getSorrow()));
        System.out.print(String.format("怒り：%s,", entity.getAnger()));
        System.out.print(String.format("エネルギー：%s,", entity.getEnergy()));
        System.out.println(String.format("穏やかさ：%s", entity.getCalm()));
        System.out.println();
    }

?コンソール出力

ファイル：新垣結衣 ニンゲン観察バラエティモニタリング_02.wavの分析結果：
喜び：11,悲しみ：0,怒り：0,エネルギー：11,穏やかさ：38

Q：エンティティによる検索だね
A：はい、ライブラリだけど、機能は半端ない！

３．ライブラリ共有

ライブラリを使ってみたい方は、下記のリンクからソースをダウンロードすることができる。
https://github.com/chaofanzheng/leadinge

わからない時があれば、下記の仕様を参考すれば助けになる！！

４．ライブラリを使用するための仕様

システムプロパティ

system.properties

    #mysqlのユーザID（各自の設定に従う）
    USER_ID = root

    #mysqlのパスワード（各自の設定に従う）
    USER_PASSWORD = MySql

    #mysqlへアクセスのURL（各自の設定に従う）
    DB_URL = jdbc:mysql://localhost:3306/speech_recognition?useSSL=false&&allowPublicKeyRetrieval=true

エンティティ

SpeechData.java

//CommonEntityを継承する必要がある
public class SpeechData extends CommonEntity {

    //コンストラクタ
    public  SpeechData() {
        //データタイプの初期化（テーブル情報に合わせて設定する）
        columnsType = new HashMap<String,Class<?>>();
        columnsType.put("id", int.class);
        columnsType.put("name", String.class);
        columnsType.put("wav", byte[].class);
        columnsType.put("error", int.class);
        columnsType.put("calm", int.class);
        columnsType.put("anger", int.class);
        columnsType.put("joy", int.class);
        columnsType.put("sorrow", int.class);
        columnsType.put("energy", int.class);
        //テーブル名を設定する（スネークケース）
        setTableName("speech_data");
    }

    public int getId() {
        return (int)columns.get("id");
    }

    public void setId(int value) {
         columns.put("id",value);
    }

    public String getName() {
        return (String)columns.get("name");
    }

    public void setName(String value) {
         columns.put("name",value);
    }

    public byte[] getWav() {
        return (byte[])columns.get("wav");
    }

    public void setWav(byte[] value) {
         columns.put("wav",value);
    }

    public int getError() {
        return (int)columns.get("error");
    }

    public void setError(int value) {
         columns.put("error",value);
    }

    public int getCalm() {
        return (int)columns.get("calm");
    }

    public void setCalm(int value) {
         columns.put("calm",value);
    }
    public int getAnger() {
        return (int)columns.get("anger");
    }

    public void setAnger(int value) {
         columns.put("anger",value);
    }

    public int getJoy() {
        return (int)columns.get("joy");
    }

    public void setJoy(int value) {
         columns.put("joy",value);
    }

    public int getSorrow() {
        return (int)columns.get("sorrow");
    }

    public void setSorrow(int value) {
         columns.put("sorrow",value);
    }

    public int getEnergy() {
        return (int)columns.get("energy");
    }

    public void setEnergy(int value) {
         columns.put("energy",value);
    }
}

DBを操作するためには

Select.java

    //sqlを作成した場合は...もちろん、外部ファイルでも手書きでもオケ
    SqlWritter writter = new SqlWritter();
    writter.select("*")
            .from("speech_data")
            .where("name")
            .like("新垣結衣 ニンゲン観察バラエティモニタリング");

    List<SpeechData> entities;

    //DBアクセサの初期化
    try (Accessor accessor = new Accessor()) {
        //sqlによる選択
        entities = accessor.selectBySql(writter, SpeechData.class);

        //エンティティの初期化
        SpeechData speechData = new SpeechData();
        speechData.setName("新垣結衣 ニンゲン観察バラエティモニタリング_02.wav");
        //エンティティによる選択
        entities = accessor.selectByEntity(speechData);
    }

Insert.java

    //sqlを作成した場合は...もちろん、外部ファイルでも手書きでもオケ
    SqlWritter writter = new SqlWritter();  
    writter.insertInto("speech_data")
            .colums("id","name")
            .values(999,"新垣結衣 ニンゲン観察バラエティモニタリング_999.wav");

    //DBアクセサの初期化
    try (Accessor accessor = new Accessor()) {
        //sqlによる選択操作
        accessor.insertBySql(writter);

        //エンティティの初期化
        SpeechData speechData = new SpeechData();
        speechData.setId(999);
        speechData.setName("新垣結衣 ニンゲン観察バラエティモニタリング_999.wav");
        //エンティティによるインサート操作
        accessor.insertByEntity(speechData);
    }

５．ソースコードについて

現状ではinsertとselectしかサポートしていないので、
更新や削除でもできるようにしたい場合は...各自がコードを改修してください。

実際にDBへアクセスするときに使っていただくか、またはソースが参考になれば、
うれしいと思います。

RDBとSQL

リレーショナルモデルの構成要素：

• SQL演算子
• データ整合性
• オブジェクト・リレーションの集合

※トランザクション管理はDBMS（データベース管理システム）の機能の一つ

E-Rモデルの構成要素：

• エンティティ
• 属性
• リレーションシップ

Select文

主な機能

• 選択：特定の行の検索（where句）
• 射影：特定の列の指定（select句）
• 統合：表の結合

引用符（''と""）

一重引用符で囲むもの：文字リテラル、日付リテラル、日付書式、数値書式
二重引用符で囲むもの：ネーミング規則に沿わない名前（列別名、オブジェクト名など）、日付書式中の文字リテラル
※列見出しはデフォルト大文字なので「"」が必要

select ename "Emp_Name", deptid "DEPT ID" from emp;
select * from emp where ename='HOGE' and hdate>'00-01-01';

NULLの扱い

• NULLを含む計算式の結果はNULL
• where a <> 100などの時はa=null値も検索対象外

列別名

• 使える：order句
• 使えない：select句(列別名を他の列で使う場合)、where句、group by句、having句

置換変数（&と&&）

• 大文字・小文字は区別されない
• ACCEPTコマンドで値を入力＆定義

ACCEPT emp_id char PROMPT '値を入力してください：'
値を入力してください：130  　※ここで値を入力

• DEFINEコマンドで事前定義　define var_no=120
• UNDEFINEコマンドで変数解除 undefine var_no
• &：実行時に値を入力　
• &&：値の入力と同時に事前定義（そのため、以降のSQL分に引き継がれる）
• set verufy onで置換後の値を表示されるよう設定（デフォルトでON）

初めの"&&EMP_NO"のみ入力を求められるスクリプト例

select * from emp where empno=&&EMP_NO;
update emp set=sal*1.2 where empno=&emp_no; 

行制限（OFFSET句, FETCH句）

• order by句の後に指定
• rowとrowsは同じ
• firstとnextは同じ
• WITH TIESはorder by句と一緒だと有効（一緒でないとONLYと同じ）

select * from emp order by hdate 
offset 10 rows fetch first 20 rows with ties

単一行関数

文字関数

• LOWER: 文字列を小文字に変換
• UPPER: 文字列を大文字に変換
• INITCAP: 文字列の先頭を大文字に、移行を小文字に変換
• CONCAT: 文字列を連結
• SUBSTR：文字列の指定位置から指定文字数分を抽出(デフォルトは最後までの文字列を抽出)
• LENGTH：文字列の長さを表示
• INSTR：指定文字列の(先頭からの)数値位置を表示（デフォルトは先頭1文字目から検索して、1回目の出現位置を表示）（見つからない場合は0を返す）
• LPAD：文字列の左側に指定文字を埋め込む
• RPAD：文字列の右側に指定文字を埋め込む
• TRIM：先行、後続文字の切り捨て
• REPLACE：文字列を置換

例	結果
LOWER('Hoge Fuga')	hoge fuga
UPPER('Hoge Fuga')	HOGE FUGA
INITCAP('Hoge Fuga')	Hoge Fuga
CONCAT('Hoge Fuga','Boo')	Hoge FugaBoo
SUBSTR('Hoge Fuga',2,6)	oge Fu
SUBSTR('Hoge Fuga',-4)	Fuga
LENGTH('Hoge Fuga')	9
INSTR('Hoge Fuga','g')	3
INSTR('Hoge Fuga','g',-1)	8
INSTR('Hoge Fuga','g',-1,2)	3
LPAD('Hoge Fuga',12,'*')	***Hoge Fuga
RPAD('Hoge Fuga',12,'*')	Hoge Fuga***
TRIM('g', FROM 'Hoge Fugagg')	Hoge Fuga
TRIM('   Hoge Fuga  ')	Hoge Fuga
TRIM('g', FROM 'Hoge Fuga')	Hoe Fua
REPLACE('Hoge Fuga','g','m')	Home Fuma

数値関数

• ROUND：数値を丸める（四捨五入する）
• TRUNC：数値を切り捨てる

※どちらも第二引数のデフォルトは0で整数を返す
※+1毎に右側（-1毎に左側）に丸める（切り捨てる）桁が移動する

例	結果
ROUND(123.567)	124
ROUND(123.567, 2)	123.57
TRUNC(123.567)	123
TRUNC(123.567, -2)	100

日付関数

• LAST_DAY：指定日付が含まれる月の最終日を表示
• NEXT_DAY：指定日付より後の最初の指定曜日の日付を表示
• CURRENT_DATE：セッションタイムゾーンの現在日付を表示（SYSDATEはDBが存在するOSの現在日時を表示）

例	結果
LAST_DAY('01-FEB-14')	28-FEB-14
NEXT_DAY('02-APR-19','MONDAY')	8-APR-19
NEXT_DAY('02-APR-19','2')	8-APR-19