データベースのクライアントソフトのDBeaverには便利な機能がいくつもありますが、その内の機能の一つにテーブルに登録済のレコードをinsert文形式に変換して取得出来る機能があります。
開発などでIT環境などに登録済のデータをlocal環境へデータコピーしたい場合などに使える便利な機能の一つです。SQL形式（insert文）以外にもCSVやHTML、XML形式などにも変換出来るので様々な用途に応用可能となります。
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 | 年月日時分まで取得して秒を切り捨て |
Oracle Databaseをインストールすると自動的に作成される管理者ユーザー「SYS」と「SYSTEM」のデフォルトパスワードについてメモしておきます。
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 PlusやSQL Developerに用意されている「DESCRIBE」コマンドを使用することでテーブル構造を確認することが出来ます。
DESCRIBEコマンドはDESCと省略することも出来ます。
