So schreiben Sie elegante native SQL-Anweisungen

Vorwort:

Als der vorherige Artikel über die grundlegende Architektur von MySQL sprach, begann er mit „Wie werden SQL-Abfrageanweisungen in ausgeführt?“. MySQL-Architektur?" Es wurde eine ausführliche Erklärung gegeben. Ich kenne den spezifischen Ausführungsprozess von SQL-Abfrageanweisungen in der MySQL-Architektur, aber um SQL-Anweisungen besser und schneller schreiben zu können, halte ich es für sehr wichtig, die Ausführungsreihenfolge jeder Klausel in der SQL-Anweisung zu kennen. Freunde, die den vorherigen Artikel gelesen haben, sollten wissen, dass die Ausführung jeder Klausel am Ende der SQL-Anweisung im Executor abgeschlossen werden sollte und die Speicher-Engine dem Executor eine Schnittstelle zum Lesen und Schreiben von Daten bereitstellt. Beginnen wir nun mit der Untersuchung der vollständigen Ausführungssequenz jeder Klausel in der

-Anweisung (ausgeführt gemäß der Sequenznummer)

1. von (Hinweis: Dazu gehört auch die Unteranweisungen in from )

2. join

3. on

4. where

5. Gruppieren nach (beginnen Sie mit der Verwendung des Alias ​​in „select“, Sie können ihn in nachfolgenden Anweisungen verwenden)

6. Durchschnitt, Summe ... und andere Aggregatfunktionen

7. mit

8. auswählen

9. eindeutige

10. Reihenfolge durch

11. Limit

Analyse der Ausführungsreihenfolge jeder Klausel

Alle Abfrageanweisungen werden ab ausgeführt Prozess, jede Klausel Jeder Schritt generiert eine virtuelle Tabelle für den nächsten Schritt, und diese virtuelle Tabelle wird als Eingabe für den nächsten Ausführungsschritt verwendet.

1. from

form ist der Anfang einer Abfrageanweisung.

• Wenn es sich um eine Tabelle handelt, wird diese Tabelle direkt bedient.

• Wenn darauf eine Unterabfrage folgt, wird die Unterabfrage ausgeführt Zuerst ausgeführt Der Inhalt der Abfrage und das Ergebnis der Unterabfrage sind die erste virtuelle Tabelle T1. (Hinweis: Der Ausführungsprozess in der Unterabfrage erfolgt ebenfalls in der in diesem Artikel beschriebenen Reihenfolge.)

• Wenn Sie Tabellen verknüpfen müssen, verwenden Sie Join, siehe 2, 3

2 gefolgt von „Mehrere Tabellen, Verbindung herstellen“, führt zunächst ein kartesisches Produkt für die ersten beiden Tabellen durch, dann wird die erste virtuelle Tabelle T1 generiert (Hinweis: Hier wird eine relativ kleine Tabelle als Basistabelle ausgewählt).

3. on

Führen Sie eine ON-Filterung für die virtuelle Tabelle T1 durch und nur die übereinstimmenden Zeilen werden in der virtuellen Tabelle T2 aufgezeichnet. (Beachten Sie, dass, wenn hier eine dritte Tabelle zugeordnet ist, das kartesische Produkt von T2 und der dritten Tabelle zur Erstellung der T3-Tabelle verwendet wird. Wiederholen Sie Schritt 3 weiter, um die T4-Tabelle zu erstellen, jedoch mit der folgenden Reihenfolge Wird hier vorerst nicht erläutert. Fahren Sie einfach mit einer Tabellenzuordnungsabfrage T2 fort.

4 wobei

eine WHERE-Bedingungsfilterung für die virtuelle Tabelle T2 durchführt. Nur übereinstimmende Datensätze werden in die virtuelle Tabelle T3 eingefügt.

5.group by

Die Group by-Klausel kombiniert die eindeutigen Werte in einer Gruppe, um die virtuelle Tabelle T4 zu erhalten. Wenn „Gruppieren nach“ angewendet wird, können alle nachfolgenden Schritte nur T4-Spalten bearbeiten oder 6. Aggregationsfunktionen (Anzahl, Summe, Durchschnitt usw.) ausführen. (Hinweis: Der Grund dafür ist, dass die endgültige Ergebnismenge nach der Gruppierung nur eine Zeile aus jeder Gruppe enthält. Denken Sie daran, dass es hier sonst zu vielen Problemen kommt und die folgenden Code-Missverständnisse ausdrücklich erwähnt werden.)

6. avg,sum .... Aggregationsfunktionen wie

Die Aggregationsfunktion führt nur eine gewisse Verarbeitung der gruppierten Ergebnisse durch, um einige gewünschte Aggregatwerte wie Summierung, statistische Mengen usw. zu erhalten, und generiert keine virtueller Tisch.

7. Haben

Wenden Sie den Haben-Filter an, um T5 zu generieren. Die HAVING-Klausel wird hauptsächlich in Verbindung mit der GROUP BY-Klausel verwendet. Der Have-Filter ist der erste und einzige Filter, der auf gruppierte Daten angewendet wird.

8. Wählen Sie

Führen Sie den Auswahlvorgang aus, wählen Sie die angegebene Spalte aus und fügen Sie sie in die virtuelle Tabelle T6 ein.

9. Deutlich

Deduplizieren Sie die Datensätze in T6. Entfernen Sie dieselben Zeilen, um die virtuelle Tabelle T7 zu generieren. (Hinweis: Wenn die Group-by-Klausel angewendet wird, ist „distinct“ tatsächlich redundant. Der Grund dafür ist auch, dass beim Gruppieren die eindeutigen Werte in der Spalte in einer Gruppe gruppiert werden. und nur für jede Gruppe wird eine Zeile mit Datensätzen zurückgegeben, sodass alle Datensätze unterschiedlich sind)

Reihenfolge nach

Wenden Sie die Reihenfolge nach-Klausel an. Sortieren Sie T7 nach order_by_condition. Zu diesem Zeitpunkt wird ein Cursor anstelle einer virtuellen Tabelle zurückgegeben. SQL basiert auf der Mengentheorie. Eine Menge sortiert ihre Zeilen nicht vor. Es handelt sich lediglich um eine logische Sammlung von Mitgliedern, und die Reihenfolge der Mitglieder ist irrelevant. Eine Abfrage, die eine Tabelle sortiert, kann ein Objekt zurückgeben, das eine logische Organisation in einer bestimmten physischen Reihenfolge enthält. Dieses Objekt wird Cursor genannt.
Einige Hinweise zu or by

• Da der Rückgabewert von order by ein Cursor ist, kann die Abfrage mit der order by-Klausel nicht auf Tabellenausdrücke angewendet werden.

• Ordnung nach Sortierung ist sehr aufwendig. Sofern Sie nicht unbedingt sortieren müssen, ist es am besten, die Sortierung nach

• Ordnung nach zwei Parametern anzugeben (aufsteigende Reihenfolge) desc (absteigende Reihenfolge)

11 limit

entfernt die Datensätze der angegebenen Zeile, generiert die virtuelle Tabelle T9 und gibt das Ergebnis zurück.

Der Parameter nach dem Grenzwert kann ein Grenzwert m oder ein Grenzwert m n sein, was bedeutet, dass er vom m-ten bis zum n-ten Datenstück reicht.

(Hinweis: Viele Entwickler verwenden diese Anweisung gerne zur Lösung von Paging-Problemen. Bei kleinen Datenmengen ist die Verwendung der LIMIT-Klausel kein Problem. Wenn die Datenmenge sehr groß ist, ist die Verwendung von LIMIT n, m sehr hilfreich ineffizient. Da der LIMIT-Mechanismus jedes Mal von vorne beginnt, müssen Sie, wenn Sie 3 Daten ab der 600.000. Zeile lesen müssen, zuerst die 600.000. Zeile scannen und dann den Scanvorgang durchführen ist ein sehr ineffizienter Prozess. Daher ist es für die Verarbeitung großer Datenmengen unbedingt erforderlich, einen bestimmten Caching-Mechanismus auf der Anwendungsebene einzurichten.

Entwickeln Sie eine 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;

-Abfrage, die gemäß bestimmten Anforderungen geschrieben wird . Ergebnis:

• Lassen Sie mich kurz darüber sprechen, was ich abfragen möchte:

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

• Passen Sie die Reihenfolge der Join-Vorgänge an, um die Leistung zu optimieren. Der Join-Vorgang erfolgt von oben nach unten. Versuchen Sie, die Zuordnung zweier Tabellen mit kleineren Ergebnismengen in den Vordergrund zu stellen, um die Leistung zu verbessern. (beitrittsbezogen) Hinweis: Ich werde zwei separate Artikel herausnehmen, um die Indizierung und Zuordnung im Detail zu erläutern. Ich werde sie in diesem Hinweis nur kurz erwähnen.

Weitere technische Artikel zum Thema MySQL finden Sie in der Spalte MySQL-Tutorial.

Das obige ist der detaillierte Inhalt vonSo schreiben Sie elegante native SQL-Anweisungen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!