Oracle學習第三天

Oracle學習第三天

Oracle第三天

一、回顧

? 內連接查詢：

? 外連接： 特殊外連接（+）

? 子查詢：返回一個值， 返回一列多行(in , =any ,=some ) , 返回多行多列（作為表使用） , 返回null

? exists運算符：和in比較效率較高

? 分頁： 偽列：rownum

? --帶有排序的分頁：先排序， 作為表，再生成行號，座位表，條件篩選

? 課堂練習： 行列轉換（條件表達式（case when ，decode））

? 集合運算符：交集（intersect），並集（union all，union），差集（minus）

? 遞歸查詢：

二、學習目標

1. 管理表的DDL語句
2. 創建表&約束
3. DML語句
4. DDL語句(視圖，索引，序列，同義詞)
5. 資料庫的備份

三，管理表的DDL語句

1. 表空間

```

Oracle體系結構

資料庫：只有一個資料庫

實例：後台運行一個進程，一個資料庫可以對應多個實例

表空間：邏輯存儲單位 書

數據文件：物理存儲單位，體現dbf文件 紙

用戶：面向用戶管理，用戶管理表空間，向表空間添加數據，最終存儲到數據文件中

/

--1.表空間

表空間創建語法

create tablespace 表空間名稱

datafile 文件路徑

size 200M -- 指定文件的大小

autoextend on -- 開啟自動擴展 ， off

next 20M; -- 每次擴展20M;

/

(此處注意須在system用戶下執行,或者授予scott dba的許可權)

create tablespace scottspace

datafile c:scottspace.bdf

size 200m

autoextend on

next 20m

--表空間的刪除語法

--drop tablespace 表空間名(不能刪除數據文件)

drop tablespace scottspace

--drop tablespace 表空間名 including contents and datafiles;(能刪除數據文件)

drop tablespace scottspace including contents and datafiles

```

1. 用戶

```

/

語法:

create user 用戶名 identified by 密碼

default tablespace 表空間名稱;

/

create user lucy identified by tiger

default tablespace scottspace

(由於Oracle資料庫對於許可權管理非常嚴格,所以新新建的用戶沒有任何許可權,這時就需要我們為其授予許可權後才可以使用)

--- 許可權管理

-- connect : 基本許可權：create session

-- resource :開發人員許可權，

-- dba ：管理員許可權

-- 賦予許可權的語法

-- grant 許可權列表 to 用戶;

-- 回收許可權

-- revoke 許可權列表 from 用戶;

grant connect to lucy; grant resource to lucy; --查詢當前用戶所有的許可權 select * from session_privs; revoke resource from lucy;

```

四、創建表&約束

-- 數據類型: -- 字元串類型 -- char（10）:固定長度字元串 ,最大長度：4000 -- varchar（10）：可變長度字元串 -- varchar2(10):可變長度字元串,由oracle公司定義，承諾向後兼容 -- long : 存儲量是 2G ，用clob取代 -- 數值類型 number: 整數 number(m,n)：浮點數類型， m是總的位數,n 是小數點後的位數 -- 日期類型 -- date :相當於mysql中的datetime -- timestamp : 時間戳類型，精確到秒後的9位 -- 大數據類型 -- blob: 存儲量是4G，位元組大數據類型, -- clob：存儲量是4G, 字元大數據類型, -- 創建表的語法 /* create table 表名( 列名 類型 約束, 列名 類型 約束, 列名 類型 約束, .... ) */ create table student( sid number primary key, sname varchar2(100), age number ); -- 修改表的操作 --添加列 --alter table 表名 add 列明 類型 約束 alter table student add sex varchar2(22); --刪除列 alter table 表名 drop colunm 列明; alter table student drop column sex; --修改列屬性 alter table 表名 modify 列名 類型 alter table student modify sname varchar2(200); --重命名列 alter table 表明 rename column 原列名 to 新列名 alter table student rename column age to gender --- 約束:保證數據的完整性 -- 主鍵：唯一，非空 --非空: -- 唯一 -- 默認 -- 檢查 drop table student; create table t_class( cid number, primary key(cid), cname varchar2(200) not null, adress varchar2(200) unique, age number default 19 check(age between 1 and 150), sex varchar2(50) check(sex in(男,女)) ); drop table t_class; -- 外鍵 -- 班級表和學生表 create table student( sid number, primary key(sid), sname varchar2(200) not null, cid number, --設置外鍵 --constraint 約束名稱 foreign key(外鍵列) references 主表(主鍵) on delete cascade constraint fk_class_student foreign key(cid) references t_class(cid) on delete cascade ); --級聯刪除(不推薦) delete from student where sid=1; delete from t_class where cid=1; select * from t_class select * from student; --物理外鍵:在建表時手動創建 --邏輯外鍵:由Java代碼所控制 --強制刪除主表 drop table t_class cascade constraint;

五、DML語句

--SQL語句的分類 -- DDL： 資料庫定義語言：create drop , alter -- DML: 資料庫操作語言： insert update ,delete -- DCL: 資料庫控制語言：grant ，revoke -- DQL: 資料庫查詢語言： select -- insert into 表名 values(); -- insert into 表名(列名,...) values(值,...) -- update 表名 set 列= 值, 列=值 where 條件 -- delete from 表 where 條件 -- truncate table 表名; -- 刪除所有的記錄（效率極高），摧毀表結構，重新建表-- 事務的特性：原子性，一致性，隔離性，持久性 -- 資料庫的隔離級別 -- read uncommited:讀未提交 -- read comminted :讀已提交 -- repeatable read ：重複讀 -- serializable : 串列化（序列化） -- mysql ：支持四個隔離級別,默認隔離級別：repeatable read -- oracle：支持三個隔離級別（read commited ,serializable ,read only） -- 默認的是：read commited -- 事務的保存點(了解) insert into student values(1,a,1); savepoint s1; insert into student values(2,b,1); savepoint s2; insert into student values(3,c,1); savepoint s3; rollback to s2; commit;

六、DDL語句

1. 視圖

```

--- 視圖: 是一張虛表, 不能存儲記錄，所有的記錄都在基本表中 ,可以對視圖進行增刪改查

-- 語句：

-- create view 視圖名 as DQL;

grant dba to scott;(此處由於Scott許可權不夠,所以需要先授予其dba許可權)

-- 視圖可以直接把它作為表查詢，修改，刪除，添加

create view emp_view as select * from emp;

select * from emp_view;

insert into emp_view(empno ,ename) values(1, rose); select * from emp; -- 作用一：可以屏蔽敏感列 create or replace view employee as select empno,ename ,job ,mgr,deptno from emp; select * from employee; -- 作用二：簡化操作 create or replace view employee as select t.* ,rownum rn from (select * from emp order by sal desc) t select * from employee where rn between 6 and 9; -- 作用三：可以定義只讀的視圖 create or replace view emp_view as select * from emp with read only; insert into emp_view(empno ,ename) values(2, mike);

```

1. 序列

```

-- 序列： sequence , 數列， 從1開始，依次遞增，沒上上限

/

創建序列的語法

create sequence 序列名;

/

create sequence emp_seq;

-- 屬性： nextval ，currval(必須先執行依次nextval，才能使用)

select emp_seq.nextval from dual;

select emp_seq.currval from dual;

insert into emp(empno ,ename) values(emp_seq.nextval ,lili); select * from emp;-- 完整的語法（了解） /* create sequence 序列名 start with 1 起始值 increment by 2 自增量 maxvalue 9999 最大值 ,nomaxvalue minvalue 1 最小值 ，nominvalue cycle 開啟循環 cache 20 ; 緩存 */

```

1. 索引

```

-- 索引：提高檢索的效率

-- 前提：百萬條記錄以上 ， 不經常修改的表

-- 語法： create index 索引名稱 on 表名(列,列,...);

-- 添加百萬條記錄

create table a(

id number primary key,

aname varchar2(100)

);

create sequence a_seq;

select sys_guid() from dual; declare begin for i in 1..1000000 loop insert into a values(a_seq.nextval, sys_guid()); end loop; end; -- 單列索引 -- 添加索引前查詢某一條記錄： 0.453 select * from a where aname = 10C4668168AE4AECB61E4F8F6B668022; -- 添加索引 create index a_index on a(aname); -- 添加索引後查詢某一條記錄：0.062 select * from a where aname = DCC4B29D2E034BC0A7DEA68604CAAC22; -- 複合索引 觸發條件 (name ,address) -- select * from 表 where name = and address = 可以觸發索引 -- select * from 表 where name = or address = 不可以觸發索引 -- select * from 表 where name = 可以觸發索引 -- select * from 表 where address = 不可以觸發索引

```

1. 同義詞

```

-- 同義詞: 作用一： 跨用戶訪問 ，作用二：縮短表名

select * from scott.emp;

-- 創建同義詞

--create synonym 同義詞名稱 for 用戶.表;

create synonym emp for scott.emp;

select * from emp; -- 縮短表名 create synonym e for scott.emp; select * from e;

```

七、資料庫的備份

-- 資料庫的備份： grant dba to itheima; -- 導出: exp scott/tiger file=c:/scott.ora [tables=(dept)] -- 導入: imp itheima/itheima file=c:/scott.ora -- fromuser=scott touser=itheima [tables=(dept)]

推薦閱讀：