Oracle學習第二天

標籤：

oracle資料庫的常見資料類型
oracle全部資料類型 有26種

char
定長字串類型長度是固定不變的例如：no char(10) 如果存入的值不足十個字元，其它位也被佔用
預設長度是1 最大長度是2000個位元組GBK編碼 1000個漢子
佔用的空間較多，但是查詢效率較高

varchar2
邊長字串類型長度可變例如：定義的長度是100個位元組，存入的值是80個位元組，那麼實際佔用的就是80個位元組
varchar在oracle中不推薦使用，強烈推薦你使用varchar2
必須要為其指定長度 1-4000個位元組多以就需要考慮編碼問題
oracle使用GBK編碼 一個中文佔兩個位元組，英文、數字佔一個位元組
佔用的空間較少，但是查詢的效率較低

number
數字類型number(all_len,point_len)
第一個參數：整個數位位元
第二個參數：以小數點為參照，右邊為正數，左邊為負數，0就是沒有小數位
123.89number123.89
123.89number(3)124
123.89number(6,2)123.89
123.89number(6,1)123.9
123.89number(4,2)exceeds precusion(有效位元為5,5>4) 超出範圍
123.89number(6,-2)100
.01234number(4,5).01234(有效位為4)
.00012number(4,5).00012
.000127number(4,5).00013
.0000012number(2,7).0000012
.00000123number(2,7).0000012

date
將資料存入資料庫中，需要進行格式化儲存的函數(方法)
預設格式：DD-MM-YY
select sysdate from dual;
查詢結果：30-9月 -15
to_date(‘存入的值‘,‘存入的格式‘);
2015-12-12 12:12:12YYYY-MM-DD HH24:MI:SS
to_date(‘2015-12-12 12:12:12‘,‘YYYY-MM-DD HH24:MI:SS‘);

create table test_date(name char(3),test_time date default sysdate);

to_char();
select to_char(test_time,‘YYYY-MM-DD HH24:MI:SS‘) tochar_time from test_date where name=‘123‘;

timestamp

clob 用來儲存單位元組字元型資料，適用於儲存超長文本例如：小說、部落格

blob 用來儲存位元據 儲存視頻、映像、音頻

-------------------------------------------------------------------------------------------------------

約束
建立表時使用約束
是強加在表上的跪著條件，確保資料庫滿足商務規則。可以保證資料的完整性
當對錶進行DML或DDL操作時，如果此操作會造成表中的資料違反約束條件或規則時，資料庫系統就會拒絕執行這個操作

定義約束時如果沒有給定一個明確的約束名稱，系統就會自動為該約束產生一個名字
oracle強烈建議建立約束時要給約束起名稱

約束的分類：

非空約束(NOT NULL)
限制欄位(列)不能為NULL值

唯一約束(UNIQUE)
在表中每一行中定義的這列或這些列的值都不能相同 即唯一性

主鍵約束(PRIMARY KEY)
唯一的標識表中的每一條資料(每一行記錄) 不能為NULL 不能重複

外鍵約束(FOREIGN KEY)
用來維護從表和主表之間引用的完整性（不推薦使用外鍵約束）

條件、檢查約束(CHECK)
表中該列每一行都要滿足該約束條件

命名規則：
約束名稱簡稱_表明_列名
非空約束NN_tablename_col1
唯一約束UK_tablename_col1
主鍵約束PK_tablename_col1
外鍵約束FK_tablename_col1
條件、檢查約束CK_tablename_col1

create table c_test(
id number(6),
name varchar2(30),
gender char,
age number(3),
birthday date,
constraint PK_C_TEST_ID primary key(id)
);
查詢某使用者下某表的約束名稱（在dba角色下查詢）
select constraint_name from dba_constraints where owner=‘SCOOT‘ and table_name=‘EMP‘;

查詢目前使用者下的資料表條件約束
select constraint_name,constraint_type,search_condition,r_constraint_name
from user_constraints where table_name=upper(‘emp‘);

刪除外鍵約束
alter table tablename
drop constraint FK_tablename_col1;
--------------------------------------------------------------------------------------
表結構操作：
修改表名：
rename tt to ttt;
添加表欄位：
alter table ttt add gender char default ‘M‘;
表欄位重新命名：
alter table ttt rename column name to username;
修改表欄位：
alter table ttt modify (name varchar2(50) default ‘Owen‘);

 

 

oracle函數
運算子：
算數運算子+ -* /
select 1+1 a from dual;
串連運算子||
select ‘jack‘||‘love‘||‘rose‘ from dual;
比較子> = >= < <= != like between is null in
select * from tt where username like ‘j%‘;
任何值與null進行運算，都是null

ascii(‘a‘);根據字元返回對應的編碼值
chr(編碼值);返回對應的字元
concat(欄位名1，欄位名2)串連兩個字串
initcap(欄位名)返回字串並將字串的第一個字母變大寫
length(欄位名)返回欄位值的長度
lower(欄位名)返回字串的全部小寫
upper(欄位名)返回欄位值全部大寫
select lower(initcap(username)) low,upper(username) up from tt;

ltrim(欄位名)清除欄位值左邊的Null 字元串

rtrim(欄位名)清除欄位值右邊的Null 字元串

substr(原字串，從第幾個字元開始，截取多少個)
select substr(‘abcdefghijklmn‘,1,3) from dual;

replace(原字串，被替換的字串，要替換的字串)
select replace(‘He love rose!‘,‘He‘,‘I‘) from dual;

trim(‘要去掉的字元‘ from ‘目標字串‘)
select trim(‘s‘ from ‘strings‘) from dual;減掉兩邊的所有需要去掉的字元
select trim(leading ‘s‘ from ‘strings‘) from dual;減去前面的所有需要去掉的字元
select trim(trailing ‘s‘ from ‘strings‘) from dual;減去後面的所有需要去掉的字元

abs(欄位名)返回數位絕對值
select abs(123) abs1,abs(-1234) abs2 from dual;

floor(欄位名)將小數位去掉，返回整數（不四捨五入）
select floor(1234.567) from dual;

ceil(欄位名) 將小數轉成整數（有小數就+1）

mod(被除數，除數)
select mod(10,3) m1,mod(3,3) m2,mod(1,3) m3 from dual;

power(原數字，冪)
select power(2,3) from dual;

round(欄位名)對小數四捨五入
select round(55.5) from dual;

trunc(欄位名)直接截取整數部分（不四捨五入）
select trunc(55.5) from dual;

to_number()

show autocommit;顯示自動認可的狀態
set autocommit on/off;開啟/關閉自動認可
commit;手動提交

rollback;復原（autocommit off），撤銷之前一部的操作（未提交狀態）

----------------------------------------------------------------------------------------
日期常用函數
last_day(date)返回日期date所在月的最後一天
select last_day(sysdate) from dual;

add_months(date,i)返回日期date加上i個月後的日期值
i：取正值整數小數：截取整數後再計算負數：減去i個月的日期值
select add_months(sysdate,1) from dual;

months_between(date,date_)返回兩個日期間隔多少月
結果可能是負數或小數（可以用ceil函數直接進位）

next_day(date,周幾)返回date日期資料的下一個周幾的日期，例如4即下一個周四的日期
Sunday、Monday、Tuesday、Wednesday、Thursday、Friday、Saturday
select next_day(sysdate,1) from dual;

least();取最小值
least(1,100,2,31);
greatest();取最大值
greatest(1,100,2,31);

extract(year/month/day from date)從參數date中提取參數指定的資料類型
select extract(year from sysdate) from dual;

null和任何值進行運算返回的都是null
select * tablename where column1=null;//對於null不能這樣進行比較
select * tablename where column1 is null;
select * tablename where column1 is not null;
空值函數
nvl(col_null,col_not_null)將null值轉變成非null值處理，請保持兩個參數的類型一致
例如：select sal,comm,sal+nvl(comm,0) as salary from emp;

nvl2(col_null,col_is_null,col_is_not_null)將null值轉變成實際值，作用有點像三目運算式
如果是null，轉成col_is_null,如果不是null，轉成col_is_not_null
例如：select sal,comm,sal+nvl2(comm,0,1000) as salary from emp;

 

Oracle學習第二天