oracle语句及语法大全

Oracle10g1第一章Oracle命令a)系统管理员连接conn*/*assysdbab)查询当前用户showuserc)创建新用户createuser用户名identifiedby密码（密码不能以数字开头）。例如createuserabcidentifiedbycbad)用户登录conn用户名/密码。例如connabc/cbae)用户授权grant权限to用户。例如grantconnect,resourcetoabc；grantselectonscott.emptoabcf)收回权限revoke权限from用户。例如revokeresourcefromabc；revokeselectonscott.empfromabcg)修改密码alteruser用户名identifiedby新密码。例如alteruserabcidentifiedbycba12h)锁定用户alteruser用户名accountlock。例如alteruserscottaccountlocki)解锁用户alteruser用户名accountunlock。例如alteruserscottaccountunlockj)创建表空间createtablespace表空间名datafile表空间文件路径size初始大小autoextendon(/off)。例如createtablespacesvse‘c:\1.dbf’size10mautoextendonk)为某个用户指定表空间alteruser用户名defaulttablespace表空间名l)修改表空间的文件大小：alterdatabasedatafile路径(路径要加’)resize新大小。例如alterdatabasedatafile‘c:\1.dbf’resize20mm)向表空间添加文件：altertablespace表空间名adddatafile路径size初始大小。例如altertablespacesvseadddatafile‘c:\2.dbf’size5mn)让表空间文件自动扩展：alterdatabasedatafile路径autoextendonnext每次扩展量maxsize文件的最大值。例如alterdatabasedatafile‘c:\2.dbf’autoextendonnext5mmaxsize50mo)修改表空间的名字：altertablespace表空间原名renameto新名。注意这个命令是10G新增加的，在9I中不能运行。p)使表空间临时脱机。使表空间脱机就相当于sqlserver2005中的分离数据库，就是让服务器不再管理这个表空间了：altertablespace表空间名offlinetemporaryq)使表空间联机。相当于sqlserver2005中的附加数据库，就是让服务器重新管理这个表空间：altertablespace表空间名onliner)删除表空间。如果表空间里面有对象用：droptablespace表空间名includingcontents。如果表空间里什么也没有用droptablespace表空间名s)更改环境变量设置每行显示setlinesize大小设置每页显示setpagesize大小。例如setpagesize500，setlines300t)设置sqlplus代码保存路径：spoolon路径，注意在9i下路径不能加’，在10G下可以加。保存代码spooloff。例如spoolon‘c:\1.sql’；u)查看表的结构：desc表名。例如：descscott.empv)代码错误后修改：edit/ed。注意在弹出的文本文件中不能在结尾加分号w)执行外部文件用下面3个命令中的任何一个都可以：start/@文件路径。例如：@‘c:\1.sql’x)清屏命令：clearscreen第二章Oracle命令修改会话的日期格式信息：altersessionsetnls_date_format=’yyyy-mm-dd’显示当前日期：selectsysdatefromdual。注意oracle规定如果一个函数没有参数则不能加()，sysdate就没有参数所以没加()to_date函数是把一个字符串按指定的格式转换成日期。例如to_date(‘1-20-2000’,’mm-dd-yyyy’)返回的就是2002年1月20日这个日期to_char函数把一个日期按指定格式转换为字符。例如to_char(sysdate,‘yyyy-mm-dd’)to_number(‘123’)函数把一个字符串转换为数字伪列rowid存储的是这条记录在硬盘上的绝对位置Oracle10g2伪列rownum可以在select语句中使用，它返回这条记录是表的第几行记录，如果放在条件语句中，rownum只能和或者=连用，例如whererownum=5是正确的，但rownum2缺查不出任何结果。伪列level显示这条记录在表中的层次oracle中连接2个字符用||而不是+。例如’ab’||’cd’结果是’abcd’根据一个旧表创建一个新表createtable新表名asselect某些列from旧表。例如createtablenewabcasselect*fromabc把一个表中的部分数据插入另一个表，原表不变。insertinto表名select某些列from原表名where…。可以加条件也可以不加。例如insertintoabcselect一些列fromscott.emp集合运算为了演示方便，首先创建2个表Createtabletable_a(avarchar2(10),bvarchar2(10))Createtabletable_b(avarchar2(10),bvarchar2(10))向table_a插入4行数据：insertintotable_avalues(‘1’,‘a’)Insertintotable_avalues(‘2’,‘a’)Insertintotable_avalues(‘3’,‘b’)向table_b插入3行数据：Insertintotable_bvalues(‘2’,‘a’)Insertintotable_bvalues(‘3’,‘a’)Insertintotable_bvalues(‘4’,‘b’)UNION并集运算就是将2个查询的结果合成一个结果，合并时重复的记录只取一次。例如select*fromtable_aunionselect*fromtable_b的结果是：AB--------------------1a2a3b3a4bUNIONALL并集运算就是将2个查询的结果合成一个结果，合并时不排除重复的记录。例如select*fromtable_aunionallselect*fromtable_b的结果是：AB--------------------1a2a3b2a3a4bINTERSECT交集运算就是只返回2个查询中共同的记录。例如：select*fromtable_aintersectselect*fromtable_b的结果是：AB--------------------2aMINUS剪集运算就是从第一个查询结果中删除2个查询都有的记录。例如Oracle10g3select*fromtable_aminusselect*fromtable_b的结果是：AB--------------------1a3b连接查询内连接：左表innerjoin右表on条件——满足条件的记录才显示出来。selecttable_a.aasaa,table_a.basab,table_b.aasba,table_b.basbbfromtable_ainnerjointable_bontable_a.a=table_b.a的结果是：AAABBABB----------------------------------------2a2a3b3a外连接：左表fulljoin右表on条件——2个表的记录都显示出来，满足条件的就显示为同一行，不满足条件的用null显示。例如：selecttable_a.aasaa,table_a.basab,table_b.aasba,table_b.basbbfromtable_afulljointable_bontable_a.a=table_b.a的结果是：AAABBABB----------------------------------------2a2a3b3a1a4b左连接：左表leftjoin右表on条件——把左表的记录全部显示出来，右表中满足条件的显示为同一行，不满足的用null表示。例如：selecttable_a.aasaa,table_a.basab,table_b.aasba,table_b.basbbfromtable_aleftjointable_bontable_a.a=table_b.a的结果是：AAABBABB----------------------------------------2a2a3b3a1a右连接：左表rightjoin右表on条件——把右表的记录全部显示出来，左表中满足条件的显示为同一行，不满足的用null表示。例如：selecttable_a.aasaa,table_a.basab,table_b.aasba,table_b.basbbfromtable_arightjointable_bontable_a.a=table_b.a的结果是：AAABBABB----------------------------------------2a2a3b3a4bOracle10g4分组函数avg求平均值count求记录总数max求最大值min求最小值sum对数字型列求和分析函数（就是sqlserver2005中的排序函数，语法都完全一样）row_number()over()对某列排序后再对排序后的结果按顺序编号：selectrow_number()over(orderbyb)as编号,table_a.*fromtable_a就是按b列排序，然后编号。运行结果是：编号AB------------------------------11a22a33b44b注意：row_number()函数和rownum伪列的区别：row_number()是先排序后编号，而rownum是按照记录在表中的位置编号。rank()over()如果排序列的值相同则编相同的号，但跳空。selectrank()over(orderbyb)as编号,table_a.*fromtable_a运行结果是：编号AB------------------------------11a12a33b34bdense_rank()over()如果排序列的值相同则编相同的号，但不跳空。selectdense_rank()over(orderbyb)as编号,table_a.*fromtable_a运行结果是：编号AB------------------------------11a12a23b24b注意：over（）函数还可以先分组再排序，请看67页上面2行。字符函数和日期函数请从61页的下面一行开始看。Oracle触发器触发器是当特定事件出现时自动执行的代码块,是一个特殊的过程.触发器和过程的区别在于:过程是由用户或应用程序显式调用的，而触发器是不能被直接调用,ORACLE会在事件请求触发器时，执行适当的触发器触发器能够执行的功能有：自动生成数据强制复杂的完整性约束条件自定义复杂的安全权限提供审计和日志记录启用复杂的业务逻辑创建触发器的语法：BEFORE和AFTER：指在事件之前或之后激发触发器insteadof:如果使用此子句，表示可以执行触发器代Oracle10g5码来代替导致触发器调用的事件insert,delete和update:指定构成触发器事件的数据操纵类型，update还可以指定列的列表referencing指定新行触发