Oracle笔记1.docx
《Oracle笔记1.docx》由会员分享，可在线阅读，更多相关《Oracle笔记1.docx（31页珍藏版）》请在冰豆网上搜索。
Oracle笔记1
Dba才能创建user
自动生产三个用户
Sys—change_on_install
System-manager
Scott--tiger
Createuser用户名identifiedby密码
Alter……更改密码
Cascade级联删除
运行中：
sqlplus.exe
Sqlplusw
Conn[ect]scott/m123@servicesassysdba;
Passw
Start/@d:
\a.sql
Editd:
\a.sql
Spoold:
\a.sql
Spooloff
Showuser
Setlinesize
Setpagesize
账户管理
Createprofilelock_accountlimit
Failed_login_attempts&password_lock_time2;
Alteruserscottprofilelock_account;
Grantdb2\connect\resourcetoscott;
安装Oracle然后启动两个服务（listener—JDBC连接、myserverservice）
卸载：
regidit改注册表删除安装目录重启
第二天：
createtablePICK_BATCH
（
MAKEBATCHVARCHAR2（10）notnull,
STATUSNUMBER（38）,
BEGINTIMEDATE,
CLOSETIMEDATE,
CHECKOVERNUMBER（38）,
REMARKVARCHAR2（100）
）
tablespacePICK_DATA
pctfree10
initrans1
maxtrans255
storage
（
initial64K
minextents1
maxextentsunlimited
）;
altertablestudentadd（classIDunmber
（2））;
descstudent;
altertablestudentmodify（classIDunmber
（2））;
insertintostudentvalues（‘’,’’,’’,’11-12月-1997’,1401）;
altersessionsetnls_date_format=’yyyy-mm-dd’;
查询空wherebirthdayisnull;
updatestudentsetsex=’女’wherexh=’1001’;
deletefromstudent;表结构还在，记录没了速度慢可以恢复
savepointaa;
rollbacktoaa;
droptablestudent;删除表的结构和数据
deletedfromstudentwherexh=’1001’;删除一条记录
truncatetablestudent;删除表中的所有记录，表结构还在，不写日志，无法恢复，速度快
settimingon显示查询时间
selectcount（*）fromusers;
insertintousers（userID,username）select*fromusers
selectdistinctjobfromdept;取消重复行
语句不区分大小写。
。
where值区分大小写
selectnvl（comm，0）*13“年奖金”fromdept;判断是否为空
wheredate>’1-1月-1993’
like‘%_’;
in（121,12,1232）;
orderbysaldesc;
子查询
selectename,salfromempwheresal=（selectmax（sal）fromemp）;
groupby的字段必须在select中出现
groupby..having…..orderby
分组函数只能出现在选择列表、having、orderby子句中
多表查询条件不能少于n-1n—表的数目
自连接empworker,empboss
单行子查询中间结果单个一列
多行子查询中间结果多个wherejobin（select………）一列
子查询结果是两列—即表内嵌视图
查询结果是张子表，可以放在from语句中，作为内嵌视图，必须要有别名
列别名可以加as表别名不能加as
分页selecta1.*,rownumrnfrom（select*fromemp）a1;
指定查询列，只需修改最里面的子查询
排序也是修改最里面的子查询
createtablemyemp2（id,ename,sal）asselectempno,ename,salfromempl
快速建表，数据也复制过来
union,unionall,intersect,minus合并查询
创建数据库
dbca工具DatabaseConfigureAssistant
使用JDBC_ODBC桥连接方式只能本地连接
JDBC方式允许远程连接
1.加载驱动Class.ForName（“oracle:
jdbc:
driver:
OracleDriver”）
2.得到连接connectionct=Drivermanager.getConnection（“jdbc:
oracle:
thin:
@127.0.0.1:
1521|myora1”,”scott”,”m123”）
3.接下来和sql一样
to_date（‘1988-12-23’,’YYYY-mm-dd’）函数
insert插入多条记录
insertintokkk（Myid,myname,mydept）selectempno,ename,deptnofromempwheredeptno=10;
updateempset（job,sal,comm）=（selectjob,sal,comm.fromempwhereename=’SMITH’）whereename=’SCOTT’;
事务+锁文件锁互斥锁
commit后，保存点就被删除，没有用了
只读事务settransactionreadonly;
字符函数
lower
upper
length
substr（ename,1,3）第一位起取3个
selectupper（）||lower（）fromemp合并
selectreplace（ename,‘A‘，’我是老鼠’）fromemp;
数学函数
round四舍五入
trunk截取
mod
floor向下取整
ceil向上取整
selectsysdatefromdual;
select*fromempwheresysdate>add_month（hiredate,8）;
selecttrunk（sysdate-hiredate）‘入职天数’，enamefromemp;
selecthiredate,last_day（hiredate）fromemp;
to_char
sys_context
selectsys_context（‘userenv’,’db_name’）fromdual;
selectsys_context（‘userenv’,’language’）fromdual;
selectsys_context（‘userenv’,’session_user’）fromdual;
selectsys_context（‘userenv’,’current_schema’）fromdual;
sys用户拥有sysdba（系统管理员）dba（数据库管理员角色）sysoper（系统操作员）权限只能以sysdba或者sysoper登入
sys方案：
基表、动态视图
system:
dbasysdba
system方案：
次级数据
可以normal登录（dba权限登录）
showparameter;
逻辑备份-工具export导出数据库到磁盘导出表、数据库、方案
恢复—工具import导入磁盘到数据库open状态
在导入和到处的时候，要到Oracle目录的bin目录exp.exe
expuserid=scott/tiger@myora1tables=（emp）file=d:
\e1.dmp
expuserid=system/manager@myora1tables=（scott.emp）file=d:
\e1.dmp导出其他用户的表
expuserid=scott/tiger@myora1tables=（emp）file=d:
\e1.dmprows=n导出表的结构
expuserid=scott/tiger@myora1tables=（emp）file=d:
\e1.dmpdirect=y;直接导出
导出方案：
expuserid=scott/tiger@myora1owner=scottfile=d:
\scott.dmp
system不能导出sys的，其他可以
导出数据库
expuserid=system/manager@myora1full=yinctype=completefile=d:
\e1.dmp
导入
impuserid=scott/tiger@tiger@myoratable=（emp）file=d:
\xx.dmp
数据字典记录静态数据，动态性能视图记录例程启动后的信息
数据字典所有者是sys,只能select系统自动维护
selecttable_namefromuser_tables当前用户所拥有的所有表
all_tables当前用户可以访问的所有表selecttable_namefromall_tables
selecttable_namefromdba_tables;sys角色才能访问
descdba_users;
selectdbanamefromdba_users;
descdba_role_prive;
select*fromdba_role_privewheregranted=’scott’
一个用户有多个角色，一个角色有多个权限,权限分为对象权限和系统权限
select*fromdba_roles;查询角色oracle有25个角色
select*fromsystem.privilege.msporderbynamel;查询系统权限140
selectdistinctprivilegefromdba.tab.privs;查询对象权限16种
一个角色包含的系统权限select*fromdba_sys_privswheregrantee=’DBA/CONNECT’;
select*fromrole_sys_privswhererole=’DBA/CONNECT’;
一个角色包含的对象权限select*fromdba_tab_privswheregrantee=’角色名、CONNECT’
select*fromdba_role_privswheregrantee=’用户名’查看某个用户具有什么样的角色
select*fromdictwherecommentslike‘%grant%’;显示当前用户可以访问的所有数据字典视图
select*fromglobal_name;显示当前数据库的全称
动态性能视图用于记录当前例程的活动信息v.$开头
表空间–逻辑数据库数据存放在数据文件中，表空间由一个或者多个数据文件组成
逻辑结构：
表空间-段-区-块
建立数据表空间
createtablespacesp001datafile‘d:
\test\data01.dbf’size20muniformsize128k
使用数据表空间
createtablemypart（depnonumber（4）,dnamevarchar2（14）,locvarchar2（13））tablespacesp001;
改变表空间状态
altertablespace表空间名offline；
altertablespace表空间名online;
只读表空间altertablespace表空间名readonly;不能insert、delete、update
altertablespace表空间名readwrite;
select*fromall_tableswheretablespace.name=’表空间名’；--显示表空间包括的所有表
selecttablespace_name,table_namefromuser_tableswheretable_name=’emp’;知道表名，查看属于哪个表空间在system表空间
droptablespace‘表空间名’includingcontentsanddatafiles;删除表空间
扩展表空间表空间的大小就是数据文件相加后的大小
（1）增加数据文件altertablespacesp001adddatafile‘d:
\sp002.dbf’size300m;
（2）增加数据文件的大小resize
（3）设置文件的自动增长autoextenfonnext10mmaxsize500m;
移动数据文件
（1）确定数据文件所在的表空间selecttablespace.namefromdba_data_fileswherefile_name=d:
\sp01.dbf;
（2）使表空间脱机altertablespacesp01offline;
（3）使用命令移动数据文件到指定的目标位置hostmoved:
\sp01.dbfc:
\sp01.dbf
（4）altertablespacesp01renamedatafile‘d:
\sp01.dbf’to‘c:
\sp01.dbf’;
（5）altertablespacesp01online;
临时表空间、undo表空间、索引表空间、非标准块的表空间
数据完整性：
约束、触发器、应用程序（过程、函数）
约束：
notnull,unique（唯一，可以为空）,primarykey,foreign（唯一，不能为空）key,check
createtablegoods（goodsidchar（8）primarykey,
goodsnamevarchar（30）,
unitpricenumber（10,2）check（unitprice>0）,
）
altertablegoodsmodifygoodsnamenotnull;
altertablecustemaddconstraintaaunique（cardid）;
altertablecustemaddconstraintbbcheck（addressin（‘东城’，‘西城’））;
altertable表名dropconstraint约束名称；
altertable表名dropprimarykeycascade;
selectconstraint_name,constraint_typefromuser_constraint（数据字典）wheretable_name=’表名’；
表级定义列写完再描述约束
列级定义同时写约束
索引：
单列索引createindex‘索引名‘on表名（列名）；
复合索引基于多列的索引同一张表有多个索引，但列的组合必须不同
createindexaaonemp（ename,job）;createindexaaonemp（job,ename）
建立索引原则：
1.大表建立
2.where经常引用到的列
3.层次不要多于4层
缺点：
1.占用1.2倍的硬盘和内存空间
2.更新数据的同时进行索引更新
unique字段自动加索引
方案：
各种数据对象--表视图触发器存储过程
权限：
系统权限：
针对数据库的权限，140种dba可以查询createtable权限
select*fromsystem_privilege_emporderbyname;
dba授予系统权限:
createuserkingidentifiedbym123;createusertomidentifiedbym123;
grantcreatesession,createtabletokingwithadminoption;
revokecreatesessionfromking;
systemking->tom
king权限收回不影响tom,不会级联回收
对象权限：
访问其他方案对象的权利selectdeletealterinsertexecutereferenceindexupdate
grantselectonemptomonkey（用户）;
grantallonemp（sal）tomonkey（用户）;
withgrantoption只能授予用户，不能是角色
revoke权限会级联回收，区分系统权限
角色是相关权限的命令集合，为了简化权限管理角色的权限包括系统权限和对象权限25种角色
角色：
自定义角色和预定义角色（25种）
预定义角色：
connect\resource\dba
connect角色拥有8个系统权限altersession\createcluster\createdatabaselink\createsession\createtable\createview\createsequence
resource拥有的系统权限：
createcluster\createindextype\createtable\createsequence\createtype\createprocedure\createtrigger
dba具有所有的系统权限及withadminoption默认的dba用户为sys和system,但dba角色不具备sysdba和sysoper的特权（启动和关闭数据库）
createrole角色名notidentified\identifiedby密码
给角色授权，包括系统权限（withadminoption）对象权限（withgrantoption）
对新用户授权角色grant角色名toalongwithadminoption;
删除角色droprole角色名；
along不能登陆了，级联删除
显示所有角色select*fromdba_roles;
显示角色具有的系统权限selectprivilege.admin_optionfromroles_sys_privswhereroel=’角色名’；
显示用户具有的角色及默认角色
selectgranted_role,default_rolefromdba_role_privswheregrantee=’用户名’;
精细访问控制自动追加where子句，返回不同的信息
pl/sql编写过程、函数、触发器，可以使用常量、变量procedurallanguage,JAVA程序中可以直接调用这些函数、触发器和过程
pl/sql可以提高应用程序的运行性能、模块化设计、减少网路传输量、提高安全性，但移植性不好
编写一个存储过程，该过程可以向表中添加记录
1.创建一个简单的表
createtablemytest（namevarchar2（30）,passwdvarchar2（30））;
2.创建过程
createorreplaceproceduresp_pro1is
begin--执行部分
insertintomytestvalues（‘戴磊’,’m1234’）;
end;
/
如何查看错误信息showerror;
如何调用该过程
1.exec过程名（参数值1，参数值2…）;
2..call过程名（参数值1，参数值2…）
块（编程）：
过程（存储过程）、函数、触发器、包
注释：
单行注释--多行注释/*….*/
标识符号：
变量v_sal常量c_rate游标cap_cursor例外e_error
块由三部分组成：
定义部分、执行部分、例外处理部分
declare可选的
begin必须的
exception可选的
end
包逻辑组合过程和函数由包规范和包体组成
createpackagejjis
procedureupdate_sal（）;
functionannual_income（）returnnumber;
end;
createpackagebodyjjis
procedureupdate_sal（）
is
begin
end
functionannual_income（）returnnumberis
begin
….
return
end
end
execsp_package.update_sal（‘scott’,120）;
触发器是隐含的执行的存储过程
pl/sql定义变量：
标量类型、复合类型、参照类型、lob
标量v_enamevarchar2（10）:
=赋值v_nameemp.ename%type
复合类型：
pl/sql记录，类似结构体；；pl/sql表，相当于数组，下标可以是负数
参照变量指用于存放数值指针的变量，分为游标变量和对象变量两种
例外：
预定义例外、自定义例外、非预定义例外
exceptionwhenno_data_foundthendbms_output.put_line（“编号没有”）；
预定义例外：
case_not_found、cursor_already_open、dup_val_on_index、invaild_cursor、invaild_number、no_data_found、too_many_rows、zero_divide、value_error
自定义例外
myexexception;--定义例外
ifsql%notfoundthen
raisemyex
endif;
exception;
whenmyexthen
dbms
升级会员 