oracle语法大全.docx
《oracle语法大全.docx》由会员分享,可在线阅读,更多相关《oracle语法大全.docx(25页珍藏版)》请在冰豆网上搜索。
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_tblwhere