1-3 SQL与建立关系型数据表
本文共 6593 字，大约阅读时间需要 21 分钟。
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 约束条件。
小问题：
对关系型数据库系统而言，删除基本表的顺序是先删除父表还是先删除子表呢？
本文转自 qianshao 51CTO博客，原文链接：http://blog.51cto.com/qianshao/347764，如需转载请自行联系原作者
你可能感兴趣的文章
EF Core数据库Provider一览
查看>>
Kafka团队修改KSQL开源许可，怒怼云厂商
查看>>
苹果在GitHub上正式开源iOS内核源码
查看>>
测试人员面临的测试挑战和必备技能
查看>>
使用Flutter之后，我们的CPU占用率降了50%
查看>>
同事反馈环：为什么度量和会议还不够充分
查看>>
[转]十问 Linux 虚拟内存管理 (glibc)
查看>>
老司机带你深入浅出 Collection
查看>>
JavaScript属性操作
查看>>
查询系统-vba
查看>>
[译]Spring Session 与 Spring Security
查看>>
python学习笔记（05）
查看>>
路由器NAT网络地址转换
查看>>
checkbox全选，全不选
查看>>
7、文档元素
查看>>
linux下的连接文件——软连接和硬连接的区别
查看>>
怎么查看linux文件夹下有多少个文件(mac同样)
查看>>
cacti监控一览无余
查看>>
第十六章--访问文件
查看>>
ASP.NET MVC学前篇之Ninject的初步了解
查看>>