SQL是数据库的查询语言，语法结构简单，相信本文会让你从入门到熟练。

掌握SQL后，不论你是产品经理、运营人员或者数据分析师，都会让你分析的能力边界无限拓展。别犹豫了，赶快上车吧！

以下的语句都在SequelPro的Query页面运行，其他操作页面不会有太大差异。标点符号必须为英文，这是新人很容易犯的错误。

SQL最小化的查询结构如下：

selectcolumnfromtable

table是我们的表名，column是我们想要查询的字段／列，column可以用*代替，指代全部字段，意为从table表查询所有数据。

where是基础查询语法，用于条件判断。

select*fromDataAnalystwherecity='上海'

上图是最简化的查询语句，将所有城市为上海的职位数据过滤出来。我们也可以用and进行多条件判断。

select*fromDataAnalystwherecity='上海'andpositionName='数据分析师'

or语句则是或的关系：

select*fromDataAnalystwherecity='上海'orpositionName='数据分析师'

查找城市为上海，或者职位名称是数据分析师的数据，它们是并集。

当我们涉及到非常复杂的与或逻辑判断，应该怎么办？比如即满足条件AB，又要满足条件C，或者是满足条件DE。此时需要用括号明确逻辑判断的优先级。

select*fromDataAnalystwhere(city='上海'andpositionName='数据分析师')or(city='北京'andpositionName='数据产品经理')

这条语句的含义是查找出上海的数据分析师或者是北京的产品经理。当有括号时，会优先进行括号内的判断，当有多个括号时，对最内层括号先进行判断，然后依次往外。

接下来的问题来了，当我们要查询多个条件，比如北京上海广州深圳南京这些城市，难道一个个用and关联起来？这太麻烦了，我们可以使用in。

select*fromDataAnalystwherecityin('北京','上海','广州','深圳','南京')

当我们遇到字段数据类型是数值时，也可以使用符号>、>=、<、<=、!=进行逻辑判断，!=指的是不等于，等价于<>。

select*fromDataAnalystwherecompanyId>=10000

上例是筛选出公司ID>=10000的职位，为数值时，不需要像字符串一样加引号。

当我们需要取区间数值时，使用betweenand：

select*fromDataAnalystwherecompanyIdbetween10000and20000

betweenand包括数值两端的边界，等同于companyId>=10000andcompanyId<=20000。

如果要模糊查找，能用like。

select*fromDataAnalystwherepositionNamelike'%数据分析%'

语句的含义是在positionName列查找包含「数据分析」字段的数据，%代表的是通配符，含义是无所谓「数据分析」前面后面是什么内容。如果是‘数据分析%’，则代表字段必须以数据分析开头，无所谓后面是什么。

除了上面所讲，还有一个常用的语法是not，代表逻辑的逆转，常见notin、notlike、notnull等。

接下来我们学习groupby，它是数据分析中常见的语法，目的是将数据按组／维度划分。类似于Excel中的数据透视表，我们以city为例。

select*fromDataAnalystgroupbycity

它将城市划分成几组，通过groupby可以快速的浏览数据有哪些城市。我们看一下它的高阶用法。

selectcity,count(1)fromDataAnalystgroupbycity

上述语句，使用count函数，统计计数了每个城市拥有的职位数量。括号里面的1代表以第一列为计数标准。这里出现新的问题，当我们遇到重复数据怎么办？在DataAnalyst这张表中，北京职位包含重复的职位ID，我们需要去重。

selectcity,count(distinctpositionId)fromDataAnalystgroupbycity

北京的数据一下子少了2000，多余的重复值被排除在外。distinct是去重函数，distinctpositionId会只计算唯一的positionId个数。日常工作中，活跃用户数、文章UV，都是用distinct计算获得，这是唯一标示符ID的重要作用。

除了count，还有max，min，sum，avg等函数，也叫做聚合函数。用法和Excel没什么区别。

当我们在groupby添加多个字段，它将以多维的形式进行数据聚合。

selectcity,workYear,count(distinctpositionId)fromDataAnalystgroupbycity,workYear

这就是数据分析师常用的多维分析法，通过groupby切分不同的维度进行对比，在不利用BI的情况下，通过SQL进行快速数据分析。

接下来学习逻辑判断，SQL也有if函数，和Excel的用法一摸一样，通过它我们能进行复杂的运算。比如我想统计各个城市中有多少数据分析职位，其中，电商领域的职位有多少，在其中的占比？

industryField是公司的行业领域，虽然我们能用wherelike计算出有几个电商的数据分析师，但是占比的计算会比较麻烦，此时可以用if。

selectif(industryFieldlike'%电子商务%',1,0)fromDataAnalyst

上面的公式利用if判断出哪些是电商行业的数据分析师，哪些不是。if函数中间的字段代表为true时返回的值，不过因为包含重复数据，我们需要将其改成positionId。之后，用它与groupby组合就能达成目的了。

selectcity,

count(distinctpositionId),

count(if(industryFieldlike'%电子商务%',positionId,null))fromDataAnalystgroupbycity

第一列数字是职位总数，第二列是电商领域的职位数，相除就是占比。记住，count是不论0还是1都会纳入计数，所以第三个参数需要写成null，代表不是电商的职位就排除在计算之外。

接下来是新的问题，如果我想找出各个城市，数据分析师岗位数量在500以上的城市有哪些，应该怎么计算？有两种方法，第一种，是使用having语句，它对聚合后的数据结果进行过滤。

selectcity,count(distinctpositionId)fromDataAnalystgroupbycityhavingcount(distinctpositionId)>=500

第二种，是利用嵌套子查询。

我们将第一次查询获得的城市职位数的结果，看作一张新的表，利用as将它命名为t1(table1的简写)，将职位数命名为一个新的字段counts。然后外面再套一层select过滤出counts>=500。

这种查询方式就叫嵌套子查询，使用场景比较广泛，where后面也能跟子查询。

很多时候，数据是凌乱的，我们希望结果能够呈现一定的顺序，这时候就用到orderby语句。

selectcity,count(distinctpositionId)ascountsfromDataAnalystgroupbycity

orderbycounts

看，数据就按照统计结果升序排列，如果需要降序，则是orderbycountsdesc，后面加一个desc就好了。如果是多个字段，按逗号分隔即可。

我们再来熟悉SQL的常用函数，首先是时间。因为我们的练习数据中没有时间，首先用now创建出一个时间字段。

selectnow()

直接执行它，就能获得当前的系统时间，精确到秒。其实select不一定后面要跟from。

selectdate(now())

它代表的是获得当前日期，week函数获得当前第几周，month函数获得当前第几个月。其余还包括，quarter，year，day，hour，minute。

时间函数也包含各种参数，比如week，因为中西方计算第几天是不一样的，西方把周日算作一周中的第一天，而我们习惯周一。

selectweek(now(),0)

除了以上的日期表达，也可以使用dayofyear、weekofyear的形式计算。它和上面的部分函数等价。

怎么对时间进行加减法呢？这时候靠date_add函数出马。

selectdate_add(date(now()),interval1day)

我们可以改变1为负数，达到减法的目的，也能更改day为week、year等，进行其他时间间隔的运算。如果是求两个时间的间隔，则是datediff(date1,date2)或者timediff(time1,time2)。

时间函数的运用比较灵活，没有特殊限定，网络上的文档和教程也不少，可以深入学习。

最后是数据清洗类的函数。

selectleft(salary,1)fromDataAnalyst

MySQL支持left、right、mid等函数，这里又和Excel一样。我们通过salary计算数据分析师的工资吧（这一步骤，在曾经的文章中已经用Excel和BI多次讲解，所以我就不多赘述了，只讲过程，不熟悉的同学可以看历史内容）。

首先利用locate函数查找第一个k所在的位置。

selectlocate("k",salary),salaryfromDataAnalyst

然后使用left函数截取薪水的下限。

selectleft(salary,locate("k",salary)-1),salaryfromDataAnalyst

为了获得薪水的上限，要用substr函数，或者mid，两者等价。

substr（字符串，从哪里开始截，截取的长度）

薪水上限的开始位置是「-」位置往后推一位。截取长度是整个字符串减去「-」所在位置，刚好是后半段我们需要的内容，不过这个内容是包含「K」的，所以最后结果还得再减去1。

这里不了解不要紧，可以将计算过程分步骤运行。基本上，了解了上面写法的含义，文本清洗这块就没有问题了（notlike用来清洗乱七八糟的薪水，我简单处理了）。再然后计算不同城市不同工作年限的平均薪资。

上面语句，我们用了文本清洗、子查询嵌套、分组聚合、排序等多种用法，属于较复杂的查询。重复数据的问题，因为我是复制了一份北京数据，数量刚好乘二，对平均数没有影响，感兴趣的朋友可以再加一步清洗掉它。

下面是三道思考题：

查询出哪家公司招聘的岗位数最多；查询出O2O、电子商务、互联网金融这三个行业，哪个行业的平均薪资最高；查询出各城市的最高薪水Top3是哪家公司哪个岗位。

做完上面的题目，你已经神功初成，数据分析的SQL意见没有大问题了。更复杂的查询，也无非是嵌套更多的内容，本质思路是一样的。

讲到这里，只剩join语法还没有教大家。因为练习数据只有一张表，而join又是SQL中比较容易混淆的难点，我会单独开一篇内容讲解，到时候使用SQLZoo和LeetCode的案例。

LeetCode是知名的算法竞赛网站，可以在上面和全世界的程序员比拼算法，当然我们只练习SQL，完成后，至少能秒杀全世界50%的程序员吧。

执行计划是数据库根据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，赶紧升级吧。

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）；

在上篇《提高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 management studio,我们输入登录密码，进入到sql server 企业管理器。

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

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

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

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

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

前面说到了直接安装mysql数据库，并没有提及数据库相关的一些基础知识，今天来补补基础。

数据库，顾名思义就是存储数据的仓库，可以简单理解为谷仓。不过这个数据仓是按一定的结构存放程序数据的，目的的避免冗余，当程序需要数据时，会发起查询取回相应的数据。

Web程序中目前最常用的仍然是基于关系模型的数据库，如mysql，Oracle，sqlserver等等，这种数据库也叫做SQL数据库，因为是使用结构化查询语言的。不过，近来也出现了文档数据库和键值对数据库，这两种数据库合称NoSQL数据库，如MongoDB。

SQL数据库：

关系数据库是把数据存储在表中的，表（二维表）模拟程序中的不同实体。表的列是固定的，称为字段（表示实体的数据属性），行是可以变的，称为记录。表中有个特殊的列，称为主键，它的值是表中各行的唯一标识符。表中还可以有外键，所谓外键就是引用同一个表或不同表中某行的主键。这样行之间的联系，就是关系，这就是关系型数据库的基础。关系数据库存储数据高效，而且避免了重复，但是，把数据分别存放在多个表中还是很复杂的，需要各种关联操作。

NoSQL数据库：

所有不遵循关系模型的数据库都可以统称为NoSQL数据库。NoSQL数据库一般使用集合代替表，使用文档代替记录。这样的方式是会使得联结变得困难的。它的特点是减少了表的数量，增加了数据的重复量，缺点是更新某数据会变得耗时，因为要跟新大量文档，优点就是可以没有复杂的关联快速查询。

两类数据库孰优孰劣呢？这个根据存在即合理，都是有各自存在优势与应用场景的，要视具体需求选择具体的数据库。

    相同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库数据库中的该表该造成分区表 注释：分区表的好处之一是减少数据查询范围总量

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、尽量避免大事务操作，提高系统并发能力。

数据重复是困扰许多企业的问题，但是一旦你了解了它的特点，以及如何去处理它，就可以提前发现并预防。在识别和消除重复数据时，也有很多潜在的选择，这样就可以找到适合你的业务和需求的最佳方法。

但是如果你想解决这个问题，你怎么开始呢？

下面是一些值得注意的最大问题：

记录问题。第一个最明显的问题是你的记录的准确性和可靠性。例如，你无意中列出了同一业务在你的销售记录中有两次；该公司的销售数字将加倍，因此，导致你的收入预测不合理地激增。当查看数据组时，你会更容易出现错误，并且在查找特定实例时，你可能会遇到更大困难，跟踪你需要的确切数据。

系统存储和批量。重复数据也会增加你的表格负担，从而阻塞你的系统，显示不必要的信息。在小规模上，这不是一个主要的数据来源，但是如果重复的数据存在于整个系统中，它可能会导致整个系统减速。

一般问题。很多人发现当查找重要信息时，重复数据集知道跟踪“正确”条目是多么烦人。例如，如果正在寻找“abc通信”，但是有一些条目是“abc公司”，“abc”和“abc通信”，它将花费你三倍或更长时间来获得正确的记录。这对于任何一个工作者来说都是个难题。

其他问题。重复数据也可能是其他原因的问题，具体而言，对于你数据表的应用而言。例如，如果你的网站上有太多重复的内容要索引，那么它可能会危及百度搜索排名还有其他搜索引擎，或者增加被索引的“错误”页面的可能性。

那么，你能做些什么来主动识别和消除重复数据？

这是一些比较好的策略：

完美的数据录入标准。每个组织都需要有一些所有工作人员应遵循的数据输入标准无论您的系统多么好，可能会有一些重复的数据点，除非所有的数据点都是一直遵循这些标准。制定严格、清晰的入门规则是一个好的第一步；除此之外，你用比较好的方法去教育你的员工，并确保他们理解这些规则，并要求他们遵守这些规则，这样他们就会一直遵循这些规则。

算法匹配非相同名称。通过创建更好的自动化流程算法可以自动匹配非相同名称。从前面章节中的例子中，我们提到了“abc公司”、“abc”和“abc通信”词条。a算法围绕着识别和自动合并“模糊匹配”之类的构建，可以防止它们作为不同记录存储起来。幸运的是在sql中安装主数据服务使创建干净、更合并列表变得非常容易。

自动化数据库清理。如果你的数据库已经在许多章节中遭受重复数据，或者过期检查，你也可以运行自动检查。你需要创建一个算法来扫描记录，以获取重复条目的标志，然后将数据合并到一个记录中。这里出错的可能性很高，所以请注意在敏感表上使用它。

手动数据库清理。作为备份，你还要执行手动数据库清理，特别是对于小表。

这些策略无法严格保证你将来不会遇到重复数据问题，但它们将消除当前大多数问题。随着数据标准的提高和数据库的清洁，你的整个团队都将能够提高自己的公众效率。

可用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贡献）