CMU 15445 学习笔记—2 Advanced SQL

本章将会介绍一些常用的 sql 语法。注意这个课程只要是为了讲解数据库的设计，但同时对 sql 也要有一定的了解，不然没法进行后续学习，所以这里做一个简单的介绍，读者也可以查看其它的 sql 基础教程。

SQL 即结构化查询语言（Structured Query Language），是数据库的标准操作语言，它大致包含了这几种类型：

•Data Manipulation Language (DML)•Data Definition Language (DDL) DML 是数据操作，例如增删改查数据，DDL 是数据定义，例如新建一个表，增加一个索引等。此外还包含其他的一些操作，例如视图、事务等。

为了演示后续 sql 的用法，将会以下面的几张表做为 demo 数据。

我是以 PostgreSQL 为例进行讲解的，但是本文中设计的 sql 语法大多比较通用，要是在其他的数据库中没看到相应的用法，可以查看其官方文档，看是否有其他的写法或者该 feature 不支持。

image.png

create table student (
  sid int primary key,
  name VARCHAR(256),
  login varchar(256),
  age int,
  gpa FLOAT8
);

insert into
student
values
(53666, 'Kanye', 'kanye@cs', 44, 4.0),
(53688, 'Bieber', 'jbieber@cs', 27, 3.9),
(53655, 'Tupac', 'shakur@cs', 25, 3.5);

create table course (cid VARCHAR(30) primary key, name VARCHAR(128));

insert into
course
values
('15-445', 'Database Systems'),
('15-721', 'Advanced Database System'),
('15-826', 'Data Mining'),
('15-823', 'Advanced Topics in Databases');

create table enrolled (sid int, cid varchar(30), grade CHAR(1));

insert into
enrolled
values
(53666, '15-445', 'C'),
(53688, '15-721', 'A'),
(53688, '15-826', 'B'),
(53655, '15-445', 'B'),
(53666, '15-721', 'C');

Basic Syntax

最基础的查询语句如下，它的意思是从某个表中根据条件查询出某些列。

select column1, column2, ...
from table 
where predicate1, predicate2,...

例如select name, gpa from student where age > 25;

Aggregates

aggregates 表示一些聚合操作，其含义是从多个 tuple 中通过计算返回一个统计值，下面的几个示例 sql 都比较简单。

AVG

求平均值，通过函数 AVG。

select AVG(gpa) from student;

MIN

求最小值，通过函数 MIN。

select MIN(gpa) from student;

MAX

求最大值，通过函数 MAX。

select MAX(gpa) from student;

SUM

求和，通过函数 SUM。

select sum(gpa) from student;

COUNT

对某列的数据计数，可以加 distinct 去重（avg 和 sum 函数都支持）。

select count(distinct sid) from enrolled;

Group By

group by 表示分组操作，是对 table 中的数据根据某个特征分组，并且可以将分组各自进行聚合计算。如果要对 group by 的分组聚合结果进行过滤，则可以使用 having 子句。下面是一个简单的例子，这条 sql 的含义是根据课程 cid 进行分类，并且统计该课程的平均分。

select avg(s.gpa), e.cid from enrolled as e, student as s where e.sid = s.sid group by e.cid;

image.png

对于 group by 的场景，需要注意几点，一是 select 的列必须包含在 group by 子句中或者是聚合的列，例如我在前面的 sql 中加入一个 s.name 列，报错信息如下：

image.png

二是可以对 group by 后输出的内容进行过滤，只不过需要注意这里过滤并不能使用 where 子句，而是需要结合 having 子句。

select avg(s.gpa) as avg_gpa, e.cid from enrolled as e, student as s where e.sid = s.sid group by e.cid having avg(s.gpa) > 3.9;

String Operations

数据库中的一些函数支持对字符串类型的数据进行处理，下面介绍比较常见的几种。更多的函数可参考 sql-92 标准文档：https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

like

like 可以对字符串进行匹配。

select * from student where login like '%r@cs';

substring

字符串截取

select SUBSTRING(name, 1, 5) from student where sid = 53688;

upper

字符大写

select UPPER(name) from student;

String Concat

字符串连接，sql-92 标准是使用 || 符号，不同的操作系统有不同的实现，例如可以使用 + 或者 concat 函数。

select name || 'is a student' from student;

Date/Time Operations

日期和时间函数也是数据库中基本的处理函数。函数 now 可以返回当前时间。

select now() as current_datetime;

更多函数可参考标准 sql 文档。

Output Control

SQL 可以支持对数据的输出结果进行过滤、排序、重命名等操作。

order by

order by 可以对数据的结果进行排序，基本语法为order by <column*> [ASC|DESC]

select * from student order by gpa desc;

还可以根据多个 column 进行排序。

select sid from enrolled where cid = '15-721' order by grade desc, sid asc;

limit

limit 子句可以控制输出结果的数量。

select * from student limit 1

还可以加 offset 参数控制获取数据的偏移量。

select * from course limit 2 offset 2;

Nested Queries

嵌套查询。

select name from student where sid in (select sid from enrolled);

这类查询主要表达的含义是一个查询的过滤条件依赖于另一个查询的输出，这两个查询前后者分别叫做 outer query 和 inner query。

image.png

在 inner query 之前可以加上一个条件匹配表达式，大致有如下几种：

•ALL：表示必须满足 inner query 中的所有条件•ANY：表示满足 inner query 中任意一条数据•IN：等同于 ANY•EXISTS：inner query 中有一条数据返回即可

exists 前面可以加 not 表示否，例如下面 sql 的含义是查找在 enrolled 中没有 course 记录的数据。

select * from course where not exists (select * from enrolled where course.cid = enrolled.cid);

Window Function

窗口函数可以对一系列与当前行有关联的行进行计算，常见的窗口函数语法如下：

image.png

下面的示例 sql 演示了一个最简单的窗口函数，row_number() 会返回当前行的序号。

select *, row_number() over() from enrolled;

over 子句表示在执行计算时怎么排列数据，例如可以加 partition 关键字进行分组，也可以进行 sort。

select cid, sid, row_number() over (partition by cid) from enrolled order by cid;

select cid, sid, row_number() over (order by cid) from enrolled order by cid;

Common Table Expressions

最常见的是 with as 语句，这种语法的含义是提供一个临时的结果，供其他查询使用，有点类似于一个临时表。

image.png

下面是一个非常简单的例子：

with tab as (select 1)select * from tab;

这一节学习了 sql 相关的基础知识，由于 sql 语法很多，所以并没有涵盖所有的用法，只列举了常见的一些 sql，读者可以参考 sql 文档及其他基础教程。后面开始会正式进入数据库系统设计，首先会来讨论一下数据库中的存储管理模块。