一、oracle数据库结构
服务器参数文件oracle\product\10.1.0\Db_2\database\spfile*.ora
oracle\10.1.0\oradata\
1.参数文件
2.*.CTL 控制文件
3.*.DBF 数据文件
4.*.LOG 日志文件
c:\>sqlplus "sys/password as sysdba"登录系统
SQL>DESC v$controlfile描述控制文件
SQL>SELECT status,name FROM v$controlfile查看控制文件
SQL>DESC v$datafile描述数据文件
SQL>SELECT file#,status,name FROM v$datafile查看数据文件
SQL>DESC v$logfile描述日志文件
SQL>SELECT member FROM v$logfile查看日志文件
ORACLE内存结构
1.SGA总称
2.DB buffer数据缓存区
(默认缓存池，保持缓存池，再生缓存池)
3.Redo buffer日志缓存区
4.共享池
(库缓存区：共享SQL区、PL/SQL区，字典缓存区)
5.大共享池
6.固定SGA
ORACLE逻辑结构
表空间tablespace(块block、盘区extent、段segment)
表空间tablespace(数据文件datafile)
二、sqlplus的使用方法
isqlplus打开方式http://主机名:oracle访问端口/isqplus
isqlplus登录名:scott 密码:tiger
c:\>sqlplus "sys/password as sysdba"登录
c:\>sqlplus/nolog
SQL>connect sys/password as sysdba
SQL>startup
SQL>archive log list
SQL>startup mount正常启动
SQL>alter database archivelog
SQL>alter database open
SQL>startup nomount丢失恢复启动
SQL>shutdown immediate正常关闭
SQL>shutdown等待关闭
SQL>shutdown transactional等待立即关闭
SQL>shutdown abort强制关闭
SQL>help index查看sqlplus帮助命令
SQL>SELECT * FROM dept;
SQL>SET SQLBLANKLINES ON设置sqlplus行输入连接为开
SQL>SELECT * FROM dept WHERE deptno=&tt设置输入参数
Enter value for tt:
SQL>list查看输入缓存区的命令，或者只输入l
SQL>l2 4查看缓存区第二行到第四行的输入
SQL>change修改刚刚输入的语句，或者只输入c
SQL>c/N/M用M替换N
SQL>/执行缓存区里的命令
SQL>? CHANGE说明某个命令的用法，比如CHANGE
SQL>DEL 4删除缓存区某一行的命令，比如第四行
SQL>DEL 2 3
SQL>save c:\oracle\test.txt将缓存区中的命令保存到文件中
SQL>get c:\oracle\test.txt获取文件中的命令
SQL>edit 调用记事本编辑刚刚输入的语句
SQL>COLUMN deptno HEADING "编号"为查询的某一列别名显示，或者写成COL方法
SQL>SELECT * FROM dept;
SQL>DESCRIBE用于描述一个对象，或者写成DESC
SQL>DESC dept比如描述dept表的结构
SQL>COL dname FORMAT A10为某列格式化显示
SQL>COL dname FORMAT A1O HEADING "部门名称"
SQL>SELECT * FROM dept;
SQL>exit退出sqlplus模式，退出后缓存中的别名列自动消失
SQL>sqlplus scott/tigger，通过scott方式登录，不需要引号
SQL>COL deptno FORMAT 999,999,999格式化显示
SQL>connect "sys/test1234 as sysdba"通过sysdba查看数据文件的大小，以999,999,999格式显示
SQL>SELECT byte FORM v$datafile
SQL>SELECT byte,name FROM v$datafile
SQL>connect scott/tiger
SQL>SET LINESIZE 50一次查看多少列
以下是为查看数据设置左、中、右标题
SQL>TTITLE CENTER "我的标题" SKIP 1-
>LEFT "测试报表" RIGHT "页"-
>FOTMAT 999 SQL.PNO SKIP 2
SQL>SELECT * FROM dept;
SQL>BREAK ON pub在某一列上相同值不显示，如在pub列上
SQL>COMPUTE COUNT LABEL "计数" OF books_name ON pub
查询pub列名称相同的books_name的统计结果，显示为计数
SQL>spool c:\oracle\spool.txt将查询结果保存到文件中，表示开始点
以下是要查询保存的语句，将查询结果保存到spool.txt中
SQL>SELECT * FROM books;
SQL>spool off表示查询结果保存结束点
SQL>edit c:\oracle\spool.txt用记事本打开spool.txt文件，查看保存是否成功
二、结构化查询语句
1.DDL
CREATE
ALTER
DROP
2.DCL
GRANT
REVOKE
3.DML
SELECT
INSERT
DELETE
UPDATE
SQL>connect scott/tiger
SQL>CREATE TABLE abc(a varchar2(10), b char(10));
SQL>ALTER TABLE abc ADD c number;
SQL>ALTER TABLE abc DROP COLUMN c;
SQL>connect tt/tt11
SQL>SELECT * FROM scott.dept;
SQL>conn scott/tiger
SQL>GRANT SELECT ON dept TO tt;
SQL>conn tt/tt11
SQL>SELECT * FROM scott.dept;
SQL>conn scott/tiger
SQL>SELECT * FROM abc
SQL>INSERT INTO abc(a,b) VALUES('abc','xy')
SQL>INSERT INTO abc VALUES('bcd','123')
SQL>UPDATE abc SET b='ttt'
SQL>UPDATE abc b='YYY' WHERE a='abc';
SQL>DELETE FROM abc WHERE a='abc';
SQL>SELECT * FROM abc;
常用系统函数
1.字符
SQL>SELECT LENGTH('ABCDEF') FROM dual;
SQL>SELECT LTRIM(' ABCDEF') FROM dual;
SQL>SELECT RTRIM('ABCDEF ') FROM dual;
SQL>SELECT TRIM(' ABCDEF ') FROM dual;
SQL>UPDATE aa SET a3='aa';
SQL>SELECT LENGTH(A2),LENCTH(A3) FROM aa;
SQL>SELECT SUBSTR('abcdefg',2,3) FROM dual;
SQL>SELECT SUBSTR('sbcdefg',LENGTH('qbcdefg')-3+1,3) FROM dual;
REPLACE
2.日期
SQL>SELECT sysdate FROM dual;
SQL>SELECT current_date FROM dual;
SQL>ALTER SESSION SET NLS_DATE_FORMAT='dd-mon-yyyy hh:m1:ss';
SQL>SELECT NEXT_DAY(sysdate,'星期三') FROM dual;
3.转换
SQL>SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') FROM dual;
SQL>SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM dual;
SQL>SELECT TO_DATE('12-3月-04') FROM dual;
SQL>SELECT TO_NUMBER('00333') FROM dual;
4.聚集函数
SQL>SELECT * FROM books
SQL>SELECT max(price) FROM books;
SQL>SELECT min(price) FROM books;
SQL>SELECT sum(price) FROM books;
SQL>SELECT avg(price) FROM books;
SQL>SELECT count(price) FORM books;
SQL>SELECT count(*) FROM books;
SQL>SELECT * FROM books WHERE price>20;
5.其他
SQL>SELECT user FROM dual;
SQL>connect tt/tt11;
SQL>connect scott/tiger;
SQL>SELECT * FROM e;
SQL>SELECT SUM(DECODE(SEX,'男',1,0)) 男人数,SUM(DECODE(SEX,'女',1,0) 女人数 FROM e;判断计数，为真加1，否则加零
SQL>SELECT a1,nvl(a2,'未输入') a2 FROM aa;对空值处理，填充'未输入'
SQL>SELECT * FROM aa WHERE a2=NULL;
SQL>SELECT * FROM aa ORDER BY a1 ASC;
SQL>SELECT * FROM aa ORDER BY a1 DESC;
SQL>SELECT DISTINCT a1 FROM aa;
SQL>SELECT ALL a1 FROM aa;
分组语句
SQL>SELECT * FROM books;
SQL>SELECT sum(price*qty) FROM books
SQL>SELECT sum(price*qty) FROM books GROUP BY pubs;
SQL>SELECT pub,books_name,sum(price*qty) FROM books GROUP BY pubs,books_name;
SQL>SELECT pub,sum(price*qty) FROM books GROUP BY pub,books_name;
SQL>SELECT pub,sum(price*qty) FROM books WHERE price>30 GROUP BY pubs;
SQL>SELECT pub,sum(price*qty) FROM books GROUP BY pub HAVING sum(price)>60;
SQL>SELECT a1,count(a1) FROM aa GROUP BY a1 HAVING COUNT(a1)>1;
模糊查询
SQL>SELECT * FROM aa;
SQL>SELECT * FROM aa WHERE a1 LIKE 'a_';
SQL>SELECT * FROM aa WHERE a1 LIKE 'a%';
SQL>SELECT * FROM aa WHERE a1 LIKE '_a';
SQL>SELECT * FROM aa WHERE a1 LIKE '%a';
SQL>SELECT * FROM aa WHERE a1 LIKE '%a%';
连接查询
SQL>SELECT * FROM e;
SQL>SELECT * FROM d;
SQL>SELECT eid 编号,ename 姓名,sex 性别,d.name 所在部门 FROM employee e,department d WHERE e.id=d.id;
内连接
SQL>SELECT eid 编号,ename 姓名,sex 性别,d.name 所在部门 FROM employee e join department d on e.id=d.id;
以下为外连接
SQL>SELECT eid 编号,ename 姓名,sex 性别,d.name 所在部门 FROM employee e join department d on e.id=d.id(+);
左连接，以左边为主，右边为辅
SQL>SELECT eid 编号,ename 姓名,sex 性别,d.name 所在部门 FROM employee e join department d on e.id(+)=d.id;
右连接，以右边为主，左边为辅
子查询
无关子查询
SQL>SELECT * FROM e WHERE id IN (SELECT id FROM d);
相关子查询，与父表有关
SQL>SELECT * FROM e WHERE id IN (SELECT id FROM d WHERE id=e.id AND id='03');
用IN的方式，子查询的字段要与父查询的条件匹配
判断子查询结果是否存在，存在就执行父查询
SQL>SELECT * FROM e WHERE EXISIS (SELECT id FROM d)
SQL>SELECT * FROM e WHERE NOT EXISIS (SELECT * FROM d WHERE id=e.id);
联合查询
SQL>SELECT e.id,ename FROM e
UNION
SELECT id,name FROM d;
SQL>SELECT id FROM e
UNION
SELECT id FROM d;
返回两个SQL语句中都出现的行,ORACLE数据库特有
SQL>INTERSECT
SQL>SELECT id FROM e
INTERSECT
SELECT id FROM d;
将一个表的查询结果，插入到另外一张表中
SQL>INSERT INTO e(eid,ename) SELECT id,name FROM d;
将一个表的查询结果，插入到新建的一张表中
SQL>CREATE TABLE ttt AS (SELECT * FROM e);
SQL>CREATE TABEL t AS SELECT eid,ename FROM e WHERE eid='001';
SQL>SELECT * FROM t;
四 PL/SQL
PL/SQL块结构
DECLARE
BEGIN
EXCEPTION 异常处理
END
变量声明，命名规则
变量由字符开头，可以包含：数字，下划线，$，#
不能是系统关键字
SQL>DECLARE
2 x varchar2(10);
3 BEGIN
4 x:='This is..'; 赋值的写法
5 DBMS_OUTPUT.PUT_LINE('x的值为：'||x) ||连接加号
6 END;
7 /
SQL> SET SERVEROUTPUT ON SIZE 10000 设置服务器输出大小
SQL> L 显示缓存中的语句
SQL> / PL/SQL执行语句
SQL>DECLARE
2 x varchar2(10);
3 BEGIN
4 x:='This is..'; 赋值的写法
5 DBMS_OUTPUT.PUT_LINE('x的值为：'||x) ||连接加号
6 END;
7 四 PL/SQL
PL/SQL块结构
DECLARE
BEGIN
EXCEPTION 异常处理
END
变量声明，命名规则
变量由字符开头，可以包含：数字，下划线，$，#
不能是系统关键字
SQL>DECLARE
2 x varchar2(10);
3 BEGIN
4 x:='This is..'; 赋值的写法
5 DBMS_OUTPUT.PUT_LINE('x的值为：'||x) ||连接加号
6 END;
7 /
SQL> SET SERVEROUTPUT ON SIZE 10000 设置PL/SQL服务器输出大小
SQL> L 显示缓存中的语句
SQL> / PL/SQL执行语句
SQL>DECLARE
2 x varchar2(10);
3 BEGIN
4 x:='This is..'; 赋值的写法
5 --DBMS_OUTPUT.PUT_LINE('x的值为：'||x); 注释掉了
DBMS_OUTPUT.PUT('x的值为：'||x);
DBMS_OUTPUT.NEW_LINE; 在一个新行显示
6 END;
7 /
多行注释用/* */
SQL>DECLARE
2 x varchar2(10);
y INTEGER:=123;
3 BEGIN
4 x:='This is..'; 赋值的写法
5 --DBMS_OUTPUT.PUT_LINE('x的值为：'||x); 注释掉了
DBMS_OUTPUT.PUT('x的值为：'||x||y);
DBMS_OUTPUT.NEW_LINE; 在一个新行显示
6 END;
7 /
SQL>save c:/plsql_01.txt 保存到文件
SQL>@ c:/plsql_01.txt 调用文件
SQL>DECLARE
2 x varchar2(10);
y string(10):=123; 必须给定长度string
3 BEGIN
4 x:='This is..'; 赋值的写法
5 --DBMS_OUTPUT.PUT_LINE('x的值为：'||x); 注释掉了
DBMS_OUTPUT.PUT('x的值为：'||x||y);
DBMS_OUTPUT.NEW_LINE; 在一个新行显示
6 END;
7 /
SQL>DECLARE
2 a number;
3 b varchar2(10);
4 BEGIN
5 a:=2;
6 IF a=1 THEN
7 b:='A';
8 ELSEIF a=2 THEN
9 b:='B';
10 ELSE
11 b:='C'
12 END IF;
13 DBMS_OUTPUT.PUT_LINE('b的值是：'||b);
14 END;
15 /
SQL>save c:/plsql_02.txt 保存到文件
SQL>@ c:/plsql_02.txt 调用文件
DECLARE
a number;
b varchar2(10);
BEGIN
a:=2;
CASE
WHEN a=1 THEN b:='A'
WHEN a=2 THEN b:='B'
WHEN a=3 THEN b:='C'
ELSE
b:='Others';
END CASE;
DBMS_OUTPUT.PUT_LINE('b的值是：'||b);
END;
/
循环语句
基本循环
SQL>DECLARE
x number;
BEGIN
x:=0;
LOOP
x:=0
LOOP
x:=x+1
IF x>3 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE('内：x='||x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('外：x='||x);
END;
/
内：x=1
内：x=2
外：x=3
SQL>save c:\plsql_loop01.txt
created file c:\plsql_loop01.txt
SQL>edit c:\plsql_loop01.txt
SQL>@ c:\plsql_loop01.txt
WHILE循环
SQL>DECLARE
x number;
BEGIN
x:=0;
LOOP
x:=0
LOOP
x:=x+1
EXIT WHEN x>3;
END IF;
DBMS_OUTPUT.PUT_LINE('内：x='||x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('外：x='||x);
END;
SQL>DECLARE
x number;
BEGIN;
x:=0;
WHILE x<3 LOOP;
x:=x+1;
DBMS_OUTPUT.PUT_LINE('内：x='||x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('外：x='||x);
/
内：x=1
内：x=2
内：x=3
内：x=4
外：x=4
SQL>save c:\plsql_loop03.txt
FOR循环
SQL>BEGIN
FOR i in 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('i='||i);
END LOOP;
DBMS_OUTPUT.PUT_LINE('END OF FOR LOOP');
END;
/
SQL>BEGIN
FOR i in REVERSE 1..5 LOOP从大到小循环
DBMS_OUTPUT.PUT_LINE('i='||i);
END LOOP;
DBMS_OUTPUT.PUT_LINE('END OF FOR LOOP');
END;
/
SQL>save c:\plsql_loop04.txt
GOTO语句，不建议用
DECLARE
x number;
BEGIN
x:=0;
<<repeat_loop>>
x:=x+1
DBMS_OUTPUT.PUT_LINE(x)
IF x<3 THEN
GOTO repeat_loop;
END IF;
SQL>save c:\plsql_loop05.txt
异常处理
DUP_VAL_ON_INDEX 向有唯一约束的表中插入重复行
NO_DATA_FOUND 在一个SELECT INTO语句中无返回值
TOO_MANY_ROWS SELECT INTO 语句返回多行
VALUE_ERROR 一个算法、转换、截断或大小约束发生错误
ZERO_DIVIDE 发生被零除
SQL>DECLARE
test varchar2(10)
BEGIN
SELECT name INTO test FROM deptment WHERE id='tt';
DBMS_OUTPUT.PUT_LINE(test);
END;
/
未查找到数据，系统异常
SQL>DECLARE
test varchar2(10)
BEGIN
SELECT name INTO test FROM deptment WHERE id='tt';
DBMS_OUTPUT.PUT_LINE(test);
EXCEPTION
WHEN NO_DATA_FOUND THEN 给定异常处理
DBMS_OUTPUT.PUTLINE('没有找到数据!')
END;
/
SQL>DECLARE
tname varchar2(10);
e exception;
BEGIN
SELECT name INTO tname FROM deptment WHERE id='01'
IF tname<>'B部门' THEN
RAISE e;
END IF;
DBMS_OUTPUT.PUT_LINE(tname);
EXCEPTION
WHEN e THEN
DBMS_OUTPUT.PUT_LINE('错误，不是需要的B部门!');
END;
/
复合变量
SQL>DECLARE
TYPE myrecord is RECORD(
id varchar2(10),
name varchar2(10));
real_record myrecord;
BEGIN
SELECT emp_id,emp_name INTO real_record FROM emp WHERE emp_id='001';
DBMS_OUTPUT.PUT_LINE(real_record.id||','||real_record.name);
END;
/
SQL>DESC emp;
SQL>EDIT
SQL>DECLARE
TYPE myrecord is RECORD(
id emp.eid%TYPE,与表中的字段类型一致
name varchar2(10));
real_record myrecord;
BEGIN
SELECT emp_id,emp_name INTO real_record FROM emp WHERE emp_id='001';
DBMS_OUTPUT.PUT_LINE(real_record.id||','||real_record.name);
END;
/
SQL>DECLARE
myrec emp%ROWTYPE;复合类型的子类型与表中字段相同
BEGIN
SELECT * INTO myrec FROM emp WHERE emp_id='001';
DBMS_OUTPUT.PUT_LINE(myrec.eid||','||myrec.ename||','||myrec.sex);
END;
/
五 游标
SQL>DECLARE
CURSOR mycur IS
SELECT * FROM books;
myrecord books%ROWTYPE;
BEGIN
OPEN mycur;打开游标
FETCH mycur INTO myrecord;把游标记录放到结果集中
WHILE mycur%FOUND LOOP 如果游标记录不为空，将记录循环显示出
DBMS_OUTPUT.PUT_LINE(myrecord.books_id||','||myrecord.books_name);
FETCH mycur INTO myrecords;把游标记录放到结果集中，游标进了一步
END LOOP;
CLOSE mycur;
END;
/
SQL>DECLARE
CURSOR cur_para(id varchar2) IS
SELECT books_name FROM books WHERE books_id=id;
t_name books.books_name%TYPE;
BEGIN
OPEN cur_para('0001');
LOOP
FETCH cur_para INTO t_name;
EXIT WHEN cur_para%NOTFOUND;当游标中的记录不存在时
END LOOP;
CLOSE cur_para;
END;
/
SQL>DECLARE
CURSOR cur_para(id varchar2) IS
SELECT books_name FROM books WHERE books_id=id;
BEGIN
DBMS_OUTPUT.PUT_LINE('******结果集为：******')
FOR cur IN cur_para('0001') LOOP 显示books_id等于'0001'的记录
DBMS_OUTPUT.PUT_LINE(cur.books_name);
END LOOP;
END;
/
SQL>DECLARE
t_name books.books_name%TYPE;
CURSOR cur(id varchar2) IS
SELECT books_name FROM books WHERE books_id=id;
BEGIN
IF cur%ISOPEN THEN 判断游标是否打开了
DBMS_OUTPUT.PUT_LINE('游标已经被打开!');
ELSE
OPEN cur('0003'); 如果没有打开，打开book_id等于'0003'的记录
END IF;
FETCH cur INTO t_name;
CLOSE cur;
DBMS_OUTPUT.PUT_LINE(t_name);
END;
/
SQL>DECLARE
t_name varchar2(10);
CURSOR mycur IS
SELECT name FROM deptment;
BEGIN
OPEN mycur;
LOOP
FETCH mycur INTO t_name;
EXIT WHEN mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE('游标mycur的ROWCOUNT是：'||mycur%ROWCOUNT);用于游标的统计
END LOOP;
CLOSE mycur;
END;
/
游标参数
%FOUND
%ISOPEN
%NOTFOUND
%ROWCOUNT
利用游标来修改数据
SQL>DECLARE
CURSOR cur IS
SELECT name FROM deptment FOR UPDATE;
text varchar2(10);
BEGIN
OPEN cur;
FETCH cur INTO text;
WHILE cur%FOUND LOOP
UPDATE deptment SET name=name||'_t' WHERE CURRENT OF cur;
FETCH cur INTO text;
END LOOP;
CLOSE cur;
END;
/
隐式游标声明
SQL>BEGIN
FOR cur IN(SELECT name FROM deptment) LOOP
DBMS_OUTPUT.PUT_LINE(cur.name);
END LOOP;
END;
/
数据量大的时候不建议使用游标
PL/SQL高级应用存储过程
SQL>CREATE OR REPLACE PROCEDURE myproc (id IN varchar2)
IS
name varchar2(10);
BEGIN
SELECT books_name INTO name FROM books WHERE books_id=id;
DBMS_OUTPUT.PUT_LINE(name);
END myproc;
/
IN 表示是输入参数
OUT 表示是输出参数
查询创建的过程编译错误
SQL>SHOW ERRORS PROCEDURE myproc;
用存储过程执行
SQL>DECLARE
tid varchar2(10);
BEGIN
tid:='0001';
myproc(tid);
END;
/
用执行语句执行
SQL>EXECUTE myproc('0001');
SQL>CREATE OR REPLACE PROCEDURE myproc2(id IN varchar2,
name OUT varchar2)
IS
BEGIN
SELECT books_name INTO name FROM books WHERE books_id=id;
END;
/
SQL>DECLARE
tid varchar2(10);
tname varchar2(10);
BEGIN
tid:='0001';
myproc2(tid,tname);
六 视图
SQL>CREATE OR REPLACE VIEW myview
AS
SELECT * FROM books;
SQL>SELECT * FROM myview;
SQL>INSERT INTO myview(books_id) VALUES('0008')
SQL>CREATE OR REPLACE VIEW myview
AS
SELECT * FROM books WHERE price>30
SQL>INSERT INTO myview VALUES('0009','ABCDE',23.5,'AA')
SQL>CREATE OR REPLACE VIEW myview
AS
SELECT * FROM books WHERE price>30
WITH CHECK OPTION 检查在插入时price>30不能插入数据，只对视图起作用
SQL>SELECT * FROM deptment;
SQL>SELECT eid,ename,sex,d.id,d.name FROM emp e,deptment d
WHERE
e.id=d.id;
SQL>CREATE OR REPLACE VIEW v_emp_dept
AS
SELECT eid,ename,sex,d.id,d.name FROM emp e,deptment d
WHERE
e.id=d.id;
SQL>INSERT INTO v_emp_dept VALUES('007','ABC','33','TT');
如果组成视图的基表两个，两个以上，不允许同时对两个和两个以上的表更新
SQL>CREATE OR REPLACE VIEW v_read
AS
SELECT eid,ename FROM emp
WITH READ ONLY; 设置视图为只读
DISTINCT情况下也是不能更新的，因为是多条，这样的视图也是不能更新的
SQL>DESC dba_views 查看所有视图
SQL>DESC user_views 查看当前用户的视图
SQL>SELECT text FROM user_views WHERE view_name='V_READ';
用数据字典来查看视图的语法，语句
同义词 DBA创建公用同义词 一般用户创建专有同义词
SQL>SELECT user FROM dual;
SQL>SELECT * FROM dept;
SQL>SELECT * FROM scott.dept;
创建同义词
SQL>CREATE SYNONYM dept FOR scott.dept;
SQL>connect tt/tt11
SQL>SELECT * FROM dept;
SQL>connect sys/test1234 as sysdba
SQL>DROP SYNONYM dept
创建公有同义词
SQL>CREATE PUBLIC SYNONYM dept FOR scott.dept;
SQL>SELECT * FROM dept;
SQL>connect tt/tt11
SQL>SELECT * FROM dept;
SQL>DESC dba_synonyms查看同义词的数据字典
SQL>CONNECT tt/tt11
SQL>CREATE SYNONYM dep FOR scott.dept;
SQL>SELECT * FROM dept;
SQL>desc user_synonyms
SQL>SELECT synonym_name,table_name,table_owner FROM
user_synonyms;
序列
创建序列
SQL>CREATE SEQUENCE myseq
START WITH 1 开始处为1
INCREMENT BY 1 增长为1
ORDER 默认排序 从小到大
NOCYCLE; 不循环
查询下一个序列的值
SQL>SELECT myseq.NEXTVAL FROM dual;序列为1
SQL>SELECT myseq.NEXTVAL FROM dual;序列为2
SQL>SELECT myseq.NEXTVAL FROM dual;序列为3
查看当前序列的值
SQL>SELECT myseq.CURRVAL FROM dual;必须写完NEXTVAL的查询，
才能查询CURRVAL
创建一个表
SQL>CREATE TABLE auto(a number,varchar2(10));
执行三条查询，当前插入的序列就从4开始
SQL>INSERT INTO auto VALUES(myseq.NEXTVAL,'dfd');序列为4
SQL>INSERT INTO auto VALUES(myseq.NEXTVAL,'dfd');序列为5
SQL>INSERT INTO auto VALUES(myseq.NEXTVAL,'dfd');序列为6
SQL>SELECT * FROM auto;
SQL>DESC dba_sequences 查看的序列的数据字典
SQL>SELECT sequence_name,sequence_owner FROM dba_sequence
WHERE sequence_owner='TT'
SQL>SELECT user FROM dual;查看当前登录用户名称
SQL>ALTER SEQUENCE myseq INCREMENT BY 3;更改序列增长为3
七 触发器
SQL>SELECT * FROM books;
SQL>DELETE FROM books WHERE books_id='0011'
提交
SQL>commit;
SQL>DELETE FROM books WHERE books_id='0010'
回滚
SQL>rollback;
SQL>DELETE FROM books WHERE books_id='00d10';
事务的特性：原子性、一致性、隔离性、永久性
SQL>SELECT * FROM emp;
SQL>SELECT * FROM deptment;
SQL>CREATE OR REPLACE TRIGGER del_deptid
AFTER DELETE ON deptment
FOR EACH ROW 行级触发器
BEGIN
DELETE FROM emp WHERE id=:old.id;一种特殊的方式，强记，逻辑表
END del_deptid;
/
SQL>DELETE FROM deptment WHERE id='01'
SQL>SELECT * FROM emp;
SQL>SELECT * FROM deptment;
SQL>CREATE OR REPLACE TRIGGER insert_dept
AFTER INSERT ON department
FOR EACH ROW
BEGIN
INSERT INTO emp(eid,ename,id) VALUES ('121','QWERT','new.id');一种特殊的方式，强记，逻辑表
END;
/
SQL>INSERT INTO deptment VALUES('77','GHJ');
SQL>SELECT * FROM emp;
OLd New
INSERT 无 有
DELETE 有 无
UPDATE 有 有
SQL>CREATE OR REPLACE TRIGGER update_dept
AFTER UPDATE ON deptment
FOR EACH ROW
BEGIN
UPDATE emp SET id=:new.id WHERE id=:old.id
END;
/
SQL>UPDATE department SET id='yy' WHERE id='01';
SQL>SELECT * FROM emp;
SQL>CREATE OR REPLACE TRIGGER books_delete
AFTER DELETE ON books
FOR EACH ROW
BEGIN
IF:old.book_id='0001' THEN
RAISE_APPLICATION_ERROR(-20000,'不允许删除！');
END IF;
END;
/
SQL>SELECT * FROM books;
SQL>DELETE FROM books WHERE books_id='0009'
SQL>DELETE FROM books WHERE books_id='0001'
触发器里不能写rollback,DBMS.OUTPUT_LINE
SQL>SELECT * FROM aa;
SQL>CREATE TABLE mylog(curr_user varchar2(100),curr_date,act char(1));
SQL>CREATE OR REPLACE TRIGGER dm1_aa
AFTER INSERT OR DELETE OR UPDATE ON aa
BEGIN
IF INSERTING THEN 语句级的触发器
INSERT INTO mylog VALUES(user,sysdate,'I')
ELSEIF DELETING THEN
INSERT INTO mylog VALUES(user,sysdate,'D')
ELSE
INSERT INTO mylog VALUES(user,sysdate,'U')
END IF;
END;
/
SQL>SELECT * FROM aa;
SQL>UPDATE aa SET a2='test' WHERE a1='cha';
SQL>SELECT * FROM aa;
SQL>DELETE FROM aa WHERE a2='dee';
SQL>INSERT INTO aa VALUES('tt','yy','uu')
SQL>SELECT * FROM mylog;
触发器日期
SQL>SELECT curr_user,TO_CHAR(curr_date,'yyyy-mm-dd' hh24:mi:ss')
D FROM mylog;
SQL>SELECT * FROM auto;
SQL>SELECT myseq.NEXTVAL FROM dual;
SQL>SELECT myseq.NEXTVAL FROM dual;
SQL>INSERT INTO auto VALUES(myseq.nextval,'SD');
SQL>SELECT * FROM auto;
SQL>CREATE OR REPLACE TRIGGER set_no
BEFORE INSERT ON auto
FOR EACH ROW
DECLARE
sn number(5);
BEGIN
SELECT myseq.nextval INTO sn FROM dual;
:NEW.a:=sn;用序列来代替插入的NEW.a的值
END;
/
SQL>INSERT INTO auto VALUE(21,'df');
SQL>INSERT INTO auto VALUE(2321,'ddtt');
SQL>SELECT * FROM auto;
替换触发器
SQL>INSERT INTO v_emp_dept VALUE('456','test','f','33','hg');
在ORACLE中替换触发器只能用在视图上，用于解决更新两个和两个以上的基表
SQL>CREATE OR REPLACE TRIGGER tr_v_e_d
INSTEAD OF INSERT ON v_emp_dept
FOR EACH ROW
BEGIN
INSERT INTO deptment VALUES(:new.id,:new.name);
INSERT INTO emp(eid,ename,sex,id) VALUES (:new.eid,:new.ename,
:new.sex,:new.id)
END;
/
SQL>INSERT INTO v_emp_dept VALUE('456','test','f','33','hg');
SQL>SELECT * FROM v_emp_dept;
八 安全管理
ORA_DBA Administrator
http://localhost:5500/em Oracle Manager System
创建用户 显示SQL
SQL> connect test/test1234
SQL> SELECT * FROM scott.dept;
SQL> connect sys/test1234 as sysdba
设置权限
SQL> GRANT SELECT ON scott.dept TO test;
改变连接方式
SQL> conn / as sysdba
改变默认表空间
SQL> ALTER USER test DEFAULT TABLESPACE tt;
SQL> ALTER USER test DEFAULT TABLESPACE users;
更改密码
SQL> ALTER USER test IDENFIFED BY test11;
SQL> conn test/test11
SQL> conn / as sysdba
锁定帐号
SQL> ALTER USER test ACCOUNT LOCK;
解除锁定
SQL> ALTER USER test ACCOUNT UNLOCK;
SQL> CREATE USER qqq IDENTIFIED BY qqq123
DEFAULT TABLESPACE tt;
SQL> connect qqq/qqq123
SQL> conn / as sysdba
SQL> GRAINT CONNECT TO qqq;
SQL> SELECT * FROM scott.dept;
SQL> GRANT SELECT ON scott.dept TO qqq;
SQL> conn / as sysdba
给予授权的权限
SQL> GRANT SELECT ON scott.dept TO test WITH GRANT OPTION
SQL> connect test/test11
SQL> GRANT SELECT ON scott.dept TO qqq;
SQL> GRANT INSERT ON scott.dept TO test WITH GRANT OPTION
包括INSERT、DELETE、UPDATE
SQL> GRANT ALL ON scott.dept TO test WITH GRANT OPTION
对过程进行授权
SQL> GRANT EXECUTE ON tt.proc01 TO test
SQL> conn test/test11
SQL> CREATE USER abc IDENTIFIED BY abc;
SQL> conn / as sysdba
创建用户的权限
SQL> GRANT CREATE USER TO test;
SQL> conn test/test11
SQL> DROP USER abc
SQL> conn / as sysdba
删除用户的权限
SQL> GRANT DROP USER TO test;
SQL> conn test/test11
SQL> DROP USER abc;
SQL> conn / as sysdba
SQL> GRANT CREATE USER TO test;
SQL> GRANT CREATE USER TO qqq;
对象授权
SQL> GRANT CREATE USER TO test WITH GRANT OPTION;
系统授权
SQL> GRANT CREATE USER TO test WITH ADMIN OPTION;
SQL> GRANT CREATE USER TO qqq;
SQL> conn qqq/qqq123;
SQL> CREATE USER ty IDENTFIED BY ty;
取消授权
SQL> REVOKE SELECT ON scott.dept FROM test;
SQL> REVOKE CREATE USER FROM test;
角色授权
创建角色
SQL> CREATE ROLE myrole;
分配角色
SQL> GRANT SELECT ON tt.emp TO myrole;
SQL> GRANT SELECT ON tt.deptment TO myrole;
SQL> connect test/test11
SQL> SELECT * FROM tt.emp;
SQL> conn / as sysdba
将角色给用户，或者说添加用户到角色
SQL> GRANT myrole TO test;
SQL> connect test/test11
SQL> SELECT * FROM tt.emp;
SQL> conn / as sysdba
SQL> GRANT myrole TO qqq;
SQL> connect qqq/qqq123
SQL> SELECT * FROM tt.emp;
ORACLE Enterprise Manager 概要文件 创建概要文件，授权给用户
九 表空间的管理
SQL> SELECT user FROM dual;
新建一个表空间
SQL> CREATE TABLESPACE tabs
DATAFILE 'C:\oracle\product\10.1.0\oradata\test\tabs.dbf'
SIZE 10M;
为用户设置默认表空间
SQL> ALTER USER test DEFAULT TABLESPACE tabs;
SQL> connect test/test11
SQL> CREATE TABLE tre(t char(10),re number);
SQL> conn / as sysdba
授予创建表空间的权限
SQL> GRANT UNLIMITED TABLESPACE,DBA TO test;
SQL> connect test/test11
SQL> CREATE TABLE tre(t char(10),re number);
创建一个表放到默认表空间中
SQL> CREATE TABLE ty(t char(10),y varchar2(10)) TABLESPACE TT;
十 表的管理
实体完整性，主键约束，域完整性是指插入数据不能大于类型的约束
SQL> SELECT * FROM emp;
SQL> INSERT INTO emp(eid,ename) VALUE ('001','test22');
建立主键约束
SQL> ALTER TABLE nn ADD CONSTRAINT pk_nn PRIMARY KEY(n1);
参照完整性,参照表需要被参照表的字段
SQL> SELECT * FROM nn;
SQL> CREATE TABLE mm(m1 char(10),m2 varchar2(10),n1 varchar2(10)).
SQL> INSERT INTO mm VALUE('avd','dd','gg');
SQL> SELECT * FROM mm;
SQL> ALTER TABLE mm ADD CONSTRAINT fk_mm FOREGIN KEY(n1)
REFERENCE nn(n1);
check约束
SQL> DESC emp
SQL> UPDATE emp SET sex='男' WHERE eid='001'
SQL> ALTER TABLE emp ADD CONSTRAINT ck_emp_sex CHECK(sex='男'
or sex='女');
SQL> DESC dba_constraints查看一个表的约束情况
查看一个表的具体的约束
SQL> SELECT constraint_name,constraint_type FROM user_constraints
WHERE table_name='EMP';
SQL> desc emp
索引，索引是建立在表字段上的
在表上的字段上建立索引
SQL> CREATE INDEX my_mm_idx ON mm(m1);
位图索引
SQL> SELECT * FROM emp;
SQL> CREATE bitmap INDEX bit_emp ON emp(sex);
唯一约束的索引
SQL> CREATE UNIQUE INDEX myidx ON mm(m2);
十一 SQL LOAD
SQL>exit
SQL>sqlldr
SQL>
记事本创建两个文件
1.数据文件
abc,xyz
def,qqq
fff,ggg
save as c:\loader.txt
2.控制文件
load data
infile 'c:\loader.txt'
append
into table mm(
m1 position(1:3) char,
m2 position(5:7) char)
save as c:\cont.ctl
SQL>sqlldr tt/tt11 control=c:\cont.ctldata=c:\loader.txt通过控制文件写入数据
SQL>sqlplus tt/tt11
SQL>SELECT * FROM mm;
load data
infile 'c:\loader.txt'
append
into table mm(
m1 lterminated by ",",
m2 char terminated by ",")如果不是固定长度，用","区分
SQL>sqlplus tt/tt11
SQL>SELECT * FROM mm;
十二 OEM的配置
Enterprise Manger 企业管理工具(IE登录方式)
sys SYSDBA方式登录
性能、管理、维护
十三 监听程序及服务的配置
TCP/IP原理 1521端口
windows管理 服务 ORACLE Listener
c:\>lsnrctl status 状态
c:\>lsnrctl start 打开
c:\>lsnrctl stop 关闭
c:\>lsnrctl start lisnt_name 指定名称打开
Net Manager ORACLE网络管理程序
添加 监听程序 如listener1 指定监听端口号如1522
新加一个数据库名称
c:\>lsnrctl start listener1
c:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\listener.ora 监听程序的配置文件
Net Configuration Assistant 用数据库管理助手配置监听程序
c:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\lnsnames.ora
SQL>connect tt/tt11@ora ora为刚刚配置的监听服务
SQL>SELECT * FROM emp
十四 数据库备份与恢复
C:\>exp tt/tt11@test
Enter array fetch buffer size:4096>5000
Export file:EXPDAT.DMP>c:\mybak.dmp
<1>E<ntire database>, <2>U<sers>, or <3>T<ables>: <2>U >deptment dept
EXP-00012:deptme?...is not a valid export mode
<1>E<ntire database>, <2>U<sers>, or <3>T<ables>: <2>U >t
Export table data <yes/no>: yes > yes
Compress extents <yes/no>: yes > yes
Export done in ZHS16GBK charater set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path...
Table<T> or Partition<T:P> to be exported: <RETURN to quit> >
Export terminated successfully with warnings
详细导出
c:\>exp tt/tt11@test
>5000
>c:mybak.dmp
>yes
>
>t
>emp
恢复导入
c:\>sqlplus "tt/tt11";
SQL>SELECT * FROM CMP;
SQL>DELECT FROM emp;
SQL>COMMIT;
SQL>SELECT * FROM emp;
c:\>imp tt/tt11
>c:mybak.dmp
输入缓存区的大小>
依次选择yes or no
冷备份
SQL>shutdown immediate
选择oradata中的文件拷贝到备份目录下
SQL>startup mount
SQL>archive log list;查看归档日志
将数据库设置为日志归档方式
SQL>alter system set log_archive_start=true scope=spfile;
SQL>shutdown immediate;
6330
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
