[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。1

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。
支持事务隔离。1

关于此文章

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

架构 Schema:
schema_de

schema_en

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

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

课件:

书: 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超过3的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;


-- 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...
阅读 1.1k

1 声望
1 粉丝
0 条评论
1 声望
1 粉丝
文章目录
宣传栏