数据库原理(一) 数据库系统与SQL

ch1.数据库系统概述

1.1 数据模型

1.1.1 分类

• 网状模型（图）
• 层次模型（树形结构）
• 关系模型（二维表）

1.1.2 组成要素

• 数据结构：研究对象类型的集合。对象：与数据类型、内容、性质有关的对象（物质）、与数据之间联系（物质之间的联系）有关的对象。
• 数据操作：类型有检索、更新（插入、删除、修改）等。
• 数据的约束条件（完整约束性）：一组完整性规则的集合，需满足实体完整性和参照完整性。

1.1.3 概念模型

用于信息世界的建模，是现实世界到机器世界的一个中间层次，是数据库设计的有力工具，是数据库设计人员和用户之间进行交流的语言。

信息世界中的基本概念有：

实体(Entity)：客观存在并可相互区别的事物称为实体。[矩形框]

属性(Attribute)：实体所具有的某一特性称为属性，一个实体可以由若干个属性来刻画。如学生可由学号、姓名，性别、系别、入学时间等属性来描述。[椭圆形]

码(Key)：能唯一标识实体的属性集称为码。（一个或多个属性，如学生可以通过学号唯一区分）

域(Domain)：属性的取值范围称为该属性的域。（如成绩0-100分）

实体型(Entity Type):用实体名及其属性集来抽象和刻画，同类实体称为实体型。如：学生（学号，姓名，性别，出生年….)

实体集(Entity Set):同型实体的集合称为实体集。

联系(Relationship):现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内部的联系和实体之间的联系。[菱形，填入联系的名字]

如下图所示，有一对一、一对多、多对多的三种关系。实体名（如仓库）用矩形框表示，属性（如联系为库存，其属性为仓库存取零件的数量）用椭圆形表示，联系用菱形框表示，ER图（实体-联系图(Entity-Relationship Diagram)）用无向边连接。

一个实体转化成一个关系数据库中的一张表，一张表就是一个关系模式，新建表来表示联系。相联系的两个实体的码及自己的属性均作为联系的表的属性。

在这里，指的是仓库号、零件号、库存量。

create table kucun(
	lingjianID int,
    cangkuID int,
    kucun int
)

插入数据：insert into kucun values (1,1,8)

其他步骤如下：

创建表：

create table cangku(
	cangkuID int,
	Cname char(80),
	yy char(90)
)

插入数据：insert into cangku values (1,'dddd','kkk');

修改数据：

update kucun set kucun=kucun-8 where cangkuID=3 and lingjianID=1

还可以通过设置主码primary key使实体不相同。

1.2 关系模型

下面介绍关系模型的相关术语。

• 关系模式：对关系的描述，一般形式为关系名(属性1,属性2,…,属性n)。如学生(学号,姓名,年龄,性别,系别,年级)。相当于概念模型中的实体型关系(Relation)：一个关系对应一张二维表。

• ==元组==(Tuple)：表中的一行即为一个元组。

• 属性(Attribute)：表中的一列即为一个属性，每一属性都有一个属性名。

• ==主码==(Key)：表中的某个属性组，它可以唯一确定一个元组。

• 域(Domain)：属性的取值范围。

• 分量：元组中的一个属性值。

​ 关系模型中存在数据操纵，包括查询、插入、删除、更新，这些操作是集合操作，操作对象和操作结果都是关系，即若干元组的集合；同时关系模型中存取路径对用户隐蔽，用户只要指出“干什么”，不必详细说明“怎么干”，大大提高了数据的独立性和用户生产率。

完整性约束有实体完整性、参照完整性和用户定义的完整性三大类。前两个是关系模型必须满足的完整性约束条件，被称作是关系的两个不变性。

关系模型的特点：

（1）优点：建立在严格的数学概念的基础上；概念单一，实体和联系都用关系表示，数据操作结果为关系；存取路径对用户透明。

（2）缺点：存取路径对用户透明导致查询效率往往不如非关系数据模型。为提高性能，必须对用户的查询请求进行优化，增加了开发数据库管理系统的难度。

1.3 数据库系统结构

1.3.1 概念

型(Type)：对某一类数据的结构和属性的说明。（如结构体，创建完不动）

值(Value)：是型的一个具体赋值。（经常变化）

模式(schema)：是数据库系统中全体数据的逻辑结构和特征的描述，是型。

【例】学生记录

记录型：(学号,姓名,性别,系别,年龄,籍贯)

该记录型的一个记录值：(210201,李明,男,计算机,22,江苏)

1.3.2 数据库系统的三级模式结构

模式(Schema)：也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图，综合了所有用户的需求，一个数据库只有一个模式。模式是数据库系统模式结构的中间层，与数据的物理存储细节和硬件环境无关，与具体的应用程序、开发工具及高级程序设计语言无关。

外模式(External Schema)：也称子模式或用户模式，是数据库用户使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。外模式通常是模式的子集，一个数据库可以有多个外模式。同一外模式可以为某一用户的多个应用系统所使用，但一个应用程序只能使用一个外模式。外模式是保证数据库安全性的一个有力措施，每个用户只能看见和访问所对应的外模式中的数据。

（1）外模式／模式映象：定义了外模式与模式之间的对应关系，每一个外模式都对应一个外模式／模式映象，该映象定义通常包含在各自外模式的描述中。

（2）外模式／模式映象保证了数据的逻辑独立性：当模式改变时，数据库管理员修改有关外模式／模式映象，使外模式保持不变；应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性，简称数据的逻辑独立性。

内模式(Internal Schema)：也称存储模式，是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式，一个数据库只有一个内模式。

模式／内模式映象保证了数据的物理独立性：当数据库的存储结构改变了(例如选用了另一种存储结构)，数据库管理员修改模式／内模式映象，使模式保持不变，应用程序不受影响，保证了数据与程序的物理独立性，简称数据的物理独立性。

ch2.关系数据库

关系数据语言能够嵌入高级语言中使用，是一种高度非过程化的语言，存取路径的选择由DBMS的优化机制来完成。

2.1 关系数据结构

2.1.1 概念

域(Domain)：是一组具有相同数据类型的值的集合。如整数、实数、介于某个取值范围的整数、指定长度字符串集合等。

（大写字母表示型，小写字母表示值）

笛卡尔积(Cartesian Product)：给定一组域$D_{1},D_{2},\ldots,D_{n}$，这些域可以相同，$D_{1},D_{2},\ldots,D_{n}$的笛卡尔积为：$D_{1} \times D_{2} \times \ldots \times D_{n} = \left{ \left( d_{1},d_{2},\ldots,d_{n} \right) \middle| d_{i} \in D_{i},i = 1,2,\ldots,n \right}$

元组(Tuple)：笛卡尔积中每一个元素$\left( d_{1},d_{2},\ldots,d_{n} \right)$。

分量(Component)：笛卡尔积元素$\left( d_{1},d_{2},\ldots,d_{n} \right)$中的每个值$d_{i}$。

基数(Cardinal number)：一个域允许的不同取值个数称为这个域的基数。若$D_{i}$(i＝1,2,…,n)为有限集，其基数为$m_{i}(i＝1,2,…,n)$，则$D_{1}×D_{2}×…×D_{n}$的基数M为：$M = {\prod\limits_{i = 1}^{n}m_{i}}$.

关系：$D_{1} \times D_{2} \times \ldots \times D_{n}$的子集称为域$D_{1} , D_{2} , \ldots , D_{n}$上的关系，表示为$R(D_{1} , D_{2} , \ldots , D_{n})$，其中R为关系名，n是关系的目或度(Degree)。当n=1时为单元关系，n=2时为二元关系。关系中的每个元素是关系中的元组，通常用t表示。关系描述有意义的元组的集合。

属性(Attribute)：关系是笛卡尔积的有限子集，所以关系也是一个二维表。关系中不同列可以对应相同的域，为了区分，必须对每列起一个名字，称为属性，n目关系必有n个属性。

候选码(Candidate key)：若关系中某一属性组的值能唯一地标识一个元组，则称该属性组为候选码。若候选码包含了关系模式的所有属性，则称该候选码为全码(All-key)。

主码(Primary key)：若一个关系有多个候选码，则选定其中的一个就称为主码，主码的诸属性称为主属性，不包含在任何候选码中的属性称为非主属性。在任一候选码中的属性都是主属性。

2.1.2 关系的基本性质

列是同质的：每一列中的分量是同一类型的数据，来自同一域；

不同列可来自同一个域：不同列(属性)要给予不同的属性名；

列的顺序无所谓：列的次序可以任意交换；

任意两个元组不能完全相同：这是由笛卡尔积的性质决定的；

行的顺序无所谓：行的次序可以任意交换；

分量必须取原子值：每一个分量都必须是不可分的数据项。

2.1.3 关系模式(Relation Schema)

对关系的描述。关系模式是型（稳定不变），而关系是值（变化）。

关系模式的形式化表示：$R(U,D,dom,F)$

其中：R为关系名，U为组成该关系的属性名集合，D为属性组U中属性所来自的域，dom为属性向域的映象集合，F为属性间的数据依赖关系集合。

2.2 关系的完整性

关系模型中包括实体完整性、参照完整性（必须支持，被称作是关系的两个不变性）和用户定义的完整性三类完整性约束。

2.2.1 实体完整性

==主属性不能为空==（在任何候选码中的属性都是主属性）。

2.2.2 参照完整性

2.2.2.1 外码(Foreign Key)

设F是关系R的一个或一组属性（属性集），但不是关系R的码。如果F与关系S的主码Ks相对应，则称F是关系R的外码，关系R称为参照关系(Referencing Relation)，关系S称为被参照关系(Referenced Relation)或目标关系(Target Relation)。

注：

• 关系R和S不一定是不同的关系；
• 目标关系S的主码Ks和参照关系的外码F必须定义在同一个(或一组)域上；
• 外码并不一定要与相应的主码同名。

[例1]学生实体、专业实体以及专业与学生间的一对多联系。

学生(学号,姓名,性别,专业号,年龄)

专业(专业号,专业名)

[例2]学生、课程、学生与课程之间的多对多联系。

学生(学号,姓名,性别,专业号,年龄)

课程(课程号,课程名,学分)

选修(学号,课程号,成绩)

【解释】学号不是选修的码，学号与学生的主码有对应的关系，故学号是选修的外码。课程号同理。

[例3]学生实体及其内部的领导联系(一对多) 。

学生(学号,姓名,性别,专业号,年龄,班长)

2.2.2.2 参照完整性规则

若属性(或属性组)F是基本关系R的外码，它与基本关系S的主码Ks相对应，则对于R中每个元组在F上的值必须：或者==取空值==(F的每个属性值均为空值)，或者等于S中==某个元组的主码值==。

比如专业号要么取空（还未安排，但因为它是主属性，实际上不能为空），要么是专业这个关系中专业号的某一个。

2.2.3 用户定义的完整性

如设定成绩不能大于100，即可通过grade int check(grade<=100)来进行限制。

2.3 关系代数

常用的关系代数运算符有集合运算符、专门的关系运算符、算术比较符、逻辑运算符。

2.3.1 集合运算

（1）并(Union)：设R和S具有相同的目n(两个关系都有n个属性)，且相应的属性取自同一个域，则R∪S 仍为n目关系，由属于R或属于S的元组组成。$R∪S = { t|t ∈ R∨t ∈S }$

（2）差(Difference)：设R和S具有相同目n，且相应的属性取自同一个域，则R - S仍为n目关系，由属于R而不属于S的所有元组组成。$R - S = { t|t∈R∧t ∉S }$

（3）交(Intersection)：设R和S具有相同目n，且相应属性取自同一个域，则R∩S仍为n目关系，由既属于R又属于S的元组组成。$R∩S = { t|t∈R∧t ∈S }\quad or \quad R∩S = R –(R-S)$

（4）广义笛卡尔积(Extended Cartesian Product) ：设R为n目关系、k1个元组，S为m目关系、k2个元组，则R×S为n+m列的元组的集合，元组的前n列是关系R的一个元组，后m列是关系S的一个元组，共有k1×k2个元组$R×S =\left{ \hat{t_{r}t_{s}} \middle| t_{r} \in R \land t_{s} \in S \right}$.

广义笛卡尔积前半部分来自R，后半部分来自S。

【例】

2.3.2 专门的关系运算符

（1）$R,t \in R,t\left\lbrack A_{i} \right\rbrack$ ：设关系模式$R\left( A_{1},A_{2},\ldots,A_{n} \right)$的一个关系为R。t∈R表示t是R的一个元组，$\left. {t\left\lbrack A \right.}{i} \right\rbrack$则表示元组t中相应于属性$A{i}$的一个分量。

（2）$A,t\left\lbrack A \right\rbrack,\overset{¯}{A}$ ：若$A = \left{ A_{i1},A_{i2},\ldots,A_{ik} \right}$，$A_{i1},A_{i2},\ldots,A_{ik}$是$A_{1},A_{2},\ldots,A_{n}$中的一部分，则A称为属性列或域列。$t\left\lbrack A \right\rbrack = \left( t\left\lbrack A_{i1} \right\rbrack,t\left\lbrack A_{i2} \right\rbrack,\ldots,t\left\lbrack A_{ik} \right\rbrack \right)$表示元组t在属性列A上诸分量的集合，$\overset{¯}{A}$则表示$\left{A_{1},A_{2},\ldots,A_{n}\right}$中去掉$\left{ A_{i1},A_{i2},\ldots,A_{ik} \right}$后剩余的属性组。

（3）元组的连接（$\hat{t_{r}t_{s}}$）：R为n目关系，S为m目关系。$t_{r} \in R$，$t_{s} \in S$，$\hat{t_{r}t_{s}}$称为元组的连接。它是一个n + m列的元组，前n个分量为R中的一个n元组，后m个分量为S中的一个m元组。

（4）象集(Images Set) $Z_{x}$：给定一个关系$R(X,Z)$，X和Z为属性组。当$t[X]=x$时，x在R中的象集为：$Z_{x} = \left{ t\left\lbrack Z \right\rbrack \middle| t \in R,t\left\lbrack X \right\rbrack = x \right}$ ，它表示R中属性组X上值为x的诸元组在Z上分量的集合。即：在X上选择满足我设定的值，但取的是Z上各个元组的值。

（5）选择(Selection)：是从行的角度进行的运算，在关系R中选择满足给定条件的诸元组，记为$\sigma_{F}\left( R \right) = \left{ t \middle| t \in R\bigwedge F\left( t \right) =^{‘}真^{‘} \right}$，其中F是选择运算符，是一个逻辑表达式。

（6）投影(Projection)：从R中选择出若干属性列组成新的关系，记为$\pi_{A}\left( R \right) = \left{ t\left\lbrack A \right\rbrack \middle| t \in R \right}$，其中A是R中的属性列。投影操作主要是从列的角度进行运算，但投影之后不仅取消了原关系中的某些列，而且还可能取消某些元组(避免重复行)。如：查询学生的姓名和所在系别，可用语句：$\pi_{Sname,Sdept}\left( {Student} \right)$来完成。

（7）关于连接：

【1】连接(Join)：又称θ连接，是从两个关系的笛卡尔积中选取属性间满足一定条件的元组，记为 ，其中A和B分别为R和S上度数相等且可比的属性组，θ为比较运算符。

连接运算从R和S的广义笛卡尔积R×S中选取R关系在A属性组上的值与S关系在B属性组上值满足比较关系的元组。

【2】等值连接(Equijoin)：θ为=的连接运算称为等值连接 ，它是从关系R与S的广义笛卡尔积中选取A、B属性值相等的元组：。等值连接有m+n目关系。

【3】自然连接(Natural join)：是一种特殊的等值连接，要求两个关系中进行比较的分量必须是相同的属性组，在结果中把重复的属性列去掉。设R和S具有相同的属性组B，则：。

有关Sql语句：

广义笛卡尔积：select * from student,sc

等值连接：select * from student,sc where student.sno = sc.sno

自然连接：select sname,sc.no,ssex from student,sc where student.sno = sc.sno

（8）除(Division)：给定关系R(X,Y) 和S(Y,Z)，其中X,Y,Z为属性组。R中的Y与S中的Y可以有不同的属性名，但必须出自相同的域集。R与S的除运算得到一个新的关系P(X)，P是R中满足下列条件的元组在X属性列上的投影：(关系R)元组在X上分量值x的象集$Y_{x}$包含S在Y上投影的集合。用数学表示为：$R \div S = \left{ t_{r}\left\lbrack X \right\rbrack \middle| t_{r} \in R \land \pi_{Y}\left( S \right) \subseteq Y_{x} \right}$，其中$Y_{x}$表示x在R中的象集，$x=t_{r}\left\lbrack X \right\rbrack$.

在实际操作中的步骤如下：

首先找出两个关系的相同属性列，记为Y，然后看所有X上每个分量值对应的象集是否包含S在相同属性列Y上投影。若有，则属于除后的结果。

如在sc表（包含sno和cno），course（包含cno和其他课程信息如课程名），执行$sc \div course$，相同的属性列为cno，那么则是将所有sno对应的象集（即选课编号cno）与course在cno上投影集合进行判断归属关系，那么该运算表示的含义则为所有课都选的同学的学号。又如$sc \div student$，相同的属性列为sno，则结果为被所有同学都选的课。又如下面的例子：

ch3.关系数据库标准语言SQL

3.1 Sql语言的动词

Sql功能	动词
数据定义	create，drop，alter（更改表的结构）
数据查询	select
数据操纵	insert，update，delete
数据控制	grant, revoke

3.2 创建、删除基本表

3.2.1 创建基本表

• **use xx(数据库)**：打开数据库。该语句以后的操作在该数据库进行。

• 定义（创建）基本表：

CREATE TABLE <表名>

(<列名> <数据类型>[ <列级完整性约束条件> ]

[,<列名> <数据类型>[ <列级完整性约束条件>] ] …

[,<表级完整性约束条件> ] )；

​ 其中：表名为所要定义的基本表的名字，列名为组成该表的各个属性(列)，列级完整性约束条件为涉及相应属性列的完整性约束条件，表级完整性约束条件为涉及一个或多个属性列的完整性约束条件。

3.2.2 约束

常用的完整性约束有：

主码约束PRIMARY KEY、唯一性约束UNIQUE、非空值约束NOT NULL、参照完整性约束FOREIGN、KEY、REFERENCES。

加入约束：CONSTRAINT salary_cap CHECK (salary < 100000) –加入约束名字

用PRIMARY KEY约束定义为列约束；若主键有两列或更多列，必须使用其作为表约束。

create table table_name(
	pk_column data_type PRIMARY KEY,
    ...
);

create table table_name(
	pk_column_1 data_type,
    pk_column_2 data_type,
    ...
    PRIMARY KEY(pk_column_1,pk_column_2)
);

查看约束：（sysobjects是系统自建的表）

select * from sysobjects where OBJECT_NAME(oarent_obj) = '表名'

增加约束：

ALTER TABLE dept_new ADD CONSTRAINT ck_dept_new_ID CHECK (ID LIKE 'NO%');

删除约束：（修改表结构使用alter语句）

alter table '表名' drop constraint '约束名'

3.2.3 修改与删除基本表

• 修改基本表（结构）：

ALTER TABLE <表名>

[ ADD <新列名> <数据类型> [ 完整性约束 ] ]

[ DROP <完整性约束名> ] [ DROP column <列名> ]

[ MODIFY <列名> <数据类型> ]；

​ 其中：表名为要修改的基本表，ADD子句为增加新列和新的完整性约束条件，DROP子句为删除指定的完整性约束条件，MODIFY子句

为用于修改列名和数据类型。

alter table student add comdate datetime --增加一个属性
alter table student drop column comdate --删除一个属性
alter table student add 姓名 char(40)
update student set 姓名=sname

sp_help 可以快速查看表结构、视图信息。

• 删除基本表：DROP TABLE <表名>;

3.2.4 索引

• 建立索引：

create [UNIQUE] index <索引名> on <表名>(<列名>[<次序>][,<列名>[<次序>]]...);

用<表名>指定要建索引的基本表。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔；

用<次序>指定索引值的排列次序，升序ASC，降序DESC（缺省为升序ASC）；

UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。

**[例]**为学生-课程数据库中Student,Course,SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); 

索引的优缺点如下：

优点：可以提高查询速度（系统内部有B+树or二分法查找）；

缺点：每次执行insert,update,delete都会将index表重新排序，会降低操作效率。

• 删除索引：drop index <索引名>;

3.3 基本表的查询操作

3.3.1 查询概述

SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] … –目标列表达式可以是列名（属性）、函数、算术表达式、字符串

FROM <表名或视图名>[， <表名或视图名> ] …

[ WHERE <条件表达式> ]

[ GROUP BY <列名1> [ HAVING <条件表达式> ] ] –分组

[ ORDER BY <列名2> [ ASC|DESC ] ]; –含第一排序位、第二排序位……

其中：SELECT子句指定要显示的属性列；FROM子句指定查询对象(基本表或视图)；WHERE子句指定查询条件；GROUP BY子句对查询结果按指定列的值分组，该属性列值相等的元组为一个组，通常会在每组中作用集函数；HAVING短语筛选出只有满足指定条件的组；ORDER BY子句对查询结果表按指定列值的升序或降序排序；DISTINCT表示只显示完全不相同的行（相同的只出现一次）。如：select distinct sno from sc代表显示所有（不同的）学号。

3.3.2 函数与使用集合函数

（一）调用函数的语句：select xx()

例：select getdate(),day(getdate())

（二）集合函数：

集合函数对一组值执行计算并返回单一的值，==集合函数作用于每一组==。

注意：where后面不能跟集合函数，比如显示score表中的最高分的学生学号和课程号，需使用语句：

select sno,cno from sc where grade = (select max(grade) from sc)。

集合函数有以下五种：avg()平均值，sum()求和，count()数量，max()最大值，min()最小值

• 计数：COUNT([DISTINCT|ALL] *) / COUNT([DISTINCT|ALL] <列名>)（NULL不算入）

  如查询学生总人数，即为select count(*) from SStudent;

• 计算总和：SUM([DISTINCT|ALL] <列名>)

• 计算平均值：AVG([DISTINCT|ALL] <列名>)

• 求最大值：MAX([DISTINCT|ALL] <列名>)

• 求最小值：MIN([DISTINCT|ALL] <列名>)

3.3.3 对查询结果分组与排序

对查询结果分组：

• 使用group by
• ==值相等的为一组==，若有两个（多个），则为排列组合值相等的为一组：select cno,class from sc group by cno, class
• ==select后面只能出现分组的属性或集合函数==

select cno,count(*) from sc group by cno having count(*)>=3  --显示有三个人以上选的课,count(*)表示分组后该组有多少元组
select sno,avg(grade) from sc group by sno having min(grade)>70 and avg(grade)>85	--查找每个人所有课的平均分，在这些人中选出每门课大于70且平均分大于85的人
select cno,avg(grade) from sc group by cno	--每门课的平均分

having和where的区别：

having对每组（分组后）限定，where对每个元组限定（筛选），where筛选完了才会分组。

对查询结果排序：

使用order by字句，可以按一个或多个（第一排序位优先）属性列排序， 升序ASC，降序DESC，缺省值为升序。当排序列含空值时，ASC排序列为空值的元组最后显示，DESC排序列为空值的元组最先显示。例如order by sage ASC,sname DESC。

3.3.4 使用别名

SQL语句可以为 表名称 或 列名称 指定别名。

• 别名的三种形式：
  • 字段名 as 别名
  • 字段名 （空格） 别名
  • 别名 = 字段名
• 若使用别名，则语句中每处（如select后）应改为别名后的名称

[例]显示score中选修多门课程的同学中分数为非最高分成绩的记录。

select a.sno,a.degree,a.cno from sc a,sc b where a.sno=b.sno and a.degree<b.degree（“<”号保证了该同学选修了多门课程）

3.3.5 字符串匹配

• 通配符**%**：代表任意长度(可以为0)的字符串。 如a%b表示以a开头、以b结尾的任意长度的字符串

• 通配符**_** ：代表任意单个字符。如a_b表示以a开头、以b结尾的长度为3的任意字符串

• 通配符**[]：代表满足其中任何一个的被筛选，[]中加^**表示不为[]中任何一个的被筛选

• 利用escape关键字，使用换码字符将通配符转义为普通字符

注：转义默认的符号是'\'，可以使用escape关键字定义别的字符为转义符。

[例1] 查询DB_Design课程的课程号和学分。

​ SELECT Cno,Ccredit FROM SCourse WHERE Cname LIKE 'DB\_Design' ESCAPE '\';

[例2] 询以DB_开头、且倒数第3个字符为i的课程的详细情况。

​ SELECT * FROM SCourse WHERE Cname LIKE 'DB\_%i__' ESCAPE '\';

select * from student where sage>20 and sname like '刘%' --'%'代表一个或多个字符，查找姓刘的人,'刘_'代表刘+一个字符,不姓刘即NOT LIKE
select * from student where sname like '[李王张刘]%' 	  --查找姓氏为这四个姓的人
select * from student where sname like '[^李王张刘]%' 	  --查找姓氏不为这四个姓的人

3.3.6 连接查询

同时涉及多个表的查询。根据连接条件进行拼接。

3.3.6.1 内连接

[例1]查询每个学生及其选修课程的情况。

SELECT SStudent.*,SC.* FROM SStudent,SC WHERE SStudent.Sno = SC.Sno;

[例2]查询每一门课的间接先修课(即先修课的先修课)。

select first.cno,second.cpno from course first,scource second where first.cpno = second.cno

内连接还可以使用join…on…方法，如：

select sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno = course.cno
--可写为
select sname,cname,grade from student join sc on student.sno=sc.sno join course on sc.cno = course.cno

3.3.6.2 外连接

​ 外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN) 和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

（Oracle）**(+)，即给其加了一个万能的空行**，拼不上则使用，拼不上的情况下Student表中的记录只拼一次（在这里即为未选课）

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno = SC.Sno(+);

(Sql Server)SELECT SStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM SStudent LEFT OUTER JOIN SC on SStudent.Sno = SC.Sno;

涉及空值的查询：在where子句的<比较条件>中使用谓词IS NULL 或 IS NOT NULL

多重条件查询：使用逻辑运算符and和or联结多个查询条件。

3.3.7 嵌套查询

外层查询/父查询+内层查询/子查询

select * from student where sno in (select sno from sc where cno in 
(select cno from course where cname='数据库原理'))

3.3.7.1 带有in谓词的子查询

[例] 查询与刘晨在同一个系学习的学生。

（1）确定刘晨所在系名；

（2）查找所有在IS系学习的学生。

SELECT Sno,Sname,Sdept FROM SStudent WHERE Sdept IN (SELECT Sdept FROM SStudent WHERE Sname='刘晨');

3.3.7.2 带有any或all谓词的子查询

any表示任意，all表示所有

	=	<>或!=	<	<=	>	>=
any	in	–	<max	<=max	>min	>=min
all	–	not in	<min	<=min	>max	>=max

[例]查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄。

法1：用All谓词

select Sname,Sage from SStudent where sage < any(select Sage from SStudent where Sdept = 'IS') and Sdept <> 'IS'

法2：用集合函数

select Sname,Sage from student where Sage < (select min(Sage) from student where Sdept ='IS') and Sdept<>'IS'

3.3.7.3 带有exists谓词的子查询

每一条、每一条地去拼接。

相当于存在量词$\exists$。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑true或false，若内层查询结果非空则返回真值，否则返回假值。由EXISTS引出的子查询的目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

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

首先在Student中依次取每个元组的Sno值，用此值去检查SC关系，若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= '1'，则取此Student.Sname送入结果关系。

--用嵌套查询 
SELECT Sname FROM Student WHERE EXISTS 	/*相关子查询*/
   (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= ‘1’)；
--用连接查询
SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= ‘1’；

[例2]查询选修了全部课程的学生姓名。

法1：首先找出Student中的一条信息，到中间层语句，找出一个课程信息，若是该学生有某一门课程没有选，则内层向中间层返回false，not exists为true，直接向外层返回true，最终not exists为false，不符合条件；若符合条件，则查找下一个课程，若其中所有课程查完，中间层语句都为false，则向外层返回true，not exists结果为true，符合条件，输出。

select sname from student where not exists
	(select * from course where not exists
		(select * from sc where sno = student.sno and cno = course.cno)

法2：

select sname from student where sno in (
	select sno from sc group by sno having count(*) = (select count(*) from course))

3.3.8 集合查询（并查询）

标准SQL对于集合查询直接支持并操作。参加并（union）操作的各结果表的列数必须相同，对应项的数据类型也必须相同。

一般格式为：
<查询块>
union
<查询块>

[例]列出所有老师和同学的 姓名、性别和生日。

select tname,sex,birthday from teacher union select sname,sex,birthday from student;

3.3.9 查询细节知识点

3.3.9.1 使用top限制结果集

• 格式：TOP n [percent] with ties ，n 为非负的整数
• TOP n:表示取查询结果的前n行
• TOP n percent:表示取查询结果的前n%行
• with ties：查询并列前n行
• 若有distinct，要写在其后面

--查询数据库课程考试成绩前三名的学生的姓名和成绩
select top 3 with ties sname,grade from
  student join sc on student.sno=sc.sno
    join course on sc.cno=course.cno where cname='数据库原理'
	 order by grade desc;
--或
select top 3 with ties sname,grade from
 student,sc,course where student.sno=sc.sno and
   sc.cno=course.cno and cname='数据库原理' order by grade desc;

3.3.9.2 将查询结果保存到新表

• select 查询表序列 into 新表名 from 数据源(表名) where [<条件>]…..
• 新表有二类：
  • 永久的表，起一个表名就可以了。
  • 临时表：
    • 局部临时表，表名前加#，当前连接可用，生存期为当前连接的生存期。
    • 全局临时表：表名前加##，所有连接都可用．生存期为当前连接的生存期。

3.3.9.3 case子句

使用case子句对查询结果进行分析，见下例。

[例]查询001号课程的学号和成绩，并进行如下处理成绩>90 显示’优’,80-89:’良’,etc。

Select sno,grade,
  CASE
    WHEN GRADE>=90 THEN '优'
    WHEN GRADE BETWEEN 80 AND 89 THEN '良'
    WHEN GRADE BETWEEN 70 AND 79 THEN '中'
    WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
    WHEN GRADE<60 THEN '不及格'
  End as degree 
From sc;

3.4 数据更新

3.4.1 插入数据

• INSERT INTO <表名> [(<属性列1>[,<属性列2 >…)] VALUES (<常量1> [,<常量2>] … )
• 功能：将新元组插入指定表中

insert into student(sno,sname,ssex,sage,sdept,birthday)
	values ('120','李白','男',20,'计算机系','97/09/01');
--oracle写法: to_date('09/01/1977','mm/dd/yyyy');
--追加查询结果到指定表中
insert into xx11(sno,avggrade1)
 select sno,avg(grade) from sc group by sno;

3.4.2 修改和删除数据

修改数据：

• UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>] … [WHERE <条件>];

• 功能：修改指定表中满足WHERE子句条件的元组

  [例]将信息系所有学生的年龄增加1岁。 UPDATE Student SET Sage = Sage+1 WHERE Sdept='IS';

删除数据：

• DELETE FROM <表名> [WHERE <条件>];
• 功能：删除指定表中满足WHERE子句条件的元组
• 需要满足实体完整性和参照完整性
• delete后表仍存在，drop后表会被删除

delete from student where sname='李白';
select * from sc;
delete from sc where sno='120';

3.5 视图

视图机制可以为不同的用户定义不同的视图，把数据对象限制在一定的范围内。也即，通过视图机制把要加密的数据对无权存取的用户隐藏起来，从而自动对数据提供一定程度的安全保护。视图机制间接地实现支持存取谓词的用户权限定义。

• 视图是一个“假的表”，数据库中只存储查询语句，查询视图即是查询视图中的查询语句。
• 视图消解是对视图的操作转换成对基本表的操作。
• CREATE VIEW <视图名> [(<列名> [,<列名>]…)] AS <子查询> [WITH CHECK OPTION];

create view avg_ss(sname,avggrade) as
  select sname,avg(grade) from student,sc
    where student.sno = sc.sno group by sname;

3.6 数据控制

3.6.1 授权

SQL语言用GRANT语句向用户授予操作权限。

GRANT <权限>[,<权限>] … [ON <对象类型> <对象名>] TO <用户>[,<用户>] …[WITH GRANT OPTION];

若加入WITH GRANT OPTION，获得的权限仍可以授予给别人。

[例1]把查询Student表权限授给用户U1。

GRANT SELECT ON TABLE Student TO U1;

[例2]把对Student表和Course表的全部权限授予用户U2和U3。

GRANT ALL PRIVILIGES ON TABLE Student, Course TO U2,U3;

[例3]把对表SC的查询权限授予所有用户。

GRANT SELECT ON TABLE SC TO PUBLIC;

[例4]把查询Student表和修改学生学号的权限授给用户U4。

GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4;

[例5]把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户。

GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION;

3.6.2 收回权限

由DBA或其他授权者用REVOKE语句收回。

一般格式：

REVOKE <权限>[,<权限>]…[ON <对象类型> <对象名>] FROM <用户>[,<用户>] …；

[例1]把用户U4修改学生学号的权限收回。

REVOKE UPDATE(Sno) ON TABLE Student FROM U4;

[例2]收回所有用户对表SC的查询权限。

REVOKE SELECT ON TABLE SC FROM PUBLIC;

[例3]把用户U5对SC表的INSERT权限收回。

REVOKE INSERT ON TABLE SC FROM U5;