SQL 语句易错点讲解
1.NULL 值不要用 '= , != ' 与具体值进行比较.
请用 IS NULL/ IS NOT NULL
比如: 哪些国家的GDP 比在欧洲任何一个国家的GDP都高
错误示范"
SELECT name FROM bbc WHERE gdp > ALL (SELECT gdp FROM bbc WHERE region = 'Europe' AND gdp<>NULL);
用 gpd <> NULL 这样是无法排除 gdp 是 NULL的情况
正确做法:
SELECT name FROM bbc WHERE gdp > ALL (SELECT gdp FROM bbc WHERE region = 'Europe' AND gdp>0);
或者用 gdp IS NOT NULL 来表示
另外: NULL = NULL 这样比较是没有用的
2.WHERE 和 HAVING 分不清楚
WHERE 过滤行,而HAVING 过滤分组
给出两个例子分别HAVING 和WHERE
表格格式:
world(name, continent, area, population, gdp)
例1: 每个大陆有多少个 人口在200000000的国家.
SELECT continent,count(name) FROM world WHERE population > 200000000 GROUP BY continent
例2; 展示总人口在500000000的大陆
SELECT continent,sum(population) FROM world GROUP BY continent HAVING sum(population) >= 500000000;
从上面的例子,我们可以 把 WHERE 比作过滤发生在分组前,而HAVING 是分组后进行过滤
比如WHERE 先把人口 200000000 的 行 先过滤了,而HAVING是在分组之上,把组内不符合条件的 数据 过滤
另外一个例子: 我想要知道总和面积大于
BBC表格:
15000000 的区域 和 区域总和面积
SELECT region, SUM(area) FROM bbc WHERE SUM(area) > 15000000 GROUP BY region
WHERE 无法根据区域总和来过滤行.
正确做法应该是:
SELECT region, SUM(area) FROM bbc GROUP BY region HAVING SUM(area) > 15000000
3.LEFT JOIN, JOIN, RIGHT JOIN
题目:http://zh.sqlzoo.net/wiki/The_JOIN_operation
中的13题
错误代码:
SELECT mdate, team1, sum(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) score1, team2, sum(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) score2 FROM game JOIN goal ON matchid = id group by mdate,team1,team2
原因:遗漏了没有进球的场数,应该用LEFT JOIN
正确代码:
SELECT mdate, team1, sum(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) score1, team2, sum(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) score2 FROM game LEFT JOIN goal ON matchid = id group by mdate,team1,team2