「SQL」タグアーカイブ

SQL:NULLの判定方法

SQLに慣れてない頃だとnullを判定する際は「xx = null」などと書いてしまいがちですが、SQLでNULLを判定するには「xx is null」 or 「xx is not null」と記載します。

使用例

サンプルテーブル

「GOODS」

NULLのデータを抽出する例

クエリー(SQL)

実行結果

NULL以外のデータを抽出する例

クエリー(SQL)

実行結果

SQL:SELECT時にCASE文で条件指定する方法

SQLではSELECT時などにCASE文を入れる事で「IF-THEN-ELSE式」を記載することも出来ます。

使用例

サンプルテーブル

「GOODS」

クエリー(SQL)

  • 以下の例ではCASE文でGOODS_CODEが特定の値と一致した場合、NAME列に文字列を付与してTEST列として出力しています。

出力結果

Oracle:年、月、日、時、分、秒以下を切り捨てて取得する方法

OracleではTRUC関数を使用することで年、月、日、時、分、秒以下を切り捨てて取得することが可能となります。

使用例

サンプルテーブル「BIRTHDAY」

クエリー(SQL)

  • BIRTHDAYテーブルのUPDATE_DATEに対してTRUNC関数を使用した例となります。第2引数へformatを指定することで指定した単位で切り捨てた値を取得することが出来ます。
  • formatの種類 
    format説明
    YYYY年まで取得して月日時分秒を切り捨て
    MM年月まで取得して日時分秒を切り捨て
    DD年月日まで取得して時分秒を切り捨て
    HH年月日時まで取得して分秒を切り捨て
    MI年月日時分まで取得して秒を切り捨て
     

出力結果

 

SQL:システム日付と誕生日を元に年齢、誕生日までの月数、日数を表示する方法

SQLでシステム日付、誕生日を使用して年齢、システム日付~誕生日までの月数、日数を取得するSQLをメモしておきます。

以下はOracleでの実行例となっています。

サンプルテーブル

「BIRTHDAY」テーブル

【Oracle】システム日付と誕生日から年齢、誕生日までの月数、日数を表示する例

クエリー(SQL)例

実行結果

SQL:UPDATE と JOIN を使用してレコードを更新する方法

UPDATEで他のテーブルと結合(JOIN)した結果に対してのみ更新を実施したい場合、SQL ServerやMySQL、PostgreSQLではUPDATE文内に直接JOIN句を記載可能ですが、Oracleでは副問合せで結合することでJOINした結果に対して更新することが可能となります。

サンプルテーブル

「GOODS」テーブル

「GOODS_TYPE」テーブル

【Oracle】UPDATE文でJOINを使用して更新する例

クエリー(SQL)例

  • 以下の例ではGOODS_TYPEテーブルに存在するGOODS_CODEと一致するレコードのみGOODSテーブルのPRICE項目を更新しています。

実行結果

  • 以下の様にGOODS_CODEが一致しているレコードのみ更新されていることが確認できます。

 

【SQL Server、MySQL、PostgreSQL】UPDATE文でJOINを使用して更新する例

クエリー(SQL)例

  • SQL Serverなどでは直接UPDATE文内にJOINを指定可能なので以下のような記載でもエラーにはならず更新されます。

     

SQL:合計や平均を算出する方法

SQLで合計を求めるにはSUM関数、平均を求めるにはAVG関数を使用します。

DBMS毎の使用可否

関数\DBMSMySQLPostgreSQLSQL ServerOracle
SUM
AVG
 

サンプルテーブル

「GOODS」テーブル

合計を求める実行例

SUM関数を使用したクエリー(SQL)例

  • 以下の例ではGOODSテーブルのPRICE項目の合計値を求めています。

実行結果

平均を求める例

AVG関数を使用したクエリー(SQL)例

  • 以下の例ではGOODSテーブルのPRICE項目の平均値を求めています。

実行結果

SQL:副問合せの結果をALL句で比較して取得する方法

ALL句を使用することでWHERE句内に指定した副問合せの結果を比較してデータを抽出する事が可能となります。

サンプルテーブル

  • 「GOODS」テーブル
  • 「GOODS_TYPE」テーブル

ALL句を利用したクエリー(SQL)例

  • 以下の例ではまず副問合せでGOODS_CODEテーブルからGOODS_CODEが101と301のデータを取得し、ALL句でGOODSテーブルのGOODS_CODEが101と301以外のデータを取得しています。

実行結果

SQL:文字列や数値項目へ埋め込み文字を挿入して桁数を揃える方法

SQLでゼロ埋めスペース埋めして桁数を揃えたい場合にはLPAD関数やRPAD関数を使用することで取得可能となります。DBMSによっては使用出来ないものもあります。

関数\DBMSMySQLPostgreSQLSQL ServerOracle
LPAD
RPAD
 

書式は「LPAD(列名,バイト数,’埋め込み文字’)」、「RPAD(列名,バイト数,’埋め込み文字’)」で指定します。第三引数の埋め込み文字を省略すると半角スペースが埋め込まれます。

サンプルではOracleでの使用例となります。

サンプルテーブル

  • 「GOODS」テーブル

LPAD関数の使用例1

  • 項目「GOODS_CODE」を10桁でゼロ埋めするSQL(クエリー)
  • 実行結果

LPAD関数の使用例2

  • 項目「GOODS_CODE」を10桁で全角「*」埋めするSQL(クエリー)
  • 実行結果
    以下のように埋め込み文字へ全角文字を指定すると1文字=2バイト扱いで埋め込まれます。

RPAD関数の使用例1

  • 項目「GOODS_CODE」を10桁でゼロ埋めするSQL(クエリー)
  • 実行結果

RPAD関数の使用例2

  • 項目「GOODS_CODE」を10桁で全角「*」埋めするSQL(クエリー)
  • 実行結果
    RPADでも以下のように埋め込み文字へ全角文字を指定すると1文字=2バイト扱いで埋め込まれます。

SQL:WHERE句内で「(+)」を指定する意味

SQLを見てるとたまにWHERE句内で「(+)」と記載されているのを見かけることがあります。

この「(+)」はOracle独自で記載が可能となる外部結合演算子といって、「g.GOODS_CODE = gt.GOODS_CODE(+)」のように指定するとLEFT OUTER JOINと同じ結果を取得することが出来ます。

サンプルテーブル

  • 「GOODS」テーブル
  • 「GOODS_TYPE」テーブル

外部結合演算子を利用したクエリー(SQL)例

通常の左外部結合でのクエリー(SQL)例

実行結果

外部結合演算子、左外部結合どちらも同じ結果となります。

 

Oracle:ユーザー作成時に「ORA-65096」エラーが出た場合の原因と対応方法

Oracleのインストール後にSQL*Plusなどでユーザー作成しようとした際、「ORA-65096」エラーが発生した場合の原因と対応方法についてメモしておきます。

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

  • ルートコンテナにローカルユーザーを作成しようとした場合に発生するエラーとなります。
    ルートコンテナには共有ユーザー(common user) と呼ばれる特殊なユーザーしか作成することはできません。
    Oracle 11gまでと違いOracle 12c以降からは一つのインスタンスには一つのコンテナ・データベース(CDB)と、プラガブル・データベース(PDB)と呼ばれる子DBが存在しています。sysなどのユーザーでログイン直後はコンテナ・データベース(CDB)に接続されている状態となっているため、そのままローカルユーザーを作成しようとしてもエラーが発生してしまうということになります。

「ORA-65096:共通ユーザーまたはロール名が無効です」の対処方法

原因が分かってしまえば対応はシンプルです。接続先がコンテナ・データベース(CDB)であるのがまずいのであればプラガブル・データベース(PDB)に変更してしまえばいいだけです。

  1. まずは「show con_name;」で現在接続されているデータベースを確認します。
  2. 次に「select name, open_mode from v$pdbs;」でPDBの名前と現在のOPEN_MODEを確認します。
  3. PDBの名前が「ORCLPDB」というのがわかったのでデータベースの接続先を「ORCLPDB」へ変更します。
  4. もう一度「show con_name;」を実行して接続先が変更されていることを確認します。
  5. 接続先がPDBへ変更されたのでもう一度ユーザー作成を実行すると正常に実行されます。