数据库系统概论 第三章

SQL

数据定义语言

1. 作用：定义了每个关系的信息说明，包括：
   每个属性的域
   完整性约束
2. 域的数据类型

• char(n)：长度为固定n个字符的字符串
• varchar(n)：长度为人为定义的n个字符的可变长度字符串
• int：整型，具体大小与机器有关
• smallint：比int小的整型
• numeric(p,d)：数学性质的数字，较精确、误差较小，p代表有几位有效数字、d代表小数点右侧有多少位小数
• real, double precision：单精度浮点数、双精度浮点数，精度由机器确定
• float(n)：用户定义精度有多少位

3. 创建一个表的语句结构
   create table r(A1 D1, A2 D2, A3 D3, A4 D4, … , An Dn, (完整性约束1), … , (完整性约束k))
   其中r是表的名字，Ai代表第i个属性的名字，Di代表第i个属性的域的类型
   
   完整性约束（Integrity Constraints）：分为“not null”和“primary key(A1, A2, A3, …, An)”两种，not null放在对应属性类型的后面（如上图），primary key放在所有属性的后面，如下图：
   
4. 删除一个表的结构：
   drop table r 和 delete table r
   drop比delete的删除程度更大，drop过后关系表的模式也会被删除，即之后不能再用对应关系表的类型声明一个关系表的实例；而delete只是删除的表的所有元组信息，没有删除关系表的模式，即之后仍然是可以用这个模式创建更多的关系表的实例的
   delete from r 不能写成 delete * from r！！！！
   每个表在数据库系统表中都有一行存储其结构等信息，当用drop table r时会在系统表中查找名为r的一行，然后删除这一行定义r表类型和结构的数据，即删除表r的结构信息。
5. 向已有的关系表中增加/删除属性的结构：
   alter table r add A D
   r是已有的关系表，A是要新增的属性的名字，D是新增的属性的域。新增的属性的所有元组的值都为null。
   alter table r drop A
   r是已有的关系表，A是要删除的属性的名字（但是许多数据库不支持这个语句）

基础查询结构

1. SQL基于关系代数和关系演算
2. SQL形式：
   
   执行顺序：from, where, group by, having, select, order by
   select A1,A2,A3, …, An from r1, r2, …, rm where P
   其中，Ai是要筛选出来的各个属性的名字，ri是所有这些属性会涉及到的所有的关系表，P是谓词表达式（可以理解为筛选的条件）
3. 查询语句 与 关系代数表达式的作用是相同的，可以和相应的关系代数表达式互相转换
4. SQL查询的结果是一个关系表
5. select from where分别举例：

• select
  与关系代数的投影的作用相似，主要看例子图：
  
  select的属性名是不区分大小写的，也就是Branch_Name、BRANCH_NAME、branch_name三者是完全相同的；
  select返回的关系表中的数据可能有重复，因为默认是select all A1, A2…，也可以写select * from …， 如果不想数据重复，可以写select distinct A1,A2… from r1，如下图：
  
  同时，select后方的属性名还可以进行一些算术操作，比如+、-、*、/等等，会对对应属性列的所有值都有改变
  一个select只能有一个distinct关键字！！！！
• from
  表示所选择的数据都来自哪些关系表，如果有多个用逗号隔开的关系表，则表示在这几个关系表做笛卡儿积后的结果中进行筛选。
• where
  就是用一些等式或者不等式指明了筛选的条件，逻辑连接词有and、or、not，操作符有<、>、<=、>=、==、<>（表示不等于）。这些操作符可以用于算术表达式、字符串、特殊的数据类型。
  where用于筛选某些行，而having用于筛选某些分好的组（所以有having则having的前面必有group by，因为先要分组了才有筛选的东西、having 是对分组后的数据进行限定的）
  除此之外，在SQL中还有between and操作符，如下：
  

6. 重命名操作(Rename Operation)
   在select所跟的每个属性的后面都可以加上as关键词，可以表示将这个属性重命名为新的名字，如下：
   
   表和属性都可以取别名，因为①可能名字太长需要简化②可能表自身可能会复制一份然后与自己进行比较。

7. 元组变量
   就是在from后面所跟的各个关系表的名字后面也可以加上as关键词，表示可以用as后面的名字代替as前面的这个关系表的名字，起到简化的作用，在select和where中都可以用新的名字。
   关键字as可能会被省略

8. 字符串操作
   可以实现字符串的连接、大小写互换、查找字符串长度、抽取子串等
   like “%main%”：作用是匹配所有含有“main”的字符串
   like “main_”：作用是返回所有包含“main”且其后方有一个字符的字符串
   like “_main”：作用是返回所有包含“main”且其前方有一个字符的字符串
   = 和 like的区别：=只能匹配字符串常量，而like可以匹配常量也可以匹配类似的字符串，相当于=加通配符
   特殊：如果需要匹配“8%”则需要用转义字符，即"8%" except “”

9. 元组排序
   通过order by句子实现，放在select from where的后面，order by后面跟要按照什么属性排序，如下图：
   
   同时，在order by 后面的每个属性的后面也可以跟上desc或者asc分别表示降序或者升序排列，如下：
   
   注：对每个属性，默认排序是升序排列的

10. 注意点！！！

• 元组排序和分组的关键字， 在涉及到多个属性的时候，会优先以前面的属性为主，即先按照前面的属性进行排序，如果出现了按照前面的属性排序无法将所有元组以一种唯一的方式排序，则依次按照后面的所有的属性进行排序
• SQL不自动去重！
• SQL不自动去重！
• SQL不自动去重！，在投影等操作的时候需要对结果进行手动去重！！！！！

集合运算

1. 集合操作并集（union）交集（intersect）差集（except），都与关系代数的∪、∩、- 对应
   都会默认去掉重复的元素
   如果不想去重，就对应地用union all、intersect all、except all
   举例：
   如果一个元组a，在关系r和s中分别出现了m次和n次，则：
   ①r union all s 中a会出现m+n次
   ②r intersect all s 中a会出现min(m,n)次
   ③r except all s中a会出现max(0, m-n)次
   ④s except all r中a会出现max(0, n-m)次

聚集函数

1. 作用：对一个关系表中，某列的多个值的集合进行操作，并根据操作的不同，返回一个对应的值。

2. 操作的类型：avg、min、max、sum、count

3. Group By函数：
   放在select from where句子的末尾，后跟要分组的参照属性，如下：
   
   注：select句子中，在聚集函数外部的属性必须在group by的列表中出现
   group by A, B 则生成组的个数为A × B，但是输出时每一组只能输出为一行
   where不能放在group by后面！！
   count函数的括号内要根据需要添加distinct去重
   count函数的括号内不能同时出现*和distinct！！！
   所有的聚集函数不能不能不能出现在where句当中！！！

4. Having函数：
   与Where函数作用相同，只是位置不同。
   Having函数出现在Group By函数之后；Where函数出现在Group By函数之前。
   

null 值：

谓词表达式：is null，可以用来检测null值，比如：

①所有包含null的算术表达式都等于null
②除了“null is null”为真，其余的比较式子都返回unknown
③三态逻辑表达式除了明确能断定是true或者false的，其余的都为unknown。null = null 结果为null，null is null结果为true。
④where句子中的谓词表达式，如果表达式结果为unknown，则当作false
⑤聚集函数中，除了count(*)，其余的都会省略聚集的属性列中有null值的元组，如：
select sum(amount) from loan，如果amount列没有非null的值，则返回null，否则会跳过amount为null的元组
null 一般都会设置缺省值，避免将本身有正确准确值的数据改成了null

嵌套查询语句

1. 也称子查询，就是在select-from-where表达式中又有另外的一个select-from-where的表达式
2. 用途：子查询通常用于对集合成员或者集合比较或者集合的个数（set cardinality）的测试
   ①集合成员测试（test for set membership）：就是用in或者not in关键字来检测某个属性是否在另一个关系表中
   ②比较符号 + some：只要左侧与右侧关系表中的任意一个满足比较符号的关系，整个式子即为真，如下：
   
   ③比较符号 + all：
   左边的属性与右边的关系表中的每个属性都满足比较符号代表的关系，则式子为真，如下：
   
   ④exists和not exists关键字：
   exists®, 即判断r是否为空，如果r为空，则整体返回false，否则返回true
   not exists®, 如果r为空，则返回false，否则返回true
   exist语句所在的外层语句中的表中的数据必须在exist的括号内出现并会使exist的括号产生不同的真值结果
   from A where exist() 是将M数据表中的每一行、每一个元组提取出来到exist的括号中去进行比较，以判断exist括号内的语句的真假，此时exist中必有数据是和外面的表A中取出的数据相同，即表A中的数据一定一定一定要在exist里面用到！！！
   ⑤unique关键字：
   判断子查询的关系表中是否有一个元组出现了重复，如果有则返回false，否则返回true，如下：
   
   注：
   外层重命名的名称对于内层的查询函数而言是可见的，可以直接使用。如上图的T
   primary key和unique的区别：primary key不能有空置，而unique可以有空值

⑥派生关系（Derived Relations）：
SQL可以在from的后面跟一个子查询的语句，子查询返回一个数据表，子查询的语句只在当前语句有效，是临时的，而且子查询语句的结果需要根据实际情况进行重命名，如下：

⑦With语句
with可以在其出现的句子中使用一个临时的名字来代替with中的as后面所产生的关系表，类似于关系代数中为临时变量赋值的操作，会创建一个临时视图，只在当前查询句中有效，这个临时的名字只能在with出现的外层查询句子中使用，如下：

当多个with语句同时出现的时候，从前往后从左往右为要计算的先后顺序，即右边的with可以用左边的with中的名字，如下： （那左边的能不能用右边with中的名字呢？）

视图（Views）

1. 创建view：
   create view v as
   其中query expression是任意合法的SQL表达式，用v所代替的名字可以代指这个视图所产生的虚拟关系表。
   视图就是为用户提供一个类似于接口的东西，用户通过这个“接口”能访问一部分数据。
   真正的数据还是保存在数据库里的
2. 视图分类：

• 视图，默认是普通视图，普通视图不包含数据，只包含怎么去查询、获取数据。
• 物化视图，包含数据，真正会存储数据，而且需要随基表变化而更新-----两种物化视图：
  ①on demand：即在需要更新的时候才更新
  ②on commit即一旦基表有了表单的提交就更新

3. 视图的使用步骤
   ①检查视图所涉及到的表是否存在、所涉及到的数据的有效性如何
   ②与外层的查询语句结合
   ③从数据库中获取数据并返回
   当一个视图被创建的时候，查询的表达式被保存在了数据库中，表达式可以通过这个视图被代入到查询语句中
   就是用所创建的视图变量名代替一长串的查询语句
4. 使用其他视图定义新的视图：
   当在定义视图v1的时候，表达式中使用到了视图关系v2，则称v1是直接依赖于v2的
5. 查询语句中的select和from是两个必须出现的关键字
6. where P中P的可能情况：

• 比较：attr OP attr / constant / （some / all）selectxxxxx
  OP可能为：==；>=；<=；>；<；<>；!=
• 范围：attr (not) between xxx and …
• 集合：attr (not) in select / {(value1 value2),(value3, value4)} 即可以是select选择出来的数据表也可以是人为编码写的一个数据表
• 字符串匹配：attr (not) like 目标字符串， 目标字符串可以包含“%”、“_” 等通配符
• 空值检查：attr is [not] null
• (not) exist (xxx)
• 多重表达式 （这个还有问题…）

数据库更改

连接的关系