SQL 查询特征
1.子查询和连接查询
子查询(所有位置,不只是WHERE后面的子查询)可以看作是左连接操作+按两表的某些字段分组。
例子:
world表如下:
name | continent | area | population | gdp |
---|---|---|---|---|
Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
Albania | Europe | 28748 | 2831741 | 12960000000 |
Algeria | Africa | 2381741 | 37100000 | 188681000000 |
Andorra | Europe | 468 | 78115 | 3712000000 |
Angola | Africa | 1246700 | 20609294 | 100990000000 |
… |
需求1:WHERE子句的子查询
Find the largest country (by area) in each continent, show the continent, the name and the area:
使用子查询:
SELECT a.continent AS continent, a.name AS name , a.area AS area FROM world AS a
WHERE a.area > (SELECT MAX(b.area) #外查询中的WHERE和子查询的SELECT组成了分组过滤条件
FROM world AS b
WHERE a.continent = b.continent AND a.name <> b.name #子查询中的where字句可以看作连接条件
);
使用连接查询:
SELECT a.continent AS continent, a.name AS name , a.area AS area
FROM world AS a LEFT OUTER JOIN world AS b
ON a.continent = b.continent AND a.name <> b.name
GROUP BY a.name ,a.continent ,a.area
HAVING a.area > MAX(b.area);
WHERE子句后的子查询内部还有很多种形式,包括子查询需要ORDER BY ,或者GROUP BY或者LIMIT,则转成的连接查询可能有其他形式,但是都可以转为连接查询。
需求2:SELECT子句的子查询
Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.
Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.
The format should be Name, Percentage for example:
name | percentage |
---|---|
Albania | 3% |
Andorra | 0% |
Austria | 11% |
… | … |
使用子查询:
SELECT name ,CONCAT(ROUND(population/(SELECT population FROM world
WHERE name = 'Germany' #子查询中的where字句可以看作连接条件
)*100),'%') AS percentage
FROM world
WHERE continent = 'Europe'
使用连接查询:此时无HAVING子句
SELECT a.name AS a, CONCAT(ROUND(a.population*100/b.population),'%') AS percentage
FROM world AS a LEFT OUTER JOIN world AS b
ON b.name = 'Germany'
WHERE a.continent = 'Europe'
综上,可以将子查询也看作连接的特殊形式。
使用技巧:
如果一条SQL想要完成需求必须参考另一个表(可以是原表)的某些字段,则必须有连接(连接指的是逻辑层面的连接)。
根据具体情况使用子查询或者连接。
通常如果只需要原表的字段,则可以优先考虑子查询能不能完成任务。
2.数字相关函数
使用技巧
如果SELECT结果中需要往原表加入或者转换一些数字列,则一定需要使用数字函数(COUNT,SUM,MAX,MIN)等。