ITPub博客

首页 > 数据库 > Oracle > 【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版)

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版)

Oracle 作者:stru09 时间:2013-10-14 10:15:11 0 删除 编辑

一、oracle  常用sql plus 命令

(1) conn[ect] 用户名/密码@网络[as sysdba /sysoper] 

(2) 一般情况下使用普通用户登录,除非需要更高权限时,在切换高级用户;

(3) disc[onnect] 断开连接,又不退出当前窗口;

(4) quit/exit 完全退出;

(5) show user 显示当前用户;

(6) 管理员修改用户密码:alter user xxxx identified by yyyy;

(7) Passw[ord]

pass 用户名;

给自己修改密码,不需要带用户名;给别的用户修改,需要带用户名;

 

创建临时表空间

create temporary tablespaceuser_temp

tempfile 'd:user_temp.dbf'

size 50m

autoextend on

next 50m maxsize 1024m

extent management  local;

 

创建表空间

createtablespaceuser_ts

datafile 'd:user_ts.dbf'

size 50m 

autoextend on

next 50m maxsize 1024m

extent management  local;

 

创建用户

create user test38  identified by test38

default  tablespaceuser_ts

temporary tablespaceuser_temp

quota 5m on user_ts;

 

给用户赋予权力

grantdba to test38;

scott用户解锁

alter user scott account unlock;

system 用户给scott修改密码

Alter user scott identified by tiger;

 

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

不常用的命令:

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

linesize一行能显示多少个字符就换行了默认就只有80字符,所以会出现一个记录一行没有显示完全就换行了。

Set linesize 120

pagesize显示多少个记录就从新开始

如图:前后差别

Set pagesize 100 

 

二、oracle 用户管理

1. 创建用户(必须是具有DBA权限的才行

create   user  名字  identified by 密码(密码不能以数字开始)

defaulttablespacexxxx;

temporary tablespacexxx2;

quotaxmon xxxx;

 

如果出现这样的情况,直接百度或者google一下。

2.给用户分配权限

grant create session to 用户名;

 

 

3.管理的用户的机制

 

 

 

 

 

 

 

 

4.综合案例

 

 

 

方案(schema)(当创建一个用户的时候,只要这个用户创建了任何的数据对象(如表,索引,触发器等),那么DBMS就会创建一个与该用户名一样的一个方案。)

赋权

grant  select/update/insert/all  on  emp  to  用户名;

 

ps:如果想看一个用户有哪些数据对象,可以通过pl/sql development 查看。

 

例子:小红查询scottemp

select from scott.emp;

 

 

表空间

 

参考:http://baike.baidu.com/view/2973562.htm

 

 Oracle表空间之基本概念  

ORACLE数据库被划分成称作为表空间的逻辑区域——形成ORACLE数据库的逻辑结构。一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。

  每个ORACLE数据库均有SYSTEM表空间,这是数据库创建时自动创建的。SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息(关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表)

  一个小型应用的ORACLE数据库通常仅包括SYSTEM表空间,然而一个稍大型应用的ORACLE数据库采用多个表空间会对数据库的使用带来更大的方便。

 

一个用户可以使用一个或多个表空间,一个表空间也可以供多个用户使用。用户和表空间没有隶属关系

 

 

 

个人阶段总结

数据库实例、表空间(逻辑结构)、用户、方案、数据对象(表,过程,包等)的关系

一个实例下可以有多个表空间。默认是有一个system表空间。一个用户可以使用一个或多个表空间,一个表空间也可以供多个用户使用。用户和表空间没有隶属关系。数据库建立一个用户,就建立了有且只有一个与该用户名字一样的方案来管理该用户建立的各种数据对象。如有两个表空间,一个是默认的system表空间,一个是新建的user_tasp表空间。 用户(wang)在system建立一个表t1,在user_tasp建立一个表t2,但是只有一个方案(wang)来管理这两个表,虽然表分布在不同的表空间上。也说明方案和表空间没有隶属关系,就像用户和表空间没有隶属关系一样。数据对象的操作一般是通过方案来实现操作的。如:

Select from scott.emp 这是完整的写法。scott是方案名

--个人实验

--建立临时表空间

create temporary tablespace user_temp

tempfile 'f:datauser_temp.dbf'

size 50m

autoextend on

next 10maxsize 100m

extent management local

 

--建立表空间

create tablespace user_tasp

datafile 'f:datauser_tasp.dbf'

size 50m

autoextend on

next 10maxsize 100m

extent management local

 

--创建用户

create user wang identified by wang default tablespace system;

grant connect,resource to wang;

alter user wang quota 10on user_tasp;  //在表空间上给用户分配空间就能让用户在该表空间建立数据对象。注意不要指定分配空间在临时表空间上,不然会报错。

 

--在默认表空间上建表

create table t_test1(id number,name varchar2(20)) tablespace system;

--user_tasp 建表

create table t_test2(id number,name varchar2(20)) tablespace user_tasp;

 

Schema(方案)

当一个用户被创建之后,只要它创建任何一个数据对象,那DBMS就会创建一个和它名字一样的方案与该用户对应。

 

 

Ps:如果想看某个用户有什么数据对象,用pl/sql development 软件。

Schema 的实际应用:

xioanghong访问scottemp

1. 连接scott

conn  soctt/tiger

2. xiaohong赋权限

grant  select/update/delete/all  on emp to xiaohong

3.

select from scott.emp;

 

 

参考:http://hi.baidu.com/tpadvjynoebbird/item/428a8fee7371253b86d9de27

 

1、with admin option 用于系统权限授权,with grant option 用于对象授权。 

2、给一个用户授予系统权限带上with admin option 时,此用户可把此系统权限授予其他用户或角色,但收回这个用户的系统权限时,这个用户已经授予其他用户或角色的此系统权限不会因传播无效,如授予A系统权限create session with admin option,然后A又把create session权限授予B,但管理员收回A的create session权限时,B依然拥有create session的权限,但管理员可以显式收回B create session的权限,即直接revoke create session from B. 

而with grant option用于对象授权时,被授予的用户也可把此对象权限授予其他用户或角色,不同的是但管理员收回用with grant option授权的用户对象权限时,权限会因传播而失效,如:grant select on 表名 to with grant option;,A用户把此权限授予B,但管理员收回A的权限时,B的权限也会失效,但管理员不可以直接收回B的SELECT ON TABLE 权限。  

同点
两个都可以既可以赋予user 权限时使用,也可以在赋予role 时用

不同点:
with admin option 只能在赋予 system privilege 的时使用
with grant option 只能在赋予 object privilege 的时使用


撤消带有with admin option 的system privileges 时,连带的权限将保留

撤消带有with grant option 的object privileges 时,连带的权限将不被保留

 

 

 

 

用户口令管理

 

使用profile进行用户口令管理

一个用户连续3次密码输入错误,那该账户锁定

1.创建profile文件

create profile文件名limitfailed_login_attempts次数password_lock_time天数;

2.把该文件(规则)分配给某个用户

alter user用户名 profileprofile文件名;

 

3.终止口令

一个账号的密码最多能用几天的问题。

Pl: 一个密码的使用时间为10天,宽限时间是2

create profile文件名limit password_life_time 10 password_grace_tim2;

4.删除profile

drop profile文件名;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

windows

lsnrctl start 

oradimstartupsidorcl;

 

unix/linux环境下

(略)

 

Oracle 的登陆认证

 

 

 

Compmgmt.msc 进入计算机管理

 

找回管理员的密码

1. 搜索PWD数据库实例名.ora

2. 删除该文件,最好备份一份。

3. 生成新的密码文件,在dos下输入命令。

orapwd file=PWD数据库实例名.ora的具体路径 password=新密码 entries 允许多少个人进入。

Ps: orapwd file =D:oracleproduct10.2.0db_1databasePWDorcl.ora password welcome entries10

file 文件路径          password 新密码    entries 登陆sys最多的用户。

4. 重新启动数据库实例。

 

 

 

 

 

Oracle 数据类型

Char 存放字符串,最大2000字符,定长

Pl:  name char(32), 只能存放32字符,多了,报错;少了,空格填满。

ps: dump() 显示一个字段具体的情况

 

 

varchar2变长字符,最大可存放4000字符。

name varchar(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.456  number  结果就是:123.456

 

 

 

原则:实际开发过程,如果number指定了小数位,那明确指定,如果没有指定,就直接用number

 

date 日期类型

默认的是dd-mm-yyyy

 

第十课

 

 

Ps: 数据类型是number 插入的是字符串型的‘12345’,oracle 会自动把这样的字符类型转化为数字类型。但是如果是‘asd123’就不行了。

2.插入空置的情况:插入null’’情况是一样的。‘’(中间有一个空格)并不是空。即‘’=null

3.字符和日期类型应该用’’包含起来。

 

 

 

第十一课

 

Ps:oracle sql不区分大小写,但是内容区分大小写;

 

 

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

Ps:给字段改名可以用as ,也可以不用;

nvl() 处理null的字段。

select ename,sal*12+nvl(comm,0) as all_sal from emp;

oracle 中的字符连接符号: ||

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

 

设置保存点并回滚

savepoint  aa;

rollback  to  aa;

 

 

第十二课

where 语句的用法

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

like 操作符

任意零个或者多个字符;任意一个字符;

条件为空与否的使用 is null 或者 is not null 处理;

 

 

Order by 排序

默认是asc  desc 是降序。

第十三课

组函数

max(),min(),sum(),count(),avg();

ps:括号里面可以是字段也可以是表达式

avg(sal) 不会吧sal为空的记录统计进来。它只计算有值记录的平均值。求所有人的平均值,可以这样:

select sum(sal)/count(*) from emp;

group by查询的结果进行分组;

having 限制分组显示结果。

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版)

 

第十四课

多表查询

1. emp ,salgrade 两张表的连接。

select ename,sal,grade from emp join salgrade on  sal between losal and hisal;

自连接

2. 显示FORD的上级

select ename ,sal from emp where empno =(select mgr from emp where ename ='FORD');

3. 显示所有员工的名字,及其上级的名字;

select t1.ename as worker,t2.ename as boss from emp t1 left join emp t2 on t1.mgr t2.empno;

第十五课

子查询

显示与SMITH同一个部分的员工

select ename from emp where deptno =

 select deptno from emp where ename 'SMITH');(结果包含了SMITH,如果不想包含SMITH ,在加上and ename<>SMITH;

 

子查询中使用all

Pl:显示工资比部分30的所有员工的工资还要高的员工姓名,工资,部门号

select ename,sal,deptno from emp where sal >

all(select sal from emp where deptno =30);

同样的写法:

select ename,sal,deptno from emp where sal >

select max(sal) from emp where deptno =30);

 

子查询中使用any

Pl:

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

第十六课

from中使用子查询

显示各部分中高于本部分平均工资的员工信息。

select emp.ename,sal,t.myavg,t.deptno from emjoin

 (select avg(sal) myavg,deptno from emp group by deptno) t

on emp.deptno t.deptno where sal t.myavg;

 

 

各个部门工资最高人的详细信息。

select emp.*,t.maxsal from emp join 

(select max(sal) maxsal,deptno from emp  group by deptno) 

on emp.deptno t.deptno where emp.sal t.maxsal;

 

显示每个部门的信息和人员数

1.显示各个部分的人数

select count(*) pernum, deptno from emp group by deptno

2.与表dept进行连接

select dept.*,t.pernum  from dept left 

join (select count(*) pernum, deptno from emp group by deptno) on dept.deptno t.deptno ;

 

 

1.表本身的复制

create table mytest as select * from emp;

ps:把emp 表的结构和记录都复制到这个mytest表中

 

2.自我复制进行插入操作:

insert into mytest select * from mytest;

 

Oracle 分页

select ename, sal

  from (select ename, sal, rownum r

          from (select * from emp order by sal desc))

 where > 5 and < 10;

 

 

第十七课

1.合并查询 union (求并集)

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

 

2.union all (并不去掉重复的部分,单纯的两个集合相加)

3.intersect (求交集)

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

 

4.minus 求差集,即存在在第一个集合而不存在于第二个集合之中的数据。

 

 

内外连接

内连接:两张表同时匹配才显示出来。

 

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

 

 

第十九、二十课

创建数据库实例

1.使用 database configuration assistant创建。(跟着向导走)

2. 用命令行的方式建立数据库   

CONNECT / AS SYSDBA 

STARTUP PFILE= 'C:oracleadmininit_testorcl.ora' NOMOUNT;  

CREATE DATABASE testOrcl DATAFILE '/u02/oracle/testOrcl/system01.dbf' SIZE 100 

 LOGFILE GROUP1 ('/u01/oracle/testOrcl/redo1a.log''/u02/oracle/testOrcl/redo1b.log') SIZE 500K

           GROUP2 ('/u01/oracle/testOrcl/redo1a.log''/u02/oracle/testOrcl/redo1b.log') SIZE 500 

 CHARACTER SET ZHS16CGB231280;  

 --将数据库直接从未建置状态转换到打开状态   

ALTER DATABASE OPEN;

 

 

1.Java 连接oracle数据库

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

主要就是JDBC

 

Java.util.Properties  能从xml文件中读取配置信息,也能写入。

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

第二十一、二十二课

Jdbc-odbc 桥连接

步骤:

1. 配置数据源(控制面板管理工具数据源(odbc--添加)

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

Sql函数

to_data(string,format)

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

pl: to_data(1989-2-15,yyyy-mm-dd);

 

表明 临时表   temp#  create table user# ;

updata emp set (job,sal) (select job,sal from emp where ename=smithwhere ename=scott;

 

sql函数分类:

一、 单行函数如length(),这样的函数值计算每个记录。

1.字符处理函数:

a.【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版)

pl: replace(ename,A,WWename之中所有的A替换成ww

ps:只是显示的时候变化,真是的数据并没有变。

b.【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版)

instr()从哪个字符串中找到某个子字符串。1指开始的位置,2指第二个出现的

cconcat(xxx,yyy两个字符串连接;

d length() 返回字符的长度。汉字占一个字符

e. ower(),upper();

fsubstr(string,m(start),n(count)) 从第m个开始,去n个 from string

g. trim()

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

h. floor()向下取整,ceil()向上取整。Power(m,n) mn次方。

round() 四舍五入

2.日期函数

add_months(日期值,增加的月份)

pl: select from emp where add_month(hiredate,3) >=sysdate;

最经三个月入职的员工。同样可以应用bbs最近n个月的帖子。

 

系统函数

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

 

第二十三、二十四课

事务

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

Jdbc中设置oracle隔离机制

conn.setTranscationIsolation(Connect.SERIALIZIABLE)方法

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

第二十五、二十六课

数据完整性

1. 约束:not nulluinqueprimary keyfroeign keycheck,default

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

Pl:

create table  worker(

id number  primary key,

name  varchar2(20) not null,

salnumber  check(sal>3000 and sal<8000),

phonenum  varchar(20) uique,

address varchar2(50) references address(id),//address 另一张表

sex char(2) check(sex in (‘男’,’女’))default ‘男’)

 

constraint

pl: alter table worker add constraint pk_address check(address in(北京))

添加约束:

alter table 表明 add constraint 约束名约束种类(字段);

constraint  fk_stu  foreign key(cid) references t_class(cid)

删除约束:
SQL> alter table表名 drop constraint约束名

建立约束的两种方法:

1. 每个字段后直接填写约束条件如 id  primary key,(字段级别约束)

2. 字段都建立好了,在表后添加(表级约束)

constraint 约束名 primary key(id),

constraint 约束名 foreign key(id) references 表(字段),

constraint 约束名 unique(id),

not null(字段)

constraint 约束名 primary key(id),

 

 

第二十七课

序列

create sequence seq 

start with 

increment by 1

minvalue 

maxvalue 3000 

cycle   //cycle 指增长到3000 在重新从1开始增长;

序列的使用 seq.next.val

序列名.currval   显示当前序列的值。

(这个使用之前必须先使用上面那个)

 

第二十八课

索引

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

1.经常用于查询、排序和分组的列(即经常在whereordergroup by子句中出现的列)。

2.大型的表(上百万级的数据表)使用索引

3.逻辑层次不要超过4层(???)

 

【云凡网络学院】传智播客韩顺平oracle笔记(个人总结版) 

 

 

索引会降低修改、删除和插入的效率

 

云凡学院第一期hadoop培训即将开始

    云凡网络学院针hadoop零基础,或者转行做hadoop的人士;

开办hadoop第一期课程,享价格优惠,名额有限,报名快快加入交流群:306770165

 

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26075559/viewspace-1116360/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-01-31