導入
リレーショナルデータベースと構造化照会言語（SQL）を使用すると、リレーショナルデータベース管理システムにデータを保存、管理、取得できます。SQLは、データベースに保存されたデータをそのまま取得できます。.
SQLでは、関数を使って計算やデータの操作を行うこともできます。例えば、関数を使って商品価格を最も近いドルに丸めて取得したり、商品の平均購入回数を計算したり、購入した商品の保証期限までの日数を調べたりすることができます。.
このチュートリアルでは、さまざまな SQL 関数を使用して数学的な計算を実行し、文字列と日付を操作し、集計関数を使用して要約を計算します。.
前提条件
このガイドに従うには、SQLベースのリレーショナルデータベース管理システム（RDBMS）が動作するコンピュータが必要です。このガイドの手順と例は、以下の環境で検証されています。
- Ubuntu 20.04 の初期サーバー セットアップ ガイドに記載されているように、管理者権限を持つ非 root ユーザーと UFW で構成されたファイアウォールを使用して Ubuntu 20.04 を実行するサーバー。.
- MySQLは、「Ubuntu 20.04にMySQLをインストールする方法」ガイドに記載されているとおり、サーバーにインストールされ、セキュリティ保護されています。このガイドでは、手順3で説明した手順で作成された非rootのMySQLユーザーを使用することを前提としています。.
- 「SQL でテーブルから行を選択する方法」ガイドで説明されているように、データベースからデータを選択するための SELECT クエリの実行に関する基本的な紹介。.
注: 多くのRDBMSは独自のSQL実装を使用しています。このチュートリアルで説明するコマンドはほとんどのRDBMSで動作しますが、標準SQL構文では限られた数の関数しか指定されていません。また、標準構文のサポートはデータベースエンジンによって異なります。MySQL以外のシステムでテストした場合、正確な構文や出力が異なる場合があります。.
また、関数を使用するには、サンプルデータがロードされたテーブルを含むデータベースも必要です。MySQLサーバーへの接続と、このガイドの例で使用するテストデータベースの作成方法の詳細については、「MySQLへの接続とサンプルデータベースの設定」セクションを確認することをお勧めします。.
MySQLへの接続とサンプルデータベースの設定
このセクションでは、このガイドの例に従うために、MySQL サーバーに接続してサンプル データベースを作成します。.
SQL データベース システムがリモート サーバー上で実行されている場合は、SSH 経由でサーバーにログインします。
ssh sammy@your_server_ip次にMySQLサーバー環境を開きます。 サミー MySQL ユーザー名を入力してください:
mysql -u sammy -pbookstore というデータベースを作成します。
CREATE DATABASE bookstore;データベースが正常に作成された場合は、次のような出力が表示されます。
Output
Query OK, 1 row affected (0.01 sec)bookstore データベースを選択するには、USE コマンドを実行します。
USE bookstore;次の出力が表示されます。
Output
Database changedデータベースを選択したら、その中にサンプルテーブルを作成できます。このガイドでは、様々な著者の様々な書籍を販売する架空の図書館を例に挙げます。.
在庫テーブルには、書店で入手可能な書籍に関する情報が含まれています。このテーブルには以下の列が含まれます。
- book_id: この列には、intデータ型で表される各書籍のIDが格納されます。この列はテーブルの主キーとなり、各値は対応する行の一意の識別子となります。.
- 著者: この列には、最大 50 文字の varchar データ型を使用して表された本の著者の名前が含まれます。.
- タイトル: この列には、購入した書籍のタイトルが含まれます。タイトルは、最大 200 文字の varchar データ型で表されます。.
- introduction_date: この列は日付データ型を使用して、書店で各書籍が紹介された日付を記録します。.
- 在庫: この列には、書店の在庫にある書籍の数が整数データ型で格納されます。.
- 価格: この列には、小数点の前に最大 5 つの値、小数点の後に最大 2 つの値を持つ小数データ型を使用して、書籍の小売価格が格納されます。.
次のコマンドでサンプル テーブルを作成します。
CREATE TABLE inventory (
book_id int,
author varchar(50),
title varchar(200),
introduction_date date,
stock int,
price decimal(5, 2),
PRIMARY KEY (book_id)
);次の出力が印刷された場合、テーブルは作成されています。
Output
Query OK, 0 rows affected (0.00 sec)次に、次の INSERT INTO 操作を実行して、purchases テーブルにサンプル データをロードします。
INSERT INTO inventory
VALUES
(1, 'Oscar Wilde', 'The Picture of Dorian Gray', '2022-10-01', 4, 20.83),
(2, 'Jane Austen', 'Pride and Prejudice', '2022-10-04', 12, 42.13),
(3, 'Herbert George Wells', 'The Time Machine', '2022-09-23', 7, 21.99),
(4, 'Mary Shelley', 'Frankenstein', '2022-07-23', 9, 17.43),
(5, 'Mark Twain', 'The Adventures of Huckleberry Finn', '2022-10-01', 14, 23.15);INSERT INTO 操作は、指定された値を持つ5冊の本を inventory テーブルに追加します。次の出力は、5行すべてが追加されたことを示しています。
Output
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
これで、ガイドの残りの部分に従って、SQL の関数を使い始める準備が整いました。.
SQL関数の理解
関数とは、1つ以上の値を受け取り、データに対して計算または変換を実行し、結果として新しい値を返す、名前付きの式です。SQL関数は数学関数に似たものと考えることができます。例えば、log(x)関数は値xを受け取り、xの対数を返します。.
たとえば、すべての書籍のタイトルとその価格を、最も高価なものから最も安価なものの順に取得したい場合は、次のコマンドを実行できます。
SELECT title, price, introduction_date FROM inventory ORDER BY price DESC;次のような出力が得られます。
Output
+------------------------------------+-------+-------------------+
| title | price | introduction_date |
+------------------------------------+-------+-------------------+
| Pride and Prejudice | 42.13 | 2022-10-04 |
| The Adventures of Huckleberry Finn | 23.15 | 2022-10-01 |
| The Time Machine | 21.99 | 2022-09-23 |
| The Picture of Dorian Gray | 20.83 | 2022-10-01 |
| Frankenstein | 17.43 | 2022-07-23 |
+------------------------------------+-------+-------------------+
5 rows in set (0.000 sec)このコマンドでは、 タイトル、 価格 そして 導入日 は列名であり、出力では、データベースは各書籍についてこれらの列から取得されたそのままの値（書籍の完全なタイトル、価格、書籍が図書館に到着した日付）を表示します。.
しかし、データベースから何らかの処理や操作を行った後に値を取得したい場合もあるでしょう。例えば、書籍の価格を最も近いドルに丸めたり、書籍のタイトルを大文字で表示したり、月や日を除いた出版年を表示したりしたい場合などです。このような場合は関数を使用します。.
SQL関数は、操作対象となるデータの種類に応じて、いくつかのグループに大まかに分類できます。最もよく使用される関数は次のとおりです。
- 数学関数: 数値を操作して、四捨五入、対数、平方根、累乗などの計算を実行する関数。.
- 文字列操作関数: 文字列およびテキスト フィールドを操作して、テキストを大文字に変換したり、切り捨てたり、値内の単語を置き換えたりするなどのテキスト変換を実行する関数。.
- 日付と時刻の関数：日付フィールドを操作する関数。これらの関数は、特定の日付に日数を加算したり、日付から年数を減算したりするなど、計算や変換を実行します。.
- 集計関数: すべての行の平均価格を計算するなど、複数の行から取得した値に対して演算を行う数学関数の特殊なケース。.
注: MySQLを含むほとんどのリレーショナルデータベースは、SQL標準で定義されている標準関数セットを拡張し、データベースエンジン固有の追加操作を提供しています。標準SQL関数セット以外の関数の多くは、多くのデータベースで同様に動作しますが、特定のRDBMSとその固有の機能に固有の関数もあります。データベースが提供する関数の詳細については、選択したデータベースのドキュメントを参照してください。MySQLの場合は、組み込み関数と演算子リファレンスについて詳しく説明しています。.
次の例は、架空の存在しない関数 EXAMPLE を使用して、SELECT クエリで書店在庫データベース内の価格値の結果を変更するための一般的な構文を示しています。
SELECT EXAMPLE(price) AS new_price FROM inventory;
関数 (EXAMPLE) は、列名 (price) を括弧で囲んだ引数として受け取ります。クエリのこの部分は、データベースに列内の価格値に対して EXAMPLE 関数を実行し、その操作の結果を返すように指示します。AS new_price は、クエリ中に計算された値に一時的な名前 (new_price) を割り当てるようにデータベースに指示します。これにより、出力で関数の結果を識別でき、WHERE 句と ORDER BY 句を使用して計算された値を参照できます。.
次のセクションでは、数学関数を使用して一般的な計算を実行します。.
数学関数の使用
数学関数は、書籍の価格やサンプルデータベース内の在庫数などの数値を操作します。これらの関数を使用することで、データベース内で計算を実行し、ニーズに合わせて結果をカスタマイズできます。.
丸めは、SQLにおける数学関数の最も一般的な用途の一つです。例えば、すべての書籍の価格を取得する必要があり、値を最も近いドルに丸めたいとします。これを行うには、次の関数を使用します。 ラウンド 丸め演算を実行するを使用します。.
次のコマンドを実行します。
SELECT title, price, ROUND(price) AS rounded_price FROM inventory;
次のような出力が表示されます。
Output
+------------------------------------+-------+---------------+
| title | price | rounded_price |
+------------------------------------+-------+---------------+
| The Picture of Dorian Gray | 20.83 | 21 |
| Pride and Prejudice | 42.13 | 42 |
| The Time Machine | 21.99 | 22 |
| Frankenstein | 17.43 | 17 |
| The Adventures of Huckleberry Finn | 23.15 | 23 |
+------------------------------------+-------+---------------+
5 rows in set (0.000 sec)上記のコマンドは列の値を返します。 タイトル そして 価格 一時的な柱で 丸められた価格 これは関数から得られる ROUND(価格) この関数は列名（ここでは 価格）を実行し、テーブル内のその列の値を最も近い整数に丸めます。.
ROUND関数は、列名の代わりに、小数点以下の桁数や算術演算を指定するための追加引数も受け入れることができます。例えば、次のクエリを実行します。
SELECT title, price, ROUND(price * stock, 1) AS stock_price FROM inventory;
次のような出力が得られます。
Output
+------------------------------------+-------+-------+-------------+
| title | stock | price | stock_price |
+------------------------------------+-------+-------+-------------+
| The Picture of Dorian Gray | 4 | 20.83 | 83.3 |
| Pride and Prejudice | 12 | 42.13 | 505.6 |
| The Time Machine | 7 | 21.99 | 153.9 |
| Frankenstein | 9 | 17.43 | 156.9 |
| The Adventures of Huckleberry Finn | 14 | 23.15 | 324.1 |
+------------------------------------+-------+-------+-------------+
5 rows in set (0.000 sec)実行 ROUND(価格 * 在庫, 1) まず、書籍1冊の価格と在庫数を掛け合わせ、その結果を小数点第1位に丸めます。結果は一時列Stock_Priceに表示されます。.
MySQLに組み込まれているその他の数学関数には、三角関数、平方根、累乗、対数、指数などがあります。SQLでの数学関数の使用法について詳しくは、「SQLで数式と集計関数を使用する方法」チュートリアルをご覧ください。.
次のセクションでは、SQL 関数を使用してデータベースからのテキストを操作します。.
テキスト関数の使用
SQLのテキスト関数は、文字列値を操作したり変更したりするために使用できます。例えば、書籍名を、タイトルの先頭と末尾にある余分なスペースを削除することで、より整然とした形式で取得したい場合などです。.
これを行うには、関数を使用します トリム この関数を使用して、文字列の先頭と末尾にあるすべてのスペースと余分な文字を削除します。.
次の例を考えてみましょう。
SELECT LOWER(title) AS title_lowercase FROM inventory;
次の出力が画面に表示されます。
Output
+------------------------------------+
| title_lowercase |
+------------------------------------+
| the picture of dorian gray |
| pride and prejudice |
| the time machine |
| frankenstein |
| the adventures of huckleberry finn |
+------------------------------------+
5 rows in set (0.001 sec)
SQL関数LOWERは、単一の引数を受け取り、その内容を小文字に変換します。エイリアス列AS title_downcaseを介して、結果のデータはtitle_downcaseという名前の一時列に格納されます。.
今度はすべての著者名を大文字に変換して取得します。次のSQLクエリを実行してみてください。
SELECT UPPER(author) AS author_uppercase FROM inventory;
次のような出力が得られます。
Output
+----------------------+
| author_uppercase |
+----------------------+
| OSCAR WILDE |
| JANE AUSTEN |
| HERBERT GEORGE WELLS |
| MARY SHELLEY |
| MARK TWAIN |
+----------------------+
5 rows in set (0.000 sec)LOWER関数の代わりにUPPER関数を使用しました。UPPER関数はLOWER関数と同様に動作しますが、テキストを大文字に変換します。データを取得する際に大文字と小文字の一貫性を保ちたい場合は、どちらの関数も使用できます。.
もう一つの便利な文字列操作関数はCONCATです。この関数は、テキスト値を含む複数の引数を受け取り、それらを連結します。試しに、著者名と書籍名を1つの列から取得してみましょう。これを行うには、次のステートメントを実行します。
SELECT CONCAT(author, ': ', title) AS full_title FROM inventory;
このコマンドは次の出力を返します。
Output +------------------------------------------------+ | full_title | +------------------------------------------------+ | Oscar Wilde: The Picture of Dorian Gray | | Jane Austen: Pride and Prejudice | | Herbert George Wells: The Time Machine | | Mary Shelley: Frankenstein | | Mark Twain: The Adventures of Huckleberry Finn | +------------------------------------------------+ 5 rows in set (0.001 sec)
CONCAT関数は複数の文字列を連結し、3つの引数で実行されます。最初の引数「author」は、著者名を含む列「author」を参照します。2番目の引数「:」は、著者と書籍名をコロンで区切るための任意の文字列値です。最後の引数「title」は、書籍名を含む列「title」を参照します。.
このクエリの結果、著者とタイトルは full_title と呼ばれる一時列に返され、データベース エンジンによって直接連結されます。.
MySQLに組み込まれているその他の文字列関数には、文字列の検索と置換、部分文字列の取得、文字列値の埋め込みとトリミング、正規表現の適用などがあります。SQL関数を使用して複数の値を連結する方法の詳細については、「SQLでCAST関数と連結式を使用してデータを操作する方法」チュートリアルをご覧ください。また、MySQLドキュメントの「文字列関数と演算子」も参照してください。.
次のセクションでは、SQL 関数を使用してデータベースの日付を操作します。.
日付と時刻関数の使用
SQLの日付と時刻関数を使用すると、SQLクエリの処理中に日付とタイムスタンプを保持する列に格納された値を操作できます。日付情報の一部を抽出したり、日付計算を実行したり、日付とタイムスタンプを必要な出力形式にフォーマットしたりするために使用できます。.
出力に単一の日付列を含めるのではなく、本の出版日を年、月、日に個別に分割する必要があるとします。.
次のコマンドを実行してみてください。
SELECT introduction_date, YEAR(introduction_date) as year, MONTH(introduction_date) as month, DAY(introduction_date) as day FROM inventory;
次のような出力が表示されます。
Output
+-------------------+------+-------+------+
| introduction_date | year | month | day |
+-------------------+------+-------+------+
| 2022-10-01 | 2022 | 10 | 1 |
| 2022-10-04 | 2022 | 10 | 4 |
| 2022-09-23 | 2022 | 9 | 23 |
| 2022-07-23 | 2022 | 7 | 23 |
| 2022-10-01 | 2022 | 10 | 1 |
+-------------------+------+-------+------+
5 rows in set (0.000 sec)このSQL文は、YEAR、MONTH、DAYという3つの独立した関数を使用しています。各関数は、日付が格納されている列の名前を引数として受け取り、日付全体のうち、それぞれ年、月、日のいずれか1つの部分のみを抽出します。これらの関数を使用することで、SQLクエリで日付の個々の部分にアクセスすることができます。.
日付を操作するのに便利な関数として、DATEDIFFがあります。この関数を使うと、2つの日付間の日数を取得できます。では、各書籍が出版された日から現在の日付までの間に何日が経過したかを確認してみましょう。.
次のクエリを実行します。
SELECT introduction_date, DATEDIFF(introduction_date, CURRENT_DATE()) AS days_since FROM inventory;
次の出力が画面に表示されます。
Output
+-------------------+------------+
| introduction_date | days_since |
+-------------------+------------+
| 2022-10-01 | -30 |
| 2022-10-04 | -27 |
| 2022-09-23 | -38 |
| 2022-07-23 | -100 |
| 2022-10-01 | -30 |
+-------------------+------------+
5 rows in set (0.000 sec)DATEDIFF関数は、開始日と終了日の2つの引数を取ります。DATEDIFF関数は、これら2つの時点間の日数を計算します。終了日の方が早い場合、結果は負の数になることがあります。この例では、最初の引数はinventoryテーブルの日付を保持するintroduction_date列の名前です。2番目の引数はCURRENT_DATEという別の関数で、現在のシステム日付を表します。このクエリを実行すると、これら2つの時点間の日数が取得され、その結果はdays_sinceという一時列に格納されます。.
注: DATEDIFF は公式のSQL標準関数セットには含まれていません。多くのデータベースがこの関数をサポートしていますが、構文はデータベースエンジンによって異なる場合があります。この例はMySQLのネイティブ構文に従っています。.
MySQLに組み込まれているその他の日付操作関数には、時間間隔や日付の加算と減算、様々な言語形式への日付のフォーマット、曜日と月の名前の取得、新しい日付値の作成などがあります。SQLでの日付操作の詳細については、「SQLで日付と時刻を操作する方法」チュートリアルをご覧ください。また、MySQLドキュメントの日付と時刻関数も参照できます。.
次のセクションでは、集計関数の使用方法を学習します。.
集計関数の使用
これまでのすべての例では、SQL関数を使用して、書店にある本を表す行内の個々の列の値に変換または計算を適用しました。SQLは、複数の行に対して数学的計算を実行する方法を提供し、データセット全体の集計情報を見つけるのに役立ちます。.
- SQL の基本的な集計関数には次のものがあります。
- 計算が実行される値の平均を表す AVG です。.
- 計算が実行される値の数をCOUNTします。.
- 最大値はMAX。.
- 最小値の場合は MIN。.
- すべての値の合計を求める SUM。.
SELECTクエリには複数の集計関数を含めることができます。書店に並んでいる本の冊数、各本の最高価格、そしてカタログ全体の平均価格を調べたいとします。これを行うには、次のステートメントを実行します。
SELECT COUNT(title) AS count, MAX(price) AS max_price, AVG(price) AS avg_price FROM inventory;
このコマンドは次の出力を返します。
Output
+-------+-----------+-----------+
| count | max_price | avg_price |
+-------+-----------+-----------+
| 5 | 42.13 | 25.106000 |
+-------+-----------+-----------+
1 row in set (0.001 sec)上記のクエリでは、3つの集計関数を同時に使用しています。COUNT関数は、クエリが検索する行数をカウントします。この例ではタイトルを引数として渡していますが、マークされた列ごとに行数は同じなので、関数の引数として他の列名を使用できます。MAX関数は、price列の最大値を計算します。ここで、列名は重要です。計算は、その列の値に対して実行されるためです。最後の関数はAVG関数で、price列のすべての価格の平均を計算します。.
このように集計関数を使用すると、集計計算の値を表す一時列を含む行がデータベースに返されます。ソース行は計算に内部的に使用されますが、クエリからは返されません。この例では、集計関数を使用して在庫テーブル全体の統計値を一度に計算し、すべての行を集計に考慮しています。.
SQLでは、テーブルの行をグループに分割し、各グループの合計を個別に計算することもできます。例えば、異なる著者の書籍の平均価格を計算して、どの著者が最も高価な書籍を出版したかを調べることができます。このような計算のための行のグループ化の詳細については、「SQLでGROUP BYとORDER BYを使用する方法」のチュートリアルをご覧ください。また、集計の使用法の詳細については、「SQLで数式と集計関数を使用する方法」のチュートリアルをご覧ください。.
結果
このガイドでは、SQLの関数を使ってデータの計算を行い、より複雑なクエリを作成する方法を学びました。これらの関数は、様々な方法でデータを操作し、より良い結果を得るのに役立ちます。SQLの正確な構文は、リレーショナルデータベース管理システム（RDBMS）の種類によって異なる場合があることにご注意ください。.
