计算机基础

数据库原理

发布日期: 2022-10-03

1. SQL查询语言概览

数据库定义语言(Data-Definition Language DDL):SQLDDL提供定义关系模式、删除关系以及修改关系模式的命令
数据操纵语言(Data-Manupulation Language,DML):SQL DML提供从数据库中查询信息以及在数据库中插入元组、删除元组、修改元组的能力
完整性(integrity):SQLDDL包括定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束。破坏完整性约束的更新是不允许的
视图定义(view definition):SQLDDL包括定义视图的命令
事务控制(transaction control):SQL包括定义事务的开始和结束的命令
嵌入式SQL和动态SQL（embedded SQL和dynamic SQL)
授权(authorization):定义对关系和视图的访问权限的命令

2. SQL数据定义

DDL不仅能够定义一组关系，还能够定义每个关系的信息

2.1 基本类型

char(n):固定长度的字符串,用户指定其长度n
varchar(n):可变长度的字符串，用户指定的最大长度n
int:整数类型(和机器相关的整数的有限子集)
smallint:小整数类型(和及其相关的整数类型的子集)
numeric(p,d):定点数,精度由用户指定。这个数有p位数字，加上一个符号位，其中d位数字在小数点的右边
real、double preciosion:浮点数与双精度浮点数
float(n):精度至少为n位浮点数

关于空值(null):它表示一个确实的值，该值可能存在但是不为人所知，或者根本不存在，在可能的情况下，我们希望禁止加入空值。

关于char数据类型与varchar:char数据类型用来存放固定长度的字符串，例如，属性A的类型是char(10)，如果我们存入的字符串是Avi，那么该字符串会追加7个空格来使其达到10个字符的串长度。

然后我们在属性B中存入字符串Avi，则不会追加空格。

当比较两个char类型的值的时候，如果它们的长度不同，在比较之前会自动在短值后加入额外的空格使他们的长度一致。

当比较一个char类型和varchar类型的时候，这时候是否在短值上补充空格则是取决于数据库系统

SQL提供nvarchar类型来存放使用Unicode表示多语言数据。

2.2 基本模式定义

我们用create table命令来定义SQL关系。例如:

create table department
(
    dept_name varchar(20),
    building varchar(15),
    budget numeric(12,2),
    primary key(dept_name)
);

SQL支持许多不同的完整性约束

primary key(A1,A2,…,An):这个声明表示属性(A1,A2,....,An)构成关系的主码，主码属性必须非空并且唯一
foreign key(A1,A2,…,An) referebce s:foreign key表示关系中任意元组在属性(A1,A2,...,An)上的取值必须对应于关系s中某元组在主码属性上的取值
not null:一个属性上的not null约束表名在该属性上不允许空值，此约束在空值排除在该属性域之外。

我们可以使用delete命令从关系中删除元组,delete from student，该命令将从 student中删除所有的元组，其他格式的删除命令允许指定待删除的元组。

如果想要从SQL数据库中去掉一个关系，我们可以使用drop table命令,drop table命令从数据库中删除关于被去掉关系的所有信息

drop table r是比delete from r更强的语句。

具体表现为后者是保留关系r的，但是会删除r中的所有元组。

前者则是不仅删除r的所有元组，还删除r的模式

我们可以使用alter table命令为已有关系增加属性，关系在所有元组在新属性的取值将被设置为null

例如:alter table r add A D

其中r是现有关系的名字，A是待添加属性的名字，D是待添加属性的域。

2.3 SQL查询的基本结构

SQL查询的基本结构由三个子句构成select、from、where。

查询的输入由from来完成，在这些关系上进行where、select子句中指定的运算，然后产生一个关系作为结果

2.3.1 单关系查询

考虑一个查询:找出所有教师的名字

select name
from instructor;

在关系模型的形式化数学定义中，关系是一个集合，因此，重复的元组不会出现在关系中。

但是在实践中，去除重复是相当费时的，所以SQL允许在关系以及SQL表达式结果中出现重复。

如果我们想要强行删除重复，可以在select后加入关键字

那么就变成了

select distinct dept_name
from instructor;

相对的，SQL也允许我们使用关键词all来显式指明不去除重复

select all dept_name
from instructor;

select子句还可以带含有+、-、*、/运算符的算术表达式，运算对象可以是常数或者元组的属性

SQL允许在where子句中使用逻辑连词and、or、not。逻辑连词的原始对象可以是包含比较运算符<、<=、>、>=、=、<>的表达式。它允许我们使用比较运算符来比较字符串、算术表达式以及特殊类型。

2.3.2 多关系查询

当一个属性出现在多个关系中，需要使用关系名作为前缀来说明我们使用的是哪个关系的属性。相反，如果属性只出现在一个关系中，这时候不需要把关系名作为前缀

来梳理一下SQL查询三种类型的子句

select子句用于列出查询结果所需要的属性，也就是投影操作
from子句是一个查询求值中需要访问的关系列表
where:子句是一个作用在from子句中关系的属性上的谓词

我们一般以这样的顺序来考察一个sql语句:

from：我们首先根据需求，确定需要查询的表项
where:通过where过滤掉不需要的信息
select:将集合结果投影出来，返回结果
为from子句中列出的子句产生笛卡尔积
在笛卡尔积的基础上使用where指定谓词过滤元组
对于过滤后的元组集合，在select子句中指定属性并且返回

for each 元组t1 in 关系r1
    for each 元组t2 in 关系r2
        ...
        for each 元组tm in 关系rm
            把t1,t2m...,tm连接成单个元组t
            将t加入结果关系中

注意:当from中做的运算是多个关系的时候，这时候就是将这几个关系做笛卡尔积。这样的话，where子句中的谓词用来限制笛卡尔积所建立的组合，只留下那些对所需答案有意义的组合。

比如说我们想要输出teaches元组只和具有相同ID值的instructor元组进行匹配:

select name,course_id
from instructor,teaches
where instructor.ID = teaches.ID;

这个查询只输出了讲授了课程的教师，但是不会输出那些没有讲授任何课程的教师。

我们想要输出这样的元组的话，就可以使用外连接的运算

2.3.3 自然连接

自然连接是一种通用的操作，from子句中的匹配条件在最通常的情况下需要在所有匹配名称的属性上相等。

SQL支持几种另外的方式来使得来自两个或者多个关系的信息可以被连接(join)起来。

**自然连接(natural join)**运算可以作用于两个关系，并产生一个关系作为结果。自然连接只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。

自然连接使用样例

select name,course_id
from instructor,teaches
where instructor.ID = teaches.ID

select name,course_id
from instructor natural join teaches

在一个from子句中，可以用自然连接将多个关系结合在一起

from r1 natural join r2 natural join ... natural join rm

from instructor natural join teaches,course

要说明的是，当两个关系有多个属性名是相同的时候，这时候将要求两个关系中这些相同的属性名都相同。例如:

where t1.a = t2.a and t1.b = t2.b and t3.c = t3.c...

SQL还提供了一种自然连接的构造形式，允许用户来指定哪些列相同

select name,title
from (instructor natural join teaches) join course using(course_id);

其中join….using运算中需要给定一个属性名列表，其中两个输入中都必须具有指定名称的属性。

2.4 附加的基本运算

2.4.1 更名运算

重新考察此前使用过的查询

select name,course_id
from instructor,teaches
where instructor.ID = teaches.ID;

此查询的结果是一个具有下列属性的关系:name,course_id

结果中的属性名来自于from子句中关系的属性名

我们不能总是用这个方法来派生名字，原因:

from子句的两个关系中可能存在同名属性，在这种情况下，结果(是指select之后产生的结果集)中就会出现重复的属性名
select子句中使用算术表达式，那么结果属性就没有名字
属性名可以从基关系中导出，但我们也许想要改变结果中的属性的名字

因此,SQL提供了一个重命名结果关系中属性的方法，as

重命名关系的另一个原因是为了适用于需要比较同一个关系中元组的情况。为此我们需要把一个关系跟它自身做笛卡尔积运算，如果不重名的话，就不可能把一个元组与其他元组区分开来。

例题：找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高

select distinct T.name
from instructor as T,instructor as S
where T.salary > S.salary and S.dept_name = "Biology";

在上面的查询中，T和S可以被认为是instructor关系的两个拷贝，

2.4.2 字符串运算

SQL中可以使用一对单引号来标示字符串，如果单引号是字符串的组成部分，那就用两个单引号字符串来标示

如it's right可以表示为'it''s right'

在SQL标准中，字符串上的相等运算是大小写敏感的，但是是可以在数据库级或者特定属性级中被修改的。

SQL中还允许在字符串上有多重函数，例如串联(||),大小写转换(upper(s)、lower(s))、去掉字符串后面的空格，使用(trim(s))

在字符串上可以使用like操作符来实现模式匹配(pattern)

百分号(%):匹配任意的子串
下划线(_):匹配任意的一个字符

注意这个模式也是大小写敏感的，(这里猜想底层应该是正则表达式)

例子:

寻找以Intro打头的字符串:Intro%
匹配任何包含Comp子串的字符串:% Comp%
匹配只含三个字符的字符串:___
匹配至少含三个字符的字符串:___%

为了使得模式中能够包含特殊模式的字符(也就是%和_)，SQL中允许定义转义字符，转义字符直接放在特殊字符的前面，表示该特殊字符被当成普通字符。我们在like比较运算中使用escape关键字来定义转义字符。

匹配所有以ab%cd开头的字符串:like 'ab\%cd%' escape '\'
匹配所有与以ab\cd开头的字符串:like 'ab\\cd%'

2.4.3 select子句中的属性说明

*星号表示在select子句中表示所有的属性。

2.4.4 排列元组的显示次序

order by:子句默认使用升序，可以用desc表示降序，或者使用asc表示升序，当有多个属性时，使用案例如下:

select *
from instructor
order by salary desc,name asc

2.4.5 where子句谓词

对于数值是一个闭区间的where子句运算，我们可以使用between比较运算符

类似地，还可以使用not between进行运算

SQL允许我们用记号(v1,v2,...,vn)来表示一个分量值为v1,v2,...,vn的n维元组，在元组上可以使用比较运算符，按字典顺序进行比较。例如，(a1,a2) <= (b1,b2)

3. 集合运算

SQL作用在关系上的union、intersect和except运算对应于数学集合论中的并集、交集和差集运算

3.1 并运算

与select子句不同的是,union运算自动会去除重复。

但是如果我们想要保留所有的重复，就必须使用union all来代替union

3.2 交运算

与并运算类似的，intersect运算会自动去除重复，如果我们想要保留所有的重复，就必须使用intersect all来代替intersect

3.3 差运算

except是需要考虑运算顺序的，它从其第一个输入中输出所有不出现在第二个输入中的元组，也即它执行的是集差操作，同样的，如果我们想要保存做差后的所有重复，那么就必须使用except all来代替

4. 空值

空值给关系运算带来了特殊的问题，包括算术运算、比较运算和集合运算

如果算术表达式的任意输入为空，则该算术表达式(+-*/)结果为空
考虑运算1<null，因为null的含义是unknown所以这个比较的结果就是unknown
and:由于and结果的是受两个被操作数同时制约的，因此，结果肯定是unknown
or:or运算的结果可以只依赖于一个操作数得得到确定的结果。
- true和unknown的结果是true
- false和unknown的结果是false
- unknown和unknown的结果是unknown
not:not unknown的结果是unknown

如果where的运算结果是false或者unknown的话，那么就不会加入到结果元组中。

当一个查询使用select distinct子句的时候，重复元组将会被去除，为了达到这个目的，当比较两个元组对应的属性值的时候，如果这两个值都是非空并且值相同，或者都是空，那么它们是相同的。

比如说(A,null)和(A，null)这样的元组被认为是相同的，那么distinct关键字会保留一份下来。

5. 聚合函数

聚合函数是以值的一个集合(集或者多重集)为输入、返回单个值的函数。

平均值:avg
最小值:min
最大值:max
总和:sum
计数:count

sum和avg的输入必须是数字集，但其他运算符还可以作用到非数字数据类型的集合上，如字符串

5.1 基本聚合

考虑查询找出计算机系老师的平均工资

select avg(salary) as avg_salary
from instructor
where dept_name = "计算机系"

在某些情况下计算聚集函数需要先删除掉重复元组，可以在聚集表达式中使用关键词distinct

select count(distinct ID)
from teaches
where semester = "spring" and year = 2010;

SQL不允许在使用count(*)的时候使用distinct。但是在使用max和min的时候使用distinct是合法的。尽管结果没有区别，我们可以使用关键字all来替代distinct来说明保留重复元组。

5.2 分组聚集

group by子句中给出的一个或者多个属性是用来构造分组的

在group by子句中的所有属性上的取值相同的元组将被分在一个组中

以下sql示例了如何找到同一个系的所有教师的平均工资

select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name;

当SQL查询使用分组的时候，一个很重要的事情是需要保证出现在select语句中没有被聚集的属性只能是出现在group by子句中的那些属性。换句话说，任何没有出现在group by子句中的属性如果出现在select子句中的话，它只能出现在聚集函数内部，否则这样的查询就是错误的

/*错误查询*/
select dept_name,ID,avg(salary)
from instructor
group by dept_name;

在一个特定分组里边(通过dept_name)来进行定义中的每位教师都有一个不同的ID，既然每个分组只输出一个元组，那么就无法确定选择哪个ID值作为输出。这是原因。

5.3 having子句

有时候，对分组限定条件对元组限定条件更有用。

例如:我们只对教师平均工资超过42000美元的系感兴趣，但是该条件并不针对单个元组，而是针对group by子句构成的分组。为了表达这样的查询，可以使用SQL的having子句，having子句中的谓词，在形成分组之后才会起作用的，因此having中可以嵌套聚合函数

select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;

与select子句的情况类似，任何出现在having子句中，但是没有被聚集的属性必须出现在group by 子句中，否则这个查询就是错误的。

包含聚集、group by或者having子句的查询的含义可以通过下列操作序列来定义:

与不带聚集的查询情况类似，最先根据from子句来计算得到一个关系
如果出现了where子句，where子句中的谓词将作用到from子句的结果关系上
如果出现了group by子句，满足where谓词的元组通过group by子句形成分组,如果没有group by子句，满足where谓词的整个元组集将被当成一个分组
如果出现了having子句，它将应用到每个分组上，不满足having子句谓词的分组将被抛弃
select子句利用剩下的分组产生出查询结果中的元组，也就是在每个分组上应用聚集函数来得到单个结果元组

对于在2009年讲授的每个课程段，如果该课程段至少有2名学生选课，找出该选修课程段的所有学生的总学分的平均值

select course_id,semster,year,sec_id,avg(tol_cred)
from takes natural join student
where year = 2009
group by course_id,semester,year,sec_id
having count(ID) >= 2;

5.4 对于空值和布尔值的聚集

空值的存在给聚集运算的处理带来麻烦，由于一些元组在被聚合的属性上取了空值，上述查询中待求和的值中就包含了空值，SQL标准并不认为总和半身为null，而是认为在运算的时候应该忽略掉输入的null值。

处理原则:

除了count(*)外的所有聚集函数都忽略输入集合中的空值
由于空值被忽略，有可能造成参加函数运算的输入值为空集。规定空集的count运算值为0
其他所有聚集运算在输入为空集的情况下返回一个空值

6. 嵌套子查询

6.1 集合成员资格

SQL允许测试元组在关系中的成员资格。连接词in测试元组是否是集合中的成员，集合是由select子句产生的一组值构成的，连接词not in则测试元组是否不是集合中的成员

我们以查询找出在2009年秋季和2010年春季学期同时开课的所有课程

在之前的知识背景下，我们可能会采取集合的并集然后去重的方式来得到答案，如果利用嵌套子查询该怎么做呢?

查找在2009年秋季开课的所有课程，看它们是否也是2010年春季开课的课程集合中的成员
先找到2010年春季开课的所有课程

(select course_id
 from section
 where semester = "spring" and year = 2010
)

从子查询形成的课程集合中找出那些在2009年秋季开课的课程

select distinct course_id
from section
where semester = "Fall" and year = 2009 and
course_id in (select course_id
              from section
              where semester = "spring" and year = 2010
);

如果想要查找在2009年秋季开课但不在2010年春季学期同时开课的所有课程呢?

select distinct course_id
from section
where semester = "Fall" and year = 2009 and
course_id  not in (select course_id
              from section
              where semester = "spring" and year = 2010
);

in和not in操作符也可以用于枚举集合的，例如:

select distinct name
from instructor
where name not in("Mozart","Einstein");

6.2 集合的比较

考虑查询:找出下面条件的所有教师，他们的工资至少要比Biglogy系某一个教师的工资要高

在之前的更名运算的学习阶段中，我们对于同一张表的比较通常通过更名运算来完成，如

select distinct T.name
from instructor as T,instructor as S
where T.salary > S.salary and S.dept_name = "Biology";

SQL提供了另外一种方式书写上面的查询。短语”至少比某一个要大”在SQL用>some来表示。

这个结构允许我们用一种更贴近此查询表达的形式重写上面的查询:

select name
from instructor
where salary > some(select salary
                    from instructor
                    where dept_name="Biology"
);

我们来分析一下这个语句，首先子查询

select salary
from instructor
where dept_name="Biology"

这个子查询将会产生Biology系所有教师工资值的集合。当元组的salary值至少比Biology系教师的所有工资集合中某一成员高的时候，外层的>some的比较为真

= some等价于in，然而<>some并不等价于not in

现在修改一下我们的查询，如果我们想要找出比所有都大的工资元组，该如何查找呢?

select name
from instructor
where salary > all(select salary
                  from instructor
                  where dept_name = "Biology");

<> all等价于not in,但 = all 并不等价于in

考虑一个查询:找出平均工资最高的系

做这个的思路是首先要比较出所有的平均工资，因此先通过>= all运算出来

select dept_name
from instructor
group by dept_name
having avg(salary) >= all(select avg(salary)
                         from instructor
                         group by dept_name);

6.3 空关系的测试

SQL还具有一个特性，可以测试一个子查询的结果中是否存在元组。

exists结构在作为参数的子查询非空时返回true值。使用exists结构，可以有一种方法书写查询:

请找出在2009年秋季学期和2010年春季学期同时开课的所有课程

select course_id
from section as S
where semester = "Fall" and year = 2009 and
exists(select * 
        from section as T
     	where semester = "Spring" and year = 2010 and
      	S.course_id = T.couse_id)

最关键的是S.course_id = T.couse_id这句。

同时注意到来自外层查询的一个相关名称(上述查询的S)可以用在where子句的子查询中。使用了来自外层查询相关名称的子查询被称为相关子查询

在包含了子查询的查询中，在相关名称上可以应用作用域规则。根据此规则，在一个子查询中只能使用此子查询本身定义的，或者在包含此子查询的任何查询中定义的相关名称。

如果一个相关名称既在子查询中定义，又在包含该子查询的查询中定义，则子查询中的定义是有效的。类似于局部变量和全局变量的意思

我们可以用not exists结构来测试子查询结果集中是否不存在元组，我们可以使用not exists结构来模拟集合包含的操作。

我们可以将关系A包含关系B写成not exists(B except A)

这个如何理解咧?来分析一下

(B except A)是说，B与A去做差集，也就是说，检查B中的元素，A中是不是都有

根据差集的运算规则，如果B中的元素A中都有，那么就会返回一个空值。那么这时候A就包含住了B

如果说B中存在一些元素，A中是没有的，那么这时候就认为A并没有完全包含住B，这时候就会返回一个元组

那么如何来判断是否有值呢？这时候not exists就派上了用处了。

如果空值,那么not exists(null)就是true，就是包含住了B

如果有元组，那么not exists(not null)就是false，就没有包含住B

例题:找出选修了Biology系开设的所有课程的学生

select S.ID,S.name
from student as S
where not exists((select course_id
                 from course
                 where dept_name = "Biology")
                 except
                (select T.course_id
                from takes as T
                where S.ID = T.ID));

这里，子查询A，找出了Biology系开设的所有课程的集合

(select course_id
from course
where dept_name = "Biology")

同时子查询B找出了S.ID选修的所有课程

(select T.course_id
from takes as T
where S.ID = T.ID);

这样:外层select对每个学生测试其选修的所有课程集合是否包含Biology系开设的所有课程的集合

这里的话我们认为子查询A的结果是一个小范围，而子查询B是一个大范围，因此有上述的查询性质

6.4 重复元组的存在性测试

SQL提供一个布尔函数，用于测试在一个子查询的结果中是否存在重复元组。如果作为参数的子查询结果中没有重复的元组，unique将返回ture值。

找出所有在2009年最多开设一次的课程

select T.course_id
from course as T
where unique(select R.course_id
             from section as R
             where T.course_id = R.course_id and
             R.year = 2009);

注意:如果某门课程不在2009年开设的话，那么子查询就会返回一个空的结果，unique谓词在空集上计算出真值

在不使用unique的情况下，上述查询的一种等价方式是

select T.course_id
from course as T
where 1>= (select count(R.course_id)
           from section as R
           where T.course_id = R.course_id and
          	R.year = 2009)

我们可以用not unique结构测试在一个子查询结果中是否存在重复元组

考虑查询找出所有在2009年最少开设两次的课程

select T.course_id
from course as T
where not unique(select R.course_id
             from section as R
             where T.course_id = R.course_id and
             R.year = 2009);

形式化地，对一个关系的unique测试的结果为假的定义是

当且仅当在关系中存在着两个元组t1和t2，由于t1或者t2的某个域为空的时候，判断t1 = t2为假
所以尽管一个元组有多个副本，只有该元组有一个属性为空，unique测试就有可能为真

6.5 from子句的子查询

简单来说，from操作的是一个输入的关系，如果对于一个查询子句，能够得到一个关系，就可以将该关系进行操作。

找出系平均工资超过42000美元的那些系中的教师的平均工资

select dept_name,avg_salary
from (select dept_name,avg(salary)as avg_salary
      from instructor
      group by dept_name)
where avg_salary >42000;

可以使用as子句给此子查询的结果关系起个名字，并且对属性进行重命名

select dept_name,avg_salary
from (select dept_name,avg(salary)
     from instctor
     group by dept_name
     as dept_avg(dept_name,avg_salary))
where avg_salary > 42000;

这个操作相当于对子句的查询结果关系进行命名，并且呢，对里面的属性也进行了命名
找出在所有系中工资总额最大的系

select max(tot_salary)
from (select dept_name,sum(salary)
      from instructor
      group by dept_name) as dept_total(dept_name,tot_salary)

6.6 with子句

with子句提供定义临时关系的方法，这个定义只对包含with子句的查询有效

找出具有最大预算的系

with max_budget(value)as
	 (select max(budget)
      from department)
select budget
from department,max_budget
where department.budget = max_budget.value;

这个with子句定义了临时关系max_budget,此关系在随后的查询中被马上使用了。

简单来说就是定义了临时变量供其使用

查出所有工资大于所有系工资总额的系

with dept_total(dept_name,value)as
	 (select dept_name,sum(salary)
     from instructor
     group by dept_name),#这是一个变量
     dept_total_avg(value)as#这是另外一个变量
     (select avg(value)
      from dept_total)
 select dept_name
 from dept_total,dept_total_avg

7. 数据库的修改操作

7.1 删除操作

删除的操作只能删除整个元组，而不能只删除某些属性上的值。SQL用如下的语句进行表示

delete from
where P;

其中P代表一个谓词，r代表一个关系，如果省略了where子句，那么r中的所有元组都会被删除

注意delete命令只能够作用于一个关系，如果我们想要从多个关系中删除元组，必须在每个关系上使用一条delete指令。

delete请求可以包含嵌套的select，该select引用待删除元组的关系

注意:delete的执行过程是先检查，后删除的，比如说要删除那些小于大学教师的平均工资的教师，那么如果说我一边删除一边检查，那么最终导致的结果是多删除掉了一部分，这样是不合理的。合理的做法应该是先检查所有教师的工资，然后对其做好标记，待遍历完毕后再进行删除

7.2 插入操作

最简单的insert语句是单个元组的插入请求，例如

insert into course
	values("1","111");

元组属性值的排列和关系模式中的属性排列的顺序需要保持一致。当然了，也可以手动指定属性，如:

insert into course(title,name)
	values(1,111);
insert into course(name,title)
	values(1,111);

更通常的情况是，我们可能想要在查询结果的基础上插入元组。

我们想让Music系每个修满144学分的学生成为Muisc系的教师，其工资是18000美元，可以写作

insert into instructor
	select ID,name,dept_name,18000
	from student
	where dept_name = 'Music' and tot_cred>144;

这个相当于是把查出来的元组给插入到了指定的关系中

7.3 更新操作

所有的教师的工资将增长5%

update instructor
set salary = salary*1.05;

对工资低于平均数的教师涨5%的工资

update instructor
set salary = salary *1.05
where salary < (select avg(salary)
                from instructor)

SQL还提供了case的结构，我们可以利用它在一条update语句中执行两种更新。

给工资超过100 000美元的教师涨3%的工资，其余教师涨5%

update instructor
set salary = case
			 when salary <= 100 000 then salary * 1.05
			 else salary * 1.03
     end

其基本模式为

case
	when pred1 then result1
	when pred2 then result2
	when pred3 then result3
	when pred4 then result4
	when pred5 then result5
end

将每个student元组的tot_cred属性值设置为该学生通过的课程学分的总和

update student S
set tot_cred = (
	select sum(credits)
	from takes natural join course
	where S.ID = takes.ID and
		  takes.grade <> 'F' and
		  takes.grade is not null);

注意，如果一个学生没有成功学完任何课程，这个更新将会将其tot_cred的属性值设置为空

select case
		   when sum(credits) is not null then sum(credits)
	  	   else 0
       end

练习

a:找到那些在银行中有账户但是没有贷款的人的ID

这道题可以这样来分析:首先我们知道，如果一个用户想要贷款，那么他一定会有账户，如果一个用户有账户，但是他不一定会有贷款，这样分析的结论就是:贷款的人群是子集，是小范围，有账户的人是全集，是大范围，因此求差集即可

select ID
from customer
except
(select ID
from borrower)

b:找到与12345居住在同一个城市，同一条街道的所有客户的ID

首先通过子查询，查出12345所居住的城市和街道

select customer_street,customer_city
from customer
where customer_name='12345'

接着再去查询客户表

select ID
from customer as S
where (S.customer_name,S.customer_street)
in(...)

其中…内容应该是上述的子查询

也就是完整答案是

select S.ID
from customer as S
where (S.customer_street,S.customer_city)
in
(select T.customer_street,T.customer_city
from customer as T
where T.customer_name='12345')

c.找出所有支行的名字，这些支行至少有一个客户居住在Harrison

这道题比较复杂，我们先来考虑一下需要查询哪些表

首先我们想要知道支行的名字，那么我们可以查询的表有branch、loan、account，我们将这三个表作为候选

接着，我们想要查找账户的信息，那么账户的信息肯定跑不掉的是account

然后，我们还想要查询用户的居住地，那么就需要查询customer了

然后我们梳理一下上面这些表之间的关系

首先我们可以通过customer的地址限定条件，来得到若干个ID

这些ID都是对应的客户的ID，然后我们想要通过这个ID，去查找账户

那么也就是通过ID去查询account表中的branch_name信息，但是我们发现account并没有ID这个属性，而是有account_number这个唯一主键

那么很容易可以想到，我们再引入一张表depositor，作为中间桥梁

我们用ID去查找depositor的account_number信息，然后把这个account_number信息拿去查找account表，最终就能得到我们想要答案

来看看SQL是怎么写的:

第一个子查询:查找得到ID

select ID
from customer
where customer_city = 'Harrison'

第二个子查询:查找depositor得到account_number

select distinct account_number
from deposiotr
where ID in(...第一个子查询的结果)

第三个子查询:根据account_number来查询account表

select distinct branch_name
from account
where account_number in (...第二个子查询的结果)

写成完整的SQL就是

select distinct branch_name
from account
where account_number 
in 
(select distinct account_number
from deposiotr
where ID in
 (select ID
from customer
where customer_city = 'Harrison'))

优化SQL的可读性:

with live_city_with_ID(value) as
	  (select ID
	   from customer
	   where customer_city = 'Harrison')

with live_city_with_ID(value) as
	  (select ID
	   from customer
	   where customer_city = 'Harrison')
select distinct branch_name
from account
where account_number 
in 
(select distinct account_number
from deposiotr
where ID = live_city_with_ID.ID);

3.9

d:找出库中工资高于”Small Bank Corporation”的每个雇员的所有雇员

这个SQL可以借助于all运算符来解决

select ID
from works
where salary > all
	(select salary
     from works
     where company_name = 'Small Bank Corporation')

e:假设公司可能会在几个不同的城市开设，找到那些和”SBC”开设在同一个城市的公司

首先我们从题目要求知道，核心的过滤条件是公司开设的城市，因此要找到与公司、城市相关的表，也就是company

select city
from company
where company_name = 'Small Bank Corporation'

然后再查询company表即可

select T.company_name
from company as T
where T.city
in
(select S.city
from company as S
where S.company_name = 'Small Bank Corporation')

注意:上面的做法其实是错误的，其实在于题目的误解，题目要求的是:

找到这些公司:这些公司开设的城市，包含住了SBC所开设的所有城市

那么可以分析一下，首先包含这个条件来说，SQL系统中并没有提供对应的API，需要我们自己手写:

(B except A)是说，B与A去做差集，也就是说，检查B中的元素，A中是不是都有

根据差集的运算规则，如果B中的元素A中都有，那么就会返回一个空值。那么这时候A就包含住了B

如果说B中存在一些元素，A中是没有的，那么这时候就认为A并没有完全包含住B，这时候就会返回一个元组

那么如何来判断是否有值呢？这时候not exists就派上了用处了。

如果空值,那么not exists(null)就是true，就是包含住了B

如果有元组，那么not exists(not null)就是false，就没有包含住B

如果我们能够拿到包含判断的boolean值，那么后面查询数据就很简单了，因此核心就是写出这个包含条件

根据上面的引用分析，我们想要的是一个公司的开设城市包含住了SBC的所有开设城市，那么套上去就是B是小范围(SBC所在城市)，A是大范围(我们想要的公司开设的所有城市)

我们先算出B

select city
from company
where company_name = 'Small Bank Corporation'

然后是算出所有公司开设的所有城市，这里的话可以分为两步来做

先算出各个公司所开设的城市

select city
from company as T
where S.company_name = T.company_name

求差

(select city
from company
where company_name = 'Small Bank Corporation')
except
(select city
from company as T
where S.company_name = T.company_name)

select N.company_name
from company as N
where not exists (select city
from company
where company_name = 'Small Bank Corporation')
except
(select M.city
from company as M
where N.company_name = M.company_name)

f:找出拥有最多雇员的公式的名称(如果有多个的，那么并列列出)

这道题的要求，是以公司为单位来计算雇员数，因此是需要分组的。

然后来分析一下需要哪些表，首先是需要获取公司名称和能够代表雇员数量的雇员名称，因此需要works表

，然后需要对雇员名称进行计数，所以需要count函数进行聚合

select S.company_name
from works as S
group by S.company_name
having count(S.ID) >= all(select count(T.ID)
 						  from works as T
                          group by T.company_name)

g:找出那些平均工资高于”FBC”的平均工资的公司

这道题可以用avg求出FBC的平均工资，接着再用分组的方式来求出公司名称。

先求出平均工资

select avg(salary)
from works
where company_name = "First Bank Corporation"

然后对公司进行分组，分组求平均,再用having进行过滤

select company_name
from works
group by company_name
having avg(salary) >(select avg(salary)
					from works
					where company_name = "First Bank Corporation")

穿山甲

https://lumxi.github.io/2022/10/03/dbs/dbs-(3)/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源穿山甲 !

计算机基础

前缀和算法与差分

2022-10-08 数据结构与算法

Java leetcode

Java工厂模式

2022-10-02 设计模式

后端开发基础

数据库原理-初级SQL

1. SQL查询语言概览

2. SQL数据定义

2.1 基本类型

2.2 基本模式定义

2.3 SQL查询的基本结构

2.3.1 单关系查询

2.3.2 多关系查询

2.3.3 自然连接

2.4 附加的基本运算

2.4.1 更名运算

2.4.2 字符串运算

2.4.3 select子句中的属性说明

2.4.4 排列元组的显示次序

2.4.5 where子句谓词

3. 集合运算

3.1 并运算

3.2 交运算

3.3 差运算

4. 空值

5. 聚合函数

5.1 基本聚合

5.2 分组聚集

5.3 having子句

5.4 对于空值和布尔值的聚集

6. 嵌套子查询

6.1 集合成员资格

6.2 集合的比较

6.3 空关系的测试

6.4 重复元组的存在性测试

6.5 from子句的子查询

6.6 with子句

7. 数据库的修改操作

7.1 删除操作

7.2 插入操作

7.3 更新操作

练习

你的赏识是我前进的动力