CMU 15445 學習之Advanced SQL

作者：roseduan 2022-10-08 00:00:00

數據庫其他數據庫

DML 是數據操作，例如增刪改查數據，DDL 是數據定義，例如新建一個表，增加一個索引等。此外還包含其他的一些操作，例如視圖、事務等。

本章將會介紹一些常用的 sql 語法。注意這個課程只要是為了講解數據庫的設計，但同時對 sql 也要有一定的了解，不然沒法進行后續學習，所以這里做一個簡單的介紹，讀者也可以查看其它的 sql 基礎教程。

SQL 即結構化查詢語言（Structured Query Language），是數據庫的標準操作語言，它大致包含了這幾種類型：

Data Manipulation Language (DML)
Data Definition Language (DDL)

DML 是數據操作，例如增刪改查數據，DDL 是數據定義，例如新建一個表，增加一個索引等。此外還包含其他的一些操作，例如視圖、事務等。

為了演示后續 sql 的用法，將會以下面的幾張表做為 demo 數據。

我是以 PostgreSQL 為例進行講解的，但是本文中設計的 sql 語法大多比較通用，要是在其他的數據庫中沒看到相應的用法，可以查看其官方文檔，看是否有其他的寫法或者該 feature 不支持。

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;

對于 group by 的場景，需要注意幾點，一是 select 的列必須包含在 group by 子句中或者是聚合的列，例如我在前面的 sql 中加入一個 s.name 列，報錯信息如下：

二是可以對 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 name || 'is a student' from student;

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。

在 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

窗口函數可以對一系列與當前行有關聯的行進行計算，常見的窗口函數語法如下：

下面的示例 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 語句，這種語法的含義是提供一個臨時的結果，供其他查詢使用，有點類似于一個臨時表。

下面是一個非常簡單的例子：

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

這一節學習了 sql 相關的基礎知識，由于 sql 語法很多，所以并沒有涵蓋所有的用法，只列舉了常見的一些 sql，讀者可以參考 sql 文檔及其他基礎教程。后面開始會正式進入數據庫系統設計，首先會來討論一下數據庫中的存儲管理模塊。

責任編輯：武曉燕來源： roseduan寫字的地方