oracle语法大全.docx
《oracle语法大全.docx》由会员分享，可在线阅读，更多相关《oracle语法大全.docx（21页珍藏版）》请在冰豆网上搜索。
oracle语法大全
Oracle数据库语句大全
一．入门部分
1.创建表空间
createtablespaceschooltbsdatafile‘D:
\oracle\datasource\schooltbs.dbf’size10Mautoextendon;
2.删除表空间
droptablespaceschooltbs[includingcontentsanddatafiles];
3.查询表空间基本信息
select*||tablespace_namefromDBA_TABLESPACES;
4.创建用户
createuserlihua
identifiedbylihua
defaulttablespaceschooltbs
temporarytablespacetemp;
5.更改用户
alteruserlihua
identifiedby123
defaulttablespaceusers;
6.锁定用户
alteruserlihuaaccountlock|unlock;
7.删除用户
dropuserlihuacascade;--删除用户模式
8.oracle数据库中的角色
connect,dba,select_catalog_role,delete_catalog_role,execute_catalog_role,exp_full_database,imp_full_database,resource
9.授予连接服务器的角色
grantconnecttolihua;
10.授予使用表空间的角色
grantresourcetolihuawithgrantoption;--该用户也有授权的权限
11.授予操作表的权限
grantselect,insertonuser_tbltoscott;--当前用户
grantdelete,updateonlihua.user_tbltoscott;--系统管理员
二．SQL查询和SQL函数
1.SQl支持的命令：
数据定义语言（DDL）:
create,alter,drop
数据操纵语言（DML）:
insert,delete,update,select
数据控制语言（DCL）:
grant,revoke
事务控制语言（TCL）:
commit,savepoint,rollback
2.Oracle数据类型
字符，数值，日期，RAW，LOB
字符型
char:
1-2000字节的定长字符
varchar2:
1-4000字节的变长字符
long:
2GB的变长字符
注意：
一个表中最多可有一列为long型
Long列不能定义唯一约束或主键约束
long列上不能创建索引
过程或存储过程不能接受long类型的参数。
数值型
number:
最高精度38位
日期时间型
date:
精确到ss
timestamp:
秒值精确到小数点后6位
函数
sysdate,systimestamp返回系统当前日期，时间和时区。
更改时间的显示
altersessionsetnls_date_language=’american’;
altersessionsetnls_date_format=’yyyy-mm-dd’;
Oracle中的伪列
像一个表列，但没有存储在表中
伪列可以查询，但不能插入、更新和修改它们的值
常用的伪列：
rowid和rownum
rowid:
表中行的存储地址，可唯一标示数据库中的某一行，可以使用该列快速定位表中的行。
rownum:
查询返回结果集中的行的序号，可以使用它来限制查询返回的行数。
3.数据定义语言
用于操作表的命令
createtable
altertable
truncatetable
droptable
修改表的命令
altertablestu_tablerenametostu_tbl;--修改表名
altertablestu_tblrenamecolumnstu_sextosex;--修改列名
altertablestu_tbladd（stu_agenumber）;--添加新列
altertablestu_tbldrop（sex）;--删除列
altertablestu_tblmodify（stu_sexvarchar2
（2））;--更改列的数据类型
altertablestu_tbladdconstraintpk_stu_tblprimarykey（id）;--添加约束
4.数据操纵语言
select,update,delete,insert
利用现有的表创建表
createtablestu_tbl_logasselectid,stu_name,stu_agefromstu_tbl;--
选择无重复的行
selectdistinctstu_namefromstu_tbl;--
插入来自其他表中的记录
insertintostu_tbl_logselectid,stu_name,stu_agefromstu_tbl;
5.数据控制语言
grant,revoke
6.事务控制语言
commit,savepoint,rollback
7.SQL操作符
算术操作符:
+-*/
比较操作符:
=,!
=,<>,>,<,>=,<=,between-and,in,like,isnull等
逻辑操作符:
and,or,not
集合操作符:
union,unionall,intersect,minus
连接操作符:
||
示例中stu_tbl_log中的数据如下：
IDSTU_NAMESTU_AGE
----------------------------------------
1000李华20
1001accp20
1003nimda3
stu_tbl中的数据如下：
IDSTU_NAMESTSTU_AGE
------------------------------------------
1000李华男20
1001accp男20
1002admin男30
示例：
select（3+2）/2fromdual;--算术操作符，结果：
2.5
select*fromstu_tblwherestu_age>=20;--比较操作符
select*fromstu_tblwherestu_namelike'%a%';--比较操作符：
like
select*fromstu_tblwherestu_namelike'a___';--比较操作符：
like
select*fromstu_tblwherestu_agein（20,30）;--比较操作符：
in
select*fromstu_tblwherestu_agebetween20and30;--比较操作符：
between
selectstu_namefromstu_tblunionall
selectstu_namefromstu_tbl_log;--集合操作符：
unionall，测试结果具体如下：
STU_NAME
-----------
李华
accp
admin
李华
accp
nimda
已选择6行。
selectstu_namefromstu_tblunion
selectstu_namefromstu_tbl_log;--集合操作符：
union，测试结果具体如下：
STU_NAME
---------
accp
admin
nimda
李华
selectstu_namefromstu_tblintersect
selectstu_namefromstu_tbl_log;--集合操作符：
intersect，测试结具体如下：
STU_NAME
----------
accp
李华
selectstu_namefromstu_tblminus
selectstu_namefromstu_tbl_log;--集合操作符：
minus，测试结果如下：
STU_NAME
----------
Admin
从中可以看出：
minus是获取第一张表独有的数据
intersect是获取两张表中都有的数据
union是整合两张表的数据，都有的只显示一次
unionall是纯粹的两张表数据整合
selectid,stu_name||''||stu_sexasname_sex,stu_age
fromstu_tbl;--连接操作符||，测试结果具体如下：
IDNAME_SEXSTU_AGE
-------------------------------------------
1000李华男20
1001accp男20
1002admin男30
8.SQL函数
单行函数：
从表中查询的每一行只返回一个值，可出现在select子句，where子句中
日期函数
数字函数
字符函数
转换函数：
ToChar（）,ToDate（）,ToNumber（）
其他函数:
Nvl（exp1,exp2）:
表达式一为null时，返回表达式二
Nvl2（exp1,exp2,exp3）:
表达式一为null时返回表达式三，否则返回表达式二
Nullif（exp1,exp2）:
两表达式相等时，返回null，否则返回表达式一
分组函数：
基于一组行来返回
Avg,Min,Max,Sum,Count
Groupby,having
分析函数
Row_number,rank,dense_rank
示例：
selectu.user_name,sum（oi.order_num*oi.order_price）astotal,row_number（）over（orderbysum（oi.order_num*oi.order_price）desc）assortfromorder_item_tbl
oi,user_tblu,order_tblowhereoi.order_id=o.idando.user_id=u.idgroupbyu.user_name;
三．锁和数据库对象
1.锁：
数据库用来控制共享资源并发访问的机制。
锁的类型：
行级锁，表级锁
行级锁：
对正在被修改的行进行锁定。
行级锁也被称之为排他锁。
在使用下列语句时，Oracle会自动应用行级锁：
insert,update,delete,select……forupdate
select……forupdate允许用户一次锁定多条记录进行更新。
使用commitorrollback释放锁。
表级锁：
locktableuser_tblinmodemode;
表级锁类型：
行共享rowshare
行排他rowexclusive
共享share
共享行排他sharerowexclusive
排他exclusive
死锁：
两个或两个以上的事务相互等待对方释放资源，从而形成死锁
2.数据库对象
oracle数据库对象又称模式对象
数据库对象是逻辑结构的集合，最基本的数据库对象是表
数据库对象：
表，序列，视图，索引
序列
用于生成唯一，连续序号的对象。
创建语法：
createsequenceuser_id_seq
startwith1000
incrementby1
maxvalue2000
minvalue1000
nocycle
cache1000;--指定内存中预先分配的序号
访问序列：
selectuser_id_seq.currvalfromdual;
selectuser_id-seq.nextvalfromdual;
更改删除序列：
altersequenceuser_id_seqmaxvalue10000;--不能修改其startwith值
dropsequenceuser_id_seq;
在Hibernate中访问序列：
user_id_seq
视图
以经过定制的方式显示来自一个或多个表的数据
创建视图：
createorreplaceview
user_tbl_view（vid,vname,vage）
asselectid,user_name,agefromuser_tbl
[withcheckoption]|[withreadonly];
创建带有错误的视图：
createforceviewuser_tbl_force_viewas
select*fromuser_table;--此时user_table可以不存在
创建外联接视图：
createviewuser_stu_viewas
selectu.id,u.user_name,u.password,s.ddress
fromuser_tblu,stu_tbls
whereu.s_id（+）=s.id;--哪一方带有（+），哪一方就是次要的
删除视图：
dropuser_stu_view;
索引
用于提高SQL语句执行的性能
索引类型：
唯一索引，位图索引，组合索引，基于函数的索引，反向键索引
创建标准索引：
createindexuser_id_indexonuser_tbl（id）tablespaceschooltbs;
重建索引：
alterindexuser_id_indexrebuild;
删除索引：
dropindexuser_id_index;
创建唯一索引：
createuniqueindexuser_id_indexonuser_tbl（id）;
创建组合索引：
createindexname_pass_indexonuser_tbl（user_name,password）;
创建反向键索引：
createindexuser_id_indexonuser_tbl（id）reverse;
四．使用PL/SQL
可用于创建存储过程，触发器，程序包，给SQL语句的执行添加程序逻辑。
支持SQL，在PL/SQL中可以使用：
数据操纵命令
事务控制命令
游标控制
SQL函数和SQL运算符
支持面向对象编程（OOP）
可移植性
更佳的性能，PL/SQL经过编译执行
分为三个部分：
声明部分，可执行部分和异常处理部分
[declare
declarations]
begin
executablestatements
[exception
handlers]
end;
打开输出
setserverouton;
--根据输入编号获取某学员的成绩--if
declare
scoreuser_tbl.score%type;
begin
selectscoreintoscorefromuser_tblwhereid='&id';
ifscore>90then
dbms_output.put_line（'优秀'）;
elsifscore>80then
dbms_output.put_line（'良好'）;
elsifscore>60then
dbms_output.put_line（'及格'）;
else
dbms_output.put_line（'差'）;
endif;
end;
--根据学员姓名获取某学员的成绩--if
declare
scoreuser_tbl.score%type;
begin
selectscoreintoscorefromuser_tblwhereuser_name='&name';
ifscore>90then
dbms_output.put_line（'优秀'）;
elsifscore>80then
dbms_output.put_line（'良好'）;
elsifscore>60then
dbms_output.put_line（'及格'）;
else
dbms_output.put_line（'差'）;
endif;
end;
--case的使用
declare
gradeuser_tbl.grade%type;
begin
selectgradeintogradefromuser_tblwhereid='&id';
casegrade
when'A'thendbms_output.put_line（'优异'）;
when'B'thendbms_output.put_line（'优秀'）;
when'C'thendbms_output.put_line（'良好'）;
elsedbms_output.put_line（'一般'）;
endcase;
end;
--基本循环
declare
inumber（4）:
=1;
begin
loop
dbms_output.put_line（'loopsize:
'||i）;
i:
=i+1;
exitwheni>10;
endloop;
end;
--while循环
declare
inumber（4）:
=1;
begin
whilei<=10loop
dbms_output.put_line（'whileloopsize='||i）;
i:
=i+1;
endloop;
end;
--for循环
declare
inumber（4）:
=1;
begin
foriin1..10loop
dbms_output.put_line（'forloopSize:
'||i）;
endloop;
end;
declare
inumber
（2）:
=1;
jnumber
（2）:
=1;
begin
foriinreverse1..9loop
forjin1..iloop
dbms_output.put（j||'x'||i||'='||j*i||''）;
endloop;
dbms_output.put_line（''）;
endloop;
end;
--动态SQL
declare
userIdnumber
（2）;
sql_strvarchar2（100）;
userNameuser_tbl.user_name%type;
begin
executeimmediate'createtabletestExe（idnumber,test_namevarchar2（20））';
userId:
='&userId';
sql_str:
='selectuser_namefromuser_tblwhereid=:
id';
executeimmediatesql_strintouserNameusinguserId;
dbms_output.put_line（userName）;
end;
（or
declare
id_paramnumber:
='&id_param';
sql_strvarchar2（100）;
name_paramstu_tbl.stu_name%type;
begin
sql_str:
='selectstu_namefromstu_tblwhereid=:
p';
executeimmediatesql_strintoname_paramusingid_param;
dbms_output.put_line（name_param）;
end;
/
）
--异常处理
declare
gradenumber（4）;
begin
grade:
='&grade';
casegrade
when1thendbms_output.put_line（'好的'）;
--elsedbms_output.put_line（'不好'）;
endcase;
exception
whencase_not_foundthen
dbms_output.put_line（'输入类型不匹配！
'）;
end;
--系统异常
declare
rowDuser_tbl%rowtype;
begin
select*intorowDfromuser_tbl;
dbms_output.put_line（rowD.id||''||rowD.user_name||''||rowD.password）;
exception
whentoo_many_rowsthen
dbms_output.put_line（'不能将多行赋予一个属性！
'）;
end;
or
declare
rowDuser_tbl%rowtype;
begin
select*intorowDfromuser_tblwhereid=5;
dbms_output.put_line（rowD.id||''||rowD.user_name||''||rowD.password）;
exception
whentoo_many_rowsthen
dbms_output.put_line（'不能将多行赋予一个属性！
'）;
whenno_data_foundthen
dbms_output.put_line（'没有您要查找的数据！
'）;
end;
--自定义错误
declare
invalidErrorexception;
categoryvarchar2（20）;
begin
category:
='&category';
ifcategorynotin（'附件','顶盘','备件'）then
raiseinvalidError;
else
dbms_output.put_line（'您输入的类别是：
'||category）;
endif;
exception
wheninvalidErrorthen
dbms_output.put_line（'无法识别的类别！
'）;
end;
--引发应用程序异常
declare
app_exceptionexception;
gradeuser_tbl.grade%type;
begin
selectgradeintogradefromuser_tblwhereid=&i
升级会员 