2.1 left,locate,length,substr等函数
left函数 : left(列名, 左边的前几个字符)
locate函数 : locate(查找字符,列名,从第几个开始查找)
length函数 :length(列名)
2.1.1 简单应用
SELECT LEFT
( salary, 1 ),
LOCATE( 'k', salary ),
salary
FROM
test.dataanalyst_sql
结果如下:
2.1.2 查找薪资下限
SELECT LEFT
( salary, LOCATE( 'k', salary )- 1 ),
salary
FROM
test.dataanalyst_sql
结果如下:
2.1.3 查找薪资上下限
方法一:
SELECT LEFT
( salary, LOCATE( 'k', salary )- 1 ) AS '薪资下限',
# locate( '-', salary ),
# length( salary ),
LEFT ( RIGHT ( salary, length( salary )- locate( '-', salary )), length( salary )- locate( '-', salary )- 1 ) AS '薪资上限',
salary
FROM
test.dataanalyst_sql
结果如下:
方法二:使用substr函数
substr(列名,从那开始,查找多少位)
SELECT LEFT
( salary, LOCATE( 'k', salary )- 1 ) AS '薪资下限',
# locate( '-', salary ),
# length( salary ),
substr( salary, locate( '-', salary )+ 1, LENGTH( salary )- locate( '-', salary )- 1 ),
salary
FROM
test.dataanalyst_sql
结果如下:
2.2 SQL子查询
将之前创建的表作为新表进行查询,此时需对新表别名化,否则会报错,如将上面的表作为新表,代码如下:
SELECT
( bottom + top )/ 2,
salary
FROM
(
SELECT LEFT
( salary, LOCATE( 'k', salary )- 1 ) AS 'bottom',# locate( '-', salary ),
# length( salary ),
substr( salary, locate( '-', salary )+ 1, LENGTH( salary )- locate( '-', salary )- 1 ) AS 'top',
salary
FROM
test.dataanalyst_sql
) AS t
结果如下:
2.3 case when 函数
常用于数据清洗
SELECT
CASE
WHEN
( bottom + top )/ 2 <= 10 THEN
'0~10'
WHEN ( bottom + top )/ 2 <= 20 THEN
'10~20'
WHEN ( bottom + top )/ 2 <= 30 THEN
'20~30' ELSE '30+'
END,
salary
FROM
(
SELECT LEFT
( salary, LOCATE( 'k', salary )- 1 ) AS 'bottom',
# locate( '-', salary ),
# length( salary ),
substr( salary, locate( '-', salary )+ 1, LENGTH( salary )- locate( '-', salary )- 1 ) AS 'top',
salary
FROM
test.dataanalyst_sql
) AS t
结果如下:
2.4 where in 嵌套
SELECT
*
FROM
test.dataanalyst_sql
WHERE
city IN (
SELECT
city
FROM
test.dataanalyst_sql
GROUP BY
city
HAVING
count( positionId )>= 100)
结果如下:
注意:where中的city要与in中嵌套里的city对应,否则用*会报错。
2.5 查找唯医网数据分析信息
SELECT
*
FROM
test.dataanalyst_sql
WHERE
companyId = ANY (
SELECT
companyId
FROM
test.company_sql
WHERE
companyShortName = '唯医网')
结果如下:
2.6 join 用法
join 相当于inner join
SELECT
*
FROM
test.dataanalyst_sql AS d
JOIN test.company_sql AS c ON c.companyId = d.companyId
结果如下:
2.7 left join
用于排除化统计,排除某列条件为*****的,获取其他的数据信息
有主表次表之分,主表信息齐全,子查询表为小表,可以匹配的返回,不能匹配的为空。
举例:获取150到500人之外的公司规模的职位信息。
SELECT
*
FROM
test.dataanalyst_sql AS d
LEFT JOIN ( SELECT * FROM company_sql WHERE companySize = '150-500人' ) AS t ON t.companyId = d.companyId
WHERE
companySize IS NULL;
结果如下: