学前须知
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
