1. What is SQL

用户只关心是否能得到所需的查询结果；DBMS会对query给出一个高效的执行方式。这就是查询优化器（Query Optimizer）所做的事情，而具体的执行方法，就是执行计划（Query Plan）。

Sql (structured query language)

前一篇笔记说过，sql是一个常用的编写查询语句的标准，可以称为一种语言。就像Java，c++等都有版本迭代一样，sql也有，我们常说的sql92、sql99等都是sql的不同版本。目前已经更新到了sql2016。

sql语言包括四大类：

• DQL 数据查询语言，就是最常见的查询语句，select from那一套
• DML 数据操作语言，对数据的增删改，insert、delete、update
• DDL 数据定义语言，create类型的操作，包括创建表、索引、视图等
• DCL 数据控制语言，包括授权、提交、回滚

SQL is based on bags (duplicates) not sets (no duplicates)

2. SQL语法

Aggregates

Functions that return a single value from a bag of tuples

• Avg, min, max, sum, count
• avg、sum、count支持对所聚合的列先进行distinct操作（select count (distinct name))

只可以用在select中输出的列名中，作为对列的一个函数操作。

group by

project tuples into subsets and calculate aggregates against each subset.

select的输出中，非agg的列必须是被group by的列。否则就不能出现在select后面。

having

对agg的结果进行一次filter。相当于针对group by 的where语句。

String operations

• like：%匹配任何子字符串；_匹配任何单个字符；

• substring，upper，lower，concat

date/time

Output redirection

将query result直接作为内容插入到其他table或创建一个新的table：

CREATE TABLE table_new (
    SELECT cid FROM table_old
);



INSERT INTO table_old2 (
    SELECT cid FROM table_old1
);

Output control

• order by
• asc/desc 可以多列一起排序，可以升序降序一起用
• limit [offset]

Nested queries

外部查询中的很多位置都可以嵌套子查询

• Not exists

-- find all courses that has no students enrolled in it.    

SELECT * FROM course
WHERE NOT EXISTS (
    SELECT * FROM enrolled
    WHERE course.cid = enrolled.cid
)

Window functions

格式：

SELECT ... FUNCTION_NAME(...) OVER (...)

-- function_name: (aggregation funcs or special funcs)
-- over: means how to "slice" up data

Special window functions:

• ROW_NUMBER(): 输出一列行号

SELECT *， ROW_NUMBER() OVER() AS row_num

• RANK()：输出一列当前行的位次
• over()：可以用于进行一个隐式的分组，例如ROW_NUMBER() OVER (PARTITION BY cid），那么之后输出的行号会是各自按照cid分组后，组内的行号。

SELECT *
        ,ROW_NUMBER() OVER (PARTITION BY cid)
FROM enrolled
ORDER BY cid

-- find the student with the highest grade for each course

SELECT * FROM (
    SELECT *
            ,RANK() OVER (PARTITION BY cid ORDER BY grade ASC) AS rank
    FROM enrolled
    ) AS ranking
WHERE ranking.rank = 1

with

生成一个临时的table，“alternative to nested queries and views".

WITH tempName (col1, col2) AS (
    SELECT 1, 2
)
SELECT col1+col2 FROM tempName

3. 总结

关系代数和SQL都是对关系型数据模型的操作语言 ，关系代数的过程化程度更高，代表了实际进行操作的次序。

SQL相当于对关系代数进行的一层封装，我们通过编写SQL（这一操作数据库的既定共识标准）来实现我们期望的查询。DBMS会设计各自的优化器来对输入的SQL进行优化，使其尽可能以最高效的方式来转化成关系代数表达式进行执行。优化后生成的具体执行逻辑，称为query plan。