基本概念
DDL 數據定義語言 - 建立數據庫對象

create /alter/ drop/ truncate ……table table_name.

DML 數據操縱語言 - 數據的查看和維護

select / insert /delete /update

TCL 事務控制語言 - 數據是否保存到數據庫中

commit / rollback / savepoint

DCL 數據控制語言 -- 查看對象的權限

grant / revoke

數據庫設計

關系數據模型的組成

一張或多張表

表的索引

視圖

觸發器

表與表的關系

數據庫Schema
概念上的 ： 一組DDL
物理上的 ： 一個命名空間，包含了表，過程，視圖的集合
常用命令
connect 用戶名/密碼 連接到指定用戶
desc tab_name 察看表tab_name的結構
quit/exit 退出
clear screen 清除屏幕
set linesize 200 設置一行顯示200個字符
set pagesize 20 設置每頁顯示20行
dbms_output.put_line() 打印,類似于System.out.println();

set serveroutput on 打開服務器輸出，否則上面打印語句報錯

edit 編輯緩存中的語句
/ 執行上一次語句塊/可重復執行
@ sqlpath 執行某sql文件
@@ sqlfile 執行sqlpath環境變量中的文件
spool file 打印日志到文本文件
spool off 停止打印
# 在輸入sql語句的過程中臨時先運行一個sql*plus命令
--查看大字短的大小
SELECT DBMS_LOB.GETLENGTH(MYCLOB) FROM ATTACHMENT; //MYCLOB是列名,ATTACHMENT 是表名
SELECT DBMS_LOB.GETLENGTH(MYBLOB) FROM ATTACHMENT;
常用表
user_constraints 用戶約束
user_tables 用戶表
all_all_tables 所有用戶表
all_constraints 所有約束
user_cons_columns 約束列
all_cons_columns 所有約束列
nls_session_parameters 當前會話信息
v$nls_parameters 系統參數
數據類型
字符型
char 最大2000個字節 定長
varchar2最大4000個字節 變長
數字類型
number 10的-38次方 到10的38次方 可以表示小數 也可以表示整數
日期
date 包含年月日和時分秒 7個字節
大對象
clob 字符型大對象 >4000字節 最大4G
blob 二進制大對象 圖像/聲音 4G
偽類型
%type 可以引用某列的類型
%rowtype 可以引用某表，作為類型
rownum 結果集每行的行號
表操作基礎
創建表
create table tab_name
(
column_name data_type ,
column_name data_type ,
column_name data_type ,
……..
)
修改表
插入列
alter table tab_name add(column_name data_type)
修改列
alter table tab_name modify(column_name data_type)
刪除列
alter table tab_name drop column column_name;
表重命名
rename tab_name to change_name
刪除表
drop table tab_name刪除記錄和結構,不可恢復

添加數據
insert into tab_name[(column_name , column_name…)] values(value,value…)
序列 sequence <僅oracle中有>
創建序列
create sequence 自定義序列名字;
獲得下一個值
seq_name.nextval
獲得當前值
seq_name.currval
復雜序列 序列的nextval是不可逆的
create sequence 自定義序列名字
increment by 5 <--遞增5-->
start with 0 <--從0開始-->
maxvalue 100 <--最大值 也可以使用nomaxvalue-->
minvalue -100 <--最小值 也可以使用nominvalue-->
cycle <--循環增長 也可以使用nocycle-->
cache 30 <--緩存 也可以使用nocache-->

修改數據
update tab_name set expression
刪除數據
delete tab_name 刪除記錄，不刪除結構，可以恢復 ?
delete tab_name where expression 條件刪除 ?
truncate table tab_name 僅清除數據，保留結構，不可恢復
約束
主鍵約束 用來唯一表示一條數據的字段，其值不能重復，不能為null
create table test
(
nationality varchar2(20),
city varchar(20),
constraint nick_pk primary key(nationality,city)
);
外鍵約束 引用其他表的主鍵到本表,在本表中叫外鍵，用來做表關系
create table test
(
nationality varchar2(20),
city varchar(20),
constraint nick_fk foreign key(nationality) references
tab_name(nationality)
);
<** create table test1
(
id varchar2(20),
name varchar(20),
tid varchar(20),
primary key(id),
constraint test1 foreign key(tid) references
test2(tid)
);
"constraint test1:外鍵關聯名"
create table test2
(
tid varchar2(20),
descp varchar(20),
primary key(tid)
); **>
非空約束
create table test
(
nationality varchar2(20) not null,
);
唯一約束
create table test
(
nationality varchar2(20) ,
constraint nick_uk unique(nationality)
);
檢查約束
create table test1
(
nationality varchar2(20) ,
constraint nick_ck check(nationality <> '美國')
);
默認值
default
設定約束條件無效
disable|enable constraint_name
級聯刪除<級聯更新要使用觸發器>
references … ON DELETE CASCADE
運算符
< 小于
<= 小于等于
> 大于
>= 大于等于
= 等于
!= 不等于
<> 不等于
:= 賦值
is null 如果操作數為null返回true
like 比較字符串 _代表一個字符 %代表多個字符

create table test
(
a number
)
insert into test value(10);
insert into test value(20);
insert into test value(30);
between 驗證值是否在范圍之內
select * from test where a between 10 and 20;
in 驗證操作書在設定的一系列值中
select * from test where a in(1,10,20,30);
all 表示子查詢返回值中的所有值,相當于比較最大值
select sal from emp where sal > all( select sal from emp where sal<2000);
any 表示子查詢返回值中的任意值，相當于最小值

and 兩個條件都滿足
or 只滿足一個
not 取反
+ 加
- 減
* 乘
/ 除
基礎查詢
<--基本的select語句-->
select 列名,列名,.. from 表名,表名,.. where 條件 group by 列名 having 條件
select 是必須的 ，后面添你要查詢的列名 ，* 代表所有
from 是必須的 ， 后面填寫你要查詢的表名，可以有多個
where 可選的，后面填寫你的查詢條件
group by 可選的，對聚合進行分組，當查詢內容多于一列且包含聚合函數時使用
having 僅用于group by 的關鍵字，和where作用一樣

查詢最大值:
select * from product where price not in (select p.price from product p, product d
where p.price<d.price)

<--消除相同的行-->
select distinct 列名,列名,..from 表名

<--排序 order by-->
select 列名,列名,.. from 表名 order by 要排序的列名

<--注意：order by指令需聚合函數配合使用，否則只能是單列-->

<--聚合函數-->
avg 平均值
select avg(emp.sal) from emp;
sum 求和
select sum(emp.sal) from emp;
max 最大值
select max(emp.sal) from emp;
min 最小值
select min(emp.sal) from emp;
count 總數
select count(emp.sal) from emp;

<--排序-->
升序
select * from emp order by emp.sal ;
降序
select * from emp order by emp.sal desc;

<--數據復制-->
select * into myemp from emp;

<--表復制-->
create table myemp as select * from emp;
create view bb as select ………
<--小技巧-->
啞元表 在沒有查詢表的情況下使用
select 1+1 from dual
查詢分割 ||
select a.a1 ||'----'|| a.a2 from a;
查詢行數限制
select * from a where rownum<=2;
查詢表結構
desc tab_name
數據復制
select tab_name into tab_name|var from tab_name

--檢索訂單總額最大的用戶
select * from users where id =
(
select userid from
(
select userid, sum(price*quantity) as total
from orders group by userid order by total desc
)
where rownum=1
);

mysql:技巧,可將文件的內容插入到表中.
load data local infile 'd:/sql.txt' into table product;
高級查詢
笛卡爾乘積
select dname,ename from emp,dept; 這種通過多張表簡單對加是沒有太大意義的

集合運算
交集 用來得到兩個或者多個不同集合的共同元素,兩個集合的交集就是其中所有屬性相等的元素.交集有一個嚴格的限制：每個結果集中所有列都必須匹配相等

減集 用來查找在一個集合中出現過，而在另一個集合中沒有出現的元素,與交集相反的是: 每個結果集中所有列都必須匹配不相等

并集 用來合并兩個或者多個類似的集合

交集 intersect
select語句 intersect select語句
減集 minus
select語句 minus select語句
并集 union or union all
select語句 union select語句

內連接
在連接條件中使用等于號(=)運算符比較被連接列的列值，其查詢結果中列出被連接表中的所有列，包括其中的重復列。
select dname,ename from dept,emp where dept.deptno = emp.deptno


外連接
條件列使用(+)，則此列為外連列，主列信息全部顯示，外連列沒有則顯示空
多行子查詢
返回多個行，必須包含一個多行運算符。
視圖
視圖語法
創建視圖: create [or replace] view <名字> as <select 語句>
視圖用于簡化查詢，視圖中實際存放的是一個查詢語句而已，返回的是結果集
在視圖中可以修改數據，但是: 建立視圖的查詢語句必須是一個簡單的select(只查詢一個表,并且不含有分組函數)
查看視圖: select * from 視圖名;

程序塊
在這之前，我們所有的sql語句都是一句一句執行的，如果我們把很多事情看作一個整體提交執行的話，必須使用程序塊。

聲明部分：聲名變量及初始化
關鍵字：declare

執行部分：存放所有可執行的代碼，這些代碼包含在begin/end中
關鍵字：begin end

每個指令結束用;表示，--表示注釋


--這是一個示例
declare
i number:=5; y number:=6;
begin
i:=i+y;
dbms_output.put_line(i);
end;
/
流程控制
--條件if......then...elseif.. .end if;
declare
i number:=50;
begin
if i=50
then dbms_output.put_line(i);
elsif i<50
then dbms_output.put_line(0);
end if;
end;
/
-----使用loop循環(exit when 退出條件)
declare
i number;
begin
i:=0;
loop
exit when i=10;
dbms_output.put_line(i);
i:=i+1;
end loop;
end;

-----使用while-loop循環
declare
i number;
begin
i:=0;
while i<10
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
/

-----使用for-loop循環

begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end;
/

-----使用for-loop反序循環

begin
for i in REVERSE 1..10
loop
dbms_output.put_line(i);
end loop;
end;
事務處理
隔離級別 臟讀 不可重復讀 虛讀
讀未提交
Read uncommitted 可以 可以 可以
讀已提交
Read committed 不可以 可以 可以
可重復讀
Repeatable read 不可以 不可以 可以
可串行化
Serializable 不可以 不可以 不可以

臟讀是指當一個事務正在訪問數據，并且對數據進行了修改，而這種修改還沒有提交到數據庫中，這時，另外一個事務也訪問這個數據，然后使用了這個數據。
例如：你銀行有1000塊，你取出500，但沒最終提交，你老婆這時通過ATM查帳，你放棄了取款，這時帳戶應該還有1000，但是你老婆看到的是500，于是你老婆提交了，結果你損失了500

Ps: 用線程方式理解------讀數據不加鎖

不可重復讀是指當事務兩次讀取同一行數據，但每次得到的數據都不一樣時，就會發生這種事件。
例如：你用網絡察看你銀行帳戶，你老婆這時用ATM察看，這時你取出了500，你老婆準備取1000塊發現不夠了…..

Ps: 用線程方式理解------讀之前加鎖，讀完放鎖

臟讀和不可重復讀的區別是，一個未提交讀取，一個以提交讀取

虛讀是指一個事物查詢兩次，另一個事物在這兩次之間插入了數據，導致兩次查詢的結果不同
Ps: 用線程方式理解------讀之前加鎖，讀完不放鎖，直到commit或rollback才放鎖

串行化-----完全安全的數據訪問模式
Ps: 用線程方式理解------讀之前加條件鎖，讀完不放鎖，直到commit或rollback才放鎖

commit 提交上一個事物，開始下一個事物
savepoint var 保存點
rollback 回滾到事務開始處，或某保存處
存儲過程
語法格式
create or replace procedure 過程名(參數 參數設置 參數類型) is
聲明語句段;
begin
執行語句段;
exception
異常處理語句段;
end;
參數設置
in參數：讀入參數，主程序向過程傳遞參數值。
out參數：讀出參數，過程向主程序傳遞參數值。
in out 參數：雙向參數，過程與主程序雙向交流數據。
調用存儲過程方法
無返回參數 execute 過程名(參數);
有返回參數的要在另一個過程中調用并賦值

例子:
--賦值語句< := >
create or replace procedure pro4(w in integer,h in integer)
as
width integer :=w;
hight integer :=h;
area real;
begin
area :=(width+hight)*2;
dbms_output.put_line('area=' || area);
end;
/

--條件語句<if .. then ... end if>
create or replace procedure myro5(num in integer) as
n integer :=num;
begin
if n=5 then
dbms_output.put_line('ok');
else
dbms_output.put_line('not equals');
end if;
end;
/

--循環<while ** loop *** end loop; for * in ***loop end loop;>
create or replace procedure mypro6(n in integer) as
con integer :=0;
begin
dbms_output.put_line('while loop.=========');
while con<n loop
dbms_output.put_line(con);
con :=con+1;
end loop;

dbms_output.put_line('for loop ==========');
con :=0;
for con in 1..n loop
for con in n..1 loop
dbms_output.put_line('*');
end loop;
end loop;
end;
/


--使SQLPLUS控制臺可以輸出
set serveroutput on
set serveroutput off

--控制臺輸出
declare
begin
dbms_output.put_line('this is ');
end;
/


declare
aaa varchar2(100);
begin
dbms_output.put_line((2+3)*6);
end;
/
異常處理
自定義異常處理

1. 定義異常處理
定義異常處理的語法如下：
declare
異常名 exception;
begin
2. 觸發異常處理
觸發異常處理的語法如下：
raise 異常名;
3. 處理異常
觸發異常處理后，可以定義異常處理部分，語法如下：
Exception
When 異常名1 then
異常處理語句段1;
When 異常名2 then
異常處理語句段2;
end;
小技巧
raise_application_error(錯誤代碼,錯誤原因) 函數可以直接拋異常
其中錯誤代碼為-20000到-20999之間，錯誤原因為2000個以內的字符
觸發器
觸發器相當于java中的事件監聽，當某事件發生時激活特定的事件并執行相應的邏輯
DML觸發器中包含了三種事件 insert update delete
語法格式
create [or replace] trigger 觸發器名
{before| after | instead of} {insert|delete|update}
on 表名
[for each row]
when 條件
begin
end;
before 在事件開始前執行begin/end
一般應用場合:
1 判斷觸發事件(一般是一個DML 語句)是否應該被執行
2 在觸發事件之前計算一個列的值

after 在事件開始后執行begin/end

一般應用場合:
1 完成觸發事件

for each row 表示每操作一次都觸發，稱作行級，不寫表示無論操作多少行，只觸發一次，稱作表級

when(條件) 必須是行級
小技巧：
觸發器中可以使用三個條件詞 Inserting,deleting,updating
觸發器中可以使用兩個變量 :old | :new 分別表示舊有的值和新值，必須是行級(在過程中加:)

instead of 替代觸發器: 只能作用在視圖上，用于替代DML語句 ，行級，不可與when同用

兩個任務:
第一題
創建一張表 create table test(aa number primary key);
執行10次 insert into test values(任意整數) ; 語句
查詢此表，結果為 1 2 3 4 5 6 7 8 9 10

第二題
創建一觸發器保證每周的周六周日，以及每天早八點前和晚六點后不允許對emp表進行任何的DML（insert,delete,update）操作。
第三題
實現emp表中的外鍵deptid的級聯更新功能
游標
游標是從數據表中提取出來的數據，以臨時表的形式存放在內存中，在游標中有一個數據指針，在初始狀態下指向的是首記錄，利用fetch語句可以移動該指針，從而對游標中的數據進行各種操作，然后將操作結果寫回數據表中。
首先我們先看看emp表,select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
我想查找sal<1000得數據,
select * from scott.emp where sal <1000
返回若干行，返回的若干行事實上是一個臨時表。游標的作用是對這個臨時表進行逐行處理
游標通常用于在存儲過程及客戶端開發中
游標流程

聲明游標；CURSOR cursor_name IS select_statement

為查詢打開游標；OPEN cursor_name

取得結果放入PL/SQL變量中；
FETCH cursor_name INTO list_of_variables;
FETCH cursor_name INTO PL/SQL_record;
關閉游標；CLOSE cursor_name
游標的屬性
mycursor%isopen --是否打開
mycursor%found --fetch有數據
mycursor%notfound –fetch沒有數據
mycursor%rowcount --返回游標的行數,若返回值為0，沒有提取出數據。

--最基本的游標
declare
cursor mycursor is select * from scott.emp where sal > 1000 ;
temp mycursor%rowtype ;
begin
open mycursor ;
fetch mycursor into temp ;
dbms_output.put_line(temp.sal);
close mycursor;
end;
/

--游標的滾動
declare
num number;
i emp%rowtype;
--定義游標
cursor mycursor is
select * from emp;
begin
select count(*) into num from emp;
if num>0 then
open mycursor; --打開游標
loop
fetch mycursor into i; --讀取游標中的值到變量中
exit when mycursor%notfound; --沒有取到記錄時，就退出循環
dbms_output.put_line(i.sal);
end loop;
close mycursor; --關閉游標
else
dbms_output.put_line('emp=0');
end if;
end;
日期函數
函數名: to_date(string)
含義: 字符串轉日期
示例: select to_date('2002-1-1'，'yyyy-MM-dd') from dual;
結果: 2002-1-1

函數名: add_months(date,month)
含義: 增加或減小月份
示例: select add_months(sysdate,1) from dual ;
結果: xxxx-xx-xx

函數名:sysdate
含義:當前日期，可直接加減天數
示例:select sysdate+1 from dual
結果:當前日期+1天

函數名: extract(year|month|day from dateType)
含義:截取日期
示例: select extract(year from sysdate)"This year" from dual;
結果: 2007
問題：獲取幾個月以后的年份

函數名: last_day(dateType)
含義: 最后一天
示例: select last_day(sysdate) from dual ;
結果: xxxx-xx-xx

函數名: months_between(dateType,dateType)
含義: 返回兩個日期間的月份
示例: select months_between('2002-1-1','2003-1-1') from dual
結果: -12

函數名: to_char(dateType,yyyy|dd|mm|hh{12|24}|mi|ss|day|ww|dy)
含義: 把日期轉換為字符串
示例: select to_char(sysdate,'yyyy-mm-dd-hh24:mi:ss') from dual;
結果: xxxxxxxx
問題: 兩個日期間共有幾星期

字符串函數
函數名: ASCII(char)
含義: 返回與指定的字符對應的十進制數
示例: select ascii('孟') from dual;
結果: 50127

函數名: CHR(int)
含義: 給出整數,返回對應的字符
示例: select chr(50127) from dual;
結果: 孟

函數名: CONCAT(string,string)
含義: 連接兩個字符串
示例: select concat('我是','孟慶晨') from dual;
結果: 我是孟慶晨

函數名: INITCAP(string)
含義: 返回字符串并將字符串的第一個字母變為大寫
示例: select INITCAP('aaa') from dual;
結果: Aaa

函數名: INSTR(被搜索字符串,搜索的字符串,搜索的開始位置默認為1,第幾次出現默認為1)
含義: 在一個字符串中搜索指定的字符,返回發現指定的字符的位置
示例: select INSTR('我是孟慶晨','孟',1,1) from dual;
結果: 3

函數名: LENGTH(string)
含義: 返回字符串的長度
示例: select length('孟慶晨') from dual ;
結果: 3

函數名: LOWER(string)
含義: 小寫形式
示例: select lower('AAA') from dual
結果: aaa

函數名: UPPER
含義: 大寫形式
示例: select upper('aaa') from dual ;
結果: AAA

函數名: RPAD & LPAD
含義: 黏貼字符串到目標字符串左右
示例: select lpad('a',10,'*') from dual ;
結果: *********a

函數名: ltrim & rtrim
含義: 刪除左邊（右邊）的字符串
示例: select ltrim('abc','a') from dual ;
結果: bc

函數名: substr
含義: 取字符串(原字符串,第幾個開始,取幾個)
示例: select substr ('abcdefg',3,4) from dual
結果: cdef

函數名: REPLACE(string,string,string)
含義: 替換字符串
示例: select replace('abcde' , 'abc' , 'aaa' ) from dual ;
結果: aaade

數學函數
函數名: abs
含義: 返回指定值的絕對值
示例: select abs(-10) from dual
結果: 10

自定義函數:

--函數function
create or replace function prices
(
proprice in number
)
return integer
is
pricecount integer;
begin
select count(price) into pricecount from product where price>proprice;
if(pricecount >0) then
return pricecount;
else
return 0;
end if;
end prices;

declare
counter number := 0;
begin
counter := prices(30);
if counter >0 then
dbms_output.put_line('counts:'||counter );
else
dbms_output.put_line('counts:'||counter );
end if;
end;
/
常見問題
1 不能啟動監聽
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleOraHome92TNSListener
添加字符串項ImagePath,值為c:\oracle\ora90\bin\TNSLSNR
2 啟動時,監聽器不啟動或打開出錯
錯誤現象：
　　Oracle啟動時，監聽器不啟動或打開出錯；服務器端：用username/password登錄正常，但用username/password@alias登錄不成功；客戶端：用username/password@alias登錄不成功
　　解決方法
　　（1）如果是因為修改了NT的機器名，則把listener.ora文件中的host參數全部改為新的NT機器名，重新啟動OracleTNSListener80服務即可。
　　
　　例如:
　　
　　LISTENER =
　　(ADDRESS_LIST =
　　(ADDRESS = (PROTOCOL = TCP)(Host = NT_Name)(Port = 1521))
　　(ADDRESS=(PROTOCOL=TCP)(Host=NT_Name)(Port= 1526))
　　)

3 運行oracle后tomcat運行出錯
原因：端口沖突
解決方法
修改tomcat的conf.xml文件，查找8080，修改為其他

4 本地日期問題
找到注冊表MACHINE/SOFTWARE/ORACLE/HOME0/
添加字符串 NLS_DATE_FORMAT 值為yyyy-mm-dd

     摘要:    1. oncontextmenu="window.event.returnValue=false" 將徹底屏蔽鼠標右鍵  <table border oncontextmenu=return(false)><td>no</table> 可用于Table  2....  閱讀全文