SQL(Structured Query Language)は半世紀以上にわたって私たちと一緒にいましたが、すぐには消えません。 SQLは、従来のリレーショナルデータベースと新しいNoSQLデータベース技術の両方で人気があり、データ分析、ビッグデータ処理、コーディング言語などに広く使用されてい
私はSQLのファンです。 それは強力でありながら単純な言語です。 この記事では、私は共有します:
- SQLの力とそれを使用している理由
- 一般的な落とし穴とベストプラクティス
SQLとは何ですか、なぜそれを使用する必要がありますか？
SQLは1970年代初頭にIBMによって開発され、その十年後にOracleによって採用され、それ以来リレーショナルデータベースで使用されてきました。 1986年、SQLは米国標準化協会（ANSI）によって標準として採用されました。
時間の経過とともにデータ量が増加するにつれて、新しいデータベースと技術が登場しました。 これは、従来のデータ処理アプリケーションで処理するには複雑すぎるデータセットのためです。 ビッグデータでは一般的に、NoSQLデータベースはリレーショナルモデルに基づいていません。 SQLのようなクエリ言語をサポートしている可能性があることを強調するために、SQLだけでなくと呼ばれることもあります。 SQLをサポートする新しい非リレーショナルデータベースとライブラリの例を次に示します:
- Apache Spark Analytics Engine
- Google Big Query–managed analytics data warehouse
- フェイスブックPrestoとAWS Athena
- Elastic search
- Pythonデータ分析ライブラリ（pandasql）
- Facebook PrestoとAWS Athena
- Pythonデータ分析ライブラリ（pandasql）
- )
SQLはOLTP(Transaction Processing)とOLAP(Analytic Processing)の両方で広く使用されていますが、この2つは非常に異なるユースケースです。
Snippet1: パンダス——
| トランザクション処理 | 分析処理 | |||
| 一般的な使用法 | リレーショナルデータベース | ビッグデータデータベース | ||
| SQLの使用法 | 多くのユーザーによる挿入、更新、およびクエリを同時に | Selectクエリ。 一度にいくつかのクエリ | ||
| 計算のスケール | 小 | 大-テーブル全体 | ||
| クエリ期間 | 高速 | は時間以上にすることができます | ||
| パフォーマンス関連機能 | インデックスとキー | パーティション、冗長データ(ユースケースごと) | ||
| 分析関数 | よく使われる | よく使われる | よく使われる | よく使われる |
SQLに飛び込む前に、pythonのデータ分析ライブラリであるデータベース外でのSQLの使用例を次に示します:
| コード | 出力 |
|
Name COUNT(DISTINCT ID)0 James 11 John 22 Ted 1 |
これらの例が示すように、SQLは最新かつ最も人気のある技術で生きています。
その力を詳しく見てみましょう。
SQL関数の例
SQLの最も強力な部分は、その関数です。 SQLがサポートする3つのタイプの関数は次のとおりです:
| タイプ | スコープ | レコードの削減 | 例 |
| シンプル | シングルレコード | いいえ | CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END |
| 集計(group by) | レコードのグループ | はい | SUM(column_1) |
| 分析 | レコードのグループ | いいえ | SUM(column_1) OVER (PARTITION BY column_2) |
クエリエンジンの機能を理解するには、クエリエンジンの機能を調べることをお勧めします。 ここでは、たとえば、集計関数と分析関数に関するPrestoのドキュメントを見つけることができます。
推奨されるSQLコーディング規則(命名など)
SQLのパワーを得るためには、コーディング規則を使用することを強くお勧めします。 これは、書き込みと読み取りの両方を改善し、チームワークを向上させます。 ここでは、より重要なものの短いリストです:
| 名前と大文字 | 名前にスペースを自然に含めるアンダースコアを使用します（姓->last_name） キーワードと関数の大文字（SELECT、FROM、WHERE、IN、SUM…） 他のすべての小文字：変数、テーブル名、列 |
| 結合でキーワード | を明示的に使用するキーワードを使用します。CROSS,INNER…ON,LEFT…ON,RIGHT…ON WHERE句の一部として結合を避ける: SELECT…FROM table_a,table_b WHERE… エイリアシング–常にASキーワードを含めます |
| ネストされたフォームを避け、代わりにWITHを使用してください | Select…FROM(SELECT…)
WITH句を使用してください！ このようにして、複雑なクエリを回避し、ステップバイステップの読み取り可能なクエリ Avoid IN/NOT In-Use JOINを作成することができます。 |
| 各キーワード定義の前後の行間隔 | |
| コメントを含める | /*とclosing*/と—の両方が許容されます |
| SELECT* | はい、コンパクトで簡単ですが、新しい列が追加され、必要なくクエリ結果に含まれます。 必要な列名を明示的に記述します |
SQLクエリの例:Good&Bad
コーディング規約の重要性を示すために、これらのgoodとbadの例を見てください。
| タイプ | 良い | 悪い |
| フロー | 二つの単純なクエリ | 一つの複雑なクエリ |
| キーワード | 大文字 | 小文字、他のテキストと同様 |
| Join | Explicit-理解しやすい | Implicit-他のWHERE述語と |
| インデント | Orの前の行間隔は、その範囲を知るのに役立ちます | ORの前の行間隔はありません。 より大きな条件は理解するのがはるかに難しいでしょう |
| 中古 | 未使用 |
スニペット4：悪い例
select emp.department, sum(exp.amount) expensesfrom employees emp, expenses expwhere emp.department in (select departmentfrom employeeswhere department like '%development%' or department like '%management%'group by departmenthaving count(distinct id) > 50) and emp.id = exp.employee_idgroup by emp.departmentスニペット5：良い例
-- filter: Development and Management departments with more than 50 employeesWITH dept AS (SELECT departmentFROM employeesWHERE department LIKE '%Development%'OR department LIKE '%Management%'GROUP BY departmentHAVING Count(DISTINCT id) > 50)-- find expenses for each of the filtered departmentsSELECT emp.department,SUM(exp.amount) AS expensesFROM employees AS empINNER JOIN deptON emp.department = dept.departmentINNER JOIN expenses AS expON emp.id = exp.employee_idGROUP BY emp.departmentここでは、使用できるSQL書式設定のための素晴らしいオンラインツールです: https://sqlformat.org/
SQLの問題トラブルシューティング-ミス&落とし穴
他の言語と同様に、SQLには独自の落とし穴があり、時間を節約し、アプリケーションのバグを防ぐために
SQLエラーの最初の原因&バグ–Null値
スニペット6:NULLクエリ結果
SELECT NULL = NULL --> NULLSELECT NULL != NULL --> NULLSELECT NULL IS NULL --> trueSELECT 1 + NULL --> NULL SELECT SUM(num_column) WHERE FALSE --> NULLこの問題を克服するには、危険があるときはいつでもcoalesceを使用する必要があります。
スニペット7:結合でNullを克服
FROM t1INNER JOIN t2 ON COALESCE(t1.name, '') = COALESCE(t2.name, '')SQLインデックスとパーティションが使用されていません
インデックスを追加してパーティションを宣言することで、データベーススキーマを構築するのに多くの時間を費やしています。 しかし、そこで停止した場合、我々は望むパフォーマンスを得ることはありません—私たちはデータベースエンジンがそれらを使用していることを確認す
たとえば、first_name列にインデックスがあるとしましょう:
|
インデックスが使用されていない |
|
使用される索引 |
ある実装から別の実装への変更があるかもしれませんが、この概念はどのSQL実装でも有効です。
インデックスとパーティションが使用されていることを確認するにはどうすればよいですか？ クエリ実行計画を分析します。 一部のSQL実装では、EXPLAINキーワードを使用できます。 たとえば、Oracleでは、EXPLAIN PLANと呼ばれています。
分析関数SQL評価順序の落とし穴
分析関数を使用している場合、評価順序を誤解するとバグが発生する可能性があるため、これは重要です。 たとえば、
スニペット8:フィルターを使用した分析関数
SELECT first_name, last_name, department, COUNT(1) OVER (PARTITION BY department) AS size_of_departmentFROM employeesWHERE last_name LIKE 'A%'部門のすべての従業員ではなく、姓がAで始まる部門の従業員の数を取得します。 分析関数は最後に実行されることに注意してください。
SQLの標準化
日付と時刻の構文、Null、文字列の連結、比較の大文字と小文字の区別、およびSQLのその他の側面は、ベンダーによって異なり、ベンダー間でアプ
標準SQLのみを使用してみてください。 たとえば、ISNULL（SQL Server）またはNVL（Oracle）の代わりにCOALESCEを使用します。
Sqlインジェクション(SQLi)攻撃の防止
webアプリケーションにおけるよく知られているセキュリティ上の落とし穴は、SQLインジェクション攻撃です。 このような攻撃は、通常、攻撃されたデータベースのデータを盗む、データを変更する、またはアクセス許可を変更するために使用されます。
SQLインジェクション攻撃の防止は開発中に開始され、アプリケーションを保護するためにスキャナとwebアプリケーションファイアウォールを使用する必 このような攻撃を防ぐための例と方法については、SQLインジェクションのブログ記事を参照してくださ
SQLをラップアップするのは素晴らしいことです。 私は個人的に他のクエリ言語やAPIよりもSQLベースの言語を好みます。 ただし、作業を容易にするためには、正しく使用する必要があります。 スキーマを正しく構築することから始め、後でWITHキーワードやwindow関数のようなコーディング規則やその他のオプションを使用します。
知られている落とし穴を念頭に置いておくことを忘れないでください。 また、アプリケーションを作成している場合は、SQLインジェクションから保護します。
SQL入門の記事: 例、ベストプラクティスと落とし穴appeared first on Blog.
***これは、オリナカーが作成したブログからセキュリティブロガーズネットワークシンジケートのブログです。 元の投稿を読む：https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/