第一节:用户权限管理
CREATE ANY TABLE; 可以在任何方案中建表; CREATE SESSION; 连接数据库;
CREATE TABLE; 创建表; CREATE VIEW; 创建视图
CREATE PROCEDURE; 建过程,函数,包 CREATE TRIGGER; 创建触发器
2>显示系统权限 SELECT * FROM system_privilege_map ORDER BY NAME;
3>创建用户 CREATE USER 用户名 IDENTIFIED BY 密码
4>授予用户权限 GRANT 权限1,权限2 TO 用户名
5>回收系统权限 REVOKE 权限 FROM 用户名;
思考问题?system --------->ken ---------->tom 假如收回了ken的权限,tom的登陆权限还有吗?
答案:有[系统权限不是级联回收](但是如果tom执行的是SELECT的话,就执行不了了)
总结:如果回收了ken的权限,tom的增删改操作将无法执行,但是系统权限还是有的!
6>查询表记录 GRANT SELECT ON 表名称 TO 用户名称;
7>修改表数据 GRANT UPDATE ON 表名称 TO 用户名;
8>删除表数据 GRANT DELECT ON 表名称 TO 用户名;
9>将上述权利全部赋予同一个用户 GRANT ALL ON 表名称 TO 用户名;
10>精确权限:能否让某个用户的访问权限更加精确。在SELECT或者UPDATE后面加字段内容(用括号括起来)!
11>赋予EXECUTE(存储过程) 权限 GRANT EXECUTE 表名称 TO 用户名;
12>授予index(索引)权限 GRANT INDEX ON 表名称 TO 用户名;
13>WITH GRANT OPTION 意思是 加入 A将权限赋予B,同时B也可以授权与其他用户,但不能授予角色!
GRANT ALL ON 表名称 TO 用户名 WITH GRANT OPTION;
第二节:角色定义
角色定义:角色就是相关权限的命令集合,使用角色目的是为了简化用户权限的管理!
角色分为预定义角色和自定义角色:预定义角色指的是Oracle自身所有的角色!下面有详细介绍!
1. connect 角色 所拥有的权限
alter session/create cluster/create database link/create session/create table/create view/create sequence
2.resource 角色 所拥有的权限
create cluster/create indextype/create table/create sequence/create type/create procedure/create trigger
3.DBA 角色 所拥有的权限
dba具有所有系统权限,包括with admin option,系统默认的dba为sys/system,它们可以将任何权限授予其他用户!但是它们不具有sysdba/sysoper权限(启动/关闭数据库)
4.创建角色:一般需要有dba创建,如果其他用具具有create role 权限 也可以进行角色创建
4.1公众角色,不验证方式:create role not identified;
4.2创建角色,需要验证:create role 角色名 identified by 密码;
5.授予权限
grant create session to 角色名 with admin option
grant select on 表明 to 角色名
grant update,delete,insert to 角色名
说明:如果删除了某个角色,被授予角色的对象的权限也随之消除。
grant select on 表明 to 角色名;
grant 角色名 to 用户名;
假如角色名删除,那么用户名所拥有角色名的权限也随之没有!
注意,系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的
显示角色信息
6.select*from dba_roles; 查看所有角色信息
7.select*from role_sys_privs where role='角色名'; 查看用户所拥有的系统权限;
8.select*from role_tab_privs where role='角色名'; 查看用户所拥有的对象权限;
第三节:数据字典
1:数据字典记录了系统信息,他是只读表和视图的集合,归属为sys用户!
2:用户只能在数据字典上只能查询操作,修改和维护由系统自动完成!
3:数据字典组成,由数据字典基表和数据字典视图组成!基表存储数据库基本信息!
User_tables;
说明:用于显示当前用户所拥有的表,它只返回用户所对应方案的表
例子:SELECT*FROM USER_TABLES;
All_tables;
说明:用于显示当前用户可以访问的所有表,不仅返回用户对应方案的表,还返回可以访问的表!
例子:SELECT*FROM ALL_TABLES;
Dba_tables;
说明:他会显示所有方案的数据库表,但是用户必须拥有DBA角色和SELECT ANY TABLE 权限
例子:SELECT 表名称 FROM DBA_TABLES;
Dba_user;
说明:可以显示所有数据库用户的详细信息!
例子:SELECT*FROM DBA_USER;
Dba_sys_privs;
说明:可以查询用户所拥有的系统权限;
例子:SELECT*FROM DBA_SYS_PRIVS WHERE GRANTEE=值
Dba_tab_privs;
说明:查询用户所拥有的对象权限;
例子:SELECT*FROM DBA_TAB_PRIVS WHERE GRANTEE=值
Dba_col_privs;
说明:查询用户所拥有的列权限;
例子:SELECT*FROM DBA_COL_PRIVS WHERE GRANTEE=值
Dba_role_privs;
说明:可以查看用户所拥有的角色!
例子:SELECT*FROM DBA_ROLE_PRIVS WHERE GRANTEE=所查询的名字
比较role
select*from role_sys_privs where role='角色名'; 查看用户所拥有的系统权限;
select*from role_tab_privs where role='角色名'; 查看用户所拥有的对象权限;
查询数据库所有角色,一般是DBA:SELECT*FROM DBA_ROLES;
查询数据表空间:SELECT 表名称 FROM DBA_TABLESPACES;
查询orale中所有的系统权限,一般是dba:
SELECT * FROM system_privilege_map ORDER BY name;
查询数据库中的所有角色:SELECT*FROM DBA_ROLES;(如果查询单个,查询的表是DBA_ROLE_PRIVS;)
显示当前数据库的名称:SELECT*FROM GLOBAL_NAME;
-----未实现(后续补充)
1.对象定义情况
2.对象占用空间大小
3.列信息
4.约束信息
★★★★后记★★★★:这都是我自己整理的,并非复制粘贴,因为Oracle不是我发明的所以我不能说是我原创的!如果有错误了,欢迎批评,让我们共同进步! 2010.09.14 日凌晨一点
第四节:数据库的逻辑结构 _表空间_段_区_块
什么是表空间?
答:表空间用于从逻辑上组织数据库的数据,数据库通常由一个表空间和多个表空间组合而成!
表空间的作用?
1.控制数据库占用磁盘空间量
2.DBA可以将不同的数据类型部署在不同的位置,有利于I/O操作,同时又有利于备份和操作!
创建表空间
CREATE TABLESPACE 表空间名称
DATAFILE 'd:\data01.dbf' //文件路径
SIZE 大小M //文件大小
AUTOEXTEND ON NEXT 50M MAXSIZE 1024M //设置文件自动增长路径
UNIFORM SIZE 128K //区的大小为128K,如果不指定,默认为64K!
修改表空间的状态
1.让表空间Online ALTER TABLESPACE 表空间名字 ONLINE;
2.使表空间离线Offline ALTER TABLESAPCE 表空间名字 OFFLINE;
3.让表空间只读 ALTER TABLESAOCE 表空间名字 READ ONLY; //注意:系统表空间无法修改此项属性
4.让表空间只读 ALTER TABLESAOCE 表空间名字 READ WRITE;
知道表空间名,显示该表空间包括的所有表
SELECT * FROM ALL_TABLES WHERE TALESPACE_NAME = '表空间名称';
SELECT * FROM TABLESPACE_NAME,TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME="表名称";
删除表空间
DROP TABLESPACE 表空间名字 INCLUDING CONTENTS AND DATAFILES;
扩展表空间
案例说明:
1. 建立一个表空间 sp01
2. 在该表空间上建立一个普通表 mydment 其结构和dept一样
3. 向该表中加入数据 insert into mydment select * from dept;
4. 当一定时候就会出现无法扩展的问题,怎么办?
5. 就扩展该表空间,为其增加更多的存储空间。
CREATE TABLESPACE sp01
DATAFILE 'D:\123.mdf'
SIZE 20M
UNIFORM 128K;
CREATE TABLE mydment AS SELECT*FROM SCOTT.DEPT;
INSERT INTO myment SELECT*FROM SCOTT.DEPT;
INSERT INTO myment SELECT*FROM myment;
对上面案例的操作
增加数据文件
ALTER TABLESPACE sp01 DATAFILE '路径' SIZE 5M;
增加数据文件的大小 有问题
ALTER TABLESPACE sp01 DATAFILE '路径' RISIZE 10M;
设置文件的自动增长
ALTER TABLE sp01 DATAFILE "路径" AUTOEXTEND ON NEXT 10M MAXSIZE 1024M;
移动数据文件步骤
有时候因为数据文件损坏,就需要来进行文件移动操作!
1.确定表空间所在地址
SELECT TABLESPACE_NAME FROM DBA_DATA_FILES WHERE FILE_NAME = ' 路径 ';
2.使表空间离线
ALTER TABLESPACE sp01 OFFLINE;
3.移动数据文件
HOST MOVE '原地址','目标地址';
4.对数据库进行逻辑修改
ALTER TABLESPACE sp01 RENAME DATAFILE '源地址'TO'目标地址';
5.使表空间联机
ALTER TABLESPACE online;
6.查询表空间信息
SELECT TABLESPACE_NAME FROM dba_tablespaces;
7.显示表空间所包含的数据文件
SELECT FILE_NAME,BYTE FROM dba_data_files WHERE TABLESPACE_NAME = 'SP01';
第五章 维护数据库的完整性
数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则,在oracle中,数据完整性可以使用约束、触发器、应用程序(过程、函数)三种方法来实现,在这三种方法中,因为约束易于维护,并且具有最好的性能,所以作为维护数据完整性的首选。
约束:约束用于确保数据库满足特定的商业规则,在Oracle中,常见的约束有not null,unique,primary key,foreign key 和check五种。
not null (非空) 也就是说插入数据的时候不可以为空!
unique(唯一) 数据的唯一性,但是可以为null。例如身份证号码。学号!
primary key(主键) 当定义主键后,该列不能重复,且不能为空值!一个表只能有一个主键,但是可以有多可UNIQUE
foreign key(引用) 用于主表和从表之间的关系!外间约束要定义在从表上!
chec(检查约束) 可以为检查约束
使用举例:在一个表中
REFERENCES 表名称(表的字段)
说明:在建表的时候,如果忘记添加约束,可以进行后序添加!
添加为空的约束:ALTER TABLE 表名称 MODIFY 字段名 NOT NULL; //只有为空约束才是MODIFY;
增加唯一约束:ALTER TABLE 表名称 ADD CONSTRAINT '约束名称' UNIQUE(身份证字段 );
增加检查约束:ALTER TABLE 表名称 ADD CONSTRAINT '约束名称' check(beijing in ('beijing','shanghai','guangzhou'));
删除约束:
删除一般约束:
ALTER TABLE 表名称
DROP CONSTRAINT 约束名称;
删除主键约束(如果连接有其他表必须加上cascade):
ALTER TABLE 表名称
DROP CONSTRAINT PRIMARY KEY CASCADE;
例子:主键约束和外键约束:
CREATE TABLE stu_01(
stu_class NUMBER(6),
stu_name VARCAHR(2),
CONSTRAINT 约束名称 FOREIGN KEY(stu_class) REFERENCES stu_class(class_id )
)
CREATE TABLE stu_class(
class_id NUMBER(6)
class_name VARCHAR(2)
)
---------------------------------->2010.10.03日
第六节 pl/sql 编程
创建一个表
CREATE TABLE student(
stu_id NUMBER(3),
stu_name VARCHAR(8)
)
创建存储过程
CREATE PROCEDURE sp_add
IS
BEGIN
INSERT INTO student VALUES(123,‘我星永恒');
END;
EXEC sp_add;
CALL sp_add;
编写规范
1.注释
单行注释 - -
多行注释 /* */
2.标志符号的命名规范
1).当定义变量时,建议用v_作为前缀v_sal
2).当定义常量时,建议用c_作为前缀c_rate
3).当定义游标时,建议用_cursor作为后缀emp_cursor
4).当定义例外时,建议用e_作为前缀e_error
pl/sql块介绍:块(block)是pl/sql的基本程序单元,编写pl/sql程序实际上就是编写pl/sql块,要完成相对简单的应用功能,可能只需要编写一个pl/sql块,但是如果想要实现复杂的功能,可能需要在一个pl/sql块中嵌套其它的pl/sql块。
pl/sql块由三个部分构成:定义部分,执行部分,例外处理部分。
定义部分:declare
执行部分:begin
异常处理:exception
end;
实例:
--打开输出选项
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.put_line('Hello,World');
END;
dbms_output是oracle所提供的包(类似java的开发包),该包包含一些过程,put_line就是dbms_output包的一个过程。
declare
v_name VARCHAR2(30);
BEGIN
SELECT ENAME INTO v_name FROM emp_hsj WHERE empno=&empno;
dbms_output.put_line('姓名:' || v_name);
END;
第七节: P/L SQL 编程具体介绍
存储过程:过程用于执行特定的操作,当建立过程时,既可以指定输入参数(in),也可以指定输出参数(out), 通过在过程中使用输入参数,可以将数据传递到执行部分;通过使用输出参数,可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。
CREATE PROCEDURE update_emp(v_name VARCAHR2,v_sal NUMBER)
IS
BEGIN
UPDATE emp SET SAL=v_sal WHERE ename=stu_name;
END;
EXEC update_stu('SMITH',800);
函数:
CREATE FUNCTION yearsal(name VARCHAR2) RETURN NUMBER;
IS year_sal NUMBER(7,2);
BEGIN
SELECT sal*12+NVL(comm,0) INTO yearSal FROM Emp WHERE ename=name;
END
--调用函数
SELECT fn_QueryYearSalByName('SMITH') FROM DUAL;
包:包用于把过程跟函数存储起来!
CREATE PACKAGE sp_package
IS
PROCEDURE update_emp(v_name VARCAHR2,v_sal NUMBER);
CREATE FUNCTION yearsal(name VARCHAR2) RETURN NUMBER;
END
包体:
CREATE PACKAGE BODY sp_package
IS
PROCEDURE update_emp(v_name VARCAHR2,v_sal NUMBER);
IS
BEGIN
UPDATE emp SET SAL=v_sal WHERE ename=stu_name;
END;
FUNCTION yearsal(name VARCHAR2) RETURN NUMBER;
IS
year_sal NUMBER(7,2);
BEGIN
SELECT sal*12+NVL(comm,0) INTO yearSal FROM Emp WHERE ename=name;
END;
END;
SELECT sp_package.fn_annual_income('SMITH') FROM dual;