回憶
ORACLE安裝 和 刪除
啟動(dòng)和關(guān)閉
SQL*PLUS windows下窗口版本
超級(jí)用戶 system/manager
sys/change_on_install
普通用戶
scott/tiger
DOS下的SQLPLUS版本
如何使用
命令show user
SQL語句
難點(diǎn)
約束 如何給表加約束？？？
SQL*PLUS環(huán)境命令
結(jié)束時(shí)可以寫; 也可以不寫
desc dept
show user
connect scott/tiger
set linesize 200
set pagesize 100
spool c:\aa.txt
SQL語句
不需要分號(hào)結(jié)束的命令,sqlplus環(huán)境命令
show user
connect scott/tiger
desc dept
set linesize 200
set pagesize 100
SQL語句結(jié)束時(shí)候一定要 有分號(hào)或者換行加/
《1》DDL語句(數(shù)據(jù)定義語言) Data Define Language
create
alter
drop
truncate 開頭的語句 truncate table <表名>
特點(diǎn):<1>建立和修改數(shù)據(jù)對(duì)象
<2>建立和修改直接存入庫(kù)中,直接生效
建立表
create table class(--班級(jí)表
classid number(2) primary key,
cname varchar2(20) not null);
/*wdsadsadsad
asdsadsadsadsad
多行注釋
*/
create table student( --學(xué)生表
xh number(4) primary key, --學(xué)號(hào)
name varchar2(10) not null, --姓名
sex char(2) check (sex in ('男','女')),--性別
birthday date,--生日
sal number(7,2), --獎(jiǎng)學(xué)金
classid number(2) references class(classid) --班級(jí)
);
外鍵引用的列一定是主鍵或有unique約束的列
alter table student add (shengfenzheng number(18));
drop table student; 刪除結(jié)構(gòu)
delete from student; 只刪除數(shù)據(jù)，速度慢，數(shù)據(jù)可以恢復(fù)
truncate table student; 刪除記錄的 速度快 數(shù)據(jù)不能恢復(fù)
《2》 DML語句(數(shù)據(jù)操作語言) Data Manupilate Language
select
insert
delete
update
特點(diǎn):<1>對(duì)數(shù)據(jù)起作用的
<2> 這些語句的修改是在內(nèi)存中發(fā)生的
要想改動(dòng)存入庫(kù)中必須要commit語句
insert into student(xh,name,sex)
values (&xh,'&n','&sd');
insert into student(xh,name,sex)
values (&學(xué)號(hào),'&姓名','&性別');
轉(zhuǎn)義字符 \
打開轉(zhuǎn)義 set escape on
insert into dept
values (90,'JO&HI','北京');
insert into dept
values (92,'JO\&HI','大家');
《3》 TCL(事務(wù)控制語句) Transaction Control Language
commit; 提交 修改保存到數(shù)據(jù)庫(kù)中
rollback; 回滾 取消內(nèi)存中的改動(dòng)
savepoint;保存點(diǎn) 分解事務(wù)的 把事務(wù)變小
DDL語句 會(huì)自動(dòng)提交以前未提交的事務(wù)
關(guān)閉SQLplus工具 也會(huì)自動(dòng)提交未提交的事務(wù)的
事務(wù) -- 就是一個(gè)完整的對(duì)數(shù)據(jù)的DML操作
所有事務(wù) 都是要明確的提交和回滾的
--轉(zhuǎn)賬
update 賬目表
set 錢=錢-500
where 帳號(hào)='A';
update 賬目表
set 錢=錢+500
where 帳號(hào)='B';
commit;
事務(wù)何時(shí)存在 DML語句中除select以外都會(huì)有事務(wù)
《《《《《《《注意》》》》》 / 重復(fù)運(yùn)行上一條SQL語句
commit; 結(jié)束上一個(gè)事務(wù) 并且開始一個(gè)新的事務(wù)
update student set sal = null where xh =1000;
savepoint c111;
insert into student(xh,name,sex) values (1004,'MIKE','男');
rollback to c111; --撤銷了插入的數(shù)據(jù)
rollback; --從c111這個(gè)點(diǎn)回滾到事務(wù)的開始點(diǎn)
《SQLPLUS規(guī)則》
a)DML語句后跟上DDL語句 DML語句的事務(wù)會(huì)被自動(dòng)提交
b)exit/quit命令 退出 SQLPLUS環(huán)境時(shí)也會(huì)自動(dòng)提交事務(wù)
點(diǎn)小叉子關(guān)閉sqlplus窗口 事務(wù)都自動(dòng)回滾了
c)非法操作是不能提交事務(wù)的 ，只能導(dǎo)致事務(wù)回滾
《4》 DCL語句(數(shù)據(jù)控制語句) Data Control Language grant 授予權(quán)限
revoke 撤銷權(quán)限
權(quán)限 select ,insert,delete,update
all (select ,insert,delete,update 總和)
角色 connect （登陸數(shù)據(jù)庫(kù)）,resource(建立表和對(duì)象)
如何建一個(gè)自己的用戶？
必須是超級(jí)用戶才能建用戶
--連接到超級(jí)用戶
connect system/manager
--建立用戶名zhangsan 密碼m123
create user zhangsan identified by m123;
--授予必要的權(quán)限connect 你能夠連接
resource 你能建表不受空間的限制,建立對(duì)象
grant connect,resource to zhangsan;
--這個(gè)普通用戶就建好了 和scott用戶的權(quán)限是一樣的
grant DBA to zhangsan; --張三的權(quán)限和System一樣
--改張三的密碼
<<1>> 自己改自己的密碼
connect zhangsan/m123
密碼改為了mm1
alter user zhangsan identified by mm1;
<<2>> 超級(jí)用戶來改
connect system/manager
alter user zhangsan identified by mm1;
在scott/tiger這個(gè)用戶下
grant select on dept to zhangsan;
在zhangsan下 可以使用select * from scott.dept;
看到結(jié)果
在scott/tiger這個(gè)用戶下
revoke select on dept from zhangsan;撤銷授權(quán)
在zhangsan下 可以使用select * from scott.dept;
看不到結(jié)果
約束
主鍵約束 -- 每個(gè)表要有主鍵，唯一的標(biāo)識(shí)一行數(shù)據(jù)
非空約束
唯一性約束
外鍵約束
檢查約束
腳本(SCRIPT)
create table cla( --班級(jí)表
id number(2) primary key, --班級(jí)編號(hào)
cname varchar2(20) not null --班級(jí)名字
);
create table stu( --學(xué)生表
xh number(4) primary key, --學(xué)號(hào)是主鍵
xm varchar2(20) not null, --姓名非空
age number(2) check (age between 10 and 90),--年齡在10到90之間(10<= age <=90 )
birthday date,
shenfenzheng number(18) unique, --身份證唯一
classid number(2) references cla(id) -- 班級(jí)編號(hào)外鍵
--(引用的一定是主鍵或唯一性約束的字段)
);
<1>建立表的同時(shí)使用約束
create table student( --學(xué)生表
xh number(4) primary key, --學(xué)號(hào)主鍵
xm varchar2(10) not null, --姓名不能為空
sex char(2) check (sex in ('男','女')), --性別
birthday date unique, --日期
sal number(7,2) check (sal between 500 and 1000),--獎(jiǎng)學(xué)金 sal >=500 and sal <=1000
classid number(2) references cla(id)
); --必須要先有cla表才對(duì)
--一定先建立班級(jí)cla表
主鍵約束 primary key
not null
check
unique 唯一約束
create table student( --學(xué)生表
xh number(4) constraint pk_stu primary key, --學(xué)號(hào)主鍵
xm varchar2(10) constraint nn_stu not null, --姓名不能為空
sex char(2) constraint ck_stu_sex check (sex in ('男','女')), --性別
birthday date constraint uq_bir unique, --日期
sal number(7,2) constraint ck_sal check (sal between 500 and 1000)--獎(jiǎng)學(xué)金 sal >=500 and sal <=1000
);
<2>建立約束的同時(shí)給約束指定名字，便于刪除
create table cla( --班級(jí)表
id number(2) constraint pk_cla primary key, --班級(jí)編號(hào)
cname varchar2(20) constraint nn_cla not null --班級(jí)名字
);
create table stu( --學(xué)生表
xh number(4) constraint pk_stu primary key, --學(xué)號(hào)是主鍵
xm varchar2(20) constraint nn_stu not null, --姓名非空
age number(2) constraint ck_stu check (age between 10 and 90),--年齡在10到90之間(10<= age <=90 )
birthday date,
shenfenzheng number(18) constraint uq_stu unique, --身份證唯一
classid number(2) constraint fk_stu references cla(id) -- 班級(jí)編號(hào)外鍵
--(引用的一定是另外表的主鍵或唯一性約束的字段)
);
<3>建完表后加約束
學(xué)生表student
create table student( --學(xué)生表
xh number(4), --學(xué)號(hào)
xm varchar2(10), --姓名
sex char(2), --性別
birthday date, --日期
sal number(7,2) --獎(jiǎng)學(xué)金
);
加約束
加主鍵
alter table student add constraint pk_stu
primary key (xh);
加非空
alter table student modify (xm not null);
檢查約束
alter table student add check(sex in ('男','女'));
alter table student add constraint ck_sal check(sal between 500 and 1000));
給student加班級(jí)字段
alter table student add (classid number(2));
班級(jí)表cla
create table cla( --班級(jí)表
id number(2), --班級(jí)編號(hào)
cname varchar2(20) --班級(jí)名字
);
添加 主鍵
alter table cla add constraint pk_cla
primary key (id);
加 not null
alter table cla modify
(cname not null);
學(xué)生表student
create table student( --學(xué)生表
xh number(4) ,
xm varchar2(20) , --姓名非空
age number(2),--年齡在10到90之間(10<= age <=90 )
birthday date,
shenfenzheng number(18), --身份證唯一
classid number(2) -- 班級(jí)編號(hào)外鍵
--(引用的一定是另外表的主鍵或唯一性約束的字段)
);
加外鍵約束
alter table student add constraint fk_stu
foreign key (classid) references cla(id);
加主鍵
alter table student add constraint pk_stu
primary key (xh);
加not null
alter table student modify(xm not null);
加檢查約束
alter table student add constraint cc_age
check (age >= 10 and age <=90);
加唯一約束
alter table student add constraint
uq_sfz unique(shenfenzheng);
加外鍵約束
alter table student add constraint
fk_stu foreign key (classid)
references cla(id);
如何刪除約束
alter table student drop constraint
fk_stu;
可以用一個(gè)統(tǒng)一的格式來刪除
alter table 表名 drop constraint 約束名
<4>如何查看約束?? 約束一定加在表上
一個(gè)表上到底有哪些約束???
select constraint_name,constraint_type
from user_constraints
where table_name = 'STUDENT'
--查看表上有什么約束
select * from user_constraints;
--查看約束作用在什么字段上
select * from user_cons_columns
where CONSTRAINT_NAME='PK_STU';
user_constraints數(shù)據(jù)字典表
<5>約束是如何起作用的??
create table cla( --班級(jí)表
id number(2) constraint pk_cla primary key, --班級(jí)編號(hào)
cname varchar2(20) constraint nn_cla not null --班級(jí)名字
);
create table stu( --學(xué)生表
xh number(4) constraint pk_stu primary key, --學(xué)號(hào)是主鍵
xm varchar2(20) constraint nn_stu not null, --姓名非空
age number(2) constraint ck_stu check (age between 10 and 90),--年齡在10到90之間(10<= age <=90 )
birthday date,
shenfenzheng number(18) constraint uq_stu unique, --身份證唯一
classid number(2) constraint fk_stu references cla(id) -- 班級(jí)編號(hào)外鍵
--(引用的一定是另外表的主鍵或唯一性約束unique的字段)
);
主鍵 = 非空 + 唯一
非空
唯一 = 有值的話 值要不同
null的話 都是可以的
外鍵 = 有值 一定要在被引用的表的數(shù)據(jù)中
null的話 是可以的
ANSI SQL92 數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)
Oracle的約束
* 如果某個(gè)約束只作用于單獨(dú)的字段，即可以在字段級(jí)定義約束，也可以在表級(jí)定義約束，但如果某個(gè)約束作用于多個(gè)字段，
必須在表級(jí)定義約束
* 在定義約束時(shí)可以通過CONSTRAINT關(guān)鍵字為約束命名，如果沒有指定，ORACLE將自動(dòng)為約束建立默認(rèn)的名稱
定義primary key約束(單個(gè)字段）
create table employees (empno number(5) primary key,...)
指定約束名
create table employees (empno number(5) constraint emp_pk primary key,...)
定義primary key約束(多個(gè)字段,在表級(jí)定義約束）
create table employees
(empno number(5),
deptno number(3) not null,
constraint emp_pk primary key(empno,deptno)
using index tablespace indx
storage (initial 64K
next 64K
)
)
ORACLE自動(dòng)會(huì)為具有PRIMARY KEY約束的字段(主碼字段)建立一個(gè)唯一索引和一個(gè)NOT NULL約束,定義PRIMARY KEY約束時(shí)可以為它的索引
指定存儲(chǔ)位置和存儲(chǔ)參數(shù)
alter table employees add primary key (empno)
alter table employees add constraint emp_pk primary key (empno)
alter table employees add constraint emp_pk primary key (empno,deptno)
not null約束(只能在字段級(jí)定義NOT NULL約束,在同一個(gè)表中可以定義多個(gè)NOT NULL約束)
alter table employees modify deptno not null/null
unique約束
create table employees
( empno number(5),
ename varchar2(15),
phone varchar2(15),
email varchar2(30) unique,
deptno number(3) not null,
constraint emp_ename_phone_uk unique (ename,phone)
)
alter table employees
add constraint emp_uk unique(ename,phone)
using index tablespace indx
定義了UNIQUE約束的字段中不能包含重復(fù)值，可以為一個(gè)或多個(gè)字段定義UNIQUE約束,因此，UNIQUE即可以在字段級(jí)也可以在表級(jí)定義，
在UNIQUED約束的字段上可以包含空值.
foreign key約束
* 定義為FOREIGN KEY約束的字段中只能包含相應(yīng)的其它表中的引用碼字段的值或者NULL值
* 可以為一個(gè)或者多個(gè)字段的組合定義FOREIGN KEY約束
* 定義了FOREIGN KEY約束的外部碼字段和相應(yīng)的引用碼字段可以存在于同一個(gè)表中，這種情況稱為"自引用"
* 對(duì)同一個(gè)字段可以同時(shí)定義FOREIGN KEY約束和NOT NULL約束
定義了FOREIGN KEY約束的字段稱為"外部碼字段",被FORGIEN KEY約束引用的字段稱為"引用碼字段",引用碼必須是主碼或唯一碼,包含外部碼的表稱為子表，
包含引用碼的表稱為父表.
A:
create table employees
(.....,
deptno number(3) NOT NULL,
constraint emp_deptno_fk foreign key (deptno)
references dept (deptno)
)
如果子表中的外部碼與主表中的引用碼具有相同的名稱，可以寫成:
B:
create table employees
(.....,
deptno number(3) NOT NULL
constraint emp_deptno_fk references dept
)
注意：
上面的例子(B)中not null后面沒有加逗號(hào),因?yàn)檫@一句的contraint是跟在那一列deptno后面的，屬于列定義，所以都無需指明列。而A例中的是表定義，需要指明那一列，所以要加逗號(hào)，不能在列后面定義，還可以寫成：
create table employees
(empno char(4),
deptno char(2) not null constraint emp_deptno_fk references dept,
ename varchar2(10)
)
表定義contraint的只能寫在最后，再看兩個(gè)例子：
create table employees
(empno number(5),
ename varchar2(10),
deptno char(2) not null constraint emp_deptno_fk references dept,
constraint emp_pk primary key(empno,ename)
)
create table employees
( empno number(5),
ename varchar2(15),
phone varchar2(15),
email varchar2(30) unique,
deptno number(3) not null,
constraint emp_pk primary key(empno,ename),
constraint emp_phone_uk unique (phone)
)
添加foreign key約束(多字段／表級(jí)）
alter table employees
add constraint emp_jobs_fk foreign key (job,deptno)
references jobs (jobid,deptno)
on delete cascade
更改foreign key約束定義的引用行為(delete cascade/delete set null/delete no action),默認(rèn)是delete on action
引用行為(當(dāng)主表中一條記錄被刪除時(shí)，確定如何處理字表中的外部碼字段）：
delete cascade : 刪除子表中所有的相關(guān)記錄
delete set null : 將所有相關(guān)記錄的外部碼字段值設(shè)置為NULL
delete no action: 不做任何操作
先刪除原來的外鍵約束,再添加約束
ALTER TABLE employees DROP CONSTRAINT emp_deptno_fk;
ALTER TABLE employees ADD CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE;
check約束
* 在CHECK約束的表達(dá)式中必須引用到表中的一個(gè)或多個(gè)字段，并且表達(dá)式的計(jì)算結(jié)果必須是一個(gè)布爾值
* 可以在表級(jí)或字段級(jí)定義
* 對(duì)同一個(gè)字段可以定義多個(gè)CHECK約束，同時(shí)也可以定義NOT NULL約束
create table employees
(sal number(7,2)
constraint emp_sal_ck1 check (sal > 0)
)
alter table employees
add constraint emp_sal_ck2 check (sal < 20000)
刪除約束
alter table dept drop unique (dname,loc) --指定約束的定義內(nèi)容
alter table dept drop constraint dept_dname_loc_uk --指定約束名
刪除約束時(shí)，默認(rèn)將同時(shí)刪除約束所對(duì)應(yīng)的索引，如果要保留索引，用KEEP INDEX關(guān)鍵字
alter table employees drop primary key keep index
如果要?jiǎng)h除的約束正在被其它約束引用，通過ALTER TABLE..DROP語句中指定CASCADE關(guān)鍵字能夠同時(shí)刪除引用它的約束
利用下面的語句在刪除DEPT表中的PRIMARY KEY約束時(shí)，同時(shí)將刪除其它表中引用這個(gè)約束的FOREIGN KEY約束:
alter table dept drop primary key cascade
禁用/激活約束(禁用/激活約束會(huì)引起刪除和重建索引的操作)
alter table employees disable/enable unique email
alter table employees disable/enable constraint emp_ename_pk
alter tabel employees modify constraint emp_pk disable/enable
alter tabel employees modify constraint emp_ename_phone_uk disable/enable
如果有FOREIGN KEY約束正在引用UNIQUE或PRIMARY KEY約束，則無法禁用這些UNIQUE或PRIMARY KEY約束，
這時(shí)可以先禁用FOREIGN KEY約束，然后再禁用UNIQUE或PRIMARY KEY約束；或者可以在ALTER TABLE...DISABLE
語句中指定CASCADE關(guān)鍵字，這樣將在禁用UNIQUE或PRIMARY KEY約束的同時(shí)禁用那些引用它們的FOREIGN KEY約束，如：
alter table employees disable primary key cascade
約束數(shù)據(jù)字典
all_constraints/dba_constraints/user_constraints 約束的基本信息，包括約束的名稱，類型，狀態(tài)
(約束類型：C(CHECK約束),P(主碼約束),R(外部碼約束),U(唯一碼約束))
all_cons_columns/dba/user 約束對(duì)應(yīng)的字段信息
Oracle的索引
索引和對(duì)應(yīng)的表應(yīng)該位于不同的表空間中,oracle能夠并行讀取位于不同硬盤上的數(shù)據(jù)，可以避免產(chǎn)生I/O沖突
B樹索引：在B樹的葉節(jié)點(diǎn)中存儲(chǔ)索引字段的值與ROWID。
唯一索引和不唯一索引都只是針對(duì)B樹索引而言.
Oracle最多允許包含32個(gè)字段的復(fù)合索引
索引創(chuàng)建策略
1.導(dǎo)入數(shù)據(jù)后再創(chuàng)建索引
2.不需要為很小的表創(chuàng)建索引
3.對(duì)于取值范圍很小的字段（比如性別字段）應(yīng)當(dāng)建立位圖索引
4.限制表中的索引的數(shù)目
5.為索引設(shè)置合適的PCTFREE值
6.存儲(chǔ)索引的表空間最好單獨(dú)設(shè)定
創(chuàng)建不唯一索引
create index emp_ename on employees(ename)
tablespace users
storage(......)
pctfree 0;
創(chuàng)建唯一索引
create unique index emp_email on employees(email)
tablespace users;
創(chuàng)建位圖索引
create bitmap index emp_sex on employees(sex)
tablespace users;
創(chuàng)建反序索引
create unique index order_reinx on orders(order_num,order_date)
tablespace users
reverse;
創(chuàng)建函數(shù)索引(函數(shù)索引即可以是普通的B樹索引，也可以是位圖索引)
create index emp_substr_empno
on employees(substr(empno,1,2))
tablespace users;
修改索引存儲(chǔ)參數(shù)(與表類似，INITIAL和MINEXTENTS參數(shù)在索引建立以后不能再改變)
alter index emp_ename storage(pctincrease 50);
由于定義約束時(shí)由oracle自動(dòng)建立的索引通常是不知道名稱的，對(duì)這類索引的修改經(jīng)常是利用alter table ..using index語句進(jìn)行的,而不是alter index語句
利用下面的語句將employees表中primary key約束對(duì)應(yīng)的索引的PCTFREE參數(shù)修改為5
alter table employees enable primary key using index pctfree 5;
清理索引碎片
1.合并索引(只是簡(jiǎn)單的將B樹葉結(jié)點(diǎn)中的存儲(chǔ)碎片合并在一起，并不會(huì)改變索引的物理組織結(jié)構(gòu)）
alter index emp_pk coalesce;
2.重建索引(不僅能夠消除存儲(chǔ)碎片,還可以改變索引的全部存儲(chǔ)參數(shù)設(shè)置，并且可以將索引移動(dòng)到其它的表空間中,重建索引
實(shí)際上就是再指定的表空間中重新建立一個(gè)新的索引,然后刪除原來的索引)
alter index emp_pk rebuild;
刪除索引
drop index emp_ename;
如果索引中包含損壞的數(shù)據(jù)塊，或者包含過多的存儲(chǔ)碎片，需要首先刪除這個(gè)索引，然后再重建它.
如果索引是在創(chuàng)建約束時(shí)由oracle自動(dòng)產(chǎn)生的,可以通過禁用約束或刪除約束的方法來刪除對(duì)應(yīng)的索引.
在刪除一個(gè)表時(shí),oracle會(huì)自動(dòng)刪除所有與該表相關(guān)的索引.
索引數(shù)據(jù)字典
all_indexes/dba_indexes/user_indexes 索引的基本信息
all_ind_columns/dba_ind_columns/user_ind_columns 索引對(duì)應(yīng)的字段信息