Oracle继续学习中。。。。。。
查询职位是"MANAGER"或职位不是"ANALYST"的员工(方式一)
select * from emp where job='MANAGER' or not(job='ANALYST'); @H_403_6@查询职位是"MANAGER"或职位不是"ANALYST"的员工(方式二):select * from emp where job='MANAGER' or job!='ANALYST';
- sql> select * from emp where job='MANAGER' or not(job='ANALYST');
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 已选择12行。
查询员工信息,按薪水升序排序:select * from emp order by sal desc; @H_403_6@
- sql> select * from emp where job='MANAGER' or job !='ANALYST';
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 已选择12行。
查询员工信息,按入职日期降序排序:select * from emp order by hiredate desc; @H_403_6@
- sql> select * from emp order by sal desc;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 已选择14行。
- sql> select * from emp order by hiredate desc;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7369 SMITH CLERK 7902 17-12月-80 800 20
order by后面可以跟列名、别名、表达式、列号 @H_403_6@ select * from emp order by sal desc; @H_403_6@
- 已选择14行
select empno "编号",ename "姓名",sal "工 资" from emp order by "工 资" desc;
- sql> select empno "编号",sal "工 资" from emp order by "工 资" desc
- ;
- 编号 姓名 工 资
- ---------- -------------------- ----------
- 7839 KING 5000
- 7902 FORD 3000
- 7788 SCOTT 3000
- 7566 JONES 2975
- 7698 BLAKE 2850
- 7782 CLARK 2450
- 7499 ALLEN 1600
- 7844 TURNER 1500
- 7934 MILLER 1300
- 7521 WARD 1250
- 7654 MARTIN 1250
- 7876 ADAMS 1100
- 7900 JAMES 950
- 7369 SMITH 800
- 已选择14行。
select empno "编号",sal "工资",sal*12 "年薪" from emp order by sal*12 desc;
select empno "编号",sal*12 "年薪" from emp order by 4 desc; @H_403_6@
- sql> select empno "编号",sal*12 "年薪" from emp order by
- sal*12 desc;
- 编号 姓名 工资 年薪
- ---------- -------------------- ---------- ----------
- 7839 KING 5000 60000
- 7902 FORD 3000 36000
- 7788 SCOTT 3000 36000
- 7566 JONES 2975 35700
- 7698 BLAKE 2850 34200
- 7782 CLARK 2450 29400
- 7499 ALLEN 1600 19200
- 7844 TURNER 1500 18000
- 7934 MILLER 1300 15600
- 7521 WARD 1250 15000
- 7654 MARTIN 1250 15000
- 7876 ADAMS 1100 13200
- 7900 JAMES 950 11400
- 7369 SMITH 800 9600
- 已选择14行。
查询员工信息,按佣金升序或降序排列,null值放后面(nulls last) @H_403_6@
- sql> select empno "编号",sal*12 "年薪" from emp order by
- 4 desc;
- 编号 姓名 工资 年薪
- ---------- -------------------- ---------- ----------
- 7839 KING 5000 60000
- 7902 FORD 3000 36000
- 7788 SCOTT 3000 36000
- 7566 JONES 2975 35700
- 7698 BLAKE 2850 34200
- 7782 CLARK 2450 29400
- 7499 ALLEN 1600 19200
- 7844 TURNER 1500 18000
- 7934 MILLER 1300 15600
- 7521 WARD 1250 15000
- 7654 MARTIN 1250 15000
- 7876 ADAMS 1100 13200
- 7900 JAMES 950 11400
- 7369 SMITH 800 9600
- 已选择14行。
select * from emp order by comm asc;
select * from emp order by comm desc nulls last; @H_403_6@
- sql> select * from emp order by comm asc;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 已选择14行。
查询员工信息,按工资降序排列,相同工资的员工再按入职时间降序排列 @H_403_6@
- sql> select * from emp order by comm desc nulls last;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 已选择14行。
select * from emp order by sal desc,hiredate desc;
测试LOWER/UPPER/INITCAP函数 @H_403_6@ select lower('Hello World') "转小写",upper('Hello World') "转大写",initcap('hello world') "首字母大写"from dual; @H_403_6@
- sql> select * from emp order by sal desc,hiredate desc;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 已选择14行。
测试CONCAT/SUBSTR函数,从1开始: @H_403_6@ select concat('hello',' world') "字符串拼接",substr('hello world',7,11) "字符串截取"from dual; @H_403_6@
- sql> select lower('Hello World') "转小写",initcap(
- 'hello world') "首字母大写" from dual;
- 转小写 转大写 首字母大写
- ---------------------- ---------------------- ----------------------
- hello world HELLO WORLD Hello World
测试LENGTH/LENGTHB函数: @H_403_6@
select length('中CHINA') "字符",lengthb('中CHINA') "字节"from dual;
sql> select length('中CHINA') "字符",lengthb('中CHINA') "字节" from dual;
- 字符 字节
- ---------- ----------
- 6 8
length:不管中英文,都是1位
lengthb:英文,是1位;中文是3位【unicode即utf-8】 @H_403_6@ 测试INSTR/LPAD/RPAD函数 @H_403_6@测试TRIM/REPLACE函数 @H_403_6@
- sql> select instr('hello world','o'),lpad('haha',10,'#'),rpad('hehe','@') fro
- m dual;
- INSTR('HELLOWORLD','O') LPAD('HAHA','#') RPAD('HEHE','@')
- ----------------------- -------------------- --------------------
- 5 ######haha hehe@@@@@@
select trim('x' from 'xxxHELLOxxxWORLDxxx'),replace('abcd','c','$')from dual;
select trim(' ' from ' HELLO WORLD ')from dual; @H_403_6@
- sql> select trim('x' from 'xxxHELLOxxxWORLDxxx'),'$') from du
- al;
- TRIM('X'FROM'XXXHELLOXXXWO REPLACE(
- -------------------------- --------
- HELLOxxxWORLD ab$d
测试ROUND/TRUNC/MOD函数 @H_403_6@ select round(3.1415926,3),trunc(3.1415926,mod(1000,300)from dual; @H_403_6@
- sql> select trim(' ' from ' HELLO WORLD ') from dual;
- TRIM(''FROM'HELLOWORLD')
- --------------------------
- HELLO WORLD
select round(sysdate,'month') from dual; @H_403_6@
- sql> select round(3.1415926,300) from dual;
- ROUND(3.1415926,3) TRUNC(3.1415926,3) MOD(1000,300)
- ------------------ ------------------ -------------
- 3.142 3.141 100
select trunc(sysdate,'month')from dual; @H_403_6@
- sql> select round(sysdate,'month') from dual;
- ROUND(SYSDATE,--------------
- 01-7月 -16
显示昨天,今天,明天的日期 @H_403_6@ select sysdate-1 "昨天",sysdate "今天",sysdate+1 明天from dual; @H_403_6@
- sql> select trunc(sysdate,'month') from dual;
- TRUNC(SYSDATE,--------------
- 01-6月 -16
以年和月形式显示员工近似工龄 @H_403_6@ select ename "姓名",sysdate-hiredate "日工龄",(sysdate-hiredate)/30 "月工龄",(sysdate-hiredate)/365 "年工龄"from emp; @H_403_6@
- sql> select sysdate-1 "昨天",sysdate+1 明天 from dual;
- 昨天 今天 明天
- -------------- -------------- --------------
- 19-6月 -16 20-6月 -16 21-6月 -16
使用MONTHS_BETWEEN函数,以精确月形式显示员工工龄 @H_403_6@ select ename "姓名",(sysdate-hiredate)/30 "近似月工龄",months_between(sysdate,hiredate) "精确月工龄"from emp; @H_403_6@
- sql> select ename "姓名",(sysdate-hiredate)/30 "月工龄
- ",(sysdate-hiredate)/365 "年工龄" from emp;
- 姓名 日工龄 月工龄 年工龄
- -------------------- ---------- ---------- ----------
- SMITH 12969.987 432.332899 35.5342109
- ALLEN 12904.987 430.166232 35.3561287
- WARD 12902.987 430.099566 35.3506492
- JONES 12863.987 428.799566 35.2437999
- MARTIN 12684.987 422.832899 34.753389
- BLAKE 12834.987 427.832899 35.1643479
- CLARK 12795.987 426.532899 35.0574985
- SCOTT 10655.987 355.199566 29.1944848
- KING 12634.987 421.166232 34.6164027
- TURNER 12704.987 423.499566 34.8081835
- ADAMS 10621.987 354.066232 29.1013342
- JAMES 12618.987 420.632899 34.572567
- FORD 12618.987 420.632899 34.572567
- MILLER 12567.987 418.932899 34.432841
- 已选择14行。
- sql> select ename "姓名",months_between(sysda
- te,hiredate) "精确月工龄" from emp;
- 姓名 近似月工龄 精确月工龄
- -------------------- ---------- ----------
- SMITH 432.332915 426.128627
- ALLEN 430.166248 424
- WARD 430.099581 423.967337
- JONES 428.799581 422.612498
- MARTIN 422.832915 416.773788
- BLAKE 427.832915 421.644756
- CLARK 426.532915 420.386692
- SCOTT 355.199581 350.064111
- KING 421.166248 415.128627
- TURNER 423.499581 417.41895
- ADAMS 354.066248 348.935079
- JAMES 420.632915 414.58024
- FORD 420.632915 414.58024
- MILLER 418.932915 412.935079
- 已选择14行。
测试ADD_MONTHS函数
select add_months(sysdate,3)from dual;
测试NEXT_DAY函数【中文平台】 @H_403_6@
- sql> select add_months(sysdate,3) from dual;
- ADD_MONTHS(SYS
- --------------
- 20-9月 -16
select next_day(sysdate,'星期三')from dual;
从今天开始算,下一个星期三是多少号? @H_403_6@
- sql> select next_day(sysdate,'星期三') from dual;
- NEXT_DAY(SYSDA
- --------------
- 22-6月 -16
select next_day(next_day(sysdate,'星期三'),'星期三')from dual;
日期隐式转换,读字符串,判断是否符合日期格式要求,再隐式转换,效率低 @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@
- sql> select next_day(next_day(sysdate,'星期三') from dual;
- NEXT_DAY(NEXT_
- --------------
- 29-6月 -16
使用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@
- sql> select ename,'yyyy-mm-dd') from emp where hiredate=to_date
- ('1980-12-17','yyyy-mm-dd');
- ENAME TO_CHAR(HIREDATE,'YY
- ---------- --------------------
- SMITH 1980-12-17
select to_char(sysdate,'YYYY-MM-DD-DAY HH:MI:SS AM')from dual; @H_403_6@
- sql> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
- TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
- --------------------------------------
- 2016-06-20 23:48:23
select to_char(sysdate,'yyyy-mm-dd "今天是" day hh24:mi:ss') "时间"from dual; @H_403_6@ 常量一定要用""双引号定界 @H_403_6@
- sql> select to_char(sysdate,'YYYY-MM-DD-DAY HH:MI:SS AM')from dual;
- TO_CHAR(SYSDATE,'YYYY-MM-DD-DAYHH:MI:SSAM')
- --------------------------------------------------------------------
- 2016-06-20-星期一 11:50:02 下午
@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@
- sql> select to_char(sysdate,'yyyy-mm-dd "今天是" day hh24:mi:ss') "时间"from dua
- l;
- 时间
- --------------------------------------------------------------------------------
- 2016-06-20 今天是 星期一 23:50:35
- sql> select ename,999') from emp;
- ENAME TO_CHAR(SAL,'$
- ---------- --------------
- SMITH $800
- ALLEN $1,600
- WARD $1,250
- JONES $2,975
- MARTIN $1,250
- BLAKE $2,850
- CLARK $2,450
- SCOTT $3,000
- KING $5,000
- TURNER $1,500
- ADAMS $1,100
- JAMES $950
- FORD $3,000
- MILLER $1,300
- 已选择14行。
使用to_number()函数将字符串转成数字 @H_403_6@ select to_number('123')+100 "结果"from dual; @H_403_6@
- sql> select ename,999')
- ---------- --------------------------------
- SMITH ¥800
- ALLEN ¥1,600
- WARD ¥1,250
- JONES ¥2,975
- MARTIN ¥1,250
- BLAKE ¥2,850
- CLARK ¥2,450
- SCOTT ¥3,000
- KING ¥5,000
- TURNER ¥1,500
- ADAMS ¥1,100
- JAMES ¥950
- FORD ¥3,000
- MILLER ¥1,300
- 已选择14行。
使用nul函数,统计员工年收入 @H_403_6@ select ename,12*sal+nvl(comm,0)from emp; @H_403_6@
- sql> select to_number('123')+100 "结果" from dual;
- 结果
- ----------
- 223
使用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@
- sql> select ename,0) from emp;
- ENAME 12*SAL+NVL(COMM,0)
- ---------- ------------------
- SMITH 9600
- ALLEN 19500
- WARD 15500
- JONES 35700
- MARTIN 16400
- BLAKE 34200
- CLARK 29400
- SCOTT 36000
- KING 60000
- TURNER 18000
- ADAMS 13200
- JAMES 11400
- FORD 36000
- MILLER 15600
- 已选择14行。
from emp;
各数据库厂都支持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@
- sql> select ename,2 case job
- 3 when 'PRESIDENT' then sal+1000
- 4 when 'MANAGER' then sal+800
- 5 else sal+400
- 6 end "涨后薪水"
- 7 from emp;
- ENAME JOB SAL 涨后薪水
- ---------- ---------- ----- ----------
- SMITH CLERK 800 1200
- ALLEN SALESMAN 1600 2000
- WARD SALESMAN 1250 1650
- JONES MANAGER 2975 3775
- MARTIN SALESMAN 1250 1650
- BLAKE MANAGER 2850 3650
- CLARK MANAGER 2450 3250
- SCOTT ANALYST 3000 3400
- KING PRESIDENT 5000 6000
- TURNER SALESMAN 1500 1900
- ADAMS CLERK 1100 1500
- JAMES CLERK 950 1350
- FORD ANALYST 3000 3400
- MILLER CLERK 1300 1700
- 已选择14行。
员工总工资,平均工资 @H_403_6@ select sum(sal) "总工资",round(avg(sal),2) "平均工资"from emp; @H_403_6@
- sql> select ename,2 decode(job,sal+400) "涨后薪水
- " from emp;
- ENAME JOB 涨前薪水 涨后薪水
- ---------- ---------- ---------- ----------
- SMITH CLERK 800 1200
- ALLEN SALESMAN 1600 2000
- WARD SALESMAN 1250 1650
- JONES MANAGER 2975 3775
- MARTIN SALESMAN 1250 1650
- BLAKE MANAGER 2850 3650
- CLARK MANAGER 2450 3250
- SCOTT ANALYST 3000 3400
- KING PRESIDENT 5000 6000
- TURNER SALESMAN 1500 1900
- ADAMS CLERK 1100 1500
- JAMES CLERK 950 1350
- FORD ANALYST 3000 3400
- MILLER CLERK 1300 1700
- 已选择14行。
员工最高工资,最低工资 @H_403_6@ select max(sal) "最高工资",min(sal) "最低工资"from emp; @H_403_6@
- sql> select sum(sal) "总工资",2) "平均工资" from emp;
- 总工资 平均工资
- ---------- ----------
- 29025 2073.21
入职最早员工,入职最晚员工 @H_403_6@ select max(hiredate) "最晚入职员工",min(hiredate) "最早入职员工"from emp; @H_403_6@
- sql> select max(sal) "最高工资",min(sal) "最低工资" from emp;
- 最高工资 最低工资
- ---------- ----------
- 5000 800
求员工总人数 @H_403_6@ select count(*) 员工总人数from emp; @H_403_6@
- sql> select max(hiredate) "最晚入职员工",min(hiredate) "最早入职员工" from emp;
- 最晚入职员工 最早入职员工
- -------------- --------------
- 23-5月 -87 17-12月-80
统计有佣金的员工人数 @H_403_6@
- sql> select count(*) 员工总人数 from emp;
- 员工总人数
- ----------
- 14
select count(comm) 有佣金的员工人数from emp;
分组函数不统计null值 @H_403_6@ 统计公司有多少个部门 @H_403_6@
- sql> select count(comm) 有佣金的员工人数 from emp;
- 有佣金的员工人数
- ----------------
- 4
@H_403_6@ 按部门求出部门平均工资 @H_403_6@ --以下sql语句是正确 @H_403_6@ select deptno,trunc(avg(sal),0)from empgroup by deptno; @H_403_6@
- sql> select count(distinct deptno) from emp;
- COUNT(DISTINCTDEPTNO)
- ---------------------
- 3
select trunc(avg(sal),0)from empgroup by deptno; @H_403_6@
- sql> select deptno,0) from emp group by deptno;
- DEPTNO TRUNC(AVG(SAL),0)
- ------ -----------------
- 30 1566
- 20 2175
- 10 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@
- sql> select trunc(avg(sal),0) from emp group by deptno;
- TRUNC(AVG(SAL),0)
- -----------------
- 1566
- 2175
- 2916
@H_403_6@ 除10号部门外,查询部门平均工资大于2000元的部门 @H_403_6@ 方式一 @H_403_6@ select deptno,0)>2000 and deptno!=10; @H_403_6@
- sql> select deptno,0) "部门平均工资"
- 2 from emp
- 3 group by deptno
- 4 having trunc(avg(sal),0)>2000;
- DEPTNO 部门平均工资
- ------ ------------
- 20 2175
- 10 2916
方式二【先行过滤,再组过滤,性能较佳】 @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@
- sql> select deptno,0)>2000 and deptno!=10;
- DEPTNO 部门平均工资
- ------ ------------
- 20 2175
显示部门平均工资的最大值 @H_403_6@ select max(avg(sal)) @H_403_6@ from emp @H_403_6@
- sql> select deptno,0) "部门平均工资"
- 2 from emp
- 3 where deptno!=10
- 4 group by deptno
- 5 having trunc(avg(sal),0)>2000;
- DEPTNO 部门平均工资
- ------ ------------
- 20 2175
group by deptno;
- sql> select max(avg(sal))
- 2 from emp
- 3 group by deptno;
- MAX(AVG(SAL))
- -------------
- 2916.66667