racle学习笔记总计Word下载.docx

1、2.password 名3.drop user 名 （如果用户有表，则加参数cascade）培训第二天（下午）（自己动手遇到无法登陆oracle）（gaotuan：gaotuan、system：G62980778cxz）一，用户授权（对象权限（select insert update delete all create index），系统权限）1.grant connect to 名（登录）2.grant resource to 名（创建数据库对象）3.sqlplus /as sysdba（sys登陆）4.alter user scott account unlock;（解锁）二，角色conn

2、ect ，dba对象权限：with grant option系统权限：with admin option收回父权限时下边的用户也没有相应得权限三，表空间一，表空间作用： 1.控制数据库占用的磁盘空间。 2.可以将不同的数据类型放在不同的位置（提高io，有利于备份与恢复）。二，建立表空间： 1. Create tablespace bkjm001 datafile C:filenam0e.bdf size 1M ; 2.（使用表空间） create table testtablespace （age int ） tablespace ;三，改变表空间状态： 1. （表空间脱机） alter ta

3、blespace 名 offline； 2. （表空间联机） alter tablespace online； 3. （只读表空间） alter tablespace read only;四，删除表空间： 1.drop tablespace including contents and datafiles;五，拓展表空间： 第三天：（上午）（密码不能以数字开头）注： 1.select 不区分大小写 from user where name=区分大小写 2.在计算时如果一个值为null，则运算结果为空 nvl（列名，0） （为null则用0替换）一，常用查询语句1.查询一个用户所拥有的表sele

4、ct table_name from user_tables;2.终止口令：create profile 名 limit password_limit_time（密码使用的时间） 10 password_grace_time（宽限时间） 2 password_reuse_time（10天内不让使用原密码） 10;alter user 名 profile 名；3.drop profile 名cascade4.set linesize 10（设置显示行的宽度，默认是80个字符）5.set pagesize 5（设置显示行的数目，默认是14个字符）;6. set timing on;（显示耗时）7.

5、查询数据库名 select * from global_name;二，创建表（规范）1.表名，列名必须以字母开头，不能超过30个字符，不能使用关键字,保留字，使用 A-Z，a-z ,0-9,$，#等2、数据类型：a，number（10的 38次方）, number（5,2）, number（5）b，char（10） 定长（最大2000字符）（查询速度快）c，varchar2（20） 变长（最大4000字符）（节省空间）d，clob （字符型大对象 最大4G）e，时间：date（年月日时分秒） timestamp（date拓展）f，blob（存放图片、声音 4G）3.表修改a，alter tab

6、le 表名 add（classid number（2）;b, alter table 表名 modify（lm char（20）;c, alter table 表名 drop column lm;d, rename 名1 to 名2；e, drop table 表名;f,插入数据 insert into bm values（）;H,oracle中默认日期格式：dd-mm月-yy 修改日期格式：alter session set nls_data_format=yyyy-mm-dd to_date（1988-12-22,）4.删除数据恢复（在commit提交之前可以使用保存点）（commit提交

7、之后 事务结束，删除保存点， 释放锁）savepoint ss;delete *from 表（delect删除时写日志）rollback to ss；（rollback 所有的都撤销）（truncate table student ）（删除表中的记录，表结构还在，不写日志，无法找回，但是速度快）5.插入数据（测试）insert into biao1 （lm1,lm2） select * from biao1（下午）注意： 1.from 中使用子查询，则子查询被作为一个视图对待（内嵌视图） 2.from 子句使用查询时必须有列名 3.表取别名 不用as ， 列时可以使用as一，经典查询1. se

8、lect a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;2. select * from emp where sal200 and ename=（数据库执行时从右向左执行，所以应先将一次能筛选多的条件放在最右边）3.单行子查询，4.多行子查询 （返回单列多行）（in） （返回多列单行） select * from emp where（deptno,job）=（select deptno,job from emp）; 或者用 select * from emp

9、 a，（select deptno,job from emp） as b where 例子：select * from emp a,（select avg（sal） avgg ,deptno from emp group by deptno） b where a.deptno=b.deptno and b.avgga.sal;5. update biaoming set （lie1,lie2,lie3）=（select l1, l2, l3 from biao where ） where二，分页显示1.（select * from emp）;2.显示 rownum oracle 分配的3.se

10、lect a.* ， rownum rn from （select * from emp） a;4. 例子：（若指定查询，则只需修改最里面的视图）select * from （select a.* ， rownum rn from （select * from emp） a where rownum6;三，根据查询结果创建新表1.create table mytable （id ,ename,sal） as select empno,ename,sal from emp;2.insert into test1（name,job） select ename,job from emp;四，查询结果

11、的合并（速度快）1. union（除去一样的行），union all（并），intersect（交），minus（差）五，创建数据库1.配置工具database configura assistance dbca【数据库配置助手】六，java链接oracle数据库package com.test1;import java.sql.*;public class connOracle /* jdbc-odbc */ public static void main（String args） / TODO Auto-generated method stub try Class.forName（sun

12、.jdbc.odbc.JdbcOdbcDriver）; Connection ct=DriverManager.getConnection（jdbc:odbc:GToracle,gaotuan Statement sm=ct.createStatement（）; ResultSet rs=sm.executeQuery（select * from test while（rs.next（） System.out.println（姓名:+ rs.getString（1）; rs.close（）; sm.close（）; ct.close（）; catch （Exception e） / TODO:

13、 handle exception e.printStackTrace（）;总结：为了保存事务的正确性:1.可以建立保存点（但是不能提交）2.建立一个新的用户,并设置为只读事务。提高速度：1. 将索引单独放在一个表空间。2. 1.将索引单独放在一个表空间。1.将索引单独放在一个表空间。1.sql 函数dual表可以用来测试一。字符函数：1.lower（char）（字符串转化为小写）2.upper（char）（字符串转化为大写）3.length（char）（返回字符串的长度）4.substr（char,m,n）（去字符串的子串）（从第m开始取n个字符）select upper（substr（en

14、ame,1,1）|lower（substr（ename,2,length（ename）-1） from emp;5. replace（ename,string1,string2）（ename要替换的字段，将String1 替换为string2）（区分大小写）6.instr（）（取字串在字符串中的位置）二，数学函数1.round（ename,n）（四舍五入）2.trunc（ename,1）（截取）3.mod（m,n）4.floor（m）（不大于的最大整数）5.ceil（m）6.abs（n）绝对值7.log（m，n）对数值8.power（m，n）m的n次幂三，日期函数1.默认日期：dd-mm-yy

15、 “12-7月-1988”2.sysdate 返回系统时间3.add_month（d,n）在d年加上n个月；4.last_day（y） 返回指定日期所在月份的最后一天select trunc（sysdate-hiredate） “入职天数” from emp；四，转化函数（oracle隐含转换）：1.to_char（hiredate,yyyy-mm-dd hh24:mi:ss2. to_char（sal,L99999.99）显示本地货币单位3.to_date（） to_date（五，其他函数：1.sys_context（userenvdb_name terminal,lanuage,db_na

16、me,nls-date_format,session_user,current_scheam,host例子： select sys_context（language） from dual;2.sys与system的区别一，sys与system的区别1.sys用户必须以 as sysdba或as sysoper形式登陆，不能以normal方式登陆2.system 如果以正常登陆，就是一个普通的dba， 如果以as sysdba登陆，就是作为sys用户登陆。3.4.3.备份与恢复（17讲）导入导出必须在安装oracle的bin目录下 运行exp使用一，逻辑备份恢复与物理备份恢复1.逻辑备份与恢复：

17、只能在open下进行2.物理备份与恢复：可以在open下进行，也可以在DB关闭情况下进行。二，导出1.导出表，导出方案，导出数据库；2.导出使用 exp命令完成 常用参数：userid,tables,owner,full,inctype,rows,file3. 导出表：exp userid=scott/G110oral tables=（emp） file=D:c.dmp; （导出表结构） exp userid=scott/G110oral tables=（emp） file=D:c.dmp rows=n （直接方式导出表结构） exp userid=scott/G110oral tables=

18、（emp） file=D:c.dmp direct=y 4. 导出方案： （导出他人方案） exp userid=scott/G110oral owner=（system,scott） file=D: （导出自己方案） exp userid=scott/G110oral owner=scott file=D:5. 导出数据库： exp userid=scott/G110oral full=y inctype=complete file=D:三，导入：1.导入表：（导入表）imp userid=scott/G110orcl tables=emp file=D:a.dmp（导入表结构）imp us

19、erid=scott/G110orcl tables=emp file=D:a.dmp rows=n;（导入数据）imp userid=scott/G110orcl tables=emp file=D:a.dmp ignore=y;2.导入方案：3.导入数据库： imp userid=scott/G110oracle file=D:a.dmp full=y;4.数据字典（18讲）一，1.数据字典： 基表（静态数据），动态视图（动态的数据）2.数据字典视图：user_xxx, all_xxx, dba_xxx user_tables, all_tables, dba_tables （dba权限）

20、二，用户名，权限，角色1.select username , passwordfrom dba_users;（查看数据库的用户）2.desc dba_role_privs; select * from dba_role_privs where GRANTEE=gaotuan3.（查询oracle所有的所有角色）select * from dba_roles;4.（查询oracle所有的所有系统权限）select * from system_privilege_map;5.（查询oracle所有的所有对象权限）select distinct privilege from dba_tab_priv

21、s;6.（查询一个角色所有的权限：）（可以在工具视图中查询）select * from role_sys_privs where role=CONNECTselect * from dba_role_privs where grantee=GAOTUAN7.表空间一，移动数据文件： 1.确定数据文件所在的表空间： select tablespace_name from dba_data_files where file_name=D:sp001.dbf 2.使表空间脱机： alter tablespace sp001 offline; 3.使用命令移动数据文件到指定的目标位置： host mo

22、ve D:sp001.dbf C:sp001.dbf 4.执行alter tablespace 命令（对数据库进行逻辑修改）： alter tablespace sp001 rename datafile sp001dbf to sp001.dbfm 5. 使表空间联机： alter tablespace sp001 online;二，显示表空间信息： select tablespace_name from dba_tablespaces;三，显示表空间所包含的数据文件： select file_name,bytes from dba_data_files where tablespace_n

23、ame=SYSTEM约束1.显示约束信息： selectconstraint_name,constraint_type,status,validated from user_constraints where table_name=表名2.显示约束信息： select column_name,position from user_cons_columns where constraint_name=索引1.显示表的所有索引信息： selectindex_name,index_type from user_indexs where table_name=2.显示索引所对应的列的信息： selec

24、ttable_name,column_name from user_ind_columns where index_name=权限,角色 系统权限：不是级联回收（with admin option，能用于角色） 对象权限：级联回收（with grant option，不能用于角色）1.查询系统权限： select * from system_privilege_map order by name;2.创建自定义角色： create role 角色名 not identified; create role 角色名 identified by 密码（将来更改时提供密码）;3.角色赋权限： gran

25、t to 角色名4.删除角色： drop role 角色名二，1.显示所有角色 select * from dba_roles;2.显示角色所具有的系统权限 select privilege,admin_option from role_sys_privs where role=角色名3.显示角色所具有的对象权限 select privilege,admin_option from dba_tab_privs where role=4.显示用户具有的角色，及默认角色 select granted_role,default_role from dba_role_privs where grant

26、ee=set serveroutput on;一，变量：1.标量%type（返回数据一样的类型）%rowtype（pl/sql提供，返回一个记录类型，其数据类型和数据库的表的数据结构一致）declarerec ac01%rowtype;beginselect * into rec from ac01 where aac001=41022300024654dbms_output.put_line（rec.aac003）;end;2.复合变量（composite） pl/sql记录（高级语言的结构体） declare type emp_record_type is record（ name emp.ename%type, salary emp.sal%type）; emp_record emp_record_type; begin select ename,sal into emp_record from emp; set serveroutput on; dbms_output.put_line（emp_record.name）; end; pl/sql表（高级语言的数组） type ename_table_type is tab