7个需要了解的SQL知识点

大家好，SQL指的是结构化查询语言，旨在管理关系型数据库，SQL在数据科学中应用广泛，属于必备技能。以下列出了SQL的特点：

• 广泛使用：虽然它已经有了大约40年的历史，但在大多数关系型数据库系统中用于查询，并已成为实验数据的标准工具。

• 简化对数据的理解：SQL在浏览数据库内容时非常方便。它能让你有效地理解细节。

• 易于学习：它是新手的完美起点，具有简单的类英语语法，只需几行代码即可提取有价值的见解。

• 能够处理海量数据：SQL可以使你以有组织的方式管理大量数据，是数据科学应用的理想选择。

• 与其他编程语言和应用程序的兼容性：将SQL与Python、C++、R等语言集成非常方便。它还支持商业智能和数据可视化工具，如Power BI和Tableau，使开发过程更加容易。

基于此，本文将介绍7个从事数据科学必须掌握的SQL关键知识点。 

1.基本命令 

掌握基本命令的知识是终身学习的基础，否则只会记忆事实，而不了解它们如何配合。以下是一些最常用的SQL命令：

• SELECT和FROM：从指定的表中检索数据的属性。

• SELECT DISTINCT：它消除重 复的行并只显示唯一的记录。

• WHERE：它过滤记录并只显示满足给定条件的记录。

• AND、OR、NOT：当条件不为True时不执行查询。而AND和OR用于应用多个条件。

• ORDER BY：它按升序或降序对数据进行排序。

• GROUP BY：它对相同的数据分组。

• HAVING：对由Group By聚合的数据可以在此进一步筛选。

• 聚合函数：聚合函数，如COUNT()、MAX()、MIN()、AVG()和SUM()，用于对给定的数据执行操作。

以Employee表为例，应用这些命令：

ID	Name	Department	Salary ($)	Gender
1	Julia	Admin	20000	F
2	Jasmine	Admin	15000	F
3	John	IT	20000	M
4	Mark	Admin	17000	M

现在想要获取在Admin部门工作的女性的平均工资：

SELECT Department,
       AVG(Salary)
FROM Employees
WHERE Gender="F"
GROUP BY Department
HAVING Department = "Admin";

输出：

Admin | 17500.0

2.Case When

Case When是SQL中一种非常强大和灵活的语句，用于编写复杂的条件语句，它提供了IF.THEN.ELSE语句的功能。它的语法如下：

CASE expression

   WHEN value_1 THEN result_1
   WHEN value_2 THEN result_2
   ...
   WHEN value_n THEN result_n

   ELSE result

END

它按顺序执行语句，并在条件为True时返回值。如果没有满足条件的情况，将执行ELSE块，如果没有ELSE块，则返回NULL。

假设我们有一个学生数据库，想根据他们的成绩对他们进行分级。可以使用以下SQL语句：

SELECT student_name,
       marks,
       CASE
           WHEN marks >= 85 THEN 'A'
           WHEN marks >= 75
                AND marks < 85 THEN 'B+'
           WHEN marks >= 65
                AND marks < 75 THEN 'B'
           WHEN marks >= 55
                AND marks < 65 THEN 'C'
           WHEN marks >= 45
                AND marks < 55 THEN 'D'
           ELSE 'F'
       END AS grading
FROM Students;

3.子查询

作为一名数据科学家，子查询的知识是必不可少的，因为他们需要处理不同的表格，并且一个查询的结果可能会再次用于限制主查询中的数据，这也被称为嵌套查询或内部查询。子查询必须用括号括起来，在主查询之前执行，如果返回多行，则称为多行子查询，并且必须使用多行运算符。

假设保险公司推出了一项新政策，取消那些年龄超过80岁的人的保险。可以使用如下所示的子查询完成：

DELETE
FROM INSURANCE_CUSTOMERS
WHERE AGE IN
    (SELECT AGE
     FROM INSURANCE_CUSTOMERS
     WHERE AGE > 80 );

 内部子查询选择了所有80岁以上的客户，然后对该组执行Delete操作。

4.连接

SQL连接是用于基于多个表格之间的逻辑关系将它们中的行组合在一起。SQL连接的4种类型如下所示：

• 内部连接（Inner Join）：内部连接仅显示满足给定条件的两个表格中的行。它可以在集合术语中称为交集。

SELECT Student.Name
FROM Student
INNER JOIN Sports ON Student.ID = Sports.ID;

它返回那些已在体育部门注册的学生。注意：Sports ID与学生的注册ID相同。

• 左连接（Left Join）：它返回左表格中的所有记录，而只显示右表格中的匹配记录。

SELECT Student.Name
FROM Student
LEFT JOIN Sports ON Student.ID = Sports.ID;

• 右连接（Right Join）：它与左连接作用正好相反。

SELECT Student.Name
FROM Student
RIGHT JOIN Sports ON Student.ID = Sports.ID;

• 完全连接（Full Join）：它包含来自两个表格的所有行，如果它没有相应的匹配条目，则显示NULL值。

SELECT Student.Name
FROM Student
FULL JOIN Sports ON Student.ID = Sports.ID;

5.存储过程

存储过程允许我们在数据库中存储多个SQL语句以便日后使用，其提供了可重用性，还可以在调用时接受参数值，也可以增强性能，并且更容易进行任何修改。

CREATE PROCEDURE SelectStudents @Major nvarchar(30),
                                       @Grade char(1) AS
SELECT *
FROM Students
WHERE Major = @Major
  AND Grade = @Grade GO;

EXEC SelectStudents @Major = 'Data Science',
                    @Grade = 'A';

此存储过程允许我们提取不同专业的学生，并根据其成绩进行筛选。例如，我们要提取所有专业为数据科学且成绩为A的学生。请注意，CREATE PROCEDURE就像函数声明一样，需要使用EXEC进行调用以便执行。

6.字符串格式化

我们都知道原始数据需要进行清洗，以提高整体生产力，从而做出高质量的决策。字符串格式化在这种情况下起着重要作用，它涉及到对字符串的操作，以去除不相关的内容。SQL提供了大量的字符串函数来转换和处理字符串，其中最常用的五个函数如下：

• CONCAT：用于将两个或多个字符串相加。

  SELECT CONCAT(Name, ' has a major of  ', Major)
  FROM Students
  WHERE student_Id = 37;

• SUBSTR：返回字符串的一部分，并在其参数中提供子字符串的起始位置和长度。

SELECT student_name,admission_date,
     SUBSTR(admission_date, 4, 2) AS day
FROM Students

从admission_date中提取出来的day列将以单独的形式出现。

• TRIM：TRIM的主要作用是从字符串的开头、结尾或两者中删除字符（如果指定了）。必须指定前导、后导或两者，然后是指定要删除的字符，再接着是要从中删除的字符串。

SELECT age,
   TRIM(trailing ' years' FROM age)
FROM Students

它将26 years更改为26。

• INSERT：允许我们在给定字符串中的指定位置插入字符串。必须指定要写入的新子字符串的位置和长度。请注意，此新字符串将覆盖先前的文本。

SELECT INSERT("OldWebsite.com", 1, 9, "NewWebsite"); 

它将被更新为NewWebsite.com。

• COALESCE：主要是用使用者定义的值替换空值，这在数据科学中经常需要。

SELECT COALESCE (NULL, NULL, 10, 'John’')

 这将返回10。

7.窗口函数

窗口函数类似于聚合函数，但在计算后不会使行合并为单个行，相反，行保留其各自的标识。其分为三个主要类别：

• 聚合函数：它从数值列中显示聚合值，如AVG()、COUNT()、MAX()、MIN()、SUM()等。

SELECT name,
       AVG(salary) over (PARTITION BY department) 
FROM Employees;

 它显示了Employee表中不同部门的平均工资。

• 值函数：使用值窗口函数为每个分区分配一些值。一些常用的值函数有LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()和NTH_VALUE()。

SELECT 
  bank_branch, month, income,
 LAG(income,1) OVER (
  PARTITION BY bank_branch
  ORDER BY month
 ) income_next_month
FROM Bank;

我们将银行不同分支机构的本月收入与上月进行比较。

• 排名函数：它们可根据预定义排序为行分配排名。ROW_NUMBER()、RANK()、DENSE_RANK()、PERCENT_RANK()、NTILE()等是其中的几个函数。

SELECT
 product_name, price,
 RANK () OVER ( 
  ORDER BY list DESC
 ) price_hightolow
FROM Products;

 使用RANK()，对产品进行基于价格的排名。

通过上面的讲述，可以知道作为数据科学家需要了解的一些SQL基本知识，为后续学习打下一定的基础。