数据库入门到精通(编程)-004-数据库软件SQL常用语句注意点

我这里不会介绍那么详细的操作语言，会在实际项目中运用到的注意点

几个简单的基本的sql语句
选择：select * fromtable1 where 范围
插入：insert into table1(field1,field2)values(value1,value2)
删除：delete from table1 where 范围
更新：update table1set field1=value1 where 范围
查找：select * from table1 where field1 like’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order byfield1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue fromtable1
平均：select avg(field1) as avgvalue fromtable1
最大：select max(field1) as maxvalue fromtable1
最小：select min(field1) as minvalue fromtable1

1. 不论一个sql中涉及到多个表，每次都用两个表（结果集）操作，得到新的结果后，再和下一个表（结果集）操作。

2. 避免在select f1,(select f2 from tableB ).... from tableA 这样得到字段列。直接用tableA和tableB关联得到A.f1,B.f2就可以了。

3.避免隐含的类型转换
 如 
 select id from employee where emp_id='8'  （错）
 select id from employee where emp_id=8    （对）
 emp_id是整数型，用'8'会默认启动类型转换，增加查询的开销。

注释：而如果是字符型代码里面遇到注意字符加  '   '
 
4. 尽量减少使用正则表达式，尽量不使用通配符。

注释：正则表达式是C#里面的内容

5. 使用关键字代替函数
   如：
   select id from employee where UPPER(dept) like 'TECH_DB'  （错）
   select id from employee where SUBSTR(dept,1,4)='TECH'    （错）
   select id from employee where dept like 'TECH%'         （对）
 注释：这是编程语言与SQL的混淆，不等于使用<> 

6.不要在字段上用转换函数，尽量在常量上用
  如：
  select id from employee where to_char(create_date,'yyyy-mm-dd')='2012-10-31'  （错）
  select id from employee where create_date=to_date('2012-10-31','yyyy-mm-dd')   （对）
  
7.不使用联接做查询
 如：select id from employee where first_name || last_name like 'Jo%'  （错）
 
8. 尽量避免前后都用通配符
  如：
  select id from employee where dept like '%TECH%' (错）
  select id from employee where dept like 'TECH%' （对）

9. 判断条件顺序
  如：
  select id from employee where creat_date-30>to_date('2012-10-31','yyyy-mm-dd')   （错） 
    select id from employee where creat_date >to_date('2012-10-31','yyyy-mm-dd')+30   （对）
    
10. 尽量使用exists而非in
 当然这个也要根据记录的情况来定用exists还是用in, 通常的情况是用exists
 select id from employee where salary in (select salary from emp_level where....)   （错）    
 select id from employee where salary exists(select 'X' from emp_level where ....)   （对）
 
11. 使用not exists 而非not in
    和上面的类似
    
12. 减少查询表的记录数范围

13.正确使用索引
  索引可以提高速度，一般来说，选择度越高，索引的效率越高。

14. 索引类型
  唯一索引，对于查询用到的字段，尽可能使用唯一索引。
  还有一些其他类型，如位图索引，在性别字段，只有男女的字段上用。

15. 在经常进行连接，但是没有指定为外键的列上建立索引

16. 在频繁进行排序会分组的列上建立索引，如经常做group by 或 order by 操作的字段。

17. 在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不建立索引。如性别列上只有男，女两个不同的值，就没必要建立索引（或建立位图索引）。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。

18. 在值比较少的字段做order by时，翻页会出现记录紊乱问题，要带上id字段一起做order by.

19. 不要使用空字符串进行查询
    如：
    select id from employee where emp_name like '%%' （错）
    
20. 尽量对经常用作group by的关键字段做索引。

21. 正确使用表关联
    利用外连接替换效率十分低下的not in运算，大大提高运行速度。
    如：
    select a.id from employee a where a.emp_no not in (select emp_no from employee1 where job ='SALE')  （错）
    
22. 使用临时表    
   在必要的情况下，为减少读取次数，可以使用经过索引的临时表加快速度。
   如：
   select e.id from employee e ,dept d where e.dept_id=d.id and e.empno>1000 order by e.id   （错）
   
   select id,empno from employee into temp_empl where empno>1000 order by id
   select m.id from temp_emp1 m,dept d where m.empno=d.id      （对）

 

23
只返回需要的列，避免用select * from t1
24.
加过滤条件限制返回的行数: select name, dep_name from t1 where age>30
25.
避免笛卡尔乘积，select * from a,b
--使用参数化查询，where col1=?，减少编译时间
26.
避免对查询条件计算，where salary*2>xx 改为salary > xx/2

27.
尽量避免在索引列上使用not，!=和<>，索引只能告诉什么在表中，而不能告诉什么不在表中，
当数据库遇上以上几种符号时，将不再使用索引，使用全表扫描
28.
in/exist, not in/not exist
可以用exists代替in，可以提高查询的效率.其实也是分情况的：       
in与exists的使用取决于子查询集合大小，IN适合于外表大而内表小的情况；
EXISTS适合于外表小而内表大的情况(前提是内表字段有索引).
所以结论: 如果子查询得出的结果集记录较少，外层主查询中的表较大且又有索引时应该用in,
反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
举例，以下两个sql是高效的:
select * from big_tab where id in (select id from small_tab);     --内表small_tab是小表，而外表big_tab是大表且有索引
select * from small_tab a where exists(select 1 from  big_tab b where b.id = s.id); --内表big_tab是大表且有索引，而外表small_tab是小表

原因: in 是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
      一直以来认为exists比in效率高的说法是不准确的。

29.
无论任何情况: not exists > not in；
原因: 　　
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；
而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

30.
如果可能，尽量避免使用order by和distinct等排序操作

31.
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

32.
注意LIKE模糊查询的使用，避免%%。

--使用for read only或for fetch only

33.
避免数字类型转换, 避免数据类型不匹配


--减少数据库访问次数

34.
注意SQL的where条件书写顺序:
对于大部分数据库而言，sql语句的解析都是有顺序的
比如Oracle数据库采用自下而上的顺序解析where字句，所以那些可以滤过大量纪录的条件应该写在where字句的末尾，例如:
(DB2就不需要，因为DB2的优化器足够智能，能够根据实际情况来对sql进行优化来决定合理的执行顺序)
select * from table e
      where  25<(select count(*) 
                          from table 
                          where count=e.count);    
                   and  h>500
                   and d='001';
说明: d='001'能够过滤掉绝大多数数据，所以这样写更高效 

--避免使用HAVING字句

35.
尽量多使用commit

36.
有条件的使用union-all代替union提高效率

37.
用UNION替换OR (适用于索引列) ， 如:
select dep_name, emp_name from tab1 where age>34 or gdp<'C';
如果age和gdp都是索引列，那么可以用union更高效:
select dep_name, emp_name from tab1 where age>34 
union
select dep_name, emp_name from tab1 where gdp<'C';

38.
用>=替代> 

高效: 
SELECT * FROM  EMP  WHERE  DEPTNO >=4 
低效: 
SELECT * FROM EMP WHERE DEPTNO >3 

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

来自

https://www.cnblogs.com/roboot/p/5054364.html 
https://blog.csdn.net/liujinwei2005/article/details/79364591