「SQL」カテゴリーアーカイブ

SQL関連のカテゴリ

SQL：大文字 ⇒ 小文字、小文字 ⇒ 大文字へ変換する方法

2022年6月20日管理者コメントする

SQLで大文字⇒小文字に変換する場合はLCASE関数 or LOWER関数、小文字⇒大文字へ変換するにはUCASE関数 or UPPER関数を使用します。DBMS毎で使用出来る関数が異なるので整理しておきます。

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

関数＼DBMS	MySQL	PostgreSQL	SQL Server	Oracle
UCASE ※小文字 ⇒ 大文字	○	✕	✕	✕
UPPER ※小文字 ⇒ 大文字	✕	○	○	○
LCASE ※大文字 ⇒ 小文字	○	✕	✕	✕
LOWER ※大文字 ⇒ 小文字	✕	○	○	○

サンプルテーブル

「goods」テーブル

UPPER関数（小文字 ⇒ 大文字へ変換）の使用例

クエリー（SQL）例

PgSQL

SELECT UPPER(name) FROM goods ORDER BY id;

1

SELECT UPPER(name) FROM goods ORDER BY id;
実行結果

LOWER関数（大文字 ⇒ 小文字へ変換）の使用例

クエリー（SQL）例

PgSQL

SELECT LOWER(name) FROM goods ORDER BY id;

1

SELECT LOWER(name) FROM goods ORDER BY id;
実行結果

SQL, データベース, ブログ

SQLで一部の文字列を取得する方法

2022年6月18日管理者コメントする

SQLで一部の文字列を取得するにはSUBSTR関数やLEFT関数、RIGHT関数を使用することで可能となります。DBMS毎に利用できる関数に違いがありOracleではLEFT関数、RIGHT関数は使用出来ません。

関数＼DBMS	MySQL	PostgreSQL	SQL Server	Oracle
SUBSTR or SUBSTRING	○	○	○	○
LEFT	○	○	○	✕
RIGHT	○	○	○	✕

サンプルテーブル

「goods」テーブル

SUBSTR関数の使用例

クエリー（SQL）例
SUBSTR関数は「SUBSTR(文字列, 開始位置, (文字数))」で指定します。第三引数を省略すると第二引数へ指定した文字以降全て取得します。
SUBSTRはSUBSTRINGでも同様の動作となります。

SELECT 
SUBSTR(name,2),   -- 先頭から見て2文字目以降を取得
SUBSTR(name,2,1), -- 先頭から見て2文字目～1文字を取得
SUBSTR(name,2,3)  -- 先頭から見て2文字目～3文字を取得
FROM goods WHERE name = 'ABCDE';

SELECT

SUBSTR(name,2), -- 先頭から見て2文字目以降を取得

SUBSTR(name,2,1), -- 先頭から見て2文字目～1文字を取得

SUBSTR(name,2,3) -- 先頭から見て2文字目～3文字を取得

FROM goods WHERE name = 'ABCDE';

実行結果

LEFT関数の使用例

クエリー（SQL）例
LEFT 関数は「LEFT(文字列, 文字数)」で指定します。

LEFT関数の使用例
PgSQL

SELECT LEFT(name,2) 　 -- 左から2文字を取得 FROM goods WHERE name = 'ABCDE';

1
2
3

SELECT
LEFT(name,2) 　 -- 左から2文字を取得
FROM goods WHERE name = 'ABCDE';
実行結果

RIGHT関数の使用例

クエリー（SQL）例
LEFT 関数は「RIGHT(文字列, 文字数)」で指定します。

RIGHT関数の使用例
PgSQL

SELECT RIGHT(name,2)　 -- 右から2文字を取得 FROM goods WHERE name = 'ABCDE';

1
2
3

SELECT
RIGHT(name,2)　 -- 右から2文字を取得
FROM goods WHERE name = 'ABCDE';
実行結果

SQL, データベース, ブログ

SQLで文字列置換や文字置換する方法

2022年6月16日管理者コメントする

SQLで文字列置換をするにはREPLACE関数を使用することで可能となります。類似の関数にTRANSLATE関数がありますが、こちらは文字列単位ではなく1文字ずつ文字単位で置換します。

ここではPostgreSQLでの実行サンプルをベースに説明していきます。

サンプルテーブル

「goods」テーブル

REPLACE関数の使用例

クエリー（SQL）例
REPLACE関数は第二引数へ指定した文字列と一致した箇所のみ置換します。

REPLACE関数の使用例
PgSQL

SELECT REPLACE(name, '商品A', '012') FROM goods ORDER BY name;

1

SELECT REPLACE(name, '商品A', '012') FROM goods ORDER BY name;
実行結果

TRANSLATE関数の使用例

クエリー（SQL）例
TRANSLATE関数は第二引数へ指定した文字列を１文字ずつ第三引数へしていた文字へ置換します。下記例の場合、’商’ ⇒ ‘0’、’品’ ⇒ ‘1’、’A’ ⇒ ‘2’へ１文字ずつ変換されます。

TRANSLATE関数の使用例
PgSQL

SELECT TRANSLATE(name, '商品A', '012') FROM goods ORDER BY name;

1

SELECT TRANSLATE(name, '商品A', '012') FROM goods ORDER BY name;
実行結果

SQL, データベース, ブログ

SQLで文字列を結合（連結）する方法

2022年6月15日管理者コメントする

SQLで文字列を結合（連結）して取得するためにはCONCAT関数やCONCAT_WS関数、文字列結合子「||」を使用することで可能となります。DBMS毎で微妙に違いはありますが、ここではPostgreSQLでの実行サンプルをベースに説明していきます。

サンプルテーブル

「goods」テーブル

CONCAT関数の使用例

クエリー（SQL）例
CONCAT関数は連結したい文字をカンマ「,」で区切って指定すれば文字列連結可能となります。 Oracleの場合はCONCAT関数の引数は2つしか指定できないのでCONCAT関数を入れ子にして指定する必要があります。面倒なのでそういう場合は文字列結合子「||」を使用します。

CONCAT関数の使用例
PgSQL

SELECT CONCAT('商品名=', name, ', 商品コード=',type_code) AS "連結文字列" FROM goods;

1

SELECT CONCAT('商品名=', name, ', 商品コード=',type_code) AS "連結文字列" FROM goods;
実行結果

文字列結合子「||」の使用例

クエリー（SQL）例
文字列結合子「||」はCONCAT関数と同様の結果を取得できます。連結する文字列が少ない場合はこちらの方が比較的シンプルに記述できます。

文字列結合子「||」の使用例
PgSQL

SELECT ('商品名=' || name || ', 商品コード=' || type_code) AS "連結文字列" FROM goods;

1

SELECT ('商品名=' || name || ', 商品コード=' || type_code) AS "連結文字列" FROM goods;
実行結果

CONCAT_WS関数の使用例

クエリー（SQL）例
CONCAT_WS関数は第一引数にセパレータ（区切り文字）を指定し、第二引数から連結したい文字列をカンマ区切りで指定します。

CONCAT_WS関数の使用例
PgSQL

SELECT CONCAT_WS('-',name, type_code) AS "連結文字列" FROM goods;

1

SELECT CONCAT_WS('-',name, type_code) AS "連結文字列" FROM goods;
実行結果

SQL, データベース, ブログ

SQL：UNION、UNION ALL演算子の使用方法

2022年6月13日管理者コメントする

SQLで複数のSELECTで問い合わせた結果を連結したいという場合は集合演算子関数と言われる「UNION」や「UNION ALL」演算子を使用することで取得可能となります。

イメージ図

UNIONは重複行を削除して取得するので「A & C」を取得出来ます。
UNION ALLは重複行を含めて取得するので「A & B & C」を取得出来ます。

サンプルテーブル

「goods」テーブル

UNIONの使用例

サンプルテーブル「goods」をUNIONで連結して取得するサンプルとなります。

SQL（クエリー）

UNIONで連結して取得するSQL
PgSQL

SELECT id,name,type_code FROM goods WHERE name = '商品A' UNION SELECT id,name,type_code FROM goods WHERE type_code = '101'

1
2
3

SELECT id,name,type_code FROM goods WHERE name = '商品A'
UNION
SELECT id,name,type_code FROM goods WHERE type_code = '101'
出力結果

UNION ALLの使用例

サンプルテーブル「goods」をUNION ALLで連結して取得するサンプルとなります。

SQL（クエリー）

UNIONで連結して取得するSQL
PgSQL

SELECT id,name,type_code FROM goods WHERE name = '商品A' UNION ALL SELECT id,name,type_code FROM goods WHERE type_code = '101'

1
2
3

SELECT id,name,type_code FROM goods WHERE name = '商品A'
UNION ALL
SELECT id,name,type_code FROM goods WHERE type_code = '101'
出力結果

SQL, データベース, ブログ

SQL：システム日付（現在日付）を取得する方法

2022年6月12日管理者コメントする

SQLでシステム日付を取得するにはMySQLやPostgreSQlとOracleでは少し異なるのでメモしておきます。

システム日付を取得するSQL：MySQL、PostgreSQL

date型で取得、time型で取得、timestamp型で取得する方法がありますがOracleのsysdateに相当するという意味でtimestamp型の方法をご紹介します。

SELECT NOW();

1	SELECT NOW();

または

SELECT CURRENT_TIMESTAMP;

1	SELECT CURRENT_TIMESTAMP;

システム日付を取得するSQL：Oracle

SELECT SYSDATE FROM DUAL;

1	SELECT SYSDATE FROM DUAL;

PostgreSQL, SQL, データベース, ブログ

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

2022年6月11日管理者コメントする

OracleでのTRUC関数と類似したものにPostgreSQLではDATE_TRUNC関数があります。

DATE_TRUNC関数は「DATE_TRUNC([‘精度’],[列名])」形式で指定する事で取得できます。

使用例

サンプルテーブル「goods」

クエリー（SQL）

サンプルテーブル「goods」のtype_code=’102’のadd_dateをサンプルとしてDATE_TRUNC関数で抽出してみます。

SELECT 
DATE_TRUNC('year',add_date),   -- 精度に'year'  を指定し年で切り捨て
DATE_TRUNC('month',add_date),  -- 精度に'month' を指定し月で切り捨て
DATE_TRUNC('day',add_date),    -- 精度に'day'   を指定し日で切り捨て
DATE_TRUNC('hour',add_date),   -- 精度に'hour'  を指定し時で切り捨て
DATE_TRUNC('minute',add_date), -- 精度に'minute'を指定し分で切り捨て
DATE_TRUNC('second',add_date)  -- 精度に'second'を指定し秒で切り捨て
FROM goods 
WHERE type_code = '102';

SELECT

DATE_TRUNC('year',add_date), -- 精度に'year' を指定し年で切り捨て

DATE_TRUNC('month',add_date), -- 精度に'month' を指定し月で切り捨て

DATE_TRUNC('day',add_date), -- 精度に'day' を指定し日で切り捨て

DATE_TRUNC('hour',add_date), -- 精度に'hour' を指定し時で切り捨て

DATE_TRUNC('minute',add_date), -- 精度に'minute'を指定し分で切り捨て

DATE_TRUNC('second',add_date) -- 精度に'second'を指定し秒で切り捨て

FROM goods

WHERE type_code = '102';

出力結果

SQL, ブログ

SQL：最大値と最小値を求める方法

2022年6月10日管理者コメントする

SQLで最大値と最小値を求めるにはMAX関数とMIN関数を使用します。

使用例

サンプルテーブル「goods」
クエリー（SQL）
サンプルテーブル「goods」のtype_codeの最大値と最小値を抽出しているサンプルとなります。

LIMITで指定した件数を取得する場合（PostgreSQL）
PgSQL

SELECT MAX(type_code), MIN(type_code) FROM goods;

1

SELECT MAX(type_code), MIN(type_code) FROM goods;
出力結果

SQL, ブログ

SQL：IN句からEXISTS句への変換方法

2022年6月9日管理者コメントする

IN句をEXISTS句へ変換するとパフォーマンスが向上すると言われることがあるので

IN句からEXISTS句への変換例をメモしておきます。

サンプルテーブル

以下の商品テーブル「goods」と属性コードテーブル「type_code」を元に説明します。

商品テーブル「goods」	属性コードテーブル「type_code」

IN句を使用したSQL例

商品テーブル「goods」のtype_codeが ‘101’で属性コードテーブル「type_code」にも存在する商品名を取得する例となります。

SELECT tc.code_name
FROM type_code AS tc
WHERE
 tc.code IN(SELECT type_code FROM goods WHERE type_code = '101');

SELECT tc.code_name

FROM type_code AS tc

WHERE

tc.code IN(SELECT type_code FROM goods WHERE type_code = '101');

EXISTS句を使用したSQL例

WHERE句後の「tc.code IN」を「EXISTS」に変更し、「AND type_code = tc.code」を追加しただけです。

SELECT tc.code_name
FROM type_code AS tc
WHERE 
 EXISTS
 (SELECT 1 FROM goods WHERE type_code = '101' AND type_code = tc.code);

SELECT tc.code_name

FROM type_code AS tc

WHERE

EXISTS

(SELECT 1 FROM goods WHERE type_code = '101' AND type_code = tc.code);

出力結果（IN句、EXISTS句）

IN句、EXISTS句どちらの場合も以下の結果となります。

NOT EXISTS句を使用したSQL例

EXISTS句は使い慣れてないと今一つ分かりにくい気がするので、上記のEXISTSをNOT EXISTSで実行してみた例も記載しておきます。なんとなくEXISTSがどういう結果を出力しているかわかるかも。。

SELECT tc.code_name
FROM type_code AS tc
WHERE 
 NOT EXISTS(SELECT 1 FROM goods WHERE type_code = '101' AND type_code = tc.code);

SELECT tc.code_name

FROM type_code AS tc

WHERE

NOT EXISTS(SELECT 1 FROM goods WHERE type_code = '101' AND type_code = tc.code);

出力結果（NOT EXISTS句）

NOT EXISTS句の結果は以下となります。

SQL, ブログ

SQL：指定した件数のみ取得する方法

2022年6月9日管理者コメントする

SQLで指定した件数のみ取得する場合、MySQLやPostgreSQLではLIMIT句を使用します。

OracleではLIMIT句は使用できないのでROWNUMを使用します。

使用例

サンプルテーブル「goods」
クエリー（SQL）
クエリーの最後にLIMIT(取得したいレコード数)を指定することで指定した件数のみ取得することが出来ます。

LIMITで指定した件数を取得する場合（PostgreSQL）
PgSQL

SELECT * FROM goods ORDER BY type_code LIMIT(3);

1

SELECT * FROM goods ORDER BY type_code LIMIT(3);

Oracleの場合、ROWNUMを指定しただけではORDER BYでソート後の状態で取得ができないので副問合せで一度ソート後にROWNUMを指定することで指定した件数で取得することが出来ます。

Oracleで指定した件数を取得する場合
Oracle PL/SQL

SELECT * FROM (SELECT * FROM goods ORDER BY type_code) WHERE ROWNUM <= 3;

1
2
3

SELECT *
FROM (SELECT * FROM goods ORDER BY type_code)
WHERE ROWNUM <= 3;
出力結果

サンプルテーブル

UPPER関数（小文字 ⇒ 大文字へ変換）の使用例

LOWER関数（大文字 ⇒ 小文字へ変換）の使用例

関連記事

サンプルテーブル

SUBSTR関数の使用例

LEFT関数の使用例

RIGHT関数の使用例

関連記事

サンプルテーブル

REPLACE関数の使用例

TRANSLATE関数の使用例

関連記事

サンプルテーブル

CONCAT関数の使用例

文字列結合子「||」の使用例

CONCAT_WS関数の使用例

関連記事

イメージ図

サンプルテーブル

UNIONの使用例

UNION ALLの使用例

関連記事

システム日付を取得するSQL：MySQL、PostgreSQL

システム日付を取得するSQL：Oracle

関連記事

使用例

クエリー（SQL）

出力結果

関連記事

使用例

関連記事

サンプルテーブル

IN句を使用したSQL例

EXISTS句を使用したSQL例

出力結果（IN句、EXISTS句）

NOT EXISTS句を使用したSQL例

出力結果（NOT EXISTS句）

関連記事

使用例

関連記事

「駑馬十駕」 IT系情報を中心に調べた事をコツコツ綴っています。

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事