语法:
SELECT [DISTINCT]
{
[ .] | * |
| [ [AS] ]
},……
FROM
{
| [[AS] ]
}
[WHERE ]
[GROUP BY [.],……,
[HAVING ] ]]
[ORDER BY
{ | } [ASC | DESC]
]
注:DISTINCT排除重复 pseudocolumn 伪列 column_alias 列别名 qualifier 限定符 predicate 判定
实例:
1、连接运算符
select ename ||' IS '|| (sal+1000) SAL_TEXT from emp;
注:(sal+1000) 运算优先级 要加括号 ,省略了as
2、DISTINCT运算符 (去掉重复行)
select distinct deptno from emp;
select distinct deptno ,job from emp;(作用于多列,列出每种不同的组合)
3、where子句限制
SQL> select * from emp
2 where sal>1100;
SQL> select * from emp
2 where sal in(1600,1500,1300);
SQL> select * from emp
2 where job like 'SAL%';
4、比较运算符
,>=,<>或!=,=
between … and … ,like , in()
\ 转义操作符 后跟符号表示原始意义 如:\_ 中_ 就不是代表任意单个字符,而是下划线
5、order by 子句排序
SQL> select * from emp
2 order by sal;
SQL> select empno "Employer Number",ename Name,(100+sal)*12 "Year Salary"
2 from emp
3 order by "Year Salary" desc;(列别名中有空格是需要用双引号“”将列别名引用)
6、group by 分组子句
*注意分组后,若是查询多列数据,则必须保证其它列返回行数与分组行数一致,保持对应关系 否则报错:ORA-00979: not a GROUP BY expression
或应该使用group by 却未使用时,报错:ORA-00937: not a single-group group function
SQL> select job,sum(sal)
2 from emp
3 group by job;
嵌套
SQL> select min(avg(sal)),max(avg(sal))
2 from emp
3 where job not like 'PRE%'
4 group by job;
7、having 子句
由于分组函数不能用于where 语句中 故用having来实现此功能
功能:显示符合条件的分组结果
SQL> select job,avg(sal)
2 from emp
3 group by job
4 having avg(sal)>=1500
5 order by 2; 2指代AVG(sal)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27662838/viewspace-749597/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27662838/viewspace-749597/