数据库复习

已知某航海俱乐部管理系统由如下三个关系模式构成。

Boats(bid,bname,color) - 游艇（游艇编号，游艇名称，游艇颜色）

Sailors(sid,sname,rating,age) - 水手（水手编号，水手姓名，级别，年龄）

Reserves(sid,bid,day) - 预订关系（水手编号，游艇编号，预订日期）

（1）检索年龄大于30岁的水手编号和姓名

select sid, sname from sailors
where age > 30

（2）检索没有被预订的红色(red)游艇的名称

满足1：没有被预定。 2：颜色为red

表达式逻辑！（color!='red' or boats.bid=reserves.bid）

select bname from boats
where not exists (
select * from reserves where boats.color!='red' or boats.bid=reserves.bid)

（3）检索预订“Marine”游艇的水手编号和姓名；

满足：被预定的游艇and名字是'Marine' and查询sailors编号

select sailors.sid, sname from sailors, boats, reserves
where boats.bid=reserves.bid and boats.bname='Marine' and sailors.sid=reserves.sid

（4）显示每艘游艇编号及其预订次数，并要求只显示预订次数大于2的信息。

考察sql语句计数语法count(*), 表reserves中是有bid信息的.

select bid, count(*) from reserves group by bid having count(bid) > 2
-- 使用方法 select xxx, count(*) from Xtabel group by xxx having count(xxx) > yyy

（5）检索没预订游艇编号为102的水手姓名；

满足1：没预定游艇 or 预订编号不是102 正着看不好看，他的反语句是：即预订了游艇且编号为102

select sname from sailors
where not exists (
    select * from reserves where sailors.sid=reserves.sid and reserves.bid='102'
)

（6）检索年龄最大水手的姓名；

考察 sql语句中 max使用方法，max(id)，求出表中id最大值

select sname from sailors
where age=(select max(age) from sailors)

（7）检索水手级别大于7的水手姓名，级别；并按级别降序排列；

考察sql语言中升序降序排列

语法 order by type asc(desc) (asc表示升序，desc为降序，缺省默认升序)

select sname, rating from sailors where rating > 7 order by rating desc

（8）检索姓名中包含字母‘a’的水手预订的游艇名称(要求去重)；

去重 distinct 语法： select distinct xxx from table , 在table表中查找xxx，并去重

方法1：如果charindex（’字符‘，字符串）> 0表示存在, 或者locate也可以

方法2：用like，用法 sailors.sname like ‘%a%’

select distinct bname from boats, sailors, reserves
where sailors.sid=reserves.sid and charindex('a', sailors.sname) > 0 and boats.bid=reserves.bid

select distinct bname from boats, sailors, reserves
where sailors.sid=reserves.sid and sailors.sname like '%a%' and boats.bid=reserves.bid

（9）创建一个红色（red）游艇预订记录的视图，包括水手姓名，游艇名称，预订日期。

创建视图的语法是：

create view 视图名 as

select xxx, yyy, zzz from Txxx, Tyyy, Tzzz

where (判断条件)

select xxx, yyy, zzz from 视图名（cg上加上查询语句）

create view v_red as
select sname, bname, day from sailors, boats, reserves
where (boats.color='red' and boats.bid=reserves.bid and sailors.sid=reserves.sid)
select sname, bname, day from v_red

（10）删除所有红色（red）游艇的预订记录。

三种删除

1.delete:用于删除表中的行数据，如果不带where条件则会删除表中所有数据，删除操作作为事务记录在日志中，可回滚操作还原数据。

2.truncate：只删除表中所有的数据，删除操作不记录在日志中，不能回滚操作还原数据。

3.drop：用于删除表（表的数据、结构、属性以及索引也会被删除），并将表所占用的空间全部释放，不能回滚操作还原数据。

使用第一种

delete from reserves
where exists (
    select * from boats
    where (boats.color='red' and reserves.bid=boats.bid)
)
select sid,bid,day from reserves

（11）把所有红色（red）游艇修改为蓝色（blue）。

使用update，用法

updata 表名 set 要修改的值 = '修改值' where (条件)

update boats set color='blue' where(color='red')
select bid, bname, color from boats

（12）查询所有水手的姓名和级别以及预订的游艇名称和预订日期，要求结果去重。

sname, rating, 预订的游艇bname 以及预期日期

select distinct sname, rating, bname, day from sailors s
left join reserves r on r .sid=s.sid
left join boats b on b.bid=r.bid

（13）查询预定了所有红色（red）游艇的水手姓名。

select sname from sailors s
where not exists (
    select * from boats b
  	where b.color = 'red' and not exists (
        select * from reserves r
        where r.bid = b.bid and r.sid = s.sid
    )
)