子查询与表连接

子查询与表连接

子查询（嵌套sql）

关系表

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。
关系表的设计就是要保证把信息分解成多个表，一类数据一个表。
各表通过某些常用值（即关系设计中的关系（relational））相互关联。
外键：
在一个表中，定义一个字段，这个字段中存储的数据是另外一张表中的主键就是在一个表中的字段，代表着这个数据属于谁。
了解：
外键实现的方式，有两种：物理外键、逻辑外键
1、物理外键：
就是在创建表时，就指定这个表中的字段是一个外键，并且强关联某个表中的某个字段，需要再定义字段时，使用sql语句来实现。
2、逻辑外键：（推荐）
就是在表中创建一个普通的字段，没有强关联关系，需要通过程序逻辑来实现。

1、一对一关系

就是在一个表中的数据，对应着另外一张表中的一个数据，只能有一个。

 员工表：
        id，姓名、性别、年龄、籍贯、联系方式、学历、工龄、、、
    由上面的一个表，拆分成两个表
    员工表：
        id，姓名、联系方式、工龄、
        12  张三  1010    3
        13  李四  1020    2

    详情表：
       yid 性别、籍贯、学历
        12    男    山东 本科
        13    男    山西 本科

    上面的表关系就是一对一的表关系，通过详情表中的yid这个字段来标记员工表中的主键。
    一个员工有着一个对应的详情信息，存储在详情表中，
    在详情表中的数据，也只属于某一个员工。

2、一对多关系（使用频率最高）

在一个表中的一条数据对应着另外一个表中的多条数据。
（在一个表中的多条数据，对应着另外一张表中一个数据）

例子：
商品分类
     id 分类名
     1  手机
     2  电脑

 商品
     id 所属分类id，商品名
     1       1         小米手机
     2       1         华为手机

3、多对多关系

 例如一本书，有多个标签，同时每一个标签下又对应多本书
 books 图书
 id  name          author
 1   <跟川哥学编程>    川哥
 2   <跟川哥学数据分析> 川哥
 3   <川哥讲法律故事>   川哥

 tags 标签
 id   name
 1    编程
 2    计算机
 3    互联网
 4    法律
 5    文学

 从图书角度看，一本书有多个标签
 1   <跟川哥学编程>    川哥   ， 编程、计算机、互联网
 2   <跟川哥学数据分析> 川哥  ，  互联网、计算机
 3   <川哥讲法律故事>   川哥 ，  法律

 换一个角度，从标签这个角度看，一个标签包含多个图书
 计算机， <跟川哥学编程>， <跟川哥学数据分析>

表联结

是一种查询的机制，用来在一个select语句中关联多个表进行查询，这种情况称为联结。

两个表相联结

需要查询出所有商品以及对应的供应商信息？
供应商名称，商品名称，商品价格
select vend_name,prod_name,prod_price
from vendors,products（限定表格）
where vendors.vend_id  = products.vend_id（指定两个表中的列名相匹配）
order by vend_name（按照名字排序）

假如没有where条件时会发生什么呢？
如果没有where条件，那么第一个表中的每一行数据会与第二个表中的每一行数据进行匹配，不管逻辑是否可以匹配。
如果没有where条件，那么这种结果称为笛卡尔积，第一个表的行数乘以第二个表中的行数。
所以千万不要忘记where条件！！！
另一种联结方式：join

select vend_name,prod_name,prod_price
from vendors
(inner)可省略 join products on vendors.vend_id = products.vend_id;

以上sql就是使用了 join 的语法，进行了两个表的联结，在 on 后面 去定义了 联结的条件。

联结多个表

案例： 查询出订单号为20005的订单中购买的商品及对应的产品供应商信息
select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
    where products.vend_id = vendors.vend_id（在where语句中说清联结关系）
    and orderitems.prod_id = products.prod_id
    and order_num = 20005;
改写为 join 的语法
select prod_name,vend_name,prod_price,quantity
from orderitems
inner join products on orderitems.prod_id = products.prod_id
inner join vendors on products.vend_id = vendors.vend_id
where order_num = 20005;

注：
MySQL在运行时关联指定的每个表以处理联结，这种处理是非常耗费资源的，因此应该自习，不要联结不必要的表，联结的表越多，性能下降越厉害。

自联结

自联结:当前这个表与自己这个表做联结（join）

例子：假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。因此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。
-- 使用子查询（嵌套查询）
select prod_id,prod_name
from products
where vend_id = (select vend_id from products where prod_id = 'DTNTR');
-- 使用 自联结方式查询
select p1.prod_id,p2.prod_name
from products as p1
join products as p2 on p1.vend_id = p2.vend_id
where p2.prod_id = 'DTNTR';
-- 改成where语句
select p1.prod_id,p2.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';
-- 深入了解 join
select
p1.prod_id,p1.prod_name,p1.vend_id,
p2.prod_id,p2.prod_name,p2.vend_id
from products as p1,products as p2
where p1.vend_id = p2.vend_id
and p2.prod_id = 'DTNTR';

子查询（嵌套查询）是目前可明确知道的 sql中运行效率最低的一种方式，尽可能不使用嵌套语句。

外部联结

许多联结将一个表的行与另一个表中的行相关联，当时有时候需要包含没有关联行的那些行
left join ：是以 left join 左侧表为基准，去关联右侧的表进行联结，如果有未关联的数据，那么结果为null。
right join ：是以 right join 右侧表为基准，去关联左侧的表进行联结，如果有未关联的数据，那么结果为null。

总结表联结

内部联结：where，inner join
自联结：在一个sql中，用当前这个表，联结自己
外部联结：left join，right join

组合查询UNION

UNION规则：

UNION必须由两条或者两条以上的SELECT语句组成，语句之间用关键字UNION分隔（如果组合4条SELECT语句，将要使用3个UNION关键字）
UNION中的每个查询必须包含相同的列，表达式或聚集函数（不过各个列不需要以相同的次序列出）
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含的转换的类型（例如：不同的数值类型或者不同的日期类型）
注意
使用UNION时，默认删除重复的行，如果想返回所有的匹配行，可以使用UNION ALL
对组合查询结果排序时：只能使用一条ORDER BY子句！对于结果集，只能存在一种排序方式。

例如：
select vend_id,prod_id,prod_price from products where prod_price <= 5
union
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002)
order by prod_price;

Vinci-Ma

MySQL高阶——表联结查询