Mysq学习-Mysql查询（4）

5.子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从MySQL4.1开始引入.在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表.
子查询中常用的操纵符有ANY(SOME),ALL,IN,EXISTS.子查询可以添加到SELECT,UPDATE和DELETE语句中,而且可以进行多层嵌套.子査询中也可以使用比较运算符,如"<""<=""!="等
下面将先容如何在SELECT语句中嵌套子查询.
(1)带ANY，SOME关键字的子查询
ANY和SOME关键字是同义词,表示满足其中任一条件,它们答应创建一个表达式对子查询的返回值列表进行比较,只要满足内层子查询中的任何一个比较条件,就返回一个结果作为外层查询的条件.
ANY关键字接在一个比较操纵符的背面,表示若与子查询返回的任何值比较为TRUE,则返回TRUE;

1. 返回tbl2表的所有num2列,然后将tbl1中的num1的值与之进行比较,只要大于num2的任何1个值,即为符合查询条件的结果
3. SELECT num1
4. FROM tb11
5. WHERE num1 > ANY(SELECT num2 FROM tbl2);

复制代码

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

1. 返回tbl1表中比tb12表num2列所有值都大的值
2. SELECT num1 FROM tbl1
3. 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,此时外层语句将不进行查询,

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

复制代码

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

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

复制代码

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

1. 查询suppliers表中是否存在s id=107的供应商,如果不存在则査询fruts表中的记录
2. SELECT *
3. FROM fruits
4. WHERE NOT EXISTS
5. (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关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较操纵.

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

复制代码

这个例子说明在处置惩罚SELECT语句的时间,MySQL现实上执行了两个操纵过程,即先执行内层子查询再执行外层查询,内层子查询的结果作为外部查询的比较条件:
SELECT语句中可以使用NOTIN关键字,其作用与IN恰恰相反

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

复制代码

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

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

1. 在suppliers表中査询s city等于"Tianjin"的供应商s id,然后在fruits表中查询所有该供应商提供的水果的种类;
3. SELECT s_id,f_name
4. FROM fruits
5. WHERE s_id =
6. (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

复制代码

6.归并查询结果
利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集,归并时,两个表对应的列数必须相同.各个SELECT语句之间使用UNION或UNION ALL关键字分隔.UNION不使用关键字ALL,执行的时间删除重复的记录,全部返回的行都是唯一的;使用关键字ALL的作用是不删除重复行也不对结果进行自动排序.
归并结果集在数据库查询中非常有效，尤其是在处置惩罚多表查询或须要对多个查询结果进行组适时。归并结果集可以做到:数据整合.简化查询逻辑.提高性能.机动性和可扩展性
须要注意的是，归并结果集时，被归并的两个结果的列数、列类型必须相同。如果列类型不相同，可以通过SELECT关键字去筛选须要的列，以确保归并操纵的正确性。

1. 查询所有价格小于9的水果的信息,査询s_id等于101和103所有水果的信息,使用UNION连接查询结果;
2. SELECT s_id,f_name,f_price
3. FROM fruits
4. WHERE f_price <9.0
5. UNION SELECT s_id,f_name,f_price
6. FROM fruits
7. WHERE s_id IN(101,103);
8. //返回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)为表取别名
当表名字很长或者执行一些特别查询时,为了方便操纵或者须要多次使用相同的表时,可以为表指定别名,用这个别名替代原来的名称.

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

复制代码

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

复制代码

由结果看到,MySQL可以同时为多个表取别名,而且表别名可以放在不同的位置,如WHERE子句,SELECT列表,ON子句以及ORDER BY子句等
在前面先容内毗连查询时指出自毗连是一种特别的内毗连,在毗连查询中的两个表都是同一个表,其查询语句如下:

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

复制代码

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

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

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

复制代码

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

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

复制代码

由结果可以看到,SELECT子句计算字段值之后增加了AS suppliers title,它指示MySQL为计算字段创建一个别名suppliers title,表现结果为指定的列别名,这样就增强了查询结果的可读性
注意:表别名只在执行查询的时间使用,并不在返回结果中表现;而列别名界说之后,将返回给客户端表现,表现的结果字段为字段列的别名,

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。