当前位置：Gxlcms > 数据库问题 > Oracle 051 的几个题（oracle 11g）

Oracle 051 的几个题（oracle 11g）

时间：2021-07-01 10:21:17 帮助过：2人阅读

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
---------------------------------------------------------------------------
INTERVAL‘54-2‘YEARTOMONTH
---------------------------------------------------------------------------
INTERVAL‘11:12:10.1234567‘HOURTOSECOND
---------------------------------------------------------------------------
+25-00
+54-02
+00 11:12:10.123457

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)
--------------------------------------------------------------------------------
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‘<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
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- -------------- -------------
   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/

Sugeei

2015/8/28

Oracle 051 的几个题（oracle 11g）

标签：

Oracle 051 的几个题 （oracle 11g）

人气教程排行

Oracle 051 的几个题（oracle 11g）