Oracle 051 的几个题 (oracle 11g)
1. INTERVAL的用法
SELECT INTERVAL '300' MONTH,INTERVAL '54-2' YEAR TO MONTH,INTERVAL '11:12:10.1234567' HOUR TO SECOND FROM dual; |
查询结果为:
INTERVAL'300'MONTH |
2. ORDERBY
select distinct deptno, sal from emp order by 1; |
等价于
select distinct deptno, sal from emp order by deptno; |
select distinct deptno, sal from emp order by 2; |
等价于
select distinct deptno, sal from emp order by sal; |
- 在SELECT 子句中使用数据合并
emp表如下
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM |
7369 | SMITH | CLERK | 7902 | 17-DEC-80 | 800 |
|
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 | 1600 | 300 |
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 | 1250 | 500 |
7566 | JONES | MANAGER | 7839 | 02-APR-81 | 2975 |
|
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 | 1250 | 1400 |
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 | 2850 |
|
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 | 2450 |
|
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 | 3000 |
|
7839 | KING | PRESIDENT |
| 17-NOV-81 | 5000 |
|
查询语句如下:
select empno || ' ' || TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365) * sal + comm) from emp; |
查询结果为:
EMPNO||''||TO_CHAR(ROUND(ROUND(SYSDATE-HIREDATE)/365)*SAL+COMM) |
查询结果只有一列,为empno的值与后面的算术值的拼接。
说明:
a) 在SELECT中用“||” 实现数据合并
b) 查询结果错误。 COMM 值为空的行无计算结果
- Q-quote delimiter
查询语句
select q'[i am' ok "ey" ]' from dual; |
输出
i am' ok "ey" |
如下符号都可行
select q'<i am' ok "ey" >' from dual; select q'\i am' ok "ey" \' from dual; select q'{i am' ok "ey" }' from dual; select q'#i am' ok "ey" #' from dual; select q'*i am' ok "ey"*' from dual; select q'(i am' ok "ey")' from dual; select q'%i am' ok "ey"%' from dual; |
- USING
Select * from emp join dept using(deptno); |
查询结果为:
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC |
使oracle使用using指定的字段来做连接,而不是natural join连接中默认的两个。
参考http://blog.163.com/wkyuyang_001/blog/static/108021228200917598857/
补充:
- COUNT(*) , COUNT(column_name) , COUNT(DISTINCT)
COUNT(*) -all, include null
COUNT(column_name) - without null
COUNT(DISTINCT) -remove duplicate
- SQL 中各子句的执行顺序
a) FROM (查询数据来源)
b) WHERE (根据WHERE中的条件筛选行)
c) GROUP BY (执行分组)
d) HAVING (根据HAVING中的条件在各组内进行筛选)
e) SELECT (挑出指定列)
f) ORDER BY (排序)
Sugeei
2015/8/28