第三章 SQL入门

Chapter 3 SQL介绍

参考书目：《数据库系统概念》第7版，机械工程出版社
Reference: Database System Concepts, 7th Edition

  • 广泛的数据库查询语言。
  • 定义数据结构，修改数据库中的数据，安全性约束。

目录

3.1 查询语言概览

  • 数据定义语言(DDL)。SQL DDL提供定义，删除和修改关系模式的命令。
  • 数据操纵语言(DML)。SQL DML提供查询信息，和插入，删除，修改元组的能力。
  • 完整性(integrity)。DDL包括了定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束。
  • 视图定义。
  • 事务控制。
  • 嵌入式和动态式。
  • 授权。

3.2 SQL数据定义

  • 定义关系的集合和信息。

img

3.2.1 基本类型

img

  • 每一种类型都具有一个null(空)的特殊值，代表一个缺失的值。可能不存在或不知道。
  • 字符串char长度小于定义长度时，一般会追加空格。但是varchar则不会，因为这是可变的。在比较char时，会自动利用空格补齐长度后比较。

3.2.2 基本模式定义

  • 采用create table来定义SQL关系。例如下面创建了department关系：
create table department
  (dept_name  varchar(20),
   building   varchar(20),
   budget     numeric(12,2),
   primary key(dept_name));
  • create table来创建一个表。我们有：
create table r
  (
    A1  D1,
    A2  D2,
    ...,
    An  Dn,
    <完整性约束1>,
    ...,
    <完整性约束n>
  );

  • 分号可有可无。鉴于C/C++习惯，我将持续沿用;。

  • SQL支持许多完整性约束。

    • primary key \((A_{j1},A_{j2},\cdots,A_{jm})\) : 主码声明表示属性 \((A_{j1},A_{j2},\cdots,A_{jm})\) 作为构成关系的主码。主码属性必须非空且唯一。
    • foreign key \((A_{k1},A_{k2},\cdots,A_{km})\) reference \(s\): 外码声明表示关系中任意的元组在属性 \((A_{k1},A_{k2},\cdots,A_{km})\) 上取值必须对应于关系 \(s\) 在某主码属性上的取值。
    • not null: 要求非空属性。

  • SQL禁止任何破坏完整性约束的更新。进行这样的操作将会返回一个错误并且阻止。

  • 更新：insert,update，删除delete。去除关系drop table。

  • delete from用于删除关系内的所有元组但是保留关系，而drop table将关系也进行删除处理。

  • alter table为已有关系增加属性。新属性的取值被赋为null。

Mysql 实验

Setup

我们采用mysql进行上述实验。鉴于其他原因，我们将不会进行非常精细化的权限管理。

首先我们安装数据库：sudo apt install mysql-server -y

执行mysql --version检查我们的版本。

img

登陆sql: sudo mysql -u root -p,没有设置密码前直接回车就可以登陆。

img

查看我们已经存在的数据库（不是关系！）
show databases;注意分号。

img

创建我们的实验性数据库create database experiment;

img

可以发现多了一个数据库。我们通过use experiment来使用它。

创建数据表和删除数据表

根据上文我们的department表格，我们可以进行如下操作：

img

img

3.3 SQL查询的基本结构

  • select, from, where.
  • from: 输入的关系。
  • where, select：关系在其子句中进行运算，产生关系作为结果。

3.3.1 单关系查询

  • 我们采用select的方式来寻找到某一个属性。我们采用这样的命令，来从关系help_topic中列出属性name：
select name
from help_topic;
  • 进行去重处理则需要使用distinct:
select distinct name
from help_topic;
  • 阻止（显式）声明不去重则使用all。
select all name
from help_topic;
  • select字句允许使用+，-，*，/运算符的算数表达式，运算对象可以是常数/元组的属性。
select ID,salary*1.1
from instructor

这样显示出来的关系就是instructor中的ID属性和salary属性中的元组。不同的是所有的salary的值将是原来的1.1倍。

  • where字句允许筛选出选定属性中满足特定条件的词组。
  • 例如：从instructor中找出CS系工作的，工资超过70000的教师姓名：
select name
from instructor
where dept_name='CS' and salary > 70000;
  • 允许在where子句中使用逻辑连词and, or, not。

3.3.2 多关系查询

  • 寻找所有教师的姓名，他们所在系的名称以及建筑物名称。
select name,instructor.dept_name, building
from instructor,department
where instructor.dept_name=department.dept_name
  • from选取了多个关系，而在where子句中选择想用的属性来讲他们关联在一起。

小结

  • SQL查询语句包括三种类型的子句:select,from,where.
  • select子句用于列出查询结果中需要的属性。
  • from子句用于查询求值中需要访问的关系列表。
  • where子句是作用在from子句中的关系属性上的谓词。
select A1,A2,...,A_n
from r1,r2,...,rn
where P;
  • 子句写出的顺序是select,from,where，但是进行的运算应该是from，where，select。

img

  • 形成笛卡尔积，相当于（不严格地）将各个表中没有相互关联的属性拼在一起，形成新的表，并从该表中筛选出符合条件的元组。

多重集关系代数Pt.I
对SQL的行为进行建模，我们定义了一个叫做 多重集关系代数(multiset relational algebra) 来进行处理。

  • 对于关系 \(r_1\)，其中的元组 \(t_1\) 有 \(c_1\) 份的拷贝并且 \(t_1\) 满足条件 \(\sigma_\theta\), 则在 \(\sigma_\theta(r_1)\) 中有 \(c_1\) 份其拷贝。
  • 对于关系 \(r_1\)，其中的元组 \(t_1\) 有 \(c_1\) 份的拷贝， 对于其中每一份拷贝， \(\prod_A(t_1)\) 都有 \(t_1\) 的拷贝。\(\prod_A(t_1)\) 称为 \(t_1\) 的投影。
  • \(r_1\times r_2\) 有元组 \((t1,t2)\) 的 \(c_1c_2\) 份拷贝。

太复杂了我们举例说明一下。注意 \(\sigma\) 即按照条件选择， \(\prod\) 即根据模式筛选出元素中的那一个对应成份。\(\times\) 即经典笛卡尔积。我们有：
\(r_1=\{(1,a),(2,a)\}\)，模式为 \((A,B)\)
\(r_2=\{2,3,3\}\)，模式为 \(C\).

  • \(\sigma_{A > 1}(r_1)=\{(2,a)\}\)
  • \(\prod_B(r_1) = \{a,a\}\)
  • \(\prod_B(r_1) \times r_2 = \{(a,2),(a,2),(a,3),(a,3),(a,3),(a,3)\}\)

因此，对于我们的SQL查询，可以写成：
\(\prod_{A_1,\ldots,A_n}(\sigma_p(r_1\times r_2\times \cdots r_m))\)

3.4 附加的基本运算

3.4.1 更名运算

  • as为更名运算。

  • old-name as new-name

  • 我们可以在select中使用as进行属性名的更换，例如：

select a as b
from relation1, relation2
where relation1.ID = relation2.ID
  • 上述展示了从关系1，关系2中选出ID相同的元组，并列出这些元组中a属性的值，并将a属性更名成了b。
  • 注意只是结果改变了名字，原关系中没有发生修改。
  • 我们可以通过这样的方式缩小关系名的长度，这样展示出来的结果相对方便查看。
  • 重命名（也就是as后的部分）称为相关名称/变量。

3.4.2 字符串运算

  • 字符串通过单标号'表示。
  • sql标准中大小写敏感，但是mySQL等一些数据库系统不一定区分大小写。
  • 字符串上作用多种函数 upper,lower(分别为大写化和小写化)，trim表示去除掉字符串后面的空格。
  • like用于实现模式匹配。模式匹配大小写敏感。
    • %:匹配任意子字符串。
    • _:匹配任意一个字符。
  • escape定义转义字符。比如我们有：
    • like 'ab\%cd%' escape '\'匹配以"ab%cd"开头的所有字符串。

3.4.3 通配符

  • * 可以用于表示select中的所有属性。

3.4.4 排列显示次序

  • order by可以使得元组按照某个顺序排列显示。缺省/asc表示升序排序，desc表示降序。

3.4.5 where子句谓词

  • between A and B：表示在where中，筛选出某个范围内的元组。
  • 允许使用 \((v_1,\ldots,v_n)\) 来包含一个n元元组，称为行构造器。元组上可以使用比较运算符，按照字典序进行运算。

3.5 集合运算

  • union,intersect,except分别对应了 \(\cup\), \(\cap\) 和 \(-\) 运算。
  • 可以用于合并从两个select...from...where中筛选出来的相同属性的元组。

3.5.1 union

  • 例如：找出2017年秋季开课，或2018年春季开课，或两个学期都开课的所有课程的集合：
select course_id
from section
where semester='fall' and year=2017
union
select course_id
from section
where semester='spring' and year=2018;
  • union语句会自动去重。如果我们不想去重而保留所有重复项，就可以使用union all。

3.5.2 intersect

  • 和上述union的举例相同，只需要将union替换成intersect，就变成了“既在2017年秋季开设，又在2018年春季开设的课程”。
  • intersect all表示不去重。
  • MySQL没有实现该运算。

3.5.3 except

  • 和上述的例子类似，将union替换成except，我们有“在2017年秋季开课，但是不在2018年春季开课的课程”。
  • 保留重复项使用except all。
  • MySQL没有实现该运算。

3.6 空值

  • 空值(null value) 给基础运算带来了困难。
  • 我们在SQL中出现了第三个布尔运算结果，称为unknown。任何涉及到比较运算的结果视为unknown，因为我们不知道空值到底代表了什么。
  • 对于where语句来说，当期比较出来的结果为false或unknownn的时候，该元组不能加入结果中。
  • SQL采用特殊的关键字null来测试空值，采用is known或is not unknown子句来测试一个比较运算结果是否为unknown，而不是true或false。
select name
from instructor
where salary > 10000 is unknown;

3.7 聚集函数

  • 聚集函数：多个值作为输入，单个值作为输出。
    • 平均：avg
    • 最小/最大值：min/max
    • 总和：sum
    • 计数：count

3.7.1 基本聚集

  • 考虑：找出CS系教师平均工资，我们有：
select avg(salary)
from instructor
where dept_name='CS';
  • 返回值是具有单属性的关系，其中只有一个元组。这个元组对应的就是CS的教师的平均工资。我们可以用as给属性取个有意义的名字。
select avg(salary) as avg_salary
from instructor
where dept_name='CS';
  • 如果使用聚集函数前需要去重，可以使用distinct。强制不去重使用all。比如我们需要通过ID计算不同的教师数量，我们就需要去重，我们有count函数进行计数。这样我们有：
select count(distinct ID)
from teachers
where semester='spring' and year=2018

这样即使有教师任课数量大于1，也只会被计数一次。

  • 采用通配符*来计算一个关系中元组的数量。
select count(*)
from instructors

3.7.2 分组聚集

  • group by可以使得聚集函数作用在一组元组集上。这样，在分组子句中所有属性取值相同的元组将分在同一个组内。
  • 考虑下面的查询实例。
select dept_name, avg(salary) as avg_salary
from instructors
group by dept_name
  • 选择了关系（表）instructors内的属性（列）dept_name, salary。
  • salary将通过dept_name进行分组并计算每一分组下的平均值。
  • 结果将属性名改成avg_salary进行显示。
  • 在没有group by子句的情况下，我们将整个关系当作是一个组。
  • 出现在select语句中但是没有被聚集的属性只能是那些出现在group by子句中的那些属性。否则将会错误。（你让他怎么展示呢？）
  • 采用/* */或者--的方式来撰写注释。

3.7.3 having子句

  • 对分组限定条件。我们在形成分组后，才可以使用having子句中的聚集函数。请看下面的例子。
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary)>42000;

  • 找出平均工资超过42000的教师和他们所处的系。

    • 从instructor中选出属性dept_name, salary属 性。
    • 根据dept_name进行分组后计算salary的均值。
    • 通过having筛选出salary均值大于42000的。

  • 同样的，包含在having子句中，但是没有被聚集的属性必须出现在group by子句中。否则查询错误。

  • 对于有having，group by和聚集的查询含义一般定义如下：

    • from确定一个关系。
    • where作用到上面的结果，计算出新的关系。
    • 出现了group by，将符合的元组通过其进行分组。否则整个关系为一个组。
    • having作用到每个分组上，不满足的分组将会被去除。
    • select在剩下的分组中产生查询结果，对每个元组应用聚集函数。

3.7.4 对空值和布尔值的聚集

  • 除了count(*) 之外的聚集函数将全部忽略控制。因此有可能对于聚集函数，输入集合为空集。
  • 空集的count运算结果为0。
  • 布尔值的结果有三个，上文已经提及。聚集函数some与any用于布尔值集合，计算or（析取）和and（合取）。

多重集关系代数Pt.II

  • 同样的，关系代数也有并，交和差。
  • 我们可以用关系代数聚集运算 \(\gamma\) 来允许在关系属性上使用聚集函数。(或者使用 \(\mathcal{G}\))。
  • 对于复杂的sql可以进行改写。
select A1,A2,sum(A3)
from r1,r2,...,rm
where P
group by A1, A2, having count(A4)>2

可以写成:
\(t_1\leftarrow \sigma_p(r_1\times r_2\cdots r_m)\)
\(\prod_{A_1,A_2,\text{sum}(A_3)}(\sigma_{count(A_4)>2}(_{A_1,A_2}\gamma_{sum(A_3)~ as ~sum(A_3),~count(A_4)}(t_1)))\)

3.8 嵌套子查询

  • 子查询嵌套在另一个查询中的select-from-where表达式中。

3.8.1 集合成员资格

  • 查询“2017年秋季和2018年春季都开设的所有课程”。
    • 确定子查询：2018年春季开设的课程。
(select course_id
from section
where semester = 'spring' and year = 2018
);
  • 确定从子查询中得到的课程，再次进行查询。
select distinct course_id
from selection
where semester = 'autumn' 
and year=2017 
and course_id in 
(select course_id
from selection
where semester = 'spring'
and year=2018
);
  • 采用in可以实现类似intersection的功能。采用notin可以实现difference的功能。
  • 他们也可以用于枚举集合。

3.8.2 集合比较

  • 查询“找出工资至少比生物系某位教师的工资要高的所有教师的姓名。”
  • 采用 >some 子句来查询。
select distinct T.name
from instructor as T
where salary > some(
  select salary
  from instructor
  where dept_name = 'Biology'
);
  • <some,<=some,>=some,=some,<>some(不等于).
  • =some等价于in, 但是 <>some不等价于not in。
    • 不等于一些元素不正确，应该是不等于任何。
  • <all,<=all,>=all,=all,<>all(不等于).
  • =all不等价于in， <>all等价于notin。
    • 和一些元素相等就代表存在了，没必要与所有的都相等（也不太可能）

3.8.3 空关系测试

  • exists子句，可以测试子查询的结果中是否存在元组。在子查询非空时返回True。
  • 外层查询的相关名称可以用在where子句的查询中。这样的子查询称为相关子查询。
  • 符合作用域规则。
  • not exists代表测试子查询是否不存在元组，子查询空时返回True。采用not exists(B except A) 可以用来说明 关系A包含关系B。

3.8.4 重复元组存在性测试

  • unique子句返回子查询结果中没有重复的元组，此时返回true的值。
  • 查询“找出在2017年最多开设一次的所有课程”。
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 = 2017)
  • not unique子句返回子查询结果中有重复的元组，此时返回true

3.8.5 from子句中的子查询

任何select-from-where表达式返回的结果都是关系。因此可以被插入到其他关系可以出现的位置。

  • 例如：“找出平均工资大于42000的教师平均工资”：
select dept_bame,avg_salary
from(
  select dept_name, 
    avg(salary) as avg_salary
  from instructor
  group by dept_name
) as dept_avg(dept_name, avg_salary)
where avg_salary > 42000
  • 这里子查询中的属性和关系进行了重命名。但是注意，MySQL和PostgreSQL都要求from子句中的每个子查询的结果关系必须被命名。
  • SQL2003标准后允许子句查询中访问外部的属性。采用lateral关键字。

3.8.6 with子句

  • 定义了临时的关系，只对with子句的查询有效。

3.8.7 标量子查询

  • 子查询只返回一个包含单个属性的元组。称为标量子查询。例如：
select a, (
  select count(*)
  from b
  where c.a = b.a
)as d
from R
  • 标量可以直接运算，不需要from子句。
select(select count(*) from teachers) / (select count(*) from instructor) from dual

3.9 数据库的修改

删除

delete from Relation
where P;
  • 删除关系中满足条件P的所有元组。
  • 也可以嵌套子查询于where子句中。

插入

insert into R (A1, A2, A3)
  values ('AAA', 12,4),
  ('111',12,3);
  • 将对个元组插入元素。
  • 我们也可以从查询结果中插入元组。例如下述的语句：
insert into student
  select ID, name
  from R
  where P;
  • 需要注意我们必须在插入之前完成执行select语句。否则在没有主码约束的情况下，会插入无数元组。

更新

  • update子句可以用于更新元素。
update relation
set A = A1
where P;
  • 也可以通过case来进行更新。
update instructor
set salary = case
  when P1 then res1
  when P2 then res2
  else res3
end

img

posted @ 2025-03-01 14:57  木木ちゃん  阅读(71)  评论(0)    收藏  举报