mysql 关联查询一张表的数据另一张表是否存在 mysql 关联查询 子查询 过程

子查询与表连接

一、子查询（嵌套SQL）

select语句是SQL的查询，前面所学的select语句都是简单查询，即从单个数据库表中检索数据的单条语句。
下面看一个实际问题。订单存储在两个表中，对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。 各订单的物品存储在相关的orderitems表中。orders表不存储客户信息，它只存储客户的ID。实际的客户信息存储在customers表中。
假如需要列出订购物品TNT2的所有客户，应该怎样检索？
1.检索包含物品TNT2的所有订单的编号；
2.检索具有前一步骤列出的订单编号的所有客户的ID；
3.检索前一步骤返回的所有客户ID的客户信息。
那么有没有更简单的方法呢？

1.利用子查询进行过滤：

SQL允许创建子查询（subquery），即嵌套在其他查询中的查询。子查询就是在一个SQL当中，它的where条件来源于另外一个SQL。当然也可以反过来理解，就是一个SQL语句的结果，作为外层SQL语句的条件。

select cust_name,cust_contact
from customers
where cust_id in (select cust_id
	from orders
	where order_num IN (select order_num
		from orderitems
		where prod_id = 'TNT2'));

为了执行上述select语句，MySQL实际上必须执行3条select语句。
最里面的子查询返回订单号列表，此列表用于其外面的子查询的where子句。
外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的where子句。
最外层查询返回所需的数据。

2.作为计算字段使用子查询：

使用子查询的另一方法是创建计算字段。
例如：如何获取customers表中每个用户的订单总数呢？
1.从customers表中获取用户列表；
2.分别获取每个用户在orders表中的订单数。
那么如何简化操作呢？
获取每个客户的订单数，对每个客户进行count函数的统计计算，把count()作为一个子查询。

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

orders_num是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询会多次执行。
注意：子查询中的where子句与前面使用的where子句稍有不同，因为它使用了完全限定列名，这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个点分隔）。因为有两个cust_id列，一个在customers中，另一个在orders中，需要比较这两个列从而正确的把订单和顾客相匹配。如果不完全限定列名，MySQL将假定你是对orders表中的cust_id进行自身比较。

二、关系表

1.关系表的意义：

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。
假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。现在，假如有由同一供应商⽣生产的多种物品，那么在何处存储供应商信息呢？
虽然可以用一个表来存储，但是这一个表将出现大量相同的供应商信息。相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值即关系设计中的关系（relational）互相关联。在这个例子中，可建立两个表，一个存储供应商信息，另一个存储产品信息，并用供应商表的主键作为产品信息表的外键来关联两个表，利用供应商ID能从供应商表中找出供应商的详细信息。这样做的好处如下：
供应商信息不重复，从而不浪费时间和空间；
如果供应商信息变动，可以只更新供应商表中的单个记录，相关表中的数据不用改动；
由于数据无重复，显然数据是一致的，这使得处理数据更简单。
关系数据可以有效的存储和方便的处理。因此，关系数据库的可伸缩性远比非关系数据库要好。
在能够有效地使用联结前，必须了解关系表以及关系数据库设计的一些基础知识。

2.相关名词：

表关系：表与表之间的关系。
外键：在一个表中定义一个字段，这个字段中存储的数据是另外一张表中的主键。就是在一个表中的字段，代表着这个数据属于谁。
外键实现的方式有两种：物理外键、逻辑外键。物理外键是在创建表时，就指定这个表中的字段是一个外键，并且强关联某个表中的某个字段需要在定义字段时，使用sql语句来实现；逻辑外键就是在表中创建一个普通的字段，没有强关联关系，需要通过程序逻辑来实现。
一对一：一个表中的数据对应着另外一张表中的一个数据，只能有一个。
一对多：在一个表中的一条数据对应着另外一个表中的多条数据。在一个表中的多条数据，对应着另外一张表中一个数据。
多对多：举个例子，一本书，有多个标签，同时每一个标签下又对应多本书，这就属于多对多。

三、表联结

如果数据存储在多个表中，怎样用单条select语句检索出数据？
使用表联结即可，表联结就是一种查询的机制，用来在一个select语句中关联多个表进行查询，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。
例如：我们需要查询出所有商品以及对应的供应商信息（供应商名称、商品名称、商品价格）。

1.使用where子句联结：

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

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。
这两个表用where子句正确联结，where子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。
在联结两个表时，实际上做的是将第一个表中的每一行与第二个表中的每一行配对。
where子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。
那么，假如没有where条件时会发生什么呢？
如果没有where条件，那么第一个表中的每一行数据会与第二个表中的每一行数据进行匹配，不管逻辑是否可以匹配。由没有联结条件的表关系返回的结果为笛卡儿积，检索出的行的数目将是第一个表的行数乘以第二个表中的行数，因此千万不要忘记where条件。

2.使用join联结：

除了可以使用where进行表的联结查询外，还可以使用另外一种联结方式：join。

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

上面这个SQL就是使用了join的语法，进行了两个表的联结，在on后面去定义了联结的条件，传递给on的实际条件与传递给where的相同，SQL规范首选inner join语法，其中inner可以省略。

3.联结多个表：

SQL对一条select语句句中可以联结的表的数目没有限制。
创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。
例如：查询出订单号为20005的订单中购买的商品及对应的产品供应商信息。

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的语法。

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在运行时关联指定的每个表以处理联结。 这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。

四、自联结

即当前这个表与自己做联结。
假如你发现某物品（其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';

改成where语句：

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

此查询中需要的两个表实际上是相同的表，因此products表在from子句中出现了两次。虽然这是完全合法的，但对products的引用具有二义性，因为MySQL不知道你引用的是products表中的哪个实例。为解决此问题需要使用表别名，products的第一次出现设置别名p1，第二次出现设置别名p2，这些别名就可以当作表名来使用。
另外，子查询（嵌套查询）是目前可明确知道的SQL中运行效率最低的一种方式，因此尽可能不要使用嵌套语句。

五、外部联结

使用where进行关联查询（内部联结）只能对两个表中相关联的数据进行查询，许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。
例如，可能需要使用联结来完成以下工作：对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户。在该例子中，联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。
left join：以left join左侧表为基准，去关联右侧的表进行联结，如果有未关联的数据，那么结果为null。
right join：以right join右侧表为基准，去关联左侧的表进行联结，如果有未关联的数据，那么结果为null。
以用户表为基准，去关联查询订单表数据：

select customers.cust_id,orders.order_num
from customers left join orders
on customers.cust_id = orders.cust_id;

select customers.cust_id,orders.order_num
from orders right join customers
on customers.cust_id = orders.cust_id;

对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户：

select customers.cust_id,count(orders.order_num) as nums
from customers left join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;

注意使用正确的联结条件，否则将返回不正确的数据。
别忘记提供联结条件，否则会得出笛卡儿积。
在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们之前，分别测试每个联结，这样可以使故障排除的更为简单。

六、组合查询union

MySQL允许执行多个查询（多条select语句），并将结果作为单个查询结果集返回。
这些组合查询通常称为并（union）或复合查询（compound query）。

1.UNION规则：

union必须由两条或两条以上的select语句组成，语句之间用关键字union分隔（例如，如果组合4条select语句，将要使用3个union关键字）；
union中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）；
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

2.示例：

假如需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品。
先查询第一个结果：

select vend_id,prod_id,prod_price from products where prod_price <= 5;

再查询第二个结果：

select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);

使用union将两个SQL一并执行：

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

这条语句由前面的两条select语句组成，语句中用union关键字分隔。
union指示MySQL执行两条select语句，并把输出组合成单个查询结果集。
以下是同样结果，使用where的多条件来实现。

select vend_id,prod_id,prod_price from products where prod_price <= 5 or
vend_id in (1001,1002);

在这个简单的例子中，使用union可能比使用where子句更为复杂。
但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用union可能会使处理更简单。

3.注意：

union会从查询结果集中自动去除重复的行，如果想返回所有匹配的行，可以使用union all；
在用union组合查询时，如果需要排序，只能使用一条order by子句，它必须出现在最后一条select语句之后，并且MySQL将用它来排序所有select语句返回的所有结果。

七、关于sql_mode

sql_mode是MySQL数据库中的一个环境变量，定义了mySQL应该支持的SQL语法，数据校验等。

1.查看当前数据库的sql_mode：

select @@sql_mode;

2.修改sql_mode：

1.在当前数据库中进行修改（服务器重启后失效）
set @@sql_mode = ‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
2.修改配置文件my.cnf
sql_mode = ‘xxx’
修改完成后要重启mySQL服务
brew services stop mysql@5.7;

3.sql_mode值的含义：

ONLY_FULL_GROUP_BY：
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的，因为列不在GROUP BY从句中。
关于ONLY_FULL_GROUP_BY是否开启的建议：
1.建议开启，符合SQL标准；
2.在mySQL中有any_value(field)函数，允许返回非分组字段（和关闭only_full_group_by模式相同）。

八、CASE WHEN和IF

用于判断是否满足指定条件并返回不同的值。

1.使用CASE WHEN：

语法格式：
CASE WHEN 条件 THEN 满足条件的返回值 ELSE 不满足条件的返回值 END
例如：

sum(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END)

2.使用IF：

语法格式：
IF(条件,满足条件的返回值,不满足条件的返回值)
例如：

max(IF(grade = ‘primary’,rate,0)) as primary