SQL，从入门到精通温馨提示：如果您已经熟悉数据库，则不需要再看这篇文章，或者只挑一部分。《写给新人的数据库指南》时，我们已经成功安装数据库并导入数据，今天我们就进入SQL实战实践。SQL是一种数据库查询语言，语法结构简单。相信这篇文章会让你从入门到精通。掌握SQL后，无论你是产品经理、运维人员，还是数据分析师，你的分析能力都会得到无限扩展。不要犹豫，赶紧上车！下面的语句都是在SequelPro的Query页面运行的，其他运行页面不会有太大区别。标点符号必须是英文的，这对新手来说很容易出错。SQL最小化的查询结构如下：selectcolumnfromtabletable是我们的表名，column是我们要查询的字段/列，column可以用*代替，它指的是所有字段，意思是从table表中查询所有数据.where是条件判断的基本查询语法。select*fromDataAnalystwherecity='Shanghai'上图是最简化的查询语句，过滤掉所有城市为上海的职位数据。我们也可以用and来做多个条件判断。select*fromDataAnalystwherecity='Shanghai'andpositionName='DataAnalyst'orstatement是or关系select*fromDataAnalystwherecity='Shanghai'orpositionName='DataAnalyst'搜索城市是上海，或者职位是数据分析师，他们是工会。当我们涉及到非常复杂的AND或者逻辑判断时，我们应该怎么办呢？例如条件AB必须满足，条件C也必须满足，或者条件DE必须满足。这时候就需要用括号来明确逻辑判断的优先级。select*fromDataAnalystwhere(city='Shanghai'andpositionName='DataAnalyst')or(city='Beijing'andpositionName='DataProductManager')意思是找出上海的数据分析师或北京的产品经理。有括号时，优先考虑括号内的判断。当有多个括号时，会先判断最里面的括号，然后依次向外判断。下一个问题来了。当我们要查询多个条件时，比如北京、上海、广州、深圳、南京，是否可以用and来一一链接起来呢？这样太麻烦了，我们可以在.select*fromDataAnalystwherecityin('Beijing','Shanghai','Guangzhou','Shenzhen','Nanjing')当我们遇到字段数据类型是数值的时候，我们也可以使用符号>、>=、<、<=,！=进行逻辑判断，!=表示不等于，相当于<>。select*fromDataAnalystwherecompanyId>=10000上面的例子是过滤掉公司ID>=10000的职位，当它是一个值的时候，不需要像字符串一样用引号引起来。当我们需要取一个区间值时，使用betweenandselect*fromDataAnalystwherecompanyIdbetween10000and20000between，并包含值两端的边界，相当于companyId>=10000andcompanyId<=20000。如果要模糊搜索，可以使用like.select*fromDataAnalystwherepositionNamelike'%DataAnalyst%'语句的意思是在positionName列中查找包含“DataAnalyst”字段的数据。%代表通配符，意思是“DataAnalyst”前面是什么无关紧要。如果是'dataanalysis%'，表示该字段必须以dataanalysis开头，后面是什么无关紧要。除了上面还有一个常用的语法isnot，代表逻辑的反转，常见的notin,notlike,notnull等。接下来我们学习groupby，这是数据分析中常用的语法，目的是将数据分成组/维度。类似于Excel中的数据透视表，我们以city为例。select*fromDataAnalystgroupbycity将城市分成若干组，通过groupby可以快速浏览到数据中有哪些城市。让我们看一下它的高级用法。selectcity,count(1)fromDataAnalystgroupbycity上面的语句使用count函数统计每个城市的jobs数量。括号中的1表示以第一列为计数标准。这里出现了一个新的问题。遇到重复数据怎么办？在DataAnalyst表中，北京jobs存在重复的jobID，需要去重。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是公司的行业字段，虽然我们可以用where之类的来计算有多少个电商岗位数据分析师，但是计算比例会比较麻烦，这时候可以用if。selectif(industryFieldlike'%e-commerce%',1,0)fromDataAnalyst上面的公式通过if判断哪些是电商行业的数据分析师，哪些不是。if函数中间的字段表示为true时返回的值，但是因为里面有重复的数据，所以我们需要把它改成positionId。之后结合groupby使用即可达到目的。selectcity,count(distinctpositionId),count(if(industryFieldlike'%e-commerce%',positionId,null))fromDataAnalystgroupbycity第一列数字为总持仓数，第二列为e中持仓数商业领域。除法就是比例。记住，count不管是0还是1都会被统计，所以第三个参数需要写成null，也就是说把非电商的仓位排除在计算之外。接下来是一个新问题。如果我想知道每个城市有哪些城市的数据分析师职位超过500个，应该怎么计算呢？有两种方法。第一种是使用having语句，对聚合后的数据结果进行过滤。selectcity,count(distinctpositionId)fromDataAnalystgroupbycityhavingcount(distinctpositionId)>=500第二种是使用嵌套子查询。我们将第一次查询得到的城市职位数的结果作为一个新的表，用as命名为t1（table1的缩写），并将职位数命名为一个新的字段counts。然后再加一层select，过滤掉counts>=500。这种查询方式称为嵌套子查询，使用场景非常广泛，where后面也可以进行子查询。很多时候，数据是杂乱无章的，我们希望结果能够按照一定的顺序呈现。这时候就用到了orderby语句。selectcity,count(distinctpositionId)ascountsfromDataAnalystgroupbycityorderbycounts，数据会根据统计结果升序排列，如果需要降序，则为orderbycountsdesc，后面加desc即可。如果有多个字段，用逗号分隔。下面我们来熟悉一下SQL的常用函数，首先是时间。因为我们的实践数据中没有时间，所以先创建一个时间字段是now。selectnow()直接执行得到当前系统时间，精确到秒。其实select后面不一定要跟from。selectdate(now())表示获取当前日期，week函数获取当前周，month函数获取当前月份。其余还包括，季、年、日、时、分。时间函数还包含各种参数，比如周，因为天数在中国和西方的计算方式不同。西方把星期日算作一周的第一天，而我们习惯于星期一。selectweek(now(),0)除了上面的日期表达式外，还可以计算dayofyear和weekofyear的形式。相当于上面的一些函数。如何加减时间？这时候就靠date_add函数了。selectdate_add(date(now()),interval1day)我们可以把1改成负数来达到减法的目的，也可以把日改成周、年等来进行其他时间间隔的计算。如果要查找两个时间之间的间隔，则为datediff(date1,date2)或timediff(time1,time2)。时间功能的使用更加灵活，没有特别的限制。网上也有很多文档和教程，可以深入研究。最后是数据清洗类的功能。selectleft(salary,1)fromDataAnalystMySQL支持left、right、mid等函数，这里和Excel一样。下面我们通过salary来计算一个数据分析师的薪水（这一步在之前的文章中用Excel和BI讲解过很多次，就不细说了，只说过程，不熟悉的同学可以阅读历史内容）。先用locate函数找到第k个的位置。selectlocate("k",salary),salaryfromDataAnalyst然后用left函数截取工资的下限。selectleft(salary,locate("k",salary)-1),salaryfromDataAnalyst为了获取工资的上限，使用substr函数或者mid，它们是等价的。substr(string,wheretostarttruncation,lengthoftruncation)工资帽的起始位置为“-”，往后推一位。截取长度是整个字符串减去“-”的位置，这正是我们后半段所需要的，但是这段内容包含了“K”，所以最后的结果还得减1。无所谓如果这里不明白，可以一步步运行计算过程。基本上理解了上面写的意思后，文字清理就没有问题了（不像是用来清理乱七八糟的工资，我就简单处理一下）。然后计算不同城市不同工作年限的平均工资。在上面的语句中，我们使用了文本清洗、子查询嵌套、分组聚合、排序等各种用法，都是比较复杂的查询。重复数据的问题，因为我复制了一份北京的数据，数量刚好翻了一番，对平均值没有影响。有兴趣的朋友可以多一步清理。以下是三个思考题：找出哪家公司的职位最多；O2O、电子商务、互联网金融哪个行业的平均工资最高；找出每个城市工资最高的前3名是公司的哪个职位。做完上面的题，你就已经大功告成了，数据分析的SQL观点没有太大问题。更复杂的查询无非就是嵌套更多的内容，本质思想是一样的。说到这里，只有join语法没有教给大家。由于练习数据只有一张表，join是SQL中容易混淆的难点，我单独开一个内容讲解，然后用SQLZoo和LeetCode的案例。LeetCode是一个知名的算法竞赛网站，在这里你可以与来自世界各地的程序员进行较量。当然，我们只是练习SQL。完成后，至少可以秒杀全球50%的程序员。
