用于数据科学的SQL

如今在数据科学中使用SQL查询数据是常见的操作，而大部分以表格（行和列）展示数据的数据格式，几乎都支持使用SQL进行查询，比如Dataframe、Excel。

同时通过Jupyter Notebook提供的Magic Statements和IPython_SQL，能够让SQL和Python进行非常方便的结合。

SQL

SQL全称为Structured Query Language 结构化查询语言，是基于Edgar F. Codd在1970年提出的关系模型理论，该模型引入了表（表格形式的数据结构）来表示数据以及其关系的概念，所以可以理解为最初的定位是用于操作和查询关系型数据库。

SQL 于 1986 年被 ANSI（美国国家标准学会）采纳，并于 1987 年被 ISO（国际标准化组织）采纳为关系型数据库的标准语言，所以目前制定标准的主要由ANSI和ISO两个组织。

SQL是一个标准，也是一个总称，SQL的定义了两个核心部分，第一个是标准的查询语句和标准的数据类型。

除了上述的这些子集，还有一些扩展的高级功能的子集，比如对非结构化数据的支持，使得SQL能够支持NoSQL、NewSQL以及大数据系统应用。

查询语句

查询语句主要是围绕着DDL、DML、DCL以及TCL几个核心的子集开展，而常见的SELCT、CREATE都是属于这些子集，核心子集每个数据库（DBMS）都是支持的，但是每个数据库针对于SQL的扩展都会都会有一些差异，所以如果考虑到迁移性，应该尽可能使用标准的核心SQL语句。

DDL（Data Definition Language）

DDL（数据定义语言），理解为数据库的地基，负责定义和修改数据库结构和对象（如表、视图和索引等），典型的语句有CREATE、ALTER和DROP等。

DML（Data Manipulation Language）

DML（数据操作语言），作用于操作数据库的数据，对数据库中的数据进行查询、插入、更新和删除等操作，典型的语句有 Select查询数据、 Insert插入数据、Update更新修改数据、 Delete删除数据等。

DQL（Data Query Language）是专门用于查询的子集，但是是由DML下的子集，所以DML包含查询。

DCL（Data Control Language）

DCL（数据控制语言），作用于控制用户权限，管理数据库对象的访问权限，典型的语句有Grant和Revoke等。

TCL（Transaction Control Language）

TCL（事物控制语言）是SQL子集，作用于管理事物，确保数据库操作的完整性，典型的语句有Commit、Rollback和Savepoint等。

数据类型

SQL除了操作查询数据的语句部分，还有定义了数据的类型，比如数值、字符串、日期、布尔值等，大部分都是比较好理解的，但日期则可以根据需求不同采用不同的日期类型，SQL定义了DATE、TIME和TIMESTAMP类型，主要差异在于：

DATE：DATE类型有8位数，分别为YYYYMMDD。
TIME：TIME类型有6位数，分别为HHMMSS。
TIMESTAMP：TIMESTAMP有20位数，分别为YYYYXXDDHHMMSSZZZZZZ，其中XX代表月份，ZZZZZZ代表微秒。

一些记录

大部分SQL可以很容易查询到用法，但是部分查询在性能和实现则有一定的区别，同时也有存在多种解决方式。

Primary Key

数据库中主键有如下几个功能：

唯一性：设置为主键的属性（数据库中被映射为了列），是唯一值，能够避免数据重复。
一致性和完整性：主键确保表每一行都有一个唯一的标识符，这对于维护数据的一致性非常重要。
关联性：在关系型数据库中，主键用于建立表与表之间的关系（通过外键引用）。
加速：数据库在创建主键的时候，会为主键创建索引，使得通过主键查询会非常快。

ER模型和ERD图

Entity-Relationship Model（ER模型）

ER模型是用于设计实体关系模型的基础理论，关注实体（比如人）、实体的属性（比如人的头发、身高、体重、肤色等）以及相互之间的关联，ER模型的好处是可以针对字段进行规划关联、字段类型、主键以及多对一还是一对多等关系，同时设计完成后可以自动化生成sql进行自动建立数据库结构。

Entity Relationship Diagram（ERD图）

而ERD则是ER的一种图的实现，基于ER理论通过ERD进行设计实体关系图，会更加直观。

优势

如果只是画图，很多都可以画，但是ER模型和ERD都是专用数据库设计的，所以有很多其它画图工具达不到的功能，比如自动根据ERD图生成SQL脚本。

字符串查询和范围查询

SQL使用字符串进行查询，可以只给出部分关键词进行模糊查询，其中有%代表通配符（即匹配任何事情）。

select * from tablename where name like 'R%'

范围查询可以使用between和in关键词，比如查询2000-2010年之间的数据，使用比较运算符的大于等于和小于等于进行查询：

select * from tablename where RealseYear >= 2000 AND RealseYear <= 2010;

而上面的方式，同样使用比较运算符between和and等替：

select * from tablename where RealseYear BETWEEN 2000 and 2010;

再比如检查是不是等于某些值：

select * from tablename where Country = 'UK' OR Country = 'US';

这样给定几个值的查询可以使用in关键词，然后给定一个元组：

select * from tablename where Country in ('UK','US');

排序

SQL的排序使用语法：

SELECT * FROM TABLE ORDER BY COLUMN

指定排序列是一种方式，也可以给数值选定列排序，比如选择返回的数据集中的第二列进行排序：

SELECT * FROM TABLE ORDER BY 2

分组

SQL中使用GROUP BY关键词对结果进行分组，GROUP BY常与聚合函数（如MAX、 MIN、COUNT、SUM）一起使用，对每组数据进行汇总计算，同时还可以使用HAVING关键词对分组结果进行过滤筛选。

GROUP BY

SQL中使用GROUP BY关键词对结果进行分组，将具有相同值的行归为一组。

SELECT column1, column2 FROM table_name GROUP BY column1, column2;

比如：

| product | category | quantity |

|---------|----------|----------|

| apple  | fruit  | 10    |

| banana | fruit  | 20    |

| carrot | vegetable| 15    |

| apple  | fruit  | 5    |

然后分组进行计算：

SELECT category, SUM(quantity) AS total_quantity

FROM sales

GROUP BY category;

结果为：

| category  | total_quantity |

|------------|----------------|

| fruit   | 15       |

HAVING

HAVING关键词用于筛选分组后的结果，与where不同的是，where会在分组前对原始数据进行筛选，而HAVING则是分组后的数据筛选，比如：

SELECT category, SUM(quantity) AS total_quantity

FROM sales

GROUP BY category

HAVING total_quantity > 20;

子查询

子查询与嵌套查询是一个意思，都是指SQL中将一个查询语句嵌套在另外一个查询语句中作为条件或者数据源。

子查询根据使用不同，可以返回单个值或者多个值来配合外部的查询。

一般在SQL语句中，子查询用括号包裹，并且存在于SELECT中作为列，WHERE作为条件，FROM或JOIN作为表。

SELECT

子查询返回多个或者单个值，作为外部查询的列值。

SELECT *, ( SELECT name FROM table2 ) as name FROM table1;

WHERE

子查询返回一个或者多个值，用于过滤外部查询的结果。

SELECT * FROM table1 WHERE age > ( SELECT avg(age) FROM table2 where age >= 18) ;

返回子查询中的结果，也就是name和age。

FROM或JOIN

子查询返回一张虚拟表，供外部查询和使用，但作为这种使用，子查询必须有一个别名，不然SQL引擎无法识别子查询的结果表。

SELECT * FROM (SELECT name,age FROM table2 where age >= 18) as subquery ;

----第二种

SELECT t1.name, t1.age FROM table1 AS t1 

LEFT JOIN 

（SELECT name, age from table2 where age >= 18） AS t2 

ON t1.name = t2.name

聚合函数

聚合函数不是SQL语言内置的，功能是由DBMS提供实现的，但SQL定义了一些基础的标准聚合函数，这些函数是可以达到夸平台的通用基础聚合函数，被大部分DBMS实现：

COUNT：计算行数
SUM：计算列的综合
AVG：计算列的平均值
MAX：计算列的最大值
MIN：计算列的最小值

除此之外，SQL还定义了部分稍微高级的聚合函数，比如EVERY、ANY、SOME等，但这些都不是被DBMS默认实现，所以不同的数据库提供的聚合函数可能有一些差异，

除此之外还有一个MID函数比较有意思，可以从字段的值里面提取指定位置的字符串。

SELECT MID(column_name, start, length) from table_name;

其中start为开始位置，而length为从开始位置开始计算提取多长。

日期函数

日期函数有助于在时间格式提取相关的值，比如YEAR、MONTH、DAY、WEEK、HOUR、MINUTE等，可以提取日期中的年、月、天、星期、小时和分钟，而还有DATEDIFF则可以计算两个日期之间的间隔，FROM_DAYS则是将天转换为日期，适用于日期相减的结果的时候使用。

除此之外，还有一些特殊的寄存器，比如CURRENT_DATE、CURRENT_TIME储存着当前的日期和当前的时间。

标量和聚合

SQL的函数可以分位聚合和标量函数，标量函数即对每一行进行计算，返回每一行的单个值，即输入多少行，返回多少行，典型的比如MONTH提取每行的月份和ROUND对输入的行的数值进行小数位设定。

聚合函数即对多行数据进行计算，并返回单个值的函数，即输入多行，返回单个值，典型的就是COUNT函数。

多表查询

多表查询有子查询、隐式JOIN和JOIN ON操作符来进行多表的查询。

子查询

比如下面的查询，想筛选部门地址为’10001’的所有员工，但是employees表没有部门的地址信息，只有departments表有，所有就可以通过子查询进行多表筛选。

SELECT * FROM employees 

WHERE DEP_ID IN

(SELECT DEPT_ID_DEP FROM departments where loc_id = '10001')

隐式JOIN

当查询中未指定连接条件（例如 ON 或 WHERE），每张表的每一行会与另一张表的每一行组合，形成笛卡尔积。

table1:

**id**	**name**

1	Alice

2	Bob

Table2:

**product_id**	**product_name**

101	Laptop

102	Smartphone

查询结果：

SELECT * FROM table1, table2;



**id**	**name**	**product_id**	**product_name**

1	Alice	101	Laptop

1	Alice	102	Smartphone

2	Bob	101	Laptop

2	Bob	102	Smartphone

但隐式JOIN可以通过条件来限定返回的内容，限定了过后，实际上是和Inner Join逻辑是相同的：

SELECT * FROM TABLE1, TABLE2 WHERE TABLE1.name = TABLE2.name;

(完)

Lucas Wu