本章將會(huì)介紹一些常用的 sql 語(yǔ)法。注意這個(gè)課程只要是為了講解數(shù)據(jù)庫(kù)的設(shè)計(jì)，但同時(shí)對(duì) sql 也要有一定的了解，不然沒(méi)法進(jìn)行后續(xù)學(xué)習(xí)，所以這里做一個(gè)簡(jiǎn)單的介紹，讀者也可以查看其它的 sql 基礎(chǔ)教程。

SQL 即結(jié)構(gòu)化查詢語(yǔ)言（Structured Query Language），是數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)操作語(yǔ)言，它大致包含了這幾種類型：

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

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

為了演示后續(xù) sql 的用法，將會(huì)以下面的幾張表做為 demo 數(shù)據(jù)。

我是以 PostgreSQL 為例進(jìn)行講解的，但是本文中設(shè)計(jì)的 sql 語(yǔ)法大多比較通用，要是在其他的數(shù)據(jù)庫(kù)中沒(méi)看到相應(yīng)的用法，可以查看其官方文檔，看是否有其他的寫(xiě)法或者該 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

最基礎(chǔ)的查詢語(yǔ)句如下，它的意思是從某個(gè)表中根據(jù)條件查詢出某些列。

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

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

Aggregates

aggregates 表示一些聚合操作，其含義是從多個(gè) tuple 中通過(guò)計(jì)算返回一個(gè)統(tǒng)計(jì)值，下面的幾個(gè)示例 sql 都比較簡(jiǎn)單。

AVG

求平均值，通過(guò)函數(shù) AVG。

select AVG(gpa) from student;

MIN

求最小值，通過(guò)函數(shù) MIN。

select MIN(gpa) from student;

MAX

求最大值，通過(guò)函數(shù) MAX。

select MAX(gpa) from student;

SUM

求和，通過(guò)函數(shù) SUM。

select sum(gpa) from student;

COUNT

對(duì)某列的數(shù)據(jù)計(jì)數(shù)，可以加 distinct 去重（avg 和 sum 函數(shù)都支持）。

select count(distinct sid) from enrolled;

Group By

group by 表示分組操作，是對(duì) table 中的數(shù)據(jù)根據(jù)某個(gè)特征分組，并且可以將分組各自進(jìn)行聚合計(jì)算。如果要對(duì) group by 的分組聚合結(jié)果進(jìn)行過(guò)濾，則可以使用 having 子句。下面是一個(gè)簡(jiǎn)單的例子，這條 sql 的含義是根據(jù)課程 cid 進(jìn)行分類，并且統(tǒng)計(jì)該課程的平均分。

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

對(duì)于 group by 的場(chǎng)景，需要注意幾點(diǎn)，一是 select 的列必須包含在 group by 子句中或者是聚合的列，例如我在前面的 sql 中加入一個(gè) s.name 列，報(bào)錯(cuò)信息如下：

二是可以對(duì) group by 后輸出的內(nèi)容進(jìn)行過(guò)濾，只不過(guò)需要注意這里過(guò)濾并不能使用 where 子句，而是需要結(jié)合 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

數(shù)據(jù)庫(kù)中的一些函數(shù)支持對(duì)字符串類型的數(shù)據(jù)進(jìn)行處理，下面介紹比較常見(jiàn)的幾種。更多的函數(shù)可參考 sql-92 標(biāo)準(zhǔn)文檔：https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

like

like 可以對(duì)字符串進(jìn)行匹配。

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

substring

字符串截取

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

upper

字符大寫(xiě)

select UPPER(name) from student;

String Concat

字符串連接，sql-92 標(biāo)準(zhǔn)是使用 || 符號(hào)，不同的操作系統(tǒng)有不同的實(shí)現(xiàn)，例如可以使用 + 或者 concat 函數(shù)。

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

Date/Time Operations

日期和時(shí)間函數(shù)也是數(shù)據(jù)庫(kù)中基本的處理函數(shù)。函數(shù) now 可以返回當(dāng)前時(shí)間。

select now() as current_datetime;

更多函數(shù)可參考標(biāo)準(zhǔn) sql 文檔。

Output Control

SQL 可以支持對(duì)數(shù)據(jù)的輸出結(jié)果進(jìn)行過(guò)濾、排序、重命名等操作。

order by

order by 可以對(duì)數(shù)據(jù)的結(jié)果進(jìn)行排序，基本語(yǔ)法為order by <column*> [ASC|DESC]

select * from student order by gpa desc;

還可以根據(jù)多個(gè) column 進(jìn)行排序。

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

limit

limit 子句可以控制輸出結(jié)果的數(shù)量。

select * from student limit 1

還可以加 offset 參數(shù)控制獲取數(shù)據(jù)的偏移量。

select * from course limit 2 offset 2;

Nested Queries

嵌套查詢。

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

這類查詢主要表達(dá)的含義是一個(gè)查詢的過(guò)濾條件依賴于另一個(gè)查詢的輸出，這兩個(gè)查詢前后者分別叫做 outer query 和 inner query。

在 inner query 之前可以加上一個(gè)條件匹配表達(dá)式，大致有如下幾種：

• ALL：表示必須滿足 inner query 中的所有條件
• ANY：表示滿足 inner query 中任意一條數(shù)據(jù)
• IN：等同于 ANY
• EXISTS：inner query 中有一條數(shù)據(jù)返回即可

exists 前面可以加 not 表示否，例如下面 sql 的含義是查找在 enrolled 中沒(méi)有 course 記錄的數(shù)據(jù)。

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

Window Function

窗口函數(shù)可以對(duì)一系列與當(dāng)前行有關(guān)聯(lián)的行進(jìn)行計(jì)算，常見(jiàn)的窗口函數(shù)語(yǔ)法如下：

下面的示例 sql 演示了一個(gè)最簡(jiǎn)單的窗口函數(shù)，row_number() 會(huì)返回當(dāng)前行的序號(hào)。

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

over 子句表示在執(zhí)行計(jì)算時(shí)怎么排列數(shù)據(jù)，例如可以加 partition 關(guān)鍵字進(jìn)行分組，也可以進(jìn)行 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

最常見(jiàn)的是 with as 語(yǔ)句，這種語(yǔ)法的含義是提供一個(gè)臨時(shí)的結(jié)果，供其他查詢使用，有點(diǎn)類似于一個(gè)臨時(shí)表。

下面是一個(gè)非常簡(jiǎn)單的例子：

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

這一節(jié)學(xué)習(xí)了 sql 相關(guān)的基礎(chǔ)知識(shí)，由于 sql 語(yǔ)法很多，所以并沒(méi)有涵蓋所有的用法，只列舉了常見(jiàn)的一些 sql，讀者可以參考 sql 文檔及其他基礎(chǔ)教程。后面開(kāi)始會(huì)正式進(jìn)入數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)，首先會(huì)來(lái)討論一下數(shù)據(jù)庫(kù)中的存儲(chǔ)管理模塊。