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