柚子快报邀请码778899分享：day43
八、子查询
示例代码如下：子查询.txt
SQL> --rownum 行号SQL> select rownum,empno,ename,sal from emp; ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 1 7369 SMITH 800 2 7499 ALLEN 1600 3 7521 WARD 1250 4 7566 JONES 2975 5 7654 MARTIN 1250 6 7698 BLAKE 2850 7 7782 CLARK 2450 8 7788 SCOTT 3000 9 7839 KING 5000 10 7844 TURNER 1500 11 7876 ADAMS 1100 ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 12 7900 JAMES 950 13 7902 FORD 3000 14 7934 MILLER 1300 已选择 14 行。SQL> select rownum,empno,ename,sal 2 from emp 3 where rownum<=3 4 order by sal desc; --按照薪水降序排列 ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 2 7499 ALLEN 1600 3 7521 WARD 1250 1 7369 SMITH 800 SQL> /*SQL> 关于行号rownumSQL> 1. rownum永远按照默认的顺序生成SQL> 2. rownum只能使用 < <=; 不能使用 > >=SQL> */SQL> select rownum,empno,ename,sal from emp order by sal desc; ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 9 7839 KING 5000 13 7902 FORD 3000 8 7788 SCOTT 3000 4 7566 JONES 2975 6 7698 BLAKE 2850 7 7782 CLARK 2450 2 7499 ALLEN 1600 10 7844 TURNER 1500 14 7934 MILLER 1300 3 7521 WARD 1250 5 7654 MARTIN 1250 ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 11 7876 ADAMS 1100 12 7900 JAMES 950 1 7369 SMITH 800 已选择 14 行。SQL> --第一题：找到员工表中工资最高的前三名SQL> select rownum,empno,ename,sal 2 from (select * from emp order by sal desc) --子查询得到新表 3 where rownum<=3; ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 1 7839 KING 5000 2 7788 SCOTT 3000 3 7902 FORD 3000 SQL> --2. rownum只能使用 < <=; 不能使用 > >=SQL> --分页SQL> select rownum,empno,ename,sal from emp 2 where rownum>=5 and rownum<=8;未选定行SQL> select rownum,empno,ename,sal from emp 2 where rownum>=5;未选定行SQL> 原因：Oracle数据库是行式数据库，NOSQL数据库是列式数据库。SQL> ed已写入 file afiedt.buf 1 select rownum,empno,ename,sal from emp 2* where rownum<=8SQL> / ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 1 7369 SMITH 800 2 7499 ALLEN 1600 3 7521 WARD 1250 4 7566 JONES 2975 5 7654 MARTIN 1250 6 7698 BLAKE 2850 7 7782 CLARK 2450 8 7788 SCOTT 3000 已选择 8 行。SQL> select * 2 from (select rownum r,e1.* 3 from (select * from emp order by sal) e1 --r是e1表的行号，是e2表的列 4 where rownum<=8 5 ) 6 where r>=5; R EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 5 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 6 7934 MILLER CLERK 7782 23-1月 -82 1300 10 7 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 8 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 SQL> /*SQL> 临时表:SQL> 1. 手动创建：create global temporary table *****SQL> 2. 自动创建: order by 排序SQL> 临时表的特点：当事务或者会话结束的时候，表中的数据会自动删除，但表结构还存在。SQL> 在Oracle中，事务提交了，数据不一定保存下来了。SQL> */SQL> create global temporary table test2 2 (tid number,tname varchar2(20)) 3 on commit delete rows;表已创建。SQL> insert into test2 values(1,'Tom');已创建 1 行。SQL> select * from test2; TID TNAME ---------- -------------------- 1 Tom SQL> commit;提交完成。SQL> select * from test2;未选定行SQL> desc test2 名称 是否为空? 类型 ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- TID NUMBER TNAME VARCHAR2(20)SQL> host clsSQL> --第二题：找到员工表中薪水大于本部门平均薪水的员工。SQL> select e.empno,e.ename,e.sal,d.avgsal 2 from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d 3 where e.deptno=d.deptno and e.sal > d.avgsal; EMPNO ENAME SAL AVGSAL ---------- ---------- ---------- ---------- 7698 BLAKE 2850 1566.66667 7499 ALLEN 1600 1566.66667 7902 FORD 3000 2175 7788 SCOTT 3000 2175 7566 JONES 2975 2175 7839 KING 5000 2916.66667 已选择 6 行。SQL> --相关子查询：将主查询中的值作为参数传递给子查询。SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal 2 from emp e 3 where sal > (select avg(sal) from emp where deptno=e.deptno); EMPNO ENAME SAL AVGSAL ---------- ---------- ---------- ---------- 7499 ALLEN 1600 1566.66667 7566 JONES 2975 2175 7698 BLAKE 2850 1566.66667 7788 SCOTT 3000 2175 7839 KING 5000 2916.66667 7902 FORD 3000 2175 已选择 6 行。SQL> host clsSQL> --第三题：统计每年入职的员工个数。不能使用子查询。SQL> select hiredate from emp;HIREDATE -------------- 17-12月-80 20-2月 -81 22-2月 -81 02-4月 -81 28-9月 -81 01-5月 -81 09-6月 -81 19-4月 -87 17-11月-81 08-9月 -81 23-5月 -87 HIREDATE -------------- 03-12月-81 03-12月-81 23-1月 -82 已选择 14 行。SQL> /*SQL> 思路讲解：SQL> select count(*) Total,SQL> SQL> sum(if 是81年 then +1 else +0) "1981",SQL> SQL> from emp;SQL> SQL> HIREDATE count81 number:=0;SQL> ---------------------------SQL> 17-12月-80 0SQL> 20-2月 -81 1SQL> 22-2月 -81 1SQL> 02-4月 -81 1SQL> 28-9月 -81 1SQL> 01-5月 -81 1SQL> 09-6月 -81 1SQL> 19-4月 -87 0SQL> 17-11月-81 1SQL> 08-9月 -81 1SQL> 23-5月 -87 0SQL> 03-12月-81 1SQL> 03-12月-81 1SQL> 23-1月 -82 0SQL> ---------------------SQL> 10SQL> */SQL> host clsSQL> --行转列函数SQL> -- wm_concat(varchar2) 这是一个组函数，注意：对于组函数，没有包含在该函数中的列，必须要在group by语句的后面。SQL> select deptno,wm_concat(ename) nameslist 2 from emp 3 group by deptno; --按部门号分组 DEPTNO ---------- NAMESLIST -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD SQL> col nameslist for a60SQL> select deptno,wm_concat(ename) nameslist 2 from emp 3 group by deptno; DEPTNO NAMESLIST ---------- ------------------------------------------------------------ 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD SQL> spool off
课堂练习：
SQL> --rownum 行号SQL> select rownum,empno,ename,sal from emp; ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 1 7369 SMITH 800 2 7499 ALLEN 1600 3 7521 WARD 1250 4 7566 JONES 2975 5 7654 MARTIN 1250 6 7698 BLAKE 2850 7 7782 CLARK 2450 8 7788 SCOTT 3000 9 7839 KING 5000 10 7844 TURNER 1500 11 7876 ADAMS 1100 ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 12 7900 JAMES 950 13 7902 FORD 3000 14 7934 MILLER 1300 已选择 14 行。SQL> select rownum,empno,ename,sal 2 from emp 3 where rownum<=3 4 order by sal desc; --按照薪水降序排列 ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 2 7499 ALLEN 1600 3 7521 WARD 1250 1 7369 SMITH 800 SQL> /*SQL> 关于行号rownumSQL> 1. rownum永远按照默认的顺序生成SQL> 2. rownum只能使用 < <=; 不能使用 > >=SQL> */SQL> select rownum,empno,ename,sal from emp order by sal desc; ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 9 7839 KING 5000 13 7902 FORD 3000 8 7788 SCOTT 3000 4 7566 JONES 2975 6 7698 BLAKE 2850 7 7782 CLARK 2450 2 7499 ALLEN 1600 10 7844 TURNER 1500 14 7934 MILLER 1300 3 7521 WARD 1250 5 7654 MARTIN 1250 ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 11 7876 ADAMS 1100 12 7900 JAMES 950 1 7369 SMITH 800 已选择 14 行。SQL> --第一题：找到员工表中工资最高的前三名SQL> select rownum,empno,ename,sal 2 from (select * from emp order by sal desc) --子查询得到新表 3 where rownum<=3; ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 1 7839 KING 5000 2 7788 SCOTT 3000 3 7902 FORD 3000 SQL> --2. rownum只能使用 < <=; 不能使用 > >=SQL> --分页SQL> select rownum,empno,ename,sal from emp 2 where rownum>=5 and rownum<=8;未选定行SQL> select rownum,empno,ename,sal from emp 2 where rownum>=5;未选定行SQL> 原因：Oracle数据库是行式数据库，NOSQL数据库是列式数据库。SQL> ed已写入 file afiedt.buf 1 select rownum,empno,ename,sal from emp 2* where rownum<=8SQL> / ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 1 7369 SMITH 800 2 7499 ALLEN 1600 3 7521 WARD 1250 4 7566 JONES 2975 5 7654 MARTIN 1250 6 7698 BLAKE 2850 7 7782 CLARK 2450 8 7788 SCOTT 3000 已选择 8 行。SQL> select * 2 from (select rownum r,e1.* 3 from (select * from emp order by sal) e1 --r是e1表的行号，是e2表的列 4 where rownum<=8 5 ) 6 where r>=5; R EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 5 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 6 7934 MILLER CLERK 7782 23-1月 -82 1300 10 7 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 8 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 SQL> /*SQL> 临时表:SQL> 1. 手动创建：create global temporary table *****SQL> 2. 自动创建: order by 排序SQL> 临时表的特点：当事务或者会话结束的时候，表中的数据会自动删除，但表结构还存在。SQL> 在Oracle中，事务提交了，数据不一定保存下来了。SQL> */SQL> create global temporary table test2 2 (tid number,tname varchar2(20)) 3 on commit delete rows;表已创建。SQL> insert into test2 values(1,'Tom');已创建 1 行。SQL> select * from test2; TID TNAME ---------- -------------------- 1 Tom SQL> commit;提交完成。SQL> select * from test2;未选定行SQL> desc test2 名称 是否为空? 类型 ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- TID NUMBER TNAME VARCHAR2(20)SQL> host clsSQL> --第二题：找到员工表中薪水大于本部门平均薪水的员工。SQL> select e.empno,e.ename,e.sal,d.avgsal 2 from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d 3 where e.deptno=d.deptno and e.sal > d.avgsal; EMPNO ENAME SAL AVGSAL ---------- ---------- ---------- ---------- 7698 BLAKE 2850 1566.66667 7499 ALLEN 1600 1566.66667 7902 FORD 3000 2175 7788 SCOTT 3000 2175 7566 JONES 2975 2175 7839 KING 5000 2916.66667 已选择 6 行。SQL> --相关子查询：将主查询中的值作为参数传递给子查询。SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal 2 from emp e 3 where sal > (select avg(sal) from emp where deptno=e.deptno); EMPNO ENAME SAL AVGSAL ---------- ---------- ---------- ---------- 7499 ALLEN 1600 1566.66667 7566 JONES 2975 2175 7698 BLAKE 2850 1566.66667 7788 SCOTT 3000 2175 7839 KING 5000 2916.66667 7902 FORD 3000 2175 已选择 6 行。SQL> host clsSQL> --第三题：统计每年入职的员工个数。不能使用子查询。SQL> select hiredate from emp;HIREDATE -------------- 17-12月-80 20-2月 -81 22-2月 -81 02-4月 -81 28-9月 -81 01-5月 -81 09-6月 -81 19-4月 -87 17-11月-81 08-9月 -81 23-5月 -87 HIREDATE -------------- 03-12月-81 03-12月-81 23-1月 -82 已选择 14 行。SQL> /*SQL> 思路讲解：SQL> select count(*) Total,SQL> SQL> sum(if 是81年 then +1 else +0) "1981",SQL> SQL> from emp;SQL> SQL> HIREDATE count81 number:=0;SQL> ---------------------------SQL> 17-12月-80 0SQL> 20-2月 -81 1SQL> 22-2月 -81 1SQL> 02-4月 -81 1SQL> 28-9月 -81 1SQL> 01-5月 -81 1SQL> 09-6月 -81 1SQL> 19-4月 -87 0SQL> 17-11月-81 1SQL> 08-9月 -81 1SQL> 23-5月 -87 0SQL> 03-12月-81 1SQL> 03-12月-81 1SQL> 23-1月 -82 0SQL> ---------------------SQL> 10SQL> select count(*) Total, 2 sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980", 3 sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981", 4 sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982", 5 sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987" 6 from emp; TOTAL 1980 1981 1982 1987 ---------- ---------- ---------- ---------- ---------- 14 1 10 1 2 SQL> */SQL> host clsSQL> --行转列函数SQL> -- wm_concat(varchar2) 这是一个组函数，注意：对于组函数，没有包含在该函数中的列，必须要在group by语句的后面。SQL> select deptno,wm_concat(ename) nameslist 2 from emp 3 group by deptno; --按部门号分组 DEPTNO ---------- NAMESLIST -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD SQL> col nameslist for a60SQL> select deptno,wm_concat(ename) nameslist 2 from emp 3 group by deptno; DEPTNO NAMESLIST ---------- ------------------------------------------------------------ 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD SQL> spool off
九、集合运算
描述集合运算符，如下图所示：
示例代码如下：集合运算.txt
SQL> /*SQL> 查询10和20号部门的员工SQL> 1. select * from emp where deptno in (10,20);SQL> 2. select * from emp where deptno=10 or deptno=20;SQL> 3. 集合运算SQL> select * from emp where deptno=10SQL> 加上SQL> select * from emp where deptno=20SQL> */SQL> select * from emp where deptno=10 2 union 3 select * from emp where deptno=20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7566 JONES MANAGER 7839 02-4月 -81 2975 20 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 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择 8 行。SQL> host clsSQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job); DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 DEPTNO JOB SUM(SAL) ---------- --------- ---------- 30 9400 29025 已选择 13 行。SQL> select deptno,job,sum(sal) from emp group by deptno,job 2 union 3 select deptno,sum(sal) from emp group by deptno 4 union 5 select sum(sal) from emp; select deptno,sum(sal) from emp group by deptno *第 3 行出现错误: ORA-01789: 查询块具有不正确的结果列数 SQL> /*SQL> 集合运算需要注意的问题：SQL> 1. 参与运算的各个集合必须列数相同且类型一致SQL> 2. 采用第一个集合作为最后的表头，即列的别名要起在第一个集合SQL> 3. order by 永远在最后一句查询语句后面SQL> 4. 使用括号改变集合运算顺序SQL> */SQL> select deptno,job,sum(sal) from emp group by deptno,job 2 union 3 select deptno,to_char(null),sum(sal) from emp group by deptno 4 union 5 select to_number(null),to_char(null),sum(sal) from emp; DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 DEPTNO JOB SUM(SAL) ---------- --------- ---------- 30 9400 29025 已选择 13 行。SQL> --break on deptno skip 2SQL> host clsSQL> --SQL 语句执行时间SQL> set timing on --查看SQL 语句执行时间的开关SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job); DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 DEPTNO JOB SUM(SAL) ---------- --------- ---------- 30 9400 29025 已选择 13 行。已用时间: 00: 00: 00.02SQL> select deptno,job,sum(sal) from emp group by deptno,job 2 union 3 select deptno,to_char(null),sum(sal) from emp group by deptno 4 union 5 select to_number(null),to_char(null),sum(sal) from emp; DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 DEPTNO JOB SUM(SAL) ---------- --------- ---------- 30 9400 29025 已选择 13 行。已用时间: 00: 00: 00.04SQL> --SQL 语句优化原则SQL> 5. 尽量不要使用集合运算，原因：随着参与结合运算的集合越多，效率越低。SQL> set timing offSQL> spool off
十、数据处理
数据碎片图解：
示例代码如下：数据处理.txt
SQL> select count(*) Total, 2 sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980", 3 sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981", 4 sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982", 5 sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987" 6 from emp; TOTAL 1980 1981 1982 1987 ---------- ---------- ---------- ---------- ---------- 14 1 10 1 2 SQL> /*SQL> SQL 语句的类型SQL> 1. DML(data manipulation Language 数据操作语言): insert update delete selectSQL> 2. DDL(Data Definition Language 数据定义语言): create table,alter table,drop table,truncate tableSQL> create/drop view,sequence(序列),index,synonym(同义词)SQL> 3. DCL(Data Control Language 数据控制语言): grant(授权) revoke(撤销权限)SQL> */SQL> --插入 insertSQL> insert into emp(empno,ename,sal,deptno) values(1001,'Tom',3000,10);已创建 1 行。SQL> --隐式插入空值：在列名表中省略该列的值。SQL> --显式插入空值：在values子句中指定空值。SQL> --之前的学习JDBC中有一个接口PreparedStatement，可以预编译sql语句，可以防止sql注入问题。SQL> --PreparedStatement pst = "insert into emp(empno,ename,sal,deptno) values(?,?,?,?)";SQL> --在Oracle数据库中SQL> --地址符 & 相当于 ?SQL> insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);输入 empno 的值: 1002输入 ename 的值: 'Mary'输入 sal 的值: 2000输入 deptno 的值: 30原值 1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)新值 1: insert into emp(empno,ename,sal,deptno) values(1002,'Mary',2000,30)已创建 1 行。SQL> /输入 empno 的值: 1003输入 ename 的值: 'Mike'输入 sal 的值: 5000输入 deptno 的值: 20原值 1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)新值 1: insert into emp(empno,ename,sal,deptno) values(1003,'Mike',5000,20)已创建 1 行。SQL> 在我们学习的所有sql语句中都可以使用地址符SQL> select empno,ename,sal,&t 2 from emp;输入 t 的值: job原值 1: select empno,ename,sal,&t新值 1: select empno,ename,sal,job EMPNO ENAME SAL JOB ---------- ---------- ---------- --------- 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7566 JONES 2975 MANAGER 7654 MARTIN 1250 SALESMAN 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7788 SCOTT 3000 ANALYST 7839 KING 5000 PRESIDENT 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK EMPNO ENAME SAL JOB ---------- ---------- ---------- --------- 7900 JAMES 950 CLERK 7902 FORD 3000 ANALYST 7934 MILLER 1300 CLERK 1001 Tom 3000 1002 Mary 2000 1003 Mike 5000 已选择 17 行。SQL> select * from &t;输入 t 的值: dept原值 1: select * from &t新值 1: select * from dept DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> rollback; --回滚掉之前插入的数据回退已完成。SQL> host clsSQL> --批处理SQL> create table emp10 as select * from emp where 1=2; --as的作用：可以把emp结果集的结构拷贝到新的表emp10上，由于where的条件结果恒为假，所以只拷贝表结构，没有拷贝表数据。表已创建。SQL> desc emp10 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- EMPNO 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 emp10; --说明新创建的表emp10中没有数据未选定行SQL> --一次性将表emp中的所有10号部门的员工插入到emp10中，对于海量数据，效率比较低。如何解决呢？SQL> insert into emp10 select * from emp where deptno=10;已创建 3 行。SQL> select * from emp10; 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 SQL> /*SQL> Oracle中如何海量拷贝数据SQL> 1. 数据泵(datapump)程序包 --> plsql程序SQL> 2. SQL*LoaderSQL> 3. (数据仓库)外部表SQL> 4. 可传输的表空间SQL> */SQL> host clsSQL> /*SQL> delete和truncate的区别:SQL> 1. delete逐条删除，truncate先摧毁表，再重建表SQL> 2. (根本区别)delete是DML，truncate是DDLSQL> （可以回滚） （不可以回滚）SQL> 3. delete不会释放空间，truncate会释放空间SQL> 4. delete可以闪回，truncate不可以闪回SQL> (flashback)SQL> 5. delete会产生碎片，truncate不会产生碎片SQL> */SQL> set feedback off --由于演示插入的数据很多，所以我们先把插入回显信息关掉，暂时不让它回显了。SQL> @d:\temp\testdelete.sqlSQL> select count(*) from testdelete; COUNT(*) ---------- 5000 SQL> set timing onSQL> delete from testdelete;已用时间: 00: 00: 00.06SQL> set timing offSQL> drop table testdelete purge; --SQL> @d:\temp\testdelete.sqlSQL> select count(*) from testdelete; COUNT(*) ---------- 5000 SQL> set timing onSQL> truncate table testdelete;已用时间: 00: 00: 00.15SQL> set timing offSQL> --原因：SQL> --Oracle中的undo数据（还原数据）SQL> set feedback offSQL> host csSQL> /*SQL> Oracle数据库事务的标志：SQL> 1. 起始标志：事务中第一条DML语句，例如：insert update delete selectSQL> 2. 结束标志：提交：显式：commitSQL> 隐式：正常退出(exit)，DDL语句，DCL语句SQL> 回滚: 显式：rollbackSQL> 隐式：非正常退出，掉电，宕机(死机)SQL> */SQL> create table testsavepoint 2 (tid number,tname varchar2(20));SQL> set feedback onSQL> insert into testsavepoint values(1,'Tom');已创建 1 行。SQL> insert into testsavepoint values(2,'Mary');已创建 1 行。SQL> --定义保存点(存储点)SQL> savepoint a;保存点已创建。SQL> select * from testsavepoint; TID TNAME ---------- -------------------- 1 Tom 2 Mary 已选择 2 行。SQL> insert into testsavepoint values(3,'Maake');已创建 1 行。SQL> select * from testsavepoint; TID TNAME ---------- -------------------- 1 Tom 2 Mary 3 Maake 已选择 3 行。SQL> rollback to savepoint a; --回滚到保存点a回退已完成。SQL> select * from testsavepoint; TID TNAME ---------- -------------------- 1 Tom 2 Mary 已选择 2 行。SQL> commit;提交完成。SQL> --SQL99标准提供了4中事务隔离级别。SQL> --MySql中支持4种事务隔离级别：read uncommitted、read committed、repeatable read(默认的隔离级别) 和 serializableSQL> --Oracle中支持3种事务隔离级别：read committed(默认的隔离级别) 、serializable 和 read onlySQL> set transaction read only; --做一个事务的时候，不想要别的事务来打扰我，该怎么办？答：在Oracle中设置事务的隔离级别为只读，隔离级别：read only，为Oracle中所特有的。事务处理集。SQL> select * from testsavepoint; TID TNAME ---------- -------------------- 1 Tom 2 Mary 已选择 2 行。SQL> insert into testsavepoint values(3,'Maake'); insert into testsavepoint values(3,'Maake') *第 1 行出现错误: ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作 SQL> rollback;回退已完成。SQL> spool off
十一、创建和管理表
示例代码如下：创建和管理表.txt
SQL> --使用DDL语句创建和管理表SQL> --一共学习10个数据库对象SQL> --create/drop table,view,sequence(序列),index,synonym(同义词)SQL> --存储过程、存储函数、触发器、包、包体SQL> --Oracle数据库默认存储表名是存为大写SQL> create table test3 2 (tid number, 3 tname varchar2(20), 4 hiredate date default sysdate);表已创建。SQL> insert into test3(tid,tname) values(1,'Tom');已创建 1 行。SQL> select * from test3; TID TNAME HIREDATE ---------- -------------------- -------------- 1 Tom 23-11月-15 已选择 1 行。SQL> --数据类型varchar(20) 可变长字符数据char(20) 定长字符数据number() 可变长数值数据data 日期型数据long 可变长字符数据，最大可达到2Gclob 字符数据，最大可达到4Graw and long raw 原始的二进制数据blow 二进制数据，最大可达到4Gbfile 存储外部文件的二进制数据，最大可达到4Growid 行地址SQL> host clsSQL> --行地址 rowid 是一个伪列SQL> select rowid,empno,ename,sal from emp;ROWID EMPNO ENAME SAL ------------------ ---------- ---------- ---------- AAAMfPAAEAAAAAgAAA 7369 SMITH 800 AAAMfPAAEAAAAAgAAB 7499 ALLEN 1600 AAAMfPAAEAAAAAgAAC 7521 WARD 1250 AAAMfPAAEAAAAAgAAD 7566 JONES 2975 AAAMfPAAEAAAAAgAAE 7654 MARTIN 1250 AAAMfPAAEAAAAAgAAF 7698 BLAKE 2850 AAAMfPAAEAAAAAgAAG 7782 CLARK 2450 AAAMfPAAEAAAAAgAAH 7788 SCOTT 3000 AAAMfPAAEAAAAAgAAI 7839 KING 5000 AAAMfPAAEAAAAAgAAJ 7844 TURNER 1500 AAAMfPAAEAAAAAgAAK 7876 ADAMS 1100 ROWID EMPNO ENAME SAL ------------------ ---------- ---------- ---------- AAAMfPAAEAAAAAgAAL 7900 JAMES 950 AAAMfPAAEAAAAAgAAM 7902 FORD 3000 AAAMfPAAEAAAAAgAAN 7934 MILLER 1300 已选择 14 行。SQL> select empno,ename,sal from emp where rowid='AAAMfPAAEAAAAAgAAK'; EMPNO ENAME SAL ---------- ---------- ---------- 7876 ADAMS 1100 已选择 1 行。SQL> --创建表：用来保存20号部门的员工的数据 set linesize 150SQL> set linesize 150SQL> create table emp20 2 as 3 select * from emp where deptno=20; --as的作用：可以把emp结果集的结构拷贝到新的表emp10上，由于where的条件结果恒为假，所以只拷贝表结构，没有拷贝表数据。如果where的条件结果为真，则一并拷贝数据。表已创建。SQL> select * from emp20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7902 FORD ANALYST 7566 03-12月-81 3000 20 已选择 5 行。SQL> --创建表：员工号 姓名 月薪 年薪 部门名称SQL> create table empinfo 2 as 3 select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname 4 from emp e,dept d 5 where e.deptno=d.deptno;SQL> 表已创建。SQL> select * from empinfo; EMPNO ENAME SAL ANNSAL DNAME ---------- ---------- ---------- ---------- -------------- 7369 SMITH 800 9600 RESEARCH 7499 ALLEN 1600 19200 SALES 7521 WARD 1250 15000 SALES 7566 JONES 2975 35700 RESEARCH 7654 MARTIN 1250 15000 SALES 7698 BLAKE 2850 34200 SALES 7782 CLARK 2450 29400 ACCOUNTING 7788 SCOTT 3000 36000 RESEARCH 7839 KING 5000 60000 ACCOUNTING 7844 TURNER 1500 18000 SALES 7876 ADAMS 1100 13200 RESEARCH EMPNO ENAME SAL ANNSAL DNAME ---------- ---------- ---------- ---------- -------------- 7900 JAMES 950 11400 SALES 7902 FORD 3000 36000 RESEARCH 7934 MILLER 1300 15600 ACCOUNTING 已选择 14 行。SQL> --创建一个视图SQL> create view empinfoview 2 as 3 select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname 4 from emp e,dept d 5 where e.deptno=d.deptno;SQL> --下节课讲解SQL> host clsSQL> --修改表：追加新列，修改列，删除列，重命名列，重命名表SQL> desc test3 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- TID NUMBER TNAME VARCHAR2(20) HIREDATE DATESQL> --追加新列SQL> alter table test3 add photo blob;表已更改。SQL> desc test3 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- TID NUMBER TNAME VARCHAR2(20) HIREDATE DATE PHOTO BLOBSQL> --修改列SQL> alter table test3 modify tname varchar2(40);表已更改。SQL> --删除列SQL> alter table test3 drop column photo;表已更改。SQL> --重命名列SQL> alter table test3 rename column tname to username;表已更改。SQL> --重命名表SQL> rename test3 to test5;表已重命名。SQL> host clsSQL> --删除表SQL> select * from tab;TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE EMP10 TABLE TEST2 TABLE TESTSAVEPOINT TABLE TESTDELETE TABLE EMP20 TABLE EMPINFO TABLE TEST5 TABLE 已选择 11 行。SQL> drop table test5; --drop没有把表删除掉表已删除。SQL> select * from tab;TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE EMP10 TABLE TEST2 TABLE TESTSAVEPOINT TABLE TESTDELETE TABLE BIN$3yTwhJ0vSH268YZ8mlL3zw==$0 TABLE EMP20 TABLE EMPINFO TABLE 已选择 11 行。SQL> --Oracle的回收站SQL> show recyclebinORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ -------------------TEST5 BIN$3yTwhJ0vSH268YZ8mlL3zw==$0 TABLE 2015-11-23:15:03:42SQL> purge recyclebin; --清空回收站回收站已清空。SQL> select * from TESTSAVEPOINT; TID TNAME ---------- -------------------- 1 Tom 2 Mary 已选择 2 行。SQL> drop table TESTSAVEPOINT;表已删除。SQL> show recyclebinORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ -------------------TESTSAVEPOINT BIN$+0nemp5PSe2adQphqd6t4A==$0 TABLE 2015-11-23:15:07:06SQL> select * from TESTSAVEPOINT;select * from TESTSAVEPOINT *第 1 行出现错误: ORA-00942: 表或视图不存在 SQL> select * from tab;TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE EMP10 TABLE TEST2 TABLE TESTDELETE TABLE BIN$+0nemp5PSe2adQphqd6t4A==$0 TABLE EMP20 TABLE EMPINFO TABLE 已选择 10 行。SQL> select * from BIN$+0nemp5PSe2adQphqd6t4A==$0;select * from BIN$+0nemp5PSe2adQphqd6t4A==$0 *第 1 行出现错误: ORA-00933: SQL 命令未正确结束 SQL> select * from "BIN$+0nemp5PSe2adQphqd6t4A==$0"; TID TNAME ---------- -------------------- 1 Tom 2 Mary 已选择 2 行。SQL> --注意：不是所有的用户都有回收站，管理员就没有回收站，回收站只针对普通用户。SQL> show recyclebinORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ -------------------TESTSAVEPOINT BIN$+0nemp5PSe2adQphqd6t4A==$0 TABLE 2015-11-23:15:07:06SQL> --闪回删除SQL> flashback table TESTSAVEPOINT to before drop;闪回完成。SQL> show recyclebinSQL> select * from TESTSAVEPOINT; TID TNAME ---------- -------------------- 1 Tom 2 Mary 已选择 2 行。SQL> --约束=数据的完整性SQL> --分为：列级约束(一般外键)和表级约束(联合主键)SQL> --约束的类型： not null unique primary key foreign key checkSQL> create table test5 2 (tid number, 3 tname varchar2(20), 4 gender varchar2(2) check (gender in ('男','女')), 5 sal number check (sal > 0) 6 );表已创建。SQL> insert into test5 values(1,'Tom','男',1000);已创建 1 行。SQL> insert into test5 values(2,'Mike','啊',1000);insert into test5 values(2,'Mike','啊',1000)*第 1 行出现错误: ORA-02290: 违反检查约束条件 (SCOTT.SYS_C005393) SQL> --primary key 非空且唯一SQL> --我们查询表中的数据，通过主键来查询最快，为什么呢？SQL> --答：primary key 非空且唯一，本身就是unique约束，而unique约束本身就是一个索引。所以主键说到底是一个唯一性的索引。SQL> --为什么通过索引来查询最快呢？SQL> --答：...SQL> --注意：子表的外键必须是父表的主键。SQL> create table student 2 ( 3 sid number constraint student_pk primary key, --constraint 约束 4 sname varchar2(20) constraint student_name_notnull not null, 5 gender varchar2(2) constraint student_gender check (gender in ('男','女')), 6 email varchar2(40) constraint student_email_unique unique 7 constraint student_email_notnull not null, --可以在一个类型上定义多个约束 8 deptno number constraint student_fk references dept(deptno) on delete set null --references：指定用部门表(父表)的主键作为学生表(子表)的外键 9 );表已创建。SQL> on delete cascade：当删除父表时，级联删除子表记录，该动作危险，SQL> on delete set null：将子表的相关依赖记录的外键值置为null，一般情况下，使用这句：级联置空SQL> insert into student values(1,'Tom','男','tom@126.com',10);已创建 1 行。SQL> insert into student values(2,'Mike','男','tom@126.com',10);insert into student values(2,'Mike','男','tom@126.com',10)*第 1 行出现错误: ORA-00001: 违反唯一约束条件 (SCOTT.STUDENT_EMAIL_UNIQUE) SQL> spool off
十二、其他数据库对象
示例代码如下：其他数据库对象.txt
SQL> --常见的数据库对象SQL> --表 基本的数据存储集合，由行和列组成。（物理概念）SQL> --视图 从表中抽出的逻辑上相关的数据集合。（逻辑概念）SQL> --序列 提供有规律的数值。SQL> --索引 提高查询的效率。SQL> --同义词 给对象起别名。SQL> --视图viewSQL> create view empinfoview 2 as 3 select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname 4 from emp e,dept d 5 where e.deptno=d.deptno;create view empinfoview *第 1 行出现错误: ORA-01031: 权限不足 SQL> --需要管理员设置权限：grant create view to scottSQL> /视图已创建。SQL> --视图详解：SQL> -- 1. 视图是一种虚表。SQL> -- 2. 视图是建立在已有表的基础上，视图赖以建立的这些表称为基表。SQL> -- 3. 向视图是建立在已有表的基础上，视图赖以建立的这些表称为基表。数据内容的语句为select语句，可以将视图理解为存储起来的 select 语句。SQL> -- 4. 视图向用户提供基表数据的另一种表现形式。SQL> -- 视图的优点：SQL> -- 1. 简化复杂查询，但视图不能提高性能。SQL> -- 2. 同样的数据，可以有不同的显示方式。SQL> -- 3. 提高数据的相互独立。SQL> -- 4. 限制数据访问。SQL> desc empinfoview --查看视图结构 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) SAL NUMBER(7,2) ANNSAL NUMBER DNAME VARCHAR2(14)SQL> select * from empinfoview; EMPNO ENAME SAL ANNSAL DNAME ---------- ---------- ---------- ---------- -------------- 7369 SMITH 800 9600 RESEARCH 7499 ALLEN 1600 19200 SALES 7521 WARD 1250 15000 SALES 7566 JONES 2975 35700 RESEARCH 7654 MARTIN 1250 15000 SALES 7698 BLAKE 2850 34200 SALES 7782 CLARK 2450 29400 ACCOUNTING 7788 SCOTT 3000 36000 RESEARCH 7839 KING 5000 60000 ACCOUNTING 7844 TURNER 1500 18000 SALES 7876 ADAMS 1100 13200 RESEARCH EMPNO ENAME SAL ANNSAL DNAME ---------- ---------- ---------- ---------- -------------- 7900 JAMES 950 11400 SALES 7902 FORD 3000 36000 RESEARCH 7934 MILLER 1300 15600 ACCOUNTING 已选择 14 行。SQL> create or replace view empinfoview 2 as 3 select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname --视图中的子查询可以是复杂的 select 语句 4 from emp e,dept d 5 where e.deptno=d.deptno 6 with read only; --定义约束视图已创建。SQL> --不建议通过视图对表进行修改。因为会有很多限制。SQL> --物化视图：可以缓存数据，自学。SQL> host clsSQL> --序列：sequenceSQL> --序列的作用：可供多个用户用来产生唯一数值的数据库对象。SQL> --序列的好处：SQL> -- 1. 自动提供唯一的数值SQL> -- 2. 共享对象SQL> -- 3. 主要用于提供主键值SQL> -- 4. 将序列值装入内存可以提高访问效率SQL> create sequence myseq;序列已创建。SQL> create table testseq 2 (tid number,tname varchar2(20));表已创建。SQL> 序列的两个伪列(属性)：currval 和 nextvalSQL> select myseq.currval from dual;select myseq.currval from dual *第 1 行出现错误: ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此会话中定义 SQL> select myseq.nextval from dual; NEXTVAL ---------- 1 已选择 1 行。SQL> select myseq.currval from dual; CURRVAL ---------- 1 已选择 1 行。SQL> insert into testseq values(myseq.nextval,'aaa');已创建 1 行。SQL> /已创建 1 行。SQL> /已创建 1 行。SQL> /已创建 1 行。SQL> commit;提交完成。SQL> select * from testseq; TID TNAME ---------- -------------------- 2 aaa 3 aaa 4 aaa 5 aaa 已选择 4 行。SQL> --序列会在下列情况下出现裂缝(即序列不连续)：SQL> -- 回滚SQL> -- 系统异常：停电SQL> -- 多个表同时使用同一序列SQL> insert into testseq values(myseq.nextval,'aaa');已创建 1 行。SQL> /已创建 1 行。SQL> rollback;回退已完成。SQL> insert into testseq values(myseq.nextval,'aaa');已创建 1 行。SQL> select * from testseq; TID TNAME ---------- -------------------- 2 aaa 3 aaa 4 aaa 5 aaa 8 aaa 已选择 5 行。SQL> --修改序列：alter sequence SQL> --修改序列的增量、最大值、最小值、循环选项、或是装入内存。SQL> --注意：修改序列，只会影响将来的值，已经被取走的值，不会受到影响。SQL> host clsSQL> --索引 indexSQL> create index myindex 2 on emp(deptno);索引已创建。SQL> --同义词（别名）SQL> --为emp表起别名SQL> create synonym myemp for emp;create synonym myemp for emp*第 1 行出现错误: ORA-01031: 权限不足 SQL> /同义词已创建。SQL> select * from myemp; 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 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 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 行。 SQL> --同义词的作用：1. 安全 2. 缩短对象名字的长度 3. 方便访问其他用户的对象SQL> --同义词的分类：1. 私有同义词(只能自己用) 2. 公有同义词(任何用户都可以使用)SQL> create synonym myemp1 for hr.employees;同义词已创建。SQL> select count(*) from hr.employees;select count(*) from hr.employees *第 1 行出现错误: ORA-00942: 表或视图不存在 SQL> select count(*) from hr.employees; COUNT(*) ---------- 107 已选择 1 行。SQL> select count(*) from myemp1; COUNT(*) ---------- 107 已选择 1 行。SQL> spool off
索引图解：
柚子快报邀请码778899分享：day43
好文推荐
发表评论