前言：这学期学校开设了数据库这门课，以前没学的时候老听别人说这数据库这一块很重要，做好认真听的准备，结果一上课发现讲的内容大多都是定义，和OS挺像，也没提起很大兴趣，到了SQL语言这一章，才发现数据库还是有点意思，不过我们老师讲的就一般，我这也只好课下再自学点，整理一下笔记。

目录

数据定义

模式的定义与删除

创建用户

1、定义模式

2、模式与表

3、删除模式

索引的建立与删除

1、建立索引

2、修改索引

3、删除索引

关于完整性约束

常用的完整性约束

基本表的定义、删除与修改 

1、数据类型

2、定义基本表

3、基本表的修改

4、基本表的删除

 

先从较基础的说起，建一个学生-课程数据库，在数据库中进行数据定义，数据操纵，数据查询和数据控制等功能。我建的这个数据库是按书上的例子来建的。

有三个表，学生表：Student （Sno , Sname , Ssex , Sage , Sdept），课程表：Course (Cno , Cname , Cpno , Ccredit) ，学生选课表：SC(Sno ,Cno , Grade) 。

学生表：

学  号

Sno

姓  名

Sname

性  别

 Ssex

年  龄

 Sage

所 在 系

 Sdept

200215121

200215122

200215123

200515125

李勇

刘晨

王敏

张立

男

女

女

男

20

19

18

19

CS

CS

MA

IS

课程表：

课程号

Cno

课程名

Cname

先行课

Cpno

学分

Ccredit

1

2

3

4

5

6

7

数据库

数学

信息系统

操作系统

数据结构

数据处理

PASCAL语言

5

 

1

6

7

 

6

4

2

4

3

4

2

4

选课表：

学 号

Sno

 课程号

  Cno

成绩

    Grade

   200215121

   200215121

   200215121

   200215122

   200215122

  1

  2

  3

  2

  3

 92

 85

 88

 90

 80

数据定义

SQL 的数据定义功能包括模式定义、表定义、视图和索引的定义。SQL 标准不提供修改模式定义和修改视图定义的操作，用户如果想修改这些对象，只能先将它们删除再重建。SQL标准也没有提供索引相关的语句，但为了提高查询效率，商用关系数据库管理系统通常都提供了索引机制和相关的语句。换句话说就是，SQL标准中只允许对表定义的修改。

模式的定义与删除

模式又称架构，定义模式实际上定义了一个命名空间，可以理解成一个一个的文件夹。一个关系数据库管理系统的实例中可以建立多个数据库，一个数据库中可以建立多个模式，一个模式（空间）下可以包含多个表、视图和索引等数据库对象。

创建用户

创建SQL sever 用户的方法是：

create login user1 with password = 'abc123efg'

create login user2 with password = '123aass'

创建数据库用户的方法是：

create user zhang for login user1 ;

create user liu for login user2 ;

 

1、定义模式

在SQL中，模式定义语句为：

create schema <模式名> authorization <用户名>

//例：
create schema zhang authorization zhang ;

如果没有指定模式名，那么模式名隐含为用户名。需要注意的是，调用该命令时用户必须有数据库管理员权限，或者获得了数据库管理员赋予的 create schema 的权限。（我在SQL中试了试，确实是出现错误的，但那个错误我不是很懂 QAQ ）。上边的用户 zhang 如果拥有创建表的权限，就可以建表 zhang.tab1 ，而不能建立 dbo.tab1，dbo 是默认的模式，但是普通用户不能使用这个模式。模式是一个命名空间，每个用户把自己创建的数据库对象放在自己的模式里面。像我们创建的 Student 表默认就是 dbo.Student 。如果创建时指明模式名，那还可以创建一个 zhang.Student 。

 

2、模式与表

每一个基本表都属于某一个模式，一个模式包含多个基本表。

定义基本表所属模式：

1、在表名中显式地给出模式名（已有模式名为 s1）

create table s1.Student ( ... )  

create table Student ; 它的模式名默认为 dbo (可以在数据库/安全性/架构中看到) ，相当于 create table dbo.Student

2、在创建模式语句中同时创建表

例：

create schema test authorization zhang
    create table tab1(
        col1 smallint ,
        col2 int,
        ......
);

为用户 zhang 创建了一个模式 test ，并在其中定义了一个表 tab1 。注意：zhang 必须是一个已经存在的数据库用户名， 和 SQL sever 的登录名不同 （可以在安全性中的用户下看到）。如果用户不存在，执行上面的命令就会出错，提示无法对用户执行查找，因为它不存在或者是你没有所需的权限。

3、删除模式

在SQL中，删除模式语句为：

drop schema <模式名> <cascade | restrict > ;

其中 cascade 和 restrict 两者必选其一一。选择 cascade（级联） ，表示在删除模式的同时把该模式中所有的数据库对象全部删除；选择 restrict（限制），表示如果该模式下已经定义了下属的数据库对象（如表、视图等），则拒绝删除语句的执行，只有当该模式下没有任何下属的对象时才能执行 drop schema 语句 。

 

索引的建立与删除

索引：索引是在表上创建的数据库对象，它可以提供到数据的更快的渠道，并且可以使查询执行更快。索引的作用：加快查询速度。创建了索引的列几乎是立即响应，而不创建索引的列则需要较长时间的等待。索引的优点是能够加快访问速度，但缺点是带索引的表在数据库中需要更多的存储空间，另外操纵数据（如更新基本表）的命令需要更长的处理时间，因为它们需要对索引进行更新和维护(DBMS自动完成)，这些都会增加数据库的负担，因此要根据实际应用的需要有选择的创建索引。SQL标准中没有设计索引，但是DBMS都支持索引。

一般来说，建立和删除索引由数据库管理员或者表的属主（即建立表的人）负责完成。关系数据库管理系统在执行查询时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。索引是关系数据库管理系统的内部实现技术，属于内模式的范畴。需要说明的是，DBMS 一般会自动建立以下列上的索引：

primary key 和 unique 。

数据库索引有许多种类型，常见的索引包括顺序文件上的索引、B+树索引、散列索引和位图索引等。顺序文件上的索引是针对按指定属性值升序或者降序存储的关系，在该属性上建立一个顺序索引文件，索引文件由属性值和相应地元祖指针组成；

1、建立索引

在SQL语句中，建立索引使用 create index 语句，其格式一般为：

create [ unique ][ clustered ] index <索引名> on <表名> (<列名>[ <次序> ] [, <列名> [ <次序> ]] ... ) ;

其中，<表名>是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个<列名> 后面还可以用<次序>指定索引值得排列次序，可选 ASC(升序) 或 DESC(降序) ，默认值是 ASC 。

unique 表明此索引的每一个索引值只对应唯一的数据记录。 clustered (或cluster，SQL sever 版本不同) 表示要建立的索引是聚簇索引。注意：在最经常查询的列上建立聚簇索引能有效提高查询效率，一个基本表最多只能建立一个聚簇索引，经常更新的列不宜建立聚簇索引，不能对有重复值的列加 unique 索引。

例：

--为Student 和 SC 三个表建立索引。
--Student 表按学号升序建唯一索引
create unique index U_Sno 
	on Student(Sno);
--SC 表按课程号降序建唯一索引
create unique index U_Cno
	on Course(Cno desc)
--SC表按学号升序和课程号降序建唯一索引
create unique index U_sc
	on SC(Sno asc , Cno desc)

另外

--在Student 表的Sname列上建立一个聚簇索引
create clustered index C_Sname
	on Student (Sname)

这个是会提示和主键约束（主键约束属于聚簇索引）有冲突的，具体关于聚簇索引放到后面再讲，此处了解一下。

 

2、修改索引

对于已经建立的索引，如果需要对其重新命名，可以使用 alter index 语句，其一般格式为：

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

例：

--修改索引名为u_Cno
alter index U_Cno rename to u_Cno

不过修改语句根据不同SQL sever 版本用法不一样，我的2014版本中不支持这样写，会报错，至于怎么改，我没去了解，直接一点的就是把索引删了再创建。

 

3、删除索引

建立索引是为了减少查询操作的时间，但如果数据增删改频繁，系统会花费许多时间去维护索引，从而降低了查询效率，这是可以删除一些不必要的索引，可以使用 drop index 语句，其一般格式为：

drop index <表名 . 索引名> 

例：

--删除索引
drop index Student.U_Sno ;
drop index Course.U_Cno ;
 drop index SC.U_sc;

在删除索引时，系统会同时从数据字典中删去有关该索引的描述。数据字典：数据字典是关系数据库管理系统内部的一组系统表，记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行 SQL 数据定义语句时，实际上就是在更新数据字典表中的信息。在进行查询优化和查询处理时，数据字典中的信息是其重要依据。

 

关于完整性约束

关系模型中有三种完整性约束：实体完整性约束，参照完整性约束和用户定义的完整性约束。其中实体完整性约束和参照完整性约束是关系模型必须满足的完整性约束，被称作是关系的两个不变性，应该由关系系统自动支持；用户定义的完整性是应用领域需要遵循的约束条件，体现了具体领域的语义约束。

实体完整性：若属性（指一个或一组属性）A是基本关系R 的主属性，则 A 不能取空值，所谓空值就是“不知道”或“不存在”或“无意义”的值。实体完整性规则是针对基本关系而言的，一个基本表通常对应现实世界的一个实体集；现实世界中的实体是可区分的，即它们有唯一性标识；相应地，关系模型中以主码作为唯一性标识。

参照完整性：设 F 是基本关系 R 的一个或一组属性，但不是关系 R 的码， K 是基本关系 S的主码。如果 F 和 K 相对应，则称 F 是 R 的外码，并称基本关系 R 为参照关系，基本关系 S 为被参照关系或目标关系。显然目标关系 S 的主码和参照关系 R 的外码 F 必须定义在同一个（或同一组）域上。参照完整性规则：若属性（或属性组）F 是基本关系 R 的外码，它与基本关系 S 的主码 K 相对应（基本关系 R 和 S 不一定是不同的关系），则对于 R 中每个元组在 F 上的值必须：取空值（F中的每个属性值均取空值）；或等于 S 中某个元祖的主码值。

用户定义的完整性：针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求。例如某个属性取唯一值、不能为空、取值范围等等。

 

常用的完整性约束

主码约束： primary key 

用户定义：check

默认值：default

唯一性约束：unique

非空值约束：not null

参照完整性约束： foreign key （列名） references 表名（列名）。

primary key 与 unique 的区别：可使用unique约束确保在非主键列中不输入重复值；一个表可以定义多个unique约束，而只能定义一个 primary key 约束 ；允许空值的列上可以定义 unique 约束，而不能定义 primary key 约束。

 

基本表的定义、删除与修改 

1、数据类型

关系模型中一个很重要的概念是域。每一个属性来自一个域，它的取值必须是域中的值。在SQL中域的概念用数据类型来实现。定义表的各个属性时需要指明其数据类型及其长度。SQL标准支持多种数据类型，下面列出几种常用的数据类型（经常用的也就常见的那几种）。需要注意的是，不同的关系数据库管理系统中支持的数据类型不完全相同。

数据类型
char(n) , character(n)长度为 n 的定长字符串
varchar(n)最大长度为 n 的变长字符串
clob字符串大对象
blob二进制大对象
int , integer长整数(4字节)
smallint短整数（2字节）
bigint大整数（8字节）
numeric(p,d)

定点数，由 p 位数字（不包括符号、小数点）组成，

小数点后有 d 位数字

decimal(p,d) ,dec(p ,d)同 numeric
real取决于机器精度的单精度浮点数
double却决于机器精度的双精度浮点数
folat(n)可选精度的浮点数，精度至少为 n 位数字
boolean逻辑布尔量
date日期，包含年月日，格式为 YYYY-MM-DD
time时间，包含时分秒，格式为HH:MM:SS
timestamp时间戳类型
interval时间间隔类型

char(n) , character(n) 长度为 n 的定长字符串 varchar(n) , charactervarying(n) 最大长度为 n 的变长字符串 clob 字符串大对象 blob 二进制大对象 int , integer 长整数（4字节） smallint 短整数（2字节） bigint 大整数（8字节） numeric(p,d) 定点数，由 p 位数字（不包括括号、小数点）组成，小数点后面有 d 位数字 decimal(p,d) , dec(p,d) 同 numeric real 取决于机器精度的单精度浮点数 double , precision 取决于机器精度的双精度浮点数 float(n) 可选精度的浮点数，精度至少为 n 位数字 boolean 逻辑布尔量 date 日期，包含年月日，格式为YYYY-MM-DD time 时间，包含时分秒，格式为HH:MM:SS timestamp 时间戳类型 interval 时间间隔类型

除此之外，还有 nchar ， nvarchar ，两者的区别是字符集不同，使用非常相似。例：Sname char(4) 可以存储四个字符或两个汉字；Sname nchar(4) 可以存储四个字符或四个汉字。还有位型数据类型（bit），相当于其他语言中的逻辑型数据，只存储 true 和 false。字符串值 true 和 false 转换为 bit 值分别是 1 和 0 。字符型常用的包括两种 char (固定长度) 和 varchar(可变长度) ，二者的区别也是字符集不同，另外 varchar (n) 的长度为输入字符串的实际字符个数。 

一个属性选用哪种数据类型要根据实际情况来决定，一般要从两个方面来考虑，一是取值范围，二是要做哪些运算。例如，对于年龄属性，可以采用 char(3) 作为数据类型，但考虑到要在年龄上做算术运算（如求平均年龄），所以要采用整数作为数据类型，因为在 char 数据类型上不能进行算术运算。整数又有长整数和短整数两种，因为一个人的年龄在百岁左右，所以选用短整数作为年龄的数据类型。

 

2、定义基本表

SQL语言使用 create table 语句定义基本表，其基本格式如下：

create table <表名>(<列名><数据类型>[列级完整性约束条件] ，

                                 <列名><数据类型>[列级完整性约束条件] ，

                                  . . .

                                  [<表级完整性与约束条件>]) ；

 

建表的同时还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由关系数据库管理系统自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及表的多个属性列，则必须定义在表级上，否则即可以定义在列级也可以定义在表级。这个相关事列在后面会具体说明一下。

下面进行对数据库的建立：

create database ST /*创建数据库 ST*/ /*drop database ST 删除数据库*/
use ST    /*使用数据库ST */

创建学生表：

create table Student(
	Sno char(9) primary key , /*列级完整性约束条件，Sno是主码*/
	Sname char(20) unique , /*Sname取唯一值*/
	Ssex char(2) ,
	Sage smallint ,
	Sdept char(20)
);

创建课程表：

create table Course(
	Cno char(4) primary key ,
	Cname char(40) not null , /*列级完整性约束条件，Cname不能取空值*/
	Cpno char(4) ,
	Ccredit smallint ,
	foreign key (Cpno) references Course (Cno)
		/*表级完整性约束，Cpno是外码，被参照表是Course，被参照列是Cno*/
);

创建学生选课表：

create table SC(
	Sno char(9) ,
	Cno char(4) ,
	Grade smallint ,
	primary key(Cno,Sno),/*主码由两个属性构成，必须作为表级完整性进行定义*/
	foreign key (Cno) references Course (Cno),
		/*表级完整性约束，Cno是外码，被参照表是Course */
	foreign key (Sno) references Student(Sno)
		/*表级完整性约束，Sno是外码，被参照表是Student */
)

现在一个简单的数据库就建好了，基本关系表也定义好了列属性，可以进行后续的操作。

在定义表的同时，可以添加约束，并起别名，如下：

create table Student1(
		Sno  numeric(6) constraint C1 check ( Sno between 90000 and 99999),
        Sname  char(20) constraint C2 not null,
        Sage numeric(3) constraint C3 check (Sage < 30),
        Ssex   char(2)  constraint C4 check (Ssex in ( '男','女')),
        constraint pk_s primary key(Sno)
		--定义了四个列级约束，并起别名C1~C4
      );
drop table Student1

 

3、基本表的修改

修改表一般分两种情况：1、添加或删除列，修改属性列；2、添加或删除约束,如 not null ，unique ，primary key ，check ， default 。其一般格式为：

alter table <表名>

        add <新列名> <数据类型> <完整性约束>       ——这是添加一个新的属性列，新的列级完整性约束条件。

        add <表级完整性约束>        ——这是添加一个新的表级完整性约束条件。

        drop column <列名>        ——用于删除表中的列，如果指定了 cascade 短语，则自动删除引用了该列的其他对象，比如视图

        drop constraint <完整性约束名>        ——用于删除指定的完整性约束条件。

        alter column <列名> <数据类型>       ——用于修改原有的列定义，包括修改列名和数据类型。

举几个关于添加（删除）列、修改属性列的例子：

--我们向Student表中增加一列 入学时间（Entime），数据类型为日期型，
use ST
alter table Student
	add Entime Date
--删除该列
alter table Student
	drop column Entime

需要注意的是，不论基本表中原来是否已有数据，新增加列一律为空值。删除该列的时候，会同时把列中的数据一并删除。

--将Sdept属性类型从 char(20) 改为 char(10)
alter table Student
	alter column Sdept char(10)

--将年龄的数据类型改为整型
alter table Student
	alter column Sage int

在修改原有列定义时，有可能会破坏已有数据。

 

下面说一下常用约束的添加和删除。

1）、not null 约束：

添加格式：alter column 属性名 数据类型 not null 。

删除格式：drop column 属性名 数据类型 。

例：

--将Student表中系别的属性设为不能为空
alter table Student
	alter column Sdept char not null

--将系别的属性还原
alter table Student
	alter column Sdept char(10)

再添加该约束时需注意，数据类型处要和原属性保持一致，若不一致则在增加 not null 约束时会同时修改该列的数据类型值；或者可以这样认为，在添加 not null 约束时，系统先将原属性列从表中删除，在修改时新增加了一列带有 not null 约束的属性列。

2）、unique 约束：

添加格式：add constraint 约束名 unique (属性名) 

删除格式：drop [ constraint ] 约束名

例：

--对Student表中的姓名增加唯一性约束
alter table Student
	add constraint U_Sname unique(Sname)
--添加时还可以这样
        add unique(Sname)
--但如果想给该约束添加一个别名,下面是错误的
        add U_Sname unique(Sname)
--正确写法应为
        add constraint U_Sname unique(Sname)

--删除唯一性约束
alter table Student
	drop constraint U_Sname

当我们删除约束时不知道约束名，我们可以先试着删除该列，若有约束删除则会报错，而在错误提示中，会显示该列的约束名，接着再进行删除。

alter table Student
	add constraint U_Sage unique(Sage)

--假设我们要删除Sage它的约束，但我们不知道约束名，我们可以
alter table Student
	drop column Sage
--根据错误消息中得到约束名，在进行删除。
alter table Student
	drop constraint U_Sage

再举一个较综合的例子：

--将 Sname 列 属性改为 not null
alter table Student
	alter column Sname char(20) not null

--提示有约束限制（最开始定义的唯一性约束），那么我们将约束去掉
alter table Student
	drop constraint UQ__Student__52723D275AB9A4AB

alter table Student
	alter column Sname char(20) not null

--但现在是没有唯一性约束的，我们在自己添上

alter table Student
	add constraint U_Sname unique(Sname)

如果是向表中增加新属性的约束，只需

alter table Student
	add Entime char unique

3）、check约束：

添加格式：add constraint 约束名 check (条件) 。

删除格式：drop constraint 约束名 。

如果想要修改 check 约束条件，先将原约束删除，再添加新的需要的约束。

例：

--对年龄限制为 18以上
alter table Student
	add constraint C_Sage check(Sage>18)
alter table Student
	drop constraint C_Sage

4）、主键约束：

添加格式：add primary key (属性名)

删除格式：drop constraint 主键约束名

例：

--对于表 Student来说，其已有对 Sno 的主键约束，我们将其约束删除再添加
--由于不知道约束名，先获取一下约束名：
alter table Student
	drop column Sno
--进行约束的修改
alter table Student
	drop constraint PK__Student__CA1FE4643F305E53
--我们这个例子中有SC的外键，故删除仍会有错误，知道方法就行
alter table Student
	add primary key(Sno)

5）、默认值 default 约束：

添加格式：add constraint 约束名 default 默认值 for 属性名

删除格式：drop constraint 约束名

修改格式仍是先删除再添加新约束。

例：

--增加一个对 Ssex 默认值为男的约束
alter table Student
	add constraint D_Ssex default '男' for Ssex

6）、外键约束：

添加格式：add constraint 约束名 foreign(属性名) references 表名 (属性名)

删除格式：drop constraint 约束名

 

小结：

我们通过上述几种约束定义和删除可知，其删除时均为 drop constraint 约束名 ；而在添加时，unique ， check ，default 和外键 约束有些相似，均需要加 add constraint 约束名 ......而主键约束则是 add primary key (属性名) 。另外，not null 约束最为特殊，它不影响修改和删除，其他约束影响修改和删除，必须先删除其他约束才能对列进行修改和删除

 

4、基本表的删除

当某个表不再需要时，可使用 drop table 语句来删除它，其一般格式为：

drop table <表名> 【 restrict | cascade】；

若选择 restrict ，则该表的删除是有限制条件的，欲删除的基本表不能被其他表的约束所引用（如 check ，foreign key 等），不能有视图，不能有触发器（不知道这是啥），不能有存储过程或函数等，如果存在这些依赖该表的对象，则此表不能被删除。

若选择 cascade ，则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。但这个需要注意的是，有的SQL sever 版本不支持这种语法，我用的 2008 和 2014 都不支持。

默认情况是 restrict 。用上面建好的的表就可以试一下，如果先删除基本表 Student 的话，下面会报错，说被约束着，这就表明了后面选择的是 restrict 。

drop table Student ; /*删除不了，默认选择的 restrict 具有限制条件*/
drop table Student cascade ; /*没有限制，相关的依赖对象也一同删除。*/

如果定义：drop table Student 。对于表 Student ，如果它有外键，则不能删除表，如果想删除表：1）、删除外键约束，然后再删除表；2）、删除和该表有外键关系的另外一个表，然后再删除该表。

 

更多推荐

数据库-SQL 语言（数据定义）