SQL学习(一)——SELECT,CONCAT,CASE

先刷一遍SQL的基础操作

2020/01/29
SQLQUIZ
https://sqlzoo.net/wiki/SELECT_names/zh

• SELECT names/zh
  

5.“Columbia 哥倫比亞”是以 C 作開始,ia 作結尾的。還有兩個國家相同。
找出所有國家,其名字以 C 作開始,ia 作結尾。

SELECT name FROM world
  WHERE name LIKE 'C%ia'

7、“Bahamas 巴哈馬”中有三個 a,還有嗎?
找出所有國家,其名字包括三個或以上的a。

SELECT name FROM world
  WHERE name LIKE '%a%a%a%'

9.“Lesotho 賴索托”和”Moldova 摩爾多瓦”都有兩個字母 o,被另外兩個字母相隔着。
找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔着。

SELECT name FROM world
 WHERE name LIKE '%o__o%'

10、“Cuba古巴”和”Togo 多哥”都是 4 個字母。
找出所有國家,其名字都是 4 個字母的。

SELECT name FROM world
 WHERE name LIKE '____'

11.“Luxembourg 盧森堡”的首都 capital 都同樣叫“Luxembourg”。
顯示所有國家名字,其首都和國家名字是相同的。

select name form world
 where name = concat(capital,"")

12.“Mexico 墨西哥”的首都是”Mexico City”。
顯示所有國家名字,其首都是國家名字加上”City”。

SELECT name FROM world
 WHERE capital = concat(name, ' city')

注：不区分大小写

13.找出所有首都和其國家名字,而首都要有國家名字中出現。

select capital,name from world
 where capital like concat('%',name,'%')

14、找出所有首都和其國家名字,而首都是國家名字的延伸。
你應顯示 Mexico City,因它比其國家名字 Mexico 長。
你不應顯示 Luxembourg,因它的首都和國家名相是相同的。

select capital,name from world
 where capital like concat(name,'%') and capital != name

注：select name,capital from world
where capital like concat(name,’%_’) 效果一样

15.“Monaco-Ville"是合併國家名字 “Monaco” 和延伸詞”-Ville".
顯示國家名字，及其延伸詞，如首都是國家名字的延伸。

select name,replace(capital,name,'') from world 
 where capital like concat(name,'%_')

replace

update 'article' set title = replace(title,'w3cschool','hello');
//把title字段中的w3cschool替换成hello

注：’%’ 如果没有’__’(下划线)，则name后无字符也可匹配

2020/01/30
https://sqlzoo.net/wiki/SQLZOO:SELECT_from_WORLD_Tutorial/zh

8.美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。
顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。

select name,population,area from world
 where (area > 3000000 and population <= 250000000) or (area <= 3000000 and population > 250000000)

10.顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。
顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000。

select name,round(gdp/population,-3) from world
 where gdp>1000000000000

ROUND() 函数用于把数值字段舍入为指定的小数位数。
11.Show the name - but substitute Australasia for Oceania - for countries beginning with N.
展示那些国家名称以N开头的，国家名称，大陆 用Australasia 替换Oceania

SELECT name,CASE WHEN continent='Oceania' THEN 'Australasia'
            ELSE continent END
  FROM world
 WHERE name LIKE 'N%'

CASE使用方法
12.Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B

select name,CASE WHEN continent in ('Europe','Asia') THEN 'Eurasia'
                 WHEN continent in ('North America', 'South America','Caribbean') THEN 'America'
                 ELSE continent END
  FROM world
 WHERE name LIKE 'A%' or name LIKE 'B%'

13.Put the continents right…
Oceania becomes Australasia
Countries in Eurasia and Turkey go to Europe/Asia
Caribbean islands starting with ‘B’ go to North America, other Caribbean islands go to South America
Show the name, the original continent and the new continent of all countries.

select name,continent,CASE WHEN continent = 'Oceania' THEN 'Australasia'
                           WHEN continent in ('Eurasia','Turkey') THEN 'Europe/Asia'
                           WHEN continent = 'Caribbean' and name LIKE 'B%' THEN 'North America' 
                           WHEN continent = 'Caribbean'and name not LIKE 'B%' THEN 'South America'
                           ELSE continent END
from world