MySQL(八)子查询和分组查询

时间:2022-06-27 02:42:41

一、子查询

1、子查询(subquery):嵌套在其他查询中的查询。

例如:select user_id from usertable where mobile_no in (select mobile_no from mobile where mobile_id = '10086');

这条SQL语句中,括号内为从mobile表汇总检索mobile_id为10086的所有行中的mobile_no列,括号外为从user_table表中检索mobile_id为10086的所有行中的user_id列;

PS:select语句中,子查询总是从内向外处理(实际上,MySQL执行了2个select操作),where子句中使用子查询,必须保证select语句具有与where子句中相同数目的列;

  子查询一般与in操作符结合使用,但也可用于测试等于(=)、不等于(<>)等。

格式化SQL:包含子查询的select语句一般相较来说阅读和调试更为不方便,特别是它比较复杂的情况下,因此把子查询分解为多行并且适当缩进,能极大的简化子查询的使用。

 

2、使用计算字段使用子查询

例如:select user_name,user_id,(select count(*)) 

     from orders where orders_cust_id = usertable_user_id)

     as orders

     from usertable

     order by user_name;

这条SQL语句对usertable表中每个用户返回3列:user_name,user_id和orders,orders是一个计算字段,由圆括号内的子查询建立,它对检索出的每个用户执行一次,

子查询中where子句它使用了完全限定表名,它告诉SQL比较orders表和usertable表中的user_id列。

相关子查询(correlated subquery):涉及外部查询的子查询(任何时候只要列名可能存在多叉性,就必须使用这种语法[表名和列名有一个句点分隔])。

PS:使用子查询建立查询的最可靠方法是逐渐进行(首先建立最内层的查询,确认后用硬编码数据建立外层查询,由内到外)

 

二、组合查询

MySQL允许执行多个查询(多条select语句),并将结果作为单个查询结果集返回,这些组合查询称为并(union)复合查询(compound query)

以下两种基本情况,需要使用组合查询:

在单个查询中从不同表返回类似结构的数据;

对单个表执行多个查询,按单个查询返回数据;

1、union

union可将多条select语句的结果组合成单个结果集,例子如下

select user_id, mobile_id, mobile_num

from mobiletables

where mobile_num = 10086

union

select user_id, mobile_id, mobile_num

from mobuletables

where user_id in (10000,10010);

这条SQL语句中,union指示MySQL执行两条select语句,并把输出组合成单个查询结果集。

union使用规则:

union必须由两条或以上的select语句组成,语句之间用关键字union分隔;

union中每个查询必须包含相同的列、表达或聚集函数(各个列不需要以相同的次序列出);

列数据类型必须兼容:类型不用完全相同,但必须是DBMS可以隐含的转换类型;

union自动从查询结果集中去除重复的行(这是union的默认行为,如果想返回所有匹配行,可使用union all

 

2、union all

union自动从查询结果集中去除重复的行,如果想返回所有匹配行,可使用union all;例子如下:

select user_id, mobile_id, mobile_num

from mobiletables

where mobile_num = 10086

union all

select user_id, mobile_id, mobile_num

from mobuletables

where user_id in (10000,10010);

union和where的区别:

union几乎可以完成与多个where条件相同的工作。union all为union的一种形式,它完成where子句完成不了的工作(如果需要每个条件匹配行全部出现,则必须使用union all)。

 

3、对组合查询结果排序

select语句的输出用order by子句排序,在用union组合查询时,只能使用一条order by子句,它必须出现在最后一条select语句之后。

select user_id, mobile_id, mobile_num

from mobiletables

where mobile_num = 10086

union all

select user_id, mobile_id, mobile_num

from mobuletables

where user_id in (10000,10010)

order by user_id, mobile_num;

MySQL将用它来排序所有的select语句返回的所有结果。