tkinterが使いたかっただけなのに一大事だった

やっぴー。
Pythonのデータベースプログラミングの本をやっていたら
「じゃあじゃあGUIアプリを作ってみようね！まずはimport tkinterだよ！」って書いてるから

import tkinter

やってみたら，モジュールがないですよと言われてしまった。
（スクショしておけばよかった。）

「tkinter　インストール」とかで調べると出てくるコマンド

sudo apt-get install tk-dev

これもできない。

色々と調べると

import sys
print("sys.executable: ", sys.executable)

これをやったときにPythonが /.pyenv/の配下にいるのが悪いらしい。

ここにいるPython3系をアンインストールすることになった。

 pyenv uninstall 3.7.3

で，Python3が消えて，デフォルトのPython2が残ってることを確認。
Pythonの公式サイトからパッケージでインストールし直した。

この時点でpythonっていうコマンドで起動するのは3系ではなく，2系だったので，

alias python="python3"

というコマンドをやってみたら，デフォルトで起動するPythonが2系から3系に変わった。

SQLite使えなくなってたらやべーと思って一応tkinterと共に確認。

import tkinter
import sqlite3

Python3でtkinterとSQLiteが共に使用できることが確認できた。

メモ：PythonでDBを使用する①

◯私の現在の状態
Python3系が使えるようになった（環境の話）
SQLがだいたいわかる，勉強した。
エディタはAtomを使用している。
DBを使用したWebアプリを何か作りたい。
弊社の蔵書管理がアナログなのでここをなんとかするものを作ってみよう。

買った本👇(´･ω･｀)

Pythonデータベースプログラミング入門

1〜5章はSQLの解説がメインっぽい。
SQLはわかるけど，Pythonがわからない人間にとって必要だった知識を抜粋する。

・PythonにはSQLiteというDBMSが標準で組み込まれている
SQLiteはデータベースサーバとしてのプロセスは不要で，アプリから直接データベースを扱うことができる。

SQLiteを使えるようにする。（Pythonの一次プロンプトに対して入力する）

import sqlite3

データベースに接続するには

conn = sqlite3.connect("Customer.db",isolation_level=None)

sqlite3.connectの最初の引数（ここではCostomer.db）が存在している場合は，そのDBへ接続。存在しなかった場合は新たに作成する。
2つめの引数（isolation_level=None)は変更をデータベースに自動的に反映させる，という設定を入れている。
変更をデータベースに自動反映させない場合は2つめの引数を省略する。

赤丸はカレントディレクトリに作成されたCustomer.db
右下はターミナルの画面。

db1

データベースに対してSQLを実行するには，適当な変数にSQL文を格納してから実行するか，そのまま実行するかのどちらかっぽい。

conn.execute("INSERT INTO Member VALUES('1001','Alice',12,'Alice@ppp.ja')")

sql="INSERT INTO Member VALUES('1001','Alice',12,'Alice@ppp.ja')"
conn.execute(sql)

どっちも同じ結果になる。要は「conn.execute(ここにSQL文)」で実行できるようだ。

SQL文の実行の仕方がわかったのでとりあえずテーブルを作成する。

#SQLiteのモジュールをインポートする
import sqlite3

#Customer.dbに接続
conn=sqlite3.connect("Customer.db",isolation_level=None)

#テーブルを作成
#table_name:Member
#column:id name age address
sql=""""
CREATE TABLE Member(
    id VARCHAR(4),
    name VARCHAR(20),
    age integer(2),
    address VARCHAR(128)
);
"""
#変数sqlに格納したINSERT文を実行する
conn.execute(sql)

で，わけがわからないのだが「カーソル」という概念がある。

db2

こんな感じでCursor object at XXXXXXXみたいなやつ。
これはconn.execute()が実行された結果で，カーソルというオブジェクトが指している位置を表している，と書いているが意味がわからねえ。
気にしなくていいとあるからとりあえず気にしないことにするが，テーブルの中身を確認するときにこの概念を使うぞ。

その前にさっき作ったテーブルに適当にレコードをインサートしてくる。
どうでもいいけど，ここで使うSQL文はお尻にセミコロンをつけないので気持ちが悪い。
（OracleDBBronzeの勉強をしてる人の感想。）

適当に入れてみたデータを確認する

db3

意味がよくわかんねーんだが，カーソルというオブジェクトの値を一旦変数cに入れて，SELECT文を実行すると，今度はcの中にSELECT文の中身が格納される，
そのあとFor文とPrint文で目に見えるようにした，という感じなんだろうか。

疲れたからデータベースを閉じたいとき

conn.close()

また接続したい時は上の手順で接続すれば（import sqlite3から始めれば）また途中からいじり始めることができる。（勝手にコミットされているので）

11. 表の作成と削除

11.1 表の作成と削除

データベースオブジェクト…表、ビュー、索引などのデータ構造を格納できるものの総称。単にオブジェクトととも。

スキーマ

データベースオブジェクトを管理するために使用される「論理的な概念」のこと。Oracleサーバの各ユーザーはユーザー名と同じ名前のスキーマを１つ所有しており、各ユーザーが作成したオブジェクトはそのユーザーが所有するスキーマに格納される。

スキーマは「ユーザーが所有するオブジェクトのリスト」である。

ただし、あくまでもスキーマは論理的な概念であり、実際に領域が割り当てられるわけではない。

別のユーザーが所有するオブジェクトを参照する方法

自身が所有するスキーマ以外のスキーマ内のオブジェクトを参照する場合、オブジェクト名に接頭辞（スキーマ名）をつけて指定する

スキーマ名.オブジェクト名

オブジェクトの命名規則

・長さ30バイト以下

・先頭の文字は数字、記号以外の文字

・使用できる文字は英数字、漢字、カタカナ、ひらがな（日本語環境のばあい）

・使用できる記号は、「_」「$」「#」のみ

・同一スキーマ内で重複する名前は指定できない

・Oracleサーバの予約語は使用できない

・アルファベットの大文字小文字は区別されない

表の作成

CREATE TABLEで作成するが、作成するためにはCREATE TABLE権限が必要。

権限の付与はデータベースの管理者が、データ制御言語を使用して実行する。

基本構文

CREATE TABLE [スキーマ名].表名

(

列名 データ型

[,列名 データ型 …］

);

スキーマ名を省略すると、CREATE TABLEを実行したユーザーが所有するスキーマ内に表が作成される。

DEFAULTオプション

表の作成時にDEFAULTオプションを指定すると、その列にデフォルト値（データの追加時にその列に対する値を省略した場合にその列に格納される値）を設定できる。

列名 データ型 [DEFAULT 式]

式が戻す値はデータ型と一致していなくてはならない。

リテラル値、式、sql関数を指定できる。

順序オブジェクトを参照するNEXTVAL擬似列やCURRVAL擬似列名も指定できる。（12cから）

別の列の名前は指定出来ない。

表の削除

DROP TABLE 表名 [PURGE];

表を削除できるのは、表の所有者またはDROP ANY TABLE権限を持つユーザーのみ。

表を削除すると表内のすべてのデータ、表に定義されている制約や索引も削除される。

表を参照するビューやシノニムは削除されない。

表はゴミ箱に移動される。

PURGE句を使用すると完全に削除される。 

11.2  データ型

文字型：列に文字データを格納できるようにするために使用する

>CHAR,VARCHAR2,LONG,CLOB,NCLOB

◆CHARとVARCHAR2の違い

CHAR

最大サイズ：2000バイト

最大サイズ指定省略時：デフォルト値の１が最大サイズ指定となる

確保するデータサイズ：固定長。格納するデータのサイズに関わらず、表作成時に定義したサイズで一定

VARCHAR2

最大サイズ：4000バイト

最大サイズ指定省略時：不可（省略するとエラー）

確保するデータサイズ：可変長。格納するデータサイズに応じて変わる。

◆LONG型

CHARやVARCHAR2に格納できない大きな文字データを格納できる(最大2GB)

・副問合せを使用した表の作成時に、LONG列はコピーできない。

・GROUP BY句、ORDER BY句に指定できない。

・1つの表に１つだけ定義できる。（LONG列またはLONG RAW列のどちらか１つ）

・制約を定義できない。

数値型：列に数値にを格納できるようにするために使用する

>NUMBER

定義方法

列名 NUMBER [ ( 最大精度 [,位取り ] ) ]

NUMBER型の構文のオプション

最大精度：格納する数値データの最大精度を指定する。

位取り：格納する数値データの小数点以下の桁数を指定する。

最大精度と位取りの両方を省略すると最大38桁の浮動小数点を格納できる。最大精度を指定して、位取りを省略した場合あ、最大精度に指定した桁数の整数値を格納できる。

日付型：列に日付データを格納できるようにするために使用する

>DATE

データサイズは固定長で7バイト。

世紀、年、月、日、時、分、秒が内部的な数値書式の形式で格納される。

バイナリ型：列にバイナリデータを格納できるように使用する

>RAW,LONG RAW,BLOB BFILE

RAW：最大2000バイトまでのバイナリデータを格納できる。可変長、最大サイズの指定はできない。

LONG RAW：最大2GBまでのバイナリデータを格納できる、可変長、LONG型と同様の制限あり。

BLOB：最大4GBまでのバイナリデータを格納できる

BFILE：最大4GBまでのバイナリデータを格納できる、読み取り専用。BFILE型が定義された列に格納したデータは、Oracleサーバのデータファイル上ではなく、OS上のファイルに保存される。

ROWID型

>ROWID

 ROWID疑似列(実際には定義されていないが、表に定義された列のように使用できる疑似的な列)から戻される値を列に保存する場合に使用する。ROWIDは表の各行に割り当てられている一意なアドレスのこと。

TIMESTAMP型

DATE型を拡張したもの。DATE型が格納できる情報に加えて、秒の小数点以下の値も格納できる。小数点以下の桁数には0～9の範囲が指定できる（デフォルトは６） 

>TIMESTAMP WITH TIME ZONE

タイムゾーンの時差を含むことができる。タイムゾーンの時差は列の一部として格納され、表示される。

>TIMESTAMP WITH LOCAL TIMEZONE

タイムゾーンの時差を含むことができる。タイムゾーンの時差は列の一部として格納されず、データ取得時にローカルセッションのタイムゾーンの値で表示される。

 11.3 制約の種類と制定方法

制約とは 表に格納するデータに関する「ルール」

制約を定義することで、ルールに反する追加や、ルールに満たさなくなるようなデータの更新・削除を行えなくなる。

制約のチェックは、データの変更時（追加、更新、削除）に自動的に行われる。

NOT NULL制約：NULL値を許可しない。

UNIQUE制約(一意制約)：重複値を許可しない。複数のNULL値は許可する。

PRIMARY KEY制約(主キー制約)：表内の各行を一意に識別できる値のみ許可する。重複値およびNULL値は許可しない。

CHECK制約(チェック制約)：指定した条件に合う値のみ許可する。

制約の定義方法

 制約は列レベルまたは表レベルで定義する。

表作成時の制約の定義構文

CREATE TABLE [スキーマ名].表名

(

列名  データ型  [ [列レベル制約 [ 列レベル制約…]

[,列名  データ型 [列レベル制約 [ 列レベル制約…] ]…]

[,表レベル制約 [ , 表レベル制約 ] ]

); 

 列レベル制約の基本構文

[CONSTRAINT 制約名 ] 制約の種類

表レベル制約の基本構文

[CONSTRAINT 制約名] 制約の種類 ( 列名 [, 列名…] )

・「CONSTRAINT制約名」は省略可能。省略時はOracleサーバが「SYS_Cｎ」の形式で名前を作成する

・表レベル制約の構文では、「()」内に制約を定義する列を1つ以上指定する。

・１つのCREATE TABLE文の中に列レベル制約と表レベル制約を同時に指定できる。

・NOT NULL制約は列レベルでのみ定義する

・複数の列の組み合わせからなる複合の制約は表レベルでのみ定義できる。

・1つの列に複数の列レベル制約を定義する場合は、改行またはスペースで区切る（カンマではない）

・１つの表に複数の表レベル制約を定義する場合は、カンマで区切る（改行、スペースではない）

NOT NULL制約

その列にNULL値を設定できなくなる。

NOT NULL制約は列レベルでのみ定義できる。

UNIQUE制約（一意キー制約）

その列に重複した値を設定できなくなる。

NULL値を含めることはできる。（複数行もできる）

自動的に制約と同じ名前の一意索引が作成される。

PRIMARY KEY制約（主キー制約）

その列（または組み合わせ）には表内の各行を一意に識別できる値しか格納できなくなる。

重複値、NULL値は設定できない。

表に1つのみ設定できる。

自動的に制約と同じ名前の一意索引が作成される。

FOREIGN KEY制約（外部キー制約)

 その列には参照先の列に存在する値しか格納できなくなる。

NULL値を含めることができる。（複数行もできる）

列レベルでの構文

[ CONSTRAINT 制約名] REFERENCES 親表名(参照する列名 [, 参照する列名…])

表レベルでの構文

[ CONSTRAINT 制約名] FOREIGN KEY ( 列名 [, 列名…])

REFERENCES 親表名 ( 参照する列名 [,参照する列名…])

親表名

参照先の表の名前。FOREIGN KEY制約で参照する表を「親表」、FOREIGN KEY制約が定義されている表を子表と呼ぶ。親表には別の表だけではなく、FOREIGN KEY制約を定義する表(つまり同じ表）を指定することもできる。

参照する列名

親表に定義されている、参照先の列の名前。

ただし、FOREIGN KEY制約はUNIQUE制約またはPRIMARY KEY制約が定義されている列しか参照できない。

ON DELETE CASCADE

子表に親表を参照する行が存在する場合に，親表の参照されていない行を削除すると，子表の参照している行も削除される

ON DELETE SET NULL

子表に親表を参照する行が存在する場合に，親表の参照されている行を削除すると，子表の参照している行にNULL値が設定される

 CHECK制約

その列には指定した条件に対してTRUEまたはNULLを戻す値しか格納できなくなる。

CHECK制約は1つの列に複数定義できる。

構文

[ CONSTRAINT 制約名 ] CHECK(条件)

・CURRVAL,NEXTVAL,LEVEL,ROWNUM疑似列の参照はできない

・SYSDATE、UID,USER,USERENV関数の呼び出しはできない

・他の行の値を参照する問い合わせはできない

11.4 副問合せを使用した表の作成と表構造の変更

副問合せを使用した表の作成

CREATE TABLEと副問合せを組み合わせると、既存の表を基にして新しい表を作成できる。

CREATE TABLE 表名 [ ( 列名 [,列名…] ) ]

AS

副問合せ;

・データ型とNOT NULL制約は新しい表にコピーされる

・NOT NULL制約以外の制約はコピーされない

・副問合せのSELECT句で計算式や関数を使用している場合，明示的に列名（列別名）を指定する必要がある。

表構造の変更

表の構造はあとから変更できる。

ALTER TABLE句を使用すると以下の操作が可能

・表に新しい列を追加する

・既存の列のデータ型を変更する

・既存の列にデフォルト値を設定する

・既存の列を削除する

・既存の列の名前を変更する

・表を読み取り／書き取りモードにする

・表を読み取り専用モードにする

◇列の追加

 ALTER TABLE　表名

ADD( 列名　データ型　[ DEFAULT　式] [ 列レベル制約 ]

　　[, 列名　データ型　[ DEFAULT 式] [ 列レベル制約 ]　… ] ) ;

 新しく追加された列にはNULL値が入っているためNOT NULL制約をつけたい場合はDEFAULT値を設定する必要がある。

◇列の変更

既存の列のデータ型，サイズ，デフォルト値を変更できる

ALTER TABLE 表名

MODIFY(列名 [ データ型 ] [ DEFAULT式 ]

　　　　　　[,列名　[ データ型 ] [ DEFAULT　式 ]… ]);

 ・サイズ，精度の増加はいつでもできる

・列のサイズは次の場合のみ減少できる

→列にNULL値のみが含まれている

→表に行がない

→列のサイズは，既存の列の値未満には減少できない

（すでに30バイトのデータが入ってる列のサイズを20にはできない）

・列にNULL値のみが含まれている場合は，データ型を変更できる

・列にNULL値以外のデータが含まれていてもサイズを変更しない場合は，CHAR型からVARCHAR2型，またはその逆へはデータ型を変更できる

・列のデフォルト値の変更は，以後の表への挿入のみに適用される。

◇列の削除

ALTER TABLE　表名　DROP(列名 [,列名…]);

・削除の対象の列にはデータが存在する場合も，存在しない場合もある

・列を削除した後も，表には1つ以上の列を残す必要がある

・列の削除は元に戻せない。

・別の列から参照される主キーは，CASCADEオプションを指定しない限り削除できない

・列に多くの値が含まれている場合は削除に時間がかかる

◇表モードの変更

「読み取り／書き込みモード」と「読み取り専用モード」がある

 表のメンテナンスを行う際に，読み取り専用モードにするとDDLやDMLによる変更を防ぐことができる。

ALTER TABLE　表名{ READ WRITE | READ ONLY };

読み取り専用モードにしても，表の削除はできるので注意。

10. データ操作とトランザクション制御

10.1　DML文によるデータの追加・更新・削除

確認

DMLとは，Data Manipulation Language:データ操作言語のこと。

表などのデータを直接操作する。

INSERT文によるデータの追加

表にデータを追加するときに使用する。

基本構文

INSERT INTO　表名　[ ( 列名1　[, 列名2…] ) ]

VALUES　値1　[,値2,…] ) ;

表名：データを追加する表の名前

列名：データを追加する表に定義されている列名。省略化。

値　：追加するデータの各列に設定する値。列名のリストの順番で各列に設定する値を指定

・列名と値は1対1の関係になる。

・追加する値は列に定義されているデータ型と一致しなければならない。

・文字値や日付値を追加する場合は値を「’」（一重引用符）で括る。 

列名を省略したINSERT文

列名を省略する場合は，VALUES句には表に定義されている列と同数の値を，表の列構成と同じ順番で指定する。

列名を明示的に指定したINSERT文

列名を指定する場合はVALUES句には列名のリストと同数の値を，同じ順番で指定する。→表に定義されている順番でなくてもOK

表に定義されていない列名を指定するとエラーになる。

NULL値を含む行の追加

暗黙的手法

列名のリストから対象の列名を省略する。省略された列にはNULL値が設定される。

明示的手法

1，NULLキーワードを指定する

2，文字値や日付値を格納する列に対して「’’」（空の文字列）を指定する。 

NOT NULLが定義されている列に対するNULL値の指定

列にNOT NULLが定義されている場合，NULL値を設定することはできない。NULL値を設定しようとするとエラーになる。

NOT NULLになっているかどうかは「desc 表名」で確認できる。

関数を使用したデータの追加

INSERT文のVALUES句には関数を使用できる。

SYSDATE関数を使用してINSERT文を実行した日時を指定するなど。

副問合せを使用したデータの追加

副問合せを使用すると値を既存の表から取得して複数行のデータを一度に追加することができる。

基本構文

INSERT INTO 表名1　[ ( 列名[ ,列名… ] )

SELECT　列名　[ , 列名…]

FROM　表名2

[WHERE 条件];

 ・副問合せを囲む（）は必須ではない。

・VALUES句は指定しない。

・INSERT句に指定する列名を省略する場合は，副問合せのSELECT句には，表に定義されている列と同数の値を，表の列構成と同じ順番で指定する。

・INSERT句に列名を指定する場合は，副問合せのSELECT句には列名のリストと同数の値を同じ順番で指定する。

UPDATE文によるデータの更新

すでに表に格納されているデータを更新できる。

基本構文

UPDATE 表名

SET　列名＝値 [ . 列名＝値…]

[WHERE　条件];

表名：データを更新する表の名前

列名：データを更新する列の名前。値とセットで指定する

値　：更新後のデータの値。列名とセットで指定する

条件：更新する行を特定するための条件（条件を満たす行が更新される。）WHERE句は省略可能だが，省略するとすべての行が更新されるので注意。

副問合せを使用したデータの更新

SET句に副問合せを使用すれば，副問合せで取り出したデータを使用して表を更新できる。

1つのUPDATE文で複数の副問合せを使用することができる。

WHERE句，SET句，および表名の代わりに副問合せを指定できる。

複数列副問合せを使用して，同時に同一行の複数列を更新することもできる。

DELETE文によるデータの削除

基本構文

DELETE　[FROM]　表名

[WHERE　条件];

表名：データを削除する表の名前

条件：削除する行を指定するための条件。WHERE句を省略するとすべての行が削除されるので注意。

・データは行単位で削除される。

・条件を満たす行が複数ある場合は，そのすべての行が同時に削除される。

・WHERE句を省略すると表内のすべての行が削除される。

・DELETE文のWHERE句に副問合せを使用することもできる。

10.2　トランザクションの制御

トランザクションとは，1つまたは複数の操作によって完結する一連のデータ操作をまとめた「論理的な処理単位」のこと。

Oracleサーバはトランザクションとしてまとめられた一連のデータ操作を必ず以下のどれかとして処理する。

・すべての操作を確定する（コミット）

・すべての操作を取り消す（ロールバック）

このように処理することで，データベース全体のデータの整合性を保持する。

Oracleサーバではデータの整合性はトランザクション単位で保証される。

トランザクションの構成要素

DML（データ操作言語）

SELECT文，INSERT文，UPDATE文，DELETE文など。トランザクションは，論理的な最小作業単位として扱われる1つまたは複数のDML文で構成される。

DDL（データ定義言語）

CREATE文，ALTER文，DROP文など。トランザクションは1つのDDL文のみで構成される。

DCL（データ制御言語）

GRANT文，REVOKE文など。トランザクションは1つのDCL文のみで構成される。

 トランザクションの開始と終了

DMLを含むトランザクションは，Oracleサーバに接続後または直前のトランザクションの終了後，「最初のDML文が実行されたとき」に自動的に開始され，以下のいずれかのイベントが発生した際に終了する。

・COMMIT文またはROLLBACK文の実行

・DDL文の実行（自動コミット）

・DCL文の実行（自動コミット）

・ユーザによるSQL DeveloperやSQL*Plusの終了

・マシン障害やシステムクラッシュの発生

明示的なトランザクション制御

トランザクション制御文

COMMIT

一連の処理（データの追加，更新，削除）を全て確定し，トランザクションを終了する。

SAVEPOINT　セーブポイント名

セーブポイントを作成する。トランザクションは継続する。

ROLLBACK

一連の処理（データの追加，更新，削除）をすべて取り消し，トランザクションを終了する。

ROLLBACK TO [SAVEPOINT]　セーブポイント名

指定したセーブポイントまでの処理を取り消す（ロールバックする）トランザクションは継続する。なお，指定されたセーブポイントよりも後に作成されたセーブポイントは削除される。 

暗黙的なトランザクション処理

 トランザクション制御文を実行していない場合でもトランザクション処理が実行される場合があり，これを「暗黙的なトランザクション処理」という。

◇自動コミット

・DDL文の実行時

・DCL文の実行時

・ユーザによるSQL DeveloperやSQL*Plusの正常終了

◇自動ロールバック

 ・ユーザによるSQL DeveloperやSQL*Plusの異常終了

 ・システム障害発生時（Oracleインスタンスの異常停止時）

（！）SQL*PlusのAUTOCOMMIT機能

 SET AUTOCOMMIT { ON | OFF }

AUTOCOMMIT機能を有効にすると，各DML文の実行時に自動的にコミット処理が実行される。

TRUNECATE文

表に格納されている全てのデータを削除する。

TRUNCATE　TABLE　表名;

・削除するデータは指定できない

・DDL文なので実行時には自動コミットが実行される。

・自動コミットされるのでROLLBACKできない。

・ロールバック用のデータを生成する必要がないため，DELETE文より短時間でデータを削除できる。

9. 集合演算子の使用方法

9.1 集合演算子の種類と使用方法

集合演算子を使用すると，複数の問合せの結果を一つにまとめることができる。集合演算子を含む問合せのことを「複合問合せ」と呼ぶ。

集合演算子

UNION

2つの問合せ結果を連結し，重複した行を排除して戻す

UNION ALL

2つの問合せ結果を連結し，重複した行も含めて戻す

INTERSECT

2つの問合せ結果のうち，共通する行だけを戻す

MINUS

1つ目の問合せ結果のうち，2つ目の問合せ結果にない行を戻す

複合問合せの基本構文

SELECT文1

{ UNION | UNION ALL | INTERSECT | MINUS }

SELECT文2

UNION演算子

2つの問合せ結果を連結し，重複した行を排除して戻す。2つの問合せに同一の行がある場合，その行は1度しか表示されない。

内部的にデータをソートした上で重複した行を排除するため，実行結果もSELECT句の先頭に指定されている列の値で昇順にソートされる。 

UNION ALL演算子

2つの問合せ結果を連結し，重複した行も含めて戻す。2つの問合せに同一の行がある場合，その行は2度表示される。

データのソート処理を行わないため，ORDER BY句を指定した場合をのぞいて，1つめの問合せの結果の後ろに2つめの問合せの結果を追加したものが表示される。

INTERSECT演算子

2つの問合せ結果のうち，共通する行だけを戻す。2つの問合せに同一の行がある場合，その行は1度しか表示されない。内部的にデータをソートした上で重複した行を排除するため，実行結果もSELECT句の先頭に指定されている列の値で昇順にソートされる。

MINUS演算子

1つ目の問合せ結果のうち，2つ目の問合せ結果にない行を戻す。

重複したデータは排除されて1度だけ表示される。内部的にデータをソートした上で重複した行を排除するため，実行結果もSELECT句の先頭に指定されている列の値で昇順にソートされる。

MINUS演算子は他の集合演算子とちがい，問合せの順番を入れ替えると表示されるデータが変わってしまうので注意。

 9.2  集合演算子の使用に関するガイドライン

SELECT句に指定する列の個数とデータ型

複合問合せでは，1つ目の問合せと2つ目の問合せでSELECT句に指定する列（または式）の個数を同数にする。

2つめの問合せのSELECT句に指定する列のデータ型は，1つ目の問合せのSELECT句に指定されているデータ型と同じ，かまたは同じデータ型グループにする。

列の名前は異なっていても問題ないが，問合せ結果の列の見出しは1つめの問合せに指定されている列名が使用される。

複合問合せでのORDER BY句の使用

使用時の注意

・ORDER BY句は，複合問合せの最後の問合せに指定する

→最初に指定するとエラーになる。

・ORDER BY句には，最初の問合せのSELECT句に指定されている列または列別名を指定する 

→それ以外を指定するとエラーになる。

・複合問合せが3つ以上あるときもORDER BY句は必ず最後の問合せに指定する。

複合問合せとNULL値

複合問合せではNULL値は無視されない。

UNION ALL演算子以外の集合演算子を使用した場合，重複したNULL値は排除される。

集合演算子の優先順位

集合演算子の優先順位は全て同じ，1つのSQL文に複数の集合演算子がある場合は，前に指定されたものから順番に実行される。ただし「（）」を使用して優先順位を明示的に指定することは可能。

【オラクル認定資格試験対策書】ORACLE MASTER Bronze[12c SQL基礎](試験番号:1Z0-061)完全詳解+精選問題集(オラクルマスタースタディガイド)

8.副問い合わせによる問い合わせの解決方法

8.1 副問い合わせの基本

副問い合わせ

OracleサーバーではSQL文の中に別の問い合わせ（SELECT文）を入れ子にすることができる。この入れ子になっている内側のSELECT文のこと。外側のSELECT文が主問い合わせ。

副問い合わせを使用すると，副問い合わせの実行結果をもとに外側のSQL文を実行できるため，通常のSQL文では複数のSQL文に分けて実行しなければならない処理も1つのSQL文で処理できる。

基本構文

SELECT　列名 [,列名…]

FROM　表名

WHERE　列名　比較演算子　(SELECT　列名　FROM　表名

　　　　　　　　　　　　　　[WHERE　条件];)

・「（）」で括る

・「WHERE」以外にも「HAVING句」「FROM句」にも副問い合わせを記述できる。

・SELECT文だけでなく，INSERT文，UPDATE文にも副問い合わせできる。

・副問い合わせは比較演算子の左辺に記述しても実行できる。

・通常，副問い合わせを使用したSQL文では，副問い合わせのSELECT文が1回実行された後に，副問い合わせの実行結果を元に主問い合わせが1回実行される。

副問い合わせの色々な使用方法

主問い合わせと異なる表を問い合わせる副問い合わせ

主問い合わせと副問い合わせのFROM句に異なる表を指定できる。

例

SELECT　empno,ename,deptno

FROM employees

WHERE deptno = (SELECT deptno FROM departments

　　　　　　　　WHERE dname ='営業');

departments表からdnameが営業であるdeptnoを取り出す

取り出したdeptnoに等しいdeptnoをもつempnoとenameとdeptnoをemployees表から取り出す。

複数の副問い合わせを使用する主問い合わせ

1つの主問い合わせに複数の副問い合わせを使用できる。

例

SELECT empno, ename, sal, deptno

FROM employees

WHERE sal >= (SELECT sal FROM employees

　　　　　　　WHERE empno ='1003')

AND　　　deptno = (SELECT deptno FROM departments

　　　　　　　　　　WHERE dname ='営業’);

employess表でempnoが1003であるsalの値以上であるsal

かつ

departments表のdnameが営業であるdeptno

であるempno,ename,sal,deptnoを表示する

副問合せのWHERE句以外で使用

HAVING句に副問合せを使用する。

例

SELECT　e.deptno , d.dname ,AVG(sal)

FROM employwws e JOIN departments d

ON e.deptno = d.deptno

GROUP BY e.deptno,d.dname

HAVING AVG(sal) >= (SELECT AVG(sal) FROM emploees)

ORDER BY e.deptno; 

 employees表のでdeptnoで昇順で表示

employees表の給与の平均が全体の平均給与より高いものを表示

employees表のdeptnoとdepartment表のdnameでグループ化

それぞれの表のdeptnoで結合する

FROM句での副問合せの使用

FROM句に指定された副問合せが実行されて実行したSELECT文の結果セットが1つの表のように扱われる。ビューに似てるので「インラインビュー」と呼ばれる。

例

SELECT　empno, ename, sal

 FROM   (SELECT empno, ename, sal FROM employees

　　　　WHERE deptno = 30);

データを1件も戻さない副問合せ

副問合せの実行結果が0件の場合，主問合わせにはNULL値が戻されるため，主問合わせの実行結果も0件になる。

8.2 単一行副問い合わせと複数行副問い合わせ

副問い合わせは，主問合せに戻すデータの件数によって「単一行副問合せ」と「複数行副問合せ」の2つに分類される。

単一行副問合せ

データを1件だけ戻す副問合せ。

WHERE句の比較演算子に単一行演算子を使用する。

単一行演算子

=　　等しい

>　　大きい

>=　以上

<　小さい

<=　以下

<>,=!,^=　等しくない

複数行副問合せ

複数件のデータを戻す副問合せ。

WHERE句の比較演算子に複数行演算子を使用する

複数行演算子

・IN（値のりすと）

リスト内のいずれかの値と等しければTRUE

・単一行演算子＋ANY（値のリスト）

リスト内のいずれかの値が条件を満たすとTRUE

直前に単一行演算子を使用する。「＝ANY」「＞ANY」など。

＝ANYはINと同じ意味。

 ・単一行演算子＋ALL（値のリスト）

リスト内のすべての値が条件を満たすとTRUE

直前に単一行演算子を使用する。「＜＞ALL」「＜ALL」など。

 複数行問合せと比較する比較演算子に単一行演算子のみを使用するとエラーになる。

副問合せにおけるIN演算子とNULL値

NOT IN（リスト）は＜＞ALL（リスト）と同じ。

NOT IN（リスト）はリスト内の全ての値がと等しくない場合にTRUEを返す。

NOT INのリストにNULL値が含まれるとデータは1件も戻されない。

7. 複数の表からのデータの取り出し

7.1 複数の表の結合

結合

複数の表を関連付けてデータを取り出す機能。

基本的な表の結合方法

FROM句に結合する表の名前を「，」で区切って指定する。

SELECT　列名1,2,3,4...　FROM　表名,表名

・結合する表の両方に存在する列をSELECT句に指定するとエラーになる。

（どちらの表の列から取り出せばいいかわからないため）

→表接頭辞をつけて「明示的に」どの表のどの列なのかを指定することで解決。

表接頭辞

表名.列名

 ・列名が重複していなくても表接頭辞はつけられる。表接頭辞を使用することで，OracleサーバーがSQL文の構文を解析する際に列名の重複有無をチェックする必要がなくなるので，SQL文のパフォーマンスがわずかに向上する。

表別名の使用

FROM句では「表別名」を使用できる。

表別名の指定

FROM　表名　表別名

・表別名は30バイト以下

・一般的に表別名にはわかりやすく，短い名前をつける

・表接頭辞に短い表別名を使用するとSQL文が短くなるため，メモリ使用量を削減できる

・表別名は表別名を指定したSQL文内でのみ有効

・表別名を指定するとSQL文全体で元の表名は無効になる

結合構文の種類

Oralceサーバーでは2種類の結合構文に含まれる方法で表を結合できる

・SQL（SQL:1999結合構文）⬅︎こっちの方が出題される

・Oracle独自結合構文 

SQL:1999結合構文

SELECT [表名.]列名　[ , 表名. [列名...]

 FROM　表名1

{

NATURAL JOIN 表名2 |

JOIN　表名2　USING(列名) |

JOIN　表名2　ON　結合条件 |

{ LEFT | RIGHT | FULL } [ OUTER ] JOIN 表名2　ON　結合条件 |

CROSS　JOIN　表名2

}

[WHERE 結合条件以外の条件];

7.2 等価結合

 結合する2つの表の「特定の列の値が等しいデータだけを取り出す」結合。

内部結合，単純結合とも。

内部結合（単純結合）…「結合条件にあうデータだけを取り出す結合。厳密には内部結合＝等価結合ではない。

自然結合

結合する2つの表に共通して存在する同じ列名かつ同じデータ型の列に基づいて表を結合する等価結合。「NATURAL JOIN句」の前後に結合する表名を指定する。

SELECT　[表名.]列名　 [,表名[.列名　...]

 FROM　表1　NATURAL JOIN　表2

[WHERE 結合条件以外の条件];

・自然結合では結合列（結合条件として使用される列）が自動的に判断されるため，明示的に結合条件を指定する必要がない。

・共通して存在する列が複数ある場合は，すべての列が結合条件として使用される。

・自然結合では，データ型の異なる同名の列があるとエラーになる。

・共通して存在する列が複数ある場合は，それらの全ての値が等しいデータのみを取り出す。

・自然結合の結合列には表接頭辞を使用できない。使用するとエラーになる。

（結合列以外の列には表接頭辞を使用できる。）

・WHERE句に結合列を指定する場合でも表接頭辞を使用するとエラーになる。

 USING句

自然結合とちがって，結合列を明示的に指定できる。

SELECT　[表名.]列名　[表名.]列名...]

FROM　表名1 JOIN 表名2　USING(列名[,列名...])

WHERE　[結合条件以外の条件];

・結合列を明示的に指定してSQL文をわかりやすくしたい場合に使用する

 ・結合する2つの表に共通して存在する列が複数ある場合に，そのいずれかを結合列として使いたい場合に使用する

・列名が同じでデータ型が異なる列を結合列として使用する場合に使用する

・結合列に表接頭辞を使用するとエラーになる

・NATURAL JOIN句とUSING句は同時に使用できない

ON句

異なる名前の列を使用して表を結合することができる。

ON句のあとに結合条件，その他の条件をWHERE句に指定する。

SELECT　[表名.]列名　[,[表名.]列名...]

FROM　表名1　JOIN　表名2

ON　[表名1.]列名 =　[表名2.]列名

WHERE　[結合条件以外の条件]; 

 ・2つの表にある同じ名前の列をSELECT句やWHERE句で使用する場合は表接頭辞を使用して列名を修飾する（表接頭辞を使用しないとエラーになる）

Oralce独自結合構文による等価結合

SELECT　[表名.]列名,　[,[表名.]列名...]

FROM　表名1,　表名2

WHERE　結合条件

[AND　結合条件以外の条件];

・結合条件はWHERE句に指定する

・2つの表に存在する列は表接頭辞を使用して修飾する必要がある。

・SQL1999結合構文とOracle独自結合構文にパフォーマンス上の差はない。

3つ以上の表の結合

SELECT　[表名.]列名　[,[表名.]列名...]

FROM　表名1

JOIN　表名2　ON　[表名.]列名　＝　[表名.]列名

JOIN　表名3　ON　[表名.]列名　＝　[表名.]列名

[JOIN　表名4　ON　[表名.]列名　＝　[表名.]列名…]

WHERE　[結合条件以外の条件]; 

7.3　その他の結合構文

 非等価結合

結合条件に「＝」以外の結合条件を指定して条件を満たすデータを取り出す。

SELECT　[表名.]列名 [,[表名.]列名...]

FROM　表名1　JOIN　表名2

ON 　　結合条件;

・結合条件には，＞，＜，＜＝，＞＝，BETWEENなどが使える

自己結合

同一の表に2つの表別名を指定することで1つの表を2つの表に見立ててデータを取り出す結合。

SELECT　[表別名.]列名 [, [表別名.]列名...]

FROM　表名1.表別名1　JOIN　表名1.表別名2

ON　[表別名1.]列名　= [表別名2.]列名...

外部結合

結合条件を満たしていないデータも取り出す結合。内部結合の反対的な。

SELECT　[表名.]列名 [,[表名.]列名...]

FROM　表名1

{LEFT | RIGHT | FULL}　[OUTER]　JOIN　表名2

ON　結合条件;

LEFT　左側外部結合

JOIN句の左側に指定された表のデータをすべて取り出す。

RIGHT　右側外部結合

JOIN句の右側に指定された表のデータを全て取り出す。

FULL　完全外部結合

JOIN句の左右に指定された表のデータを全て取り出す。

クロス結合

デカルト積（結合する表に格納されている全てのデータの全ての組み合わせ，直積）を戻す結合。

SELECT　[表別名.]列名 [, [表別名.]列名...]

FROM　表名1　CROSS JOIN　表名2;