Mysql连接查询

连接查询

基本概念：

就是将两个或两个以上的表，“连接起来”，当做一个数据源，并从中去取得所需要的数据；

 

到底怎么连接？

将每一个表的每一行数据两两之间相互对接起来，每次对接的结果都是连接结果的“一行”数据；

示例：

先看原始数据：

然后，将他们进行“连接”：

他们其实这样这样的方式连接起来的结果数据：

这种没有条件的连接，也可以写成这样：

select * from  表1， 表2；

select * from  表1  join 表2；

select * from  表1  cross  join  表2；

其实他们都叫做“交叉连接”；其实i就是没有条件的连接——全部连接起来了；

连接基本形式：

在代码级别，连接的基本形式为：

表1  【连接形式】 join  表2  【on  连接条件】；

 

如果是3个表，则进一步扩展为：

表1  【连接形式】 join  表2  【on  连接条件】 【连接形式】 join  表3  【on  连接条件】

更多表，依次类推；

 

连接的分类

主要分为以下几种：

 

交叉连接 cross  join：

其实就是刚才讲连接的基本概念的时候的连接形式（结果）——它没有条件，只是按连接的基本概念，将所有数据行都连接起来的结果。它又叫做“笛卡尔积”；

对于表1（n1个字段，n2行），表2,（m1个字段，m2行），他们交叉连接的结果是：

有n1+m1个列；

有n2*m2个行；

形式有：

select * from  表1， 表2；

select * from  表1  join 表2；

select * from  表1  cross  join  表2；

内连接  inner  join：

形式：

select  *  from  表1  【inner】  join  表2  on  连接条件；

 

先来研究没有条件的“内连接”（其实就是交叉连接）的结果：

 

 

结果：

其中框框内的“数据行”，是正确的，有意义的数据。而其他行就是错误的数据。

这些有意义的数据的一个特点，就是，所标示出的两个字段的值相等：

该两个字段，分别来源于两个参与连接的表；

我们的内连接，就是要找出这样的一些有意义的数据，如下所示：

结果为：

可见，这里查询的结果，都是前述查询（交叉连接）中，有意义的那些数据！

其中 product.protype_id = product_type.protype_id 被称为“连接条件”，它基本上就是我们之前所学的“外键关系”的一个描述。

注意：

这种的表跟表之间的内连接查询，虽然可以体现为表跟表之间的“关系”——通常就是外键关系——但并不是有外键关系才能使用这种连接。

 

另外的一些变通形式：

可见，数据表也可以使用“as 别名”的形式来取别名；

左（外）连接 left (outer) join：

形式：

表1（左表）  left  【outer】 join  表2（右表）  on  连接条件

含义：

其实就是将两个表的内连接的结果，再加上左边表的不符合内连接所设定的条件的那些数据的结果；

 

举例：

结果：

 

可见，左连接的结果，左边表的数据，一定都会“全部取出”；

右（外）连接 right (outer) join：

形式：

表1（左表）  right  【outer】 join  表2（右表）  on  连接条件

含义：

其实就是将两个表的内连接的结果，再加上右边表的不符合内连接所设定的条件的那些数据的结果；

 

举例：

结果：

全（外）连接 full (outer) join：

形式：

没有形式，因为mysql不支持全连接的语法；

 

含义：

其实就是将两个表的内连接的结果，再加上左边表的不符合内连接所设定的条件的那些数据的结果，以及再加上右边表的不符合内连接所设定的条件的那些数据的结果；

连接查询举例：

例1：对商品表：

查出每个品种各有多少个商品：

结果为：

例2：对学生院系表：

语法如下：

结果为：

子查询

基本含义

一个select语句，就是一个查询语句：

select  字段或表达式   from  数据源   where  XX 条件判断；

上述select部分，from部分，where部分，往往都是一些“数据”或数据的组合；比如：

from部分，当然就是“表”，或“表的连接结果”，他们也是数据，只是通常为“表数据”；

则，这些数据部分，也可以通过一种“预先”的查询结果来代替，比如：

如果将该“select 5000”的子查询语句，替换为“select出来所有商品的平均价”，就具有了现实含义：

 

 

可见，所谓子查询，就是在一个查询语句（select语句）中的内部，某些位置，又出现的“查询语句”。

则有两个概念：

主查询：

子查询：

通常，子查询是为主查询服务的，而，通常，都是子查询获得一定的结果数据之后，才去执行主查询；

 

在形式上，可以有如下表达：

• selelct 字段或表达式或子查询 [as 别名] from 表名或链接结果或子查询 where  字段或表达式或子查询的条件判断

 

即可以在这几个位置出现子查询（其中having其实也可以，因为它跟where是一样含义）；

按子查询结果，分为：

• 表子查询 ：
  • 一个子查询返回的结果理论上是“多行多列”的时候。此时可以当做一个“表”来使用，通常是放在from后面。
• 行子查询 ：
  • 一个子查询返回的结果理论上是“一行多列”的时候。此时可以当做一个“行”来使用，通常放在“行比较语法”中；
  • 行比较语法类似这样：where  row(字段1，字段2) = (select 行子查询)
• 列子查询 ：
  • 一个子查询返回的结果理论上是“多行一列”的时候。此时可以当做“多个值”使用，类似这种：(5, 17, 8, 22)。
• 标量子查询：
  • 一个子查询返回的结果理论上是“一行一列”的时候。此时可以当做“一个单个值”使用，类似这种：select 5 as c1; 或select ...where a = 17，或select ... where b > 8;即上述“单个数据值”，可以用标量子查询来代替；

子查询，按位置（场合）分：

• 作为主查询的结果数据：
  • select c1,(select f1 from tab2) as f11 from tab1; #这里子查询应该只有一个数据（一行一列，标量子查询）
• 作为主查询的条件数据：
  • select c1 from tab1 where c1 in (select f1 from tab2); #这里子查询可以是多个数据（多行一列，列子查询）
• 作为主查询的来源数据：select c1 from (select f1 as c1, f2 from tab2) as t2; #这里子查询可以是任意查询结果（表子查询）。

 

常见子查询

比较运算符中的子查询

形式：

操作数 比较运算符 (标量子查询)；

说明：

操作数，其实就是比较运算符的2个数据之一而已，通常就是一个字段名；

select ....  from XXX where  id > 5;

 

举例：

找出最高价的商品；

select * from product  where  price = (select  max(price)  from  product );

使用in的子查询

以前用的in的用法：

XX  in  (值1，值2，值3，....）；

则in子查询为：

XX  in  (列子查询)

举例：

找出所有类别名称中带“电”这个字的所有商品；

可以分两步思考：

select  protype_id  from  product_type  where  protype_name  like ‘%电%’;

结果为：

然后，将此结果，当做“in”的数据项来使用，去找出商品：

select  *  from  product  where  protype_id  in(

select  protype_id  from  product_type  where  protype_name  like ‘%电%’

);

 

 

使用any的子查询

形式：

操作数 比较运算符 any  (列子查询)；

含义：

当某个操作数（字段） 对于该列子查询的其中任意一个值，满足该比较运算符，则就算是满足了条件；

即：只要有一个值满足，就算是满足；

进一步解释：

假设表1(tab1)有数据为：

id,   name

1     ‘aa’,

5     ‘bb’

11    ‘cc’

假设表2(tab2)有数据为：

f1           f2

3            ‘x1’

6            ‘x2’

12           ‘x3’

则：

select  *  from  tab1  where  id >  any  (select  f1  from  tab2);

则可以取出的结果数据有：

5     ‘bb’

11    ‘cc’

使用all的子查询：

形式：

操作数  比较运算符 all   (列子查询)；

含义：

当某个操作数（字段） 对于该列子查询的所有数据值，都满足该比较运算符，才算满足了条件；

即：要求全部都满足，才算是满足；

进一步解释：

假设表1(tab1)有数据为：

id,   name

1     ‘aa’,

5     ‘bb’

11    ‘cc’

假设表2(tab2)有数据为：

f1           f2

3            ‘x1’

6            ‘x2’

12           ‘x3’

则：

select  *  from  tab2  where  f1  >  all  ( select  id  from   tab1 );

结果是：

12           ‘x3’

 

举例1：

查询出所有非最高价的商品；

思考第一步：

select  price  from  product;

思考第二步：这些非最高价的商品的价格，只是会小于上述“所有价格”中的某一个；

select  *  from  product  where  price <  any ( select  price  from  product );

举例2：

查询出所有最高价的商品；

思考：最高价的商品价格，有一个特征：大于等于“所有价格”：

select  *  from  product  where  price  >=  all ( select  price  from  product );

 

使用some的子查询

一句话：some是any的同义词。

 

使用exists的查询

形式：

where  exists( 子查询 )

含义：

该子查询如果“有数据”，则exists的结果是true，否则就是false

 

说明：

因为，exists子查询的该含义，造成主查询往往出现这样的情形：要么全都取出，要么都不取出。

如果局限于这个含义（使用情形），其基本就失去了它的现实使用意义。

但：

实际应用中，该子查询，往往都不是独立的子查询，而是会需要跟“主查询”的数据源（表），建立某种关系——通常就是连接关系。建立的方式是“隐式的”，即没有在代码上体现关系，但却在内部有其连接的“实质”。

此隐式连接方式，通常就体现在子查询中的ｗｈｅｒｅ条件语句中，使用了主查询表中的数据（字段）；

 

举例：

查询商品表中其类别名称中带“电”这个字的所有商品；

 

 

结果就是我们想要的：

注意：

１，这种子查询语句，没法“独立存在（独立运行）”，而是必须跟主查询一起使用；

２，其他子查询，是可以独立运行的，而且会得到一个运行的结果。

３，该子查询中的条件，应该设定为跟主查询的某个字段有一定的关联性判断，通常该判断就是这两个表的“本来该有的连接条件”

 

最后一个结论：

如果一个查询需求，可以使用连接查询的，也可以使用子查询得到，则通常推荐使用连接插叙，效率归更高。

联合查询ｕｎｉｏｎ

一个翻译问题的解释：

在ｍｙｓｑｌ的手册中，将连接查询（Ｊｏｉｎ）翻译为联合查询；

而联合查询（ｕｎｉｏｎ），没有明确翻译。

但：

在通常的书籍或文章中，ｊｏｉｎ被翻译为“连接”查询；而ｕｎｉｏｎ才被翻译为联合查询。

 

基本概念

将两个具有相同字段数量的查询语句的结果，以“上下堆叠”的方式，合并为一个查询结果。

图示如下：

 

 

可见：

１，两个ｓｅｌｅｃｔ语句的查询结果的“字段数”必须一致；

２，通常，也应该让两个查询语句的字段类型具有一致性；

３，也可以联合更多的查询结果；

 

语法形式

ｓｅｌｅｃｔ　语句１

ｕｎｉｏｎ　【ａｌｌ　｜　ｄｉｓｔｉｎｃｔ】

ｓｅｌｅｃｔ　语句２；

 

此联合查询语句，默认会“自动消除重复行”，即默认是ｄｉｓｔｉｎｃｔ

如果想要将所有数据都显示（允许重复行），就使用ａｌｌ

即，这里，写ａｌｌ才有意义；

对比普通ｓｅｌｅｃｔ语句：

ｓｅｌｅｃｔ　【ａｌｌ　｜　ｄｉｓｔｉｎｃｔ】　。。。。

对于ｓｅｌｅｃｔ语句，写ｄｉｓｔｉｎｃｔ才有意义；

 

细节：

• 应该将这个联合查询的结果理解为最终也是一个“表格数据”，且默认使用第一个select语句中的字段名；

 

 

 对比 

 

 

• 默认情况下，order by子句和limit子句只能对整个联合之后的结果进行排序和数量限定：select... union select... order by XXX limit m,n;

• 基本用法：

• 

   

不对的做法：

无效的做法：

• 如果第一个select语句中的列有别名，则order by子句中就必须使用该别名。

 

最后，来一个“应用”：

实现“全外连接”：

select  * f rom  表1  left  join 表2 on  条件

union

select  * f rom  表1  right  join 表2  on  条件

结果为：