Oracle 051 的几个题 (oracle 11g)
来源:互联网 发布:手机虚拟键盘软件 编辑:程序博客网 时间:2024/05/14 14:34
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
---------------------------------------------------------------------------
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/
补充:
- 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
- Oracle 051 的几个题 (oracle 11g)
- Oracle 11g安装时遇到的几个概念
- oracle 11g RAC 中几个重要参数的解释
- Oracle的安装(11g)
- ORACLE 11g 的安装
- oracle 11g的卸载
- ORACLE 11g的安装
- Oracle 11g的卸载
- oracle 11g 的安装
- Oracle 11g的卸载
- Oracle 11g的安装
- Oracle 11g 的卸载
- oracle 10g与oracle 11g的不同
- Oracle 11g安装后几个重要文件路径
- oracle 11g ctss 集群时间同步服务几个要点
- Oracle 11g R2在windows 7(32)上安装后的几个服务说明
- oracle 11g 中 (oracle 10g) crsctl 的 替换命令
- oracle SGA(11g)
- Spring-IOC简单原理 + 实现
- python bug
- 面试知识储备_操作系统:处理机调度与死锁
- asp.net mvc文件上传
- ERROR ITMS-90032: "Invalid Image Path - No image found at the path referenced under key 'CFBundkeIco
- Oracle 051 的几个题 (oracle 11g)
- flash脚本as3的位运算符
- 能耗折算标准煤公式
- 关于memset函数
- 第六章:数组和字符串
- js,php 使用正则表达式解析GPS数据
- dao、pojo、javabean
- php第一阶段html_table(2)
- FilenameFilter总结