数据库(一) | SQL | 面试所需基础SQL知识数据围绕着我们的生活，我们一般都把数据写在纸上并放入储物柜，但是最

前言

什么是SQL

数据围绕着我们的生活，我们一般都把数据写在纸上并放入储物柜，但是最终我们都需要把它们存储在线上，我们管线上的储物柜就叫做数据库。我们如何方便快速地从数据库拉取数据呢？答案是使用SQL（Structured Query Language），这是一种与数据库进行通信的语言。

为什么学

5G时代即将到来，新基建的东风吹向工业互联网，未来AI、云计算与大数据相关人才需求会越来越大，而这些工作都离不开对数据的收集，处理，解读。可以肯定地说几乎所有与数据有关的工作都会有涉及到SQL的面试题，如果SQL都做不出来的话，那么后续的面试基本也就凉了。在这个数据为王的时代，SQL是每一个开发人员都必须要掌握的基础技能，我们不光是要学会SQL的语法，更要学会SQL在处理数据时候的思想和模型，体会在面对实际问题编写SQL的时候需要运用的分析数据的思想，增强对数据处理的理解，更好地为即将到来的5G时代做好准备。

你会学到什么

本篇作为数据库系列的第一篇文章，会从以下3个方面层层递进地讲解。

数据库&表
SQL子句、函数与谓词
SQL执行顺序

看完本篇文章后你将掌握使用数据库与SQL处理数据的思维并掌握大部分常用的SQL基础知识，具备应对大部分简单数据处理场景的能力。

应对复杂的数据处理场景与一些常用的思维，会在本系列的第二篇SQL | 面试所需高阶SQL知识——复杂查询与常用模型中进行介绍与总结。

基础知识

数据库&表

数据库是存放数据的地方，就像excel。数据库由多张表组成，每张表就像excel中的一个sheet，只不过数据库的表之间是有联系的，数据库=表+表之间的关系。

主键&外键

主键是表里面每一条数据的唯一标识。
主键不能包含重复值和空值。
外健是当前表和其他表产生联系的桥梁，可能是其他表的主键（也可能不是）。
外键可以包含重复值和空值。我们把大表A拆成两张表来进行存储，那么customerId在表C中就是主键，在表B中的作用是用来和表C进行关联，所以customerId在表B中就是外键。

基本语法规则

如果没有SQL的基础，可以跳过这个章节，忘记了语法的话可以回到这部分当作工具书去查询。

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

笛卡尔积

有序对

(a , b) 顾名思义由一对数据a, b组成，我们需要记住两点就行了

它是一对数据（两个）。
需要考虑顺序，即 (a , b) 不等于 (b , a)。

笛卡尔积

笛卡尔积 A $\times$ B 就是所有有序对 (a , b) 的集合，其中a $\in$ A，b $\in$ B，即A $\times$ B = { (a, b) $\vert$ a $\in$ A，b $\in$ B }

例如： A = {1, 2, 3} B = {4, 5, 6} A $\times$ B ={(1,4), (1,5), (1,6), (2,4), (2,5), (2,6), (3,4), (3,5), (3, 6)}

SQL子句

SELECT FROM

SELECT用于告诉数据库我们想得到哪些列的数据。
FROM后面接表名，告诉数据库我们想从哪张表中获取数据。如果FROM后面有多张表则使用','隔开->FROM t1,t2,t3。此时SQL语句从由t1,t2,t3的笛卡尔积构成的临时表中提取数据。

例：

表stadium：

+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
+------+------------+-----------+

SQL语句：

SELECT t1.*, t2.*, t3.* FROM stadium t1, stadium t2, stadium t3

结果: stadium表和自己笛卡尔积运算结果： $2^3=8$ 行数据

+------+------------+--------+------+------------+--------+------+------------+--------+
| id   | visit_date | people | id   | visit_date | people | id   | visit_date | people |
+------+------------+--------+------+------------+--------+------+------------+--------+
|    1 | 2017-01-01 |     10 |    2 | 2017-01-02 |    109 |    1 | 2017-01-01 |     10 |
|    2 | 2017-01-02 |    109 |    2 | 2017-01-02 |    109 |    1 | 2017-01-01 |     10 |
|    1 | 2017-01-01 |     10 |    1 | 2017-01-01 |     10 |    1 | 2017-01-01 |     10 |
|    2 | 2017-01-02 |    109 |    1 | 2017-01-01 |     10 |    1 | 2017-01-01 |     10 |
|    1 | 2017-01-01 |     10 |    2 | 2017-01-02 |    109 |    2 | 2017-01-02 |    109 |
|    2 | 2017-01-02 |    109 |    2 | 2017-01-02 |    109 |    2 | 2017-01-02 |    109 |
|    1 | 2017-01-01 |     10 |    1 | 2017-01-01 |     10 |    2 | 2017-01-02 |    109 |
|    2 | 2017-01-02 |    109 |    1 | 2017-01-01 |     10 |    2 | 2017-01-02 |    109 |
+------+------------+--------+------+------------+--------+------+------------+--------+

WHERE

WHERE告诉数据库SELECT选出的数据中，我们想要筛选出哪些行的数据。
WHERE后面可以接逻辑运算符（'='，'<>','<','>','>=','<=','BETWEEN','Like','IN', etc）。

例：

表stadium：

+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
+------+------------+-----------+

SQL语句：

SELECT * FROM stadium WHERE id=2

结果：

+------+------------+--------+
| id   | visit_date | people |
+------+------------+--------+
|    2 | 2017-01-02 |    109 |
+------+------------+--------+

GROUP BY

GROUP BY依据选定的列内的内容，将数据分到不同的组内。
并可以搭配聚合函数(aggregate functions)使用，对每个组内的数据分别进行聚合。
Aliase 可以帮助我们给返回的结起一个方便理解的别名，别名前使用AS。例：表stadium：

+------+------------+-----------+-----------+
| id   | visit_date | province  | people    |
+------+------------+-----------+-----------+
| 1    | 2017-01-01 | Jiangsu   | 10        |
| 2    | 2017-01-02 | Henan     | 109       |
| 3    | 2017-01-02 | Liaoning  | 109       |
| 4    | 2017-01-03 | Liaoning  | 110       |
| 5    | 2017-01-04 | Jiangsu   | 120       |
| 6    | 2017-01-04 | Henan     | 200       |
+------+------------+-----------+-----------+

SQL语句：

SELECT visit_date, SUM(people) AS total_people FROM stadium GROUP BY visit_date

结果：

+------------+--------------+
| visit_date | total_people |
+------------+--------------+
| 2017-01-01 |           10 |
| 2017-01-02 |          218 |
| 2017-01-03 |          110 |
| 2017-01-04 |          320 |
+------------+--------------+

GROUP BY执行过程 GROUP BY执行过程主要分成三个步骤，分组、聚合与汇总。

Group 还有一种特殊的写法，Group【num】

SELECT account_id, open_emp_id
         ^^^^        ^^^^
          1           2

FROM account
GROUP BY 1;

在上面的查询中GROUP BY 1指的是first column in select statement那就是account_id，一般工作中不建议这么写，但是网上看到别人写的sql语句的时候要懂。

HAVING & Order BY

HAVING必须搭配GROUP BY一起使用,可作用于GROUP BY分组后的组内数据，对其进行进一步筛选。
Order By用于对结果进行排序，默认是升序，也通过DESC指定降序。
Order By后面如果接多个列名的话，那么按照列名的先后顺序依次排序。

例：

表stadium：

+------+------------+-----------+-----------+
| id   | visit_date | province  | people    |
+------+------------+-----------+-----------+
| 1    | 2017-01-01 | Jiangsu   | 10        |
| 2    | 2017-01-02 | Henan     | 109       |
| 3    | 2017-01-02 | Liaoning  | 109       |
| 4    | 2017-01-03 | Liaoning  | 110       |
| 5    | 2017-01-04 | Jiangsu   | 120       |
| 6    | 2017-01-04 | Henan     | 200       |
+------+------------+-----------+-----------+

SQL语句：

SELECT visit_date FROM stadium GROUP BY visit_date HAVING COUNT(*) >= 2

结果：

+------------+
| visit_date | 
+------------+
| 2017-01-02 |
| 2017-01-04 |
+------------+

SQL语句：

SELECT id, visit_date, province, people FROM stadium ORDER BY people DESC

结果：

+------+------------+-----------+-----------+
| id   | visit_date | province  | people    |
+------+------------+-----------+-----------+
| 6    | 2017-01-04 | Henan     | 200       |
| 5    | 2017-01-04 | Jiangsu   | 120       |
| 4    | 2017-01-03 | Liaoning  | 110       |
| 2    | 2017-01-02 | Henan     | 109       |
| 3    | 2017-01-02 | Liaoning  | 109       |
| 1    | 2017-01-01 | Jiangsu   | 10        |
+------+------------+-----------+-----------+

INNER JOIN & LEFT JOIN & RIGHT JOIN & FULL OUTER JOIN

我们用JOIN ON来告诉数据库，两张表应该以怎样的方式进行横行连接。例：

表stadium：

+------+------------+-----------+-----------+
| id   | visit_date | provinceId  | people  |
+------+------------+-----------+-----------+
| 1    | 2017-01-01 | 1         | 10        |
| 2    | 2017-01-02 | 2         | 109       |
| 3    | 2017-01-02 | 3         | 109       |
+------+------------+-----------+-----------+

表province:

+------+------------+
|id    | name       |
+------+------------+
| 1    | Jiangsu    |
| 2    | Henan      |
+------+------------+

SQL语句(INNER JOIN)：

SELECT stadium.id AS id, stadium.visit_date AS visit_date, stadium.provinceId AS  provinceId, province.name AS provinceName, stadium.people AS people FROM stadium INNER JOIN province ON stadium.provinceId=province.id

结果：

+------------+------------+-----------+-------------+-----------+
| id         |visit_date  |provinceId | provinceName|people     |
+------------+------------+-----------+-------------+-----------+
| 1          | 2017-01-01 | 1         | Jiangsu     |10         |
| 2          | 2017-01-02 | 2         | Henan       |109        |
+------------+------------+-----------+-------------+-----------+

SQL语句(LEFT JOIN)：

SELECT stadium.id AS id, stadium.visit_date AS visit_date, stadium.provinceId AS  provinceId, province.name AS provinceName, stadium.people AS people FROM stadium LEFT JOIN province ON stadium.provinceId=province.id

结果：

+------------+------------+-----------+------------+-----------+
| id         |visit_date  |provinceId |provinceName|people     |
+------------+------------+-----------+------------+-----------+
| 1          | 2017-01-01 | 1         | Jiangsu    |10         |
| 2          | 2017-01-02 | 2         | Henan      |109        |
| 3          | 2017-01-02 | 3         | <NA>       |109        |
+------------+------------+-----------+------------+-----------+

SQL语句(RIGHT JOIN)：

表province:

+------+------------+
|id    | name       |
+------+------------+
| 1    | Jiangsu    |
| 4    | Jinan      |
+------+------------+

SELECT stadium.id AS id, stadium.visit_date AS visit_date, stadium.provinceId AS  provinceId, province.name AS provinceName, stadium.people AS people FROM stadium RIGHT JOIN province ON stadium.provinceId=province.id

结果：

+------------+------------+-----------+------------+-----------+
| id         |visit_date  |provinceId |provinceName|people     |
+------------+------------+-----------+------------+-----------+
| 1          | 2017-01-01 | 1         | Jiangsu    |10         |
| <NA>       | <NA>       | <NA>      | Jinan      |<NA>       |
+------------+------------+-----------+------------+-----------+

Outer Join: Outer Join用的很少，结果集就是LEFT JOIN和RIGHT JOIN的并集。

UNION

UNION 用于将两个表相同列的数据纵向连接。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
UNION会去重，如果想保留重复的行可以使用UNION ALL。例：

表：o_stadium

+------+------------+-----------+-----------+
| id   | visit_date | province  | people    |
+------+------------+-----------+-----------+
| 1    | 2017-01-01 | Jiangsu   | 10        |
| 2    | 2017-01-02 | Henan     | 109       |
| 3    | 2017-01-02 | Liaoning  | 109       |
+------+------------+-----------+-----------|

表：n_stadium

+------+------------+-----------+-----------+
| id   | visit_date | province  | people    |
+------+------------+-----------+-----------+
| 1    | 2018-01-01 | Hebei     | 10        |
| 2    | 2018-01-02 | Jilin     | 109       |
| 3    | 2018-01-02 | Liaoning  | 109       |
+------+------------+-----------+-----------|

SQL语句：

SELECT visit_date, province, people FROM o_stadium UNION SELECT visit_date, province, people FROM n_stadium

结果：

+------------+-----------+-----------+
| visit_date | province  | people    |
+------------+-----------+-----------+
| 2017-01-01 | Jiangsu   | 10        |
| 2017-01-02 | Henan     | 109       |
| 2017-01-02 | Liaoning  | 109       |
| 2018-01-01 | Hebei     | 10        |
| 2018-01-02 | Jilin     | 109       |
+------------+-----------+-----------+

LIMIT/OFFSET

跳过OFFSET，获取LIMIT行记录
有两种写法
select * from 表名 limit {offsetnum},{limitnum} —— 推荐
select * from 表名 limit {num} offset {num}

假设数据库中存在15条数据：

select * from 表名 limit 9, 4 或者 select * from 表名 limit 4 offset 9 返回第10， 11，12，13 四行数据

子查询

子查询（Sub Query）或者说内查询（Inner Query），也可以称作嵌套查询（Nested Query），是一种嵌套在其他SQL查询中的查询。简单的来说就是一段SQL，这段SQL返回的数据会在其它SQL内使用，即当一个查询是另一个查询的条件时，称之为子查询。
子查询在SELECT语句执行完毕之后就会消失。
由于子查询需要命名，因此需要根据处理内容来指定恰当的名称。

有的教材会按照不同的视角，把子查询分类，本文也简单的介绍一下。可以简单看一下，然后在后续的练习中慢慢体会，不必要教条式的进行分类区分，对解决实际问题并没有多大的用处，可以看一下建议部分进行感悟。

按照返回的数据规模来进行区分：

标量子查询：子查询返回的结果是一个数据（一行一列）
列子查询：返回的结果是一列（一列多行）
行子查询：返回的结果是一行（一行多列）
表子查询：返回的结果是多行多列（多行多列）

建议：

我们不需要区分子查询是否是标量子查询还是列子查询，当在SQL中有一些位置必须要求使用一行一列的数据即单一值，这时如果使用子查询，那么就只能使用标量子查询，无论是SELECT子句、GROUP BY子句、HAVING子句、还是ORDER BY子句，几乎所有的地方都可以使用。

按照子查询的执行过程进行区分：

关联子查询
非关联子查询

建议：

子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为关联子查询。简单来说就是如果这个子查询是套在for循环体里面会被执行多次的那么这个就是关联子查询。至于什么内外结果数据的流向这个我们不必去深究，就按照java里面的循环结构去理解，那些特性自然而然就理解了。可以结合下面这个伪代码理解。

for row in rows(外部表){
	结果：子查询
    使用结果
}

SQL函数与谓词

函数与编程语言里面的函数类似，是已经封装好的工具包。利用函数，可以将很多复杂的问题简化。
谓词是一种特殊的函数，是能输出真假的函数。
CASE WHEN 与编程语言里面的if else类似。

函数

函数的的种类很多，无需全部记住。只需要记住常用的就可以了，剩下的遇到了随时查询即可，下面我会列出一些常用的函数。函数的细节就不会过多进行介绍了，朋友们可以使用的时候去查手册，但是大部分的函数对NULL的处理结果都是NULL。

字符函数（用来进行字符串操作的函数）
- CONCAT(str1, str2....) —— 字符串拼接函数
- Upper()或Lower() —— 大小写转换
- LENGTH(字符串) —— 字符串长度
- LOWER(字符串) —— 小写转换
- REPLACE(对象字符串, 被替换的字符串, 目标字符串) — — 替换字符串中的部分字符
  
  例：
```
SELECT REPLACE('AB', 'B', 'C');

查找AB，然后将AB中的B替换为C，得出来的结果即为 AC 。
```
- SUBSTRING(对象字符串, 截取的起始位置, 截取的字符数) —— 截取字符串中的一部分（下标从0开始）
  
  例：
```
SELECT SUBSTRING('MySQL SUBSTRING',1,5);

从下标1——M开始截取5位（包含M），得到MySQL。
```
算术函数（用来进行数值计算的函数）
- ABS（数值）——求绝对值
- MOD（被除数，除数）——求余
- ROUND（对象数值，保留小数的位数）——四舍五入。
时间日期函数（用来进行日期操作的函数）
- CURRENT_DATE —— 返回当前日期
  
  日期以“YYYY-MM-DD”（字符串）或者YYYYMMDD（数字）方式返回。
- CURRENT_TIME —— 返回当前时间
  
  时间以“ HH-MM-SS”（字符串）或HHMMSS.uuuuuu（数字）形式返回。
- CURRENT_TIMESTAMP —— 返回当前日期和时间
  
  日期和时间以“YYYY-MM-DD HH-MM-SS”（字符串）或者 YYYYMMDDHHMMSS.uuuuuu（数字）形式返回。
- EXTRACT —— 截取日期里面的一部分（年，月，日, etc.）
  
  例：
```
SELECT EXTRACT(MONTH FROM "2017-06-15");

得到月份6
```
转换函数（用来转换数据类型和值的函数）
- CAST(值 AS 想要转换的类型)
  
  例：
```
SELECT CAST(‘001’ AS INTEGER) AS int_col

得到数字1
```
聚合函数（用来进行数据聚合的函数）如果SELECT中除了聚合函数还有其它的字段，则这个字段必须包含在GROUP BY中，聚合函数一般与GROUP BY一起使用，下面是一些常用的聚合函数。
- COUNT
- SUM
- AVG
- MAX
- MIN
用于子查询的函数 any,all关键字必须与一个比较操作符一起使用。any关键词可以理解为“对于子查询返回的列中的任一数值，如果比较结果为true，则返回true”。all的意思是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true” any 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

all可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

谓词

LIKE谓词——字符串的部分一致查询 LIKE类似于Java中的Pattern.matches正则表达式匹配字符串，但是支持的功能有限，语法也比较简单。%代表一个或者多个字符，_代表一个字符。当匹配上时返回true。
```
SELECT * FROM SampleLike WHERE strcol LIKE 'abc__';
```
会筛选出strcol的值长度为5且以abc为前缀的行
```
SELECT * FROM SampleLike WHERE strcol LIKE 'abc%';
```
会筛选出strcol的值以abc为前缀的行
BETWEENT谓词——范围查询

y BETWEEN a AND b

当y的值处于[a,b]时返回true。
```
SELECT * FROM SampleBETWEEN WHERE betcol BETWEEN 100 AND 1000;
```
会筛选出betcol的值处于[100, 1000]区间的行，注意结果包含100和1000这两个边界
IS NULL、IS NOT NULL——判断是否为NULL
IN谓词

y IN (x, ..., z)

当y的值等于（x,...,z）列表中的任意一个值的时候返回true。

 SELECT * FROM SampleBETWEEN WHERE betcol IN (100, 1000, 999);

会筛选出betcol的值等于(100, 1000, 999)列表中任意一个值的行，注意结果包含100和1000这两个边界

EXISTS

[NOT] EXISTS (子查询)

子查询的结果不为空的时候返回真，并不是很不太常用。

CASE WHEN

用值比较

case value

when compareValue1 then result1

when compareValue2 then result2

……

else resulte

end

如果字段值等于预期值，则返回result{num}，否则返回结果resulte。

例：

表stadium：

+------+------------+-------------+-----------+
| id   | visit_date | provinceId  | people    |
+------+------------+-------------+-----------+
| 1    | 2017-01-01 | 1           | 10        |
| 2    | 2017-01-02 | 2           | 109       |
| 3    | 2017-01-02 | 3           | 109       |
+------+------------+-------------+-----------+

SQL语句：

SELECT id, (CASE visit_date 
WHEN '2017-01-01' THEN '2017年1月1日'
WHEN '2017-01-02' THEN '2017年1月2日'
ELSE '其他' END)visit_date FROM stadium

结果：

+------+-------------+
| id   | visit_date  |
+------+-------------+
| 1    | 2017年1月1日 |
| 2    | 2017年1月2日 |
| 3    | 2017年1月2日 |
+------+-------------+

用条件判定

case


when condition1 then result1

when condition2 then result2

……

else resulte

end

语句中的condition是条件判断，如果该判断结果为true，那么CASE语句将返回result{num}，否则返回resulte，如果没有ELSE，则返回null。

例：

表stadium：

+------+------------+-------------+-----------+
| id   | visit_date | provinceId  | people    |
+------+------------+-------------+-----------+
| 1    | 2017-01-01 | 1           | 10        |
| 2    | 2017-01-02 | 2           | 109       |
| 3    | 2017-01-02 | 3           | 109       |
+------+------------+-------------+-----------+

SQL语句：

SELECT id, (CASE 
WHEN people<=100 THEN '10-100'
WHEN people>=100 THEN '100+'
ELSE '其他' END) people FROM stadium

结果：

+------+-------------+
| id   | people      |
+------+-------------+
| 1    | 10-100      |
| 2    | 100+        |
| 3    | 100+        |
+------+-------------+

开窗函数

开窗函数用处很大，是很特殊的一个存在，所以这里我们单独拿出来说。开窗函数的作用和Group By很相似，我们对比着来学习，记住不同之处就可以了。最终要不同就是，Group By会丢失掉单独每一行的信息，只保留每组执行聚合函数后的结果。而开窗函数执行后每一个单独行的信息也会被保留，同时开窗函数的结果也会被保留。 ps:这里我们说的保留是指保证准确性的保留，group by之后，不在group by子句中的字段会随机选取保留（Mysql我印象中应该是第一行），不能保证准确性。下面我们来看一下具体的例子：

表stadium：

+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-01 | 11        |
| 3    | 2017-01-01 | 12        |
| 4    | 2017-01-02 | 10        |
| 5    | 2017-01-02 | 109       |
| 6    | 2017-01-02 | 110       |
+------+------------+-----------+

Group By:

select visit_date, max(people) as top_people_total from staduim group by visit_date

结果:

|------------+-----------------+
| visit_date | top_people_total|
+------------+-----------------+
| 2017-01-01 | 12              |
| 2017-01-02 | 110             |
+------+-----+-----------------+

开窗函数:

select id, visit_date, people, max(people) over(partition by visit_date) as top_people_total from staduim

结果:

+------+------------+-----------+-----------------+
| id   | visit_date | people    | top_people_total|
+------+------------+-----------+-----------------+
| 1    | 2017-01-01 | 10        |       12        |
| 2    | 2017-01-01 | 11        |       12        |
| 3    | 2017-01-01 | 12        |       12        |
| 4    | 2017-01-02 | 10        | 110             |
| 5    | 2017-01-02 | 109       | 110             |
| 6    | 2017-01-02 | 110       | 110             |
+------+------------+-----------+-----------------+

自定义函数

数据库允许我们自定义函数，使用的时候和内置函数是一样的。
自定义函数内可以包含任意的SQL语句。
RETURN是必须的。

例：

表stadium：

+------+------------+-------------+-----------+
| id   | visit_date | provinceId  | people    |
+------+------------+-------------+-----------+
| 1    | 2017-01-01 | 1           | 10        |
| 2    | 2017-01-02 | 2           | 109       |
| 3    | 2017-01-02 | 3           | 109       |
+------+------------+-------------+-----------+

自定义函数创建

CREATE FUNCTION getStadiumPeople(stadiumid INT) RETURNS INT
BEGIN 
RETURN (
  SELECT people FROM stadium WHERE id=stadiumid
);
END;

CREATE FUNCTION：这是创建函数的关键字，属固定语法。

getStadiumPeople：这是函数名称。

stadiumid INT：定义参数名和类型，定义了一个名为stadiumid，类型为INT的参数名。

RETURNS INT：返回值类型。

BEGIN ... END ：这是函数体的固定语法，你需要执行的SQL功能以及流程控制就写在这中间。

定义变量

定义变量其实在日常中用得也并不多，因为大部分业务逻辑都在业务程序这一层处理掉了，复杂的逻辑其实并不会压到数据库，我们能够定义的变量就两类：用户变量和局部变量。掌握这两个应付面试就足够了。

用户变量

作用域：当客户端连接上MySQL服务器之后就可以自己定义一些变量，这些变量在整个连接过程中有效，当连接断开时，这些用户变量消失。

声明与赋值：

用户变量不需要事前声明，可以直接使用，用户变量以@var_name的形式命名变量，变量名必须以@开头。

使用set进行赋值：
```
  SET @var_name := value;
```
在select子句中进行赋值
```
select @var_name := value;
```
局部变量

作用域：局部变量只在当前BEGIN……END代码块中有效，其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了

声明与赋值：

定义局部变量的语法结构：
```
  DECLARE var_name [,……] type [DEFAULT value];
```
使用set进行赋值：
```
  set var_name = value;
```

SQL执行顺序与过程

表格.png 上面这张表格务必铭记于心，我们后续去解决SQL问题都要依据这张表来。

练一练

第N高的薪水

编写一个 SQL自定义函数，获取 Employee 表中第 n 高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

解决思路：这是一个典型的TOP N问题，并且这个TOP N是全局的，求全局意义上的第N个数据，可以使用order by排序后limit offset返回第N个数据。求第N个数据即要跳过N-1个数，因为offset后无法使用N-1，所以要自定义变量。Salary可能有一样多的情况，这个时候需要去重，SQL子句中可以实现去重的有GROUP BY和DISTINCT。所以这道题涉及的SQL知识就是上面这些了，接下来我们来根据SQL执行顺序那张图，去把这些子句组织起来，从而得到结果数据。

函数定义架子搭建：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
 RETURN (
   // 真正的逻辑写在这里
 );
END

首先我们确定数据的范围，这里就是整张表。

SELECT Salary FROM Employee

对Salary进行从大到小排序，然后跳过N-1行数据，并取第一行数据。

SET N = N - 1
Order by Salary DESC Limit N,1

Order By排序之前需要进行去重，可以在聚合阶段使用Group By进行去重，也可以在结果数据阶段使用DISTINCT进行去重。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
 SET N = N - 1;
 RETURN (
   SELECT Salary FROM Employee 
   GROUP BY Salary 
   ORDER BY Salary DESC LIMIT N,1
 );
END

或

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
 SET N = N - 1;
 RETURN (
   SELECT DISTINCT Salary FROM Employee 
   ORDER BY Salary DESC LIMIT N,1
 );
END