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