Oracle学习笔记（四）

约束条件

主键:primary key, PK = unique + not null

外键:foreign key, FK

唯一:unique

非空:not null

检查:check

--建表

create table student(

id number primary key,

name varchar2(20),

email varchar2(40),

registtime date default sysdate);

--删除一个表

drop table student

--新增记录

insert into Student(id,name,email,registtime)

values(1,'jakcy','[e-mail]',sysdate)

insert into Student(id,name,email)

values(2,'jakcy','[e-mail]')

insert into Student(id,name,email,registtime)

values(3,'jakcy','[e-mail]',to_date('2015-10-01','yyyy-mm-dd'))

--如果新增全部字段,可以省略字段名称 --不推荐

--建议写出完整的字段列表

insert into Student

values(4,'jakcy','[e-mail]',to_date('2015-10-02','yyyy-mm-dd'))

--一次增加多条记录

--使用子查询代替values

insert into student(id,name)

select empno,ename from emp

-- 删除

delete student where id=739

select * from student

delete student where id>1000

--修改

update student set name='jacky1' where id=1

update student set name=name||id where id<>1

总结：

DDL: create

DML: insert/ delete /update

--创建约束条件

--如果创建的约束条件没有命名,由系统自动命名的.SYS_Cn.

--1.创建主键约束:

--1)建表时创建,可以建立在列级或表级.

--2)在建表后创建,建立在表级.

例1. --以student为例

--创建主键约束,不命名,由系统命名,建立在列级.

create table student(

id number primary key,

name varchar2(20),

email varchar2(40),

registtime date default sysdate);

drop table student

例2.

--创建主键约束,自己命名,建立在表级

--约束条件命名为:stuning1_id_pk

CREATE TABLE student1(

id NUMBER,

name VARCHAR2(20),

CONSTRAINT stuning1_id_pk PRIMARY KEY(id));

例3.-- 一些建模工具常用的方法 power builder, visio,...

--建表后创建主键约束

--约束条件名称自定义(stuning2_id_pk),最好有意义.

create table student2(

id number,

name varchar2(20)

);

alter table student2

add constraint stuning2_id_pk primary key (id);

--注意 primary key (id1,id2);

--查看主键约束条件 --了解

select constraint_name, constraint_type

from user_constraints

where table_name='student2';

1.主键约束 primary key

作用：

1）唯一性，反止重复

2）防止为空

--不提供主键值,将返回"id不能为空"错误

select * from student

insert into student(id,name) values(1,'aaa') --error

insert into student(name) values('aaa') --error

重点：

任何时候，一个表要有主键。

2.创建非空约束:只能建立在列级 unique

create table student1(

id number primary key,

name varchar2(20) not null);

select constraint_name, constraint_type

from user_constraints

where table_name='student1';

--要求此列上必须有值.错误的sql:

insert into student1(id) values(1);

3.创建唯一约束

--email的唯一约束建立在列级

--nickname的唯一约束建立在表级

create table student1(

id number primary key,

name varchar2(20),

nickname varchar2(20),

email char(30) unique,

constraint stu_ning1_nickname_uk1 unique(nickname));

--唯一约束允许为空,但不允许重复.

原因：pk一个表上只能有一个；对于其它不能重复的字段，只能选择unique

用于：1. email,但email不是主键时; 2.手机号

4.check约束

create table student2(

id number primary key,

name varchar2(20),

sex char,

constraint stuning2_sex_ck check (sex in ('M','F'))); --female,male

--正确的例子:

insert into student2 values(1234,'peter','M');

--错误的例子:

insert into student2 values(1235,'chris','A');

5. 外键 --重点

--主表(父表) 被参照的表

create table major(

mid number(2) primary key,

mname varchar2(30)

);

select * from major

--从表(子表) 参照别的表

create table student(

id number(5) primary key,

name varchar2(20),

mid number(2),

constraint stuning_mid_fk foreign key (mid) references major(mid)

);

delete major

insert into major

values(1, 'computer');

insert into major

values(2, 'music');

insert into major

values(3, 'movie');

insert into student

values(101,'peter',1);

insert into student

values(102,'chris',3);

insert into student

values(103,'king', 2);

select *

from student s join major m

on s.mid = m.mid

and s.name = 'peter';

--试图增加一个学生记录,指定一个不存在的专业编码10

select * from student

select * from major

insert into student

values(110,'dawson',10);

--报错:未找到父项关键字

--表示:在父表中没有编码为10的专业

--试图删除major表中的1专业,这个专业被某些学生选修(被参照),此时删除不成功,

delete from major where mid = 1;

--报错:已找到子记录

主表：major，因为mid为pk，一个专业的记录为一条；

从表：student,因为每一个mid，在student可以有0条，1条或多条记录对应

insert into student values(119,'aaa',null) --还没有分配专业

update student set mid=1 --分配专业

select * from student