Oracle 笔记 day05
Oracle day5
20120220
========================
一.约束条件
数据必须遵循的规则或限制.
primary key 主键 PK
foreign key 外键 FK
not null 非空 NN
unique 唯一 UK
check 检查 CK
--建表时,建立主键约束条件
--id列受限,
create table student_ning(
id number(4) primary key,
name char(10)
);
--1.ok
insert into student_ning
values(1, 'peter');
--2.ok
insert into student_ning
values(2, 'tom');
--3.错 ORA-00001: 唯一约束被违反,主键重复
--constraint: 约束
insert into student_ning
values(2, 'jerry');
--4.错, 主键列不允许有空值
insert into student_ning(name)
values('zhangsan');
某些数据库: 主键自增长
mysql / sql server
oracle : 主键发生器: 序列
drop table student_ning;
--建表时,设置name列为Not Null约束
create table student_ning(
id number primary key,
name char(10) not null);
--1.ok,插入记录
insert into student_ning values(1,'peter');
--2.错,name不能插入null值
insert into student_ning(id) values(2);
--insert into student_ning values(2, null);
drop table student_ning;
--建表时,给email列创建唯一约束
create table student_ning(
id number(4) primary key,
name char(10) not null,
email char(20) unique
);
--1.ok
insert into student_ning
values(1, 'zhangsan','zs@163.com');
--2.错,email相同, 违反唯一约束, ORA-00001
insert into student_ning
values(2, 'zhangsanf', 'zs@163.com')
drop table student_ning;
--gender:性别, 只允许:'M', 'F'
create table student_ning(
id number(4) primary key,
name char(10) not null,
email char(20) unique,
gender char(1) check (gender in ('F','M'))
);
--1.ok
insert into student_ning
values(1,'zhangsan', 'zs@163.com','M');
--2.
insert into student_ning
values(2,'zhangsanf', 'zsf@163.com','X');
--查询约束条件
select constraint_name,
constraint_type
from user_constraints
where table_name = 'STUDENT_NING';
--命名规则:表名_列名_约束条件类型
--列级约束
create table student_ning1(
id number(4)
constraint stu_n_id_pk primary key,
name char(10)
constraint stu_n_name_nn not null,
email char(20)
constraint stu_n_email_uk unique,
gender char(1)
constraint stu_n_gender_ck
check (gender in ('F','M'))
);
--表级约束
--非空,只能定义在列级.其他约束可以定义在表级create table student_ning2(
id number(4),
name char(10)
constraint stu_n2_name_nn not null,
email char(20),
gender char(1),
constraint stu_n2_id_pk primary key (id),
constraint stu_n2_email_uk unique (email),
constraint stu_n2_gender_ck
check (gender in ('F','M'))
);
--建表,除了非空以外的约束,全部放在建表后再建
create table student_ning3(
id number(4),
name char(10) not null,
email char(20),
gender char(1)
);
alter table student_ning3 add
constraint stu_n3_id_pk primary key (id);
alter table student_ning3 add
constraint stu_n3_email_uk unique (email);
alter table student_ning3 add
constraint stu_n3_gender_ck
check (gender in ('F','M'));
select constraint_name, constraint_type
from user_constraints
where table_name = 'STUDENT_NING3';
--专业表
create table major_ning(
id number(2) primary key,
name char(20) not null);
insert into major_ning values(1, 'java');
insert into major_ning values(2, 'oracle');
insert into major_ning values(3, 'c++');
insert into major_ning values(4, 'android');
commit;
select * from major_ning;
create table student_ning4(
id number(4),
name char(10) not null,
mid number(2));
alter table student_ning4 add
constraint stu_n4_id_pk primary key (id);
alter table student_ning4 add
constraint stu_n4_mid_fk foreign key (mid)
references major_ning(id);
insert into student_ning4
values(1, 'peter', 1);
insert into student_ning4
values(2, 'tom', 3);
--错:parent key not found, 父表中没有9这个id
insert into student_ning4
values(3, 'jerry', 9);
--错:parent key not found
update student_ning4
set mid = 8 where id = 1;
--错:child record found
--major_ning表的子表,有参照id=3的记录
delete major_ning where id = 3;
主键 PK = Not Null + Unique
外键 FK : 表间的一对多关系
非空 Not Null
唯一 Unique
检查 Check
create table student(
last_name char(20),
first_name char(20),
score number,
age number(2)
);
alter table student add
constraint stu_ln_fn_pk primary key (last_name, first_name);
alter table student add
constraint stu_age_ck check (age > 17);
last_name first_name
张 三
张 三丰
smith john
smith tom
李 三
--把约束条件重建,加on delete set null短语
alter table student_ning4 drop
constraint stu_n4_mid_fk;
alter table student_ning4 add
constraint stu_n4_mid_fk foreign key (mid)
references major_ning(id)
on delete set null;
--删除主表(major_ning)id=2的记录,成功,把子表中(student_ning)所有专业2的学生,mid列设置为null
delete major_ning where id = 2;
--把约束条件重建,加on delete cascade短语
--cascade: 级联,株连
alter table student_ning4 drop
constraint stu_n4_mid_fk;
alter table student_ning4 add
constraint stu_n4_mid_fk foreign key (mid)
references major_ning(id)
on delete cascade;
--当删除主表(major_ning)的记录时,子表中所有参照该条记录的记录全部被级联删除.
delete major_ning where id = 2
--不复制约束条件.只复制表结构和数据.
--复制结构.
create table student_ning5
as
select * from student_ning4
where 1 = 0;
--给新表student_ning5增加约束条件.
insert into student_ning5
(select * from student_ning4
where mid = 2);
建立约束条件的几种语法:
1.建表时, 列级
--约束条件由系统命名
create table student(
id number primary key,
name char(10) not null
);
--约束条件自定义名字
create table student(
id number constraint stu_id_pk primary key,
name char(10) constriant stu_name_nn not null
);
2.建表时, 表级
create table student(
id number,
name char(10) not null,
constraint stu_id_pk primary key (id)
);
3.建表后,表级
create table student(
id number,
name char(10) not null
);
alter table student add
constraint stu_id_pk primary key(id);
--脚本文件:
-------begin--------
--1.删除所有的外键约束
alter table student_ning drop
constraint stu_mid_fk;
--2.删除所有的表
drop table student_ning;
drop table major_ning;
--3.建表及建立约束.
create table major_ning.....
insert into major_ning values(....);
create table student_ning(....);
alter table student_ning add constraint...
insert into student_ning values(....);
commit;
-------end-------------
二.数据库的其他对象
user_tables
user_objects
select distinct object_type
from user_objects;
table 表
view 视图
index 索引
sequence 序列
PL/SQL程序块:
function 函数
procedure 过程
package 包 (package和package body)
trigger
synonym: 同义词
database link
1.视图 view
--sql(select)语句查询结果的映像
create view v_emp_num
as
select deptno, count(*) num
from emp_ning
where deptno is not null
group by deptno
order by deptno;
select * from v_emp_num;
create or replace view v_emp
as
select empno, ename, job, deptno
from emp_ning;
create or replace view 视图名
as
SQL语句;
select * from v_emp;
--隐藏数据
--简化查询
--视图中不包含数据,只是基表的映像.
create or replace view top_stu
as
select student_name, total_score
from (
select stu.student_name,
sum(per.test_score) total_score
from t_student_ning stu join t_performance_ning per on stu.student_id = per.student_id
where stu.class_id = 1
group by stu.student_name
order by total_score desc)
where rownum < 2;
--创建视图,内容是每个部门的编码,名字,位置和在这个部门工作的员工人数.
select * from v_emp_count;
deptno dname location num
---------------------------------
10 developer beijing 5
20 finance shanghai 4
30.....
create or replace view v_emp_count
as
select d.deptno, d.dname, d.location,
count(e.empno) num
from emp_ning e join dept_ning d
on e.deptno = d.deptno
group by d.deptno, d.dname, d.location;
create or replace view emp_count
as
select deptno, count(*) num
from emp_ning e
group by deptno;
--另一种实现语法:
create or replace view v_emp_count
as
select d.deptno, d.dname, d.location, x.num
from dept_ning d join (
select deptno, count(*) num
from emp_ning e
group by deptno) x
on d.deptno = x.deptno;
2.索引 Index
全表扫描 Full Table Scan (FTS)
select * from student
where name = 'zhangsan';
名字 地址
zhangsan 0xABCD
lisi 0xEEFF
名字 位置
Java编程思想 三层211号架
天龙八部 二层467号架
.... .....
1)如果某个列建立PK约束条件,索引自动建立.
create table student_ning6(
id number primary key,
name char(10)
);
--查找主键约束的名字
select constraint_name
from user_constraints
where table_name = 'STUDENT_NING6';
SYS_C00658255
--索引自动创建, 查找索引名字
select index_name from user_indexes
where table_name = 'STUDENT_NING6';
SYS_C00658255
insert into student_ning6 values(1,'peter');
insert into student_ning6 values(2,'tom');
索引中有如下结构的数据:
id 地址
-------------
1 0XABCD
2 0X2345
--如果按id查找,自动使用索引
select * from student_ning6 where id = 2;
--如果按id以外的列查,不会使用索引.
select * from student_ning6 where name = 'tom';
2)手动创建索引
--在经常做查询的列上手动创建索引
create index idx_stu6_name
on student_ning6(name);
索引中有如下结构的数据:
id 地址
----------------
peter 0XABCD
tom 0X2345
jerry 0X68EF
select * from student_ning6 where name='tom';
索引对查询有帮助,对DML操作是阻碍作用.
索引由Oracle Server自动维护.
drop index idx_stu6_name;
3.序列 Sequence
create sequence seq_ning;
insert into student_ning6
values(seq_ning.nextval, 'peter');
drop sequence seq_ning;
create sequence seq_ning start with 1000
increment by 10;
select seq_ning.nextval from dual;
select seq_ning.currval from dual;
nextval = next value
currval = current value
--Mysql主键自增长
create table student_ning
(id int primary key auto_increment,
name char(10)
);
insert into student_ning(name)
values('peter');
id name
---------------
1 peter
浙公网安备 33010602011771号