数据库 第3章 SQL

第三章 SQL

3.2 SQL数据定义

3.2.1 基本类型

SQL标准支持多种固有类型，包括：

char(n)：固定长度的字符串。varchar(n)：可变长度的字符串。int：整数类型。smallint：小整数类型。numeric(p, d)：定点数，用户指定。p表示数的个数，d表示小数点后的位数。real，double precision：浮点数与双精度浮点数，精度与机器相关。float(n)：精度至少为n位的浮点数。

对于char(n)而言，若字符串中字符个数小于n，则使用空格自动补齐。对于varchar(n)而言，上述情况不会自动补齐。varchar保存的字符个数等于字符串的个数，即使它小于n。

3.2.2 基本模式

创建关系

使用create table命令来创建关系。一般格式如下。

create table r (A1, D1, A2, D2, ..., An, Dn, <完整性约束>, ..., <完整性约束>);

r是关系名，Ai和Di分别是关系的属性，域和可选的约束。

可选的约束有以下几种。

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

完整性约束有以下几种。

primary key(Aj1, Aj2, …, Ajm)：primary key声明了主码，关系中没有一个元组可以在主码属性上取空值或重复。foreign key(Ak1, Ak2, …, Akn) references s：foreign key声明了在关系中任意元组在(Ak1, Ak2, …, Akn) 上的取值必须在s中存在某元组，其在上述属性中取相同值。

SQL禁止任何破坏完整性约束的数据库更新。主要有以下几种方式。

不允许在一条新插入或新修改的元组在任意一个主码上有空值或与其他元组重复。不允许在一条新插入或新修改的元组在被引用关系中，不存在在外码上与新插入或新修改的元组相同的元组。

插入元组

插入元组使用insert命令。

删除元组

删除元组使用delete命令。

删除表

删除表使用drop命令。

修改表的定义

修改表的定义使用alter命令。

3.3 SQL查询的基本结构

SQL查询的基本结构是select-from-where。查询的输入是在from中列出的关系，然后再这些关系上执行select和where指定的运算，产生一个关系作为结果。

3.3.1 单关系查询

单关系指的是在from后只有一个关系，例如。

”找出所有教师的名字“。

select name from instructor;

在SQL查询语句中，运行结果默认是不去重的，如果要去除重复，则需要加入distinct。

select distinct name from instructor;

select子句中还可以进行四则运算，运算对象可以是常数或元组的属性。

select ID, name, salary * 1.1 from instructor;

where子句允许我们只选出那些在from子句满足特定谓词条件的元组，例如。

“找出所有在Comp.Sci.部门且工资超过70000美元的教师的姓名”。

select name from instructor where dept_name = 'Comp.Sci' and salary > 70000;

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

3.3.2 多关系查询

SQL多关系查询可以理解如下。

为from子句中列出的关系产生笛卡尔积。在步骤1的结果上应用where指定的谓词。在步骤2的结果中的每个元组，输出select子句中指定的属性。

例如，“选出所有教师的姓名，以及他们所在系的名称和系所在建筑的名称”

select name, instructor.dept_name, building from instructor, department where instructor.name = department.name

当from子句中产生的元组会出现重名的属性时，需要加上前缀以区分。

3.3.3 自然连接

自然连接natural join作用于两个关系，并产生一个关系作为结果。自然连接只选出两个关系中在相同的属性上取值也相同的元组，并且结果中不含有重名的属性值。

“找出所有讲述课程的教师姓名以及他们所讲述的所有课程标识”

select name, course_id from instructor natural join teaches;

其等价表述是。

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

自然连接可以多次结合使用，但会出现一些问题，“找出所有讲述课程的教师姓名以及他们所讲述的所有课程名”

select name, title from instructor natural join teaches natural, course where teaches.course_id = course.course_id;

若进行多次自然连接，则找出的结果为“教师教授的课程必须是教师所在部门开设的课程”，是上述要求结果的子集。

select name, title from instructor natural join teaches natural natural join course;

这是因为自然连接在运算时也要求一些不必要相等的属性（如上面的dept_name）也保持相等。实际上，我们可以使用join...using来为自然连接指定考察的属性。

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

3.4 附加的基本运算

3.4.1 更名运算

我们可以使用as来为属性或者是关系更名。例如

select name as instructor_name, course_id from instructor, course; select S.name, T.course_id from instructor as S, course as T where S.ID = T.ID;

更名的另一个用途是在同一个关系的比较上。例如

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

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

3.4.2 字符串运算

SQL使用单引号来表示字符串，如‘computer’。若单引号是字符串的一部分，则在单引号前再加一个单引号，如‘It’‘s a’。

在SQL标准中，字符串上的相等运算是大小写敏感的。

SQL允许在字符串上有多种函数，如串联（“||”）、提取字串、计算字符串长度、大小写转换（upper(s) lowe(s)）、去掉字符串后面的空格（trim(s)）。

在字符串上可以使用like操作符来进行模式匹配。

%：匹配任意子串。_：匹配任意一个字符。

例如

‘Intro%’:匹配任意以Intro开头的字符串。%Comp%：匹配任何包含Comp的字符串。___：匹配只含3个字符的字符串。___%：匹配任何以3个字符开头的字符串。

当匹配字符串中有特殊字符%，_时，需要使用\作为转义字符，也可以使用not like来寻找不匹配项。

3.4.3 select子句中的属性说明

星号*可以用在select子句中表示“所有的属性”。

3.4.4 排列元组的显示次序

使用order by子句就可以让查询结果中元组按排列顺序显示，用desc表示降序，用asc表示升序，也可以指定在多个属性上进行，例如。

select * from instructor order by salary desc, name asc;

3.4.5 where子句谓词

where子句中提供between来表示闭区间，例如

where salary between 900 and 1000;

也可以将多个and连接起来的相等运算表示为两个元组相等。

where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

3.5 集合运算

SQL使用union、intersect、except来表示数学中的交、并和差运算。

3.5.1 并运算

例如，“找出在2009年秋季学期开课或在2010年春季学期开课的课程”

(select course_id from section where year = 2009 and semester = 'Fall') union (select course_id from section where year = 2010 and semester = 'Spring');

3.5.2 交运算

例如，例如，“找出在2009年秋季学期开课且在2010年春季学期开课的课程”

(select course_id from section where year = 2009 and semester = 'Fall') intersect (select course_id from section where year = 2010 and semester = 'Spring');

3.5.3 差运算

例如，例如，“找出在2009年秋季学期开课且不在2010年春季学期开课的课程”

(select course_id from section where year = 2009 and semester = 'Fall') intersect (select course_id from section where year = 2010 and semester = 'Spring');

union, intersect, except自动去除重复元组，若想保留重复，需要在上面三个关键字后加上all关键字。

3.6 空值

SQL允许空值null的存在，判断空值时不使用等号而是使用is null和 is not null。当涉及比较运算时，null既可以是true也可以是false。

3.7 聚集函数

聚集函数是以值的一个集合（集或多重集）为输入，返回单个值的函数。SQL提供了5个固有的聚集函数。

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

3.7.1 基本聚集

例如，“找出Computer Science系教师的平均工资”

select avg(salary) from instructor where dept_name = 'Comp.Sci';

也可以给查询结果起一个名字

select avg(salary) as avg_salary from instructor where dept_name = 'Comp.Sci';

可以在聚集前删除重复的元组，“找出在2010年春季学期上课的教师人数”

select count(distinct ID) from teaches where semester = 'Spring' and year = '2010'

可以在count中使用*来表示元组，从而计算出所有的元组个数。

select count(*) from instructor;

3.7.2 分组聚集

使用group by可以构造出多个分组，使得聚集函数分别作用在每个元组上。当不使用group by 时，当作一个分组处理。

例如，“找出每个系的平均工资”

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

当使用group by时，需要保证select 后的属性是已经在group by中已经出现的。

3.7.3 having子句

having子句在形成分组后对作用在每一个分组整体上。

例如，“对于在2009年开设的课程，如果该课程至少有两名学生选课，找出选修该课程段的所有学生的总学分”

select course_id, semester, year, sec_id, avg(tot_cred) from takes natrual join student where year = 2009 group by course_id, semester, sec_id having count(ID) > 1;

3.8 嵌套子查询

因为查询语句的结果还是一个关系，因此，查询可以嵌套在from和where语句中，称为嵌套子查询。

3.8.1 集合成员资格测试

使用in来测试元组是否在关系中。

例如，“找出在2009年秋季学期开课且在2010年春季学期开课的课程”

select course_id from section where year = 2009 and semester = 'Fall' and course_id in ( select course_id from section where year = 2010 and semester = 'Spring' );

in和not in也适合枚举集合，例如

select name from instructor where name in ('Mozart', 'Einstein');

也作用在元组上，例如，“找出不同的学生总数，他们选修了ID为10101的教师所讲授的课程”。

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

3.8.2 集合的比较

some

例如，> some表示至少比某一个大。

“找出所有满足下列条件的教师，他们的工资至少比Biology系的某一教师工资多”

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

all

例如，> all表示比所有的都大。

“找出所有满足下列条件的教师，他们的工资比Biology系的所有的教师工资多”

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

3.8.3 空关系测试

使用exists可以测试一个关系是否为空，若为空，则exists语句返回true，否则返回false。

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

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

上面的查询说明了在SQL中，来自外层查询的一个相关名称可以用在where语句的子查询中。使用了来自外层查询相关名称的子查询被称为相关子查询。

not exists和exists的结果相反，但有其独特用途。

例如，可以使用not exists来表示包含关系。若$A\subseteq B$，则$A-B$为空集，用SQL语句表示如下。

not exists (A except B)

返回真等价于$A\subseteq B$。

例如，“找出所有选修了Biology系开设的所有课程的学生”

select ID from student where not exists ( ( select course_id from takes where takes.ID = student.ID ) except ( select course_id from course where course.dept_name = 'Biology' ) );

3.8.4 重复元组存在性测试

unique可用于检查关系中是否存在重复元组。如果关系中存在重复元组，则unique返回真，否则返回假。

例如，“找出所有在2009年最多开设一次的课程”

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

not unique与unique的作用相反。

3.8.5 from中的子查询

由于子查询得出的是1个关系，from后面跟随的也是一个关系。因此，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;

又如，“找出所有系的工资总额中最大的工资总额”

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

注意，在from子句中的嵌套子查询不能使用来自from子句之外的关系的相关变量。

3.8.6 with子句

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

例如，“找出具有最大预算值的系”

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

相对于嵌套子查询，with子句可以使得sql语句的逻辑更加清晰。

例如，“找出所有工资总额大于所有系得平均工资总额的系”

with dept_total(dept_name, tot_salary) as ( select dept_name, sum(salary) as tot_salary from instructor group by dept_name ), dept_total_avg(avg_tot_salary) ( select avg(tot_salary) from dept_total) select dept_name from dept_total, dept_total_avg where dept_total.tot_salary > dept_total_avg.avg_tot_salary;

3.8.7 标量子查询

标量子查询如查询所有教师的平均工资，其只返回单个值，但仍然认为标量子查询返回的是一个关系。

3.9 数据库的修改

3.9.1 删除

delete用于数据库的删除操作，其只能删除元组，而不能只删除元组上的某些值。一般用法如下。

delete from r where p;

where可以被省略，这会导致关系中所有的元组都被删除。

3.9.2 插入

insert用于数据库的插入，有如下用法。

单个元组的插入请求

insert into course values ('CS-457', 'Database System','Comp.Sci',4);

values中给出的值必须要和course中的属性对应。

指定属性插入

insert into course(course_id, title, dept_name, credits) values ('CS-457', 'Database System','Comp.Sci',4);

指定属性插入后，未被指定的属性将会被赋值为null。

插入关系

insert into course select ID, name, dept_name, 18000 from student where dept_name = 'Music';

其中，select语句在insert之前就被执行完毕。

3.9.3 更新

update用于在不改变整个元组的情况下修改其部分属性的值。例如

update instructor set salary = salary * 1.5 where salary < 15000;

SQL先检查关系中所有的元组，在满足更新条件下才会被更新。

SQL提供case结构，可以提供多种选择来更新。例如

update instructor set salary = case when p_1 then value_1 when p_2 then value_2 ... when p_n then value_n else value_0 end