mysql笔记（11）--子查询

Author：傻猫
发布时间：March 1, 2012
5348views
No comments
2084 words
Categories：弄数据

CREATE TABLE clients (
&nbsp; clno INT,
&nbsp; fname VARCHAR(15),
&nbsp; lname VARCHAR(15),
&nbsp; job VARCHAR(15),
&nbsp; account_balance DECIMAL(7,2));
&nbsp;
INSERT INTO clients VALUES
&nbsp; (10, 'sam','smith','auditor',5525.75),
&nbsp; (20,'james','jones','manager',8960.25);
&nbsp;
CREATE TABLE firms (
&nbsp; clno INT,
&nbsp; company VARCHAR(15),
&nbsp; city VARCHAR(15));
&nbsp;
INSERT INTO firms VALUES
&nbsp; (10,'abc co','leduc'),
&nbsp; (20,'def ltd','nisku'),
&nbsp; (30,'ghi inc','nisku');
select * from firms where clno in(select clno from clients );
select * from firms where clno =(select clno from clients &nbsp;where job=&rsquo;manager&rsquo;);
select fname,lname from clients where clno=(select max(clno) from firms);
select fname,lname from clients where clno=(select avg(clno) from firms);
如果内部查询返回空结果集，子查询会返回错误
子查询共有三种类型，区分它们的方法是根据返回结果的列数和行数。
如果一个子查询返回仅仅一列一行数据，被成为数量级子查询。一个数量级子查询可以用在任何适合数据值比较的地方（一个列值或者文本）。它常常被使用在where子句当中，跟随在比较操作符号后面。 如果子查询返回多个列，并且是一行记录的话，它被称为行级子查询。行级子查询是数量级子查询的发展，可以用在任何适合数量级子查询的地方。 最后，如果一个子查询返回多个列和多行的话，它被称为表级子查询。表级子查询可以用作涉及到表的查询操作，包含在from子句当中。它也常常被用在where子句当中，后面使用in或者exist或者一个确定的比较操作（确定的比较操作是一个比较操作通过使用some,all,或者any限定）
SELECT * FROM clients WHERE clno &lt; ANY&nbsp;(SELECT clno FROM firms);
使用any的规则如下：
ANY返回true，如果子查询的比较操作至少有一个是是true。 ANY返回false,如果子查询返回为空行，或者比较任意一行都是false。 ANY的同义词是SOME,使用in等同于使用= ANY。
在这里，第一次的比较还是返回false，而第二此返回true，最后，子查询的结果是false，所以查询返回空行。使用all的规则是：
返回true，如果子查询返回空行记录，或者子查询的每一个结果比较都为true的话。 返回false，如果子查询至少有一个比较结果返回false。
在有些时候，查询的结果是子查询是否有一个结果返回。使用[NOT] EXISTS来判断结果集是否为空。如果你的子查询至少有一行返回，使用EXISTS返回true；否则，返回为false。还有一种用法，NOT EXISTS，如果子查询结果集为空，它返回为true，否则的话，返回为true。一般情况下，子查询跟随在[NOT] EXISTS后面，以select *开头。在这里，星号(*)不是表示列出所有的列，而是表示为列出一些列。
SELECT * FROM clients WHERE EXISTS &nbsp;(SELECT * FROM firms);
也可以把一个子查询，作为一个表名，放在from子句后面（就像oracle里面的用法一样，作为内部视图）：
select * from(select * from clients where job like'a%')as cl;//cl作为一个子查询的临时结果集：
当子查询被使用在from子句当中，关键字as是强制使用的；中间的临时结果必须被命名，作为外部的查询被引用。
mysql&gt; update clients set account_balance=(select sum(clno)*2 from firms) where
fname='sam';