Mysql-SQL查询（⽔⼿预订船案例）学习视频：东南⼤学-徐⽴臻-数据库原理
drop database
if exists navigator;
# 创建数据库
create schema navigator;
use navigator;
# ⽔⼿表
create table saliors (
sid int PRIMARY KEY,
sname VARCHAR(20),
rating int,概览是什么意思
age float
);
# 船表
create table boats (
bid int PRIMARY KEY,
bname VARCHAR(20),
color VARCHAR(20)
);
# 预订情况表
create table reserves (
sid int,
bid int,
day date,
primary key(sid,day),
foreign key(sid)references saliors(sid),
foreign key(bid)references boats(bid)
);
#desc saliors;
#desc boats;
#desc reserves;
#插⼊
insert into saliors values(22,'dustin',7,45);
insert into saliors values(29,'brutus',1,33);
insert into saliors values(31,'lubber',8,55.5);
insert into saliors values(32,'andy',8,25.5);
insert into saliors values(58,'rusty',10,35);
函数concatenate怎么合并日期insert into saliors values(64,'horatio',7,35);
insert into saliors values(74,'horatio',9,35);
insert into saliors values(95,'bob',3,63.5);
insert into saliors values(96,'frodo',3,25.5);
insert into boats values(101,'tiger','red');
insert into boats values(103,'lion','green');
insert into boats values(105,'hero','blue');
insert into reserves values(22,101,'96-10-10');
insert into reserves values(22,103,'96-10-12');
insert into reserves values(58,103,'96-11-12');
# 查询预订了编号为103的船的⽔⼿的姓名(表连接)
select s.sname
from saliors s, reserves r
where s.sid = r.sid and r.bid =103;
where s.sid = r.sid and r.bid =103;
# 查询预订了⾄少⼀条船的⽔⼿的编号
# (如果是查询姓名，则使⽤distinct不使⽤于有重名的情况)
select distinct s.sid
from saliors s, reserves r
where s.sid = r.sid;
# 模糊查询
select s.sname, s.age, s.age -5as age1
from saliors s
where s.sname like'b_%b';
# 查询预订了红⾊船或者绿⾊船的⽔⼿编号
select distinct s.sid
from saliors s, boats b, reserves r
where s.sid = r.sid and b.bid = r.bid
lor ='red'lor ='green');
# 如果查询预订了红船和绿船的⽔⼿，不能简单的将or改为and
select s.sid
from saliors s, boats b, reserves r
where s.sid = r.sid and b.bid = r.bid lor ='red'
union
select s.sid
from saliors s, boats b, reserves r
where s.sid = r.sid and b.bid = r.bid lor ='green';
# 查询预订了红⾊船和绿⾊船的⽔⼿姓名(⾃连接)
select distinct s.sname
from saliors s, boats b1, reserves r1, boats b2, reserves r2
where s.sid = r1.sid and r1.bid = b1.bid and s.sid = r2.sid and r2.bid = b2.bid lor ='red'lor ='green');
# 查询预订了编号为103的船的⽔⼿的姓名(⾮关联嵌套⼦查询)
select s.sname
from saliors s
系统模拟软件
where s.sid in(select r.sid from reserves r where r.bid =103);
# 查询预订了编号为103的船的⽔⼿的姓名(关联嵌套⼦查询)
# 相⽐于⾮关联嵌套⼦查询，效率更低
select s.sname
from saliors s
where exists(select*from reserves r where r.bid =103and r.sid = s.sid);
# 查只被⼀个⽔⼿预订过的船的编号
select r1.bid
from reserves r1
where r1.bid not in(select r2.bid from reserves r2 where r1.sid <> r2.sid);
# 查⽐任意⼀个名叫horatio的⽔⼿级别⾼的所有⽔⼿
select*
from saliors s
where s.rating >any(select s2.rating from saliors s2 where s2.sname ='horatio');
# 查询预订了所有船的⽔⼿(sql除法)
# 所有的船减去⽔⼿预订过的船船，如果结果不存在，则该⽔⼿预订了所有船
/*
select s.sid
from saliors s
where not exists (
(select b.bid from boats)
except
(select r.bid from reserves r where r.sid = s.sid)
(select r.bid from reserves r where r.sid = s.sid)
);
由于mysql不⽀持except操作，故⽤下⾯的⽅法替代
*/
select s.sid
from saliors s
where not exists(
select b.bid from boats b where b.bid
游戏机switch是什么
not in
(select r.bid from reserves r where r.sid = s.sid)
);
# 不使⽤except，双重否定
#
select s.sname
from saliors s
where not exists
(select b.bid from boats b where not exists
(select r.bid from reserves r where r.bid = b.bid and r.sid = s.sid)); # 使⽤聚集函数的例⼦
select count(*)
from saliors s;
select avg(s.age)
from saliors s;
select s.sname
from saliors s
where s.rating =(select max(s2.rating)from saliors s2);
# 分组聚集
select s.rating,min(s.age)as migage
from saliors s
where s.age >=18
group by s.rating
having count(*)>1;
select s.rating,min(s.age)as migage
from saliors s
where s.age >=18
group by s.rating
having1<(select count(*)from saliors s2 where s2.rating = s.rating); # 查询每个红船有多少个预订信息
select b.bid,count(*)as scount
from boats b, reserves r
mysql面试题sqlwhere b.bid = r.bid lor ='red'
group by b.bid;
/*注意下⾯这种查询⽅法是错误的
select b.bid, count(*) as scount
from boats b, reserves r
where b.bid = r.bid
group by b.bid
lor = 'red';
*/
# 查平均年龄最⼩的等级
with temp(rating, avgage)as(
select s.rating,avg(s.age)
from saliors s
group by s.rating)
chatgpt在哪select temp.rating
from temp
where temp.avgage =(select min(temp.avgage)from temp);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。