Oracle 051的几个题（oracle11g）
| SELECT INTERVAL '300' MONTH,INTERVAL '54-2' YEAR TO MONTH,INTERVAL '11:12:10.1234567' HOUR TO SECOND FROM dual; |
| INTERVAL'300'MONTH --------------------------------------------------------------------------- INTERVAL'54-2'YEARTOMONTH --------------------------------------------------------------------------- INTERVAL'11:12:10.1234567'HOURTOSECOND --------------------------------------------------------------------------- +25-00 +54-02 +00 11:12:10.123457 |
| 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; |
1. INTERVAL的用法
查询结果为：
2. ORDERBY
等价于
等价于
在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) -------------------------------------------------------------------------------- 7369 7499 56300 7521 44250 7566 7654 43900 7698 7782 7788 7839 7844 51000 7876 7900 7902 7934 |
查询结果只有一列，为empno的值与后面的算术值的拼接。
说明：
a) 在SELECT中用“||” 实现数据合并
b) 查询结果错误。 COMM 值为空的行无计算结果
Q-quote delimiter
查询语句
|
select q'[i am' ok "ey" ]' from dual; |
输出
|
i am' ok "ey" |
如下符号都可行
|
select q' 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 ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- -------------- ------------- 10 7782 CLARK MANAGER 7839 09-JUN-81 2450 ACCOUNTING NEW YORK 10 7839 KING PRESIDENT 17-NOV-81 5000 ACCOUNTING NEW YORK 10 7934 MILLER CLERK 7782 23-JAN-82 1300 ACCOUNTING NEW YORK 20 7566 JONES MANAGER 7839 02-APR-81 2975 RESEARCH DALLAS 20 7902 FORD ANALYST 7566 03-DEC-81 3000 RESEARCH DALLAS 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 RESEARCH DALLAS 20 7369 SMITH CLERK 7902 17-DEC-80 800 RESEARCH DALLAS 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 RESEARCH DALLAS 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 SALES CHICAGO 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 SALES CHICAGO 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 SALES CHICAGO 30 7900 JAMES CLERK 7698 03-DEC-81 950 SALES CHICAGO 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 SALES CHICAGO 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 SALES CHICAGO |
使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 (排序)