Oracle学习笔记.docx

Oracle学习笔记.docx

《Oracle学习笔记.docx》由会员分享，可在线阅读，更多相关《Oracle学习笔记.docx（26页珍藏版）》请在冰豆网上搜索。

Oracle学习笔记

Oracle学习笔记

---破攻

连接命令：

connscott/000@oracleassysdba/sysoper

1.passwscott[dba]

2.文件操作：

a.startD:

\aa.sql或@d:

\aa.sqlb.editd:

\aa.sql

c.spoold:

\aa.sql（spooloff）

3.交互式命令：

a.&select*fromempwherejob=’&job’

4.显示和设置环境变量：

a.showlinesizeb.setlinesize90c.pagesize用法同上

5.用户管理：

a.创建createuserscottidentifiedbym000[dba]

b.改密：

passwscott[dba]/alteruserscottidentifiedby000

c.删除：

dropuserscott（cascade）[dba]

d.授权：

1.grantconnect/resourcetoscott（withadminoption）

2.grantselect/all/index/alteronscott.emptoxiaoming（withgrantoption）

e.收回权限：

revokeselect/allonscott.empfromxiaoming[谁授权谁收回][系统权限回收不是级联，对象权限是级联]

f.grantexecuteondbms_transactiontoscott;//执行包权限

g.帐户锁定1.createprofilexxlimitfailed_login_attempts3password_lock_time2

2.终止口令createprofilexxlimitpassword_life_time10password_grace_time2

3.createprofilepassword_life_time10password_grace_time2password_reuse_time10[10天后可以重用密码]

4.配置：

alteruserusprofilexx删除：

dropprofilexx（cascade）

h.解锁：

alteruserusaccountunlock

i.sysdbaandsysoper

6.表的管理：

a.建表:

createtablestudent（xhnumber（4）,xmvarchar2（20）,

sexchar

（2）,birthdaydate,salnumber（7,2））;（clob,timestamp,blob）

b.加字段：

altertablestudentadd（classidnumber

（2））

c.改字段：

altertablestudentmodify（xmvarchar2（30））

d.该字段类型/名字：

altertablestudentmodify（xmchar（30））

e.删字段：

altertablestudentdropcolumnsal

f.改表名：

renamestudenttostu

g.删表：

droptablestudent

h.加数据：

insertintostudentvalues（…）

i.改日期格式：

altersessionsetnls_date_format=‘yyyy-mm-dd’j.插入部分字段：

insertintostudent（xh,xm,sex）values（…）[空值用null]

k.改字段：

updatestudentsetsex=’女’,birthday=’1989-01-11’wherexh=’114’[空值用isnull]

l.删除数据：

deletefromstudent（wherexh=’114’）

m.删除结构：

droptablestudent

n.删除不可找回：

truncatetablestudent;

o.to_date函数：

insertintoxxvalues（7788,to_date（‘1989-12-12’,’yyyy-mm-dd’））;

p.多行插入：

insertintokkk（id,name,dept）selectempno,ename,deptnofromempwheredeptno=10;

q.多行修改：

updateempsetjob=（selectjobfromempwhereename=’SMITH’）,sal=（selectsalfromempwhereename=’SMITH’）whereename=’SMITH’;

7.表的查询：

a.结构：

descemp列：

selectename,salfromempwhere..

b.去重：

selectdistinctdeptno,jobfromemp

c.显示查询时间settimingon

d.查询记录数：

selectcount（*）fromemp

e..表达式：

selectename,sal*12as“年工资”fromemp

f.nvl（comm,0）:

如果comm不存在就赋值为0

g.连接字符串selectename||‘isa’||jobfromemp

h.逻辑操作符：

1.orderby:

selectsal*12“年薪”fromemporderby“年薪”asc/desc;

2.函数：

selectmax（sal）,min（sal）,avg（sal）fromemp;

eg:

selectename,salfromempwheresal=（selectmax（sal）fromemp）;

3.groupby:

selectavg（sal）,max（sal）,deptno,jobfromemp（orderbydeptno,job）（havingavg（sal）>2000）;

i.多表查询:

1.selecta1.ename,a1.sal,a2.dnamefromempa1,depta2wherea1.deptno=a2.deptno（anda1.deptno=10）;

eg:

selecta1.ename,a1.sal,a2.gradefromempa1,salgradea2（wherea1.salbetweena2.losalanda2.hisal）（orderbydept）;

2.自连接：

selectworker.ename,boss.enamefromempworker,empbosswhereworker.mgr=boss.empno（andworker.ename=’FORD’）;

j.子查询：

1.单行：

select*fromempwheredeptno=（selectdeptnofromempwhereename=’SMITH）’;

2.多行：

select*fromempwherejobin（selectdistinctjobfromempwheredeptno=10）;

3.all:

selectename,sal,deptnofromempwheresal>all（selectsalfromempwheredeptno=30）;

4.多列：

select*fromempwhere（deptno,job）=（selectdeptno,jobfromempwhereename=’SMITH’）;

5.from：

selecta2.ename,a2.sal,a2.deptno,a1.mysalfromempa2,

（selectdeptno,avg（sal）mysalfromempgroupbydeptno）a1

wherea2.deptno=a1.deptnoanda2.sal>a1.mysal;

6.分页：

a.selecta1.*,rownumrnfrom（select*fromemp）a1;

b.select*from（selecta1.*,rownumrnfrom（selectename,salfromemporderbysal）a1whererownum<=10）wherern>6;

7.用查询结果建新表：

createtablemyt（id,name,sal,job）asselectempno,ename,sal,jobfromemp;

k.合并查询：

1.去重2.不去重3.取交集4.取差集：

selectename,sal,jobfromempwheresal>25000

（1.union/2.unionall/3.intersect/4.minus）

selectename,sal,jobfromempwherejob=’MANAGER’;

8.java中操作数据库：

[分页项目]

分页案例

<%

Class.forName（"sun.jdbc.odbc.JdbcOdbcDriver"）;

Connectionct=DriverManager.getConnection（"jdbc:

odbc:

oracle","scott","000"）;

Statementst=ct.createStatement（）;

Strings_pageNow=（String）request.getParameter（"pageNow"）;

intpageNow=1;

if（s_pageNow!

=null）{

pageNow=Integer.parseInt（s_pageNow）;

}

intpageCount=0;

introwCount=0;

intpageSize=4;

ResultSetrs=st.executeQuery（"selectcount（*）fromemp"）;

if（rs.next（））{

rowCount=rs.getInt

（1）;

if（rowCount%pageSize==0）{

pageCount=rowCount/pageSize;

}else{

pageCount=rowCount/pageSize+1;

}

}

rs=st.executeQuery（"select*from（selecta1.*,rownumrnfrom（select*fromemp）a1whererownum<="+pageNow*pageSize+"）wherern>="+（（pageNow-1）*pageSize+1）+""）;

while（rs.next（））{

out.println（"

out.println（"

out.println（"

out.println（"

}

for（inti=1;i<=pageCount;i++）{

out.print（"

myoracle.jsp?

pageNow="+i+">["+i+"]"）;

}

%>

9.事务处理：

a.建回滚点：

savepointaa

b.删除后回滚:

rollbacktoaa[取消部分事务]

c.rollback[取消全部事务]

d.java中：

try{

ct.setAutoCommit（false）;//设置不能自动提交事务

Statementsm=ct.createStatement（）;

sm.executeUpdate（“updateempsetsal=sal+200whereename=’SCOTT’”）;

inti=7/0;//设置异常

sm.executeUpdate（“updateempsetsal=sal-200whereename=’SCOTT’”）;

mit（）;//手动提交

}catch（Exceptione）{

ct.rollback（）;

}

e.只读/读写事务：

settransactionreadonly/write

10.函数：

a.大小写：

selectlower（ename）/upper（ename）,salfromemp;

b.字符长度：

select*fromempwherelength（ename）=5;

c.截取字段：

selectsubstr（ename,1,3）fromemp;

d.综合：

selectupper（substr（ename,1,1））fromemp;//首字母大写

selectlower（substr（ename,2,length（ename）-1）fromemp;

//后面字母小写

e.替换：

selectreplace（ename,’A’，‘老鼠’）fromemp;

f.找字符串位置：

instr（char1,char2,[,n[,m]]）;

g.selectround（sal,1）fromemp;//四舍五入小数保留一位

h.selecttrunc（comm，1）fromemp;//截取到小数点后一位

i.selectmod（10,3）fromdual;//取余数

j.selectfloor（comm）,commfromemp;//向下取整

k.selectceil（comm）,commfromemp;//向上取整

l.系统函数:

selectsys_context

（‘userenv’,’terminal/language/db_name/nls_data_format/

session_user/current_schema/host’）fromdual;

11.日期：

1.select*fromempwheresysdate>

add_months（hiredate,8）;//现在>hiredate时候+8个月

2.selecttrunc（sysdate-hiredate）“入职天数”fromemp；

3.selectlast_day（hiredate）fromemp;//当月最后一天

4.selectto_char（hiredate/sal,’yyyy-mm-ddhh24:

mi:

ss’/’L999999.99’）fromemp;//to_char函数

5.to_char

12.数据库管理：

a.显示初始化参数：

showparameter

b.expuserid=system（/scott）/000@oracletables=（scott.emp（/dept,emp））file=d:

\aa.dmp;//导出表

c.expuserid=system（/scott）/000@oracletables=（scott.emp（/dept,emp））file=d:

\aa.dmprows=n/（directly=y）;//导出表结构/（直接导出无rows）

d.expsystem（scott）/000@oracleowner=（system,scott）/scottfile=d:

\system.dmp/scott.dmp//用system来导Scott的

e.expuserid=system/000@oraclefull=yinctype=completefile=e:

\aa.dmp

//导出数据库

f.导入表：

impuserid=scott/000@oracletables=（emp）file=d:

\aa.dmp

g.导入到其他用户：

impuserid=system/000@oracletables=（emp）file=d:

\xx.dmptouser=scott;

h.导入表结构：

impuserid=scott/000@oracletables=（emp）file=d:

\ss.dmprows=n;

i.导入数据：

impuserid=scott/000@oracletables=（emp）file=d:

\ss.dmpignore=y;

j.导入方案：

impuserid=scott/000@oraclefile=d:

\ss.dmp;

k.导入其他方案：

impuserid=system/000@oraclefile=d:

\ss.dmpfromuser=systemtouser=scott;

l.导入数据库：

impuserid=system/000full=yfile=d:

\aa.dmp

m.数据字典：

1.selecttable_namefromuser_tables;

//返回用户对应方案的所有表

n.selecttable_namefromall_tables;//显示用户可以访问的所有的表

o.selecttable_namefromdba_tables;[dba]//整个数据库所有的表

p.select*fromdba_users/dba_sys_privs/

dba_tab_privs/dba_col_privs/dba_role_privs/dba_roles

wheregrantee/role=’DBA’;//数据库所有用户/系统权限/对象权限/列权限/用户具有的角色/所有角色用户名/角色

q.查询表空间:

selecttablespace_namefromdba_tablespaces

r.查询角色系统权限：

select*fromrole_sys_privs;

s.当前用户可访问的所有数据字典视图：

select*fromdictwherecommentslike‘%grant%’;

t.select*fromglobal_name;

u.1.建立空间：

createtablespacedatadatafile‘d:

\aa.dbf’size30m

uniformsize128k;

2.使用表空间：

createtablexx（）tablespacedata;

3.改变空间状态：

altertablespacexxoffline/online/readonly/write;

4.droptablespacexxincludingcontentsanddatafiles;

//删除空间数据和文件

5.扩展空间：

altertablespacexxadddatafile‘d:

\aa.dbf’size20m;

//增加数据文件

2.altertablespacexx‘d:

\aa.dbf’resize20m;//增加数据文件大小

3.altertablespacexx‘d:

\aa.dbf’autoextendonnext10mmaxsize500m;//设置文件自动增长

6.数据文件：

1.确定数据文件所在空间：

selecttablespace_namefromdba_data_fileswherefile_name=’d:

\aa.dbf’;

2.移动数据文件：

1.hostmoved:

\aa.dbfc:

\aa.dbf;

2.altertablespacexxrenamedatafile‘d:

\aa.dbf’to‘c:

\aa.dbf’

3.显示表空间包含的数据文件：

selectfile_name,bytesfromdba_data_fileswheretablespace_name=’xx’;

13.约束:

1.createtablexx（goodsidchar（8）

primary/foreignkey,--主键/外键

unitpricenumber（5,2）check（unitprice>0）,检查条件

namevarchar2（50）notnull,--不为空

emailvarchar2（50）unique;--唯一

sexchar

（2）default‘男’check（sexin（‘男’,’女’））默认为男customeridnumber（8）references

customer（customerid））numsnumber（4）check（numsbetween1and30））；

2.增加notnull约束：

altertablegoodsmodifygoodsnamenotnull;

增加其他约束：

用addconstraint

altertablecustomeraddconstraintxxunique（cardid）;

3.删除：

altertablexxdropconstraint约束名；

删除主键时：

altertablexxdropprimarykeycascade；

4.显示当前用户约束：

selectconstraint_name,constraint_type,status,

validatedfromuser_constraintswheretable_name=’emp’;

5.显示约束列：

selectcolumn_name,positionfromuser_cons_columnswhereconstraint_name=’约束名’;

6.表级定义：

createtableempe（emp_idnumber,namevar

dept_idnumber,

constraintpkp