oracle笔记--3.24

-------------------------以scott权限登录oracle---------------------

1.查找部门号为10的部门名称及部门所有员工的名字及薪水:select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10;

2.查找雇员和其领导的名字:select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;

3.查找与十号部门有相同工作的所有雇员的信息：select * from emp where job in (select distinct job from emp where deptno=10);

4.查找比十号部门所有员工工资都高的雇员的信息:select * from emp where sal>(select max(sal) from emp where deptno=30);

5.oracle分页1--根据rowid来分,效率适中:select * from (select a1.*,rownum rn from ((select * from emp) a1) where rownum<=10) where rn>=6;

6.oracle根据rowid分页:select * from emp where rowid in(select rid from(select * from (select rownum rn,rid from (select rowid rid,ename from emp where rownum<=10)) where rn>=6));

7.根据查询结果创建一张新的表:create table newEmp(eno,ename,esal) as select empno,ename,sal from emp;

8.oracle链接java:（1）加载驱动Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

（2）取得连接Connection conn = DriverManager.getConnection("jdbc:odbc:TestOra","scott","***********");

（3）创建statement :Statement sm = conn.createStatement();

（4）执行sql语句 ResultSet rs = sm.executeQuery("select * from emp where rowid in(select rid from(select * from (select rownum rn,rid from (select rowid rid,ename from emp where rownum<=10)) where rn>=6));");
           
(5)获得结果（6）关闭资源

9.insert 语句插入多条数据 insert int new_table values(select * from emp);

10.查询首字母大写其余字母小写(函数的使用upper,lower,substr等函数):select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;

11.在orcale测试的时候可以用一个虚拟的表dual:select mod(10,2) from dual;

12.查找入职超过300个月的员工的信息:select * from emp where sysdate>add_months(hiredate,300);

13.查询所有员工的入职时间:select trunc(sysdate-hiredate) as "入职时间",ename from emp;

14.查找所有1980年入职的员工信息:select * from emp where to_char(hiredate,'yyyy')=1980;

15.查找所有12月份入职的员工信息:select * from emp where to_char(hiredate,'mm')=12;

16.查找所有员工的工资:select ename,to_char(sal,'L9999.99') from emp;