首先呢,我们的第一步就是要建立一个数据库,我给他取名为srb(create database srb)。建立好了之后我们就开始了表的创建(create table...)我这里呢是建了三个表(sailors,boats,reserves,这也是我们小白常取的名字了hhh)
先是创建三个表(别忘了加约束条件了哦)
为表插入数据
为Boats表的Bname字段创建降序普通索引。
create index bnameno(自己取的名字) on boats(bname);
为Sailors的Sname字段创建唯一索引。
create unique index snameno on sailors(sname);
删除Sailors的Sname字段的唯一索引
drop index snameno on sailors;
有很多初学的小伙伴会为这个查询而烦恼,这个烦恼接下来就可以解决了
1.查找定了红色船而没有定绿色船的水手姓名
select distinct s.sname
from sailors s
where s.sid in (select sid from reserves where bid in (select bid from boats where color = 'red'))
and s.sid not in (select sid from reserves where bid in (select bid from boats where color = 'green'));
2.查找定了红色船水手的姓名
select s.sname
from sailors s
where s.sid in (select sid from reserves where bid in (select bid from boats where color = 'red'));
3.将年龄小于30的水手级别+1
update sailors
set rating=rating+1
where age < 30;
4.查找定了103号船的水手
select s.*, r.bid
from sailors s, reserves r
where s.sid = r.sid and r.bid = '103';
1~4的图(我懒得打代码直接上图www)
(这里我把安全性调为了0)