现代关系数据库管理系统提供了一个层次化的数据库对象命名机制。一个关系数据库管理系统的实例可以建立多个数据库，一个数据库中可以建立多个模式，一个模式下可以包括多个表、视图和索引。定义模式实际上定义了一个命名空间，在这个空间中可以定义包含的基本表、视图、索引等

模式定义和删除：

定义模式：

例：为用户wang定义一个模式S-T

create schema 'S-T' authorization wang
  • 1.

如果没有指定模式名，那么模式名默认为用户名

create schema authorization wang;
  • 1.

在create schema中可以接受create table,create view和grant子句。

为用户zhang创建一个模式TEST，并且在其中定义一个表TAB1。

create schema TEST authorization zhang
create table tab1(
   ....
 );
  • 1.
  • 2.
  • 3.
  • 4.

删除模式：

drop schema zhang cascade;
  • 1.

cascade(级联)：删除模式同时把模式中所有的数据库对象全部删除

restrict(限制)：如果该模式中定义了下属的数据库对象，则拒绝该删除语句的执行。

基本表定义删除和修改：

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)    

建立学生表Student。学号是主码，姓名取值唯一。

create table Student
(Sno    char(9)  primary key,
 Sname  char(20) unique,
 Ssex   char(2)  smallint,
 Sdept  char(20)
);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

课程表：Course(Cno,Cname,Cpno,Ccredit)    

建立一个课程表Course

create table Course
(Cno     char(4)  primary key,
 Cname   char(40),
 Cpno    char(4),
 Ccredit smallint,
 foreign key(Cpno) references Course(Cno)
);

Cpno是外码,被参照表是Course,被参照列是Cno
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

学生选课表：SC(Sno,Cno,Grade)

create table SC
(
  Sno char(9),
  Cno char(4),
  Grade smallint,
  primary key(Sno,Cno), 主码由两个属性构成
  foreign key(Sno) references Student(Sno),
  foreign key(Cno) references Course(Cno)
);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

模式与表：

一个模式有多个基本表。

定义基本表所属模式

（1）create table "S-T".Student(..);

（2）create schema "ST" authorization wang creat table Student();

（3）设置所属的模式

创建基本表，如果没有指定模式，系统根据搜索路径来确定该对象所属的模式。关系数据管理系统会使用模式中第一个存在的模式作为数据库对象的模式名。

若搜索路径中的模式名都不存在，系统将给出错误。 

数据库管理员可以设置搜索路径，然后定义基本表。

修改基本表:

增加列:                alter table tb add 列名 数据类型

删除列：              alter table tb drop 列名 [ cascade | restrict ]

增加唯一值约束:  alter table tb add unique(列名)

修改列属性:         alter table tb alter column 列名 数据类型


例：向Stduent表增加入学时间列，数据类型为日期类

alter table Student add S_entrance date;
  • 1.

新加的列一律为空值

例：将年龄的数据类型由字符型改为整数

alter table Student alter column Sage int;
  • 1.

例：增加课程名称必须取唯一值的约束条件

alter table Course add unique(Cname);
  • 1.

删除基本表：

例:删除Student表

drop table Student cascade;
  • 1.

cascade:删除该表没有限制。在删除基本表的同时，相关的依赖对象(索引、视图、触发器)一起删除。

restrict：有限制条件。

练习：

创建一张教师表

【数据库系统】模式&基本表&索引定义和删除_服务器

 tabl

create table Teacher
(
  Tno        char(8) primary key,
  Tname      char(10),
  Sex        char(1),
  Birth      date,
  Institute  char(10),
  address    char(50)
);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

例：为教师表增加一列Pt，字符型，宽度为10

alter table Teacher add PT char(10);
  • 1.

例：修改教师表Institute字段宽度为20 

alter table Teacher alter column Institute char(20);
  • 1.

例：删除教师表中的address字段

alter table Teacher drop address cascade;
  • 1.

索引的建立与删除：

建立索引是加快查询速度的有效手段。

关系数据库管理系统中常见索引：

顺序文件上的索引，B+树索引(动态平衡)，散列(hash)索引(查找速度快),位图索引。

主键、外键、在表联结操作上经常用到的列、经常查询的字段可以考虑建立索引。

很少被查询的列，大部分属性值重复的字段不考虑建立索引。

但是，索引有代价(时空),为了维护索引，对数据进行插入、更新、删除操作所花费的时间会更长。

索引存储为索引记录，包括键值(定义索引时指定的所有字段的值)+逻辑指针(指向数据页或另一索引页)

可以直接用create index创建索引，也可以用定义主键约束或者唯一性键约束间接创建索引。

可以用create unique index 创建唯一性索引

可以用create index xx on table tb(col1,col2)创建复合索引。

聚集索引：   表数据按照索引的顺序来存储

对表的物理数据页中的数据按索引进行排序，在数据页中数据按照索引顺序存储。

每张表只能有一个聚集索引（通常为主键）。

聚集索引适用于大量非重复值的列，经常被连续访问的列，经常使用连接或group by子句查询访问的列。不适用于频繁更改的列。

聚集索引的叶级索引页就是数据页。

非聚集索引：表数据存储顺序与索引顺序无关

一个表可以建立多个非聚集索引。

非聚集索引的叶层不包含数据页。

创建索引：

create [unique] [clustered | nonclustered] index 索引名 on table（列名 [asc|desc],..）

unique:用于创建唯一索引，即不允许存在索引值相同的两行

clustered:      用于创建聚集索引

nonclustered:用于创建非聚集索引(默认)

例：在Student表上创建一个名为index_classno的非聚集索引，索引关键字为classno，升序

create nonclustered index index_classno on student(classno asc)
  • 1.

 例：在SC表上创建一个名为index_snocno的非聚集索引，索引关键字为sno和cno，升序。

create nonclustered index index_snocno on sc(sno asc,cno asc);
  • 1.

例：为Student,Course,SC三个表建立索引。Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

create unique index Stusno on Student(Sno);
create unique index Coucno on Course(Cno);
create unique index SCno   on SC(Sno asc,Cno desc);
  • 1.
  • 2.
  • 3.

 

删除索引：

drop index 表名.索引名,...

执行drop index语句后，收回被索引占用的磁盘空间

不能用drop index语句删除primary key约束或unique约束创建的索引。必须先删除约束，使索引自动删除。

删除表，表上所有所有同时被删除。

删除聚集索引时，表中所有非聚集索引都会自动被重建。

例：用drop语句将表sc的索引index_snocno删除

drop index index_snocno on sc
  • 1.

修改索引：

alter index 旧索引名 rename to 新索引名

例：将SC表的SCno索引名改为SCSno 

alter index SCno rename to SCSno;
  • 1.

 

数据字典:

数据字典是关系数据库管理内部的一组系统表,它记录了数据库中所有的定义信息。

关系模式定义,视图定义,索引定义,完整性约束定义,各类用户对数据库的操作权限，统计信息等

关系数据库管理系统在执行SQL的数据定义语句时，实际上是在更新数据字典表中的相应信息。