oracle快速入门笔记.docx
- 文档编号:29531388
- 上传时间:2023-07-24
- 格式:DOCX
- 页数:30
- 大小:2.93MB
oracle快速入门笔记.docx
《oracle快速入门笔记.docx》由会员分享,可在线阅读,更多相关《oracle快速入门笔记.docx(30页珍藏版)》请在冰豆网上搜索。
oracle快速入门笔记
小型数据库:
Access、Foxbase
中型数据库:
MySql、SqlServer、Informix
大型数据库:
Oracle、DB2、sybase
数据库的选择考虑因素主要有以下3点:
1.负载量有多大,用户有多少
2.成本
3.安全性
Sys超级管理员权限高于system
在命令行中输入:
setORACLE_SID=DAVE(实例名)
Sqlplus
Connsys/adminassysdba(assysdba不能少否则报错);也可使用connsystem/admin
Sqlplus常用命令:
创建用户-授权
和我们普通项目中的权限思想基本一致,相当于oracle数据库已经建好了用户和角色类,我们可以创建新的用户,把角色或权限直接授予用户,我们也可以创建新的角色。
自己不能删除自己的用户。
新创建的用户连登录数据库的权限都没有需要授权。
权限分系统权限(140多个)和对象权限(25个左右)。
系统权限是指用户对数据库的相关权限如:
建库、建表、建索引、建存储过程、建函数、登录数据库等。
对象权限:
用户对其他用户的数据对象(表、视图、存储过程、触发器、表空间、序列)操作的权限。
角色是权限的集合。
角色分:
预定义角色和自定义角色。
SQL>connsystem/admin;
已连接。
SQL>grantresourcetoxiaoming;
授权成功。
SQL>connxiaoming/m123;
已连接。
SQL>createtabletest(useridvarchar2(12));
表已创建。
授权:
Grantselectonemptoxiaoming;(将emp表的查询权限授予小明)
Grantallonemptozhangsan;(将emp表的所有权限增删改查等授予张三)
此时如果小明的表空间中也有emp表如何验证?
select*fromscott.emp;
删除权限:
revokeselectonempfromxiaoming;
对权限的维护:
grantselectonemptoxiaomingwithgrantoption;
withadminoptionVSwithgrantoption
不同点:
-withadminoption只能在赋予systemprivilege的时使用
-withgrantoption只能在赋予objectprivilege的时使用
-撤消带有adminoption的systemprivileges时,连带的权限将保留
例如:
1.DBA给了CREATETABLE系统权限给张三WITHADMINOPTION
2.张三建了若干张表。
3.张三将建表系统权限给李四
4.李四建了若干张表
5.DBA将张三的建表权限收回
结果:
张三‘STABLE依然存在,但不能创建新的TABLE了
李四’STABLE依然存在,他还保留着CREATETABLE系统权限
-撤消带有grantoption的objectprivileges时,连带的权限也将撤消
例如:
1.JEFF给了SELECTobjectprivileges在EMP上WITHADMINOPTION
2.JEFF给了SELECT权限在EMP上TOEMI
3.后来,撤消JEFF的SELECT权限
结果:
EMI的权限也被撤消了
建表
添加一个字段:
altertable表名add(classidnumber(4));
修改多个字段
SQL>altertablestumodify(stunonumber(4),stunamechar
(2));
删除表的某个字段:
SQL>altertablet_studropcolumnclassid;
Tablealtered
修改表的名字:
SQL>renamestutot_stu;
Tablerenamed
删除表:
droptalbet_stu;
insertintot_stuvalues(1,10001,'小明','1999-09-09',12300,15)
ORA-01861:
文字与格式字符串不匹配
SQL>altersessionsetnls_date_format='yyyy-mm-dd';
Sessionaltered
SQL>insertintot_stuvalues(1,10001,'小明','1999-09-09',12300,15);
1rowinserted
SQL>select*fromt_stu;
IDSTUNOSTUNAMEBIRTHDAYSALARYCLASSID
----------------------------------------------------------------
110001小明1999-9-91230015
SQL>
设置保存点:
savepointaa;
恢复至保存点:
rollbacktoaa;
删除表:
truncatetablet_class;删除表的所有数据,不写日志,速度很快,但无法找回删除的记录。
显示查询时间:
settimingon;
24rowsselecte;
Executedin0.438seconds
对表已有数据的复制:
insertintot_class(classid,classname)select*fromt_class;
selectsal*12+nvl(comm,0)*12“年薪”fromemp;
模糊查询:
%表示匹配任意多个字符_表示匹配一个字符
使用列的别名排序:
orderby1
显示每个部门的每种岗位的平均工资和最低工资。
其实就是按照多个字段分组groupbydeptno,job;
多表查询的条件:
不能少于表的个数-1如4张表的关联至少需要3个条件才能避免出现笛卡尔积。
多列子查询:
多列子查询则是指返回多列数据的子查询语句。
当多列子查询返回单行数据时,在where子句中可以使用单行比较符。
而返回多行数据时,在where字句中必须使用多行比较符。
(in,all,any)
select*fromempwhere(deptno,job)=(selectdeptno,jobfromempwhereename=‘Smith’);
使用子查询比较多个列的数据时,既可以使用成对比较,也可以使用非成对比较。
其中,成对比较要求多个列的数据必须同时匹配,而非成对比较则不要求多个列的数据必须同时匹配。
1.成对比较示例
SELECTename,sal,comm,deptnoFROMemp
WHERE(sal,nvl(comm,-1))IN(SELECTsal,nvl(comm,-1)FROMempWHEREdeptno=30);
2.非成对比较示例
SELECTename,sal,comm,deptnoFROMemp
WHEREsalIN(SELECTsalFROMempWHEREdeptno=30)ANDnvl(comm,-1)IN(SELECTnvl(comm,-1)FROMempWHEREdeptno=30);
oracle表取别名时不要加as否则报错
oracle分页:
共有3种方式:
select*from(
selecte.*,rownumrnfrom(select*fromemp)ewhererownum<=最大值)wherern>=最小值
2.如果只查询几列,只需要修改最里层视图即可
select*from(
selecte.*,rownumrnfrom(selectempno,enamefromemp)ewhererownum<=12)wherern>=9
3.如果想在分页中按某些字段排序也只修改最里层视图即可。
select*from(selecte.*,rownumrnfrom(selectempno,ename,salfromemporderbysaldesc)ewhererownum<=12)wherern>=9
用查询结果直接创建表
createtableemp_bak(id,name,sal)asselectempno,ename,salfromemp;
union:
该操作符用于取得两个结果集的幷集,当使用该操作符时会自动去掉结果集中重复行。
过滤掉重复的记录这点我之前不知道。
unionall:
两个结果集直接相加不去除重复行
intersect取交集
minus:
从集合A中排除集合B中的元素。
A可以比B小,不是数学意义上的减法,而是只要是这个元素在集合B中也存在就过滤掉。
事务一旦提交,之前设置的保存点将失效(消失)。
只读事务
如18点时统计本日销售量,之后新增或删除的记录都不在统计之列
1.首先以system/admin账户登录,查看emp_bak表数据并设置开启只读事务。
2.以proxy/proxy登录系统插入2条数据
proxy看到17条数据
4.proxy看到的还是14条数据
函数:
substr(列名,从第几个字符开始,截取几个字符);
将每个人名首字母大写,其余字母小写:
replace(列名,要替换的字符,替换后的字符)函数:
round(col,n)四舍五入到小数点后几位,如果不写n默认为整数
trunc(sal,n):
截取到小数点后第几位,默认为整数,砍掉的位置0;
floor(n):
返回<=n的最大整数
ceil(n):
返回>=n的最小整数
mod(m,n)取模函数10%3=1
add_months函数:
时间向前,或向后几个月后的时间
入职30年以上的员工:
selectfloor(sysdate-hiredate)"入职天数",trunc(sysdate-hiredate)fromemp;
last_day(日期)该日期月份的最后一天
转换函数:
to_char(hiredate,'yyyy/MM/ddhh24:
mi:
ss')
to_char(sal,'$99,999.99')显示工资时前面带$:
$2,975.00
to_char(sal,'L99,999.99')本地货币:
¥1,600.00
to_char(sal,'C99,999.99')国际货币:
CNY2,850.00
查询1980年入职的员工
selectempno,ename,hiredatefromempwhereto_char(hiredate,'yyyy')='1980';
*:
表连接条件为多个时可以用on(...and...and...)
如:
每个部门薪水最高的员工
以前:
selectename,e.sal,e.deptno,t.msfromempeinnerjoin(selectdeptno,max(sal)msfromempgroupbydeptno)tone.deptno=t.deptnowheree.sal=t.ms;
selectename,e.sal,e.deptnofromempeinnerjoin(selectdeptno,max(sal)msfromempgroupbydeptno)ton(e.deptno=t.deptnoande.sal=t.ms);
**查询出每个部门中薪水高于其部门平均薪水的职员信息。
SQL>selectename,e.sal,t.avg_salfromempeinnerjoin(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)ton(e.deptno=t.deptnoande.sal>t.avg_sal);
**显示每个部门的名称和平均薪水等级
SQL>selectt.deptno,d.dname,s.gradefrom(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)t
2innerjoindeptdont.deptno=d.deptno
3innerjoinsalgradeson(t.avg_salbetweens.losalands.hisal)
4orderby1asc;
**查询工资>1200的员工,按部门编号分组后平均薪水>1500的按倒序排列(不是最初想象的那么简单)。
SQL>selectename,sal,e.deptnofromempeinnerjoin(selectdeptnofromempwheresal>1200groupbydeptnohavingavg(sal)>1500)tone.deptno=t.deptno
wheree.sal>1200orderbye.deptnoasc,salasc;
**显示每个员工的姓名,工作,和经理人姓名
selecte1.ename,e1.job,e2.enamemgrfromempe1leftouterjoinempe2one1.mgr=e2.empnoorderbye1.empnoasc;
**求部门平均的薪水等级
**不用组函数求最高薪水:
selectdistinctsalfromempwheresalnotin(selectdistincte1.salfromempe1joinempe2on(e1.sal 我的: selectrownum,e.*from(select*fromemporderbysaldesc)ewhererownum=1; **按薪水排序,查询第4-9高的员工信息 select*from(selectrownumrn,e.*from(select*fromemporderbysaldesc)ewhererownum<=9)twheret.rn>=4; ****求平均薪水最高的部门编号 selectt.*from(selectdeptno,avg(sal)avg_salfromempgroupbydeptnoorderby2desc)twhererownum=1;(每个部门平均薪水,排序后取第一个) 下面的写法很不常用: 部门平均薪水=最高的平均薪水利用了函数的嵌套 selectdeptno,avg(sal)asmaxfromempgroupbydeptnohavingavg(sal)=(selectmax(avg(sal))fromempgroupbydeptno); **平均薪水最高的部门名称 selectd.*fromdeptdinnerjoin( selectdeptno,avg(sal)asmaxfromempgroupbydeptnohavingavg(sal)=(selectmax(avg(sal))fromempgroupbydeptno) )tond.deptno=t.deptno; *****平均薪水的等级最低的部门名称, select*fromdeptwheredeptnoin( selectt.deptnofrom( selectdeptno,avg(sal)avgfromempgroupbydeptno )tinnerjoinsalgradegon(t.avgbetweeng.losalandg.hisal) whereg.grade= (selectmin(g.grade)from(selectdeptno,avg(sal)avgfromempgroupbydeptno)tjoinsalgradegon(t.avgbetweeng.losalandg.hisal)) ); 如果还要显示平均薪水、平均薪水等级 【我的】selectd.dname,t.avg,t.gradefromdeptdinnerjoin( selectt.deptno,avg,gradefrom( selectdeptno,avg(sal)avgfromempgroupbydeptno )tinnerjoinsalgradegon(t.avgbetweeng.losalandg.hisal) wheregrade= (selectmin(g.grade)from(selectdeptno,avg(sal)avgfromempgroupbydeptno)tjoinsalgradegon(t.avgbetweeng.losalandg.hisal)) )tond.deptno=t.deptno; 函数: sys_context selectsys_context('userenv','db_name')"数据库名",sys_context('userenv','terminal')"客户端机器名称", sys_context('userenv','language')"语言", sys_context('userenv','session_user')"数据库用户名", sys_context('userenv','current_schema')"方案名" fromdual; 数据库管理员的职责: 从下图可以看出: system不具有sysoper的权限 通过对比看出: sysoper比sysdba不具备的权限有: 创建数据库、删除数据库,改变字符集、恢复数据库等重要权限。 showparameter;显示参数 oracle导出表、方案、数据库 前提: 打开命令行,切换到进入oracle_home\bin目录下(直接在sqlplush命令行中无效) D: \oracle\product\10.2.0\db_1\BIN 1.导出某些表: ***示例: expuserid=proxy/proxy@cslgsfile=d: \db_bak.dmptables=(emp,dept,salgrade) rows=ndirect=y[可选] 补充: 只导出表结构不导数据只需后面加rows=n,当数据量很大时可以增加direct=y直接导出方式来加快速度。 2.导出用户所有的表|导出方案 ***示例: exp[userid=]proxy/proxy@cslgs file=d: \expAll.dmp owner=proxy(以proxy身份导出该方案的所有表) 【以system身份导出所有表】: ***示例: expuserid=system/admin@cslgs file=d: \expSys.dmp owner=(system,proxy) 3.导出整个数据库 要求该用户必须具备dba或exp_full_database的权限 ***示例: expuserid=system/admin@cslgs full=y[关键是这句话] file=d: \expAllDb.dmp oracle入表、方案、数据库 1.导入某些表 ***示例: impuserid=proxy/proxy@cslgsfile=d: \db_bak.dmptables=(emp,dept,salgrade) 2.导入方案 ***示例: 【导入自己的方案】 impuserid=proxy/proxy@cslgsfile=d: \fangan.dmp fromuser=proxytouser=proxy或者full=y(写一个即可) 3.导入数据库 ***示例: impuserid=proxy/proxy[实例名不用写oracle会自动创建] full=yfile=d: \db.dmp 从数据字典中查询所有的账号,密码: SQL>selectusername,passwordfromdba_users; 查看proxy具有的角色: select*fromdba_role_privswheregrantee='PROXY'; 创建表空间: createtablespacelobom_tbldatafile'D: \oracle\product\10.2.0\db_1\database\data01.dbf'size100muniformsize128k[指定区的大小]autoextendon; 创建完成后就可以使用表空间了,在其上创建表 createtabletest_tbl_space(idint,namevarchar2(10))tablespaceds_weisy; 改为只读后无法插入数据,脱机后无法查询,如何消除只读呢? altertablespace空间名readwrite 如果输入的empno不存在呢? 函数: 输入雇员姓名返回雇员年薪 在pl/sql中如何调用? 在java程序中如何调用? 语法如下: createorreplacefunctionfunction_name(argu1[mode1]datatype1,argu2[mode2]datatype2,........) returndatatypeis begin end; 执行varv1varchar2(100)exec: v1: =function_na 包体: 类似于java的类,调用其中的函数或过程时
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 快速 入门 笔记
![提示](https://static.bdocx.com/images/bang_tan.gif)