- 实验目的及内容
1、熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用；加深理解表的定义对数 据更新的作用；
2、对已经建立的数据库进行修改、删除、插入等操作；
3、掌握关系数据库的各种查询方法，理解其含义，包括嵌套查询、连接查询等；
4、对关系数据库进行查询，包括嵌套查询、连接查询；
对于建表，有两个问题，一是在建表过程中重复建表，导致出现错误，二是建表是没有标记主键，标记主键时用primary key（）；注意建表顺序，先后顺序是不一样的；查询时应注意对应的表的关键字段，通过这次作业练习查询，让我有了更深的印象。切实的感觉到了自身的提高，此次学习，使自己的理论基础有了比较明显的提高，进一步增强了学习理论的自觉性与坚定性，希望能学习好数据库系统这门科目。
1.建表
代码如下（示例）：
2.插入数据
代码如下（示例）：
INSERT INTO Member VALUES
('M001','符柳欣','女',1983/5/21,18383111234,'陕西西安'),
('M002','许梦凡','女',1967/10/22,18585111234,'北京'),
('M003','陈俊龙','男',1974/4/15,18787111234,'河南郑州'),
('M004','陈燕','女',1986/3/10,18987111234,'四川成都'),
('M005','吴乾柱','男',1978/6/8,19118111234,'湖北武汉'),
('M006','周颖','女',1977/10/26,19383111234,'上海'),
('M007','许梦凡','女',1994/7/8,19585111234,'浙江杭州'),
('M008','刘天州','男',1987/10/9,15051511234,'江苏苏州'),
('M009','林国媚','女',1990/6/16,15151511234,'广东广州'),
('M010','吴乾柱','男',1989/2/24,15252521234,'湖南长沙'),
('M011','刘立','男',1980/5/31,15353531234,'安徽合肥'),
('M012','黄大山','男',1978/4/10,15757111234,'辽宁大连');
INSERT INTO Route VALUES
('DHI01','三亚风情游',7,20000,'日月湾、猴岛、天涯海角、大东海海滩、亚龙湾森林公园，费用包含往返机票，全程五星酒店及正餐费用'),
('DSN01','陕西历史文化游',5,5000.00 ,'大雁塔、钟楼、鼓楼、回民街、兵马俑、骊山、华清池、明城墙等，双卧天晚，四星酒店含早餐'),
('DSN02','陕北红色之旅',4,4000.00 , '西安、洛川、延安、子长、榆林、绥德，枣园旧址，杨家岭旧址，王家坪旧址等'),
('DXJ01','新疆丝路游',15,13000,'天山、高昌古城、吐鲁番、喀纳斯湖、博斯腾湖、魔鬼城等,全程自驾包车'),
('DYN01','七彩云南' ,7,10000.00 ,'昆明、大理、丽江、版纳双飞，纯玩，全程酒店五星带游泳池。费用包含往返机票，不含景点门票费'),
('IAS01','钟爱新马泰',7,18000.00 ,'曼谷大皇宫、芭提雅、云顶高原、独立广场等景点。费用含往返机票、标准四星级酒'),
('IAS02','日韩日游',6, 9000.00 ,'北海道，富士山，首尔、济州岛，釜山。不含机票价格，费用包含全程四星级酒店及正餐费用'),
('IEU01','欧洲周游',14 ,40000.00 ,'包括凡尔赛宫、塞纳河、比萨斜塔、圣彼得大教堂、斗兽场、铁力士雪山、卢塞恩等。费用包含往返机票，全程欧洲-4星级标准酒店及早餐，包含景点门票费用及旅游人身意外险'),
('IEU02','俄罗斯日游',8,12000.00, '包括莫斯科红场、贝加尔湖、圣彼得堡冬宫博物馆、莫斯科克里姆林宫等景点。费用包含往返机票，精选三星酒店'),
('ISN01','北美风情游',14,38000.00,'黄石公园、渔人码头、拉斯维加斯、好莱坞、大峡谷国家森林公园、尼亚加拉大瀑布、夏威夷群岛等。费用含酒店住宿、正餐费用，不含往返机票');
INSERT INTO Plans VALUES
('P00001','IEU01','2021-01-10',0.85,40),
('P00002','IEU02','2021-01-15',1,30),
('P00003','ISN01','2021-01-10',0.95,18),
('P00004','ISN01','2021-01-20',0.88,20),
('P00005','IAS01','2021-01-28',0.95,45),
('P00006','IEU01','2021-03-10',0.98,30),
('P00007','DSN01','2021-04-01',1,45),
('P00008','DSN02','2021-06-10',0.75,60),
('P00009','DSN02','2021-07-01',1,50),
('P00010','DXJ01','2021-07-01',0.9,35),
('P00011','IAS02','2021-07-05',0.75,60),
('P00012','DXJ01','2021-08-01',1,25),
('P00013','DYN01','2021-08-01',1,30),
('P00014','DSN01','2021-08-02',1,50),
('P00015','DYN01','2021-09-01',0.9,40),
('P00016','DXJ01','2021-09-10',0.85,20),
('P00017','DYN01','2021-10-03',0.75,15),
('P00018','DSN01','2021-10-08',0.8,40),
('P00019','DHI01','2021-11-10',0.9,30),
('P00020','IEU02','2021-11-11',0.95,40),
('P00021','DHI01','2021-12-20',0.8,20),
('P00022','IAS01','2021-12-28',0.9,25),
('P00023','IAS02','2021-12-28',0.65,50);
INSERT INTO Booking VALUES
('M001','P00001',2),
('M001','P00019',2),
('M002','P00003',6),
('M002','P00021',4),
('M003','P00005',3),
('M004','P00004',5),
('M004','P00016',5),
('M005','P00004',7),
('M006','P00011',4),
('M007','P00013',4),
('M008','P00003',6),
('M008','P00007',6),
('M008','P00016',6),
('M009','P00003',6),
('M010','P00007',6),
('M010','P00020',6),
('M011','P00021',3),
('M012','P00004',4);
3.查询
代码如下（示例）：
--4.3.1单表查询
--1.查询所有旅游线路的名称和天数及价格
select Rname,Rday,Rprice
from Route
--2.查询全体会员的姓名及其年龄
select Mname,YEAR(GETDATE())-YEAR (Mbirth) AS 'Mage'
from Member
--3.消除取值重复的行
select distinct Mno
from Booking
--4.查询旅行天数为8天的线路信息
select *
from Route
where Rday=8
--5.查询价格在10000到20000之间的线路信息，并按价格升序排序
select *
from Route
where Rprice BETWEEN 10000 AND 20000
order by Rprice ASC
--6.查询报名旅游计划编号为P00001、P00002、P00003的会员编号
select distinct Mno
from Booking
where Pno in('P00001','P00002','P00003')
--7.查询所有欧洲旅行线路的名称，报价和线路介绍
select Rname,Rprice,Rdetails
from Route
where SUBSTRING(Rno,2,2)='EU'
--8.查询所有刘姓会员的姓名、性别和电话
select Mname,Msex,Mtel
from Member
where Mname like '刘%'
--9.查询报名了两项及以上旅行计划的会员编号及项数
select Mno,COUNT(Pno)
from Booking
GROUP BY Mno
HAVING COUNT(*)>=2
--4.3.2连接查询
--1.查询报旅游团的会员详细信息
select distinct Booking.Mno,Member.Mname,Member.Msex,
Member.Mbirth,Member.Mtel,Member.Maddress
from Booking,Member
where Booking.Mno=Member.Mno
--2.查询所有开团计划编号、名称、开始日期、折后价格并按价格升序显示
select Plans.Pno,Route.Rname,Plans.Pstart,Route.Rprice*Plans.Pdiscount
from Plans,Route
where Route.Rno=Plans.Rno
order by Pdiscount ASC
--3.查询每个会员的编号、姓名、所报旅行路线名称及开团日期
select Booking.Mno,Member.Mname,Route.Rname
from Booking,Member,Route,Plans
where Booking.Mno=Member.Mno
and Plans.Pno=Booking.Pno
and Plans.Rno=Route.Rno
--4.查询姓名为“符柳欣”的会员报名的旅行线路总价
select SUM(Rprice) as '总价'
from Route,Member,Booking,Plans
where Member.Mname='符柳欣'
and Booking.Mno=Member.Mno
and Booking.Pno=Plans.Pno
and Plans.Rno=Route.Rno
--4.3.3嵌套查询
--1.查询与"M002"报名相同旅行计划的会员编号、旅行安排代码及报名人数
select Mno,Pno,Bnum
from Booking
where Pno in(
select Pno
from Booking
where Mno='M002'
)
--2.查询报了“三亚风情游”的会员编号，姓名和电话
select Member.Mno,Member.Mname,Member.Mtel
from Member,Booking
where Member.Mno=Booking.Mno
and Booking.Pno in(
select Pno
from Plans
where Rno in(
select Rno
from Route
where Rname='三亚风情游'
)
)
--3.查询所有选择了“P00003”的会员姓名
select Member.Mname
from Member
where exists(
select *
from Booking
where Booking.Mno=Member.Mno
and Booking.Pno='P00003'
)
--4.查询报名人数比选择“P00004”报名人数少的会员编号、旅行安排代码、报名人数
select distinct Mno,Pno,Bnum
from Booking
where Bnum< all(
select Bnum
from Booking
where Pno='P00004'
)
--5.用聚集函数查询报名人数比选择“P00004”的任意报名人数少的会员编号、旅行计划代码、报名人数
select distinct Mno,Pno,Bnum
from Booking
where Bnum<(
select MIN(Bnum)
from Booking
where Pno='P00004'
)
--4.3.4集合查询
--1.查询报名“P00003”或者“P00004”的会员编号、计划编号和报名人数
select *
from Booking
where Pno='P00003'
union
select *
from Booking
where Pno='P00004'
--2.查询即报名“P00003”又“P00004”的会员编号
select Mno
from Booking
where Pno='P00003'
intersect
select Mno
from Booking
where Pno='P00004'
--3.查询报名“P00004”与报名人数不大于5的会员的差集
select Mno
from Booking
where Pno='P00004'
except
select Mno
from Booking
where Bnum<5
--基于派生表的查询
--1.查询目前各个团编号、名称、报名总人数、以及空缺名额数
select Sum_Plan.Pno as 开团编号,Route.Rname as 线路名称,
Plans.Pstart as 开团日期,Sum_Plan.Sum_Renshu as 已报名人数,
Plans.Ptop-Sum_Plan.Sum_Renshu as 空缺名额
from Route,Plans,(Select Pno,Sum(Bnum)
from Booking
group by Pno) as Sum_Plan(Pno,Sum_Renshu)
where Route.Rno=Plans.Rno
and Plans.Pno=Sum_Plan.Pno
4.查询结果
注：如有错误，请大家指出改正，看到点个赞或者收藏都是对我莫大的鼓励~谢谢，希望大家学习愉快！
4038
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
