Oracle学习02【持续更新】

前端之家收集整理的这篇文章主要介绍了Oracle学习02【持续更新】前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

Oracle继续学习中。。。。。。

查询职位是"MANAGER"或职位不是"ANALYST"的员工(方式一)

select * from emp where job='MANAGER' or not(job='ANALYST'); @H_403_6@
  1. sql> select * from emp where job='MANAGER' or not(job='ANALYST');
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7369 SMITH CLERK 7902 17-12月-80 800 20
  6. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  7. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  8. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  9. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  10. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  11. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  12. 7839 KING PRESIDENT 17-11月-81 5000 10
  13. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  14. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  15. 7900 JAMES CLERK 7698 03-12月-81 950 30
  16. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  17.  
  18. 已选择12行。
查询职位是"MANAGER"或职位不是"ANALYST"的员工(方式二):select * from emp where job='MANAGER' or job!='ANALYST';
  1. sql> select * from emp where job='MANAGER' or job !='ANALYST';
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7369 SMITH CLERK 7902 17-12月-80 800 20
  6. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  7. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  8. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  9. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  10. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  11. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  12. 7839 KING PRESIDENT 17-11月-81 5000 10
  13. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  14. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  15. 7900 JAMES CLERK 7698 03-12月-81 950 30
  16. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  17.  
  18. 已选择12行。
查询员工信息,按薪水升序排序:select * from emp order by sal desc; @H_403_6@
  1. sql> select * from emp order by sal desc;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7839 KING PRESIDENT 17-11月-81 5000 10
  6. 7902 FORD ANALYST 7566 03-12月-81 3000 20
  7. 7788 SCOTT ANALYST 7566 19-4 -87 3000 20
  8. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  9. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  10. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  11. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  12. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  13. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  14. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  15. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  16. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  17. 7900 JAMES CLERK 7698 03-12月-81 950 30
  18. 7369 SMITH CLERK 7902 17-12月-80 800 20
  19.  
  20. 已选择14行。
查询员工信息,按入职日期降序排序:select * from emp order by hiredate desc; @H_403_6@
  1. sql> select * from emp order by hiredate desc;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  6. 7788 SCOTT ANALYST 7566 19-4 -87 3000 20
  7. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  8. 7902 FORD ANALYST 7566 03-12月-81 3000 20
  9. 7900 JAMES CLERK 7698 03-12月-81 950 30
  10. 7839 KING PRESIDENT 17-11月-81 5000 10
  11. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  12. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  13. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  14. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  15. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  16. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  17. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  18. 7369 SMITH CLERK 7902 17-12月-80 800 20
  1. 已选择14
order by后面可以跟列名、别名、表达式、列号 @H_403_6@ select * from emp order by sal desc; @H_403_6@

select empno "编号",ename "姓名",sal "工 资" from emp order by "工 资" desc;

  1. sql> select empno "编号",sal "工 资" from emp order by "工 资" desc
  2. ;
  3.  
  4. 编号 姓名
  5. ---------- -------------------- ----------
  6. 7839 KING 5000
  7. 7902 FORD 3000
  8. 7788 SCOTT 3000
  9. 7566 JONES 2975
  10. 7698 BLAKE 2850
  11. 7782 CLARK 2450
  12. 7499 ALLEN 1600
  13. 7844 TURNER 1500
  14. 7934 MILLER 1300
  15. 7521 WARD 1250
  16. 7654 MARTIN 1250
  17. 7876 ADAMS 1100
  18. 7900 JAMES 950
  19. 7369 SMITH 800
  20.  
  21. 已选择14行。

select empno "编号",sal "工资",sal*12 "年薪" from emp order by sal*12 desc;

  1. sql> select empno "编号",sal*12 "年薪" from emp order by
  2. sal*12 desc;
  3.  
  4. 编号 姓名 工资 年薪
  5. ---------- -------------------- ---------- ----------
  6. 7839 KING 5000 60000
  7. 7902 FORD 3000 36000
  8. 7788 SCOTT 3000 36000
  9. 7566 JONES 2975 35700
  10. 7698 BLAKE 2850 34200
  11. 7782 CLARK 2450 29400
  12. 7499 ALLEN 1600 19200
  13. 7844 TURNER 1500 18000
  14. 7934 MILLER 1300 15600
  15. 7521 WARD 1250 15000
  16. 7654 MARTIN 1250 15000
  17. 7876 ADAMS 1100 13200
  18. 7900 JAMES 950 11400
  19. 7369 SMITH 800 9600
  20.  
  21. 已选择14行。
select empno "编号",sal*12 "年薪" from emp order by 4 desc; @H_403_6@
  1. sql> select empno "编号",sal*12 "年薪" from emp order by
  2. 4 desc;
  3.  
  4. 编号 姓名 工资 年薪
  5. ---------- -------------------- ---------- ----------
  6. 7839 KING 5000 60000
  7. 7902 FORD 3000 36000
  8. 7788 SCOTT 3000 36000
  9. 7566 JONES 2975 35700
  10. 7698 BLAKE 2850 34200
  11. 7782 CLARK 2450 29400
  12. 7499 ALLEN 1600 19200
  13. 7844 TURNER 1500 18000
  14. 7934 MILLER 1300 15600
  15. 7521 WARD 1250 15000
  16. 7654 MARTIN 1250 15000
  17. 7876 ADAMS 1100 13200
  18. 7900 JAMES 950 11400
  19. 7369 SMITH 800 9600
  20.  
  21. 已选择14行。
查询员工信息,按佣金升序或降序排列,null值放后面(nulls last) @H_403_6@

select * from emp order by comm asc;

  1. sql> select * from emp order by comm asc;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  6. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  7. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  8. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  9. 7788 SCOTT ANALYST 7566 19-4 -87 3000 20
  10. 7839 KING PRESIDENT 17-11月-81 5000 10
  11. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  12. 7900 JAMES CLERK 7698 03-12月-81 950 30
  13. 7902 FORD ANALYST 7566 03-12月-81 3000 20
  14. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  15. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  16. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  17. 7369 SMITH CLERK 7902 17-12月-80 800 20
  18. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  19.  
  20. 已选择14行。
select * from emp order by comm desc nulls last; @H_403_6@
  1. sql> select * from emp order by comm desc nulls last;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  6. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  7. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  8. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  9. 7788 SCOTT ANALYST 7566 19-4 -87 3000 20
  10. 7839 KING PRESIDENT 17-11月-81 5000 10
  11. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  12. 7900 JAMES CLERK 7698 03-12月-81 950 30
  13. 7902 FORD ANALYST 7566 03-12月-81 3000 20
  14. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  15. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  16. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  17. 7369 SMITH CLERK 7902 17-12月-80 800 20
  18. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  19.  
  20. 已选择14行。
查询员工信息,按工资降序排列,相同工资的员工再按入职时间降序排列 @H_403_6@

select * from emp order by sal desc,hiredate desc;

  1. sql> select * from emp order by sal desc,hiredate desc;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7839 KING PRESIDENT 17-11月-81 5000 10
  6. 7788 SCOTT ANALYST 7566 19-4 -87 3000 20
  7. 7902 FORD ANALYST 7566 03-12月-81 3000 20
  8. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  9. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  10. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  11. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  12. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  13. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  14. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  15. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  16. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  17. 7900 JAMES CLERK 7698 03-12月-81 950 30
  18. 7369 SMITH CLERK 7902 17-12月-80 800 20
  19.  
  20. 已选择14行。
测试LOWER/UPPER/INITCAP函数 @H_403_6@ select lower('Hello World') "转小写",upper('Hello World') "转大写",initcap('hello world') "首字母大写"from dual; @H_403_6@
  1. sql> select lower('Hello World') "转小写",initcap(
  2. 'hello world') "首字母大写" from dual;
  3.  
  4. 转小写 转大写 首字母大写
  5. ---------------------- ---------------------- ----------------------
  6. hello world HELLO WORLD Hello World
测试CONCAT/SUBSTR函数,从1开始: @H_403_6@ select concat('hello',' world') "字符串拼接",substr('hello world',7,11) "字符串截取"from dual; @H_403_6@
  1. sql> select concat('hello',11) "字
  2. 符串截取" from dual;
  3.  
  4. 字符串拼接 字符串截取
  5. ---------------------- ----------
  6. hello world world
测试LENGTH/LENGTHB函数: @H_403_6@

select length('中CHINA') "字符",lengthb('中CHINA') "字节"from dual;

sql> select length('中CHINA') "字符",lengthb('中CHINA') "字节" from dual;

  1. 字符 字节
  2. ---------- ----------
  3. 6 8

length:不管中英文,都是1位

lengthb:英文,是1位;中文是3位【unicode即utf-8】 @H_403_6@ 测试INSTR/LPAD/RPAD函数 @H_403_6@
  1. sql> select instr('hello world','o'),lpad('haha',10,'#'),rpad('hehe','@') fro
  2. m dual;
  3.  
  4. INSTR('HELLOWORLD','O') LPAD('HAHA','#') RPAD('HEHE','@')
  5. ----------------------- -------------------- --------------------
  6. 5 ######haha hehe@@@@@@
测试TRIM/REPLACE函数 @H_403_6@

select trim('x' from 'xxxHELLOxxxWORLDxxx'),replace('abcd','c','$')from dual;

  1. sql> select trim('x' from 'xxxHELLOxxxWORLDxxx'),'$') from du
  2. al;
  3.  
  4. TRIM('X'FROM'XXXHELLOXXXWO REPLACE(
  5. -------------------------- --------
  6. HELLOxxxWORLD ab$d
select trim(' ' from ' HELLO WORLD ')from dual; @H_403_6@
  1. sql> select trim(' ' from ' HELLO WORLD ') from dual;
  2.  
  3. TRIM(''FROM'HELLOWORLD')
  4. --------------------------
  5. HELLO WORLD
测试ROUND/TRUNC/MOD函数 @H_403_6@ select round(3.1415926,3),trunc(3.1415926,mod(1000,300)from dual; @H_403_6@
  1. sql> select round(3.1415926,300) from dual;
  2.  
  3. ROUND(3.1415926,3) TRUNC(3.1415926,3) MOD(1000,300)
  4. ------------------ ------------------ -------------
  5. 3.142 3.141 100
select round(sysdate,'month') from dual; @H_403_6@
  1. sql> select round(sysdate,'month') from dual;
  2.  
  3. ROUND(SYSDATE,--------------
  4. 01-7 -16
select trunc(sysdate,'month')from dual; @H_403_6@
  1. sql> select trunc(sysdate,'month') from dual;
  2.  
  3. TRUNC(SYSDATE,--------------
  4. 01-6 -16
显示昨天,今天,明天的日期 @H_403_6@ select sysdate-1 "昨天",sysdate "今天",sysdate+1 明天from dual; @H_403_6@
  1. sql> select sysdate-1 "昨天",sysdate+1 明天 from dual;
  2.  
  3. 昨天 今天 明天
  4. -------------- -------------- --------------
  5. 19-6 -16 20-6 -16 21-6 -16
以年和月形式显示员工近似工龄 @H_403_6@ select ename "姓名",sysdate-hiredate "日工龄",(sysdate-hiredate)/30 "月工龄",(sysdate-hiredate)/365 "年工龄"from emp; @H_403_6@
  1. sql> select ename "姓名",(sysdate-hiredate)/30 "月工龄
  2. ",(sysdate-hiredate)/365 "年工龄" from emp;
  3.  
  4. 姓名 日工龄 月工龄 年工龄
  5. -------------------- ---------- ---------- ----------
  6. SMITH 12969.987 432.332899 35.5342109
  7. ALLEN 12904.987 430.166232 35.3561287
  8. WARD 12902.987 430.099566 35.3506492
  9. JONES 12863.987 428.799566 35.2437999
  10. MARTIN 12684.987 422.832899 34.753389
  11. BLAKE 12834.987 427.832899 35.1643479
  12. CLARK 12795.987 426.532899 35.0574985
  13. SCOTT 10655.987 355.199566 29.1944848
  14. KING 12634.987 421.166232 34.6164027
  15. TURNER 12704.987 423.499566 34.8081835
  16. ADAMS 10621.987 354.066232 29.1013342
  17. JAMES 12618.987 420.632899 34.572567
  18. FORD 12618.987 420.632899 34.572567
  19. MILLER 12567.987 418.932899 34.432841
  20.  
  21. 已选择14行。
使用MONTHS_BETWEEN函数,以精确月形式显示员工工龄 @H_403_6@ select ename "姓名",(sysdate-hiredate)/30 "近似月工龄",months_between(sysdate,hiredate) "精确月工龄"from emp; @H_403_6@
  1. sql> select ename "姓名",months_between(sysda
  2. te,hiredate) "精确月工龄" from emp;
  3.  
  4. 姓名 近似月工龄 精确月工龄
  5. -------------------- ---------- ----------
  6. SMITH 432.332915 426.128627
  7. ALLEN 430.166248 424
  8. WARD 430.099581 423.967337
  9. JONES 428.799581 422.612498
  10. MARTIN 422.832915 416.773788
  11. BLAKE 427.832915 421.644756
  12. CLARK 426.532915 420.386692
  13. SCOTT 355.199581 350.064111
  14. KING 421.166248 415.128627
  15. TURNER 423.499581 417.41895
  16. ADAMS 354.066248 348.935079
  17. JAMES 420.632915 414.58024
  18. FORD 420.632915 414.58024
  19. MILLER 418.932915 412.935079
  20.  
  21. 已选择14行。

测试ADD_MONTHS函数

select add_months(sysdate,3)from dual;

  1. sql> select add_months(sysdate,3) from dual;
  2.  
  3. ADD_MONTHS(SYS
  4. --------------
  5. 20-9 -16
测试NEXT_DAY函数中文平台】 @H_403_6@

select next_day(sysdate,'星期三')from dual;

  1. sql> select next_day(sysdate,'星期三') from dual;
  2.  
  3. NEXT_DAY(SYSDA
  4. --------------
  5. 22-6 -16
从今天开始算,下一个星期三是多少号? @H_403_6@

select next_day(next_day(sysdate,'星期三'),'星期三')from dual;

  1. sql> select next_day(next_day(sysdate,'星期三') from dual;
  2.  
  3. NEXT_DAY(NEXT_
  4. --------------
  5. 29-6 -16
日期隐式转换,读字符串,判断是否符合日期格式要求,再隐式转换,效率低 @H_403_6@ select ename,hiredate from emp where hiredate='17-12月-80'; @H_403_6@ 隐式转换 @H_403_6@ '123'字符串-->123数值 @H_403_6@ '1-1月-13'--->1-1月-13 @H_403_6@ 123---------->'123' @H_403_6@ 1-1月-13----->'1-1月-13' @H_403_6@ 日期显式转换,效率高,项目中提倡使用 @H_403_6@ select ename,to_char(hiredate,'yyyy-mm-dd')from emp where hiredate=to_date('1980-12-17','yyyy-mm-dd'); @H_403_6@
  1. sql> select ename,'yyyy-mm-dd') from emp where hiredate=to_date
  2. ('1980-12-17','yyyy-mm-dd');
  3.  
  4. ENAME TO_CHAR(HIREDATE,'YY
  5. ---------- --------------------
  6. SMITH 1980-12-17
使用to_char()函数将日期转成字符串,显示如下格式:2012-12-1 今天是 星期六 20:12:47 @H_403_6@ select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual; @H_403_6@
  1. sql> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
  2.  
  3. TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
  4. --------------------------------------
  5. 2016-06-20 23:48:23
select to_char(sysdate,'YYYY-MM-DD-DAY HH:MI:SS AM')from dual; @H_403_6@
  1. sql> select to_char(sysdate,'YYYY-MM-DD-DAY HH:MI:SS AM')from dual;
  2.  
  3. TO_CHAR(SYSDATE,'YYYY-MM-DD-DAYHH:MI:SSAM')
  4. --------------------------------------------------------------------
  5. 2016-06-20-星期一 11:50:02 下午
select to_char(sysdate,'yyyy-mm-dd "今天是" day hh24:mi:ss') "时间"from dual; @H_403_6@ 常量一定要用""双引号定界 @H_403_6@
  1. sql> select to_char(sysdate,'yyyy-mm-dd "今天是" day hh24:mi:ss') "时间"from dua
  2. l;
  3.  
  4. 时间
  5. --------------------------------------------------------------------------------
  6.  
  7. 2016-06-20 今天是 星期一 23:50:35
@H_403_6@ 显示员工工资,加上$或¥符号和千位符 @H_403_6@ select ename,to_char(sal,'$9,999') from emp; @H_403_6@ select ename,'L9,999') from emp; @H_403_6@
  1. sql> select ename,999') from emp;
  2.  
  3. ENAME TO_CHAR(SAL,'$
  4. ---------- --------------
  5. SMITH $800
  6. ALLEN $1,600
  7. WARD $1,250
  8. JONES $2,975
  9. MARTIN $1,250
  10. BLAKE $2,850
  11. CLARK $2,450
  12. SCOTT $3,000
  13. KING $5,000
  14. TURNER $1,500
  15. ADAMS $1,100
  16. JAMES $950
  17. FORD $3,000
  18. MILLER $1,300
  19.  
  20. 已选择14行。
  1. sql> select ename,999')
  2. ---------- --------------------------------
  3. SMITH ¥800
  4. ALLEN ¥1,600
  5. WARD ¥1,250
  6. JONES ¥2,975
  7. MARTIN ¥1,250
  8. BLAKE ¥2,850
  9. CLARK ¥2,450
  10. SCOTT ¥3,000
  11. KING ¥5,000
  12. TURNER ¥1,500
  13. ADAMS ¥1,100
  14. JAMES ¥950
  15. FORD ¥3,000
  16. MILLER ¥1,300
  17.  
  18. 已选择14行。
使用to_number()函数将字符串转成数字 @H_403_6@ select to_number('123')+100 "结果"from dual; @H_403_6@
  1. sql> select to_number('123')+100 "结果" from dual;
  2.  
  3. 结果
  4. ----------
  5. 223
使用nul函数统计员工年收入 @H_403_6@ select ename,12*sal+nvl(comm,0)from emp; @H_403_6@
  1. sql> select ename,0) from emp;
  2.  
  3. ENAME 12*SAL+NVL(COMM,0)
  4. ---------- ------------------
  5. SMITH 9600
  6. ALLEN 19500
  7. WARD 15500
  8. JONES 35700
  9. MARTIN 16400
  10. BLAKE 34200
  11. CLARK 29400
  12. SCOTT 36000
  13. KING 60000
  14. TURNER 18000
  15. ADAMS 13200
  16. JAMES 11400
  17. FORD 36000
  18. MILLER 15600
  19.  
  20. 已选择14行。
使用nul2函数统计员工年收入 @H_403_6@ 使用nullif函数,比较10和10.0是否相同 @H_403_6@ select nullif(10,10.0)from dual; @H_403_6@ @H_403_6@ 使用case表达式,职位是总载的,工资+1000;职位是经理的,工资+800;职位是其它的,工资+400 @H_403_6@ select ename,job,sal,@H_403_6@ case job @H_403_6@ when 'PRESIDENT' then sal+1000 @H_403_6@ when 'MANAGER' then sal+800 @H_403_6@ else sal+400 @H_403_6@ end "涨后薪水" @H_403_6@

from emp;

  1. sql> select ename,2 case job
  2. 3 when 'PRESIDENT' then sal+1000
  3. 4 when 'MANAGER' then sal+800
  4. 5 else sal+400
  5. 6 end "涨后薪水"
  6. 7 from emp;
  7.  
  8. ENAME JOB SAL 涨后薪水
  9. ---------- ---------- ----- ----------
  10. SMITH CLERK 800 1200
  11. ALLEN SALESMAN 1600 2000
  12. WARD SALESMAN 1250 1650
  13. JONES MANAGER 2975 3775
  14. MARTIN SALESMAN 1250 1650
  15. BLAKE MANAGER 2850 3650
  16. CLARK MANAGER 2450 3250
  17. SCOTT ANALYST 3000 3400
  18. KING PRESIDENT 5000 6000
  19. TURNER SALESMAN 1500 1900
  20. ADAMS CLERK 1100 1500
  21. JAMES CLERK 950 1350
  22. FORD ANALYST 3000 3400
  23. MILLER CLERK 1300 1700
  24.  
  25. 已选择14行。
数据库厂都支持ANSIsql标准 @H_403_6@ 使用decode函数,职位是总载的,工资+1000;职位是经理的,工资+800;职位是其它的,工资+400 @H_403_6@ select ename,sal 涨前薪水,@H_403_6@ decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) "涨后薪水" from emp; @H_403_6@ decode函数专用 @H_403_6@
  1. sql> select ename,2 decode(job,sal+400) "涨后薪水
  2. " from emp;
  3.  
  4. ENAME JOB 涨前薪水 涨后薪水
  5. ---------- ---------- ---------- ----------
  6. SMITH CLERK 800 1200
  7. ALLEN SALESMAN 1600 2000
  8. WARD SALESMAN 1250 1650
  9. JONES MANAGER 2975 3775
  10. MARTIN SALESMAN 1250 1650
  11. BLAKE MANAGER 2850 3650
  12. CLARK MANAGER 2450 3250
  13. SCOTT ANALYST 3000 3400
  14. KING PRESIDENT 5000 6000
  15. TURNER SALESMAN 1500 1900
  16. ADAMS CLERK 1100 1500
  17. JAMES CLERK 950 1350
  18. FORD ANALYST 3000 3400
  19. MILLER CLERK 1300 1700
  20.  
  21. 已选择14行。
员工总工资,平均工资 @H_403_6@ select sum(sal) "总工资",round(avg(sal),2) "平均工资"from emp; @H_403_6@
  1. sql> select sum(sal) "总工资",2) "平均工资" from emp;
  2.  
  3. 总工资 平均工资
  4. ---------- ----------
  5. 29025 2073.21
员工最高工资,最低工资 @H_403_6@ select max(sal) "最高工资",min(sal) "最低工资"from emp; @H_403_6@
  1. sql> select max(sal) "最高工资",min(sal) "最低工资" from emp;
  2.  
  3. 最高工资 最低工资
  4. ---------- ----------
  5. 5000 800
入职最早员工,入职最晚员工 @H_403_6@ select max(hiredate) "最晚入职员工",min(hiredate) "最早入职员工"from emp; @H_403_6@
  1. sql> select max(hiredate) "最晚入职员工",min(hiredate) "最早入职员工" from emp;
  2.  
  3.  
  4. 最晚入职员工 最早入职员工
  5. -------------- --------------
  6. 23-5 -87 17-12月-80
求员工总人数 @H_403_6@ select count(*) 员工总人数from emp; @H_403_6@
  1. sql> select count(*) 员工总人数 from emp;
  2.  
  3. 员工总人数
  4. ----------
  5. 14
统计有佣金的员工人数 @H_403_6@

select count(comm) 有佣金的员工人数from emp;

  1. sql> select count(comm) 有佣金的员工人数 from emp;
  2.  
  3. 有佣金的员工人数
  4. ----------------
  5. 4
分组函数统计null值 @H_403_6@ 统计公司有多少个部门 @H_403_6@
  1. sql> select count(distinct deptno) from emp;
  2.  
  3. COUNT(DISTINCTDEPTNO)
  4. ---------------------
  5. 3
@H_403_6@ 按部门求出部门平均工资 @H_403_6@ --以下sql语句是正确 @H_403_6@ select deptno,trunc(avg(sal),0)from empgroup by deptno; @H_403_6@
  1. sql> select deptno,0) from emp group by deptno;
  2.  
  3. DEPTNO TRUNC(AVG(SAL),0)
  4. ------ -----------------
  5. 30 1566
  6. 20 2175
  7. 10 2916
select trunc(avg(sal),0)from empgroup by deptno; @H_403_6@
  1. sql> select trunc(avg(sal),0) from emp group by deptno;
  2.  
  3. TRUNC(AVG(SAL),0)
  4. -----------------
  5. 1566
  6. 2175
  7. 2916
--以下sql语句是错误,原因在于select子句中的非组函数没有全部出现在group by子句中 @H_403_6@ select ename,deptno,0) @H_403_6@ from emp @H_403_6@ group by ename; @H_403_6@ @H_403_6@ 查询部门平均工资大于2000元的部门 @H_403_6@ select deptno,0) "部门平均工资" @H_403_6@ from emp @H_403_6@ group by deptno @H_403_6@ having trunc(avg(sal),0)>2000; @H_403_6@
  1. sql> select deptno,0) "部门平均工资"
  2. 2 from emp
  3. 3 group by deptno
  4. 4 having trunc(avg(sal),0)>2000;
  5.  
  6. DEPTNO 部门平均工资
  7. ------ ------------
  8. 20 2175
  9. 10 2916
@H_403_6@ 除10号部门外,查询部门平均工资大于2000元的部门 @H_403_6@ 方式一 @H_403_6@ select deptno,0)>2000 and deptno!=10; @H_403_6@
  1. sql> select deptno,0)>2000 and deptno!=10;
  2.  
  3. DEPTNO 部门平均工资
  4. ------ ------------
  5. 20 2175
方式二【先行过滤,再组过滤,性能较佳】 @H_403_6@ select deptno,0) "部门平均工资" @H_403_6@ from emp @H_403_6@ where deptno!=10 @H_403_6@ group by deptno @H_403_6@ having trunc(avg(sal),0)>2000; @H_403_6@
  1. sql> select deptno,0) "部门平均工资"
  2. 2 from emp
  3. 3 where deptno!=10
  4. 4 group by deptno
  5. 5 having trunc(avg(sal),0)>2000;
  6.  
  7. DEPTNO 部门平均工资
  8. ------ ------------
  9. 20 2175
显示部门平均工资的最大值 @H_403_6@ select max(avg(sal)) @H_403_6@ from emp @H_403_6@

group by deptno;

  1. sql> select max(avg(sal))
  2. 2 from emp
  3. 3 group by deptno;
  4.  
  5. MAX(AVG(SAL))
  6. -------------
  7. 2916.66667

猜你在找的Oracle相关文章