LoginSignup
0
0
@fuk101(Saya Fukuhara)

SQLの基本知識や言語構造について浅く紹介

Last updated at Posted at 2023-06-28

はじめに

なんとなくSQLを知っている人向けに、SQLってこんなことができるよ、というのをいくつかかいつまんで浅く紹介します。

本記事に記載されているSQL文は、製品名を明記していないものはSQL Serverを対象にしています。

本記事は次の情報を参考に執筆しました。

目次

  1. SQLの基本知識
    • SQLでできることと、データベース製品によるSQLの違いについて
  2. SQLの言語体系
    • SQL言語の構造について
  3. 基本のSQL文
    • 基本的なSQL文について
  4. 高度なSQL文
    • 高度なSQL文を用いてできることについて
  5. クエリパフォーマンスの最適化
    • SQL文を書くうえで大事なパフォーマンスについて

1. SQLの基本知識

SQLでできることと、データベース製品によるSQLの違いについて紹介します。

SQLとは

データベースとやり取りするときに使う言語のことです。
SQLという言語を用いることで、データベースからデータを抽出したり、蓄積したり、加工ができます。

SQLとは

SQLとさまざまなデータベース

SQLは、規格団体であるANSIやISOにより標準化されており、さまざまなデータベースで使用できます。

主なデータベースには次のようなものがあります。

  • Oracle Database
  • Microsoft SQL Server
  • Access
  • DB2
  • PostgreSQL
  • MySQL

データベースによるSQLの違い

SQLはさまざまなデータベースで使用できる共通の言語ですが、実際にはデータベースによって細かな記述の違いや独自の機能が存在します。
データベースによって使用できる型や関数が違うこともあるため、移行や併用などの際は注意が必要です。

たとえば数値から文字列に型変換する関数にはCAST関数、TO_CHAR関数などがありますが、データベースによって使える関数が違ったり、引数が異なったりします。

数値から文字列に型変換する関数の例

  • CAST関数
    型の指定方法がデータベースによってことなります。
    • CAST(0.123 AS VARCHAR2(10))（Oracle）
    • CAST(0.123 AS VARCHAR)（SQL Server）
    • CAST(0.123 AS CHAR(10))（DB2）
    • CAST(0.123 AS CHAR)（MySQL）
  • TO_CHAR関数
    データベースによって指定する引数の数が異なります。
    • TO_CHAR(0.123)（Oracle）
    • TO_CHAR(0.123,'999,999')（PostgreSQL）

2. SQLの言語体系

SQL言語の体系・構成について紹介します。

手続き型言語と非手続き型言語

SQLは、データベースからデータを取得したり登録することを目的とする非手続き型言語です。
基本的なSQL命令には、IFによる条件制御やWHILEによる繰り返しなどの制御命令は含まれません。
SQLを制御するためには、各データベース製品が提供している手続き型言語を使用します。

たとえばSQL Serverでは、Transact-SQLという独自に拡張された手続き型言語が実装されています。
Oracle Databaseでは、PL/SQLという手続き型言語が実装されています。

SQL Serverに実装されているTransact-SQLの例

Transact-SQLの例

SQL言語の主な構成要素

SQL言語の主な構成要素は「予約語」、「データベースオブジェクト」、「式」があります。

  • 予約語
    • 「SELECT」や「CREATE」のような命令や、命令中の単語のこと
  • データベースオブジェクト
    • テーブルやビューのこと
  • 式
    • テーブルやビューの列、演算子、関数のこと

SQL言語の主な構成要素の例

SQL言語の主な構成要素の例

3. 基本のSQL文

SQLの主なコマンド命令について、基本となるものを紹介します。

データ操作命令（DML）

データを操作する命令のことを「データ操作命令（DML）」と呼びます。

データ操作命令には次の4つの命令があります。

  1. SELECT：テーブルからデータを検索し、その結果を返す
  2. INSERT：テーブルに新しくデータ行を追加する
  3. UPDATE：既存データ行を更新する
  4. DELETE：データ行を削除する

SELECT命令

SELECT命令は、データベースのテーブルからデータを選択する、基本的な命令です。
SELECTの次に「選択列リスト」に記述した列のみが結果として得られます。
テーブルのすべての列を得るには、メタ文字である*を使用するとすべての列を得ることができます。

次の例は、Salesテーブルからすべての列、すべての行を選択するSQL文です。

SELECT * FROM [Sales]

INSERT命令

INSERT命令は、テーブルに行を追加できます。
列を指定すると、その列の値だけを追加できます。

次の例は、Salesテーブルに行を追加するSQL文です。

INSERT INTO [Sales]
VALUES(
	 99999
	,999999
	,Null
	,1
	,9999
	,1000
	,'2023-04-24'
)

UPDATE命令

UPDATE命令は、テーブルの既存データ行を更新できます。
WHEREを省略するとテーブルのすべての行が更新対象になります。

次の例は、SalesテーブルのOrderDateが'2023-04-24'の行のみを更新するSQL文です。

UPDATE
	[Sales]
SET
	[Quantity] = 10
WHERE
	[OrderDate] = '2023-04-24'

DELETE命令

DELETE命令は、テーブルの行を削除できます。
WHEREを省略するとテーブルのすべての行が削除されます。

次の例は、SalesテーブルのOrderDateが'2023-04-24'の行のみを削除するSQL文です。

DELETE FROM
	[Sales]
WHERE
	[OrderDate] = '2023-04-24'

4. 高度なSQL文

高度なSQL文を用いてできることについて紹介します。

副問い合わせ（サブクエリ）

SQLではSELELCTの式としてSELECTによる結果を記述できます。これを「副問い合わせ」または「サブクエリ」と呼びます。
副問い合わせによって、より複雑で高度な問い合わせが可能です。
データベースによっては、副問い合わせを使える部分が制限されていることもあるので、注意が必要です。

副問い合わせは、SELECT句やFROM句、WHERE句などで使うことができます。

副問い合わせの例（FROM句で使う場合）

FROM句で副問い合わせを記述すれば、SELECTの結果が1つの表として扱われます。

次の例はProductIDの重複を省いた件数を取得するSQL文です。

SELECT
	Count([ProductID]) as [ProductIDCount]
FROM (
	SELECT DISTINCT
		[ProductID]
	FROM
		[Sales]
) as sub

副問い合わせの例（WHERE句で使う場合）

WHERE句でも副問い合わせを記述できます。
次の例は、Quantity列の最大値と一致する行を返すSQL文です。

SELECT
	*
FROM
	[Sales]
WHERE
	[Quantity] = (
		SELECT Max([Quantity]) FROM [Sales]
	)

相関副問い合わせ

副問い合わせのSELECT文で、主問い合わせの列を参照して検索する問い合わせを、相関副問い合わせといいます。

次の例は、ProductIDごとのOrderDateが直近のレコードを返すSQL文です。

SELECT *
FROM [Sales] as S1
WHERE [OrderDate] = (
		SELECT Max([OrderDate])
		FROM [Sales] as S2
		WHERE S1.[ProductID] = S2.[ProductID]
	)

分析関数（ウィンドウ関数）

分析関数とは、行のグループ全体に対して処理を行う関数のことです。
集計関数とは異なり、分析関数では集計対象となる行の範囲を任意で指定できます。

分析関数を使えば、全体に対しての構成比を集計したり、1つ前の行との合計を集計できます。
分析関数は他の行を参照できる関数です。

集計関数と分析関数を比較してみます。

集合関数の例

まず、集計関数では、GROUP BYで指定した列の値グループごとに値を返します。

次の例は、ProductIDごとのQuantityの合計を返すSQL文です。

SELECT
	 [ProductID]
	,sum([Quantity]) as [QuantitySum]
FROM
	[Sales]
GROUP BY
	[ProductID]

分析関数の例（全体の合計）

一方、分析関数では、GROUP BYで指定した列の値グループにかかわらず、任意の行の範囲集計した結果を返します。

次の例は、GROUP BYで指定した列と関係なく全体のQuantityの合計を返すSQL文です。

SELECT
	 [ProductID]
	,sum([Quantity]) as [QuantitySum]
	,sum(sum([Quantity])) OVER() as [QuantitySumAll]
FROM
	[Sales]
GROUP BY
	[ProductID]

分析関数の例（グループごとの合計）

PARTITION BY句を使えば、グループごとの合計ができます。
次の例は、OrderYearMonthごとのQuantityの合計を返すSQL文です。

SELECT
	 [ProductID]
	,[OrderYearMonth]
	,sum([Quantity]) AS [Quantity]
	,sum(sum([Quantity])) OVER(PARTITION BY [OrderYearMonth]) AS [QuantitySum]
FROM
	[Sales]
GROUP BY
	 [ProductID]
	,[OrderYearMonth]
ORDER BY
	 [ProductID]
	,[OrderYearMonth]

分析関数の例（前月比）

LAG関数を使用すれば前の行、LEAD関数を使用すれば後ろの行を取得できます。

次の例は、OrderYearMonthごとの前年比を返すSQL文です。

SELECT
	 [OrderYearMonth]
	,sum([Quantity]) AS [QuantitySum]
	,lag(sum([Quantity]),1) OVER(ORDER BY [OrderYearMonth]) AS [QuantitySum_lastMonth]
	,sum([Quantity]) - lag(sum([Quantity]),1) OVER(ORDER BY [OrderYearMonth]) AS [MoM]
FROM
	[Sales]
GROUP BY
	[OrderYearMonth]
ORDER BY
	[OrderYearMonth]

5. クエリパフォーマンスの最適化

SQL文を実行するうえで大事なパフォーマンスについて紹介します。

パフォーマンス最適化とは

SQLのパフォーマンスを最適化することによって、クエリを実行してから結果が返るまでのレスポンス時間を短縮できます。
また、処理に必要なリソースの使用量を最小限に抑えることでスループットの向上にもなります。

パフォーマンスの最適化の実例を紹介します。

LEFT JOIN + is nullではなくnot existsを使う

1度も売れていない商品を抽出する際などに、LEFT JOIN + is nullだと一度JOINしてから検索するため、not exists句を使えば速度改善できる可能性があります。

--OKパターン
SELECT [ProductID] FROM [Product] as P
WHERE not exists (SELECT * FROM [Sales] as S
		WHERE P.[ProductID] = S.[ProductID])

--NGパターン
SELECT P.[ProductID] FROM [Product] as P
LEFT JOIN [Sales] as S
ON P.[ProductID] = S.[ProductID]
WHERE S.[ProductID] is null

UNION ALLを使用する

UNIONなどの集合演算は、重複を排除するためにソートが行われます。
重複を気にしなくてよいのであれば、UNION ALLを使用すればソートが発生しないためパフォーマンスの向上につながります。

--OKパターン
SELECT * FROM [Sales]
UNION ALL
SELECT * FROM [Sales]

--NGパターン
SELECT * FROM [Sales]
UNION
SELECT * FROM [Sales]

インデックスを使用した検索を行う

インデックスとはテーブルへの処理を高速化するためのデータ構造で、索引（さくいん）ともいいます。
本で例えると、特定の項目のページを探す場合、索引を使わなければ全ページを順番に調べる必要があり検索に時間がかかります。
しかし索引を使えば、調べたい項目のページ番号がわかり、検索を高速化できます。

インデックスを適切に設定し、かつインデックスを活用できるSQL文を書くことで、速度改善が見込めます。

インデックスが使用されない例（後方一致）

インデックスを設定した列に対して検索する場合でも、後方一致や部分一致の場合はインデックスが使用されません。

--OKパターン
SELECT * FROM [Sales]
WHERE [SalesOrderID] like '7230%'

--NGパターン
SELECT * FROM [Sales]
WHERE [SalesOrderID] like '%2301'

インデックスが使用されない例（型変換）

また、検索対象のカラムで関数を使用した場合もインデックスを使用できないときがあります。
文字列のカラムに数値で検索することにより暗黙の型変換が行われる場合もあるので注意です。

--OKパターン
SELECT * FROM [Sales]
WHERE [SalesOrderID] = '72301'

--NGパターン
SELECT * FROM [Sales]
WHERE [SalesOrderID] = 72301

さいごに

ここで紹介したSQLの機能やテクニックはごく一部ですが、少しでもSQLを理解するきっかけになればうれしいです。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0