SQL 프로그래밍의 좋은 습관에 대한 자세한 소개
- 黄舟원래의
- 2017-03-06 13:14:511008검색
소프트웨어 개발에 종사하는 대부분의 사람들은 데이터베이스를 다루지 않고는 할 수 없습니다. 특히 데이터베이스를 더 자주 다루는 ERP 개발자는 데이터 양이 많고 회전율이 높을 경우 쉽게 수천 개의 행을 가질 수 있습니다. 인력이 많은데, 다음 기간에도 시스템이 원활하게 운영될 것이라고 보장할 수 있습니까? 다음 사람이 저장 프로시저를 이해할 수 있다고 확신할 수 있습니까? 그럼 회사의 평소 교육과 개인 업무 경험을 바탕으로 여러분께 공유해드리도록 하겠습니다.
SQL 문을 알려면 sqlserver 쿼리 분석기가 sql 문을 어떻게 실행하는지 알아야 할 것 같습니다. 우리 중 많은 사람들이 실행 계획을 살펴보거나 프로필을 사용하여 쿼리 문이나 저장 프로시저를 모니터링하고 조정할 것입니다. .느린 이유는 쿼리 분석기의 실행 로직 순서를 알면 시작할 때 자신감이 생기기 때문입니다.
개인적으로 생각하는 SQL 프로그래밍의 좋은 습관은 다음과 같습니다.
쿼리의 논리적 실행 순서
-
실행순서
필요한 데이터만 반환
반복적인 작업을 최대한 적게 하세요
주의하세요 임시 테이블 및 테이블 변수
서브 쿼리 사용법
Index를 사용해 보세요
다중 테이블 조인 및 인덱스
-
기타
1: 쿼리의 논리적 실행 순서
(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>표준 SQL 구문 분석 순서는 다음과 같습니다:
(1).FROM 子句 组装来自不同数据源的数据 (2).WHERE 子句 基于指定的条件对记录进行筛选 (3).GROUP BY 子句 将数据划分为多个分组 (4).使用聚合函数进行计算 (5).使用HAVING子句筛选分组 (6).计算所有的表达式 (7).使用ORDER BY对结果集进行排序
두 번째 실행 순서:
1.FROM: FROM 절에 대해 처음 두 테이블에 데카르트 곱을 수행하여 가상 테이블 vt1
2.ON: vt1 테이블에 ON 필터를 적용하고 19a074dabc6f8943ba4329528fff9778을 충족하는 행만 true입니다. vt2에 삽입됩니다.
3.OUTER(join):OUTER JOIN이 지정되면 보존된 테이블에 없는 행이 외부 행으로 vt2에 추가됩니다. 두 개 이상의 테이블을 포함합니다. 그런 다음 테이블은 이전 연결과 다음 테이블에 의해 생성된 결과 테이블에 대한 단계와 단계를 반복하고 직접 끝납니다.
4.WHERE: vt3의 경우 WHERE 필터 53eeedc3ad968e8149ac7a216219f687만 사용해야 합니다. true인 행만 vt4에 삽입됩니다
5.GROUP BY: GROUP BY 절의 열 목록에 따라 vt4의 행을 그룹화합니다. vt5
6.CUBE|ROLLUP: vt6을 생성하려면 vt6에 슈퍼그룹을 삽입하세요
7.HAVING: <인 경우에만 HAVING 필터를 vt6에 적용합니다. ;having_condition>이 true입니다. 그룹이 vt7에 삽입됩니다
8.SELECT: 선택 목록을 처리하여 vt8을 생성합니다
9.DISTINCT: vt8에서 중복 행 제거 vt9
10.ORDER BY: vt9의 행을 order by 절의 열 목록으로 정렬하여 커서 vc10
11.TOP: vt11을 생성하기 위해 vc10 시작 부분부터 지정된 수 또는 행의 비율을 선택하여 호출자에게 반환합니다
이렇게 보니 linqtosql 구문이 좀 비슷하죠? sqlserver의 실행 순서를 이해하면 일상적인 SQL 습관을 더욱 발전시킬 수 있습니다. 즉, 기능을 구현할 때 성능을 고려해야 합니다. 데이터베이스는 집합 작업을 수행할 수 있는 도구이므로 이 도구를 최대한 활용해야 합니다. . 소위 설정 작업은 실제로 일괄 작업으로, 클라이언트에서 대용량 데이터의 루프 작업을 최소화하고 대신 SQL 문이나 저장 프로시저를 사용하는 것을 의미합니다.3. 필요한 데이터만 반환
클라이언트에 데이터를 반환하려면 최소한 데이터베이스 추출, 데이터의 네트워크 전송, 클라이언트의 데이터 수신 및 클라이언트 처리가 필요합니다. 데이터 등 링크에 불필요한 데이터가 반환되면 서버, 네트워크 및 클라이언트의 비효율적인 작업이 증가하게 됩니다. 이러한 사고를 방지하려면 주의가 필요합니다.A. 가로 보기
(1) SELECT * 문을 작성하지 말고 필요한 필드를 선택하세요. (2) SQL 문에서 여러 테이블을 연결할 때 테이블의 별칭을 사용하고 각 열에 별칭을 붙이면 구문 분석 시간이 줄어들고 열 수를 줄일 수 있습니다. 모호함으로 인해 발생하는 구문 오류입니다. 테이블 table1(ID, col1)과 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. 세로 보기:
(1) WHERE 절을 적절하게 작성하고 WHERE 없이 SQL 문을 작성하지 마십시오. (2) SELECT TOP N * -- WHERE 조건이 없으면 대신 사용넷째: 반복 작업을 최대한 적게 한다
A. 동일한 명령문의 다중 실행, 특히 일부 기본 데이터의 다중 실행을 제어하는 것은 많은 프로그래머가 거의 주의를 기울이지 않는 것입니다. B. 데이터 변환 횟수를 줄이는 것은 설계 문제일 수 있지만 횟수를 줄이는 것은 프로그래머가 할 수 있는 일입니다. C. 불필요한 하위 쿼리를 제거하고 테이블을 조인합니다. 하위 쿼리는 일반적으로 실행 계획에서 외부 조인으로 해석됩니다.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）！