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