东南大学数据库课程03-User Interfaces and SQL Language

User Interfaces and SQL Language 用户接口和SQL语言

目录

• User Interfaces and SQL Language 用户接口和SQL语言
• User interface of DBMS
• SQL Language概况
  • Relational Query Languages
  • Formal Relational Query Languages
  • SQL Language
  • Important terms and concepts
• Example Instance and Basic Grammar
  • Example
  • Basic Grammar
  • Conceptual Evaluation Strategy
• 3.1 简单的SQL查询语句
• 3.2 基于集合
• 3.3 基于嵌套子查询
• 3.4 除法与聚集函数
  • Division除法
  • Aggregate Operators 聚集函数
• 3.5 分组(GROUP BY)聚集函数
  • 分组的动机
  • 基本语法
  • 例子
• 3.6 CAST与CASE表达式
• 3.7 Sub Query
  • Scalar sub query 标量子查询
  • table expression：表表达式
  • common table expression 公共表表达式
• 3.8 Outer Join
• 3.9 Recursion 递归
• Data Manipulation Language
• View in SQL
• Embedded SQL
• Dynamic SQL
• Store Procedure

User interface of DBMS

A DBMS must offer some interfaces to support user to access database, including:

• Query Languages
• Interface and maintaining tools (GUI)
• APIs
• Class Libarary

一是用于人机交互，二是用于程序调用

Query Language分类

• Formal
• Tabular
• Graphic
• Limited Natural Language

SQL Language概况

Relational Query Languages

Formal Relational Query Languages

SQL Language

• It can be divided into four parts acording to function:
  • Data Definition Language(DDL)：used to define, delete, or alter data schema
  • Query Language(QL)：used to retrieve data
  • Data Manipulation Language (DML)：used to insert, delete, or update data.
  • Data Control Language (DCL)：used to control user’s access authority to data.
• QL and DML are introduced in detail in this chapter

Important terms and concepts

• Basic Table : 存在数据库里的表【基表】
• View :视图，虚表：根据基表计算出来的（三层抽象）
• Data type supported
• NULL
• UNIQUE：说明某个表的属性是否允许重复【主键只能有一个，必然不允许重复，其他不允许重复的属性就可以用UNIQUE指定】
• DEFAULT：没填的属性值，填默认值
• PRIMARY KEY：主键
• FOREIGN KEY：外键：检查引用完整性约束
• CHECK (Integration Constraint)

Example Instance and Basic Grammar

Example

Basic Grammar

💡 Basic Idea：从realation-list【几张表】，中选择使qualification为真的tuple，然后这些tuple投影到target-list【一组属性】，得到的即为查询结果

注：正如Join时所讲的，系统一般不会主动删除重复元组，除非用户指定，指定词就是DISTINCT 如果对结果没有影响的话一般不加

Conceptual Evaluation Strategy

1. 先做笛卡尔乘积，在剔除不符合条件的元组，得到一组符合条件的元组
2. 将符合条件的元组在投影到对应的元素上，最后如果必要就去重得到结果。

低效，但可以先这么理解，后面会讲优化

3.1 简单的SQL查询语句

R.sid, R.sid, R.bid中的S，R成为Range Variables(范围变量），要写全，更规范

Find sailors who’ve reserved at least one boat

• 在Select中加入DISTINCT有影响吗：没有
• 如果把SELECT中的S.sid换成S.sname，此时加上DISTINCT有影响吗：有，可能会删除同名水手

3.2 基于集合

💡 [这里直接把并操作中的UNION改成AND是不行的，因为没有一艘船既是红又是绿。 所以需要把S,R,B的连接表再做一次自连接，让两次选船的情况体现在两个attribute里，再进行筛选即可](https://www.notion.so/User-Interfaces-and-SQL-Language-SQL-daaee1b4b41641099104a413f0a69f54?pvs=21)

集合运算表达：Union、Insert、EXCEPT、IN、NOT IN

量词表达：IN、NOT IN、EXISTS、NOT EXIST、UNIQUE、NOT UNIQUE、ANY、ALL

S.sid不能改为s.sname因为有重名的可能

注意空值NULL

NULL表示unknown或者Inapplicable

NULL的加入使得SQL变成三值语句：0/1/NULL，许多运算如AND\OR\NOT\Union需要考虑更多

常见做法是直接判断是否为空值再运算

3.3 基于嵌套子查询

非关联

嵌套查询
得到一个Set，看S.sid是否在Set里
在就输出名字
O(N+N)

关联

嵌套查询，逻辑是两层循环
把外层的S.sid带入内循环不断循环

O(N*N)

内循环：除了R1.sid水手以外的水手定过的船的集合

如果外层的bid不在上面的集合里，说明这条船只被R1.sid这个水手订过，即这条船只被订过一次

启发：不同的说法会产生不同的方法，用属性来转换，正反面转换

//非关联写法：
SELECT bid
FROM Reserves R
WHERE bid NOT IN(
	SELECT bid
	FROM Reserves R1, Reserves R2
	WHERE R1.sid != R2.sid AND R1.bid == R2.bid) [被订过不止一次的船的集合]

3.4 除法与聚集函数

Division除法

💡 除法用于“所有”关键字的题目，基本思想是“否定之否定”

Find sailors who’ve reserved all boats

内层嵌套查询：该水手没有订过的船

NOT EXISTS 没有订过的船，说明该水手订过所有船

Aggregate Operators 聚集函数

聚集函数返回的是一个值

• Significant extension of relational algebra.
  • COUNT (*)：Relation的tuple数
  • COUNT ( [DISTINCT] A)：属性A有多少种[不同的]值
  • SUM ( [DISTINCT] A)
  • AVG ( [DISTINCT] A)
  • MAX (A)
  • MIN (A)
• A is a single column

最后一个表示查找最高级水手的名字

第一段文字：SELECT中的聚集函数通常作用在GROUP BY的分组中

3.5 分组(GROUP BY)聚集函数

分组的动机

基本语法

1. 将relation-list中的表用qualification进行筛选，得到一组符合条件的tuple set
2. 将tupel set 按group-list中的属性进行分组，满足group-qualification的分组会被保留
3. 将得到的分组用target-list中的聚集函数进行运算，并投影到target-list中的属性

💡 注意点：要保证分组的独特性 1. group-qualification对分组进行筛选，所以要保证这一个判断对任何一个分组都是没有二值的 2. SELECT要保证不同的分组投影到某一属性后不会出现同值的现象，否则分组的特异性也会消失 由于SQL不会根据业务规则灵活判断这一点，所以它提供简单的语义检查来保证这一点 ：SELECT和HAVING中出现的元素必须是group-list的子集

例子

💡 将自然语言翻译从SQL语言

注意：SELECT中的聚集函数MIN是对每个分组group使用的，而不是整个relation

rating 3 的分组被HAVING过滤掉了，不参与聚集

---

逻辑上可以，但是由于数据库系统在语义上规定HAVING中的Attribute必须是GOUPBY的子集，所以不行，数据库会报错

可以写为

SELECT B.bid, COUNT(*) AS scount
FROM Boats B, Reserves R
WHERE R.bid = B.bid
GROUP BY B.bid, B.color = 'red' 【要等号吗】
HAVING B.color = 'red'

也可以写为

SELECT B.bid, COUNT(*) AS scount
FROM Boats B, Reserves R
WHERE R.bid = B.bid
GROUP BY B.bid
HAVING B.bid IN (SELECT bid
				FROM Boats
				WHERE color = 'red')

---

这里的Sailors S2是指整个relation而不是指group

---

聚集函数不能嵌套使用，而且第一段语句的查询逻辑是错误的

💡 FROM语句中嵌套了一个Query，得到了一张临时表，这个用法很常见

3.6 CAST与CASE表达式

CAST表达式：强制类型转换

创建View的语法

CREATE VIEW prospects (name, school, service) AS 

CASE表达式：就是if语句

例子

3.7 Sub Query

• Embedded query & embedded query with correlation【相互关系】
• The functoins of sub-queries have been enhanced in new SQL standard. Now they can be used in SELECT and FROM and WHERE clause
  • Scalar sub-query：标量子查询【查询结果是一个值（查最大年龄的水手的名字）】
  • Table expression：表表达式【得到的结果是一个表，一个集合，FROM中建立临时表】
  • Common table expression：公共表表达式【同一个表表达式不止出现一次，课定义为公共表表达式，计算一次，多次使用】

Scalar sub query 标量子查询

emp：employee

FROM、WHERE得到了一组d.location=‘New York’ 的tuple set，将这组tuple set送入sub-query再次进行查询

table expression：表表达式

common table expression 公共表表达式

💡 启发： 封装的好处： 1. 可读性：起名 2. 复用性：调用就行，不必重写 3. 易维护：改一个原型，调用出自动生效 4. 细节屏蔽：js.map

需要两次查询的地方都可以用自连接来简化表达

💡 WITH关键字用来建临时表

3.8 Outer Join

要求：建立一张表，表示Fall 96（96年秋季）的教师授课情况

（name, rank, subject, enrollment）

有老师上的课程四个属性全满

没老师上的课程前两列为NULL

空闲的老师后两列为NULL

3.9 Recursion 递归

这里的下属可以是下属的下属，所以需要递归

由于结构是树，所以递归会自动结束

---

有向无环图，递归会自动结束

---

||字符串拼接

递归终止条件

由于下属那题的结构是树，飞机零件那题的结构是有向无环图，最终递归都会终止

但本题是有向有环图，所以必须要保证递归终止

Data Manipulation Language

View in SQL

temporary:View的定义也不保存

Embedded SQL

宿主变量

GRADEI：GRADE Indicator表明是否为空

CURSUR

💡 Cursor:

理解成一个文件
open Cursor就执行Cursor对应的SQL语句
用完之后CLOSE
返回的结果set，有一个读写指针，每读取一个结果指针就下移一位

执行过程

Dynamic SQL

place holder：占位符； macro processin：宏处理； :y：不是一个宿主变量，仅仅是一个place holder

:birth year：填充占位符的需要是一个宿主变量

USING :GIVENCNO：填充place holder

Store Procedure