/*---------------------------------------------------------------------------------------------
Author : 赵方方
Name : Oracle 数据开发.SQL
Date : 2006-10-1
Condition : Windows XP Professional SP2 + Oracle 9i(9.2.0.1.0)
Description : Oracle 9i 开发代码
Explain : 全文关键字均为大写,--...及/*...*/为注释。下面是Oracle 9i默认安装的用户
system/manager --SYSDBA 或 NORMAL ,默认系统系统管理员
sys/change_on_install --SYSDBA 或 NORMAL ,默认系统系统管理员
scott/tiger --NORMAL 普通用户
---------------------------------------------------------------------------------------------*/
--[1]// Oracle Basic 1
---------------------------------------------------------------------------------------------//
--可在登陆时转换登陆身份(转换为system登陆)
CONNECT system/Manager
--回滚事务(返回上一次操作)
--ROLLBACK;
--提交事务
COMMIT;
--查看表结构
DESC table_name;
--查询表中数据
SELECT * FROM table_name;
--或指定需返回的字段
SELECT id,name FROM table_name;
--显示当前用户
SHOW USER;
--查看当前用户所拥有的表的结构
DESC USER_TABLES;
--查看当前用户所拥有的表
SELECT table_name FROM USER_TABLES;
--查看当前用户能够访问的表的结构
DESC ALL_TABLES;
--查看当前用户能够访问的表
SELECT table_name FROM ALL_TABLES;
--查看整个数据库所拥有的表的结构
DESC DBA_TABLES;
--查看整个数据库所拥有的表
SELECT table_name FROM DBA_TABLES;
--查看数据库版本
SELECT * FROM V$VERSION;
--查看数据库实例名称
SELECT instance_name FROM V$INSTANCE;
--查看数据文件的物理位置
SELECT * FROM V$DATAFILE;
--查看日志文件的物理位置
SELECT * FROM V$LOGFILE;
--查看控制文件的物理位置
SELECT * FROM V$CONTROLFILE;
--查看表空间的信息
SELECT * FROM V$TABLESPACE;
--查看数据库参数信息
--下面列出几种参数
--简单查询方法
SHOW PARAMETER DB_BLOCK_SIZE;
SHOW PARAMETER NLS_LANGUAGE;
SHOW PARAMETER JAVA_POOL_SIZE;
SHOW PARAMETER SORT_AREA_SIZE;
--通过视图查询
SELECT * FROM V$PARAMETER WHERE NAME='JAVA_POOL_SIZE';
SELECT name,value FROM V$PARAMETER WHERE NAME='JAVA_POOL_SIZE';
--修改参数值
--!UPDATE V$PARAMETER SET VALUE='500000' WHERE name='JAVA_POOL_SIZE';
--附注-------------------------------------------------------------//
SPOOL d:/file.txt; --联机
SPOOL OFF; --关闭(将联机后查询到的数据保存在d盘file.txt文件下)
--命令方式打开和关闭数据库
--以sysdba身份登陆数据库
CONNECT /AS SYSDBA;
--关闭数据库
SHUTDOW IMMEDIATE;
--启动数据库
STARTUP;
--清除屏幕
CLEAR SCREEN;
--预定义格式打印系统当前日期
SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
--可以利用脚本执行SQL语句(d:/test.sql)
@d:/test.sql;
--编辑当前SQL语句
EDIT;
--保存当前SQL语句
SAVE d:/test.sql;
--退出数据库
EXIT;
------------------------------------------------------------------------------------------End//
--[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 PRIMARY KEY, --主键约束
name VARCHAR2(20),
sex VARCHAR2(3),
age NUMBER,
address VARCHAR2(50)
);
--表2
CREATE TABLE score
(
id NUMBER NOT NULL, --非空
clanguage NUMBER(5,2), --共五位数字,小数点占用两位
java NUMBER(5,2),
vb NUMBER(5,2),
asp NUMBER(5,2)
);
--为上面创建的表插入数据
INSERT INTO student
VALUES('2','JianLi','F','20','ShangHaiShi YangPu');
--也可为指定列插入
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//