エレガントな SQL ネイティブ ステートメントを作成する方法

前書き:

前回の記事では、Mysql の基本アーキテクチャについて説明しましたが、「SQL クエリ ステートメントはどのように実行されるのか」ということから始まりました。 MySQL アーキテクチャ?」 包括的な説明が行われました。 MySql アーキテクチャにおける SQL クエリ ステートメントの具体的な実行プロセスは理解していますが、SQL ステートメントをより適切かつ高速に記述するためには、SQL ステートメント内の各句の実行順序を知ることが非常に必要だと思います。前回の記事を読んだ方は、SQL ステートメントの最後の各句の実行はエグゼキューターで完了する必要があり、ストレージ エンジンはデータの読み取りおよび書き込みインターフェイスをエグゼキューターに提供することを知っているはずです。ここで、

ステートメント (シーケンス番号に従って実行)

1. from (注: これには、 from )

2. join

3. on

4. ##where
   のサブステートメント
5. group by (select でエイリアスの使用を開始すると、後続のステートメントで使用できます)

6. avg,sum.... およびその他の集計関数

7. having

8. select

9. distinct

10. order by

11. limit

各句の実行順序分析

すべてのクエリ ステートメントは from から実行されます。プロセス、各句 各ステップは次のステップの仮想テーブルを生成し、この仮想テーブルは次の実行ステップの入力として使用されます。

1. from

form はクエリ ステートメントの始まりです。

• これがテーブルの場合、このテーブルは直接操作されます。

• この from の後にサブクエリが続く場合、サブクエリは次のようになります。クエリの内容とサブクエリの結果が第１の仮想テーブルＴ１である。 (注: サブクエリの実行プロセスも、この記事で説明されている順序になります)。

• テーブルを関連付ける必要がある場合は、結合を使用します。2、3

2 を参照してください。続いて、複数のテーブルの結合結合により、最初に最初の 2 つのテーブルに対してデカルト積が実行され、その後、最初の仮想テーブル T1 が生成されます (注: ここでは、比較的小さなテーブルがベース テーブルとして選択されます);

3. on

仮想テーブル T1 に対して ON フィルタリングを実行すると、一致する行のみが仮想テーブル T2 に記録されます。 (ここで 3 番目のテーブルが関連付けられている場合は、T2 と 3 番目のテーブルのデカルト積を使用して T3 テーブルが生成されることに注意してください。引き続き 3. の手順を繰り返して T4 テーブルを生成しますが、次のシーケンスはここでの T3 と T4 については、テーブル関連付けクエリ T2)

4 から続行し、where

仮想テーブル T2 に対して WHERE 条件フィルタリングを実行します。一致するレコードのみが仮想テーブル T3 に挿入されます。

5.group by

group by 句は、一意の値をグループに結合して、仮想テーブル T4 を取得します。 group by が適用される場合、後続のすべてのステップは T4 列に対して操作するか、6. 集計関数 (count、sum、avg など) を実行することのみ可能です。 (注: その理由は、グループ化後の最終結果セットには各グループから 1 行しか含まれていないためです。そうしないと、ここで多くの問題が発生し、次のコードの誤解が具体的に言及されることを覚えておいてください。)

6. avg,sum .... 集計関数を待機しています

集計関数は、合計や統計量などの必要な集計値を取得するために、グループ化された結果に対して何らかの処理を実行するだけであり、仮想関数は生成しません。テーブル。

7.having

having フィルターを適用して T5 を生成します。 HAVING 句は主に GROUP BY 句と組み合わせて使用​​されます。HAVING フィルタは、グループ化されたデータに適用される最初で唯一のフィルタです。

8. select

選択操作を実行し、指定された列を選択して、仮想テーブル T6 に挿入します。

9.distinct

T6 のレコードの重複を除去します。同じ行を削除して仮想テーブル T7 を生成します。 (注: 実際には、group by 句が適用されている場合、distinct は冗長です。その理由は、グループ化するときに、列内の固有の値が 1 つのグループにグループ化されるためでもありますが、各グループに対してのみ 1 行のレコードが返されるため、すべてのレコードは異なります。)

10. order by

order by 句を適用します。 T7 を order_by_condition に従ってソートすると、仮想テーブルの代わりにカーソルが返されます。 SQL はセットの理論に基づいています。セットは行を事前に並べ替えません。セットは単なるメンバーの論理的なコレクションであり、メンバーの順序は関係ありません。テーブルを並べ替えるクエリは、特定の物理的順序で論理構成を含むオブジェクトを返すことができます。このオブジェクトはカーソルと呼ばれます。
oder by に関する注意事項

• order by の戻り値はカーソルであるため、order by 句を使用したクエリはテーブル式に適用できません。

• 並べ替えによる順序付けは非常にコストがかかります。並べ替える必要がない限り、order by、

• order by を指定しないことをお勧めします。2 つのパラメータasc (昇順) desc (降順)

11.limit

指定した行のレコードを取得し、仮想テーブル T9 を生成し、結果を返します。

limit の後のパラメーターは、limit m またはlimit m n にすることができます。これは、データの m 番目の部分から n 番目の部分までを意味します。

(注: 多くの開発者は、ページングの問題を解決するためにこのステートメントを使用することを好みます。データが小さい場合は、LIMIT 句を使用しても問題ありません。データの量が非常に大きい場合、LIMIT n、m を使用すると、 LIMIT機構により毎回先頭からスキャンが開始されるため、60万行目から3つのデータを読み込む必要がある場合、まず60万行目をスキャンして見つけてから読み込む必要があります。プロセス 非常に非効率なプロセスであるため、ビッグ データを処理する場合、アプリケーション層で特定のキャッシュ メカニズムを確立することが非常に必要です)

特定の要件に合わせて作成された SQL クエリを開発する

SELECT `userspk`.`avatar` AS `user_avatar`, 
`a`.`user_id`, 
`a`.`answer_record`, 
 MAX(`score`) AS `score`FROM (select * from pkrecord  order by score desc) as a 
INNER JOIN `userspk` AS `userspk` ON `a`.`user_id` = `userspk`.`user_id`WHERE `a`.`status` = 1 AND `a`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953' GROUP BY `user_id`ORDER BY `a`.`score` DESC LIMIT 9;

結果:

• クエリしたい内容について簡単に説明します:

想要查询pk记录表中分数最高的9个用户记录和他们的头像。

• 通过这段sql实际想一遍sql各字句的执行顺序

pk记录表的数据结构设计，每个用户每天每个馆下可能会有多条记录，所以需要进行分组，并且查询结果只想拿到每个分组内最高的那条记录。

这段sql的一些说明：

1. 可能有些同学会认为子查询没有必要 直接查询pk记录表就可以，但是并不能拿到预期的结果，因为分组后的每个组结果是不进行排序的，而且max拿到的最高分数肯定是对应的该分组下最高分数，但是其它记录可能就不是最高分数对应的那条记录。所以子查询非常有必要，它能够对原始的数据首先进行排序，分数最高的那条就是第一条对应的第一条记录。

看一下代码和执行结果与带有子查询的进行比较，就能理解我上面说的一段话：

//不使用子查询SELECT `userspk`.`avatar` AS `user_avatar`, 
`pkrecord`.`user_id`, 
`pkrecord`.`answer_record`, 
`pkrecord`.`id`, 
 MAX(`score`) AS `score`FROM pkrecordINNER JOIN `userspk` AS `userspk` ON `pkrecord`.`user_id` = `userspk`.`user_id`WHERE `pkrecord`.`status` = 1 AND `pkrecord`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953' GROUP BY `user_id`ORDER BY `pkrecord`.`score` DESC LIMIT 9;

查询结果

2. 在子查询中对数据已经进行排序后，外层排序方式如果和子查询排序分数相同，都是分数倒序，外层的排序可以去掉，没有必要写两遍。

sql语句中的别名

别名在哪些情况使用

在 SQL 语句中，可以为表名称及字段（列）名称指定别名

• 表名称指定别名

同时查询两张表的数据的时候： 未设置别名前：

SELECT article.title,article.content,user.username FROM article, userWHERE article.aid=1 AND article.uid=user.uid

设置别名后：

SELECT a.title,a.content,u.username FROM article AS a, user AS u where a.aid=1 and a.uid=u.uid

好处：使用表别名查询，可以使 SQL 变得简洁而更易书写和阅读，尤其在 SQL 比较复杂的情况下

• 查询字段指定别名

查询一张表，直接对查询字段设置别名

SELECT username AS name,email FROM user

查询两张表

好处：字段别名一个明显的效果是可以自定义查询数据返回的字段名；当两张表有相同的字段需要都被查询出，使用别名可以完美的进行区分，避免冲突

SELECT a.title AS atitle,u.username,u.title AS utitle FROM article AS a, user AS u where a.uid=u.uid

• 关联查询时候，关联表自身的时候，一些分类表，必须使用别名。

• 别名也可以在group         by与having的时候都可使用

• 别名可以在order by排序的时候被使用

  查看上面一段sql

• delete ， update MySQL都可以使用别名，别名在多表（级联）删除尤为有用

delete t1,t2 from t_a t1 , t_b t2 where t1.id = t2.id

• 子查询结果需要使用别名

  查看上面一段sql

别名使用注意事项

• 虽然定义字段别名的 AS 关键字可以省略，但是在使用别名时候，建议不要省略 AS 关键字

书写sql语句的注意事项

书写规范上的注意

• 字符串类型的要加单引号

• select后面的每个字段要用逗号分隔，但是最后连着from的字段不要加逗号

• 使用子查询创建临时表的时候要使用别名，否则会报错。

为了增强性能的注意

• 不要使用“select * from ……”返回所有列，只检索需要的列，可避免后续因表结构变化导致的不必要的程序修改，还可降低额外消耗的资源

• 不要检索已知的列

select  user_id,name from User where user_id = ‘10000050’

• 使用可参数化的搜索条件，如=, >, >=, , !=, !>, !

• 当需要验证是否有符合条件的记录时，使用exists，不要使用count(*)，前者在第一个匹配记录处返回，后者需要遍历所有匹配记录

• Where子句中列的顺序与需使用的索引顺序保持一致，不是所有数据库的优化器都能对此顺序进行优化，保持良好编程习惯（索引相关）

• 不要在where子句中对字段进行运算或函数（索引相关）

1. 如where  amount / 2 > 100，即使amount字段有索引，也无法使用，改成where amount > 100 * 2就可使用amount列上的索引

2. 如where substring( Lastname, 1, 1) = ‘F’就无法使用Lastname列上的索引，而where Lastname like ‘F%’或者where Lastname >= ‘F’ and Lastname

• 在有min、max、distinct、order by、group by操作的列上建索引，避免额外的排序开销（索引相关）

• 小心使用or操作，and操作中任何一个子句可使用索引都会提高查询性能，但是or条件中任何一个不能使用索引，都将导致查询性能下降，如where member_no = 1 or provider_no = 1，在member_no或provider_no任何一个字段上没有索引，都将导致表扫描或聚簇索引扫描（索引相关）

• Between一般比in/or高效得多，如果能在between和in/or条件中选择，那么始终选择between条件，并用>=和=和

• 結合操作の順序を調整してパフォーマンスを最適化します。結合操作はトップダウンです。パフォーマンスを向上させるには、結果セットが小さい 2 つのテーブルの関連付けを前に置くようにしてください。 (結合関連) 注: インデックス作成と関連付けについては 2 つの記事で詳しく説明する予定ですが、このメモでは簡単に説明するだけに留めます。

MySQL 関連の技術記事の詳細については、MySQL チュートリアル 列にアクセスして学習してください。

以上がエレガントな SQL ネイティブ ステートメントを作成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。