MySQL(三)--子查询

Posted by Suzeyu on 2016-05-17

自学小笔记

连接查询

概念: 就是将两个或两个以上的表,连接起来,当做一个数据源,并从中去取得所需要的数据。

就是将每一个表的每一行数据两两之间相互对接起来,每次对接的结果都是连接结果的 一行 数据。

没有条件的连接写法:

  • select * from 表1, 表2;
  • select * from 表1 join 表2;
  • select * from 表1 cross join 表2;

连接基本形式

在代码级别,连接的基本形式为:

  • 表1 [连接形式] join 表2 [on 连接条件];

如果是3个表,则进一步扩展为:

  • 表1 [连接形式] join 表2 [on 连接条件] [连接形式] join 表3 [on 连接条件]

连接的分类

交叉连接

  • 其实就是上面的连接形式,–因为没有条件,只是按连接的基本概念,将所有数据行都连接起来的结果。它又叫做‘笛卡尔积’。
  • 对于表1 和表2 的连接,交叉结果是: 行数=两个表字段之和; 列数=两个表的乘积数

内连接

  • 形式: select * from 表1 [inner] join 表2 on 连接条件。

例如有 student表和 type表 学生表中包含科目pro_id, 而Type表有每个pro_id字段并对应说明。这时候如果需要类连接查询如下:

select * from student inner join type on student.pro_id = type.pro_id;

优化: SELECT o.* ,t.sex FROM db1.join1 as o inner join db1.join2 as t on o.type = t.type;

注意: 这种的表和表之间的内连接传,虽然可以提现为表跟表之间的关系– 一般情况下这就是外键的关系,单并不是有外键关系才可以使用。

左(外)连接 left(outer) join

  • 形式: 表1(左表) left [outer] join 表2(右表) on 连接条件
  • 含义: 就是将两个表的内连接的结果,再加上左边表不符合内连接所设定的条件的那些数据结果。
  • SELECT o.* ,t.sex FROM db1.join1 as o left join db1.join2 as t on o.type = t.type;

右(外)连接 right(outer) join

同左连接–只不过关键字为right(outer)

全连接 full(outer) join

mysql中不支持全连接的语法;

含义: 将两个表内连接的结果,加上左边不符合的结果,和右边不符合的结果。

子查询

含义: 一个select语句,就是一个查询语句

  • select 字段或表达式 from 数据源 where xx条件判断;

上述select部分,from部分,where部分,往往都是一些数据 或者数据的集合。

from部分, 当然就是 表 ,或 表的连接结果,他们也是数据,只是通常为表数据。

例子 select pro_id, price from product where price > (select avg(price) from product)

例子的where后面括号中的select语句就是子查询。

所以可以认为就是在一个查询语句的内部,某些位置,又出现了查询语句。 由此引出了主查询和子查询的概念。

通常,子查询是为主查询服务的,所以子查询获得一定的结果数据之后,才去执行主查询。

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

按子查询结果,可分为如下几个

  • 表 子查询

一个子查询返回的结果理论上是多行多列的时候,此时可以当做一个表来使用,通常是放在from后面。

  • 行 子查询

一个子查询返回的结果理论是一行多列的时候。此时可以当做一个行来使用,通常放在行比较语法上。
行比较语法类似这样: where row(字段1,字段2) = (select 行子查询);

  • 列 子查询

一个子查询返回的结果理论上是多行一列的时候,此时可以当做多个值来使用。
类似(5,8,10,11).

  • 标量 子查询

一个子查询返回的结果理论上是一行一列的时候,此时可以当做一个单个值使用,类似这种: select 5 as c1; 或select ... where id = 17, 或select ... where b>8;

子查询,按位置(场合)分

  • 作为主查询的结果数据
  • 作为主查询的条件数据
  • 作为主查询的来源数据

常见子查询

比较运算符总的子查询

形式: 操作数 比较运算符 (标量子查询)

说明: 操作数,其实就是比较运算符的2个数据之一而已,通常就是一个字段名。

举例:

找出最高价的商品:

  • 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 %电%;

为子查询条件 — 以下为主程序 嵌入where其中。

select * product where protype_id in('此处为上一步');

使用any的子查询

形式: 操作数 比较运算符 any (列子查询);

含义: 当某个操作数(字段) 对于该列子查询的任意一个值,只要有一个满足该比较运算符,则就算是满足了条件。

使用all的子查询

形式: 操作数 比较运算符 all (列子查询);

含义: 当某个操作数(字段) 对于该列子查询的任意一个值,必须全部满足该比较运算符,则就算是满足了条件。

列子查询 子查询只能是一个字段。

举例: 查询出所有非最高价的商品

  1. 查询所有的价格 select price form product;
  2. 在所有的价格中只有最高价会不满足 <any(全部价格),所以可以这样
  • select * from product where price < any(select price from product);

同理: 求出最高价的商品价格,特征: 大于所有的价格

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

使用some的子查询

一句话: someany的同义词。

使用exists的查询

形式: where exists (子查询)

含义: 该子查询如果有数据,则exists的结果是true,否则就是false

在实际应用中,该子查询往往都不是独立的子查询,而是会需要跟主查询的数据源(表),建立牟宗关系—通常就是连接关系。建立的方式是”隐式的”,既没有在代码上体现关系,单却在内部有其连接的 实质 。

此隐式方式,通常就体现在查询中的where条件语句中,使用了主查询表中的数据(字段),

问题: 查询商品表中其类别名称中带”电”这个字的所有商品。

select * from product where exists(select * from product_type where protype_name like '%电%' and protype_id = product.protype_id);

注意:

  1. 这种子查询语句,没法独立存在或者独立运行的,而是必须跟主查询一起使用。
  2. 其他子查询,是可以独立运行的,而且会得到一个运行结果。
  3. 盖子查询中的条件,应该设定为跟主查询的某个字段有一定的关联性判断。筒仓该判断就是这两个表的 本来该有的脸连接条件。

联合查询union

提示:

mysql的手册中,将连接查询(join) 翻译为联合查询。而联合查询(union),没有明确翻译。

在通常的书籍或文章,join被翻译为连接查询;而union才被翻译为联合查询。

基本概念

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

形式: select 语句1 union [all | distinct] select 语句2;

此联合查询语句,默认会”自动消除重复行” ,既默认是distinct。 如果想要将所有数据都显示(允许重复行),就是用all