子查询、联结表、创建高级联结、组合查询

最新推荐文章于 2022-05-30 23:08:10 发布

大妮子噻

最新推荐文章于 2022-05-30 23:08:10 发布

阅读量160

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/bd_nini/article/details/101211267

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

子查询

嵌套在其他查询中的查询

利用子查询过滤

select cust_id 
from orders
where order_num  in (select order_num
                                   from orderitems
                                   where prod_id ='TNT2');

列必须匹配
在where子句中使用子查询，应该保证select语句具有与where子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列

作为计算字段使用子查询

select cust_name,
          cust_state.
          (select count(*)
          from orders
          where order.cust_id =costomers.cust_id) as oreders
 from customers
 oeder by cust_name;

上述举例采用了相关子查询 — 涉及外部查询的子查询
使用了完全限定列名（表名和列名由一个句点分隔）

联结表

理解关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各个表通过某些常用的值互相关联

外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系

优点：
信息不重复，节省时间和空间
变动单表单个信息，相关表数据不用改动数据是高度一致的，处理数据更简单

可伸缩性（scale） 能适应不断增加的工作量而不失败。涉及良好的数据库或应用程序称之为可伸缩性好

联结是一种机制，用来在一条select语句中关联表。使用特殊的语法可以联结多个表返回一组输出

注意：联结不是物理实体，它在实际的数据库中不存在，而是存在于查询执行当中

创建联结

使用了完全限定名
指定的列出现在不同的表中

where子句重要性→ where子句作为过滤条件，它只包含哪些匹配给定条件（这里是联结条件）的行。没有where子句则第一个表的每一行和第二个表每一行配对，而不管逻辑上是否可以匹配

笛卡尔积 由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数据未第一个表的行数与第二个表行数的乘积

select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend=products.vend
order by vend_name,prod_name

内部联结

基于两个表之间的相等测试，这种联结叫做等值联结，又叫内部联结

语法：INNER JOIN ON

select vend_name,prod_name,prod_price
from vendors  inner join products
on vendors.vend_id=products.vend_id;

SQL对一条select语句中可以联结的表的数目没有限制

select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
where products.vend_id=vendors.vend_id
and orderitems.prod_id=products.proid_id
and order_num=2005;

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

高级联结

在联结表时使用表别名、聚集函数以及不同类型的联结

使用表别名

优势：
缩短SQL语句；
允许在单条select语句中多次使用相同的表；

select cust_name ,cust_concat
from customers as c ,orders as o,orderitems as oi 
where c.cust_id =o.cust_id
          and oi.order_num =o.order_num
          and prod_id = 'TNT2';

注意：表别名不仅能用于子句，还可以用于select的列表、order by子句以及语句的其他部分

不同类型的联结

自联结、自然联结 和 外部联结

自联结

在单条select 语句中不知一次引用相同的表

select p1.prod_id,p1.prod_name
from products as p1,products as p2                       #product表出现两次
where p1.vend_id = p2.vend_id                            #联结两个表
          and p2.prod_id = 'DINTR'  ；                     #过滤数据



解析：product表在from子句中出现2次虽然是合法的，但对此表的引用具有第二性，因为MySQL不知道你引用的是表中哪个实例。
因此为了解决次问题，使用了表别名

注意：自联结通常作为外部语句用来替代从相同表中检索数据时使用的字查询语句。结果相同，但有时候处理联结比处理子查询更快

外部联结

联结包含了那些在相关表中没有关联的行，这种类型的联结称为外部联结

语法：OUTER JOIN ON
关键字 :LEFT/RIGHT
在使用OUTER JOIN 语法时，必须使用left/right关键字指定包括其所有行的表（right指出的是outer join右边的表），下面的例子选中的是左边customers表

select customers.cust_id ,orders.order_num
from customers  LEFT  OUTER JOIN orders
ON customers.cust_id=orders.cust_id;

使用聚集函数联结

聚集函数用来汇总数据，不近可以从单个表汇总数据，也可以与联结一起使用（内外联结都可以）

select customers.cust_name,
          customers.cust_id,
          count(order.order_num) as num_ord
 from customers left outer join orders
 group by customers.cust_id;

联结总结

注意所使用的的联结类型。一般使用内部联结，但使用外部联结是有效的
保证使用正确的联结条件，否则将返回不正确的数据
应该总是提供联结条件，否则会得出笛卡尔积
在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型

组合查询

利用UNION 操作符将多条select预计组合成一个结果集
MySQL允许执行多个查询（多条select语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询

使用组合查询情况：

在单个查询中从不同表返回类似结构的数据；
对单个表执行多个查询，按单个查询返回数据；

#组合查询和多个where条件

组合查询
select vend_id,prod_id,proid_price
from products
where prod_price<=5
union
select vend_id,prod_id,proid_price
from products
where vend_id in (1001,1002)


多个where条件
select vend_id,prod_id,proid_price
from products
where prod_price<=5
         or vend_id in(1001,1002)

UNION规则