[DAY3]SQL新手的懒人笔记

[DAY3]SQL的新手懒人笔记 (大写为内建语法)

21.ORDER BY可用来排序资料，如果是字串变数会照A~Z去排序，若开头有数字，则排在A之前。若是数字变数，则由小排到大。

SELECT title
FROM films
ORDER BY release_year DESC;

22.在ORDER BY的变数之後加上DESC，则资料会反向排序。(由大到小)

SELECT name
FROM people
ORDER BY name DESC;

23.GROUP BY 是所有整理资料的语言中，最重要的语法之一，因为可以利用GROUP BY照自己希望的方式把资料做分组。以下则是把资料按照sex这个变数去做分组，再去output出自己想要的运算与结果。

SELECT sex, count(*)
FROM employees
GROUP BY sex;

24.HAVING的语法与WHERE相似，但是我们会在有GROUP BY的时候使用HAVING，使用WHERE的话，程序码会无法运行。

SELECT release_year
FROM films
GROUP BY release_year
HAVING COUNT(title) > 10;

25.LIMIT 後面接上数字，表示写列出多少个资料。

SELECT country, avg(budget) AS avg_budget, avg(gross) AS avg_gross
FROM films  
GROUP BY country
HAVING COUNT(title) > 10
ORDER BY country
LIMIT 5;

26.FROM ... INNER JOIN可以把两个不同的table合并在一起，合并的方式取决於ON後面，来自不同table的同样意涵的variable。(在叫某table的variable时，需用"."座连接，ex:table1.variable = table2.variable)

SELECT * 
FROM cities
  INNER JOIN countries
    ON cities.country_code = countries.code;

27.在INNER JOIN之後可以用AS设定简写，如此一来在SELECT之後就可以使用较为简短的语法去描述。

SELECT c.code AS country_code, name, year, inflation_rate
FROM countries AS c
  INNER JOIN economies AS e
    ON c.code = e.code;

28.当两个table有相同名称的变数可以做合并时，我们可以用USING去省略语法。

SELECT c.name AS country, continent, l.name AS language, official
  From countries AS c
  INNER JOIN languages AS l
    USING(code)

29.以下INNER JOIN的语法可称为SELF JOIN，意思是把自己与自己做合并，也就是把想要变数的所有组合的可能性都给列出来。

SELECT p1.country_code, p1.size AS size2010, p2.size AS size2015
From populations AS p1
  INNER JOIN populations AS p2
    USING(country_code);

30.INNER JOIN ... ON 之後也可加入一些想要的条件(布林运算)，去得到你想要的资料。

SELECT p1.country_code,
       p1.size AS size2010, 
       p2.size AS size2015,
       ((p2.size - p1.size)/p1.size * 100.0) AS growth_perc
FROM populations AS p1
  INNER JOIN populations AS p2
    ON p1.country_code = p2.country_code
        AND p1.year = p2.year - 5;