【数据库】|第三章_ SQL

DDL\DML\DQL\DCL

3.2数据定义

基本类型

基本模式定义

create table

定义SQL关系

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

完整性约束：

• primary key：声明主码，非空且唯一
• foreign key references s：声明外码
• check：详见第四章check语句
• not null : 属性不允许空值

示例：大学生数据库部分SQL数据定义

create table department
      (dept_name varchar(20),
	   building varchar(15),
	   budget numeric(12,2),
	   primary key (dept_name));
create table course
      (course_id varchar(8),
	   title varchar(50),
	   dept_name varchar(20),
	   credits numeric(2,0),
	   primary key (course_id),
	   foreign key (dept_name) references department);
create table instructor
	  (ID varchar(5),
	   name varchar(20) not null,
	   dept_name varchar(20),
	   salary numeric(8,2),
	   primary key (ID),
	   foreign key (dept_name) references department);
create table section
	  (course_id varchar(8),
	   sec_id varchar(8),
	   semester varchar(6),
	   year numeric(4,0),
	   building varchar(15),
	   room_number varchar(7),
	   time_slot_id varchar(4),
	   primary key (course_id,sec_id,semester,year),
	   foreign key (course_id) references course);
create table teaches
	  (ID varchar(5),
	   course_id varchar(8), 
	   sec_id varchar(8),
	   semester varchar(6),
	   year numeric(4,0),
	   building varchar(15),
	   primary key (ID, course_id,sec_id, semester,year),
	   foreign key (course_id , sec_id,semester,year) references section,
	   foreign key (ID) references instructor);

添加

insert into

插入数据
值应遵循对应属性在关系模式中列出的顺序

insert into instructor
		values(10211,'Smith','Biology',66000);

添加属性

alter table r add A D;

删除

delete

删除所有元组

delete from student;

drop table

去掉关系，删除的是关于被去掉关系的所有信息

去掉属性

alter table r drop A;

3.3SQL查询的基本结构

单关系查询

select基本结构

select 属性名1,属性名2,...,属性名n
from 关系名1,关系名2,...,关系名n
<where P>

• select子句用于列出查询结果中所需要的属性

• from子句是在查询求值中需要访问的关系列表

• where子句是作用在from子句中的关系属性上的谓词

  • where⼦句⽤于过滤记录，即缩小访问数据的范围。
  • where后跟⼀个返回 true 或 false 的条件。

• P是一个谓词，可以理解为条件表达式

select 详细结构

select <all｜distinct> 目标列表达式1,目标列表达式2,...
from 关系名1,关系名2,...
<where 行条件表达式>
<group by 属性名序列 <having 组条件表达式>>
<limit a,b>
<order by 属性名 <ASC｜DESC>>

• 目标列表达式包括：属性名，算术表达式，聚集函数，* （ * 表示“所有的属性”）
• 读取from子句的表或视图做笛卡尔积，from子句列出查询需要访问的关系列表
• where子句找出满足条件表达式的元组
  • 比较运算符：<，<=，>，>=，= ，<>，!
  • 逻辑运算符：and，or，not
• 按group by子句指定列名分组，值相等的元组为一组，每个组产生结果表中的一条记录。可在每组中用聚集函数。如果group by子句带having短语，则只有满足指定条件的组才予输出
• 按select子句中给出的列名或表达式求值输出
• 若要去掉重复元组，可用关键字distinct指明
• 为保留重复元组，可用关键字all显式指明
  • 第⼀个参数a为起始行，从 0 开始；
  • 第⼆个参数b为返回的总行数，即返回第a+1~a+b行
• 按order子句列名的值升或降序,
  • asc ：升序（默认）
  • desc：降序

自然连接

natural join

1. 定义：作用于两个关系，并产生一个关系作为结果。
2. 只考虑在两个关系模式上都出现的属性上取值相同的元组对
3. 属性出现的顺序：

• 先是两个关系模式中的共同属性（只出现一次）
• 只出现在第一个关系模式中的属性
• 只出现在第二个关系模式中的属性

select A1,A2,...An
from r1 natural join r2 natural join...
where P;

例：列出教师的名字以及他们所讲授课程的名字

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

解释：

• 先计算instructor和teaches的自然连接，再计算该结果个course的笛卡尔积
• where从这个结果中取出：来自连接结果的课程标识与来自course关系的课程标识相匹配

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

会计算出错误结果:

• instructor和teaches的自然连接包括属性（ID,name,dept_name,salary,course_id,sec_id)
• course包含的属性（course_id,title,dept_name,credits)
• 二者自然连接的结果，需要既在depy_name上取值相同，又在course_id上取值相同
• 因此结果只输出将是所讲授的课程是他所在系的课程的（教师姓名，课程名称）结果对

join…using

select 属性名1,属性名2,...,属性名n
from 关系名1 join 关系名2 using (属性名1,属性名2,...属性名n)

• 按指定属性连接，不考虑未指定的共同属性
• 只考虑关系名2与关系名1在属性名1…属性名n上的匹配，其他的共有属性不考虑
• 这里的关系名可以是自然连接后的关系，即
  from（关系名1 join 关系名2）join 关系名3 using （属性名1,属性名2,...属性名n）

3.4附加的基本运算

更名运算

old-name as new-name

• 可以出现在select子句中，也可以在from子句中
• 可用于重命名关系
• 用于需要比较同一个关系中的元组的情况

#重命名关系
select T.name,S.course_id
from instructor as T,teaches as S
where T.ID=S.ID;

#比较同一个关系中的元组
select distinct T.name
from instructor as T, instructor as S
where T.salary>S.salary and S.dept_name='Biology';

#T\S 被称为表别名/相关名称/相关变量/元组变量

字符串运算

• 单引号标示字符串，两个单引号字符来区分字符串中的单引号
• 字符串相等运算大小写敏感
• 函数
  • 串联||
  • 提取字串
  • 计算字符串长度
  • 大小写转换：upper(s) \ lower(s)
  • 去掉字符串后的空格trim(s)

like模式匹配

大小写敏感

• 百分号（%）：匹配任意字串
• 下划线（_）：匹配任意一个字符

举例说明：
Intro%匹配任何以“Intro”开头的字符串
%comp%匹配任何包含“comp”的字符串
___%匹配至少包含三个字符的字符串
___匹配只包含三个字符的字符串

select 属性名
from 关系名
where 属性名 <not> like 字符串模式

escape定义转义字符

转义字符放在特殊字符前面，表示该特殊字符被当成普通字符

like 'ab\%cd%' escape '\'匹配所有以ab%cd开头的字符串
like 'ab\\cd%' escape '\'匹配所有以ab\cd开头的字符串

'^ [0-9]+$'纯数字字符串查找

示例：检查字符串上是否全为数字

select 属性名
from 关系名
where 属性名 like (属性名,'^[0-9]+$')

select 子句中的属性说明

*表示所有属性

形如select*的 select字句表示from子句结果关系的所有属性都被选中

select instructor.*
from instructor,teaches
where instructor.ID=teaches.ID;
# 表示instructor 中的所有属性都被选中

排序元组的显示次序

可以按多个列进行排序，并且为每个列指定不同的排序方式，默认为升序

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

where子句谓词

between
表示大于等于条件1并且小于等于条件2的条件

not between

表示既不大于等于条件1也不小于等于条件2的条件
格式：where 属性 <not> between 条件1 and 条件2...and 条件n

示例：列出Biology系教课的教师名和课程标识

select name,course_id
from instructor,teaches
where instructor.ID=teaches.ID and dept_name='Biology';
可写成：
select name,course_id
from instructor,teaches
where(instructor.ID, dept_name)=(teaches.ID,'Biology');

3.5集合运算

并运算 union

1. 基本规则：

• 所有查询的列数和列顺序必须相同
• 每个查询中涉及的表的列的数据类型必须相同或兼容
• 通常返回的列名取自第一个查询
• 默认去除相同行，只保留一行。使用union all保留相同行。
• 只能包含一个order by 子句，必须位于语句的最后
• 将查询之后的行放在一起（垂直放置）
  

找出2009年秋季开课或2010春季开课的课程或者两个学期都开设的课程

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

交运算 intersect (all)

找出2009年秋季和2010春季都开设的课程

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

差运算except (all)

从第一个输入中输出所有不出现在第二个输出中的元组。

找出2009年秋季开课但2010春季不开设的课程

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

3.6空值

• 将涉及空值的任何比较运算的的结果视为unknown
• 算数运算中：任一输入为空，则算数表达式的结果为空。
• and:
  • true and unknown = unknown
  • false and unknown = false
  • unknown and unknown = unknown
• or:
  • true or unknown = true
  • false or unknown = unknown
  • unknown or unknown = unknown
• not:
  • not unknown = unknown
• 可用is (not) unknown测试一个表达式的结果是否为unknown

测试空值

is (not) null

• 若谓词is not null所作用的值非空，那么为真。

找出instructor关系中salary为空值的所有教师

select name
from instructor
where salary is null;

• 如果元组在所有属性上取值相同或都为空值，就被当作相同组

3.7聚集函数/分组函数

• 以值的一个集合作为输入，返回单个值的函数。
• 五个固有聚集函数：
  • 平均值：avg（数字集）
  • 最小值：min
  • 最大值：max
  • 总和：sum（数字集）
  • 计数：count
    • count(具体字段）：表示统计该字段下所有不为null的元素总数
    • count（*)：统计表当中总行数
• 必须先分组才能使用，默认整张表是一组，因此不能直接使用在where后面
• 分组函数自动忽略null

基本聚集

格式：

select 函数符号(<distinct> 目标列表达式) <as 新属性名>
from 关系名
<where P>

分组聚集group by

• 在group by 子句中的所有属性上取值相同的元组将被分到一个组中
• 出现在select子句中但没有被聚集的属性只能是出现在group by子句中的那些属性：就是在select语句中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数
• 先执行group by 再执行select

找出每个系在2010年春季学期讲授一门课程的教师人数
分析：由于有关每位教师在每个学期讲授每个课程段的信息在teaches关系中，但是这些信息要与来自instructor关系的信息进行连接，才能得到每位教师所在系名

select dept_name,count(distinct ID) as instr_count
from instructor natural join teaches
where semester ='Sping' and year=2010
group by dept_name;

/*错误查询*/
select dept_name,ID,avg(salary)
from instructor
group by dept_name;

//在一个特定分组(dept_name)中的每位教师都有一个不同的ID,由于每个分组只输出一个元组，无法确定选哪个作为ID输出。

having子句

• having子句中的谓词在形成分组后才起作用
• 任何出现在having子句中，但没有被聚集的属性必须出现在group by子句中
• having和where的区别
  • where执行在group by之前，针对原始的数据筛选
  • having只能用在group by之后，针对分组后的内容筛选
  • where后的条件表达式里不允许使用聚集函数，而having可以。
• 代码逻辑
  • 最先根据from子句计算出一个关系
  • 如果有where，将where中的谓词应用到关系上
  • 如果有group by，满足where谓词的元组通过group by子句形成分组；
  • 没有group by,则满足where谓词的整个元组当作一个分组
  • 如果有having，将应用到每个分组上；不满足having谓词的将被抛弃
  • select利用剩下的分组产生查询结果元组，在每个分组上应用聚集函数来得到结果元组

对空值和布尔值的聚集

空值

• null参与聚集运算，除了count(*)以外的所有聚集函数都忽略输入中的空值
• 对于聚集函数，若输入集合为空，count返回0，其他函数返回空值

布尔值

• 布尔数据类型：true、false、unknown
• 聚集函数some和every分别计算布尔值的析取（or）和合取（and）

嵌套子查询

子查询是嵌套在另一个查询中的select-from-where表达式，通过将子查询嵌套在where子句中，可以实现对集合成员资格的测试、对集合的比较以及对集合基数的确定

集合成员资格

• in/(not in)测试元组是否是/（不是）集合中的成员
• in/not in可以用于枚举集合

找出2017秋季和2018春季都开课的课程id

select distinct course_id
from section
where semester= 'Fall' and year=2017 and course_id in 
(select course_id
from section
where semester = 'Spring' and year=2018);

列出既不叫Mozart, 也不叫Einstein的教师姓名

select distinct name
from instructor
where name not 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);

集合的比较

比较运算符some

• 至少比某一个要大：>some
• 形式：(<some,<=some,=,<>some,>=some, >some)
• 特殊： =some 等价于in; 但<> some不等价于not in

找出工资至少比Biology系某一个教师的工资要高的老师姓名

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

比较运算符all

• 比所有的都大：>all
• 形式：(<all ,<=all ,=all ,<>all ,>=all , >all )
• 特殊： =all 不等价于in; 但<> all等价于not in

空关系测试

exists

• 在作为参数的子查询非空时，返回true

列出2009秋季和2010同时开课的所有课程id

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

• 测试子查询结果集中是否不包含元组
• 用于模拟集合包含（超集）的逻辑测试
  • 若关系A包含关系B（A为B的超集），则not exists (B except A)为 true
  • 对于B except A，可以表达：在B中存在，但在A中不存在的记录；

如果B是A的子集，则B except A结果为空集，则not exists (B except A)为true.

找出选修了Biology系开设的所有课程的学生

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)

重复元组存在性测试

• unique:
  • 作为参数的子查询中没有重复的元组，返回true

找出所有在2009年最多开设一次的课程

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

//另一种等价表达方式
select T.course_id
from course as T
where 1>=(
select count(R.course_id)
from section as R
where T.course_id=R.course_id and R.year=2009);

• not unique:

找出所有在2009年最少开设两次的课程

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

from子句中的子查询

(课本P54,55,详细例子解释）

with子句

• with子句提供定义临时关系的方法，此定义只对包含with子句的查询有效

标量子查询

• 只返回包含单个属性的单个元组的子查询
• 可出现在返回单个值的表达式能够出现 的任何地方

3.9数据库的修改

删除

• 元组的删除：delete from 关系名 <where 条件表达式>
• 清空表中的数据：truncate 关系名

删除instructor中Finance系的教工元组

delete from instructor
where dept_name = 'Finance'

删除instructor中，所在系位于Watson大楼的教工元组

 delete from instructor
 where dept_name in (select dept_name                                    
                   from department                                                     
                   where building = 'Watson')

插入

• 插入一条：insert into 关系名 <(属性名[,属性名]...)> values (值[,值]...)
• 插入若干：insert into 关系名 <(属性名[,属性名]...)> ()子查询

让Music系每个修满144学分的学生成为Music系的教师，其工资为18000美元

insert into instructor
	select ID,name,dept_name,18000
	from student
	where dept_name='Music' and tot_cred>144;
#即在查询结果的基础上插入元组

更新

update

• 格式：update 关系名 set 属性名 = 表达式/子查询 <,属性名= 表达式/子查询...> <where 条件表达式>
• 规则：首先检查关系中的所有元组，看是否应该被更新，然后执行
• update中语句的顺序重要

给工资超过100000美元的教师涨3%的工资，其余教师涨5%
若改变两条语句的顺序，就变成了工资略少于000美元的教师涨8%的工资

update instructor
set salary=salary*1.03
where salary>100000;

update instructor
set salary=salary*1.05
where salary<100000;

case结构

• 在一条update语句中执行多种更新，避免更新次序引发的问题
• 结构：

当i是第一个满足的$pre{d}_i$时，此操作返回$resul{t}_i$
若没有满足的，返回$resul{t}_0$

case 
	when pred1 then result1
	when pred2 then result2
	...
	when predn then resultn
	else result0
end

• 标量子查询在 SQL更新语句中也非常有用，它们可以用在 set子句中。

我们把每个 student 元组的 tot_cred 属性值设为该生成功学完的课程学分的总和。我们假设如果一个学生在某门课程上的成绩既不是’F’，也不是空，那么他成功学完了这门课程

#使用set子句中的子查询
update student S
set tot_cred =(
	select sum(credits)
	from takes natural join course
	where S. ID = takes. ID and
		takes. grade <> ’F’and
		takes. grade is not null);

注意子查询使用了来自update语句中的相关变量S。
如果一个学生没有成功学完任何课程，上述更新语句将把其tot_cred属性值设为空。
如果想把这样的属性值设为0的话，我们可以使用另一条 update语句来把空值替换为0。

更好的方案是把上述子查询中的“select sum(credits)”子句替换为如下使用case 表达式的 select 子句:

select case
	when sum(credits) is not null then sum(credits)
	else 0
	end