Detaillierte Einführung in einige gute Gewohnheiten der SQL-Programmierung

Die meisten von uns, die in der Softwareentwicklung tätig sind, kommen nicht ohne den Umgang mit Datenbanken aus, insbesondere ERP-Entwickler, die sich häufiger mit Datenbanken befassen. Wenn die Datenmenge groß ist und der Umsatz größer ist, können gespeicherte Prozeduren leicht zu Problemen führen Das Personal ist groß. Können wir also trotzdem garantieren, dass das System in der nächsten Zeit reibungslos läuft? Können wir trotzdem garantieren, dass die nächste Person unsere gespeicherte Prozedur verstehen kann? Dann werde ich es basierend auf der üblichen Ausbildung des Unternehmens und meiner persönlichen Arbeitserfahrung mit Ihnen teilen. Ich hoffe, dass es für alle hilfreich ist.

Um die SQL-Anweisung zu kennen, müssen wir meiner Meinung nach wissen, wie der SQL Server-Abfrageanalysator unsere SQL-Anweisung ausführt. Viele von uns werden sich den Ausführungsplan ansehen oder Profile verwenden, um Abfrageanweisungen oder gespeicherte Prozeduren zu überwachen und zu optimieren . Der Grund für die Langsamkeit, aber wenn wir die Ausführungslogiksequenz des Abfrageanalysators kennen, sind wir zuversichtlich, wenn wir beginnen.

Das Folgende sind einige meiner persönlichen guten Gewohnheiten in der SQL-Programmierung:

1. Logische Ausführungsreihenfolge von Abfragen

2. Ausführungsauftrag

3. Nur ​​die erforderlichen Daten zurückgeben

4. Machen Sie so wenig sich wiederholende Arbeiten wie möglich

5. Achten Sie darauf temporäre Tabellen und Tabellenvariablen Verwendung von

6. Verwendung der Unterabfrage

7. Versuchen Sie, Index zu verwenden

8. Mehrere Tabellenverknüpfungen und Indizes

9. Andere

1: Logische Ausführungsreihenfolge der Abfrage

(1) FROM < left_table> 
 
(3) < join_type>  JOIN < right_table>   (2) ON < join_condition> 
 
(4) WHERE < where_condition> 
 
(5) GROUP BY < group_by_list> 
 
(6) WITH {cube | rollup}
 
(7) HAVING < having_condition> 
 
(8) SELECT  (9) DISTINCT (11) < top_specification>  < select_list> 
 
(10) ORDER BY < order_by_list>

Die Standard-SQL-Parsing-Reihenfolge ist:

(1).FROM 子句 组装来自不同数据源的数据
 
(2).WHERE 子句 基于指定的条件对记录进行筛选
 
(3).GROUP BY 子句 将数据划分为多个分组
 
(4).使用聚合函数进行计算
 
(5).使用HAVING子句筛选分组
 
(6).计算所有的表达式
 
(7).使用ORDER BY对结果集进行排序

Zweite Ausführungsreihenfolge:

1. FROM: Führen Sie ein kartesisches Produkt für die ersten beiden Tabellen in der FROM-Klausel durch, um die virtuelle Tabelle vt1 zu generieren.

2.ON: Wenden Sie den ON-Filter nur auf die Tabelle vt1 an Zeilen, die erfüllen, werden in vt2 eingefügt

3.OUTER(join):Wenn OUTER JOIN angegeben ist, werden Zeilen, die nicht in der beibehaltenen Tabelle gefunden werden, zu vt2 hinzugefügt als externe Zeilen zum Generieren von t3, wenn aus mehr als zwei Tabellen, wiederholen Sie die Schritte und Schritte für die durch die vorherige Verbindung generierte Ergebnistabelle und die nächste Tabelle und beenden Sie direkt

4.WO: Für VT3 sollte der WHERE-Filter nur verwendet werden. < where_condition> Nur Zeilen, die wahr sind, werden in VT4 eingefügt die Spaltenliste in der GROUP BY-Klausel, um vt5 zu generieren

6.CUBE|ROLLUP: Supergruppen in vt6 einfügen, um vt6 zu generieren

7.HAVING: Wenden Sie den HAVING-Filter nur auf vt6 an, indem Sie < getting_condition> vt9 durch die Spaltenliste in der Order-by-Klausel, um einen Cursor vc10 zu generieren

11.TOP: Wählen Sie eine angegebene Anzahl oder einen bestimmten Anteil von Zeilen vom Anfang von vc10 aus, um vt11 zu generieren und zurückzugeben an den Aufrufer

Wenn Sie das sehen, ist die Syntax von linqtosql etwas ähnlich? Wenn wir die Ausführungsreihenfolge von SQL Server verstehen, werden wir gute tägliche SQL-Gewohnheiten weiterentwickeln, das heißt, wir sollten bei der Implementierung von Funktionen die Leistung berücksichtigen. Die Datenbank ist ein Tool, das festgelegte Operationen ausführen kann, und wir sollten dieses Tool vollständig nutzen .Die sogenannte Set-Operation ist eigentlich eine Batch-Operation, was bedeutet, dass die Schleifenoperation großer Datenmengen auf dem Client minimiert und stattdessen SQL-Anweisungen oder gespeicherte Prozeduren verwendet werden.

3. Geben Sie nur die erforderlichen Daten zurück

Die Rückgabe von Daten an den Client erfordert mindestens eine Datenbankextraktion, eine Netzwerkübertragung von Daten, einen Client-Empfang von Daten und eine Client-Verarbeitung Daten usw. Wenn unnötige Daten zurückgegeben werden, erhöht dies die ineffektive Arbeit des Servers, des Netzwerks und des Clients. Um solche Vorfälle zu vermeiden, müssen Sie darauf achten:

A. Horizontale Ansicht

(1) Schreiben Sie keine SELECT *-Anweisung, sondern wählen Sie die benötigten Felder aus. (2) Wenn Sie mehrere Tabellen in einer SQL-Anweisung verbinden, verwenden Sie bitte den Alias ​​der Tabelle und stellen Sie den Alias ​​jeder Spalte voran. Auf diese Weise können Sie die Analysezeit verkürzen und die Anzahl der Spalten reduzieren werden durch die Spalte Syntaxfehler verursacht.

Wenn es Tabellen table1 (ID, col1) und table2 (ID, col2) gibt

B. Vertikal:

( 1 ) Schreiben Sie die WHERE-Klausel sinnvoll und schreiben Sie keine SQL-Anweisungen ohne WHERE.

(2) SELECT TOP N * – Verwenden Sie dies stattdessen, wenn es keine WHERE-Bedingung gibt

Viertens: Machen Sie so wenig sich wiederholende Arbeit wie möglich

A. Viele Programmierer achten kaum darauf, mehrere Ausführungen derselben Anweisung zu steuern, insbesondere mehrere Ausführungen einiger grundlegender Daten.

B. Die Notwendigkeit der Datenkonvertierung kann ein Designproblem sein, aber die Anzahl der Konvertierungen zu reduzieren, ist etwas, das Programmierer tun können.

 Select A.ID, A.col1, B.col2
 
 -- Select A.ID, col1, col2 –不要这么写，不利于将来程序扩展
 
from table1 A inner join table2 B on A.ID=B.ID Where …

C. Eliminieren Sie unnötige Unterabfragen und Join-Tabellen. Unterabfragen werden in Ausführungsplänen im Allgemeinen als äußere Joins interpretiert.

D、合并对同一表同一条件的多次UPDATE，比如

UPDATE EMPLOYEE SET FNAME='HAIWER'
WHERE EMP_ID=' VPA30890F' UPDATE EMPLOYEE SET LNAME='YANG'
WHERE EMP_ID=' VPA30890F'

这两个语句应该合并成以下一个语句

UPDATE EMPLOYEE SET FNAME='HAIWER',LNAME='YANG'  WHERE EMP_ID=' VPA30890F'

E、UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。

五、注意临时表和表变量的用法

在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：

A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。

B、如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。

C、如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。

D、其他情况下，应该控制临时表和表变量的使用。

E、关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，

(1)主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。

(2)执行时间段与预计执行时间(多长)

F、关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，一般情况下，

SELECT INTO会比CREATE TABLE + INSERT INTO的方法快很多，

但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，

所以我的建议是，在并发系统中，尽量使用CREATE TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT INTO。

六、子查询的用法（1）

子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。

任何允许使用表达式的地方都可以使用子查询，子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，

往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。

相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。 关于相关子查询，应该注意：

(1)

A、NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。比如： SELECT PUB_NAME FROM PUBLISHERS WHERE PUB_ID NOT IN (SELECT PUB_ID FROM TITLES WHERE TYPE = 'BUSINESS') 可以改写成： SELECT A.PUB_NAME FROM PUBLISHERS A LEFT JOIN TITLES B ON B.TYPE = 'BUSINESS' AND A.PUB_ID=B. PUB_ID WHERE B.PUB_ID IS NULL

(2)

SELECT TITLE FROM TITLES 
WHERE NOT EXISTS 
 (SELECT TITLE_ID FROM SALES 
WHERE TITLE_ID = TITLES.TITLE_ID)

可以改写成：

SELECT TITLE 
FROM TITLES LEFT JOIN SALES 
ON SALES.TITLE_ID = TITLES.TITLE_ID 
WHERE SALES.TITLE_ID IS NULL

B、 如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。比如：

SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID IN
 (SELECT PUB_ID 
 FROM TITLES 
 WHERE TYPE = 'BUSINESS')

可以改写成：

SELECT A.PUB_NAME --SELECT DISTINCT A.PUB_NAME 
FROM PUBLISHERS A INNER JOIN TITLES B 
ON        B.TYPE = 'BUSINESS' AND
A.PUB_ID=B. PUB_ID

(3)

C、 IN的相关子查询用EXISTS代替，比如

SELECT PUB_NAME FROM PUBLISHERS 
WHERE PUB_ID IN
(SELECT PUB_ID FROM TITLES WHERE TYPE = 'BUSINESS')

可以用下面语句代替：

SELECT PUB_NAME FROM PUBLISHERS WHERE EXISTS 
(SELECT 1 FROM TITLES WHERE TYPE = 'BUSINESS' AND
PUB_ID= PUBLISHERS.PUB_ID)

D、不要用COUNT(*)的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS，比如有人写这样的语句：

SELECT JOB_DESC FROM JOBS 
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0

应该改成：

SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE  
ON EMPLOYEE.JOB_ID=JOBS.JOB_ID 
WHERE EMPLOYEE.EMP_ID IS NULL
  
SELECT JOB_DESC FROM JOBS 
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0

应该改成：

SELECT JOB_DESC FROM JOBS 
WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)

七：尽量使用索引

建立索引后，并不是每个查询都会使用索引，在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引，

索引的选择和使用方法是SQLSERVER的优化器自动作的选择，而它选择的根据是查询语句的条件以及相关表的统计信息，这就要求我们在写SQL

语句的时候尽量使得优化器可以使用索引。为了使得优化器能高效使用索引，写语句的时候应该注意：

（1）

A、不要对索引字段进行运算，而要想办法做变换，比如

SELECT ID FROM T WHERE NUM/2=100

应改为:

SELECT ID FROM T WHERE NUM=100*2
SELECT ID FROM T WHERE NUM/2=NUM1

如果NUM有索引应改为:

SELECT ID FROM T WHERE NUM=NUM1*2

如果NUM1有索引则不应该改。

(2)

发现过这样的语句：

SELECT 年,月,金额 FROM 结余表  WHERE 100*年+月=2010*100+10

应该改为：

SELECT 年,月,金额 FROM 结余表 WHERE 年=2010 AND月=10

B、 不要对索引字段进行格式转换

日期字段的例子：

WHERE CONVERT(VARCHAR(10), 日期字段,120)='2010-07-15'

应该改为

WHERE日期字段〉=&#39;2010-07-15&#39;   AND   日期字段<&#39;2010-07-16&#39;

ISNULL转换的例子：

WHERE ISNULL(字段,&#39;&#39;)<>&#39;&#39;应改为:WHERE字段<>&#39;&#39;
WHERE ISNULL(字段,&#39;&#39;)=&#39;&#39;不应修改
WHERE ISNULL(字段,&#39;F&#39;) =&#39;T&#39;应改为: WHERE字段=&#39;T&#39;
WHERE ISNULL(字段,&#39;F&#39;)<>&#39;T&#39;不应修改

(3)

C、 不要对索引字段使用函数

WHERE LEFT(NAME, 3)='ABC' 或者WHERE SUBSTRING(NAME,1, 3)='ABC'

应改为: WHERE NAME LIKE 'ABC%'

日期查询的例子：

WHERE DATEDIFF(DAY, 日期,'2010-06-30')=0

应改为:

WHERE 日期>=&#39;2010-06-30&#39; AND 日期 <&#39;2010-07-01&#39;

WHERE DATEDIFF(DAY, 日期,'2010-06-30')>0

应改为:

WHERE 日期 <&#39;2010-06-30&#39;

WHERE DATEDIFF(DAY, 日期,'2010-06-30')>=0

应改为:

WHERE 日期 <&#39;2010-07-01&#39;

WHERE DATEDIFF(DAY, 日期,&#39;2010-06-30&#39;)<0

应改为:

WHERE 日期>='2010-07-01'

WHERE DATEDIFF(DAY, 日期,&#39;2010-06-30&#39;)<=0

应改为:

WHERE 日期>='2010-06-30'

D、不要对索引字段进行多字段连接

比如：

WHERE FAME+ '. '+LNAME='HAIWEI.YANG'

应改为:

WHERE FNAME='HAIWEI' AND LNAME='YANG'

八. 多表连接条件与索引选择

A、多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。

B、连接条件尽量使用聚集索引

C、注意ON、WHERE和HAVING部分条件的区别

ON是最先执行， WHERE次之，HAVING最后，因为ON是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，WHERE也应该比 HAVING快点的，因为它过滤数据后才进行SUM，在两个表联接时才用ON的，所以在一个表的时候，就剩下WHERE跟HAVING比较了

考虑联接优先顺序：

(1)INNER JOIN
(2)LEFT JOIN (注：RIGHT JOIN 用 LEFT JOIN 替代)
(3)CROSS JOIN

九. 其它

A、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数

B、注意UNION和UNION ALL的区别。--允许重复数据用UNION ALL好

C、注意使用DISTINCT，在没有必要时不要用

D、TRUNCATE TABLE 与 DELETE 区别

E、减少访问数据库的次数

还有就是我们写存储过程，如果比较长的话，最后用标记符标开，因为这样可读性很好，即使语句写的不怎么样但是语句工整，C# 有region

sql我比较喜欢用的就是

--startof 查询在职人数

sql语句

--end of

正式机器上我们一般不能随便调试程序，但是很多时候程序在我们本机上没问题，但是进正式系统就有问题，但是我们又不能随便在正式机器上操作，那么怎么办呢？我们可以用回滚来调试我们的存储过程或者是sql语句，从而排错。

BEGIN TRAN
 UPDATE a SET 字段=''
ROLLBACK

作业存储过程我一般会加上下面这段，这样检查错误可以放在存储过程，如果执行错误回滚操作，但是如果程序里面已经有了事务回滚，那么存储过程就不要写事务了，这样会导致事务回滚嵌套降低执行效率，但是我们很多时候可以把检查放在存储过程里，这样有利于我们解读这个存储过程，和排错。

BEGIN TRANSACTION

--事务回滚开始

--检查报错

 IF ( @@ERROR > 0 )     
        BEGIN

--回滚操作

ROLLBACK TRANSACTION       
RAISERROR('删除工作报告错误', 16, 3)        
     RETURN         
      END

--结束事务

  COMMIT TRANSACTION

        以上就是详细介绍SQL编程的一些良好好习惯的内容，更多相关内容请关注PHP中文网（www.php.cn）！