【CMU 15-445/645 Database Systems】02 Advanced SQL

·  阅读 394
【CMU 15-445/645 Database Systems】02 Advanced SQL

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。

分类:
阅读
标签:
分类:
阅读
标签:
收藏成功!
已添加到「」, 点击更改