Detailed introduction to some good habits of SQL programming
- 黄舟Original
- 2017-03-06 13:14:51956browse
Most of us who are engaged in software development cannot do without dealing with databases, especially ERP developers, who deal with databases more frequently. Stored procedures can easily have thousands of rows. If the amount of data is large and personnel turnover is large, So can we still guarantee that the system will run smoothly in the next period of time? Can we still ensure that the next person can understand our stored procedure? Then I will share it with you based on the company's usual training and personal work experience. I hope it will be helpful to everyone.
To know the sql statement, I think we need to know how the sqlserver query analyzer executes our sql statement. Many of us will look at the execution plan, or use profiles to monitor and tune query statements or stored procedures. The reason for the slowness, but if we know the execution logic sequence of the query analyzer, we will be confident when we start. So, are we confident when we start?
The following are some good habits of SQL programming that I personally think:
Logical execution sequence of queries
-
Execution sequence
##Return only the required data
Do as little repetitive work as possible
Pay attention to temporary tables and table variables Usage
Usage of subquery
Try to use Index
##Multiple table joins and indexes
Others
(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>
The standard SQL parsing order is: (1).FROM 子句 组装来自不同数据源的数据
(2).WHERE 子句 基于指定的条件对记录进行筛选
(3).GROUP BY 子句 将数据划分为多个分组
(4).使用聚合函数进行计算
(5).使用HAVING子句筛选分组
(6).计算所有的表达式
(7).使用ORDER BY对结果集进行排序
1.FROM:
For the FROM clause Perform Cartesian product on the first two tables to generate virtual table vt1
2.ON:Apply ON filter to vt1 table. Only rows that satisfy 19a074dabc6f8943ba4329528fff9778 is true will be inserted into vt2.
3.OUTER(join):If OUTER JOIN is specified, rows not found in the preserved table will be added to vt2 as external rows to generate t3 if from contains more than two The table then repeats the steps and steps for the result table generated by the previous connection and the next table and ends directly
4.WHERE:For vt3, the WHERE filter should only use 53eeedc3ad968e8149ac7a216219f687 Only rows that are true are inserted into vt4
5.GROUP BY:Group the rows in vt4 according to the column list in the GROUP BY clause to generate vt5
6.CUBE|ROLLUP：Insert supergroups into vt6 to generate vt6
7.HAVING：Apply HAVING filter to vt6 only if 5104a28b0bd9ed3aa070d5072f7dcf5e is true The group is inserted into vt7
8.SELECT:Processes the select list to generate vt8
9.DISTINCT:Remove duplicate rows from vt8 Generate vt9
10.ORDER BY:Sort the rows of vt9 by the column list in the order by clause to generate a cursor vc10
11.TOP:Select a specified number or proportion of rows from the beginning of vc10 to generate vt11 and return it to the caller Seeing this, the syntax of linqtosql is somewhat similar? If we understand the execution order of sqlserver, then we will further develop good daily sql habits, that is, we should consider performance while implementing functions. The database is a tool that can perform set operations, and we should make full use of this tool. , the so-called set operation is actually batch operation, which means to minimize the loop operation of large data volume on the client, and use SQL statements or stored procedures instead.
3. Only return the required dataReturning data to the client requires at least database extraction, network transmission of data, client reception of data, and client processing of data, etc. link, if unnecessary data is returned, it will increase the ineffective work of the server, network and client. The harm is obvious. To avoid such incidents, you need to pay attention:
A. Horizontal view(1) Do not write a SELECT * statement, but select the fields you need.
(2) When connecting multiple tables in a SQL statement, please use the alias of the table and prefix the alias to each Column. In this way, you can reduce the parsing time and reduce the number of columns caused by Column. Syntax errors caused by ambiguity.
If there are tables table1 (ID, col1) and table2 (ID, col2)
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 …B. View vertically:
(1) Write WHERE clauses appropriately and do not write SQL statements without WHERE.
(2) SELECT TOP N * --If there is no WHERE condition, use this instead
Four: Do as little repetitive work as possibleA. Controlling multiple executions of the same statement, especially multiple executions of some basic data, is something that many programmers rarely pay attention to.
B. Reduce the number of data conversions. The need for data conversion may be a design issue, but reducing the number of times is something that programmers can do.
C. Eliminate unnecessary subqueries and connection tables. Subqueries are generally interpreted as outer connections in the execution plan. Redundant connection tables bring additional overhead.
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）！