2018-06-28 第五十三天 oracle

一、數(shù)據(jù)庫對象

oracle 的數(shù)據(jù)庫對象：表、用戶、約束、索引、序列、視圖


（一）關(guān)于用戶的操作

--創(chuàng)建一個新的用戶，然后給新的用戶創(chuàng)建一張表，然后給表中添加一些數(shù)據(jù)。查詢表中的數(shù)據(jù)。


--?對用戶進行操作

--?創(chuàng)建用戶?需要當(dāng)前用戶擁有dba?的權(quán)限。

--新創(chuàng)建的用戶沒有任何?的權(quán)限，連基本的登錄的權(quán)限都沒有。

create?user?hw?identified?by?hw


--通過給用戶授予角色來給用戶授予一組權(quán)限。

--角色是一組權(quán)限的集合

--?給用戶授予權(quán)限

grant?dba?to?scott

grant?connect?,?resource?to?hw


--?給用戶撤銷權(quán)限

revoke?dba?from?scott


--?修改密碼

alter?user?hw?identified?by?hwei


--?刪除用戶

drop?user?hw


--?創(chuàng)建表格

--?建立一張用來存儲學(xué)生信息的表

--?字段包含學(xué)號、姓名、性別，年齡、入學(xué)日期、班級，email等信息

create?table?student(

???????sno?number(6),

???????sname?varchar2(12),

???????gender?varchar2(3)?default?'男',

???????age?number(3),

???????sdate?date,

???????clazz?varchar(10),

???????email?varchar2(30)

);


select?*?from?student


--給表插入數(shù)據(jù)

insert?into?student?values(100001,'小剛','男',20,sysdate,'501','xiaogang@qq.com')

commit


insert?into?student?values(100002,'小張',null,20,sysdate,'501','xiaozhang@qq.com');

commit


insert?into?student?(sno,sname,age,sdate,clazz,email)?values?(100003,'小蕾',30,sysdate,'501','xiaotiantian@qq.com');


update?student??set?gender='女'?where?sno=100003


--表刪除數(shù)據(jù)

delete?fromstudent?where?sno=100002


（二）關(guān)于表的操作

--對表的以及表的結(jié)構(gòu)的操作

--給表格添加字段

alter?table?student?add?(birthday?date)--新字段的內(nèi)容為null

alter?table?student?add?(score?number(3)?default?100)--新字段的內(nèi)容指定為默認(rèn)值


--刪除表的字段

alter?table?student?drop?column?birthday


--修改字段的名稱?

alter?table?student?rename?column?sdate?to?enterdate


--修改字段的數(shù)據(jù)類型

--?如果想要修改某一列的數(shù)據(jù)類型，那么該列的所有的數(shù)據(jù)都需要是null.

alter?table?student?modify?(birthday?varchar2(20))


--重命名表

rename?student?to?stu


---刪除表操作

drop?table?emp

--查看回收站

select?*?from?recyclebin

--從回收站還原表格

flashback?table?emp?to?before?drop

select?*?from?stu


--將回收站中的某個表刪除

purge?table?emp--凈化


--不進入回收站，直接刪除

drop?table?emp?purge


--清空回收站

purge?recyclebin


（三）約束

完整性約束分類

域完整性約束（非空not null，檢查check）

實體完整性約束（唯一unique，主鍵primary key）

參照完整性約束（外鍵foreign key）

三種完整性約束的區(qū)別

域完整性約束:字段約束

實體完整性約束：行和行之間的約束

引用完整性約束：表和表之間的約束


命名規(guī)則推薦采用：約束類型_約束字段

非空約束?NN_表名_列名

唯一約束?UK_表名_列名

主鍵約束?PK_表名

外鍵約束?FK_表名_列名

檢查約束?CK_表名_列名


--數(shù)據(jù)庫對象--約束


--?創(chuàng)建表格

--?學(xué)號是主鍵

--?姓名不能為空

--?年齡范圍18---30歲

-- Email唯一


--【1】主鍵約束--primary key

--?字段?非空+?唯一??

drop?table?student?purge;

create?table?student(

??????-- sno number(6) constraints pk_student primary key, --列級?主鍵約束

???????sno?number(6)?primary?key,--簡化的列級主鍵約束?

???????sname?varchar2(12),

???????gender?varchar2(3)?default?'男',

???????age?number(3),

???????sdate?date,

???????clazz?varchar(10),

???????email?varchar2(30)

???????--constraints PK_STUDENT primary key (sno)--表級設(shè)置主鍵約束

???????--primary key (sno)--簡化的表級設(shè)置主鍵約束

);

insert?into?student??values(100001,'小剛','男',20,sysdate,'501','xiaogang@qq.com');

select?*?from?student


--聯(lián)合主鍵約束--primary key

--?字段?的組合?必須是唯一的

--?每一個字段的值都不能是null

drop?table?student?purge;

create?table?student(

???????sno?number(6),--聯(lián)合主鍵?約束?只能?在表級別設(shè)置

???????sname?varchar2(12),

???????gender?varchar2(3)?default?'男',

???????age?number(3),

???????sdate?date,

???????clazz?varchar(10),

???????email?varchar2(30),

???????constraints?PK_STUDENT?primary?key?(sno?,?sname)--表級設(shè)置主鍵約束

??????-- primary key (sno , sname)--簡化的表級設(shè)置聯(lián)合主鍵約束

);

insert?into?student??values(100001,'小紅','男',20,sysdate,'501','xiaogang@qq.com');

insert?into?student??values(100002,'小剛','男',20,sysdate,'501','xiaogang@qq.com');

select?*?from?student


--【2】非空約束??not null

--指定的域不能為null ??姓名不能為空

drop?table?student?purge;

create?table?student(

???????sno?number(6),

???????--sname varchar2(12) constraints nn_student_sname not null, --?只能在列級別設(shè)置??非空約束

???????sname?varchar2(12)?not?null,--簡化的列級別設(shè)置非空約束

???????gender?varchar2(3)?default?'男',

???????age?number(3),

???????sdate?date,

???????clazz?varchar(10),

???????email?varchar2(30),

???????constraints?PK_STUDENT?primary?key?(sno)--表級設(shè)置主鍵約束

??????-- primary key (sno , sname)--簡化的表級設(shè)置聯(lián)合主鍵約束

??????--constraints nn_student_sname not null (sname)--非空約束?只能在?列級?設(shè)置,不能在表級別設(shè)置

);

insert?into?student??values(100001,null,'男',20,sysdate,'501','xiaogang@qq.com');


--【3】唯一約束?unique

--字段必須是唯一的，可以為null?但是只能有一條數(shù)據(jù)的值為?null

drop?table?student?purge;

create?table?student(

???????sno?number(6),

???????sname?varchar2(12)?constraints?nn_student_sname?not?null,

???????gender?varchar2(3)?default?'男',

???????age?number(3),

???????sdate?date,

???????clazz?varchar(10),

??????-- email varchar2(30) unique,--?列級別的唯一約束

??????email?varchar2(30),-- constraints uk_student_email unique,---列級別的唯一約束

???????constraints?PK_STUDENT?primary?key?(sno),--表級設(shè)置主鍵約束

???????constraints?uk_student_email?unique?(email)--表級別設(shè)置唯一約束

???????--自己測試是否可以使用簡化的表級別的設(shè)置唯一約束

);


insert?into?student??values(100001,'小剛','男',20,sysdate,'501','xiaogang@qq.com');

select?*?from?student


--【4】檢查約束??check

--?控制列字段的取值的范圍

drop?table?student?purge;

create?table?student(

???????sno?number(6),

???????sname?varchar2(12)?constraints?nn_student_sname?not?null,

???????gender?varchar2(3)?default?'男'?check?(gender?in?('男'?,?'女')),--列級別的檢查約束

???????age?number(3),

???????sdate?date,

???????clazz?varchar(10),

???????email?varchar2(30),-- constraints uk_student_email unique,---列級別的唯一約束

???????constraints?PK_STUDENT?primary?key?(sno),--表級設(shè)置主鍵約束

???????constraints?uk_student_email?unique?(email),--表級別設(shè)置唯一約束

???????--constraints ck_student_age check (age >=18 and age <=24)--表級別的檢查約束

???????constraints?ck_student_age?check?(age?between?18?and?24)--表級別的檢查約束

);


insert?into?student??values(100003,'小剛','女',24,sysdate,'501','xiaogang2@qq.com');

select?*?from?student


--【5】外鍵約束??foreign key

--創(chuàng)建主表??clazz

create?table?myclazz(

???????cno?varchar2(6)?primary?key,

???????cname?varchar2(20)?not?null,

???????loc?varchar2(30)

);

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

insert?into?myclazz?values(100002,'502','bj')

insert?into?myclazz?values(100003,'306','sh')

insert?into?myclazz?values(100001,'213','hf')

select?*?from?myclazz


--從表?依賴于主表的字段必須是主表的主鍵

drop?table?student?purge;

create?table?student(

???????sno?number(6),

???????sname?varchar2(12)?constraints?nn_student_sname?not?null,

???????gender?varchar2(3)?default?'男'?check?(gender?in?('男'?,?'女')),--列級別的檢查約束

???????age?number(3),

???????sdate?date,

???????cno?varchar(6)?references?myclazz?(cno),--列級定義外鍵約束

???????email?varchar2(30),

???????constraints?PK_STUDENT?primary?key?(sno),--表級設(shè)置主鍵約束

???????constraints?uk_student_email?unique?(email),--表級別設(shè)置唯一約束

???????constraints?ck_student_age?check?(age?between?18?and?24)--表級別的檢查約束

??????-- constraints fk_student_cno foreign key (cno) references myclazz (cno)--表級別設(shè)置外鍵約束

);

insert?into?student??values(100003,'小剛','女',24,sysdate,'100001','xiaogang2@qq.com');

insert?into?student??values(100001,'小剛','女',24,sysdate,'100005','xiaogang1@qq.com');


--外鍵級聯(lián)刪除

--刪除主表中的某些數(shù)據(jù)

delete?from?myclazz?where?cno=100003

--在刪除主表字段的時候，從表中的相關(guān)的數(shù)據(jù)的解決方案：

---提供了3種解決方法：

--1：restrict ?受限制的，默認(rèn)的解決方案。不讓刪除。

--2：cascade ?級聯(lián)刪除，串聯(lián)刪除。??作用：如果主表中某些數(shù)據(jù)刪除，那么從表中相關(guān)的數(shù)據(jù)一并被刪除掉。

--3：set null ??將從表中相關(guān)的字段設(shè)置為null.

drop?table?student?purge;

create?table?student(

???????sno?number(6),

???????sname?varchar2(12)?constraints?nn_student_sname?not?null,

???????gender?varchar2(3)?default?'男'?check?(gender?in?('男'?,?'女')),--列級別的檢查約束

???????age?number(3),

???????sdate?date,

???????cno?varchar(6),-- references myclazz (cno),--列級定義外鍵約束

???????email?varchar2(30),

???????constraints?PK_STUDENT?primary?key?(sno),--表級設(shè)置主鍵約束

???????constraints?uk_student_email?unique?(email),--表級別設(shè)置唯一約束

???????constraints?ck_student_age?check?(age?between?18?and?24),--表級別的檢查約束

???????--constraints fk_student_cno foreign key (cno) references myclazz (cno) on delete cascade--表級別設(shè)置外鍵約束,并設(shè)置串聯(lián)刪除

???????constraints?fk_student_cno?foreign?key?(cno)?references?myclazz?(cno)?on?delete?set?null?--?設(shè)置級聯(lián)刪除為??set null

);

insert?into?student??values(100001,'小剛','女',24,sysdate,'100001','xiaogang2@qq.com');

insert?into?student??values(100002,'小剛','女',24,sysdate,'100001','xiaogang1@qq.com');

insert?into?student??values(100003,'小剛','女',24,sysdate,'100002','xiaogang3@qq.com');

insert?into?student??values(100004,'小剛','女',24,sysdate,'100002','xiaogang4@qq.com');

insert?into?student??values(100005,'小剛','女',24,sysdate,'100003','xiaogang5@qq.com');


delete?from?myclazz?where?cno=100001


--刪除myclazz?主表，即使從表中沒有一條記錄，那么也不能直接刪除被外鍵引用的主表。

---不能刪除

drop?table?myclazz

--只能強制刪除,把和當(dāng)前表的相關(guān)的約束一并刪除掉。

drop?table?myclazz?cascade?constraints


---創(chuàng)建表之后，添加約束

--創(chuàng)建student?表，創(chuàng)建表的過程中，不添加任何的約束

create?table?student(

???????sno?number(6),

???????sname?varchar2(12)?constraints?nn_student_sname?not?null,---非空約束只能是列級的，不能在創(chuàng)建表之后再添加非空約束。

???????gender?varchar2(3)?default?'男'?,

???????age?number(3),

???????sdate?date,

???????cno?varchar(6),

???????email?varchar2(30)

);


drop?table?student

--給相應(yīng)的字段添加約束

alter?table?student?add?constraints?pk_student?primary?key?(sno);

alter?table?student?add?constraints?uk_student_email?unique?(email);

alter?table?student?add?constraints?ck_student_gender?check?(gender?in?('男'?,?'女'));

alter?table?student?add?constraints?ck_student_age?check?(age?between?18?and?24);

alter?table?student?add?constraints?fk_student_cno?foreign?key?(cno)?references?myclazz?(cno)?on?delete?cascade;


insert?into?student??values(100002,'小剛','女',24,sysdate,'100001','xiaogang1@qq.com');


--刪除約束

alter?table?student?drop?constraints?ck_student_gender;


（四）序列-索引

--序列??Sequence ?是oralce?數(shù)據(jù)庫專有的數(shù)據(jù)庫對象。

--作用：用于某些有規(guī)律的逐漸遞增的字段的值的生成。

--創(chuàng)建序列

create?sequence?seq_student;


--訪問序列的值

--必須先訪問?序列的nextval?才能訪問?currval.

select?seq_student.nextval?from?dual--查詢序列的下一個的值，每次查詢序列的下一個的值，序列都會自動增長?序列中定義的增量的值。

select?seq_student.currval?from?dual--查詢序列當(dāng)前的值。


insert?into?student??values(seq_student.nextval,'小剛','女',24,sysdate,'100001','xiaogang5@qq.com');


--?通過sql?去指定序列的屬性

create?sequence?seq_stu

increment?by?5?--增量

start?with?666?--開始數(shù)

maxvalue?99999999---|nomaxvalue 10^27 or -1

minvalue?666--|no minvalue

cycle---|nocycle--是否循環(huán)

nocache;--cache n|--是否緩存


select?seq_stu.nextval?from?dual


insert?into?student??values(seq_stu.nextval,'小剛','女',24,sysdate,'100001','xiaogang7@qq.com');


select?*?from?student


--刪除序列

drop?sequence?seq_stu.


----索引

--創(chuàng)建索引有兩種方式

--1：自動創(chuàng)建，一個表中的primary key?和unique?的列，都被數(shù)據(jù)庫默認(rèn)的創(chuàng)建了索引。

--2：手動創(chuàng)建?create index...

--給指定的表的字段添加索引

select?*?from?student

--測試根據(jù)名字來查找內(nèi)容，還沒有添加索引

select?*?from?student?where?sname='小明3'


--給sname?添加索引??需要指明給哪個字段添加索引

create?index?index_student_sname?on?student?(sname);


--索引一旦創(chuàng)建，自動使用。


--刪除索引

drop?index?index_student_sname;


--希望通過查詢sname?的內(nèi)容是降序的

create?index?index_student_sname?on?student?(sname?desc);

--降序輸出結(jié)果

select?sname?from?student


索引：

開發(fā)中使用索引的要點：

1.索引數(shù)據(jù)可能要占用大量的存儲空間。

2.索引改善檢索操作的性能，但降低數(shù)據(jù)插入、修改和刪除的性能。在執(zhí)行這些操作時，DBMS必須動態(tài)地更新索引。

3.限制表中索引的數(shù)目。索引越多，在修改表時對索引做出修改的工作量越大

4.并非所有數(shù)據(jù)都適合于索引。唯一性不好的數(shù)據(jù)（如?。乃饕玫降暮锰幉槐染哂懈嗫赡苤档臄?shù)據(jù)（如姓名）從索引得到的好處多

5.索引用于數(shù)據(jù)過濾和數(shù)據(jù)排序。如果你經(jīng)常以某種特定的順序排序數(shù)據(jù)，則該數(shù)據(jù)可能是索引的備選。

6.可以在索引中定義多個列（如省加城市），這樣的索引只在以省加城市的順序排序時有用。如果想按城市排序，則這種索引沒有用處。


?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 觀其大綱 page 01 基礎(chǔ)知識 1 MySQL數(shù)據(jù)庫概要 2 簡單MySQL環(huán)境 3 數(shù)據(jù)的存儲和獲取 4 M...
    周少言閱讀 3,250評論 0贊 33
  • 最近打算采用關(guān)系型數(shù)據(jù)庫來理一下公司的運營數(shù)據(jù)，先拿點東西練手找感覺。下面是幾個關(guān)于學(xué)生課業(yè)的表，需要建立一個數(shù)據(jù)...
    九天朱雀閱讀 1,045評論 0贊 3
  • 數(shù)據(jù)字典 數(shù)據(jù)庫系統(tǒng)中存放三層結(jié)構(gòu)定義的數(shù)據(jù)庫稱為數(shù)據(jù)字典（DD），對數(shù)據(jù)庫的操作都要通過DD才能實現(xiàn)。DD系統(tǒng)中...
    panda_say閱讀 1,195評論 0贊 6
  • 1）.創(chuàng)建數(shù)據(jù)庫 create database學(xué)生選課數(shù)據(jù)庫 2）.創(chuàng)建四張表 Create table Stu...
    blvftigd閱讀 1,683評論 0贊 0
  • 人生旅途中 有許多 路過風(fēng)景 走走停停 一邊花開 一邊落寞 走過喧囂 又歸于寧靜 看過多少繁華 又知多少蒼涼 手心...
    八度黑白閱讀 355評論 9贊 7

友情鏈接更多精彩內(nèi)容