回忆
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 索引对应的字段信息