SQL Server教程（第6版）(2008版) 课件 第3-5章 表的创建和操作、数据库的查询和视图、索引和数据完整性

第3章

表的创建和操作——表结构和数据类型01表和表结构表和表结构每个数据库包含了若干个表。表是SQLServer中最主要的数据库对象，它是用来存储数据的一种逻辑结构。表由行和列组成，因此也称为二维表。表是在日常工作和生活中经常使用的一种表示数据及其关系的形式，表就是用来表示学生情况的一个“学生”表。学

号姓名性别出生日期专

业总学分备

注221101王林男2004-02-10计算机15

221102程明男2005-02-01计算机15

221103王燕女2003-10-06计算机15参加校女子足球队201103严红女2002-08-11计算机60

221201刘华男2004-06-10通信工程13辅修计算机专业221202王林男2004-01-29通信工程13

表和表结构下面简单介绍与表有关的几个概念。（1）表结构。组成表的各列的名称及数据类型，统称为表结构。（2）记录（行）。每个表包含了若干行数据，它们是表的“值”，表中的一行称为一个记录。因此，表是记录的有限集合。（3）列（字段）。每个记录由若干个数据项构成，将构成记录的每个数据项称为字段，又直接称列。例如，表3.1中的表结构为（学号，姓名，性别，出生日期，专业，总学分，备注），包含7个列，由6个记录组成。（4）空值。空值（NULL）通常表示未知、不可用或将在以后添加的数据。若一个列允许为空值，则向表中输入记录值时可不为该列指定具体值；而一个列若不允许为空值，则在输入时必须给出具体值。（5）关键字。若表中记录的某一列或列组合能唯一标识记录，则称该列或列组合为候选关键字（Candidatekey）。若一个表有多个候选关键字，则选定其中一个为主关键字（Primarykey），也称为主键。当一个表仅有唯一的一个候选关键字时，该候选关键字就是主关键字。02数据类型整数型精确数值型浮点型货币型位型字符型Unicode字符型文本型二进制型日期时间类型时间戳型图像数据类型xml类型地理空间类型和几何数据类型其他数据类型数据类型列的数据类型可以是SQLServer提供的系统数据类型，也可以是用户定义的数据类型。SQLServer提供了丰富的数据类型，列于表中。数据类型符号标识整数型bigint,int,smallint,tinyint精确数值型decimal,numeric浮点型float,real货币型money,smallmoney位型bit字符型char,varchar、varchar(MAX)Unicode字符型nchar,nvarchar、nvarchar(MAX)文本型text,ntext二进制型binary,varbinary、varbinary(MAX)日期时间类型datetime,smalldatetime,date,time,datetime2,datetimeoffset时间戳型timestamp图像型image其他sql_variant,uniqueidentifier,xml,hierarchyid,geometry数据类型在讨论数据类型时，使用了精度、小数位数和长度3个概念，前两个概念是针对数值型数据的，它们的含义是：精度：指数值数据中所存储的十进制数据的总位数。小数位数：指数值数据中小数点右边可以有的数字位数的最大值。例如，数值数据3890.587的精度是7，小数位数是3。长度：指存储数据所使用的字节数。1．整数型整数型包括bigint、int、smallint和tinyint，从标识符的含义就可以看出，它们表示数的范围逐渐缩小。（1）bigint：大整数，范围为

263～263

1，其精度为19，小数位数为0，长度为8字节。（2）int：整数，范围为

231～231

1，其精度为10，小数位数为0，长度为4字节。（3）smallint：短整数，范围为

215～215

1，其精度为5，小数位数为0，长度为2字节。（4）tinyint：微短整数，范围为0～255，长度为1字节，其精度为3，小数位数为0，长度为1字节。数据类型2．精确数值型精确数值型数据由整数部分和小数部分构成，其所有的数字都是有效位，能够以完整的精度存储十进制数。精确数值型包括decimal和numeric两类。在SQLServer中，这两种数据类型在功能上完全等价。声明精确数值型数据的格式是numeric|decimal(p[,s])，其中p为精度，s为小数位数，s的默认值为0。例如，指定某列为精确数值型，精度为6，小数位数为3，即decimal(6,3)，那么当向某记录的该列赋值56.342689时，该列实际存储的是56.343。decimal和numeric可存储–1038+1～1038–1的固定精度和小数位的数字数据，它们的存储长度随精度变化而变化，最少为5字节，最多为17字节。数据类型3．浮点型有两种近似数值数据类型：float[(n)]和real，两者通常都使用科学记数法表示数据，即形为：尾数E阶数，如5.6432E20，–2.98E10，1.287659E-9等。（1）real：使用4字节存储数据，表数范围为–3.40E+38～3.40E+38，数据精度为7位有效数字。（2）float：表数范围为–1.79E+308～1.79E+308。定义中的n取值范围是1～53，用于指示其精度和存储大小。当n在1～24之间时，实际上将定义一个real型数据，存储长度为4字节，精度为7位有效数字。4．货币型SQLServer提供了两个专门用于处理货币的数据类型：money和smallmoney，它们用十进制数表示货币值。（1）money：范围为

263～263

1，其精度为19，小数位数为4，长度为8字节。money的数范围与bigint相同，不同的只是money型有4位小数。实际上，money就是按照整数进行运算的，只是将小数点固定在末尾4位。（2）smallmoney：范围为–231～231

1，其精度为10，小数位数为4，长度为4字节。数据类型5．位型SQLServer中的位（bit）型数据相当于其他语言中的逻辑型数据，它只存储0和1，长度为1字节。但要注意，SQLServer对表中bit类型列的存储进行了优化：如果一个表中有不多于8个bit列，则这些列将作为1字节存储；如果表中有9～16个bit列，则这些列将作为2字节存储，更多列的情况以此类推。6．字符型SQLServer字符字符型数包括两类：定长（char）和变长（varchar）。（1）char[(n)]：定长字符数据类型，其中n定义字符型数据的长度，在1～8000之间，默认为1。当表中的列定义为char(n)类型时，若实际存储的串长度不足n时，则在串的尾部添加空格以达到长度n，所以char(n)的长度为n。（2）varchar[(n)]：变长字符数据类型，其中，n的规定与定长字符型char中的n完全相同，但这里n表示的是字符串可达到的最大长度。数据类型7．Unicode字符型Unicode字符型包括nchar[(n)]和nvarchar[(n)]两类。nchar是定长Unicode数据的数据类型，nvarchar是变长Unicode数据的数据类型，二者均使用UNICODEUCS-2字符集。（1）nchar[(n)]：nchar[(n)]为包含n个字符的固定长度Unicode字符型数据，n的值在1～4000之间，长度为2n字节。若输入的字符串长度不足n，将以空白字符补足。（2）nvarchar[(n)]：nvarchar[(n)]为最多包含n个字符的可变长度Unicode字符型数据，n的值在1～4000之间，默认为1。长度是所输入字符个数的两倍。8．文本型文本型包括text和ntext两类，分别对应ASCII字符和Unicode字符。（1）text类型：可以表示最大长度为231

1个字符，其数据的存储长度为实际字符个数字节。（2）ntext类型：可表示最大长度为230

1个Unicode字符，其数据的存储长度是实际字符个数两倍的字节。数据类型9．二进制型二进制数据类型表示的是位数据流，包括binary（固定长度）和varbinary（可变长度）两种。（1）binary[(n)]：固定长度的n个字节二进制数据。n的取值范围为1～8000，默认为1。binary(n)数据的存储长度为n+4个字节。若输入的数据长度小于n，则不足部分用0填充；若输入的数据长度大于n，则多余部分被截断。（2）varbinary[(n)]：n个字节变长二进制数据。n取值范围为1～8000，默认为1。varbinary(n)数据的存储长度为实际输入数据长度+4个字节。数据类型10．日期时间类型（1）datetime类型：可表示的日期范围从1753年1月1日～9999年12月31日，精确度为0.03s（3.33ms或0.00333s）。例如，1～3ms的值都表示为0ms，4～6ms的值都表示为4ms。datetime类型数据长度为8字节，日期和时间分别使用4个字节存储。前4字节用于存储datetime类型数据中距1900年1月1日的天数。为正数表示日期在1900年1月1日之后，为负数则表示日期在1900年1月1日之前。后4字节用于存储datetime类型数据中距12:00（24小时制）的毫秒数。日期部分的表示形式常用的格式如下：年月日2001Jan20、2001January20年日月200120Jan月日[,]年Jan202001、Jan20,2001、Jan20,01月年日Jan200120日月[,]年20Jan2001、20Jan,2001日年月202001Jan年（4位数）2001表示2001年1月1日年月日20010120、010120月/日/年01/20/01、1/20/01、01/20/2001、1/20/2001月-日-年01-20-01、1-20-01、01-20-2001、1-20-2001月.日.年01.20.01、1.20.01、01.20.2001、1.20.2001数据类型时间部分常用的表示格式如下：时:分10:20、08:05时:分:秒20:15:18、20:15:18.2时:分:秒:毫秒20:15:18:200时:分AM|PM10:10AM、10:10PM（2）smalldatetime：该类型数据可表示从1900年1月1日～2079年6月6日的日期和时间，数据精确到分钟。即29.998s或更低的值向下舍入为最接近的分钟，29.999s或更高的值向上舍入为最接近的分钟。（3）date：该类型数据可以表示从公元元年1月1日到9999年12月31日的日期，date类型只存储日期数据，不存储时间数据，存储长度为3字节，表示形式与datetime数据类型的日期部分相同。数据类型（4）time：该类型数据只存储时间数据，表示格式为“hh:mm:ss[.nnnnnnn]”。hh表示小时，范围为0～23。mm表示分钟，范围为0～59。ss表示秒数，范围为0～59。n是0～7位数字，范围为0～9999999，表示秒的小数部分，即微秒数。所以time数据类型的取值范围为00:00:00.000

0000～23:59:59.9999999。time类型的存储大小为5字节。另外还可以自定义time类型微秒数的位数，例如，time(1)表示小数位数为1，默认为7。（5）datetime2：新的datetime2数据类型和datetime类型一样，也用于存储日期和时间信息。但是datetime2类型取值范围更广，日期部分取值范围从公元元年1月1日～9999年12月31日，时间部分的取值范围为00:00:00.0000000～23:59:59.999999。另外，用户还可以自定义datetime2数据类型中微秒数的位数，例如，datetime(2)表示小数位数为2。datetime2类型的存储大小随着微秒数的位数（精度）而改变，精度小于3时为6字节，精度为4和5时为7字节，所有其他精度则需要8字节。（6）datetimeoffset：该数据类型也用于存储日期和时间信息，取值范围与datetime2类型相同。但datetimeoffset类型具有时区偏移量，此偏移量指定时间相对于协调世界时（UTC）偏移的小时和分钟数。数据类型11．时间戳型标识符是timestamp。若创建表时定义一个列的数据类型为时间戳类型，那么每当对该表插入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。记录timestamp列的值实际上反映了系统对该记录修改的相对（相对于其他记录）顺序。一个表只能有一个timestamp列。timestamp类型数据的值实际上是二进制格式数据，其长度为8字节。12．图像数据类型标识符是image，它用于存储图片、照片等。实际存储的是可变长度二进制数据，介于0与231

1（2147483647）字节之间。在SQLServer中，该类型是为了向下兼容而保留的数据类型。微软推荐用户使用varbinary(MAX)数据类型来替代image类型。数据类型13.xml类型用来在数据库中保存xml文档和片段的一种类型，大小不能超过2GB。例如描述地址：'{"省":"江苏","市":"南京","区县":"栖霞","街道":"仙林智谷","电话":"1381390100X"}'。14.地理空间类型和几何数据类型geometry：用于存储地理空间类型。例如地球某点的经纬度为：（118.912000，32.096790），把空间描述字符串转换为地理空间类型数据：geometry::STGeomFromText('POINT(118.91200032.096790)',4326)，其中4326是坐标系的参数，代表GCS-WGS-1984坐标系，是系统默认的坐标系。数据类型15．其他数据类型（1）sql_variant：一种存储SQLServer支持的各种类型（除text、ntext、image、timestamp和sql_variant外）值的数据类型，，最大长度可达8016字节。（2）uniqueidentifier：唯一标识符类型。系统将为这种类型的数据产生唯一标识值，它是一个16字节长的二进制数据。（3）hierarchyid：SQLServer新增加的一种长度可变的系统数据类型，可用来表示层次结构中的位置。另外，varchar、nvarchar、varbinary这3种数据类型可以使用MAX关键字，如varchar(MAX)、nvarchar(MAX)、varbinary(MAX)，加了MAX关键字的这几种数据类型最多可存放231-1个字节的数据，分别可以用来替换text、ntext和image数据类型。03表结构设计表结构设计在创建表之前，先要确定表的名字、表的属性，同时确定表所包含的列名、列的数据类型、长度、是否可为空值、约束条件、默认值设置、规则以及所需索引、哪些列是主键、哪些列是外键等，这些属性构成表结构。例如：学生成绩管理数据库（xscj）包含学生表（xsb）、课程表（kcb）和成绩表（cjb）。学生表xsb包含的属性有学号、姓名、性别、出生日期、专业、总学分、备注，如表。列

名数据类型长

度是否可空默

认

值说

明学号定长字符型（char）6×无主键,前两位表示年级,中间两位为专业,后两位为序号姓名定长字符型（char）8×无

性别位型（bit）默认值√11:男,0:女出生日期日期型（date）默认值√无

专业不定长字符型（varchar）12√计算机

总学分整数型（int）默认值√00≤总学分<160备注不定长字符型（varchar）500√无

表结构设计参照xsb表结构的设计方法，同样可以设计出kcb和cjb其他两个表的结构，如表。列

名数据类型长

度是否可空默

认

值说

明课程号定长字符型（char）3×无主键课程名定长字符型（char）16×无

开课学期整数型（tinyint）1√1只能为1～8学时整数型（tinyint）1√0

学分整数型（tinyint）1×0只能为1～6kcb的表结构列

名数据类型长

度是否可空默

认

值说

明学号定长字符型（char）6×无主键课程号定长字符型（char）3×无主键成绩整数型（int）默认值√0范围为0～100

cjb的表结构第3章

表的创建和操作——表结构创建、修改和删除01表结构创建表结构创建T-SQL语法格式如下。CREATETABLE表名( <列定义>, …)<列定义>::=列名,数据类型[属性][默认值][列约束]其中：（1）表名如果创建的表非当前数据库的表，需要采用“数据库-表名”。一般使用下列命令指定当前数据库：USE数据库名（2）列名必须符合标识符规则，长度不能超过64个字符，而且在表中要唯一。（3）数据类型和长度通过关键字声明具体的类型，有的数据类型需要指明长度n并用()括起。表结构创建（4）属性SQLServer支持用多种关键字定义列的属性，比如，用NOTNULL|NULL指定列是否允许空值，NOTNULL表示列不可取空值，NULL表示列可取空值。本例的“学号”和“姓名”列以NOTNULL明确不允许空值。如果不指定，SQLServer视为指定了NULL。（5）默认值用“DEFAULT常量表达式”为所在列指定默认值，其中“常量表达式”必须是一个常量值、标量函数或NULL值。DEFAULT定义可适用于除定义为timestamp或带IDENTITY属性的列以外的任何列。（6）列约束列的完整性约束，指定主键、替代键、外键等。例如，若指定该列为主键，则使用PRIMARYKEY关键字，本例在“学号”列上创建了主键约束，将该列设为整个表的主键：学号 char(6) NOTNULLPRIMARYKEY,表结构创建【例3.1】在xscj数据库中创建学生表xsb，该表的结构如表3.3所示。语句如下。USExscjCREATETABLExsb(

学号 char(6) NOTNULLPRIMARYKEY,

姓名 char(8) NOTNULL,

性别 bit NULLDEFAULT1,

出生日期 date NULL,

专业 varchar(12) NULLDEFAULT'计算机',

总学分 int NULLDEFAULT0,

备注 varchar(500) NULL)表结构创建说明：（1）USExscj语句将数据库xscj指定为当前数据库，然后CREATETABLE语句在数据库xscj中创建表xsb。如果xscj数据库此前没有创建，可以采用默认方式创建xscj数据库，然后打开xscj数据库，再在该数据库中创建表。CREATEDATABASExscj（2）后面只要在xscj数据库中操作，就不再需要打开该数据库。（3）xsb表创建后，在SSMS的对象资源管理器的xscj的“表”下就会显示xsb。【例3.2】创建一个带计算列的表，表中包含课程的课程号、总成绩和学习该课程的人数，以及课程的平均成绩。T-SQL语句如下。CREATETABLEkcb_pjcj(

课程号 char(3) PRIMARYKEY,

总成绩 real NOTNULL,

人数 int NOTNULL,

平均成绩 AS总成绩/人数PERSISTED)02分区表创建分区函数分区方案使用分区方案创建分区表分区表创建创建分区表的步骤如下：（1）创建分区函数，指定如何分区。（2）创建分区方案，定义分区函数在文件组上的位置。（3）使用分区方案。1．分区函数1）创建分区函数语法格式如下：CREATEPARTITIONFUNCTION函数名(输入参数类型) ASRANGE[LEFT|RIGHT] FORVALUES([边界值[,...]])说明：函数名：要创建的分区函数的名称。输入参数类型：用于分区的列的数据类型。LEFT|RIGHT：指定当间隔值由数据库引擎按升序从左到右排列时，边界值属于每个边界间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为LEFT。边界值：为该分区函数的已分区表或索引的每个分区指定边界值。分区表创建【例3.3】如果需要对学号列进行分区，需要对char类型列创建一个分区函数，名为PFunXH，数据分成4个区。包括小于或等于'210000'的区、大于'210000'且小于或等于'220000'的区、大于'220000'且小于或等于'230000'的区和大于'230000'的区。T-SQL语句如下。USExscjCREATEPARTITIONFUNCTIONPFunXH(char(6)) ASRANGELEFTFORVALUES('210000','220000','230000')语句执行后，在xscj→存储→分区函数下，就会看到“PFunXH”项。2）分区函数删除DROPPARTITIONFUNCTION分区函数名分区表创建2.分区方案1）创建分区方案语法格式如下：CREATEPARTITIONSCHEME方案名 ASPARTITION函数名 [ALL]TO({文件组名|[PRIMARY]}[,...n])说明：方案名：创建的分区方案的名称，在创建表时使用该方案即可创建分区表。函数名：使用分区方案的分区函数的名称，该函数必须在数据库中存在，分区函数所创建的分区将映射到在分区方案中指定的文件组。ALL：指定所有分区都映射到在“文件组名”中提供的文件组，或映射到主文件组（如果指定了[PRIMARY]）。文件组名：指定用来持有由“函数名”指定的分区的文件组的名称。分区分配到文件组的顺序是从分区1开始，按文件组在[,...]中列出的顺序进行分配。分区表创建【例3.3续】在数据库xscj中增加文件组fgroup1、fgroup2、fgroup3和fgroup4，根据前面定义的分区函数创建一个分区方案，将分区函数中的4个分区分别存放在这4个文件组中。T-SQL语句如下。ALTERDATABASExscjADDFILEGROUPfgroup1ALTERDATABASExscjADDFILEGROUPfgroup2ALTERDATABASExscjADDFILEGROUPfgroup3ALTERDATABASExscjADDFILEGROUPfgroup4CREATEPARTITIONSCHEMEPSchXH ASPARTITIONPFunXH TO(fgroup1,fgroup2,fgroup3,fgroup4)语句执行后，在xscj→存储→分区方案下，就会出现“PSchXH”项。2）删除分区方案DROPPARTITIONSCHEME分区方案名分区表创建3．使用分区方案创建分区表分区函数和分区方案创建以后就可以创建分区表了。创建分区表使用CREATETABLE语句，只要在ON关键字后指定分区方案和分区列即可。【例3.3续】在数据库xscj中创建学生子表，分区方案是对该表学号列分区。T-SQL语句如下。USExscjCREATETABLExsb1_p(

学号 char(6) NOTNULLPRIMARYKEY,

姓名 char(8) NOTNULL,

出生日期 date NULL, )ONPSchXH(学号)【例3.3续】删除学生子表分区及其分区方案和分区函数。USExscjDROPTABLExsb1_pDROPPARTITIONSCHEMEPSchXHDROPPARTITIONFUNCTIONPFunXH03表结构修改表结构修改语法格式如下：ALTERTABLE表名 ALTERCOLUMN 列名

列定义… |ADD <列定义> |DROPCOLUMN 列名其中，ALTERCOLUMN子句修改已有列的属性；ADD子句添加新的列；DROPCOLUMN子句删除已有的列。【例3.4】在xsb1_p表中增加1个新列“奖学金等级”，然后再删除该列。T-SQL语句如下。（1）增加“奖学金等级”列：USExscjALTERTABLExsb1_p ADD奖学金等级tinyintNULL说明：执行后，“xscj”中dbo.xsb1_p表就出现了“奖学金等级”列。如果原表中存在和添加列同名的列，则语句运行将出错。（2）删除“奖学金等级”列：ALTERTABLExsb1_p DROPCOLUMN奖学金等级表结构修改【例3.4续】将xsb1_p表“姓名”的列长度由原来的8改为10，“出生日期”的列的数据类型由原来的date改为smalldatetime。T-SQL语句如下。ALTERTABLExsb1_p ALTERCOLUMN姓名char(10)ALTERTABLExsb1_p ALTERCOLUMN出生日期smalldatetime说明：（1）姓名列长度由8改为10（变大）是可以的，但如果变小，则需要该列所有数据均不超过修改后的长度，否则不能执行。（2）若表中该列所存数据的数据类型与将要修改的列类型冲突，则发生错误。04删

除

表删除表语法格式：DROPTABLE表名其中，“表名”是要被删除的表的名称。【例3.4续】删除xsb1_p表。T-SQL语句如下。USExscjDROPTABLExsb1_pGO05界面方式创建表创建表结构修改表结构删除表界面方式创建表1.创建表结构【例3.5】界面方式创建xsb、kcb和cjb表。（1）在“对象资源管理器”中展开“数据库”，右击“xscj”数据库菜单下的“表”选项，在弹出的快捷菜单中选择“新建表”菜单项，打开“表设计器”窗口，如图。（2）在“表设计器”窗口中，根据已经设计好的xsb的表结构分别输入或选择各列的名称、数据类型、是否允许为空值等属性。界面方式创建表（3）在“列属性”选项卡包含当前列默认值或绑定、标识规范、说明等属性。例如，“专业”列的“默认值或绑定”项输入“'计算机'”，对应性别的默认值输入“1”。说明项是对列的注释。如图。界面方式创建表（4）在“学号”列上右击鼠标，选择“设置主键”菜单项，选择“设为主键”选项，学生表结构设计完成后的结果如图。（5）在表的各列属性均编辑完成后，单击工具栏中的“保存”按钮，出现“选择表名”对话框。在其中输入表名“xsb”，单击“确定”按钮即可创建xsb表。界面方式创建表2.修改表结构1）更改表名SQLServer2008中允许改变一个表的名字，但当表名改变后，与此相关的某些对象（如视图），以及通过表名与表相关的存储过程将无效。在“对象资源管理器”中选择需要更名的表xsb，右击鼠标，在弹出的快捷菜单上选择“重命名”菜单项，输入新的表名“student”，单击“Enter”键即可更改表名。2）增加列和删除列向表xsb中添加一个“奖学金等级”列，列的数据类型为“tinyint”，允许为空值。（1）在“xscj”数据库中选择表“dbo.xsb”，右击鼠标，在弹出的快捷菜单上选择“设计”菜单项，打开“表设计器”窗口。（2）在“表设计器”窗口中选择第一个空白行，输入列名“奖学金等级”，选择数据类型“tinyint”。（3）当向表中添加的列均输入完毕后，关闭该窗口，此时将弹出一个“保存更改”对话框，单击“是”按钮（或单击面板中的按钮），保存修改后的表。这时，dbo.xsb就增加了“奖学金等级”列。（4）在“表dbo.xsb设计器”窗口中选择需删除的列（例如，在xsb表中删除“奖学金等级”列），右击鼠标，在弹出的快捷菜单上选择“删除列”菜单项，该列即被删除。界面方式创建表3）修改列（1）具有以下特性的列不能修改：数据类型为timestamp的列。计算列。全局标识符列。用于索引的列（但若用于索引的列为varchar、nvarchar或varbinary数据类型时，可以增加列的长度）。用于由CREATESTATISTICS生成统计的列，如需修改这样的列，则必须先用DROPSTATISTICS语句删除统计。用于主键或外键约束的列。用于CHECK或UNIQUE约束的列。关联有默认值的列。（2）当改变列的数据类型时，要求满足下列条件：

原数据类型必须能够转换为新数据类型。新数据类型不能为timestamp类型。界面方式创建表【例3.6】在xsb表中，将“姓名”列名改为“name”，数据长度由8改为10，允许为空值。将“出生日期”列名改为“birthday”，数据类型由“date”改为“datetime”。在“对象资源管理器”中展开数据库→xscj→表，选择xsb，右击鼠标，选择“设计”菜单项，进入表xsb的设计窗口，单击需要修改的列，修改相应的属性。修改完后保存。3.删除表删除一个表时，表的定义、表中的所有数据以及表的索引、触发器、约束等均被删除。例如，选择xsb，右击鼠标，在弹出的快捷菜单上选择“删除”菜单项。系统弹出“删除对象”窗口。单击“确定”按钮即可删除xsb表。第3章

表的创建和操作——表记录的插入、修改和删除01插入记录单记录插入多记录插入插入查询记录插入记录1.单记录插入语法格式如下：INSERTINTO表名(列名,...)VALUES(值表)或者INSERTINTO表名 VALUES(值表)其中，“列名,…”就是要向表中插入数据的那些列的名称，之间以逗号（,）分隔；“值表”则指明列表中的每个列对应要插入的数据内容。可以对表中所有的列插入数据，也可以只对部分列插入数据。插入记录【例3.7】向xscj数据库的表xsb中插入下列记录。221101，王林，男，2004-02-10，计算机，15，NULLT-SQL语句如下。USExscjINSERTINTOxsb VALUES('221101','王林',1,'2004-02-10','计算机',15,NULL)使用以下语句与上面的语句执行效果是相同的。INSERTINTOxsb VALUES('221101','王林',1,'2004-02-10',DEFAULT,15,NULL);或者：USExscjINSERTINTOxsb(学号,姓名,性别,出生日期,总学分) VALUES('221101','王林',1,'2004-02-10',15)插入记录2.多记录插入一条SQL语句完成多条记录插入的任务。语法格式如下：INSERTINTO表名(列,...)VALUES(值表1),(值表2),...,(值表n);【例3.8】一次向xsb表中插入5行记录。221201，刘华，男，2004-06-10，通信工程，13，辅修计算机专业221202，王林，男，2004-01-29，通信工程，13221204，马琳琳，女，2003-02-10，通信工程，15 221206，李计，男，2003-09-20，通信工程，16211201，李红庆，男，2002-05-01，通信工程，43T-SQL语句如下。USExscjINSERTINTOxsbVALUES ('221201','刘华',1,'2004-06-10','通信工程',13,'辅修计算机专业'), ('221202','王林',1,'2004-01-29','通信工程',13,NULL), ('221204','马琳琳',0,'2003-02-10','通信工程',15,NULL), ('221206','李计',1,'2003-09-20','通信工程',16,NULL), ('211201','李红庆',1,'2002-05-01','通信工程',43,NULL)插入记录3.插入查询记录可以把一个表的记录移植到另一个表或者合并已有多张表的内容。语法格式如下：INSERTINTO目标表名(列名,...) SELECT列名,...FROM源表名[WHERE条件]其中，第一个“(列名,...)”是要插入记录的目标表中的列，第二个“列名,...”是从已存有记录的源表中查询出来的结果列，如果源表与目标表有着完全相同的结构，也可省略这两个列表。插入记录【例3.9】从表xsb中生成通信工程专业的学生表xsb2，包括num（学号）、name（姓名）、speiality(专业)，要求新表中的数据为结果集中前5行。建立表xsb2：T-SQL语句如下。USExscjCREATETABLExsb2( num char(6) NOTNULLPRIMARYKEY, name char(8) NOTNULL, speiality char(10) NULL)用INSERT语句向xsb2表中插入xsb表通信工程专业的学号、姓名、专业列。插入记录T-SQL语句如下。INSERTINTOxsb2 SELECT学号,姓名,专业 FROMxsb WHERE专业='通信工程'从xsb2表中查询结果：SELECT* FROMxsb2查询结果如图。02修改记录修改记录表中的数据行记录可以用命令修改。语法格式如下：UPDATE表名 SET列名=值,... [WHERE条件]

其中，SET子句指示要更新的列以及对应的新值，新值可以是表达式或关键字DEFAULT（默认值）。【例3.10】将xsb2表中学号为211201的学生学号改为计算机专业编号和专业改为“计算机”。USExscjUPDATExsb2 SETnum='211121',speiality='计算机' WHEREnum='211201'SELECT*FROMxsb2执行结果显示如图。03同步记录同步记录在SQLServer中新增加了一个MERGE语句，用于根据与源表连接的结果，对目标表执行插入、更新或删除操作。例如，根据一个表的数据在另一个表中插入、更新或删除有差异的行，可以对两个表进行信息同步。语法格式如下：MERGEINTO目标表[AS别名]USING源表ON条件 /*（1）*/ WHENMATCHED /*（2）*/ THENUPDATESET…|DELETE WHENNOTMATCHED /*（3）*/ THENINSERT[(列表)]VALUES(值表) WHENNOTMATCHEDBYSOURCE /*（4）*/ THENUPDATESET…|DELETE;同步记录说明：（1）目标表：指定要更新数据的表或视图。也可用AS为表定义别名。USING子句：指定用于更新的源表，ON指定在源表与目标表进行连接时所遵循的条件。（2）WHENMATCHED子句：目标表存在与源表匹配的行时，对这些行在THEN子句中指定修改或删除的操作。（3）WHENNOTMATCHED子句：如果存在与目标表中的行不匹配，则向目标表中插入该行记录。要插入的数据在THEN关键字后的INSERT子句中指定。（4）WHENNOTMATCHEDBYSOURCE子句：返回的行不匹配但满足其他查找条件的所有行，根据THEN关键字后的子句进行修改或删除。同步记录【例3.11】将xscj数据库中表xsb2与xsb表同步。语句如下：USExscjMERGEINTOxsb2 USINGxsbONxsb2.num=xsb.学号 WHENMATCHED THENUPDATESET=xsb.姓名,xsb2.speiality=xsb.专业 WHENNOTMATCHED THENINSERTVALUES(xsb.学号,xsb.姓名,xsb.专业);SELECT*FROMxsbSELECT*FROMxsb2运行结果显示如图。同步记录说明：（1）运行后xsb2为7条记录，比xsb表多了一条记录num为211121记录。（2）上述MERGEINTOxsb2…不包含下列子句：WHENNOTMATCHED THENINSERTVALUES(xsb.学号,xsb.姓名,xsb.专业)记录num为211201记录不会包含其中。（3）上述MERGEINTOxsb2…加入下列子句：WHENNOTMATCHEDBYSOURCE THENDELETE04删除记录删除符合条件的记录删除表的所有记录删除记录1．删除符合条件的记录语法格式如下。DELETEFROM表名 [WHERE条件]其中，WHERE子句为删除操作指定条件，若省略，则DELETE语句将删除所有记录。【例3.12】将xscj数据库的xsb2表中num为211201的记录删除。语句如下。DELETEFROMxsb2 WHEREnum='211201'SELECT*FROMxsb2删除记录2．删除表的所有记录使用TRUNCATETABLE语句将删除指定表中的所有记录，因此也称为清除表数据语句。其语法格式如下。TRUNCATETABLE表名说明：（1）该语句删除了指定表中的所有行，它比“DELETEFROM表名”速度快，且使用的系统和事务日志资源少。但表记录删除后不能恢复。（2）对于由外键（FOREIGNKEY）约束引用的表，只能使用“DELETE表名”语句删除所有记录。TRUNCATETABLE语句不能用于参与了索引视图的表。（3）TRUNCATETABLE语句执行后表结构及其列、约束、索引等保持不变，而新行标识所用的记数值重置为该列的初始值。（4）如果删除表记录的同时删除表结构，则使用“DROPTABLE表名”命令。删除记录【例3.13】删除xsb2表。语句如下：USExscjTRUNCATETABLExsb2SELECT*FROMxsb2 /*（a）*/GODROPTABLExsb2SELECT*FROMxsb2 /*（b）*/GO运行结果显示如图。

05界面方式操作表记录插入记录删除记录修改记录界面方式操作表记录1.插入记录插入记录的操作方法如下。将光标定位列，逐列输入列的值，按“Enter”键，光标自动跳到下一列。若当前列是表的最后一列，则该列编辑完后按“Enter”键，光标将自动跳到下一行的第一列，此时上一行输入的数据已经保存，可以增加下一行继续输入列值，也可采用鼠标移到下一列或者指定列。如图。界面方式操作表记录2.删除记录在表数据窗口中定位需删除的记录行，单击该行最前面的黑色箭头处选择全行，右击鼠标，选择“删除”菜单项。选择“删除”后，将出现一个确认对话框，单击“是”按钮将删除所选择的记录，单击“否”按钮将不删除该记录。3.修改记录先定位被修改的记录列，然后对该列值进行修改，修改之后将光标移到下一行即可保存修改的内容。第4章

数据库的查询和视图——关系运算关系运算1．选择选择运算的记号为

F(R)。其中，

是选择运算符，下标F是一个条件表达式，R是被操作的表。例如，若要在T表（表）中找出T1<20的行形成一个新表，则运算式为：

F(T)上式中F为T1<20，该选择运算的结果如表。T1T2T3T4T5

T1T2T3T4T51A133M

1A133M2B120N

2B120N3A21212O

3A21212O5D1024P

5D1024P20F14Q

100A328N

关系运算2．投影投影也是单目运算，该运算从表中选出指定的属性值组成一个新表，记为ΠA(R)。其中，A是属性名（即列名）表，R是表名。例如，在T表中对T1、T2和T5投影，运算式为：

T1,T2,T5(T)该运算得到如表所示的新表。T1T2T5T1T2T51A1M5DP2B1N20FQ3A20100A3N关系运算3．连接连接是把两个表中的行按照指定的条件进行拼接而形成新表，记为。其中，R、S是被操作的表，F是条件。例如，若表A和表B分别如表1和表2所示，则如表3所示，其中F为T1=T3。T1T2T1T2T1T21A6F2BT3T4T5T3T4T513M20NT1T2T3T4T51A13M2B20N表1A表表2B表表3关系运算数据库应用中最常用的是“自然连接”。进行自然连接运算要求两个表有共同属性（列），自然连接运算的结果表是在参与操作的两个表的共同属性上进行等值连接后，再去除重复的属性后所得的新表。自然连接运算记为，其中R和S是参与运算的两个表。例如，若表A和B分别如表1和表2所示，则

如表3所示。T1T2T3T1T2T3T1T2T310A1B15A1C220D2C2T1T4T5T6T1T4T5T61100A1D1200A2D11002B2C1510A2C2T1T2T3T4T5T65A1C210A2C220D2C20A2D1表1A表表2B表表3第4章

数据库的查询和视图——数据库的查询数据库的查询当用户登录到SQLServer后，即被指定一个默认数据库，通常是master数据库。使用“USE数据库名”可以选择当前要操作的数据库。例如，要选择xscj为当前数据库，可以使用如下语句。USExscj一旦选择了当前数据库后，若不对操作的数据库对象加以限定，则其后的命令均是针对当前数据库中的表或视图等进行的。下面介绍SELECT语句，它是T-SQL的核心。其语法格式如下。SELECT列名,…[INTO新表] /*（1）*/ FROM表名|视图名 /*（2）*/ [WHERE条件] /*（3）*/ [GROUPBY条件] /*（4）*/ [HAVING 条件] /*（5）*/ [ORDERBY…] /*（6）*/说明：（1）指定查询结果所要输出的列，还可以用INTO指定将查询结果存入新表。（2）指定查询源：表或视图。（3）指定查询条件。（4）指定查询结果分组条件。（5）指定查询结果分组统计条件。（6）指定查询结果排序顺序。01选择输出项选择指定的列替换查询结果中的数据计算列值消除结果集中的重复行限制结果集返回行数聚合函数选择输出项1．选择指定的列SELECT*|列名[AS]别名|表达式[AS]别名，...说明：（1）选择表中的部分列，各列名之间要以逗号分隔，“*”表示所有列。（2）用AS子句可更改查询结果的列标题，当自定义的列标题中含有空格时，必须使用引号将标题括起来。不允许在WHERE子句中使用列别名。【例4.1】分别查询学生表计算机专业所有记录和通信工程专业中学生的学号姓名和总学分。在SSMS窗口主菜单下面的工具栏，单击“新建查询”按钮，系统新建一个文本编辑窗口，在其中输入下列T-SQL语句：USExscj;SELECT*FROMxsbWHERE专业='计算机'; /*(a)*/SELECT学号ASID,姓名ASNAME,总学分AS'Totalcredit’ /*(b)*/ FROMxsb WHERE专业='通信工程';选择输出项单击工具栏“！执行”按钮”，查询结果如图。

选择输出项2．替换查询结果中的数据语句如下：CASEWHEN条件1THEN表达式1WHEN条件2THEN表达式2……ELSE表达式nEND选择输出项【例4.2】把成绩表百分制成绩分成等级。USExscj;SELECT学号,成绩, CASE WHEN成绩>=90THEN'优秀' WHEN成绩>=80AND成绩<90THEN'良好' WHEN成绩>=70AND成绩<80THEN'中等' WHEN成绩>=60AND成绩<70THEN'及格' ELSE'不及格' ENDAS等级 FROMcjb WHERE课程号='206';查询结果如图。选择输出项3．计算列值在结果中可以输出对包含列在内的表达式计算后的值，即可使用表达式作为输出项。【例4.3】查询2022年入学学生年龄。SELECT学号,姓名,出生日期,year(getdate())-year(出生日期)+1AS年龄 FROMxsb WHEREleft(学号,2)='22'查询结果如图。选择输出项4．消除结果集中的重复行对表查询输出部分列时，可能会出现重复行。可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行。【例4.4】查询学生表包含的专业和成绩表已有课程号。语句如下：SELECTDISTINCT专业 FROMxsbSELECTDISTINCT课程号 FROMcjb查询结果如图。

选择输出项5．限制结果集返回行数如果SELECT语句返回的结果集的行数非常多，那么可以使用TOP选项限制其返回的行数。TOP选项的语法格式如下。TOP表达式[PERCENT]指示只能从查询结果集返回指定数目的行或指定百分比数目的行。“表达式”可以是指定数目或百分比数。若带PERCENT关键字，则表示返回结果集的前（表达式值）％行。【例4.5】对xscj数据库的xsb表选择姓名、专业和总学分，返回结果集的前6行。语句如下：SELECTTOP6姓名,专业,总学分 FROMxsb选择输出项6．聚合函数聚合函数常常用于对一组值进行计算，然后返回单个值。聚合函数通常与GROUPBY子句一起使用。如果一个SELECT语句中有一个GROUPBY子句，则这个聚合函数对所有列起作用，如果没有，则SELECT语句只产生一行作为结果。SQLServer所提供的聚合函数列于表中。函

数

名说

明AVG求组中值的平均值BINARY_CHECKSUM返回对表中的行或表达式列表计算的二进制校验值，可用于检测表中行的更改CHECKSUM返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引CHECKSUM_AGG返回组中值的校验值COUNT求组中项数，返回int类型整数COUNT_BIG求组中项数，返回bigint类型整数GROUPING产生一个附加的列GROUPING_ID为聚合列列表中的每一行创建一个值以标识聚合级别MAX求最大值MIN求最小值SUM返回表达式中所有值的和STDEV返回给定表达式中所有值的统计标准偏差STDEVP返回给定表达式中所有值的填充统计标准偏差VAR返回给定表达式中所有值的统计方差VARP返回给定表达式中所有值填充的统计方差选择输出项【例4.6】求选修101号课程的学生人数、最高分、最低分和平均分，并统计学生总数和专业个数。语句如下：SELECT'计算机导论',COUNT(成绩)人数,MAX(成绩)最高分,MIN(成绩)最低分,AVG(成绩)平均分 FROMcjb WHERE课程号='101' /*(a)*/SELECTCOUNT(*)AS'学生总数',COUNT(DISTINCT专业)AS'专业个数' FROMxsb /*(b)*/查询结果如图。

02查询条件表达式比较模式匹配范围比较空值比较EXCEPT和INTERSECT全文索引查询条件1．表达式比较语法格式如下：表达式1比较运算符表达式2说明：（1）“表达式”是除text、ntext和image以外类型的表达式。比较运算符共有9个。（2）当两个表达式的值均不为空值（NULL）时，比较运算返回逻辑值TRUE（真）或FALSE（假）。而当两个表达式的值中有一个为空值或都为空值时，比较运算将返回UNKNOWN。【例4.7】查询xsb表中通信工程专业总学分大于等于42的同学的情况。语句如下：SELECT* FROMxsb WHERE专业='通信工程'AND总学分>=42查询结果如图。查询条件2．模式匹配LIKE谓词用于指定字符串与指定的字符串相匹配，返回逻辑值TRUE或FALSE。LIKE谓词表达式的语法格式如下。表达式[NOT]LIKE模式串[ESCAPE转义符]说明：（1）表达式：一般为字符串表达式，在查询语句中可以是列名。（2）NOTLIKE：使用NOTLIKE与LIKE的作用相反。（3）模式串：可以使用通配符，表列出了LIKE谓词可以使用的通配符及其说明。通

配

符说

明%代表倒数_（下划线）代表单个字符[]指定范围（如[a-f]、[0-9]）或集合（如[abcdef]）中的任何单个字符[^]指定不属于范围（如[^a-f]、[^0-9]）或集合（如[^abcdef]）的任何单个字符查询条件（4）转义符：应为有效的SQLServer字符，没有默认值，且必须为单个字符。当模式串中含有与通配符相同的字符时，应通过该字符前的转义符指明其为模式串中的一个匹配字符。使用ESCAPE可指定转义符。【例4.8】查询xsb表中姓“王”且单名的学生情况。语句如下：SELECT* FROMxsb WHERE姓名LIKE'王_'查询结果如图。查询条件【例4.9】查询xsb表中学号20打头，倒数第1个数在1～5之间的学生学号、姓名及专业。语句如下：SELECT学号,姓名,专业 FROMxsb WHERE学号LIKE'20%_[1-5]'查询结果如图。查询条件3．范围比较（1）当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，其语法格式如下。表达式[NOT]BETWEEN表达式1AND表达式2当不使用NOT时，若“表达式”的值在“表达式1”与“表达式2”之间（包括这两个值），则返回TRUE，否则返回FALSE；当使用NOT时，返回值刚好相反。【例4.10】查询xsb表中不在2002年10～12月出生的学生情况。语句如下：SELECT学号,姓名,专业,出生日期 FROMxsb WHERE出生日期NOTBETWEEN'2002-10-1'AND'2002-12-31'查询结果如图。查询条件（2）使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的语法格式如下。表达式IN(表达式,…)【例4.11】查询xsb表中专业为“软件工程”或“通信工程”的学生情况。语句如下：SELECT* FROMxsb WHERE专业IN('软件工程','通信工程')该语句与下列语句等价。SELECT* FROMxsb WHERE专业='软件工程'OR专业='通信工程'查询条件4．空值比较当需要判定一个表达式的值是否为空值时，使用ISNULL关键字，其语法格式如下。表达式IS[NOT]NULL当不使用NOT时，若表达式的值为空值，则返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例4.12】查询包括备注内容的学生情况。语句如下：SELECT姓名,备注 FROMxsb WHERE备注ISNOTNULL查询结果如图。查询条件5.EXCEPT和INTERSECTEXCEPT和INTERSECT用于比较两个查询的结果，返回非重复值。语法格式如下。查询EXCEPT|INTERSECT查询说明：（1）使用EXCEPT和INTERSECT比较两个查询的规则和UNION语句一样。（2）EXCEPT从左边的查询中返回右边查询没有找到的所有非重复值。INTERSECT返回INTERSECT左右两边查询都返回的所有非重复值。（3）EXCEPT或INTERSECT返回的结果集的列名与左侧的查询返回的列名相同。（4）ORDERBY子句中的列名或别名必须引用左侧查询返回的列名。查询条件【例4.13】查找计算机专业出生日期不大于2004-1-1的学生信息。语句如下。SELECT*FROMxsbWHERE专业='计算机’ EXCEPT SELECT*FROMxsbWHERE出生日期>'2004-1-1'查询结果如图。查询条件6．全文索引1）CONTAINS谓词CONTAINS谓词用于在表中搜索指定的字符串，可以是精确匹配，也可以是模糊匹配，还可以是加权匹配，这样可以在表中搜索指定的单词、短语或近义词等。新建全文索引步骤如下：（1）启动全文搜索服务这项服务默认是禁用的。在Windows命令行键入“SQLServerManager13.msc”按“Enter键”，打开“SqlServerConfigurationManager（SQLServer配置管理器）”窗口，选择启动全文搜索服务。（2）创建全文目录在“对象资源管理器”面板中展开数据库“xscj”，选择“存储”目录下的“全文目录”，右击，在弹出的快捷菜单中选择“新建全文目录”命令，在打开的“新建全文目录-xscj”窗口中填写全文目录名称，如“fulltext”，单击“确定”按钮。查询条件（3）注册全文索引表在全文目录中注册需要全文索引的表。在“全文目录”目录下右击刚刚新建的全文目录“fulltext”，在弹出的快捷菜单中选择“属性”命令，打开“全文目录属性-fulltext”窗口。选择“表/视图”选项卡，选择需要全文索引的表，如“dbo.xsb”，单击按钮，在“合格列”选项组中会显示能够进行全文索引的列，勾选需要的列（这里勾选“姓名”和“备注”）复选框，单击“确定”按钮。在全文目录中注册了需要全文索引的表后，即可使用CONTAINS谓词对表进行包含式查询。CONTAINS谓词语法格式如下：CONTAINS(列名|*,'包含查询条件')查询条件说明：（1）“列名”表示在指定的列中搜索，可以指定多个列，类型为char、varchar、nchar、nvarchar、text、ntext、image、xml和varbinary(MAX)的列是可进行全文搜索的有效列；“*”表示在所有列中搜索。（2）包含查询条件指定要在列中搜索的文本和匹配条件。【例4.14】使用CONTAINS谓词搜索xsb表中包含字符“计算机”的所有行。语句如下：SELE