CMU15445学习高级SQL

本章将介绍一些常用的sql语法。注意，本课程只是讲解数据库的设计，同时，你必须对sql有一定的了解，否则无法进行后续的学习，所以这里简单介绍一下，以及读者也可以查看其他sql基础教程。SQL即结构化查询语言（StructuredQueryLanguage），是数据库的标准操作语言。大致包括这几种：DataManipulationLanguage(DML)DataDefinitionLanguage(DDL)DML是数据操作，比如数据的增删改查，DDL是数据定义，比如新建表，添加索引，etc.除此之外，还包含了一些其他的操作，比如views，transactions等。为了演示sql的后续用法，将使用以下表格作为demo数据。我以PostgreSQL为例进行讲解，但本文设计的sql语法大多比较笼统。如果在其他数据库中没有看到相应的用法，可以查看其官方文档，看是否有其他写法或者不支持该特性。创建表学生（sidint主键，名称VARCHAR（256），登录varchar（256），ageint，gpaFLOAT8）；插入studentvalues(53666,'Kanye','kanye@cs',44,4.0),(53688,'Bieber','jbieber@cs',27,3.9),(53655,'Tupac','shakur@cs',25,3.5);createtablecourse(cidVARCHAR(30)primarykey,nameVARCHAR(128));insertintocoursevalues('15-445','数据库系统'),('15-721','高级数据库系统'),('15-826','数据挖掘'),('15-823','数据库中的高级主题');createtableenrolled(sidint,cidvarchar(30),gradeCHAR(1));插入登记值(53666,'15-445','C'),(53688,'15-721','A'),(53688,'15-826','B'),(53655,'15-445','B'),(53666,'15-721','C');BasicSyntax最基本的查询语句如下，意思是根据条件查询某个表中的某些列。selectcolumn1,column2,...fromtablewherepredicate1,predicate2,...例如，selectname,gpafromstudentwhereage>25;Aggregatesaggregates表示一些聚合操作，意思是从多个元组中计算返回一个统计值，下面几个示例sql比较简单。AVG平均，通过函数AVG。从学生中选择AVG(gpa)；MIN求最小值，通过函数MIN。从学生中选择MIN(gpa)；MAX找到最大值并使用函数MAX。从学生中选择MAX(gpa)；SUM求和，通过函数SUM。从学生中选择总和（gpa）；COUNT可以添加distinct来对列的数据计数进行去重（avg和sum函数都支持它）。从已注册中选择计数（不同的sid）；GroupByGroupby表示分组操作，就是将表中的数据按照某种特征进行分组，分组可以单独聚合计算。如果要按聚合结果过滤组，可以使用having子句。下面是一个简单的例子。这条sql的意思是根据课程cid进行分类，统计课程的平均分。选择avg(s.gpa),e.cidfromenrolledase,studentasswheree.sid=s.sidgroupbye.cid;对于groupby场景，需要注意几点，一是select列必须包含在groupby子句中或者是聚合列。比如我在之前的sql中添加了一个s.name列，报错信息如下：二、可以过滤groupby后的输出，但是需要注意这里过滤不能使用where子句，但having子句需要组合。选择avg(s.gpa)asavg_gpa,e.cidfromenrolledase,studentasswheree.sid=s.sidgroupbye.cidhavingavg(s.gpa)>3.9;StringOperations数据库中的一些函数支持字符串类型数据的处理，下面介绍比较常见的几种。更多功能请参考sql-92标准文档：https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txtlikelike可以匹配字符串。select*fromstudentwhereloginlike'%r@cs';substringstring截取selectname||'isastudent'fromstudent;uppercharacteruppercaseselectUPPER(name)fromstudent;StringConcatstringconnection,sql-92标准是使用||symbol，不同的操作系统有不同的实现，比如可以使用+或者concat函数。选择名称||“是学生”来自学生；日期/时间操作日期时间函数也是数据库中的基本处理函数。该函数现在可以返回当前时间。选择now()作为current_datetime；更多功能请参考标准sql文档。输出ControlSQL可以支持对数据的输出结果进行过滤、排序、重命名等操作。orderbyorderby可以对数据的结果进行排序，基本语法是orderby[ASC|DESC]select*fromstudentorderbygpadesc;它还可以按多列排序。selectsidfromenrolledwherecid='15-721'orderbygradedesc,sidasc;limitlimit子句可以控制输出结果的数量。select*fromstudentlimit1还可以加上offset参数来控制获取数据的偏移量。从课程限制2偏移量2中选择*；NestedQueries嵌套查询。selectnamefromstudentwheresidin(selectsidfromenrolled);这类查询的主要含义是一个查询的过滤条件取决于另一个查询的输出，前者和后者分别称为外查询和内查询。在内查询前可以加一个条件匹配表达式，大致如下：ALL：表示必须满足内查询中的所有条件ANY：表示满足内查询中的任意一条数据IN：相当于ANYEXISTS：有在内层查询中可以返回一条数据。exists前面可以加not表示no。例如下面sql的意思是查找enrolled中没有课程记录的数据。从不存在的课程中选择*（从已注册的课程中选择*，其中course.cid=enrolled.cid）；窗口函数窗口函数可以计算与当前行相关的一系列行。常见的窗口函数语法如下：下面的例子sql演示了一个最简单的窗口函数，row_number()返回当前行的序号。select*,row_number()over()fromenrolled;over子句表示在进行计算时如何排列数据，例如可以在group或sort中加上partition关键字。selectcid,sid,row_number()over(partitionbycid)fromenrolledorderbycid;选择cid,sid,row_number()over(orderbycid)fromenrolledorderbycid;CommonTableExpressions最常见的是withas语句，这个语法的意思是提供一个临时的结果给其他查询使用，有点类似于临时表。下面是一个很简单的例子：withtabas(select1)select*fromtab;本节学习sql的基础知识，由于sql语法较多，所以并没有涵盖所有的用法，只列出了一些常用的sql，读者可以参考sql文档等基础教程。稍后，我们将正式进入数据库系统设计。首先，我们将讨论数据库中的存储管理模块。