SQLに慣れてない頃だとnullを判定する際は「xx = null」などと書いてしまいがちですが、SQLでNULLを判定するには「xx is null」 or 「xx is not null」と記載します。
「GOODS」
|
1 2 3 |
SELECT * FROM GOODS g WHERE GOODS_CODE IS NULL; |
|
1 2 3 |
SELECT * FROM GOODS g WHERE GOODS_CODE IS NOT NULL; |
SQLではSELECT時などにCASE文を入れる事で「IF-THEN-ELSE式」を記載することも出来ます。
「GOODS」
|
1 2 3 4 5 6 7 |
SELECT NAME, GOODS_CODE, (CASE GOODS_CODE WHEN 101 THEN '【PC】' || NAME WHEN 201 THEN '【DESK】' || NAME ELSE '【OTHER】' || NAME END) test FROM GOODS g; |
OracleではTRUC関数を使用することで年、月、日、時、分、秒以下を切り捨てて取得することが可能となります。
|
1 2 3 4 5 6 7 |
SELECT TRUNC(b.UPDATE_DATE, 'YYYY'), TRUNC(b.UPDATE_DATE, 'MM'), TRUNC(b.UPDATE_DATE, 'DD'), TRUNC(b.UPDATE_DATE, 'HH'), TRUNC(b.UPDATE_DATE, 'MI') FROM BIRTHDAY b; |
| format | 説明 |
|---|---|
| YYYY | 年まで取得して月日時分秒を切り捨て |
| MM | 年月まで取得して日時分秒を切り捨て |
| DD | 年月日まで取得して時分秒を切り捨て |
| HH | 年月日時まで取得して分秒を切り捨て |
| MI | 年月日時分まで取得して秒を切り捨て |
SQLでシステム日付、誕生日を使用して年齢、システム日付～誕生日までの月数、日数を取得するSQLをメモしておきます。
以下はOracleでの実行例となっています。
|
1 2 3 4 5 6 7 |
SELECT TO_CHAR(sysdate,'YYYY-MM-DD') "本日の日付", TO_CHAR(b.BIRTHDAY,'YYYY-MM-DD') "誕生日", EXTRACT(YEAR FROM sysdate) - EXTRACT(YEAR FROM b.BIRTHDAY) "年齢", TRUNC(MONTHS_BETWEEN(sysdate,b.BIRTHDAY)) "誕生日までの月数", TRUNC(sysdate - b.BIRTHDAY) "誕生日までの日数" FROM dual, BIRTHDAY b; |
UPDATEで他のテーブルと結合（JOIN）した結果に対してのみ更新を実施したい場合、SQL ServerやMySQL、PostgreSQLではUPDATE文内に直接JOIN句を記載可能ですが、Oracleでは副問合せで結合することでJOINした結果に対して更新することが可能となります。
|
1 2 3 4 |
UPDATE GOODS SET PRICE = 5000 WHERE GOODS_CODE = ANY (SELECT g.GOODS_CODE FROM GOODS g INNER JOIN GOODS_TYPE gt ON gt.GOODS_CODE = g.GOODS_CODE) |
|
1 2 3 4 |
UPDATE g SET r.PRICE = 5000; FROM GOODS g INNER JOIN GOODS_TYPE gt ON gt.GOODS_CODE = g.GOODS_CODE |
SQLで合計を求めるにはSUM関数、平均を求めるにはAVG関数を使用します。
関数＼DBMS MySQL PostgreSQL SQL Server Oracle
SUM ○ ○ ○ ○
AVG ○ ○ ○ ○
|
1 2 |
SELECT SUM(PRICE) FROM GOODS; |
以下の例ではGOODSテーブルのPRICE項目の平均値を求めています。
|
1 2 |
SELECT AVG(PRICE) FROM GOODS; |
ALL句を使用することでWHERE句内に指定した副問合せの結果を比較してデータを抽出する事が可能となります。
|
1 2 3 4 5 6 7 |
SELECT * FROM GOODS WHERE GOODS_CODE <> ALL ( SELECT GOODS_CODE FROM GOODS_TYPE WHERE GOODS_CODE in('101','301') ); |
SQLでゼロ埋めスペース埋めして桁数を揃えたい場合にはLPAD関数やRPAD関数を使用することで取得可能となります。DBMSによっては使用出来ないものもあります。
関数＼DBMS MySQL PostgreSQL SQL Server Oracle
LPAD ○ ○ ✕ ○
RPAD ○ ○ ✕ ○
書式は「LPAD(列名,バイト数,’埋め込み文字’)」、「RPAD(列名,バイト数,’埋め込み文字’)」で指定します。第三引数の埋め込み文字を省略すると半角スペースが埋め込まれます。
サンプルではOracleでの使用例となります。
|
1 |
SELECT LPAD(g.GOODS_CODE , 10, '0') FROM USER1.GOODS g; |
|
1 |
SELECT LPAD(g.NAME , 10, '＊') FROM USER1.GOODS g; |
|
1 |
SELECT RPAD(g.GOODS_CODE , 10, '0') FROM USER1.GOODS g; |
|
1 |
SELECT RPAD(g.NAME , 10, '＊') FROM USER1.GOODS g; |
SQLを見てるとたまにWHERE句内で「(+)」と記載されているのを見かけることがあります。
この「(+)」はOracle独自で記載が可能となる外部結合演算子といって、「g.GOODS_CODE = gt.GOODS_CODE(+)」のように指定するとLEFT OUTER JOINと同じ結果を取得することが出来ます。
|
1 2 3 |
SELECT g.name, gt.type_name FROM USER1.GOODS g, USER1.GOODS_TYPE gt WHERE g.GOODS_CODE = gt.GOODS_CODE(+); |
|
1 2 3 |
SELECT g.name, gt.type_name FROM USER1.GOODS g LEFT OUTER JOIN USER1.GOODS_TYPE gt ON g.GOODS_CODE = gt.GOODS_CODE; |
外部結合演算子、左外部結合どちらも同じ結果となります。
Oracleのインストール後にSQL*Plusなどでユーザー作成しようとした際、「ORA-65096」エラーが発生した場合の原因と対応方法についてメモしておきます。
原因が分かってしまえば対応はシンプルです。接続先がコンテナ・データベース(CDB)であるのがまずいのであればプラガブル・データベース(PDB)に変更してしまえばいいだけです。
