我这里不会介绍那么详细的操作语言,会在实际项目中运用到的注意点
几个简单的基本的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