Oracle10g數據庫
1． 數據類型：字符類型：char(標準通用拉丁字符),nchar(漢字等其他字符),varchar2(長度可變字符),nvarchar2,long;
數字類型：number(通用),integer,float
日期和時間：date,timestamps(分秒，時區)
行：rowid(邏輯地址),urowid（邏輯地址，內存地址）;
二進制:raw(size)(原始二進制數據),long raw,blob(二進制大型對象;最大4G字節),clob(字符大型對象),nclob,bfile;
2.oracle WEB管理頁面：localhost:5560/isqlplus; localhost:5500/em
3.net設置遠程測試：tnsping datebasename；遠程連接:sqlplus name/password@datebasename;
4.創建表空間：create tablespace test
datafile 'test.dbf' size 10m autoextend on next 2m
maxsize unlimited
logging
permanent
extent management local autoallocate
blocksize 8k
segment space management manuaL;//段空間
5．創建用戶并連接： create user "TEST" identified by "TEST"
default tablespace TEST
temporary tablespace TEMP
quota unlimited on TEST
quota unlimited on TEMP
grant "connect" to test//分配基本權限。
conn test/test;
6．重設用戶密碼：scott/tiger為默認用戶，alter user scott identified by tiger;
解鎖：alter user scott account unlock;
7.sql腳本的執行：@路徑/filename.sql;
8.創建表：create table t1(c1 type 約束,c2 type 約束（not null,unique,check,primary key））
9.查詢：select distinct c1 from t1 where 條件 group by c1 having by 子條件order by c1;
10．連接字符串：select c1 (as可省) 列1 ||c2 from t1;
11.單行函數：select lower(c1) from t1；upper全大寫，initcap第一個字母大寫，length；
12．Select Sysdate from dual(系統默認的空表)顯示系統時間,months_between(date,date);
13.round(數據,5位數),to_date(1997-10-11,’yyyy-mm-dd’)，to_char()函數使用要轉換。
14.nvl(c1,0)把字段為空的值換為0，nvl2(c1,1,0)不空的為1，空的值為0；
15.操作符：比較：=，<>,>=,<=,>,<；邏輯：and,or,not
其他：in/not in,between..and..,is null/is not null,like，exists/not exists；
Eg:select count(distinct c1) as 種類 from t1 where c1 like ‘%l%’(模糊查詢如m_n)(c1 between 10 and 100) group by c1 order by c1 desc，c2 desc（不寫就默認asc）
16．聚合函數：count(*)返回所有行的記錄數,min(c1),max(c1),sum(c1),avg(c1);
Eg:select c1,count(*) from t1 group by c1 having by count(*)>1;(having不能用變量申明);等價于select c1,count(*) as cn from t1 group by c1 where cn>1;
17．聲明主鍵和check：一.create table t1(c1 primary key)
二.創建表的時候指定限制的名稱:create table t1(c1 constraint pk_emp primary key);
三：create table t1(emp_no number not null , constraint pk_emp primary key(emp_no))； 為已經存在的表增加主鍵:alter table t1 add constraint pk_emp2 primary key (c1)；
創建check: create table t1(budget number(7),constraint CHK_PROJ check(budget>10000 and budget<1000000))
18.創建外鍵: create table t1(dept_no varchar2(4) not null, constraint fk_emp foreign key (dept_no) references t2(dept_no), 對已經存在表創建外鍵:alter table t1
add constraint foreign_work_emp foreign key(c1) references t2(c1)；
刪除一個外鍵alter table t1 drop constraint foreign_work_emp；
19.刪除表：drop table t1; 查看回收站:show recyclebin; 清空回收站 purge recyclebin;
從回收站中恢復數據表:flashback table t1 to before drop;
20.更新數據操作：插入記錄：insert into t1(c1,c2)values(‘’,’’)；
插入一字段:insert into t1(c1,c2) select c3,c4 from t2;
更新記錄：update t1 set c1=’’ where ;
刪除記錄：delete from t1 where;truncate table t1;drop table t1;
21.合并查詢：intersect(select * intersect select *),union(去掉空),union all(包括空),minus（減集）;
22.多表查詢:select * from t1,t2;(笛卡爾集c1行*c2行)；select * from t1 join t2 using(num);等價于select * from t1 inner join t2 on(t1.no=t2.no);join邏輯連接，只連接有聯系的字段，full join物理機械連接，out join,left out join(右邊變成空),right out join;
23．控制語句：select emp_no, case project_no
when 'p1' then case when enter_date<to_date('1997-10-1','yyyy-mm-dd')then '三室'
when enter_date>to_date('1998-10-1','yyyy-mm-dd')then '等兩年'//時間非字段時間型
else '兩室一廳'end
when 'p2' then case when enter_date<to_date('1997-10-1','yyyy-mm-dd')
then '三室兩廳' when enter_date>to_date('1998-10-1','yyyy-mm-dd')
then '看情況再分' else ' 一室一廳'end
end as 分房情況from works_on；
if then end if,loop end loop,declare type *,while( )loop end loop,case when then end
24.嵌套查詢select c1 from t1 where c2 in(select c2 from t2 where c3=(select c3 from t3))
25．Pl/sql語言：模塊語法和流程控制：（頭控制符沒有標點；尾控制符都有；）
declare icount number;
begin
icount :=0;
for mycount in reverse 0..10//mycount 為自定義變量
---while(icount<100)
Loop icount :=icount+10;
---exit when(icount>100);
if(mycount>5) then
dbms_output.put_line('result='||icount);//流程輸出語句
end if;
end loop;
end; //(set serverout on才能輸出)
26．序列Sequence. create sequence se_1 increment by 1 start with 100
maxvalue 999999//minvalue n --表示序列可以生成的最小值(降序). cycle;//到頭自動循環
查看：select se_1.nextval from dual; select se_1.currval from dual;（必先next初始化）
使用：create table stu1(stu_no number(10) not null,stu_name varchar2(20) not null);
insert into stu1(stu_no,stu_name) values(se_1.nextval,'joi');
修改序列:alter sequence <sequencen_name>//start with 語句不能用了，否則重復定義
刪除序號：drop sequence <sequence_name>；
自動序列rownum:select * from t1 where rownum<5;
select * from(select rownum as a,e.* from t1 e)where a=3//單行必先關聯，e為表的別名
27．創建視圖create or replace view v1 as
select c1 from t1;
使用視圖：select * from v1;
28．創建函數：create or replace function get_maxe_empno return varchar2 is
tmpvar varchar2(20);//局部變量聲明
begin //可執行語句
select max(emp_no) into tmpvar from employee;//把取出的值賦給一個變量
return tmpvar;//函數的重要特征就是通過return語句來傳遞參數
end get_maxe_empno;
使用：select get_maxe_empno() from dual;
數字字典select distinct(name) from user_source where type='FUNCTION'
從數字字典里查找信息：select text from all_source where name='GET_MAXE_EMPNO';
刪除函數：drop function get_maxe_empno;
29.過程：create or replace procedure sp_test
(fname in varchar2, update_count out int)//參數列表是過程的特征 is
cursor emp_cursor//定義游標
is
select emp_no from employee where employee.emp_fname=fname;//輸入in
begin // 可執行語句
update_count:=0;
for emp_rec in emp_cursor
loop
update employee set emp_fname=emp_lname,emp_lname=fname
where employee.emp_no=emp_rec.emp_no;update_count:=update_count+1;
end loop;commit;//控制語句，強制執行
end；//過程可以有in/out變量
查看過程：select distinct(name) from user_source where type='PROCEDURE'
select text from all_source where name='SP_TEST' order by line;
使用過程：declare fname varchar2(200);
update_count number;
begin
fname:='John';update_count:=0;
sp_test(fname,update_count);commit;dbms_output.put_line(update_count);
end;
30.觸發器：create or replace trigger tgr_emp_change
after insert or update or delete on employee
referencing new as n old as o// :new為引用最新列值; :old為引用以前列值;
for each row
begin
if inserting then
insert into emp_update_info(emp_no,oper_type) values(:n.emp_no,1);
end if;
if updating then // updating為觸發器的操作符；
insert into emp_update_info(emp_no,oper_type) values(:o.emp_no,3);
end if;
end;
建表：create table emp_update_info(emp_no varchar2(20) not null,oper_type integer);
查看trigger：select distinct( name ) from user_source where type='TRIGGER';
對employee的操作就自動出發的emp_update_info
31．聲明包：create or replace package pkg_test as
function get_max_empno return varchar2;//子程序說明，公共對象聲明
procedure sp_test(fname in varchar2,update_count out int);
end pkg_test;
創建包體:create or replace package body pkg_test as // is也行
function get_max_empno return varchar2 as //公共類型與對象聲明
tmpvar varchar2(20);//公共類型和對象聲明，
begin // 語句體中是初始化語句，特殊用途
select max(emp_no) into tmpvar from employee; return tmpvar;
end get_max_empno;
//子程序主體部分
procedure sp_test(fname in varchar2,update_count out int) is
cursor emp_cursor is
select emp_no from employee where employee.emp_fname = fname;
begin
update_count := 0;
for emp_rec in emp_cursor
loop
update employee set emp_fname = emp_lname, emp_lname = fname
where employee.emp_no = emp_rec.emp_no;
update_count := update_count + 1;
end loop;
commit;//強制執行
end;
end pkg_test;
查看定義的package：select distinct( name ) from user_source where type='PACKAGE'
使用package中的對象：select pkg_test.get_max_empno from dual;
32．數據庫設計：業務規則和流程—實體和關系—ER圖（一對一，一對多，出現多對多進行拆分）—SQL語句—視圖和索引—儲存過程和觸發器—維護。