第3章数据表及其应用

第3章SQL中的表及其应用3.1表的基础知识3.2表的创建3.3表的操作3.4数据完整性控制第3章SQL中的表及其应用教学重点：（1）T-SQL的数据类型（2）使用对象资源管理器实现表的创建和操作（3）使用T-SQL语言实现表的创建和操作3.1表的基础知识3.1.1表的定义3.1.2数据类型3.1.3约束3.1.1表的定义表是一系列行和列的集合。在表中，数据成二维行列格式，每一行代表一个惟一的记录，每一列代表一个域。1．表的概念（1）关系数据库中的所有数据存储在表对象中，表是数据库中最重要的对象，每个表代表着一个实体集或实体集之间的联系。（2）创建一个表最有效的方法是将表中所需的信息一次定义完成，包括数据约束和附加成份。也可以先创建一个基础表，向其中添加一些数据并使用一段时间，再根据应用情况定义其它成份。（3）在SQLServer中创建表有如下限制：①每个数据库里最多有20亿个表；每个表最多可以包含1024个字段；每条记录最多可以占用8060字节，但不包括text字段和image字段。②每个表上最多可以创建一个聚集索引和249个非聚集索引；2．创建表的步骤（1）定义表结构：确定表的各字段的名称，并为每个字段确定数据类型、数据长度、是否可以为空等。（2）设置约束：设置约束是为了限制字段输入值的取值范围，以保证数据的完整性。（3）添加数据：表结构建立完成之后，就可以向表中输入数据了。

3.1.2T-SQL的数据类型

数据类型是指数据所代表信息的类型，是数值型、字符型、日期型，还是货币型、图像型等其它的类型数据，它关系到SQLServer2005系统在内存或磁盘上开辟相应的存储空间。定义表的各字段时必须说明该字段的数据类型，数据类型定义了该字段可接受的数据值及其占用的内存空间，同时也决定访问、显示、更新数据的方法。T-SQL的数据类型包括系统数据类型及用户自定义的数据类型两大类SQLServer的九种数据类型

1）整型数据类型(Integer)2）精确数字数据类型(ExactNumeric)3）近似数字数据类型(ApproximateNumeric)4）货币数据类型(Money)5）日期和时间数据类型(DateTime)6）字符数据类型(Character)7）二进制数据类型8）特殊数据类型9）自定义数据类型(UDT)字符型数据类型主要用来存储由字母、数字和符号组成的字符串。SQLServer提供了三种字符型数据类型：

char、varchar和text。char用于存储长度固定的字符串；varchar用于存储长度可变的字符串；text用于存储无限长的字符串(每行可达2GB)。系统数据类型--字符型系统数据类型--字符型

字符型对每个字符都使用一个字节进行存储：

char[(n)]，n的取值为1～8000，固定长度的字符串，长度从1到8000，如果省略了长度，则缺省值为1。存储字节数为N字节，若实际输入的字符个数不足N个，在输入的数据后面填充空格补足到N个

varchar[(n)]，n的取值为1～8000。可变长度的字符串，最大长度从1到8000，如果省略了长度，则缺省值为1。存储字节数为输入的实际字符长度，不会填充额外的空格。系统数据类型--字符型

text数据类型用于存储数据量庞大且变长的字符文本数据，如简历信息等。最多达到2GB，行中存储指向第一个数据页的指针，实际文本以b-树数据页来存储。不能使用”=”运算，也不能使用很多系统函数。Varchar（Max）：SQLServer2005引入代替text数据类型，最多可存储2GB数据，对使用Varchar（Max）数据类型的操作或函数没有限制二进制型是指数据是由二进制值组成，如程序代码或图像数据。SQLServer提供了三种二进制数据类型：binary、varbinary和image：

binary用于存储长度固定的二进制字符串；

varbinary用于存储长度可变的二进制字符串；

image用于存储大的字符串。系统数据类型--二进制型系统数据类型--二进制binary型数据类似于字符型数据，当实际的二进制数据长度小于定义长度时，binary类型会在实际数据的尾部添加二进制的0，而不是空格。image型数据的存储长度为0~231-1个字节，可以用来存储照片、图片或者图画。SQLServer支持两种日期时间数据类型：

datetime和smalldatetime：datetime类型用于存储从1753年1月1日到9999年12月31日的日期和时间数据，精确到3%秒（或30毫秒）。存储长度为8字节，日期和时间各用4字节存储。第一个四字节存储自1900年1月1日之前或之后的天数（1900年1月1日之前的日期天数小于0，1900年1月1日之后的日期天数大于0），另外一个4字节存储以午夜（00:00:00.000）后毫秒数所代表的每天的时间系统数据类型--日期型系统数据类型--日期型smalldatetime类型用于存储从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。它只需4个字节的存储空间，第一个2字节存储自1900年1月1日之后的天数，另外一个2字节存储以午夜（00:00:00.000）后的分钟数。精度小于datetime类型，包含的日期范围较窄，但占用的空间小。对于定义为日期时间数据类型的字段，并不需要同时输入日期和时间，可省略其中的一个。如果只指定时间，则日期默认为1900年1月1日；如果只指定日期，则时间默认为12：00AM默认情况下，日期型数据的格式是按照“月/日/年”的顺序来设定的。系统数据类型--日期型英文+数字：月份可用英文命名或缩写，不区分大小写，年月日之间可不用逗号。年份可4位或两位。若为两位，小于50为2000年后的年份，大于或等于50则为1900年后的年份，若日部分省略，则为当月1日。如：‘1980mar5’数字+分隔符可使用斜杠(/)、连字符(-)或句号(.)作为分隔符来指定月、日、年。如：‘4/15/1998’,‘1978-04-15’,’1998.04.15’、常见的日期类型输入格式：整型数据类型存储不带小数的精确数字，包括年龄、数量等，有4种整数数据类型用于存储不同范围的值：数据类型存储字节数取值范围使用说明Bigint8-263~263-1存储非常大的正负整数，如科学计算Int4-231~231-1存储正负整数Smallint2-215~215-1存储正负整数Tinyint10～255存储小范围正整数系统数据类型—整型数据类型精确数值数据类型存储带小数的精确数字，有两种精确的数值数据类型：decimal和numeric。格式如下：decimal[(p[，s])]

numeric[(p[，s])]其中：p指定精度，即可存储的十进制数字的最大位数，即除小数点之外的位数。s指定小数位数，即小数点右边可以存储的十进制数字的最大位数。P默认值为18，S默认值为0，最大可存储38位十进制数。系统数据类型—精确数值数据类型近似数据类型用来存储系统所能提供的最大精度的实数数据，由于是一种近似数据，存在误差，故不能用于需要固定精度的运算，如货币数据的运算中，可存储非常大或小的数字，如恒星的质量与大小。近似数据类型有两种：real和float或float（n）real数据类型默认精确到第7位小数。每个real类型的数据占用4个字节的存储空间。float数据类型默认精确到第15位小数。每个float类型的数据占用8个字节的存储空间。系统数据类型—近似数据类型(实数)

float数据类型可写成float(n)的形式，n指定float数据尾数的位数，为1～53之间的整数。当n取1～24时实际上定义了一个real类型的数据，系统用4个字节存储。当n取25～53时系统认为是float类型的数据，用8个字节存储。注意：Unicode字符型是基于SQL-92标准中的NationalCharacter数据类型的。Unicode字符串常量的前面有一个大写N

，例如，'Student'，是字符串常量，而N'Student'，则是Unicode字符型常量。

SQLServer允许使用多国语言，可采用Unicode

标准字符集。Unicode

字符串的格式与普通字符串相似，但Unicode

数据中的每个字符都使用两个字节进行存储。Unicode字符型有nchar、nvarchar和ntext三种系统数据类型--Unicode字符型nvarchar[(n)]存放可变长度的Unicode字符类型，n的取值为1~4000。nchar[(n)]存放固定长度的Unicode字符类型，n的取值为1~4000。ntext

、Nvarchar（Max）最多存放包含个230-1个Unicode字符。注意：nchar、nvarchar和ntext的用法分别与char、varchar和text相同，只是Unicode支持的字符范围更大，存储Unicode字符所需的空间更大(Unicode数据中的每个字符都使用两个字节进行存储)。

Unicode字符数据类型货币数据类型包括money和smallmoney两种。money用于存储货币值，存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，存储范围-9223372136854775808~9223372136854775807，精确到货币单位的1%

。smallmoney与money数据类型类似，但范围比money数据类型小，其存储范围为-2147483468到2147483467之间，精确到货币单位的1%。系统数据类型--货币型位数据类型bit位数据类型，其数据有两种取值：0和1，长度为1字节。在输入0以外的其他值时，系统均把它们当1看待。适用于判断真/假的场合，通常用来存放布尔数据。

Uniqueidentifier用于存储一个16字节长的二进制数据类型，它是SQLServer根据计算机网卡地址和CPU时钟使用NewId（）函数产生的全局惟一标识符代码(GloballyUniqueIdentifier，简写为GUID)｡Timestamp时间戳数据类型，它提供数据库范围内的惟一值，反应数据库中数据修改的相对顺序，相当于一个单调上升的计数器。当它所定义的列在更新或插入数据时会被自动更新，一个计数值将自动地添加到此Timestamp数据列中，该值大于以前的值。用户不能直接设置timestamp列的值，一个表只能有一个timestamp列。可以使用timestamp列的值判断自从上次检索之后是否有其他进程修改数据行。系统数据类型--其它数据类型Table用于存储对表或者视图处理后的临时结果集。Sql_variant（可变数据类型）：通配符数据类型，用于存储除文本、图形数据和Timestamp类型数据外的其他SQLServer数据。

XML可以存储可扩展标记文本数据的数据类型。利用它可以将XML实例存储在表列中或者XML类型的变量中。

Cursor这是变量或存储过程OUTPUT参数的一种数据类型，表示对游标的引用，不能用于列定义。系统数据类型--其它数据类型用户自定义数据类型SQLSERVER允许用户自定义数据类型，用户自定义数据类型是建立在SQLServer系统数据类型基础上的。当多个表的列中要存储同样类型的数据，且想确保这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型。例如，可以基于smalldatetime

数据类型创建名为birthday的用户定义数据类型，用来刻画“出生日期”字段。定义用户自定义数据类型时，需要指定该类型的名称，依赖的系统数据类型，及是否允许为空值。创建用户定义的数据类型的方法有两种：（1）使用对象资源管理器（2）使用系统存储过程sp_addtype

通过对象资源管理器创建用户定义的数据类型在“对象资源管理器”窗口中，选择“数据库”节点在选择要在其中创建用户定义数据类型的数据库”studentCourse”“可编程性”节点“类型”节点“用户定义数据类型”节点，右击“用户定义数据类型”，在弹出的快捷菜单中选择“新建用户定义数据类型"命令。输入新建数据类型的名称。在“数据类型”列表中，选择基础数据类型。如“精度”处于活动状态，若要更改此数据类型可存储的最大数据长度，请键入另外的值。若要允许此数据类型接受空值，请选择“允许空值”命令。在"规则"和"默认值"列表中选择一个规则或默认值（若有）以将其绑定到用户定义数据类型上。语法如下：sp_addtype

type_name,phystype[(length)|([precision,scale]),null|notnull|identity]其中：type_name，用户定义的数据类型的名字；Phystype，用户自定义数据类型所基于的系统数据类型，可以包括长度、精度、标度；null|notnull|identity，用户自定义数据类型的性质，分别为允许空值、不允许为空值、具有标识列性质。默认为notnull。利用系统存储过程创建用户定义数据类型下面创建一个名为birthday的用户定义数据类型（基于smalldatetime），不允许取空值。USEStudentCourseEXECsp_addtypebirthday,datetime,‘NOTNULL'下面的示例为国内及国际电话和传真号码分别创建两个用户定义的数据类型telephone和fax。USEStudentCourseEXECsp_addtypetelephone,'varchar(24)','NOTNULL'EXECsp_addtypefax,'varchar(24)','NULL'注意用户定义的数据类型名称在数据库中必须是唯一的，但是名称不同的用户定义的数据类型可以有相同的定义。执行sp_addtype

创建的用户定义的数据类型，会添加到当前数据库的systypes

系统表中。如果想在所有新创建的数据库中都可使用这一用户定义的数据类型，可将其添加到model数据库中。在创建了用户数据类型之后，可在CREATETABLE或ALTERTABLE中使用它，也可以将默认值和规则绑定到用户定义的数据类型。不能使用timestamp数据类型来定义用户定义的数据类型。删除用户定义的数据类型如果没有任何表或其它数据库对象引用某用户定义数据类型时，可除去这种数据类型。（1）使用对象资源管理器

“数据库”节点在选择要在其中删除用户定义数据类型的数据库”studentCourse”“可编程性”节点“类型”节点“用户定义数据类型”节点，选中要删除的用户定义数据类型，右击在弹出的快捷菜单中选择“删除"命令。（2）使用系统存储过程查看、重命名、删除用户定义的数据类型。 sp_helptype_name查看用户自定义数据类型的特征；sp_rename

oldname,newname

给用户自定义数据类型重新命名；sp_droptypetype_name删除一个已经定义且未被使用的用户自定义数据类型。例如：USEStudentCourseEXECsp_droptype'birthday'字段数据类型的确定原则为一个字段选择数据类型时，应选择能支持用户可能在该字段存储的所有数据的数据类型，同时所需的空间量最小。可参照以下原则：为字段选择一个合适的长度，如果字段值的长度不需要大幅改变，就使用固定长度数据类型（char和Nchar），例如学号、课程号等。如果属性值的长度需要大幅改变，就使用可变长度数据类型（Varchar和Nvarchar），例如存储学生简历等。如果用户存储的字符串来源于不同的国家，就使用Unicode数据类型。3.1.3约束的类型（1）非空值约束（NotNull）：限制字段不接受NULL值，即当对表进行插入（INSERT）操作时，非空值约束的字段必须给出确定的值。空值是指未填写、未知、不可用或将在以后添加的数据，并不等价于空字符串或数值0。（2）默认约束（Default）：为字段定义一个默认值，输入数据时若没有为该字段提供值，则将所定义的默认值提供给该列。默认值可以是常量，也可以是表达式。（3）惟一性约束（Unique）：限制约束的字段，不允许有两条记录具有相同的非空值。但该字段可以出现多个空值。（4）主键约束（PrimaryKey）：标识字段或字段的组合为主键，主键的值惟一标识表中的每一条记录。在一个表中，不能有两条记录包含相同的主键值，主键的各组成字段的值也不能为NULL。（5）外键约束（ForeignKey）：通常是根据从另一个表中某字段（通常是主键）获得的数据集合来进行有效性判定。这时，被约束字段所在的表称为外键表，提供数据的表称为主键表或引用表，提供数据的字段称为引用字段，所提供的数据称为键值。外键常用来标识表与表之间的关系。外键与关系中的外关键字是等价的。

(6)检查约束（check）：一个检查约束由一个关键字CHECK后面跟着一个放在圆括弧里的布尔表达式组成。检查约束允许用户声明该字段里的数值必须满足该布尔表达式。3.2表的创建两种方法：1．使用对象资源管理器创建表2.使用CREATETABLE语句创建表使用CREATETABLE语句创建表创建一个基本表要对基本表命名，定义表的每个列，并定义表的完整性约束条件。CREATETABLE语句格式如下：

CREATETABLE<表名>(<列定义>,…,<列定义>[,<表约束定义>,…,<表约束定义>])；定义基本表—列定义<列定义>定义每个列的名称、类型、缺省值和列上的约束条件：

<列定义>::=<列名><数据类型>[DEFAULT<缺省值>][<列约束定义>,…,<列约束定义>]可选短语“DEFAULT<缺省值>”定义列上的缺省值，<缺省值>是一个特定值或NULL；每个列上可定义零个或或多个约束条件，约束列的取值定义基本表—列约束定义<列约束定义>

::=

[CONSTRAINT<约束名>]<列约束><列约束>

::={NOTNULL|PRIMARYKEY|UNIQUE|

CHECK(<条件>)}其中：NOTNULL：该列不允许取空值，不加NOTNULL限制时，该列可以取空值。PRIMARYKEY：指明该列是主码，其值非空、惟一。UNIQUE：该列上的值必须惟一，相当于说明该列为候选码CHECK(<条件>)：<条件>是一个涉及该列的布尔表达式，指明该列的值必须满足的条件，

定义基本表—表约束定义

一个表可包含零个或多个<表约束定义>，用于定义主码、候选码、外码和表上的其它约束。表约束定义定义形式如下：[CONSTRAINT<约束名>]<表约束><表约束>::={PRIMARYKEY(A1,…,Ak)|

UNIQUE(A1,…,Ak)|

CHECK(<条件>)|FOREIGNKEY(A1,…,Ak)REFERENCES<外表名>(<外表主码>)<参照触发动作>]

}表约束定义说明PRIMARYKEY(A1,…,Ak)：说明属性列A1,…,Ak构成该关系的主码。当主码只包含一个属性时，通常用列约束定义主码。UNIQUE(A1,…,Ak)：说明属性列A1,…,Ak上的值必须惟一；相当于说明A1,…,Ak构成该关系的候选码。当候选码只包含一个属性时，通常用列约束定义候选码。CHECK(<条件>)：说明该表上的一个完整性约束条件。通常<条件>是涉及该表一个或多个列的布尔表达式表约束定义说明外码比较复杂：

FOREIGNKEY(A1,…,Ak)REFERENCES<外表名>(<外表主码>)[<参照触发动作>]属性A1,…,Ak是关系（表）的外码<外表名>给出被参照关系的表名<外表主码>给出被参照关系的主码<参照触发动作>说明违反参照完整性时需要采取的措施:其中<参照动作>可以是CASCADE、SETNULL、SETDEFAULT和NOACTION

之一，例4.1下面的语句创建教师表TeacherCREATETABLETeacher

(教师号 CHAR(7)PRIMARYKEY,

姓名 CHAR(10)NOTNULL,

性别CHAR(2)CHECK(性别=‘男’OR性别=‘女’),

出生日期DATETIME,

职称 CHAR(6),

所在系号 CHAR(4),

FOREIGNKEY(所在系号)REFERENCES系(系号));CREATETABLE语句的较完整格式CREATETABLE[数据据库名.]数据库表名(列名数据类型[NOTNULL][identity(初值,步长)][DEFAULT默认值][UNIQUE][PRIMARYKEY]

[CLUSTERED|NONCLUSTERED]

[,...n][，列名AS计算列值的表达式[，…n]][，[CONSTRAINT约束名]PRIMARYKEY(属性名)[ASC|DESC][,...n]][，[CONSTRAINT约束名]CHECK(逻辑表达式)[，n]][，[CONSTRAINT约束名]FOREIGNKEY(外键属性)REFERENCES参照表(参照属性)

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}][,...n][,[CONSTRAINT约束名]UNIQUE(属性名)[,...n])[ON{文件组名|DEFAULT}]【例】创建数据表book，由书号、书名、出版社、出版日期、单价、数量、总价（单价*数量）、电子邮件地址字段组成。其中：书号列定义为主键并且为系统自动编号即标识列，种子值（起始值）为1000，增量为1。要求出版社字段的值只能是高教、浙大、电子和中央四个之一。电子邮件字段中必须包含@符号。单价必须大于0，数量必须大于等于0，出版日期的默认值设置为当前日期函数。说明（1）Identity属性：可将表中的某列指定为自动增长列，当表中新增行时，系统自动为新行在该列上填充新值。标识属性有两个参数：初值和增量（步长）。每个表只能有一个自动增长列，该列的数据类型必须定义为整型或不带小数的decimal和numeric型。设置book表中的属性“书号”为Identity属性CREATETABLEbook(书号intidentity(1000,1)，……）（2）计算所得的列该列由同一个表中其他列通过“计算列值的表达式”计算得到，并不存储具体数据注意：计算列的值不能修改计算列不能使用默认值约束、外键约束及非空值约束例如：设置book表中属性“总价”为计算所得到的列CREATETABLEbook(……总价AS单价*数量，……）（3）Cheak约束：每个列可有一个或多个check约束，约束条件是逻辑表达式。列级check约束只能引用该列本身，表级check约束可引用同一个表中的列。CREATETABLEbook(……单价smallintcheck(单价>0),数量smallintcheck(数量>=0),电子邮件地址varchar(25)check(电子邮件地址like'%@%'),check(出版社in('高教','浙大','电子','中央'),……)Like的匹配方式LIKE：主要用于在逻辑表达式中对字符串进行比较，它的否定形式为NOTLIKE。语法格式匹配表达式

[NOT]LIKE模式[ESCAPEescape_character

]ESCAPE

子句定义转义符：搜索包含一个或多个特殊通配符的字符串4种字符串匹配方式见下表通配符描述示例%包含零个或更多字符的任意字符串WHEREtitlelike‘%computer%’将查找书名包含单词computer的所有书名_（下划线）任何单个字符WHEREau_fnamelike‘_ean’将查找以ean

结尾的所有4个字母的名字（Dean、Sean等）[]范围([a-d])或集合([abcd])中的任何单个字符WHEREau_lnamelike‘[C-P]arsen’将查找以arsen

结尾且以介于C与P之间的任何单个字符开始的作者姓氏，例如Carsen、Larsen等[^]不属于指定范围([a-d])或集合([abcd)中任何单个字符WHEREau_lnamelike‘de[^i]%’将查找以de开始且其后的字母不为i的所有作者的姓氏。创建book表的完整命令如下：CREATETABLEbook（书号intidentity(1000,1)PRIMARYKEY,书名varchar(20)notnull,出版社varchar(20),出版日期datetimeDEFAULT(getdate()),单价smallintcheck(单价>0),数量smallintcheck(数量>=0),总价as单价*数量,电子邮件varchar(25),check(电子邮件like'%@%'),check(出版社in('高教','浙大','电子','中央')))示例为studentCourse数据库定义如下三个关系数据表：列名数据类型长度允许空默认值是否主键学号char5√姓名char8性别char2男出生日期datetime1980-01-01系varchar20电话

char8√学生基本信息表S列名数据类型长度允许空默认值是否主键课程号char3

√课程名varchar20学分smallint√预选课程号char3√教师char8√课程数据表C学生选课数据表SC列名数据类型长度允许空默认值是否主键学号char5√课程号char3成绩SMALLINT2√数据库的完整性定义

列名UNIQUECHECKFOREIGN学号由a-z间字母开头,后面只能取0-9之间的数字，限5位性别性别的值只能取‘男’或‘女’电话

电话的格式为021-7777学生基本信息表S约束列名UNIQUECHECKFOREIGN课程号由字母C开头,后2位只能取0-9之间的数字预选课程号参照C.课程号课程数据表C约束列名UNIQUECHECKFOREIGN课程号√参照C.课程号学号参照S.学号成绩成绩只能在0到100之间或者是空值学生选课数据表SC约束1）创建学生基本信息表S的语句如下：CREATETABLES(

学号Char(5)

NOTNULL,

姓名Char(8)

NOTNULL,

性别Char(2)

NOTNULL

default('男'),

出生日期

DatetimeNOTNULLDEFAULT('19800101'),

系

Varchar(20),

电话

Char(8),

CHECK(学号

Like'[A-Z][0-9][0-9][0-9][0-9]'),

CHECK(性别='女'OR性别='男'),

CHECK(电话

Like'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),CONSTRAINTPK_SPRIMARYKEYCLUSTERED(学号ASC))ON[PRIMARY]2）创建课程表C的语句如下：CREATETABLEC(

课程号Char(3)NOTNULL,

课程名Varchar(20)

NOTNULL,

学分

Smallint

NULL,

预选课程号

Char(3)

NULL,

教师

Char(8)

NULL,

CONSTRAINTFK_PcnoFOREIGNKEY(预选课程号)

REFERENCESC(课程号),CONSTRAINTCK_CnoCHECK(课程号

Like'C[0-9][0-9]'),CONSTRAINTPK_CPRIMARYKEYCLUSTERED(课程号ASC))ON[PRIMARY]3）创建学生选课数据表SC的语句如下：CREATETABLESC

(

学号Char(5)NOTNULL,课程号Char(3)NOTNULL,成绩SmallintNULL,FOREIGNKEY(课程号)REFERENCESC(课程号),FOREIGNKEY(学号)REFERENCESS(学号),CHECK(成绩>=(0)AND成绩<=(100)OR成绩

ISNULL),PRIMARYKEYCLUSTERED(学号ASC,课程号

ASC)

)也可将约束定义在列级，各约束名由系统自动产生：1）创建学生基本信息表S的语句如下：CREATETABLES(

学号Char(5)

NOTNULL

CHECK(学号

Like'[A-Z][0-9][0-9][0-9][0-9]')

PrimarykeyClustered,

姓名Char(8)

NOTNULL,

性别Char(2)

NOTNULL

default('男')

CHECK(性别='女'

OR性别='男'),

出生日期

DatetimeNOTNULLDEFAULT('19800101'),

系

Varchar(20)

NOTNULL,

电话

Char(8)

NULL

CHECK(电话

Like'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'))ON[PRIMARY]2）创建课程表C的语句如下：CREATETABLEC(

课程号Char(3)NOTNULL

CHECK(课程号

Like'C[0-9][0-9]')

PRIMARYKEYCLUSTERED,

课程名Varchar(20)

NOTNULL,

学分

Smallint

NULL,

预选课程号

Char(3)

NULL

FOREIGNKEY(预选课程号)

REFERENCESC(课程号),

教师

Char(8)

NULL,)ON[PRIMARY]3）创建学生选课数据表SC的语句如下：CREATETABLESC(

学号Char(5)NOTNULL

FOREIGNKEY(学号)REFERENCESS(学号),课程号Char(3)NOTNULL

FOREIGNKEY(课程号)REFERENCESC(课程号),

成绩SmallintNULL

CHECK(成绩>=(0)AND成绩<=(100)OR成绩

ISNULL),PRIMARYKEYCLUSTERED(学号ASC,课程号

ASC))使用对象资源管理器创建表步骤如下：1、在【对象资源管理器】窗口中，展开的“数据库”节点，右击需在其中创建数据表的数据库studentcourse的【表】项，在出现的快捷菜单中选择【新建表】，出现表设计器窗口。2、在【列名】中依次输入列名，在【数据类型】中选择数据类型，并选择各个列是否允许空值，也可在下部的【列属性】对话框中修改某列的属性：包括标识identity属性、计算所得到的列、默认值等，如下图示3、选中要设为主键的行，单击工具栏上的【设置主键】按钮或右击并在弹出的快捷菜单中选择【设置主键】命令。4、单击工具栏上的【管理check约束】按钮或在表设计器中右击在弹出的快捷菜单中选择【check约束】命令，在弹出的“check约束”对话框中设置check约束（添加、删除，关闭）。5、单击工具栏中的【保持】按钮，出现【选择名称】对话框，输入表名，单击【确定】，该表出现在【对象资源管理器】窗口的【表】项下【例】创建数据表book【例】创建数据库“studentcourse”的学生基本信息表S、课程表C、选课表SC6、若要在多个表中设置外键关系，可单击工具栏上的【关系】按钮或在表设计器中右击在弹出的快捷菜单中选择【关系】命令，设置各表之间的外键关系7、单击【外键关系】对话框中【表和列规范】右边的按钮，在弹出的【表和列】对话框中选择主键表和外键表，设置外键关系，返回【外键关系】对话框，在该对话框的【标识】中更改外键的名称，完成外键的设置。查看表信息和属性1.查看表数据启动SQLServerManagementStudio，选择数据库，右击要打开的表，选择“打开表”命令，在右边区域中将显示表中所有的数据。2.查看表属性启动SQLServerManagementStudio，选择数据库，右击要打开的表，选择“属性”命令，在右边区域中将显示表的属性信息。或execsp_help

表名修改数据表结构1.利用对象资源管理器增加（删除）列和修改列增加列操作步骤：（1）右击要修改的表，选择“修改”命令，打开【表设计器】窗口。（2）若要在固定列之前加入新属性，可在该列上右击，选择“插入列”。然后输入列名以及相关设置。

2）删除列操作选择要删除的列，右击弹出的快捷菜单中选择“删除列”命令：3）修改列：在【表设计器】中选择要修改的列，更改数据类型，设置identity属性，及默认值等2.利用T-SQL语言修改表【格式】：

ALTERTABLE<表名>{ALTERCOLUMN<列名>数据类型(宽度)

[NULL|NOTNULL]|ADD<列名>数据类型(宽度)[NULL|NOTNULL][约束][,…n]|ADD列名AS计算列值的表达式|DROPCOLUMN[<列名>[CASCADE︱RESTRICT]][,…n]|ADD

<table_constraint>|DROP[CONSTRAINT<约束名>|ALL][,…n]}2）说明ALTERCOLUMN：修改指定列。列的修改不能与列或表的其他定义相冲突。如某列的默认值为字符串，则数据类型不能更改为非字符串类型，要想修改必须先删除默认约束。DROPCOLUMN[<列名>[CASCADE︱RESTRICT]删除指定列：CASCADE表示级联删除，即依赖于该列的数据库对象（如涉及该列的视图）一并删除RESTRICT表示受限，仅当没有依赖于该列的数据库对象时删除才能成功【例】数据库“studentcourse”中S表的系属性修改为char(25)，增加一个入学时间字段，它的数据类型为datetime，并设置默认值为getdate()，最后删除入学时间字段。方法一：使用SQLServerManagementStudio方法二：使用T-SQL命令。ALTERTABLESALTERCOLUMN系char(25)GoALTERTABLESADD入学时间datetime

DEFAULT(getdate())Go【例】数据库“studentcourse”中的S表增加一个年龄字段，该字段由出生日期字段，结合当前系统日期计算产生。Add年龄

asyear(getdate())-year(出生日期)将学生表S的性别默认值修改为‘女’AltertableSALTERCOLUMN性别default('女')修改表的名称方法一：使用对象资源管理器右击要重命名的表，选择【重命名】命令，在弹出的【重命名】窗口中输入新表名。方法二：使用T-SQL命令：

sp_rename<原数据表名>,<新数据表名>如：EXECsp_rename'book','书籍资料'删除表1.使用对象资源管理器删除表右击要删除的表，选择“删除”命令。

2.使用DROPTABLE语句删除表：DROPTABLE表名[,…n]例如：droptables删除表时，表上的规则或默认值将解除绑定，任何与表关联的约束或触发器将自动删除。定义有外键约束的表必须先删除外键约束，才能删除系统表不能使用DROPTABLE语句删除。3.3数据表的操作使用对象资源管理器插入、修改与删除表中的数据选中要插入数据的表名，单击鼠标右键，在出现的快捷菜单中选中【打开表】，将会出现一个窗口供用户按行输入数据。输入结束后，直接关闭该窗口就能将用户输入写入数据库中。2.使用T-SQL语句插入、修改与删除表中的数据1）插入数据【格式】INSERTINTO数据表名(列名表)VALUES(元组值)INSERTINTO数据表名(列名表)SELECT查询语句2）功能向指定数据表的属性列插入数据，VALUES后跟的元组值为属性列提供数据。其中列名表中的属性排列顺序和VALUES后跟的元组值的排列顺序要一致。对应的数据类型要一致。如果没有指定列名表，则表示数据表中的所有属性列。【例1】使用属性列显示指定插入到每个列的值

INSERTINTOs(学号,姓名,性别,出生日期,系)VALUES('L0401','张云龙','男','1987-11-11',‘IS')Go【例2】将查询结果插入数据表INSERTINTOSCSELECT'L0401',课程号,80FROMCGo2）修改数据【格式】UPDATE表名SET列名=值表达式[，列名=值表达式…][WHERE条件表达式]【功能】将指定表中满足WHERE子句条件的记录的修改为指定属性值。其中值表达式可以是常量、变量、表达式。若缺省WHERE，则修改表中的所有元组。但在进行修改操作时，需注意数据库的一致性。【例】更新S表中的出生日期列中的值变为原出生日期值加1UPDATESSET出生日期=出生日期+1【例】将选C01课程的学号是L0401的学生的成绩改成85分UPDATESCSET成绩=85WHERE课程号='C01'AND学号='L0401‘GO【例】将张云龙学生的成绩减少5分UPDATESCSET成绩=成绩-5WHERE学号IN(SELECT学号FROMSWHERE姓名='张云龙')3）删除数据【格式】DELETEFROM表名[WHERE条件表达式]【例】删除学号为L0401的学生选课信息DELETEFROMSCWhere学号='L0401’【例】从SC表中删除所有行DELETEFROMSC4）清空表中数据TruncateTable表名3.4数据完整性概述1．完整性的概念数据完整性是指数据的正确性、有效性、一致性和相容性。由于数据库是一个动态的集合，数据不断地被插入和被修改，因此由于主观或客观的原因，可能破坏数据库的数据完整性。2．完整性分类

在SQLServer2005中，数据完整性分成四类：实体完整性、域完整性、参照完整性和用户自定义完整性域完整性域完整性也可称为列完整性。关系中每个属性列都必须在一个值域上取值。保证该列具有正确的数据类型、格式以及有效的数据范围。

例如，向SelectCourse库的SC表的成绩列输入数据时，不能出现字符，也不能输入小于0或大于100的数值。域完整性要求DBMS对新插入的值进行域完整性检查，还要对查询进行检测，从而保证比较是有意义的域完整性在原理上类似于编程语言中变量的类型，就像不同的变量可以有相同的数据类型一样，不同的属性可以有相同的域。域完整性SQLSERVER提供了一些标准数据类型(域),也允许用户根据需要定义新的域，声明一个域包括(1)域值类型：包括数据的类型、长度、单位、精度等例如，可以规定PersonName（人名）域的数据类型是字符型，长度为8；RMB（人民币）域和Dollars（美元）域的类型都是长度为12位十进制数，小数点后有两位(2)缺省值。例如可以规定RMB和Dollars域的缺省值为0.00(3)域值的格式。例如可以规定出生日期的格式为：YYYY.MM.DD(4)对取值范围或取值集合的约束例如，可以规定性别域的取值集合为{男，女}，学生成绩域的取值范围为[0,100]。域完整性由于域完整性规定不同域上的值不能比较，故可使用用户自定义的域说明属性防止无意义的比较:例如，如果用字符串类型说明属性Dname（部门名）和Ename（职工名），则比较Dname=Ename是合法的，但这种比较没有实际意义如果Dname说明为部门域，Ename说明为人名域，则系统可以通过域约束检查，发现比较Dname=Ename不合法。完整性机制

为了保证数据完整性，DBMS应能对数据库进行数据完整性控制。在SQLServer2005中，提供了如下机制对数据完整性进行控制：约束默认值规则触发器等定义约束是SQLServer2005实现数据完整性的主要手段：

定义主键约束——实体完整性default约束——域完整性定义外键约束——参照完整性CHECK约束——用户自定义完整性默认值在SQLServer2005中，在表中插入数据行时，系统自动为没有指定数据的列提供事先定义的默认值。使用默认值有两种方法：（1）在创建表时指定默认值（DEFAULT约束）（2）使用CREATEDEFAULT语句创建默认值对象，然后使用存储过程将该默认值对象绑定到指定的列上。

创建默认值语法格式如下：CREATEDEFAULT默认值名称AS常量表达式

与DEFAULT约束不同的是，使用CREATEDEFAULT语句创建的默认值是一种数据库对象，在数据库中只需定义一次后，就可以被一次或多次应用于任意表中的一列或多列，还可以用于用户自定义的数据类型。绑定默认值对象默认值对象创建后必须将其绑定到某列或用户自定义的数据类型上才能使用。绑定可用存储过程sp_bindefault，语法格式如下：

sp_bindefault[@defname=]'default',[@objname=]'object_name'[,[@futureonly=]'futureonly_flag']【功能】把默认值对象default绑定到object_name指定的列上或用户自定义数据类型上。【例】为studentCourse数据库创建一个名为Sex_Default的默认值对象，值为“男”。然后把该默认值对象绑定到S表的性别字段上。CREATEDefaultSex_DefaultAs'男'GOEXECsp_bindefault'Sex_Default','S.性别'GOEXECsp_bindefault'Sex_Default','Teachers.性别'GO绑定默认对象—参数说明[@futureonly=]‘futureonly_flag‘选项仅在将默认值对象绑定到用户定义的数据类型时才使用。将此参数设置为futureonly

时，它会防止现有的属于此数据类型的列采用新的默认值。示例【例】如已创建了用户定义的数据类型birthday，且表S中的‘出生日期’字段使用birthday数据类型，创建一默认值birth_default，取值为“1981-1-1”。然后把该默认值对象绑定到birthday数据类型上。--创建了用户定义的数据类型birthday，USEStudentcourseEXECsp_addtypebirthday,datetime,'NOTNULL'--将S表中的‘出生日期’字段修改为birthday数据类型altertablesaltercolumn出生日期

birthday--创建一默认值birth_default，将它绑定到birthday数据类型上CREATEDefaultbirth_DefaultAs'1981-1-1'GOEXECsp_bindefault'birth_Default','birthday'GO--给s表插入一新行，不提供出生日期值，观察该字段的取值情况？？insertintos(学号,姓名)values('a1234','aaa')Goselect*froms

where学号='a1234'当某列上同时定义了default约束、并绑定了默认值对象时，default约束优先。分析--若再创建一新默认值birth_default2，取值为“1982-1-1”将它绑定birthday数据类型上CREATEDefaultbirth_Default2As'1982-1-1'GOEXECsp_bindefault'birth_Default2','birthday'--给s表插入一新行，不提供出生日期值，观察该字段的取值情况？？insertintos(学号,姓名)values('b1234','bbb')Goselect*froms

where学号='b1234'

可以使用sp_bindefault

在不取消现有默认值绑定的情况下，将新默认值绑定到列或者绑定到用户定义的数据类型，原有默认值将被替代。分析--若再创建一新默认值birth_default3，取值为“1983-1-1”使用‘futureonly’选项将它绑定birthday数据类型上CREATEDefaultbirth_Default3As'1983-1-1'GOEXECsp_bindefault'birth_Default3','birthday','futureonly'--给s表插入一新行，不提供出生日期值，观察该字段的取值情况？？insertintos(学号,姓名)values('c1234','bbb')Goselect*froms

where学号='c1234'分析创建如下teacher数据表，其中的出生日期字段使用用户定义的数据类型BIRTHDAY：CREATETABLETeacher

(教师号 CHAR(7)PRIMARYKEY,

姓名 CHAR(10)NOTNULL,

性别CHAR(2)CHECK(性别='男'OR性别='女'),

出生日期BIRTHDAY,

职称 CHAR(6));--给TEACHER表插入一新行，不提供出生日期值，观察该字段的取值情况？？insertintoTEACHER(教师号,姓名)values(‘t1234',‘ttt')Goselect*fromteacher

where学号=‘t1234'

如果默认值和要绑定的列不兼容，那么在试图插入默认值时（不是在绑定时），SQLServer将返回错误信息。如：CREATEDefaultScore_DefaultAs