1.子查询

1.1什么是子查询

  • 子查询的概念
  • 一条select 查询语句的结果, 作为另一条 select 语句的一部分
  • 子查询的特点
  • 子查询必须放在小括号中
  • 子查询一般作为父查询的查询条件使用
  • 子查询常见分类
  • where型 子查询: 将子查询的结果, 作为父查询的比较条件
  • from型 子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用
  • exists型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

1.2 子查询的结果作为查询条件

SELECT 查询字段 FROM 表 WHERE 字段 = （子查询） ;

 1.2.1 通过子查询的方式，查询价格最高的商品信息

# 通过子查询的方式 , 查询价格最高的商品信息



-- 1. 先查询出最高价格



SELECT MAX (price) FROM products;



-- 2. 将最高价格作为条件 , 获取商品信息



SELECT * FROM products WHERE price = ( SELECT MAX (price) FROM products);

1.2.2 查询化妆品分类下的商品名称商品价格

# 查询化妆品分类下的 商品名称 商品价格



-- 先查出化妆品分类的 id



SELECT cid FROM category WHERE cname = ' 化妆品 ' ;



-- 根据分类 id , 去商品表中查询对应的商品信息



SELECT



p. `pname` ,



p. `price`



FROM products p



WHERE p. `category_id` = ( SELECT cid FROM category WHERE cname = ' 化妆品 ' );

1.2.3 查询小于平均价格的商品信息

-- 1. 查询平均价格



SELECT AVG (price) FROM products; -- 1866



-- 2. 查询小于平均价格的商品



SELECT * FROM products



WHERE price < ( SELECT AVG (price) FROM products);

1.3 子查询的结果作为一张表

SELECT 查询字段 FROM （子查询）表别名 WHERE 条件 ;

1.3.1 查询商品中，价格大于500的商品信息，包括商品名称 商品价格 商品所属分类名称

-- 1. 先查询分类表的数据



SELECT * FROM category;



-- 2. 将上面的查询语句 作为一张表使用



SELECT



p. `pname` ,



p. `price` ,



c .cname



FROM products p



-- 子查询作为一张表使用时 要起别名 才能访问表中字段



INNER JOIN ( SELECT * FROM category) c



ON p. `category_id` = c .cid WHERE p. `price` > 500 ;

1.4 子查询结果是单列多行

  • 子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
  • 语法格式

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询） ;

 1.4.1 查询价格小于两千的商品，来自于哪些分类（名称）

# 查询价格小于两千的商品 , 来自于哪些分类 ( 名称 )



-- 先查询价格小于 2000 的商品的 , 分类 ID



SELECT DISTINCT category_id FROM products WHERE price < 2000 ;



-- 在根据分类的 id 信息 , 查询分类名称



-- 报错 : Subquery returns more than 1 row



-- 子查询的结果 大于一行



SELECT * FROM category



WHERE cid = ( SELECT DISTINCT category_id FROM products WHERE price < 2000 );

mysql查询订单总数前三的用户和订单数量 mysql查询价格前三的商品_子查询

  •  使用in函数，in（c002.003）

-- 子查询获取的是单列多行数据



SELECT * FROM category



WHERE cid IN ( SELECT DISTINCT category_id FROM products WHERE price < 2000 );

 1.4.2 查询家电类与鞋服类下面的全部商品信息

# 查询家电类 与 鞋服类下面的全部商品信息



-- 先查询出家电与鞋服类的 分类 ID



SELECT cid FROM category WHERE cname IN ( ' 家电 ' , ' 鞋服 ' );



-- 根据 cid 查询分类下的商品信息



SELECT * FROM products



WHERE category_id IN ( SELECT cid FROM category WHERE cname IN ( ' 家电 ' , ' 鞋服 ' ));

 1.5 子查询总结

 1.子查询如果查出的是一个字段（单列），那就在where后面作为条件使用。

 2.子查询如果查询出最多的多个字段（条例），就当做一张表使用（要起别名）。

 1.6 数据库设计

 16.1  数据库三范式（空间最省）

  • 概念：三范式就是设计数据库的规则.
  • 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据 库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关 系型数据库，必须满足一定的范式
  • 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据 库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关 系型数据库，必须满足一定的范式

1.6.2 第一范式1NF

  • 概念
  • 原子性：做到列不可拆分
  • 第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分 , 如果数据表中每个 字段都是不可再分的最小数据单元，则满足第一范式。

mysql查询订单总数前三的用户和订单数量 mysql查询价格前三的商品_字段_02

 

 1.6.3 第二范式2NF

  • 概念：
  •   在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
  •  一张表只能描述一件事.
  • 示例 :
  • 学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息
  • 如果放在一张表中,会导致数据的冗余,如果删除学员信息, 成绩的信息也被删除了

 

mysql查询订单总数前三的用户和订单数量 mysql查询价格前三的商品_子查询_03

 

1.6.4 第三范式3NF

  • 概念
  • 消除传递依赖
  • 表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放
  • 示例
  • 通过number 与 price字段就可以计算出总金额,不要在表中再做记录(空间最省)

mysql查询订单总数前三的用户和订单数量 mysql查询价格前三的商品_子查询_04

 

 1.6.5 数据库的反三范式

概念：

  • 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
  • 浪费存储空间,节省查询时间 (以空间换时间)

什么是冗余字段？

  • 设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表，且完全等同于它在其本 =来所属表的意义表示，那么这个字段就是一个冗余字段

反三范式示例



  • 两张表，用户表、订单表，用户表中有字段name，而订单表中也存在字段name。

mysql查询订单总数前三的用户和订单数量 mysql查询价格前三的商品_子查询_05

 

  •  使用场景
  • 当需要查询“订单表”所有数据并且只需要“用户表”的name字段时, 没有冗余字段 就需要去join
  • 连接用户表,假设表中数据量非常的大, 那么会这次连接查询就会非常大的消耗系统的性能.
  • 这时候冗余的字段就可以派上用场了, 有冗余字段我们查一张表就可以了.

16.6 总结

  • 创建一个关系型数据库设计，我们有两种选择 1 ，尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致、优雅、让人心 醉。 2 ，合理的加入冗余字段这个润滑剂，减少 join ，让数据库执行性能更高更快。