Java基础——Oracle（四）

一、Sql * plus 常用命令

1.关于登录,连接的几个命令

1) conn[nect] //例  conn system/manager

用法 conn 用户名/密码 @网络服务名 (as sysdba/sysoper)

当特权用户登录的时候,必须带上 as sysdba/sysope

比如 sys用户登录,就必须这么写 conn  sys/aaaaaaa as sysdba

2) show user //当显示当前登录的用户是哪一个

3) disc[onnect] 断开连接

4) exit 断开,退出

5) clear scr

6) alter user identified by 新密码

2.关于文件操作的命令

1) start 和 @

运行文件中的 sql脚本

@ c:sql; 或 start c:sql;

2) spool

可以将sqlplus 屏幕上的内容存到文本文件中

spool c:.sql;

运行sql脚本

....

select * from table1

spool off;

3.交互式命令

1 & 可以替代变量,该变量在执行时,要由用户输入 

select * from userinfo where userId='&AAA' //在 sql developer 中也好用

4. 显示和设置环境变量

可以用来控制各种格式,如果希望永久保存格式,可以去修改 glogin.sql角本

1）linesize 设置显示行的宽度,默认是80字符

show linesize
set linesize 120

2）pagesize 设置每页显示行的数目 默认40 行

二、Oracle 中表的管理（重要内容）

1) 命名规则

oracle中表和列的命名规则

-- 必须以字母开头(数字不行)    ,下划线 _ 也不行

-- 长度不能超过30个字符

-- 不能使和Oracle 保留字 //比如用 level 做字段名

-- 只能使用如下字符 'A-Z','a-z',0-9,$,# 等               

2) 数据类型

-- char 定长字符串,最大2000个字符

-- varchar2 变长字符串,最大4000个字符

-- clob(character large object ) 字符型大对象 最大4G

-- bolb 二进制数据,可以存放图片,声音等 最大 4G

-- number 范围 -10 的38 次方,到10 的 38 次方

-- number (8,2) 可以表示带小数点的数

-- data 日期类型,包 含年月日,时分秒

-- timestamp 时间戳

3）关于日期类型的说明

//创建一个表
create table STUDENT
(
    ID       NUMBER(4),
    STUNAME  VARCHAR2(20),
    SEX      CHAR(2),
     BIRTHDAY DATE,
     SAL      NUMBER(5,2)
)

添加一条数据

insert into student values (1,'陈鹏飞','男','1999-09-09',9999.99)
//会报错,文字与格式字符串不匹配
//因为这里的  '1999-09-09' 格式不正确 oracle 默认的日期格式是 DD-MON-YY
//修改日期格式的语句
alter session set nls_date_format ='yyyy-MM-dd'

3) 删除数据

delete from student  //删除所有记录,表格构还在,写日志,可以恢复,速度慢

drop tablue student //连表结构一起删除

truncate table student //删除表中的数据,表结构在,不写日志,速度快,无法恢复,在sqlserver 或mysql中,它会重置自增主键

savepoint aaa;
delete from student ;
rollback to aaa;

4) 关于查询

查看执行sql语句用的时候

set timing on //显示一条语句的执行时间

//例如 :
set timing on
select * from student 

 结果:

 ID STUNAME              SEX BIRTHDAY        SAL
 ----- -------------------- --- ----------- -------
1 陈鹏飞               男  1999-9-9     999.99
Executed in 0.016 seconds

// 注意：严格区分大小写

select * from student where stuName='AA'; 

//大写的AA和小写的aa 在oracle中是不同的

 mysql :默认查询的时候是不区分大小写的 如果就想区分大小写  SELECT * FROM userInfo where userName= binary ('aaa')

sqlservler :默认也不区分 如果想区分, select * from userInfo where userName= 'aaa' collate Chinese_PRC_CS_AI

或 alter  column 字段名 nvarchar(20) collate Chinese_PRC_CS_AI

//关于 null 值

-- 查看姓名,工资,工资 + 奖金 , 奖金

select ename,sal,sal+comm, comm  from scott.emp;

可以发现 , 数值和null 相加,结果也是 null

输出结果：                  
ENAME            SAL   SAL+COMM      COMM
---------- --------- ---------- ---------
SMITH         800.00            
ALLEN        1600.00       1900    300.00
WARD         1250.00       1750    500.00
JONES        2975.00            
MARTIN       1250.00       2650   1400.00
BLAKE        2850.00           

如何处理? 使用 nvl

select ename,sal,nve(sal,0)+nvl(comm,0), comm  from scott.emp;

查年薪 : select ename,sal,sal*12+nvl(comm,0)*12 as 年薪, comm  from scott.emp;

查工资最高的人的姓名和工资 SQL> select ename ,sal from scott.emp where sal =(select max(sal) from scott.emp);

三、Oracle 中的序列（重要内容）

序列( sequence ) 是一个计数器,它不会与特定的表关联

通过创建 sequence 和触发器实现表的自增主键,序列一般用来添充主键或计数

//创建序列

create sequence seq_id    //seq_id 是可以任意指定的序列的名称
minvalue 1
start with 1
increment by 1
cache 20;   //定义存放序列的内存块的大小,默认是20,实例异常关闭的时候,可能会造成数据丢失

//查看序列

select * from USER_SEQUENCES  //只能查看用户自己的
select * from ALL_SEQUENCES  
select * from DBA_SEQUENCES 

//删除序列
drop sequence  seq_id 

思考：如何使用

当要使用序列的值的时候, 直接调用它的 nextval

比如：

seq_id.nextval //注意:后面没有() ,它是一个属性值

select   seq_id.nextval from dual;

使用的方式

方式一 ,不使用触发器,在插入数据的时候,使用序列生成主键

insert into student (id,stuName,sex,sal) values ( seq_id.nextval,'马苗','女',8888.88) 

说明：　seq_id是序列的名称

方式二　建立触发器，当有数据添加的时候由触发器使用序列生成主键           

create trigger TRG_TEST before insert on student
for each row
begin
select seq_id.nextval into :new.id from dual;
end ; //后面的分号不能少

创建触发器以后,再对这个表进行inser 的时候,主键就会自动增长

//需要注意: 对于两个表的自增主键,用同一个序列,它产生的序号会被分着用

附 hibernate  和  sequnce

根据hibernate的文档，有两种方式实现实体对象的主键自动增长。

第一种：设置ID的增长策略是sequence，同时指定sequence的名字，最好每个表建一个sequence，此种做法就如同MS-SQL,MY-SQL中的自动增长一样，不需要创建触发器，具体的oracle数据库脚本及hibernate配置文件: 略 */

可以看到 在oracle 使用序列还是很麻烦的,所以建议对oracl中的主键,尽量不要使用自增,可以使用varchar2 ,添加的时候,用uuid java.util.UUID.randomUUID();

四、Oracle 中的用户管理 

1、创建用户

create user  //一般具有 dba(数据库管理员) 的权限才能使用

create user nicecat identified by nicecat      建立一个名为nicecat 的用户,密码也是nicecat

注意: 密码必须以 字母开头

2.删除用户

一般情况下,要用dab的身份去删除用户, 如果是别的用户进行用户删除操作,要有 drop user 的权限，自己不能删除自已 drop user 用户名 [cascade] 

注意：cascade 是级联的意思,如果被删除的用户已经创建了表,则连他的表一起删除

3.授权

grant connect to nicecat ; //把 connect 这个角色授给 nicecat

4.口令管理

使用 profile 管理用户口令

profile 是口令限制,资源限制的集合,当建立数据库时,oracle 会自动建立名为 default 的profile。当建立用户的时候,如果我们没指定 profile,那么oracle 就会将 default分配给用户。

思考：如何实现用户锁定

1.可以指定用户登录时最多可以输入密码的次数

2.可以指定用户锁定的时间(天)

3.一般会用dba的身份去执行该命令

例子: 指定nice cat 用户,最多能尝试3次登录,锁定时间为2天

1) 创建 profile 文件

create profile aaa_prifle limit failed_login_attempts 3 password_lock_time 2 ;  
//时间可以用小数

2) 把规则交给某个用户

alter user nicecat profile aaa_prifle


//附解锁
alter user nicecat account unlock; //必须有这个权限的用户才能执行

3)口令历史

如果希望用户在修改密码的时候,不能使用以前10天以用过的密码

create  profile bbb_profile limit password_reuse_time 10

4)删除 profile

drop  profile +名称

五、Oralcle 中的权限管理

Oracle 中的权限分两类

第一类：系统权限

系统权限通常是针对修改数据字典,修改数据库实例的情况进行控制,例如，创建用户,创建表空间,控制会话,建库,建表,建存储过程,登录数据库等,描述的就是用户对数据库的相关权限。

比如

-- 用户 必须有 create table 权限 则在自已的方案中建表

-- create any table 权限,可以在任意的方案中建表

-- create session 要登录数据库,就要有这个权限

常见的系统权限有

create session  //连接数据库的权限

create table //建表

create view // 建视图

create public synonym //建同意词

create trigger  //建触发器

create procedure  //建存储过程

...

 //查询一共有多少种系统权限

select * from system_privilege_map   //在oracle 10g中,实测166条

==授予系统权限

一般情况下,授予系统权限是由dba来完成的,如果是其他的用户,要有grant any privilge 权限才可以

1) 创建两个用户   mm 马苗   jcp

create user mm identified by mm ;
create user jcp identified by jcp;

2) 授权

grant create session,create table  to mm   with admin option    //注意 是 with "admin" option

如果在授权时,带用  with admin option  ,则被授权的用户,可以把这个权限再传给别人

3) 回收

revoke  create session, create table  from mm 

用的 是system 回收了 mm 的系统权限,能不能影响到 mm 授给 jcp的权限呢 ? 答案是不会，对于系统权限,在回收的时候,不会级联回尽管。

第二类：对象权限

访问其他方案中的数据对象的权力,用户可以访问自已方案中的对象,但要访问别人方案中的对象,则要有对应的对象权限才。 数据对象 表,存储过程,触发器,序列等。常用的对象权限有:

alter //修改(表结构)

delete

update

insert

index //建索引

references //引用

execute  //执行

...

查询一共有多少对象权限  select * from table_privilege_map    //约 24 种

==授予对象权限

对象权限即可以授予用户,也可以授予角色,和public (public 表示数据中所有的用户都具有些权限) grant create session to public

如果带用 with grant option 选项,则可以把权限往下传递, 但是,要注意:    /* with grant option  不能授予角色 */

着重说明:

如果给 mm 授予了对象权限,带了  with grant option  选项, 如果对mm 进行对象权限的回收,会不会影响 mm 授予其他人的权限呢?

答案是会，对于对象权限,在回收的时候,是级联回收的。

//例子 scoot 用户把emp表的所有对象权限交给 mm ,mm 再把 该表的select 权限交给 jcp ,然后再回收 mm 的权限,查看jcp会不会受到影响

1) 用scott用户登录

conn scott/scott
grant all on emp to mm with grant option;  //把emp表的所有权限授给mm,并且允许mm往下传递 

2) 用 mm 登录

conn mm/mm;
select * from scott.emp //可以了,因为有权限了
grant select on scott.emp to jcp; //由马苗把 权限授给 jcp 可以

3) 用 jcp 登录

conn jcp/jcp
select * from scott.emp  //可以.因为从mm处得到的授权
delete from scott.emp //不可以,没有权限 

4) 用scott登录,收回给mm权限

revoke all on emp from mm;

这时,mm 对 emp表所有的对象权限都会被回收,同时, jcp对该表的权限也被回收了。

谁可以对 scott.emp 表进行对象权限的授权 ?

sys , system , scott

==关于权限的传递

希望 cat 用户,可以查询scott 的 dept 表,还希望他可以把这个权限往下传递

===这个需求是对对象权限的需求 加入 with grant option  //级联回收

conn system/aaaaaaaa
grant all on scott.emp to cat with grant option

===假如系统权限 要加入 with admin option  //不级联回收

grant create session ,create table on 用户名 with admin option

六、角色管理

角色: 相关权限命令的集合

一般是由dba 创建的,或者有由有create role 系统权限的用户创建的。

角色有两种：

自定义角色,预定义角色 以下几个,是系统预定义的角色

--connect //用于连接

--dba

--resurce //可以让用户在任意表空间建表

问题 如果nicecat 用户授权了 dba ,还用不用授权connect 了? 不需要因为 dba 包含 connect

== 建立角色

在建立角色的时候,可以指定验证方式,也可以不指定

1) 建立角色(不验证)

如果角色是共用的角色,可以采用不用验证的方式建立

create role 角色名 not identified  
//创建一个角色 查询所有的角色 select * from dba_roles (名字变大写了)

2) 建立角色(数据验证)

采用这种方式时,角色名,口令,放在数据库中,当激活角色时,必须提供口令,在建立的时候,也要提供口令

create role 角色名 identified by 密码

== 角色的授权

角色开始建立的时候,它是空的,必须授予相应的权限才能使用。

给角色授权和给用户授权差不多,但系统的 unlimited tablespace 和 对象的 with grant option 选项不能授予角色

create role super_admin not identified 
//创建角色 super_admin 现在是空的,下面是授权

grant  create session  to super_admin with admin option  
//给角色授予系统权限,并可以让它进权限传递

grant  select on scott.emp to super_admin
//给角色授予对象权限,后面不可以加  with grant option 

grant  select,insert,update on scott.dept to super_admin

== 把角色分配给用户

grant 角色名 to 用户名  //with admin option

-- 删除角色

drop role 角色名

//如果删除了角色,则这个角色对应的用户将失去角色所对应的权限

-- Oracle 中包含多少角色

select * from dba_rolse; //30多种

 -- 显示角色所具有系统权限

select * from role_sys_privs where role='角色名'

-- 显示角色所具有的对象权限

select * from role_tab_privs where role='角色名'

 -- 显示用户具有哪些角色

select * from dba_role_privs where grantee ='SCOTT'

结果:

GRANTEE                        GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
SCOTT                          RESOURCE                       NO           YES
SCOTT                          CONNECT                        NO           YES
ADMIN_OPTION  表示该用户是不是可以把这个权限往下传递