执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，如果该表进行了归档，当前只剩下5000条记录了，那查询优化器就会改变方案，采用 “全表扫描”方式。

1、 首先要搞明白什么叫执行计划？

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，如果该表进行了归档，当前只剩下5000条记录了，那查询优化器就会改变方案，采用 “全表扫描”方式。

可见，执行计划并不是固定的，它是“个性化的”。产生一个正确的“执行计划”有两点很重要：

(1) SQL语句是否清晰地告诉查询优化器它想干什么？

(2) 查询优化器得到的数据库统计信息是否是最新的、正确的？

2、 统一SQL语句的写法

对于以下两句SQL语句，程序员认为是相同的，数据库查询优化器认为是不同的。

select*from dual

select*From dual

其实就是大小写不同，查询分析器就认为是两句不同的SQL语句，必须进行两次解析。生成2个执行计划。所以作为程序员，应该保证相同的查询语句在任何地方都一致，多一个空格都不行！

3、 不要把SQL语句写得太复杂

我经常看到，从数据库中捕捉到的一条SQL语句打印出来有2张A4纸这么长。一般来说这么复杂的语句通常都是有问题的。我拿着这2页长的SQL语句去请教原作者，结果他说时间太长，他一时也看不懂了。可想而知，连原作者都有可能看糊涂的SQL语句，数据库也一样会看糊涂。

一般，将一个Select语句的结果作为子集，然后从该子集中再进行查询，这种一层嵌套语句还是比较常见的，但是根据经验，超过3层嵌套，查询优化器就很容易给出错误的执行计划。因为它被绕晕了。像这种类似人工智能的东西，终究比人的分辨力要差些，如果人都看晕了，我可以保证数据库也会晕的。

另外，执行计划是可以被重用的，越简单的SQL语句被重用的可能性越高。而复杂的SQL语句只要有一个字符发生变化就必须重新解析，然后再把这一大堆垃圾塞在内存里。可想而知，数据库的效率会何等低下。

4、 使用“临时表”暂存中间结果

简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

5、 OLTP系统SQL语句必须采用绑定变量

select*from orderheader where changetime >'2010-10-20 00:00:01'

select*from orderheader where changetime >'2010-09-22 00:00:01'

以上两句语句，查询优化器认为是不同的SQL语句，需要解析两次。如果采用绑定变量

select*from orderheader where changetime >@chgtime

@chgtime变量可以传入任何值，这样大量的类似查询可以重用该执行计划了，这可以大大降低数据库解析SQL语句的负担。一次解析，多次重用，是提高数据库效率的原则。

6、 绑定变量窥测

事物都存在两面性，绑定变量对大多数OLTP处理是适用的，但是也有例外。比如在where条件中的字段是“倾斜字段”的时候。

“倾斜字段”指该列中的绝大多数的值都是相同的，比如一张人口调查表，其中“民族”这列，90%以上都是汉族。那么如果一个SQL语句要查询30岁的汉族人口有多少，那“民族”这列必然要被放在where条件中。这个时候如果采用绑定变量@nation会存在很大问题。

试想如果@nation传入的第一个值是“汉族”，那整个执行计划必然会选择表扫描。然后，第二个值传入的是“布依族”，按理说“布依族”占的比例可能只有万分之一，应该采用索引查找。但是，由于重用了第一次解析的“汉族”的那个执行计划，那么第二次也将采用表扫描方式。这个问题就是著名的“绑定变量窥测”，建议对于“倾斜字段”不要采用绑定变量。

7、 只在必要的情况下才使用begin tran

SQL Server中一句SQL语句默认就是一个事务，在该语句执行完成后也是默认commit的。其实，这就是begin tran的一个最小化的形式，好比在每句语句开头隐含了一个begin tran，结束时隐含了一个commit。

有些情况下，我们需要显式声明begin tran，比如做“插、删、改”操作需要同时修改几个表，要求要么几个表都修改成功，要么都不成功。begin tran 可以起到这样的作用，它可以把若干SQL语句套在一起执行，最后再一起commit。好处是保证了数据的一致性，但任何事情都不是完美无缺的。Begin tran付出的代价是在提交之前，所有SQL语句锁住的资源都不能释放，直到commit掉。

可见，如果Begin tran套住的SQL语句太多，那数据库的性能就糟糕了。在该大事务提交之前，必然会阻塞别的语句，造成block很多。

Begin tran使用的原则是，在保证数据一致性的前提下，begin tran 套住的SQL语句越少越好！有些情况下可以采用触发器同步数据，不一定要用begin tran。

8、 一些SQL查询语句应加上nolock

在SQL语句中加nolock是提高SQL Server并发性能的重要手段，在oracle中并不需要这样做，因为oracle的结构更为合理，有undo表空间保存“数据前影”，该数据如果在修改中还未commit，那么你读到的是它修改之前的副本，该副本放在undo表空间中。这样，oracle的读、写可以做到互不影响，这也是oracle 广受称赞的地方。SQL Server 的读、写是会相互阻塞的，为了提高并发性能，对于一些查询，可以加上nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。使用 nolock有3条原则。

(1) 查询的结果用于“插、删、改”的不能加nolock ！

(2) 查询的表属于频繁发生页分裂的，慎用nolock ！

(3) 使用临时表一样可以保存“数据前影”，起到类似oracle的undo表空间的功能，

能采用临时表提高并发性能的，不要用nolock 。

9、 聚集索引没有建在表的顺序字段上，该表容易发生页分裂

比如订单表，有订单编号orderid，也有客户编号contactid，那么聚集索引应该加在哪个字段上呢？对于该表，订单编号是顺序添加的，如果在orderid上加聚集索引，新增的行都是添加在末尾，这样不容易经常产生页分裂。然而，由于大多数查询都是根据客户编号来查的，因此，将聚集索引加在contactid上才有意义。而contactid对于订单表而言，并非顺序字段。

比如“张三”的“contactid”是001，那么“张三”的订单信息必须都放在这张表的第一个数据页上，如果今天“张三”新下了一个订单，那该订单信息不能放在表的最后一页，而是第一页！如果第一页放满了呢？很抱歉，该表所有数据都要往后移动为这条记录腾地方。

SQL Server的索引和Oracle的索引是不同的，SQL Server的聚集索引实际上是对表按照聚集索引字段的顺序进行了排序，相当于oracle的索引组织表。SQL Server的聚集索引就是表本身的一种组织形式，所以它的效率是非常高的。也正因为此，插入一条记录，它的位置不是随便放的，而是要按照顺序放在该放的数据页，如果那个数据页没有空间了，就引起了页分裂。所以很显然，聚集索引没有建在表的顺序字段上，该表容易发生页分裂。

曾经碰到过一个情况，一位哥们的某张表重建索引后，插入的效率大幅下降了。估计情况大概是这样的。该表的聚集索引可能没有建在表的顺序字段上，该表经常被归档，所以该表的数据是以一种稀疏状态存在的。比如张三下过20张订单，而最近3个月的订单只有5张，归档策略是保留3个月数据，那么张三过去的 15张订单已经被归档，留下15个空位，可以在insert发生时重新被利用。在这种情况下由于有空位可以利用，就不会发生页分裂。但是查询性能会比较低，因为查询时必须扫描那些没有数据的空位。

重建聚集索引后情况改变了，因为重建聚集索引就是把表中的数据重新排列一遍，原来的空位没有了，而页的填充率又很高，插入数据经常要发生页分裂，所以性能大幅下降。

对于聚集索引没有建在顺序字段上的表，是否要给与比较低的页填充率？是否要避免重建聚集索引？是一个值得考虑的问题！

10、加nolock后查询经常发生页分裂的表，容易产生跳读或重复读

加nolock后可以在“插、删、改”的同时进行查询，但是由于同时发生“插、删、改”，在某些情况下，一旦该数据页满了，那么页分裂不可避免，而此时nolock的查询正在发生，比如在第100页已经读过的记录，可能会因为页分裂而分到第101页，这有可能使得nolock查询在读101页时重复读到该条数据，产生“重复读”。同理，如果在100页上的数据还没被读到就分到99页去了，那nolock查询有可能会漏过该记录，产生“跳读”。

上面提到的哥们，在加了nolock后一些操作出现报错，估计有可能因为nolock查询产生了重复读，2条相同的记录去插入别的表，当然会发生主键冲突。

11、使用like进行模糊查询时应注意

有的时候会需要进行一些模糊查询比如

select*from contact where username like ‘%yue%’

关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%，

12、数据类型的隐式转换对查询效率的影响

sql server2000的数据库，我们的程序在提交sql语句的时候，没有使用强类型提交这个字段的值，由sql server 2000自动转换数据类型，会导致传入的参数与主键字段类型不一致，这个时候sql server 2000可能就会使用全表扫描。Sql2005上没有发现这种问题，但是还是应该注意一下。

13、SQL Server 表连接的三种方式

(1) Merge Join

(2) Nested Loop Join

(3) Hash Join

SQL Server 2000只有一种join方式——Nested Loop Join，如果A结果集较小，那就默认作为外表，A中每条记录都要去B中扫描一遍，实际扫过的行数相当于A结果集行数x B结果集行数。所以如果两个结果集都很大，那Join的结果很糟糕。

SQL Server 2005新增了Merge Join，如果A表和B表的连接字段正好是聚集索引所在字段，那么表的顺序已经排好，只要两边拼上去就行了，这种join的开销相当于A表的结果集行数加上B表的结果集行数，一个是加，一个是乘，可见merge join 的效果要比Nested Loop Join好多了。

如果连接的字段上没有索引，那SQL2000的效率是相当低的，而SQL2005提供了Hash join，相当于临时给A，B表的结果集加上索引，因此SQL2005的效率比SQL2000有很大提高，我认为，这是一个重要的原因。

总结一下，在表连接时要注意以下几点：

(1) 连接字段尽量选择聚集索引所在的字段

(2) 仔细考虑where条件，尽量减小A、B表的结果集

(3) 如果很多join的连接字段都缺少索引，而你还在用SQL Server 2000，赶紧升级吧。

    相同sql在不同数据库耗时不同分析    sql查询时间不同，有很多方面影响，上层比如索引、数据量、分区、锁等原因，下层存储、架构等原因，具体事件具体分析，以下只是我遇到的一种情况。操作步骤：1：执行sql2：执行计划对比3：数据总量对比4：表结构对比5：索引对比6：数据行数对比 7：建议

执行sqlselect * from FFXX where FFXXID in (select min(FFXXid) from FFXX where SJZT=1 and FJBJ=3 and fjr=1 and JGSJ>=to_date('2014-11-22 00:00:00','yyyy-mm-dd HH24:Mi:SS') and JGSJ

执行计划对比分析：B库该表分区，条件时间所在分区数据基数非常小，所以直接走全表扫描C库该表不是分区，索引基数非常大，但是走索引是该表最好的方式

数据总量对比select sum(bytes)/1024/1024 from dba_segments where segment_name='FFXX';分析：从中可以看出sql在B库数据库执行该表，数据总范围是32M但是在C库数据库总范围是2628M

表结构对比分析：B库该表创建了分区，C库没有创建分区

索引对比分析：B库时分区索引，C库时普通索引

数据行数对比分析：B库所在分区数据量才8W，C库是749W 

建议将C库数据库中的该表该造成分区表 注释：分区表的好处之一是减少数据查询范围总量

交叉连接查询

这种查询方式基本不会使用，原因就是这种查询方式得到的是两个表的乘积（笛卡儿集）

语法就是select * from a，b；

内连接查询，可以有效的去除笛卡尔集现象

内连接查询分为两类：

隐式内连接 select * from A，B where 条件隐式连接使用别名：select * from A 别名1，B 别名2 where 别名1.xx=别名2.xx；显示内连接 select * from A inner join B on 条件 （inner可以省略）显示连接使用别名： select * from A 别名1 inner join B 别名2 on 别名1.xx=别名2.xx

举例：

SELECT * FROM category c,product p WHERE c.cid=p.category_id;

外连接

外连接有两种方式，一种是左外连接，一种是右外连接

左外连接：select * from A left outer join B on条件右外连接：select * from A right out join B on 条件左外连接就是左边的表的内容全部显示，然后匹配右边的表，如果右边的表匹配不到，则空右外连接就是右边的表的内容全部显示，然后匹配左边的表，如果左边的表匹配不到，则空

总结：

内连接就是两个表的交集

左外连接就是左边表加两表交集

右外连接就是右边表加两表交集

子查询

子查询就是查询中还有查询，就是一条select语句结果作为另外一条select语法的一部分（查询结果，查询条件，表等）

子查询的用处很多比如：查询本公司工资最高的员工的详细信息

select * from emp where sal=max（sal）这个是错误的，原因是聚合函数不可以用在条件中，要想解决这个问题，只能用子查询select* from emp where sal=（select max（sal）from emp）

exists关键字select * from emp where exists（select max（sal） from emp）这句话的意思是只要（select max（sal） from emp）有结果则执行select * from emp，否则不执行

子查询出现在where后是作为条件出现的

子查询出现在from之后是作为表存在的

作为表举例 select e.emono,e.ename from(select * from where deptno=30) e还给表起了一个别名e

作为条件有以下几种情况

单行单列：可以使用=，>,<,>=,<=,!=多行单列（集合）可以用All ANY IN not IN单行多列（对象），就是一行，像一个对象一样什么属性都有多行多列：多行多列一直用在from后面作为表

单行单列举例：select * from emp where sal >(select avg(sal) from emp)

多行多列举例：select * from emp where sal> All(select sal from emp where deptno=10)

单行多列举例：select * from emp where（job，deptno，sal）IN（select job，deptno，sal from emp where ename='殷天正'）;（查询和殷天正一样工作，工号，工资的人的工作，工号，工资从emp表中）

每天分享编程语言，欢迎关注

在上篇《提高mysql千万级大数据SQL查询优化几条经验(1)》中我们从几个方面提出了15条优化或是注意的。接下来我们接着看还有那些需要注意的。

本节主要内容：

1：字段数据类型上需注意

2：临时表使用需注意

16：尽量使用数字类型字段，若只含有数值信息的字段，尽量不要设计字符类型，这会降低查询和连续查询性能，并会增加存储开销。这是因为引擎在处理查询和连接时候会逐个比较字符串中每一个字符，而对于数字类型来说只需要比较一次就够了。

另：mysql

17：尽可能的使用varchar/nvarchar 代替 char/nchar。因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高许多。

18：任何地方都不要使用select * from user ，使用具体的字段列来代替"*".不要返回用不到的任何字段。

19：尽量使用表变量来代替临时表，如果表变量包含大量数据，请注意索引非常有限(只有主键索引)

20：避免频繁创建和删除临时表，以减少系统表资源消耗

21：临时表并不是不可使用，适当的使用它们可以使某些更有效。

例如，当需啊哟重复引用大型表或者常用表中的某个数据集的时候。但是，对于一次性事件，最后使用导出表。

22：在创建临时表时，如果一次性插入数据量很大，那么可以使用select into 语句来代替create table，避免造成大量log日志，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后 insert.

23：如果使用到了临时表，在存储过程的最后务必将所有临时表显示删除，先truncate table，然后 drop table.这样可以避免系统表的较长时间锁定。

24：尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过一万行，那么就应该考虑改写了。

25：使用基于游标的方法或是临时表方法之前，应先找基于集的解决方案来解决问题，基于集的方法通常更有效。

26：与临时表一样，游标并不是不可使 用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

27：在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。

28：尽量避免大事务操作，提高系统并发能力。尽量避免大事务操作，提高系统并发能力。

29：.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

相关文章：

提高mysql千万级大数据SQL查询优化几条经验(1)

简介从SQL Server 2005开始，您可以使用通用表表达式（CTE）创建递归查询。它们是非常强大的工具，可用于查询分层数据，您不能预先知道多少次必须加入到同一个表。这可能是最常见的用途。但是它们也可以用于做各种各样的事情，包括但不限于：根据数量字段创建n行数，从字段中提取多个匹配的子串，从集合中创建排列/组合，或者采取日期范围从一行并将其分解成多行较小的范围。递归CTE的基本结构这是CTE的基本结构：

展开| 选择| 包裹| 行号

WITH cte AS（

基本查询

UNION ALL

递归查询回调到cte

在哪里终止检查

）

SELECT * FROM cte

递归CTE由3个关键组件组成。

返回要在递归中使用的初始行的基本查询。

一个具有回调CTE本身的查询。

一个子句最终导致一个空的结果集，所以递归可以终止。

＃3绝对是关键。在某些时候，递归需要结束，所以可以返回结果。否则，如果最大递归设置为0，它将在SQL Server中设置最大递归值时出错，否则将无限期运行，直到终止。如果最大递归设置为0，则可以设置max递归选项

OPTION (MAXRECURSION #)

，其中＃是0到32767之间的数字。默认情况下，当未指定该选项时，为100. 下面的所有示例都是在SQL Server 2008 R2上创建和测试的。防爆。1：从数量字段创建附加行此示例以数量表示的次数重复数据，并重复数据。样品数据：

展开| 选择| 包裹| 行号

物品数量

a 1

b 2

c 3

d 4

e 5

查询：

展开| 选择| 包裹| 行号

DECLARE @t TABLE（item char（1），quantity int）

INSERT INTO @t VALUES（'a'，1），（'b'，2），（'c'，3），（'d'，4），（'e'，5）

; WITH cte AS（

选择物品，

数量 - 1 AS数量

从T

UNION ALL

选择物品，

quantityLeft - 1 AS quantityLeft

从

CTE

- 终止条款

WHERE quantityLeft>0

）

SELECT * FROM cte ORDER BY item;

结果：

展开| 选择| 包裹| 行号

商品数量低

a 0

b 1

b 0

c 1

c 0

c 2

d 3

d 2

d 1

d 0

e 4

e 3

e 2

e 1

e 0

防爆。2：创建排列/组合此示例将获取数据集，并创建行的所有可能的组合和排列。要小心这个，因为可能性的数量呈指数级增长。样品数据：

展开| 选择| 包裹| 行号

项目

一个

b

C

查询：

展开| 选择| 包裹| 行号

DECLARE @t TABLE（item CHAR（1））

INSERT INTO @t VALUES（'a'），（'b'），（'c'），（'d'），（'e'）

DECLARE @maxLen INT

SET @maxLen =（SELECT COUNT（*）FROM @t）

- 组合，顺序没关系

; WITH cte AS（

选择物品，

CONVERT（VARCHAR（255），item）AS组合

从T

UNION ALL

SELECT t.item，

CONVERT（VARCHAR（255），ctebined + t.item）AS组合

从

CTE

INNER JOIN @t t

ON cte.item

WHERE LEN（ctebined + t.item）<= @maxLen

）

SELECT组合FROM cte ORDER BY LEN（组合），组合;

- 排列，秩序事宜

; WITH cte AS（

选择物品，

CONVERT（VARCHAR（255），item）AS组合

从T

UNION ALL

SELECT t.item，

CONVERT（VARCHAR（255），ctebined + t.item）AS组合

从

CTE

INNER JOIN @t t

ON ctebined NOT LIKE'％'+ t.item +'％'

WHERE LEN（ctebined + t.item）<= @maxLen

）

SELECT组合FROM cte ORDER BY LEN（组合），组合;

组合结果：

展开| 选择| 包裹| 行号

结合

一个

b

C

AB

AC

广告

AE

公元前

BD

是

光盘

CE

德

ABC

ABD

安倍晋三

ACD

高手

ADE

BCD

BCE

BDE

CDE

A B C D

ABCE

ABDE

ACDE

BCDE

ABCDE

排列结果：放入文章的行数太多。防爆。3：从字段中提取多个PDF文件名本示例从每个PDF文件名称结束的字段中的较大字符串中排除未知数量的PDF文件名，

.pdf

并在第一个

>

符号开始之前

.pdf

，其他所有内容都是多余的。样品数据：

IDField fieldName1 >>>>>> 1.pdf test>> b> c> xyz.pdf bob> hello world.pdf foo> womp womp.pdf>2> 2.pdf其他不需要的东西>bar.pdf

查询：注释掉的行是找到并排除PDF文件名的构建块。

DECLARE @f TABLE（fieldName VARCHAR（255），IDField int）

INSERT INTO @f VALUES（'>>>>>> 1.pdf test>> b> c> xyz.pdf bob> hello world.pdf foo> womp womp.pdf>'，1）

INSERT INTO @f VALUES（'> 2.pdf其他不需要的东西>bar.pdf'，2）

; WITH cte2 AS（

选择

IDField，

--PATINDEX（'％。pdf％'，fieldName）+ 3 AS PDFLocation，

--SUBSTRING（fieldName，1，（PATINDEX（'％。pdf％'，fieldName）+ 3））AS PDFSubstring，

--REVERSE（SUBSTRING（fieldName，1，（PATINDEX（'％。pdf％'，fieldName）+ 3）））AS PDFSubstringReverse，

- PATINDEX（'％>％'，REVERSE（SUBSTRING（fieldName，1，（PATINDEX（'％。pdf％'，fieldName）+ 3））））AS ReverseSymbolLocationBeforePDF，

--LEN（SUBSTRING（fieldName，1，（PATINDEX（'％。pdf％'，fieldName）+ 3））） - PATINDEX（'％>％'，REVERSE（SUBSTRING（fieldName，1，（PATINDEX（' pdf％'，fieldName）+ 3））））+ 2 AS SymbolLocationBeforePDF，

CONVERT（VARCHAR（255），SUBSTRING（fieldName，

LEN（SUBSTRING（fieldName，1，（PATINDEX（'％。pdf％'，fieldName）+ 3））） - PATINDEX（'％>％'，REVERSE（SUBSTRING（fieldName，1，（PATINDEX（'％。pdf％ '，fieldName）+ 3））））+ 2，

PATINDEX（'％。pdf％'，fieldName）+ 3 - （LEN（SUBSTRING（fieldName，1，（PATINDEX（'％。pdf％'，fieldName）+ 3））） - PATINDEX（'％>％'，REVERSE （SUBSTRING（fieldName，1，（PATINDEX（'％。pdf％'，fieldName）+ 3））））+ 2）+ 1

））AS PDFName，

CONVERT（VARCHAR（255），STUFF（fieldName，1，PATINDEX（'％。pdf％'，fieldName）+ 3，''））AS strWhatsLeft

FROM @f

UNION ALL

选择

IDField，

--PATINDEX（'％。pdf％'，strWhatsLeft）+ 3 AS PDFLocation，

--SUBSTRING（strWhatsLeft，1，（PATINDEX（'％。pdf％'，strWhatsLeft）+ 3））AS PDFSubstring，

--REVERSE（SUBSTRING（strWhatsLeft，1，（PATINDEX（'％。pdf％'，strWhatsLeft）+ 3）））AS PDFSubstringReverse，

- PATINDEX（'％>％'，REVERSE（SUBSTRING（strWhatsLeft，1，（PATINDEX（'％。pdf％'，strWhatsLeft）+ 3））））AS ReverseSymbolLocationBeforePDF，

--LEN（SUBSTRING（strWhatsLeft，1，（PATINDEX（'％。pdf％'，strWhatsLeft）+ 3））） - PATINDEX（'％>％'，REVERSE（SUBSTRING（strWhatsLeft，1，（PATINDEX（' pdf％'，strWhatsLeft）+ 3））））+ 2 AS SymbolLocationBeforePDF，

CONVERT（VARCHAR（255），SUBSTRING（strWhatsLeft，

LEN（SUBSTRING（strWhatsLeft，1，（PATINDEX（'％。pdf％'，strWhatsLeft）+ 3））） - PATINDEX（'％>％'，REVERSE（SUBSTRING（strWhatsLeft，1，（PATINDEX（'％。pdf％ '，strWhatsLeft）+ 3））））+ 2，

PATINDEX（'％。pdf％'，strWhatsLeft）+ 3 - （LEN（SUBSTRING（strWhatsLeft，1，（PATINDEX（'％。pdf％'，strWhatsLeft）+ 3））） - PATINDEX（'％>％'，REVERSE （SUBSTRING（strWhatsLeft，1，（PATINDEX（'％。pdf％'，strWhatsLeft）+ 3））））+ 2）+ 1

））AS PDFName，

CONVERT（VARCHAR（255），STUFF（strWhatsLeft，1，PATINDEX（'％。pdf％'，strWhatsLeft）+ 3，''））AS strWhatsLeft

FROM cte2

WHERE strWhatsLeft LIKE'％.pdf％'

）

SELECT * FROM cte2 ORDER BY IDField

结果：

IDField PDFName strWhatsLeft

1 1.pdf test>> b> c> xyz.pdf bob> hello world.pdf foo> womp womp.pdf>

1 xyz.pdf bob> hello world.pdf foo> womp womp.pdf>

1 hello world.pdf foo> womp womp.pdf>

1 womp womp.pdf>

2 2.pdf其他不必要的东西>bar.pdf

2 bar.pdf

防爆。4：将日期范围分割成多个较小的范围此示例使用定义开始和结束日期的行，并创建最多间隔为3天的多行。样品数据：

TimespanID StartDate EndDate

1 2015-01-01 2015-01-02

2 2015-01-05 2015-02-11

查询：

DECLARE @t TABLE（TimespanID int，StartDate DATE，EndDate DATE）

INSERT INTO @t VALUES（1，'1/1/2015'，'1/2/2015'），（2，'1/5/2015'，'2/11/2015'）

; WITH cte AS（

选择

TimespanID，

开始日期，

DATEADD（DAY，2，StartDate）>EndDate的情况

THEN EndDate

ELSE DATEADD（DAY，2，StartDate）

END AS EndDate，

EndDate AS OriginalEndDate

从T

UNION ALL

选择

TimespanID，

DATEADD（DAY，1，EndDate）AS StartDate，

DATEADD（DAY，3，EndDate）>OriginalEndDate

THEN OriginalEndDate

ELSE DATEADD（DAY，3，EndDate）

END AS EndDate，

OriginalEndDate AS OriginalEndDate

从cte

WHERE DATEADD（DAY，1，EndDate）<= OriginalEndDate

）

SELECT * FROM cte ORDER BY TimespanID

结果：

TimespanID StartDate EndDate OriginalEndDate1 2015-01-01 2015-01-02 2015-01-022 2015-01-05 2015-01-07 2015-02-112 2015-01-08 2015-01-10 2015-02-112 2015-01-11 2015-01-13 2015-02-112 2015-01-14 2015-01-16 2015-02-112 2015-01-17 2015-01-19 2015-02-112 2015-01-20 2015-01-22 2015-02-112 2015-01-23 2015-01-25 2015-02-112 2015-01-26 2015-01-28 2015-02-112 2015-01-29 2015-01-31 2015-02-112 2015-02-01 2015-02-03 2015-02-112 2015-02-04 2015-02-06 2015-02-112 2015-02-07 2015-02-09 2015-02-112 2015-02-10 2015-02-11 2015-02-11

防爆。5：从关系表中检索层次结构本示例采用存储在表中的层次结构，其中ParentID是可用于建立层次结构级别的唯一链接。通常这是一个问题，因为您永远不知道员工的级别，以及您需要多少次连接到表以检索完整的层次结构。然而，对于递归的CTE来说这是微不足道的，因为它将不断地自我连接，直到建立完整的层次结构。请注意，此示例与其他示例不同之处在于没有WHERE子句终止检查。相反，当达到层次结构的最低级别时，会发生终止，意味着没有较低级别的员工可以加入，因为没有人向他们报告。这也意味着如果数据不好，存在层次结构中无限循环的可能性。样品数据：

PK EmployeeName ParentID

1先生0

2安迪1

3比利·吉恩1

4查尔斯2

5丹尼2

6伊甸园2

弗兰克3

8 Geri 5

查询：

DECLARE @t TABLE（PK INT，EmployeeName VARCHAR（10），ParentID INT）;

INSERT INTO @t VALUES

（1，“CEO先生”，0）

（2，'Andy'，1），

（3，'比利·让'，1），

（4，'查尔斯'，2），

（5，'Danni'，2），

（6，'Eden'，2），

（7，'Frank'，3），

（8，'Geri'，5）

;

; WITH cte AS（

选择

PK，

员工姓名，

1层级层次，

CONVERT（VARCHAR（255），PK）AS HierarchySortString

从T

WHERE ParentID = 0

UNION ALL

选择

t.PK，

t.EmployeeName，

cte.HierarchyLevel + 1 AS HierarchyLevel，

CONVERT（VARCHAR（255），HierarchySortString +'，'+ CONVERT（VARCHAR（255），t.PK））AS HierarchySortString

FROM @t AS t

内在加入

ON t.ParentID = cte.PK

）

选择

PK，

REPLICATE（'+'，HierarchyLevel - 1）+ EmployeeName AS EmployeeName，

HierarchyLevel

从cte

订购

HierarchySortString

结果：

展开| 选择| 包裹| 行号

PK EmployeeName层次结构层

1先生CEO 12 +安迪24 ++查尔斯35 ++丹尼38 +++ Geri 46 ++伊甸园33 + Billy Jean 27 ++弗兰克3

自增长是auto_increment

drop table 表名 删除表

show create table 表名 可以看出该表的结构和看数据库结构类似

字段类型

sql插入语句规则

图片来自培训机构

插入数据在dos乱码问题的解决

将utf8改成gbk就ok，但是这个改变的是系统文件，不好，还有一种方式是

set names gbk;

使用这种方式很好，不会涉及系统文件

删除

这个可以看出，这个在事务内删除，删除成功了，但是后来回滚了事务，删除的内容又回来了

这个可以看出，即使回滚了，但是删除的数据还是回不来了，truncate是删除这个表，在重新创建一张，所以在插入数据的时候，这个表的自增长主键又从1开始了，和新表一样

查询

查询的时候可以给表或者字段设置别名，使用关键字as

select * from tsuiau as student这就相当于给tsuiau设置了别名student那么下面我们查询tsuiau表的时候可以直接使用studentselect * from student

distinct用于去除重复数据select distinct （字段） from 表名

条件查询

图片来自培训机构

查询之后，查寻结果排序，默认升序

聚合函数

分组查询group by 字段

根据某个字段分组，字段一样的为一组

上面是根据cid进行分组，cid一样的为一组，然后使用聚合函数统计每一组中的个数

where和having后面都是条件，二者区别是where用于分组前，having用于分组后，这几个的执行顺序是where-》group by-》having-》order by，where是分组前的条件，having是分组后的条件，分组后的条件可以使用分完组之后的查询结果的内容

顺序是这样的

分页查询limit

select * from emp limit 0， 5 从第一行开始查，一共查5行select * from emp limit 8， 5 从第9行开始查，一共查5行select * from emp limit 12, 5 从第11行开始查，一共查5行

行数是从0开始算的，0算第一行

假如规定每页有10行记录，那么如果查询第三页所有记录这个语句怎么写？

select * from emp limit 20，10；

这个20是这样求的（查询的第几页-1）*每页记录数=（3-1）*10=20

每天分享编程知识，欢迎关注

CREATE TABLE

CREATE TABLE用来创建一个表

//创建一个表，表的名称为table_name，该表有三列，列名分别为column_1，column_2，column_3.列中的数据类型分别为整形，文本，整形

举例如下：

INSERT INTO

INSERT INTO用于向列中插入值

//分别向列id, name, age中插入 1, 'Justin Bieber', 21 insert into table_test (id,name,age) values(1,"Justin Bieber",21);

//插入多条数据

1、MySQL数据库：

2、oracle数据库：

UPDATE

用于修改表中的数据

语法 UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

举例：修改id=1这一行的age列的数据为22 SQL如下：

UPDATE table_test SET age = 22 WHERE id = 1;

ALTER TABLE

修改表，用于增加，修改，删除列

例如：ADD COLUMN添加一个列 ALTER TABLE table_test ADD COLUMN address TEXT;

例如：DROP COLUMN删除一个列 ALTER TABLE table_test DROP COLUMN address ;

DELETE

删除表中的行

例如：IS NULL表示值是NULL或者不存在，删除age列中值不存在的行 DELETE FROM table_test WHERE age IS NULL;

SQL查询 DISTINCT 过滤重复的数据 语法如下：

WHERE

通过各种条件定位到具体的数据

例如：统计age大于24的数据 SELECT * FROM table_test WHERE age >24;

LIKE

模糊匹配

举例：_通配一个字符 SELECT * FROM table_test WHERE name LIKE 'Mar_y';

举例：%通配任意多个字符 SELECT * FROM table_test WHERE name LIKE '%Bieber%';

BETWEEN 匹配一个范围

例如：查到age范围在23到30之间的数据 SELECT * FROM table_test WHERE age BETWEEN 23 and 30;

AND 逻辑与

例如：查到age范围在23到30之间的数据 SELECT * FROM table_test WHERE age >= 23 and age <= 30;

OR逻辑或

例如：查到age范围不在在23到30之间的数据 SELECT * FROM table_test WHERE age <= 23 or age >= 30;

等同于 SELECT * FROM table_test WHERE age not BETWEEN 23 and 30;

ORDER BY

例如：依据某一列进行排序，ASC是顺序排序，DESC是逆序排序。 SELECT * FROM table_test ORDER BY age ASC;

COUNT()

COUNT()是最快的方式，统计一张表总共的行数。COUNT()函数的参数是一个列的名称，统计整个表所有的行数时，采用通配符*;

例如：统计整个表的行数 SELECT COUNT(*) FROM table_test ;

例如：统计age为25的行数 SELECT COUNT(*) FROM table_test WHERE age= 25;

GROUP BY

GROUP BY <列名>将一列中相同值的列分成一组

例如：按照age进行分组，并统计每组元素的个数 SELECT age, COUNT(*) FROM table_test GROUP BY age;

SUM()

SQL通过SUM()可以很容易统计一列的和

例如：计算age一列的和 SELECT SUM(age) FROM table_test ;

MAX()

例如：MAX()函数可以找到一列中的最大值 SELECT MAX(age) FROM table_test ;

MIN()

MIN()函数可以找到一列中最小的值，用法与MAX()相同

AVG()

AVG()函数计算一列的平均值，用法与MAX()相同

ROUND() 设定数值到指定的精度

举例：设定计算的平均值精度精确到小数点后两位 SELECT ROUND(AVG(age), 2) FROM table_test;

PRIMARY KEY

在使用CREATE TABLE时为id添加PRIMARY KEY，PRIMARY KEY是一张表中每一行独一无二的标识，该表将id作为主键。通过主键将多个表联系起来。

CREATE TABLE table_test (id INTEGER PRIMARY KEY, name TEXT）；

在查询数据的过程中，我们经常会遇到某个字符或者某个字符串在一个长字符串中出现次数的问题，这个时候，我们就有必要根据实际情况进行分析处理了。

我们首先打开sql server management studio,我们输入登录密码，进入到sql server 企业管理器。

我们点击工具栏的新建查询按钮，打开一个查询代码编辑窗口。

我们定义一个查询函数，代码如图所示。

我们用代码调用查询函数进行查询，当查询不到的时候，次数显示为0.

当能在目标字符串中查找到字符串时，我们可以看到查找到的次数。

从上述步聚，我们可以看到大功告成了。上面的步聚适合于单个字符及多个字符的匹配。

1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

4、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5、下面的查询也将导致全表扫描：(不能前置百分号)

select id from t where name like ‘%c%’

若要提高效率，可以考虑全文检索。

6、in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’–name以abc开头的id

select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

应改为:

select id from t where name like ‘abc%’

select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。

12、不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(…)

13、很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21、避免频繁创建和删除临时表，以减少系统表资源的消耗。

22、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使 用导出表。

23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

30、尽量避免大事务操作，提高系统并发能力。

可用group by……having来实现。

可做如下测试：

1、创建表插入数据：

create table test(id int,name varchar(10))insert into test values (1,'张三')insert into test values (2,'李四')insert into test values (3,'张三')insert into test values (4,'王五')insert into test values (5,'赵六')

其中name是张三的有两行，也就是重复行。

2、执行sql语句如下：

select * from test where name in (select name from test group by name having COUNT(*)>1)

结果如图：

（本文内容由百度知道网友badkano贡献）