Mysql小练习

表名：[boys]

Boy_id	Boy	Toy_id
1	Tony	3
2	Andy	2
3	Frank	1
4	Only	2
4	Only	3
5	Terrance	4
5	Terrance	6

表：[toys]

Toy_id	Toy
1	ToyA
2	ToyB
3	ToyC
4	ToyD
5	ToyE

create table boys
(Boy_id tinyint,
Boy char(11),
Toy_id tinyint)
default charset utf8mb4;
​
create table toys
(Toy_id int not null primary key auto_increment,
Toy char(4))
default charset utf8mb4;
​
insert into boys
values
(1, 'Tony', 3),
(2, 'Andy', 2),
(3, 'Frank', 1),
(4, 'Only', 2),
(4, 'Only', 3),
(5, 'Terrance', 4),
(5, 'Terrance', 6);
​
insert into toys(Toy)
values
('ToyA'),
('ToyB'),
('ToyC'),
('ToyD'),
('ToyE');

\1. 请用left join写出查询语句，找出每个男孩买了哪个玩具，并写出输出结果集

select boys.Boy, toys.Toy from boys left join toys on boys.Toy_id = toys.Toy_id where boys.Toy_id = toys.Toy_id order by boys.Boy_id;

\2. 找出既买过“ToyB”也买过”ToyC”的男孩

select Boy from boys where Toy_id in (select Toy_id from toys where Toy in ('ToyB', 'ToyC')) group by Boy having count(Toy_id) > 1;

表名：[饮料信息]

饮料名称	价格	碳水化合物	颜色	加冰	卡路里
A	1	8.4	Yellow	N	33
B	2.5	3.2	Blue	N	12
C	3.5	8.8	Orange	Y	35
D	2.5	5.4	Green	Y	24
E	5.5	42.5	Purple	Y	171

create table drink_info
(饮料名称 char(11),
价格 float,
碳水化合物 float,
颜色 char(11),
加冰 char(1),
卡路里 int(4))
default charset utf8mb4;
​
insert into drink_info
values
('A', 1, 8.4, 'Yellow', 'N', 33),
('B', 2.5, 3.2, 'Blue', 'N', 12),
('C', 3.5, 8.8, 'Orange', 'Y', 35),
('D', 2.5, 5.4, 'Green', 'Y', 24),
('E', 5.5, 42.5, 'Purple', 'Y', 171);

\1. 列出加冰，且颜色为yellow，且卡路里大于30的饮料名称和价格

select 饮料名称, 价格 from drink_info where 颜色 = 'yellow' and 卡路里 > 30;

\2. 列出碳水化合物小于4，或者加冰的饮料名称和颜色

select 饮料名称, 颜色 from drink_info where 碳水化合物 < 4 or 加冰 = 'Y';

\3. 我想买所有卡路里小于100的饮料各一杯，需要多少钱

select sum(价格) as money from(select 价格 from drink_info where 卡路里 < 100) as e;