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);
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;


# 查询预订了至少一条船的水手的编号
# (如果是查询姓名，则使用distinct不使用于有重名的情况)
select distinct s.sid
from saliors s, reserves r
where s.sid = r.sid;


# 模糊查询
select s.sname, s.age, s.age - 5 as 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
	and (b.color = 'red' or b.color = 'green'); 
	# 如果查询预订了红船和绿船的水手，不能简单的将or改为and

select s.sid
from saliors s, boats b, reserves r
where s.sid = r.sid and b.bid = r.bid and b.color = 'red'
union
select s.sid
from saliors s, boats b, reserves r
where s.sid = r.sid and b.bid = r.bid and b.color = '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
	and (b1.color = 'red' and b2.color = '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 = 103 and 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)
);
由于mysql不支持except操作，故用下面的方法替代
*/

select s.sid
from saliors s
where not exists (
	select b.bid from boats b where b.bid
		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
having 1 < (select count(*) from saliors s2 where s2.rating = s.rating);


# 查询每个红船有多少个预订信息
select b.bid, count(*) as scount
from boats b, reserves r
where b.bid = r.bid and b.color = '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
having b.color = 'red';
*/


# 查找平均年龄最小的等级
with temp(rating, avgage) as (
	select s.rating, avg(s.age)
	from saliors s
	group by s.rating)
select temp.rating
from temp
where temp.avgage = (select min(temp.avgage) from temp);