整合的ORACLE數(shù)據(jù)庫方面的知識.doc

連接各數(shù)據(jù)庫的連接字符串:sqlserver2000 jtds.jar class: net.sourceforge.jtds.jdbc.Driver url: jdbc:jtds:sqlserver:/localhost:1433/數(shù)據(jù)庫-sqlserver2000 (microsoft mssqlserver2.jar)class: com.microsoft.jdbc.sqlserver.SQLServerDriverurl: jdbc:microsoft:sqlserver:/localhost:1433;DatabaseName=pubs-sqlserver2005 (microsoft sqljdbc.jar)class: com.microsoft.sqlserver.jdbc.SQLServerDriverurl: jdbc:sqlserver:/localhost:1433; databaseName=pubs user:sapassword:niit(也可以設(shè)置為空)-Oracle (ojdbc.jar) class: oracle.jdbc.driver.OracleDriver url: jdbc:oracle:thin:teacher:1521:SID-mysql (mysql-connector-java-5.1.7-bin.jar) class: com.mysql.jdbc.Driverurl: jdbc:mysql:/localhost:3306/數(shù)據(jù)庫名1一些基本的操作-創(chuàng)建表-create table (.)create table student ( name varchar2(30), age number(2)select * from student;drop table qq1;-插入語句-insert into (.) values (.)insert into student (name,age) valus (小明,10);insert into student (name) values (小紅);insert into student (age) values (20);insert into student values (李四,23);-這種形式插入數(shù)據(jù)的話必須將數(shù)據(jù)填寫完整-提交，必須執(zhí)行此命令才會將數(shù)據(jù)放到數(shù)據(jù)表中(提交和回滾-否則數(shù)據(jù)一直保存在緩存中)commit;-刪掉表中的記錄delete from student;truncate table student;-清空整張表的記錄最好用這個，速度快select name from student where age=23;-2.利用現(xiàn)有的表創(chuàng)建新表的方式-create table as select from where .create table student_1 as select name from student;select * from student_1;-修改-update set =? where .update student set name=趙六 where age =23;commit;-必須有此命令來提交數(shù)據(jù)，才能真正達(dá)到更新的操作-刪除-delete from |delete from student where (name=趙六 and age=23);-注：清空表不建議使用delete from 表名，使用truncate table -表中數(shù)據(jù)量很大，用delete 需要花費較長的時間所以不建議使用truncate table student;-用命令truncate的時候并不需要commit命令要記住哦-刪除表(包括字段屬性都會刪除，而delete, truncate刪除的只是記錄而已)drop table student;select * from student ;-增加列-alter table add ( 默認(rèn)值)alter table student add (sex varchar2(2) default 男);-修改列-alter table rename column to alter table student rename column sex to code;-修改列的數(shù)據(jù)類型-alter table modify （ 列類型 默認(rèn)值）update student set code =null;commit;alter table student modify (code number(10) default 100001);update student set code=100001;select * from student;-修改表的名字-alter table rename to ;alter table student rename to teacher;select * from teacher;-刪除列-alter table drop ()；alter table teacher drop (code);select * from teacher;drop table teacher;-alter 修改的東西都不用commit 命令的哦-數(shù)據(jù)類型-字符類型-char 固定長度的字符串,最大容量是2000 個字節(jié)(即1000 個漢字)-varchar2 可變長度的字符串,最大容量是4000 個字節(jié)(即2000 個漢字)-long 可變長度的字符串，最大容量是2GBcreate table qq1( name char(10)select * from qq1;insert into qq1 values (abcdef);insert into qq1 values (dddd);commit;-增加一列varchar2類型alter table qq1 add (code varchar2(10) default null);update qq1 set code =abdef where name=abcdef ;commit;-類型為字節(jié)，一個漢字代表2 個字節(jié)insert into qq1 (name) values (常會信息職);select length(name) from qq1;-函數(shù)返回的長度是根據(jù)字符個數(shù)來計算的-數(shù)值類型 number(p,s) p表示精度 s 小數(shù)點后的位數(shù)-增加一列 number 默認(rèn)類型alter table qq1 add (account number default null);-默認(rèn)情況下插入的是實數(shù)(包括整數(shù)和小數(shù))select * from qq1;insert into qq1 (account) values (123456.789);commit;-增加一列number類型，帶精度的(保存的只是一個整數(shù)，會把小數(shù)位按照四舍五入的方式去掉)alter table qq1 add(account1 number(6) default null ); insert into qq1 (account1) values (123456.523456);commit;-增加一列number 類型，(p= 精度+ 小數(shù)點位數(shù))=38alter table qq1 add (account2 number(16,4) default null);-默認(rèn)允許為空insert into qq1 (account2) values (1234.5468);commit;-時間類型-date-增加一列date 類型alter table student add (date1 date default null);-顯示格式注意to_date 函數(shù)，年月日不能少insert into student (date1) values (to_date(2010-5-9,yyyy-mm-dd);-注意該格式的寫法yyyy-mm-dd hh24:mi:ss會顯示為24 進制的時間insert into student (date1) values (to_date(2012-12-9 11:20:26,yyyy-mm-dd hh:mi:ss);insert into student (date1) values (to_date(2012-12-9 23:20:26,yyyy-mm-dd hh24:mi:ss);commit;select * from student;drop table student;select cast(date1 as date) from student;select date1 from student;-精簡的select cast(date1 as timestamp) from student;-具體的2關(guān)于創(chuàng)建表時的運算符等create table qq1( name varchar(10)truncate table qq1;insert into qq1 (name) values (nihao);commit;select * from qq1;-大對象數(shù)據(jù)類型 Lob(clob blob bfile)-增加3 列alter table qq1 add(cl clob default null);alter table qq1 add(bl blob default null);alter table qq1 add(bf bfile default null);-for update 鎖表，修改( 必須有這個語句才能保證不被查出的數(shù)據(jù)可以被當(dāng)前用戶更改，其他用戶只可以讀取)select * from qq1 for update;-偽列rowid rownum-1.rowid 是指記錄的物理序號，用于定位數(shù)據(jù)表中的某條數(shù)據(jù)的位置唯一不可變（系統(tǒng)自動生成的）-2.rownum 是記錄的序號，表示查詢某條記錄在結(jié)果集中的位置，同一條記錄查詢條件不同，對應(yīng)的rownum不同但是rowid 不會變-3.偽列只能查詢，不能增刪改-4.rownum可以限制返回的行數(shù)select t.*,rownum from qq1 t where rownum =5;select t.*, t.rowid,rownum from qq1 t;-選擇無重復(fù)的行distinctselect distinct name from qq1;select distinct account,t.* from qq t;-對列起別名用as( 達(dá)到計算的目的)select account*2 as a from qq;-插入日期的兩種方式insert into qq (date1) values (22-12月-2012);insert into qq (date1) values (to_date(2012-12-15,yyyy-mm-dd);-插入來自其他表中的數(shù)據(jù)insert into () select from -事務(wù)控制-commit savepoint rollbackinsert into qq1 (name) values (令狐沖);insert into qq1 (name) values (楊過);savepoint p1;insert into qq1 (name) values (郭靖);insert into qq1 (name) values (小龍女);savepoint p2;insert into qq1 (name) values (喬峰);insert into qq1 (name) values (韋小寶);rollback to savepoint p1;rollback to savepoint p2;rollback ;-返回到初始點commit;select name from qq1;-sql操作符-1.算術(shù)操作符（+，-，*，/）select * from emp;-雇員表（員工）-empno 雇員的編號-ename 雇員的名字-job 雇員的工作-mgr 上級的編號-hiredate 入職時間-sal 工資-comm 獎金 -deptno 部門編號select * from dept;-部門表-deptno 部門編號-dname 部門名稱-loc 部門地址select * from salgrade;-工資等級表-grade 級別-losal 最低工資-hisal 最高工資-一年工資加獎金select * from emp;select t.*,sal*(12+1) as 年薪 from emp t;create table emp1 as select * from emp;select * from emp1;create table dept1 as select * from dept;select * from dept1;create table salgrade1 as select * from salgrade;select * from salgrade1;-更新emp1update emp1 set comm =0 where comm is null;-一年工資加獎金select t.*,(sal*(12+1)+comm*12) as 年薪 from emp1 t;select t.*,(hisal -losal) as 工資幅度 from salgrade1 t;select count(sal) from emp1 ;-個數(shù)函數(shù)select sum(sal)/count(sal) from emp1;-計算平均工資 -比較操作符-= ，！= ，,=, between.and ,in, like,is null/is not nullselect * from emp1;select * from emp1 where ename=SMITH;select * from emp1 where sal=800;select * from emp1 where sal !=800;select * from emp1 where deptno 10;-注意不能有多余的空格select * from emp1 where sal 2000;select * from emp1 where sal =1000 and sal 2000 and sal 3000;select * from dept;select * from emp1 where deptno=10 or deptno=20;select * from emp1 where not ename =SMITH;-集合操作符-union (返回兩個表中特定字段的所有數(shù)據(jù)，相同的數(shù)據(jù)只顯示一次)select deptno from emp1 union select deptno from dept;-union all ( 返回兩個表中的特定字段的所有屬性，不去除重復(fù)行 )select deptno from emp1 union all select deptno from dept;-intersect ( 返回兩個表中特定字段相同的數(shù)據(jù)行,且相同的數(shù)據(jù)只顯示一次-minus ( 返回從第一個查詢結(jié)果中去除第二個查詢結(jié)果中出現(xiàn)的行 )select deptno from emp1 minus select deptno from dept; -注：sql 優(yōu)化 ：union 替換 or ( 適用于索引項 ) 避免全表掃描-注：用 union all 替換union 可以減少查詢排序帶來的消耗-連接操作符|select * from emp1;-select (comm|ename|.) as s from emp1;select (員工的提成：|comm|員工的姓名是：|ename) as s from emp1;-員工的編號是： empno,姓名是：enameselect (員工的編號是：|empno) as a from emp1;select (工作：|job) as b from emp1;select (員工的薪水：|sal) as c from emp1;select (員工的姓名是：|ename) as d from emp1;3約束及主外鍵create table student( sid number not null,-非空約束 name varchar2(20);select * from student;insert into student (name) values (nihao);commit;insert into student values (1,喬峰);insert into student values (1,短語);-unique唯一性約束不能插入重復(fù)的值，但是可以插入多個空值null-列級的 unique 只能對單列進行約束-表級的unique 可以對單列或者多列組合進行約束drop table student;create table student(-單列約束 sid number unique , name varchar2(20) , type varchar2(20);delete from student;select * from student;insert into student (sid,name,type) values (null,很好,風(fēng)流笨蛋);insert into student (sid,name,type) values (1,不好,風(fēng)流笨蛋);insert into student (sid,name,type) values (2,較好,風(fēng)流笨蛋);insert into student (sid,name,type) values (3,差勁,風(fēng)流笨蛋);insert into student (sid,name,type) values (3,不是很好,風(fēng)流笨蛋);-違反約束了commit;-create table student1( sid number, name varchar2(20), type varchar2(20), constraint student1_sid_un unique(sid,name)-組合約束，即表級別的約束);-primary key 主鍵約束功能相當(dāng)于 (非空且唯一),在字段級別和表級別都可以定義drop table student;create table student( sid number primary key, name varchar2(20), type varchar2(20);insert into student values (2,西門吹雪,冷酷型);commit;select * from student;create table student1 ( sid number , name varchar2(20), type varchar2(20), constraint student1_sid_pk primary key(sid,name)-聯(lián)合主鍵,每個字段都不能為空，只能在表級別定義);insert into student1 values (1,你好,笨蛋);insert into student1 values (1,陸小鳳2,也是笨蛋);commit;select * from student1;-外鍵約束foreign key -用于確保相關(guān)的兩個字段之間的參照關(guān)系-實現(xiàn)參照完整性約束-注：如果字表字段中有主表主鍵相關(guān)聯(lián)的記錄，主表中的相關(guān)聯(lián)記錄不能被刪除-字段末尾 constraint foreign key (相關(guān)的字段名)-references （主表字段名）drop table student ;create table student ( sid number , name varchar2(20), type varchar2(20), constraint student_sid_pk primary key(sid);create table information( sid number , address varchar2(50), tel varchar2(11), home varchar2(20), constraint student_sid_fk foreign key(sid) references student(sid);select * from student;select * from information;insert into student values(1,張三,學(xué)習(xí)型);insert into information values (1,常州,88888,sdfsdf);commit;delete from student where sid=1;-其中包含主鍵，所以刪除的時候要注意是否含有外鍵約束等信息delete from information where sid=1;-按照順序應(yīng)該先刪除包含外鍵約束的那張表，然后再刪除主表中的響應(yīng)的記錄才行commit;-檢查約束check-定義指定字段都必須滿足的條件-注意：只能在字段級別定義drop table student ;drop table information;create table student ( sid number primary key, name varchar2(20) check (length(name)3 and length(name)=0 and age=120);insert into student values(1,tomy,16);insert into student values(2,tomy2,-1);-查看 check 檢查性約束commit;select * from student;- 查看約束select * from user_constraints ;select * from user_cons_columns;-修改約束-建表后添加的約束-alter table add constraint 約束類型 ()-特例:not null 約束必須要用 modify 來修改drop table student1;create table student( sid number , name varchar2(20);alter table student add constraint sutdent_sid_pk primary key(sid);alter table student modify (constraint student_name name not null);-實際此處的名字student_name并沒有真實效果-刪除約束-alter table drop constraint ；-主鍵:也可用此刪除 alter table drop primary key;-刪除not null 約束（要先查找相應(yīng)的約束然后再刪除這是比較特殊的）alter table student drop constraint SYS_C009671;-student_name 對應(yīng)not null的約束名字-刪除級聯(lián)約束 加上cascade-alter table drop primary key cascade; -禁用約束-alter table disable constraint cascade-啟用的時候要注意:必須一個一個的啟用約束 .( 即不能在加上cascade)alter table student disable constraint SUTDENT_SID_PK;alter table student enable constraint SUTDENT_SID_PK;alter table student disable constraint SYS_C009674 cascade; -級聯(lián)禁用可以alter table student enable constraint SYS_C009674; -級聯(lián)啟用有問題則不行，必須一個一個的啟用才行-視圖-create or replace view 定義的列名 as create or replace view v_emp (員工編號,員工姓名,工資) as select empno,ename,sal from emp where sal2000;grant dba to scott;select * from v_emp;-刪除視圖drop view v_emp;-強制創(chuàng)建視圖 force（如果表不存在）-create or replace force view 定義的列名-as create or replace force view v_emp(員工編號,姓名,工資)as select empno,ename,sal from emp2;select * from v_emp; -本身此表emp2 并不存在-復(fù)雜視圖-1.函數(shù)的使用create or replace view v_emp1 (部門編號,部門最高工資,部門最低工資,部門平均工資)asselect deptno,max(sal),min(sal),avg(sal) from emp group by deptno;select * from v_emp1;-2.多表的使用create or replace view v_emp2(人員編號,人員姓名,部門編號,部門名稱,人員年薪)asselect empno,ename,deptno,dname,sal*13 from emp right join dept using (deptno);select * from v_emp2;-select count(*) from emp1;select * from emp1;truncate table emp1;drop table emp1;create table emp1 as select * from emp;alter table emp1 modify(empno number(6);-索引-創(chuàng)建索引-create index on (列名.列名)create index i_emp1 on emp1 (empno);select * from user_indexes;-查看索引select * from user_ind_columns;-查看索引是建立在哪一列上的-刪除索引drop index i_emp1;-索引查找數(shù)據(jù) ( 一般在where 條件后的字段 上設(shè)置索引 來提高查詢效率 數(shù)據(jù)量大的時候才能看出來區(qū)別)-基于函數(shù) ( 表達(dá)式 )的索引-create index on (function)create index i1_emp1 on emp1 (lower(ename);select * from user_indexes;select * from emp1 where lower(ename) =jerry;-序列（獨立的對象，與表沒有關(guān)系）-創(chuàng)建序列-create sequence -increment by n 設(shè)置序列步長-start with n 序列初始值-maxvalue/nomaxvalue 有無序列最大值-minvalue/nominvalue 有無序列最小值-cycle/nocycle 是否循環(huán)-cache/nocache 是否設(shè)置緩存-order/noorder 是否排序create sequence seq_emp1 increment by 1start with 1maxvalue 200minvalue 1nocycle;-修改序列-alter sequence -increment by n 設(shè)置序列步長-start with n 序列初始值 -初始值可以修改但是必須要與最小值對應(yīng)要注意理解-maxvalue/nomaxvalue 有無序列最大值 -maxvalue 可以更改，無限制-minvalue/nominvalue 有無序列最小值 -最小值不能大于等于初始值 ( 即必須小于初始值)，前提是修改的情況下-cycle/nocycle 是否循環(huán)-cache/nocache 是否設(shè)置緩存-order/noorder 是否排序-刪除序列-drop sequence drop sequence seq_emp1;-查看序列 user_sequencesselect * from user_sequences;-表如何引用，使用該索引呢？-使用序列-獲取當(dāng)前序列值select seq_emp1.nextval from dual;select seq_emp1.currval from dual;-select count(*) from emp1;select * from emp1;create table emp2 as select * from emp1;select * from emp2;truncate table emp1;alter table emp1 add constraint emp_pk primary key(empno);alter table emp1 modify (empno number(6);insert into emp1 (empno,ename,sal,deptno)values (seq_emp1.nextval,jerry,3000,30);commit;-同義詞-synonym 縮短對象名的長度-create synonym for create synonym e1 for emp1;select * from e1;-刪除同義詞 drop synonym drop synonym e1;-很重要必須掌握 導(dǎo)入/ 導(dǎo)出-導(dǎo)出（導(dǎo)出表 導(dǎo)出方案（對象） d:-cd bin目錄所在的地址- 導(dǎo)出命令語句-A 導(dǎo)出表-導(dǎo)出命令：exp userid=scott/gaoorcl tables=(emp1) file=d:e.dmp;create table emp1 as select * from emp;select * from emp1;-2. 導(dǎo)出其他用戶（方案）的表 ( 注意是誰導(dǎo)出的，還得是那個人進行導(dǎo)入)-注：導(dǎo)出其他用戶的表需要 dba 權(quán)限或 exp_full_database 的權(quán)限-修改system用戶的密碼可以設(shè)置sys 超級管理員登陸用這個語句修改用戶的密碼alter user system identified by manager;-exp userid=system/managerorcl tables=(scott.emp1) file=d:e1.dmp-注意導(dǎo)出的是誰的表，要在表前面加上用戶名-3.導(dǎo)出表結(jié)構(gòu)，不導(dǎo)出表中的數(shù)據(jù)exp userid=scott/gaoorcl tables=(emp) file=d:e2.dmp rows=n-沒有數(shù)據(jù)只有表的結(jié)構(gòu)，導(dǎo)出后會很小-4. 直接導(dǎo)出方式（當(dāng)數(shù)據(jù)量比較大的情況下）-注：速度比常規(guī)方式快，使用此法服務(wù)器上的字符集必須與客戶端字符集一致exp userid=scott/gaoorcl tables=(emp) file=d:e3.dmp direct=y-B 導(dǎo)出方案(某用戶擁有的對象)-1. 導(dǎo)出自己的方案（包括該用戶下的表，同義詞，索引，序列等信息）exp scott/gaoorcl owner=(scott) file=d:scott.dmp;-2. 導(dǎo)出其他用戶方案exp system/managerorcl owner=(system,scott) file=d:sys.dmp-C 導(dǎo)出數(shù)據(jù)庫 -導(dǎo)出自己的數(shù)據(jù)庫 ( 內(nèi)容好多哦 )-一般不會導(dǎo)出整個數(shù)據(jù)庫中的內(nèi)容的exp userid=system/managerorcl full=y file=d:sys1.dmpexp userid=scott/gaoorcl full=y file=d:scott1.dmp-此處會報錯，必須是dba 才能執(zhí)行完整數(shù)據(jù)庫或表空間的導(dǎo)出操作-導(dǎo)入 （導(dǎo)入表 導(dǎo)入方案/對象 導(dǎo)入數(shù)據(jù)庫）-imp 導(dǎo)入命令-userid 執(zhí)行導(dǎo)入操作的用戶名，口令，連接字符串-tables 執(zhí)行導(dǎo)入操作的表-fromuser 指定源用戶-touser 指定目標(biāo)用戶 -file 指定導(dǎo)入文件名-full=y 指定執(zhí)行導(dǎo)入整個數(shù)據(jù)庫-inctype 指定執(zhí)行導(dǎo)入操作的增量類型-rows 指定是否導(dǎo)入表中的數(shù)據(jù)-ignore 如果表存在，只導(dǎo)入表中數(shù)據(jù)-A 導(dǎo)入表-1. 導(dǎo)入自己的表select count(*) from emp1;truncate table emp1;drop table emp1;imp userid=scott/gaoorcl tables=(emp1) file=d:e.dmp-2.導(dǎo)入表到其他用戶drop table scott.emp1;select * from scott.emp1;