C：\Adminstrator>?sqlplus "/as sysdba"?查看是否連接到數(shù)據(jù)庫(kù)

SQL> select status from?v$instance;

v$動(dòng)態(tài)表開(kāi)頭，查看動(dòng)態(tài)實(shí)例，open為啟動(dòng)。

SQL> shutdown immediate關(guān)閉數(shù)據(jù)庫(kù)

SQL>?startup?繼續(xù)啟動(dòng)

SQL>?show parameter db_name?查看數(shù)據(jù)庫(kù)名稱(chēng)

查詢(xún)數(shù)據(jù)庫(kù)SCOTT用戶默認(rèn)的狀態(tài)

SQL>?select username,account_status from dba_users where username='SCOTT';

SQL>?desc?user_tables;?描述

SQL>?alter?user ~scott數(shù)據(jù)庫(kù)用戶名~ account unlock;

SQL> show user查看當(dāng)前數(shù)據(jù)庫(kù)用戶

切換用戶 SQL>?conn?scott/dan?用戶名/密碼

?C：\Adminstrator> sqlplus scott/dan 進(jìn)入數(shù)據(jù)庫(kù)

oracle 命令 操作

內(nèi)容包括三大項(xiàng):
????1.oracle基本操作語(yǔ)句

????2.SQLServer基本操作語(yǔ)句

????3.各種數(shù)據(jù)庫(kù)連接方法

**************************************************oracle基本操作語(yǔ)句********************************************************
打開(kāi)服務(wù)器
net start oracleservicebinbo
打開(kāi)監(jiān)聽(tīng)器
lsnrctl start
關(guān)閉服務(wù)器
net stop oracleservicebinbo
關(guān)閉監(jiān)聽(tīng)器
lsnrctl stop
===============================================================
清屏
clear screen
****************************************************************
數(shù)據(jù)字典 ===========desc user_views(關(guān)鍵詞)
****************************************************************
===============================================================
查看當(dāng)前用戶的角色
SQL>select * from user_role_privs;
===============================================================
查看當(dāng)前用戶的系統(tǒng)權(quán)限和表級(jí)權(quán)限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
===============================================================
查看當(dāng)前用戶的缺省表空間
SQL>select username,default_tablespace from user_users;
===============================================================
換用戶
conn as sysdba
sys
tsinghua
sqlplus "sys/tsinghua as sysdba"
conn sys/zl as sysdba
===============================================================
修改表結(jié)構(gòu)
alter table test modify(name not null);
alter table test add(name varchar2(20));
alter table test drop column sex;
alter table test set unused column sex;
alter table test drop unused columns;
===============================================================
更改用戶密碼
sql>alter user 管理員 identified by 密碼；
===============================================================
創(chuàng)建表空間的數(shù)據(jù)文件
sql>create tablespace test datafile 'd:oraclebinbo.dbf' size 10m;
===============================================================
創(chuàng)建用戶
sql>create user 用戶名 identified by 用戶名;
===============================================================
bfile類(lèi)型實(shí)例
創(chuàng)建目錄
create directory tnpdir as 'c:';
刪除目錄
drop directory tnpdir
授權(quán)
grant read on directory tn pdir to scott;
建表
create table bfiletest(id number(3), fname bfile);
添加數(shù)據(jù)
insert into bfiletest values(1,bfilename('TMPDIR','tmptest.java'));
===============================================================
查看用戶
sql>show user
===============================================================
檢查語(yǔ)句是否有錯(cuò)
show error
===============================================================
鎖定用戶
sql>alter user 用戶名 account lock
===============================================================
解除用戶
sql>alter user 用戶名 account unlock
===============================================================
刪除用戶
sql>drop user zl;
===============================================================
給用戶創(chuàng)建表權(quán)限
sql>grant create table to 用戶名;
===============================================================
授管理員權(quán)限
sql>grant dba to 用戶名;
===============================================================
給用戶登錄權(quán)限
sql>grant connect to 用戶名
===============================================================
給用戶無(wú)限表空間權(quán)限
sql>grant unlinmited tablespace to 用戶名;
===============================================================
收回權(quán)限
sql>revoke dba from 用戶名;
===============================================================
查看用戶下所有的表
????????????SQL>select * from user_tables;
===============================================================
查看名稱(chēng)包含log字符的表
????????????SQL>select object_name,object_id from user_objects
????????????????where instr(object_name,'LOG')>0;
===============================================================
查看某表的創(chuàng)建時(shí)間
????????????SQL>select object_name,created from user_objects where object_name=upper('&table_name');
===============================================================
查看某表的大小
????????????SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
????????????????where segment_name=upper('&table_name');
===============================================================
查看放在ORACLE的內(nèi)存區(qū)里的表
????????????SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

===============================================================

再添加一個(gè)表空間的數(shù)據(jù)文件
sql>alter tablespace test add datafile 'd:oracletest1.dbf' size 10m;
===============================================================
建表????SQL>create table studen(stuno int,stuname varchar(8) not null,stubirth date default to_date('1987-5-9','YYYY-MM-DD'));
向表結(jié)構(gòu)中加入一列??SQL>alter table studen add(stuphoto varchar(9));
從表結(jié)構(gòu)中刪除一列??SQL>alter table studen drop column stuphoto;
修改表一列的長(zhǎng)度????SQL>alter table studen modify(stuno number(4));
隱藏將要?jiǎng)h除的一列??SQL>alter table studen set unused column stuphoto;
刪除隱藏的列????????SQL>alter table studen drop unused columns;
向表中加入約束??????SQL>alter table studen add constraint pk primary key(stuno);
刪除約束????????????SQL>alter table studen drop constraint pk;
===============================================================
創(chuàng)建表

sql>create table 用戶名(name varchar2(20),password varchar(20)) tablespace 空間名;
===============================================================
添加字段
sql>alter table test add(column_x char(10) not null);
===============================================================
更改字段
sql>alter table emp modify(column_x char (20));
===============================================================
刪除字段
?如待刪除域?qū)儆谀硞€(gè)索引，則不允許刪除操作，必須將此域先設(shè)置為NULL。
sql>alter table emp modify(column_x null);
sql>update emp set column_x=null;
sql>commit;
sql>alter table emp drop(column_x);
===============================================================
選擇表空間
sql>alter user 用戶名 default tablespace test;
===============================================================
管理員刪除別的用戶中的表
sql>drop table 用戶名.表名;
===============================================================
退出
sql>exit;
===============================================================
默認(rèn)進(jìn)入
sql>sqlplus "/ as sysdba"
===============================================================
查看數(shù)據(jù)庫(kù)
sql>show parameter block;
===============================================================
寫(xiě)大量語(yǔ)句用記事本，新建方式。
輸入"ed"回車(chē)
保存后
輸入"/"運(yùn)行;
===============================================================
查詢(xún)用戶有多少表
sql>select * from tab;
===============================================================
SQLServer取時(shí)間
sql>select getdate
oracle 取時(shí)間
sql>sysdate;
===============================================================
操作表結(jié)構(gòu)數(shù)據(jù)庫(kù)定義語(yǔ)言命令
(不記錄在日志文件中)
create table建表
sql>create table test(name varchar2(20),age date,sex char(2));
sql>insert into test(name,age,sex) values('aa',sysdate,'男');
sql>insert into test(name,age,sex) values('bb',to_date('1888-8-8',"yyyy-aa-dd hh24:mi:ss"),'男');
sql>select * from test;
===============================================================
查詢(xún)男和女總數(shù)
sql>select sex,count(sex) from test group by sex;
---------------------------------------------------------------
test表中數(shù)據(jù)輸入test1表中
SQLSserver---select * into test1 from test;
oracle---create table test1 as select * from test;
---------------------------------------------------------------
更改會(huì)話時(shí)間
sql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
---------------------------------------------------------------
sql>show parameter block 表和視圖
sql>show parameter date 查數(shù)據(jù)結(jié)構(gòu)
---------------------------------------------------------------
SQLServer中
--刪除表中相同數(shù)據(jù)
sql>create table test1 as select distinct * from test;
--刪除表數(shù)據(jù)
sql>truncate table test;
--把test中數(shù)據(jù)輸入到test1中
sql>insert into test(select * from test1);
---------------------------------------------------------------
rowid(表中存儲(chǔ)地址相當(dāng)表id)和rownum(表序號(hào))稱(chēng)偽列(用法)
sql>select name,age,sex,rowid,rownum from test1;
查出前三行
sql>select * from test where rownum<=3;
查出后三行
sql>select * from (select name n,age a,sex s,rownum r from test) where r>(select count(*) from test)-3;
刪除后三行
SQL> delete from test where name not in(select name from test where rownum<=(select count(*) from test)-3);
刪除相同行
sql>delete from test where rowid not in(select max(rowid) from test group by name,age,sex);
刪除所有表
sql>select??'drop table' ||tname|| ':' from tab;
sql>spool c:test.sql;
sql>select??'drop table' ||tname|| ':' from tab;
sql>spool off
sql>@c:test.sql;
---------------------------------------------------------------
alter table修改表
truncate table節(jié)段表(只刪除數(shù)據(jù))
drop table刪除表
===============================================================
查看表結(jié)構(gòu)
desc 表名;
===============================================================
查出成績(jī)的前三名
sql>select * from (select * from stu order by score desc) where rownum<=3;
===============================================================
更改字符集
SQL>startup mount
SQL>alter system enable restricted session;
SQL>alter system set job_queue_processes=0;
SQL>alter database open;
SQL>alter database character set ZHS16GBK;
SQL>shutdown
SQL>startup
===============================================================
將一張表或幾張表中的域重新組合后插入新表。
假定原先的兩張表為emp,work，現(xiàn)選擇部分?jǐn)?shù)據(jù)域合并為emp_work
建立emp_work
SQL>insert into emp_new select a.no, sysdate, a.name, b.service_duration from emp a, work b where a.no=b.no;
SQL>commit;
這樣的方式仍然要使用回滾段，為加快數(shù)據(jù)遷移速度，可將insert替換成insert （大小寫(xiě)不論），指示oracle以直通方式直接寫(xiě)數(shù)據(jù)文件，繞過(guò)回滾空間。
SQL>insert into emp_new select a.no, sysdate, a.name, b.service_duration from emp a, work b where a.no=b.no;
SQL>commit;
===============================================================
DDL數(shù)據(jù)定義語(yǔ)言(create,alter,drop)
DML數(shù)據(jù)操縱語(yǔ)言(insert,select,delete,update)
TCL事務(wù)控制語(yǔ)言(commit,savepoint,rollback)
DCL數(shù)據(jù)控制語(yǔ)言(GRANT??REVOKE)
===============================================================

一個(gè)表中的某一列輸?shù)搅硪粋€(gè)表中
insert into stu1(name)(select name from stu);
===============================================================
事務(wù)
rollback;
insert into stu1(name)(select name from stu);
commit;提交
===============================================================
COMMIT - 提交并結(jié)束事務(wù)處理
ROLLBACK -??撤銷(xiāo)事務(wù)中已完成的工作
SAVEPOINT – 標(biāo)記事務(wù)中可以回滾的點(diǎn)
SQL>??update order_master set del_date ='30-8月-05' WHERE orderno <= 'o002';
?
SQL>??savepoint mark1;
SQL>??delete FROM order_master WHERE orderno = 'o002';
SQL>??savepoint mark2;
SQL>??rollback TO SAVEPOINT mark1;
SQL>??COMMIT;
===============================================================
換名
set sqlprompt "scott>";
===============================================================
GRANT 授予權(quán)限
SQL> GRANT SELECT ON vendor_master TO accounts WITH GRANT OPTION;
REVOKE 撤銷(xiāo)已授予的權(quán)限
SQL> REVOKE SELECT, UPDATE ON order_master FROM MARTIN;
===============================================================
比較操作符
SQL> SELECT vencode,venname,tel_no
?????FROM vendor_master
?????WHERE venname LIKE 'j___s';
SQL> SELECT orderno FROM order_master
?????WHERE del_date IN (‘06-1月-05’,‘05-2月-05');
SQL> SELECT itemdesc, re_level
?????FROM??itemfile
?????WHERE qty_hand < max_level/2;
===============================================================
邏輯操作符
SQL> SELECT * FROM order_master
?????WHERE odate > ‘10-5月-05'
?????AND del_date < ‘26-5月-05’;
===============================================================
集合操作符將兩個(gè)查詢(xún)的結(jié)果組合成一個(gè)結(jié)果
SQL> SELECT orderno FROM order_master
?????MINUS
?????SELECT orderno FROM order_detail;
-----------------------------------------------------------------
select * from scott.stu
union (all)重復(fù)的去掉[intersect把相同的取出來(lái)][minus顯示不相同的數(shù)]
select * from stu
-----------------------------------------------------------------
顯示相同的數(shù)據(jù)
select name from stu intersect select name from stu1;
===============================================================
連接操作符
連接操作符用于將多個(gè)字符串或數(shù)據(jù)值合并成一個(gè)字符串
SQL> SELECT (venname|| ' 的地址是 '
?????||venadd1||' '||venadd2 ||' '||venadd3) address
?????FROM vendor_master WHERE vencode='V001';
===============================================================
操作符的優(yōu)先級(jí)
SQL 操作符的優(yōu)先級(jí)從高到低的順序是：
算術(shù)操作符???????????--------最高優(yōu)先級(jí)
連接操作符
比較操作符
NOT 邏輯操作符
AND 邏輯操作符
OR???邏輯操作符???--------最低優(yōu)先級(jí)

===============================================================
用來(lái)轉(zhuǎn)換空值的函數(shù)
NVL
NVL2
NULLIF
SELECT itemdesc, NVL(re_level,0) FROM itemfile;
SELECT itemdesc, NVL2(re_level,re_level,max_level) FROM itemfile;
SELECT itemdesc, NULLIF(re_level,max_level) FROM itemfile;
===============================================================
GROUP BY和HAVING子句

GROUP BY子句
用于將信息劃分為更小的組
每一組行返回針對(duì)該組的單個(gè)結(jié)果

HAVING子句
用于指定 GROUP BY 子句檢索行的條件

SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category;
SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category HAVING p_category NOT IN ('accessories');
===============================================================
ROW_NUMBER (row_number)返回連續(xù)的排位，不論值是否相等
RANK(rank) 具有相等值的行排位相同，序數(shù)隨后跳躍
DENSE_RANK(dense_rank) 具有相等值的行排位相同，序號(hào)是連續(xù)的
SELECT d.dname, e.ename, e.sal, DENSE_RANK()
??OVER (PARTITION BY e.deptno ORDER BY e.sal DESC)
??AS DENRANK
FROM emp e, dept d WHERE e.deptno = d.deptno;
===============================================================
日期函數(shù)
ADD_MONTHS(當(dāng)前只加月)
alter session set nls_date_format='yyyymmdd hh24miss';
select add_months(sysdate,2) from dual;
----------------------------------------------------------------
MONTHS_BETWEEN(前面時(shí)間減后面時(shí)間=得之間月差)
select months_between(sysdate,to_date('2007-6-10','yyyy-mm-dd')) from dual;
----------------------------------------------------------------
LAST_DAY(求得當(dāng)前月的最后一天)
select last_day(sysdate) from dual;
----------------------------------------------------------------
ROUND(round年-月-日-->四舍五入)
select round(2.3) from dual;
select round(to_date('2007-6-10','yyyy-mm-dd'),'year') from dual;
select round(to_date('2007-6-10','yyyy-mm-dd'),'month') from dual;
select round(to_date('2007-6-10','yyyy-mm-dd'),'day') from dual;
----------------------------------------------------------------
NEXT_DAY(下一星期的星期二）
select next_day(to_date('2007-6-10','yyyy-mm-dd'),'星期二') from dual;
----------------------------------------------------------------
TRUNC(trunc)
----------------------------------------------------------------
EXTRACT(extract)
select extract(year from date '1998-03-07') from dual;
select extract(month from to_date ('1998-03-07','yyyy-mm-dd')) from dual;

----------------------------------------------------------------
2008年2月有多少天
inbo---->select extract(day from last_day(to_date ('2008-02-07','yyyy-mm-dd'))) from dual;
----------------------------------------------------------------
2003-4-3與1956-3-1之間有多少天
inbo---->select round(months_between(to_date('2003-4-3','yyyy-mm-dd'),to_date('1956-3-1','yyyy-mm-dd'))/12) from dual;
===============================================================
把兩邊的9去掉
select trim('9' from '9999ddddddd99999') from dual;
去空格
select trim(' ' from '?????9999ddddddd99999') from dual;
===============================================================
???函數(shù)???????????????????輸入??????????????????????????輸出
Initcap(char) ???????????Select initcap(‘hello’) from dual;?????????????????Hello
Lower(char) ???????????Select lower(‘FUN’) from dual;?????????????????fun
Upper(char)???????????????Select upper(‘sun’) from dual;?????????????????SUN
Ltrim(char,set) ???Select ltrim( ‘xyzadams’,’xyz’) from dual;????????adams
Rtrim(char,set) ???Select rtrim(‘xyzadams’,’ams’) from dual; ?xyzad
Translate(char, from, to) Select translate(‘jack’,’j’ ,’b’) from dual;?back
Replace(char,searchstring,[rep string])???Select replace(‘jack and jue’ ,’j’,’bl’) from dual;?black and blue
Instr (char, m, n) ???Select instr (‘worldwide’,’d’) from dual; ??5
Substr (char, m, n) ???Select substr(‘a(chǎn)bcdefg’,3,2) from dual; ?????????cd
Concat (expr1, expr2)???Select concat (‘Hello’,’ world’) from dual; ?Hello world

===============================================================
數(shù)字函數(shù)接受數(shù)字輸入并返回?cái)?shù)值結(jié)果

???函數(shù)????輸入???輸出
Abs(n) ?????????Select abs(-15) from dual; ?15

Ceil(n) ?Select ceil(44.778) from dual; ?45

Cos(n) ?????????Select cos(180) from dual; ?-.5984601

Cosh(n) ?Select cosh(0) from dual; ?1

Floor(n) ?Select floor(100.2) from dual; ?100

Power(m,n) ?Select power(4,2) from dual; ?16

Mod(m,n) ?Select mod(10,3) from dual; ?1

Round(m,n) ?Select round(100.256,2) from dual; ?100.26

Trunc(m,n) ?Select trunc(100.256,2) from dual; ?100.25

Sqrt(n) ?Select sqrt(4) from dual; ?2

Sign(n)?????????Select sign(-30) from dual;???????????-1

===============================================================
字符函數(shù)
查看有多少個(gè)字符
SQL> SELECT LENGTH('frances') FROM dual;
-----------------------------------------------------------------
SQL> SELECT vencode,
?????DECODE(venname,'frances','Francis') name
?????FROM vendor_master WHERE vencode='v001';
-----------------------------------------------------------------
查找人是否存在 加字段decode主明是否有人
select name,decode(name,'rbb','有人') from stu;

===================================================================
排續(xù)
select dense_rank() over(partition by sex order by score) from test;
select row_number() over(order by score),name,sex,score from test;
select rank() over(order by score) from test;
select dense_rank() over(order by score) from test;
==========================================================================

創(chuàng)建同義詞
SQL> create public synonym test for rbb.test;
SQL> create synonym test for mytest;
同一類(lèi)的才可以替換,同義詞替換同義詞
替換
SQL> create or replace synonym emp_sysn for scott.emp;

**********************************************************************************************
創(chuàng)建序列
SQL>create sequence xule increment by 1 start with 1 maxvalue 999;
increment by??增長(zhǎng)值
start with????起始值
maxvalue 最大值
minvalue 最小值
nocycle 不循環(huán)
chare 10緩存
xule.nextval??===========下一個(gè)序列的值
xule.currval??===========可以查詢(xún)序列當(dāng)前的值
更改序列 start with 不能改
alter sequence xule maxvalue 100 [sycle nocycle]；
**********************************************************************************************
序列用法
SQL>create table xl(name varchar2(4));
SQL>insert into test values(xule.nextval);
SQL>select xl.currval from dual;
**********************************************************************************************
刪除序列
drop sequence x;
desc user_sequences
**********************************************************************************************
創(chuàng)建視圖???視圖中可以使用函數(shù)和表達(dá)式
create or replace view
**********************************************************************************************
創(chuàng)建視圖
SQL> create or replace view 視圖名 as select * from rbb union all select * from rbbb union all select * from test;
SQL> create or replace view 視圖名 as
??2??select empno as 編號(hào),ename as 姓名 from scott.emp
??3??where deptno=10;
==========================================================================
如果在當(dāng)前用戶下沒(méi)有這個(gè)視圖就創(chuàng)建此視圖
如果有此視圖就覆蓋此視圖
create or replace view view_name as select empno,ename from emp where deptno=10;
**********************************************************************************************
在創(chuàng)建視圖前要為當(dāng)前用戶授權(quán)
grant resource to scott;
create or replace view v_sal as select ename,sal from emp order by sal desc;
**********************************************************************************************
使用視圖
select * from v_sal;
**********************************************************************************************
刪除一個(gè)視圖
drop view view_name;
**********************************************************************************************
重新編譯已有的視圖
alter view view_name compile;
**********************************************************************************************
數(shù)據(jù)字典 ===========desc user_views
**********************************************************************************************

常用的轉(zhuǎn)換函數(shù)有
TO_CHAR
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual;　

TO_DATE
SELECT TO_DATE('2005-12-06', 'yyyy-mm-dd') FROM dual;

TO_NUMBER
SELECT TO_NUMBER('100') FROM dual;
**********************************************************************************************
集合操作符
union all??連接兩個(gè)表或者多個(gè)表為一個(gè)視圖
MINUS 操作符返回從第一個(gè)查詢(xún)結(jié)果中排除第二個(gè)查
詢(xún)中出現(xiàn)的行。
INTERSECT 操作符只返回兩個(gè)查詢(xún)的公共行。
**********************************************************************************************

鎖定的優(yōu)點(diǎn)

1.一致性 - 一次只允許一個(gè)用戶修改數(shù)據(jù)

2.完整性 - 為所有用戶提供正確的數(shù)據(jù)。如果一個(gè)用戶進(jìn)行了修改并保存，所做的修改將反映給所有用戶

3.并行性 －允許多個(gè)用戶訪問(wèn)同一數(shù)據(jù)

行級(jí)鎖和表級(jí)鎖

行級(jí)鎖:是一種排他鎖,防止其他事務(wù)修改此行.

解鎖：提交事務(wù)(commit),(rollback)
---------------------------------------------------------------
更新表數(shù)據(jù):update test set score=80 where name='xiaoli';
--------------------------------------------------------------
自動(dòng)提交
set autocommit on
set sutocommit off
------------------------------------------------------------
鎖定某行更新語(yǔ)句
select * from scott.test where name='xiaoli' for update;
SELECT * FROM order_master WHERE vencode='V002' FOR UPDATE OF odate,del_date;
select * from scott.test where name='xiaoli' for update of score;

select * from scott.test atest,test b where a.name=b.name and b.name='bbb' for update of b.score;
--------------------------------------------------------------------
等待update
select * from scott.test where name='xiaoli' for update wait 2;
select * from scott.test where name='xiaoli' for update nowait;

-------------------------------------------------------------------
表級(jí)鎖：鎖定整個(gè)表
表級(jí)鎖語(yǔ)法:lock table 表名 in mode mode;
-------------------------------------------------------------------------
行共享row share--行排他row exclusive--共享share-共享行排他share row exclusive-----排他exclusive
---------------------------------------------------------------------------------
行共享(row share):lock table scott.test in (row share) mode;
[其他用戶.行共享---其他用戶.行排他---其他用戶.共享----其他用戶.共享行排他----其他用戶.不可以(排他)]

--------------------------------------------------------------------------------
行排他(row exclusive):lock table scott.test in (row exclusive) mode;

[其他用戶.行共享----其他用戶.行排他----其他用戶.不可以(共享)---其他用戶.不可以(共享行排他)--其他用戶.不可以(排他)]

---------------------------------------------------------------------------------
共享(share):lock table scott.test in (share) mode;

[其他用戶.行共享---其他用戶.不可以(行排他)---其他用戶.共享----其他用戶.不可以(共享行排他)---其他用戶.不可以(排他)]

-----------------------------------------------------------------------------------
共享行排他(share row exclusive):lock table scott.test in (share row exclusive) mode;

[其他用戶.行共享,其他用戶.不可以(行排他),其他用戶.不可以(共享),其他用戶.不可以(共享行排他),其他用戶.不可以(排他)]

--------------------------------------------------------------------------------------
排他(exclusive):lock table scott.test in (exclusive) mode;

[其他用戶.不可以(行共享),其他用戶.不可以(行排他),其他用戶.不可以(共享),其他用戶.不可以(共享行排他,)其他用戶.不可以(排他)]

----------------------------------------------------------------------------------
死鎖

當(dāng)兩個(gè)事務(wù)相互等待對(duì)方釋放資源時(shí)，就會(huì)形成死鎖

Oracle會(huì)自動(dòng)檢測(cè)死鎖，并通過(guò)結(jié)束其中的一個(gè)事務(wù)來(lái)解決死鎖
----------------------------------------------------------------------------------

表分區(qū)

---范圍分區(qū)
create table test(name varchar2(20),sex char(2),score number(3))
partition by range(score)
(
partition p1 values less than (50) tablespace users,
partition p2 values less than (80),
partitiom p3 values less than (maxvalue)
)
select * from test partition(p1) union select * from test partitiom(p3);
---
刪除分區(qū)
alter table test drop partition p3;
添加分區(qū)
alter table test add partition p3 values less than (maxvalue);
拆分分區(qū)
alter table test split partition p2 at(60)
into (partition p21,partition p22);
合并分區(qū)
alter table test merge partitions p21,p22 into partition p2;
截?cái)喾謪^(qū)(刪除數(shù)據(jù))
alter table test truncate partition p3;

現(xiàn)有表分區(qū)
create table str as select * from student;
drop table student;
create table student(
?studentid integer not null,
?studentname varchar2(20),
?score integer
)
partition by range(score)(
?partition p1 values less than(60),
?partition p2 values less than(75),
?partition p3 values less than(85),
?partition p4 values less than(maxvalue)
)
insert into student(select * from stu);

select * from test?scott.emp@tsinghua

?

**********************************************************************************************

表分區(qū)
Oracle允許用戶對(duì)表進(jìn)一步的規(guī)化，即對(duì)表進(jìn)一步拆分，將表分成若干個(gè)邏輯部分，每個(gè)部分稱(chēng)其為表分區(qū)
優(yōu)點(diǎn)：增強(qiáng)可用性，單個(gè)分區(qū)出現(xiàn)故障，不影響其他分區(qū)
均衡的I/O，不同的分區(qū)可以映射到不同的磁盤(pán)???改善性能
**********************************************************************************************
①范圍分區(qū)法
create table st(
?studentid integer not null,
?studentname varchar2(20),
?score integer
)
partition by range(score)(
?partition p1 values less than(60),
?partition p2 values less than(75),
?partition p3 values less than(85),
?partition p4 values less than(maxvalue)
)
========================select * from stu partition(p1)============
②散列分區(qū)
create table st(deptno int,deptname varchar(14))
partition by hash(deptno)(
partition p1,partition p2
)
組合分區(qū)
alter table test coalesce partition;
**********************************************************************************************
③復(fù)合分區(qū)
范圍分區(qū)和列表分區(qū)
create table salgrade(
grade number(2),losal number(2),hisal number(2)
)
partition by range(grade)
subpartition by list(losal)
(
partition p1 values less than(10)

??(
???subpartition p1a values('湖北'),
???subpartition p1b values(default)
??),
partition p2 values less than(20)
??(
???subpartition p1a values('河南'),
???subpartition p1b values(default)
??),
partition p3 values less than(30)
??(
???subpartition p1a values('上海'),
???subpartition p1b values(default)
??)
)

范圍分區(qū)和散列分區(qū)
create table salgrade(
grade number(2),losal number(2),hisal number(2)
)
partition by range(grade)
subpartition by hash(losal)
[subpartitions 5]
(
partition p1 values less than(10)(subpartition p1a,subpartition p1b),
partition p2 values less than(20)(subpartition p2a,subpartition p2b),
partition p3 values less than(30)(subpartition p3a,subpartition p3b)
)
--------------------------------------------
?create table salg(
?grade number(2),losal number(2),hisal number(2)
?)
?partition by range(grade)
?subpartition by hash(losal)
?subpartitions 3
?(
?partition p1 values less than(10),
?partition p2 values less than(20),
?partition p3 values less than(30)
?)
**********************************************************************************************
④列表分區(qū)
create table test stu(id int,name varchar(20),add varchar(8))
partition by list(add)
(
partition p1 values('中國(guó)'),
partition p2 values('英國(guó)'),
partition p3 values(default)
)
**********************************************************************************************
移動(dòng)分區(qū)

alter table test move partition p5 tablespace users;

********************************************************************************************

修改存檔

SQL> shutdown immediate
數(shù)據(jù)庫(kù)已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫(kù)。
ORACLE 例程已經(jīng)關(guān)閉。

SQL> startup mount
ORACLE 例程已經(jīng)啟動(dòng)。

Total System Global Area??135338868 bytes??????????????????????????????????????
Fixed Size???????????????????453492 bytes??????????????????????????????????????
Variable Size?????????????109051904 bytes??????????????????????????????????????
Database Buffers???????????25165824 bytes??????????????????????????????????????
Redo Buffers?????????????????667648 bytes??????????????????????????????????????
數(shù)據(jù)庫(kù)裝載完畢。

SQL> alter database archivelog;

數(shù)據(jù)庫(kù)已更改。
alter database open;

SQL> archive log list;
數(shù)據(jù)庫(kù)日志模式????????????存檔模式
自動(dòng)存檔?????????????禁用
存檔終點(diǎn)????????????d:oracleora92RDBMS
最早的概要日志序列?????1
下一個(gè)存檔日志序列???2
當(dāng)前日志序列???????????2

SQL> alter system set log_archive_dest=true scope=spfile;

系統(tǒng)已更改。

SQL> alter database open;

數(shù)據(jù)庫(kù)已更改。

SQL> spool off
********************************************************************************************

PL/SSQL(過(guò)程化語(yǔ)言) 聲明部分 執(zhí)行語(yǔ)句部分 異常處理部分

identifier constant datatype not null
[:=|default expr];

?

declare
my number(5);
begin
?select quantity into my
from products where product='wawa'
for update of quantity;
if my>0 then
update products set quantity=quantity+1
where product='wawa';
insert into purchase_record
values('wawawa',sysdate);
end if;
commit;
Exception
where others then
dbms_output.put_line('chucuo'||SQLERRM);
END;

declare icode varchar2(6)
p_catg varchar2(20);
c_catg constant datatype:=0.10

數(shù)字類(lèi)型
number
??decrmdl
??int/integer
??real(實(shí)數(shù))
??binary_integer(帶符號(hào)的整數(shù))
??pls_integer(同上)
字符類(lèi)型
character
??char 3276
??Raw(2000)
??long/long Raw(32760)
??Rowid/rowid()
??varchar2 (string(nchar/nvarchar)/varchar)
日期時(shí)間
date
??timeStamp(固定日期dd-mm-yy 秒6位)
??子 timestamp with time zone
??ti timestamp(9)
布爾
boolean
??true
??false
??null
打印出時(shí)間
declare
test_tz timestamp with time zone;
begin test_tz:=to_timestamp_tz('2006-6-22 09:07:11','yyyy-mm-dd hh24:mi:ss');
dbms_output.put_line(test_tz);
end;

lob類(lèi)型
??BFILE
??BLOB
??CLOB
??NCLOB
屬性類(lèi)型
??%type??%rowtype
===============================================================
bfile類(lèi)型實(shí)例
創(chuàng)建目錄
create directory tnpdir as 'c:';
刪除目錄
drop directory tnpdir
授權(quán)
crant read on directory tnpdir to scott;
建表
create table bfiletest(id number(3), fname bfile);
添加數(shù)據(jù)
insert into bfiletest values(1,bfilename('TMPDIR','tmptest.java'));

===============================================================
向數(shù)據(jù)庫(kù)中添加圖片
create directory images as 'c:images';
crant read on directory images to scott;
create table my_diagrams(
chapter_descr varchar2(40);
diagram_no integer,
diagram blob
);

declare
??l_bfile bfile;
??l_blob blob;
begin
?insert into my_diagrams(diagram)
?values(emptv_blob())
?return diagram into l_blob;
?l_bfile:=bfilename('images','nvimage.jpg');
?dbms_lob.open(l_bfile,dbms_lob.file_readonly);
?dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob,getlength(l_bfile));
?dbms_lob.close(l_bfile);
?commit;
end;

===============================================================
%type實(shí)例 查詢(xún)
declare
dtr dept.dname%type;
begin
select dname into str from dept where deptno=30;
dbms_output.put_line(str);
end;
set serverout on

===============================================================
%rowtype實(shí)例

declare
row dept%rowtype;
begin
select * into row from dept where deptno=30;
dbms_output.put.line(row.dname||' '||row.deptno||' '||row.loc);
//異常
exception
when no_data_found then
?dbms_output.put_lin('沒(méi)有數(shù)據(jù)');
when too_many_rows(others) then
?dbms_output.put_lin('太多拉');
end;

===============================================================
格式

if 條件 then

elsif 條件 then

else

end if
===============================================================
格式

begin
?case'&grade'
??when 'a' then dbms_output.put_line('優(yōu)異');
??when 'b' then dbms_output.put_line('良好');
??else dbms_output.put_line('其它')
?end case;
end;
===============================================================
外界變量
var vnm varchar2(20);
begin
:v:='aaaaa';
end;
打印
print v
===============================================================
loop實(shí)例

begin
loop
exit when 3>4;

end loop;
end;
===============================================================
while實(shí)例
begin
while (條件)condition loop
語(yǔ)句體;
end loop;
end;
===============================================================
循環(huán)實(shí)例
正
begin
for c in 1..10
loop
dbms_output.put_line(c);
end loop
end;
倒
begin
for c in reverse(倒) 1..10
loop
dbms_output.put_line(c);
end loop
end;
===============================================================

declare
?num number(3):=1;
begin
?while num<10 loop
??dbms_output.put_line(num);
??num:=num+1;
??end loop;
end;

declare
?num number(3):=1;
begin
?loop
??dbms_output.put_line(num);
??exit when num>10;//退出
??num:=num+1;
??end loop;
end;
===============================================================
goto實(shí)例
DECLARE
??qtyhand itemfile.qty_hand%type;
??relevel itemfile.re_level%type;
BEGIN
??SELECT qty_hand,re_level INTO qtyhand,relevel
??FROM itemfile WHERE itemcode = 'i201';
??IF qtyhand < relevel THEN
????GOTO updation;
??ELSE
????GOTO quit;
??END IF;
??<<updation>>
??UPDATE itemfile SET qty_hand = qty_hand + re_level
??WHERE itemcode = 'i201';
??<<quit>>
??NULL;
END;
===============================================================
動(dòng)態(tài)SQL 查詢(xún)
declare
cl varchar2(20);
va varchar2(20);
tb varchar2(20);
nm number(13);

begin
tb:='&table';
cl:='&aadd';
nm:=&num;
EXECUTE IMMEDIATE
'select '||cl||' from '||tb||' where '||cl||'=:1' into va using nm;
dbms_output.put_line(va);
end;

===============================================================
動(dòng)態(tài)SQL

declare

sql_stmt varchar2(200);
emp_id number(4):=7566;
emp_rec emp% rowtype;

begin
Execute immedlate
'create table bonus1(id number,amt number)';

sql_stmt:='select * from emp where empno=:id';
Execute immedlate sql_stmt into emp_rec using emp_id;

end;
===============================================================
declare
aaa varchar2(20);
num number(10);
bbb varchar2(20);
begin
aaa='&aaa';
num=&kkk;
execute immedlate 'select '||aaa||' from test where age=:a'into bbb using num;(標(biāo)準(zhǔn)SQL語(yǔ)句)
dbms_output.put_line(bbb);
end;
into 變量(給值)
:a(外界參數(shù)) using bb(邦定常量)

===============================================================
自己定義異常
declare
invar exception;
cate varchar2(10);
begin
cate:='&cate';
if cate not in('aa','ff','dd') then
raise invar;
else
dbms_output.put_line('你輸入的類(lèi)別是:'||cate);
end if;
exception
when invar then
dbms_output.put_line('無(wú)法認(rèn)識(shí)這個(gè)類(lèi)別!');
raise_application_error(-20200,'自己寫(xiě)');
end;
讓數(shù)據(jù)庫(kù)真正出錯(cuò)
raise_application_error(-20200,'自己寫(xiě)');

例子2
declare
rate itemfile.itemrate%type;
ratee exception;
begin
select nvl(itemrate,0) into rate from itemfile
where itemcode='i207';
if rate=0 then
raise ratee;
else
dbms_output.put_line('項(xiàng)費(fèi)率是:'||rate);
end if;
exception
when ratee then
RAISE_APPLICATION_ERROR(-20001, '未指定項(xiàng)費(fèi)率');
end;
===============================================================
create procedure存儲(chǔ)過(guò)程
===============================================================
創(chuàng)建標(biāo)準(zhǔn)索引
SQL> CREATE INDEX item_index ON itemfile (itemcode)
?????TABLESPACE index_tbs;
重建索引
SQL> ALTER INDEX item_index REBUILD;
刪除索引
SQL> DROP INDEX item_index;
唯一索引確保在定義索引的列中沒(méi)有重復(fù)值
Oracle 自動(dòng)在表的主鍵列上創(chuàng)建唯一索引
使用CREATE UNIQUE INDEX語(yǔ)句創(chuàng)建唯一索引
SQL> CREATE UNIQUE INDEX item_index
?????ON itemfile (itemcode);
組合索引是在表的多個(gè)列上創(chuàng)建的索引
索引中列的順序是任意的
如果 SQL 語(yǔ)句的 WHERE 子句中引用了組合索引的所有列或大多數(shù)列，
則可以提高檢索速度

SQL> CREATE INDEX comp_index
?????ON itemfile(p_category, itemrate);
反向鍵索引反轉(zhuǎn)索引列鍵值的每個(gè)字節(jié)
通常建立在值是連續(xù)增長(zhǎng)的列上，使數(shù)據(jù)均勻地分布在整個(gè)索引上
創(chuàng)建索引時(shí)使用REVERSE關(guān)鍵字
SQL> CREATE INDEX rev_index
?????ON itemfile (itemcode) REVERSE;
SQL> ALTER INDEX rev_index REBUID NOREVERSE;
位圖索引適合創(chuàng)建在低基數(shù)列上
位圖索引不直接存儲(chǔ)ROWID，而是存儲(chǔ)字節(jié)位到ROWID的映射
減少響應(yīng)時(shí)間
節(jié)省空間占用
SQL> CREATE BITMAP INDEX bit_index
?????ON order_master (orderno);
基于一個(gè)或多個(gè)列上的函數(shù)或表達(dá)式創(chuàng)建的索引
表達(dá)式中不能出現(xiàn)聚合函數(shù)
不能在LOB類(lèi)型的列上創(chuàng)建
創(chuàng)建時(shí)必須具有 QUERY REWRITE 權(quán)限
SQL> CREATE INDEX lowercase_idx
?????ON toys (LOWER(toyname));
SQL> SELECT toyid FROM toys
?????WHERE LOWER(toyname)='doll';
與索引有關(guān)的數(shù)據(jù)字典視圖有：
USER_INDEXES － 用戶創(chuàng)建的索引的信息
USER_IND_PARTITIONS － 用戶創(chuàng)建的分區(qū)索引的信息
USER_IND_COLUMNS － 與索引相關(guān)的表列的信息
SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
?????FROM USER_IND_COLUMNS
?????ORDER BY INDEX_NAME, COLUMN_POSITION;
可以將索引存儲(chǔ)在不同的分區(qū)中
與分區(qū)有關(guān)的索引有三種類(lèi)型：
局部分區(qū)索引 － 在分區(qū).
表上創(chuàng)建的索引，在每個(gè)表分區(qū)上創(chuàng)建獨(dú)立的索引，索引的分區(qū)范圍與表一致
全局分區(qū)索引 － 在分區(qū)表或非分區(qū)表上創(chuàng)建的索引，索引單獨(dú)指定分區(qū)的范圍，與表的分區(qū)范圍或是否分區(qū)無(wú)關(guān)
全局非分區(qū)索引 － 在分區(qū)表上創(chuàng)建的全局普通索引，索引沒(méi)有被分區(qū)

SQL> CREATE TABLE ind_org_tab (
?????vencode NUMBER(4) PRIMARY KEY,
???????venname VARCHAR2(20)
????)
????ORGANIZATION INDEX;
與索引有關(guān)的數(shù)據(jù)字典視圖有：
USER_INDEXES － 用戶創(chuàng)建的索引的信息
USER_IND_PARTITIONS － 用戶創(chuàng)建的分區(qū)索引的信息
USER_IND_COLUMNS － 與索引相關(guān)的表列的信息

SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
?????FROM USER_IND_COLUMNS
?????ORDER BY INDEX_NAME, COLUMN_POSITION;

?

----游標(biāo)簡(jiǎn)介

逐行處理查詢(xún)結(jié)果,經(jīng)編程的方式訪問(wèn)數(shù)據(jù)

---游標(biāo)類(lèi)型:
隱式游標(biāo):在 PL/SQL 程序中執(zhí)行DML SQL 語(yǔ)句時(shí)自動(dòng)創(chuàng)建隱式游標(biāo)。
顯式游標(biāo):顯式游標(biāo)用于處理返回多行的查詢(xún)。
REF 游標(biāo):REF 游標(biāo)用于處理運(yùn)行時(shí)才能確定的動(dòng)態(tài) SQL 查詢(xún)的結(jié)果

------隱式游標(biāo)的屬性有：
%FOUND – SQL 語(yǔ)句影響了一行或多行時(shí)為 TRUE
%NOTFOUND – SQL 語(yǔ)句沒(méi)有影響任何行時(shí)為T(mén)RUE
%ROWCOUNT – SQL 語(yǔ)句影響的行數(shù)
%ISOPEN??- 游標(biāo)是否打開(kāi)，始終為FALSE

刪除游標(biāo)

delete from table_name where cursor of cursor_name;
===============================================================
------隱式游標(biāo)示例
------too_many_rows的用法!

??1??declare
??2??empid varchar2(20);
??3??begin
??4??select name into empid from test;
??5??exception
??6??when too_many_rows then
??7??dbms_output.put_line('該查詢(xún)多于兩行!');
??8* end;
SQL> /
該查詢(xún)多于兩行!

PL/SQL 過(guò)程已成功完成。

===============================================================

------no_data_found的用法!
SQL> set serverout on
SQL> ed
已寫(xiě)入文件 afiedt.buf

??1??declare
??2??empid varchar2(20);
??3??desig varchar2(20);
??4??begin
??5??empid:='&emp';
??6??select name into desig from test where name=empid;
??7??dbms_output.put_line('你查詢(xún)的名字是:'||desig);
??8??exception
??9??when no_data_found then
?10??dbms_output.put_line('沒(méi)有時(shí)間！');
?11* end;
SQL> /
輸入 emp 的值:??xiaoli
原值????5: empid:='&emp';
新值????5: empid:='xiaoli';
你查詢(xún)的名字是:xiaoli

PL/SQL 過(guò)程已成功完成。

SQL> /
輸入 emp 的值:??ss
原值????5: empid:='&emp';
新值????5: empid:='ss';
沒(méi)有時(shí)間！

PL/SQL 過(guò)程已成功完成。

===============================================================
SQL> set serveroutput on
SQL> begin
??2??update test set name='renbinbo' where name='binbo';
??3??if sql%found then
??4??dbms_output.put_line('表已經(jīng)更新!');
??5??end if;
??6??end;
??7??/
test_t表中name也已經(jīng)更新!
表已經(jīng)更新!

===============================================================
SQL>
??declare
???aa varchar2(20);
??bb varchar2(20);
???begin
??bb:='&bb';
???select score into aa from test where name=bb;
???if sql%found then
???dbms_output.put_line(bb||'的分?jǐn)?shù)為:'||aa);
???end if;
??end;
SQL> /
輸入 bb 的值:??renbinbo
原值????5: bb:='&bb';
新值????5: bb:='renbinbo';
renbinbo的分?jǐn)?shù)為:100

PL/SQL 過(guò)程已成功完成。
===============================================================
SQL> ed
已寫(xiě)入文件 afiedt.buf

??1??declare
??2??my_toy rbb.test.name%type;
??3??cursor toy_cur is
??4??select name from test where name='xiaoli';
??5??begin
??6??open toy_cur;
??7??loop
??8??fetch toy_cur into my_toy;
??9??exit when toy_cur%notfound;
?10??dbms_output.put_line('你查詢(xún)?nèi)说男彰?'||my_toy);
?11??end loop;
?12??close toy_cur;
?13* end;
SQL> /
你查詢(xún)?nèi)说男彰?xiaoli

PL/SQL 過(guò)程已成功完成。

SQL> ed
昨天我說(shuō)現(xiàn)在的想法那些確實(shí)的真的 也是事實(shí) 那是
已寫(xiě)入文件 afiedt.buf

??1??declare
??2??name_n rbb.test.name%type;
??3??sex_s rbb.test.name%type;
??4??sex_t rbb.test.name%type;
??5??cursor test_t is
??6??select name,sex,score from test;
??7??begin
??8??open test_t;
??9??dbms_output.put_line('你所查資料列表:');
?10??loop
?11??fetch test_t into name_n,sex_s,sex_t;
?12??exit when test_t%notfound;
?13??dbms_output.put_line(name_n||'??'||sex_s||'??'||sex_t);
?14??end loop;
?15??close test_t;
?16* end;
?17??/
你所查資料列表:
xiaoli??女???90
renbinbo??男???100
xiaoming??男???89
xiaowang??男???91
xiaohua??女???98
yunfeng??男???88
wangming??男???78
wuming??男???98
xiaobin??男???68
binbin??男???44
tianhua??女???55
liyun??女???65

PL/SQL 過(guò)程已成功完成。

===============================================================

bibno-->ed
已寫(xiě)入文件 afiedt.buf

??1??declare
??2??cursor test_cur is
??3??select name,sex,score from test;
??4??begin
??5??dbms_output.put_line('用戶資料列表:');
??6??for namet in test_cur
??7??loop
??8??dbms_output.put_line(namet.name||' '||namet.sex||' '||namet.score);
??9??end loop;
?10* end;
?11??/
用戶資料列表:
xiaoli 女??90
renbinbo 男??100
xiaoming 男??89
xiaowang 男??91
xiaohua 女??98
yunfeng 男??88
wangming 男??78
wuming 男??98
xiaobin 男??68
binbin 男??44
tianhua 女??55
liyun 女??65

PL/SQL 過(guò)程已成功完成。

===============================================================

帶參數(shù)的顯式游標(biāo)
SET SERVEROUTPUT ON
SQL> DECLARE
??desig????VARCHAR2(20);
??emp_code VARCHAR2(5);
??empnm????VARCHAR2(20);
??CURSOR emp_cur(desigparam VARCHAR2) IS
???SELECT empno, ename FROM employee
???WHERE designation=desig;
?????BEGIN
??desig:= '&desig';
??OPEN emp_cur(desig);
??LOOP
???FETCH emp_cur INTO emp_code,empnm;
???EXIT WHEN emp_cur%NOTFOUND;
???DBMS_OUTPUT.PUT_LINE(emp_code||' '||empnm);
????END LOOP;
??CLOSE emp_cur;
?????END;
===============================================================
SET SERVEROUTPUT ON
SQL> DECLARE
??new_price NUMBER;
??CURSOR cur_toy IS
????SELECT toyprice FROM toys WHERE toyprice<100
????FOR UPDATE OF toyprice;
BEGIN
??OPEN cur_toy;
??LOOP
????FETCH cur_toy INTO new_price;
????EXIT WHEN cur_toy%NOTFOUND;
????UPDATE toys
????SET toyprice = 1.1*new_price
????WHERE CURRENT OF cur_toy;
??END LOOP;
??CLOSE cur_toy;
??COMMIT;
END;

===============================================================
游標(biāo)變量的功能強(qiáng)大，可以簡(jiǎn)化數(shù)據(jù)處理

游標(biāo)變量的優(yōu)點(diǎn)有:
1.可從不同的 SELECT 語(yǔ)句中提取結(jié)果集
2.可以作為過(guò)程的參數(shù)進(jìn)行傳遞
3.可以引用游標(biāo)的所有屬性
4.可以進(jìn)行賦值運(yùn)算

使用游標(biāo)變量的限制：
1.不能在程序包中聲明游標(biāo)變量
2.FOR UPDATE子句不能與游標(biāo)變量一起使用
3.不能使用比較運(yùn)算符
===============================================================
===============================================================

?

創(chuàng)建過(guò)程
create procedure test_b(test varchar2,test1 number)
as
begin

dbms_output.put_line(test);
dbms_output.put_line(test1);
end;

?

create procedure test_c(test varchar2,test1 char)
as
aa varchar2(20);
bb char(10);
begin
select name into aa from test where name=test;
dbms_output.put_line(aa);
?select age into bb from test where age=test1;
dbms_output.put_line(bb);
end;
===============================================================
創(chuàng)建函數(shù)
create or replace function test_binbo return varchar2
as
begin
return '我愛(ài)你!';
end
執(zhí)行:
select test_binbo from dual;

?create or replace function test_binbo return varchar2
?as
?aa varchar2(20);
?bb char(3);
?begin
?bb:='&bb';
?select name into aa from test where sex=bb;
?return 'name';
?end;
執(zhí)行:
select test_binbo from dual;

create or replace function item_price_range(price number)
return varchar2 as
min_price number;
max_price number;
begin
select max(itemrate),min(temrate) into max_price,min_price
from test;
if price>=min_price and price<=max_price then
return '將計(jì)就計(jì)機(jī)';
else
return '哩哩啦啦理論';
end if;
end;
執(zhí)行:
select test_binbo from dual;
===============================================================
自主事務(wù)處理
CREATE OR REPLACE PROCEDURE p1 AS
??b VARCHAR2(50);
BEGIN
??UPDATE vendor_master SET venadd1='10 Walls Street'
??WHERE vencode='V002';
??P2();
??SELECT venadd1 INTO b
??FROM vendor_master WHERE vencode='V002';
??DBMS_OUTPUT.PUT_LINE(b);
END;
/
執(zhí)行
EXECUTE p1;

CREATE OR REPLACE PROCEDURE p2 AS
??a VARCHAR2(50);
??PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
??SELECT venadd1 INTO a
??FROM vendor_master WHERE vencode='V002';
??DBMS_OUTPUT.PUT_LINE(a);
??ROLLBACK;
END;
/

===============================================================
創(chuàng)建程序包

SQL> ed
已寫(xiě)入文件 afiedt.buf

??1??create or replace package package_me as
??2??procedure proc_test(test varchar2);
??3??function fun_test(funt varchar2) return varchar2;
??4* end;
??5??/

程序包已創(chuàng)建。

已寫(xiě)入文件 afiedt.buf

??1??create or replace package body package_me as
??2??procedure proc_test(test varchar2) as
??3??nam varchar2(20);
??4??begin
??5??select name into nam from test where name=test;
??6??dbms_output.put_line('你所查的人的姓名是:'||nam);
??7??end;
??8??function fun_test(funt varchar2) return varchar2 as
??9??funn varchar2(20);
?10??begin
?11??select next_day(funt,'星期六')-7 into funn from dual;
?12??return funn;
?13??end;
?14* end package_me;
SQL> /

程序包主體已創(chuàng)建。
SQL> select package_me.fun_test('2008-10-16') from dual;

PACKAGE_ME.FUN_TEST('2008-10-16')
---------------------------------------------------------

2008-10-11

SQL> exec package_me.proc_test('xiaoli');
你所查的人的姓名是:xiaoli
PL/SQL 過(guò)程已成功完成。
===============================================================
create or replace package pack_me as
procedure order_pr(orn varchar2);
function order_fu(onr varchar2) return varchar2;
end pack_me;
/

CREATE OR REPLACE PACKAGE BODY pack_me AS
??PROCEDURE order_proc (orno VARCHAR2) IS
????stat CHAR(1);
??BEGIN
????SELECT ostatus INTO stat FROM order_master
????WHERE orderno = orno;
????IF stat = 'p' THEN
??????DBMS_OUTPUT.PUT_LINE('暫掛的訂單');
????ELSE
??????DBMS_OUTPUT.PUT_LINE('已完成的訂單');
????END IF;
??END order_proc;
?
??FUNCTION order_fun(ornos VARCHAR2)
??RETURN VARCHAR2
??IS
????icode???VARCHAR2(5);
????ocode???VARCHAR2(5);
????qtyord??NUMBER;
????qtydeld NUMBER;
??BEGIN
????SELECT qty_ord, qty_deld, itemcode, orderno
????INTO???qtyord, qtydeld, icode, ocode
????FROM order_detail
????WHERE orderno = ornos;
????IF qtyord < qtydeld THEN
??????RETURN ocode;
????ELSE
??????RETURN icode;
????END IF;
??END order_fun;
END pack_me;
/

===============================================================

執(zhí)行
EXECUTE pack_me.order_proc('o002');

DECLARE
??msg VARCHAR2(10);
BEGIN
??msg := pack_me.order_fun('o002');
??DBMS_OUTPUT.PUT_LINE('值是 ' || msg);
END;

/

CREATE OR REPLACE PACKAGE BODY cur_pack AS
?CURSOR ord_cur(vcode VARCHAR2)
?RETURN order_master%ROWTYPE IS
?SELECT * FROM order_master
?WHERE VENCODE=vcode;
?PROCEDURE ord_pro(vcode VARCHAR2) IS
???or_rec order_master%ROWTYPE;
?BEGIN
??OPEN ord_cur(vcode);
??LOOP
????FETCH ord_cur INTO or_rec;
????EXIT WHEN ord_cur%NOTFOUND;
????DBMS_OUTPUT.PUT_LIne('返回的值為' || or_rec.orderno);
??END LOOP;
?END ord_pro;
END cur_pack;

EXEC cur_pack.ord_pro('V001');
===============================================================

COLUMN OBJECT_NAME FORMAT A18

SELECT object_name, object_type
FROM USER_OBJECTS
WHERE object_type IN ('PROCEDURE', 'FUNCTION',
?'PACKAGE', 'PACKAGE BODY');

DESC USER_SOURCE

COLUMN LINE FORMAT 9999
COLUMN TEXT FORMAT A50

SELECT line, text FROM USER_SOURCE
WHERE NAME='TEST';

DESC pack_me;

===============================================================

數(shù)據(jù)庫(kù)級(jí)觸發(fā)器
CREATE TABLE system.session_info (
??username???VARCHAR2(30),
??logontime??DATE,
??session_id VARCHAR2(30),
??ip_addr????VARCHAR2(30),
??hostname???VARCHAR2(30),
??auth_type??VARCHAR2(30)
);
顯示
set serverout on

create or replace trigger trg_session_info defore logoff on database
declare
session_id varchar2(30);
ip_addr????varchar2(30);
hostname???varchar2(30);
auth_type??varchar2(30);
logontime??date;

begin
?select sys_context('userenv','sessionid') -- 會(huì)話編號(hào)
??--??用戶登錄的客戶端IP地址
?select sys_context('userenv','ip_address') into ip_addr from dual;
??--??用戶登錄的客戶端主機(jī)名?
?select sys_context('usernv','host') into hostname from dual;
??--??登錄認(rèn)證方式，數(shù)據(jù)庫(kù)認(rèn)證或外部認(rèn)證
?select sys_context('usernv','authentication_type') into auth_type from dual;
?insert into system.session_info values (user,sysdate,session_id,ip_addr,hostname,auth_type);
end;
SELECT * FROM system.session_info;
===============================================================
對(duì)表employees創(chuàng)建觸發(fā)器
create or replace trigger tr_employee after update on employees
for each row
begin
?if(:new.salary>40000) then
?raise_application_error(-20002,'職員工資不能超過(guò) 40000');
?end if;
end;
===============================================================

create or replace procedure demo(salary in number) as
??cursor_name integer;
??rows_processed interger;
begin
??cursor_name:=dbms_sql.open_cursor;
??dbms_sql.parse(cursor_name,'delete from salary_records where empsal>:temp_sal',dbms_sql.native);
??dbms_sql.bind_variable(cursor_name,':temp_sal',salay);
??rows_processed:=dbms_sql.execute(cursor_name);
??dbms_sql.close_cursor(crusor_name);
exception
??when others then
????dbms_sql.close_cursor(cursor_name);
end;
===============================================================
1.寫(xiě)一個(gè)帶程序包的函數(shù)，只要傳入文件名和地址就可以把這個(gè)文件的內(nèi)容存到BLOB類(lèi)型的字段中。
binbo>create directory tnpdir as 'c:bfile';
binbo>grant read on directory tnpdir to scott;
binbo>CREATE TABLE my_dia
(
??chapter_descr VARCHAR2(40),
??diagram_no INTEGER,
??diagram BLOB
);

DECLARE
??l_bf??BFILE;
??l_bl BLOB;
BEGIN
??INSERT INTO my_dia (diagram)
??VALUES (EMPTY_BLOB())
??RETURN diagram INTO l_bl;
??l_bf := BFILENAME('jsp', 'test.jsp');
??DBMS_LOB.OPEN(l_bf, DBMS_LOB.FILE_READONLY);
??DBMS_LOB.LOADFROMFILE(l_bl, l_bf, DBMS_LOB.GETLENGTH(l_bf));
??DBMS_LOB.CLOSE(l_bf);
??COMMIT;
END;
===============================================================
2.有一張表，字段的值是這樣的：name varchar2(20),sex char(2),score number(3)。其中的SCORE字段為分?jǐn)?shù)字段。請(qǐng)用一條SQL語(yǔ)句把九十分以上的顯示為A。九十到七十分的為B。七十分以下的為C。

binbo>create table test(name varchar2(20),sex char(2),score number(3));
binbo>select name,sex,case when score<=70 then 'C'
when score<=90 and score>70 when 'B'
when score>90 when 'A'
end case from test;
===============================================================
3.有一個(gè)表，其中有一個(gè)字段為自動(dòng)增長(zhǎng)的數(shù)據(jù)類(lèi)型。請(qǐng)?jiān)贠RACLE中實(shí)現(xiàn)。
binbo>create table test(id number,name varchar2(20));
create sequence seq_test increment by 1 start with 1 maxvalue 999;
create or replace trigger tr_test before insert or update of id on test
for each row
begin
if insert into then
select seq_test.nextval into :new.id from dual;
else
raise_application_error(-20002,'不允許更新ID序列!');
end if;
end;
===============================================================
4.如何刪除一個(gè)用戶下的所有表。

binbo>spool c:test.sql
binbo>select 'drop table '||tname||';'from tab;
binbo>spool off
binbo>@c:test;
===============================================================
5。如何把數(shù)據(jù)庫(kù)的日志模式從歸檔模式變?yōu)榉菤w檔模式

binbo>shutdown immediate
binbo>startup mount
binbo>alter database archivelog;
binbo>archive log list;
binbo>alter system set log_archive_dest=false scope=spfile;
binbo>alter database open;
===============================================================

6。建立一個(gè)用戶和表空間，在這個(gè)用戶和表空間下建立一張表。并授予SCOTT用戶查詢(xún)權(quán)利。
binbo>create user binbo identified by binbo;
binbo>create tablespace test datafile 'e:test.dbf' size 10m;
binbo>GRANT SELECT ON scott.test to scott;
===============================================================
7。寫(xiě)一個(gè)過(guò)程，計(jì)算某個(gè)月有多少天。
?create or replace procedure dept(test in varchar2)
?as
?aa varchar2(20);
??begin
?select extract(day from last_day(to_date (test,'yyyy-mm'))) into aa from dual;
??dbms_output.put_line(aa);
??end;
===============================================================
8。有一章表，字段為name,sex,score，score字段為分?jǐn)?shù)字段，查詢(xún)出這個(gè)班的第五名到第七名的人的姓名。
binbo>create table test(name varchar2(20),sex char(3),score number(3));
binbo>select * from (select name n,score sc,rownum r from (select name,score,rownum from test order by score desc)) where r between 5 and 7;
===============================================================
9。查詢(xún)出當(dāng)前這個(gè)星期的星期六是幾號(hào)。

binbo>select next_day(sysdate,'星期六') from dual;
===============================================================
10。做一個(gè)外鍵關(guān)聯(lián)的兩個(gè)表。然后用觸發(fā)器做級(jí)聯(lián)更新。
create table test(name varchar2(20),sex char(3),score number(3));
?create table test_t(name varchar2(20));

?create or replace trigger test_test before insert or update of name on test
?for each row
?begin
?if inserting then
??insert into test_t(name) values (:new.name);
??dbms_output.put_line('test_t表中name也已經(jīng)插入!');
?elseif updating then
??update test_t set name=:new.name where name=old.name;
??dbms_output.put_line('test_t表中name也已經(jīng)更新!');
?elseif deleting then
??delete from test_t where name=:old.name;
??dbms_output.put_line('test_t表中name也已經(jīng)刪除!');
?else
??raise_application_error(-20002,'不允許更新test表中的name字段');
?end if;
?end;

===============================================================
---從外界向數(shù)據(jù)庫(kù)中插入數(shù)據(jù)
SQL> create table test_file(name varchar(30),shell varchar2(30));
表已創(chuàng)建。

G:盤(pán)data.ctl:(tab鍵隔開(kāi)時(shí)間用x'09')
load data into table test_file fields terminated by '=='(name,shell);

G:盤(pán)data.txt:
aaaaaaaa==11111111
bbbbbbbb==22222222
cccccccc==33333333
binbo==hehehehe

C:Documents and SettingsAdministrator>sqlldr rbb/rbb control=G:data.ctl data=G:data.txt

SQL*Loader: Release 9.2.0.1.0 - Production on 星期二 7月 10 20:37:47 2007

Copyright (c) 1982, 2002, Oracle Corporation.??All rights reserved.

達(dá)到提交點(diǎn)，邏輯記錄計(jì)數(shù)3
達(dá)到提交點(diǎn)，邏輯記錄計(jì)數(shù)4

SQL> select * from test_file;

NAME???????????????????????????SHELL
------------------------------ ------------------------------
aaaaaaaa???????????????????????11111111
bbbbbbbb???????????????????????22222222
cccccccc???????????????????????33333333
binbo??????????????????????????hehehehe

?

===============================================================
?declare
???result clob;
???xmlstr varchar2(32767);
???line varchar2(2000);
???line_no integer:=1;
???begin
???result:=dbms_xmlquery.getxml('select * from test');
???xmlstr:=dbms_lob.substr(result,32767);
???loop
??exit when xmlstr is null;
?line:=substr(xmlstr,1,instr(xmlstr,chr(10))-1);
??dbms_output.put_line(line_no||':'||line);
??xmlstr:=substr(xmlstr,instr(xmlstr,chr(10))+1);
??line_no:=line_no+1;
?end loop;
?end;
SQL> /

PL/SQL 過(guò)程已成功完成。

?

SQL> select instr('abcdefsfssdfabcabcsdfs','bc',2,2) "instring" from dual;

??instring
----------
????????14

===============================================================
---創(chuàng)建表中自動(dòng)增長(zhǎng)列(利用觸發(fā)器)
//創(chuàng)建表
create table test_bin(id number(3),name varchar2(20));
//創(chuàng)建序列
create sequence test_sq increment by 1 start with 1 maxvalue 1000;
//創(chuàng)建觸發(fā)器
行級(jí)觸發(fā)器
create or replace trigger test_test before insert or update of id on test_bin
for each row
begin
if inserting then
select test_sq.nextval into :new.id from dual;
else
raise_application_error(-20002,'不允許更新id');
end if;
end;
===============================================================
語(yǔ)句級(jí)觸發(fā)器

create or replace trigger trgdemo after insert or update or delete on order_master
begin
?if updating then
??dbms_output.put_line('已更新order_master中的數(shù)據(jù)');
?elseif deleting then
??dbms_output.put_line('已刪除order_master中的數(shù)據(jù)');
?elseif inserting then
??dbms_output.put_line('已在order_master中插入數(shù)據(jù)');
?end if;
end;
===============================================================
instead of 觸發(fā)器(主要用在視圖中[視圖中只能有for each row])

create or replace trigger upda_ord instead of update on ord_view
for each row
begin
??update order_master set vencode=:new.vencode where orderno=:new.orderno;
??dbms_output.put_line('已激活觸發(fā)器');
end;
===============================================================
觸發(fā)器由三部分組成：
觸發(fā)器語(yǔ)句（事件）
定義激活觸發(fā)器的 DML 事件和 DDL 事件
觸發(fā)器限制
執(zhí)行觸發(fā)器的條件，該條件必須為真才能激活觸發(fā)器
觸發(fā)器操作（主體）
包含一些 SQL 語(yǔ)句和代碼，它們?cè)诎l(fā)出了觸發(fā)器語(yǔ)句且觸發(fā)限制的值為真時(shí)運(yùn)行

===============================================================
觸發(fā)器類(lèi)型

DDL 觸發(fā)器
數(shù)據(jù)庫(kù)級(jí)觸發(fā)器
DML 觸發(fā)器
語(yǔ)句級(jí)觸發(fā)器
行級(jí)觸發(fā)器
INSTEAD OF 觸發(fā)器
===============================================================
模式觸發(fā)器
create table dropped_obj(obj_name varchar2(30),obj_type varchar2(20),drop_date date);

create or replace trigger log_drop_obj after drop on schema
begin
?insert into dropped_obj values(ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;
===============================================================
啟用和禁用觸發(fā)器
alter trigger aiu_name disable;
alter trigger aiu_name enable;
===============================================================
刪除觸發(fā)器
drop trigger aiu_name;
===============================================================
user_triggers數(shù)據(jù)字典視圖包含有關(guān)觸發(fā)器的信息
select trigger_name from user_triggers where table_name='emp';

select trigger_type,triggering_event,when_clause from user_triggers
?where trigger_name='biu_emp_deptno';
===============================================================
dbms_output包顯示pl/sql塊和子程序的調(diào)試信息
set serveroutput on
BEGIN
??DBMS_OUTPUT.PUT_LINE('打印三角形');
??FOR i IN 1..9 LOOP
????FOR j IN 1..i LOOP
??????DBMS_OUTPUT.PUT('*');
????END LOOP for_j;
????DBMS_OUTPUT.NEW_LINE;
??END LOOP for_i;
END;
打印三角形
*
**
***
****
*****
******
*******
********
*********

PL/SQL 過(guò)程已成功完成。
===============================================================
1.DBMS_LOB 包提供用于處理大型對(duì)象的過(guò)程和函數(shù)
2.DBMS_XMLQUERY 包用于將查詢(xún)結(jié)果轉(zhuǎn)換為 XML 格式
DECLARE??result CLOB;
??xmlstr VARCHAR2(32767);
??line???VARCHAR2(2000);
??line_no INTEGER := 1;
BEGIN
??result := DBMS_XMLQuery.getXml('SELECT * FROM test');
??xmlstr := DBMS_LOB.SUBSTR(result,32767);
?LOOP
??EXIT WHEN xmlstr IS NULL;
??line := SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10))-1);
??DBMS_OUTPUT.PUT_LINE(line_no || ':' || line);
??xmlstr := SUBSTR(xmlstr,INSTR(xmlstr,CHR(10))+1);
??line_no := line_no + 1;
?END LOOP;
END;

===============================================================
一些常用的內(nèi)置程序包：
DBMS_OUTPUT 包輸出 PL/SQL 程序的調(diào)試信息
DBMS_LOB 包提供操作 LOB 數(shù)據(jù)的子程序
DBMS_XMLQUERY 將查詢(xún)結(jié)果轉(zhuǎn)換為 XML 格式
DBMS_RANDOM 提供隨機(jī)數(shù)生成器
UTL_FILE 用于讀寫(xiě)操作系統(tǒng)文本文件
===============================================================
觸發(fā)器

CREATE OR REPLACE TRIGGER biu_emp_deptno
BEFORE INSERT OR UPDATE OF deptno
ON emp
FOR EACH ROW
WHEN (New.deptno <> 40)
BEGIN
??:New.comm := 0;
END;

/
觸發(fā)器已創(chuàng)建
===============================================================
----沒(méi)有表還不能測(cè)試

CREATE VIEW ord_view AS
SELECT order_master.orderno, order_master.ostatus,
???????order_detail.qty_deld, order_detail.qty_ord???
FROM order_master, order_detail
WHERE order_master.orderno = order_detail.orderno;

CREATE OR REPLACE TRIGGER order_mast_insert
INSTEAD OF INSERT ON ord_view
REFERENCING NEW AS n
FOR EACH ROW
DECLARE
??CURSOR ecur IS SELECT *??FROM order_master
????WHERE order_master.orderno = :n.orderno;
??CURSOR dcur IS SELECT * FROM order_detail
????WHERE order_detail.orderno = :n.orderno;
??a ecur%rowtype;
??b dcur%rowtype;
BEGIN
??OPEN ecur;
??OPEN dcur;
??FETCH ecur into a;
??FETCH dcur into b;
??IF dcur%notfound THEN
????INSERT INTO order_master(orderno,ostatus)
????VALUES(:n.orderno, :n.ostatus);
??ELSE
????UPDATE order_master SET order_master.ostatus = :n.ostatus
????WHERE order_master.orderno = :n.orderno;
??END IF;
??IF ecur%notfound THEN
????INSERT INTO order_detail(qty_ord,qty_deld,orderno)
????VALUES(:n.qty_ord, :n.qty_deld, :n.orderno);
??ELSE
????UPDATE order_detail
????SET order_detail.qty_ord??= :n.qty_ord,
????????order_detail.qty_deld = :n.qty_deld
????WHERE order_detail.orderno = :n.orderno;
??END IF;
??CLOSE ecur;
??CLOSE dcur;
END;
/
===============================================================
CREATE TABLE dropped_obj
(
??obj_name VARCHAR2(30),
??obj_type VARCHAR2(20),
??drop_date DATE
);

CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
??INSERT INTO dropped_obj
??VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
END;
/

ALTER TRIGGER biu_emp_deptno DISABLE;

ALTER TRIGGER biu_emp_deptno ENABLE;

DROP TRIGGER biu_emp_deptno;

DESC USER_TRIGGERS;

===============================================================
DECLARE
??l_num???NUMBER;
??counter NUMBER;
BEGIN
??counter:=1;
??WHILE counter <= 10
??LOOP
????l_num := ABS((DBMS_RANDOM.RANDOM MOD 100)) + 1;
????DBMS_OUTPUT.PUT_LINE(l_num);
????counter := counter + 1;
??END LOOP;
END;
/
40
4
35
52
68
5
94
38
49
51

PL/SQL 過(guò)程已成功完成。
===============================================================
===============================================================
---查詢(xún)出表中數(shù)據(jù)轉(zhuǎn)換為xml格式
-- 以 SYSTEM 用戶登錄執(zhí)行命令
CREATE DIRECTORY TEST_DIR AS 'C:DEVELOP';
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;

-- 以 SCOTT 用戶登錄
DECLARE
??src CLOB;
??xmlfile UTL_FILE.FILE_TYPE;
??length INTEGER;
??buffer VARCHAR2(16384);
BEGIN
??src := DBMS_XMLQuery.getXml('select * from emp');
??length := DBMS_LOB.GETLENGTH(src);
??DBMS_LOB.READ(src, length, 1, buffer);
??xmlfile := UTL_FILE.FOPEN('TEST_DIR', 'employees.xml', 'w');
??UTL_FILE.PUT(xmlfile, buffer);
??UTL_FILE.FCLOSE(xmlfile);
END;
/
===============================================================
-------讀取xml格式的文件
UTL_FILE 包用于讀寫(xiě)操作系統(tǒng)文本文件
操作文件的一般過(guò)程是打開(kāi)、讀或?qū)憽㈥P(guān)閉
UTL_FILE 包指定文件路徑依賴(lài)于 DIRECTORY 對(duì)象
1.CREATE DIRECTORY TEST_DIR AS 'C:DEVELOP';
2.GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT

SET SERVEROUT ON FORMAT WRAPPED
DECLARE
??input_file???UTL_FILE.FILE_TYPE;
??input_buffer VARCHAR2(4000);
BEGIN
??input_file := UTL_FILE.FOPEN('TEST_DIR', 'employees.xml', 'r');
??FOR I IN 1..11 LOOP
????UTL_FILE.GET_LINE(input_file, input_buffer);
????DBMS_OUTPUT.PUT_LINE(input_buffer);
??END LOOP;
??UTL_FILE.FCLOSE(input_file);
EXCEPTION
??WHEN NO_DATA_FOUND THEN
????DBMS_OUTPUT.PUT_LINE('------------------');
END;
===============================================================

oracle 數(shù)據(jù)庫(kù)重生$rman
oracle 數(shù)據(jù)庫(kù)備份兩種類(lèi)型:物理備份和邏輯備份
===============================================================
oracle 故障有4種類(lèi)型
?1.語(yǔ)句故障
在執(zhí)行 SQL 語(yǔ)句過(guò)程中發(fā)生的邏輯故障可導(dǎo)致語(yǔ)句故障。如果用戶編寫(xiě)的 SQL 語(yǔ)句無(wú)效，就會(huì)發(fā)生邏輯故障
2.用戶進(jìn)程故障
當(dāng)用戶程序出錯(cuò)而無(wú)法訪問(wèn)數(shù)據(jù)庫(kù)時(shí)發(fā)生用戶進(jìn)程故障。導(dǎo)致用戶進(jìn)程故障的原因是異常斷開(kāi)連接或異常終止進(jìn)程
3. 實(shí)例故障
當(dāng) Oracle 的數(shù)據(jù)庫(kù)實(shí)例由于硬件或軟件問(wèn)題而無(wú)法
繼續(xù)運(yùn)行時(shí)，就會(huì)發(fā)生實(shí)例故障

4.介質(zhì)故障
在數(shù)據(jù)庫(kù)無(wú)法正確讀取或?qū)懭肽硞€(gè)數(shù)據(jù)庫(kù)文件時(shí)，
會(huì)發(fā)生介質(zhì)故障?
===============================================================
oracle 導(dǎo)出導(dǎo)入模式

1.完全數(shù)據(jù)庫(kù)
導(dǎo)出和導(dǎo)入整個(gè)數(shù)據(jù)庫(kù)中的所有對(duì)象
2.表
導(dǎo)出和導(dǎo)入一個(gè)或多個(gè)指定的表或表分區(qū)
3.用戶
導(dǎo)出和導(dǎo)入一個(gè)用戶模式中的所有對(duì)象
4.表空間
導(dǎo)出和導(dǎo)入一個(gè)或多個(gè)指定的表空間中的所有對(duì)象

===============================================================
導(dǎo)出實(shí)用程序有以下常用命令參數(shù)

USERID?確定執(zhí)行導(dǎo)出實(shí)用程序的用戶名和口令
BUFFER?確定導(dǎo)出數(shù)據(jù)時(shí)所使用的緩沖區(qū)大小，其大小用字節(jié)表示
FILE?指定導(dǎo)出的二進(jìn)制文件名稱(chēng)，默認(rèn)的擴(kuò)展名是.dmp
FULL?指定是否以全部數(shù)據(jù)庫(kù)方式導(dǎo)出，只有授權(quán)用戶才可使用此參數(shù)
OWNER?要導(dǎo)出的數(shù)據(jù)庫(kù)用戶列表
HELP?指定是否顯示幫助消息和參數(shù)說(shuō)明
ROWS?確定是否要導(dǎo)出表中的數(shù)據(jù)
TABLES?按表方式導(dǎo)出時(shí)，指定需導(dǎo)出的表和分區(qū)的名稱(chēng)
PARFILE?指定傳遞給導(dǎo)出實(shí)用程序的參數(shù)文件名
TABLESPACES?按表空間方式導(dǎo)出時(shí)，指定要導(dǎo)出的表空間名

按用戶方式導(dǎo)出數(shù)據(jù)
exp?scott/tiger@tsinghua?file=scott_back owner=scott

按表方式導(dǎo)出數(shù)據(jù)
exp?scott/tiger@tsinghua?tables=(emp, dept) file=scott_back_tab

按表空間方式導(dǎo)出數(shù)據(jù)
exp?system/zl@tsinghua?tablespaces=(users) file=tbs_users

使用參數(shù)文件導(dǎo)出數(shù)據(jù)
exp system/zl parfile='C:parameters.txt'

===============================================================
導(dǎo)入實(shí)用程序有如下常用命令參數(shù)

USERID?指定執(zhí)行導(dǎo)入的用戶名和密碼
BUFFER?指定用來(lái)讀取數(shù)據(jù)的緩沖區(qū)大小，以字節(jié)為單位
COMMIT?指定是否在每個(gè)數(shù)組（其大小由BUFFER參數(shù)設(shè)置）插入后進(jìn)行提交
FILE?指定要導(dǎo)入的二進(jìn)制文件名
FROMUSER?指定要從導(dǎo)出轉(zhuǎn)儲(chǔ)文件中導(dǎo)入的用戶模式
TOUSER?指定要將對(duì)象導(dǎo)入的用戶名。FROMUSER與TOUSER可以不同
FULL?指定是否要導(dǎo)入整個(gè)導(dǎo)出轉(zhuǎn)儲(chǔ)文件
TABLES?指定要導(dǎo)入的表的列表
ROWS?指定是否要導(dǎo)入表中的行
PARFILE?指定傳遞給導(dǎo)入實(shí)用程序的參數(shù)文件名，此文件可以包含這里列出的所有參數(shù)
IGNORE?導(dǎo)入時(shí)是否忽略遇到的錯(cuò)誤，默認(rèn)為N
TABLESPACES?按表空間方式導(dǎo)入，列出要導(dǎo)入的表空間名

將整個(gè)文件導(dǎo)入數(shù)據(jù)庫(kù)
imp?tsinghua/tsinghua@tsinghua?file=item_back.dmp ignore=y full=y

將scott用戶的表導(dǎo)入到martin用戶
imp?system/zl@tsinghua?file=scott_back fromuser=scott touser=martin tables=(emp,dept)

使用參數(shù)文件導(dǎo)入數(shù)據(jù)
imp system/oracle parfile='C:parameters.txt'
===============================================================
歸檔日志方式下的數(shù)據(jù)庫(kù):自動(dòng)歸檔和手動(dòng)歸檔

手動(dòng)歸檔允許用戶手動(dòng)歸檔非活動(dòng)日志文件文件的已填充組
自動(dòng)歸檔對(duì)非活動(dòng)日志文件文件進(jìn)行自動(dòng)歸檔
===============================================================
數(shù)據(jù)庫(kù)可在兩種方式下運(yùn)行：非歸檔日志方式和歸檔日志方式
非歸檔日志方式可以避免實(shí)例故障，但無(wú)法避免介質(zhì)故障。在此方式下，數(shù)據(jù)庫(kù)只能實(shí)施冷備份.
歸檔日志方式產(chǎn)生歸檔日志，用戶可以使用歸檔日志完全恢復(fù)數(shù)據(jù)庫(kù).
===============================================================

導(dǎo)出和導(dǎo)入實(shí)用程序的特點(diǎn)有：
可以按時(shí)間保存表結(jié)構(gòu)和數(shù)據(jù)
1.允許導(dǎo)出指定的表，并重新導(dǎo)入到新的數(shù)據(jù)庫(kù)中
2.可以把數(shù)據(jù)庫(kù)遷移到另外一臺(tái)異構(gòu)服務(wù)器上
3.在兩個(gè)不同版本的Oracle數(shù)據(jù)庫(kù)之間傳輸數(shù)據(jù)
4.在聯(lián)機(jī)狀態(tài)下進(jìn)行備份和恢復(fù)
5.可以重新組織表的存儲(chǔ)結(jié)構(gòu)，減少鏈接及磁盤(pán)碎片
===============================================================

oracle表輸格式為xml全過(guò)程.txt

SQL> conn sys/sys as sysdba
已連接。
SQL> drop directory test_dir
??2??/

目錄已丟棄。

SQL> CREATE DIRECTORY TEST_DIR AS 'C:';

目錄已創(chuàng)建。

SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO rbb;

授權(quán)成功。

SQL> conn rbb/rbb
已連接。
SQL> ed
已寫(xiě)入文件 afiedt.buf

??1??DECLARE
??2????src CLOB;
??3????xmlfile UTL_FILE.FILE_TYPE;
??4????length INTEGER;
??5????buffer VARCHAR2(16384);
??6??BEGIN
??7????src := DBMS_XMLQuery.getXml('select * from liuxing');
??8????length := DBMS_LOB.GETLENGTH(src);
??9????DBMS_LOB.READ(src, length, 1, buffer);
?10????xmlfile := UTL_FILE.FOPEN('TEST_DIR', 'emp.xml', 'w');
?11????UTL_FILE.PUT(xmlfile, buffer);
?12????UTL_FILE.FCLOSE(xmlfile);
?13* END;
SQL> /

PL/SQL 過(guò)程已成功完成。
===============================================================

SQL> ed
已寫(xiě)入文件 afiedt.buf

??1??declare
??2??lname number;
??3??counter number;
??4??begin
??5??counter:=1;
??6??while counter<=10
??7??loop
??8??lname:=dbms_random.random;
??9??dbms_output.put_line(lname);
?10??counter:=counter+1;
?11??end loop;
?12* end;
SQL> /
277652640
-479979827
-1049652647
-1006595853
1252280346
196435204
466478280
-85782435
-1489036577
-927786638

PL/SQL 過(guò)程已成功完成。

已用時(shí)間:??00: 00: 00.00
===============================================================

修改表名

alter table old_table_name rename to new_table_name;
===============================================================
估算SQL執(zhí)行的I/O數(shù)

SQL>SET AUTOTRACE ON ;

SQL>SELECT * FROM TABLE;

OR

SQL>SELECT * FROM v$filestat ;
===============================================================

?如何查有多少個(gè)數(shù)據(jù)庫(kù)實(shí)例

SQL>SELECT * FROM V$INSTANCE;
===============================================================

查詢(xún)數(shù)據(jù)庫(kù)有多少表

SQL>select * from all_tables;

===============================================================

顯示測(cè)試SQL語(yǔ)句執(zhí)行所用的時(shí)間

SQL>set timing on ;
===============================================================

監(jiān)控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
===============================================================
回滾段的爭(zhēng)用情況
select name, waits, gets, waits/gets "Ratio"
from v$rollstat C, v$rollname D
where C.usn = D.usn;
===============================================================
監(jiān)控表空間的 I/O 比例
select B.tablespace_name name,B.file_name "file",A.phyrds pyr,
A.phyblkrd pbr,A.phywrts pyw, A.phyblkwrt pbw
from v$filestat A, dba_data_files B
where A.file# = B.file_id
order by B.tablespace_name;
===============================================================
監(jiān)控文件系統(tǒng)的 I/O 比例
select substr(C.file#,1,2) "#", substr(C.name,1,30) "Name",
C.status, C.bytes, D.phyrds, D.phywrts
from v$datafile C, v$filestat D
where C.file# = D.file#;
===============================================================
在某個(gè)用戶下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
===============================================================
監(jiān)控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
===============================================================
監(jiān)控 SGA 中字典緩沖區(qū)的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
===============================================================
監(jiān)控 SGA 中共享緩存區(qū)的命中率，應(yīng)該小于1%?
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;

select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
===============================================================
顯示所有數(shù)據(jù)庫(kù)對(duì)象的類(lèi)別和大小
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
===============================================================
監(jiān)控 SGA 中重做日志緩存區(qū)的命中率，應(yīng)該小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
===============================================================
監(jiān)控內(nèi)存和硬盤(pán)的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

===============================================================
監(jiān)控當(dāng)前數(shù)據(jù)庫(kù)誰(shuí)在運(yùn)行什么SQL語(yǔ)句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
===============================================================
監(jiān)控字典緩沖區(qū)
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
===============================================================
后者除以前者,此比率小于1%,接近0%為好。

SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
===============================================================

監(jiān)控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5時(shí)，參數(shù)需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers時(shí)，參數(shù)需加大
===============================================================
知道當(dāng)前用戶的ID號(hào)
SQL>SHOW USER;
OR
SQL>select user from dual;
===============================================================
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
===============================================================
知道表在表空間中的存儲(chǔ)情況
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
===============================================================
知道索引在表空間中的存儲(chǔ)情況
select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;
===============================================================
知道使用CPU多的用戶session 11是cpu used by this session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc;
===============================================================

?

?

?

?

?

?

?

?

?

?

?

?

?

?

**************************************************SQLServer語(yǔ)言基本語(yǔ)句***********************************************************

SQL分類(lèi)：

　　DDL—數(shù)據(jù)定義語(yǔ)言(CREATE，ALTER，DROP，DECLARE)
　　DML—數(shù)據(jù)操縱語(yǔ)言(SELECT，DELETE，UPDATE，INSERT)
　　DCL—數(shù)據(jù)控制語(yǔ)言(GRANT，REVOKE，COMMIT，ROLLBACK)

　　首先,簡(jiǎn)要介紹基礎(chǔ)語(yǔ)句：
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　1、說(shuō)明：創(chuàng)建數(shù)據(jù)庫(kù)

CREATE DATABASE database-name
create database database
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　2、說(shuō)明：刪除數(shù)據(jù)庫(kù)
drop database database
drop database dbname
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　3、說(shuō)明：備份sql server

　　--- 創(chuàng)建 備份數(shù)據(jù)的 device
user pubs
exec database 'disk'
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'

　　--- 開(kāi)始 備份
backup database pubs to testback
BACKUP DATABASE pubs TO testBack
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　4、說(shuō)明：創(chuàng)建新表
create table tabname(name nvchar(20) primary key,password nvchar(16))
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

　　根據(jù)已有的表創(chuàng)建新表：

A：create table tab_new like tab_old (使用舊表創(chuàng)建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　5、說(shuō)明：

　　刪除新表：drop table tabname
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　6、說(shuō)明：

　　增加一個(gè)列：Alter table tabname add column col type
alter table tabanme add column col

　　注：列增加后將不能刪除。DB2中列加上后數(shù)據(jù)類(lèi)型也不能改變，唯一能改變的是增加varchar類(lèi)型的長(zhǎng)度。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　7、說(shuō)明：

　　添加主鍵：Alter table tabname add primary key(col)

　　說(shuō)明：

　　刪除主鍵：Alter table tabname drop primary key(col)
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　8、說(shuō)明：

　　創(chuàng)建索引：create [unique] index idxname on tabname(col….)

　　刪除索引：drop index idxname

　　注：索引是不可更改的，想更改必須刪除重新建。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　9、說(shuō)明：

　　創(chuàng)建視圖：create view viewname as select statement

　　刪除視圖：drop view viewname
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　10、說(shuō)明：幾個(gè)簡(jiǎn)單的基本的sql語(yǔ)句

　　選擇：select * from table1 where 范圍

　　插入：insert into table1(field1,field2) values(value1,value2)

　　刪除：delete from table1 where 范圍

　　更新：update table1 set field1=value1 where 范圍

　　查找：select * from table1 where field1 like ’%value1%’ ---like的語(yǔ)法很精妙，查資料!

　　排序：select * from table1 order by field1,field2 [desc]

　　總數(shù)：select count * as totalcount from table1

　　求和：select sum(field1) as sumvalue from table1

　　平均：select avg(field1) as avgvalue from table1

　　最大：select max(field1) as maxvalue from table1

　　最小：select min(field1) as minvalue from table1
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　11、說(shuō)明：幾個(gè)高級(jí)查詢(xún)運(yùn)算詞
下列語(yǔ)句創(chuàng)建 STAFF 表 中 20 部門(mén)的非經(jīng)理人員視圖，其中薪水和傭金不通過(guò)基表顯示。

　　CREATE VIEW STAFF_ONLY

　　AS SELECT ID, NAME, DEPT, JOB, YEARS

　　FROM STAFF

　　WHERE JOB <> 'Mgr' AND DEPT=20

　　在創(chuàng)建視圖之后，下列語(yǔ)句顯示視圖的內(nèi)容:

　　SELECT *　FROM STAFF_ONLY

　　A： UNION union 運(yùn)算符

　　UNION 運(yùn)算符通過(guò)組合其他兩個(gè)結(jié)果表（例如 TABLE1 和 TABLE2）并消去表中任何重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 UNION 一起使用時(shí)（即 UNION ALL），不消除重復(fù)行。兩種情況下，派生表的每一行不是來(lái)自 TABLE1 就是來(lái)自 TABLE2。

　　B： EXCEPT except運(yùn)算符

　　EXCEPT 運(yùn)算符通過(guò)包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 EXCEPT 一起使用時(shí) (EXCEPT ALL)，不消除重復(fù)行。

　　C： INTERSECT intersect運(yùn)算符

　　INTERSECT 運(yùn)算符通過(guò)只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 INTERSECT 一起使用時(shí) (INTERSECT ALL)，不消除重復(fù)行。

　　注：使用運(yùn)算詞的幾個(gè)查詢(xún)結(jié)果行必須是一致的。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　12、說(shuō)明：使用外連接

　　A、left outer join：

　　左外連接（左連接）：結(jié)果集幾包括連接表的匹配行，也包括左連接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

　　B：right outer join:

　　右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右連接表的所有行。

　　C：full outer join：

　　全外連接：不僅包括符號(hào)連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。

　　其次，大家來(lái)看一些不錯(cuò)的sql語(yǔ)句

　　1、說(shuō)明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a 新表名：b) (Access可用)

　　法一：select * into b from a where 1<>1

　　法二：select top 0 * into b from a

　　2、說(shuō)明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b) (Access可用)

insert into b(a, b, c) select d,e,f from b;

　　3、說(shuō)明：跨數(shù)據(jù)庫(kù)之間表的拷貝(具體數(shù)據(jù)使用絕對(duì)路徑) (Access可用)

insert into b(a, b, c) select d,e,f from b in ‘具體數(shù)據(jù)庫(kù)’ where 條件

　　例子：..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..

　　4、說(shuō)明：子查詢(xún)(表名1：a 表名2：b)

select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

　　5、說(shuō)明：顯示文章、提交人和最后回復(fù)時(shí)間

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

　　6、說(shuō)明：外連接查詢(xún)(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

　　7、說(shuō)明：在線視圖查詢(xún)(表名1：a )

select * from (SELECT a,b,c FROM a) T where t.a > 1;

　　8、說(shuō)明：between的用法,between限制查詢(xún)數(shù)據(jù)范圍時(shí)包括了邊界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 數(shù)值1 and 數(shù)值2

　　9、說(shuō)明：in 的使用方法

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

　　10、說(shuō)明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒(méi)有的信息

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

　　11、說(shuō)明：四表聯(lián)查問(wèn)題：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

　　12、說(shuō)明：日程安排提前五分鐘提醒

SQL: select * from 日程安排 where datediff('minute',f開(kāi)始時(shí)間,getdate())>5

　　13、說(shuō)明：一條sql 語(yǔ)句搞定數(shù)據(jù)庫(kù)分頁(yè)

select top 10 b.* from (select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段

　　14、說(shuō)明：前10條記錄

select top 10 * form table1 where 范圍

　　15、說(shuō)明：選擇在每一組b值相同的數(shù)據(jù)中對(duì)應(yīng)的a最大的記錄的所有信息(類(lèi)似這樣的用法可以用于論壇每月排行榜,每月熱銷(xiāo)產(chǎn)品分析,按科目成績(jī)排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

　　16、說(shuō)明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表

(select a from tableA ) except (select a from tableB) except (select a from tableC)

　　17、說(shuō)明：隨機(jī)取出10條數(shù)據(jù)

select top 10 * from tablename order by newid()

　　18、說(shuō)明：隨機(jī)選擇記錄

select newid()

　　19、說(shuō)明：刪除重復(fù)記錄

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

　　20、說(shuō)明：列出數(shù)據(jù)庫(kù)里所有的表名

select name from sysobjects where type='U'

　　21、說(shuō)明：列出表里的所有的

select name from syscolumns where id=object_id('TableName')

　　22、說(shuō)明：列示type、vender、pcs字段，以type字段排列，case可以方便地實(shí)現(xiàn)多重選擇，類(lèi)似select 中的case。

select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type

　　顯示結(jié)果：

type????vender pcs
電腦 A 1
電腦 A 1
光盤(pán) B 2
光盤(pán) A 2
手機(jī) B 3
手機(jī) C 3

　　23、說(shuō)明：初始化表table1

TRUNCATE TABLE table1

　　24、說(shuō)明：選擇從10到15的記錄

select top 5 * from (select top 15 * from table order by id asc) table_別名 order by id desc

　　隨機(jī)選擇數(shù)據(jù)庫(kù)記錄的方法（使用Randomize函數(shù)，通過(guò)SQL語(yǔ)句實(shí)現(xiàn)）

　　對(duì)存儲(chǔ)在數(shù)據(jù)庫(kù)中的數(shù)據(jù)來(lái)說(shuō)，隨機(jī)數(shù)特性能給出上面的效果，但它們可能太慢了些。你不能要求ASP“找個(gè)隨機(jī)數(shù)”然后打印出來(lái)。實(shí)際上常見(jiàn)的解決方案是建立如下所示的循環(huán)：

Randomize
RNumber = Int(Rnd*499) +1
　
While Not objRec.EOF
If objRec("ID") = RNumber THEN
... 這里是執(zhí)行腳本 ...
end if
objRec.MoveNext
Wend

　　這很容易理解。首先，你取出1到500范圍之內(nèi)的一個(gè)隨機(jī)數(shù)（假設(shè)500就是數(shù)據(jù)庫(kù)內(nèi)記錄的總數(shù)）。然后，你遍歷每一記錄來(lái)測(cè)試ID 的值、檢查其是否匹配RNumber。滿足條件的話就執(zhí)行由THEN 關(guān)鍵字開(kāi)始的那一塊代碼。假如你的RNumber 等于495，那么要循環(huán)一遍數(shù)據(jù)庫(kù)花的時(shí)間可就長(zhǎng)了。雖然500這個(gè)數(shù)字看起來(lái)大了些，但相比更為穩(wěn)固的企業(yè)解決方案這還是個(gè)小型數(shù)據(jù)庫(kù)了，后者通常在一個(gè)數(shù)據(jù)庫(kù)內(nèi)就包含了成千上萬(wàn)條記錄。這時(shí)候不就死定了？

　　采用SQL，你就可以很快地找出準(zhǔn)確的記錄并且打開(kāi)一個(gè)只包含該記錄的recordset，如下所示：

Randomize
RNumber = Int(Rnd*499) + 1
　
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber
　
set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email")

　　不必寫(xiě)出RNumber 和ID，你只需要檢查匹配情況即可。只要你對(duì)以上代碼的工作滿意，你自可按需操作“隨機(jī)”記錄。Recordset沒(méi)有包含其他內(nèi)容，因此你很快就能找到你需要的記錄這樣就大大降低了處理時(shí)間。

　　再談隨機(jī)數(shù)

　　現(xiàn)在你下定決心要榨干Random 函數(shù)的最后一滴油，那么你可能會(huì)一次取出多條隨機(jī)記錄或者想采用一定隨機(jī)范圍內(nèi)的記錄。把上面的標(biāo)準(zhǔn)Random 示例擴(kuò)展一下就可以用SQL應(yīng)對(duì)上面兩種情況了。

　　為了取出幾條隨機(jī)選擇的記錄并存放在同一recordset內(nèi)，你可以存儲(chǔ)三個(gè)隨機(jī)數(shù)，然后查詢(xún)數(shù)據(jù)庫(kù)獲得匹配這些數(shù)字的記錄：

　　SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3

　　假如你想選出10條記錄（也許是每次頁(yè)面裝載時(shí)的10條鏈接的列表），你可以用BETWEEN 或者數(shù)學(xué)等式選出第一條記錄和適當(dāng)數(shù)量的遞增記錄。這一操作可以通過(guò)好幾種方式來(lái)完成，但是 SELECT 語(yǔ)句只顯示一種可能（這里的ID 是自動(dòng)生成的號(hào)碼）：
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"

　　注意：以上代碼的執(zhí)行目的不是檢查數(shù)據(jù)庫(kù)內(nèi)是否有9條并發(fā)記錄。

　　隨機(jī)讀取若干條記錄，測(cè)試過(guò)

Access語(yǔ)法：SELECT top 10 * From 表名 ORDER BY Rnd(id)
Sql server:select top n * from 表名 order by newid()
mysql select * From 表名 Order By rand() Limit n

　　Access左連接語(yǔ)法(最近開(kāi)發(fā)要用左連接,Access幫助什么都沒(méi)有,網(wǎng)上沒(méi)有Access的SQL說(shuō)明,只有自己測(cè)試, 現(xiàn)在記下以備后查)

　　語(yǔ)法 select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where ...

　　使用SQL語(yǔ)句 用...代替過(guò)長(zhǎng)的字符串顯示

　　語(yǔ)法：

　　SQL數(shù)據(jù)庫(kù)：select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename
　　Access數(shù)據(jù)庫(kù)：SELECT iif(len(field)>2,left(field,2)+'...',field) FROM tablename;

　　Conn.Execute說(shuō)明

　　Execute方法

　　該方法用于執(zhí)行SQL語(yǔ)句。根據(jù)SQL語(yǔ)句執(zhí)行后是否返回記錄集，該方法的使用格式分為以下兩種：

　　1．執(zhí)行SQL查詢(xún)語(yǔ)句時(shí)，將返回查詢(xún)得到的記錄集。用法為：

　　Set 對(duì)象變量名=連接對(duì)象.Execute("SQL 查詢(xún)語(yǔ)言")

　　Execute方法調(diào)用后，會(huì)自動(dòng)創(chuàng)建記錄集對(duì)象，并將查詢(xún)結(jié)果存儲(chǔ)在該記錄對(duì)象中，通過(guò)Set方法，將記錄集賦給指定的對(duì)象保存，以后對(duì)象變量就代表了該記錄集對(duì)象。

　　2．執(zhí)行SQL的操作性語(yǔ)言時(shí)，沒(méi)有記錄集的返回。此時(shí)用法為：

　　連接對(duì)象.Execute "SQL 操作性語(yǔ)句" [, RecordAffected][, Option]

　　·RecordAffected 為可選項(xiàng)，此出可放置一個(gè)變量，SQL語(yǔ)句執(zhí)行后，所生效的記錄數(shù)會(huì)自動(dòng)保存到該變量中。通過(guò)訪問(wèn)該變量，就可知道SQL語(yǔ)句隊(duì)多少條記錄進(jìn)行了操作。

　　·Option 可選項(xiàng)，該參數(shù)的取值通常為adCMDText，它用于告訴ADO，應(yīng)該將Execute方法之后的第一個(gè)字符解釋為命令文本。通過(guò)指定該參數(shù)，可使執(zhí)行更高效。

　　·BeginTrans、RollbackTrans、CommitTrans方法

　　這三個(gè)方法是連接對(duì)象提供的用于事務(wù)處理的方法。BeginTrans用于開(kāi)始一個(gè)事物；RollbackTrans用于回滾事務(wù)；CommitTrans用于提交所有的事務(wù)處理結(jié)果，即確認(rèn)事務(wù)的處理。

　　事務(wù)處理可以將一組操作視為一個(gè)整體，只有全部語(yǔ)句都成功執(zhí)行后，事務(wù)處理才算成功；若其中有一個(gè)語(yǔ)句執(zhí)行失敗，則整個(gè)處理就算失敗，并恢復(fù)到處里前的狀態(tài)。

　　BeginTrans和CommitTrans用于標(biāo)記事務(wù)的開(kāi)始和結(jié)束，在這兩個(gè)之間的語(yǔ)句，就是作為事務(wù)處理的語(yǔ)句。判斷事務(wù)處理是否成功，可通過(guò)連接對(duì)象的Error集合來(lái)實(shí)現(xiàn)，若Error集合的成員個(gè)數(shù)不為0，則說(shuō)明有錯(cuò)誤發(fā)生，事務(wù)處理失敗。Error集合中的每一個(gè)Error對(duì)象，代表一個(gè)錯(cuò)誤信息。

?

?

?

?

?

?

?

?

?

?

?

?

***************************************各種數(shù)據(jù)庫(kù)連接方法**********************************************************************

==================TestSQLServer.java連接SQLServer====================================

import java.sql.*;
import java.util.*;
public class TestSQLServer{?
???public static void main (String args[]){?????????
??????try{?
??Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
??String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=testdb";
??String user ="sa";
??String password = "sa";??
??Connection conn = DriverManager.getConnection(url,user,password);??????
?????????Statement stat = conn.createStatement();
?????????ResultSet result = stat.executeQuery("SELECT * FROM users");????????
?????????result.next();????????
?????????System.out.println(result.getString(1));
?????????System.out.println(result.getString(2));
?????????System.out.println(result.getString(3));????????
?????????result.close();?????
?????????stat.close();
?????????conn.close();
????????
??????}catch(ClassNotFoundException en){
????????System.out.println("數(shù)據(jù)庫(kù)驅(qū)動(dòng)找不到！");
????????en.printStackTrace();?????
??????}catch(SQLException ex) {???????
?????????while (ex != null) {?
????????????ex.printStackTrace();
????????????ex = ex.getNextException();
?????????}????????
??????}catch(Exception e){
??????????System.out.println("其他未知異常！");
??????????e.printStackTrace();
??????}
???}
}

=================TestMysql.java連接mysql數(shù)據(jù)庫(kù)=======================================

package org.binbo.dom;
import java.sql.*;
public class TestMysql{?
???public static void main (String args[]){?????????
??????try{?
??Class.forName("com.mysql.jdbc.Driver").newInstance();??
??String url = "jdbc:mysql://localhost:3306/binbo";
??String user ="root";
??String password = "binbo";??
??Connection conn = DriverManager.getConnection(url,user,password);
??
????????Statement stat = conn.createStatement();
?????????ResultSet result = stat.executeQuery("SELECT * FROM testxml");????????
?????????result.next();????????
?????????System.out.println(result.getString(1));
?????????System.out.println(result.getString(2));
?????????System.out.println(result.getString(3));????????
?????????result.close();?????
?????????stat.close();
?????????conn.close();????????
??????}catch(ClassNotFoundException en){
????????System.out.println("數(shù)據(jù)庫(kù)驅(qū)動(dòng)找不到！");
????????en.printStackTrace();?????
??????}catch(SQLException ex) {???????
?????????while (ex != null) {?
????????????ex.printStackTrace();
????????????ex = ex.getNextException();
?????????}????????
??????}catch(Exception e){
??????????System.out.println("其他未知異常！");
??????????e.printStackTrace();
??????}
???}

}
=================TestOracle.java連接oracle數(shù)據(jù)庫(kù)==================================

package org.binbo.dom;
import java.sql.*;
public class TestOracle{?
???public static void main (String args[]){?????????
??????try{?
??????Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();??
??????String url = "jdbc:oracle:thin:@localhost:1521:binbo";
??????String user ="scott";
??????String password = "tiger";???
??Connection conn = DriverManager.getConnection(url,user,password);
??
????????Statement stat = conn.createStatement();
?????????ResultSet result = stat.executeQuery("SELECT * FROM test");????????
?????????result.next();????????
?????????System.out.println(result.getString(1));
?????????System.out.println(result.getString(2));
?????????System.out.println(result.getString(3));????????
?????????result.close();?????
?????????stat.close();
?????????conn.close();????????
??????}catch(ClassNotFoundException en){
????????System.out.println("數(shù)據(jù)庫(kù)驅(qū)動(dòng)找不到！");
????????en.printStackTrace();?????
??????}catch(SQLException ex) {???????
?????????while (ex != null) {?
????????????ex.printStackTrace();
????????????ex = ex.getNextException();
?????????}????????
??????}catch(Exception e){
??????????System.out.println("其他未知異常！");
??????????e.printStackTrace();
??????}
???}
}
===============XML連接數(shù)據(jù)庫(kù)=====================================================
da.xml
<?xml version="1.0" encoding="UTF-8"?>
<PEOPLE><!--
??<PERSON PERSONID="E01">
????<className>com.microsoft.jdbc.sqlserver.SQLServerDriver</className>
????<url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=binbo</url>
????<user>sa</user>
????<password>sa</password>
??</PERSON>
??--><PERSON PERSONID="E02">
?????<className>com.mysql.jdbc.Driver</className>
????<url>jdbc:mysql://localhost:3306/binbo</url>
????<user>root</user>
????<password>binbo</password>
??</PERSON><!--
?<PERSON PERSONID="E03">
?????<className>oracle.jdbc.driver.OracleDriver</className>
????<url>jdbc:oracle:thin:@localhost:1521:binbo</url>
????<user>scott</user>
????<password>tiger</password>
??</PERSON>
--></PEOPLE>
--------------------------------------------
content.java
package org.binbo.dom;
import java.sql.Connection;
import java.sql.DriverManager;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
public class Content {
?public static Connection getConnection(){
??Connection conn =null;
??try {
???DocumentBuilderFactory factory=DocumentBuilderFactory.newInstance();
???DocumentBuilder builder=factory.newDocumentBuilder();
???Document doc=builder.parse("da.xml");
???NodeList nl=doc.getElementsByTagName_r("PERSON");
???Element node=(Element) nl.item(0);
???
?String className =node.getElementsByTagName_r("className").item(0).getFirstChild().getNodeValue();
????????
?String url =node.getElementsByTagName_r("url").item(0).getFirstChild().getNodeValue();
????????
?String user =node.getElementsByTagName_r("user").item(0).getFirstChild().getNodeValue();
???????
?String pwd =node.getElementsByTagName_r("password").item(0).getFirstChild().getNodeValue();???????
????????Class.forName(className);
????????conn =DriverManager.getConnection(url,user,pwd);?????
??
??} catch (Exception e) {
???e.printStackTrace();
??}
??return conn;
?}
}
--------------------------------------------
Domtest.java
package org.binbo.dom;
import java.io.FileOutputStream;
import java.sql.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class Domtest {
?public static void main(String[] args){
??try{
???Connection conn =Content.getConnection();
???PreparedStatement ps=conn.prepareStatement("select * from testxml");
???ResultSet rs =ps.executeQuery();
???DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
???DocumentBuilder builder = factory.newDocumentBuilder();
???Document doc = builder.newDocument();
???Element Stu = doc.createElement_x("binboxml");
???Element uname = doc.createElement_x("username");
???Element nameid = doc.createElement_x("userid");
???Element pass = doc.createElement_x("password");
???while(rs.next()){????
????String id=rs.getString(1);
????String name=rs.getString(2);
????String pwd=rs.getString(3);
????System.out.print(rs.getString(1));
????System.out.print(rs.getString(2));
????System.out.print(rs.getString(3));
???????System.out.print("寫(xiě)入成功！");
????nameid.appendChild(doc.createTextNode(id));
????uname.appendChild(doc.createTextNode(name));
????pass.appendChild(doc.createTextNode(pwd));
???}
???
???Stu.appendChild(nameid);???
???Stu.appendChild(uname);
???Stu.appendChild(pass);
???doc.appendChild(Stu);
???TransformerFactory tf = TransformerFactory.newInstance();
???Transformer tr = tf.newTransformer();
???tr.transform(new DOMSource(doc), new StreamResult(
?????new FileOutputStream("Binbo.xml")));
???rs.close();
??} catch(Exception e){
???e.printStackTrace();
??}

?}

}

=====================數(shù)據(jù)庫(kù)連接池=====================================

加到tomcat中的server.xml

<Logger className="org.apache.catalina.logger.FileLogger"
?????????????????directory="logs"??prefix="localhost_log." suffix=".txt"
????????????timestamp="true"/>

這個(gè)后面
<Context path="/myjsp" docBase="myjsp"
????????debug="5" reloadable="true" crossContext="true">
???????<Resource name="jdbc/myjsp"
???????????????auth="Container"
???????????????type="javax.sql.DataSource"/>
??<ResourceParams name="jdbc/myjsp">
????<parameter>
??????<name>factory</name>
??????<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
????</parameter>
????<parameter>
??????<name>maxActive</name>
??????<value>100</value>
????</parameter>
????<parameter>
??????<name>maxIdle</name>
??????<value>30</value>
????</parameter>
????<parameter>
??????<name>maxWait</name>
??????<value>10000</value>
????</parameter>
?
????<parameter>
?????<name>username</name>
?????<value>sa</value>
????</parameter>
????<parameter>
?????<name>password</name>
?????<value>sa</value>
????</parameter>
????<parameter>
???????<name>driverClassName</name>
???????<value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
????</parameter>
????<parameter>
??????<name>url</name>
??????<value>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jsp</value>
????</parameter>
??</ResourceParams>
</Context>
-----------------------------------------------------------------------
用DBPool.java獲得數(shù)據(jù)庫(kù)連接池
package com.binbo.dbo;
import java.sql.Connection;
import java.sql.DriverManager;
import javax.naming.Context;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBPool {
?public static synchronized Connection getConnection()throws Exception{
??DataSource ds = null;
??try{
??Context ininCtx??= new javax.naming.InitialContext();
??
??Context envCtx = (Context)ininCtx.lookup("java:comp/env");
????ds = (DataSource)envCtx.lookup("jdbc/myjsp");
??}catch(NamingException e){
???e.printStackTrace();???
??}??
??Connection conn = ds.getConnection();
??return conn;??
?}
}
-----------------------------------------------------------
操作數(shù)據(jù)庫(kù)
package com.binbo.dbo;
import java.sql.*;
import com.binbo.javabean.BreakBean;
import com.binbo.javabean.OpenBean;
public class DataBaseClass {
?private Statement sta = null;
?private ResultSet rs = null;
?Connection conn = null;
?private int count;
?public DataBaseClass() throws Exception {
??// 取得數(shù)據(jù)庫(kù)的連接
??conn = DBPool.getConnection();
??sta = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
????ResultSet.CONCUR_UPDATABLE);
?}
?public void getExecute(String sql) {
??try {
???System.out.println(sql);
???sta.executeUpdate(sql);
??} catch (SQLException e) {
???e.printStackTrace();
??}
?}

?
?public ResultSet getQuery(String sql) {
??try {
???System.out.println(sql);
???rs = sta.executeQuery(sql);
??} catch (SQLException e) {
???e.printStackTrace();
??}
??return rs;
?}

?
??* 取得結(jié)果集的行數(shù)
?
?public int getCount(ResultSet rs2) {
??try {
???rs2.next();
???count = rs2.getRow();
??} catch (SQLException e) {
???e.printStackTrace();
??}
??return count;
?}
??//添加！?
?public void getRegister(OpenBean open) {
??String sql = "insert into f_info(nam,email,titl,content,tem,mid)values(?,?,?,?,?,?)";
??try {
???PreparedStatement ps = conn.prepareStatement(sql);
???ps.setString(1, open.getNam());
???ps.setString(2, open.getEmail());
???ps.setString(3, open.getTitl());
???ps.setString(4, open.getContent());
???ps.setString(5, open.getTem());
???ps.setString(6, open.getDepa());
???ps.execute();
??} catch (SQLException e) {
???e.printStackTrace();
??}
?}
????????// 刪除
?public void getRealys(OpenBean real) {
??try {
???String sql = "delete from departments where id=?";
???PreparedStatement ppt = conn.prepareStatement(sql);
???ppt.setString(1, real.getUnam());
???ppt.execute();

??} catch (SQLException e) {
???e.printStackTrace();
??}

?}

?// 查詢(xún)
?public void getRealy(OpenBean hg) {

??try {
???String sql = "select * from departments where id=?";
???PreparedStatement ppg = conn.prepareStatement(sql);
???ppg.setString(1, hg.getUnam());
???ppg.execute();
??} catch (SQLException e) {
???e.printStackTrace();
??}
?}
}
============================hibernate操作數(shù)據(jù)庫(kù)====================================
package com.binbo.hibernate.xml;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class PersonOperate {
?private Session session = null ;
?public PersonOperate()
?{
??Configuration config = new Configuration().configure() ;
??SessionFactory factory = config.buildSessionFactory() ;
??this.session = factory.openSession() ;
?}?
?// 增加數(shù)據(jù)
?public void insert(Person p)
?{
??Transaction tran = this.session.beginTransaction() ;
??this.session.save(p) ;
??tran.commit() ;
??this.session.close() ;
?}
------------------------------------------
增加數(shù)據(jù)調(diào)用
????????????????LinOperate po = new LinOperate();
????????????????Lin p = new Lin();
??p.setLname(lname);
??p.setLmonery(lmonery);
??p.setLmain(lmain);
??p.setLtime(ltime);
??LinOperate po = new LinOperate();
??po.insert(p);
??errors.add("success", new ActionMessage("xiangxi"));
??request.setAttribute("org.apache.struts.action.ERROR", errors);
??return mapping.findForward("jinru");
------------------------------------------
?
?// 修改
?public void update(Person p)
?{
??Transaction tran = this.session.beginTransaction() ;
??this.session.update(p) ;
??tran.commit() ;
??this.session.close() ;
?}?
?// 用戶登錄
?public boolean queryById(Person person)
?{
??boolean flag = false;
??String hql = "from Person as p where p.id=? and p.password=?" ;
??Query q = this.session.createQuery(hql) ;
??q.setString(0,person.getId()) ;
??q.setString(1,person.getPassword());
??Iterator iter = q.list().iterator();
??if (iter.hasNext()) {
???flag = true;
???person.setName(((Person) iter.next()).getName());
???
??}
??this.session.close() ;
??return flag;
?}
?//驗(yàn)證用戶存不存在
?public boolean queryC(Person person)
?{
??boolean flag = false;
??String hql = "from Person as p where p.name=?" ;
??Query q = this.session.createQuery(hql) ;
??q.setString(0,person.getName()) ;
??Iterator iter = q.list().iterator();
??if (iter.hasNext()) {
???flag = true;???
??}
??this.session.close() ;
??return flag;
?}
?//查看個(gè)人資料
?public Person queryZliao(String id)
?{
??Person p = null ;
??String hql = "from Person as p where p.id=?" ;
??Query q = this.session.createQuery(hql) ;
??q.setString(0,id) ;
??List l = q.list() ;
??Iterator iter = l.iterator() ;
??if(iter.hasNext())
??{
???p = (Person)iter.next() ;
??}
??return p ;
?}?
?// 刪除數(shù)據(jù)
?public void delete(Person p)
?{
??Transaction tran = this.session.beginTransaction() ;
??this.session.delete(p) ;
??tran.commit() ;
?}?
?// 修改
?public void delete(String name)
?{
??String hql = "delete Person where name=?" ;
??Query q = this.session.createQuery(hql) ;
??q.setString(0,name) ;
??q.executeUpdate() ;
??this.session.beginTransaction().commit() ;
?}
?
?// 查詢(xún)?nèi)繑?shù)據(jù)
?public List queryAll()
?{
??List l = null ;
??String hql = "from Person as p" ;
??Query q = this.session.createQuery(hql) ;
??l = q.list() ;
??return l ;
?}
-------------------------------
(
查詢(xún)?nèi)繑?shù)據(jù)調(diào)用
????????????????LinOperate po = new LinOperate();
??List l = po.queryAll();
??Iterator iter = l.iterator();
??ArrayList lus = new ArrayList();
??while (iter.hasNext()) {
???Lin p = (Lin) iter.next();
???Luser lu = new Luser();
???lu.setName(p.getLname());
???lu.setChange(p.getLmonery());
???lu.setMainn(p.getLmain());
???lu.setLtime(p.getLtime());
???lus.add(lu);
???System.out.print(p.getLname() + " ");
???request.setAttribute("lus", lus);

)
------------------------?
?// 模糊查詢(xún)
?public List queryByLike(String cond)
?{
??List l = null ;
??String hql = "from Person as p where p.name like ?" ;
??Query q = this.session.createQuery(hql) ;
??q.setString(0,"%"+cond+"%") ;
??l = q.list() ;
??return l ;
?}
}

總結(jié)

以上是生活随笔為你收集整理的dos命令行连接oracle数据库的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。