一、99語法--表連接证芭，rowid與rownum

（一）99語法--表連接

1行瑞、交叉連接cross join --->笛卡爾積

select * from emp cross join dept;

2、自然連接(主外鍵、同名列) natural join -->等值連接

select * from emp natural join dept;

--在指定列過程中同名列歸共同所有(*除外)

select deptno,e.ename,d.dname from emp e natural join dept d;

3涮拗、連接(同名列) -->等值連接

--jion using(等值連接字段名) 當(dāng)存在多個同名字段,可以指明使用哪一個做等值連接

select ename,sal,deptno from emp join dept using(deptno);

4、join on 連接 -->等值連接 非等值 自連接 (解決一切) 關(guān)系列必須區(qū)分

-- 數(shù)據(jù)來源1 join 數(shù)據(jù)來源2 on 連接條件 ; 即可以實現(xiàn)等值連接 可以實現(xiàn)非等值連接

select * from emp e join dept d on e.deptno = d.deptno;

--非等值連接

--查詢員工信息以及每一個員工的薪資等級

select * from emp e join salgrade s on e.sal between s.losal and s.hisal;

-- 查詢非20部門并且薪資>1500的員工信息以及薪資等級信息

select *

? from emp e

? join salgrade s

? ? on e.sal between s.losal and s.hisal

where e.deptno != 20

? and sal > 1500

order by sal desc;

5、join on|using -->外連接

--想要某張表中不滿足連接條件的數(shù)據(jù)都顯示,把這張表定義為主表

--左外? left join

--右外? right join

select * from emp e1 right join emp e2 on e1.mgr = e2.empno;

6多搀、full join on|using -->全連接 滿足直接匹配歧蕉，不滿足 相互補充null ，確保 所有表的記錄 都至少出現(xiàn)一次

select * from emp e1 full join emp e2 on e1.mgr = e2.empno;? -- 兩張表都作為主表

（二）rowid 與 rownum

????????ROWID 是 ORACLE 中的一個重要的概念康铭。用于定位數(shù)據(jù)庫中一條記錄的一個 相對唯一地址值惯退。通常情況下，該值在該行數(shù)據(jù)插入到數(shù)據(jù)庫表時即被確定且唯一从藤。 ROWID 它是一個偽列催跪，它并不實際存在于表中。它是 ORACLE 在讀取表中數(shù)據(jù)行時夷野， 根據(jù)每一行數(shù)據(jù)的物理地址信息編碼而成的一個偽列懊蒸。所以根據(jù)一行數(shù)據(jù)的 ROWID 能 找到一行數(shù)據(jù)的物理地址信息。從而快速地定位到數(shù)據(jù)行悯搔。數(shù)據(jù)庫的大多數(shù)操作都是 通過 ROWID 來完成的骑丸，而且使用 ROWID 來進行單記錄定位速度是最快的。我們可以將其用于刪除重復(fù)數(shù)據(jù)妒貌。

????????ROWNUM 是一種偽列通危，它會根據(jù)返回記錄生成一個序列化的數(shù)字。排序后的 結(jié)果集的順序號 灌曙，每一個結(jié)果集 都有自己順序號 菊碟，不能直接查詢大于 1 的數(shù)。利用 ROWNUM在刺，我們可以生產(chǎn)一些原先難以實現(xiàn)的結(jié)果輸出逆害。 例如實現(xiàn)? 分頁? 操作。?????ps: oracle 中 索引從 1 開始蚣驼，java 程序 從 0 開始魄幕。

1、rowid

實現(xiàn)重復(fù)記錄的刪除

要求：刪除重復(fù)記錄颖杏，一條記錄只保留一次

思路->將所有記錄按照某種特定規(guī)律分組（相同的記錄為一組）纯陨，保留下每組中的一

條記錄即可，其他記錄刪除

1）找出重復(fù)數(shù)據(jù) :哪個學(xué)生 哪門課重復(fù)了

select name,course,count(1) from tb_student group by name,course;

select name,course,count(1) from tb_student group by name,course having count(1)>1;

2）刪除重復(fù)數(shù)據(jù) :刪除重復(fù)記錄

-- 每條記錄的唯一標(biāo)識

select s.* , rowid from tb_student s;

--找出 保留的rowid

select min(rowid) from tb_student group by name,course;

--刪除

delete from tb_student where rowid not in (select min(rowid) from tb_student group by name,course);

2输玷、rownum

--規(guī)律:? 把已確定的結(jié)果集中的數(shù)據(jù)從第一個開始 設(shè)置rownum,從1開始,依次+1

--優(yōu)點:? 有規(guī)律,規(guī)律可循,是數(shù)字,可以進行判斷和分頁操作

?rownum :1）必須排序 ? ? ? ?? 2）不能直接取大于 1 的數(shù)

舉個栗子：

--最底層 rownum 數(shù)據(jù)庫默認(rèn)順序號 -->沒有用的

select emp.*, rownum from emp蘸秘；

select emp.*, rownum from emp order by sal ;

--自己 排序后結(jié)果集的順序號

select e.*, rownum from (select * from emp order by sal desc) e;

--取出工資前5名

select e.*, rownum

?from (select * from emp order by sal desc) e

where rownum <= 5;

--實現(xiàn)分頁查詢

select empno,ename,rownum from emp where rownum <=5;

--如果在使用rownum值判斷之前就已經(jīng)確定了一個結(jié)果集,這個結(jié)果集中的rownum就是已經(jīng)確定的

--再嵌套一個select

select empno,ename,rownum n from emp;? --數(shù)據(jù)來源? 確定rownum

select *

? from (select empno, ename, rownum n from emp)

where n >= 5

? and n <= 10;? --where中rownum要使用別名,確定內(nèi)部select語句的字段,如果直接寫rownum,被認(rèn)為是外部select語句的

--如果存在排序,rownum的序號可能出現(xiàn)問題(亂號),如果:根據(jù)主鍵進行order by,先排序再rownum,如果根據(jù)其他字段排序,一般會先rownum,再order by

select deptno,dname,rownum from dept order by deptno;

select empno,ename,deptno,rownum from emp order by deptno;

--如果rownum亂掉怎么辦?

--解決方案: 外層嵌套一個select

select empno,ename,deptno,rownum num from emp order by deptno;

--以確定的有規(guī)律的rownum,如果判斷,根據(jù)這個rownum判斷,把當(dāng)前select當(dāng)做數(shù)據(jù)源使用

select empno, ename, deptno, rownum 外層的rownum, num 內(nèi)層rownum

? from (select empno, ename, deptno, rownum num from emp order by deptno);

--分頁

select * from (select empno, ename, deptno, rownum n, num 內(nèi)層rownum

? from (select empno, ename, deptno, rownum num from emp order by deptno))

? where n>5;

二贸辈、視圖與索引

（一）視圖

????????視圖:建立在表|結(jié)果集|視圖上的虛擬表瓢娜，有以下作用

1赘理、簡化:select 查詢語句

2浮驳、重用:封裝select語句 命名

3赘来、隱藏:內(nèi)部細節(jié)

4皮假、區(qū)分:相同數(shù)據(jù)不同查詢

????????不是所有的用戶都有創(chuàng)建視圖的權(quán)限

1转锈、前提： create view -->組 connect resource dba

2、授權(quán): -->sqlplus /nolog

a)尤误、sys登錄 conn sys/123456@orcl as sysdba

b)侠畔、授權(quán): grant dba to scott;

? ? ?? 回收: revoke dba from scott;

c)、重新登錄

????????create or replace view 視圖名 as select語句 [with read only];

????要求:所有列必須存在名稱损晤。

????對視圖的刪除不會刪除原有表的數(shù)據(jù)

????drop view 視圖名;

以下為操作過程：

-- 視圖：

-- 建立在表和結(jié)果集之間的就是視圖软棺，其實也是結(jié)果集，但是這個結(jié)果集可以被存儲尤勋，以后可以查詢視圖中的數(shù)據(jù)

-- 最大的優(yōu)點：就是封裝喘落，簡化sql

-- 合理使用視圖，不要過于使用

select empno,ename,sal,deptno from emp where deptno in (20,30);

-- create or replace view 視圖名 as 結(jié)果集 with read only;

create or replace view vw_emp as select empno,ename,sal,deptno from emp where deptno in (20,30) with read only;

select * from vw_emp;

-- 如果權(quán)限不夠

-- 切換管理員sys用戶

-- 進行授權(quán)：grant dba to scott;

-- 回收：revoke dba from scott;

grant dba to scott;

（二）索引

????????索引是數(shù)據(jù)庫對象之一最冰，用于加快數(shù)據(jù)的檢索瘦棋，類似于書籍的索引。在數(shù)據(jù)庫中索引可以減少數(shù)據(jù)庫程序查詢結(jié)果時需要讀取的數(shù)據(jù)量暖哨，類似于在書籍中我們利用索引可以不用翻閱整本書即可找到想要的信息赌朋。

????????索引是建立在表上的可選對象；索引的關(guān)鍵在于通過一組排序后的索引鍵來取代默認(rèn)的全表掃描檢索方式篇裁，從而提高檢索效率沛慢。

????????索引在邏輯上和物理上都與相關(guān)的表和數(shù)據(jù)無關(guān)，當(dāng)創(chuàng)建或者刪除一個索引時茴恰，不會影響基本的表颠焦；

????????索引一旦建立，在表上進行DML 操作時（例如在執(zhí)行插入往枣、修改或者刪除相關(guān)操作時）伐庭，oracle 會自動管理索引，索引刪除分冈，不會對表產(chǎn)生影響圾另。

????????索引對用戶是透明的，無論表上是否有索引雕沉，sql 語句的用法不變集乔。

????????oracle 創(chuàng)建主鍵時會自動在該列上創(chuàng)建索引。

索引: 提高查詢速度的一種手段 -->目錄

1坡椒、唯一性較好字段適合建立索引

2扰路、大數(shù)據(jù)量才有效果

3、主鍵|唯一: 唯一索引

create index 索引名 on表名 (字段列表...)

drop index 索引名

create index idx_emp on emp(sal,ename);

drop index idx_emp;

select * from emp order by sal,ename;

三倔叼、設(shè)計表

設(shè)計表首先應(yīng)該按需遵循三范式

--表與表之間的關(guān)系: 一對一 ? ? ? ? 一對多|多對一(主外鍵) ? ? ? ?? 多對多{中間表}?

--表 ?? 表名 ? 字段 ? 約束 ? ?? 表與表之間的關(guān)系

1）確定表名 ? ? ? ? ? ?? 2）確定字段名 類型 +約束(主鍵 外鍵 非空 默 檢查認(rèn) 唯一)

主鍵：唯一標(biāo)識一條記錄（唯一并且非空）

唯一：唯一

非空：不能為空

默認(rèn)：當(dāng)沒給值時使用給定一個默認(rèn)值

外鍵：參考其他表（自己）的某個（某些）字段

檢查：自定義的規(guī)則

--創(chuàng)建表與約束問題

? ? ? ? ? ? --1)創(chuàng)建表的同時不創(chuàng)建約束, 結(jié)束后追加約束

? ? ? ? ? ? --2)創(chuàng)建表的同時為字段添加約束

? ? ? ? ? ? --3)創(chuàng)建表的結(jié)構(gòu)結(jié)束之間添加約束

--約束的添加: 1)物理約束 :表中字段上添加? ? ? ? ? ? ? ?? 2)邏輯約束:java代碼上使用邏輯判斷

（一） 創(chuàng)建表（不加約束）

????????表名必須唯一汗唱，如果存在，必須刪除

--1)創(chuàng)建表的同時不添加約束

/*

? create table 表名(

? ? ? ? 字段 字段類型,

? ? ? ? 字段 字段類型,

? ? ? ? ....

? )

*/

--分析:尚學(xué)堂 : 教師表? 班級表? 學(xué)生表 ...

--學(xué)生表

create table sxt_student(

? ? ? sid number(5), --5代表有效數(shù)字 (5,2)其中2為是小數(shù)位

? ? ? sname varchar2(15), --可變長字符 默認(rèn)字節(jié)個數(shù)? 指明字符個數(shù):(5 char)

? ? ? sage number(3),

? ? ? sgender char(1 char), --定長字符

? ? ? hiredate date

)

--刪除表

drop table sxt_student;

select * from sxt_student;

-- 添加測試數(shù)據(jù)

insert into sxt_student values(01,'迪麗熱巴',25,'女',sysdate,020);

insert into sxt_student values(02,'胡歌',27,'男',sysdate,020);

insert into sxt_student values(03,'劉德華',40,'男',sysdate,022);

insert into sxt_student values(04,'鐵臂阿童木',41,'男',sysdate,020);

insert into sxt_student values(05,'神奇女俠',1,'女',sysdate,022);

insert into sxt_student(sid) values(04);

（二）創(chuàng)建表（同時創(chuàng)建約束+默認(rèn)名稱）

-- 創(chuàng)建表的同時丈攒，添加約束? 1)字段后直接添加約束哩罪，沒有約束語? ? 2)字段后直接添加約束授霸，包括約束名

create table sxt_student(

? ? ? -- 學(xué)生編號? 主鍵約束

? ? ? sid number(5) primary key,

? ? ? -- 學(xué)生姓名? 非空、唯一

? ? ? sname varchar2(15) not null unique,

? ? ? -- 年齡? 檢查約束0-150?

? ? ? sage number(3) check(sage between 0 and 45),

? ? ? -- 性別? 檢查約束 '男'&'女'

? ? ? sgender char(1 char) check(sgender in('男','女')),

? ? ? -- 入學(xué)日期? 默認(rèn)值 sysdate

? ? ? hiredate date default(sysdate),

? ? ? cid number(5)

)

--加入注釋

comment on table sxt_student is '尚學(xué)堂學(xué)生表';

comment on column sxt_student.sid is '學(xué)號,主鍵';

comment on column sxt_student.sname is '學(xué)生姓名';

comment on column sxt_student.sage is '年齡';

comment on column sxt_student.sgender is '性別';

comment on column sxt_student.hiredate is '入學(xué)日期';

comment on column sxt_student.cid is '班號';

（三）創(chuàng)建表（同時創(chuàng)建約束+指定名稱）

創(chuàng)建表的同時創(chuàng)建約束并指定約束的名稱际插，后期方便排錯碘耳，推薦使用

-- 字段后添加約束并指定約束名

create table sxt_student(

? ? ? -- 學(xué)生編號? 主鍵約束

? ? ? sid number(5) constraints pk_sxt_studnet_sid primary key,

? ? ? -- 學(xué)生姓名? 非空

? ? ? sname varchar2(15) constraints sxt_student_sname_notnull not null,

? ? ? -- 年齡? 檢查約束0-150?

? ? ? sage number(3) check(sage between 0 and 45),

? ? ? -- 性別? 檢查約束 '男'&'女'

? ? ? sgender char(1 char),

? ? ? -- 入學(xué)日期? 默認(rèn)值 sysdate

? ? ? hiredate date default(sysdate),

? ? ? cid number(5),

? ? ? -- 創(chuàng)建表結(jié)構(gòu)結(jié)束前 添加約束

? ? ? -- constraints pk_sgender check(sgender in('男','女')),? -- 添加檢查約束

? ? ? constraints sxt_student_sname_unique unique(sname)? -- 添加唯一約束

)

-- 后續(xù)追加約束

alter table sxt_student add constraints pk_sgender check(sgender in('男','女'));

-- 刪除約束

alter table sxt_student drop constraints sxt_student_sname_notnull;

（四）約束的禁用與啟用

ALTER TABLE tb_user disable constraint nn_user_name;

ALTER TABLE tb_user enable constraint nn_user_name;

（五）刪除約束

alter table tb_user drop constraint uq_user_email cascade;

（六）修改約束

--非空

alter table tb_user modify (username varchar2(20));

--默認(rèn)

alter table tb_user modify (age default null);

（七）序號

使用工具|程序管理流水號，序列在創(chuàng)建時 沒有與表關(guān)聯(lián) ,在操作數(shù)據(jù)時 與表關(guān)聯(lián)

1框弛、創(chuàng)建

create sequence序列名 start with 起始值 increment by 步進;

2辛辨、使用

在操作數(shù)據(jù) 添加 更新-->主鍵

1)、currval :當(dāng)前值

2)瑟枫、nextval:下個值

create sequence seq_tb_user start with 2 increment by 2;

drop sequence seq_tb_user;

select seq_tb_user.nextval from dual;

select seq_tb_user.currval from dual

3愉阎、刪除

drop sequence序列名