数据类型
数据表基础
表逻辑设计
表的创建(CREATETABLE)
表结构的修改(ALTERTABLE)
表的删除、截断与重命名
创建与删除数据库
数据表也被称为表或基本表,是数据库最基本的用于存储数据的对象。可以认为关
系数据库中的数据表是以行和列组成的二维表格,通常人们将行称为记录,将列称为
字段。
本章将主要介绍数据库中的数据类型、表结构、逻辑设计、表的创建语句、修改表
结构的语句和数据库的创建语句等。
数据表
第章
SQL技术与网络数据库开发详解
·30·
4.1数据类型
在创建数据表时,需要用到数据类型。因此,在介绍创建表之前,本节将介绍一些SQL支持的数
据类型。
4.1.1字符型数据
字符型数据是数据库中最常用的数据类型之一,有时人们将其成为字符串。例如,一个存储学生
信息的表中,学生姓名、来源地、所属院系等都是字符型数据。字符型数据可由以下几类符号组成。
null字母:小写字母a~z与大写字母A~Z共52个。
null数字:0~9共10个。
null空白符:空格符、制表符、换行符等统称为空白符。空白符只在字符常量和字符串常量中起作
用。在其他地方出现时,只起间隔作用,编译程序对它们忽略。因此在程序中使用空白符与否,
对程序的编译不发生影响,但在程序中适当的地方使用空白符将增加程序的清晰性和可读性。
null标点、特殊字符与汉字:在数据表中允许存储标点、特殊字符与汉字。
在SQL语言中,字符型数据被放在单引号('''')中,用于区别其他类型的数据。例如,''home''、
''张三''、''047122813810''、''123_^''等都是字符型数据。每个字符型数据都有长度,其长度是该字符
型数据的字符个数,例如,''home''的长度为4,''047122813810''的长度为12等。不过,需要注意的是,
每个汉字占两个字符的位置,例如,''张三''的长度是4,而不是2。
注意:虽然电话号码047122813810看起来是数字,但因为将其放在了单引号内,所以是字符型数据。
这里所说的单引号,必须是英文输入法状态下的单引号。
存放字符型数据的变量被称为字符型变量。在数据库中有一种特殊的字符型变量——字符型字段
变量。由于还没有真正接触到字段的概念,因此关于字段变量将在后面的章节中进行讲解。
4.1.2数字型数据
数字型数据就是通常所说的数字,它可以由0~9之间的数字、正负符号与小数点(.)组成。例如,
100、23.234、-123、-58.42等都是数字型数据。数字型数据不允许放在任何定界符之内。数字型数据
除了上述形式以外,有时也可以用浮点形式的科学记数法表示,例如,3.46E+03等。在具体的数据库
系统中,数字型数据又被详细分为整数型数据、浮点型数据和货币型数据等。数字型数据与字符型数
据一样也有长度。例如,100的长度为3,23.234的长度为6(数字型数据长度包含小数点),-123的
长度为3等。
4.1.3日期时间数据
SQL中还有一种日期时间数据。例如,2009-03-1012:25:30、2010年09月15日、01/JAN/2010、
第4章数据表
·31·
22:30:10等。它们用来表示日期和时间。在不同的数据库系统中,日期时间数据的表示法有所不同。
在Access中,日期时间数据是被包含在井号(#)中的,例如#2009-03-10#。而SQLServer和Oracle
中并没有专门区别日期时间数据的定界符,因为它们可以识别日期时间格式的字符型数据。
4.1.4二进制数据
在计算机中所有数据都被保存为二进制数据,如前面介绍的字符型数据、数字型数据和日期时间
数据等,其实在计算机中都是以二进制数据的形式存放的。二进制数据还可以表示图形图像、视频动
画和其他类型的文件等。当前流行的所有数据库系统都支持二进制数据。
4.1.5图片、声音、视频数据
数据库中可以存储图片、声音、视频等多媒体数据,但需要相应数据类型的支持。例如,Access
中提供了OLE对象类型,用于存放多媒体数据,在SQLServer2000中提供了IMAGE数据类型,通常
用于存放图片等。
4.1.6自定义数据类型
除了数据库系统提供的数据类型以外,用户还可以根据自己的需要自定义数据类型。SQL中的
CREATETYPE就是用于自定义数据类型的语句。不过遗憾的是,并非全部的数据库系统都支持
CREATETYPE语句,例如,SQLServer2000中就不可以使用CREATETYPE语句定义用户数据类型。
4.1.7Access中的数据类型
Access中有10种数据类型,即字符、备注、数字、日期/时间、货币、自动编号、是/否、OLE对
象、超链接和查询向导。下面分别对常用的前7种数据类型进行介绍。
1.字符类型
用于存储字符型数据。该类型允许的字符型数据的最大长度是255。可以设置“字段大小”属性控
制可输入的最大字符长度。
Access中有两种字符数据类型,即VARCHAR和CHAR类型。VARCHAR数据类型用于存放可
变长度字符串,CHAR数据类型用于存放固定长度的字符串。例如,如下两条语句:
姓名VARCHAR(20)
和
姓名CHAR(20)
都声明了“姓名”是一个字符类型的字段,其后括号内的20代表了该字段中能够输入的最大长度。
SQL技术与网络数据库开发详解
·32·
说明:VARCHAR和CHAR的区别是:假设当“姓名”字段中最长的值为“孛尔吉济特”时,前者会
自动调整“姓名”字段的长度为10(一个汉字占两个字节的位置),而后者则仍旧保持字段长
度为20,在没有达到20长度的字段值后会自动添加空格,例如,因为姓名“张三”没有达到
20长度,因此,在其后添加16个空格。
技巧:虽然VARCHAR能够自动调整字段长度,以此达到节省空间的目的,但是,在查询检索方面,
查询CHAR类型的数据会比查询VARCHAR类型的数据更快。因此,应当将经常查询的字符字
段设置为CHAR类型。当然,如果更注重节省空间的话,应当使用VARCHAR类型。
2.备注类型
因为字符类型的最大长度是255,所以没办法存储更长的文字说明(例如,要存储一个人的简历)。
此时,便可以使用备注类型。该类型允许字段能够存储长达64000个字符的内容。但Access不能对备
注字段进行排序或索引。在备注字段中虽然可以搜索文本,但不如在有索引的字符字段中搜索得快。
Access中的备注类型被称为MEMO。例如,下面的语句声明了“简历”字段为备注类型的字段。
简历MEMO
注意:不能给MEMO类型的数据设置长度。
3.数字类型
用于存储数字型数据。Access中有6种数字数据类型。表4.1列出了这些数字数据类型及其说明。
表4.1Access中的数字数据类型
数字数据类型说明
BYTE(字节型)整数,从0~255
SMALLINT(整型)整数,大约为从-32000~32000
INTEGER(或NUMBER)(长整型)整数,大约为从-2000000000~2000000000
MONEY(或CURRENCY)
(货币型)整数和4个小数位。正负900000000000000
使用货币格式自动格式化
REAL(单精度型)浮点数字,大约为从1.4E-45~3.4E+38
FLOAT(双精度型)浮点数字,大约为从4.9E-324~1.8E+308
下面的语句声明了“基本工资”字段为货币类型。
基本工资MONEY
或
基本工资CURRENCY
4.日期/时间类型
用于存储日期值、时间值或日期时间混合值。Access中的日期/时间类型是DATETIME,例如,
下面的语句将设置“出生日期”字段为日期/时间类型。
出生日期DATETIME
这里需要注意的是,在向DATETIME类型的字段输入值时,必须遵循固定的日期/时间格式。
第4章数据表
·33·
表4.2中列出了可供选择的日期/时间类型与格式。
表4.2日期/时间类型与格式
类型格式
常规日期YYYY-MM-DDHH:MM:SS
长日期YYYY年MM月DD日
中日期YY-MM-DD
短日期YYYY-MM-DD
长时间HH:MM:SS
中时间下午HH:MM
短时间HH:MM
5.自动编号类型
此类型是一种特殊类型,每当向表格添加新记录时,Access会自动插入唯一顺序或者随机编号,
即在自动编号字段中指定某一数值。自动编号一旦被指定,就会永久地与记录连接。如果删除了表格
中含有自动编号字段的一个记录后,Access并不会为表格自动编号字段重新编号。当添加某一记录时,
Access不再使用已被删除的自动编号字段的数值,而是重新按递增的规律赋值。例如,下面的语句将
设置“编号”字段为自动编号类型。
编号COUNTER
6.是/否类型
针对于某一字段中只包含两个不同的可选值而设立的字段。例如,下面的语句将设置“婚否”字
段为是/否类型。
婚否YESNO
7.OLE对象类型
此类型的字段允许单独地“链接”或“嵌入”OLE对象,OLE对象字段最大可为1GB,主要受
磁盘空间限制。下面的语句将设置“照片”字段为OLE对象类型。
照片IMAGE
4.1.8MySQL中的数据类型
MySQL支持多种数据类型,可以分为数字类型、字符类型、日期和时间类型和复合类型4大类。
1.数字类型
nullTINYINT类型:可以存放-128~127之间的所有正负整数。该类型的数据,在内存中占用1
个字节的空间,即使用8位二进制数表示,其中的1位二进制数表示整数值的正负号,其他7
位表示整数值的长度和大小。
nullTINYINTUNSIGNED类型:无符号的TINYINT类型,可以存放0~255之间的所有整数。该
类型的数据也只占用内存中的1个字节。
SQL技术与网络数据库开发详解
·34·
nullSMALLINT类型:用于保存-32768~32767之间的所有正负整数。该类型的数据,在内存中占
用2个字节的空间。
nullSMALLINTUNSIGNED类型:无符号的SMALLINT类型,可以存放0~65535之间的所有整
数。该类型的数据,在内存中占用2个字节的空间。
nullMEDIUMINT类型:用于存储-8388608~8388607之间的所有正负整数。该类型的数据,在内
存中占用3个字节的空间。
nullMEDIUMINTUNSIGNED类型:无符号的MEDIUMINT类型,用于存放0~16777215之间的
所有整数。该类型的数据,在内存中占用3个字节的空间。
nullINT或INTEGER类型:用于存放-2147483648~2147483647之间的所有正负整数。该类型的
数据,在内存中占用4个字节的空间。
nullINTUNSIGNED或INTEGERUNSIGNED类型:无符号的INT或INTEGER类型,用于存
放0~4294967295之间的所有整数。该类型的数据,在内存中占用4个字节的空间。
nullBIGINT类型:用于存放-9223372036854775808~9223372036854775807之间的所有正负整数。
该类型的数据,在内存中占用8个字节的空间。
nullBIGINTUNSIGNED类型:无符号的BIGINT,可以存放0~18446744073709551615之间的所
有整数。该类型的数据,在内存中占用8个字节的空间。
nullFLOAT类型:用于存放数据范围为-3.402823466E+38~-1.175494351E-38,0,
1.175494351E-38~3.402823466E+38之间的浮点数。该类型的数据,在内存中占用4个字节的
空间。
nullDOUBLE或DOUBLEPRECISION或REAL类型:用于存放数据范围为
-1.7976931348623157E+308~-2.2250738585072014E-308,0,2.2250738585072014E-308~
1.7976931348623157E+308之间的浮点数。该类型的数据,在内存中占用8个字节的空间。
nullDECIMAL[(M,[D])]或NUMERIC(M,D)类型:由M(整个数字的长度,包括小数点、小数点
左边的位数、小数点右边的位数,但不包括负号)和D(小数点右边的位数)决定的数字数据
类型,M默认为10,D默认为0。
2.字符串类型
nullCHAR(M)[BINARY]或NCHAR(M)[BINARY]类型:用于保存定长的字符串,其中,M表示
字符串的最大长度,其范围为1~255,字符串中的每个字符占用1个字节的存储空间。默认
为BINARY,则表示不分大小写字母。NCHAR表示使用默认的字符集。当输入的字符串个数
小于M,则数据库系统将以空格补足,但在取出来时末尾的空格将自动去掉。
null[NATIONAL]VARCHAR(M)[BINARY]类型:用于存放变长的字符串,占用的存储空间范围
为0~255字节,M的取值范围为1~255。如果没有BINARY项,默认为BINARY,则表示
不分大小写字母。当输入的字符串个数小于M,则数据库系统将以空格补足,但在取出来时
末尾的空格将自动去掉。
nullTINYBLOB类型:用于保存不超过255个字符的二进制字符串,所占用的存储空间范围为0~
255字节。
nullTINYTEXT类型:用于存储短文字符串,所占用的存储空间范围为0~255字节。
第4章数据表
·35·
nullBLOB类型:用于存储二进制的长文本数据,所占用的存储空间范围为0~65535字节。
nullTEXT类型:用于存储长文本数据,所占用的存储空间范围为0~65535字节。
nullMEDIUMBLOB类型:用于存储二进制形式的中等长度的长文本数据,所占用的存储空间范
围为0~16777215字节。
nullMEDIUMTEXT类型:用于存储中等长度的长文本数据,所占用的存储空间范围为0~16777215
字节。
nullLONGBLOB类型:用于保存二进制形式的极大长度的长文本数据,所占用的存储空间范围为
0~4294967295字节。
nullLONGTEXT类型:用于保存极大长度的长文本数据,所占用的存储空间范围为0~4294967295
字节。
3.日期与时间类型
nullDATE类型:用于存储日期数据,日期数据的范围为1000-01-01至9999-12-31。每个DATE
类型的数据占用3字节的存储空间,其输入格式为“年-月-日(YYYY-MM-DD)”。
nullDATETIME类型:用于存储混合日期和时间数据,日期和时间数据的范围为1000-01-01
00:00:00至9999-12-3123:59:59。每个DATETIME类型的数据占用8字节的存储空间,其输
入格式为“年-月-日时-分-秒(YYYY-MM-DDHH:MM:SS)”。
nullTIME类型:用于存储时间数据或持续时间的数据,时间数据的范围为-838:59:59至838:59:59。
每个TIME类型的数据占用3字节的存储空间,其输入格式为“时-分-秒(HH:MM:SS)”。
nullYEAR类型:用于存储年份数据,年份的取值范围为1901~2155。每个YEAR类型的数据占
用1字节的存储空间,其输入格式为“年(YYYY)”。
nullTIMESTAMP类型:用于存储混合日期和时间值、时间戳,混合日期和时间数据、时间戳的范
围为1970-01-0100:00:00-2037年的某个时候。每个TIMESTAMP类型的数据占用8字节的
存储空间,其输入格式为“年-月-日时-分-秒(YYYY-MM-DDHH:MM:SS)”。
4.复合类型
nullENUM(''value1'',''value2'',…)类型:用于存储从预先定义的字符集合中选取互斥的数据值,可
以有65535个不同的值。
nullSET(''value1'',''value2'',…)类型:用于存储从预先定义的字符集合中选取任意数目的值,最多
有64个成员。
4.1.9SQLServer中的数据类型
SQLServer中的数据类型非常丰富,下面列出了常用的几种数据类型供读者参阅。
1.整数数据类型
整数数据类型是较常用的数据类型之一。
nullINT(INTEGER)数据类型:用于存放-2147483648~2147483647之间的所有正负整数。该类
型的数据,在内存中占用4个字节。
nullSMALLINT数据类型:用于存放-32768~32767之间的所有正负整数。该类型的数据,在内存
SQL技术与网络数据库开发详解
·36·
中占用2个字节。
nullTINYINT数据类型:用于存放0~255之间的所有整数。该类型的数据,在内存中占用1个
字节。
nullBIGINT数据类型:用于存放-9223372036854775808~9223372036854775807之间的所有正负
整数。该类型的数据,在内存中占用8个字节。
2.浮点数据类型
浮点数据类型也是比较常用的数据类型之一。该数据类型用于存放带有小数点的数值。
nullDECIMAL[p[s]]数据类型:用于存放浮点数据,其精度非常高,可以保留到浮点数据的最小
有效数字,但是也有一定限制,详细内容请参阅相关SQLServer的书籍。这里的p代表浮点
数的总位数,但是不包括小数点;s代表小数点后的位数。
nullNUMERIC数据类型:与DECIMAL数据类型基本相同,有关详细区别请参阅相关书籍。
nullREAL数据类型:用于存放精度在1~7之间的浮点数。该类型数据的范围是-3.40E-38~3.40E
+38。
nullFLOAT数据类型:用于存放精度在8~15之间的浮点数。该类型的数据的范围是-1.79E-308~
1.79E+308。
3.二进制数据类型
nullBINARY(n)数据类型:用于存放二进制数据。其中,n表示数据的长度,取值范围为1~8000。
nullVARBINARY(n)数据类型:与BINARY类型基本相同。不同的是该数据类型存放可变长度
二进制数据。
4.字符数据类型
可以说字符数据类型是所有数据类型中使用最多的数据类型。SQLServer中有两种常用字符数据
类型,分别是VARCHAR和CHAR类型。VARCHAR数据类型存放可变长度字符串,CHAR数据类
型存放固定长度的字符串。例如,如下两条语句:
姓名VARCHAR(20)
和
姓名CHAR(20)
都声明了“姓名”是一个字符类型的字段,其后括号内的20代表该字段中能够输入的最大长度。
说明:VARCHAR和CHAR的区别是,假设当“姓名”字段中最长的值为“孛尔吉济特”时,前者会
自动调整“姓名”字段的长度为10(一个汉字占两个字节的位置),而后者则仍旧保持字段长
度为20。在没有达到20长度的字段值后会自动添加空格,例如,因为姓名“张三”没有达到
20长度,因此,在其后添加16个空格。
技巧:虽然VARCHAR能够自动调整字段长度,以此达到节省空间的目的,但是,在查询检索方面,
查询CHAR类型的数据会比查询VARCHAR类型的数据更快。因此,应当将经常查询的字符字
段设置为CHAR类型。当然,如果更注重节省空间的话,应当使用VARCHAR类型。
第4章数据表
·37·
5.文本和图形数据类型
SQLServer中常用的文本和图形数据类型是TEXT和IMAGE类型。
nullTEXT数据类型:用于存放大量的文本数据。
nullIMAGE数据类型:用于存放大量的二进制数据,通常用来存储图像。
6.日期和时间数据类型
nullDATETIME数据类型:用于存放日期时间数据,可以说是日期和时间的组合。其数据格式为
“YYYY-MM-DDHH:MM:SS”。该类型数据的日期时间范围是,公元1753年1月1日0时~
公元9999年12月31日23时59分59秒,其精度为百分之三秒。
nullSMALLDATETIME数据类型:与DATETIME数据类型相似,但是精度只能精确到分钟,其
日期时间范围是1900年1月1日~2079年6月6日。
7.货币数据类型
nullMONEY数据类型:实际上,该类型的数据是一种特殊的DECIMAL数据,它有4位小数。
该类型的范围是-922337203685477.5808~+922337203685477.5807,数据精度为万分之一货
币单位。
nullSMALLMONEY数据类型类:与MONEY类型相似,但是其取值范围是-214748.3648~
+214748.3647。
4.1.10Oracle中的数据类型
Oracle中有许多种数据类型,其基本类型可分为字符数据类型、数字据类型、日期时间类型、
LOB、RAW等5大类型。
1.字符数据类型
nullCHAR类型:用于存放字符串数据,定义形式为CHAR[(n)]。以CHAR类型存放的字符串中
的每个字符和符号占用一个字节的存储空间。n表示所有字符所占的存储空间,n的取值范围
为1~2000,即最多可容纳2000个字符。若不指定n值,则系统默认值为1。如果所输入的字
符串的字符个数小于n,则系统自动在实际字符串后添加空格来填满设定好的空间,但在取出
来时末尾的空格将自动去掉。若输入的数据过长,将会截掉其超出部分。
nullVARCHAR2类型:用于存放可变长的字符串,具体定义时指明最大长度n,这种数据类型可
以放数字、字母以及ASCII码字符集(或者EBCDIC等数据库系统接受的字符集标准)中的
所有符号。如果数据长度没有达到最大值n,Oracle会根据数据大小自动调节字段长度,如果
数据前后有空格,Oracle会自动将其删去。
nullLONG型:用来存放可变长度的字符串数据,最多能存储占用2GB空间的文本数据。需要注
意的是在一个表中只能有一字段可以为LONG型,并且LONG类型的字段不能被定义为主键
或唯一约束,也不能使用LONG类型的字段建立索引,过程或存储过程不能接受LONG数据
类型的参数。
SQL技术与网络数据库开发详解
·38·
2.数字数据类型
NUMBER类型用于存放可变长的数值,允许正负值和0值,格式为NUMBER(P,S),其中,P表
示数据的总长度,取值范围为1~38,S表示小数的位数,取值范围为-84~127之间的数字。如NUMBER
(8,2),则这个字段的总长度是8,可以有2位小数,如果数值超出了位数限制,多余的位数就会被截取。
例如,NUMBER(6,3),输入45.12378,则保存到字段中的数值是45.124;又例如,NUMBER(4,0),
输入1565.316,真正保存的数据是1565。
3.日期和时间类型
nullDATE类型:用于存放日期和时间数据,该数据类型的范围是,公元前4712年1月1日~公
元9999年12月31日。
nullTIMESTAMP类型:与DATE基本相同,但是可以返回当前的时区。
4.RAW数据类型
此类数据类型主要用于存储二进制数据。
nullRAW类型:用于存放基于字节的二进制数据,最多能存放2000个字节,没有默认大小,所
以在使用时要指定大小,可以建立索引。
nullLONGRAW类型:用于存放可变长度的二进制数据,最多能存放2GB,受的限制和LONG类
型一样。
5.LOB数据类型
主要有CLOB、BLOB和BFILE3种子类型。
nullCLOB类型(CHARACTERLOB):用于存放大量字符数据,可以存放非结构化的XML文档。
nullBLOB类型(BINARYLOB):可以存放较大的二进制对象,如图形、音视频剪辑等数据。
nullBFILE类型(BINARYFILE):能够将二进制文件存放在数据库外部的操作系统文件中,BFILE
字段存储一个BFILE定位器,指向位于服务器文件系统上的二进制文件,支持的文件最大为
4GB。
4.2数据表基础
数据表又被称为表。在关系型数据库系统中,一个关系就是一个表,表结构指的就是数据库的关
系模型。表是若干列(Column)和若干行(Row)的集合,每一行代表一个唯一的记录,每一列代表
一个字段。在确定表结构时首先要定义表的字段,即定义字段名、数据类型及其宽度,其次输入行
(记录)。
4.2.1记录和字段
关系数据库中的数据表,其实很像人们生活中的二维表格,甚至有人会说它就是二维表格。数据
表由行和列组成,通常人们将行称为记录,而将列称为字段,如图4.1所示。
第4章数据表
·39·
图4.1数据表
每个字段中的数据必须具有相同的数据类型,且每个字段都有字段名,如图4.1中的“学号”、“姓
名”等就是字段名。关系数据库中规定,在同一个表内不能有重复的字段。实际上,表内也不应该有
重复的记录,只是多数数据库管理系统不会强制这点而已。
说明:有些专家认为数据表的行和列不应该称为记录和字段,而应当称为行(Row)和列(Column)。
4.2.2表结构
一个非空数据表实际上由两部分组成,分别是表结构和其内的数据。可以认为表结构由表中所有
字段的字段信息组成,这些信息包括字段名、字段类型、字段大小和字段约束、表约束等信息。创建
一个数据表,其实就是在创建其表结构。因此,在创建表时必须告诉DBMS,表包括哪些字段,每个
字段的数据类型和大小等。例如,观察下面创建表的SQL语句,就会发现这一点。
CREATETABLEtest
(
学号char(4),
姓名char(20),
);
该SQL语句创建一个有两个字段的数据表Test,两个字段的字段名分别为“学号”和“姓名”,
数据类型都是字符型,长度分别为4和20。
4.3表逻辑设计
数据表的设计是数据库设计的主要部分。表逻辑设计的好坏将会影响数据库系统最终的运行效果、
数据安全以及完整性等问题。对于数据库系统开发人员来说,必须将表的逻辑结构设计得尽量完美,
因为开发人员与最终用户看待数据的方式不一样。表的逻辑结构设计必须满足用户的需求,能使用户
准确理解数据的本质和容易掌握,并且没有二义性。E-R模型将帮助系统开发人员能很好地完成表逻
辑设计。
4.3.1E-R模型图
E-R是Entity-Relationship的缩写,即实体-关系。E-R模型是一种自上而下的数据库设计方法。一
SQL技术与网络数据库开发详解
·40·
个完整的数据库系统的E-R模型图是由若干局部E-R模型图组合而成的。
1.局部E-R模式设计
在E-R方法中将局部概念结构图称为局部E-R模型图。局部E-R模式的设计过程如图4.2所示。
图4.2局部E-R模式的设计过程
例如,学校的综合数据库中的教师管理部分与课程管理的局部E-R模型图,如图4.3与图4.4所示。
图4.3教师管理局部E-R图图4.4课程管理局部E-R图
2.合并局部E-R模型图
合并方法有两种:一种是一次合并多个局部E-R模型图;另一种是逐步合并局部E-R模型图,如
图4.5与图4.6所示。由于一次合并法方法复杂而难度大,所以常用的合并法是逐步合并法。
无论采用哪种方法,合并局部E-R模型图的准则是先解决局部E-R模型图的冲突,合并成初步E-R
模型图。其次是进行初步E-R模型图的优化与修改,而最终得到全局E-R模型图。
例如,合并教师管理与课程管理局部E-R模型图后,得到的E-R模型图如图4.7所示。
第4章数据表
·41·
图4.5一次合并图4.6逐步合并
图4.7合并后的E-R图
4.3.2规范化与范式
规范化是一种用来产生数据表集合的技术,通过规范化表将具有符合用户需求的属性。规范化通
常作为对表结构的一系列测试来决定其是否满足和符合给定范式。数据库逻辑结构设计产生的结果应
该满足规范化要求,以使关系模式的设计合理,达到冗余少和提高查询效率的目的,所以对数据库进
行规范化非常重要。对数据库的规范化先要确定规范化级别,然后按要求进行并且要达到这一级别。
一般情况下,规范化处理主要进行以下3个步骤:
(1)确定数据依赖:通过数据依赖表示出数据项之间的关系。此项工作在需求分析阶段完成。
(2)定义键,并消除冗余的关系:此项工作在概要设计阶段完成。
(3)确定范式级别:规范化必须要达到范式级别。
范式,简称NF(NormalForm),是满足一定条件的关系模式。范式是规范化确定的级别,数据
库设计的范式有多种,常用的有第一范式(1NF)、第二范式(2NF)和第三范式(3NF)。所有范式
都基于数据表中的字段之间的关系。
null第一范式:若关系模式P的所有属性的值域中每个值都是不可再分解的值,则称P为第一范
式。第一范式是最低的规范化要求,数据表不能存在相同的记录,需设定一个关键字,并且要
求每个字段都不可再分解。
null第二范式:若关系模式P是第一范式,P的表以及每个非主键字段都可以由构成主键的全部字
段得到,则称P为第二范式。第二范式可以消除大量的冗余数据,并对数据表可以进行异常的
SQL技术与网络数据库开发详解
·42·
插入和删除。
null第三范式:若关系模式P是第二范式,且每个非主属性都不传递依赖于P的候选键,则称P
是第三范式。第三范式的关系不具有多义性,其属性值唯一,且每个非主属性必须依赖于整个
主键而不能依赖于其他关系中的属性。
4.4表的创建(CREATETABLE)
SQL语言中创建表将用CREATETABLE语句来实现。CREATETABLE语句可以定义各种表的结
构、约束以及继承等内容。
4.4.1使用CREATETABLE语句创建表
CREATETABLE将在当前数据库创建一个新的、初始为空的数据表,该表将由发出此命令的用户
所有。下面是CREATETABLE语句的基本语法格式。
CREATETABLE<表名>
(
<字段名1><数据类型>[NOTNULL][DEFAULT<默认值>],
[<字段名2><数据类型>[NOTNULL][DEFAULT<默认值>],
……
<字段名n><数据类型>……
);
具体说明如下:
nullNOTNULL:为可选项,如果在某字段后加上该项,则向表添加数据时,必须给该字段输入内
容,即不能为空。
nullDEFAULT<默认值>:为可选项,如果在某字段后加上该项,则向表添加数据时,如果不向该
字段添加数据,系统就会自动用默认值填充该字段。
下面通过一个例题介绍CREATETABLE语句的使用法。
【例4.1】创建一个Student表,设置其学号、姓名和性别3个字段不能为空,并且给性别字段指
定默认值为“男”。
运行环境为Access或SQLServer时,其创建语句如下所示。
CREATETABLEstudent
(
学号char(4)NOTNULL,
姓名char(20)NOTNULL,
性别char(2)NOTNULLDEFAULT''男'',
出生日期datetime,
来源地varchar(50),
联系方式1char(12),
联系方式2char(12),
第4章数据表
·43·
所属院系char(20)
);
运行环境为Oracle时,其创建语句如下所示。
CREATETABLEstudent
(
学号char(4)NOTNULL,
姓名char(20)NOTNULL,
性别char(2)NOTNULL,
出生日期date,
来源地varchar2(50),
联系方式1char(12),
联系方式2char(12),
所属院系char(20)
);
注意:Oracle的有些数据类型名称和Access、SQLServer的数据类型名称不同。例如,上面语句中的
日期时间型,在Oracle中是date,而在Access和SQLServer中是datetime;又例如,变长字符
型在Oracle中使用的是varchar2,而在Access和SQLServer中是varchar。因此,在创建数据表
时应当注意所使用的数据库管理系统中的数据类型名称。
4.4.2创建带有主键的表
在数据表中能够唯一识别记录的字段,都会被人们设置为主键,例如,“学号”字段。当某个字
段被设置为主键后,该字段中就不能再有重复值,也不能有空值,数据库管理系统将强制执行这一规
则,这就是主键约束。在创建数据表时,设置主键的方法有两种,下面通过例题介绍具体的方法。
【例4.2】创建以“学号”字段作为主键的Student表。设SQL运行环境为SQLServer。
方法一:
CREATETABLEstudent(
学号char(4)CONSTRAINTfirstkeyPRIMARYKEYNOTNULL,
姓名char(20)NOTNULL,
性别char(2)NOTNULL,
出生日期datetime,
来源地char(50),
联系方式1char(12),
联系方式2char(12),
所属院系char(20)
);
方法二:
CREATETABLEstudent(
学号char(4)NOTNULL,
姓名char(20)NOTNULL,
性别char(2)NOTNULL,
出生日期datetime,
来源地char(50),
SQL技术与网络数据库开发详解
·44·
联系方式1char(12),
联系方式2char(12),
所属院系char(20),
CONSTRAINTxhPRIMARYKEY(学号)
);
说明:在运行上面的语句时,可能会因为student表已经存在而失败。此时,应当先将现有的student
表删除,然后再执行上面的创建语句。删除student表的SQL语句如下所示。
DROPTABLEstudent;
如果想设置多个字段为主键,则必须使用上例的方法二。例如,要创建一个存放学生多门成绩的
Score表,此时,其主键应该是“学号”和“课号”两个字段的联合,因为只有学号和课号联合起来,
才能识别唯一记录。
【例4.3】创建Score表,并设置“学号”和“课号”两个字段为联合主键。
CREATETABLEscore
(
学号char(4),
课号char(3),
考试成绩decimal(6,2),
平时成绩decimal(6,2),
CONSTRAINTxh_khPRIMARYKEY(学号,课号)
);
注意:表中的主键必须设置为NOTNULL。
4.5表结构的修改(ALTERTABLE)
在数据库操作时,可能需要更改表结构,例如,修改某字段的数据类型、添加新字段、删除指定
字段等。ALTERTABLE语句可以完成这些要求,本节将介绍该语句的详细用法。
4.5.1ALTERTABLE语句格式
使用ALTERTABLE语句可以修改字段的类型和长度,可以添加新字段,还可以删除不需要的字
段等。下面分别介绍使用ALTERTABLE修改字段、添加字段和删除字段的语法格式。
1.修改字段的语法格式
ALTERTABLE表名
ALTERCOLUMN
字段名数据类型[(长度)];
具体说明如下:
null字段名:需要修改的字段名称。
null数据类型:需要修改字段的新数据类型。
第4章数据表
·45·
null长度:需要修改字段的长度。该项为可选项,当需要修改的字段类型为带长度的数据类型时必
须定义其长度,例如,字符类型。
注意:在Oracle中,修改字段的语法格式为:
ALTERTABLE表名
MODIFY
字段名数据类型[(长度)];
2.添加字段的语法格式
ALTERTABLE表名
ADD
字段名数据类型[(长度)];
具体说明如下:
null字段名:需要添加的字段名称。
null数据类型:需要添加字段的数据类型。
null长度:需要添加字段的长度。其余说明与上面的相同。
3.删除字段的语法格式
ALTERTABLE表名
DROPCOLUMN
字段名;
具体说明如下:字段名为要删除的字段名。
注意:使用ALTERCOLUMN时要更改的字段不能是:数据类型为text、image、ntext或timestamp的
字段、表的ROWGUIDCOL字段、计算字段或用于计算字段中的字段、被复制字段、用在索引
中的字段。
4.5.2增加新字段
前面介绍了使用ALTERTABLE语句增加新字段的语法格式,下面通过例题说明其具体用法。
【例4.4】在SQLServer的Student表中,增加新字段“政治面貌”,该字段的类型为字符型,长
度为10。其SQL语句如下所示。
ALTERTABLEstudent
ADD
政治面貌char(10);
运行上面的语句后,通过SQLServer的企业管理器查看Student表结构,如图4.8所示。从图中看
到“政治面貌”字段已经被添加到Student表内。
【例4.5】在Oracle的Student表中,增加新字段“政治面貌”。
ALTERTABLEstudent
ADD
政治面貌char(10);
SQL技术与网络数据库开发详解
·46·
使用下面的语句查看Student的表结构。
DESCstudent
说明:DESCRIBE命令用于查看表定义。使用全称DESCRIBE或缩写DESC均可。
运行结果如图4.9所示。
图4.8添加“政治面貌”字段后的表结构
图4.9添加“政治面貌”字段后的表结构
观察上面的两个例题会发现,Oracle和SQLServer的添加新字段的SQL语句是相同的,因此,本
书约定如果语句相同,则只用SQLServer举例。
注意:在Oracle中,必须在语句末尾添加分号(;),而在SQLServer中分号是可有可无的。
4.5.3增加带有默认值的新字段
在使用ALTERTABLE语句添加新字段的同时,也可以给该字段设置默认值。
【例4.6】在Student表中,添加新字段“婚否”的同时,给其设置默认值“否”。
第4章数据表
·47·
ALTERTABLEstudent
ADD
婚否char(2)DEFAULT''否'';
运行上面的语句后,使用SQLServer的企业管理器查看表结构,如图4.10所示。
图4.10添加“婚否”字段后的表结构
4.5.4修改字段的类型和宽度
ALTERTABLE语句形式可以改变字段的数据类型和宽度。满足以下情况的字段是不可以更改其
数据类型的:
null数据类型为TEXT、IMAGE、NTEXT或TIMESTAMP的字段;
null有UNIQUE约束的字段。
null设置默认值的字段。
null重复的字段。
null计算的或用在计算的字段中。
null用于CHECK约束的字段。
【例4.7】将Student表的字段“政治面貌”的数据类型改变为变长字符型,宽度为6。
(1)如果运行环境为SQLServer,则其语句如下所示。
ALTERTABLEStudent
ALTERCOLUMN
政治面貌varchar(6);
运行上面的语句后,使用SQLServer的企业管理器查看表结构,如图4.11所示。
(2)如果运行环境为Oracle,则其语句如下所示。
ALTERTABLEStudent
MODIFY
政治面貌varchar2(6);
SQL技术与网络数据库开发详解
·48·
使用下面的语句查看Student的表结构。
DESCstudent
运行结果如图4.12所示。
图4.11修改“政治面貌”字段后的表结构
图4.12修改“政治面貌”字段后的表结构
注意:将字段的当前数据类型转换为另一种数据类型时,字段中当前已有的数据必须与新数据类型相
互兼容。
4.5.5删除字段
SQL语句为ALTERTABLE语句提供了DROPCOLUMN子句来完成删除数据表中的字段。
【例4.8】从Student表中删除字段“政治面貌”与“婚否”。
ALTERTABLEstudent
DROPCOLUMN政治面貌;
第4章数据表
·49·
运行上面的语句后,使用SQLServer的企业管理器查看表结构,这时字段“政治面貌”已不存在,
如图4.13所示。
图4.13删除字段“政治面貌”后的表结构
4.5.6删除带有默认值的字段
ALTERTABLE…DROPCOLUMN语句形式不能删除数据表中有主键约束和默认值的字段。
【例4.9】使用ALTERTABLE…DROPCOLUMN语句直接删除Student表的“婚否”字段。
ALTERTABLEstudent
DROPCOLUMN婚否;
运行结果如图4.14所示。运行出错的原因是“婚否”字段被设置了默认值。
图4.14删除字段“婚否”的操作失败
注意:在图中的对象‘DF__student__婚否__05D8E0BE’是字段“婚否”的默认值名称。实际上,这
个名称并不是在例4.7中声明的,是系统自动为字段“婚否”的默认值生成的。
为了删除带有约束和默认值的字段,必须先删除约束和取消默认值,其语法格式如下所示。
ALTERTABLE表名
DROPCONSTRAINT约束名|默认值名
【例4.10】使用ALTERTABLE语句删除Student表的“婚否”字段。
SQL技术与网络数据库开发详解
·50·
(1)删除“婚否”字段的默认值。
ALTERTABLEstudent
DROPCONSTRAINTDF__student__婚否__05D8E0BE
(2)执行删除“婚否”字段的语句如下。
ALTERTABLEstudent
DROPCOLUMN婚否;
运行结果如图4.15所示。
图4.15删除字段“婚否”的语句成功执行
4.5.7更改主键
使用ALTERTABLE…ADD语句形式可以给数据表添加主键(PRIMARYKEY)约束。添加主键
约束的语法格式如下所示。
ALTERTABLE表名
ADDCONSTRAINT主键约束名
PRIMARYKEY(<字段名1>
[,<字段名2>,…])
说明:主键约束名由用户指定,PRIMARYKEY子句可以设置联合主键约束。
【例4.11】为Student表中的“学号”字段添加主键约束。
ALTERTABLEstudent
ADDCONSTRAINTxh_1
PRIMARYKEY(学号);
如果需要改变数据表当前的主键约束时,则应当先删除其当前的主键约束,然后再使用上述方法
添加新的主键约束,例如下面的例题所示。
【例4.12】将上例中设置的Student表中的“学号”字段的主键约束改变为“学号”与“姓名”字
段的联合主键约束。
(1)删除当前主键约束。
ALTERTABLEstudent
DROPCONSTRAINTxh_1;
第4章数据表
·51·
(2)添加字段联合主键约束。
ALTERTABLEstudent
ADDCONSTRAINTxh_xm
PRIMARYKEY(学号,姓名)
注意:被设置主键约束的字段必须设置NOTNULL约束。
4.6表的删除、截断与重命名
对表可进行删除与重命名操作,SQL语言提供了DROPTABLE语句进行表删除操作,提供了
RENAMETABLE语句进行表重命名操作。
4.6.1删除表
当不再需要数据库中的某表时,就应当删除该表,释放该表所占有的资源。在SQL语言中,删除
数据表使用DROPTABLE语句。例如,下面的语句用于删除Student表。
DROPTABLEstudent;
说明:有时,在使用DROPTABLE语句删除数据表时会出现删除失败的情况。导致删除失败的绝大多
数原因是该表可能与数据库中的其他表存在联系。此时,应当先解除表之间的联系,然后再使
用DROPTABLE语句删除表。
4.6.2截断表
使用DROPTABLE语句会将表彻底地删除掉,包括表内的数据和表本身。但有时,用户可能希望
只删除表中的数据,而不删除表本身。这时可以使用TRUNCATE语句将表截断,即删除其中的所有
数据。例如,下面的语句将截断Student表。
TRUNCATETABLEstudent;
注意:使用SQL语言中的DELETE语句也能删除表中的所有数据,但是使用TRUNCATE语句会得到
更快的速度,而且在Oracle中,TRUNCATE语句会重置表的存储空间。关于DELETE语句和
TRUNCATE语句的具体内容与差别,可以查看本书第15章的内容。
4.6.3重命名表
表的名称在创建时便被赋予了,但是,后期可能会因为各种原因需要重命名表。重命名表在Oracle
中可以使用RENAME语句完成。在SQLServer中,则要使用SP_RENAME完成。例如,要将表Student
重命名为Stu_info,则在Oracle和SQLServer中使用的语句分别如下所示。
(1)在Oracle中重命名Student表。
RENAMEstudentTOstu_info;
SQL技术与网络数据库开发详解
·52·
(2)在SQLServer中重命名Student表。
SP_RENAMEstudent,stu_info
4.7创建与删除数据库
在创建数据库对象之前,必须先创建数据库。数据库中包含数据表、视图、索引、查询、规则、
默认值等数据库对象,并且对这些对象进行同一管理。
4.7.1创建数据库
在面向对象的关系型数据库管理系统中,一般情况下用户使用环境中的工具创建数据库,如SQL
Server2000中可以使用企业管理器新建一个数据库,其操作方法简单且方便。用户也可以使用SQL语
言中的CREATEDATABASE语句创建数据库,其基本语法格式如下所示。
CREATEDATABASE;
【例4.13】在SQLServer中,使用CREATEDATABASE语句创建一个Test数据库。
CREATEDATABASEtest;
运行结果如图4.16所示。
图4.16创建Test数据库
启动企业管理器,从中可以查看创建数据库的结果,如图4.17所示。
图4.17创建数据库的结果
第4章数据表
·53·
4.7.2删除数据库
删除数据库可以使用DROPDATABASE语句。其简单语法格式如下所示。
DROPDATABASE;
【例4.14】在SQLServer中,使用DROPDATABASE语句删除Test数据库。
DROPDATABASEtest;
运行结果如图4.18所示。
图4.18删除Test数据库
通过企业管理器,查看删除数据库的结果,如图4.19所示。图中已经找不到Test数据库,这表明
数据库被成功地删除了。
图4.19删除数据库的结果
4.7.3创建本书使用的数据表
本书多数实例使用了College数据库的Student、Teacher、Course和Score等数据表。下面列出创
建这些数据表的SQL语句,供读者参考。
1.创建Student表的SQL语句
(1)SQLServer或Access环境。
CREATETABLEStudent
SQL技术与网络数据库开发详解
·54·
(
学号char(4)NOTNULL,
姓名char(20)NOTNULL,
性别char(2)NOTNULL,
出生日期smalldatetime,
来源地varchar(50),
联系方式1char(12),
联系方式2char(12),
所属院系char(20),
PRIMARYKEY(学号)
);
(2)Oracle环境。
CREATETABLEStudent
(
学号char(4)NOTNULL,
姓名char(20)NOTNULL,
性别char(2)NOTNULL,
出生日期date,
来源地varchar2(50),
联系方式1char(12),
联系方式2char(12),
所属院系char(20),
PRIMARYKEY(学号)
);
2.创建Teacher表的SQL语句
(1)SQLServer或Access环境。
CREATETABLETeacher(
教工号char(6)NOTNULL,
姓名char(20)NOTNULL,
性别char(2)NOTNULL,
年龄integer,
职称char(8),
PRIMARYKEY(教工号)
);
(2)Oracle环境。
CREATETABLETeacher
(
教工号char(6)NOTNULL,
姓名char(20)NOTNULL,
性别char(2)NOTNULL,
年龄number,
职称char(8),
PRIMARYKEY(教工号)
);
第4章数据表
·55·
3.创建Course表的SQL语句
(1)SQLServer或Access环境。
CREATETABLECourse
(
课号char(3)NOTNULL,
课名char(30)NOTNULL,
类型char(10)NOTNULL,
学分integerNOTNULL,
PRIMARYKEY(课号)
);
(2)Oracle环境。
CREATETABLECourse
(
课号char(3)NOTNULL,
课名char(30)NOTNULL,
类型char(10)NOTNULL,
学分numberNOTNULL,
PRIMARYKEY(课号)
);
4.创建Score表的SQL语句
(1)SQLServer或Access环境。
CREATETABLEscore
(
学号char(4)REFERENCESstudent(学号)NOTNULL,
课号char(3)REFERENCEScourse(课号)NOTNULL,
考试成绩decimal(9,2),
平时成绩decimal(9,2),
);
(2)Oracle环境。
CREATETABLEscore
(
学号char(4)REFERENCESstudent(学号)NOTNULL,
课号char(3)REFERENCEScourse(课号)NOTNULL,
考试成绩number(9,2),
平时成绩number(9,2),
);
说明:如果考虑国际标准化、书写方便等因素,在命名字段时应当尽量使用英文或英文缩写。本书为
了方便读者阅读,所以使用了汉字命名字段。
|
|