SQL learning（day 1）

SELECT … FROM …

A SELECT statement gets data from a table. Each table contains rows and columns - you can SELECT some columns and ignore others

• The column names on the select line control which columns you get
• The FROM clause controls which table you access

选中table中对应的列，至于之后的WHERE是对此列数据的筛选。

模式匹配 LIKE operator

• The % is a wild-card it can match any characters
  %也可以表示空字符
  比如Cameroon可以被 '%o%o%'匹配到
• You can use the underscore as a single character wildcard.
  _可以表示单个字符， LIKE '_t%'可以匹配到Italy

FUNCTION

CONCAT

CONCAT allows you to stick two or more strings together.

This operation is concatenation.

CONCAT(s1, s2 ...)   

ROUND

ROUND(f,p) returns f rounded to p decimal places.

f为需要处理的数据，p代表要开始处理的位置所在，与数轴方向相同，四舍五入哦
比如

ROUND(7253.86, 0)    ->  7254
ROUND(7253.86, 1)    ->  7253.9
ROUND(7253.86,-3)    ->  7000

LEFT

LEFT(s,n) allows you to extract n characters from the start of the string s.
提取字符串s的前n个字符

Aggregates

The functions SUM, COUNT, MAX and AVG are “aggregates”, each may be applied to a numeric attribute resulting in a single row being returned by the query. (These functions are even more useful when used with the GROUP BY clause.)
上述的函数能够接受多个输入属性，产生一行数据，在GROUP BY中很有用。

GROUP BY and HAVING

Functions such as SUM and COUNT are applied to groups of items sharing values by including a GROUP BY clause.
When you specify GROUP BY continent the result is that you get only one row for each different value of continent. All the other columns must be “aggregated” by one of SUM, COUNT …

The HAVING clause allows use to filter the groups which are displayed. The WHERE clause filters rows before the aggregation, the HAVING clause filters after the aggregation.

HAVING和WHERE的差异在于:
1.一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
2.WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
3.WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
4.WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名

DISTINCT

通常情况下SELECT的结果可能包含很多重复的行，使用DISTINCT使得这样的重复行仅保留一行。

ORDER BY

对SELECT的结果进行排序。
We may indicate ASC or DESC for ascending (smallest first, largest last) or descending order.

TIPS

关于字符串比对：
如果字符串中有单引号',需要在此单引号前再添加一个单引号
比如

关于排序：
降序descend

当排序有多个条件时，从左到右优先级降低。

ALL的使用：
ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较

WHERE column_name comparison_operator ALL (subquery)

一些ALL的使用练习