orcal学习笔记之子查询，dual表，

子查询
嵌套语句
形式：
select 字段列表 form 表 where =(select…);
分类：
—单行子查询
—多行子查询
1.单行子查询
返回一行数据的子查询语句

例：查询与SMITH同部门的所有员工

 select * from emp where deptno=(select deptno from emp where ename='SMITH';);

2.多行子查询 in,all, any

例1：查询和部门10的工作相同的雇员的名字、岗位工资、部门号

select ename,job,sal,deptno from emp where job in(select job from emp where deptno=10);

 例2：查询工资比任意一个部门的平均工资高的员工信息。

select * from emp where sal>any(select avg(sal) from emp group by deptno);

 例3；查询工资比所有部门的平均工资都高的员工信息。


select * from emp where sal>all(select avg(sal) from emp group by deptno);

3.多列子查询
在字查询的结果中返回多个列
–如果返回的是一列，则使用单行比较操作符（=，>=,<=,<>）
–如果返回的是多列，则使用多行比较操作符（in,any,all）

 例：查询和scott相同部门相同职位的员工

select ename,job,deptno from emp where (deptno,job)=
（select deptno,job from emp where ename=‘SCOTT’）;
例：查询每个部门最低薪水的员工

select empno,ename,job,sal,deptno from emp where(deptno,sal) in(select deptno,min(sal) from emp group by deptno);

习题：

1. 查询最高工资员工的名字，工作岗位

  select ename,job,from emp where sal=(select max(sal) from emp):

2. 算出部门 30 中得到最多奖金的员工姓名

   select ename from emp where deptno=30 and comm=
      (select max(comm) from emp where deptno=30);

3. 找出工资比ford高的员工的信息

   select * from emp where sal>(select sal from emp where ename='FORD');

4. 找出不在部门20，且比部门20的任何一个员工的工资都高的员工的姓名和所在部门名称。

  1) select ename,danem from emp e join dept d on e.deptno=d.deptno
   where e.deptno!=20 and e.sal>all(select sal from emp where deptno=20);
    2) select ename,danem from emp e join dept d on e.deptno=d.deptno
   where e.deptno!=20 and e.sal>(select max(sal) from emp where deptno=20);

5. 查询与SMITH的部门和岗位完全相同的所有雇员。

select ename from emp where(deptno,job)=
(select deptno,job from emp where ename=‘SMITH’);

6. 查询每个部门工资最高的员工

select ename from emp where sal in(select max(sal) from emp group by deptno);

7. 显示员工‘FORD’的上级

   1)select ename from emp where mgr=(select mgr from emp where ename='FORD');
     2)select m.ename from emp e join emp m on e.mgr=m.mgr where e.ename='FORD';

8. 查询工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

1)select ename,sal,deptno from emp where sal>
(select max(sal) from emp where deptno=30);
2)select ename,sal,deptno from emp where sal>all
(select sal from emp where deptno=30);

9. 查询工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

   select ename,sal,deptno from emp where sal>any
          (select sal from emp where deptno=30);

四、dual表

1. dual表所有者是SYS，可被所有用户使用。
   2.dual表包含一个字段dummy，只有一条记录，值为x。
   作用：当你需要数据库返回一个并不存在于任何表中的常熟、伪字段或表达式

select sysdate from dual;//格式化输出日期
select user from dual;//查看当前连接用户
select dbms_random.random from dual;
select dbms_random.value from dual;//返回一个具有38位精度的数值，范围从0.0到1.0，但不包括1.0：

五。数学函数。

  1.round( ,n):四舍五入
  2.trunc( ,n):取整
  3.dbms_random.random/dbms_random.value(x,y):随机获取一个数
  4.ceil(n):对n向上取整
  5.floor（n）:对N向下取整
 

例：

      1）select round(45.923,2),round(45.923,0),round(45.923,-1) from dual;
     2） select trunc(45.923,2),trunc(45.923),trunc（45.923，-1） from dual;
      3）select dbms_random.random from dual;
      4）select dbms_random.value(1,20) from dual;

六、字符串函数
函数 作用
lower(column|expression) 字符串转化为小写
upper() 字符串转化为大写
initcap() 字符串首字母大写，其它字母小写
concat（column1|expression1,
coulmn2|expression2） 连接两个字符，等价于||
substr(expression,m[,n]) 从字符串第M个位置开始，连续取n个字符， 。 m为负数，倒着第m个开始取，

事例

ltrim(column|expression [,c]) 将字符串左侧字符c删除，
ritrim() 将右侧c删除
concat(‘good’,‘morning’) goodmoring
length(‘string’) 6
instr(‘string’,‘r’) 3
ltrim(’ sring’) ‘string’
rtrim(‘string’,‘g’) ‘strin’

事例：1.对于所有工作年限少于70个月的员工，输出员工号，雇佣日期，工作年限（月），6个月试用期结束的时间，开始上班以后的第一个星期五。

  select empno,hiredate, months_between(sysdate, hiredate), add_months(hiredate, 6),next_day(hiredate,'星期五') from emp where months_between(sysdate,hiredate)<461;

七。类型转换
函数 作用
to_char(number|date[,‘fmt’) 将数值或日期转换为字符串
to_number(char) 将字符串转换为数字
to_date(char,[‘fmt’]) 把字符串转换为日期，缺省格式DD-MON-YY

例：

 1.select empno, to_char (hiredate,'mm/yy') from emp where ename='BLAKE';
2.select to_char (sal,'$99,999') salary from emp where ename='ALLEN';//  1600----&1,600
3.select extract(month from date'2003-08-22')  from dual;//用于从date类型或interval类型中截取到特定的部分；

4.select extract(hour from timestamp '2013-11-30 20:18:20') from dual;//截取小时

5.select extract(year from systimestamp) year from dual;//截取当前年份

获取两个日期之间的具体时间间隔，可以用extract()函数；

select extract(day from dt2-dt1) day,extract(hour from dt2-dt1) hour,extract(minute from dt2-dt1) minute,extract(second from dt2-dt1) second from( select to_timestamp('2013-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1,to_timestamp('2013-11-30 22:08:46', 'yyyy-mm-dd hh24:mi:ss') dt2 from dual);