【初学者】OracleMasterへの道「SQL編」Part4

もすきー(Mosuky)

2022年6月25日 14:59

どうも、mosukyです。

OracleDB編Part4になりました。

(前回記事)

https://note.com/weekly_report/n/ne76c2c0e77f8

題名の通り、いよいよOracleSQLについて、よく使う(？)確認系のSQLをまとめようと思います。

SQLを使った作業はほとんど業務ではやらないので(やるとしてもALTER句ばっか)、箇条書きでデータ問い合わせのSQL文をまとめたので、初学者の方に読んでいただければと思います。

では、

データ抽出系(Select)の基本SQL

#Select文の処理順序

Selectの処理順序

#Select文の基本構造

select @A, @C, @D [表示する列名A,C,D]
from @B JOIN @E ON [データを取り出すテーブルをテーブルB,Eを結合]
@F = @G [列名Fと列名Gを結合する値として利用する]
WHERE @H <比較演算子> 比較値 [列名H(グルーピングされてない)を比較して値とTrueの結果を表示]
GROUP BY @C [同じ値としてまとめる時に列名C]※1
HAVING @C <比較演算子> 比較値 [列名C(グルーピングされた)を比較して値とTrueとの結果を表示]※1
ORDER BY @D [並び替えの基準とする列名D];

※1…「GROUP BY」と「HAVING」の記載順序はどちらでも可能！
※2… @はカラムまたはテーブル名
※3…order byは、別列名とselect位置指定(数字)も可能だが、group byは列別名もselect位置指定(数字)が出来ない

#Order byによるデータのソート

Order byを使用することで指定した列名をデフォルトでは、昇順でソートを行う。

select 列名 from 表名 order by 列名;
(ex.
select emp_id from employees order by emp_id;
output: emp_idの昇順で表示を行う。

また、descを使用することで、降順にすることができる。

select 列名 from 表名 order by 列名 desc;
(ex.
select emp_id from employees order by emp_id desc;
output: emp_idの降順で表示を行う。

そして、Order byの後ろに「offset」や「fetch」を入力することで、表示する行を制御できる。※order byを指定しなくても大丈夫！

#Offset句

Offset句にて、指定した行数分スキップして表示を行う

select 列名 from 表名 offset 行数 row(またはrows);
(ex.
select * from employees offset 5 rows;
output: 最初に表示される5行分をスキップする

offsetで記載するrowとrowsに違いはない！
でも、記載しないとダメ！
また、order byを指定する場合は、必ずorder byの後ろに記載する。

#Fetch句

表示する行数の数または割合を指定する。

select 列名 from 表名 
fetch First(またはNext) 行数(数値) [(row_count) または percent]
row(またはrows) [only または with ties];

(ex.
select * from employees fetch Next 5 rows only;
output: 5行分のデータを表示する
 
select * from employees fetch First 5 row with ties;
output: 5行分のデータを表示するが、5行目のデータと同じものについては6行目以降に表示する
 
select * from employees fetch First 50 percent row only;
output: 50%分のデータを表示する。

・FirstまたはNextは同じ意味で違いはないが、必ず記載する必要がある
・row_countは記載する必要はなく、指定した値分の行数を表示する
・percentを記載することで表示する割合を指定できる
・rowもrowsも同じ意味で違いはないが、必ず記載する必要がある
・onlyは指定した分のみを表示する
・with tiesは指定した行数分を表示するが、比較値が同じ行については表示する

#Group by によるデータのグルーピング

Group byを使用するには、以下の条件を満たす必要がある
・Group by句には、1つ以上の列を指定する必要がある。
・列別名(列の名前を別の名前にすること)を指定出来ない。
・select句の選択リストには、「group byで選択した列」と「グループ関数」のみ指定できる。
・order by句とGroup by句を併用する場合、Order by句には、「Group by句で指定した列」と「グループ関数」のみ指定できる。

select 列名1, 列名2, ... from テーブル group by 列名n;

※「ORA-00979: GROUP BYの式ではありません。」のエラーが表示されることがありますが、
これはselectで指定しているカラムがグルーピングに適用していないため表示される。
(例えば、notnullキーの設定されているカラムなどをselectで表示しようとした場合は、それに付随する列をグルーピングしようとすると、該当のカラムがグルーピングに適していないため、エラーになる)

表示行数制限

・where句でrownumを使うことで表示件数を指定できる

select 列名 from 表名
where rownum <= 表示件数;
(ex.
select * from all_tables
where table_name like '%TABLE%'
and
rownum <= 5;
output: 5行以下で表示する。かつtable_nameがTABLEを含むものを

集合演算子

集合演算子について

#UNION演算子

・2つの問い合せ結果を連結し、重複した行を一度だけ表示して戻す

select 列名1 from 表名1 where 条件
union
select 列名2 from 表名2 where 条件;

(ex.
select * from employees where job_id = ANY(1003, 1004)
union
select * from employees where job_id = ANY(1003, 1005);
output: job_idの1003,1004,1005の結果を返す。(1003は2重で表示されない)

#UNISON ALL演算子

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

select 列名1 from 表名1 where 条件
union all
select 列名2 from 表名2 where 条件;

(ex.
select * from employees where job_id = ANY(1003, 1004)
union all
select * from employees where job_id = ANY(1003, 1005);
output: job_idの1003,1004,1005の結果を返す。1003が2重で表示される。

#INTERSECT演算子

・2つの問い合わせ結果のうち、共通した行を戻す

select 列名1 from 表名1 where 条件
intersect
select 列名2 from 表名2 where 条件;

(ex.
select * from employees where job_id = ANY(1003, 1004)
intersect
select * from employees where job_id = ANY(1003, 1005);
output: job_idが1003のみ結果を返す。

#MINUS演算子

・1つ目の問い合わせ結果のうち、2つ目の問い合わせ結果に存在しない行を戻す

select 列名1 from 表名1 where 条件
minus
select 列名2 from 表名2 where 条件;

(ex.
select * from employees where job_id = ANY(1003, 1004)
minus
select * from employees where job_id = ANY(1003, 1005);
output: job_idが1004の結果を返す。

集合演算子の注意点
・1つ目と2つ目の問い合わせをselect句に指定する列は同数にする必要がある
・2つ目の問い合わせのselect句に指定する列はデータ型とデータ型グループを合わせる必要がある
・Order by句は問い合わせの最後に記載する

表結合

# NATURAL JOIN結合

自然結合で、表Aと表Bの同列名かつ同じデータ型の列を自動で判別して結合してくれる。

select 列名A, ... from 表名A NATURAL JOIN 表名B;

#USING句を使用した結合

・結合列を明示的に指定して、SQL文をわかりやすくする場合
・結合する2つの表に共通して、存在する列が複数ある場合に、そのいずれかを結合列として使用したい場合
・列名が同じでデータ型が異なる列を結合列として使用する場合

select 列名A, 列名B from 表名A JOIN 表名B USING(列名B);
※列名Bは表A、表Bで必ず同一の列名がある必要がある。

# ON句を使用した場合の結合

・ON句を使用した場合は、異なる名前の列を使用して表を結合する
・selectやwhere句を使用する場合は、必ず表名や表接頭辞を使用して列名を修飾する必要がある(↓のSQLでいう、a,bの別列名のこと)

select a.列名, b.列名
from 表名A a JOIN 表名B b
ON a.列名 = b.列名;

#自己結合

・一つの表を複数用意して結合する。

select * from 表名A e1 JOIN 表名2 e2 ON e1.列名A = e2.列名B;

#外部結合

・外部結合は結合条件を満たしていないデータも取り出す結合方式
・LEFT JOINは左側(先方)をベースに結合を行い、右側(後続)がそれに合わせて結合を行う。よって、右側(先方)が余剰に表示される
・RIGHT JOINは右側(後続)をベースに結合を行い、左側(先方)がそれに合わせて結合を行う。よって、左側(先方)が余剰に表示される
・FULL JOIINはどちらにも結合を行う。よって、双方の余剰分も表示される

select * from 表名A e1 LEFT JOIN 表名2 e2 ON e1.列名A = e2.列名B;
select * from 表名A e1 RIGHT JOIN 表名2 e2 ON e1.列名A = e2.列名B;
select * from 表名A e1 FULL JOIN 表名2 e2 ON e1.列名A = e2.列名B;

#副問合せ

そのまんまの意味、where句などでselectした結果を条件に絞り込みを行う

select * from 表名A where 列名A > (select 列名A from 表名A where 列名B = XXX);

上の例だと、まず列名Bで絞り込みを行って表示された列名Aの結果に対して、列名Aの結果よりも大きい値を絞り込む、といった結果が返ってくる。

また副問合せにはHAVING句はもちろんFROM句でも実施ができる。

そして、副問い合わせには分類が存在する。

単一行副問合せ：=,>,<,<>,<=,>=
複数行副問合せ：IN,ANY,ALL

#複数行問い合わせ

IN：比較するリストのどれかと一致すればTrue
ANY：比較するリストのどれかと比較した際に正しければTrue
(例：=ANY(リスト)ならINと同じ、>=ANY(リスト)がリストの数値と比較してリストのどれかより大きければTrueになる)
ALL：リスト内のすべての値と比較して正しければTrueになる

#集合演算子

UNION：2つの問い合わせ結果を連結し、重複した行を排除して戻す
UNION ALL：2つの問い合わせ結果を連結し、重複した行も含めて戻す
INTERSECT：2つの問い合わせ結果のうち、共通する行だけを戻す
MINUS：1つ目の問い合わせのうち、2つ目の問い合わせ結果になり行を戻す

集合演算子は列名が異なる場合でも、データ型が一緒であれば実行できる。

文字関数

#INITCAP関数(頭文字を大文字にする)

・単語の先頭文字を大文字に変換し、2文字目以降を小文字に変換する
・単語なので、文字列だと半角スペースごとに行われる

select initcap('abcd efg') from dual;
(output: Abcd Efg)

#CONCAT関数(2つ文字結合)

・2つの文字列を結合する
・3文字以上は結合出来ない

select concat('Oracle ', 'DB') from dual;
(output: Oracle DB)

#SUBSTR関数(文字列任意番目から表示)

・引数として受け入れた文字列のm番目からn文字分の文字列を返す
・nについては省略可能で、m番目のみ指定することでそれ以降すべての文字を返す

select substr('oracle server', 3) from dual;
(output: acle server
 
select substr('oracle server', 3, 4) from dual;
(output: acle

#INSTR関数(文字検索)

・指定した文字列パターンが現れる位置を返す
・文字列1に対して文字列2がm文字目からn回目に検知した文字数目を返す

select instr('oracle server', 'er', 1) from dual;
(output: 9
 
select instr('oracle server', 'er', 1, 2) from dual;
(output: 12

#TRIM関数(文字列前後の任意1字文字削除)

・文字列の前後にある任意文字1文字に対して削除をして返す
・任意1文字の指定がなかった場合は、半角スペースを取り除く

select trim('x' from 'xxxxoracle serverxxxxx') from dual;
(output: oracle server

select trim('     oracle server   ') from dual;
(output: oracle server

#REPLACE関数(置換)

・文字列1に対して、文字列2を検索し、文字列3に置き換える
・文字列3を指定しない場合は、文字列2を削除する

select replace('oracle server', 'server', 'master'), 
replace('oracle server', 'server') from dual;
(output: oracle master と oracle

数値関数

#Round関数(四捨五入)

・小数点n桁目を表示するために、n+1桁目を四捨五入して返す
・また、n桁目の記載を省略すると、少数点1桁目を四捨五入して整数値を返す
・n桁目に-(マイナス)値を使うと、整数n桁目の値を四捨五入して返す

select round(200.1234, 3) from dual;
(output: 200.123
 
select round(200.5432) from dual;
(output: 201
 
select round(235.5432, -1) from dual;
(output: 240

#Trunc関数(切り捨て)

・小数点n桁目を表示するために、n+1桁目を切り捨て返す。
・その他はRound関数と一緒

#MOD関数(割り算のあまり)

・２つの値の割り算のあまりを返す。
それ以外特になし。

日付関数

#MONTH_BETWEEN関数(nヶ月間)

・2つの日付間の月数を戻す関数

select MONTHS_BETWEEN(SYSDATE,'20-12-15') from dual;
(output: 18.27....

#Trunc関数(切り捨て)

・日付を切り捨てして戻す
・日付(DD)を指定した場合は、時刻が0:00になる

select trunc(sysdate, 'year') from dual;
select trunc(sysdate, 'month') from dual;
select trunc(sysdate, 'date') from dual;
(output: 
22-01-01
22-06-01
22-06-23

汎用関数

文字、日付、数字については以下の変換や型でのみやり取りを行うことができる

#TO_CHAR関数(日付→文字列)

・日付書式のパターンによって表示の仕方が変化する
・デフォルトでは、時間は表示されない

select to_char(sysdate, 'yyyy-mm-dd HH24:MI:SS') from dual;
(output: 2022-06-23 16:01:52

その他オプション
・「/」や「-」、「(」などの半角記号はそのまま結果に表示される
・「年」や「月」、「日」などの漢字、ひらがな、カタカナ、アルファベットは「"(ダブルクウォート)」で囲むと表示される

select to_char(sysdate, 'yyyy"年"mm"月"dd"日" HH24:MI:SS (day)') from dual;
(output: 2022年06月23日 16:06:01 (木曜日)

・NLSパラメータによる言語環境の指定も実施できる

select to_char(sysdate,
'yyyy"年"mm"月"dd"日" HH24:MI:SS (day)',
'Nls_date_language = AMERICAN' )
from dual;
(output: 2022年06月23日 16:09:21 (thursday )

・また、日付の大文字、小文字は区別される

select to_char(sysdate, 'month, mon, day, dy', 
'Nls_date_language = AMERICAN' ) from dual;
(output: june     , jun, thursday , thu

select to_char(sysdate, 'MONTH, MON, DAY, DY', 
'Nls_date_language = AMERICAN' ) from dual;
(output: JUNE     , JUN, THURSDAY , THU

表示形式の変更も行える
・TH:順序表記(DDTH→4th)
・SP:スペル表記(DDSP→FOUR)
・SPTHまたはTHSP:順序＋スペル(DDTHSP→FOURTH)

select to_char(sysdate, 'DDTH' ) from dual;
(output: 23RD

select to_char(sysdate, 'DDSP' ) from dual;
(output: "TWENTY-THREE"
 
select to_char(sysdate, 'DDTHSP' ) from dual;
(output: TWENTY-THIRD

#TO_CHAR関数(数値→文字列)

・数値書式によって数値を文字に変化する

・数値を表現する場合、桁数が足らないと#(文字列)が返ってくる
・「9」は桁を明示的に指定して、指定した桁数よりも小さいとそのままの値で返す
・「0」は明示的に桁を指定して、指定した桁数よりも小さいと「0」を埋めて値を返す

select to_char(1234.5678, 'L999999999.999999999') from dual;
(output: ¥1234.567800000

select to_char(1234.5678, 'L000000000.000000000') from dual;
(output: ¥000001234.567800000

#TO_DATE関数(文字→日付)

・文字列を日付に変換する関数
・基本的にはto_charと逆の操作を行う

select to_date('2022年12月24日', 'yyyy"年"mm"月"dd"日"') from dual;
(output: 22-12-24

・日付書式の一部として、YY要素,RR要素のによって西暦や世紀の扱いが異なる

#TO_NUMBER関数(文字→数字)

・文字列を数字に変換する関数
・基本的にはto_charと逆の操作を行う

条件判断汎用関数

#NVL関数(nullによる判断)

・引数1がNullじゃない場合は引数1、Nullの場合は引数2を返す関数

select 列名 NVL(引数1, 引数2) from 表名;

(ex:
select EMPLOYEE_ID, salary, NVL(commission, 0) from employees;
output: nullなら0を返す、null以外はcommissionの値を返す

#NVL2関数(nullによる判断)

・引数1が、Nullの場合は引数3を返して、Nullでない場合は引数2を返す
・データ型は引数2に合わせる、または合わさる

select 列名 NVL2(引数1, 引数2 ,引数3) 
from 表名;

(ex.
select EMPLOYEE_ID, salary, NVL2(commission, 'not null dayo' , 'null dayo') 
from employees;
output: commissionがnullなら'null dayo'、nullでないなら'not null dayo'を返す

#NULLIF関数(比較して、nullか引数1を返す)

・引数2つを比較して、等しい場合はnullを返して、等しくない場合は引数1を返す

select 列名 NULLIF(引数1, 引数2) from 表名;

(ex.
select EMPLOYEE_ID, salary, NULLIF(salary, 200000) from employees;
output: salaryが200000ならnullを、200000でなければsalaryの値を返す

#CAOALSCE関数(nullではない値を探す関数)

・関数にリスト形式で引数を入れて、順番に値を確認の上でnullではない値がヒットした場合に値を返す

select 列名, COALESCE(引数1, 引数2, 引数3) from 表名;

(ex.
select EMPLOYEE_ID, commission, salary, 
COALESCE(commission, salary, 0) 
from employees;
output: commission → salary → 0　の順番にnull値ではない値がヒットしたら値を返す。

#Case関数(caseの条件分岐)

DBインスタンス情報の操作SQL

# インスタンス名、ホスト名、バージョン、起動時間を表示する

SELECT INSTANCE_NAME, HOST_NAME, VERSION, STARTUP_TIME FROM V$INSTANCE ;

https://www.shift-the-oracle.com/view/data-dictionary-view/database.html

#セッション中のユーザ

SELECT USERNAME,STATUS,OSUSER,MACHINE,LOGON_TIME 
FROM V$SESSION 
WHERE USERNAME IS NOT NULL;

https://sql-oracle.com/?p=266

#すべてのテーブル情報と詳細情報

SELECT TABLE_NAME, TABLESPACE_NAME, PARTITIONED FROM ALL_TABLES ORDER by table_name ;
DESC "テーブル名"

#ユーザー作成

CREATE USER ora01 IDENTIFIED BY Oracle19;

ユーザー名：ora01
パスワード：Oracle19

たいてい何かを作成しようとするとなぜかうまくいかないのは、ITあるあるなのだろうか。。。

ORA-65096: 共通ユーザーまたはロール名が無効です

いろいろ調べると、現在自分が使っている、コンテナが良くないらしい。(ルートコンテナだとユーザー作成は出来ない臭い)

show con_name; -- 現在のコンテナを表示

select name, open_mode from v$pdbs;　-- 利用できるコンテナを表示

alter session set container = ORACLE;

https://obenkyolab.com/?p=1083

https://cosol.jp/knowledge/knowledge_post/ora-65096/

https://qiita.com/tokio_k_/items/ed82bf8944f0a9f840f2

https://xn--w8j8bac3czf5bl7e.com/2018/06/14/cdbpdb%E3%81%B8%E3%81%AE%E6%8E%A5%E7%B6%9A/

#ユーザーの確認

SELECT * FROM ALL_USERS ORDER BY USERNAME;

#テーブル作成

CREATE TABLE {テーブル名} 
 (
 {列1} {列1のデータ型},
 {列2} {列2のデータ型},
 {列3} {列3のデータ型},
 CONSTRAINT {主キー名} PRIMARY KEY(列1) 
 ) 
TABLESPACE {表領域名};

https://sql-oracle.com/?p=1694

#テーブルの型の変更

ALTER TABLE 'テーブル名' MODIFY ('カラム' '型');
(ex. ALTER TABLE employees MODIFY (employee_name VARCHAR2(30));

#表領域の確認

SELECT *　FROM DBA_TABLESPACES;

#データファイルの確認

SELECT * FROM DBA_DATA_FILES;

#表領域の作成

-- Oracleの表領域を作成する（自動拡張あり）
CREATE TABLESPACE {表領域名} DATAFILE
 {ファイルパス} SIZE {ファイルサイズ}
 AUTOEXTEND ON NEXT {増加量} MAXSIZE UNLIMITED;

-- Oracleの表領域を作成する（自動拡張なし）
CREATE TABLESPACE {表領域名} DATAFILE
 {ファイルパス} SIZE {ファイルサイズ}
 AUTOEXTEND OFF;

https://sql-oracle.com/?p=190

Tips

#SQLテストデータ

Ping-tのサイトにテストデータがありました。

https://ping-t.com/mondai3/sql/setup.sql

上のやつですが、一部データの型が指定バイト数と一致しないため、エラーになります。
勉強のためにもどこで何がエラってるのか利用される方は確認してみてくださいｗ

#MacでのSQL Developperのフリーズについて

macでSQL Developperを起動して使おうとしたら、秒でフリーズする。。。
どうも、最初のwelcomeページがmacと相性が最悪らしいので、起動したら、一番最初にスタートページをX閉じしましょう。。。

また、以下の手順に従って、ymlに設定を埋め込むとwelcomeページがなくなるので、設定するのが吉です！

https://qiita.com/terumejp/items/105206bb26a909dd66f9

では、

(なんとか、Oracle 12c SQLもpassしました。
次は2019シルバー)

この記事が気に入ったらサポートをしてみませんか？