Oracle 051的几个题（oracle11g） - 数据库编程

TOP

Oracle 051的几个题（oracle11g）(一)

2015-11-21 01:31:15 来源: 作者: 【大中小】浏览:2次

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;

Oracle 051 的几个题（oracle 11g）
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 中

首页上一页 1 2 下一页尾页 1/2/2
【大中小】【打印】【繁体】【投稿】【收藏】【推荐】【举报】【评论】【关闭】【返回顶部】
分享到:
上一篇：HP-UNIX安装OraclePHCO-40381	下一篇：数据库基础知识概述

帐　　号:

密码: (新用户注册)

验证码:

表　　情:

内　　容: