Oracle學習第三天
Oracle第三天
一、回顧
? 內連接查詢:
? 外連接: 特殊外連接(+)
? 子查詢:返回一個值, 返回一列多行(in , =any ,=some ) , 返回多行多列(作為表使用) , 返回null
? exists運算符:和in比較效率較高
? 分頁: 偽列:rownum
? --帶有排序的分頁:先排序, 作為表,再生成行號,座位表,條件篩選
? 課堂練習: 行列轉換(條件表達式(case when ,decode))
? 集合運算符:交集(intersect),並集(union all,union),差集(minus)
? 遞歸查詢:
二、學習目標
- 管理表的DDL語句
- 創建表&約束
- DML語句
- DDL語句(視圖,索引,序列,同義詞)
- 資料庫的備份
三,管理表的DDL語句
- 表空間
```
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
```
- 用戶
```
/ 語法: 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語句
- 視圖
```
--- 視圖: 是一張虛表, 不能存儲記錄,所有的記錄都在基本表中 ,可以對視圖進行增刪改查 -- 語句: -- 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);
```
- 序列
```
-- 序列: 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 ; 緩存 */
```
- 索引
```
-- 索引:提高檢索的效率 -- 前提:百萬條記錄以上 , 不經常修改的表 -- 語法: 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 = 不可以觸發索引
```
- 同義詞
```
-- 同義詞: 作用一: 跨用戶訪問 ,作用二:縮短表名 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)]
推薦閱讀:
※業務系統的數據資產管理為什麼這麼難?
※RedisConf2018記錄--Day 1 sessions by 夏周、白宸
※Confluence 6 當前使用的資料庫狀態
※Mysql優化
※Gartner: 2017全球資料庫安全市場趨勢