【专业课学习】《数据库原理》第四章习题

4.1 简述SQL Server的特点。

综合统一、语法简单、易学易用、面向集合操作、高度非过程化和一语多用等。

4.2 解释ODBC。简述SQL包含的子语言系统。

ODBC：Open Database Connectivity（开放数据库互连），便于各种编程语言访问和操作数据库。

子语言系统：数据定义语言（Data Definition Language，DDL）；数据操纵语言（Data Manipulation Language，DML）；数据控制语言（Data Control Language，DCL）。

4.3 简述关系代数的选择、投影和连接，分别对应SELECT…FROM…WHERE的短语。

• 选择$σ$用于从某个关系$R$中筛选出符合给定条件的元组(记录)，对应where；
• 投影$π$用于从某个关系$R$中选择出若干属性列组成的关系，对应select；
• 连接$⋈$用于将两个关系按一定条件进行连接后生成新关系，对应from；

4.4 使用SQL语句创建习题2.5的4表。

--供应商表
create table S(
    SNo char(4) primary key check(SNo ~ '^S[0-9]+$'),
    SName char(8) not NULL,
    City char(10) not NULL
);

--零件表
create table P(
    PNo char(4) primary key check(PNo ~ '^P[0-9]+$'),
    PName char(8) not NULL,
    Color char(4) not NULL,
    Weight int not NULL
);

--工程表
create table J(
    JNo char(4) primary key check(JNo ~ '^J[0-9]+$'),
    JName char(8) not NULL,
    City char(20)
);

--供应情况表
create table SPJ(
    Qty int not NULL,
    SNo char(4),
    PNo char(4),
    JNo char(4),
    foreign key(SNo) references S(SNo),
    foreign key(PNo) references P(PNo),
    foreign key(JNo) references J(JNo)
);

在实验课使用的SQL Server 2016中，不支持使用正则表达式作为check约束的条件。

因此在如上的sql命令中只能将约束条件写成形如SNo like 'S[0-9][0-9][0-9]'的形式。

4.5 在习题4.4的4表中，使用SQL语句完成如下操作：

（1）查询所有供应商的姓名和所在城市。

select SName,City from S;

（2）查询所有零件的名称、颜色、重量。

select PName,Color,Weight from P;

（3）查询使用供应商S001所供应零件的名称。

select distinct PName from SPJ,P
    where SPJ.PNo=P.PNo and SNo='S001';

（4）查询提供了P002零件的供应商及提供数量。

select SNo,Qty from SPJ where PNo='P002'; 

（5）查询上海厂商供应的所有零件号码。

select PNo from SPJ,S
    where SPJ.SNo=S.SNo and City='上海';

（6）查询由供应商S003供应的P003零件的名称。

select PName from SPJ,P
    where SPJ.PNo=P.PNo and SPJ.SNo='S003' and SPJ.PNo='P003';

（7）查询有供应记录的非天津供应商。

select SPJ.SNo from SPJ,S
    where SPJ.SNo=S.SNo and S.City!='天津';

或

select SNo from SPJ
    except select SPJ.SNo from SPJ,S where SPJ.SNo=S.SNo and City='天津';

（8）把全部红色零件的颜色改成蓝色。

update P set Color='蓝' where Color='红';

（9）删除供应商S004，并从供应关系中删除其相应的记录。

--在这个例子中，由于建表的时候没有开启级联删除，
--必须首先在SPJ表中删除相关记录，否则会违反外键约束规则
delete from SPJ where SNo='S004';
delete from S where SNo='S004';

当然在实际问题中，如果条件允许的话，我们也可以直接为外键表SPJ配置级联删除，这样就只需要删除S表中的记录，SPJ表就会自动更新：

--先移除原先SPJ表中SNo字段的外键约束规则
alter table SPJ
drop constraint spj_sno_fkey;

--为SPJ表的SNo字段配置级联删除和级联更新
alter table SPJ
add constraint spj_sno_fkey 
foreign key(SNo) references S(SNo)
on delete cascade on update cascade;

--直接删除S表中的记录，发现SPJ表中的相应记录也被删除
delete from S where SNo='S004';

(10) 将记录(S006, P006, J001, 300)插入到表SPJ中

--默认情况下，insert into语句在插入新记录时，values括号里填的数据的先后顺序
--应与使用create table创建数据表时候声明字段的先后顺序保持一致
insert into SPJ values(300, 'S006', 'P006', 'J001');

或

--如果不想记忆各个字段的先后顺序，可以显式声明一个参数列表，保证values括号里填
--的数据与参数列表的顺序保持一致即可
insert into SPJ(SNo, PNoL, JNo, Qty) values('S002', 'P003', 'J001', 999);

4.6 已知数据库的关系分别为职工（工号，姓名，年龄，性别）、社团（团号，团名，团长，地点）、参加（工号，团号，日期）。

数据表已经建表完成，具体如下：

create table 职工(
    工号 varchar(3) primary key,
    姓名 varchar(4) not NULL,
    年龄 int not NULL,
    性别 boolean
);

create table 社团(
    团号 varchar(3) primary key,
    团名 varchar(4) not NULL,
    团长 varchar(3),
    地点 varchar(20) not NULL,
    foreign key(团长) references 职工(工号)
    on delete no action on update no action
);

create table 参加(
    工号 varchar(3),
    团号 varchar(3),
    日期 date,
    foreign key(工号) references 职工(工号)
        on delete cascade on update cascade,
    foreign key(团号) references 社团(团号)
        on delete cascade on update cascade
);

在实验课使用的SQL Server 2016中，布尔值类型的字段类型名为bit，取值为True或者False

在设计外键约束时，需要注意避免多重路径级联或者循环级联的问题。例如本例中若为"社团"表的"团长"外键设置级联操作on delete no action或者on update action则会出错。因为这种情况下可能会存在两种级联操作的路径，例如当"职工"表中某个社团的团长工号发生更新，那么这个时候"社团"表中的"团长"字段和"参加"表中的"职工"字段都需要更新，而数据库软件无法确定需要先更新两者中的哪一个，这便是多重路径级联问题。在sql server 2019中，一旦检测到此类问题，便会直接拒绝执行sql语句。

现在请使用SQL语句完成如下操作：

（0）为数据表"社团"添加"团长"字段。

-- 以下为在PostgreSql中的写法
alter table 社团
add column 团长 varchar(3);

alter table 社团
add foreign key (团长) references 职工(工号)
on update cascade on delete no action;

-- 以下为在Sql Server 2016中的写法，添加字段不需要column关键字
-- 此外这里演示一下设置外键约束和add写在同一行的写法
alter table 社团
add 团长 varchar(3) foreign key references 职工(工号)
on update cascade on delete cascade;

这里在为字段设置约束规则的时候，我没有显式地使用add constraint命令。在这种情况下，数据库软件会自动为我所设置的外键约束生成一个默认名称。

（1）查询参加篮球队的职工的工号和姓名。

select 职工.工号,职工.姓名
    from 职工,社团,参加
    where 参加.工号=职工.工号 and 参加.团号=社团.团号 and 社团.团名='篮球队';

（2）查询职工号为666的职工参加的社团名称。

select 社团.团名
    from 职工,社团,参加
    where 参加.工号=职工.工号 and 参加.团号=社团.团号 and 参加.工号='666';

（3）查询各个社团编号及相应的参加人数。

select 团号,count(工号) from 参加 group by 团号;

（4）建立视图SInfo（工号，姓名，性别，年龄，团号，团名，团长，日期）。

-- 建立视图的写法实际上就是一个create view语句嵌套一个select... from...语句
create view SInfo as
    select 职工.工号,姓名,性别,年龄,社团.团号,团名,团长,日期
    from 参加,职工,社团
    where 参加.工号=职工.工号 and 参加.团号=社团.团号;

（5）查询参加歌唱队或者篮球队的职工的工号和姓名。

select 职工.工号,姓名
    from 参加,职工,社团
    where 参加.工号=职工.工号 and 参加.团号=社团.团号 and 团名 in ('歌唱队', '篮球队');

（6）查询没有参加任何社团的职工基本信息。

方法一：

select 工号,姓名,年龄,性别 from 职工
except
select 职工.工号,姓名,年龄,性别 from 职工,参加 where 职工.工号=参加.工号;

方法二：

select * from 职工 where not exists (
    select distinct 工号 from 参加
    where 职工.工号=参加.工号
);

方法三：

select * from 职工 where 工号 not in
    (select distinct 工号 from 参加);

not exists嵌套查询构成了一个"相关子查询"，而not in则是纯粹依赖判断数据值是否相等来进行工作。因此使用前者时必须加上where 职工.工号=参加.工号来声明连接关系，而后者则没有必要。

（7）查询参加全部社团的职工基本信息。

方法一：

针对每一个职工，尝试去查询除了在"参加"数据表中已经参加了的社团外，在"社团"数据表中是否有他还没参加的社团。如有，则该职工一定没有参加所有社团，需要排除。

代码如下：

select * from 职工 where not exists (
    -- 这里运用了不相关子查询，先把当前遍历到的员工所有参加的社团找出来
    -- 再去"社团表"中查员工没参加的社团
    select * from 社团 where 团号 not in (
        select 团号 from 参加 where 职工.工号=参加.工号
    )
);

或

select * from 职工 where not exists (
    -- 这里运用了相关子查询
    -- 第二层嵌套在对第一层子查询的结果进行筛选
    -- 以查出当前正在遍历的职工有哪些社团没参加
    select * from 社团 where not exists (
        -- 第一层嵌套查出当前正在遍历的社团记录在"参加"表中被当前正在遍历的职工参加的记录
        select * from 参加 where 职工.工号 = 参加.工号 and 社团.团号 = 参加.团号
    )
);

或

select * from 职工 where not exists (
    select 团号 from 社团
    except
    select 团号 from 参加 where 职工.工号=参加.工号
);

exists关键字表示若后接的"相关子查询"结果为空，则保留当前遍历到的记录作为查询结果；not exists则反之。 关于exists/not exists，具体可参考zhuanlan.zhihu.com/p/20005249

方法二：

先连接职工表和参加表，对连接后所得的表按不同的员工进行分组。对分到同一组（即对应同一个职工）的记录进行计数，如记录数恰好等于社团表中的记录总数，则说明该职工参加了所有的社团。

select 职工.姓名, 职工.姓名, 职工.年龄, 职工.性别
from 职工 join 参加 on 职工.工号 = 参加.工号
group by 职工.工号
having count(参加.团号) = (select count(*) from 社团);

（8）查询参加工号为666的职工所参加的社团的职工工号。

select distinct 工号 from 参加 where 团号 in (
    select 团号 from 参加 where 工号='666'
);

（9）统计每个社团的参加人数。

select 团号,count(*) as 人数 from 参加
group by 团号;

此处as用于为查询结果字段取别名，例如使用这里展示的命令会得到形如

团号	人数
...	...

的查询结果。而非形如

团号	count
...	...

的查询结果。

（10）查询参加人数最多的社团的团名和参加人数。

方法一：

先分组统计出每个社团的参加人数，存到一个临时表中。再从临时表中筛选出人数最多的那一个社团。

with temp as (select 团号, count(工号) as 人数 from 参加 group by 团号)
select 团号, 人数 from temp where 人数 = (select max(人数) from temp);

特别注意，形如以下的写法是错误的。因为其表达的意思是按"团号"对子查询的结果进行分类，再求每个分类中的最大值，而不是所有COUNT(工号)之中的最大值。同时我们又不能把group by给去掉，因为同时查询聚合列和非聚合列时必须使用group up来声明分组，而我们希望的是不分组直接求出COUNT(工号)中最大值及其对应的团号。因此形如这种写法的sql命令是完全行不通的。

SELECT 团号, MAX(人数) FROM (
    SELECT 团号, COUNT(工号) AS 人数
    FROM 参加
    GROUP BY 团号
) AS tempTable
GROUP BY 团号;

方法二：

-- 先按部就班根据团号分组，并计算出每个社团的参加人数
-- 对查询结果进行降序排序，再去第一项即为所求

-- PostgreSQL命令如下：
select 团号, count(工号) as 人数 from 参加 group by 团号
order by 人数 desc
limit 1;

-- Sql Server命令如下：
select TOP 1 团号, count(工号) as 人数 from 参加 group by 团号 
order by 人数 desc;

（11）查询参加人数超过99人的社团的团名、团长和人数。

方法一：显式创建临时表

with temp as (select 团号, count(工号) as 人数 from 参加 group by 团号)

select 社团.团名, 团长, 人数
from temp,社团
where 社团.团号=temp.团号 and 人数>99;

方法二：利用分组后对组进行筛选（group by+having）

select 团名,团长,人数
from 社团, (
    select count(团号) as 人数 from 参加 group by 团号 having count(团号)>99
) as temp
where 社团.团号=temp.团号;

（12）把对职工和社团的查询和插入权限赋给李明，并允许其继续授权给其他人。

grant select,insert on 职工,社团 to 李明 with grant option;

(课后补充) 查询参加了所有工号为'001'的职工所参加社团的所有职工编号

这题本质上与(7)是同一个模板，但本题中需要注意的是需要对"参加"表作两次遍历，而"社团"表与本题的查询条件无关，在本题中是不需要考虑的。

select 工号 from 职工 where not exists (
    select * from 参加 as t1 where t1.工号='001' and not exists (
        select * from 参加 as t2 where t2.团号=t1.团号 and t2.工号=职工.工号
    )
);

(课后补充) 查询仅参加了团号为'001'、'002'和'003'的所有职工的工号

思路：把参加了这三个社团的职工先全部筛出来，在此基础上筛选出除了这三个社团之外没有参加别的社团的职工，即为答案。

先展示一下一开始我做题的时候写的sql命令，用了多个exists串联，比较啰嗦：

with temp as (select * from 职工 where exists (
    select * from 参加 where 参加.工号=职工.工号 and 参加.团号='001'
) and exists (
    select * from 参加 where 参加.工号=职工.工号 and 参加.团号='002'
) and exists (
    select * from 参加 where 参加.工号=职工.工号 and 参加.团号='002'
))

select 工号 from temp where not exists (
    select * from 参加 where 参加.工号=temp.工号 and 团号 not in ('001', '002', '003')
);

后来我采用count聚合函数来简化命令，最终答案如下：

select 工号 from 职工 where (
   select count(*) from 参加 where 参加.工号=职工.工号 and 参加.团号 in ('001', '002', '003') 
)=3 and (
   select count(*) from 参加 where 参加.工号=职工.工号
)=3;

(课后补充) 查询所有张姓职工的详细信息

select * from 职工 where 姓名 like '张%';

通配符 % 在SQL中表示匹配任意多个任意字符，主要用于模糊搜索。在标准SQL语言中，没有通配符*，此处一定要与常规的正则表达式语法区分！！！