查询语句实例,无结果集的
--对oracle 查询的学习 --select <*,column [alias],...> --from tble [where condition(s)] --[group by group_by_expression] --[order by column[desc|asc] ] --emp表 describe emp; select * from emp; --dept表 describe dept; select * from dept; --基本查询 select * from dept; select empno, ename, job from emp; --通过distinct删除重复值 select job from emp; select distinct job from emp; --distinct 限制的是后面所有列组合出现的重复值,这里是job和deptbo组合为键值的来判别不同 select distinct job, deptno from emp; --算术表达式 select empno, ename , sal ,sal*12 year_salary from emp; --别名的使用 select empno as id , ename name , sal "Salary" ,sal*12 "Annual Salary" from emp; --连接表达式 select ename || ' 的职位是 ' || job from emp; --处理null 如果不处理null,null+任意值还是等于null select ename , sal ,comm , sal+comm total_salary from emp; select ename , sal,comm, sal+nvl(comm,0) total_salary from emp; --对日历列的处理，默认的显示格式是DD-MON-RR==>两位日-月明-两位年 --可以使用to_char()对日期格式化 select ename , hiredate from emp; select ename ,to_char(hiredate,'YYYY-MM-DD') from emp; --to_date对字符串转化为date类,以方便比较日期大小 select ename, sal, hiredate from emp where hiredate>to_date('1982-01-01','YYYY-MM-DD'); --限制查询 -- where的使用 select ename , sal from emp where sal<2000; select ename, job from emp where job ='SALESMAN'; --可以通过to_date()函数转换为date类型比较的 select ename, hiredate, job from emp where hiredate>'01-1月-82'; select ename, sal, job from emp where sal between 1500 and 3000;--[1500,3000] 闭区间 --这里的集合使用的是括号; select ename, sal, job from emp where ename in ('FORD','SCOTT'); --名字中第二个字是A select ename, sal, job from emp where ename like '_A%'; --转义符,通过escape声明转义符,转义符可以是任意字符串 --insert into emp (empno,ename) values(5566,'JOHN_SMITH'); select ename, empno from emp where ename like'%\_%' escape''; --查询某个空值,如果使用comm==null会发现没有结果 select ename, sal, comm from emp where comm is null; --and or not select ename, sal, job from emp where sal>1000 and job ='CLERK'; --查询奖金非null的 select ename, sal, job from emp where comm is not null; --优先级 比较符>not>and>or select ename, sal, job from emp where job='SALESMAN' or sal>2000 and comm is not null; --order by asc desc null的值被默认为最大的 select ename, sal from emp order by sal asc; --使用union并集查询时如果列名不同则使用位置排序 select empno, ename, sal from emp order by 1; --多列排序 select empno, ename, job, sal from emp order by job asc, sal desc; --分组查询 select count(ename) num from emp ; --出现null的值的话不计算在内的 select avg(sal) avg_sal,sum(sal) sum_sal from emp; select max(sal) max_salaary, min(sal) min_salary from emp; --单列分组:查询按部分分组 select deptno,avg(sal) avg_salary, sum(sal), max(sal), min(sal) from emp group by deptno order by deptno; --多列分组:按部门和职位分组 select deptno, job, avg(sal) avg_salary, max(sal) max_salary from emp group by deptno, job order by deptno,job; --rollup()函数,按行小计,把分组计算后的结果,再根据行列统计每行的信息 select deptno, job, avg(sal) avg_salary from emp group by rollup(deptno,job); select deptno, job, avg(sal) avg_salary from emp group by cube(deptno,job); --cube()函数,对每行每列进行小计 CLERK MANAGER PRESIDENT ANALYST SALESMAN total 10 1300 2450 5000 2916 (1300+2450+5000)/3 20 950 2975 3000 2175 (950+2975+3000)/3 30 950 2850 1400 1566 (950+2850+1400)/3 1037 2758 5000 3000 1400 是总的表的平均值2073 --这里的2073不等于total的平均值,每个total可以这么理解total=(a+b+(c+d)/2+e)/4 (c+d)/2相当于两人在同一组中的平均值 -- a,b,c,d,e分别是员工的薪水,c和d是相同岗位和部门的员工,那么tatal就不是这五个员工薪水的平均值了; --having限制分组结果 select deptno, max(sal) max_sal from emp group by deptno having max(sal)>4000; --连接查询 --笛卡儿积:两个表直接相乘 select emp.ename, dept.dname from emp, dept; --等值连接 select emp.ename, emp.job,dept.dname, dept.loc from emp,dept where emp.deptno=dept.deptno; --自连接 通过别名虚拟成两个表 select e.empno, e.ename, manager.empno, manager.ename from emp e, emp manager where e.mgr = manager.empno; --内连接：返回满足条件的记录 外连接：还返回不满足条件的记录 select e.ename, d.dname from emp e inner join dept d on(e.deptno = d.deptno); --左连接 select e.ename, d.dname from emp e left join dept d on(e.deptno = d.deptno); --右连接 select e.ename, d.dname from emp e right join dept d on(e.deptno = d.deptno); --全连接: 不同于笛卡儿积,是左连接和右连接的并集 select e.ename, d.dname from emp e full join dept d on(e.deptno = d.deptno); --子查询 --单行单列子查询 select ename, job from emp where job=(select job from emp where ename='SCOTT'); --多行单列子查询 使用in,any(>any:b比其中一个大就ok),all (其中的所有数据都满足) -- 查询和salesman在同一个部门,却不是SALESMAN的职位 select ename, deptno from emp where deptno in(select deptno from emp where job='SALESMAN') and job !='SALESMAN'; -- 查询比salesman最低工资高的所有人信息 select ename, sal, deptno from emp where sal >any(select sal from emp where job='SALESMAN') and job!='SALESMAN'; --多行多列子查询 --查询和scott相同部门和职位的员工 select ename, job, deptno from emp where (job, deptno ) in (select job, deptno from emp where ename='SCOTT'); --行内视图:把子查询当视图 --查询薪水比本部门平均薪水高的员工 语句一使用子查询,语句二：使用视图 比较容易理解,先查询出每个部门的平均值,再用原语句和结果比较 select ename, sal, deptno from emp e where sal>(select avg(sal) from emp x where x.deptno = e.deptno) order by deptno; select ename, sal, deptno from emp e (select deptno, avg(sal) avg_sal from emp group by deptno) x where e.deptno = x.deptno and e.sal>x.avg_sal order by deptno; --exists语句 只要子查询语句有结果,那么这条主查询语句就有结果 获取有员工的部门信息 select deptno, dname, loc from dept where exists (select * from emp where emp.deptno = dept.deptno); --集合查询 select statement1 [union|union all|intersect|minus] select statemens2 并集 交集 差集 --职位是manager但是薪水低于2500 语句一使用交集,二使用差集 select ename, job, sal from emp where job='MANAGER' intersect select ename, job, sal from emp where sal<2500; select ename, job, sal from emp where job='MANAGER' minus select ename, job, sal from emp where sal>2500;
上述所有查询语句的结果
SQL> @ d: empfileoracleinput.txt; SQL> --对oracle 查询的学习 SQL> --select <*,column [alias],...> SQL> --from tble [where condition(s)] SQL> --[group by group_by_expression] SQL> --[order by column[desc|asc] ] SQL> SQL> --emp表 SQL> describe emp; 名称 是否为空? 类型 ----------------------------------------------------- -------- ------------------------------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> select * from emp; 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 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 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 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 5566 JOHN_SMITH 已选择15行。 已用时间: 00: 00: 00.00 SQL> SQL> --dept表 SQL> describe dept; 名称 是否为空? 类型 ----------------------------------------------------- -------- ------------------------------------ DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 已用时间: 00: 00: 00.00 SQL> SQL> SQL> --基本查询 SQL> SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 已用时间: 00: 00: 00.00 SQL> SQL> select empno, ename, job from emp; EMPNO ENAME JOB ---------- ---------- --------- 7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER 7788 SCOTT ANALYST 7839 KING PRESIDENT 7844 TURNER SALESMAN 7876 ADAMS CLERK 7900 JAMES CLERK 7902 FORD ANALYST 7934 MILLER CLERK 5566 JOHN_SMITH 已选择15行。 已用时间: 00: 00: 00.00 SQL> SQL> --通过distinct删除重复值 SQL> select job from emp; JOB --------- CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK 已选择15行。 已用时间: 00: 00: 00.00 SQL> select distinct from emp; select distinct from emp * 第 1 行出现错误: ORA-00936: 缺失表达式 已用时间: 00: 00: 00.00 SQL> SQL> --distinct 限制的是后面所有列组合出现的重复值,这里是job和deptbo组合为键值的来判别不同 SQL> select distinct job, deptno from emp; JOB DEPTNO --------- ---------- MANAGER 20 PRESIDENT 10 CLERK 10 SALESMAN 30 ANALYST 20 MANAGER 30 MANAGER 10 CLERK 30 CLERK 20 已选择10行。 已用时间: 00: 00: 00.00 SQL> SQL> --算术表达式 SQL> select empno, ename , sal ,sal*12 year_salary from emp; EMPNO ENAME SAL YEAR_SALARY ---------- ---------- ---------- ----------- 7369 SMITH 800 9600 7499 ALLEN 1600 19200 7521 WARD 1250 15000 7566 JONES 2975 35700 7654 MARTIN 1250 15000 7698 BLAKE 2850 34200 7782 CLARK 2450 29400 7788 SCOTT 3000 36000 7839 KING 5000 60000 7844 TURNER 1500 18000 7876 ADAMS 1100 13200 7900 JAMES 950 11400 7902 FORD 3000 36000 7934 MILLER 1300 15600 5566 JOHN_SMITH 已选择15行。 已用时间: 00: 00: 00.00 SQL> SQL> --别名的使用 SQL> select empno as id , ename name , sal "Salary" ,sal*12 "Annual Salary" from emp; ID NAME Salary Annual Salary ---------- ---------- ---------- ------------- 7369 SMITH 800 9600 7499 ALLEN 1600 19200 7521 WARD 1250 15000 7566 JONES 2975 35700 7654 MARTIN 1250 15000 7698 BLAKE 2850 34200 7782 CLARK 2450 29400 7788 SCOTT 3000 36000 7839 KING 5000 60000 7844 TURNER 1500 18000 7876 ADAMS 1100 13200 7900 JAMES 950 11400 7902 FORD 3000 36000 7934 MILLER 1300 15600 5566 JOHN_SMITH 已选择15行。 已用时间: 00: 00: 00.00 SQL> SQL> SQL> --连接表达式 SQL> select ename || ' 的职位是 ' || job from emp; ENAME||'的职位是'||JOB ------------------------------- SMITH 的职位是 CLERK ALLEN 的职位是 SALESMAN WARD 的职位是 SALESMAN JONES 的职位是 MANAGER MARTIN 的职位是 SALESMAN BLAKE 的职位是 MANAGER CLARK 的职位是 MANAGER SCOTT 的职位是 ANALYST KING 的职位是 PRESIDENT TURNER 的职位是 SALESMAN ADAMS 的职位是 CLERK JAMES 的职位是 CLERK FORD 的职位是 ANALYST MILLER 的职位是 CLERK JOHN_SMITH 的职位是 已选择15行。 已用时间: 00: 00: 00.00 SQL> SQL> --处理null 如果不处理null,null+任意值还是等于null SQL> select ename , sal ,comm , sal+comm total_salary from emp; ENAME SAL COMM TOTAL_SALARY ---------- ---------- ---------- ------------ SMITH 800 ALLEN 1600 300 1900 WARD 1250 500 1750 JONES 2975 MARTIN 1250 1400 2650 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 0 1500 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 JOHN_SMITH 已选择15行。 已用时间: 00: 00: 00.00 SQL> select ename , sal,comm, sal+nvl(comm,0) total_salary from emp; ENAME SAL COMM TOTAL_SALARY ---------- ---------- ---------- ------------ SMITH 800 800 ALLEN 1600 300 1900 WARD 1250 500 1750 JONES 2975 2975 MARTIN 1250 1400 2650 BLAKE 2850 2850 CLARK 2450 2450 SCOTT 3000 3000 KING 5000 5000 TURNER 1500 0 1500 ADAMS 1100 1100 JAMES 950 950 FORD 3000 3000 MILLER 1300 1300 JOHN_SMITH 已选择15行。 已用时间: 00: 00: 00.00 SQL> SQL> --对日历列的处理，默认的显示格式是DD-MON-RR==>两位日-月明-两位年 SQL> --可以使用to_char()对日期格式化 SQL> select ename , hiredate from emp; ENAME HIREDATE ---------- -------------- SMITH 17-12月-80 ALLEN 20-2月 -81 WARD 22-2月 -81 JONES 02-4月 -81 MARTIN 28-9月 -81 BLAKE 01-5月 -81 CLARK 09-6月 -81 SCOTT 19-4月 -87 KING 17-11月-81 TURNER 08-9月 -81 ADAMS 23-5月 -87 JAMES 03-12月-81 FORD 03-12月-81 MILLER 23-1月 -82 JOHN_SMITH 已选择15行。 已用时间: 00: 00: 00.00 SQL> select ename ,to_char(hiredate,'YYYY-MM-DD') from emp; ENAME TO_CHAR(HI ---------- ---------- SMITH 1980-12-17 ALLEN 1981-02-20 WARD 1981-02-22 JONES 1981-04-02 MARTIN 1981-09-28 BLAKE 1981-05-01 CLARK 1981-06-09 SCOTT 1987-04-19 KING 1981-11-17 TURNER 1981-09-08 ADAMS 1987-05-23 JAMES 1981-12-03 FORD 1981-12-03 MILLER 1982-01-23 JOHN_SMITH 已选择15行。 已用时间: 00: 00: 00.00 SQL> --to_date对字符串转化为date类,以方便比较日期大小 SQL> select ename, sal, hiredate from emp where hiredate>to_date('1982-01-01','YYYY-MM-DD'); ENAME SAL HIREDATE ---------- ---------- -------------- SCOTT 3000 19-4月 -87 ADAMS 1100 23-5月 -87 MILLER 1300 23-1月 -82 已用时间: 00: 00: 00.00 SQL> SQL> SQL> --限制查询 SQL> SQL> -- where的使用 SQL> select ename , sal from emp where sal<2000; ENAME SAL ---------- ---------- SMITH 800 ALLEN 1600 WARD 1250 MARTIN 1250 TURNER 1500 ADAMS 1100 JAMES 950 MILLER 1300 已选择8行。 已用时间: 00: 00: 00.00 SQL> SQL> select ename, job from emp where job ='SALESMAN'; ENAME JOB ---------- --------- ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN TURNER SALESMAN 已用时间: 00: 00: 00.00 SQL> SQL> --可以通过to_date()函数转换为date类型比较的 SQL> select ename, hiredate, job from emp where hiredate>'01-1月-82'; ENAME HIREDATE JOB ---------- -------------- --------- SCOTT 19-4月 -87 ANALYST ADAMS 23-5月 -87 CLERK MILLER 23-1月 -82 CLERK 已用时间: 00: 00: 00.00 SQL> SQL> select ename, sal, job from emp where sal between 1500 and 3000;--[1500,3000] 闭区间 2 SQL> --这里的集合使用的是括号; SQL> select ename, sal, job from emp where ename in ('FORD','SCOTT'); ENAME SAL JOB ---------- ---------- --------- SCOTT 3000 ANALYST FORD 3000 ANALYST 已用时间: 00: 00: 00.00 SQL> --名字中第二个字是A SQL> select ename, sal, job from emp where ename like '_A%'; ENAME SAL JOB ---------- ---------- --------- WARD 1250 SALESMAN MARTIN 1250 SALESMAN JAMES 950 CLERK 已用时间: 00: 00: 00.00 SQL> --转义符,通过escape声明转义符,转义符可以是任意字符串 SQL> --insert into emp (empno,ename) values(5566,'JOHN_SMITH'); SQL> select ename, empno from emp where ename like'%\_%' escape''; ENAME EMPNO ---------- ---------- JOHN_SMITH 5566 已用时间: 00: 00: 00.00 SQL> SQL> --查询某个空值,如果使用comm==null会发现没有结果 SQL> select ename, sal, comm from emp where comm is null; ENAME SAL COMM ---------- ---------- ---------- SMITH 800 JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 JOHN_SMITH 已选择11行。 已用时间: 00: 00: 00.00 SQL> --and or not SQL> select ename, sal, job from emp where sal>1000 and job ='CLERK'; ENAME SAL JOB ---------- ---------- --------- ADAMS 1100 CLERK MILLER 1300 CLERK 已用时间: 00: 00: 00.00 SQL> --查询奖金非null的 SQL> select ename, sal, job from emp where comm is not null; ENAME SAL JOB ---------- ---------- --------- ALLEN 1600 SALESMAN WARD 1250 SALESMAN MARTIN 1250 SALESMAN TURNER 1500 SALESMAN 已用时间: 00: 00: 00.00 SQL> --优先级 比较符>not>and>or SQL> select ename, sal, job from emp where job='SALESMAN' or sal>2000 and comm is not null; ENAME SAL JOB ---------- ---------- --------- ALLEN 1600 SALESMAN WARD 1250 SALESMAN MARTIN 1250 SALESMAN TURNER 1500 SALESMAN 已用时间: 00: 00: 00.00 SQL> SQL> --order by asc desc null的值被默认为最大的 SQL> select ename, sal from emp order by sal asc; ENAME SAL ---------- ---------- SMITH 800 JAMES 950 ADAMS 1100 WARD 1250 MARTIN 1250 MILLER 1300 TURNER 1500 ALLEN 1600 CLARK 2450 BLAKE 2850 JONES 2975 FORD 3000 SCOTT 3000 KING 5000 JOHN_SMITH 已选择15行。 已用时间: 00: 00: 00.00 SQL> --使用union并集查询时如果列名不同则使用位置排序 SQL> select empno, ename, sal from emp order by 1; EMPNO ENAME SAL ---------- ---------- ---------- 5566 JOHN_SMITH 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 已选择15行。 已用时间: 00: 00: 00.00 SQL> --多列排序 SQL> select empno, ename, job, sal from emp order by job asc, sal desc; EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7902 FORD ANALYST 3000 7788 SCOTT ANALYST 3000 7934 MILLER CLERK 1300 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7369 SMITH CLERK 800 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250 5566 JOHN_SMITH 已选择15行。 已用时间: 00: 00: 00.00 SQL> SQL> SQL> --分组查询 SQL> select count(ename) num from emp ; NUM ---------- 15 已用时间: 00: 00: 00.00 SQL> --出现null的值的话不计算在内的 SQL> select avg(sal) avg_sal,sum(sal) sum_sal from emp; AVG_SAL SUM_SAL ---------- ---------- 2073.21429 29025 已用时间: 00: 00: 00.00 SQL> select max(sal) max_salaary, min(sal) min_salary from emp; MAX_SALAARY MIN_SALARY ----------- ---------- 5000 800 已用时间: 00: 00: 00.00 SQL> --单列分组:查询按部分分组 SQL> select deptno,avg(sal) avg_salary, sum(sal), max(sal), min(sal) from emp group by deptno order by deptno; DEPTNO AVG_SALARY SUM(SAL) MAX(SAL) MIN(SAL) ---------- ---------- ---------- ---------- ---------- 10 2916.66667 8750 5000 1300 20 2175 10875 3000 800 30 1566.66667 9400 2850 950 已用时间: 00: 00: 00.00 SQL> --多列分组:按部门和职位分组 SQL> select deptno, job, avg(sal) avg_salary, max(sal) max_salary from emp group by deptno, job order by deptno,job; DEPTNO JOB AVG_SALARY MAX_SALARY ---------- --------- ---------- ---------- 10 CLERK 1300 1300 10 MANAGER 2450 2450 10 PRESIDENT 5000 5000 20 ANALYST 3000 3000 20 CLERK 950 1100 20 MANAGER 2975 2975 30 CLERK 950 950 30 MANAGER 2850 2850 30 SALESMAN 1400 1600 已选择10行。 已用时间: 00: 00: 00.00 SQL> --rollup()函数,按行小计,把分组计算后的结果,再根据行列统计每行的信息 SQL> select deptno, job, avg(sal) avg_salary from emp group by rollup(deptno,job); DEPTNO JOB AVG_SALARY ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 2916.66667 20 CLERK 950 20 ANALYST 3000 20 MANAGER 2975 20 2175 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1400 30 1566.66667 2073.21429 已选择15行。 已用时间: 00: 00: 00.00 SQL> select deptno, job, avg(sal) avg_salary from emp group by cube(deptno,job); DEPTNO JOB AVG_SALARY ---------- --------- ---------- 2073.21429 CLERK 1037.5 ANALYST 3000 MANAGER 2758.33333 SALESMAN 1400 PRESIDENT 5000 10 2916.66667 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 2175 20 CLERK 950 20 ANALYST 3000 20 MANAGER 2975 30 1566.66667 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1400 已选择21行。 已用时间: 00: 00: 00.00 SQL> --cube()函数,对每行每列进行小计 SQL> CLERK MANAGER PRESIDENT ANALYST SALESMAN total SP2-0734: 未知的命令开头 "CLERK MANA..." - 忽略了剩余的行。 SQL> 10 1300 2450 5000 2916 (1300+2450+5000)/3 SQL> 20 950 2975 3000 2175 (950+2975+3000)/3 SQL> 30 950 2850 1400 1566 (950+2850+1400)/3 SQL> 1037 2758 5000 3000 1400 是总的表的平均值2073 SQL> --这里的2073不等于total的平均值,每个total可以这么理解total=(a+b+(c+d)/2+e)/4 (c+d)/2相当于两人在同一组中的平均值 SQL> -- a,b,c,d,e分别是员工的薪水,c和d是相同岗位和部门的员工,那么tatal就不是这五个员工薪水的平均值了; SQL> SQL> --having限制分组结果 SQL> select deptno, max(sal) max_sal from emp group by deptno having max(sal)>4000; DEPTNO MAX_SAL ---------- ---------- 10 5000 已用时间: 00: 00: 00.00 SQL> SQL> SQL> SQL> --连接查询 SQL> SQL> --笛卡儿积:两个表直接相乘 SQL> select emp.ename, dept.dname from emp, dept; ENAME DNAME ---------- -------------- SMITH ACCOUNTING ALLEN ACCOUNTING WARD ACCOUNTING JONES ACCOUNTING MARTIN ACCOUNTING BLAKE ACCOUNTING CLARK ACCOUNTING SCOTT ACCOUNTING KING ACCOUNTING TURNER ACCOUNTING ADAMS ACCOUNTING JAMES ACCOUNTING FORD ACCOUNTING MILLER ACCOUNTING JOHN_SMITH ACCOUNTING SMITH RESEARCH ALLEN RESEARCH WARD RESEARCH JONES RESEARCH MARTIN RESEARCH BLAKE RESEARCH CLARK RESEARCH SCOTT RESEARCH KING RESEARCH TURNER RESEARCH ADAMS RESEARCH JAMES RESEARCH FORD RESEARCH MILLER RESEARCH JOHN_SMITH RESEARCH SMITH SALES ALLEN SALES WARD SALES JONES SALES MARTIN SALES BLAKE SALES CLARK SALES SCOTT SALES KING SALES TURNER SALES ADAMS SALES JAMES SALES FORD SALES MILLER SALES JOHN_SMITH SALES SMITH OPERATIONS ALLEN OPERATIONS WARD OPERATIONS JONES OPERATIONS MARTIN OPERATIONS BLAKE OPERATIONS CLARK OPERATIONS SCOTT OPERATIONS KING OPERATIONS TURNER OPERATIONS ADAMS OPERATIONS JAMES OPERATIONS FORD OPERATIONS MILLER OPERATIONS JOHN_SMITH OPERATIONS 已选择60行。 已用时间: 00: 00: 00.00 SQL> --等值连接 SQL> select emp.ename, emp.job,dept.dname, dept.loc from emp,dept where emp.deptno=dept.deptno; ENAME JOB DNAME LOC ---------- --------- -------------- ------------- CLARK MANAGER ACCOUNTING NEW YORK MILLER CLERK ACCOUNTING NEW YORK KING PRESIDENT ACCOUNTING NEW YORK JONES MANAGER RESEARCH DALLAS SMITH CLERK RESEARCH DALLAS SCOTT ANALYST RESEARCH DALLAS FORD ANALYST RESEARCH DALLAS ADAMS CLERK RESEARCH DALLAS WARD SALESMAN SALES CHICAGO TURNER SALESMAN SALES CHICAGO ALLEN SALESMAN SALES CHICAGO JAMES CLERK SALES CHICAGO MARTIN SALESMAN SALES CHICAGO BLAKE MANAGER SALES CHICAGO 已选择14行。 已用时间: 00: 00: 00.00 SQL> --自连接 通过别名虚拟成两个表 SQL> select e.empno, e.ename, manager.empno, manager.ename from emp e, emp manager where e.mgr = manager.empno; EMPNO ENAME EMPNO ENAME ---------- ---------- ---------- ---------- 7902 FORD 7566 JONES 7788 SCOTT 7566 JONES 7844 TURNER 7698 BLAKE 7499 ALLEN 7698 BLAKE 7521 WARD 7698 BLAKE 7900 JAMES 7698 BLAKE 7654 MARTIN 7698 BLAKE 7934 MILLER 7782 CLARK 7876 ADAMS 7788 SCOTT 7698 BLAKE 7839 KING 7566 JONES 7839 KING 7782 CLARK 7839 KING 7369 SMITH 7902 FORD 已选择13行。 已用时间: 00: 00: 00.00 SQL> --内连接：返回满足条件的记录 外连接：还返回不满足条件的记录 SQL> select e.ename, d.dname from emp e inner join dept d on(e.deptno = d.deptno); ENAME DNAME ---------- -------------- CLARK ACCOUNTING MILLER ACCOUNTING KING ACCOUNTING JONES RESEARCH SMITH RESEARCH SCOTT RESEARCH FORD RESEARCH ADAMS RESEARCH WARD SALES TURNER SALES ALLEN SALES JAMES SALES MARTIN SALES BLAKE SALES 已选择14行。 已用时间: 00: 00: 00.00 SQL> --左连接 SQL> select e.ename, d.dname from emp e left join dept d on(e.deptno = d.deptno); ENAME DNAME ---------- -------------- MILLER ACCOUNTING KING ACCOUNTING CLARK ACCOUNTING FORD RESEARCH ADAMS RESEARCH SCOTT RESEARCH JONES RESEARCH SMITH RESEARCH JAMES SALES TURNER SALES BLAKE SALES MARTIN SALES WARD SALES ALLEN SALES JOHN_SMITH 已选择15行。 已用时间: 00: 00: 00.00 SQL> --右连接 SQL> select e.ename, d.dname from emp e right join dept d on(e.deptno = d.deptno); ENAME DNAME ---------- -------------- CLARK ACCOUNTING MILLER ACCOUNTING KING ACCOUNTING JONES RESEARCH SMITH RESEARCH SCOTT RESEARCH FORD RESEARCH ADAMS RESEARCH WARD SALES TURNER SALES ALLEN SALES JAMES SALES MARTIN SALES BLAKE SALES OPERATIONS 已选择15行。 已用时间: 00: 00: 00.00 SQL> --全连接: 不同于笛卡儿积,是左连接和右连接的并集 SQL> select e.ename, d.dname from emp e full join dept d on(e.deptno = d.deptno); ENAME DNAME ---------- -------------- SMITH RESEARCH ALLEN SALES WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES ADAMS RESEARCH JAMES SALES FORD RESEARCH MILLER ACCOUNTING JOHN_SMITH OPERATIONS 已选择16行。 已用时间: 00: 00: 00.00 SQL> SQL> --子查询 SQL> --单行单列子查询 SQL> select ename, job from emp where job=(select job from emp where ename='SCOTT'); ENAME JOB ---------- --------- SCOTT ANALYST FORD ANALYST 已用时间: 00: 00: 00.00 SQL> --多行单列子查询 使用in,any(>any:b比其中一个大就ok),all (其中的所有数据都满足) SQL> -- 查询和salesman在同一个部门,却不是SALESMAN的职位 SQL> select ename, deptno from emp where deptno in(select deptno from emp where job='SALESMAN') and job !='SALESMAN'; ENAME DEPTNO ---------- ---------- JAMES 30 BLAKE 30 已用时间: 00: 00: 00.00 SQL> -- 查询比salesman最低工资高的所有人信息 SQL> select ename, sal, deptno from emp where sal >any(select sal from emp where job='SALESMAN') and job!='SALESMAN'; ENAME SAL DEPTNO ---------- ---------- ---------- KING 5000 10 SCOTT 3000 20 FORD 3000 20 JONES 2975 20 BLAKE 2850 30 CLARK 2450 10 MILLER 1300 10 已选择7行。 已用时间: 00: 00: 00.00 SQL> --多行多列子查询 SQL> --查询和scott相同部门和职位的员工 SQL> select ename, job, deptno from emp where (job, deptno ) in (select job, deptno from emp where ename='SCOTT'); ENAME JOB DEPTNO ---------- --------- ---------- FORD ANALYST 20 SCOTT ANALYST 20 已用时间: 00: 00: 00.00 SQL> --行内视图:把子查询当视图 SQL> --查询薪水比本部门平均薪水高的员工 语句一使用子查询,语句二：使用视图 比较容易理解,先查询出每个部门的平均值,再用原语句和结果比较 SQL> select ename, sal, deptno from emp e where sal>(select avg(sal) from emp x where x.deptno = e.deptno) order by deptno; ENAME SAL DEPTNO ---------- ---------- ---------- KING 5000 10 JONES 2975 20 SCOTT 3000 20 FORD 3000 20 ALLEN 1600 30 BLAKE 2850 30 已选择6行。 已用时间: 00: 00: 00.00 SQL> select ename, sal, deptno from emp e (select deptno, avg(sal) avg_sal from emp group by deptno) x 2 where e.deptno = x.deptno and e.sal>x.avg_sal order by deptno; select ename, sal, deptno from emp e (select deptno, avg(sal) avg_sal from emp group by deptno) x * 第 1 行出现错误: ORA-00933: SQL 命令未正确结束 已用时间: 00: 00: 00.00 SQL> SQL> --exists语句 只要子查询语句有结果,那么这条主查询语句就有结果 获取有员工的部门信息 SQL> select deptno, dname, loc from dept where exists (select * from emp where emp.deptno = dept.deptno); DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 已用时间: 00: 00: 00.00 SQL> SQL> --集合查询 select statement1 [union|union all|intersect|minus] select statemens2 并集 交集 差集 SQL> --职位是manager但是薪水低于2500 语句一使用交集,二使用差集 SQL> select ename, job, sal from emp where job='MANAGER' intersect select ename, job, sal from emp where sal<2500; ENAME JOB SAL ---------- --------- ---------- CLARK MANAGER 2450 已用时间: 00: 00: 00.00 SQL> select ename, job, sal from emp where job='MANAGER' minus select ename, job, sal from emp where sal>2500; ENAME JOB SAL ---------- --------- ---------- CLARK MANAGER 2450 已用时间: 00: 00: 00.00 SQL> SQL> spool off;