--对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
1300 2450 5000 2916 (1300+2450+5000)/3
950 2975 3000 2175 (950+2975+3000)/3
950 2850 1400 1566 (950+2850+1400)/3
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:\tempfile\oracle\input.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
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
SMITH CLERK 7902 17-12月-80 800 20
ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
WARD SALESMAN 7698 22-2月 -81 1250 500 30
JONES MANAGER 7839 02-4月 -81 2975 20
MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
BLAKE MANAGER 7839 01-5月 -81 2850 30
CLARK MANAGER 7839 09-6月 -81 2450 10
SCOTT ANALYST 7566 19-4月 -87 3000 20
KING PRESIDENT 17-11月-81 5000 10
TURNER SALESMAN 7698 08-9月 -81 1500 0 30
ADAMS CLERK 7788 23-5月 -87 1100 20
JAMES CLERK 7698 03-12月-81 950 30
FORD ANALYST 7566 03-12月-81 3000 20
MILLER CLERK 7782 23-1月 -82 1300 10
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
---------- -------------- -------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON
已用时间: 00: 00: 00.00
SQL>
SQL>
SQL> --基本查询
SQL>
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON
已用时间: 00: 00: 00.00
SQL>
SQL> select empno, ename, job from emp;
EMPNO 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> --通过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
---------- ---------- ---------- -----------
SMITH 800 9600
ALLEN 1600 19200
WARD 1250 15000
JONES 2975 35700
MARTIN 1250 15000
BLAKE 2850 34200
CLARK 2450 29400
SCOTT 3000 36000
KING 5000 60000
TURNER 1500 18000
ADAMS 1100 13200
JAMES 950 11400
FORD 3000 36000
MILLER 1300 15600
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
---------- ---------- ---------- -------------
SMITH 800 9600
ALLEN 1600 19200
WARD 1250 15000
JONES 2975 35700
MARTIN 1250 15000
BLAKE 2850 34200
CLARK 2450 29400
SCOTT 3000 36000
KING 5000 60000
TURNER 1500 18000
ADAMS 1100 13200
JAMES 950 11400
FORD 3000 36000
MILLER 1300 15600
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] 闭区间
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
---------- ---------- ----------
JOHN_SMITH
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
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
---------- ---------- --------- ----------
FORD ANALYST 3000
SCOTT ANALYST 3000
MILLER CLERK 1300
ADAMS CLERK 1100
JAMES CLERK 950
SMITH CLERK 800
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
KING PRESIDENT 5000
ALLEN SALESMAN 1600
TURNER SALESMAN 1500
WARD SALESMAN 1250
MARTIN SALESMAN 1250
JOHN_SMITH
已选择15行。
已用时间: 00: 00: 00.00
SQL>
SQL>
SQL> --分组查询
SQL> select count(ename) num from emp ;
NUM
----------
已用时间: 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
----------- ----------
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)
---------- ---------- ---------- ---------- ----------
2916.66667 8750 5000 1300
2175 10875 3000 800
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
---------- --------- ---------- ----------
CLERK 1300 1300
MANAGER 2450 2450
PRESIDENT 5000 5000
ANALYST 3000 3000
CLERK 950 1100
MANAGER 2975 2975
CLERK 950 950
MANAGER 2850 2850
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
---------- --------- ----------
CLERK 1300
MANAGER 2450
PRESIDENT 5000
2916.66667
CLERK 950
ANALYST 3000
MANAGER 2975
2175
CLERK 950
MANAGER 2850
SALESMAN 1400
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
2916.66667
CLERK 1300
MANAGER 2450
PRESIDENT 5000
2175
CLERK 950
ANALYST 3000
MANAGER 2975
1566.66667
CLERK 950
MANAGER 2850
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
---------- ----------
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
---------- ---------- ---------- ----------
FORD 7566 JONES
SCOTT 7566 JONES
TURNER 7698 BLAKE
ALLEN 7698 BLAKE
WARD 7698 BLAKE
JAMES 7698 BLAKE
MARTIN 7698 BLAKE
MILLER 7782 CLARK
ADAMS 7788 SCOTT
BLAKE 7839 KING
JONES 7839 KING
CLARK 7839 KING
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
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
---------- -------------- -------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
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;