参考教程链接
练习教程链接
练习教程答案
select的基础用法
示例表格:
name | continent | area | population | gdp |
---|---|---|---|---|
Albania | Europe | 28748 | 2831741 | 12960000000 |
Algeria | Africa | 2381741 | 37100000 | 188681000000 |
Andorra | Europe | 468 | 78115 | 3712000000 |
Angola | Africa | 1246700 | 20609294 | 100990000000 |
… | … | … | … | … |
用select … from … 从一张table里面调出数据:
select XXX from table
其中select用大写小写都可以,不同的调取数据之间用英文逗号 隔开
select name from student
select和其他搭配使用
- where:调取名称为Germany的国家人口
SELECT population FROM world WHERE name = 'Germany'
- IN:调取名称为Sweden, Norway 和 Denmark的国家名称和人口数
SELECT name, population FROM world WHERE name IN ('Sweden', 'Norway', 'Denmark');
- between and:调取人口在200000和250000之间的国家和面积
SELECT name, area FROM world WHERE area BETWEEN 200000 AND 250000
- like:调取以Al开头的国家的名称和人口
SELECT name, population FROM world WHERE name LIKE "Al%"
- 比较运算符:>、<
SELECT name, area, population FROM world WHERE area > 50000 AND population < 10000000
- 逻辑计算
SELECT name, area*2 FROM world WHERE population = 64000
- 嵌入函数:SUM、MAX、COUNT、DISTINCT
SELECT SUM(population) FROM world
select count(name) from world where area > 1000000
SELECT DISTINCT continent from world
select count(name) from world where area > 1000000
注意这里的count、sum选取的是非空值,如果表中有一些是空值,是不会计数在内的
- 和select嵌套使用:调取人均GDP比United Kingdom多的欧洲国家名称
SELECT name FROM world
WHERE (gdp/population > ((SELECT gdp FROM world WHERE name='United Kingdom')/(SELECT population FROM world WHERE name='United Kingdom'))) and continent = 'Europe'
- ORDER BY:升序或者降序排列展示,显示获奖者、年份、科目并且获奖者名字是以“Sir”开头,先按年份降序排列,再按获奖者名字升序排列
select winner, yr, subject from nobel where winner like 'Sir%' order by yr desc, winner asc
- LIMIT:限制显示结果的行数,查询人口为第四到第七的国家和人口
select name from world order by population desc limit 3, 4
- GROUP BY:按地区调取一个地区的国家总数量
SELECT continent, COUNT(name) FROM world GROUP BY continent
- GROUP BY 和 HAVING:调取所有人口总数大于100000000的地区
SELECT continent FROM world GROUP BY continent HAVING SUM(population)>100000000
having是对聚合之后的数据进行筛选,所以它的运行是正在group by之后,而where是对聚合前字段进行筛选