SQL基础教程之函数

函数

• 语法：SELECT "函数名"("栏位名") FROM "表格名"

• COUNT ： COUNT 让我们能够数出在表格中有多少笔资料被选出来

  例子：SELECT COUNT(store_name) FROM Store_Information WHERE store_name is not NULL

  COUNT 和 DISTINCT 经常被合起来使用，目的是找出表格中有多少笔不同的资料 (至于这些资料实际上是什么并不重要)。

  SELECT COUNT(DISTINCT store_name) FROM Store_Information

• GROUP BY ： 我们现在回到函数上。记得我们用 SUM 这个指令来算出所有的 Sales (营业额)吧！如果我们的需求变成是要算出每一间店 (store_name) 的营业额 (sales)，那怎么办呢？在这个情况下，我们要做到两件事：第一，我们对于 store_name 及 Sales 这两个栏位都要选出。第二，我们需要确认所有的 sales 都要依照各个 store_name 来分开算。这个语法为：

  SELECT "栏位1", SUM("栏位2") FROM "表格名" GROUP BY "栏位1"

  当我们选不只一个栏位，且其中至少一个栏位有包含函数的运用时，我们就需要用到 GROUP BY 这个指令。在这个情况下，我们需要确定我们有 GROUP BY 所有其他的栏位。换句话说，除了有包括函数的栏位外，我们都需要将其放在 GROUP BY 的子句中。

  例子：

  源数据
  STORE_NAME	SALES	DAT	STR
          A	1500	3	啊啊
  	A	1300	3	我
  	B	1500	2	啊啊
  	C	1600	1	大
  	B	1700	1	散打
  	C	1800	1	大
  
  ----分组求函数，select 使得你需要显示那些数据，与分组，语法无法
  select store_name,avg(dat) from Store_Information group by store_name
  ---OK
  select avg(dat) from Store_Information group by str
  ---OK
  SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name
  ---OK
  select store_name,sum(sales),avg(dat) from Store_Information group by store_name,str
  ---OK
  select store_name,str,sum(sales),avg(dat) from Store_Information group by store_name,str
  ---OK
  

• HAVING ： 对函数产生的值来设定条件

  语法：SELECT "栏位1", SUM("栏位2") FROM "表格名" GROUP BY "栏位1" HAVING (函数条件)

  HAVING 子句通常是在一个 SQL 句子的最后。一个含有 HAVING 子句的 SQL 并不一定要包含 GROUP BY 子句。

  例子：select store_name,sum(sales) from Store_Information group by store_name having sum(sales)>3000

• ALIAS ： 别名

  最常用到的别名有两种：栏位别名及表格别名。

  语法：SELECT "表格别名"."栏位1" "栏位别名" FROM "表格名" "表格别名"

  基本上，这两种别名都是放在它们要替代的物件后面，而它们中间由一个空白分开。例子： SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales" FROM Store_Information A1 GROUP BY A1.store_name

• 表格链接

  现在我们介绍连接(join)的概念。要了解连接，我们需要用到许多我们之前已介绍过的指令。我们先假设我们有以下的两个表格，

  Store_Information 表格:

  store_name	Sales	Date
  Los Angeles	$1500	Jan-05-1999
  San Diego	$250	Jan-07-1999
  Los Angeles	$300	Jan-08-1999
  Boston	$700	Jan-08-1999

  Geography 表格 :

  region_name	store_name
  East	Boston
  East	New York
  West	Los Angeles
  West	San Diego

  而我们要知道每一区 (region_name) 的营业额 (sales)。 Geography 这个表格告诉我们每一区有哪些店，而 Store_Information 告诉我们每一个店的营业额。若我们要知道每一区的营业额，我们需要将这两个不同表格中的资料串联起来。当我们仔细了解这两个表格后，我们会发现它们可经由一个相同的栏位，store_name，连接起来。我们先将 SQL 句列出，之后再讨论每一个子句的意义：

  SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM Geography A1, Store_Information A2 WHERE A1.store_name = A2.store_name GROUP BY A1.region_name

  结果:

  REGION	SALES
  East	$700
  West	$2050

  在第一行中，我们告诉 SQL 去选出两个栏位：第一个栏位是 Geography 表格中的 region_name 栏位 (我们取了一个别名叫做 REGION)；第二个栏位是 Store_Information 表格中的 sales 栏位 (别名为 SALES)。请注意在这里我们有用到表格别名：Geography 表格的别名是 A1，Store_Information 表格的别名是 A2。若我们没有用表格别名的话，第一行就会变成

  SELECT Geography.region_name REGION, SUM(Store_Information.Sales) SALES

  很明显地，这就复杂多了。在这里我们可以看到表格别名的功用：它能让 SQL 句容易被了解，尤其是这个 SQL 句含盖好几个不同的表格时。

  接下来我们看第三行，就是 WHERE 子句。这是我们阐述连接条件的地方。在这里，我们要确认 Geography 表格中 store_name 栏位的值与 Store_Information 表格中 store_name 栏位的值是相等的。这个 WHERE 子句是一个连接的灵魂人物，因为它的角色是确定两个表格之间的连接是正确的。如果 WHERE 子句是错误的，我们就极可能得到一个笛卡儿连接 (Cartesian join)。笛卡儿连接会造成我们得到所有两个表格每两行之间所有可能的组合。在这个例子中，笛卡儿连接会让我们得到 4 x 4 = 16 行的结果。

• 外部链接

  之前我们看到的左连接 (left join)，又称内部连接 (inner join)。在这个情况下，要两个表格内都有同样的值，那一笔资料才会被选出。那如果我们想要列出一个表格中每一笔的资料，无论它的值在另一个表格中有没有出现，那该怎么办呢？在这个时候，我们就需要用到 SQL OUTER JOIN (外部连接) 的指令。

  假设我们有以下的两个表格： Store_Information 表格

  store_name	Sales	Date
  Los Angeles	$1500	Jan-05-1999
  San Diego	$250	Jan-07-1999
  Los Angeles	$300	Jan-08-1999
  Boston	$700	Jan-08-1999

  Geography表格

  region_name	store_name
  East	Boston
  East	New York
  West	Los Angeles
  West	San Diego

  我们需要知道每一间店的营业额。如果我们用一个普通的连接，我们将会漏失掉 'New York'这个店，因为它并不存在于 Store_Information 这个表格。所以，在这个情况下，我们需要用外部连接来串联这两个表格：

  SELECT A1.store_name, SUM(A2.Sales) SALES FROM Georgraphy A1, Store_Information A2 WHERE A1.store_name = A2.store_name (+) GROUP BY A1.store_name

  我们在这里是使用了 Oracle 的外部连接语法。

  结果:

  store_name	SALES
  Boston	$700
  New York
  Los Angeles	$1800
  San Diego	$250

  请注意： 当第二个表格没有相对的资料时， SQL 会传回 NULL 值。在这一个例子中， 'New York' 并不存在于 Store_Information 表格，所以它的 "SALES" 栏位是 NULL.

• CONCATENATE ： 将由不同栏位获得的资料串连在一起

  MySQL: CONCAT()

  Oracle: CONCAT(), ||

  SQL Server: +

  语法：CONCAT(栏位名1, 栏位名2, 栏位名3, ...): 栏位名、栏位名、栏位名，等栏位名连在一起。请注意，Oracle的CONCAT()只允许两个参数；换言之，一次只能将两个字符串串连起来。不过，在Oracle中，我们可以用'||'来一次串连多个字符串。

• SUBSTRING ： 抓出一个栏位资料中的其中一部分 ，类似String的subString

  MySQL: SUBSTR(), SUBSTRING()

  Oracle: SUBSTR()

  SQL Server: SUBSTRING()

  最常用到的方式如下 (在这里我们用SUBSTR()为例)：

  SUBSTR(str,pos): 由str中，选出所有从第pos位置开始的字符。请注意，这个语法不适用于SQL Server上。

  SUBSTR(str,pos,len): 由str中的第pos位置开始，选出接下去的个字符。

• TRIM : 用来移除掉一个字符串中的字头或字尾。最常见的用途是移除字首或字尾的空白???

  MySQL: TRIM(), RTRIM(), LTRIM()

  Oracle: RTRIM(), LTRIM()

  SQL Server: RTRIM(), LTRIM()

  各种 trim 函数的语法如下：

  TRIM([[位置] [要移除的字符串] FROM ] 字符串): [位置] 的可能值为 LEADING (起头), TRAILING (结尾), or BOTH (起头及结尾)。 这个函数将把 [要移除的字符串] 从字符串的起头、结尾，或是起头及结尾移除。如果我们没有列出 [要移除的字符串] 是什么的话，那空白就会被移除。

  LTRIM(字符串): 将所有字符串起头的空白移除。

  RTRIM(字符串): 将所有字符串结尾的空白移除。