【课堂笔记】《数据库系统概论（第5版）》-第3章：关系数据库标准语言

关系数据库标准语言（SQL）

Structural Query Language.

3.1：SQL概述

3.1.1：SQL的产生与发展

概括：nb!

3.1.2：SQL的特点

1. 综合统一：
   SQL集数据定义语言、数据操纵语言、数据控制语言的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括以下一系列操作和要求：

   • 定义、修改、删除关系模式，定义、删除视图，插入数据，建立数据库。
   • 对数据库中的数据进行查询和更新。
   • 数据库的安全性、完整性控制，以及事务控制。
   • 嵌入式SQL和动态SQL。

   此外，关系模型中实体与实体之间的联系均用关系表示，这种数据结构的单一性带来了数据操作符的统一性：查找删除插入更新等每一种操作只需要一种操作符。

2. 高度非过程化
   使用SQL进行数据操纵时，无需像使用“过程化”的语言一样指定存取路径，这部分由系统自动完成。

3. 面向集合的操作方式
   非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录。
   SQL采取操作集合的方式，插入删除更新查找的对象全部是元组的集合。

4. 以同一种语法结构提供多种使用方式。
   作为独立的语言、被其他高级语言调用。

5. 语言简洁，易学易用
   NB！！！
   核心功能只有九个动词：

   SQL功能	动词
   数据查询	SELECT
   数据定义	CREATE、DROP、ALTER
   数据操纵	INSERT、UPDATE、DELETE
   数据控制	GRANT、REVOKE

3.1.3：SQL的基本概念

支持SQL的数据库同样支持关系数据库的三级模式，其中外模式包括若干视图和部分基本表，模式包括若干基本表，内模式包括若干储存文件（详见书本图示）。

• 用户可以使用SQL对视图或者基本表进行操作，基本表与视图都是关系。

• 基本表是本身独立存在的表，在关系数据库管理系统中一个关系对应一个基本表。

• 储存文件的逻辑结构组成了关系数据库的内模式，储存文件的物理结构对用户隐藏。

• 视图是从一个或几个基本表导出的表，本身不存在于数据库中，数据库只存放数据的定义，数据始终在基本表中，因此视图是一个虚表。

3.2：学生-课程数据库

• 学生表：$Student(\underline{Sno},Sname,Ssex,Sage,Sdept)$
  学号、姓名、性别、院系

• 课程表：$Course(\underline{Cno},Cname,Cpno,Ccredit)$
  课程号、课程名、先修课、学分

• 学生选课表：$SC(\underline{Sno,Cno},Grade)$
  学号、课程号、成绩

以这三张表讲解SQL。

3.3：数据定义

SQL的数据定义功能包括模式定义、表定义、视图和索引的定义。

操作对象	创建	删除	修改
模式	create schema	drop schema	-
表	create table	drop table	alter table
视图	create view	drop view	-
索引	create index	drop index	alter index

一个关系数据库管理系统的实例中可以建立多个数据库，一个数据库中可以建立多个模式，一个模式下通常包括多个表、视图、索引等数据库对象。

3.3.1：模式的定义与删除

1. 模式定义

   create schema <模式名> authorization <用户名>;
   

   建立表和视图的语句可以在建立模式的语句中嵌套。

2. 删除模式

   drop schema <模式名> <cascade | restrict>;
   

   cascade（级联）表示删除模式的同时删除此模式下所有的数据库对象；
   restrict表示只删除没有任何下属对象的模式。

3.3.2：基本表的定义、删除、修改

1. 定义基本表：

   create table <表名> (
       <列名> <数据类型> [列级完整性约束条件],
       [<列名> <数据类型> [列级完整性约束条件]],
       ...,
       [<表级完整性约束条件>],
   );
   

   下面是取自菜鸟教程的一个例子：

   CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
   )ENGINE=InnoDB DEFAULT CHARSET=utf8;
   

   • 在 RUNOOB 数据库中创建数据表runoob_tbl
   • 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
   • AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
   • PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
   • ENGINE 设置存储引擎，CHARSET 设置编码。

2. 数据类型：

   传送至菜鸟教程

3. 模式与表
   三种方法在定义基本表的时候定义所属的模式：

   1. 在表名中显示给出：

      create table `table_name`.schema_name ();
      

   2. 创建表时同时创建模式。如书本例3.3
   3. 设置所属的模式，这样在创建时就不必给出。

   有点懵逼，贴几个博客：
   MySql中的schema和database
   schema概念的理解

4. 修改基本表：

   alter table <表名>
   [add [column] <新列名><数据类型>[完整性约束条件]]
   [add <表级完整性约束条件>]
   [drop [column] <列名> [cascade | restrict]]
   [drop constraint]
   

5. 删除基本表：

   drop table <表名> [restrict|cascade]
   

   使用cascade，与此表相关的所有对象，如视图。

3.3.3：索引的建立与删除

建立索引是加快查询速度的有效手段。

顺序文件上的索引：
按照指定属性升序或者降序的储存关系，在该属性上建立一个索引文件，索引文件由属性值和相应的元组的指针组成。

B+树索引：
建立B+树。。。。

散列索引：
hash

位图索引：
看不懂：用位向量记录索引属性中可能出现的值，每一个位向量对应一个可能的值。

1. 建立索引：

   create [unique] [cluster] index <索引名>
   on <表明> (<列名>[<次序>], ...)
   

2. 修改索引：

   alter index <旧索引名> rename to <新索引名>
   

3. 删除索引：

   drop index <索引名>
   

3.3.4：数据字典

关系数据库管理系统内部的一组系统表，记录数据库中所有的定义信息，包括：关系模式定义、视图定义、索引定义、完整性约束定义、用户权限、统计信息……
关系数据库管理系统在执行SQL语句的时候，就是在更新数据字典中的相关信息。

3.4：数据查询

查询语句的基本格式：

select [all|distinct] <目标列表达式> [,<目标列表达式>]···
from <表明或视图名> [,<表明或视图名>] | (<select 语句>) [as] <别名>
[where<条件表达式>]
[group by <列名1> [having <条件表达式>]]
[order by <列名2> [asc | desc]]

这整个语句的含义是，根据where的表达式从from中指定的表中找出满足条件的元组，再按select中的目标列表达式从元组中选出属性值形成基本表。

group by和order by对结果进行分组和排序。

3.4.1：单表查询

1. 选择若干列

   • 选择一些属性

     select Sno, Sname from Student;
     

   • 选择全部列

     select * from Student;
     

   • 查询经过计算的值

     select Sname, Sage, 2019 - Sage from Student;
     

     结果会像下面这样：

     Sname	Sage	2019 - Sage
     王尼玛	20	1999

   • 目标表达式还可以是字符串常量

     select Sname, 'Year og Birth' from Student;
     

     结果会像下面这样：

     Sname	‘Year of Birth’
     王尼玛	Year of Birth

     查询时可以通过指定别名来改变列名

     select Sname NAME, 2019 - Sage BIRTH from Student;
     

     结果会像下面这样：

     NAME	BIRTH
     草泥马	1998

2. 选择表中若干元组

   • 消除取值重复的行

     select distinct Sage from Student;
     

     不加distinct相当于select all ···

   • 查询满足条件的元组

     使用where语句实现，常用的查询条件有：

     查询条件	谓词
     比较	$=,>,<,\le ,\ge ,!=,<>,!>,!<$以及NOT加上述运算符
     确定范围	between and, not between and
     字符匹配	like, not like
     空值	is null, is not null
     多重条件（逻辑运算符）	and or not

     比较大小举例

     select Sname from Student where Sage > 18;
     

     范围查询举例

     select Sname from Student where Sage between 20 and 30;
     

     确定集合的查询

     select Sname from Student where Sdept in ('CS', 'EE');
     

     字符串匹配

     %代表任意长度的字符串；
     _代表单个字符；
     \_ \%表示转义字符；

     例如：

     select Sname from Student where Sname like '操%';
     

     选出所有姓操的人。

     涉及空值的查询

     select Sname from Student where Grade is not null;
     

     多重条件查询

     select Sname from Student where Grade > 60 and Sage > 18;
     

   • order by 子句

     select Sname from Student order by Sage asc;
     

     按照年龄升序排序。

   • 聚集函数

     函数	用途
     count(*)	统计元组个数
     count([all | distinct] <列名>)	计算某一列中值的个数
     sum([all | distinct] <列名>)	计算某一列的总和（必须为数值类型）
     avg	平均值（用法同上）
     max	-
     min	-

     举例：查询学生总人数

     select count(*) from Student;
     

     只有count(*)不会跳过null。
     聚集函数只能用于select子句和group by中的having子句。

   • group by子句

     将查询结果按照一列或者多列的值进行分组，值相等的为一组。
     分组的目的是细化聚集函数的作用对象。

     举例：（表的定义见最本章前面）

     select Cno, count(Sno) from SC group by Cno;
     

     从选课记录表中选择Cno相同的为一组，然后计算每一组中的Sno的个数。

     再举个例子：选出选课人数超过3人的课程号

     select Cno from SC group by Sno having count(*) >= 3;
     

     where作用于基本表或者视图，having作用于组（选出满足条件的组）。

3.4.2：连接查询

若一个查询同时涉及两个以上的表，则称之为连接查询。

1. 等值连接查询与非等值连接查询
   连接查询的where子句中，用来连接两个表的条件称为连接条件或者连接谓词，一般格式为：

   [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

   当连接运算符为 = 时，称之为等值连接，否则，称之为非等值连接。

   例：将两个表中学号相等的元素连接起来

   select Student.*, SC.* from Student, SC 
   where Student.Sno = SC.Sno;
   

   表名前缀可以省略，当属性名唯一时。
   在等值连接的目标列中将重复的属性去掉称为自然连接。

2. 自身连接

   一个表自己与自己连接，称为自身连接。

   还是以3.2中定义的三张表中的$Course$表为例，查询每一门课的先修课：

   select a.Cno, b,Cpno from Course a, Course b 
   where a.Cpno = b.Cno; /*为Course取了两个别名
   

3. 外连接

   外连接的概念在上一章中给出，这里举个例子：
   将$Student$表和$SC$表进行左外连接，即将没有选课的学生的保留在结果中，相应的选课信息设置为$null$。

   select Student.Sname, SC.Cno 
   from Student left outer join SC on (Student.Sno = SC.Sno);
   

   结果大概会像下面这样：

   Sname	Cno
   草泥马	1
   草泥马	2
   王尼玛	null

4. 多表连接
   涉及两个以上的表打查询：

   select Sname, Cname from Student, SC, Course
   where Student.Sno = SC.Sno ans SC.Cno = Course.Cno;
   

3.4.3 嵌套查询

在SQL中，一个select-from-where称为一个查询块，将一个查询块嵌套在另一个查询块中称为嵌套查询。

上层的查询块称为外层查询或者父查询，下层的查询块称为内层查询或者子查询。

子查询的select中不能使用order by子句，order by只能对最终结果排序。

1. 带有in谓词的子查询

   例：查询与草泥马在同一个系的学生信息：

   select Sname, Sno from Student where Sdept in 
   (select Sdept from Student where Sname = '草泥马');
   

   子查询的条件不依赖于父查询，称为不相关子查询，否则，称为相关子查询，整个查询语句称为相关嵌套查询。

2. 带有比较运算符的子查询

   这是一个相关子查询的例子：

   select Sno, Cno from SC x where Grade >=
   (select avg(Grade) from SC y where x.Sno = y.Sno);
   

   首先计算某一学生的平均成绩，然后选择出分数大于平均成绩的课程。

3. 带有any(some)、all谓词的子查询

   any和all搭配比较运算符使用。

   select Sname, Sage from Student where
   Sage < any(select Sage from Student where Sdept = 'CS')
   and Sdept <> 'CS'; /*这里的and还是父查询里的
   

   在非计算机系中，查询比某一个计算机系学生年龄小的学生的姓名年龄。

   一些等价转换关系：

   any与all与运算符的组合	=	<>	<	<=	>	>=
   any	in	–	< max	<= max	> min	>= min
   all	–	not in	< min	<= min	> max	>= max

4. 带有exists谓词的子查询

   exists代表$\exists$，带有exists的查询子句不返回任何数据，只返回true或者false。

   例：查询所有选修了1号课程的学生姓名。

   select Sname from Student where exists 
   (select * from SC where Sno = Student.Sno and Cno = '1');
   

   这个例子中的子查询依赖父查询的某个属性（Student的Sno），因此也是一个相关子查询。

   与exists相对应的是not exists。

   SQL中没有全称量词（for all），因此，需要用以下等价形式转换：

   $$(\forall x)P\equiv \neg (\exists x(\neg P))$$

   例如，要查询选了所有课的学生，就查询没有一门课是这个学生不选的。

   select Sname from Student where not exists
   (
       select * from Course where not exists 
       (
           select * from SC where Sno = Student.Sno 
           and Cno = Course.Cno
       )
   );
   

   有一点点小懵
   Page109 - Page111, mark

3.4.4 集合查询

select语句的查询结果是元组，因此不同的select语句的查询结果之间可以进行集合运算。

集合操作主要包括union、intersect、except（并、交、差）。

参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同。

例：查询计算机系年龄大于19的学生

select * from Student where Sdept = 'CS' union
select * fron Student where Sage >= 19;

例：查询同时选修了课程1和课程2的学生的学号

select Sno from SC where Cno = '1' intersect
select Sno from SC where Cno = '2';

3.4.5基于派生表的查询

子查询不仅仅可以出现在where子句中，还可以出现在from子句中，这时子查询生成临时的派生表成为主查询的对象。

例：查询所有选修了1号课程的学生姓名学号

select Sname, Sno from Student, 
(select Sno from SC where Cno = '1') as SC_tmp
where Student.Sno = SC_tmp.Sno;

通过派生表进行查询时，as关键字不是必须的，但必须为派生表指定一个别名。

3.4.6select语句的一般格式

1. 目标列表达式的可选形式

$$\left\{\begin{array}{c}\ast \\ <表名>.\ast \\ count([distinct|all]\ast )\\ [<表名>.]<属性列名表达式>[,<表名>.<属性列名表达式>]\cdot \cdot \cdot \end{array}\right\}([distinct|all]<列名>)$$

2. 聚集函数的一般格式

$$\left\{\begin{array}{c}count\\ sum\\ avg\\ max\\ min\end{array}\right\}([distinct|all]<列名>)$$

3. where子句的可选格式

$$<属性列名>\theta \left\{\begin{array}{c}<属性列名>\\ <常量>\\ [any|all](select语句)\end{array}\right\}$$

---

$$<属性列名>[not]between\left\{\begin{array}{c}<属性列名>\\ <常量>\\ (select语句)\end{array}\right\}and\left\{\begin{array}{c}<属性列名>\\ <常量>\\ (select语句)\end{array}\right\}$$

---

$$<属性列名>[not]in\left\{\begin{array}{c}(select语句)\\ (<值1>[,<值2>]\cdot \cdot \cdot )\end{array}\right\}$$

---

$$<属性列名>[not]like<匹配串>$$

---

$$<属性列名>is[not]null$$

---

$$[not]exists(select语句)$$

---

$$很多很多个andor连起来$$

3.5数据更新

插入，修改，删除。

3.5.1插入数据

1. 插入元组

   例如在学生信息中插入一条新学生的信息：

   insert
   into Student(Sno, Sname, Ssex, Sdept, Sage)
   values ('233', 草泥马, ⚧,'CS', 666);
   

   into中属性的顺序可以与建表时的顺序不一样。

2. 插入子查询结果

   create table Dept_age
   (
       Sdept CHAR(15)
       Avg_age INT
   );
   
   insert into Dept_age(Sdept, Avg_age)
   select Sdept, avg(Sage)
   from Student
   group by Sdept;
   

   将学生按系求平均年龄然后将系名和平均年龄存入新表中。

3.5.2修改数据

一般形式

update <表名>
set <列名>=<表达式>[,<列名>=<表达式>]···
[where <条件>];

1. 修改某一个元组的值

   update Student
   set Ssex = null
   where Sname = '草泥马';
   

2. 修改多个元组的值

   update Student
   set Sage = Sage + 1;
   

3. 带有子查询的修改语句

   update SC
   set Grade = 59
   where Sno in(
       select Sno 
       from Student
       where Sdept = 'CS'
   );
   

   这作者和CS有仇

3.5.3删除数据

一般形式

delete 
from <表名>
[where <条件>];

没有where子句的话，删除表中所有的数据。

1. 删除某一个元组

   delete
   from Student 
   where Sname = 'BBO';
   

2. 删除多个元组的值

   delete
   from Student
   where Sdept = 'CS';
   

3. 带子查询的删除语句

   delete 
   from SC
   where Sno in (
       select Sno
       from Student
       where Sdept = 'CS'
   );
   

对基本表进行增删改操作potentially会破坏参照完整性，见5.2节

3.6空值的处理

所谓的空值，就是不存在or不知道or无意义的值，一般有以下几种情况取空值：

• 某属性值应当存在但目前未知；
• 某属性不应该有值；
• 由于特⚦殊⚦原⚦因⚦不便填写的值

1. 空值的产生

   insert into SC(Sno, Cno, Grade)
   values ('233', '1', null);
   

2. 空值的判断

   is null
   is not null
   

3. 空值的约束条件

   在属性定义时，制定了not null或者unique的属性，不能取空值。

4. 空值的算术运算、比较运算、逻辑运算

   空值与空值的算术运算结果为空值；

   空值与空值的比较运算结果为unknown；

   true、false、unknown的三值逻辑运算；

   x y	x and y	a or y	not x
   T T	T	T	F
   T U	U	T	F
   T F	F	T	F
   U T	U	T	U
   U U	U	U	U
   U F	F	U	U
   F T	F	T	T
   F U	F	U	T
   F F	F	F	T

3.7视图

视图是从一个或几个基本表（或视图）导出的表；与基本表不同，是虚表，数据库中只存放视图的定义，数据仍在原来的基本表中，视图的数据随表中的数据的变化而变化。

3.7.1定义视图

1. 建立视图

   create view <视图名>[(<列名>[,<列名>]···)]
   as <子查询>
   [with check option];
   

   with check option表示对视图进行update insert delete操作时，保证更新、插入、删除的行，满足视图定义中的谓词条件。

   组成视图的属性名要么全部指定，要么全部不指定；如果不指定，由select子句中的诸字段组成。

   以下情况必须指定所有的列名：

   • 某个目标列是聚集函数或者列表达式
   • 多表连接时，出现了重名的属性
   • 需要一个更合适的名字(￣_￣|||)

   create view CS_Student
   as
   select Sno, Sname, Sage
   from Student where Sdept = 'CS'
   with check option;
   

   视图在定义时，只将视图定义存入数据库字典中，只有在查询视图时，才执行select语句。

   加上了with check option后，对该视图进行插入修改删除时，会自动检查Sdept = 'CS'的条件。

   如果一个视图是从单个基本表导出，并且保留了主码，则称这一类视图为行列子视图。

   /*选修了1号课程的学生的视图*/
   create view SC_1(Sno, Sname, Grade)
   as
   select Student.Sno, Sname, Grade
   from Student, SC
   where Sdept = 'CS', and Student.Sno = SC.Sno and SC.Cno = '1';
   

   /*利用上面建立的视图建立视图*/
   create view SC_2
   as
   select Sno, Sname, Grade
   from SC_1
   where Grade >= 90;
   

   在定义视图的时候，也可以根据需要定义一些派生属性列，由于这些派生属性列在基本表中并不实际存在，也称他们为虚拟列；带有虚拟列的视图也称为带有表达式的视图。

   create view BT_S(Sno, Sname, Sbirth)
   as
   select Sno, Sname, 2019 - Sage
   from Student;
   

   还可以使用带有聚集函数和group by子句的查询来定义视图，这种视图被称为分组视图。

   create view S_G(Sno, Gavg)
   as
   select Sno, avg(Grade)
   from SC
   group by Sno;
   /*按学号统计平均分组成视图*/
   

2. 删除视图

   基本格式：

   drop view <视图名>[cascade];
   

   如果由被删除的视图还导出了其他的视图，则cascade会将导出的视图一并删除。

   基本表在删除时，不会级联删除由其导出的视图，这些视图需要手动删除。

3.7.2查询视图

视图在定义后，就可以像查询基本表一样查询视图。

在用户查询视图的时候，首先进行有效性检查，及检查查询涉及的视图、基本表等是否存在；如果存在，则从数据字典中取出视图的定义，将定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行此修正过的查询，这一转换过程称为视图消解。

有时候，视图消解无法正常进行，这时候，需要直接对基本表进行查询（详见书本Page125样例）。

3.7.3更新视图

视图的更新指的是通过视图来插入、删除、修改数据。

由于视图并不实际储存数据，所以对视图的更新最终都要转换为对基本表的更新（即通过视图消解）。

为防止在修改视图时，对不属于视图范围的基本表数据进行操作，可以在定义视图时加上with check option。

例：

update CS_Student 
set Sname = 'BBO'
where Sno = '66666666';

会转换为：

update Student
set Sname = 'BBO'
where Sno = '66666666';

例：

insert
into CS_Student
values ('66666666', 'BBO')

会转换为：

insert 
into Student(Sno, Sname, Sage, Sdept)
values ('66666666', 'BBO', 20, 'CS')

例：

delete 
from CS_Student
where Sname = 'BBO';

会转化为：

delete 
from Student
where Sname = 'BBO' and Sdept = 'CS';

有些视图的更新不能唯一的、有意义的转换为对基本表的更新（比如前面求了平均成绩的那个视图）。

针对于具体的数据库产品（比如My⚦Ass⚦Cure⚦ALL），还可能会有更具体的限制。

3.7.4视图的作用

下面我们来吹一吹使用视图的好处，大家听好了：

1. 视图能够简化用户的操作

2. 视图使用户能够从多种角度看待同一数据···

3. 视图对重构数据库提供了一定程度的逻辑独立性

4. 视图能够对机密数据提供安全保护

5. 适当利用视图可以更清晰的表达查询

3.8小结

ASS CURE ALL可以分为数据定义，数据查询，数据控制四大部分，本章详解了前三部分。