文章目录
数据表的创建
约束的名称
外键约束
索引的创建和使用

数据表的创建
DEMO:创建一张保存老师信息的表
DROP TABLE teacher PURGE;
CREATE TABLE teacher(
   tno NUMBER(4),
   tname VARCHAR2(10), 
   tage NUMBER(3),
   tdate DATE);

INSERT INTO teacher  VALUES (1001,'x老师',100,SYSDATE);
INSERT INTO teacher  VALUES (1002,'y老师',100,SYSDATE);
INSERT INTO teacher  VALUES (1003,'z老师',100,SYSDATE);



DEMO:观察代码
DROP TABLE teacher PURGE;
CREATE TABLE teacher (
	tno NUMBER(4),
	tname VARCHAR2(10),
	tage NUMBER(3),
	tdate DATE
);

INSERT INTO teacher VALUES (1001,'x老师',100,SYSDATE);
INSERT INTO teacher VALUES (1002,'y老师',100,SYSDATE);
INSERT INTO teacher VALUES (1003,'z老师',100,SYSDATE);
INSERT INTO teacher (tname,tage,tdate) VALUES ('z老师',100,SYSDATE);

运行结果如下：



数据表必须有一个字段表示主键作为数据的唯一标识，这种字段中的数据不应该为null，

此时在创建数据表的时候可以指定该字段不能为null（给该字段做一个约束）

DEMO:指定字段不能为空
DROP TABLE teacher PURGE;
CREATE TABLE teacher (
	tno NUMBER(4) NOT NULL,
	tname VARCHAR2(10),
	tage NUMBER(3),
	tdate DATE
);

INSERT INTO teacher VALUES (1001,'x老师',100,SYSDATE);
INSERT INTO teacher VALUES (1002,'y老师',100,SYSDATE);
INSERT INTO teacher VALUES (1003,'z老师',100,SYSDATE);
INSERT INTO teacher (tname,tage,tdate) VALUES ('z老师',100,SYSDATE);

运行结果如下：



使用以上的方式就控制了插入数据表中的数据的tno一定不能为null，对tno字段的数据进行了约束，这种约束操作叫作非空约束。

DEMO:继续观察代码
DROP TABLE teacher PURGE;
CREATE TABLE teacher (
    tno NUMBER(4) NOT NULL,
    tname VARCHAR2(10),
    tage NUMBER(3),
    tdate DATE
);

INSERT INTO teacher VALUES (1001,'x老师',100,SYSDATE);
INSERT INTO teacher VALUES (1002,'y老师',100,SYSDATE);
INSERT INTO teacher VALUES (1003,'z老师',100,SYSDATE);
INSERT INTO teacher VALUES (1004,'w老师',100,SYSDATE);
INSERT INTO teacher VALUES (1004,'c老师',100,SYSDATE);

运行结果如下：



主键字段是数据的唯一标识，是不能重复的，可以使用另外一种约束实现避免编号重复的现象。

这种约束就是唯一约束

DEMO:唯一约束
DROP TABLE teacher PURGE;
CREATE TABLE teacher (
    tno NUMBER(4) NOT NULL UNIQUE,
    tname VARCHAR2(10),
    tage NUMBER(3),
    tdate DATE);

INSERT INTO teacher VALUES(1001,'x老师',100,SYSDATE);
INSERT INTO teacher VALUES(1002,'y老师',100,SYSDATE);
INSERT INTO teacher VALUES(1003,'z老师',100,SYSDATE);
INSERT INTO teacher VALUES(1004,'w老师',100,SYSDATE);
INSERT INTO teacher VALUES(1004,'c老师',100,SYSDATE);

运行结果如下：



此时保证插入表中的数据的编号是不可能出现重复的，这种叫作唯一约束，其实还有一种约束可以替代以上两种约束，叫作主键约束。也就是一种约束可以有以上两种约束（唯一约束和非空约束）的效果

DEMO:主键约束
DROP TABLE teacher PURGE;
CREATE TABLE teacher (
    tno NUMBER(4) PRIMARY KEY,
    tname VARCHAR2(10),
    tage NUMBER(3),
    tdate DATE
 );

INSERT INTO teacher VALUES (1001,'x老师',100,SYSDATE);
INSERT INTO teacher VALUES (1002,'y老师',100,SYSDATE);
INSERT INTO teacher VALUES (1003,'z老师',100,SYSDATE);
INSERT INTO teacher VALUES (1004,'w老师',100,SYSDATE);
INSERT INTO teacher VALUES (1004,'c老师',100,SYSDATE);

运行结果如下：



主键约束是非空约束和唯一约束的叠加

总结：

1.非空约束：使用NOT NULL 实现，保证了指定字段插入的值不能为null；
2.唯一约束：使用UNIQUE实现，保证指定字段的数据不能重复；
3.主键约束：使用PRIMARY KEY实现，保证指定字段不能为null也不能重复。

约束的名称

在创建约束指定名称，如果要使用自定约束名称需要使用CONSTRAINT来创建。

DEMO:观察默认的约束名称
DROP TABLE teacher PURGE;
CREATE TABLE teacher (
    tno NUMBER(4) PRIMARY KEY,
    tname VARCHER2(10) UNIQUE,
    tage NUMBER(3),
    tdate DATE);

INSERT INTO teacher VALUES(1001,'x老师',100,SYSDATE);
INSERT INTO teacher VALUES(1002,'y老师',100,SYSDATE);
INSERT INTO teacher VALUES(1003,'z老师',100,SYSDATE);

DEMO:指定自定义的约束名称
DROP TABLE teacher PURGE;
CREATE TABLE teacher (
   tno NUMBER(4),
   tname VARCHAR2(10),
   tage NUMBER(3),
   tdate DATE,
   CONSTRAINT pk_tno PRIMARY KEY(tno),
   CONSTRAINT uk_tname UNIQUE(tname)
);
INSERT INTO teacher VALUES(1001,'x老师',100,SYSDATE);
INSERT INTO teacher VALUES(1002,'y老师',100,SYSDATE);
INSERT INTO teacher VALUES(1003,'z老师',100,SYSDATE);

DEMO:检查约束

检查约束就是可以为字段值进行简单的验证，比如果让年龄的范围是0-250岁，那么可以使用检查约束

DROP TABLE teacher PURGE;
CREATE TABLE teacher (
   tno NUMBER(4),
   tname VARCHAR2(10),
   tage NUMBER(3),
   tdate DATE,
   CONSTRAINT pk_tno PRIMARY KEY(tno),
   CONSTRAINT uk_tname UNIQUE(tname)，
   CONSTRAINT ck_tage CHECK(tage BETWEEN 0 AND 250)
);
INSERT INTO teacher VALUES(1001,'x老师',100,SYSDATE);
INSERT INTO teacher VALUES(1002,'y老师',100,SYSDATE);
INSERT INTO teacher VALUES(1003,'z老师',251,SYSDATE);

运行结果如下：



总结：

1.如果要指定约束的名称，那么需要使用CONSTRAINT引导创建。
2.检查约束在开发中不会去使用的，因为很耗费性能，如果真有这样的需求是交给程序去实现。

外键约束

所谓的外键约束就是一张表中的某个字段的数据来源依赖于另外一张中的某个字段的数据

DEMO:创建数据表
 -- 删除数据表
DROP  TABLE  sc  PURGE;  
DROP  TABLE  course  PURGE;
DROP  TABLE  teacher  PURGE;
DROP  TABLE  student PURGE;
-- 创建数据表
create table student(
   sno  varchar2(10),
   sname varchar2(20),
   sage number(2),
   ssex varchar2(5),
   CONSTRAINT  pk_sno  PRIMARY  KEY(sno)
);
create table teacher(
  tno varchar2(10) ,
  tname varchar2(20),
  CONSTRAINT  pk_tno   PRIMARY KEY(tno)
);
create table course(
  cno varchar2(10),
  cname varchar2(20),
  tno varchar2(20),
  constraint pk_con  primary key (cno)
);
-- 最后创建选课表（有顺序）
create table sc( 
   sno varchar2(10),  
   cno varchar2(10),  
   score number(4,2)
 );
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');        
insert into student values ('s002','李四',23,'男');    
insert into student values ('s003','吴鹏',25,'男');      
insert into student values ('s004','琴沁',20,'女');      
insert into student values ('s005','王丽',20,'女');      
insert into student values ('s006','李波',21,'男');      
insert into student values ('s007','刘玉',21,'男');          
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
/****************** 初始化教师表 ***********************/
insert into teacher values ('t001', '刘阳');      
insert into teacher values ('t002', '谌燕');        
insert into teacher values ('t003', '胡明星');       
commit;);
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');    
insert into course values ('c004','Oracle','t001');     
insert into course values ('c005','SQL SERVER 2005','t003');    
insert into course values ('c006','C#','t003');       
insert into course values ('c007','JavaScript','t002');     
insert into course values ('c008','DIV+CSS','t001');    
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
--  插入选课信息
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);     
insert into sc values ('s003','c001',81.9);       
insert into sc values ('s004','c001',60.9);       
insert into sc values ('s001','c002',82.9);       
insert into sc values ('s002','c002',72.9);       
insert into sc values ('s003','c002',81.9);     
insert into sc values ('s001','c003','59'); 
insert into sc values ('s005','c004','91'); 
insert into sc values ('s007','c008','55.7'); 
commit;

DEMO:观察问题

INSERT INTO sc(sno,cno,score) VALUES ('s100','c1000',90);
DEMO:创建外键约束
DROP TABLE sc PURGE;
CREATE TABLE sc(
	sno VARCHAR2(10),
	cno VARCHAR2(10),
	score NUMBER(4,2),
	CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno),
	CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno)
);

INSERT INTO sc VALUES ('s001','c001',78.9);
INSERT INTO sc VALUES ('s002','c001',80.9);
INSERT INTO sc VALUES ('s10000','c100000',80.9);

运行结果如下：



使用了外键约束保证了不合逻辑的数据不能插入数据表中。

总结：

1.外键约束指的是数据表中的某个字段的数据来源依赖于其他数据表中的某个字段的数据，也就是说当前的数据表中插入的数据必须在其他数据表中有记录。
2.使用了外键约束之后，表之间就存在一定的依赖关系，依赖的数据表（引用其他表中数据的表）叫作子表或者从表，被依赖的数据表叫做父表或者主表。
3.外键约束的语法。

CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno);
4.删除数据表的时候要先删除子表再删除父表。
5.创建数据表的时候先创建父表再创建子表。

索引的创建和使用
DEMO:观察代码
SELECT * 
	FROM SCOTT.emp
	  WHERE sal>1000;


以上的查询只能看到数据，而不能看到整个查询中sql语句执行计划过程，如果观察过程则可以现切换到超级管理员下，打开跟踪器即可。

DEMO:开启跟踪器
conn SYS/CHANGE_ON_INSTALL AS SYSDBA;//切换用户
SET AUTOTRACE ON;//打开跟踪器

DEMO:继续观察
SELECT *
	FROM SCOTT.emp
	  WHERE sal>4000;


TABLE ACCESS FULL 表示在查询数据的时候使用的是全表扫面的模式。

创建索引的基本语法：

CREATE INDEX 索引名 ON 数据表（字段名）

相当于将数据表的指定字段的值保存到索引数节点中。

DEMO:创建索引

CREATE INDEX emp_sal_index ON SCOTT.emp(sal);
DEMO:删除索引

DROP INDEX emp_sal_index;
复合索引创建：

复合索引创建就是一个索引在多个字段上创建，就是一个索引作用于多个字段。

DEMO:创建复合索引

CREATE INDEX emp_job_sal_index ON SCOTT.emp(job,sal);
DEMO:查询数据
SELECT *
	FROM SCOTT.emp
	  WHERE sal>1000;


此时使用的是全表扫面模式

DEMO:继续查询
SELECT *
	FROM SCOTT.emp
	  WHERE job='SALEMAN';


当使用复合索引的时候如果只要其中一个字段作为判断条件，那么只有使用第一个字段作为判断条件的时候（在创建索引时候的字段顺序）索引才会生效，这叫做索引的最左原则。

DEMO:查询（使用两个字段）
SELECT * 
	FROM SCOTT.emp
	  WHERE job='SALEMAN' AND sal>1000;

SELECT * 
	FROM SCOTT.emp
	  WHERE  sal>1000 AND job='SALEMAN';


在oracle中如果使用了两个多个字段，并且是AND连接的条件，那么字段的顺序不影响扫描的方式（都使用索引扫描）

如果是mysql则需要和复合索引的字段的顺序一致。

DEMO:在OR逻辑中使用复合索引
SELECT *
	FROM SCOTT.emp
	  WHERE job='SALEMAN' OR sal>1000;


使用复合索引的时候如果放到了OR查询中则会导致索引失效。可以使用UNION ALL 代替OR查询

DEMO:使用UINON ALL
SELECT *
	FROM SCOTT.emp
	  WHERE job='SALEMAN'
UNION ALL
SELECT * 	
	FROM SCOTT.emp
	  WHERE sal>1000;

总结：

索引不能随便使用，否则就是滥用，如果一张数据表中的数据更新频率太高，因为更新数据之后需要重新创建索引，这一过程是很耗费性能的。


如果一个项目中要求一张数据表按照某个字段查询的速度很高，但是该数据表的数据经常改变，请问：要怎么解决这一问题：


1.可以提高硬件性能
2.读写分离，暂时可以设计两张数据表：一张用于数据的查询，一张用于数据的更新，等到夜间人少的时候做一次数据的汇总。

即使某一项字段设置了唯一约束，null值也不会收到唯一约束的影响，即含有唯一约束的数据列上可以存在一次或多次为空 

语法：例如为email 字段设置唯一约束 

constraint uk_email unique(email)

/*初始化两张表，测试添加约束语句*/
use E_Market
go

--创建部门表
if exists(select * from sysobjects where name = 'department')
drop table department
create table department --部门表
(
	DepartmentId char(5) not null primary key,
	DeparmentName varchar(20)
)
--创建员工表
if exists(select * from sysobjects where name = 'employee')
drop table employee
create table employee   --员工表
(
	Id char(18) not null,   --身份证号
	JobId char(6) not null, --员工编号
	Name varchar(20) not null,  --姓名
	Sex char(2) null,   --性别
	Birthday datetime null, --出生日期
	Salary Money not null,  --工资
	DepartmentId char(5) null   --部门编号
)
/*
对员工表要求：
	id的值是唯一，作为  （主键）
	jobid的值也是唯一的，作为为一键  （唯一键）
	sex取值只能为男或女     （检查约束）
	birthday要求必须年满18        （检查约束）
	salary默认值为3000      （默认约束）
	department值必须在department表中存在departmentid（外键）
*/

/*添加主键约束*/
alter table employee
add constraint PK_id primary key(Id)

/*添加唯一约束*/
alter table employee
add constraint UQ_jobid unique(Jobid)

/*添加检查约束*/
alter table employee
 add constraint CK_sex check(sex in('男','女'))

/*添加检查约束*/
alter table employee
add constraint CK_birthday check (year(GETDATE())-year(birthday)>=18)   --表示当前时间减去出生日期时间大于18岁

/*添加默认约束*/
alter table employee
add constraint DF_salray default 3000 for salary

/*添加外键约束*/
alter table employee
add constraint FK_departmentid foreign key (departmentid) references department(departmentid)

/*删除约束*/
alter table employee
drop constraint ck_sex   --删除CK_sex约束

time:2008/03/25
author:skate
在有重复数据的列上添加unique constraints,大家正常的解决办法就修改重复数据,但也可以
保留重复数据,使约束对以后的数据有限制,不过我们还可以用以下的方法来添加唯一约束.
SQL> create table aa(num number(6),email varchar2(32));
表已创建。
SQL> insert into aa values(1,'11');
已创建 1 行。
SQL> insert into aa values(2,'11');
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter table aa add constraint aa_un unique(email) ;
alter table aa add constraint aa_un unique(email)
                              *
第 1 行出现错误:
ORA-02299: 无法验证 (PERFSTAT.AA_UN) - 找到重复关键字

SQL> create index aa_inx on aa(email);
索引已创建。
SQL> alter table aa add constraint aa_un unique(email) enable novalidate;
表已更改。
SQL> commit;
提交完成。
SQL> insert into aa values(1,'11');
insert into aa values(1,'11')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (PERFSTAT.AA_UN)

SQL>

----- end --------