Oarcle继续学习中。。。。。。
/*
以下代码是对emp表/dept表/salgrade表进行显示宽度设置*/
col empno for 9999;
col ename for a10;
col job for a10;
col mgr for 9999;
col hiredate for a12;
col sal for 9999;
col comm for 9999;
col deptno for 99;
col dname for a14;
col loc for a14;
col grade for 9999;
set pagesize 20;
select * from emp;
- 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
- 已选择14行。
select * from dept;
- sql> select * from dept;
- DEPTNO DNAME LOC
- ------ -------------- --------------
- 10 ACCOUNTING NEW YORK
- 20 RESEARCH DALLAS
- 30 SALES CHICAGO
- 40 OPERATIONS BOSTON
select * from salgrade;
@H_404_30@sql> select * from salgrade; GRADE LOSAL HISAL ----- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999员工表和部门表的笛卡尔集(笛卡尔集表=列数之和,行数之积)
select * from dept,salgrade;
最终产生的表,列是各子表列之和,行是各子表行之积
笛卡尔集的结果无任何实际意思,我们得从笛卡尔集的结果中抽选中有意思的数据
如果通过表名.列名来取得某个表中的字段
等值连接,显示员工的编号,姓名,部门
- sql> select * from dept,salgrade;
- DEPTNO DNAME LOC GRADE LOSAL HISAL
- ------ -------------- -------------- ----- ---------- ----------
- 10 ACCOUNTING NEW YORK 1 700 1200
- 10 ACCOUNTING NEW YORK 2 1201 1400
- 10 ACCOUNTING NEW YORK 3 1401 2000
- 10 ACCOUNTING NEW YORK 4 2001 3000
- 10 ACCOUNTING NEW YORK 5 3001 9999
- 20 RESEARCH DALLAS 1 700 1200
- 20 RESEARCH DALLAS 2 1201 1400
- 20 RESEARCH DALLAS 3 1401 2000
- 20 RESEARCH DALLAS 4 2001 3000
- 20 RESEARCH DALLAS 5 3001 9999
- 30 SALES CHICAGO 1 700 1200
- 30 SALES CHICAGO 2 1201 1400
- 30 SALES CHICAGO 3 1401 2000
- 30 SALES CHICAGO 4 2001 3000
- 30 SALES CHICAGO 5 3001 9999
- 40 OPERATIONS BOSTON 1 700 1200
- 40 OPERATIONS BOSTON 2 1201 1400
- DEPTNO DNAME LOC GRADE LOSAL HISAL
- ------ -------------- -------------- ----- ---------- ----------
- 40 OPERATIONS BOSTON 3 1401 2000
- 40 OPERATIONS BOSTON 4 2001 3000
- 40 OPERATIONS BOSTON 5 3001 9999
- 已选择20行。
select emp.empno,emp.ename,dept.dnamefrom emp,dept
where emp.deptno=dept.deptno;
使用表别名,等值连接,显示员工的编号,姓名,部门
- sql> select emp.empno,dept.dname from emp,dept where emp.deptno=
- 2 dept.deptno;
- EMPNO ENAME DNAME
- ----- ---------- --------------
- 7782 CLARK ACCOUNTING
- 7839 KING ACCOUNTING
- 7934 MILLER ACCOUNTING
- 7566 JONES RESEARCH
- 7902 FORD RESEARCH
- 7876 ADAMS RESEARCH
- 7369 SMITH RESEARCH
- 7788 SCOTT RESEARCH
- 7521 WARD SALES
- 7844 TURNER SALES
- 7499 ALLEN SALES
- 7900 JAMES SALES
- 7698 BLAKE SALES
- 7654 MARTIN SALES
- 已选择14行。
select e.empno,e.ename,d.dnamefrom emp e,dept dwhere e.deptno=d.deptno;
oracle表名不能出现as关j键字,字段可以
不等值连接,显示员工的编号,姓名,月薪,级别,只要不是使用=号即就
- sql> select e.empno,d.dname from emp e,dept d where e.deptno=d.deptno;
- EMPNO ENAME DNAME
- ----- ---------- --------------
- 7782 CLARK ACCOUNTING
- 7839 KING ACCOUNTING
- 7934 MILLER ACCOUNTING
- 7566 JONES RESEARCH
- 7902 FORD RESEARCH
- 7876 ADAMS RESEARCH
- 7369 SMITH RESEARCH
- 7788 SCOTT RESEARCH
- 7521 WARD SALES
- 7844 TURNER SALES
- 7499 ALLEN SALES
- 7900 JAMES SALES
- 7698 BLAKE SALES
- 7654 MARTIN SALES
- 已选择14行。
select e.empno,e.sal,s.gradefrom emp e,salgrade swhere e.sal between s.losal and s.hisal;
【左外连接】,按部门,统计员工人数,显示部门号,部门名,人数
- sql> select e.empno,s.grade from emp e,salgrade s where e.sal betw
- een s.losal and s.hisal;
- EMPNO ENAME SAL GRADE
- ----- ---------- ----- -----
- 7369 SMITH 800 1
- 7900 JAMES 950 1
- 7876 ADAMS 1100 1
- 7521 WARD 1250 2
- 7654 MARTIN 1250 2
- 7934 MILLER 1300 2
- 7844 TURNER 1500 3
- 7499 ALLEN 1600 3
- 7782 CLARK 2450 4
- 7698 BLAKE 2850 4
- 7566 JONES 2975 4
- 7788 SCOTT 3000 4
- 7902 FORD 3000 4
- 7839 KING 5000 5
- 已选择14行。
select d.deptno "部门号",d.dname "部门名",count(e.deptno) "人数" from dept d,emp e
where d.deptno=e.deptno(+) group by d.deptno,d.dname;
(+)号一定是出现在where子句中,
依据(+)出现在=号二个的位置不同,叫法不相:
(+)出现在=号左边,叫右[外]连接
(+)出现在=号右边,叫左[外]连接
依据=号二边,看哪一边少了值,就将(+)号加上少值的那边
右外连接,按部门,统计员工人数,显示部门号,部门名,人数
- sql> select d.deptno "部门号",count(e.deptno) "人数" from dept
- d,emp e
- 2 where d.deptno=e.deptno(+) group by d.deptno,d.dname;
- 部门号 部门名 人数
- ---------- ---------------------------- ----------
- 10 ACCOUNTING 3
- 40 OPERATIONS 0
- 20 RESEARCH 5
- 30 SALES 6
select d.deptno "部门号",count(e.deptno) "人数"
from dept d,emp ewhere d.deptno(+)=e.deptnogroup by d.deptno,d.dname;
自连接,显示"SMITH的老板是FORD"这种格式
- sql> select d.deptno "部门号",count(e.deptno) "人数"
- 2 from dept d,emp e where d.deptno(+)=e.deptno group by d.deptno,d.dname;
- 部门号 部门名 人数
- ---------- ---------------------------- ----------
- 10 ACCOUNTING 3
- 20 RESEARCH 5
- 30 SALES 6
select e.ename || '的老板是' || b.enamefrom emp e,emp bwhere e.mgr = b.empno;
原则:将一个表,看作二张不同的表
- sql> select e.ename || '的老板是' || b.ename from emp e,emp b where e.mgr = b
- .empno;
- E.ENAME||'的老板是'||B.ENAME
- ----------------------------------------------------------------
- FORD的老板是JONES
- SCOTT的老板是JONES
- TURNER的老板是BLAKE
- ALLEN的老板是BLAKE
- WARD的老板是BLAKE
- JAMES的老板是BLAKE
- MARTIN的老板是BLAKE
- MILLER的老板是CLARK
- ADAMS的老板是SCOTT
- BLAKE的老板是KING
- JONES的老板是KING
- CLARK的老板是KING
- SMITH的老板是FORD
- 已选择13行。
查询工资比SCOTT高的员工信息
子查询主要解决:条件是未知的查询,而且需要经过多个步骤才能完成。
1)SCOTT的工资?
select * from emp where ename='SCOTT';
2) 比3000高的员工信息?
- sql> select * from emp where ename='SCOTT';
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
select * from emp where sal>3000;
3)将1)和2)二步合二为一,形成主子查询
- sql> select * from emp where sal>3000;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7839 KING PRESIDENT 17-11月-81 5000 10
select *from emp where sal>(select salfrom empwhere ename='SCOTT');
- sql> select * from emp where sal>(select sal from emp where ename='SCOTT'
- );
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7839 KING PRESIDENT 17-11月-81 5000 10
查询部门名为'ACCOUNTING'的员工信息(方式一:子查询)
- <span style="font-family: Arial,Helvetica,sans-serif; background-color: rgb(255,255,255);"></span>
1)查询部门名为'ACCOUNTING'的部门号
select deptno from dept where dname='ACCOUNTING';
2)根据10号查询对应的员工信息
- sql> select deptno from dept where dname='ACCOUNTING';
- DEPTNO
- ------
- 10
select * from emp where deptno=10;
3)将1)和2)二步合二为一,形成主子查询
- sql> select * from emp where deptno=10;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
select *
from emp
where deptno=(
select deptno
from dept
where dname='ACCOUNTING');
查询部门名为'ACCOUNTING'的员工信息(方式二:多表查询,优先)
- sql> select *
- 2 from emp
- 3 where deptno=(
- 4 select deptno
- 5 from dept
- 6 where dname='ACCOUNTING' );
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
select e.ename,e.deptnofrom emp e,dept d
where d.deptno=e.deptno and d.dname='ACCOUNTING';
- sql> select e.ename,e.deptno from emp e,dept d
- 2 where d.deptno=e.deptno and d.dname='ACCOUNTING';
- ENAME SAL DEPTNO
- ---------- ----- ------
- CLARK 2450 10
- KING 5000 10
- MILLER 1300 10
查询工资最低的员工信息
1)查询工资最低是多少钱?
select min(sal) from emp;
2)查询800的员工信息
- sql> select min(sal)from emp;
- MIN(SAL)
- ----------
- 800
select * from emp where sal=800;
3)将1)和2)二步合二为一,形成主子查询
- sql> select * from emp where sal=800;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7369 SMITH CLERK 7902 17-12月-80 800 20
select *from empwhere sal=(select min(sal)from emp);
查询部门名为'ACCOUNTING'或'SALES'的员工(方式一:子查询)
- sql> select * from emp where sal=(select min(sal) from emp );
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7369 SMITH CLERK 7902 17-12月-80 800 20
select *
from emp
where deptno = (
select deptno
from dept
where dname='ACCOUNTING' or dname='SALES'
);
-------------------------【以上代码出错,原因在于主查询只需要一个值,但是子查询确返回多个值】
select *
from emp
where deptno in (
select deptno
from dept
where dname='ACCOUNTING' or dname='SALES'
);
查询部门名为'ACCOUNTING'或'SALES'的员工(方式二:多表查询)
- sql> select *
- 2 from emp
- 3 where deptno in (
- 4 select deptno
- 5 from dept
- 6 where dname='ACCOUNTING' or dname='SALES'
- 7 );
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 已选择9行。
select e.* from dept d,emp e
where d.deptno=e.deptno and d.dname in('ACCOUNTING','SALES');
- <pre name="code" class="sql">sql> select e.* from dept d,emp e
- 2 where d.deptno=e.deptno and d.dname in('ACCOUNTING','SALES');
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 已选择9行。
查询工资比10号部门【任意】一个员工【低】的员工信息1)查询10号部门的员工工资 select * from emp where deptno=10;
2)查询员工比2450或5000或1300低的员
- sql> select * from emp where deptno=10;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
select *
from emp
where sal<2450 or sal<5000 or sal<1300;
3)将1)和2)二步合二为一,形成主子查询
- sql> select *
- 2 from emp
- 3 where sal<2450 or sal<5000 or sal<1300;
- 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
- 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
- 已选择13行。
select *
from emp
where sal < any (select sal from emp where deptno=10);
<any 就相当于<最大值
查询工资比10号部门【所有】员工【低】的员工信息
- sql> select *
- 2 from emp
- 3 where sal < any (select sal from emp where deptno=10);
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 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
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 已选择13行。
1)查询10号部门的员工工资
select * from emp where deptno=10;
2)查询员工比2450和5000和1300低的员
- sql> select * from emp where deptno=10;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
select *
from emp
where sal<2450 and sal<5000 and sal<1300;
3)将1)和2)二步合二为一,形成主子查询
- sql> select *
- 2 from emp
- 3 where sal<2450 and sal<5000 and sal<1300;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 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
select *
from emp
where sal < all (select sal from emp where deptno=10);
<all 就相当于<最小值
查询'CLERK'的领导信息,那查询'PRESIDENT'的领导信息呢?说明null值问题
- sql> select *
- 2 from emp
- 3 where sal < all (select sal from emp where deptno=10);
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- ---------- ----- ------------ ----- ----- ------
- 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
1)select ename,mgr from emp where job='CLERK'; 子查询---mgr
2)select ename from emp where empno=7902; 主查询---empno
- sql> select ename,mgr from emp where job='CLERK';
- ENAME MGR
- ---------- -----
- SMITH 7902
- ADAMS 7788
- JAMES 7698
- MILLER 7782
3)将1)和2)二步合二为一,形成主子查询
- sql> select ename from emp where empno=7902;
- ENAME
- ----------
- FORD
select ename "领导名"
from emp
where empno in (
select mgr
from emp
where job='CLERK'
);
- sql> select ename "领导名"
- 2 from emp
- 3 where empno in (
- 4 select mgr
- 5 from emp
- 6 where job='CLERK'
- 7 );
- 领导名
- --------------------
- BLAKE
- CLARK
- SCOTT
- FORD