Oracle 开发 - 2

--[2]// Oracle Basic 2
-------------------------------------------------------------------------------------//
--用户-------------------------------------------------------------//
--（注：一定要以DBA身份的用户登陆才能完成下列操作）
--查看数据库已有的用户信息
SELECT * FROM DBA_USERS;

--创建新用户（用户名：laozhao ，密码：laozhao）
CREATE USER laozhao IDENTIFIED BY laozhao;
--删除用户
DROP USER laozhao;
--用户授权（赋予CONNECT,RESOURCE角色），不授权，用户将不可用
GRANT CONNECT,RESOURCE TO laozhao;

--角色-------------------------------------------------------------//
--（注：一定要以DBA身份的用户登陆才能完成下列操作）
--查看数据库已有角色
SELECT * FROM DBA_ROLES;

--创建角色
CREATE ROLES role_name;
--删除角色
DROP ROLES role_name;
--为新建角色赋予已有的角色权限
GRANT CONNECT TO role_name;
--为新建角色赋予student表的SELECT权限
GRANT SELECT ON student TO role_name;

--权限-------------------------------------------------------------//
--查询所有系统权限
SELECT PRIVILEGE FROM DBA_SYS_PRIVS;

--查看当前用户的系统权限
SELECT * FROM USER_SYS_PRIVS;
--查看当前用户的对象权限
SELECT * FROM USER_TAB_PRIVS;

--锁---------------------------------------------------------------//
--表一旦锁住，别的用户将不可有（增、删、改）操作
--表级共享锁（多用户可以放置）
LOCK TABLE emp IN SHARE MODE;
--表级排它锁（单个用户放置）
LOCK TABLE emp IN EXCLUSIVE MODE;
--行锁
--在执行DML语句时，Oracle会自动为执行行添加行级锁

--创建表-----------------------------------------------------------//
--下面一张学院资料表，一张得分表
--表1
CREATE TABLE student
(
 id NUMBER,
 name VARCHAR2(20),
 sex VARCHAR2(3),
 age NUMBER,
 address VARCHAR2(50),
 CONSTRAINT student_pk PRIMARY KEY(id)   --主键约束
);
--或创建后修改表格
--ALTER TABLE students ADD CONSTRAINT student_pk PRIMARY KEY(id);
--表2
CREATE TABLE score
(
 id NUMBER,	
 clanguage NUMBER(5,2),  --共五位数字，小数点占用两位
 java      NUMBER(5,2),
 vb        NUMBER(5,2),
 asp       NUMBER(5,2),
 CONSTRAINT score_fk FOREIGN KEY(id) REFERENCES student(id)  --外键约束
);

--为上面创建的表插入数据
INSERT INTO student
	VALUES('2','JianLi','F','20','ShangHaiShi YangPu');
INSERT INTO score
	VALUES('2','87','79','92','99');
--也可为指定列插入
INSERT INTO student(id,address)
	VALUES('1','AnHui SuZhou');

--删除表内指定数据
DELETE student WHERE id=1;
--删除表
DROP TABLE student;

--查询语句(查询所有)
SELECT * FROM student
--查询语句(查询指定列)
SELECT id,address FROM student;
--可指定“用户名.表名”查询，如想查询scott用户下的emp表，可写为（注：权限低的用户查询不到权限高的用户表）
SELECT * FROM scott.emp;

--序列-------------------------------------------------------------//
--创建序列(创建名为stu_id的序列)
CREATE SEQUENCE stu_id
INCREMENT BY 1   --对上一数字的增量(可为负数)
START WITH 1     --序列起始值
NOMAXVALUE       --指定序列最大值，或无最大值(设置最大值只需去除no，并在其后加入最大值即可)
NOMINVALUE       --指定序列最小值，或无最小值
NOCYCLE          --指定到达最大或最小值时是否循环处理
ORDER            --保证产生的序列是按照一定的顺序(不保证可在前加入no，默认为NOORDER)
CACHE 10;        --指定在内存中可使用的序列个数，(当内存中序列用完时再生成下一批序列放入内存，以提高效率)

--查询内存中剩余的序列
SELECT stu_id.NEXTVAL FROM student;
--使用序列(NEXTVAL,CURRVAL)
--插入数据时(NEXTVAL)
INSERT INTO student(id,address)
	VALUES(stu_id.NEXTVAL,'AnHui SuZhou');
--使用当前值(CURRVAL)
INSERT INTO student(id,name)
	VALUES(stu_id.CURRVAL,'zhao');

--删除序列
DROP SEQUENCE stu_id;

--同义词-----------------------------------------------------------//
--创建同义词(公有),创建了student 的同义词 stu
CREATE PUBLIC SYNONYM stu FOR student;
--创建同义词(私有)
CREATE SYNONYM stu FOR student;

--删除同义词(注：删除时要指定公有还是私有)
DROP PUBLIC SYNONYM stu;
/*
  可把同义词(SYNONYM)或表(TABLE)、视图(VIEW)、快照(SNAPSHOT)、序列(SEQUENCE)、过程(PROCEDURE)、
  函数(FUNCTION)、包(PACKAGE)、对象类型(OBJECT TYPE)等从别的用户那里引用过来创建属于自己的，
  如：要引用system用户的stu同义词（注：权限低的用户不能操作权限高的用户）
*/
CREATE SYNONYM stu FOR system.stu;

--视图-------------------------------------------------------------//
--创建一个简单的视图
CREATE VIEW stu_info
	AS
	SELECT id,name FROM student;

--创建一个拥有两张（上述：Student、scort）表的视图
CREATE VIEW stu_Java
	AS
	SELECT student.id,name,java FROM student,score
	WHERE student.id = score.id;  --视图连接条件，必选

--删除试图
DROP VIEW stu_info;

--索引-------------------------------------------------------------//
--创建示例表
CREATE TABLE students
(
	id NUMBER,
	name VARCHAR2(20),
	sex CHAR(1),
	age NUMBER
);
--BTree索引
CREATE INDEX ind_id ON students(id);
--位图索引
CREATE BITMAP INDEX ind_sex ON students(sex);
--反向键索引
CREATE INDEX ind_age ON students(age) REVERSE;
--函数索引
CREATE INDEX ind_name ON students(lower(name));

--查看所创建的索引信息
SELECT index_name,index_type FROM USER_INDEXES WHERE table_name='STUDENTS';

--删除索引
DROP INDEX ind_id;

--附注-------------------------------------------------------------//
--创建概要（配置）文件
CREATE PROFILE
	appl_profile
LIMIT
	SESSIONS_PER_USER               2  --
	CPU_PER_SESSION             10000  --hunderth of seconds
	CPU_PER_CALL                    1  --hunderth of seconds
	CONNECT_TIME            UNLIMITED  --minutes
	IDLE_TIME                      30  --minutes
	LOGICAL_READS_PER_SESSION DEFAULT  --db bLOCKs
	LOGICAL_READS_PER_CALL    DEFAULT  --db bLOCKs
	PRIVATE_SGA                   20M  --
	FAILED_LOGIN_ATTEMPTS           3  --
	PASSWORD_LIFE_TIME             30  --days
	PASSWORD_REUSE_TIME            12  --days
	PASSWORD_REUSE_MAX      UNLIMITED  --
	PASSWORD_LOCK_TIME        DEFAULT  --days
	PASSWORD_GRACE_TIME             2  --days
	PASSWORD_VERIFY_FUNCTION     NULL  --
/

--修改概要（配置）文件
ALTER PROFILE appl_profile LIMIT
	CPU_PER_CALL DEFAULT;
--删除概要（配置）文件
DROP PROFILE appl_profile;
--如要删除的概要（配置）文件已经分配给用户，则必须利用下面语句进行删除
DROP PROFILE appl_profile CASCADE;

--在创建用户时可以指定概要（配置）文件，如不指定，则使用默认概要（配置）文件
CREATE USER zhao IDENTIFIED BY zhao PROFILE appl_profile;
--如需要改变用户的概要（配置）文件
ALTER USER user_name PROFILE perfile_name;

--DBA用户可以修改默认概要（配置）文件，但任何用户都不能删除概要（配置）文件
ALTER PROFILE DEFAULT LIMIT CPU_PER_CALL 2;
----------------------------------------------------------------------------------End//