Oracle备忘录（七）--综合

1. 管理员登录
c:\>sqlplus sys/你的密码 as sysdba  (密码认证)
c:\>sqlplus / as sysdba             (主机认证  优先)


2. 解锁
SQL>alter user scott account unlock;

3. 改密码
SQL>alter user scott identified by 新密码;


SQL> -- rownum 行号  伪列
SQL> --查询工资的前3位

SQL> /*
SQL> --关于行号(注意点)
SQL> --1. 按照默认的顺序生成
SQL> --2. rownum只能使用< <=, 不能> >=
SQL> -- */
SQL> select rownum, empno,ename,sal
  2  from emp
  3  order by sal desc;

    ROWNUM      EMPNO ENAME        SAL                                                                                  
---------- ---------- ---------- -----                                                                                  
         9       7839 KING        5000                                                                                  
        13       7902 FORD        3000                                                                                  
         8       7788 SCOTT       3000                                                                                  
         4       7566 JONES       2975                                                                                  
         6       7698 BLAKE       2850                                                                                  
	...      ...  ...         ...
已选择14行。

SQL> host cls

SQL> --第一题 查询工资的前3位
SQL> select rownum,empno,ename,sal
  2  from (select * from emp order by sal desc)
  3  where rownum<=3;

    ROWNUM      EMPNO ENAME        SAL                                                                                  
---------- ---------- ---------- -----                                                                                  
         1       7839 KING        5000                                                                                  
         2       7788 SCOTT       3000                                                                                  
         3       7902 FORD        3000                                                                                  


---不能使用>=
SQL> select rownum,empno,ename,sal
  2  from emp
  3  where rownum>=5 and rownum<=8;

未选定行


--Oracle分页查询
SQL> select *
  2  from (select rownum r,e1.*
  3  	      from (select * from emp order by sal) e1
  4  	      where rownum <=8
  5  	   )e2 
  6  where e2.r >=5; 
-- 这时的r>=5 是 e2的一个字段，而不是行数，r为e1的行数，所以在e1这个表，不能使用>=


         R      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                        
---------- ---------- ---------- --------- ---------- -------------- ----- ---------- ----------                        
         5       7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30                        
         6       7934 MILLER     CLERK           7782 23-1月 -82      1300                    10                        
         7       7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                        
         8       7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                        

SQL> host cls

SQL> --第二题
SQL> --查询出比部门平均工资高的员工
SQL> select e.empno,e.ename,e.sal,d.avgsal
  2  from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
  3  where e.deptno=d.deptno and e.sal>d.avgsal;

     EMPNO ENAME        SAL     AVGSAL                                                                                  
---------- ---------- ----- ----------                                                                                  
      7698 BLAKE       2850 1566.66667                                                                                  
      7499 ALLEN       1600 1566.66667                                                                                  
      7902 FORD        3000       2175                                                                                  
      7788 SCOTT       3000       2175                                                                                  
      7566 JONES       2975       2175                                                                                  
      7839 KING        5000 2916.66667                                                                                  

已选择6行。


---重要：
SQL> --相关子查询: 将主查询中的某个值  作为参数传递给子查询
SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
  2  from emp e
  3  where sal> (select avg(sal) from emp where deptno=e.deptno); --传入父查询的deptno

     EMPNO ENAME        SAL     AVGSAL                                                                                  
---------- ---------- ----- ----------                                                                                  
      7499 ALLEN       1600 1566.66667                                                                                  
      7566 JONES       2975       2175                                                                                  
      7698 BLAKE       2850 1566.66667                                                                                  
      7788 SCOTT       3000       2175                                                                                  
      7839 KING        5000 2916.66667                                                                                  
      7902 FORD        3000       2175                                                                                  

已选择6行。

SQL> host cls

SQL> --第三题，统计在某一年入职员工的数目

--RR表示后面两位数的年份
--decode(表达式,'条件',条件成立值,条件不成立值)
SQL> select
  2    sum(decode(to_char(hiredate,'RR'),'81',1,0))
  3  from emp
SQL> /

--我的解决方法：
SQL> select 
	sum(
		case to_char(hiredate,'yyyy')
			when '1981'
			then 1
			else 0
		end
            ) "1980"
     from emp

     

SQL> --组函数 行转列  wm_concat                                                                                                             
SQL> col 部门成员 for a50
SQL> select deptno 部门编号, wm_concat(ename) 部门成员 from emp group by deptno;

  部门编号 部门成员                                                                                                     
---------- --------------------------------------------------                                                           
        10 CLARK,KING,MILLER                                                                                            
        20 SMITH,FORD,ADAMS,SCOTT,JONES                                                                                 
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD                                                                         
SQL> spool off

传智播客---Oracle视频教程