1-3 SQL与建立关系型数据表

1-3 SQL与建立关系型数据表
u 了解关系型数据库的完整性基本理论

u 掌握通过SQL语句创建基本表

u 掌握通过SQL语句修改基本表的模式结构

u 掌握通过SQL删除基本表

1-3-1 关系型数据库的完整性理论
SQL（Structured Query Language结构化查询化语言）不仅仅具有查询功能。它是一个通用性的数据库操纵、定义语言，被普遍使用在信息系统的应用中。SQL的数据定义语句首先是针对基本表、视图和索引的创建、删除和修改，具体的定义包括：

表1-4 主文件组和用户定义文件组的差异

SQL在建立基本表信息的时候，必须按照关系型数据库完整性理论建立基本表，该理论包括：实体完整性，参照完整性和用户定义完整性。各个完整性内容包括：

1. 实体完整性
即主码的非空性原则，如果主码为空则意味着这条元组不知道或者无意义。

2. 参照完整性
要求外码的填写或者为空，表示尚无规定的信息与此条主码信息对应；如果非空，则填充的外码信息必须出自父表的主码信息集合。

3. 用户定义完整性
由用户根据具体的信息逻辑所定义的信息填充方案，如性别只能够是男或者女

1-3-2 由一个案例所见到的关系数据理论1． 基本表情况说明
这里给出一个学校数据库的三个基本表，用在以后的实例中说明SQL语句的各种用法。

（1）学生表：Student(Sno,Sname,Ssex,birthday,class)

Student由学号(Sno)、姓名(Sname)、性别(Ssex)、生日(birthday)、班级(class)五个属性组成，其中Sno为主码。

（2）课程表：Course(Cno,Cname,Cpno,Ccredit,tno)

Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(ccredit)、任课教师号(tno)五个属性组成，其中Cno为主码。

（3）学生选课表：SCore(Sno,Cno,Grade）

SC由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，主码为(Sno,Cno)。

（4）教师表：Teacher(tno,name,sex,prof,depart,salary）

Teacher由教师号( tno)、教师名(name)、性别(sex)、职称(prof)、部门(depart)、薪酬(salary)六个属性组成，主码为(tno)。

 表1-5  student基本表表                        ______________________________________________ Sno Sname Ssex Birthday Class 103 李勇 男 1982-3-4 95031 105 刘晨 女 1983-5-8 95031 107 王明 女 1983-12-21 95033 109 赵明棋 男 1982-11-4 95033 110 张惠 女 1982-9-26 95033 ______________________________________________ 1-6  sc基本表 ______________________________________________ Sno Cno Grade 103 322 92 103 343 85 105 105 93 105 245 63 107 166 78 ______________________________________________ 表1-7  course基本表 ______________________________________________ Cno Cname Cpno Ccredit Tno 322   数据库 166 4 804 343   数学  2  105   信息系统  322 4 825 245   操作系统  166 3 835 166   数据结构  888 4 804 324   数据处理  166 2 856 888   C语言 343 4 856 ______________________________________________ 表1-8  Teacher基本表 ______________________________________________ Tno Name Sex Birthday   Prof    Depart Salary 804 李诚    男      1958-12-2  副教授  计算机系     3400 825 王萍   女      1972-5-5   助教    计算机系     1230 835 刘冰    女      1977-8-14  助教    电子工程系   1142 856 张旭    男      1969-3-12  讲师    电子工程系   2450 ______________________________________________

实验：可视化状态下建立基本表实验

第一步：启动SQL Server2005的Management Studio，进入管理平台界面后，鼠标右击对象资源管理器中的数据库，选择“新建数据库”后，新建数据库school。

第二步：在对象资源管理器中用鼠标右键单击新建数据库school，在弹出的快捷菜单中选择 “新建表”，如表1-13。

第三步：按照基本表说明内容依次建立四张基本表，需要说明的是针对于每一张基本表需要认真规定每个属性的名称和数据类型，见图1-14所示。另外对于主键以及外键的设计必须按照数据库概念设计模型进行规划设计后才可以实施，具体设计的方法和步骤见数据库模式设计篇章。


图1-13 新建基本表 图1-14 定义基本表的属性名称和数据类型

第四步：在对象资源管理器school数据库的树形结构中找到数据库关系图一项，用鼠标右键单击后，于弹出的快捷菜单中选择“新建关系图”，见图1-15所示。

第五步：在弹出的“添加表”中将全部的表都选中，单击添加按钮后，将全部表添加到新建关系图之中，见图1-16所示。


图1-16 添加基本表和基本关系图

第六步：在基本关系图之中，用鼠标左键点击某张表的外键，将之拖至父表的主键上，从而建立起主、外键的关联。需要特别说明的是对于基本表course，由于cpno是先修课程号码，必须来自course表中的cno主码集合，因此cpno属于单表自映射的主外键关系。见图1-17所示。在建立完该关系图后，既可以将具体数据录入数据表之中。


图1-15 新建数据库关系图图 1-17 建立主外键之间的关系

1-3-3 通过SQL创建基本表
通过SQL可以更便捷的创建基本表，同时也可以更好的反映数据库完整性的思维方式。建立基本表语句格式见下：

create table 表名（

列名 数据类型 [default 缺省值] [not null][ UIQUE]

[，列名 数据类型 [default 缺省值] [not null]]

……

[，primary key（列名 [，列名] …）]

[，foreign key （列名 [，列名] …） references 表名 （列名 [，列名] …）]

[，check（条件）]）

1． SQL建立基本表注意事项：
(1) 表名是所要定义的基本表名称，它可以由一个或多个属性列组成。

(2) 建立表的时候，还可以定义与该表有关的关系性约束性条件，这些关系性约束性条件被存入系统的数据字典中。当用户操作数据库中表内的数据的时候，由DBMS自动检查该项操作是否违背这些关系性约束条件。

(3) 语句定义说明：

qDefault：设置该列的缺省值，当插入数据，没有指定该列的值的时默认取该值。

qUNIQUE：唯一性约束，该列不允许取重复的值。

qNOT NULL：该列不允许取空值。

qPrimary Key：主键约束。

qforeign key 本表中的外码 .references 对应主表中的主键：外键约束。

qCHECK：用户自定义的约束条件，根据实际需要而定。

2． SQL Server 2005的主要数据类型
SQL Server 2005的基本数据类型包括有：

qchar（n）：固定长度的字符串，此数据类型可存储1~8000个定长字符串，字符串长度在创建时指定；如未指定，默认为char(1)。每个字符占用1byte存储空间。

qvarchar（n）：可变长字符串。（由于该类型可根据实际录入的数据字节量存储，因此在一定程度上较char要节省磁盘空间，故对于字符串类型数据建议使用该种类型。）此数据类型可存储最大值为8000个字符的可变长字符串。可变长字符串的最大长度在创建时指定，如varchar(50)，每个字符占用1byte存储空间。

qint：整数，此数据类型存储-2147483648~2147483647的整数，占用4bytes存储空间。

qsmallint：小整数类型，此数据类型存储-32768~32767的整数，占用2bytes存储空间。

qnumeric（p，d）：定点数，小数点左边p位，右边q位。

qfloat：此数据类型存储1~53的可变精度的浮点值，n为1~24，占用4bytes存储空间；n为25~53，占用8bytes存储空间。

qreal：此数据类型存储-3.40E+38~-1.18E-38的负数和1.18E~3.40E+38的正数。占用4bytes存储空间。

qdatetime：此数据类型存储从1753年1月1日到9999年12月31日的日期。占用8bytes存储空间。

qsmalldatetime：此数据类型存储从1900年1月1日到2079年6月6日的日期。占用4btyes存储空间。

qtime：时间（小时、分、秒）。

qmoney：此数据类型存储-922337203685477.5808~922337203685477.5807的货币值，精确到小数后4位。占用8bytes存储空间。

qbinary：此数据类型存储1~8000个字符的二进制数据，其指定长度即为占用的存储空间。

实验：建立基本表的SQL实验

--例1：建立基本表student

CREATE TABLE Student

(Sno varchar (5) NOT NULL UNIQUE,

/*Sno取值唯一,不许取空值*/

Sname varchar (20) UNIQUE,

Ssex char(1),

Sage INT,

Sdept varchar (15));

--例2：建立基本表sc

CREATE TABLE SC

(Sno varchar (5) NOT NULL UNIQUE,

Cno varchar (4) default '1', /*cno默认值为1*/

GRADE Smallint,

Primary Key(Sno,Cno),

Foreign Key (sno) References student(sno),

/*Sno为当前表的外键，对应于student表的主键*/

Foreign Key (Cno) References Course(Cno),

Check(Grade between 0 AND 100));

/*grade成绩属性在0～100之间，用户定义完整性*/

--例3：建立基本表student

CREATE TABLE STUDENT

(SNO varchar(4),

SNAME varchar (8) NOT NULL,

SAGE Smallint,

SSEX varchar (1),

sdept varchar (8),

PRIMARY KEY (SNO), /*Sno为当前表的主键*/

CHECK (SEX=0 OR SEX=1)); /*Sex只能够取0或者1，用户定义完整性*/

--例4：建立基本表course

CREATE TABLE COURSE

(CNO varchar (4),

CNAME varchar (10) NOT NULL,

CPNO varchar (4),

CCREDIT SMALLINT, PRIMARY KEY(cno));

1-3-4 SQL修改基本表关系结构

建立完基本表后，由于关系数据库的模式设计需要，或者项目逻辑关系的变化，经常需要进行基本表逻辑关系的修改。修改基本表的语法见下：

alter table 表名

          [add <新列名> <数据类型> [完整性约束]]   --这里可以增加新的属性

          [drop <完整性约束名>]  --删除列

          [alter column <列名> <数据类型>] --修改列属性

注意：

增加完整性约束可以是Add Constraint 数据库中约束名称 [完整性约束条件]，如果是用户定义完整性约束可以是check()语句。

实验：修改基本表的SQL实验

--例1：向student表中增加“入学时间”列，其数据类型为日期型

 ALTER TABLE Student ADD Scome datetime;

--例2：将年龄的数据类型改成为短整型，需注意的是修改原有的列可能会破坏已有的数据

alter table student alter column sage smallint;

--例3：重新设定student的主键是sno

 ALTER TABLE Student ADD PRIMARY KEY(Sno);

--例4：去除掉sage 列

ALTER TABLE Student drop column sage;

--例5：给教师表增加一项“教研室号码”，int类型，并且不可以大于

Alter table teacher

Add 教研室号码int Constraint fk1 check(教研室号码<20)

--例6：将刚才的教研室号码约束删掉后再将教研室号删掉

Alter table teacher drop fk1

Alter table teacher drop column 教研室号码

--例7：更改一条外键的约束

alter table sc add constraint fk3 foreign key(sno) references student(sno)

--例8：删除一个属性列

CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)

     ALTER TABLE doc_exb DROP COLUMN column_b /*删除列*/

1-3-5 SQL删除基本表

删除基本表的SQL语法比较简单，基本格式为：DROP TABLE <表名>。例如删除学生表student就是DROP TABLE Student即可。

注意：

q 撤消基本表后，基本表的定义、表中数据、索引都将被删除。

q Drop Table不能够除去由foreign key约束引用的表，因此必须先除去引用的 foreign key约束条件。

小问题：

对关系型数据库系统而言，删除基本表的顺序是先删除父表还是先删除子表呢？

父表是主键所在表，子表是外键所在表，根据参照完整性规则，外键必须受制于主键集合的约束，添加信息或者为空，否则必须出自主键集合。如果先删除父表，则外键将无所依靠，必将产生错误。因此对关系型数据库系统而言，删除基本表的顺序是先删除子表，再删除父表。