Oracle 笔记（三）
Oracle的数据库对象
七大对象：用户、表、约束、序列、视图、同义词和索引
知识点一：用户 - User - 账户、管理员-一切对象的宿主
|
1、创建用户 |
???? |
|
2、授权 |
???? |
|
授权+创建用户 grant create session to 用户名 identified by 密码 |
|
|
3、锁定账户 |
???? |
|
4、解锁账户 |
???? |
|
5、修改用户 |
???? |
|
6、撤销授权 |
Revoke 权限 from 用户 Revoke 权限 on 对象 from 用户 |
|
7、删除账户 |
？？？？ |
补充知识点一：授权任务—连带系统授权
步骤一：创建账户A，连带系统授权
|
create user rose identified by 123456; grant create session to rose with admin option; |
步骤二：创建账户B
|
create user lily identified by 123456; |
步骤三：连接账户A，授权给账户B
|
Conn rose/123456@orcl; grant create session to lily; |
步骤四：链接账户B
|
conn lily/123456@orcl; |
疑问：如果我们回收用户A的权限，那么用户B的权限会被收回吗？不会。
补充知识点二：授权任务—连带对象授权
步骤一：创建了用户C，并赋予create session权限
|
create user nacy identified by 123456; grant create session to nacy; |
步骤二：赋予用户A create table权限，并在开放uesrs表空间权限
|
grant create session,create table to rose; alter user rose quota 1m on users; |
步骤三：建立一个表，并插入一个数据
|
conn rose/123456@orcl; create table a(aid number(2)); insert into a values(1); |
步骤四：连接用户A并将对象表a的select权限赋予用户B并且进行连带授权
|
grant select on a to lily with grant option; |
步骤五：连接用户B，并将对象表A.a的select权限赋予用户c
|
conn lily/123456@orcl; grant select on rose.a to nacy; |
步骤六：连接用户C，对rose.a表进行查询
|
conn nacy/123456@orcl; select * from rose.a; |
疑问：如果我们回收用户B的select权限（对对象表a），那么用户C的权限会被回收吗？会。
角色：
3种标准角色：select*from dba_sys_privs where grantee ='CONNECT'
Oracle为了兼容以前的版本，提供了三种标准的角色(role)：connect、resource和dba。
1. connect role(连接角色)
|
Grant connect to rose; |
2. resource role(资源角色)
|
Grant connect, resource to rose; |
3. dba role(数据库管理员角色)
dba role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有。
注意：撤消一个用户的所有权限，并不意味着从oracle中删除了这个用户，也不会破坏用户创建的任何表;只是简单禁止其对这些表的访问。其他要访问这些表的用户可以象以前那样地访问这些表。
创建角色
除了前面讲到的三种系统角色----connect、resource和dba，用户还可以在oracle创建自己的role。用户创建的role可以由表或系统权限或两者的组合构成。为了创建role，用户必须具有create role系统权限。
create role命令的实例：
这条命令创建了一个名为student的role：
|
create role stu; |
给student角色授权：
|
grant create session,create table,create view,create type to stu; |
用student角色给用户授权：
|
grant stu to rose; |
删除角色
|
drop role stu; |
注意：指定的role连同与之相关的权限将从数据库中全部删除。自己验证一下？？？
知识点二：表– table -- 维护使用sql语句完成数据存储
|
1、创建表class student |
createtableclass( classid number(3), classname varchar2(50) ) createtable student( stuno number(4), stuname varchar2(20), stureg date, stusex varchar2(10), classid number(3) ) |
|
2、修改表student添加telephone和address两个字段 |
--修改表student添加telephone和address两个字段 altertable student add(telephone varchar2(11),address varchar2(50)) |
|
3、修改student表中telephone这个字段number(8)类型 |
altertable student modify(telephone number(8)) |
|
4、修改student表，删除telephone字段 |
altertable student drop column telephone |
|
5、设置列不可用 |
altertable student set unused(address) |
|
6、删除不可用列 |
altertable student drop unused column |
|
7、修改字段名称 |
altertable student rename column stuno to sno |
|
8、给表改名字 |
rename student to stu |
|
9、删除表 |
Drop table student |
知识点三：约束– constraint –数据完整性和一致性
大体分类：实体完整性 -- 减少数据冗余 -- 主键约束（非空，唯一）
域完整性 -- 数据的准确性 -- check约束（default）
引用完整性 -- 数据的一致性 -- 外键约束
Oracle约束对象5大分类：主键（primary key）、非空（not null）、唯一性（unique）、检查（check）、外键（foreign key）
创建约束的两种方式：create table添加约束、alter table 添加约束
select * from user_constraints;--显示用户拥有的约束的具体内容
select * from user_cons_columns;显示用户拥有的约束具体约束在了表的哪个列上
|
--创建表的时候添加行级约束 createtable class1( classid number(2) primary key, classname varchar2(20)notnullunique )
createtable student1( stuno number(4) primary key,--主键约束 stuname varchar2(20)notnull,--非空约束 stureg datedefaultsysdate,--设定默认值 stusex varchar2(3)default'男'check(stusex in('男','女')),--检查约束 classid number(2) references class1(classid)--外键约束 ) |
|
--创建表的过程中添加表级约束 createtable student2( stuno number(4), stuname varchar2(20)notnull, stureg datedefaultsysdate, stusex varchar2(10)default'男', classid number(3), constraint pk_s2_stuno primary key(stuno), constraint uq_s2_stuname unique(stuname), constraint ck_s2_stusex check(stusex in('男','女')), constraint fk_s2_classid foreign key(classid) references class1(classid) ) l not null约束没有表级别约束（default默认值也没有表级约束的添加形式） |
|
--创建完成表格后，添加相应的约束 altertableclass add constraint pk_class_classid primary key(classid);
altertableclass add primary key(classid);
altertableclass modify(classname notnull); altertablestudent modify(ssexdefault ‘男’); |
|
--删除表中的约束 alter table class1 drop constraint SYS_C0011073 ; alter table class1 drop constraint pk_class_classid; altertableclass modify(classname null); |
总结：select * from user_constraints; select * from user_cons_columns;
添加约束【主键、唯一、check、foreign key】
Alter table 表名 add [constraint 自定义约束名]
primary key() |
unique() |
check() | 列名 between 0 and 100 | 列名 in(‘男’,’女’)
foreign key() references 主键表名(主键列)
添加约束【非空、缺省】
Altert table 表名 modify (列名非空 | 缺省)
知识点四：序列（sequence）来实现字段的自增长特性
语法：create sequence 序列名
start with 起始值
Increment by 步长
maxvalue最大值
minvalue最小值
Cycle （nocycle）
Cache 缓存>1的数字
语法：
|
问题1：建立序列，从1开始每次增加1 |
create sequence seq_classid |
|
问题2：如何得到序列值？ |
Selectseq_classid.nextval from dual; Select seq_classid.currval from dual; |
|
问题3：建立序列，从10开始每次增加3个，最大值20，循环? |
create sequence seq_test1 startwith10 increment by3 maxvalue 20 cycle cache 2
create sequence seq_test2 startwith10 increment by3 maxvalue 20 minvalue 10 cycle cache 2 |
|
问题4：minvalue 和startwith关系? |
|
|
问题5：序列的增长超过了maxvalue的结果？ |
1． 如果序列循环，从最小值或者1开始循环。 2． 如果序列不循环，超过最大值则报错 |
|
删除序列： |
drop sequence 序列名字; |
|
修改序列？ 删除序列重新建立 |
|
|
--利用序列完成class表的classid的数据插入功能 --步骤一：创建表 --步骤二：创建序列 create sequence seq_classid --步骤三：录入小班、中班、大班 insertintoclassvalues(seq_classid.nextval,'Web') |
|
|
--删除序列 drop sequence seq_classid; |
知识点五：视图view - 简化查询、提高安全性
语法 : create [or replace] view 视图名
as
复杂的select语句
使用：select * from 视图 where group by having order by
注意：有权限才能创建视图grant create view to用户
|
步骤一：定义视图 --创建视图：查询用户的姓名，所在部门的名称和工资水平 create view vw_eds as select emp.ename,dept.dname,salgrade.grade from emp,dept,salgrade where emp.deptno=dept.deptno and emp.sal between salgrade.losal and salgrade.hisal |
|
步骤二：使用视图 select * from vw_eds where grade > 3; |
|
步骤三：删除视图 Drop view vw_eds; |
知识点六：同义词（synonym）对象table的别名
语法：create synonym 同义词名称for表名
分类：公有-授权用户可以使用，私有-创建用户可以使用
|
问题1：创建scott用户下的emp表的私有同义词 |
--为emp创建私有同义词 grant create synonym to scott; create synonym syn_emp for emp; |
|
问题2：公有同义词 |
--为emp创建公有同义词 grant create public synonym to scott; create public synonym synp_emp for emp; |
知识点七：索引（index）加快查询速度，择优选择
在实际工作中，B树索引是Oracle数据库中最常用的一种索引。如在使用Create Index语句创建索引的时候，默认采用的就是B树索引。在B树索引中，是通过在索引中保存排序过的索引列以及其对应的Rowid列的值来实现的。不过对于某些比较特殊的情况，如基数比较小的列，使用这个B树索引反而会降低数据库的查询效率。
语法：createindex索引名on表(字段名1，[字段名2])
分类：单列索引：一个列
联合索引：两列以上的索引
|
问题1：在emp表的hiredate上建立索引idx_h |
select*from scott.emp where hiredate > to_date('1981-01-01','yyyy-mm-dd')
|
|
问题2：索引字段中不能使用函数 |
select*from scott.emp where to_char(hiredate,'yyyy')='1981'—索引失效
select*from emp where hiredate >=to_date('1981-01-01','yyyy-mm-dd') and hiredate <=to_date('1981-12-31','yyyy-mm-dd')–索引有效 |
|
问题3:查询在12000天以前工作的员工 索引不能参与运算 |
select * from emp where sysdate -hiredate > 12000;--索引失效
select*from emp wheresysdate-12000> hiredate;--索引有效 |
2017-10-31 18:34:59
