[数据库萌新入门] 第二章表联结查询

最新推荐文章于 2024-09-13 22:15:00 发布

predawnlove

最新推荐文章于 2024-09-13 22:15:00 发布

阅读量208

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/predawnlove/article/details/107702401

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一、子查询：

把一个sql语句的结果，作为外层sql语句的条件。

作为计算字段使用子查询

# 获取每个用户的订单总数

# 如果分开查询我们需要像下面这样，根据第一行的结果，手动重复第二行sql，

# 相当于对第一行sql的每一行结果，都执行一遍第二行sql

select cust_id,cust_name from customers;
select count(*) as orders_num from orders where cust_id = 10001;

# 把上述两段sql，合成成下面这条sql

# 复杂度的话，相当于对主查询语句中的每一行，都执行一遍子查询。

select cust_id,cust_name,
       (select count(*) from orders where orders.cust_id = customers.cust_id) as orders_num
from customers;

二、表关系：

外键：

在一个表中,定义一个字段,这个字段中存储的数据是另外一张表中的主键

就是在一个表中的字段,代表着这行数据属于谁。

了解:外键实现的方式

1、物理外键 2、逻辑外键

物理外键:就是在创建表时,就指定这个表中的字段是一个外键,并且强关联某个表中的某个字段。

需要在定义字段时,使用sql语句来实现。（不推荐，并发时可能降低效率）

逻辑外键:就是在表中创建一个普通的字段,没有强关联关系,需要通过程序逻辑来实现。

一对一：

在一个表中的数据，只能对应着另外一张表中的一个数据。（比如，另一个表中有个外键，是唯一的）

一对多：

在一个表中的一行数据，对应着另一个表中的多行数据。（比如，另一个表中有个外键，是重复多次的）

在一个表中的多行数据，对应着另一个表中的一行数据。

多对多：

例如：一本书,有多个标签,同时每一个标签下又对应多本书

是两张表，互相，一行数据对对面多行数据。

三、表联结

一个示例：

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

上面的语句，用 where 限定了我们要查询的三个字段之间的逻辑。使之成表。

假如没有where条件时会发生什么呢?

select vend_name, prod_name, prod_price from vendors, products;

如果没有where条件,那么第一个表中的每一行数据会与第二个表中的每一行数据进行匹配,不管逻辑是否可以匹配

如果没有where条件,那么这种结果称为笛卡尔积 ,结果数量为第一个表的行数乘以第二个表中的行数。

所以千万不能忘记where条件

另一种方式是使用 join 关键字，在 on 后面定义联结条件。

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

实例：查询订单号为20005的订单中购买的商品及商品对应的供应商信息。

第一步：先写出需要得到的信息，及他们所在的表

select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors

第二步：根据我们要的字段，补充表与表之间的逻辑关系，（表1 中特定字段a 和表2 中特定字段b 的关系）

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

完成。

上面的语句改写成 join 如下：（单独 join 默认为 inner join）

select prod_name,vend_name,prod_price,quantity
from orderitems 
join products on orderitems.prod_id = products.prod_id
join vendors on products.vend_id = vendors.vend_id
where order_num = 20005;

自联结：

假如你发现某物品(其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,p1.prod_name
from products as p1
join products as p2 on p1.vend_id = p2.vend_id
where p2.prod_id = 'DTNTR';

或者是：

select p1.prod_id,p1.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';

为什么上面我们全选 p1 表的数据呢？

-- 深入理解一下表联结：

# 第一步


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;

# 第二步

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;

# 第三步

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';

第三步的运行结果如下：

左边三列是 p1 表，右边三列是 p2 表。

以上三步逻辑为： p1 表和 p2 表先进行笛卡儿积，得到一张超大表，

然后在超大表中，取出两边 vend_id 相等的行，得到一张中等大小的表

再进一步从中取出行中 p2 的 prod_id 为 DTNTR 的数据。所以根据这个表，我们要取的列均在 p1 中。

DTNTR   Detonator   1003   DTNTR   Detonator   1003
FB   Bird seed   1003   DTNTR   Detonator   1003
FC   Carrots   1003   DTNTR   Detonator   1003
SAFE   Safe   1003   DTNTR   Detonator   1003
SLING   Sling   1003   DTNTR   Detonator   1003
TNT1   TNT (1 stick)   1003   DTNTR   Detonator   1003
TNT2   TNT (5 sticks)   1003   DTNTR   Detonator   1003

或者这样思考： p2 表中，因为我们限定要 p2.prod_id = 'DTNTR'，所以 p2 表的数据固定为 DTNTR 了。那肯定不能从 p2 拿数据，否则必然拿到的是重复数据。

注意：

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