分享
 
 
 

结构化子查询:在mysql4.1中的应用

王朝mysql·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

By Trudy Pelzer

翻译:guipei

随着mysql 4.1版的发布,现在有两种方法可以使用一个单独的查询从多个数据表中取得数据:使用连接(join)或者使用子查询(subquery)。我们来举例说明,假设你有一下的表格:

CREATE TABLE clients (

clno INT,

fname VARCHAR(15),

lname VARCHAR(15),

job VARCHAR(15),

account_balance DECIMAL(7,2));

INSERT INTO clients VALUES

(10, 'sam','smith','auditor',5525.75),

(20,'james','jones','manager',8960.25);

CREATE TABLE firms (

clno INT,

company VARCHAR(15),

city VARCHAR(15));

INSERT INTO firms VALUES

(10,'abc co','leduc'),

(20,'def ltd','nisku'),

(30,'ghi inc','nisku');

下面我们使用连接(join)方法取得client表格中id为10的数据。

SELECT

fname, lname, city, job, company, account_balance

FROM clients c, firms f

WHERE c.clno = f.clno

AND c.clno = 10;

但是你并非总是可以通过使用连接(join)方式查询你所需要的数据。举一个例子,假如你需要最大的帐户表中的客户信息。下面的查询,看起来好像应该可以正确返回你所需要的数据,但是实际上会发生错误:

SELECT

fname, lname, city, job, company, account_balance

FROM clients c, firms f

WHERE c.clno = f.clno

AND c.account_balance = MAX(c.account_balance);

错误的原因是:invalid use of group function,就是这个聚合函数,Max,不允许出现在where子句当中。这也就是子查询诞生的原因,在本文当中,我将简短的描述添加到mysql数据库系统的子查询功能。

子查询就是包含在括号中选择语句

简单的将,子查询就是一个被写在另一个sql语句(一般情况下,是select语句,但是不总是这样)中的查询语句。判断是否是子查询的方式是看查询语句是否在括号中间,下面是一个例子。

SELECT * FROM clients WHERE clno IN -- outer query

(SELECT clno FROM firms WHERE city = 'leduc'); -- inner query

SELECT * FROM clients WHERE clno IN -- 外部查询

(SELECT clno FROM firms WHERE city = 'leduc'); -- 内部查询

这个查询将会返回表clients中clno中所有城市为’leduc’的记录。为了得到结果,dbms首先会取得内部查询的结果,查找所有 表 clno中所有城市为‘leduc’的记录。然后比较表clients,返回符合clno值的每一行。因为只有一个值符合子查询,实际上,等效于下面语句:

SELECT * FROM clients WHERE clno = 10;

当然,这个查询语句也可以写成以下方式:

SELECT

c.clno, fname, lname, job, account_balance

FROM clients c INNER JOIN firms f USING (clno)

WHERE city = 'leduc';

目前,就上面提到的问题,可以通过子查询解决(查询这些客户clno值最大的记录):

SELECT fname, lname FROM clients WHERE clno =

(SELECT MAX(clno) FROM firms);

如果内部查询返回空结果集,子查询会返回错误,举例说明,参看下面查询:

SELECT * FROM clients WHERE clno =

(SELECT clno FROM firms WHERE city = 'gibbons');

如果内部查询单独运行的话,很显然结果为空:因为这个表中没有city等于’gibbons’的记录。但是空的结果集不能作为值来比较。SQL 标准要求子查询的结果集,空的结果集,或者为空。因为没有任何东西等于null,查询返回空消息。

一般来说,子查询是嵌套在其他的查询当中,MySql支持子查询嵌套在其他sql语句当中,这是一个很好的功能。

Types of Subqueries

子查询类型

子查询共有三种类型,区分它们的方法是根据返回结果的列数和行数。

如果一个子查询返回仅仅一列一行数据,被成为数量级子查询。一个数量级子查询可以用在任何适合数据值比较的地方(一个列值或者文本)。它常常被使用在where子句当中,跟随在比较操作符号后面。

如果子查询返回多个列,并且是一行记录的话,它被称为行级子查询。行级子查询是数量级子查询的发展,可以用在任何适合数量级子查询的地方。

最后,如果一个子查询返回多个列和多行的话,它被称为表级子查询。表级子查询可以用作涉及到表的查询操作,包含在from子句当中。它也常常被用在where子句当中,后面使用in 或者 exist或者一个确定的比较操作(确定的比较操作是一个比较操作通过使用some,all,或者any限定)。

数量级子查询和表级子查询的区别非常小。经常会出现在一个数量级子查询被编写的情况下,但是子查询返回多个记录行。假设我们有两个表和一些记录数据。

INSERT INTO clients VALUES

(10, 'sam','smith','auditor',5525.75);

INSERT INTO firms VALUES

(10,'abc co','leduc'),(30,'ghi inc','nisku');

因为在这个查询中表firms有两行记录返回:

SELECT * FROM clients WHERE clno <

(SELECT clno FROM firms);

错误原因:

"Subquery returns more than 1 row"

这种情况有两个解决方法。第一个是修改查询让其用any包含,使外部查询的比较任意的值 :

SELECT * FROM clients WHERE clno < ANY

(SELECT clno FROM firms);

在这里,第一个比较是(10<10),返回为false,但是第二个比较(10<30),返回true,因此子查询返回true,使用的是 clno 10相比较的结果,使用any 的规则如下:

ANY返回true,如果子查询的比较操作至少有一个是是true。

ANY 返回 false,如果子查询返回为空行,或者比较任意一行都是false。

ANY的同义词是SOME,使用in 等同于使用 = ANY。

第二种方法是修改查询,使用all包含子查询,比较外部的查询结果使用每一个子查询的值。

SELECT * FROM clients WHERE clno < ALL

(SELECT clno FROM firms);

在这里,第一次的比较还是返回false,而第二此返回true,最后,子查询的结果是false,所以查询返回空行。使用all的规则是:

返回true,如果子查询返回空行记录,或者子查询的每一个结果比较都为true的话。

返回false,如果子查询至少有一个比较结果返回false。

Does the Subquery Return at least One Row?

子查询至少要求返回一行么?

在有些时候,查询的结果是子查询是否有一个结果返回。使用[NOT] EXISTS来判断结果集是否为空。如果你的子查询至少有一行返回,使用EXISTS 返回 true;否则,返回为false。还有一种用法,NOT EXISTS,如果子查询结果集为空,它返回为true,否则的话,返回为true。一般情况下,子查询跟随在 [NOT] EXISTS后面,以select * 开头。在这里,星号(*)不是表示列出所有的列,而是表示为列出一些列。这里有一个小例子,返回所有client表的结果。

SELECT * FROM clients WHERE EXISTS

(SELECT * FROM firms);

在本例当中,where子句返回true,因为表firms不为空。通常[NOT] EXISTS会使用在更加复杂的情况下面,假设你有一下数据表:

CREATE TABLE passengers (

name VARCHAR(15),

compartment INT);

INSERT INTO passengers VALUES ('smith',20);

INSERT INTO passengers VALUES ('jones',25);

CREATE TABLE cars (

compartment INT,

class VARCHAR(10));

INSERT INTO cars VALUES (20,'first');

下面是这中类型的查询:

SELECT * FROM cars c1 WHERE NOT EXISTS

(SELECT * FROM passengers p1 WHERE NOT EXISTS

(SELECT * FROM cars c2

WHERE c2.compartment = p1.compartment

AND c2.compartment = c1.compartment));

这个查询的作用是请求查找所有存在乘客的汽车。为了理解这个结果集的含义,我们假设Smith在20号汽车,Jones在25号汽车,但是在cars表中没有包含25号汽车。这样就会有一个乘客―Jones―乘坐一个不存在的汽车。(在标准的数据库中,不会存在这种情况,可以通过使用主键/外键来约束这两个表间的数据完整性)。这个查询中的第二个NOT EXISTS子查询,返回true对于乘客Jones。

另外,这里的一个乘客―Jones,没有乘坐20号汽车。因此第一个NOT EXISTS子查询在这里返回false。因为这里没有其他汽车被检查,这个查询的结果是空(返回零行)――这里没有汽车每一个乘客都乘坐。

子查询的其他用法

在sql标准1999中,Mysql提供了增强的子查询支持。在前面提到的行级子查询,现在支持一次比较多个列的值:

SELECT ROW ('smith', 'auditor') =

(SELECT lname, job FROM clients WHERE clno = 10);

在这个例子中,子查询包含值为'smith' 和 'auditor'的数据。这些数据和ROW的值就行比较时,他们的内容一样,因此返回1(true)。

你也可以把一个子查询,作为一个表名,放在from子句后面(就像oracle里面的用法一样,作为内部视图):

SELECT * FROM

(SELECT * FROM clients WHERE job LIKE 'a%') AS cl;

为了取得这个查询的结果,mysql服务器熟悉取得子查询的结果,然后把结果集赋予别名(cl,在本例中)。然后让外部的查询使用。实际上,上面的例子可以被分解如下:

SELECT * FROM cl;

这里cl作为一个子查询的临时结果集:

SELECT * FROM clients WHERE job LIKE 'a%';

当子查询被使用在from 子句当中,关键字as是强制使用的;中间的临时结果必须被命名,作为外部的查询被引用。

使用子查询修改数据

子查询还有这样的一个功能:用来修改数据库的数据。这就是说,你可以把子查询放在delete,insert,update或者replace语句里面,我们举例说明:

UPDATE clients SET account_balance =

(SELECT SUM(amount) FROM accounts where clno=clients.clno);

这个update语句为每一个客户修改 account_balance 数据,通过计算account表中的客户帐号。

这里有一个提示:目前不允许使用同一个表的子查询来修改这个表中的数据。

总结:

子查询在mysql 4.1版中新添加,现在支持数量、行、表的子查询。

比较操作符号= <> < <= > >=可以使用在子查询中,并且可以使用[NOT] IN 和 [NOT] EXISTS子句。

表级子查询可以使用短句ANY/SOME 或者 ALL做比较。

子查询可以用来修改数据。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有