SQL学习之路：2

select name from world
where name like '%a%'
and name like '%e%'
and name not like '% %'

查询包含ae 且无括号字段

SELECT name from world
LIMIT 3

查询返回前3行（包括0 1 2行）

SELECT name from world
LIMIT 2,3

查询返回 行2 开始取3行

SELECT yr,subject,winner
FROM nobel
where yr = 1950

SELECT winner
FROM nobel
Where yr = 1962
AND subject = 'Literature'

SELECT yr,subject
FROM nobel
WHERE winner = 'albert einstein'

字符串不区分大小写，都可识别

SELECT winner
FROM nobel
WHERE subject = 'peace'

SELECT *
FROM nobel
WHERE yr between 1980 and 1989

SELECT *
FROM nobel
WHERE yr between 1980 and 1989
order by yr desc,winner asc

排序 默认asc升序 desc降序 多字段按先后优先级排序

SELECT winner,subject,yr
FROM nobel
WHERE yr=1984
order by subject in('Chemistry','Physics'),winner
选中的关键词排序在最后面   in 括号里顺序默认升序排序

SELECT name,area from world
order by area desc
LIMIT 3

查询面积排名前三的国家和面积

SELECT name,area from world
order by area desc
LIMIT 3,4

查询面积排名第四 五 六 七的国家和面积

SELECT sum(population) 人口总数
FROM world
WHERE continent ='Africa' 忽略空值

聚合函数 求和

SELECT MAX(population) 人口总数
FROM world
WHERE continent ='Africa'

聚合函数 最大值

SELECT avg(population) 人口总数
FROM world
WHERE continent ='Africa'

聚合函数 平均值 若计算某个字段空值不计算在内，忽略空值

SELECT count(*)
FROM world
计算表格行数 若查询某个字段行数计算时，空值不计算在内，忽略空值