mysql实训七实训内容 mysql实训4

表名：[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

#创建表及表字段
mysql> create table  boys(
    -> boy_id int not null,
    -> boy varchar(10),
    -> toy_id int);

#查看表结构
mysql> desc boys;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| boy_id | int(11)     | NO   |     | NULL    |       |
| boy    | varchar(10) | YES  |     | NULL    |       |
| toy_id | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

#插入数据
mysql> 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);

#查询表的数据
mysql> select * from 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

#创建表及表字段
mysql> create table toys(
    -> toy_id int primary key not null,
    -> toy varchar(10));
 
#查看表字段   
mysql> desc toys;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| toy_id | int(11)     | NO   | PRI | NULL    |       |
| toy    | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

#插入数据
mysql> insert into toys values
    -> (1,"ToyA"),
    -> (2,"ToyB"),
    -> (3,"ToyC"),
    -> (4,"ToyD"),
    -> (5,"ToyE");

#查询表的数据
mysql> select * from toys;
+--------+------+
| toy_id | toy  |
+--------+------+
|      1 | ToyA |
|      2 | ToyB |
|      3 | ToyC |
|      4 | ToyD |
|      5 | ToyE |
+--------+------+

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

在表boys中，并没有玩具的名称，只有玩具的id，故需要将两个表进行联合，题目已经要求用left join联合，由表可知用toy_id进行连接。
由于题目要求知道每个男孩买的玩具，故需要对男孩进行分类(group by)，为了更好的显示玩具名，可使用group_concat函数将每个男孩买的玩具(toy)拼接到一起

mysql> select boy_id,boy,group_concat(toy separator ',') as toys
    -> from boys left join toys on boys.toy_id=toys.toy_id
    -> group by boy_id;
+--------+----------+-----------+
| boy_id | boy      | toys      |
+--------+----------+-----------+
|      1 | Tony     | ToyC      |
|      2 | Andy     | ToyB      |
|      3 | Frank    | ToyA      |
|      4 | Only     | ToyB,ToyC |
|      5 | Terrance | ToyD      |
+--------+----------+-----------+

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

分析：

• 首先会想到使用in，看哪些人买过“ToyB”和”ToyC”，但是使用in只能查询买过，不能知道是否同时都买了。
• 随后想到能否对买了“ToyB”和”ToyC”的boy_id进行分组，看男孩买过的玩具是否不止一个。如果不止一个的话，则既买过“ToyB”也买过”ToyC”。

步骤：

• 首先使用(select Toy_id from toys where toy in(“ToyB”,“ToyC”))语句得到ToyB”和”ToyC”的toy_id。g使用in得到一个买的玩具只有“ToyB”和”ToyC”的表。
• 再对上一步的表进行分析，对boy_id进行分组，就能得到每个男孩买的玩具情况，再使用count统计boy_id看其是否不止出现一个

mysql>  select boy from boys where toy_id in
    -> (select Toy_id  from  toys where toy in("ToyB","ToyC")) 
    -> group by boy_id having count(boy_id)>1;
+------+
| boy  |
+------+
| Only |
+------+

表名：[饮料信息]

饮料名称	价格	碳水化合物	颜色	加冰	卡路里
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

#创建表及表字段
mysql> create table drink(
    -> 名称 varchar(5),
    -> 价格 decimal(8,2),
    -> 碳水化合物 decimal(8,2),
    -> 颜色 varchar(20),
    -> 加冰 varchar(10),
    -> 卡路里 int);

#查看表结构
mysql> desc drink;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| 名称       | varchar(5)   | YES  |     | NULL    |       |
| 价格       | decimal(8,2) | YES  |     | NULL    |       |
| 碳水化合物 | decimal(8,2) | YES  |     | NULL    |       |
| 颜色       | varchar(20)  | YES  |     | NULL    |       |
| 加冰       | varchar(10)  | YES  |     | NULL    |       |
| 卡路里     | int(11)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

#插入数据
mysql> insert into drink 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);

#查询表的数据
mysql> select * from drink;
+------+------+------------+--------+------+--------+
| 名称 | 价格 | 碳水化合物 | 颜色   | 加冰 | 卡路里 |
+------+------+------------+--------+------+--------+
| A    | 1.00 |       8.40 | Yellow | N    |     33 |
| B    | 2.50 |       3.20 | Blue   | N    |     12 |
| C    | 3.50 |       8.80 | Orange | Y    |     35 |
| D    | 2.50 |       5.40 | Green  | Y    |     24 |
| E    | 5.50 |      42.50 | Purple | Y    |    171 |
+------+------+------------+--------+------+--------+

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

• 需要显示的数据是“名称”，“价格”
• 条件是不加冰(加冰=“N”)，颜色为yellow(颜色=“yellow”)，卡路里大于30(卡路里>30)

mysql> select 名称,价格 from drink where 颜色="yellow" and 加冰="N" and 卡 路里>30;
+------+------+
| 名称 | 价格 |
+------+------+
| A    | 1.00 |
+------+------+

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

• 需要显示的数据是“名称”，“颜色”
• 条件是碳水化合物小于4(碳水化合物<4)，加冰(加冰=“Y”)

mysql> select 名称,颜色 from drink where 碳水化合物<4 or 加冰="Y";
+------+--------+
| 名称 | 颜色   |
+------+--------+
| B    | Blue   |
| C    | Orange |
| D    | Green  |
| E    | Purple |
+------+--------+

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

• 需要显示的数据是“价格总和”，使用sum函数算总和
• 条件是卡路里小于100(卡路里<100)

mysql> select sum(价格) as '价格总和' from drink where 卡路里<100;
+----------+
| 价格总和 |
+----------+
|     9.50 |
+----------+