Comment écrire des instructions SQL natives élégantes

Avant-propos :

Lorsque l'article précédent parlait de l'architecture de base de Mysql, il commençait par "Comment les instructions de requête SQL sont-elles exécutées dans le Architecture MySQL ?" Une explication complète a été donnée. Je connais le processus d'exécution spécifique des instructions de requête SQL dans l'architecture MySql, mais pour écrire des instructions SQL mieux et plus rapidement, je pense qu'il est très nécessaire de connaître l'ordre d'exécution de chaque clause dans l'instruction SQL. Les amis qui ont lu l'article précédent doivent savoir que l'exécution de chaque clause à la fin de l'instruction SQL doit être terminée dans l'exécuteur, et que le moteur de stockage fournit une interface de lecture et d'écriture de données à l'exécuteur. Commençons maintenant notre étude de la séquence d'exécution complète de chaque clause dans l'instruction

(exécutée selon le numéro de séquence)

1. from (Remarque : cela inclut également le sous-déclarations dans de )

2. rejoindre

3. sur

4. où

5. group by (commencez à utiliser l'alias dans select, vous pouvez l'utiliser dans les instructions suivantes)

6. avg, sum.... et autres fonctions d'agrégation

7. ayant

8. sélectionner

9. ordre distinct

10. by

11. limit

Analyse de l'ordre d'exécution de chaque clause

Toutes les instructions de requête sont exécutées à partir de Pendant l'exécution. processus, chaque clause Chaque étape générera une table virtuelle pour l'étape suivante, et cette table virtuelle sera utilisée comme entrée de l'étape d'exécution suivante.

1. from

form est le début d'une instruction de requête.

• S'il s'agit d'une table, cette table sera exploitée directement

• Si ce from est suivi d'une sous-requête, la sous-requête sera exécuté en premier Le contenu de la requête et le résultat de la sous-requête constituent la première table virtuelle T1. (Remarque : le processus d'exécution dans la sous-requête suit également l'ordre décrit dans cet article).

• Si vous devez associer des tables, utilisez join, veuillez voir 2, 3

2. est suivi de Plusieurs tables, jointure d'association, effectuera d'abord un produit cartésien sur les deux premières tables, puis la première table virtuelle T1 sera générée (remarque : une table relativement petite sera sélectionnée comme table de base ici

) ;

3. on

Effectuez le filtrage ON sur la table virtuelle T1, et seules les lignes correspondantes seront enregistrées dans la table virtuelle T2. (Notez que s'il y a une troisième table qui lui est associée ici, le produit cartésien de T2 et de la troisième table sera utilisé pour produire la table T3. Continuez à répéter l'étape 3. on pour générer la table T4, mais la séquence suivante ne sera pas expliqué pour le moment. Pour T3 et T4 ici, continuez simplement à partir d'une requête d'association de table T2)

4 où

effectuez un filtrage de condition WHERE sur la table virtuelle T2. Seuls les enregistrements correspondants seront insérés dans la table virtuelle T3.

5.group by

La clause group by combine les valeurs uniques dans un groupe pour obtenir la table virtuelle T4. Si le groupe par est appliqué, toutes les étapes suivantes ne peuvent fonctionner que sur les colonnes T4 ou exécuter 6. fonctions d'agrégation (compte, somme, moyenne, etc.). (Remarque : la raison est que le résultat final défini après le regroupement ne contient qu'une seule ligne de chaque groupe. N'oubliez pas que sinon il y aura de nombreux problèmes ici et les malentendus de code suivants seront spécifiquement mentionnés.)

6. avg,sum .... Fonctions d'agrégation telles que

La fonction d'agrégation effectue uniquement un certain traitement sur les résultats groupés pour obtenir certaines valeurs agrégées souhaitées, telles que la sommation, les quantités statistiques, etc., et ne génère pas de table virtuelle.

7. avoir

Appliquer le filtre avoir pour générer T5. La clause HAVING est principalement utilisée en conjonction avec la clause GROUP BY. Le filtre have est le premier et le seul filtre appliqué aux données groupées.

8. select

Effectuez l'opération de sélection, sélectionnez la colonne spécifiée et insérez-la dans la table virtuelle T6.

9. distinct

Dédupliquer les enregistrements dans T6. Supprimez les mêmes lignes pour générer la table virtuelle T7. (Remarque : en fait, si la clause group by est appliquée, distinct est redondant. La raison est aussi que lors du regroupement, les valeurs uniques de la colonne sont regroupées en un seul groupe, et seulement pour Chaque groupe renvoie une ligne d'enregistrements, donc tous les enregistrements seront différents)

10 order by

Appliquer la clause order by. Triez T7 selon order_by_condition. À ce stade, un curseur est renvoyé au lieu d'une table virtuelle. SQL est basé sur la théorie des ensembles. Un ensemble ne trie pas ses lignes à l'avance. Il s'agit simplement d'une collection logique de membres et l'ordre des membres n'a pas d'importance. Une requête qui trie une table peut renvoyer un objet contenant une organisation logique dans un ordre physique spécifique. Cet objet s'appelle un curseur.
Quelques notes sur oder by

• Étant donné que la valeur de retour de order by est un curseur, la requête utilisant la clause order by ne peut pas être appliquée aux expressions de table.

• La commande par tri est très coûteuse. À moins que vous ne deviez trier, il est préférable de ne pas spécifier la commande par

• la commande par deux paramètres asc. (ordre croissant) desc (ordre décroissant)

11. limit

sort les enregistrements de la ligne spécifiée, génère la table virtuelle T9 et renvoie le résultat.

Le paramètre après la limite peut être une limite m ou une limite m n, ce qui signifie de la mième pièce à la nième donnée.

(Remarque : de nombreux développeurs aiment utiliser cette instruction pour résoudre les problèmes de pagination. Pour les petites données, il n'y a aucun problème à utiliser la clause LIMIT. Lorsque la quantité de données est très importante, en utilisant LIMIT n, m est très inefficace. Étant donné que le mécanisme LIMIT commence l'analyse depuis le début à chaque fois, si vous devez lire 3 éléments de données à partir de la 600 000e ligne, vous devez d'abord analyser et localiser la 600 000e ligne, puis lire le processus d'analyse. C'est un processus très inefficace. Par conséquent, pour le traitement du Big Data, il est très nécessaire d'établir un certain mécanisme de mise en cache au niveau de la couche application)

Développer une requête 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;

écrite selon certains Exigences Résultat :

• Permettez-moi de parler brièvement de ce que je souhaite interroger :

想要查询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条件，并用>=和=和

• Ajustez l'ordre des opérations de jointure pour optimiser les performances. L'opération de jointure est descendante. Essayez de placer l'association de deux tables avec des ensembles de résultats plus petits au premier plan pour améliorer les performances. (rejoindre en relation) Remarque : je publierai deux articles distincts pour expliquer en détail l'indexation et l'association. Je ne les mentionnerai que brièvement dans cette note.

Pour plus d'articles techniques liés à MySQL, veuillez visiter la colonne Tutoriel MySQL pour apprendre !

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!