oracle汇总.docx
- 文档编号:29098277
- 上传时间:2023-07-20
- 格式:DOCX
- 页数:53
- 大小:38.16KB
oracle汇总.docx
《oracle汇总.docx》由会员分享,可在线阅读,更多相关《oracle汇总.docx(53页珍藏版)》请在冰豆网上搜索。
oracle汇总
解锁:
alteruserscottaccountunlock;
使用profile管理用户口令
profile是口令限制,资源限制的命令集合,当建立数据库的时候,oracle会自动建立名为default的profile,当建立用户没有指定profile选项时,系统会自动将default分配给用户。
1账户锁定
指定该账户登录时最多可以输入密码的次数,指定用户锁定的时间(天),一般这个命令要dba来执行。
例子:
指定xiaoming最多可以尝试3次登录,锁定时间为2天
createprofilelock_accountlimitfailed_login_attempts3password_lock_time2;
alteruserxiaomingprofilelock_account;
2解锁
alteruserxiaomingaccountunlock;
3终止口令
为了让用户定期修改自己的密码,可是使用终止口令来完成,dba来完成此任务
例子:
让xiaoming每10天更改一次密码,宽限期2天
createprofilemyplimitpassword_life_time10password_grace_time2;
alteruserxiaomingprofilemyp;
4口令历史
如果希望用户修改的密码不能使用以前的密码,可以使用口令历史,将旧的密码都放在数据字典里面,当新旧密码一样的时候,提示用户重新输入密码。
createprofilepwd_historylimitpassword_life_time10password_grace_time2password_reuse_time10;
alteruserxiaomingprofilepwd_history;
5删除profile
dropprofilepwd_history[cascade];
6更改profile
alterprofileterminate_accountlimitpassword_life_time10password_grace_time2,password_reuse_time10;
7显示用户的profile
selectusername,profilefromdba_user;
dba_profiles可以显示profile的口令和资源限制信息
select*fromdba_profiles;
考虑:
sys是否有权限将scott.emp上的操作权限授予xiaoming?
角色管理
角色是相关的命令的集合,目的是为了简化权限的管理。
假定有用户a,b,c,给他们授予权限:
1连接数据库2在emp表上有insertupdatdeleteselect的权限
如果要单独授予权限
首先将createsession,selectonempupdateonemp,deleteonemp,insertonemp授予一个角色,然后再把角色授予用户,可以使用自定义完成
2预定义角色
2.1connect角色
具有一般应用开发人员需要的大部分的权限,当建立一个用户时,多数情况下,只要授予了该角色和resource就够了
altersession,createcluster,createdatabaselink,createsession,createsynonym,createview,createsequence
2.2resource角色
具有应用开发人员所需要的其他权限,例如建立存储过程,触发器等,该角色隐含了unlimitedtablespace系统权限
2.3dba角色
具有所有系统权限,以及withadminoption选项,默认的dba是sys,system,但是dba角色不具备syssystem的特权(启动和关闭数据库)
3自定义角色
一般dba来建立,若别的用户必须具备createrole的权限,当建立角色时,可以指定验证方式(不验证,数据库验证)
1)建立角色(不验证)createrole角色名notidentified;
2)建立角色(数据库验证)createrole角色名identifiedby密码;
3)角色授权当建立一个角色的时候,该角色没有任何权限,必须对其授予相应的系统权限和对象权限。
3.1给角色授权
角色授权和用户授权没太大区别,注意:
unlimitedtablespace和withgrantoption不能授予角色
例如:
grantcreatesessionto角色名withadminoption
grantcreatesessiontomyrolewithadminoption
grantinsert,update,deleteonscott.empto角色名
3.2角色分配给用户
一般分配角色是有dba来完成的,如果普通用户具备grantanyrole的系统权限。
例如:
grant角色名to用户名【withadminoption】
3.3删除角色
一般dba来执行,如果要普通用户必须具备dropanyrole的系统权限
例如:
droprole角色名
===================================
考虑:
角色被删除,xiaoming这个用户还能登陆数据库么?
3.4显示角色信息
1)显示所有角色select*fromdba_roles;
2)显示所有的系统权限selectprivilege,admin_optionfromrole_sys_privswhererole='角色名';
3)显示所有的用户selectusernamefromdba_users;
4)显示角色具有的对象权限通过查询dba_tab_privs可以查看角色具有的对象权限或列的权限
5)显示用户具有的角色及默认角色
查询dba_role_privs可以显示用户具有的角色及默认角色
selectgranted_role,default_rolefromdba_role_privswheregrantee='用户名';
表的管理
表的命名规则:
必须以字母开头,长度不能超过30字符,不是使用Oracle里面的保留字,只能使用如下字符A--Z,a---z,0--9,$#等
字符类型:
char最大2000字符,固定长度的字符类型(如果确定字段的长度,设置该字符类型,查询速度会快)
varchar2可变长度的字符类型最大4000字符
数字型有效区间10负38次方----10正38次方
number(6,2)6代表的有效数,2代表小数位数
number(5)5位整数
日期类型
date默认格式'dd-mon-yy'
timestamp精确到毫秒
图片
blob二进制图片、声音最大4G
=========================
创建表
createtablestudent(xhnumber(4),xmvarchar2(10),sexchar
(2),birthdaydate,chengjinumber(6,2));
添加一个字段
altertablestudentadd(classidnumber
(2));
修改字段长度
altertablestudentmodify(xmvarchar2(12));
删除一个字段
altertablestudentdropcolumnchengji;
修改表名
renamestudenttostu;
删除表
droptablestudent;
==================
插入数据
insertintostudentvalues('2005','ddd','男','01-6月-95',85);
修改日期格式
altersessionsetnls_date_format='yyyy-mm-dd'
insertintostudentvalues('2005','ddd','男','2005-02-12',85);
insertintostudent(xh,xm,sex)values('','','')
insertintostudentvalues('2005','ddd','男',null,85);
select*fromstudentwherebirthdayisnull;
select*fromstudentwherebirthdayisnotnull;
updatestudentsetchengji=chengji+5wherexh='2005';
deletefromstudent;删除所有记录表的结构还在,写日志,可以恢复,速度慢
droptablestudent;删除表结构
truncatetablestudent;删除所有记录表的结构还在,不写入日志,无法恢复,速度快
oracleselect查询1
1.select*fromemp;2.selectempno,enamefromemp;注意:
*慎用?
因为检索速度慢尤其对于大数量的表
settimingon;显示查询时间?
?
?
自己验证*和字段的时间区别
3.selectdistinctdeptno,jobfromemp;消除重复行4.使用算数表达式?
如何显示每个雇员的年工资selectsal*12,enamefromemp;
5.如何处理null值使用nvl函数来处理
6.如何连接,使用||selectename||'isa'||jobfromemp;
7.起别名使用as或者直接输入别名selectenameas姓名fromemp;selectename"姓名"fromemp;
8.加条件的selectselect*fromempwhereename='SMITH';
8.模糊查询like关键字like有两种通配符%:
0----多个字符;_:
单个字符
like用在where后面
?
如何显示首字符是s的员工姓名和工资selectename,salfromempwhereenamelike'S%';
9.使用in关键字查询单个值查找empno是7369,7521的员工信息
select*fromempwhereempnoin(7369,7521);select*fromempwhereempno=7369orempno=7521;
练习:
1显示工资高于3000的员工?
select*fromempwheresal>3000;
2如何查找1982.2.3以后入职的员工?
select*fromempwhereHIREDATE>to_date('1982/2/3','yyyy/mm/dd');
3如何显示工资在1000到2000的员工情况?
select*fromempwheresal>1000andsal<2000;
4如何显示第三个字符为大写O的所有员工的姓名和工资selectename,salfromempwhereenamelike'__O%';
5如何显示没有上级的员工情况?
select*fromempwhereMGRisnull;select*fromempwherein(selectMGRfromemp);
上次课留下的问题:
select*fromempwhereto_date(HIREDATE,'DD-mm-YY')>to_date('01-01-82','DD-mm-YY');
===========================
oracle查询2
1.使用逻辑操作符号orand
?
查询工资大于400并且岗位为salesman的雇员,同时还要满足他们的姓名第一个字母为大写的Tselect*fromempwheresal>400andjob='SALESMAN'andenamelike'T%';
2.排序使用orderby默认的asc升序降序是desc?
如何按照工资升序显示雇员信息select*fromemporderbysal;?
将雇员信息按照部门号升序而雇员的工资降序排列
select*fromemporderbydeptno,saldesc;?
将销售人员按照部门号升序而雇员的工资降序排列select*fromempwherejob='SLAESMAN'orderbydeptno,saldesc;
3.子查询(嵌套查询)select字段名from表名where字段比较运算符(子查询)
?
查找工资超过2000的雇员的上级员工姓名SELECTENAMEFROMEMPwhereempnoin(SELECTDISTINCTmgrfromempwheresal>=3000);
oracle查询3
1、数据分组max()min()avg()sum()count()聚集函数
?
如何显示所有员工的最高工资和最低工资selectmax(sal),min(sal)fromemp;
?
显示最高工资的员工姓名selectename,salfromempwheresal=max(sal);错误!
!
!
!
!
!
selectename,salfromempwheresal=(selectmax(sal)fromemp);selectename,max(sal)fromempwheresal=(selectmax(sal)fromemp);错误!
!
!
!
!
?
如何显示所有员工的平均工资和工资总和selectavg(sal),sum(sal)fromemp;
?
如何计算总共有多少员工?
selectcount(empno)fromemp;
?
显示工资高于平均工资的员工信息select*fromempwheresal>(selectavg(sal)fromemp);
=================================
2.groupby分组和having子句筛选
作用:
用于对查询结果分组统计having作用:
在分组的基础上按照having后的条件筛选
?
如何显示每个部门的平均工资和最高工资selectavg(sal),max(sal),deptnofromempgroupbydeptno;(注意)selectavg(sal),max(sal)fromempgroupbydeptno;
?
显示每个部门的每种岗位的平均工资和最低工资selectavg(sal),min(sal),deptno,jobfromempgroupbydeptno,job;
?
显示平均工资低于2000的部门号和它的平均工资selectavg(sal),deptnofromempgroupbydeptnohavingavg(sal)<2000;
==================
总结:
1.分组函数只能出现在选择列表、having、orderby子句
2.如果在select语句中同时包含groupbyhavingorderby,那么他们出现的顺序是groupby,having,orderby
3、在选择列中如果有列、表达式、分组函数,那么这些列和表达式,必须有一个出现在groupby子句中
============================================
3.多表查询:
基于两个或者两个以上的表或者视图的查询
?
显示sales部门的位置和员工姓名selectd.loc,e.enamefromempe,deptdwheree.deptno=d.deptno;
?
显示部门号为10的部门名、员工名、工资selectd.dname,e.ename,e.salfromempe,deptdwheree.deptno=d.deptnoande.deptno=10;
?
显示各个员工的姓名、工资及工资级别selecte.ename,e.sal,s.gradefromempe,salgradeswheree.salbetweens.losalands.hisal;
自连接:
在同一张表的连接查询
?
显示ford的上级领导的姓名selecte.ename,f.enamefromempe,empfwheree.mgr=f.empnoande.ename='FORD';select*fromempe,empfwheree.mgr=f.empno
oracle查询(4)
子查询:
嵌套查询
1、单行子查询:
返回一行数据?
显示与SMITH同部门的所有员工
select*fromempwheredeptno=(selectdeptnofromempwhereename='SMITH')
2.多行子查询:
返回多行数据的子查询?
如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号selectename,job,sal,deptnofromempwherejobin(selectdistinctjobfromempwheredeptno=10);selectename,job,sal,deptnofromempwherejob=any(selectdistinctjobfromempwheredeptno=10);
在多行子查询中使用all?
如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号selectename,sal,deptnofromempwheresal>all(selectsalfromempwheredeptno=30);selectename,sal,deptnofromempwheresal>(selectmax(sal)fromempwheredeptno=30);
在多行子查询中使用any关键字?
如何显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号selectename,sal,deptnofromempwheresal>any(selectsalfromempwheredeptno=30);selectename,sal,deptnofromempwheresal>(selectmin(sal)fromempwheredeptno=30);
3.多列子查询:
子查询结果返回多个列?
如何查询与SMITH的部门和岗位完全相同的所有雇员select*fromempwhere(deptno,job)=(selectdeptno,jobfromempwhereename='SMITH');
4.在from字句中使用子查询?
如何显示高于自己部门平均工资的雇员姓名、部门号、工资、平均工资selecte.ename,e.deptno,e.sal,ds.avgsfromempe,(selectdeptno,avg(sal)avgsfromempgroupbydeptno)dswheree.deptno=ds.deptnoande.sal>ds.avgs;
总结:
当在from字句中使用子查询时,该子查询会被作为一个视图来对待,因此也叫做内嵌视图,当在from字句中使用子查询时,必须给子查询制定。
(给表起别名时,不能使用as关键字,给列其别名的时候可以加as)
5.分页查询
分页一共有三种方式:
根据rowid分页,根据分析函数来分,按照rownum来分
使用rowid分页
select*fromTablenamewhererowidin(selectridfrom(selectrownumrn,ridfrom(selectrowidrid,cidfromTablenameorderbyciddesc)whererownum<100)wherern>50orderbyciddesc;
使用分析函数来分
select*from(selectt.*,row_number()over(orderbyciddesc)rkfromTablename)whererk<100andrk>50;
使用rownum
select*from(selectt.*,rownumrnfrom(select*fromTablenameorderbyciddesc)twhererownum<100)wherern>50;
其中Tablename为表名,cid为表的关键字段,取按照cid降序排序后的第51到99条记录。
select*from(selecte.*,rownumrnfrom(select*fromemp)ewhererownum<=10)wherern>=6;
oracle查询5
分页查询几个查询变化
1指定查询列,只需要修改最里层子查询?
只查询雇员的编号和工资
select*from(selecte.*,rownumrnfrom(selectename,salfromemp)ewhererownum<=10)wherern>=6
2排序查询,只需要修改最里层的子查询按工资排序后查询6--10条数据
select*from(selecte.*,rownumrnfrom(selectename,salfromemporderbysal)ewhererownum<=10)wherern>=6
利用查询结果创建表createtabletable_name(id,name,sal,job,deptno)asselectempno,ename,sal,job,deptnofromemp;
合并查询
使用union,unionall,intersect,minus将多个查询结果合并,多用于数据量比较大的数据库
1、union:
取查询结果的并集,并且使用该操作符,会自动去
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 汇总