Loading... <p class="MsoNormal"><span>CREATE TABLE clients (</span></p> <p class="MsoNormal"><span> clno INT,</span></p> <p class="MsoNormal"><span> fname VARCHAR(15),</span></p> <p class="MsoNormal"><span> lname VARCHAR(15),</span></p> <p class="MsoNormal"><span> job VARCHAR(15),</span></p> <p class="MsoNormal"><span> account_balance DECIMAL(7,2));</span></p> <p class="MsoNormal"><span> </span></p> <p class="MsoNormal"><span>INSERT INTO clients VALUES</span></p> <p class="MsoNormal"><span> (10, 'sam','smith','auditor',5525.75),</span></p> <p class="MsoNormal"><span> (20,'james','jones','manager',8960.25);</span></p> <p class="MsoNormal"><span> </span></p> <p class="MsoNormal"><span>CREATE TABLE firms (</span></p> <p class="MsoNormal"><span> clno INT,</span></p> <p class="MsoNormal"><span> company VARCHAR(15),</span></p> <p class="MsoNormal"><span> city VARCHAR(15));</span></p> <p class="MsoNormal"><span> </span></p> <p class="MsoNormal"><span>INSERT INTO firms VALUES</span></p> <p class="MsoNormal"><span> (10,'abc co','leduc'),</span></p> <p class="MsoNormal"><span> (20,'def ltd','nisku'),</span></p> <p class="MsoNormal"><span> (30,'ghi inc','nisku');</span></p> <p><span style="font-size: 10.5pt;">select * from firms where clno in(select clno from clients );</span></p> <p><span style="font-size: 10.5pt;">select * from firms where clno =(select clno from clients <span> </span>where job=’manager’);</span></p> <p><span style="font-size: 10.5pt;">select fname,lname from clients where clno=(select max(clno) from firms);</span></p> <p><span style="font-size: 10.5pt;">select fname,lname from clients where clno=(select avg(clno) from firms);</span></p> <p><span>如果内部查询返回空结果集,子查询会返回错误</span></p> <p class="MsoNormal"><span>子查询共有三种类型,区分它们的方法是根据返回结果的列数和行数。</span></p> <p class="MsoNormal"><span>如果一个子查询返回仅仅一列一行数据,被成为数量级子查询。一个数量级子查询可以用在任何适合数据值比较的地方(一个列值或者文本)。它常常被使用在</span><span>where</span><span>子句当中,跟随在比较操作符号后面。</span><span><br /></span><span>如果子查询返回多个列,并且是一行记录的话,它被称为行级子查询。行级子查询是数量级子查询的发展,可以用在任何适合数量级子查询的地方。</span><span><br /></span><span>最后,如果一个子查询返回多个列和多行的话,它被称为表级子查询。表级子查询可以用作涉及到表的查询操作,包含在</span><span>from</span><span>子句当中。它也常常被用在</span><span>where</span><span>子句当中,后面使用</span><span>in</span><span>或者</span><span>exist</span><span>或者一个确定的比较操作(确定的比较操作是一个比较操作通过使用</span><span>some,all,</span><span>或者</span><span>any</span><span>限定)</span></p> <p class="MsoNormal"><span>SELECT * FROM clients WHERE clno < ANY<span> </span>(SELECT clno FROM firms);</span></p> <p class="MsoNormal"><span>使用</span><span>any</span><span>的规则如下:</span></p> <p class="MsoNormal"><span>ANY</span><span>返回</span><span>true</span><span>,如果子查询的比较操作至少有一个是是</span><span>true</span><span>。</span><span><br />ANY</span><span>返回</span><span>false,</span><span>如果子查询返回为空行,或者比较任意一行都是</span><span>false</span><span>。</span><span><br />ANY</span><span>的同义词是</span><span>SOME,</span><span>使用</span><span>in</span><span>等同于使用</span><span>= ANY</span><span>。</span></p> <p class="MsoNormal"><span>在这里,第一次的比较还是返回</span><span>false</span><span>,而第二此返回</span><span>true</span><span>,最后,子查询的结果是</span><span>false</span><span>,所以查询返回空行。使用</span><span>all</span><span>的规则是:</span></p> <p class="MsoNormal"><span>返回</span><span>true</span><span>,如果子查询返回空行记录,或者子查询的每一个结果比较都为</span><span>true</span><span>的话。</span><span><br /></span><span>返回</span><span>false</span><span>,如果子查询至少有一个比较结果返回</span><span>false</span><span>。</span></p> <p class="MsoNormal" style="text-indent: 21pt;"><span>在有些时候,查询的结果是子查询是否有一个结果返回。使用</span><span>[NOT] EXISTS</span><span>来判断结果集是否为空。如果你的子查询至少有一行返回,使用</span><span>EXISTS</span><span>返回</span><span>true</span><span>;否则,返回为</span><span>false</span><span>。还有一种用法,</span><span>NOT EXISTS</span><span>,如果子查询结果集为空,它返回为</span><span>true</span><span>,否则的话,返回为</span><span>true</span><span>。一般情况下,子查询跟随在</span><span>[NOT] EXISTS</span><span>后面,以</span><span>select *</span><span>开头。在这里,星号</span><span>(*)</span><span>不是表示列出所有的列,而是表示为列出一些列。</span></p> <p class="MsoNormal"><span>SELECT * FROM clients WHERE EXISTS <span> </span>(SELECT * FROM firms);</span></p> <p class="MsoNormal"><span>也可以把一个子查询,作为一个表名,放在</span><span>from</span><span>子句后面(就像</span><span>oracle</span><span>里面的用法一样,作为内部视图):</span></p> <p class="MsoNormal"><span>select * from(select * from clients where job like'a%')as cl;//</span><span>cl</span><span>作为一个子查询的临时结果集:</span></p> <p class="MsoNormal"><span>当子查询被使用在</span><span>from</span><span>子句当中,关键字</span><span>as</span><span>是强制使用的;中间的临时结果必须被命名,作为外部的查询被引用。</span></p> <p class="MsoNormal"><span>mysql> update clients set account_balance=(select sum(clno)*2 from firms) where</span></p> <p class="MsoNormal"><span>fname='sam';</span></p> 相关文章 无相关文章 Last modification:March 2nd, 2012 at 08:54 pm © 允许规范转载 Support 如果觉得我的文章对你有用,请随意赞赏 ×Close Appreciate the author Sweeping payments Pay by AliPay Pay by WeChat