DDL建表操作和相关约束

发表于 | 分类于 ，

表的创建

一、标准的建表语法：
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr] , …
);
schema：不同数据库对应意义不一样。表示的是相应模式的规范。
datatype：对应的类型匹配

▪ 在创建新表时，指定的表名必须不存在，否则将出错。
▪ 使用默认值：当插入行时如果不给出值，dbms将自动采用默认值。
▪ 在用Create语句创建基本表时，最初只是一个空的框架，用户可
以使用insert命令把数据插入表中。

案例：
设计要求：建立一张用来存储学生信息的表，表中的字段包含了学生的学号、姓名、年龄、入学日期、年级、班级、email等信息，
–并且为grade指定了默认值为1，如果在插入数据时不指定grade的值，就代表是一年级的学生

1
2
3
4
5
6
7
8
9
10
11
12
13
create table student
(
stu_id number(10),
name varchar2(20),
age number(3),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50)
);
insert into student values(20191109,'zhangsan',22,to_date('2019-11-09','yyyy-mm-dd'),'2','1','123@qq.com');
insert into student(stu_id,name,age,hiredate,classes,email) values(20191109,'zhangsan',22,to_date('2019-11-09','yyyy-mm-dd'),'2','123@qq.com');
select * from student;

二、使用子查询创建表的语法：
CREATE TABLE table [column(, column…)]
AS subquery;

  1. 新表的字段列表必须与子查询中的字段列表匹配
  2. 字段列表可以省略
    1
    create table emp2 as select * from emp;

表结构的修改

▪ 在基本表建立并使用一段时间后，可以根据实际需要对基本表的结构进行修改

  • 增加新的列用“alter table … add …”语句
  • 删除原有的列用“alter table … drop…”语句，语法格式：alter table 表名 drop column 列名
  • 修改字段“alter table…modify…”
    ▪ 在基本表不需要时，可以使用“drop table”语句撤消。在一个基
    本表撤消后，所有的数据都丢弃。所有相关的索引被删除。
     drop table emp cascade constraints

    级联删除：A表中dept=10，B表中dept=10，它们两个是一一对应的关系（B表中包含了其他表的一些列）。如果现在想把B表进行删除的话，那对应A表中的字段就不在了，肯定会报错的。级联删除就是先删除A，才能删除B。
    ▪ 可以使用RENAME语句改变表名（视图）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    --正规的表结构设计需要使用第三方工具 powerdesigner
    --在添加表的列的时候，不能允许设置成not null
    alter  table student add address varchar(20);
    alter table student drop column address;
    alter table student modify(email varchar2(100));
    --重新命名表
    rename student to stu;
    --删除表
    /*
    在删除表的时候，经常会遇到多个表关联的情况。多个表关联的时候，不能随意删除，需要使用级联删除。
    cascade：如果A,B，A中的某个字段和B中的某一个字段做关联，那么删除A表的时候，需要将B表先删除
    set null：在删除的时候，把表的关联字段设成空
    */
    drop table stu;

约束 constraint

▪ 当我们创建表的时候，同时可以指定所插入数据的一些规则，比如说某个字段不能为空值，某个字段的值（比如年龄）不能小于零等等，这些规则称为约束。约束是在表上强制执行的数据校验规则。
▪ Oracle 支持下面五类完整性约束:

  1. NOT NULL非空： 插入数据的时候，某些列不能为空

  2. UNIQUE 唯一： 可以限定某一个列的值是唯一的，唯一键的列一般被用作索引列

  3. PRIMARY KEY 主键：非空且唯一，任何一张表最好有主键，用来唯一标识一行记录

  4. FOREIGN KEY 外键：当多个表之间有关联关系（一个表的某个列的值依赖于另一张表的某个值）的时候），需要使用外键

    作为主键的表称为“主表”，作为外键的关系称为“依赖表”
    外键参照的是主表的主键或者唯一键
    对于主表的删除和修改主键值的操作，会对依赖关系产生影响，以删除为例：当要删除主表的某个记录（即删除一个主键值），那么对依赖的影响可采取下列3种做法：

    1. RESTRICT方式：只有当依赖表中没有一个外键值与要删除的主表中主键值相对应时，才可执行删除操作。
    2. CASCADE方式：将依赖表中所有外键值与主表中要删除的主键值相对应的记录一起删除
    3. SET NULL方式：将依赖表中所有与主表中被删除的主键值相对应的外键值设为空值
      FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
      [ON DELETE [CASCADE|SET NULL]] 如省略on短语，缺省为第一种处理方式。

  5. CHECK 自定义检查约束：可以根据用户自己的需求去限定某些列的值

约束的添加和撤销
▪ 可增加或删除约束，但不能直接修改
alter table tablename
增加
add constraint con_name unique(col)
删除
drop constraint com_name [cascade]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
--执行失败，外键约束
insert into emp(empno,ename,deptno) values(9999,'hehe',50);

create table student
(
stu_id number(10) primary key,
name varchar2(20) not null,
age number(3) check(age>0 and age<126),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50) unique,
deptno number(2),
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);
select * from student;
--建议在创建表的时候直接将各个表的约束添加好，如果包含外键约束的话，最好把外键关联表的数据优先插入
--失败，非空约束
insert into student(stu_id,age,hiredate,classes,email) values(20191109,22,to_date('2019-11-09','yyyy-mm-dd'),'2','123@qq.com');
--失败，唯一约束
insert into student(stu_id,name,age,hiredate,classes,email) values(20191109,'zhangsan',22,to_date('2019-11-09','yyyy-mm-dd'),'2','123@qq.com');
insert into student(stu_id,name,age,hiredate,classes,email) values(20191109,'zhangsan',22,to_date('2019-11-09','yyyy-mm-dd'),'2','123@qq.com');
--失败，主键约束
insert into student(stu_id,name,age,hiredate,classes,email) values(20191109,'zhangsan',22,to_date('2019-11-09','yyyy-mm-dd'),'2','123@qq.com');
insert into student(name,age,hiredate,classes,email) values('lisi',22,to_date('2019-11-09''yyyy-mm-dd'),'2','124@qq.com');
--失败，检查约束
insert into student(stu_id,name,age,hiredate,classes,email) values(20191110,'zhangsan',144,to_date('2019-11-09','yyyy-mm-dd'),'2','124@qq.com');

--添加后，如果想删除dept表中的dept=10的那条记录，会失败。因为外键关联了.但是可以直接删除student中这条记录 
insert into student(stu_id,name,age,hiredate,classes,email,deptno) values(20191109,'zhangsan',22,to_date('2019-11-09','yyyy-mm-dd'),'2','123@qq.com',10);
--可以选择后续额外添加
alter table student add constraint fk_0001 foreign key(deptno) references dept(deptno);

索引

▪ 索引是为了加快对数据的搜索速度而设立的。
▪ 索引的作用：在数据库中用来加速对表的查询,通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O

索引：指针，偏移量。索引不存放在内存（内存一开关机就不存在了。）所以索引数据是存放在磁盘中的。
1、局部性原理：（时间、空间）经常访问的数据和代码都经常聚在一起
2、磁盘预读：磁盘1T数据，从中读取1字节数据，怎么读？每次其实是读入的多个字节（一个块空间，一般是4k）。
3、减少I/O：一个是减少访问的次数，一个是减少IO的量。
4、索引是有数据结构的，每次读数据不是把所有数据都加到内存，而是需要哪快加哪快。（B+树）
什么叫回表？
什么叫覆盖索引？
什么叫字符匹配？
什么叫组合索引？

▪ 索引的创建有两种情况
 1、自动：当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时,Oracle数据库自动创建一个对应的唯一索引
 2、手动：用户可以创建索引以加速查询

▪ 开发中使用索引的要点：
 1、索引改善检索操作的性能，但降低数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引
 2、索引数据可能要占用大量的存储空间
  可以在索引中定义多个列（如省加城市）先限定死大的范围

▪ 在一列或者多列上创建索引
CREATE INDEX index ON table (column[, column]…);
▪ 下面的索引将会提高对EMP表基于 ENAME 字段的查询速度.
CREATE INDEX emp_last_name_idx
ON emp (ename)
▪ 通过DROP INDEX 命令删掉一个索引.
– DROP INDEX index;
▪ 删掉 UPPER_LAST_NAME_IDX 索引.
– DROP INDEX upper_last_name_idx;

1
2
3
4
5
--创建索引
create index i_ename on emp(ename);
--删除索引
drop index i_ename;
select * from emp where ename='SMITH';

为什么加索引比没有加索引时间更长？

  1. 数据量太小
  2. 回表
  3. 覆盖索引
  4. 组合索引
  5. 最左匹配