SQLServer表的创建和操作.ppt

第第3章章 表的创建和操作表的创建和操作3.2 以界面方式创建表以界面方式创建表3.4 以命令方式操作表数据以命令方式操作表数据*3.1.1 表和表结构表和表结构表是在日常工作和生活中经常使用的一种表示数据及其关系的形式，表3.1就是用来表示学生情况的一个“学生”表。学号姓名性别出生时间专业总学分备注191301王林男1990-2-10计算机50191302程明男1991-2-1计算机50191303王燕女1989-10-6计算机50191304韦严平男1990-8-26计算机50191306李方方男1990-11-20计算机50191307李明男1990-5-1计算机54提前修完数据结构191308林一帆男1989-8-5计算机52班长*下面简单介绍与表有关的几个概念。（1）表结构。组成表的各列的名称及数据类型，统称为表结构。（2）记录。每个表包含若干行数据，它们是表的“值”，表中的一行称为一个记录。（3）字段。每个记录由若干个数据项构成，将构成记录的每个数据项称为字段。例如，表3.1中的表结构为（学号，姓名，性别，出生时间，专业，总学分，备注），包含7个字段，由5个记录组成。（4）空值。空值（NULL）通常表示未知、不可用或将在以后添加的数据。若一个列允许为空值，则向表中输入记录值时可不为该列给出具体值；而一个列若不允许为空值，则在输入时必须给出具体值。（5）关键字。若表中记录的某一字段或字段组合能唯一标识记录，则称该字段或字段组合为候选关键字（Candidatekey）。*列的数据类型可以是SQLServer提供的系统数据类型，也可以是用户定义的数据类型。SQLServer提供的数据类型如表3.2所示。数据类型符号标识整数型int,smallint,tiny,bigint精确数值型decimal,numeric浮点型real,float 货币型money,smallmoney位型Bit字符型字符型Unicode字符型字符型文本型文本型char,varchar,varchar(MAX)nchar,nvarchar,nvarchar(MAX)text,ntext二进制型图像型binary(n),varbinary(n),varbinary(MAX)Image日期时间型date,datetime,smalldatetime,datetime2,datetimeoffset,time时间戳型timestamp平面和地理空间数据类型geometry,geography其他sql_variant,uniqueidentifier,xml,hierarchyid*1整数型：整数型：int，smallint，tiny，bigint整数型包括4种类型，从标识符的含义就可以看出，它们的表示数范围逐渐缩小。2精确数值型：精确数值型：decimal，numeric精确数值型数据由整数部分和小数部分构成，其所有的数字都是有效位，能够以完整的精度存储十进制数。decimal和numeric在功能上完全等价。格式：numeric|decimal(p,s)，其中p为精度，s为小数位数，sp,默认值为0。存储1038+110381的固定精度和小数位的数字数据。类型名称数范围精度存储字节int整数2312311104smallint短整数215215152tinyint微短整数025531bigint大整数2632631198*3浮点型：浮点型：real，float浮点型不能精确表示数据的精度，用于处理取值范围非常大且对精确度要求不太高的数值量。类型数范围定义长度(n)精度字节real3.40E+383.40E+3812474float 1.79E+3081.79E+30825531584货币型：货币型：money，smallmoney用十进制数表示货币值。类型数范围小数位数精度字节money26326314198smallmoney231231141045位型：位型：bit它只存储0和1。当为bit类型数据赋0时，其值为0，而赋非0时，其值为1。字符串值TRUE转换为1，FALSE转换为0。*6字符型、字符型、Unicode字符型和文本型：字符型和文本型：char/nchar，varchar/nvarchar，text/ntext（1）char(n)：定长字符数据类型，其中n定义字符型数据的长度，n在18000之间。默认n=1。（2）varchar(n)：变长字符数据类型，n(18000)表示的是字符串可达到的最大长度。实际长度为输入字符串的实际字符个数，而不一定是n。（3）text：可以表示最大长度为2311个字符，其数据的存储长度为实际字符个数。（4）varchar(MAX)、nvarchar(MAX)：最多可存放231-1个字节的数据，可以用来替换text、ntext数据类型。*7二进制型和图像型：二进制型和图像型：binary(n)，varbinary(n)，varbinary(MAX)，Image（1）binary(n)：固定长度的n个字节二进制数据。n的取值范围为18000，默认为1。binary(n)数据的存储长度为n+4个字节。（2）varbinary(n)：n个字节变长二进制数据。（3）image（图像数据型）：用于存储图片、照片等。实际存储的是可变长度二进制数据，介于0与2311字节之间。该类型是为了向下兼容而保留的数据类型。（4）varbinary(MAX)：最多可存放231-1个字节的数据，推荐用户使用varbinary(MAX)数据类型来替代image类型。*8日期时间型：日期时间型：date，datetime，smalldatetime，datetime2，datetimeoffset，time日期时间类型数据用于存储日期和时间信息，用户以字符串形式输入日期时间类型数据，系统也以字符串形式输出日期时间类型数据。数据类型日期范围精确度说明date1.1.19999.12.31日期datetime1753.1.19999.12.313.33ms日期和时间分别给出smalldatetime1900.1.12079.6.6分日期和时间分别给出datetime21.1.19999.12.31hh:mm:ss.nnnnnnndatetime(n)表示n(=17)位微秒datetimeoffsetYYYY-MM-DDhh:mm:ss.nnnnnnn+|hh:mm带时区偏移量Timehh:mm:ss.nnnnnnntime(n)表示n(=17)位微秒*（1）日期部分的表示形式常用的格式如下：年月日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*（2）时间部分常用的表示格式如下：时:分10:20、08:05时:分:秒20:15:18、20:15:18.2时:分:秒:毫秒20:15:18:200时:分AM|PM10:10AM、10:10PM*9时间戳型：时间戳型：timestamp该类型反映系统对该记录修改的相对（相对于其他记录）顺序，它实际上是二进制格式数据，其长度为8字节。每当对该表加入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。一个表只能有一个timestamp列。10平面和地理空间数据类型：平面和地理空间数据类型：geometry，geography（1）geometry（平面空间数据类型）：它作为.NET公共语言运行时（CLR）数据类型实现，表示欧几里得（平面）坐标系中的数据。（2）geography（地理空间数据类型）：它作为.NET公共语言运行时（CLR）数据类型实现，表示圆形地球坐标系中的数据。SQLServer支持geography数据类型用于存储GPS纬度和经度坐标之类的椭球体（圆形地球）数据。*11其他数据类型：其他数据类型：sql_variant，uniqueidentifier，xml，hierarchyid（1）sql_variant：一种存储SQLServer支持的各种数据类型（除text、ntext、image、timestamp和sql_variant外）值的数据类型。sql_variant的最大长度可达8016字节。（2）uniqueidentifier：唯一标识符类型。系统将为这种类型的数据产生唯一标识值，它是一个16字节长的二进制数据。（3）xml：用来在数据库中保存xml文档和片段的一种类型，但是此种类型的文件大小不能超过2GB。（4）hierarchyid：可表示层次结构中的位置。*“学号”列：只有学号列能唯一标识一个学生，将学号列设为该表主键。学号值有一定的意义，例如，“191301”中“19”表示所属班级，“13”表示学生的年级，“01”表示学生在班级中的序号，所以学号列的可以是6位的定长字符型数据，数据类型为char(6)。“姓名”列：姓名一般不超过4个中文字符，可以采用8位定长字符型数据，数据类型为char(8)。“性别”列：只有“男”、“女”两种值，所以可以使用bit型数据，值1表示“男”，值0表示“女”，默认是1。“出生时间”列：该列可能进行日期运算，存放日期时间类型数据，列类型定为date。“专业”列：假定专业名最多为6个汉字，可定为12位定长字符型数据char(12)。“总学分”列：是整数型数据，值在0160之间，列类型定为int，默认是0。“备注”列：需要存放学生的备注信息，备注信息的内容在0500个字之间，所以应该使用varchar类型。*最后设计的xsb的表结构如表3.3所示。列名数据类型长度是否可空默认值说明学号定长字符型（char）6无主键，前2位表示班级,中间2位为年级号，后2位为序号姓名定长字符型（char）8无性别位型（bit）默认值11：男；0：女出生时间日期型（date）默认值无专业定长字符型（char）12无总学分整数型（int）默认值0备注不定长字符型（varchar）500无*参照xsb的表结构的设计方法，同样可以设计出其他两个表的结构，如表3.4所示为kcb的表结构，如表3.5所示为cjb的表结构。列名数据类型长度是否可空默认值说明课程号定长字符型（char）3无主键课程名定长字符型（char）16无开课学期整数型（tinyint）11范围为18学时整数型（tinyint）10学分整数型（tinyint）10范围为16列名数据类型长度是否可空默认值说明学号定长字符型（char）6无主键课程号定长字符型（char）3无主键成绩整数型（int）默认值0范围为0100*3.2.1 创建表创建表【例3.1】通过“对象资源管理器”创建表xsb。步骤如下：（1）打开“表设计器”。在“SSMS”的“数据库”中展开“pxscj”，右击“表”选项，选择快捷菜单“新建“表”菜单项，系统打开“表设计器”窗口，在“表设计器”窗口中，输入学生表（xsb）结构，如图3.1所示。*（2）设置“列属性”。在表设计器列属性卡中输入各列然后设置下列内容。不能为空：去掉“学号”、“姓名”和性别“允许Null值”列上的勾。设置主键：在“学号”列上右击鼠标，选择“设置主键”菜单项，该字段前就会显示小钥匙图标。默认值或绑定：专业字段设置为“计算机”；性别字段设置为1；总学分字段设置为0。列属性很多，上述属性是常规的属性，其他部分属性随着SQLServer的学习深入可以得到应用和理解。（3）设置表属性。在列编辑区域中，按右键，在快捷菜单中选择“属性”，右边表“属性”页中显示数据库名称为“pxscj”，用户修改（表名称）为“xsb”。*【例3.1续】修改“pxscj”数据库中的“xsb”表结构。修改表结构包括增加列、删除列、修改已有列的属性（列名、数据类型、是否为空值等）。在“对象资源管理器”中选择“pxscj”数据库中的“xsb”表，右击鼠标，在弹出的快捷菜单上选择“设计”菜单项，打开“表设计器”窗口。1加入新列和删除某列加入新列和删除某列加入新列：右击该列，选择“插入列”，在增加的空列中加入新列名称及其属性。删除某列：右击该列，选择“删除列”。2列没有值修改列属性列没有值修改列属性如果当前表没有输入数据，或者需要修改的列没有值，则可以直接修改。如果出现问题，可以先删除该列，再增加列。*3列有值修改列属性列有值修改列属性当表中有了记录后，一般不要轻易改变表结构，特别是不要改变列的数据类型，以免产生错误。在需要改变列的数据类型时，要求满足下列条件：（1）原数据类型必须能够转换为新数据类型。（2）新数据类型不能为timestamp类型。（3）如果被修改列属性中有“标识规范”属性，则新数据类型必须是有效的“标识规范”数据类型。*注意：如果不能通过界面方式修改表，在“SSMS”的面板中单击“工具”主菜单，选择“选项”子菜单，在出现的“选项”对话框中选择“设计器”，将“阻止保存要求重新创建表的更改”复选框前的钩去掉，如图3.2所示。*4更改表名更改表名在“对象资源管理器”中选择需要更名的表，右击鼠标，在弹出的快捷菜单上选择“重命名”菜单项，输入新的表名后确定。*在“对象资源管理器”中选择表，右击鼠标，在弹出的快捷菜单上选择“删除”菜单项。系统弹出“删除对象”窗口。单击“确定”按钮即可删除指定表。*【例3.2】以“pxscj”数据库中xsb、kcb、cjb表为例，介绍表数据记录的插入、修改和删除。1插入记录插入记录刚开始输入数据，光标定位在第1行，然后逐列-1输入列的值。输入完成后，将光标定位到当前表尾的下一行。插入记录将新记录添加在表尾，可以向表中插入多条记录。输入时需要注意：（1）没有输入数据的记录所有列显示为“NULL”。*（2）若表的某些列（例如学号、姓名）不允许为空值，则必须为该列输入值，否则系统显示错误信息。已经输入内容的列系统显示“！”提示，如图3.3(a)所示。（3）输入不允许为空值的列，其他列没有输入，光标定位就可以定位到下一行，此时设置默认值的列就会填入默认值，如图3.3(b)所示。*（4）性别字段为“bit”类型，用户需要输入“1”或者“0”，系统对应显示“True”和“False”。（5）输入的记录中的主键（学号）字段列不能重复值，否则在光标试图定位定位到下一行系统显示错误信息，并且不能离开该行，如图3.4所示。*图3.5所示为插入数据后的xsb表。*2删除记录删除记录当表中的某些记录不再需要时，要将其删除。在表数据窗口中定位需删除的记录行，单击该行最前面的黑色箭头处选择全行，右击鼠标，选择“删除”菜单项。选择“删除”后，将出现一个确认对话框，单击“是”按钮将删除所选择的记录，单击“否”按钮将不删除该记录。*3修改记录修改记录先定位被修改的记录的行，在列中直接进行修改，修改之后将光标移到下一行即可保存修改的内容。按照上述方法，向课程表（kcb）和成绩表（cjb）中输入样本记录，如图3.6和3.7所示。*3.3.1 创建表：创建表：CREATE TABLE1创建表命令创建表命令创建表命令的主要格式如下：CREATETABLE表名(|,.n);*列的定义可以是下列3种。1）列）列:=列名/*指定列名、列的数据类型*/NULL|NOTNULL/*指定是否为空*/CONSTRAINT约束名DEFAULT常量表达式/*指定默认值*/|IDENTITY(初值,增量)/*指定列为标识列*/ROWGUIDCOL/*指定列为全局标识符列*/*指定列的约束*/:=类型名(精度,小数位|max*2）计算列）计算列计算列中的值是通过其他列计算出来的，该列实际并不存放值。:=列名AS计算列表达式PERSISTEDNOTNULL*3）列集）列集列集用于XML列。【例3.3】设已经创建了数据库test1，现在该数据库中需创建学生情况表xsb1，该表的结构如表3.3所示。单击“新建查询”，在查询编辑器中输入下列T-SQL命令：USEtest1GOCREATETABLExsb1(学号char(6)NOTNULLPRIMARYKEY,姓名char(8)NOTNULL,性别bitNULLDEFAULT1,出生时间 dateNULL,-2005无date，应datetime专业char(12)NULLDEFAULT计算机,总学分intNULLDEFAULT0,备注varchar(500)NULL,年龄AS2015-year(出生时间)获取当前时间用getdate()函数)GO*Mysql中CREATETABLExsb1(学号char(6)NOTNULLPRIMARYKEY,姓名char(8)NOTNULL,性别bitNULLDEFAULT1,出生时间 dateNULL,-2005无date，应datetime专业char(12)NULLDEFAULT计算机,总学分intNULLDEFAULT0,备注varchar(500)NULL,-年龄AS2015-year(出生时间)PERSISTEDmysql中没有计算列，获取当前时间可以用now()函数)*2创建临时表创建临时表(以一个井号以一个井号(#)开头的那些表名开头的那些表名)在SQLServer中创建的表通常称为持久表。在数据库中，持久表一旦创建，则将一直存在，多个用户或者多个应用程序可以同时使用持久表。有时需要临时存放数据，例如，临时存储复杂的SELECT语句的结果。此后，可能要重复地使用这个结果，但这个结果又不需要永久保存。这时，可以使用临时表。用户可以像操作持久表一样操作临时表。只不过临时表的生命周期较短，当断开与该数据库的连接时，服务器会自动删除它们。存储在tempdb的sysobjects 表中的临时表如：createtable#abc(idintidentity,abcintnull)*修改表结构语法格式如下：ALTERTABLE表名ALTERCOLUMN列名,/*修改列属性*/|ADD/*添加列*/,.|DROP/*删除列*/CONSTRAINT约束名/*删除约束*/|COLUMN列名,.*1）命令主体）命令主体ALTERTABLE命令主体结构说明如下：（1）表名：要修改的表名。（2）ALTERCOLUMN子句：修改表中指定列的属性，“列名”给出要修改的列。若表中该列所存数据的数据类型与将要修改的列类型冲突，则发生错误。例如，原来char类型的列要修改成int类型，而原来列值包含非数字字符，则无法修改。（3）ADD子句：向表中增加新列，新列的定义方法与CREATETABLE命令中定义列的方法相同。一次还可以添加多个列，中间用逗号隔开。（4）DROP子句：从表中删除列或约束。（5）WITH子句：WITHCHECK|NOCHECK指定表中的数据是否用新添加的或重新启用的FOREIGNKEY或CHECK约束进行验证。*2）ALTER COLUMN子句子句该子句的内容格式为：ALTERCOLUMN列名类型名(精度,小位数)COLLATE排序名NULL|NOTNULL（1）类型名：为被修改列的新的数据类型。当要修改成数值类型时，可以使用“(精度,位数)”分别指定数值的精度和小数位数。（2）NULL|NOTNULL：表示将列设置为是否可为空，设置成NOTNULL时要注意表中该列是否有空数据。*2修改表结构命令举例修改表结构命令举例【例3.4】修改test1数据库xsb1表结构。（1）增加列：在test1数据库xsb1表中增加“入学时间”列。在查询编辑器中输入下列T-SQL命令：ALTERTABLExsb1ADD入学时间date如果原表中已经存在与添加列同名的列，则语句运行将出错。（2）修改列：修改表xsb1中已有列的属性：将名为“姓名”的列长度由原来的8改为10；将名为“入学时间”的列的数据类型由原来的date改为smalldatetime。在查询编辑器中输入下列T-SQL命令：ALTERTABLExsb1ALTERCOLUMN姓名char(10)ALTERTABLExsb1ALTERCOLUMN入学时间smalldatetime（3）删除列：删除入学时间和年龄列。在查询编辑器中输入下列T-SQL命令：ALTERTABLExsb1DROPCOLUMN入学时间,年龄*语法格式：DROPTABLE表名,.;其中，“表名”是要被删除的表的名称。*3.4.1 插入记录插入记录1插入记录命令插入记录命令插入记录使用INSERT语句，语法格式如下：INSERTTOP(表达式)PERCENTINTO表名|视图名(列表)VALUES(DEFAULT|NULL|表达式)/*指定列值*/|DEFAULTVALUES/*强制新行包含为每个列定义的默认值*/|SELECT命令*2插入记录命令举例插入记录命令举例【例3.5】向test1数据库的表xsb1中插入记录。（1）插入一行。231301,王一平,1,1990-02-10,计算机,50,NULLT-SQL命令如下：INSERTINTOxsb1VALUES(231301,王一平,1,1990-02-10,管理工程,50,NULL)插入上例数据也可以使用以下命令：INSERTINTOxsb1(学号,姓名,出生时间,专业,总学分)VALUES(191301,王林,1990-02-10,管理工程50)或者：INSERTINTOxsb1VALUES(191301,王林,DEFAULT,1990-02-10,管理工程,50,NULL);*（2）一次向表中插入2条记录。201301,王海,1,1996-05-10,软件工程,50,NULL201302,李娜,0,1996-04-12,软件工程,52,NULLT-SQL命令如下：INSERTINTOxsb1VALUES(201301,王海,1,1991-05-10,软件工程,50,NULL),(201302,李娜,0,1991-04-12,软件工程,52,NULL)*（3）从其他表的记录插入其中：从test1数据库表xsb1中生成软件工程专业的学生表（xsb2）。用界面方式或者CREATETABLE语句建立表xsb2，表结构与xsb1相同：用INSERT语句向xsb2表中插入数据：INSERTINTOxsb2SELECT*FROMxsb1WHERE专业=软件工程使用SELECT语句查询xb1和xsb2表记录。SELECT*FROMxsb1/*显示xsb1表记录*/GOSELECT*FROMxsb2/*显示xsb2表记录*/GO*执行结果如图3.8所示。*在T-SQL中，UPDATE语句可以用来修改表中的数据行。语法格式：UPDATETOP(表达式)PERCENT表名|视图名SET列名=表达式,/*赋予新值*/FROMWHERE|/*指定条件*/*【例3.6】将test1数据库的xsb1表中学号为“231301”的学生的备注值改为“外校互认学分课程”，同时将总学分+3。UPDATExsb1SET备注=外校互认学分课程,总学分=总学分+3WHERE学号=231301执行完上述语句后，xsb1表学号为“231301”的学生的记录如图3.9所示。*1删除符合条件记录删除符合条件记录语法格式：DELETETOP(表达式)PERCENTFROM表名|视图名|WHERE|/*指定条件*/【例3.7】在test1数据库的xsb1表中删除软件工程专业学生。DELETEFROMxsb1WHERE专业=软件工程执行完上述语句后，xsb1表记录如图3.10所示。*2删除表所有记录删除表所有记录使用TRUNCATETABLE语句将删除指定表中的所有数据，因此也称为清除表数据语句。语法格式：TRUNCATETABLE表名【例3.8】删除test1数据库的xsb1和xsb2表中的所有行。T-SQL命令如下：USEtest1DELETExsb1TRUNCATETABLExsb2GOSELECT*FROMxsb1/*显示没有记录*/SELECT*FROMxsb2/*显示没有记录*/GODROPTABLExsb2SELECT*FROMxsb2/*显示错误信息，因为xsb2已经没有了*/GO*执行结果如图3.11所示。*