5 SQL*PLUS报表功能 SQL*PLUS的一些基本格式命令 column deptno heading department column ename heading name column sal heading salary column sal format ＄99,999.00 ttitle sample report for|hitech corp btitle strictly confidential break on deptno compute sum of sal on deptno run 表头和表尾 ttitle sample report for|hitech corp btitle right strictly confidential “|”表示换行，结尾不必加分号 选项有三种：left right center 使用TTITLE，系统将自动地在每页的顶部显示日期和页号。 TTITLET和BTITLE命令有效，直至重新设置表头或表尾，或退出SQL*PLUS。 下面命令使标题语句失效 TTITLE OFF BTITLE OFF 列名 column命令定义用于显示列名 若名字为一个单词，不必加引号 column ename heading employee column ename heading 'employee|name' （|为换行） 取消栏定义 column ename clear 列的格式 column ename format A15 column sal format ＄9,999.99 column comm like sal like子句，使得某一列的格式参照另一列格式，它拷贝列名及其格式 控制记录显示分组顺序 break on deptno （不显示重复值） select deptno,ename from emp order by deptno; （ORDER BY子句用于控制BREAK） 显示为 10 clark niller 20 smith scott 30 allen blake 每次只有一个BREAK命令起作用，但一次可以在多个列上使用BREAK命令 break on 列名1 on 列名2 记录分组 break on deptno skip 2 select deptno,ename from emp order by deptno; 每个deptno之间空两行 clear break（取消BREAK命令） break on page（每次从一新页开始） break on report（每次从一新报表开始） break on page on report（联合使用） 分组计算 break on deptno skip 2 compute sum of sal on deptno 计算每个部门的工资总和 skip子句使部门之间的信息分隔开 其他计算命令 compute avg of sal on deptno（平均值） count 非空值的总数 MAX 最大值 MIN 最小值 STD 标准偏差 VAR 协方差 NUMBER 行数 使compute命令失效 一旦定义了COMPUTE，则一直有效，直到 关闭COMPUTE（clear compute） SQL/PLUS环境命令 show 选项 （显示当前参数设置情况） show all（显示全部参数） 设置参数 set 选项 值或开关 set autocommit on SET命令包括 set autocommit {off|on|immediate} （自动提交，OFF缺省） set echo {off|on} （命令文件执行，是否在终端上显示命令本身，OFF缺省） set feedback {off|on} （ON：查询结束时，给出结果，记录数的信息，缺省； OFF：无查询结果，记录数的信息） set heading {off|on} （ON：列的头标在报表上显示，缺省；OFF：不在报表上显示） set linesize {n} 一行显示的最大字符数，缺省为80 set pagesize {n} 每页的行数，缺省是14 set pause {off|on|text} （ON：当报表超过一页时，每显示一屏就暂停显示，等待用户打回车键，再接着显示； OFF：页与页不停顿，缺省；text：页与页停顿，并向用户提示信息） SET BUFFER buffer 设置当头的命令缓冲区，通常情况下，SQL命令缓冲区已为当前缓冲区。 由于SQL命令缓冲区只能存放一条SQL命令， 所以可用其它缓冲区来存放SQL命令和SQL*PLUS命令。 经常用到的设置可放在login.sql文件中。 SET NULL set null 'no data' select ename,comm from emp where deptno=30; 把部门30中无佣金雇员的佣金显示为“NO DATA”。 set null是SQL*PLUS命令，用它来标识空值（NULL），可以设置为任意字符串。 存盘命令SAVE save 文件名 input 1 select empno,ename,job 2 from emp 22 where job='analyst' save research 目录中会增加一个research.sql文件。 编辑命令EDIT edit EDIT编辑当前缓冲区中的内容。 编辑一个文件 edit research 调入命令GET get research 把磁盘上的文件内容调入缓冲区，并显示在屏幕上，文件名尾不必加.sql。 START命令 运行指定的文件 start research 输出命令SPOOL spool tryfile 不仅可以使查询结果在屏幕上显示，还可以使结果存入文件 停止向文件输出 spool off 把查询结果在打印机上输出，先把它们存入一个文件中， 然后不必使用SPOOL OFF，而用： spool out SPOOL OUT关闭该文件并在系统缺省的打印机上输出 制作报表举例 edit tryfile set echo off set autocommit on set pagesize 25 insert into emp (empno,ename,hiredate) values(9999,'geiger',sysdate); insert into emp (empno,ename,deptno) values(3333,'samson',20); spool new_emp select * from emp where deptno=20 or deptno is null / spool off set autocommit off 用start命令执行这个文件 -------- 6 函数 字符型函数 initcap(ename);将ename中每个词的第一个字母改为大写。 如：jack smith--Jack Smith length(ename);计算字符串的长度。 substr(job,1,4); 其它 lower upper least 取出字符串列表中按字母排序排在最前面的一个串 greatest 取出字符串列表中按字母排序排在最后的一个串 日期函数 add_month(hiredate,5) 在雇佣时间上加5个月 month_between(sysdate,hiredate) 计算雇佣时间与系统时间之间相差的月数 next_day(hiredate,'FRIDAY') 计算受雇日期之后的第一个星期五的日期 例 select ename,sal,next_day(sysdate,'FRIDAY') as_of from emp where deptno=20; （as_of是别名） 如果不用to_char函数，日期在ORACLE中的缺省格式是'DD_MON_YY' to_char(date,date picture) select ename,to_char(hiredate,'Dy Mon dd,yyyy') hired from emp where deptno=10; to_date(字符串,格式) insert into emp(empno,ename,hiredate) values(7999,'asms',to_date('070387083000','MMDDYYHHMISS')); 日期型数据的格式 dd 12 dy fri day friday ddspth twelfth mm 03 mon mar month march yy 87 yyyy 1987 例 Mar 12,1987 'Mon dd,yyyy' MAR 12,1987 'MON dd,yyyy' Thursday MARCH 12 'Day MONTH dd' Mar 12 11:00am 'Mon dd hh:miam' Thu,the twelfth 'Dy,"the"ddspth' 算术函数 least(v1,v2) select ename,empno,mgr,least(empno,mgr) lownum from emp where empno0 trunc(sal,0) 取sal的近似值（截断） 空值函数 nvl(v1,v2) v1为列名，如果v1不是空值，nvl返回其列值。 v1为空值，返回v2的值。 聚组函数 select sum(comm) from emp; （返回一个汇总信息） 不能把sum用在select语句里除非用group by 字符型、日期型、数字型的聚组函数 min max count可用于任何数据类型 select min(ename) from emp; select min(hiredate) from emp; select min(sal) from emp; 有多少人有工作？ select count(job) from emp; 有多少种不同的工种？ select count(distinct job) from emp; count distinct 计算某一字段中不同的值的个数 其它聚组函数（只用于数字型数据） avg 计算平均工资 select avg(sal) from emp; stddev 计算工资的平均差 select stddev(sal) from emp; sum 计算总工资 select sum(sal) from emp; group by子句 select deptno,sum(sal),avg(sal) from emp group by deptno; 按多个条件分组 每个部门的雇员数 select deptno,count(*) from emp group by deptno; 每个部门的每个工种的雇员数 select deptno,job,count(*) from emp group by deptno,job; 满足条件的分组 （where是针对select的，having是针对group by的） 哪些部门的工资总和超过了9000 select deptno,sum(sal) from emp group by deptno having sum(sal)>9000; select小结 除去职员，哪些部门的工资总和超过了8000 select deptno,sum(sal) from emp where job!='clerk' group by deptno having sum(sal)>8000 order by sum(sal); --------- 7 高级查询 等值联接 select empno,ename,job,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno; 外联接 select ename,dept.deptno,loc from emp,dept where emp.deptno(+)=dept.deptno; 如果在dept.deptno中有的数值，在emp.deptno中没有（如deptno=40）， 则作外联接时，结果中会产生一个空值 自联接：同一基表的不同行要做联接，可使用自联接 指出每个雇员的经理名字 select worker.ename,manager.ename manager from emp worker,emp manager where worker.mgr=manager.empno; 非等值联接 哪些雇员的工资属于第三级别 select ename,sal from emp,salgrade where grade=3 and sal between losal and hisal; （基表salgrade：grade losal hisal） 集合运算 行的连接 集合运算把2个或多个查询结果合并为一个 union-set union Rows of first query plus of second query, less duplicate rows intersect-set intersection Rows both queries have in common minus-set difference rows unique to the first query 介绍几个视图 account view ename sal job sales view ename sal job research view ename sal job union运算 返回一个查询结果中有但又不重要的行，它将基表或视图中的记录合并在一起 所有部门中有哪些雇员工资超过2000 对应列的数据类型必须相同 select ename,sal from account where sal>2000 union select ename,sal from research where sal>2000 union select ename,sal from sales where sal>2000; intersect运算 返回查询结果中相同的部分 各个部门中有哪些相同的工种 select job from account intersect select job from research intersect select job from sales; minus运算 返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。 有哪些工种在财会部中有，而在销售部中没有？ select job from account minus select job from sales; 子查询 slect ename,deptno from emp where deptno= (select deptno from emp where ename='smith'); 多级子查询 select ename,job,sal from emp where job= (select job from emp where ename='clark') or sal> (select sal from emp where ename='clark'); 多个基表与子查询 select ename,job,sal from emp,dept where loc='new york' and emp.deptno=dept.deptno and sal> (select sal from emp where ename='scott'); 子查询中使用聚组函数 select ename,hiredate from emp where hiredate= (select min(hiredate) from emp); ------------ 8 授权 系统权限 DBA 所有权限 RESOURCE 注册，创建新的基表 CONNECT，注册，查询 只有DBA才有权创建新的用户 grant connect to scott identified by tiger; DBA或用户自己可以改变用户口令 grant connect to scott identified by leopard; 基表权限1 有两种方法获得对基表操作的权限 创建自己的基表 获得基表创建用户的许可 grant select,insert on emp to scott; 这些权限有 select insert update delete alter index 把所有权限授于他人 grant all on emp to scott; 同义词 select * from scott.emp 创建同义词 为用户allen的EMP基表创建同义词employee create synonym employee for allen.emp 基表权限2 你可以使其他用户有这样的权力，即其他用户可以把你的基表权限授予他人 grant all on emp to scott with grant option; 收回权限 系统权限 只有被DBA收回 基表权限 随时都可以收回 revoke insert on emp from scott; --------- 9 索引 建立索引 create index emp_ename on emp(ename); 删除索引 drop index emp_ename; 关于索引 只对较大的基表建立索引（至少50条记录） 建立索引之前插入数据 对一个基表可建立任意多个索引 一般是在作为主键的列上建立索引 建立索引之后，不影响SQL命令的执行 建立索引之后，ORACLE自动维护和使用索引 保证数据唯一性 提高执行速度的同时，索引还可以保证每个记录中的每个列值是不重复的。 create unique index emp_empno on emp(empno); -------- 练习和答案 有没有工资比奖金多的雇员？如果有，按工资的降序排列。 如果有两个以上的雇员工资相同，按他们的名字排序。 select ename employee,sal salary,comm commision from emp where sal>comm order by sal desc,ename; 列出有关雇员姓名、奖金占收百分比的信息。 要求显示时列名意义明确，按雇员姓名排序，不包括奖金未知的雇员。 select ename employee,(comm/(comm+sal))*100 incentive from emp where comm is not null order by ename; 在chicago（部门30）工作的所有雇员的工资上涨10%。 update emp set sal=1.1*sal where deptno=30; update emp set sal=1.1*sal where deptno=(select deptno from dept where loc='chicago'); 为hitech公司新建一个部门，编号为50，其它信息均不可知。 insert into dept(dname,deptno) values('faclities',50); 创建视图，三个列名，其中不包括职员信息 create view employee("employee name", "employee number", "employee job") as select ename,empno,job from emp where job!='clerk'; 制作工资报表，包括雇员姓名、受雇时间（按星期计算），工资和部门编号， 一页显示一个部门的信息，每页尾，显示该页的工资之和以及受雇时间之和， 报表结尾处，显示所有雇员的工资总和以及受雇时间总和， 工资按美元计算，受雇时间按星期计算，每页的上方应有标题。 ttitle 'service' break on deptno on page on report compute sum of sal on deptno compute sum of sal on report compute sum of service_length on deptno compute sum of service_length on report column sal format ＄99,999.00 column service_length format 9999 select deptno,ename employee,(sysdate-hiredate)/7 service_length,sal from emp order by deptno; 制作报表，包括雇员姓名、总收入和受佣日期， 且：姓名的第一个字母必须大写，雇佣日期格式为MM/DD/YYYY， 总收入包括没有奖金的雇员的总收入，姓名按字母顺序排列。 col "hire date"format A12 col "employee" format A10 col "compensation" format ＄99,999.00 select initcap(ename) "employee", (sal+nvl(comm,0)) "compensation", to_char(hiredate,'MM/DD/YYYY') "hire date" from emp order by ename; 列出有超过7个周边国家的国家名字和面积。 select nation,area from nation where code in (select nation_code from border group by nation_code having count(*)>7); 列出所有面积大于等于日本的岛国的国名和人口。 select nation,population from nation,border where code=nation_code(+) and nation_code is null and area>= (select area from nation where upper(nation)='JAPAN'); 列出所有边界在其它国家中的国名，并且显示其边界国家名字。 break on nation select nation1.nation, nation2.nation borderin_country from nation nation1,border,nation nation2 where nation1.code=border.nation_code and border.border_code=nation2.code order by nation1.nation;
转载于:https://www.cnblogs.com/zwl12549/archive/2007/01/24/629238.html
相关资源：数据结构—成绩单生成器
