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:\b.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 表示该用户是不是可以把这个权限往下传递
