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)    语句定义说明：

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

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

q NOT NULL ：该列不允许取空值。

q Primary Key ：主键约束。

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

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

2． SQL Server 2005的主要数据类型

    SQL Server 2005 的基本数据类型包括有：

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

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

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

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

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

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

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

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

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

q time ：时间（小时、分、秒）。

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

q binary ：此数据类型存储 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约束条件。

小问题：

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

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

 

转载于:https://blog.51cto.com/qianshao/347764