sequence 序列、synonym同義詞滥嘴、DBLink連接木蹬、索引index、view視圖、數(shù)據(jù)建模镊叁、PL/SQL 語言
接著前天的多表連接尘颓、子查詢、分組查詢等知識點晦譬，今天繼續(xù)分享心得知識點疤苹。
sequence 序列
--序列可以讓主鍵自動增長
--使用的時候，要先創(chuàng)建
create sequence stu_seq;
create table tb_class(
? ? ? id int primary key,
? ? ? code varchar(20)
);
insert into tb_class values(stu_seq.nextval,'java091');
select * from tb_class;
insert into tb_class values(stu_seq.nextval,'java09');
--synonym同義詞
--先創(chuàng)建敛腌，后使用
create synonym myclass for tb_class;
select * from? myclass;--用myclass代替了tb_class表卧土，也就是別名。
select * from tb_class;
---- DBLink連接
--找到oracle的安裝路徑
--C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN
--1像樊、listener.ora監(jiān)聽器
--2尤莺、sqlnet.ora網(wǎng)絡
--3、tnsnames.ora tns配置文件
--創(chuàng)建連接
create database link hello
? ? ? connect to system identified by "1234"
? ? ? using '(DESCRIPTION=
? ? ? (ADDRESS_LIST=(
? ? ? ADDRESS=(PROTOCOL=TCP)(HOST=10.25.161.113)(PORT=1521))
? ? ? )
? ? ? (CONNECT_DATA=(SERVER=DEDICATED)
? ? ? (SERVICE_NAME=Oracle)
? ? ? )
? ? ? )';
? ? ? select * from scott.emp@hello;
如何優(yōu)化你的數(shù)據(jù)庫查詢生棍？
1、數(shù)據(jù)庫的查詢方式涂滴，三種：
--全表掃描友酱，最慢，select * from tb_stu;
--利用索引掃描柔纵，快缔杉，推薦使用。方式是key-value
--共享語句：必須建立在全表掃描的基礎上首量，已經(jīng)查詢過的數(shù)據(jù)留在緩存中壮吩，直接去緩存中查找。
index索引
作用：在數(shù)據(jù)庫中用來加速對于表的查詢加缘。（也就是優(yōu)化數(shù)據(jù)庫）
原理：通過使用快速路徑訪問方法快速定位數(shù)據(jù)鸭叙，減少磁盤的I/O.
特點2個：與表獨立存放，但不能獨立存在拣宏，必須屬于某個表沈贝。
由數(shù)據(jù)庫自動維護，當表被刪除時勋乾，該表上的索引自動被刪除宋下。
--索引的建立
自動：當建立一個表的時候，定義主鍵primary key或者unique約束條件時辑莫，數(shù)據(jù)庫自動創(chuàng)建索引学歧。
手動：用戶可以創(chuàng)建索引加速查詢（最好是指定經(jīng)常要查詢的字段）
--怎么工作的：當創(chuàng)建索引的時候，oracle 會默認建立一張與當前表相關的索引頁各吨，里面保存了索引字段和對應的真實的磁盤地址枝笨，當用戶發(fā)送sql
語句帶來索引的時候，oracle會到索引頁中查詢索引字段，直接定位磁盤的IO,提取數(shù)據(jù)横浑。
select * from tb_stu;
insert into tb_stu values(1,'jss',1);
--創(chuàng)建索引index
create index 索引名 on 表名（需要索引的字段）;
create index 索引名 on 表名（索引字段）剔桨；
eg:
create index stu_index on tb_stu(name);
select * from tb_stu where name="翠花";
--多使用共享語句，可以到達sql語句的優(yōu)化徙融。
--特殊的：當sql語句中包含not in ,<>不等于洒缀，is not null,like '%%'的時候不會建立索引。
in：會堆成一堆or欺冀，可以使用表的索引树绩。
not in :強烈推薦不使用。因為它不能應用表中的索引脚猾。
<>操作符 不等于：是永遠不會有用到索引的葱峡，因此它只會建立全表查詢。（優(yōu)化的方案：用其他相同功能的代替龙助，比如：a<>0,改成a<0 or a>0,a<>’’ 改為 a>’’）
is null或者is not null (判斷字段是否為空)：判斷字段是否為空一般也是不應用索引的，優(yōu)化方法跟<>一樣蛛芥。
like :like操作符可以應用通配符查詢提鸟，但是用的不好則會產(chǎn)生性能上的問題。
優(yōu)化方案：如LIKE ‘%001%’ 這種查詢不會引用索引仅淑，會產(chǎn)生全表掃描称勋，
而LIKE ‘001%’則會引用范圍索引。進行范圍的查詢涯竟，性能肯定大大提高赡鲜。
create table aaa (
? ? ? comm int default 0,
? ? ? status varchar(20) default '已發(fā)'
);
create table bbb (
? ? ? email varchar(50) default 'zsfz053@126.com'
);
select * from aaa where comm is null; -- 不會用到 index
select * from bbb where email = 'zsfz053@126.com';? -- 會用到 index
--視圖:
- 創(chuàng)建表空間
create tablespace Jss
datafile 'F:\dataspace\jss.dbf'
size 20M
autoextend on; -- 自動擴展，如果容量不夠的話庐船，則自動加
-- 千萬記得：不能手動到路徑下把生成的 xxx.dbf 文件刪掉银酬。
-- 如果需要刪除表空間
drop tablespace jss including contents and datafiles;
-- 查詢所有的表空間
select * from DBA_tablespaces;
-- 查詢所有的數(shù)據(jù)文件
select * from Dba_Data_Files;
-- 創(chuàng)建用戶
create user lin? ? -- 用戶名
identified by 1234 -- 密碼
default tablespace jss? -- 對應的表空間
temporary tablespace temp? -- 臨時表空間
-- 刪除用戶
drop user jss;
-- 查詢所有的用戶
select * from Dba_Users;
-- 給用戶加鎖和解鎖
alter user jss account lock;
alter user jss account unlock;
-- 操作權限
-- 連接權限
grant connect to jss;
-- 如果 jss 有連接權限，可以指定給 admin 賬戶
grant connect to jss with admin option;
-- 如果想要放大權限的話筐钟，可以指定 DBA 權限給它
grant dba to jss with admin option;
grant dba to jss with admin option;
-- 收回權限
revoke connect from jss;
revoke dba from jss;
創(chuàng)建用戶的時候通過dba賦予的權限揩瞪，創(chuàng)建視圖的時候會報：沒有權限。
--解決方案：使用system登錄篓冲，再給用戶賦予權限李破。
grant create any table to jss with admin option;
grant create any view to jss with admin option;
graant select? any table to jss with admin option;
--看看視圖是怎么樣讓查詢變的簡單的：
select deptno 部門, sum(sal) 總薪資, max(sal) 最大值--注意要給別名，不然會出錯
from scott.emp
group by deptno;
create view aaa
as
select deptno 部門, sum(sal) 總薪資, max(sal) 最大值--注意要給別名壹将，不然會出錯
from scott.emp
group by deptno;
接著：
select * from aaa;
--復雜視圖(多個表)
create view bbb
as
select d.dname 部門, d.loc 地址, e.*
from scott.dept d, scott.emp e
where d.deptno = e.deptno
select * from bbb;
--刪除視圖
drop view aaa;
--注意：可以通過視圖操作表嗤攻，但是不建議。
軟件開發(fā)過程:
1. 需求調(diào)研,與客戶進行溝通
2. 需求分析诽俯，將現(xiàn)實工作中的動作模擬到計算機
? 數(shù)據(jù)建模
3. 開發(fā)
4. 測試
5. 上線部署
從關系數(shù)據(jù)庫的表中刪除冗余信息的過程稱為規(guī)范化妇菱，
是得到高效的關系型數(shù)據(jù)庫表的邏輯結構最好和最容易的方法。
獲得數(shù)據(jù)規(guī)范化的方法: 三范式
第一范式：必須要有主鍵,并且每個屬性值,都是不可再分的最小數(shù)據(jù)單位，
? ? ? ? ? 則稱R是第一范式的關系恶耽。
第二范式：所有非主關鍵字都完全依賴于主關鍵字(通常用于聯(lián)合主鍵)
第三范式：非主關鍵字不能依賴于其他非主關鍵字（通常用于一個主鍵）
-- 有 N 個班級密任，有 N 個學生，有 N 門課程
-- 一個班可以有多個學生偷俭，一個學生只能屬于一個班浪讳，
-- 一個學生可以選修多門課程，一門課程可以被多個學生選修
數(shù)據(jù)建模
1. 根據(jù)三個范式
2. 分析實體之間的關系, ER 圖 Entity-Reference（畫出來看涌萤，會明顯很多）
一對一：一個人只有一個身份證淹遵，唯一外鍵關聯(lián)或者主鍵關聯(lián)扼仲。
一對多：一個班級可以有多個學生审葬。一個學生只屬于一個班級（clazz - student）
? ? ? ? 關聯(lián)：一對多使用主外鍵關聯(lián)宙帝，通常在多方（student）建立外鍵
多對多：一個學生可以選擇多門課程洛巢，一門課程可以被多個學生選修 （student - course）
? ? ? ? 關聯(lián)：多對多通常使用中間表（再多建一張表存儲）關聯(lián)數(shù)據(jù)
? ? ? ? ? ? ? 通常中間表會有兩張表的id作為聯(lián)合主鍵庇麦，并且作為外鍵指向關聯(lián)表
下面有個實例：
網(wǎng)上購書
圖書表: id禾怠、書名糕珊、出版社哄尔、出版時間崖堤、價格侍咱、作者、描述
用戶表：id密幔、姓名楔脯、地址、電話胯甩、email
訂單表: id昧廷、編號code
-- 用戶表
create table tb_user (
? ? ? id int primary key,
? ? ? name varchar(20) not null,
? ? ? address varchar(50) default '廣州天河',
? ? ? phone varchar(20) unique,
? ? ? email varchar(20) unique
);
insert into tb_user
values (1, '翠花', '中國香港', '13800138000', 'cuihua@163.com');
insert into tb_user
values (2, '春花', '中國臺灣', '13800138001', 'chunhua@163.com');
-- 圖書表
create table tb_book (
? ? ? id int primary key,
? ? ? title varchar(50) not null,
? ? ? publication varchar(20) not null,
? ? ? publictime date,
? ? ? price number,
? ? ? author varchar(20),
? ? ? note varchar(100)
)
insert into tb_book
values (1, 'Java 從入門到放棄', '電子工業(yè)出版社', sysdate, 100, 'Jss', '很好的書');
insert into tb_book
values (2, 'Python 從入門到放棄', '電子工業(yè)出版社', sysdate, 120, 'Jss', '很好的書');
insert into tb_book
values (3, 'C++ 從入門到放棄', '電子工業(yè)出版社', sysdate, 99, 'Jss', '很好的書');
-- 訂單表
create table tb_order (
? ? ? id int primary key,
? ? ? orderno varchar(30) not null,
? ? ? user_id int,
? ? ? foreign key (user_id) references tb_user(id)
);
insert into tb_order
values (1, '001', 1);
insert into tb_order
values (2, '002', 1);
insert into tb_order
values (3, '003', 2);
-- 訂單和書籍是多對多的關系，創(chuàng)建中間表維護關聯(lián)關系?
create table tb_order_book (
? ? ? order_id int,
? ? ? book_id int,
? ? ? count int,
? ? ? primary key(order_id, book_id),
? ? ? foreign key(order_id) references tb_order(id),
? ? ? foreign key(book_id) references tb_book(id)
);
insert into tb_order_book
values (1, 1, 2);
insert into tb_order_book
values (2, 1, 5);
insert into tb_order_book
values (3, 3, 10);
select * from tb_user;
select * from tb_book;
select * from tb_order;
3. 查詢“翠花”的詳細信息偎箫，包括：
? 用戶信息木柬、訂單信息、購買書籍名稱镜廉、購買的數(shù)量
select u.name 名字, o.orderno 訂單號, b.title 圖書名稱, ob.count 數(shù)量
from tb_user u, tb_order o, tb_book b, tb_order_book ob
where u.id = o.user_id
and b.id = ob.book_id
and o.id = ob.order_id
and u.name = '翠花';
- PL/SQL 也是一種程序語言弄诲，叫做過程化SQL語言（Procedural Language/SQL），是 Oracle 數(shù)據(jù)庫對 SQL 語句的擴展娇唯。 PL/SQL 只有 Oracle 數(shù)據(jù)庫有齐遵。
-- MySQL 目前不支持 PL/SQL 的，但支持 Navicat Premium塔插。
-- 實際開發(fā)中梗摇，我們一般都是通過 Java 代碼來處理數(shù)據(jù)
-- 將處理好的結果，再交給 SQL 去執(zhí)行即可想许。
-- 語法格式
declare? -- 聲明部分
? ? -- 定義變量
begin? -- 開始
? ? -- 主要內(nèi)容
exception? -- 處理異常
end;? -- 結束
declare --聲明
begin--開始伶授，主要內(nèi)容
end;結束
--打印語句
begin
--包.方法（存儲過程）
dbms_output.put_line('jss打游戲打不過人家断序。。糜烹。');
end;
--定義變量
declare
? vid int;
vname varchar(20):='jss';--:=是賦值
begin
? dbms_output.put_line('jss打游戲打不過人家违诗。。疮蹦。');
end;
--復雜類型
declare
? vid int;
vname varchar(20);
type car is record(--復雜類型的聲明：type car is record
id int,
name varchar(20),
price number
);
--如果要給復雜類型數(shù)據(jù)賦值诸迟，需要給它一個變量才可以。
vcar car;
begin
--declare處聲明愕乎，begin處賦值
vname:='jss';
vcar.name:='BMW X6';
dbms_output.put_line(vname||'就算有'||vcar.name||'jss打游戲打不過人家阵苇。。感论。');--連接字符用||
end;
PL/SQL的使用：插入數(shù)據(jù)绅项、查詢數(shù)據(jù)、for循環(huán)比肄，if else邏輯語句
--插入數(shù)據(jù)
declare
vid int:=1;
vname varchar(20):='花花';
begin
insert into tb_stu(id,name) values (vid,vname);
end;
--查詢數(shù)據(jù)(查詢在聲明變量中有的字段)
declare
vid int:=1;
vname varchar(20);
begin
select name into vname from tb_stu where id=vid;
dbms_output.put_line(vname);
end;
--if else邏輯語句
--if
declare
i number:=10;
begin
if(i=10) then
dbms_output.put_line('此處打印出10...');
end if;--要給if 結束語句end if;
end;--每句后面都加個;來去隔開快耿。
--if else
declare
i number:=20;
begin
if(i=10) then
dbms_output.put_line('此處打印出10...');
else
dbms_output.put_line('此處打印的不是10...');
end if;--要給if 結束語句end if;
end;
--if...else if
declare
i number:=20;
begin
if(i=10) then
dbms_output.put_line('此處打印出10...');
else if(i=20)
dbms_output.put_line('此處打印的是20...');
else
dbms_output.put_line('此處打印出不是10，也不是20...');
end if;--要給if 結束語句end if;
end;
--loop循環(huán)
declare
i number:=0;
begin
loop
i:=i+1;
dbms_output.put_line('jss...');
exit when i=5;
end loop;
end;
--for循環(huán)
declare
i number:=0;
begin
for i in 1..6--for用來指定條件
loop
dbms_output.put_line('jss...');
end loop;
end;
--while循環(huán)
declare
i number:=0;
begin
while i<6
loop
dbms_output.put_line('cuihua..');
i:=i+1;
end loop;
end;
