欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle day05

程序员文章站 2023-02-26 14:35:10
--constraint--not null 非空约束create table demo01(empno number(4),ename varchar2(10) not null,job varchar2(10))insert into demo01 values(1234,' ','SALES' ......

--constraint
--not null 非空约束
create table demo01(
empno number(4),
ename varchar2(10) not null,
job varchar2(10)
)

insert into demo01 values(1234,' ','sales');
select * from demo01;


--unique 唯一键
create table demo02(
empno number(4) unique,
ename varchar2(10),
job varchar2(8)
)
select * from demo02;
insert into demo02 values(1234,'zhangsan','sales');
insert into demo02 values(1234,'lisi','sales');
insert into demo02 values(null,'wangwu','sales');
insert into demo02 values(null,'zhaoliu','sales');

--非空&唯一
create table demo03(
empno number(4) not null unique,
ename varchar2(10),
job varchar2(8)
)
insert into demo03 values(1234,'zhangsan','sales');
insert into demo03 values(null,'lisi','sales');

--自定义检查约束
create table demo04(
empno number(4),
ename varchar2(10),
job varchar2(8),
age number(3) check(age>0 and age<200),
gender varchar2(3) check(gender='男' or gender='女')
)

insert into demo04 values(1234,'zhangsan','sales',56,'男')
insert into demo04 values(1234,'zhangsan','sales',44,'详')

--创建约束时,为约束起名 constraint
create table demo05(
empno number(4),
ename varchar2(10),
job varchar2(8),
age number(3) constraint ck_age check(age>0 and age<200)
)

insert into demo05 values(1234,'zhangsan','sales',300);


--在添加完列后,还可以添加约束
--除了not null不可以
create table demo06(
empno number(4),
ename varchar2(10),
age number(3),
constraint uq_empno unique(empno),
constraint ck_age2 check(age>0 and age<200)
)

insert into demo06 values(1234,'zhangsan');
insert into demo06 values(1234,'lisi');


--主键约束
--为了保证该列的数据能够证明行记录在表中是唯一的
--主键约束从形式看,类同于(非空和唯一)约束
create table demo07(
empno number(4) primary key,
ename varchar2(10),
age number(3)
)

insert into demo07 values(1234,'zhangsan',18);
insert into demo07 values(1234,'lisi',18);
insert into demo07 values(null,'wangwu',20);

--在列之后添加
create table demo08(
empno number(4),
ename varchar2(10),
age number(3),
constraint pk_empno primary key(empno)
)

-- 一张表只有一个主键
create table demo09(
empno number(4) primary key,
ename varchar2(10) primary key,
age number(3)
--constraint pk_empno primary key(empno)
)

-- 一张表虽然只有一个主键,但可以选择多个列,作为联合主键
create table demo09(
empno number(4) ,
ename varchar2(10),
age number(3),
constraint pk_empno2 primary key(empno,ename)
)

insert into demo09 values(1234,'zhangsan',18);
insert into demo09 values(1234,'lisi',20);
insert into demo09 values(2345,'zhangsan',20);

insert into demo09 values(1234,null,18);

--外键foreign key
--外键用来描述两张表之间的关系,必须保证外键列的值必须为参考列的值中的一个
create table t_class(
classid number(2) primary key,
classname varchar2(20) not null,
classnum varchar2(30)
)

insert into t_class values(10, 'java班','教室6');
insert into t_class values(20, '大数据班','教室3');
insert into t_class values(30, 'python班','教室4');

delete from t_class where classid = 20;

create table t_student(
stuid number(8) primary key,
stuname varchar2(30) not null,
stuphone varchar2(11),
classid varchar2(2) references t_class(classnum)
)

insert into t_student values(11111111,'方子恒',110,10);
insert into t_student values(11111112,'马子明',120,20);
insert into t_student values(11111113,'王福禄',130,10);

select * from t_class;
select * from t_student;

--存在外键的表为从表,被依赖的表为主表
--外键不一定为主表的主键,也可以为主表的唯一键


--约束除了在建表时,可以添加约束外,还可以修改表时修改约束
alter table demo07 add constraint uq_demo07_ename unique(ename);

alter table demo07 drop constraint uq_demo07_ename

--索引:
create index idx_emp_ename_job on emp(ename,job)


select * from emp where ename = 'smith' and job= 'xxx';

--只有添加索引的列,通过该列进行查询时,速度才可以变快



create table demo110(
id number(5) primary key,
name varchar2(20) not null,
nickname varchar2(30) unique
)