sql经典实例笔记

1.1 检索所有行和列

   select * from emp

1.2 筛选行

  select * from emp where deptno = 10

1.3 查找满足多个查询条件的行

   select * from emp 
   where deptno = 10
              or comm is not null
              or sal <= 2000 and deptno = 20

1.4 筛选列

    select ename, deptno, sal from emp

直接指定列即可。

1.5 为列创建别名

     select sal as salary, comm as commission from emp

1.6 在where子句中引用别名
如果想直接这样使用，可能会出现问题:

     select sal as salary, comm as commission
     from emp
     where salary < 5000

原因：where子句比select子句先执行，所以当where子句执行时，select子句中的salary和commission还不存在。所以就会出错。但是from子句会先于where子句执行。
可以把查询包装为一个内嵌视图，这样就可以正确的引用别名列了:

     select * from (
        select sal as salary, comm as commission from emp 
      ) x
      where salary < 1000