Maison >base de données >tutoriel mysql >Introduction détaillée à quelques bonnes habitudes de programmation SQL
Introduction détaillée à quelques bonnes habitudes de programmation SQL
- 黄舟original
- 2017-03-06 13:14:511033parcourir
La plupart d'entre nous qui sommes engagés dans le développement de logiciels ne peuvent pas se passer de bases de données, en particulier les développeurs ERP, qui traitent plus fréquemment les bases de données. Les procédures stockées peuvent facilement contenir des milliers de lignes si la quantité de données est importante et si le chiffre d'affaires est important. le personnel est important, pouvons-nous donc toujours garantir que le système fonctionnera sans problème dans la prochaine période ? Pouvons-nous toujours garantir que la personne suivante pourra comprendre notre procédure stockée ? Ensuite, je le partagerai avec vous sur la base de la formation habituelle de l'entreprise et de votre expérience professionnelle personnelle. J'espère qu'il sera utile à tout le monde.
Pour connaître l'instruction SQL, je pense que nous devons savoir comment l'analyseur de requêtes sqlserver exécute notre instruction SQL. Beaucoup d'entre nous examineront le plan d'exécution ou utiliseront des profils pour surveiller et ajuster les instructions de requête ou les procédures stockées. . La raison de la lenteur, mais si nous connaissons la séquence logique d'exécution de l'analyseur de requêtes, nous serons confiants au démarrage. Alors, sommes-nous confiants au démarrage ?
Voici quelques bonnes habitudes en programmation SQL auxquelles je pense personnellement :
Ordre d'exécution logique des requêtes
-
Ordre d'exécution
Renvoyer uniquement les données requises
Faites le moins de travail répétitif possible
Faites attention à tables temporaires et variables de table Utilisation de
Utilisation de la sous-requête
Essayez d'utiliser Index
Jointures et index de tables multiples
-
Autres
1 : Ordre logique d'exécution de la requête
(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>L'ordre d'analyse SQL standard est :
(1).FROM 子句 组装来自不同数据源的数据 (2).WHERE 子句 基于指定的条件对记录进行筛选 (3).GROUP BY 子句 将数据划分为多个分组 (4).使用聚合函数进行计算 (5).使用HAVING子句筛选分组 (6).计算所有的表达式 (7).使用ORDER BY对结果集进行排序
Deuxième ordre d'exécution :
1. FROM : Effectuer un produit cartésien sur les deux premières tables de la clause FROM pour générer la table virtuelle vt1
2.ON : Appliquer le filtre ON à la table vt1 pour n'avoir que les lignes qui satisfont 19a074dabc6f8943ba4329528fff9778 sont insérées dans vt2
3.OUTER(join):Si OUTER JOIN est spécifié, les lignes introuvables dans la table préservée seront ajoutées à vt2 comme lignes externes pour générer t3 if from S'il contient plus de deux tables, répétez les étapes et étapes pour la table résultat générée par la connexion précédente et la table suivante et terminez directement
4.OÙ : Pour vt3, le filtre WHERE ne doit être utilisé que 491f0a4d2d93007009e4e19a488e671b Seules les lignes vraies sont insérées dans vt4
5.GROUP BY : Regroupez les lignes dans vt4 en fonction la liste des colonnes dans la clause GROUP BY pour générer vt5
6.CUBE|ROLLUP : Insérez des supergroupes dans vt6 pour générer vt6
7.HAVING : Appliquer le filtre HAVING à vt6 uniquement en utilisant 6b22e475466d4aedebb851ca9a27012d est uniquement inséré dans le groupe où vt7 est vrai. La suppression de vt8 produit vt9
10.ORDER BY : Trier les lignes de vt9 par la liste des colonnes dans la clause order by pour générer un curseur vc10
11.TOP : Sélectionnez un nombre ou une proportion spécifié de lignes à partir du début de vc10 pour générer vt11 et renvoyez-le à l'appelant
Voyant cela, la syntaxe utilisée dans linqtosql est quelque peu similaire ? Si nous comprenons l'ordre d'exécution de sqlserver, nous développerons davantage de bonnes habitudes SQL quotidiennes, c'est-à-dire que nous devrions prendre en compte les performances lors de la mise en œuvre des fonctions. La base de données est un outil qui peut effectuer des opérations définies, et nous devons utiliser pleinement cet outil. , ce qu'on appelle l'opération set est en fait une opération par lots, ce qui signifie minimiser le fonctionnement en boucle d'un grand volume de données sur le client et utiliser à la place des instructions SQL ou des procédures stockées.
3. Renvoyez uniquement les données requisesLe renvoi des données au client nécessite au moins l'extraction de la base de données, la transmission réseau des données, la réception des données par le client et le traitement par le client des données. lien de données, etc., si des données inutiles sont renvoyées, cela augmentera le travail inefficace du serveur, du réseau et du client. Le préjudice est évident Pour éviter de tels incidents, vous devez faire attention :
A. Vue horizontale
(1) N'écrivez pas d'instruction SELECT *, mais sélectionnez les champs dont vous avez besoin.
(2) Lorsque vous connectez plusieurs tables dans une instruction SQL, veuillez utiliser l'alias de la table et préfixer l'alias à chaque colonne. De cette façon, vous pouvez réduire le temps d'analyse et le nombre de colonnes provoquées. par des erreurs de syntaxe de colonne causées par une ambiguïté. S'il y a des tables table1 (ID, col1) et table2 (ID, col2)B. Vue verticale :
( 1) Écrivez la clause WHERE de manière raisonnable et n'écrivez pas d'instructions SQL sans WHERE.
(2) SELECT TOP N * -- Utilisez ceci à la place s'il n'y a pas de condition WHERE
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 …
Quatre : faites le moins de travail répétitif possible
A. Contrôler plusieurs exécutions de la même instruction, en particulier plusieurs exécutions de certaines données de base, est une chose à laquelle de nombreux programmeurs prêtent rarement attention. B. Réduire le nombre de conversions de données. Le besoin de conversion de données peut être un problème de conception, mais réduire le nombre de fois est quelque chose que les programmeurs peuvent faire. C. Éliminez les sous-requêtes et les tables de jointure inutiles. Les sous-requêtes sont généralement interprétées comme des jointures externes dans les plans d'exécution.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日期字段〉='2010-07-15' AND 日期字段<'2010-07-16'
ISNULL转换的例子：
WHERE ISNULL(字段,'')<>''应改为:WHERE字段<>'' WHERE ISNULL(字段,'')=''不应修改 WHERE ISNULL(字段,'F') ='T'应改为: WHERE字段='T' WHERE ISNULL(字段,'F')<>'T'不应修改
(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 日期>='2010-06-30' AND 日期 <'2010-07-01'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')>0
应改为:
WHERE 日期 <'2010-06-30'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')>=0
应改为:
WHERE 日期 <'2010-07-01'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')<0
应改为:
WHERE 日期>='2010-07-01'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')<=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）！