[SQL]基础SQL

SQL简介

SQL是结构化查询语言(Structure Query Language)的缩写，它是使用关系模型的数据库(RDBMS)应用语言。

SQL历史

在1970年代初，由IBM公司San Jose,California研究实验室的Edgar Frank Codd(又称Ted Codd)发表将数据组成表格的应用原则（Codd's Relational Algebra）。1974年，同一实验室的D.D.Chamberlin和R.F. Boyce对Codd's Relational Algebra在研制关系数据库管理系统System R中，研制出一套规范语言-SEQUEL（Structured English Query Language），并在1976年11月的IBM Journal of R&D上公布新版本的SQL（叫SEQUEL/2）。1980年改名为SQL。
1979年ORACLE公司首先提供商用的SQL，IBM公司在DB2和SQL/DS数据库系统中也实现了SQL。¹

Ted Codd是绝对的数据库开创者，以relation model获得1981年图灵奖。我们在关系模型中的Boyce-Codd Normal From(介于第三范式和第四范式)也来自于他的名字。

System R在数据库历史上也绝对的重要。特别是看书的时候不断被提起。

SQL标准

1986年10月，美国ANSI采用SQL作为关系数据库管理系统的标准语言（ANSI X3. 135-1986），后为国际标准化组织（ISO）采纳为国际标准。
1989年，美国ANSI采纳在ANSI X3.135-1989报告中定义的关系数据库管理系统的SQL标准语言，称为ANSI SQL 89，该标准替代ANSI X3.135-1986版本。该标准为下列组织所采纳：
国际标准化组织（ISO），为ISO 9075-1989报告“Database Language SQL With Integrity Enhancement”
美国联邦政府，发布在The Federal Information Processing Standard Publication(FIPS PUB)127
当前，所有主要的关系数据库管理系统支持某些形式的SQL，大部分数据库至少遵守ANSI SQL89标准。
ANSI SQL92标准在交叉连接（cross join）和内部连接之上，新增加了外部连接，并支持在FROM子句中写连接表达式。支持集合的并运算、交运算。支持Case (SQL)表达式。支持CHECK约束。创建临时表。支持cursor。
支持事务隔离。¹

关于此文章

我们这一篇文章采用PostgreSQL的SQL语法。重点我们关注select...from...where这种读操作，分析query　(analytical query)。
数据集在　https://hyper-db.de/interface...　可以直接使用。另外在这个网页不允许进行写操作:insert, update, delete之类的transactional query。当然create table和drop table也不被允许。

架构 Schema:

下载:
https://db.in.tum.de/teaching...

Schma和大部分SQL语句来自Prof. Alfons Kemper, Ph.D.的课件和书。

课件：

• https://db.in.tum.de/teaching...
• https://db.in.tum.de/teaching...

书：　https://db.in.tum.de/teaching...

基础SQL

• 搜索所有rang是C4的教授：

select persnr, name
from professoren
where rang = 'C4'

• 对所有的教授先对rang倒序排序，再对name正序排序：

select persnr, name, rang
from professoren
order by rang desc, name asc

• 得到professoren的rank的所有可能：

select distinct rang
from professoren

• 搜索开Maeeutik这个课的教授：

select p.name, v.titel
from professoren p, vorlesungen v
where p.persnr = v.gelesenvon and titel = 'Maeeutik'

• 列出学生名字和该学生听过的课：

select s.name, v.titel
from studenten s, hoeren h, vorlesungen v
where s.matrnr = h.matrnr and h.vorlnr = v.vorlnr

• 搜索每个课被多少学生听，考虑没有人听的课，进行倒序排序：

select v.vorlnr, v.titel, count(h.matrnr) as num
from vorlesungen v left outer join hoeren h on v.vorlnr = h.vorlnr
group by v.vorlnr, v.titel
order by num desc 

• 搜索assistenten的name和professoren的name的并集：

(select name
from assistenten)
union
(select name
from professoren)

• 搜索没有任何课的教授：

-- correlated sub-query
select p.name
from professoren p
where not exists(
    select *
    from vorlesungen v
    where v.gelesenvon = p.persnr
    )

-- un-correlated sub-query
-- not in:　集合的比较
select p.name
from professoren p
where p.persnr not in (
    select v.gelesenvon
    from vorlesungen v
    )

• 搜索不上任何课的学生：

-- correlated sub-query
select s.name
from studenten s
where not exists(
    select *
    from hoeren h
    where h.matrnr = s.matrnr
    )

-- un-correlated sub-query
-- not in:　集合的比较
select s.name
from studenten s
where s.matrnr not in (
    select h.matrnr
    from hoeren h
    )

• 听大于等于3门课学生

select s.matrnr, s.name
from studenten s, hoeren h
where s.matrnr = h.matrnr
group by s.matrnr, s.name
having count(*) >= 3

• 搜索semester最大的学生：

select s.name
from studenten s
where s.semester >= all (
    select semester
    from studenten
    )

• 求得studenten的平均semester：

select avg(semester)
from studenten

• 搜索sws>=4并且作为至少其他两门课以上的vorgaenger:

select v.vorlnr, v.titel
from vorlesungen v, voraussetzen vor
where v.vorlnr = vor.vorgaenger and v.sws >= 4
group by v.vorlnr, v.titel
having count(*) >= 2

• 搜索每一个教授上的课的SWS的各自总和：

select v.gelesenvon, sum(v.sws)
from vorlesungen v
group by v.gelesenvon

• 搜索有rank C4的professoren中上课总SWS超过３的professoren：

select v.gelesenvon, p.name, sum(v.sws)
from vorlesungen v, professoren p
where v.gelesenvon = p.persnr and rang = 'C4'
group by v.gelesenvon, p.name
having avg(v.sws) >= 3

这里需要注意一下aggregation operation。
对每一个group都会生成一个tuple。所以对于带有group by子句的SQL语句，select子句里面只能是group by子句提到的属性值和aggregation operation。

• 比较的syntax sugar(语法糖)

SELECT *
FROM studenten 
WHERE semester >= 1 AND semester <= 4;

SELECT *
FROM studenten 
WHERE semester between 1 and 4;

SELECT *
FROM studenten 
WHERE semester in (1,2,3,4);

• 字符串比较：

SELECT *
FROM studenten 
WHERE name like 'T%eophrastos';

SELECT DISTINCT s.name
FROM vorlesungen v, hoeren h, studenten s
WHERE s.matrnr = h.matrnr AND h.vorlnr = v.vorlnr AND v.titel LIKE '%thik%';

• case：

SELECT matrnr, 
            (case when note <= 1.5 then 'sehr gut'
               when note <= 2.5 then 'gut'
               when note <= 3.5 then 'befriedigend'
               when note <= 4.0 then 'ausreichend'
               else 'nicht bestanden'
            end)
FROM pruefen;

• left outer join:

SELECT p.persnr, p.name, f.persnr, f.note, f.matrnr, s.matrnr, s.name
FROM professoren p left outer join pruefen f left outer JOIN studenten s ON f.matrnr = s.matrnr ON p.persnr = f.persnr;

• right outer join:

SELECT p.persnr, p.name, pf.persnr, pf.note, pf.matrnr, s.matrnr, s.name
FROM professoren p right outer JOIN pruefen pf right outer JOIN studenten s ON pf.matrnr = s.matrnr ON p.persnr = pf.persnr;

• full outer join:

SELECT p.persnr, p.name, pf.persnr, pf.note, pf.matrnr, s.matrnr, s.name
FROM professoren p full outer JOIN pruefen pf full outer JOIN studenten s ON pf.matrnr = s.matrnr ON p.persnr = pf.persnr;

• 制作一个乘法表(Multiplication table)

-- precompute look up table
with mtable as (values
( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
( 2, 4, 6, 8, 10, 12, 14, 16, 18, 20),
( 3, 6, 9, 12, 15, 18, 21, 24, 27, 30),
( 4, 8, 12, 16, 20, 24, 28, 32, 36, 40),
( 5, 10, 15, 20, 25, 30, 35, 40, 45, 50),
( 6, 12, 18, 24, 30, 36, 42, 48, 54, 60),
( 7, 14, 21, 28, 35, 42, 49, 56, 63, 70),
( 8, 16, 24, 32, 40, 48, 56, 64, 72, 80),
( 9, 18, 27, 36, 45, 54, 63, 72, 81, 90),
( 10, 20, 30, 40, 50, 60, 70, 80, 90, 100)
)
select * from mtable

或者

with mtable_column(a, b, c, d, e, f, g, h, i, k) as (values
(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) -- 其实被写成一行
),
mtable_row(x) as (values
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10) -- 其实被写成一列
)
select
a * x,
b * x,
c * x,
d * x,
e * x,
f * x,
g * x,
h * x,
i * x,
k * x
from mtable_column, mtable_row

---

1. SQL 维基百科. https://zh.wikipedia.org/wiki... ↩