第一篇:高效SQL语句(SQL Server)(写写帮推荐)
以下的SQL语句以Northwind为例
1、不要再where子句中“=”的左侧使用函数和表达式,因为系统无法应用函数或表达式中的索引
SELECT * FROM Customers WHERE Cast(CustomerID AS VARCHAR(20))='1'--Clustered Index Scan 全表扫描
SELECT * FROM Customers WHERE CustomerID ='1'--Clustered Index Seek 索引扫描
2、只返回必要的行或列
2.1 减少I/O次数
2.2 减少加载到内存的数据量
3、关于组合索引(待定)
组合索引中的顺序很重要,当查询语句中的列只能与组合索引中的第1列相匹配时,才能在查询中应用此索引
4、Distinct语句的使用原则
尽量少用,因为数据库引擎需要花费大量的时间对所有字段进行比较,过滤掉重复的记录,因此影响了查询的效率
字段较少时,可适当采用;较多时,不宜采用
5、Union语句
Union必须满足以下要求:
1、所有select语句的列数必须相同
2、所有select语句中对应列的数据类型必须兼容
执行包含Union的查询语句的过程如下:
1、依次执行所有select语句
2、将所有select语句的结果集合并为一个结果集
3、对结果集进行排序,并过滤掉重复的记录(由于需要第3步的操作,导致联合查询效率很低)可使用union all,不用排序和过滤重复记录,效率高
6、使用存储过程
数据库引擎可以在创建存储过程时对其进行分析和优化,并可在首次执行该过程后使用该过程的内存中版本。
相对来说,每次运行sql语句时,都要从客户端重复发送,并且在sqlserver每次执行这些语句时,对其进行编译和优化。
7、如果需要多次对一个数据量非常大的表中一部分数据进行查询操作,可以将这部分数据放在临时表中,然后对临时表进行操作。
8、模糊匹配符%和索引的关系
假定有一个表Good,主键为聚集索引,类型为nvarchar(50)
SELECT * FROM Goods AS g WHERE g.GoodsName LIKE '%商品1'--Clustered
Index Scan 全表扫描
SELECT * FROM Goods AS g WHERE g.GoodsName LIKE '商%品1'--Clustered Index Seek 索引扫描
SELECT * FROM Goods AS g WHERE g.GoodsName LIKE '商品1%'--Clustered Index Seek 索引扫描
结论:通配符%放在中间和后面会走索引,放在前面不会
9、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
SELECT * FROM Employees AS e WHERE e.EmployeeID =1--走聚集索引
SELECT * FROM Employees AS e WHERE e.EmployeeID IS NULL--Constanct Scan10、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
11、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON,在结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送
DONE_IN_PROC 消息。
第二篇:高效SQL语句
1.SELECT子句中避免使用 “*”
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 „*‟ 是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中, 会将“*” 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.2.使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.例如:
Sql代码
1.SELECT COUNT(*),SUM(SAL)FROM EMP WHERE DEPT_NO = 0020 ANDENAME LIKE „SMITH%‟;
2.SELECT COUNT(*),SUM(SAL)FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE „SMITH%‟;
SELECT COUNT(*),SUM(SAL)FROM EMP WHERE DEPT_NO = 0020 ANDENAME LIKE „SMITH%‟;
SELECT COUNT(*),SUM(SAL)FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE „SMITH%‟;
你可以用DECODE函数高效地得到相同结果:
Sql代码
1.SELECT COUNT(DECODE(DEPT_NO,0020,‟X‟,NULL))D0020_COUNT,2.COUNT(DECODE(DEPT_NO,0030,‟X‟,NULL))D0030_COUNT,3.SUM(DECODE(DEPT_NO,0020,SAL,NULL))D0020_SAL,4.SUM(DECODE(DEPT_NO,0030,SAL,NULL))D0030_SAL5.FROM EMP WHERE ENAME LIKE „SMITH%‟;
SELECT COUNT(DECODE(DEPT_NO,0020,‟X‟,NULL))D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,‟X‟,NULL))D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL))D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL))D0030_SAL FROM EMP WHERE ENAME LIKE „SMITH%‟;
类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.3.删除重复记录
最高效的删除重复记录方法(因为使用了ROWID)
Sql代码
1.DELETE FROM EMP E WHERE E.ROWID >(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
DELETE FROM EMP E WHERE E.ROWID >(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
4.用TRUNCATE替代DELETE
当删除表中的记录时,在通常情况下,回滚段(rollback segments)用来存放可以被恢复的信息,如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况),而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.5.计算记录条数
和一般的观点相反, count(*)比count(1)稍快,当然如果可以通过索引检索,对索引列的计数仍旧是最快的.例如 COUNT(EMPNO)
6.用Where子句替换HAVING子句
避免使用HAVING子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤,这个处理需要排序、总计等操作,如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销, 例如:
Sql代码
1.--低效
2.SELECT REGION,AVG(LOG_SIZE)FROM LOCATION GROUP BY REGION HAVING REGION REGION!= „SYDNEY‟ AND REGION!= „PERTH‟3.--高效
4.SELECT REGION,AVG(LOG_SIZE)FROMLOCATION WHERE REGION REGION!= „SYDNEY‟ ND REGION!= „PERTH‟ GROUP BY REGION
--低效
SELECT REGION,AVG(LOG_SIZE)FROM LOCATION GROUP BY REGION HAVING REGION REGION!= „SYDNEY‟ AND REGION!= „PERTH‟--高效
SELECT REGION,AVG(LOG_SIZE)FROMLOCATION WHERE REGION REGION!= „SYDNEY‟ ND REGION!= „PERTH‟ GROUP BY REGION
7.用EXISTS替代IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.Sql代码1.--低效
2.SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = „MELB‟)
3.--高效:
4.SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS(SELECT „X‟FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = „MELB‟)
--低效
SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = „MELB‟)
--高效:
SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS(SELECT „X‟FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = „MELB‟)
8.用NOT EXISTS替代NOT IN
在子查询中,NOT IN子句将执行一个内部的排序和合并.无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOT IN,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.例如:
SELECT …FROM EMPWHERE DEPT_NO NOT IN(SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=‟A‟);
Sql代码
1.--为了提高效率改写为:(方法一: 高效)
SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+)= „A‟2.--(方法二: 最高效)
SELECT ….FROM EMP E WHERE NOT EXISTS(SELECT „X‟FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = „A‟);3.--为了提高效率改写为:(方法一: 高效)
SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+)= „A‟
4.--(方法二: 最高效)
SELECT ….FROM EMP E WHERE NOT EXISTS(SELECT „X‟FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = „A‟);
9.用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换 例如:
Sql代码1.--低效:
2.SELECT DISTINCT DEPT_NO,DEPT_NAMEFROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO3.--高效:
4.SELECT DEPT_NO,DEPT_NAMEFROM DEPT D WHERE EXISTS(SELECT „X‟ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
5.--EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.--低效:
SELECT DISTINCT DEPT_NO,DEPT_NAMEFROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO
--高效:
SELECT DEPT_NO,DEPT_NAMEFROM DEPT D WHERE EXISTS(SELECT „X‟ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
--EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.10.用索引提高效率
索引是表的一个概念部分,用来提高检索数据的效率,实际上ORACLE使用了一个复杂的自平衡B-tree结构,通常通过索引查询数据比全表扫描要快,当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引,同样在联结多个表时使用索引也可以提高效率,另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证,除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列.通常, 在大型表中使用索引特别有效.当然,你也会发现, 在扫描小表时,使用索引同样能提高效率,虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改,这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O,因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢注:定期的重构索引是有必要的.11.避免在索引列上使用计算
WHERE子句中,如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描.举例:Sql代码
1.--低效:
2.SELECT …FROM DEPT WHERE SAL * 12 > 25000;3.--高效:
4.SELECT … FROM DEPT WHERE SAL> 25000/12;--低效:
SELECT …FROM DEPT WHERE SAL * 12 > 25000;--高效:
SELECT … FROM DEPT WHERE SAL> 25000/12;
12.用>=替代>
Sql代码
1.--如果DEPTNO上有一个索引2.--高效:
SELECT *FROM EMPWHERE DEPTNO >=4
3.--低效:
SELECT *FROM EMPWHERE DEPTNO >3--如果DEPTNO上有一个索引 4.--高效:
SELECT *FROM EMPWHERE DEPTNO >=45.--低效:
SELECT *FROM EMPWHERE DEPTNO >3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
第三篇:高效的SQL语句
如何写高效率的SQL语句、Where子句中的连接顺序:
ORACLE采用自下而上的顺序解析WHERE子句。
根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
举例:
(低效)
select...from table1 t1 where t1.sal > 300 and t1.jobtype = '0001' and 20 <(select count(*)from table1 t2 where t2.pno = t1.tno;
(高效)
select...from table1 t1 where 20 <(select count(*)from table1 t2 where t2.pno = t1.tno and t1.sal > 300 and t1.jobtype = '0001';
2、Select子句中避免使用 “ * ”:
当你想在select子句中列出所有的column时,使用动态SQL列引用 ‘*' 是一个方便的方法。不幸的是,这是一个非常低效的方法。
实际上,ORACLE在解析的过程中,会将 '*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
3、减少访问数据库的次数:
当执行每条SQL语句时,ORACLE在内部执行了许多工作:
解析SQL语句、估算索引的利用率、绑定变量、读数据块等等。
由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量。
举例:
题目——我要查找编号为0001、0002学生的信息。
(低效)
select name,age,gender,address from t_student where id = '0001';
select name,age,gender,address from t_student where id = '0002';
(高效)
select a.name,a.age,a.gender,a.address,b.name,b.age,b.gender,b.address from t_student a,t_student b where a.id = '0001' and b.id = '0002';
4、使用Decode函数来减少处理时间:
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。
举例:
(低效)
select count(*), sum(banace)from table1 where dept_id = '0001' and name like 'anger%';select count(*), sum(banace)from table1 where dept_id = '0002' and name like 'anger%';(高效)
select count(decode(dept_id,'0001','XYZ',null))count_01,count(decode(dept_id,'0002','XYZ',null))
count_02,sum(decode(dept_id,'0001',dept_id,null))sum_01,sum(decode(dept_id,'0002',dept_id,null))sum_02
from table1
where name like 'anger%';
5、整合简单,无关联的数据库访问:
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)
举例:
(低效)
select name from table1 where id = '0001';
select name from table2 where id = '0001';
select name from table3 where id = '0001';
(高效)
select t1.name, t2.name, t3.name
from table1 t1, table2 t2, table3 t3
where t1.id(+)= '0001' and t2.id(+)= '0001' and t3.id(+)= '0001'
【注:上面例子虽然高效,但是可读性差,需要量情而定啊!】
6、删除重复记录:
最高效的删除重复记录方法(因为使用了ROWID)
举例:
delete from table1 t1
where t1.rowid >(select min(t2.rowid)from table1 t2 where t1.id = t2.id);
7、尽量不要使用having子句,可以考虑用where替换。
having只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作。如果能通过where子句限制记录的数目,那就能减少这方面的开销。
8、尽量用表的别名:
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
9、用exists替代in(发现好多程序员不知道这个怎么用):
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下,使用exists(或not exists)通常将提高查询的效率。
举例:
(低效)
select...from table1 t1 where t1.id > 10 and pno in(select no from table2 where name like 'www%');
(高效)
select...from table1 t1 where t1.id > 10 and exists(select 1 from table2 t2 where t1.pno = t2.no and name like 'www%');
10、用not exists替代not in:
在子查询中,not in子句将执行一个内部的排序和合并。
无论在哪种情况下,not in都是最低效的(因为它对子查询中的表执行了一个全表遍历)。为了避免使用not in,我们可以把它改写成外连接(Outer Joins)或not exists。
11、用exists替换distinct:
当提交一个包含一对多表信息的查询时,避免在select子句中使用distinct.一般可以考虑用exists替换
举例:
(低效)
select distinct d.dept_no, d.dept_name from t_dept d, t_emp e where d.dept_no = e.dept_no;(高效)
select d.dept_no, d.dept_name from t_dept d where exists(select 1 from t_emp where d.dept_no = e.dept_no);
exists使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.12、用表连接替换exists:
通常来说,采用表连接的方式比exists更有效率。
举例:
(低效)
select ename from emp e where exists(select 1 from dept where dept_no = e.dept_no and dept_cat = 'W');
SELECT ENAME
(高效)
select ename from dept d, emp e where e.dept_no = d.dept_no and dept_cat = 'W';
13、避免在索引列上使用is null和is not null
避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引。
对于单列索引,如果列包含空值,索引中将不存在此记录;
对于复合索引,如果每个列都为空,索引中同样不存在此记录;
如果至少有一个列不为空,则记录存在于索引中。
举例:
如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null),ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入),然而如果所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空。
因此你可以插入1000 条具有相同键值的记录,当然它们都是空!
因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。
14、最好把复杂的sql,去看下它的执行计划,这样有利于你分析知道自己的sql效率如何。
第四篇:sql语句
简单基本的sql语句 几个简单的基本的sql语句
选择:select * from table1 where范围
插入:insert into table1(field1,field2)values(value1,value2)
删除:delete from table1 where范围
更新:update table1 set field1=value1 where范围
查找:select * from table1 where field1 like ’%value1%’
(1)数据记录筛选:
sql=“select * from 数据表 where 字段名=字段值 order by 字段名 [desc]”
sql=“select * from 数据表 where 字段名 like '%字段值%' order by 字段名 [desc]”sql=“select top 10 * from 数据表 where 字段名=字段值 order by 字段名 [desc]”sql=“select top 10 * from 数据表 order by 字段名 [desc]”
sql=“select * from 数据表 where 字段名 in('值1','值2','值3')”
sql=“select * from 数据表 where 字段名 between 值1 and 值2”
(2)更新数据记录:
sql=“update 数据表 set 字段名=字段值 where 条件表达式”
sql=“update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式”
(3)添加数据记录:
sql=“insert into 数据表(字段1,字段2,字段3 …)values(值1,值2,值3 …)”
sql=“insert into 目标数据表 select * from 源数据表”(把源数据表的记录添加到目标数据表)
(4)数据记录统计函数:
AVG(字段名)得出一个表格栏平均值
COUNT(*;字段名)对数据行数的统计或对某一栏有值的数据行数统计MAX(字段名)取得一个表格栏最大的值
MIN(字段名)取得一个表格栏最小的值
SUM(字段名)把数据栏的值相加
引用以上函数的方法:
sql=“select sum(字段名)as 别名 from 数据表 where 条件表达式”
set rs=conn.excute(sql)
用 rs(“别名”)获取统计的值,其它函数运用同上。
查询去除重复值:select distinct * from table1between的用法
between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
in 的使用方法
select * from table1 where a [not] in(‘值1’,’值2’,’值4’,’值6’)
第五篇:SQL语句
SQL语句,用友的SQL2000,通过查询管理器写的语句
1、查询
2、修改
3、删除
4、插入
表名:users 包含字段:id,sname,sage
查询 select * from users查询users表中所有数据
select id,sname,sage from users查询users表中id,sname,sage字段所有数据
可以限定查询条件比如:
select sname from users where sage>20查询年龄大于20岁的所有人的名字
修改 update users set sname='李四',sage=22将users表中数据都改为姓名李四,年龄22
update users set sname='李四',sage=22 where id=1限定id为1的人的信息修改为
姓名李四,年龄22
可以加where条件。
删除 delete from users where id=2删除users表中id为2的一行数据delete from users 代表删除users中所有数据
插入 insert into users(id,sname,sage)values(5,'刘三',21)插入一条数据
SQL四条最基本的数据操作语句:Insert,Select,Update和Delete。
例如:SELECT columns FROM tables;其中columns为列的名称,tables为表格名称
1、查询:select 字段名 from 表名 where 条件
查找在表(如A)中符合条件的字段
2、修改:update 表名 set 字段名=‘所要修改的内容’
修改在表(如A)中的字段的值为:所要修改的内容
3、删除: delete from 表名 where 条件
删除符合条件的表(如A)中的信息
4、插入: insert into 表名(字段名)(‘插入内容’)
在表(如A)中插入字段为:插入内容 的一条信息