【SQL入门系列一】SQLZOO-SELECT

0 SELECT basics

WHERE

SELECT population FROM world
WHERE name = 'Germany'

IN list中的多项是否存在

SELECT name, population FROM world
  WHERE name IN ('Sweden', 'Norway', 'Denmark');

BETWEEN 范围

SELECT name, area FROM world
  WHERE area BETWEEN 200000 AND 250000

1 SELECT name

LIKE
模糊查询

// 以B开头
SELECT name FROM world
  WHERE name LIKE 'B%'
  
// 以B结尾
SELECT name FROM world
  WHERE name LIKE '%B'
  
// 包含x
SELECT name FROM world
  WHERE name LIKE '%x%'
  
SELECT name FROM world
  WHERE name LIKE 'C%ia'
  
// name中有连续两个o
SELECT name FROM world
  WHERE name LIKE '%oo%'
  
// name中有三个及以上的a
SELECT name FROM world
  WHERE name LIKE '%a%a%a%'

_
表示单个字符的通配符

// 第二个字母为t
SELECT name FROM world
 WHERE name LIKE '_t%'
ORDER BY name

concat
组合多个字符串

//capital = name+City
SELECT name FROM world
 WHERE capital = concat(name,' City')

//capital中包含name
SELECT capital, name FROM world
WHERE capital LIKE concat('%',name,'%')

//capital是name的延伸，且capital和name不一样
SELECT capital, name FROM world
WHERE capital LIKE concat(name,'%') AND capital!=name

SELECT capital, name FROM world
WHERE capital LIKE concat(name,'_%') //_至少有一个字符

REPLACE(f, s1, s2)
把f中的s1替换为s2

2 SELECT from world

XOR？？？

ROUND function
ROUND(population/1000000,2)保留两位小数

// per-capita GDP 最接近的1000
SELECT name,ROUND(GDP/population,-3) FROM world
WHERE GDP>=1000000000000

LENGTH function
字符数

LEFT
LEFT(s,n) 左边的n个字符
<>表示不等于

// 包括所有的元音，但是不包括空格
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 '% %'

3 SELECT from Nobel

4 SELECT within SELECT

//population大于Russia的国家name
SELECT name FROM world
 WHERE population >
    (SELECT population FROM world
     WHERE name='Russia')

List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

SELECT name,continent FROM world
WHERE continent in 
    (SELECT continent FROM world
       WHERE name in ('Argentina','Australia'))
ORDER by name

BETWEEN 是闭区间

SELECT name,population FROM world
WHERE population BETWEEN
(SELECT population FROM world
 WHERE name='Canada') 
AND 
(SELECT population FROM world
 WHERE name='Poland') 

SELECT name,population FROM world
WHERE population >
(SELECT population FROM world
 WHERE name='Canada') 
AND population <
(SELECT population FROM world
 WHERE name='Poland') 

ALL
Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

SELECT name 
FROM world
WHERE GDP > =ALL(SELECT GDP
                FROM world
               WHERE continent='Europe' AND gdp>0)

Find the largest country (by area) in each continent, show the continent, the name and the area:

SELECT continent,name,area FROM world x
 WHERE area>= ALL
   (SELECT area FROM world y
       WHERE y.continent=x.continent
         AND area>0)