Oracle+编程手册SQL大全.docx
《Oracle+编程手册SQL大全.docx》由会员分享，可在线阅读，更多相关《Oracle+编程手册SQL大全.docx（64页珍藏版）》请在冰点文库上搜索。
Oracle+编程手册SQL大全
OraclePL/SQL编程手册（SQL大全）--
一、SQLPLUS
1引言
SQL命令
以下17个是作为语句开头的关键字：
alterdroprevoke
auditgrantrollback*
commit*insertselect
commentlockupdate
createnoauditvalidate
deleterename
这些命令必须以“;”结尾
带*命令句尾不必加分号，并且不存入SQL缓存区。
SQL中没有的SQL*PLUS命令
这些命令不存入SQL缓存区
@definepause
#delquit
$describeremark
/disconnectrun
acceptdocumentsave
appendeditset
breakexitshow
btitlegetspool
changehelpsqlplus
clearhoststart
columninputtiming
computelistttitle
connectnewpageundefine
copy
---------
2数据库查询
数据字典
TAB用户创建的所有基表、视图和同义词清单
DTAB构成数据字典的所有表
COL用户创建的基表的所有列定义的清单
CATALOG用户可存取的所有基表清单
select*fromtab;
describe命令描述基表的结构信息
describedept
select*
fromemp;
selectempno,ename,job
fromemp;
select*fromdept
orderbydeptnodesc;
逻辑运算符
=!
=或<>>>=<<=
in
betweenvalue1andvalue2
like
%
_
innull
not
noin,isnotnull
谓词in和notin
有哪些职员和分析员
selectename,job
fromemp
wherejobin（'clerk','analyst'）;
selectename,job
fromemp
wherejobnotin（'clerk','analyst'）;
谓词between和notbetween
哪些雇员的工资在2000和3000之间
selectename,job,salfromemp
wheresalbetween2000and3000;
selectename,job,salfromemp
wheresalnotbetween2000and3000;
谓词like,notlike
selectename,deptnofromemp
whereenamelike'S%';
（以字母S开头）
selectename,deptnofromemp
whereenamelike'%K';
（以K结尾）
selectename,deptnofromemp
whereenamelike'W___';
（以W开头，后面仅有三个字母）
selectename,jobfromemp
wherejobnotlike'sales%';
（哪些雇员的工种名不以sales开头）
谓词isnull,isnotnull
没有奖金的雇员（即commision为null）
selectename,jobfromemp
wherecommisnull;
selectename,jobfromemp
wherecommisnotnull;
多条件查询
selectename,job
fromemp
wheredeptno=20
andjob!
='clerk';
表达式
+-*/
算术表达式
选择奖金高于其工资的5%的雇员
selectename,sal,comm,comm/salfromemp
wherecomm>.05*sal
orderbycomm/saldesc;
日期型数据的运算
addtwodaysto6-Mar-87
6-Mar-87+2=8-Mar-87
addtwohoursto6-Mar-87
6-Mar-87+2/24=6-Mar-87and2hrs
add15secondsto6-Mar-87
6-Mar-87+15/（24*60*60）=6-Mar-87and15secs
列名的别名
selectenameemployeefromemp
wheredeptno=10;
（别名：
employee）
selectename,sal,comm,comm/sal"C/SRATIO"fromemp
wherecomm>.05*sal
orderbycomm/saldesc;
SQL命令的编辑
listorl显示缓冲区的内容
list4显示当前SQL命令的第4行，并把第4行作为当前行，在该行号后面有个*。
changeorc用新的内容替换原来在一行中第一次出现内容
SQL>c/（...）/（'analyst'）/
inputori增加一行或多行
appendora在一行后追加内容
del删除当前行删除SQL缓冲区中的当前行
run显示并运行SQL缓冲区中的命令
/运行SQL缓冲区中的命令
edit把SQL缓冲区中的命令写到操作系统下的文本文件，
并调用操作系统提供的编辑器执行修改。
-------------
3数据操纵
数据的插入
insertintodept
values（10,'accounting','newyork'）;
insertintodept（dname,deptno）
values（'accounting',10）;
从其它表中选择插入数据
insertintoemp（empno,ename,deptno）
selectid,name,department
fromold_emp
wheredepartmentin（10,20,30,40）;
使用参数
insertintodept
values（&deptno,&dname,&loc）;
执行时，SQL/PLUS对每个参数将有提示用户输入
参数对应日期型或字符型数据时，可在参数上加引号，输入时就可不用引号
insertintodept
values（&deptno,'&dname','&loc'）;
插入空值（NULL）
insertintodept
values（50,'education',null）;
插入日期型数据
日期型数据缺省格式：
DD-MON-YY
insertintoemp
（empno,ename,hiredate）
values（7963,'stone','07-APR-87'）;
系统时间：
SYSDATE
insertintoemp
（empno,ename,hiredate）
values（7600,'kohn',SYSDATE）;
数据更新
updateemp
setjob='manager'
whereename='martin';
updateemp
setjob='marketrep'
whereename='salesman';
updateemp
setdeptno=40,job='marketrep'
wherejob='salesman';
数据删除
deleteemp
whereempno=765;
更新的提交
commit
自动提交方式
setautocommiton
如果状态设为开，则使用inesrt,update,delete会立即提交。
更新取消
rollback
两次连续成功的commit之间的操作，称为一个事务
---------------
4创建基表、视图
创建基表
createtabledept
（deptnonumber
（2）,
dnamechar（14）,
locchar（13））;
数据字典会自动更新。
一个基表最多254列。
表名列名命名规则：
限制
第一个字符必须是字母，后面可任意（包括$#_但不能是逗号）。
名字不得超过30个字符。
唯一
某一用户的基表名必须唯一，不能是ORACLE的保留字，同一基表的列名互不相同。
使用双引号
如果表名用双引号括起来，则可不满足上述规则；
只有使用双引号，才能区别大、小写；
命名时使用了双引号，在以后的操作也必须使用双引号。
数据类型：
char（n）（不得超过240字符）
number（n,d）
date
long（最多65536字符）
raw（二进制原始数据）
空值处理
有时要求列值不能为空
createtabledept
（deptnonumber
（2）notnull,
dnamechar（14）,
locchar（13））;
在基表中增加一列
altertabledept
add（headcntnumber（3））;
修改已有列属性
altertabledept
modifydnamechar（20）;
注：
只有当某列所有值都为空时，才能减小其列值宽度。
只有当某列所有值都为空时，才能改变其列值类型。
只有当某列所有值都为不空时，才能定义该列为notnull。
例：
altertabledeptmodify（locchar（12））;
altertabledeptmodifylocchar（12）;
altertabledeptmodify（dnamechar（13）,locchar（12））;
创建视图
createviewmanagersas
selectename,job,sal
fromemp
wherejob='manager';
为视图列名取别名
createviewmydept
（person,title,salary）
asselectename,job,sal
fromemp
wheredeptno=10;
withcheckoption选项
使用withcheckoption，保证当对视图插入或更新数据时，
该数据必须满足视图定义中select命令所指定的条件。
createviewdept20as
selectename,job,sal,deptno
fromemp
wheredeptno=20
withcheckoption;
在做下述操作时，会发生错误
updatedept20
setdeptno=30
whereename='ward';
基表、视图的拷贝
createtableemp2
asselect*fromemp;
基表、视图的删除
droptable表名
dropview视图名
------------
5SQL*PLUS报表功能
SQL*PLUS的一些基本格式命令
columndeptnoheadingdepartment
columnenameheadingname
columnsalheadingsalary
columnsalformat$99,999.00
ttitlesamplereportfor｜hitechcorp
btitlestrictlyconfidential
breakondeptno
computesumofsalondeptno
run
表头和表尾
ttitlesamplereportfor｜hitechcorp
btitlerightstrictlyconfidential
“｜”表示换行，结尾不必加分号
选项有三种：
leftrightcenter
使用TTITLE，系统将自动地在每页的顶部显示日期和页号。
TTITLET和BTITLE命令有效，直至重新设置表头或表尾，或退出SQL*PLUS。
下面命令使标题语句失效
TTITLEOFF
BTITLEOFF
列名
column命令定义用于显示列名
若名字为一个单词，不必加引号
columnenameheadingemployee
columnenameheading'employee｜name'
（｜为换行）
取消栏定义
columnenameclear
列的格式
columnenameformatA15
columnsalformat$9,999.99
columncommlikesal
like子句，使得某一列的格式参照另一列格式，它拷贝列名及其格式
控制记录显示分组顺序
breakondeptno
（不显示重复值）
selectdeptno,ename
fromemp
orderbydeptno;
（ORDERBY子句用于控制BREAK）
显示为
10clark
niller
20smith
scott
30allen
blake
每次只有一个BREAK命令起作用，但一次可以在多个列上使用BREAK命令
breakon列名1on列名2
记录分组
breakondeptnoskip2
selectdeptno,ename
fromemp
orderbydeptno;
每个deptno之间空两行
clearbreak（取消BREAK命令）
breakonpage（每次从一新页开始）
breakonreport（每次从一新报表开始）
breakonpageonreport（联合使用）
分组计算
breakondeptnoskip2
computesumofsalondeptno
计算每个部门的工资总和
skip子句使部门之间的信息分隔开
其他计算命令
computeavgofsalondeptno（平均值）
count非空值的总数
MAX最大值
MIN最小值
STD标准偏差
VAR协方差
NUMBER行数
使compute命令失效
一旦定义了COMPUTE，则一直有效，直到
关闭COMPUTE（clearcompute）
SQL/PLUS环境命令
show选项
（显示当前参数设置情况）
showall（显示全部参数）
设置参数
set选项值或开关
setautocommiton
SET命令包括
setautocommit{off｜on｜immediate}
（自动提交，OFF缺省）
setecho{off｜on}
（命令文件执行，是否在终端上显示命令本身，OFF缺省）
setfeedback{off｜on}
（ON：
查询结束时，给出结果，记录数的信息，缺省；
OFF：
无查询结果，记录数的信息）
setheading{off｜on}
（ON：
列的头标在报表上显示，缺省；OFF：
不在报表上显示）
setlinesize{n}
一行显示的最大字符数，缺省为80
setpagesize{n}
每页的行数，缺省是14
setpause{off｜on｜text}
（ON：
当报表超过一页时，每显示一屏就暂停显示，等待用户打回车键，再接着显示；
OFF：
页与页不停顿，缺省；text：
页与页停顿，并向用户提示信息）
SETBUFFERbuffer
设置当头的命令缓冲区，通常情况下，SQL命令缓冲区已为当前缓冲区。
由于SQL命令缓冲区只能存放一条SQL命令，
所以可用其它缓冲区来存放SQL命令和SQL*PLUS命令。
经常用到的设置可放在login.sql文件中。
SETNULL
setnull'nodata'
selectename,comm
fromemp
wheredeptno=30;
把部门30中无佣金雇员的佣金显示为“NODATA”。
setnull是SQL*PLUS命令，用它来标识空值（NULL），可以设置为任意字符串。
存盘命令SAVE
save文件名
input
1selectempno,ename,job
2fromemp
3wherejob='analyst'
saveresearch
目录中会增加一个research.sql文件。
编辑命令EDIT
edit
EDIT编辑当前缓冲区中的内容。
编辑一个文件
editresearch
调入命令GET
getresearch
把磁盘上的文件内容调入缓冲区，并显示在屏幕上，文件名尾不必加.sql。
START命令
运行指定的文件
startresearch
输出命令SPOOL
spooltryfile
不仅可以使查询结果在屏幕上显示，还可以使结果存入文件
停止向文件输出
spooloff
把查询结果在打印机上输出，先把它们存入一个文件中，
然后不必使用SPOOLOFF，而用：
spoolout
SPOOLOUT关闭该文件并在系统缺省的打印机上输出
制作报表举例
edittryfile
setechooff
setautocommiton
setpagesize25
insertintoemp（empno,ename,hiredate）
values（9999,'geiger',sysdate）;
insertintoemp（empno,ename,deptno）
values（3333,'samson',20）;
spoolnew_emp
select*fromemp
wheredeptno=20
ordeptnoisnull
/
spooloff
setautocommitoff
用start命令执行这个文件
--------
6函数
字符型函数
initcap（ename）;将ename中每个词的第一个字母改为大写。
如：
jacksmith--JackSmith
length（ename）;计算字符串的长度。
substr（job,1,4）;
其它
lower
upper
least取出字符串列表中按字母排序排在最前面的一个串
greatest取出字符串列表中按字母排序排在最后的一个串
日期函数
add_month（hiredate,5）在雇佣时间上加5个月
month_between（sysdate,hiredate）计算雇佣时间与系统时间之间相差的月数
next_day（hiredate,'FRIDAY'）计算受雇日期之后的第一个星期五的日期
例
selectename,sal,next_day（sysdate,'FRIDAY'）as_of
fromemp
wheredeptno=20;
（as_of是别名）
如果不用to_char函数，日期在ORACLE中的缺省格式是'DD_MON_YY'
to_char（date,datepicture）
selectename,to_char（hiredate,'DyMondd,yyyy'）hired
fromemp
wheredeptno=10;
to_date（字符串,格式）
insertintoemp（empno,ename,hiredate）
values（7999,'asms',to_date（'070387083000','MMDDYYHHMISS'））;
日期型数据的格式
dd12
dyfri
dayfriday
ddspthtwelfth
mm03
monmar
monthmarch
yy87
yyyy1987
例
Mar12,1987'Mondd,yyyy'
MAR12,1987'MONdd,yyyy'
ThursdayMARCH12'DayMONTHdd'
Mar1211:
00am'Monddhh:
miam'
Thu,thetwelfth'Dy,"the"ddspth'
算术函数
least（v1,v2）
selectename,empno,mgr,least（empno,mgr）lownum
fromemp
whereempno0
trunc（sal,0）
取sal的近似值（截断）
空值函数
nvl（v1,v2）
v1为列名，如果v1不是空值，nvl返回其列值。
v1为空值，返回v2的值。
聚组函数
selectsum（comm）
fromemp;
（返回一个汇总信息）
不能把sum用在select语句里除非用groupby
字符型、日期型、数字型的聚组函数
minmaxcount可用于任何数据类型
selectmin（ename）
fromemp;
selectmin（hiredate）
fromemp;
selectmin（sal）
fromemp;
有多少人有工作？
selectcount（job）
fromemp;
有多少种不同的工种？
selectcount（distinctjob）
fromemp;
countdistinct计算某一字段中不同的值的个数
其它聚组函数（只用于数字型数据）
avg计算平均工资
selectavg（sal）
fromemp;
stddev
升级会员 