数据分析师SQL进阶知识（数据分析师sql笔试题）

在学习SQL过程中，看过不少教程文档，像W3C、SQL必知必会是被推荐得最多的，在当中学到的知识是SELECT、FROM、WHERE、JOIN、GROUP BY几个语句加几个常用函数（count、max），但是在一些交流群里面，那些数据分析大佬张口就是CASE WHEN、循环遍历，怎么这些我都没学过啊，问也没人回复（可能大佬觉得这些都太简单）。查了MySQL官方文档和SQL标准，来补齐这块知识，所以以下内容是适用于MySQL，其他支持SQL数据库会有差异。

SQL（Structured Query Language）是一门计算机语言，它与我们学习的其他编程语言如Python一样，都有变量、流程、函数的概念。像Python一样，不写函数也可以完成任务，而函数、模块会让工作更高效，特别是在大的项目里。

变量

在SQL中变量分为三类，系统变量、用户变量、局部变量，看分类就知道三者之间有范围不同，是按作用域来划分的，根据名称可以体会下。

用户变量

用户变量是用户可以在表达式中使用自己定义的变量，（个人想法，按这个定义局部变量也是用户变量）

注意用户变量的作用域，一个客户端定义的变量不能被其他客户端使用，当客户端退出后，该客户端连接的所有变量将自动释放。

用户变量的定义和初始化，是使用SET或SELECT语句来做。

 SET @user_variable[:]=expression1 [,user_variable2=expression2,…];
 # 或者
 SELECT @user_variable1:=expression1[,user_variable2:=expression2];

• 用户变量的引用要在变量名前加标志@
• 变量名可以使用英文字母、数字也可以用中文
• SET语句中变量的赋值使用=或者:=都可以，但在SELECT语句中只能用:=赋值，因为=在SELECT中是相等比较操作
• 变量被赋值的值可以是整数、字符串、NULL这些数据库常量外，也可以是查询结果

 #中文变量名，是可以不是推荐这样
 SET @日期:='20200323';
 ?
 #同时定义初始化多个变量
 SET @a:=1, @b=2, @c=3;
 或者
 SELECT @a:=1,@b:=2,@c:=3;
 ?
 #变量引用
 SELECT @a,@b,@c;
 SELECT sno, sname, sclass FROM student WHERE sclass=@c;#查询三年级的学生
 ?
 @使用查询结果给变量赋值
 SET @student=(SELECT sname FROM student WHERE sno='20200104');
 SELECT @class3:=sname FROM student WHERE sclass=@c LIMIT 0,1;
 ?

局部变量

局部变量是块级变量，它作用范围是BEGIN……END语句块，这跟Python中相同，局部变量值在函数方法中有效。

局部变量的定义，局部变量使用DECLARE语句定义

 DECLARE var_name [,...] type [DEFAULT value];

• 局部变量不需要用@开头；
• 局部变量名与用户变量规则相同，注意保留关键字；
• type是指定变量类型；
• DEFAULT指定变量默认值，可选，未指定则默认值为NULL

 DECLARE myvar int DEFAULT 0;

系统变量

系统变量按作用范围来说是最顶端的，放在第三部分是因为相比来说，个人觉得它的使用没有用户变量和局部变量那么多。

系统变量又分为全局变量（global）和会话变量（session）

全局系统变量在MySQL启动时由服务器来初始化它们的默认值，这些默认值可以通过my.ini文件修改。

会话系统变量是在每次建立一个新的连接时，有MySQL初始化。会话中修改，随连接结束释放。

系统变量的引用和修改，是使用SET语句

 SET system_var_name=expression | [global|session] system_var_name=expression |@@[global.|session.]system_var_name=expression

• 指定了global或者@@global.关键字的是全局系统变量；
• 指定了session或@@session.关键字的是会话系统变量；
• session和@@session.与local和@@local.的含义相同；
• 如果在使用系统变量时不指定关键字，则默认是会话系统变量

 SET @@global.sort_buffer_size=40000;

流程控制

顺序、条件、循环是任何编程语言的基础，当然在SQL也有，与Python不同的是SQL的流程控制语言是只用于过程、函数中。

BEGIN END语句块

BEGIN……END可以定义SQL语句块，这些语句块作为一组语句执行，允许嵌套。

条件语句

0. IF……ELSE语句

SQL中的IF语句块是以IF开头，END IF结尾（不同于Python以缩进区分）

 IF search_condition THEN
   statement_list
 [ELSEIF search_condition THEN statement_list]
 [ELSE statement_list]
 END IF;

• search_confition是返回true或false的逻辑表达式，如果逻辑表达式中含有SELECT语句，则用括号括起来
• statement_list既可以是单条SQL语句也可以是BEGIN……END的语句块

 IF (SELECT office FROM department WHERE deptname='中文系') IS NULL THEN
 BEGIN
   SELECT `教室` AS `上课地点`;
   SELECT * FROM department WHERE deptname='中文系';
 END;
 ELSE
   SELECT office FROM department WHERE deptname='中文系'
 END IF；

2. CASE分支语句

CASE分支语句有两种语法：

 CASE input_expression
 WHEN when_expression THEN result_expression;
 [ELSE else_result_expression]
 END CASE;
 ?
 CASE
 WHEN Boolean_expression THEN result_expression;
 [ELSE else_result_expresion]
 END CASE;

CASE分支语句很清楚就不举例了，需要重点说明的一点是在MySQL中是CASE流程控制函数和CASE流程控制语句两个很类似但是全然不同的两个概念的，我们通常在SELECT语句中使用CASE WHEN是流程控制函数，两者最大的区分是流程控制函数的THEN之后不能接操作语句块的。

另一个同样的坑是IF语句也有相同功能的函数，包括IF(),IFNULL(),NULLIF()，好在IF中语句和函数区分还是比较大，IF（expr1，expr2, expr3)。

循环语句

SQL中的循环语句一共三个，WHILE……END，REPEAT……END，LOOP……END，从功能上说三者没有太大的分别，任何一个都可以转化成另外的两种形式。

 #WHILE
 [begin_label:]WHILE Boolean_expression DO 
     {sql_statement|statement_block}; 
     [LEAVE begin_label;] 
     {sql_statement|statement_block}; 
     [ITERATE begin_label;] 
     {sql_statement|statement_block}; 
 END WHILE;
 ?
 #REPEAT
 [begin_label:]REPEAT 
    {sql_statement|statement_block}; 
    [LEAVE begin_label;]
    {sql_statement|statement_block}; 
    [ITERATE begin_label;] 
    {sql_statement|statement_block}; 
    UNTIL Boolean_expression 
 END REPEAT;
 ?
 #LOOP
 begin_label:LOOP 
    {sql_statement|statement_block}; 
    LEAVE begin_label; 
    {sql_statement|statement_block}
    [ITERATE begin_label;] 
    {sql_statement|statement_block}; 
 END LOOP

使用三者进行进行1~100的求和，看下三者的区别。

 #WHILE 循环
 SET @i=1,@sum=0; 
 WHILE @i<=100 DO 
     BEGIN 
        SET @sum=@sum+@i; 
        SET @i=@i+1; 
     END; 
 END WHILE; 
 SELECT @sum;
 ?
 #REPEAT
 SET @i=1,@sum=0; 
 REPEAT 
     BEGIN 
        SET @sum=@sum+@i; 
        SET @i=@i+1; 
     END; 
 UNTIL  @i>100 
 END REPEAT; 
 SELECT @sum;
 ?
 #LOOP
 SET @i=1,@sum=0; 
 add_sum:LOOP 
  SET @sum=@sum+@i; 
  SET @i=@i+1; 
   IF @i>100 THEN  
  LEAVE add_sum; 
  END IF; 
 END LOOP; 
 SELECT @sum;

• while是先判断再执行，repeat是先执行操作再判断，loop是一直循环
• loop的begin_label是必须的，loop是自己不会结束循环的，需要设定结束条件。

过程与函数

过程和函数的作用很类似，都是将一些操作封装起来，可以重复调用，在Python没有分开的过程和函数两个概念，但想SQL、VBA等一些语言是分开的，在SQL中，函数是有返回值的，过程则不必须有返回值。

过程的创建

 CREATE PROCEDURE procedure_name([proc_parameter[,…]]) 
 [characteristic[,…]]  
 Routine_body

• procedure_name：过程的名称
• proc_parameter：过程中的参数列表。其形式如下。 [IN|OUT|INOUT]param_name type 其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出，默认为IN；param_name表示参数名称；type表示参数的类型；可以声明一个或多个参数。
• Routine_body：是包含在过程中的SQL语句块，可以用BEGIN…END来表示SQL代码的开始与结束。

函数的创建

 CREATE FUNCTION func_name([func_parameter[,…]]) 
   RETURNS type   
   [characteristic[,…]] 
   Routine_body

其中各参数含义如下。

• func_name：函数的名称。
• func_parameter：函数中的参数列表。其形式与过程相同。
• RETURNS type：指定返回值的类型。
• Routine_body：是包含在存储函数中的SQL语句块，可以用BEGIN…END来表示SQL代码的开始与结束
• characteristic：指定函数的特性，该参数的取值与过程中的取值一样。

过程与函数的调用都是CALL语句

 CALL func_name（params)

过程、函数示例

 #过程
 >DELIMITER //
 >CREATE PROCEDURE proc_stud() 
    ->READS SQL DATA 
    ->BEGIN 
    ->  SELECT sno,sname,classno,saddress FROM student 
    ->  WHERE saddress LIKE '% 青岛%' ORDER BY sno; 
    ->END// 
 Query OK, 0 rows affected 
 >DELIMITER ;

 >DELIMITER &&   
 > CREATE  FUNCTION  func_name (class_no varchar(8))   
      -> RETURNS varchar(8)   
      -> BEGIN   
      ->    RETURN  (SELECT  header  FROM  class   
      ->    WHERE  classno=class_no);   
      -> END && 
 Query OK, 0 rows affected 
 > DELIMITER ;