ORACLE DBA常用语句.docx
《ORACLE DBA常用语句.docx》由会员分享，可在线阅读，更多相关《ORACLE DBA常用语句.docx（26页珍藏版）》请在冰点文库上搜索。
ORACLEDBA常用语句
连接命令
conn[ect]
用法:
conn用户名/密码@网络服务名[assysdba/sysoper]
当用特权用户身份链接时，必须带上assysdba或者assysoper
注：
sysdba权限最大
disc[onnect]
断开连接
passw[ord]
修改用户的密码
需先登陆
文件操作命令
start或者@
运行sql脚本
案例@d:
\a.sql
spool
将sql*plus屏幕上的内容输出到制定文件中
案例：
spoold:
\b.sql并输入spooloff
显示和设置环境变量
linesize设置显示行宽度，默认80
setlinesize
pagesize设置每页显示的行数目，默认14
用户管理
权限：
系统权限：
用户对数据库的相关权限
对象权限：
用户对其他用户的数据对象（该用户创建的数据：
表视图过程）访问权限（select,insertupdatadeleteallcreateindex...）
角色：
一种特定的用户，包含已被赋予的权限
自定义角色，预定义角色
eq:
connectdbaresource
创建用户
createuser用户名identifiedby密码
创建的用户是没有任何权限的，需要给权限
赋予权限：
grantconnectto用户名
grantselectonempto用户名-----select*from用户名.table;
权限维护grantselecton用户名.empto用户名withgrantoption
grantconnecttoxiaomingwithadminoption
注：
取消中间者权限后被授权者人不再有该权限
收回权限
revokeselecton用户名.empfrom用户名
修改密码
password用户名
删除用户
dropuser用户名
如果要删除的用户已经创建了表，那么删除是要带一个参数cascade
使用profile管理用户口令
creatrprofile配置文件名limitfailed_login_attempts3password_lock_time2;
alteruser用户名profile配置文件名;
解锁
alteruser用户名accountunlock;
密码终止口令（可让用户定期修改密码）
createprofile配置文件名limitpassword_life_time10password_grace_time2;
alteruser用户名profile配置文件名
口令历史
建立PROFILE
createprofilepassword_historylimitpassword_life_time10password_grace_time2password_reuse_time10
删除配置文件
dropprofile配置文件名【casecade】
表的创建
1\必须以字母开头
2\长度不能超过30字符
3\不能使用ORACLE的保留字
4\只能使用A-Z,a-z,0-9等
字符类型
char定长最大2000字符
varchar2变长最大4000字符
clob字符型大对象
数字型
number范围10的-38次方到10的38次方可以整数和小数
number（5,2）
表示一个小数有5位有效数，2位小数
范围-999.99到999.99
number（5）
表示一个五位整数
范围-99999-99999
日期型
date
timestamp
图片
blob二进制数据可以存档图片\声音容量限制4GB
显示表空间
selecttablespace_namefromdba_tableswheretable_name=TABLE
显示某用户的所有表
selectTABLE_NAMEfromall_tableswhereowner='USER';
显示当前数据库的所有表
select*fromtab;
显示当前用户的所有表
select*fromuser_tables;
创建表
createtable表名（行名称字段属性）；
删除表
droptable表名
添加字段
altertable表名add（行名称字段属性）；
修改字段长度
altertable表名modify（行名称字段属性）；有数据的最好不改
删除字段
altertable表名dropcolumn行名称；
修改表名
renametable表名to新表名；
查看表结构（字段名称属性是否为空等属性）
desc表名;
向表添加数据
insertinto表名称values（各行数据）
默认格式日期是DD-MON-YY例：
‘25-12月-16’
改日期默认格式
altersessionsetnls_date_format='yyyy-mm-dd';
修改后
insertintostudentvalues（'2016-12-25'）
插入部分字段
insertinto表名（字段名）values（'字段值'）
多个字段用逗号隔开
插入空值
insertinto表名（字段名）values（‘字段值’，null）;
修改字段
update表名set字段名=‘更改后的值’where字段名或主键=‘字段值’；
修改多个字段
update表名set字段名=’更改后的值’，字段名=‘更改后的值’where字段名或者主键=‘字段值’；
修改含有null值的数据
删除数据
deletefrom表名；
删除表的记录，但是表仍存在。
并且存在日志，可恢复。
truncatetablestudent;
删除表中所有记录，表仍存在。
无日志，不可恢复。
有点速度快。
设置还原点
savepoint点名称；
还原数据
rollbackto点名称;
显示操作时间
settimingon;
insertinto表名（字段名）select*from表名
可实现大批量复制
selectcount（*）from表名；
可数一个表中的所有行数
select字段名from表名
selectdistinct字段名from表名；
查询emp表员工年工资范例
selectsal*13+nvl（comm,0）*13"年工资",enamefromemp;
多个条件用AND
EQ:
selectename,salfromempwheresal>=2000andsal<=2500;
like查询
selectename,salfromempwhereenamelike'S%';
_表示单个任意字符，%表示0-无限大的多个字符
in用于查询多个条件
EQ:
select*fromempwhereempnoin（条件，条件）;
查询工资高于500或者是岗位为MANAGER雇员，同时还要满足他们的姓名首字母为大写的J
select*fromempwhere（sal>500orjob='manager'）andenamelike'J%';
orderby字句
排序语句
select*fromempoderbysal;按照薪水从低到高
select*fromemporderbysaldesc;从高到低
select*fromemporderbydeptno,saldesc;
使用别名排序
selectename,（sal+nvl（comm,0））*12as"年薪"fromemporderby“年薪”；
分页查询
selectmax（sal）,min（sal）fromemp;
selectename,salfromempwheresal=（selectmax（sal）fromemp）;
groupby用于对查询的结果分组统计
having子句用于限制分组显示结果
EQ：
查看部门平均工资和最高工资
selectavg（sal）,max（sal）,deptnofromempgroupbydeptno;
查看部门不同职位的平均工资和最低工资
selectavg（sal）,max（sal）,deptno,jobfromempgroupbydeptno,job;
显示平均工资低于2000的部门好和它的平均工资
selectavg（sal）,max（sal）,deptnofromempgroupbydeptnohavingavg（sal）>2000orderbyavg（sal）；
多表查询：
显示雇员名，雇员工资以及所在部门的名字
selecta1.ename,a1.sal,a2.dnamefromempa1,depta2wherea1.deptno=a2.deptno;
显示部门号为10的部门名\员工名和工资
selecta2.ename,a1.dname,a2.salfromdepta1,empa2wherea1.deptno=a2.deptnoanda1.deptno=10
显示各个员工的姓名，工资，及其工资的级别。
selecta1.ename,a1.sal,a2.gradefromempa1,salgradea2wherea1.salbtweena2.losalanda2.hisal;
显示雇员名，雇员工资及所在部门的名字，并按部门排序
selecta1.ename,a1.sal,a2.dnamefromempa1,depta2wherea1.deptno=a2.deptnoorderbya1.deptno;
自连接
指同一张表的连接查询
显示某个员工的上级领导的姓名。
selectworker.name,boss.enamefromempworker,empbosswhereworker.mgr=boss.empnoandworker.ename='FORD';
子查询
如何显示与SMITH同一部门的所有员工
selectdeptnofromemowhereename='SMITH';
select*fromempwheredeptno=（selectdeptnofromemowhereename='SMITH'）;
多行子查询
如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
select*fromempwherejobin（selectdistinctjobfromempwheredeptno=10）;
在多行子查询中使用all操作符
如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
selectename,sal,deptnofromempwheresal>all（selectsalfromempwheredeptno=30）;
slect*fromempwheresal>（selectmax（sal）fromempwheredeptno=30）;
any操作符
如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号。
selectename,sal,deptnofromempwheresal>any（selectsalfromempwheredeptno=30）;
select*fromempwheresal>（selectmin（sal）fromempwheredeptno=30）;
多列子查询
如何查询与smith的部门和岗位完全相同的所有雇员。
select*fromempwhere（deptno,job）=（selectdeptno,jobfromempwhereename='SMITH'）;
在FROM子句中使用子查询
如何显示高于自己部门平均工资的员工信息
selecta2.ename,a2.sal,a2.deptno,a1.mysalfromempa2,（selectdeptno,avg（sal）mysalfromempgroupbydeptno）a1wherea2.deptno=a1.deptnoanda2.sal>a1.mysal
分页查询一共有三种方式
rownum分页
selecta1.*,rownumrnfrom（select*fromemp）a1whererownum<=10;
select*from（selecta1.*,rownumrnfrom（select*fromemp）a1whererownum<=10）wherern>=6;
指定查询列，只需修改最里层的子查询
排序只需修改最里层
rowid分页
select*fromt_xiaoxiwhererowidin（selectridfrom（selectrownumrn,ridfrom（selectrowidrid,cidfromt_xiaoxiorderbyciddesc）whererownum<10000）wherern>9980）orderbyciddesc;
查询表的是所有行数
selectcount（*）fromemp;
用查询结果创建新表
createtable表名（id,name,sal,job,deptno）asselectempo,enmae,sal,job,deptnofromemp;
合并查询
union
selectename,sal,jobfromempwheresal>2500union
selectename,sal,jobfromemowherejob='manager';
unionall
该操作赋予union相似，但是它不会取消重复行，而且不会排序
selectename,sal,jobfromempwheresal>2500unionallselectename,sal,jobfromempwherejob='manager';
intersect
取两个查询结果的交集
selectename,sal,jobfromempwheresal>2500intersectselectename,sal,jobfromempwherejob='manager';
minus
取两个结果的差集，只会显示存在第一个集合中，而不存在第二个集合中的数据
selectename,sal,jobfromempwheresal>2500minusselectename,sal,jobfromempwherejob='manager';
使用特定格式插入日期值
insertintoempvalues（9998,'小红’，'mannager',7782,'11-11月-1988',78.9,55.33,10）
insertintoempvalues（9997,'小红2',to_date（'1988-12-12',YYYY-mm-dd）,78.9,55.33,10）;
行迁移
insertintokkk（myid,myname,mydept）selectempo,ename,deptnofromempwheredeptno=10;
希望员工scott的岗位，工资，补助和SMITH一样
updateempset（job,sal,comm）=（selectjob,sal,commfromempwhereename='SMITH'）whereename='SCOTT';
事务处理DML数据处理语言增删改
只读事务
只读事务是指只允许执行查询的操作，而不是允许执行任何其他DML操作的事务
settransacationreadonly;
SQL函数
字符函数-----是ORACLE中最常用的函数
lower（char）:
将字符串转换为小写格式
将所有员工的名字按小写的方式显示
selectlower（ename）fromemp;
upper（char）:
将字符串转换为大写格式
将所有员工的名字按大写的方式显示
selectupper（ename）fromemp;
length（char）:
返回字符串的长度
显示正好为5个字符的员工的姓名
select*fromempwherelength（ename）=5;
substr（char,m,n）：
取字符串的子串
显示所有员工姓名的前三个字符
selectsubstr（ename,1,3）fromemp;----从第几个取，取三个
以首字母大写的方式显示所有员工的姓名
1.完成首字母大写
selectupper（substr（ename,1,1））fromemp;
2.完成后面字母小写
selectlower（substr（ename,2,length（ename）-1））fromemp;
3合并
selectupper（substr（ename,1,1））||lower（substr（ename,2,length（ename）-1））fromemp;
以首字母小写的方式显示所有员工的姓名
selectlower（substr（ename,1,1））||upper（substr（ename,2,length（ename-1））fromemp;
替换函数
replace（char1,search_string,replace_string）
selectreplace（ename,'A','a'）fromemp;
instr（char1,char2,[,n[,m]]取子串在字符串的位置
round（n,[m]）该函数用于执行四舍五入，如果省略掉m,则四舍五入到整数；如果m是正数，则四舍五入到小数点的m位后，如果m是负数，则四舍五入到小数点的m位前
selectround（sal,1）,salfromempwhereename='shunping';
效果:
2456.34-----2456.355.66----55.7
trunc（n,[m]）该函数用于截取数字，如果省掉m，就截去小数部分，如果m是正数就截取到小数点的m位后，如果m是负数，则截取到小数点的前m位
selecttrunc（comm,1）,commfromempwhereename='shunping';
效果：
55.66------55.6
selecttrunc（comm,-1）,commfromempwhereename='shunping';
效果：
55.66----50
mod（m,n）取余数
selectmod（10,2）fromdual;
floor（n）返回小于或是等于n的最大整数
向下取整
ceil（n）返回大于或是等于n的最小整数
向上取整
dual在做ORACLE测试，可以使用dual表
显示在一个月为30天的情况下所有员工的日薪金，忽略余数。
selecttrunc（sal/30）,enamefromemp;
selectfloor（sal/30）,enamefromemp;
数学函数
abs（n）返回数字n的绝对值
acos（n）返回数字的反余旋值
asin（n）返回数字的反正旋值
atan（n）返回数字的反正切
cos（n）
exp（n）返回e的n次幂
log（m,n）返回对数值
power（m,n）返回m的n次幂
日期函数
sysdate:
add_months（d,n）
显示入职八个月多的员工
select*fromempwheresysdate>add_months（hiredate,700）;
last_day（d）
返回指定日期所在的月份的最后一天
显示工作满十年的员工
select*fromempwheresysdate>a=dd_months（hiredate,12*10）;
对于每个员工，显示其加入公司的天数
selecttrunc（sysdate-hiredate）"入职天数",enamefromemp;
找出个月倒数第三天受雇的所有员工。
selecthiredate,enamefromempwherelast_day（hiredate）-2=hiredate;
转换函数
用于将数据类型从一种转为另外一种，在某些情况下，ORACLESERVER允许值的数据类型和实际的不一样，这是ORACLESERVER会隐含的转化数据类型
例：
createtabletable1（idint）;
insertintotable1values（'10'）-------这样ORACLE会自动将'10'转换为10
createtabletable2（idvarchar2（10））;
insertintotable2values
（1）;--------这样ORACLE就会自动的将1转换为'1'
tochar
selectename,to_char（hiredate,'yyyy-mm-ddhh24:
mi:
ss'）fromemp;
to_char（sal,'L99，999.99'）
注“9显示数字并忽略前面的0
0：
显示数字，如位数不足，则用0补齐
.在制定位置显示小数点
，在指定位置显示逗号
$在数字前加美元
L在数字前加本地货币符号
C在数字前加国际货币符号
G在指定位置显示组分隔符
D在指定位置显示小数点符号
to_char
显示1980年入职的所有员工
select*fromempwhereto_char（hiredate,'yyyy'）=1980;
显示所有12月份入职的员工
select*fromempwhereto_char（hiredate,'mm'）=12;
系统函数sys_context
terminal当前会话客户所对应的终端的标识符
lanuage语言
db_name当前数据库名称
nls_date_format当前会话客户所对应的日期格式
session_user当前会话客户所对应的数据库用户名
current_schema当前会话客户所对应的默认方案名
host返回数据库所在的主机的名称
selectsys_context（'userenv','db_name'）fromdual;
number（7,2）7位数字，2位小数=5位整
升级会员 