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的类，调用其中的函数或过程时
升级会员 