数据库（TDA357、DIT621）

第三讲：SQL 2

• 课堂讲稿：第2章
• 书：第2章

上次

基本SQL

• 创建和删除数据库表
• 插入、更新和删除表中的数据
• 涉及一个表的数据库查询
• 主键
• 约束条件
• 聚合

今天

更多SQL

• 更多聚合示例
• 涉及多个表的数据库和查询
  • 设置操作（并集、交集等）
  • 交叉积和联接
• 外键
• 复合关键点
• 意见

继续使用相同的数据库示例

包含国家信息的数据库

• 国家

  姓名	首都	面积	人口	欧洲大陆	货币
  丹麦	哥本哈根	43094	5484000	欧盟	丹麦克朗
  爱沙尼亚	塔林	45226	1291170	欧盟	欧元
  芬兰	赫尔辛基	337030	5244000	欧盟	欧元
  挪威	奥斯陆	324220	5009150	欧盟	挪威克朗
  瑞典	斯德哥尔摩	449964	9555893	欧盟	瑞典克朗
  …	…	…	…	…	…

继续使用相同的数据库示例

• 第一个架构
  • 国家（名称、首都、地区、人口、大陆、货币）
• SQL语言

  • 创建 表国家(名称TEXT大写TEXT,区域浮动,人口INT,大陆文本,货币TEXT);
    

继续使用相同的数据库示例

• 改进的模式
  • 国家(名称，首都，地区，人口，大陆，货币）
• SQL语言

  • 创建 表国家(名称TEXT主要 钥匙,大写TEXT,区域浮动不是 无效的,总体INT不是 无效的,大陆CHAR(2) 不是 无效的,货币CHAR(三)
    );
    

PostgreSQL Live演示

文件夹

• 国家.tsv（数据来自网址：www.geonames.org)
• 表格.sql
• 插入.sql

聚合

• 各大洲的国家数量和总人口：

• 选择大陆,计数(名称),SUM（总和）(人口)
  发件人国家集团 由大陆;
  

• 聚合功能如计数和SUM（总和）是通常与一起使用分组依据
• 如果我们只想知道不分组的世界？

更多聚合示例

不使用GROUP BY的聚合

• 表中有多少行？

• 选择 计数(*) 发件人国家;
  

• 有多少国家有首都？

• 选择 计数(首都) 发件人国家;
  

• 国家和人口总数是多少？

• 选择 计数(名称),SUM（总和）(人口) 发件人国家;
  

• 有多少大国（人口超过5000万）？

• 选择 计数(*) 发件人国家哪里人口>50000000;
  

通用货币

• 有多少国家使用欧元？

• 选择 计数(*) 发件人国家哪里货币=“欧元”;
  

• 有多少国家使用各种货币？

• 选择货币,计数(名称)
  发件人国家集团 由货币;
  

• 如果我们只想在一个以上的国家使用货币怎么办？

基于聚合值选择行

• 哪些货币被一个以上的国家使用？

• 选择货币,计数(名称)
  发件人国家集团 由货币有 计数(名称)>1;
  

• 哪里 条件在单个行上工作，在前面分组依据.
• 有 条件在聚合值上工作分组依据.

数学中的集合和集合运算

• 集合的概念及其运算来自离散数学。
• 通常用维恩图.
• 示例：
  • 如果A={1,2,5}，B={1,6}，C={4,7}，则
  • 联合：AáB={1,2,5,6}
  • 交叉口：AüB={1}
  • 差异：A\B={2,5}

关系数据库中的集合操作

• 数据库表可以看作是一组行。
• SQL已设置操作：
  • 联合国
  • 横断
  • 除
• 这些可以用于合并查询结果。

关系数据库中的集合操作

参观地点

• 我们认为国家和城市都是地方。
• 列出所有的地方。

•   选择名称AS公司地方发件人国家联合国
    选择首都AS公司地方发件人国家;
  

关系数据库中的集合操作

• 列出首都名称与国家的名称。

• 选择名称发件人国家哪里名称=首都;
  

• 列出与任何国家名称相同的首都名称。

  •   选择名称AS公司地方发件人国家横断
      选择首都AS公司地方发件人国家;
    

关系数据库中的集合操作

• 列出所有国家，并将其分为大小。

•   选择名称,“大” AS公司大小发件人国家哪里人口>=50000000
  联合国
    选择名称,“小” AS公司大小发件人国家哪里人口<50000000 ;
  

包含多个表的数据库

表架构

• 国家(名称，首都，地区，人口，大陆，货币）货币(代码，名称，值）
• 货币

  代码	名称	价值
  瑞典克朗	克朗	1
  丹麦克朗	克朗	1.41
  欧元	欧元	10.54
  GBP公司	英镑	12.50
  美元	美元	9.57
  英国电信公司	比特币	85634.34

包含多个表的数据库

SQL语言

• 创建 表国家(名称TEXT主要 钥匙,大写TEXT,
    ...,                    --和以前一样货币CHAR(三)
  );
  
  创建 表货币(代码CHAR(三) 主要 钥匙,名称TEXT,值FLOAT);
  

• 表格.sql
• 插入.sql

合并表格

笛卡尔积（叉积）

• 选择国家.名称,代码,价值发件人国家,货币哪里货币=代码;
  

• 这首先从两个表生成所有行组合。
  • 6+3=9列
  • 252*6=1512行
• 然后选择满足条件currency=code的行。
  • 在这个例子中，我们得到61行。
• 最后，选择三个感兴趣的列。
  • 结果是一个包含3列61行的表。

合并表格

内部连接

• 这相当于上一页上的查询：

• 选择国家.名称,代码,价值发件人国家加入货币打开货币=代码;
  

• 结果只包含匹配的行。一样：

• 选择国家.名称,代码,价值发件人国家内部 加入货币打开货币=代码;
  

合并表格

外部连接

• 包括所有货币（即使没有国家使用）：

• 选择国家.名称,代码,价值发件人国家正确的 外部 加入货币打开货币=代码;
  

• 包括所有国家（即使货币不在货币表中）

• 选择国家.名称,货币,价值发件人国家左图 外部 加入货币打开货币=代码;
  

• 确保两个表中所有行的信息都包含在结果中

• 选择国家.名称,货币,价值发件人国家FULL（满） 外部 加入货币打开货币=代码;
  

合并表格

自然加入？

• 选择 * 发件人国家自然 加入货币

• 基于同名列的联接。
• 在我们的示例中，它与以下内容相同：

  • 选择 * 发件人国家加入货币使用 (名称)
    

• 它类似于

  • 选择 * 发件人国家加入货币打开国家.名称=货币.名称

• 在我们的示例中没有意义。仅在数据库中有用使用精心选择的列名…

自然连接

一个有意义的例子

• 资本金 国家 毛细管的 瑞典 斯德哥尔摩 挪威 奥斯陆 法国 巴黎

  货币 国家 货币 挪威 挪威克朗 德国 欧元 瑞典 瑞典克朗

• 资本自然 加入货币

• 国家	首都	货币
  挪威	奥斯陆	挪威克朗
  瑞典	斯德哥尔摩	瑞典克朗

  • 仅显示在两个表中的国家（即资本和货币都是已知的）。

自然连接→ 一个有意义的例子

• 资本自然 左图 外部 加入货币

• 国家	首都	货币
  挪威	奥斯陆	挪威克朗
  瑞典	斯德哥尔摩	瑞典克朗
  法国	巴黎

  • 所有国家，即使货币缺失。

• 资本金自然 正确的 外部 加入货币

• 国家	首都	货币
  挪威	奥斯陆	挪威克朗
  瑞典	斯德哥尔摩	瑞典克朗
  德国		欧元

  • 所有货币，即使资本缺失。

自然连接→ 一个有意义的例子

• 资本金自然 FULL（满） 外部 加入货币

• 国家	首都	货币
  挪威	奥斯陆	挪威克朗
  瑞典	斯德哥尔摩	瑞典克朗
  法国	巴黎
  德国		欧元

  • 包含两个表中的所有行。
• 这些示例的SQL代码：自然.sql

外键

• 货币(代码，值）国家(名称，首都，地区，人口，大陆，货币）货币->货币代码

• 创建 表国家(名称TEXT主要 钥匙,大写TEXT,区域浮动不是 无效的,
    ...,货币CHAR(三) 参考文献货币(代码)
  );
  

• 中只允许使用货币表中的货币代码国家表。
• 如果目的是为了让货币表定义一组有效的货币代码。

内联约束与其他约束

• 到目前为止，我们只看到了内联约束。
• 也可以在列定义之后放置约束。

• 创建 表国家(名称TEXT,大写TEXT,区域浮动...,货币CHAR(三),
    
    主要 钥匙 (名称),
    不是 无效的 (地区),
    国外 钥匙 (货币) 参考文献货币(代码)
  );
  

• 你为什么要这样做？

复合关键点

• 主键或外键可以是两个或多个属性的组合。
• 例如：如果我们假设城市名称在一个国家内是唯一的，但不是全球性的：
  • 城市(名称,国家,...)
国家(名称，大写，…）
（首都，名称）->城市（名称，国家）

• 创建 表城市(名称TEXT,国家TEXT,
    ...,
    主要 钥匙 (名称,国家) );
  
  创建 表国家(名称TEXT主要 钥匙大写TEXT,
    ...,
    国外 钥匙 (首都,名称) 参考文献城市(名称,国家) );
  

意见

• 创建 视图汇率AS公司
    (选择名称,代码,价值发件人国家加入货币打开货币=代码);
  

• 为查询提供名称。
• 可以在其他查询中将该名称用作表。
• 有用，例如，当经常一起使用的信息被拆分为多个桌子。

SQL摘要

与上次相比的新事物

• 选择T1类.一,T2段.一--属性名称的表前缀
  发件人T1类,T2段,T3航站楼--许多表=交叉积
  哪里条件集团 由价值有价值--聚合值的条件
  订单 由价值

• 查询联合国/横断/除查询

• 创建 表T型(属性类型参考文献表(属性)
  
    主要 钥匙 (一,b条)
    国外 钥匙 (一,b条) 参考文献表(c（c）,d日)
  )
  

• 创建 视图视图（_N）AS公司 (查询)
  

SQL摘要

连接

• 桌子::=表格名称 桌子 AS公司 表格名称 桌子 自然 参加 桌子 桌子 参加 桌子 怎样
• 怎样::=使用（a、b、c）打开 条件 参加::=内部?加入 [FULL|LEFT|RIGHT]外部?加入

联合与交叉产品

• 这些都是相同的：

• 发件人T1类,T2段哪里条件

• 发件人T1类加入T2段打开条件

• 发件人T1类内部 加入T2段打开条件

本地查询

• 哪些货币被一个以上的国家使用？

• 选择货币,计数(名称)
  发件人国家集团 由货币有计数(名称)>1;
  

• 很高兴有有但没有它我们也能活下去：

• 与货币计数AS公司
    (选择货币,计数(名称) AS公司计数发件人国家集团 由货币)
  选择 *
  发件人货币计数哪里计数>1;
  

嵌套查询

• 哪些货币国家中缺少货币?

  • 选择 与众不同货币AS公司代码发件人国家哪里货币不是 英寸 (选择代码发件人货币);
    

• 插图
  • 在哪里部分。
• 条件::=价值 不是?英寸 (查询)

嵌套查询

• 将所有缺少的货币代码复制到货币：

• 插入 INTO公司货币(选择 与众不同货币AS公司代码发件人国家哪里货币不是 英寸 (选择代码发件人货币)
     );
  

• 插图
  • 的变体插入取值位置根据查询结果。
  • 我们为代码列。其他的列(名称,价值)将缺少值(无效的).

更改表格

• 当所有货币代码都以货币表示时，我们可以添加外币国家的关键

• 改变 表国家添加 国外 钥匙 (货币) 参考文献货币(代码);
  

今天就到此为止

问题？

星期三练习：SQL

周四讲座：实体关系建模