ORACLE相关学习.docx
- 文档编号:29009465
- 上传时间:2023-07-20
- 格式:DOCX
- 页数:33
- 大小:27.77KB
ORACLE相关学习.docx
《ORACLE相关学习.docx》由会员分享,可在线阅读,更多相关《ORACLE相关学习.docx(33页珍藏版)》请在冰豆网上搜索。
ORACLE相关学习
ORACLE相关学习
(一)用户管理
1.创建用户
createuseruser_name--创建用户
identifiedbypassword
[defaulttablespacedef_tablespace]--指定用户默认表空间
[temporarytablespacetemp_tablespace]--指定用户临时表空间
2.用户授权与收回权限
grantDBAtouser_name--给用户授予DBA角色权限
grantcreateuser,createtabletouser_name(用户名)[withadminoption](withadminoption选
项表示该用户可以将这种系统权限转授予其他用户)--给用户授予系统权限
grantselect,update,insertontable_name(表名)touser_name(用户名)[withgrantoption](with
grantoption选项表示允许该用户将当前的对象权限转授予其他用户)--为用户添加对象权
限
revokecreatetable,createuserfromuser_name(用户名)--收回用户的系统权限(建表,建用
户)
revokeselece,insert,update,deleteontable_name(表名)fromuser_name(用户名)--收回用户
的对象权限(查询,插入,更新,删除).
3.修改用户密码
alteruseruser_name--为用户修改密码
identifiedbynew_password;
或者直接输入:
password--修改当前用户密码.
4.修改用户默认表空间
alteruseruser_name--修改用户的默认表空间
defaulttablespacenew_def_tablespace
temporarytablespacenew_tem_tablespace;
5.锁定或解锁用户帐号
alteruser用户名;--锁定或解锁一个已存在的用户帐号
accountlock|unlock(锁定/解锁)
6.删除用户
dropuseruser_name(用户名);--删除用户
dropuseruser_name(用户名)cascade;--删除用户的同时还要删掉用户所拥有的数据库对象
(如表,索引,簇,视图等);
7.创建角色
createrolerole_name(角色名)
2
[identifiedbyrole_password];--为角色创建密码
revokerole_name(角色名)fromuser_name(用户名);--收回用户的角色权限
droprolerole_name;--删除角色
8.查看用户和系统相关信息
settimingon--查看执行效率。
settimingoff--关掉效率记录。
select*fromrole_sys_privs;--查看当前用户的角色信息
select*fromuser_sys_privs;--查看当前用户授予的权限;
select*fromuser_role_privs;--查看当前用户的角色信息;
SELECT*FROMUSER_PASSWORD_LIMITS;--查看授予用户的密码配置文件参
数
select*fromuser_users;--查看当前用户的帐号信息
select*fromRESOURCE_COST;--查看所有资源的耗费情况
selectconstraint_name,constraint_type,statusfromuser_constraintswheretable_name='表名';
--查看指定表的约束信息
9.常用用户系统权限和对象权限
(常用Oracle系统权限)
createuser可以创建用户的帐号
dropuser可以删除用户的帐号
createsession连接到数据库服务器并能创建对话
createtable在用户自己的模式中创建表
createanytable在数据库的任何模式下创建表
createview在用户自己的模式中创建视图
createanyview在数据库的任何模式下创建视图
createtype在用户自己的模式中创建类型
createanytype在数据库的任何模式下创建类型
createprocedure在用户自己的模式中创建过程
createtrigger在用户自己的模式中创建触发器
createsynonym在用户自己的模式中创建同义词
createsequence在用户自己的模式中创建序列
insertanytable在数据库的所有表中进行插入记录操作
selectanytable在数据库的所有表中进行查询操作
dropanytable可以删除或卸掉数据库中的任何表
updateanytable在数据库的所有表中进行更新操作
deleteanytable在数据库的所有表中进行删除记录操作
executeanyprocedure在数据库中执行任何过程
executeanytype在数据库中能引用和执行数据库中任何类型的方法
由于系统权限过于强大,Oracle推荐尽量避免授予非DBA用户带有any的系统权限,尤其
避免用户拥有对于数据字典操作的权限。
(常用Oracle对象权限)
3
select可以执行检索操作
insert可以执行数据插入操作
update可以执行数据更新操作
delete可以执行删除数据操作
execute可以执行过程等操作
alter可以修改对象属性操作
(二)表操作
connectsystem/manager--连接数据库(cmd中连接数据库:
sqlplussystem/manager@byfwl)
1.创建表
create[globaltemporary]tabletable_name
(column_nametype[constraint])
[oncommit{delete|preserve}now]
[tablespacetablespace_name];--指定该表所在的表空间,如果没有指定,那么该表将被放置
在当前用户默认的表空间中。
globaltemporary选项用于指定该表中的数据是临时的,表示创建一个临时表,临时表中的
数据的有效持续时间由oncommit选项指定。
oncommit选项控制临时表中的数据的持续时间
delete选项表示临时表中的数据将在事务结束时被删除
preserve选项表示临时表中的数据将在会话结束时被删除。
2.查看表结构
desc/describetable_name;--查看表结构
3.修改表结构
altertabletable_name--增加列altertabletable_name--更新列
addcolumn_nametype;modifycolumn_nametype;
altertabletable_name--删除列
dropcolumncolumn_name;
altertabletable_name--更新列值为空或不为空
modifycolumn_namenotnull/null;
altertabletable_name--创建主键约束
addconstraintconstraint_name(约束名)primarykey(column_name[,column_name]);
altertabletable_name--创建惟一约束
4
addconstraintconstraint_name(约束名)unique(column_name[,column_name];
altertabletable_name
addconstraintconstraint_name(约束名)foreignkey(column_name)references
ref_table_name(参照的表名)(ref_column_name)(参照的字段名)
[ondeletecascade]--级联删除
[onupdatecascade];--级联更新
altertabletable_name--创建检查约束
addconstraintconstraint_namecheck(expression)(检查的条件表达式);
altertabletable_name--创建缺省约束
modifycolumn_nametype(列的类型)default(constraint_expression)(指定缺省值)
altertabletable_name--禁止或激活约束
disable(禁止)/enable(激活)[novalidate](表示激活约束时不验证表中已有的数据是否满足约束
的定义,没有这个关键字,则需要验证)constraintconstraint_name(约束名);
altertabletable_name--删除约束
dropconstraintconstraint_name(约束名);
renameold_table_name(旧表名)tonew_table_name(新表名);--修改表名
droptabletable_name--删除表
truncatetabletable_name;--清空表资料(完全清空,不具有事务特性,不能回滚)
4.注释表
commentontabletable_name(表名)istable_comment(注释内容)--为表加注释
commentoncolumntable_name.column_nameiscolumn_comment--为表中目标列加注释
(三)合并操作
mergerintotable_name_1u
usingtable_name_2
on(condition_expression)
whenmatchedthen
update_statement
whennotmatchedthen
insert_statement
table_name_1表示将要把数据合并进来的目标表名称
5
table_name_2表示在合并数据时另外一个数据来源表名称
contidition_expression表示合并数据是否相同的判断条件
update_statement表示当合并条件匹配时执行的更新数据语句。
与一般update语句惟一不同
的是,在这里不需要提供表名称,因为表名称已经在table_name_1参数中指定了。
insert_statement表示当合并条件不匹配时执行的插入数据语句。
与一般insert语句惟一不同
的是,在这里不需要提供表名称,因为表名称已经由table_name_1参数指定了。
(例子:
mergeintoauthors_oldo
usingauthors_newn
on(o.author_id=n.author_id)
whenmatchedthen
update
seto.first_name=n.first_name,
o.last_name=n.last_name
o.birthdate=n.birthdate
whennotmatchedthen
insert
(o.author_id,o.first_name,o.last_name,o.birthdate)values(n.author_id,n.first_name,n.last_name,n.b
irthdate);
注:
表结构应该完全相同)
(四)查询操作
1.闪回查询
闪回查询是通过使用dbms_flashback程序包完成,由sys用户拥有,必须拥有该程序包的
execute权限才可以执行闪回查询。
为用户授予权限:
grantexecuteondbms_flashbacktouser_name(用户帐号)
基于时间的闪回查询语法:
executedbms_flashback.enable_at_time(datetime)--datetime表示将要闪回查询的时间
如:
executedbms_flashback.enable_at_time(sysdate-1/1440);
基于系统改变号的闪回查询语法:
executedbms_flashback.enable_at_system_change_number(scn);--scn表示将要把数据闪回
查询至的系统改变号,系统改变号可以使用dbms_flashback程序包的
get_system_change_number()过程获得。
如:
execute:
current_scn:
=dbms_flashback.get_system_change_number();
executedbms_flashback.enable_at_system_change_number(:
current_scn);
闪回查询执行完毕后,应该关闭闪回查询的功能,语法:
executedbms_flashback.disable
2.查询分类:
按连接类型查询分为:
内查询,外查询,交叉查询,自查询
按子查询分为:
单列单行子查询,单列多行子查询,多列单行子查询,多列多行子查询,关
6
联子查询,嵌套子查询。
集合查询(union,unionall),层次查询;
3.集合查询
unionall--返回查询语句中的所有行,包括重复的数据行
union--返回查询语句中的所有行,但是不包括重复的数据行
intersect--返回在两个查询语句中都出现的数据行
minus--返回第一个查询中的数据行减去出现在第二个查询语句中的数据行后的
差;
4.层次查询语法:
selectlevel,column_name,expression,..fromtable_name[wherewhere_condition]
startwithstart_condition
connectbypriorprior_condition
5.情景查询语法:
decode(value_expression,search_value,result_value,default_value);
value_expression参数表示将要与搜索值比较的值或表达式
search_value参数表示将要的搜索的值
result_value参数表示search_value参数与value_expression参数相等时
value_expression参数的取值结果
default_value参数表示search_value参数与value_expression参数不相等时
value_expression参数的取值结果
如:
selectisbn,title_chinese,book_type_id,decode(book_type_id,'11','经济类图书','12','历史类图
书','其他类型图书')frombooks;
当图书类型代码是11时,显示'经济类图书',当图书类型代码是12时,显示'历史类图
书',两者都不是时显示其他类型图书'。
6.分类汇总
selectcolumn_listfromtable_namewherewhere_conditiongroupbygroup_column_listhaving
group_condition;
7.case表达式
casesearch_expression
whenexpression_1thendisplay_result_1
whenexpression_2thendisplay_result_2
.......
whenexpression_nthendisplay_result_n
elsedefault_display_result
end
7
(五)常用函数
1.字符函数:
selectascii('a')fromdual;--返回字符a的ascii值;
selectchar(35)fromdual;--返回ascii值35对应的的字符;
selectlength('hahahah')fromdual;--统计'hahahah'的字符个数;
selectlengthb('哈哈中国')fromdual;--统计'哈哈中国'的字节数;
selectlower('AAADsss')fromdual;--将'AAADsss'中大写字母转换成小写字母;
selectsubstr('dhdhhdhd',2,6)fromdual;--返回从字符串'dhdhhdhd'的第2位开始往
后数6个字符的字符串;
selectnvl2(emp_name,'白云飞','中国')fromdual;--如果emp_name列为空则返回白云飞,否
则返回中国;
ltrim(x[,trim_string])--删除左空格。
rtrim(x[,trim_string])--删除右空格
trim()--删除左右空格
selectconcat(emp_id,emp_name)fromemp;--返回字段emp_id与emp_name的连接,
这种连接是紧密连接,两个字符串之间没有空格;
2.数学函数
selectceil(12.345)fromdual;--返回大于或等于12.345的最大整数值,该执行结果为13;
selectmod(10,3)fromdual;--返回10除3的余数;
3.转换函数
selectto_char(123.456,'11.11.11')fromdual;--将123.456以11.11.11的格式转换
成字符形式;
selectto_number('123456')fromdual;--将'123456'转换成数字类型;
selectto_date('2006,6月15','yyyy,month,dd')fromdual;--将'2006,6月15'转换成以
'yyyy,month,dd'格式的日期形式;
4.合计函数
avg()--平均值
count()--统计数量
max()--最大值
min()--最小值
median()--中位数
stddev()--标准差
sum()--汇总
variance()--方差
5.日期函数
add_months(x,y)--在x上增加y个月,如果y是负数,表示从x中减去y个月
8
last_day(x)--返回包含在x中的月份的最后一天
months_between(x,y)--返回x和y之间的月数,从日历上来看,如果x在y之前,返回负
数,否则,返回正数;
next_day(x,day)--返回紧接着x的下一天,day是一个字符串;
round(x[,unit])--圆整x,在默认情况下,x被圆整到最接近的一天,可以使用unit参
数提供圆整的单位,例如mm表示圆整到最接近月的第一天;
sysdate()--返回系统当前时间;
trunk(x[,unit])--截断x,默认情况下,x被截断为当天的开始。
可以使用unit参数提供
截断的单位,例如mm表示截断到该月的第一天;
(六)数据库编程结构
1.条件结构:
ifcondition_1then
executable_statements_1
elseifcondition_2then
executable_statements_2
elseifcondition_3then
executable_statements_3
else
executable_statements_4
endif
2.循环结构:
简单循环结构:
loop.....executable_statements.....endloop;
while循环:
whileconditionloop
executable_statements
endloop;
for循环结构:
forloop_variable_namein[reverse]lower_bound..upper_boundloop
executable_statements
endloop;
loop_variable_name参数指定循环变量。
reverse关键字指定循环变量按照递减方式增长,这时,循环变量的初始值是最高边界,然
后按照步长递减。
lower参数指定循环变量的最低边界
upper参数指定循环变量的最高边界
executable_statements循环体中的语句
9
(七)其他创建操作
1.创建游标
cursorcursor_name(游标名)isselect_statement(包括的结果集);--声明游标
opencursor_name--打开游标
fetchcursor_nameintovariable_list(变量列表,与select中的select_list列表相对应)--从游
标中取数据
closecursor_name--关闭游标
2.创建存储过程
create[orreplace](判断系统是否有重名,如果有则覆盖)procedureprocedure_name(存储过程
名)
[(parameter_name[in|out|inout]type[,....])](参数列表,需要带括号)
is|as
begin
procedure_body
endprocedure_name(可不带过程名);
in|out|inout关键字指定过程的模式,in是默认值,表示该参数的值在过程运行过程中不被
改变;out关键字表示该参数的值只在运行过程中赋值;inout表示该参数的值既可以事先
赋值,也可以在过程运行过程中设置;
executeprocedure_name--执行存储过程
3.创建函数
create[orreplace]functionfunction_name
[(parameter_name[in|out|inout]type[,....])](参数列表,需要带括号)
returntype
is|as
begin
fu
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 相关 学习
![提示](https://static.bdocx.com/images/bang_tan.gif)