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','jacky@qq.com',sysdate)

insert into Student(id,name,email) 
values(2,'jakcy','jacky@qq.com')

insert into Student(id,name,email,registtime) 
values(3,'jakcy','jacky@qq.com',to_date('2015-10-01','yyyy-mm-dd'))


--如果新增全部字段,可以省略字段名称 --不推荐
--建议写出完整的字段列表
insert into Student 
values(4,'jakcy','jacky@qq.com',to_date('2015-10-02','yyyy-mm-dd'))

--一次增加多条记录
--使用子查询代替values


insert into student(id,name)
select empno,ename from emp

-- 删除

delete student where id=7369

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