实验六 多表查询

一、实验目的
1、掌握多表操作的数据操作等相关命令
2、掌握几种子查询形式和方法。
二、实验内容

1. 查询id=6的分类所具有的属性信息，将属性按照层级并排显示。
2. 查询id=5的商品的所有属性信息，将属性名称和属性值并排显示。
3. 查询id=1的属性的所有子属性信息。
4. 查询拥有属性值个数大于1的商品id和名称。
5. 查询属性不少于两个的商品信息（编号和名称）。

三、实验步骤和过程记录

1、向表sh_goods_attr和表sh_goods_attr_values中插入数据。

sh_goods_attr表：

1. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (1, 0, 6, '基本信息', 0);
2. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (2, 1, 6, '机身颜色', 0);
3. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (3, 1, 6, '输入方式', 1);
4. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (4, 1, 6, '操作系统', 2);
5. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (5, 0, 6, '屏幕', 1);
6. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (6, 5, 6, '屏幕尺寸', 0);
7. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (7, 5, 6, '屏幕材质', 1);
8. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (8, 5, 6, '分辨率', 2);
9. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (9, 0, 6, '摄像头', 2);
10. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (10, 9, 6, '前置摄像头', 0);
11. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (11, 9, 6, '后置摄像头', 1);
12. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (12, 0, 6, '电池信息', 3);
13. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (13, 12, 6, '电池容量', 0);
14. INSERT INTO `sh_goods_attr`(`id`, `parent_id`, `category_id`, `name`, `sort`) VALUES (14, 12, 6, '是否可拆卸', 1);

sh_goods_attr_values表：

1. INSERT INTO `sh_goods_attr_value`(`id`, `goods_id`, `attr_id`, `attr_value`) VALUES (1, 5, 2, '黑色');
2. INSERT INTO `sh_goods_attr_value`(`id`, `goods_id`, `attr_id`, `attr_value`) VALUES (2, 5, 3, '触摸屏');
3. INSERT INTO `sh_goods_attr_value`(`id`, `goods_id`, `attr_id`, `attr_value`) VALUES (3, 5, 4, 'Android');
4. INSERT INTO `sh_goods_attr_value`(`id`, `goods_id`, `attr_id`, `attr_value`) VALUES (4, 5, 6, '5.5寸');
5. INSERT INTO `sh_goods_attr_value`(`id`, `goods_id`, `attr_id`, `attr_value`) VALUES (5, 5, 7, 'IPS');
6. INSERT INTO `sh_goods_attr_value`(`id`, `goods_id`, `attr_id`, `attr_value`) VALUES (6, 5, 8, '1920*1080');
7. INSERT INTO `sh_goods_attr_value`(`id`, `goods_id`, `attr_id`, `attr_value`) VALUES (7, 5, 10, '1600万');
8. INSERT INTO `sh_goods_attr_value`(`id`, `goods_id`, `attr_id`, `attr_value`) VALUES (8, 5, 11, '800万');
9. INSERT INTO `sh_goods_attr_value`(`id`, `goods_id`, `attr_id`, `attr_value`) VALUES (9, 5, 13, '3500mAh');
10. INSERT INTO `sh_goods_attr_value`(`id`, `goods_id`, `attr_id`, `attr_value`) VALUES (10, 5, 14, '否');

1. 根据要求进行查询：

（1）查询id=6的分类所具有的属性信息，将属性按照层级并排显示。

1. Select a.sort sort1,a.name name1,b.sort sort2,b.name name2 From sh_goods_attr a Join sh_goods_attr b On a.id=b.parent_id Where a.category_id=6 Order By a.sort Asc,b.sort Asc;

（2）查询id=5的商品的所有属性信息，将属性名称和属性值并排显示。

1. Select b.name,a.attr_value From sh_goods_attr_value a Join sh_goods_attr b On a.attr_id=b.id Where a.goods_id=5;

（3）查询id=1的属性的所有子属性信息。

1. Select c.sort sort1,c.name name1,b.sort sort2,b.name name2,attr_value From sh_goods_attr_value a Join sh_goods_attr b On a.attr_id=b.id Join sh_goods_attr c On b.parent_id=c.id Where a.goods_id=5 Order By c.sort ASC,b.sort ASC;

（4）查询拥有属性值个数大于1的商品id和名称。

1. Select attr_value From sh_goods_attr_value Where attr_id In (Select id From sh_goods_attr Where parent_id=1);

（5）查询属性不少于两个的商品信息（编号和名称）。

1. Select id,name From sh_goods Where id In (Select goods_id From sh_goods_attr_value Group By goods_id Having Count(id)>1);

  

• 问题思考与总结

1. 本次实验使用了几种子查询？

两种，标量子查询和列子查询。

1. 各有什么特点？

①标量子查询：利用比较运算符判断子查询语句返回的数据是否与指定的条件相等或者不等。WHERE 条件判断{= | < >}

②列子查询：利用比较运算函数IN( )或者NOT IN( )判断指定条件是否在子查询语句返回的结果集中。WHERE 条件判断 {IN | NOT IN}

1. 实验遇到的问题记录和分析如下。

①按子表出现位置，子查询的方式可以划分为WHERE子查询和FROM子查询。其中，标量子查询、列子查询和行子查询都属于WHERE子查询，表子查询属于FROM子查询。

②WHERE EXISTS(子查询语句)；//子查询返回的结果只有0和1两个值。0代表不成立，1代表成立。

③WHERE 表达式 比较运算符 ANY(子查询语句)；//给定的判定条件只要符合ANY语句中的任意一个就返回1

④WHERE 表达式 比较运算符 ALL(子查询语句)；//给定的判定条件只有ALL语句
中的所有都符合才返回1

遇到的问题：在查询的过程中会遇到各种各样的关键字，有一些关键字的用法非常相似但是又有着差异，需要多练习熟悉每一个关键字的具体用法，比如WHERE和HAVING后面都加条件表达式用做查询的判别条件，但是WHERE在所有查询中都可以使用，而HAVING用于分组之后。此外，要理清查询语句中的条件部分和查询部分，根据要求写出查询语句。