目录
字符串函数（concat、replace、left、right、substring）
学前须知
sqlzoo数据介绍
world
nobel
covid
ge
game、goal、eteam
teacher、dept
movie、casting、actor
基础语句
select&from
SELECT from WORLD Tutorial - SQLZoo
基础查询select单列&多列&所有列&别名应用
例题一
SELECT name, continent, population FROM world例题二
SELECT * FROM world例题三
SELECT name as 国家名,continent 大洲,population 人口 from world 
as是可以不加的，一般直接用空格代替
select使用distinct去重
例题四
SELECT distinct continent from world 例题五
SELECT distinct name, continent from world 
SELECT name,distinct continent from world select计算字段的使用
例题六
SELECT name,gdp,population,gdp/population 人均gdp from world总结
where
运算符
下面这种是空值
下面这种是null字符串
例题七
SELECT name,gdp/population 人均gdp from world where population >= 200000000例题八
SELECT population from world where name = 'Germany'例题九
SELECT name,population from world where name in ('Germany','Norway','Denmark')例题十
SELECT name,area FROM world
WHERE area between 250000 and 300000迷糊查询like
例题十一
SELECT name from world where name like 'C%ia' 例题十二
SELECT name from world where name like '_t%'
例题十三
SELECT name from world where name like '%o__o%' 多条件查询
例题十四
SELECT name,area from world where name like '%a%a%a%' and area >= 600000 例题十五
SELECT name,area from world
where name like '%a%a%a%'
and area >= 600000
or population > 1300000000
and area >= 5000000这里没有按顺序去执行是因为，and的优先级大于or
SELECT name,area from world
where (name like '%a%a%a%'
and area >= 600000)
or (population > 1300000000
and area >= 5000000)例题十六
SELECT name,population from world
where name = 'Germany' or name = 'Norway'or name = 'Denmark'
SELECT name,area FROM world
WHERE area >= 250000 and area <= 300000
总结
练习题
1
SELECT from Nobel Tutorial - SQLZoo
select name,population/1000000 population_in_millions from world
where continent = 'South America'2
SELECT from Nobel Tutorial - SQLZoo
select * from nobel
where yr = 1980 and subject not in ('Chemistry','Medicine')3
SELECT name FROM world
WHERE name LIKE '%a%'
and name LIKE '%e%'
and name LIKE '%i%'
and name LIKE '%o%'
and name LIKE '%u%'
AND name NOT LIKE '% %'4
select * from nobel
where (subject = 'Medicine' and yr < 1910)
or (subject = 'Literature' and yr >= 2004)order by
例题十七
SELECT from Nobel Tutorial - SQLZoo
select winner,yr,subject from nobel
where winner like 'Sir%'
order by yr desc,winner asc
总结
练习题
SELECT winner, subject FROM nobel
WHERE yr=1984
ORDER BY subject IN ('physics','chemistry'),subject,winner
limit
例题十八
SELECT name FROM world order by area desc limit 3例题十九
SELECT name FROM world order by population desc limit 3,4总结
练习题
select * from nobel limit 99,21聚合函数&group by
单独使用聚合函数
例题二十
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 Min(population) 最小人口 FROM world
where continent='Africa'
例题二十一
SELECT count(*) FROM world
例题二十二
SELECT count(name),count(continent),
count(area),count(population),
count(gdp),count(*) FROM world 
例如下面加一个name就会报错
单独使用group by
例题二十三
SELECT continent FROM world
group by continent
聚合函数和group by 联合使用
例题二十四
例题二十五
select yr,subject,count(winner) 获奖人数 from nobel
where yr between 2013 and 2015
group by yr,subject
order by yr desc,count(winner) asc总结
练习题
1
2
having
having是基于聚合运算的结果进行筛选的（是在数据透视表创建之后进行筛选的，已经进行聚合运算了，再进行的筛选）
where是聚合前进行筛选的（是在数据透视表创建之前进行筛选的）
例题二十六
例题二十七
select continent,avg(gdp) from world
where (gdp > 20000000000 and population > 60000000)
or (gdp < 8000000000 and capital like '%a%a%a%')
group by continent
having sum(population) >= 300000000
order by count(name) desc
limit 1总结
练习题
SELECT continent,population FROM world
where gdp between 20000000000 and 30000000000
group by continent
having sum(gdp)/sum(population) > 3000常见函数
数学函数（round）
字符串函数（concat、replace、left、right、substring）
数据类型转换函数(cast)
日期时间函数
year、month、day
date_add、date_sub
datediff
date_format
条件判断函数（if、case when）
例题二十八
总结
练习题
1
2
