mysql笔记(11)--子查询

作者: 傻猫    分类: 弄数据    发布时间: 03-01 23:28    浏览次数: 4522     无留言

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');

select * from firms where clno in(select clno from clients );

select * from firms where clno =(select clno from clients  where job=’manager’);

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 < ANY (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  (SELECT * FROM firms);

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

select * from(select * from clients where job like'a%')as cl;//cl作为一个子查询的临时结果集:

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

mysql> update clients set account_balance=(select sum(clno)*2 from firms) where

fname='sam';

添加新评论