oracle语句及语法大全.docx

oracle语句及语法大全.docx

《oracle语句及语法大全.docx》由会员分享，可在线阅读，更多相关《oracle语句及语法大全.docx（48页珍藏版）》请在冰豆网上搜索。

oracle语句及语法大全

第一章Oracle命令

a）系统管理员连接conn*/*assysdba

b）查询当前用户showuser

c）创建新用户createuser用户名identifiedby密码（密码不能以数字开头）。

例如createuserabcidentifiedbycba

d）用户登录conn用户名/密码。

例如connabc/cba

e）用户授权grant权限to用户。

例如grantconnect,resourcetoabc；grantselectonscott.emptoabc

f）收回权限revoke权限from用户。

例如revokeresourcefromabc；revokeselectonscott.empfromabc

g）修改密码alteruser用户名identifiedby新密码。

例如alteruserabcidentifiedbycba12

h）锁定用户alteruser用户名accountlock。

例如alteruserscottaccountlock

i）解锁用户alteruser用户名accountunlock。

例如alteruserscottaccountunlock

j）创建表空间createtablespace表空间名datafile表空间文件路径size初始大小autoextendon（/off）。

例如createtablespacesvse‘c:

\1.dbf’size10mautoextendon

k）为某个用户指定表空间alteruser用户名defaulttablespace表空间名

l）修改表空间的文件大小：

alterdatabasedatafile路径（路径要加’）resize新大小。

例如alterdatabasedatafile‘c:

\1.dbf’resize20m

m）向表空间添加文件：

altertablespace表空间名adddatafile路径size初始大小。

例如altertablespacesvseadddatafile‘c:

\2.dbf’size5m

n）让表空间文件自动扩展：

alterdatabasedatafile路径autoextendonnext每次扩展量maxsize文件的最大值。

例如alterdatabasedatafile‘c:

\2.dbf’autoextendonnext5mmaxsize50m

o）修改表空间的名字：

altertablespace表空间原名renameto新名。

注意这个命令是10G新增加的，在9I中不能运行。

p）使表空间临时脱机。

使表空间脱机就相当于sqlserver2005中的分离数据库，就是让服务器不再管理这个表空间了：

altertablespace表空间名offlinetemporary

q）使表空间联机。

相当于sqlserver2005中的附加数据库，就是让服务器重新管理这个表空间：

altertablespace表空间名online

r）删除表空间。

如果表空间里面有对象用：

droptablespace表空间名includingcontents。

如果表空间里什么也没有用droptablespace表空间名

s）更改环境变量设置每行显示setlinesize大小设置每页显示setpagesize大小。

例如setpagesize500，setlines300

t）设置sqlplus代码保存路径：

spoolon路径，注意在9i下路径不能加’，在10G下可以加。

保存代码spooloff。

例如spoolon‘c:

\1.sql’；

u）查看表的结构：

desc表名。

例如：

descscott.emp

v）代码错误后修改：

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存储的是这条记录在硬盘上的绝对位置

伪列rownum可以在select语句中使用，它返回这条记录是表的第几行记录，如果放在条件语句中，rownum只能和<或者<=连用，例如whererownum<=5是正确的，但rownum>2缺查不出任何结果。

伪列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

--------------------

1a

2a

3b

3a

4b

UNIONALL并集运算就是将2个查询的结果合成一个结果，合并时不排除重复的记录。

例如

select*fromtable_aunionallselect*fromtable_b的结果是：

AB

--------------------

1a

2a

3b

2a

3a

4b

INTERSECT交集运算就是只返回2个查询中共同的记录。

例如：

select*fromtable_aintersectselect*fromtable_b的结果是：

AB

--------------------

2a

MINUS剪集运算就是从第一个查询结果中删除2个查询都有的记录。

例如

select*fromtable_aminusselect*fromtable_b的结果是：

AB

--------------------

1a

3b

连接查询

内连接：

左表innerjoin右表on条件——满足条件的记录才显示出来。

selecttable_a.aasaa,table_a.basab,

table_b.aasba,table_b.basbb

fromtable_ainnerjointable_bontable_a.a=table_b.a的结果是：

AAABBABB

----------------------------------------

2a2a

3b3a

外连接：

左表fulljoin右表on条件——2个表的记录都显示出来，满足条件的就显示为同一行，不满足条件的用null显示。

例如：

selecttable_a.aasaa,table_a.basab,

table_b.aasba,table_b.basbb

fromtable_afulljointable_bontable_a.a=table_b.a的结果是：

AAABBABB

----------------------------------------

2a2a

3b3a

1a

4b

左连接：

左表leftjoin右表on条件——把左表的记录全部显示出来，右表中满足条件的显示为同一行，不满足的用null表示。

例如：

selecttable_a.aasaa,table_a.basab,

table_b.aasba,table_b.basbb

fromtable_aleftjointable_bontable_a.a=table_b.a的结果是：

AAABBABB

----------------------------------------

2a2a

3b3a

1a

右连接：

左表rightjoin右表on条件——把右表的记录全部显示出来，左表中满足条件的显示为同一行，不满足的用null表示。

例如：

selecttable_a.aasaa,table_a.basab,

table_b.aasba,table_b.basbb

fromtable_arightjointable_bontable_a.a=table_b.a的结果是：

AAABBABB

----------------------------------------

2a2a

3b3a

4b

分组函数

avg求平均值

count求记录总数

max求最大值

min求最小值

sum对数字型列求和

分析函数（就是sqlserver2005中的排序函数，语法都完全一样）

row_number（）over（）对某列排序后再对排序后的结果按顺序编号：

selectrow_number（）over（orderbyb）as编号,table_a.*fromtable_a就是按b列排序，然后编号。

运行结果是：

编号AB

------------------------------

11a

22a