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位整