SQLとは何ですか？データ分析の共通語

現在、構造化照会言語は、リレーショナルデータベース内のデータを操作および照会するための標準的な手段ですが、製品間に独自の拡張機能があります。SQLの容易さと遍在性により、多くの「NoSQL」またはHadoopなどの非リレーショナルデータストアの作成者は、SQLのサブセットを採用したり、独自のSQLのようなクエリ言語を考案したりしています。

しかし、SQLは必ずしもリレーショナルデータベースの「ユニバーサル」言語ではありませんでした。当初（1980年頃）から、SQLはそれに対して特定のストライキを行っていました。私を含む当時の多くの研究者や開発者は、SQLのオーバーヘッドにより、SQLが本番データベースで実用的でなくなると考えていました。

明らかに、私たちは間違っていました。しかし、多くの人は、SQLの使いやすさとアクセシビリティのすべてのために、実行時のパフォーマンスに課せられる価格が高すぎることが多いと信じています。

SQL履歴

SQLが登場する前は、データベースにはタイトなナビゲーションプログラミングインターフェイスがあり、通常はCODASYLデータモデルと呼ばれるネットワークスキーマを中心に設計されていました。CODASYL（データシステム言語委員会）は、COBOLプログラミング言語（1959年から）とデータベース言語拡張（10年後から）を担当するコンソーシアムでした。

CODASYLデータベースに対してプログラミングしたときは、1対多の関係を表すセットを介してレコードに移動していました。古い階層型データベースでは、レコードを1つのセットにのみ属することができます。ネットワークデータベースでは、レコードを複数のセットに含めることができます。

CS 101に登録されている学生を一覧表示するとします。まず、名前でセットを検索"CS 101"し、Coursesセットの所有者または親としてEnrollees設定し、レコードffmであるEnrolleesセットの最初のメンバー（）を検索してStudent一覧表示します。それ。次に、ループに入ります。次のメンバー（fnm）を見つけて、リストします。ときにfnm失敗し、あなたは、ループを終了します。

これはデータベースプログラマーにとっては大変な作業のように思えるかもしれませんが、実行時には非常に効率的でした。カリフォルニア大学バークレー校のMichaelStonebrakerやIngresのような専門家は、IDMSなどのCODASYLデータベースでこの種のクエリを実行すると、SQLを使用したリレーショナルデータベースでの同じクエリと比べて、CPU時間の約半分とメモリの半分未満しかかからないと指摘しました。。

比較のために、CS101のすべての学生を返す同等のSQLクエリは次のようになります。

SELECT Student.name FROMコース、登録者、学生WHEREcourse.name

この構文は、以下で説明するように、リレーショナル内部結合（実際には2つ）を意味し、結合に使用されるフィールドなど、いくつかの重要な詳細を省略しています。

リレーショナルデータベースとSQL

実行速度とメモリ使用量の2倍の改善をあきらめるのはなぜですか？開発のしやすさと移植性という2つの大きな理由がありました。1980年には、パフォーマンスとメモリの要件に比べてどちらもそれほど重要ではないと思いましたが、コンピュータハードウェアが改善されて安価になるにつれて、人々は実行速度とメモリを気にすることをやめ、開発コストを心配しました。

言い換えれば、ムーアの法則は、リレーショナルデータベースを支持してCODASYLデータベースを殺害しました。たまたま、開発時間の改善は重要でしたが、SQLの移植性は夢のようなものでした。

リレーショナルモデルとSQLはどこから来たのですか？ EF“ Ted” Coddは、IBM San Jose Research Laboratoryのコンピューター科学者であり、1960年代にリレーショナルモデルの理論を考案し、1970年に公開しました。IBMは、そのCODASYLデータベースIMS / DB。 IBMがついにSystemRプロジェクトを開始したとき、開発チーム（DonChamberlinとRayBoyce）はCoddの下にいませんでした。彼らは、Coddの1971年のAlphaリレーショナル言語ペーパーを無視して、独自の言語であるSEQUEL（Structured English Query Language）を設計しました。 1979年、IBMが製品をリリースする前に、Larry Ellisonはその言語をOracleデータベースに組み込みました（IBMのリリース前のSEQUEL出版物を仕様として使用）。 SEQUELは、国際的な商標違反を回避するためにすぐにSQLになりました。

「SQLを打ち負かすトムトム」（Michael Stonebrakerが述べたように）は、OracleとIBMだけでなく、顧客からも来ていました。 CODASYLデータベースの設計者やプログラマーを雇ったり訓練したりするのは簡単ではなかったので、SEQUEL（およびSQL）ははるかに魅力的に見えました。 SQLは1980年代後半に非常に魅力的だったため、多くのデータベースベンダーは、本質的にCODASYLデータベースの上にSQLクエリプロセッサをステープルで留めていました。

Coddによって設計された純粋なリレーショナルデータベースは、一階述語論理と一致する、リレーションにグループ化されたタプル上に構築されています。実際のリレーショナルデータベースには、フィールド、制約、およびトリガーを含むテーブルがあり、テーブルは外部キーを介して関連付けられています。SQLは、返されるデータを宣言するために使用され、SQLクエリプロセッサとクエリオプティマイザは、SQL宣言をデータベースエンジンによって実行されるクエリプランに変換します。

SQLには、スキーマを定義するためのサブ言語であるデータ定義言語（DDL）と、データを変更するためのサブ言語であるデータ操作言語（DML）が含まれています。これらは両方とも、初期のCODASYL仕様にルーツがあります。SQLの3番目のサブ言語は、SELECTステートメントとリレーショナル結合を介してクエリを宣言します。

SQL `SELECT`ステートメント

このSELECTステートメントは、クエリオプティマイザに、返すデータ、調べるテーブル、従う関係、および返されたデータに課す順序を指示します。特定のデータベースがインデックスヒントをサポートしていない限り、クエリオプティマイザは、ブルートフォーステーブルスキャンを回避し、優れたクエリパフォーマンスを実現するために使用するインデックスを自分で把握する必要があります。

リレーショナルデータベース設計の技術の一部は、インデックスの賢明な使用にかかっています。頻繁なクエリのインデックスを省略すると、読み取りの負荷が高い場合にデータベース全体の速度が低下する可能性があります。インデックスが多すぎると、書き込みと更新の負荷が高くなると、データベース全体の速度が低下する可能性があります。

もう1つの重要な技術は、すべてのテーブルに適切で一意の主キーを選択することです。一般的なクエリに対する主キーの影響だけでなく、別のテーブルに外部キーとして表示されたときに結合でどのように再生されるか、およびデータの参照の局所性にどのように影響するかを考慮する必要があります。

水平シャーディングと呼ばれる、主キーの値に応じて異なるボリュームに分割されるデータベーステーブルの高度なケースでは、主キーがシャーディングにどのように影響するかについても考慮する必要があります。ヒント：テーブルをボリューム全体に均等に分散させる必要があります。これは、日付スタンプや連続する整数を主キーとして使用したくないことを示しています。

SELECT声明の議論は単純に始まるかもしれませんが、すぐに混乱する可能性があります。考えてみましょう：

SELECT * FROM顧客;

簡単ですよね？Customersテーブルのすべてのフィールドとすべての行を要求します。ただし、Customersテーブルに1億行と100フィールドがあり、フィールドの1つがコメント用の大きなテキストフィールドであるとします。各行に平均1キロバイトのデータが含まれている場合、10メガビット/秒のネットワーク接続ですべてのデータをプルダウンするのにどのくらい時間がかかりますか？

おそらく、あなたはあなたがワイヤーを介して送る量を減らすべきです。考えてみましょう：

SELECT TOP 100 companyName、lastSaleDate、lastSaleAmount、totalSalesAmount FROM Customers
州と市の場所
lastSaleDateの降順で注文します。

これで、はるかに少ないデータをプルダウンすることになります。データベースに、4つのフィールドのみを提供し、クリーブランドの会社のみを考慮し、最新の売上を上げた100社のみを提供するように依頼しました。データベース・サーバで最も効率的にそれを行うには、しかし、Customersテーブルには、上のインデックス必要state+cityのためのWHERE句とのインデックスlastSaleDateのためORDER BYとTOP 100句を。

ちなみに、SQLServerTOP 100とSQLAzureには有効ですが、MySQLやOracleには有効ではありません。MySQLではLIMIT 100、WHERE句の後に使用します。OracleではROWNUM、WHERE句の一部としてバインドを使用しますWHERE... AND ROWNUM <=100。つまり、。残念ながら、ANSI / ISO SQL標準（および現在までに9つあり、1986年から2016年まで）はこれまでのところ、各データベースが独自の条項と機能を導入しています。

SQL結合

これまで、SELECT単一テーブルの構文について説明してきました。JOIN句を説明する前に、外部キーとテーブル間の関係を理解する必要があります。これについては、SQL Server構文を使用して、DDLの例を使用して説明します。

これの短いバージョンはかなり単純です。リレーションで使用するすべてのテーブルには、主キー制約が必要です。これは、単一のフィールド、または式で定義されたフィールドの組み合わせのいずれかです。例えば：

CREATE TABLE Persons（
    PersonID int NOT NULL PRIMARY KEY、
    PersonName char（80）、
    ..。

関連する必要のあるすべてのテーブルPersonsには、Persons主キーに対応するフィールドが必要です。また、関係の整合性を維持するには、そのフィールドに外部キー制約が必要です。例えば：

CREATE TABLE Orders（
    OrderID int NOT NULL PRIMARY KEY、
    ..。
    PersonID int外部キー参照Persons（PersonID）
）;

CONSTRAINTキーワードを使用する両方のステートメントの長いバージョンがあり、制約に名前を付けることができます。これは、ほとんどのデータベース設計ツールが生成するものです。

主キーは常にインデックスが付けられ、一意です（フィールド値を複製することはできません）。オプションで、他のフィールドにインデックスを付けることができます。書き込みと更新によるオーバーヘッドが発生する可能性があるため、常にではありませんが、外部キーフィールドとWHEREおよびORDER BY句に表示されるフィールドのインデックスを作成すると便利なことがよくあります。

John Doeによるすべての注文を返すクエリをどのように記述しますか？

SELECT PersonName、OrderID FROM Persons
Persons.PersonID = Orders.PersonIDの内部結合注文
WHERE PersonName;

実際には、4つの種類があるJOIN：INNER、OUTER、LEFT、とRIGHT。これINNER JOINはデフォルトであり（単語は省略できますINNER）、両方のテーブルで一致する値を含む行のみが含まれるものです。注文があるかどうかに関係なく人を一覧表示する場合は、次のように使用しますLEFT JOIN。

SELECT PersonName、OrderID FROM Persons
Persons.PersonID = Orders.PersonIDのLEFTJOIN Orders
PersonNameによる注文;

3つ以上のテーブルを結合するクエリ、式を使用するクエリ、またはデータ型を強制変換するクエリを実行し始めると、最初は構文が少し複雑になる可能性があります。幸い、多くの場合、スキーマ図からクエリ図にテーブルとフィールドをドラッグアンドドロップすることで、正しいSQLクエリを生成できるデータベース開発ツールがあります。

SQLストアドプロシージャ

SELECTステートメントの宣言的な性質により、目的の場所に到達できない場合があります。ほとんどのデータベースには、ストアドプロシージャと呼ばれる機能があります。残念ながら、これはほぼすべてのデータベースがANSI / ISOSQL標準の独自の拡張機能を使用している領域です。

SQL Serverでは、ストアドプロシージャ（またはストアドプロシージャ）の最初の方言はTransact-SQL、別名T-SQLでした。Oracleでは、PL-SQLでした。どちらのデータベースにも、C＃、Java、Rなどのストアドプロシージャ用の言語が追加されています。単純なT-SQLストアドプロシージャは、SELECTステートメントのパラメータ化されたバージョンにすぎない場合があります。その利点は、使いやすさと効率です。ストアドプロシージャは、実行されるたびではなく、保存されるときに最適化されます。

より複雑なT-SQLストアドプロシージャは、複数のSQLステートメント、入力パラメータと出力パラメータ、ローカル変数、BEGIN...ENDブロック、IF...THEN...ELSE条件、カーソル（セットの行ごとの処理）、式、一時テーブル、およびその他のホスト全体を使用する場合があります。手続き型構文。明らかに、ストアドプロシージャ言語がC＃、Java、またはRの場合は、これらのプロシージャ言語の関数と構文を使用します。言い換えると、SQLの動機は標準化された宣言型クエリを使用することでしたが、現実の世界では、データベース固有の手続き型サーバープログラミングがたくさん見られます。

これは、CODASYLデータベースプログラミングの古き良き時代に戻ることはできませんが（カーソルは近づいていますが）、SQLステートメントを標準化する必要があり、パフォーマンスの懸念はデータベースクエリオプティマイザーに任せるべきであるという考えからは遡ります。。結局、パフォーマンスを2倍にすることは、多くの場合、テーブルに残すには多すぎます。

SQLを学ぶ

以下にリストされているサイトは、SQLを学習したり、さまざまなSQL方言の癖を発見したりするのに役立ちます。