| 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(col umn_name) , COUNT(DISTINCT)?
COUNT(*) -all, include null
COUNT(column_name) - without null
COUNT(DISTINCT) -remove duplicate
?
SQL 中