Oracle建表插資料等等

標籤：

Oracle的表的管理：
表名和列的命名規則
. 必須以字母開頭
. 長度不能超過30個字元
. 不能使用Oracle的保留字
. 只能使用如下字元 column_name-Z，column_name-z，0-9，$，#等

Oracle支援的資料類型

字元類：
char 定長，最大2000個位元組。
例子：char(10) 如果內容為‘小傑‘則前四個位元組放‘小傑‘，後添6個空格補全
varchar2() 變長 最大4000個字元。
例子：varchar2(10) ‘小傑‘，Oracle分配四個字元。這樣可以節省空間的。
clob(character large object) 字元型大對象，最大4G
char 查詢的速度極快浪費空間，查詢比較多的資料用，varchar2 節省空間的
long 儲存最大長度為2GB的變長字元資料
raw 儲存非結構化資料的變長字元資料，最長尾2KB
long raw 儲存非結構化資料的變長字元資料，最長尾2GB
rowid 儲存表中列的物理地址的位元據，佔用固定的10個位元組
urowid 儲存表示任何類型列地址的位元據
bfile 把非結構化的位元據作為檔案儲存體在資料庫之外
數字型：
number(n,m) 其中預設m表示整數，範圍：-10的38次方 到 10的38次方
可以表示整數，也可以表示小數，number(5,2) 表示一位小數有5位有效數，2位小數 範圍：-999.99到999.99，number(5) 表示一個5位整數 範圍99999到-99999
float 儲存浮點數
日期類型：
date 包含年月日和時分秒，Oracle預設格式：1-1月-1999 timestamp 這是oracle9i對date資料類型的擴充。可以精確到毫秒。
圖片：
blob 位元據可以存放圖片/聲音，4G，一般來講，在真實項目中是不會把圖片和聲音真的往資料庫裡存放，一般存放圖片、視頻的路徑，如果安全需要比較高的話，則放入資料庫。
建表：
create table table_name(
column1_name type [constraint constraint_def] [primary key] [default default_def],
column2_name type [constraint constraint_def] [references table2_name(column2_name)] [default default_def],
......
);
create table tb_Employee(
pk_Employee_ID number(4) primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(4)
);

create table tb_Department(
pk_Department_ID number(4) primary key,
dname varchar2(14),
loc varchar2(13)
);

create table tb_SalGra(
pk_SalGra_ID number primary key,
losal number,
hisal number
);

insert into tb_Department
values (10,‘ACCOUNTING‘,‘NEW YORK‘);
insert into tb_Department
values (20,‘RESEARCH‘,‘DALLAS‘);
insert into tb_Department
values (30,‘SALES‘,‘CHICAGO‘);
insert into tb_Department
values (40,‘OPERATIONS‘,‘BOSTON‘);

insert into tb_Employee
values(7369,‘SMITH‘,‘CLERK‘,7902,TO_DATE(‘17-12-1980‘,‘dd-mm-yyyy‘),800,null,20);
insert into tb_Employee
values(7499,‘ALLEN‘,‘SALESMAN‘,7698,TO_DATE(‘20-2-1981‘,‘dd-mm-yyyy‘),1600,300,30);
insert into tb_Employee
values(7521,‘WARD‘,‘SALESMAN‘,7698,TO_DATE(‘22-2-1981‘,‘dd-mm-yyyy‘),1250,500,30);
insert into tb_Employee
values(7566,‘JONES‘,‘MANAGER‘,7839,TO_DATE(‘2-4-1981‘,‘dd-mm-yyyy‘),2975,NULL,20);
insert into tb_Employee
values(7654,‘MARTIN‘,‘SALESMAN‘,7698,TO_DATE(‘28-9-1981‘,‘dd-mm-yyyy‘),1250,1400,30);
insert into tb_Employee
values(7698,‘BLAKE‘,‘MANAGER‘,7839,TO_DATE(‘1-5-1981‘,‘dd-mm-yyyy‘),2850,NULL,30);
insert into tb_Employee
values(7782,‘CLARK‘,‘MANAGER‘,7839,TO_DATE(‘9-6-1981‘,‘dd-mm-yyyy‘),2450,NULL,10);
insert into tb_Employee
values(7839,‘KING‘,‘PRESIDENT‘,NULL,TO_DATE(‘17-11-1981‘,‘dd-mm-yyyy‘),5000,NULL,10);
insert into tb_Employee
values(7788,‘SCOTT‘,‘ANALYST‘,7566,‘19-4月-1987‘,3000.00,NULL,20);
insert into tb_Employee
values(7844,‘TURNER‘,‘SALESMAN‘,7698,TO_DATE(‘8-9-1981‘,‘dd-mm-yyyy‘),1500,0,30);
insert into tb_Employee
values(7900,‘JAMES‘,‘CLERK‘,7698,TO_DATE(‘3-12-1981‘,‘dd-mm-yyyy‘),950,NULL,30);
insert into tb_Employee
values(7902,‘FORD‘,‘ANALYST‘,7566,TO_DATE(‘3-12-1981‘,‘dd-mm-yyyy‘),3000,NULL,20);
insert into tb_Employee
values(7934,‘MILLER‘,‘CLERK‘,7782,TO_DATE(‘23-1-1982‘,‘dd-mm-yyyy‘),1300,NULL,10);

insert into tb_SalGra
values (1,700,1200);
insert into tb_SalGra
values (2,1201,1400);
insert into tb_SalGra
values (3,1401,2000);
insert into tb_SalGra
values (4,2001,3000);
insert into tb_SalGra
values (5,3001,9999);

select * from tb_Department;
select * from tb_Employee;
select * from tb_SalGra;

drop table table_name;--刪除表
rename table_name to table_newname;--重新命名表名或者使用
alter table table_name rename to table_newname
或者create table table_newname as select * from table_name;drop table table_name;
alter table table_name rename column column_name to column_newname;--修改表中的欄位名
alter table table_name add column_name newtype;--增加欄位
alter table table_name modify column_name newtype;--修改欄位的類型
alter table table_name drop column column_name;--刪除欄位
alter session set nls_date_format=‘yyyy-mm-dd‘;--日期設定成中國人習慣的方式
alter session set time_zone;--改變會話時區

約束用於確保資料庫資料滿足特定的商業規則。

在Oracle中，約束包括：not null、unique，primary key，foreign key和check五種,增加約束
alter table table_name modify column_name not null;--非空
alter table table_name add constraint constraint_def unique(column_name);
alter table table_name add constraint constraint_def primary key(column_name);
alter table table_name add constraint constraint_def foreign key(column_name) references r2(column_name);
alter table table_name add constraint constraint_def check(條件);
alter table table_name drop constraint column_name constraint_def;--刪除約束
alter table table_name modify column_name null;--為空白
alter table table_name drop constraint primary key cascade;
禁止約束:
約束 disable;--建立約束時
alter table table_name disable constraint constraint_def;
允許約束：
alter table table_name enable constraint constraint_def;
獲得約束資訊：
select * from user_constraints;

DML（資料操作語言）：

select * from tb_Employee;--查詢
insert into table_name values(); --插入資料（表中內容）
delete from table_name where ...;--刪除元組（表中內容），寫日誌，可恢複
truncate table table_name;--刪除元組（表中內容），速度快，不寫日誌，不可恢複

update table_name set column_name where ...;--更新資料（表中內容）
update table_name set (a1,a2,a3)=(select (A1,A2,A3) from table_name where...) where...;

分頁：
rownum
經典例句：
select * from (select column_name.*,rownum rn from (select * from tb_Employee) column_name where rownum<=10) where rn>=6;
根據結果集建立表,複製一部分：
create table tb_Mytable(pk_ID,name) as select pk_Employee_ID,ename from tb_Employee;

進行行遷移：
insert into r2(A1,A2,A3) select a1,a2,a3 from r1 where ...;

常用sql關鍵詞：

distinct;
(=、<、<=、>、>=、<>);--算術運算比較符
in;--包含測試
natural join;--相當於=、join..on、inner join..on
natural left outer join;--相當於left outer join..on =>右邊欄位(+)
natural right outer join;--相當於right outer join..on =>左邊欄位(+)
natural full outer join;--相當於natural left outer join union all natural right outer join
cross join;--交叉串連，也稱笛卡爾串連
natural
any;
all;
問題：如何顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號？
select ename,sal,deptno from tb_Employee where sal>all(select sal from tb_Employee where deptno=30);
或者select ename,sal,deptno from tb_Employee where sal>(select max(sal) from tb_Employee where deptno=30);--可以用min()或者是max()函數替代
like;--匹配測試
%：表示0到多個字元；_：表示任意單個字元；首字元為S，like ‘S%‘； 第三個字元為大寫S，like ‘__S%‘； 含有S，like ‘%S%‘；以"ab%cd"開頭,like ‘ab\%cd%‘ escape ‘\‘；
desc tb_Employee;--查看錶的結構（describe（描述）的意思）
order by;
group by;--group by用於對查詢的結果分組統計
having;--having子句用於限制分組顯示結果
||;--字串串連操作符
between and;--範圍測試，在某個範圍之內
is null;--空值測試
(and、or、not);--複合條件查詢

Oracle建表插資料等等