韩顺平 oracle视频教程上课笔记.docx
《韩顺平 oracle视频教程上课笔记.docx》由会员分享，可在线阅读，更多相关《韩顺平 oracle视频教程上课笔记.docx（64页珍藏版）》请在冰豆网上搜索。
韩顺平oracle视频教程上课笔记
一、oracle常用sqlplus命令
（1）conn[ect]用户名/密码@网络[assysdba/sysoper]
（2）一般情况下使用普通用户登录，除非需要更高权限时，在切换高级用户；
（3）disc[onnect]断开连接，又不退出当前窗口；
（4）quit/exit完全退出；
（5）showuser显示当前用户；
（6）管理员修改用户密码：
alteruserxxxxidentifiedbyyyyy;
（7）Passw[ord]
pass用户名；
给自己修改密码，不需要带用户名;给别的用户修改，需要带用户名；
创建临时表空间
createtemporarytablespaceuser_temp
tempfile'd:
\user_temp.dbf'
size50m
autoextendon
next50mmaxsize1024m
extentmanagementlocal;
创建表空间
createtablespaceuser_ts
datafile'd:
\user_ts.dbf'
size50m
autoextendon
next50mmaxsize1024m
extentmanagementlocal;
创建用户
createusertest38identifiedbytest38
defaulttablespaceuser_ts
temporarytablespaceuser_temp
quota5monuser_ts;
给用户赋予权力
grantdbatotest38;
给scott用户解锁
alteruserscottaccountunlock;
用system用户给scott修改密码
Alteruserscottidentifiedbytiger;
不常用的命令：
linesize一行能显示多少个字符就换行了默认就只有80字符，所以会出现一个记录一行没有显示完全就换行了。
Setlinesize120
pagesize显示多少个记录就从新开始
如图：
前后差别
Setpagesize100
二、oracle用户管理
1.创建用户（必须是具有DBA权限的才行）
createuser名字identifiedby密码（密码不能以数字开始）
defaulttablespacexxxx;
temporarytablespacexxx2;
quotaxmonxxxx;
如果出现这样的情况，直接XX或者google一下。
2.给用户分配权限
grantcreatesessionto用户名；
3.管理的用户的机制
4.综合案例
方案（schema）（当创建一个用户的时候，只要这个用户创建了任何的数据对象（如表，索引，触发器等），那么DBMS就会创建一个与该用户名一样的一个方案。
）
赋权
grantselect/update/insert/allonempto用户名；
ps:
如果想看一个用户有哪些数据对象，可以通过pl/sqldevelopment查看。
例子：
小红查询scott的emp表
select*fromscott.emp;
表空间
参考：
Oracle表空间之基本概念
ORACLE数据库被划分成称作为表空间的逻辑区域——形成ORACLE数据库的逻辑结构。
一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。
表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。
每个ORACLE数据库均有SYSTEM表空间,这是数据库创建时自动创建的。
SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息（关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表）。
一个小型应用的ORACLE数据库通常仅包括SYSTEM表空间,然而一个稍大型应用的ORACLE数据库采用多个表空间会对数据库的使用带来更大的方便。
一个用户可以使用一个或多个表空间，一个表空间也可以供多个用户使用。
用户和表空间没有隶属关系
个人阶段总结
数据库实例、表空间（逻辑结构）、用户、方案、数据对象（表，过程，包等）的关系
一个实例下可以有多个表空间。
默认是有一个system表空间。
一个用户可以使用一个或多个表空间，一个表空间也可以供多个用户使用。
用户和表空间没有隶属关系。
数据库建立一个用户，就建立了有且只有一个与该用户名字一样的方案来管理该用户建立的各种数据对象。
如有两个表空间，一个是默认的system表空间，一个是新建的user_tasp表空间。
用户（wang）在system建立一个表t1，在user_tasp建立一个表t2，但是只有一个方案（wang）来管理这两个表，虽然表分布在不同的表空间上。
也说明方案和表空间没有隶属关系，就像用户和表空间没有隶属关系一样。
数据对象的操作一般是通过方案来实现操作的。
如：
Select*fromscott.emp这是完整的写法。
scott是方案名
--个人实验
--建立临时表空间
createtemporarytablespaceuser_temp
tempfile'f:
\data\user_temp.dbf'
size50m
autoextendon
next10mmaxsize100m
extentmanagementlocal
--建立表空间
createtablespaceuser_tasp
datafile'f:
\data\user_tasp.dbf'
size50m
autoextendon
next10mmaxsize100m
extentmanagementlocal
--创建用户
createuserwangidentifiedbywangdefaulttablespacesystem;
grantconnect,resourcetowang;
alteruserwangquota10monuser_tasp;//在表空间上给用户分配空间就能让用户在该表空间建立数据对象。
注意不要指定分配空间在临时表空间上，不然会报错。
--在默认表空间上建表
createtablet_test1（idnumber,namevarchar2（20））【tablespacesystem】;
--在user_tasp建表
createtablet_test2（idnumber,namevarchar2（20））tablespaceuser_tasp;
Schema（方案）
当一个用户被创建之后，只要它创建任何一个数据对象，那DBMS就会创建一个和它名字一样的方案与该用户对应。
Ps:
如果想看某个用户有什么数据对象，用pl/sqldevelopment软件。
Schema的实际应用：
xioanghong访问scott的emp表
1.连接scott
connsoctt/tiger
2.给xiaohong赋权限
grantselect/update/delete/allonemptoxiaohong
3.
select*fromscott.emp;
参考：
1、withadminoption用于系统权限授权，withgrantoption用于对象授权。
2、给一个用户授予系统权限带上withadminoption时，此用户可把此系统权限授予其他用户或角色，但收回这个用户的系统权限时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效，如授予A系统权限createsessionwithadminoption,然后A又把createsession权限授予B,但管理员收回A的createsession权限时，B依然拥有createsession的权限，但管理员可以显式收回Bcreatesession的权限，即直接revokecreatesessionfromB.
而withgrantoption用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，不同的是但管理员收回用withgrantoption授权的用户对象权限时，权限会因传播而失效，如：
grantselecton 表名 toAwithgrantoption;，A用户把此权限授予B，但管理员收回A的权限时，B的权限也会失效，但管理员不可以直接收回B的SELECTONTABLE权限。
相同点：
-两个都可以既可以赋予user权限时使用，也可以在赋予role时用
不同点：
-withadminoption只能在赋予systemprivilege的时使用
-withgrantoption只能在赋予objectprivilege的时使用
-撤消带有withadminoption的systemprivileges时，连带的权限将保留
撤消带有withgrantoption的objectprivileges时，连带的权限将不被保留
用户口令管理
使用profile进行用户口令管理
一个用户连续3次密码输入错误，那该账户锁定
1.创建profile文件
createprofile文件名limitfailed_login_attempts次数password_lock_time天数;
2.把该文件（规则）分配给某个用户
alteruser用户名profileprofile文件名;
3.终止口令
一个账号的密码最多能用几天的问题。
Pl:
一个密码的使用时间为10天，宽限时间是2天
createprofile文件名limitpassword_life_time10password_grace_time2;
4.删除profile
dropprofile文件名；
windows下
lsnrctlstart
oradim–startup–sidorcl;
unix/linux环境下
（略）
Oracle的登陆认证
Compmgmt.msc进入计算机管理
找回管理员的密码
1.搜索PWD数据库实例名.ora
2.删除该文件，最好备份一份。
3.生成新的密码文件，在dos下输入命令。
orapwdfile=PWD数据库实例名.ora的具体路径password=新密码entries=允许多少个人进入。
Ps:
orapwdfile=D:
\oracle\product\10.2.0\db_1\database\PWDorcl.orapassword=welcomeentries10
file文件路径password新密码entries登陆sys最多的用户。
4.重新启动数据库实例。
Oracle数据类型
Char存放字符串，最大2000字符，定长
Pl:
namechar（32）,只能存放32字符，多了，报错；少了，空格填满。
ps:
dump（）显示一个字段具体的情况
varchar2变长字符，最大可存放4000字符。
namevarchar（16）name‘abc’实际上只占3字符，其余的13个就回收
第九课
nchar
定长，编码方式unicode，最大2000字符
Ps:
char类型的，一种中文字符站两个字符，而nchar类型，一个中文字符只占一个字符
number
1.可以存放小数，也可以是小数。
2.number（p,s）
p为有效位，s为小数位。
范围：
P[1,38],s[-84,127]。
整数部分的个数为：
p-s
PS：
如果单单就是number，后面没有任何数字，该数是多少就是多少。
Pl:
123.456number结果就是：
123.456
原则：
实际开发过程，如果number指定了小数位，那明确指定，如果没有指定，就直接用number。
date日期类型
默认的是dd-mm-yyyy
第十课
Ps:
数据类型是number插入的是字符串型的‘12345’，oracle会自动把这样的字符类型转化为数字类型。
但是如果是‘asd123’就不行了。
2.插入空置的情况：
插入null和’’情况是一样的。
‘’（中间有一个空格）并不是空。
即‘’=null
3.字符和日期类型应该用’’包含起来。
第十一课
Ps:
oracle的sql不区分大小写，但是内容区分大小写；
Ps:
给字段改名可以用as,也可以不用；
nvl（）处理null的字段。
selectename,sal*12+nvl（comm,0）asall_salfromemp;
oracle中的字符连接符号：
||
设置保存点并回滚
savepointaa;
rollbacktoaa;
第十二课
where语句的用法
like操作符
%任意零个或者多个字符；_任意一个字符；
条件为空与否的使用isnull或者isnotnull处理；
Orderby排序
默认是asc,desc是降序。
第十三课
组函数
max（）,min（）,sum（）,count（）,avg（）;
ps:
括号里面可以是字段也可以是表达式
avg（sal）不会吧sal为空的记录统计进来。
它只计算有值记录的平均值。
求所有人的平均值，可以这样：
selectsum（sal）/count（*）fromemp;
groupby查询的结果进行分组；
having限制分组显示结果。
第十四课
多表查询
1.emp,salgrade两张表的连接。
selectename,sal,gradefromempjoinsalgradeonsalbetweenlosalandhisal;
自连接
2.显示FORD的上级
selectename,salfromempwhereempno=（selectmgrfromempwhereename='FORD'）;
3.显示所有员工的名字，及其上级的名字；
selectt1.enameasworker,t2.enameasbossfromempt1leftjoinempt2ont1.mgr=t2.empno;
第十五课
子查询
显示与SMITH同一个部分的员工
selectenamefromempwheredeptno=
（selectdeptnofromempwhereename='SMITH'）;（结果包含了SMITH,如果不想包含SMITH,在加上andename<>’SMITH’）;
子查询中使用all
Pl：
显示工资比部分30的所有员工的工资还要高的员工姓名，工资，部门号
selectename,sal,deptnofromempwheresal>
all（selectsalfromempwheredeptno=’30’）;
同样的写法：
selectename,sal,deptnofromempwheresal>
（selectmax（sal）fromempwheredeptno=’30’）;
子查询中使用any
Pl:
第十六课
在from中使用子查询
显示各部分中高于本部分平均工资的员工信息。
selectemp.ename,sal,t.myavg,t.deptnofromempjoin
（selectavg（sal）myavg,deptnofromempgroupbydeptno）t
onemp.deptno=t.deptnowheresal>t.myavg;
各个部门工资最高人的详细信息。
selectemp.*,t.maxsalfromempjoin
（selectmax（sal）maxsal,deptnofromempgroupbydeptno）t
onemp.deptno=t.deptnowhereemp.sal=t.maxsal;
显示每个部门的信息和人员数
1.显示各个部分的人数
selectcount（*）pernum,deptnofromempgroupbydeptno；
2.与表dept进行连接
selectdept.*,t.pernumfromdeptleft
join（selectcount（*）pernum,deptnofromempgroupbydeptno）tondept.deptno=t.deptno;
1.表本身的复制
createtablemytestasselect*fromemp;
ps:
把emp表的结构和记录都复制到这个mytest表中
2.自我复制进行插入操作：
insertintomytestselect*frommytest;
Oracle分页
selectename,sal
from（selectename,sal,rownumr
from（select*fromemporderbysaldesc））
wherer>5andr<10;
第十七课
1.合并查询union（求并集）
2.unionall（并不去掉重复的部分，单纯的两个集合相加）
3.intersect（求交集）
4.minus求差集，即存在在第一个集合而不存在于第二个集合之中的数据。
内外连接
内连接：
两张表同时匹配才显示出来。
第十九、二十课
创建数据库实例
1.使用databaseconfigurationassistant创建。
（跟着向导走）
2.用命令行的方式建立数据库
CONNECT/ASSYSDBA
STARTUPPFILE='C:
oracleadmininit_testorcl.ora'NOMOUNT;
CREATEDATABASEtestOrclDATAFILE'/u02/oracle/testOrcl/system01.dbf'SIZE100M
LOGFILEGROUP1（'/u01/oracle/testOrcl/redo1a.log'，'/u02/oracle/testOrcl/redo1b.log'）SIZE500K，
GROUP2（'/u01/oracle/testOrcl/redo1a.log'，'/u02/oracle/testOrcl/redo1b.log'）SIZE500K
CHARACTERSETZHS16CGB231280;
--将数据库直接从未建置状态转换到打开状态
ALTERDATABASEOPEN;
1.Java连接oracle数据库
主要就是JDBC
Java.util.Properties能从xml文件中读取配置信息，也能写入。
第二十一、二十二课
Jdbc-odbc桥连接
步骤：
1.配置数据源（控制面板—管理工具—数据源（odbc）--添加）
Sql函数
to_data（string,’format’）
pl:
to_data（‘1989-2-15’,’yyyy-mm-dd’）;
表明#临时表temp#createtableuser#;
updataempset（job,sal）=（selectjob,salfromempwhereename=’smith’）whereename=’scott’;
sql函数分类：
一、单行函数如length（），这样的函数值计算每个记录。
1.字符处理函数:
a.
pl:
replace（ename,’A’,’WW’）把ename之中所有的A替换成ww
ps:
只是显示的时候变化，真是的数据并没有变。
b.
instr（）从哪个字符串中找到某个子字符串。
1指开始的位置，2指第二个出现的
c.concat（‘xxx’,’yyy’）两个字符串连接；
dlength（）返回字符的长度。
汉字占一个字符
e.ower（）,upper（）;
f．substr（string,m（start）,n（count））从第m个开始，去n个fromstring
g.trim（）
h.floor（）向下取整，ceil（）向上取整。
Power（m,n）m的n次方。
round（）四舍五入
2.日期函数
add_months（日期值，增加的月份）
pl:
select*fromempwhereadd_month（hiredate,3）>=sysdate;
最经三个月入职的员工。
同样可以应用bbs最近n个月的帖子。
系统函数
第二十三、二十四课
事务
Jdbc中设置oracle隔离机制
conn.setTranscationIsolation（Connect.SERIALIZIABLE）方法
第二十五、二十六课
数据完整性
1.约束：
notnull，uinque，primarykey，froeignkey，check,default
Pl:
createtableworker（
idnumberprimarykey,
namevarchar2（20）notnull,
salnumbercheck（sal>3000andsal<8000）,
phonenumvarchar（20）uique,
addressvarchar2（50）referencesaddress（id）,//address另一张表
sexchar
（2）check（sexin（‘男’,’女’））default‘男’）
constraint
pl:
altertableworkeraddconstraintpk_addresscheck（addressin（‘北京’））；
添加约束：
altertable表明addconstraint约束名约束种类（字段）；
constraintfk_stuf
升级会员 