数据表的创建、约束的名称、外键约束、索引的创建和使用

最新推荐文章于 2025-04-04 17:47:22 发布

原创最新推荐文章于 2025-04-04 17:47:22 发布 · 3.1k 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#oracle 数据表的创建 #oracle 约束的名称 #oracle 外键约束 #oracle索引的创建和使用

原创专栏收录该内容

31 篇文章

订阅专栏

本文详细介绍了在Oracle数据库中如何创建数据表，包括指定非空约束、唯一约束和主键约束。讨论了约束的命名，并展示了如何设置检查约束。接着讲解了外键约束的概念，以及它在数据库表间建立的关系。最后，探讨了索引的创建和使用，包括全表扫描、索引的最左原则以及在不同查询场景下的表现。强调了索引使用应考虑数据更新频率和性能影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据表的创建

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.读写分离，暂时可以设计两张数据表：一张用于数据的查询，一张用于数据的更新，等到夜间人少的时候做一次数据的汇总。