英文网站排版,深圳平面设计公司排行榜,郑州网站建设蝶动,建立企业网站5.子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从MySQL4.1开始引入.在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表. 子查询中常用的操作符有ANY(SOME),ALL,IN,EXISTS.子查询可以添加到SELECT,UPD…5.子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从MySQL4.1开始引入.在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表. 子查询中常用的操作符有ANY(SOME),ALL,IN,EXISTS.子查询可以添加到SELECT,UPDATE和DELETE语句中,而且可以进行多层嵌套.子査询中也可以使用比较运算符,如!等 下面将介绍如何在SELECT语句中嵌套子查询.
(1)带ANYSOME关键字的子查询 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 id107的供应商,如果存在,则查询fruits表中的记录
SELECT *
from fruits
WHERE EXISTS
(SELECT s_name FROM suppliers WHERE s_id107);
EXISTS可以和条件表达式一起使用
查询suppliers表中是否存在s id107的供应商,如果存在,则査询fruits表中的f price大于10.20的记录
SELECT *
FROM fruits
WHERE f_price10.20 AND EXISTS
(SELECT s_name FROM suppliers WHERE s_id107);
注意,内层查询结果为true才去执行外层的查询语句
NOT EXISTS与EXISTS使用方法相同,返回的结果相反.子査询如果至少返回一行,那么NOT EXISTS的结果为false,此时外层查询语句将不进行查询.如果子查询没有返回任何行,那么NOT EXISTS返回的结果为true,此时外层语句将进行查询,
查询suppliers表中是否存在s id107的供应商,如果不存在则査询fruts表中的记录
SELECT *
FROM fruits
WHERE NOT EXISTS
(SELECT s_name FROM suppliers WHERE s_id107);
查询语句SELECTs name FORM suppliers WHEREsid107,对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_idc0);
这个例子说明在处理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_idc0);
注意:子查询的功能也可以通过连接查询完成,但是子查询使得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_num30001;
为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 price8的水果的名称;
SELECT f1.f_name As fruits_name,f1.f_price As fruit_price
FROM fruits AS f1
MWHERE f1.f_price8;
也可以为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,显示结果为指定的列别名,这样就增强了查询结果的可读性
注意:表别名只在执行查询的时候使用,并不在返回结果中显示;而列别名定义之后,将返回给客户端显示,显示的结果字段为字段列的别名,
