版權聲明：本文為部落客原創文章，遵循 CC 4.0 BY-SA 版權協定，轉載請附上原文出處連結和本聲明。
本文連結：https://blog.csdn.net/zhao1299002788/article/details/101758010
/*
起别名使用雙引号 處理特殊字元使用
資料庫裡的字元串都是使用單引号
*/
/*
DDL語句 是資料定義語言 使用語句建立資料庫的對象
表空間 是執行個體配置設定的一塊空間 用于開發使用
建立文法： create tablespace 表空間名
datafile 檔案的路徑
size 檔案大小
autoextend on
next 擴充大小
*/
--示範表空間建立 需要使用管理者操作
create tablespace baidu
datafile 'c:\baidu.dbf'---linux系統
size 100m
autoextend on
next 10m
---------------------------------
/*
開發使用管理者建立配置設定的使用者
使用者的建立
create user 使用者名
identified by 密碼
default tablespace 表空間名
*/
---建立使用者
create user baidu
identified by baidu
default tablespace baidu
--建立完成使用者 登陸測試 缺少權限
/*
授權文法：
grant 權限 to 使用者
權限的分類
connect 連接配接的權限可以連接配接資料庫
resource 進階一點的權限 可以建表
dba 最進階的權限 相當于管理者
*/
---授權connect給baidu使用者 測試登陸
grant connect to baidu
--授予dba權限
grant dba to baidu
------
create table p(
pid number(9),
pname varchar(10)
)
---------------------------------------------------------------
---plsql developer 圖形化工具 instanclient
/*
對表結構的設計
表中含有多少個字段 根據需求來
表中字段都是什麼資料類型
主鍵外鍵 限制
資料類型
數值類型
int double bigint --mySql支援的資料類型
number(16,v2) v1是數值的總長度 v2是數值的小數位數 預設為0
number(6,2)---9999.99
字元類型
char() --固定長度的字元類型 char(20) zs
實際長度是 2 占用空間 20個長度
varchar() --可變長度的字元類型 varchar(20) zs 張三 utf8 6 gbk 4
實際長度是 2 占用空間 2個長度
varchar2() --可變長度的字元類型 varchar(20) zs 張三 utf8 6 gbk 4
實際長度是 2 占用空間 2個長度 推薦使用
日期類型
date mySql格式 yyyy-mm-dd
oracle 格式 yyyy-mm-dd hh:mi:ss
datetime mySql格式 yyyy-mm-dd hh:mi:ss
oracle 格式 yyyy-mm-dd hh:mi:ss 精确到後面的9位納秒
大文本類型
clob 字元類型大文本 最大支援4G的長度
資料庫存放網頁的源代碼
blob 二進制類型大文本 最大支援4G的長度
long 長文本 最大支援2g的長度
*/
/*
限制
主鍵限制 primary key 非空加唯一
外鍵限制 foreign key
唯一限制 unique 唯一
非空限制 not null 非空
檢查限制 check (gender in (0,1)) 判斷數值是否違反表達式
手動使用關鍵字指定限制 constraint 限制名稱 限制類型(列)
*/
---使用資料類型和限制建表操作
create table person(
pid number(11) ,
pname varchar2(11) not null,
phone varchar2(11) unique,
gender number(1) check (gender in (0,1)),
constraint pk_person_pid primary key(pid)
)
---插入資料測試限制 oracel事務必須手動選擇送出或者復原
insert into person values(1,'zs','11122223333',1);
insert into person values(1,'zs','11122223333',1);--違反主鍵限制
insert into person values(2,'zs','11122223333',1);--違反唯一限制
insert into person values(2,'zs','11122224444',1);
insert into person values(3,null,'11122225555',1);--違反非空限制
insert into person values(3,'','11122225555',1); --空串違反非空限制
insert into person values(3,' ','11122225555',1);
insert into person values(4,'zs','11122226666',3);--違反檢查限制
insert into person values(4,'zs','11122226666',0);
commit;
/*
表結構的修改
增加一列 alter table 表名 add(列名 數值類型)
修改一列 alter table 表名 modify(列名 數值類型)
重命名列 alter table 表名 rename column 舊列名 to 新列名
删除一列 alter table 表名 drop column 列名
*/
--給person表增加位址一列
alter table person add(address varchar2(20));
--修改address為char 類型 10個長度
alter table person modify(address char(10));
alter table person modify(pname number(11)); --列如有有資料不可以修改類型 報錯
--重命名gender性别為sex
alter table person rename column gender to sex
--删除位址address
alter table person drop column address
/*
DML語句 資料操作語言 對表中資料做增删改
插入資料 insert into 表名 values(.....)
insert into person values(1,'zs',1); --不能插入 列數量不比對
insert into person(pid,pname,sex) values(1,'zs',1); --指定列名插入資料
修改資料
update 表名 set 列名=值 where 條件 修改滿足條件的記錄
删除資料
delete from 表名 where 條件 删除比對的資料
delete from 表名 删除所有記錄 一條條删除
效率低 可以加條件
truncate table 表名 摧毀表結構 再重建表結構
效率高 不能加條件
*/
--想使用emp表的資料做測試
select * from emp;
--建立表的同時拷貝表的資料 scott使用者下的emp
create table emp as select * from scott.emp;
--修改SMITH使用者更改名稱為SSSS
update emp set ename='SSSS' where ename='SMITH';
commit;
/*
存在主外鍵的情況下 直接删除主表的記錄
一、 1.先删除從表記錄
2.再删除主表記錄
二、級聯删除 on delete cascade
三、直接删除主表
*/
--建立主表訂單 orders表
create table orders(
oid number(11) primary key,
oname varchar2(11) ,
oprice number(6,2)
)
--建立字表訂單明細表 order_detail
create table order_detail(
detail_id number(11) primary key,
detail_name varchar2(11) ,
detail_price number(6,2),
oid number(11),
constraint fk_detail_oid foreign key(oid) references orders(oid)
-- on delete cascade
)
--插入主表和從表的記錄
insert into orders values(1,'訂單1',1000);
--insert into order_detail values(1,'訂單1',1000,2);--違反外鍵限制
insert into order_detail values(1,'訂單1',1000,1);
commit;
----
select * from orders;
select * from order_detail;
----直接删除主表記錄測試
delete from orders where oid=1;
delete from order_detail where detail_id = 1;
commit;
drop table order_detail;
--直接删除主表 強制删除 不建議使用
drop table orders cascade constraint
/*
事務 作為一個邏輯操作單元 執行的任務全部成功，或者全部失敗
特性：ACID (原子性 持久性 隔離性 一緻性)
沒有隔離級别 髒讀 幻讀 不可重複讀
調整隔離級别
oracel資料庫隔離級别 READ COMMITED ,SERIALIZABLE,READ ONLY
預設隔離級别是READ COMMITED
事務的儲存點：
事務儲存點的概念: 儲存起執行成功的任務
意義：可以保證執行成功的任務正常送出
使用方法：
聲明事務儲存點 savepoin 儲存點名
出現錯誤復原到儲存點 rollback to 儲存點
再繼續送出 commit
spring管理事務 在servie層切入點
*/
declare
begin
insert into orders values(1,'訂單1',1000);
insert into orders values(2,'訂單1',1000);
insert into orders values(3,'訂單1',1000);
insert into orders values(4,'訂單1',1000);
insert into orders values(5,'訂單1',1000);
savepoint s1; --聲明儲存點
insert into orders values(6,'訂單1',1000);
insert into order_detail values(1,'訂單1',1000,100);
commit;
exception
when others then
rollback to s1;
commit;
end;
/*
資料庫其餘對象
視圖 是一個虛拟的表 不存放資料 資料來源為原始表
意義是： 為了資料的安全
為了權限的細分
建立視圖 檢視特定的資料
create view 視圖名 as select * from 表
*/
--檢視員工資訊
select * from emp;
--建立視圖
create view emp_view as select empno,ename,job,deptno from emp;
--查詢視圖
select * from emp_view
--修改視圖
update emp_view set ename='SMITH' where ename='SSSS';
commit;
--建立隻讀的視圖
create view e_view as select empno,ename,job,deptno from emp with read only
update e_view set ename='SSSS' where ename='SMITH';
commit;
/*
序列 是oracle資料生成的一系列數值 用來實作 (序列是對象,是以有屬性)
表中記錄id的自增長
建立序列
create sequence 序列名稱
序列的屬性
nextval --下一個值
currval --目前值
create sequence sequence
[INCREMENT BY n] 表示自增長,每次增長n個
[START WITH n] 表示初始值,n就是初始值.
[{MAXVALUE n | NOMAXVALUE}] 表示最大值
[{MINVALUE n | NOMINVALUE}] 表示最小值
[{CYCLE | NOCYCLE}] 表示循環 表示如果設定了最大值,當數字達到最大值以後,會進行循環的設定值,這個不适用于自動增長的主鍵(非空唯一的)
[{CACHE n | NOCACHE}] 表示緩存,表示緩存n個,如果n是10,表示一次緩存10個序列.
*/
--建立序列
create sequence order_sequence
select order_sequence.nextval from dual; --序列預設值從1開始 nextval生成不會因為插入失敗回退
select order_sequence.currval from dual; --檢視目前值 必須先生成nextval
insert into orders values(order_sequence.nextval,'訂單1',1000);
commit;
/*
索引 了解為一本書的目錄
沒有目錄找到特定章節 費時很長
意義：為了提升查詢資料的速度(通過id,建立樹結構,來提升查詢效率)
前提 ： 隻有資料量非常大的情況下 才有意義
建立索引
單行索引 create index 索引名稱 on 表(列)
複合索引 create index 索引名稱 on 表(列,列2)
create index index_order on orders(oname)
建立索引是在資料庫中建立索引結構,放的是索引的列的數值rowid,将oname的數值按照從小到大的排序,
當where oid = 139,通過條件去索引結構定義記錄的位置,資料庫會通過rowid真實位址去查找資料.
優點 : 提升查詢速度.
缺點 : 占用空間.
增删改資料效率降低,需要更新索引結構,如果表中有主鍵和唯一限制會自動建立索引.
索引使用規範 : 用在于列的數值重複資料很少情況.例如 : 當gender列都是1,會影響索引的查詢效率
索引分類 : 常用的是 unique唯一索引,normal普通索引.
*/
--建立大資料量的表
--序列生成的屬性 在同一sql語句中隻會生成一次
declare
begin
for i in 1..5000000 loop
insert into orders values(order_sequence.nextval,'訂單'||order_sequence.nextval,1000);
commit;
end loop;
end;
select count(*) from orders
--先查詢資料 記錄耗時
select * from orders where oname='訂單3333333' --2.234 15 18 19
--建立索引
create index index_order on orders(oname) --一分多鐘
--建立索引後 查詢同樣的記錄 耗時
select * from orders where oname='訂單3333333' ---0.204
--多個條件作為查詢 不是觸發單行索引
select * from orders where oname='訂單3333333' and oprice=1000
--自動建立索引,根據oid建立的.
select * from orders where oid=4444444
/*
rowid 是資料庫在儲存資料時候 生成的真實實體位址
差別rownum 是一個僞列 是在查詢資料時候才會生成的，可變
rowid 是插入資料就已經生成了 固定的實體位址 唯一不變
*/
--通過以下查詢,可以對表進行直接修改.
select rowid,emp.* from emp;
/*
同義詞 可以了解為一個對象的别名
意義： 為了資料的安全
為了權限的細分
建立同義詞
create synonym 同義詞名 for 使用者.對象
oracel的from後面可以跟同義詞,視圖,表,這三種對象.
*/
--查詢員工表
select * from scott.emp;
create synonym syn_emp for scott.emp;
---查詢同義詞
select * from syn_emp
/*
資料庫的導入導出
1.為了資料安全 備份和還原使用
2.為了伺服器的遷移 把以前舊伺服器的整個資料庫遷移到新伺服器
3.開發人員的操作 是為了部署項目導入表結構
開發是在測試環境 開發完成 需要上線
代碼 上傳到伺服器 tomcat
開發的資料庫表 上傳到伺服器資料庫
去資料庫建表 直接new 有風險
公司裡一般都是 導出建立的表結構 通過指令運作
導入導出實作
通過指令行導入導出 需要安裝oracel資料庫伺服器
導出指令 exp
整個資料庫導入 exp 使用者名/密碼 file=檔案.dmp full = y
按照使用者導出 exp 使用者名/密碼 owner= 使用者 file=檔案.dmp
按照表來導出 exp 使用者名/密碼 file=檔案.dmp tables=表名，表名2
導入 将exp 換成 imp
通過圖形化工具導入導出
tools --export user objects 導出表結構 不能備份資料 包含所有的對象
tools --exprot tables 導出表可以備份結構和資料 預設情況隻能備份資料
oracel資料庫格式 .dmp檔案 伺服器才能做
sql檔案格式 .sql檔案
圖形化工具格式 .pde檔案
*/ 複制