SQL基础学习笔记＜第二天＞

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;

结果如下: