MYSQL从入门到精通

【转发】

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

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

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

select column from table

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

注意：标点符号必须为英文，这是新人很容易犯的错误。

1 条件判断：where，and，or

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

SELECT * FROM DataAnalyst WHERE city = '北京'

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

1. SELECT * FROM DataAnalyst

2. WHERE city = '北京' AND positionName = '数据分析师'

or语句则是或的关系

1. SELECT * FROM DataAnalyst

2. WHERE city = '北京' OR positionName = '数据分析师'

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

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

1. SELECT * FROM DataAnalyst

2. WHERE (city='北京' AND positionName='数据分析师') OR (city = '北京' AND positionName = '数据产品经理')

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

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

1. SELECT * FROM DataAnalyst

2. WHERE city IN('北京','上海','广州','深圳')

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

SELECT * FROM DataAnalyst WHERE companyId > 10000

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

当我们需要取区间数值时，使用between and

1. SELECT * FROM DataAnalyst

2. WHERE companyId BETWEEN 10000 AND 10500

between and包括数值两端的边界，等同于companyId >=10000 and companyId <= 10500。

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

1. SELECT * FROM DataAnalyst

2. WHERE positionName LIKE '%分析师%'

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

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

2 分组：group by

接下来我们学习group by，它是数据分析中常见的语法，目的是将数据按组/维度划分，我们以city为例：

1. SELECT * FROM DataAnalyst

2. GROUP BY city

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

1. SELECT city,COUNT(1) FROM DataAnalyst

2. GROUP BY city

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

1. SELECT city,count(DISTINCT positionId) FROM DataAnalyst

2. GROUP BY city

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

除了count，还有max、min、sum、avg等函数，也叫做聚合函数。

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

1. SELECT city,workYear,count(DISTINCT positionId) FROM DataAnalyst

2. GROUP BY city,workYear

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

3 逻辑判断

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

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

1. SELECT IF(industryField like '%电子商务%',1,0)

2. FROM DataAnalyst

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

1. SELECT city,

2. COUNT(DISTINCT positionId),

3. COUNT(IF(industryField like '%电子商务%',positionId,NULL))

4. FROM DataAnalyst

5. GROUP BY city

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

Q：如果我想找出各个城市，数据分析师岗位数量在500以上的城市有哪些，应该怎么计算？

有两种方法：

第一种，是使用having语句，它对聚合后的数据结果进行过滤。

1. SELECT city,

2. COUNT(DISTINCT positionId) AS counts

3. FROM DataAnalyst

4. GROUP BY city HAVING counts >= 500

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

1. SELECT * FROM(

2. SELECT city,COUNT(DISTINCT positionId) as counts

3. FROM DataAnalyst

4. GROUP BY city

5. ) as t1

6. WHERE t1.counts >= 500

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

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

4 order by

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

1. SELECT city,COUNT(DISTINCT positionId) as counts

2. FROM DataAnalyst

3. GROUP BY city

4. ORDER BY counts

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

5 SQL常用函数

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

select now()

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

select date(now())

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

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

select week(now(),0)

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

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

select date_add(date(now()) ,interval 1 day)

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

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

6 清洗类函数

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

select left(salary,1) from DataAnalyst

MySQL支持left、right、mid等函数。

我们通过salary计算数据分析师的工资吧，首先利用locate函数查找第一个k所在的位置。

SELECT LOCATE('k',salary),salary FROM DataAnalyst

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

1. SELECT LEFT(salary,LOCATE('k',salary)-1),salary

2. FROM DataAnalyst

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

SUBSTR(str,pos,len)

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

1. SELECT

2. LEFT(salary,LOCATE('k', salary)-1) as bottomSalary,

3. SUBSTR(salary, LOCATE('-', salary) + 1, LENGTH(salary) - LOCATE('-', salary) - 1) as topSalary,

4. salary

5. FROM DataAnalyst

6. WHERE salary NOT LIKE '%以上%'

这里不了解不要紧，可以将计算过程分步骤运行。基本上，了解了上面写法的含义，文本清洗这块就没有问题了。再然后计算不同城市不同工作年限的平均薪资。

1. SELECT city,

2. workYear,

3. AVG((bottomSalary + topSalary) / 2) as avgSalary

4. FROM(

5. SELECT

6. LEFT(salary,LOCATE('k', salary)-1) as bottomSalary,

7. SUBSTR(salary, LOCATE('-', salary) + 1, LENGTH(salary) - LOCATE('-', salary) - 1) as topSalary,

8. city,positionId,workYear

9. FROM DataAnalyst

10. WHERE salary NOT LIKE '%以上%'

11. ) as t1

12. GROUP BY city,workYear

13. ORDER BY city,avgSalary