数据库原理（三）

SQL

Sql查询语言概览

Sql最早的版本是由IBM开发，它最初的被叫做Sequel，在20世纪70年代早期作为System R项目的一部分。Sequel语言一直发展至今，其名称已经变为SQL(结构化查询语言)。Sql已经很明显的确立了自己作为标准的关系数据库语言的地位。

Sql语言包括一下几个部分：

l 数据定义语言（Data-Definition Language,DDL）:SQL DDL提供定义关系模式、删除关系以及修改关系模式的命令。

l 数据操纵语言（Data-Manipulation Language,DML）SQL DML提供从数据库查询信息，以及在数据库中插入元组、删除元组、修改元组的能力。

l 完整性（integrity）:SQL DDL包括定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性月火速。破坏完整性约束的更新是不允许的。

l 视图定义（view definition） ：SQL DDL包括定义视图的命令。

l 事务控制（transaction control）：SQL 包括定义事务的开始和结束的命令

l 嵌入式SQL和动态SQL（embedded SQL and dynamic SQL）:嵌入式和动态SQL定义SQL语言如何嵌入到通用编程语言中。

l 授权（authorization）：SQL DDL 包括定义对关系和视图的访问权限的命令。

Sql数据定义

DDL不仅能够定义关系，还可以定义每个 每个关系的信息：

l 每个关系的模式

l 每个属性的取值类型

l 完整性约束

l 每个关系维护的索引集合

l 每个关系的安全性和权限信息

l 每个关系在磁盘上的物理存储结构

基本类型：

Char（n）：固定长度的字符串，用户指定长度n，全称character

Varchar（n）：可变长度字符串，用户指定最大长度n，等价于全称character varying。

Int：整型，全称integer

Smallint：小整数类型。

Numeric（p,d）：定点数，精度由用户指定，这个数有p位数字，其中有d位在小数点右边

Real，double precision：浮点数与双精度浮点数，精度与机器相关。

Float（n）：精度至少为n位的浮点数。

基本模式定义：

用create table 定义sql关系：

Create table department

(

dept_name varchar(20),

building varchar(15),

budget numeric(12,2),

primary key(dept_name)

);

Primary key（A），声明属性A构成关系的主码。主码属性必须非空且唯一，也就是说没有一个元组在主码属性上取空值，关系中也没有两个元组在所有主码属性上取值相同。虽然主码的声明是可选的，但为每个关系指定一个主码通常会更好。

Foreign key(A1,A2,A3…..) references:

Foreign key 声明表示关系中任意元组在属性（A1,A2…）上的取值必须对应与关系s中某元组在主码属性上的值。

Not null：一个属性上的not null 约束表明在该属性上不允许空值。

一个新创建的关系最初是空的，可以用insert 命令将数据加载到关系中。

Insert into instructor  values(10211,’Smith’,’Biology’,6600);

值的顺序应该遵循对应属性在关系模式中列出的顺序。

用delete命令删除元组：

Delete from student；

将删除student的所有元组。

如果要从sql数据库中去掉一个关系，我们使用drop table 命令

Drop talbe r；

不仅删除r的所有元组，还删除了关系模式；

我们用alter table 命令为已有关系增加属性。关系中的所有元组在新属性上的取值将被设为null。

Alter table r add A D；

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

我们也可以通过命令去除某些属性：

Alter table r drop A;

其中r是现有关系的名字，A是关系中一个属性的名字。很多数据库系统比你更不支持去掉属性，金瓜它们允许去掉整个表。

Sql查询的基本结构

单关系查询

Select name from instructor ;

其结果是由属性名为name的单个属性构成的关系。

“找出所有教师所在的系名”:

Select dept_name from instructor;

在关系模型的形式化数学定义中，关系是一个集合。因此，重复的元组不会出现在关系中。在实践中，去除重复是相当费时的，所以sql允许在关系以及sql表达式结果中出现重复。因此在上述的sql查询中，每个系名在instructor关系的元组没出现一次，都会在查询结果中列出一次。

去除重复的方法。可以在select 后加入关键字distinct。

Select distinct dept_name from instructor;

上述查询的结果中，每个系名最多只出现一次；

Sql允许我们使用关键字all来显式指明不去除重复。

Select all dept_name from instructor;

保留重复元组是默认的，所以可以省去all。

Select 子句还可以带有+，-，*，/运算符的表达式。运算对象通常可以是常数或元组的属性。

例如：

查询：

Select ID,name,dept_name,salary*1.1

From instructor;

Where 子句允许我们只选出那些在from子句的结果关系中满足特定谓词的元组。

“找出所有在computer science系并且工资超过70000美元的教师的姓名”，该查询用sql可以写为：

Select name from instructor where dept_name=’Comp.Sci’ and salary >70000;

Sql允许在where子句中使用逻辑连接词and，or和not。

逻辑连接词的运算对象可以是包含比较运算符<,>/<=,>=等表达式。

多关系查询

通常查询需要从多个关系中获取信息。

“找出所有教师的行in个，以及他们所在系的名称和所在建筑的名称”。

老驴instructor关系的模式，我们可以从dept_name 属性中得到系名，但是系所在建筑的名称是在department关系的building属性中给出的。为了回答查询，instructor关系中的每个元组必须与department关系中的元组匹配，后者在dept_name上的取值相配与instructor元组在dept_name上的取值。

Select name,instructor,dept_name,building from instructor,department where instructor.dept_name=department.dept_name;

Select,from和where 子句的作用如下：

l Select子句用于列出查询结果中所需的属性。

l From 子句是一个查询求值中需要访问的关系列表

l Where 子句是一个作用在from子句中关系的属性上的谓词。

自然连接

Natural join 运算作用于两个关系，并产生一个关系作为结果。不同于连个关系上的笛卡尔积，它将第一个关系的每个元组与第二个关系的所有元组进行连接。

“对于大学中所有讲授课程的教师，找出他们的姓名以及所讲述的所有课程标识”

Select name,course_id

From instructor,teaches

Where instructor.ID=teacher.ID;

该查询可以用sql的自然连接运算更简洁的写作：

Select name,cours_id

From instructor natural join teaches;

以上两个查询产生的相同的结果。

在一个sql查询的from子句中，可以用自然连接多个关系结合在一起，如下所示：

Select A1,A2,A3…..n

From r1 natural join r2 natural join 。。。。。 natural join rn

Where

P

“列出教师的名字以及他们所讲授课程的名称”。

次查询可以用sql写为：

Select name，title 

From instructor natural join teaches,course

Where teaches.course_id=course.course_id;

下面的sql查询不会计算出相同的结果：

Select name,title

From instructor natural join teaches natural join course;

为了发扬自然连接的优点，同时避免不必要相等属性带来的危险，sql提供了一种自然连接的构造形式，允许用户来指定需要哪些列相等。

Select name,title

From (instructor natural join teaches) join course using (course_id)

Join …..using 运算中需要给定一个属性名列表，其两个输入中都必须具有指定名称的属性。考虑运算r1 join r2 using（A1，A2），它与r1和r2的自然连接类似。

附加的基本运算

更名运算

Select name,course_id

From instructor,teaches

Where instructor.ID=teaches.ID;

更名：

Old-name as new-name;

用名字instructor_name来代替属性名name:

Select name as instructor_name,course_id

From instructor,teaches

Where instructor.ID=teaches.ID;

“找出满足下面条件的所有教师的姓名，他们的工资至少比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关系的两个拷贝，但更准确的说是instructor关系的别名，想T和S那样被用来重命名关系的标识符在sql标准中被称作相关名称（correlation name），但通常也被称作表别名（table alias），或者相关变量（correlation variable）或者元组变量（tuple variable）。

字符串运算

Sql使用单引号来标示字符串，字符串的相等运算是大小写敏感的。

Sql在字符串上有多种函数。

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

l 百分号（%）：匹配任意一个子串。

l 下划线（_）：匹配任意一个字符。

模式是大小写敏感的，也就是大写字符与小写字符不匹配，反之亦然。

详细例子：

u ‘Intro%’匹配任何以“Intro”打头的字符串。

u ‘%Comp%’匹配任何包含“Comp”子串的字符串，例如‘Intro to Computer Science’ 和‘Computational Biology’。

u ‘_ _ _’匹配只含有三个字符的字符串。

u ‘_ _ _%’匹配至少含有三个字符的字符串。

Sql中用比较运算符like来表达模式。

“找出所在建筑名称中包含子串‘Watson’的所有系名”

Select dept_name from department where building like ‘%Watson%’

Like比较运算中用escape关键词来定义转义字符。

为了说明这一用法，考虑一下模式，它使用反斜线（\）作为转义字符

like ‘ab\%cd%’ escape ‘\’匹配所有以“ab%cd”开头的字符串。

like ‘ab\\cd%’ escape ‘\’ 匹配所有以“ab\cd”开头的字符串。

星号 “*”可以用在select子句中表示“所有的属性”，因而如下查询的select子句中使用instructor.*:

Select instructor .*

From  instructor,teaches

Where instructor.ID=teaches.ID ;

表示instructor中的所有属性都被选中。

排列元组的显示次序

Sql为用户提供了一些对关系中元组显示次序的控制。Order by子句就可以让查询结果按排列顺序显示。为了按字母顺序列出在Physics系的所有教师。

Select name

From  instructor

Where dept_name =’Physics’

Order by  name;

Order by 子句默认使用升序。要说明排序顺序，我们可以使用desc表示降序，或者使用asc表示升序。

排序可以在多个属性上进行。假设我们希望按salary的降序列出整个instructor关系。如果有几位教师的工资相同，就将它们按姓名升序排列。

Select * 

From

 Instructor

Order by salary desc, name asc;

首先是工资，然后是姓名。

Where 子句谓词

Sql提供between比较运算符来说明一个值是小于或等于某个值。

如果我们想找出工资在90000和10000美元之间的教师的姓名，我们可以使用between比较运算符。

Select name

From instructor

Where salary between 90000 and 100000;

它可以取代

Select name

From instructor

Where salary <=1000000 and salary >=90000;

Likewise ，还可以使用not between比较运算符。

Select  name,course_id

From instructor,teaches

Where instructor.ID=teaches.ID and dept_name=’Biology’;

Sql允许我们用记号（v1，v2…vn）来表示一个分量值分别为v1，v2….vn的n维元组。在元组上可以运用比较运算符。按字典顺序可以进行比较运算。（a1，a2）<=(b1,b2)在a1<=b1且a2<=b2时为真。类似的，当两个元组在所有属性上相等时，它们是相等的。

Select name,course_id

From instructor,teaches

Where (instructor.ID,dept_name)=(teaches.ID,’Biology’);

集合运算

Sql作用在关系上union，intersect和except运算对应于数学集合论中并集，交集和补集运算。

在2009年秋季学期开设的所有课程的集合

Select course_id

From section

Where semester=’Fall’ and year=2009;

在2010年春季开设的所有课程的集合：

Select course_id

From section

Where semester=’Spring’ and year=2010;

并运算

为了找出在2009年秋季开课，或者在2010年春季开课或两个学期都开课的所有课程，我们可以写查询语句：

（select course_id

From section 

Where semester=’Fall’ and year=2009）

Union

(select course_id

From section

Where semester=’Spring’ and year=2010);

与select子句不同，union运算自动会去除重复。如果我们想保留所有重复，就必须用union all 代替union：

（select course_id

From section 

Where semester=’Fall’ and year=2009）

Union all

(select course_id

From section

Where semester=’Spring’ and year=2010);

交运算

为了找出2009年秋季和2010年春季同时开课的所有课程的集合，我们写出

（select course_id

From section 

Where semester=’Fall’ and year=2009）

Intersect

(select course_id

From section

Where semester=’Spring’ and year=2010);

Intersect自动去除重复。

如果想保留重复，就必须使用intersect all代替intersect

差运算

为了找出在2009年秋季开课但是在2010年春季不开的所有课程，我们写：

（select course_id

From section 

Where semester=’Fall’ and year=2009）

Except

(select course_id

From section

Where semester=’Spring’ and year=2010);

前一个关机减去后一个关系的差。

差为正的。自动去除所有重复的。

如果要保留重复则是用except all 代替except：

（select course_id

From section 

Where semester=’Fall’ and year=2009）

Except all

(select course_id

From section

Where semester=’Spring’ and year=2010);

空值

u And: true and unknown 结果是unknown， false and unknown 是false,unknown and unknown 的结果是unknown。

u Or true or unknown 结果是true ，false or unknown 的结果是unknown，unknown or unknown的结果似乎unknown。

u Not： not unknown的结果是unknown。

Select name 

From instructor

Where salary is null;//salary为空值的教师

聚集函数

聚集函数是以值的一个集合（集或者多重集）为输入、返回单个值的函数。Sql提供了五个固有聚集函数：

u 平均值：avg

u 最小值：min

u 最大值：max

u 总和：sum

u 计数：count

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

基本聚集

“找出Computer Science系教师的平均工资”。

Select avg（salary）

From instructor

Where dept_name=’Comp.Sci’;

该查询的结果是一个具有单属性的关系，其中只包含一个元组，这个元组的数值赌赢Computer Science系教师的平均工资。数据库系统可以给结果关系的属性一个任意的名字，该属性是由聚集产生的，我们可以给属性赋一个有意义的名称，如下所示：

Select avg（salary） as avg_salary

From instructor

Where dept_name=’Comp.Sci’;

有些情况下在计算聚集函数前需要先删除重复元组。如果我们确实需要删除重复元组，可以在聚集表达式中使用关键词distinct。比方有这样一个查询示例“找出在2010年春季学期讲授一门课程的教师数”，在该例中不论一个教师讲授了几个课程段，他只应该被计算一次。

所需信息包含在teaches关系中，我们书写查询如下：

Select count(distinct ID)

From teaches

Where semester=’Spring’ and year=2010;

我们经常使用聚集函数count计算一个关系中元组的个数。Sql中该函数的写法是count(*)

因此，要找出course关系中的元组数，可写成：

Select count(*)

From course;

Sql不允许用count(*)时使用distinct，在用max和min时使用distinct是合法的，尽管结果无差别。我们可以使用关键字all替代distinct来说明保留重复元组。但是，既然all是默认的，就没有必要这么做了。

分组聚集

有时候我们不仅希望将聚集函数作用在单个元组集上，而且希望将其作用在一组元组集上；在sql可用group by子句实现这个愿望。Group by子句中给出的一个或多个属性是用来构造分组的。

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

作为示例，“找出每个系的平均工资”，该查询的书写如下：

Select dept_name,avg(salary) as avg_salary

From instructor

Group by dept_name;

“找出所有教师的平均工资”，我们查询写在如下形式：

Select avg(salary)

From instructor;

在这里省略了group by子句，因此整个关系被当做是一个分组。

“找出每个系在2010年春季讲授一门课程的教师人数”，有关每位教师在每个学期讲授每个课程段的信息在teaches关系中。但是，这些信息需要与来自instructor关系的信息进行连接，才能得到每位教师所在的系名。这样我们把查询写做如下形式：

Select dept_name ,count(distinct ID) as instr_count

From instructor natural join teaches

Where semester=’Spring’ and year=2010

Group by dept_name;

Having 子句 

有时候，对分组限定条件比对元组限定条件更有用。例如，我们只对教师平均工资超过42000美元的系感兴趣。该条件并不针对单个分组，而是针对group by 子句构成的分组。为表达这样的查询，我们使用sql的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 子句的查询的定义可以通过下述操作序列来定义：

1. 与不带聚集的查询情况类似，最先根据from子句来计算出一个关系。

2. 如果出现了where 子句，where 子句中的谓词将应用到from子句的结果关系上。

3. 如果出现了group by子句，满足where 谓词的元组通过group by子句形成分组。数学如果没有group by子句，满足where 谓词的整个元组集被当作一个分组。

4. 如果出现了having子句，它将应用到每个分组上，不满足having子句谓词的分组将被抛弃。

5. Select子句利用剩下的分组产生出查询结果中的元组，即在每个元组上应用聚集函数来得到单个结果元组。

说明同时使用having 和where子句的情况，“对于在2009年讲授的每个课程段，如果该课程段有至少2名学生选课，找出选修该课程段的所有学生的总学分（tot_cred）的平均值”

Select course_id,semester,year,sec_id,avg(tot_cred)

From takes natural join student

Where year=2009

Group by course_id,semester,year,sec_id

Having count(ID)>=2;

对空值和布尔值的聚集

空值的存在给聚集运算的处理带来麻烦。

嵌套子查询

Sql提供嵌套子查询机制。子查询是嵌套在另一个查询中的select-from-where表达式。子查询嵌套在where子句中。通常对于集合的成员资格、集合的比较以及集合的基数进行检查。

集合成员资格

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

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

先前，我们通过对两个集合进行交运算来书写该查询，我们下面用另一种写法：

//(select course_id from section where semester=’Spring’ and year=2010

Select distinct couse_id from section where semester=’Fall’ and year 2009 and course_id in 

(select couse_id from section where semester =’Spring’ and year=2010);

也可以使用类似的方式使用not in。

In和not in操作符也能用于枚举集合。

Select distinct name from instructor

Where name not in (‘Mozart’,’Einstein’);

例题：

Select count(distinct ID)

From takes

Where (course_id,sec_id,semester,year) in 

(select course_id,sec_id,semester,year from teaches where teaches.ID=10101);

集合的比较

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

空关系测试

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.course_id);

同样可以使用not exists 属性。

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));

子查询

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

重复元组存在性测试

Sql 提供一个布尔函数，用于测试一个子查询的结果中是否存在重复元组。如果作为子查询结果中没有重复的元组，unique结构将返回true值。我们可以使用unique结构书写查询“找出所有在2009年最多开设一次的课程”

Select T.course_id

From course as T

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

From 子句的子查询。