Mysq学习-Mysql查询（4）

5.子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从MySQL4.1开始引入.在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表.
子查询中常用的操作符有ANY(SOME),ALL,IN,EXISTS.子查询可以添加到SELECT,UPDATE和DELETE语句中,而且可以进行多层嵌套.子査询中也可以使用比较运算符,如"<""<=""!="等
下面将介绍如何在SELECT语句中嵌套子查询.

(1)带ANY，SOME关键字的子查询
ANY和SOME关键字是同义词,表示满足其中任一条件,它们允许创建一个表达式对子查询的返回值列表进行比较,只要满足内层子查询中的任何一个比较条件,就返回一个结果作为外层查询的条件.

ANY关键字接在一个比较操作符的后面,表示若与子查询返回的任何值比较为TRUE,则返回TRUE;

返回tbl2表的所有num2列,然后将tbl1中的num1的值与之进行比较,只要大于num2的任何1个值,即为符合查询条件的结果

SELECT num1
FROM tb11
WHERE num1 > ANY(SELECT num2 FROM tbl2);

(2)带ALL关键字的子查询
ALL关键字与ANY和SOME不同,使用ALL时需要同时满足所有内层査询的条件,例如,修改前面的例子,用ALL关键字替换ANY.
ALL关键字接在一个比较操作符的后面,表示与子査询返回的所有值比较为TRUE,则返回TRUE;

返回tbl1表中比tb12表num2列所有值都大的值
SELECT num1 FROM tbl1
WHERE num1 > ALL(SELECT num2 FROM tbl2);

在子査询中,返回的是tbl2表的所有num2列结果(6,14,11,20),然后将tbl1中的num1列的值与之进行比较,大于所有num2列值的num1值只有27,因此返回结果为27

(3)带EXISTS关键字的子查询
EXISTS关键字后面的参数是一个任意的子査询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么EXISTS的结果为true,此时外层査询语句将进行査询:如果子查询没有返回任何行,那么EXISTS返回的结果为false,此时外层语句将不进行查询,

查询suppliers表中是否存在s id=107的供应商,如果存在,则查询fruits表中的记录
SELECT * 
from fruits
WHERE EXISTS
(SELECT s_name FROM suppliers WHERE s_id=107);

EXISTS可以和条件表达式一起使用

查询suppliers表中是否存在s id=107的供应商,如果存在,则査询fruits表中的f price大于10.20的记录
SELECT *
FROM fruits
WHERE f_price>10.20 AND EXISTS
(SELECT s_name FROM suppliers WHERE s_id=107);

注意,内层查询结果为true才去执行外层的查询语句

NOT EXISTS与EXISTS使用方法相同,返回的结果相反.子査询如果至少返回一行,那么NOT EXISTS的结果为false,此时外层查询语句将不进行查询.如果子查询没有返回任何行,那么NOT EXISTS返回的结果为true,此时外层语句将进行查询,

查询suppliers表中是否存在s id=107的供应商,如果不存在则査询fruts表中的记录
SELECT *
FROM fruits
WHERE NOT EXISTS
(SELECT s_name FROM suppliers WHERE s_id=107);

查询语句SELECTs name FORM suppliers WHEREsid=107,对suppliers表进行查询返回了条记录,NOT EXISTS表达式返回false,外层表达式接收false,将不再查询fruits表中的记录
注意:EXISTS和NOT EXISTS的结果只取决于是否会返回行,而不取决于这些行的内容,所以这个子查询输入列表通常是无关紧要的

(4)带IN关键字的子查询
IN关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较操作.

在orderitems表中查询f id为c0的订单号,并根据订单号查询具有订单号的客户cid;
SELECT c_id FROM orders
WHERE o_num IN
(SELECT o_num FROM orderitems WHERE f_id='c0');

这个例子说明在处理SELECT语句的时候,MySQL实际上执行了两个操作过程,即先执行内层子查询再执行外层查询,内层子查询的结果作为外部查询的比较条件:

SELECT语句中可以使用NOTIN关键字,其作用与IN正好相反

与前一个例子类似,但是在SELECT语句中使用NOT IN关键字:
SELECT c_id
FROM orders
WHERE o_num NOT IN
(SELECT o_num FROM orderitems WHERE f_id='c0');

注意:子查询的功能也可以通过连接查询完成,但是子查询使得MySQL代码更容易阅读和编写

(5)带比较运算符的子查询
在前面介绍的带ANYALL关键字的子查询时使用了">"比较运算符,子查询时还可以使用其他的比较运算符,如"<""<="'>=""|="等

在suppliers表中査询s city等于"Tianjin"的供应商s id,然后在fruits表中查询所有该供应商提供的水果的种类;

SELECT s_id,f_name
FROM fruits
WHERE s_id =
(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

6.合并查询结果
利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集,合并时,两个表对应的列数必须相同.各个SELECT语句之间使用UNION或UNION ALL关键字分隔.UNION不使用关键字ALL,执行的时候删除重复的记录,所有返回的行都是唯一的;使用关键字ALL的作用是不删除重复行也不对结果进行自动排序.
合并结果集在数据库查询中非常有用，尤其是在处理多表查询或需要对多个查询结果进行组合时。合并结果集可以做到:数据整合.简化查询逻辑.提高性能.灵活性和可扩展性

需要注意的是，合并结果集时，被合并的两个结果的列数、列类型必须相同。如果列类型不相同，可以通过SELECT关键字去筛选需要的列，以确保合并操作的正确性。

查询所有价格小于9的水果的信息,査询s_id等于101和103所有水果的信息,使用UNION连接查询结果;
SELECT s_id,f_name,f_price
FROM fruits
WHERE f_price <9.0
UNION SELECT s_id,f_name,f_price
FROM fruits
WHERE s_id IN(101,103);
//返回12行

使用UNION ALL包含重复的行,在前面的例子中,分开查询时,两个返回结果中有相同的记录.UNION从查询结果集中自动去除了重复的行,如果要返回所有匹配行,而不进行删除,可以使用UNION ALL.

注意:UNION和UNION ALL的区别:使用UNION ALL的功能时不删除重复行,加上ALL关键字语句执行时所需要的资源少,所以尽可能地使用它,因此知道有重复行但是想保留这些行,确定查询结果中不会有重复数据或者不需要去掉重复数据的时候,应当使用UNION ALL以提高查询效率

在大部分情况下，UNION ALL的效率要高于UNION。这是因为UNION在进行表连接后会筛选掉重复的记录，这个过程涉及到排序运算和删除重复记录的操作，相对较为耗时。而UNION ALL只是简单地将两个结果合并后就返回，不会进行去重和排序操作，因此执行速度更快。总之，在选择使用UNION还是UNION ALL时，应综合考虑业务需求、数据特点以及性能要求等因素，以选择最适合的操作符。

7.为表和字段取别名
在前面介绍分组查询,集合函数查询和嵌套子查询章节中,其实我们有的地方已经使用了AS关键字为查询结果中的某一列指定一个特定的名字.在内连接查询时,则对相同的表fruits分别指定两个不同的名字,这里可以为字段或者表取一个别名,在查询时,使用别名替代其指定的内容

(1)为表取别名
当表名字很长或者执行一些特殊查询时,为了方便操作或者需要多次使用相同的表时,可以为表指定别名,用这个别名替代原来的名称.

为orders表取别名o,查询30001订单的下单日期;
SELECT *
FROM orders As o
WHERE o.o_num=30001;

为customers和orders表分别取别名,并进行连接查询;(左连接)
SELECT c.c_id,o.o_num
FROM customers AS c LEFT OUTER JOIN orders AS o
ON c.c_id = o.c_id;

由结果看到,MySQL可以同时为多个表取别名,而且表别名可以放在不同的位置,如WHERE子句,SELECT列表,ON子句以及ORDER BY子句等

在前面介绍内连接查询时指出自连接是一种特殊的内连接,在连接查询中的两个表都是同一个表,其查询语句如下:

SELECT f1.f_id,f1.f_name
FROM fruits AS fl,fruits AS f2 
WHERE f1.s_id =f2.s_id AND f2.f_id ='a1';

在这里,如果不使用表别名,MySQL将不知道引用的是哪个fruits表实例,这是表别名一个非常有用的地方.
在为表取别名时,要保证不能与数据库中其他表的名称冲突

(2)为字段取别名
在使用SELECT语句显示查询结果时,MVSQL会显示每个SELECT后面指定的输出列,在有些情况下显示的列的名称会很长或者名称不够直观,MySQL可以指定列别名,替换字段或表达式.

查询fruits表中,为f_name取别名fruit_name,f_price取别名fruits,为fruits表取别名f1,查询表中f price<8的水果的名称;
SELECT f1.f_name As fruits_name,f1.f_price As fruit_price
FROM fruits AS f1
MWHERE f1.f_price<8;

也可以为SELECT子句中的计算字段取别名,例如,对使用COUNT聚合函数或者CONCAT等系统函数执行的结果字段取别名

查询suppliers表中字段s name和s city,使用CONCAT函数连接这两个字段值,并取列别名为suppliers title.
SELECT CONCAT(TRIM(s_name)，'('，TRIM(s_city)')')
As suppliers_title
FROM suppliers
ORDER BY s_name;

由结果可以看到,SELECT子句计算字段值之后增加了AS suppliers title,它指示MySQL为计算字段创建一个别名suppliers title,显示结果为指定的列别名,这样就增强了查询结果的可读性

注意:表别名只在执行查询的时候使用,并不在返回结果中显示;而列别名定义之后,将返回给客户端显示,显示的结果字段为字段列的别名,