之数据表市公开课一等奖省赛课获奖课件

第五章

SQLServer表操作5.1SQLServer中数据类型5.2创建表5.3修改表结构5.4向表中添加统计5.5修改表中统计5.6删除表中统计5.7删除表8/

账号wxy密码wxy\10制作人：王晓燕之数据表第1页5.1SQLServer中数据类型在SQLServer中，每个列、局部变量、表示式和参数都有其各自数据类型。指定对象数据类型相当于定义了该对象四个特征：（1）对象所含数据类型，如字符、整数或二进制数。（2）所存放值长度或它大小。（3）数字精度（仅用于数字数据类型）。（4）小数位数（仅用于数字数据类型）。

SQLServer提供系统数据类型集，定义了可与SQLServer一起使用全部数据类型；另外用户还能够使用Transact-SQL或.NET框架定义自己数据类型，它是系统提供数据类型别名。每个表能够定义至多250个字段，除文本和图像数据类型外，每个统计最大长度限制为1962个字节。\10制作人：王晓燕之数据表第2页SQLServer支持数据类型

数据类型符

号

标

识整数型bigint,int,smallint,tinyint准确数值型decimal,numeric位型Bit（0、1）真假逻辑关系（on/off、yes/no、true/false货币型money,smallmoney浮点型float,real)字符型char,varchar文本型text,ntextUnicode字符型nchar,nvarchar日期时间类型datetime,smalldatetime二进制型binary,varbinary图象型Image、word文档、execl文档、图象数据时间戳型Timestamp其它cursor,sql_variant,table,uniqueidentifier，XML系统数据类型1、准确数字类型；2、近似数字类型；3、字符数据类型；4、Unicode字符数据类型；5、日期和时间类型；6、二进制字符数据类型；7、其它数据类型。\10制作人：王晓燕之数据表第3页

(1)

整数型

bigint：大整数，数范围为-263

～263-1，其精度为19，小数位数为0，长度为8字节。

int：整数，数范围为-231

～231-1，其精度为10，小数位数为0，长度为4字节。

smallint：短整数，数范围为-215～215-1，其精度为5，小数位数为0，长度为2字节。

tinyint：微短整数，数范围为0～255，长度为1字节，其精度为3，小数位数为0，长度为1字节。

下面分别说明系统数据类型\10制作人：王晓燕之数据表第4页(2)准确整数型decimal和numeric可存放从-1038+1到1038–1固定精度和小数位数字数据，它们存放长度随精度改变而改变，最少为5字节，最多为17字节。但只有numeric能够用于带有identity关键字列。精度为1～9时，存放字节长度为5；精度为10～19时，存放字节长度为9；精度为20～28时，存放字节长度为13；精度为29～38时，存放字节长度为17。格式：decimal|numeric（p[,s]）p为精度，s为小数位数.p和s确定了准确总位数和小数位。其中p表示可供存放值总位数，默认设置为18；s表示小数点后位数，默认设置为0。[例：]createtabledecimal_table(

c1decimal(3,2))insertdecimal_tablevalues(4.5678)select*fromdecimal_table结果：c14.57\10制作人：王晓燕之数据表第5页(3)浮点型(不要求绝对准确时使用）

real：使用4字节存放数据，表数范围为-3.4038~3.4038，数据精度为7位有效数字。

float：-1.79308~1.79308

。表示方法：尾数E阶数Bit称为位数据类型，其数据有两种取值：0和1，长度为1字节。在输入0以外其它值时，系统均把它们当1对待。这种数据类型常作为逻辑变量使用，用来表示真、假或是、否等二值选择。(4)位型\10制作人：王晓燕之数据表第6页(5)货币型（专门用于货币处理）money：数据数范围为-263～263-1，其精度为19，小数位数为4，长度为8字节。smallmoney：数范围为–231

～231-1，其精度为10，小数位数为4，长度为4字节。[注：]货币数值之前必须带有货币符号”$”。[例：]createtablemoney_table(c1money,c2smallmoney)insertmoney_tablevalues($123456,$1234)select*frommoney_table结果：C1c2123456.00001234.0000\10制作人：王晓燕之数据表第7页(6)字符型（ASCII字符集）字符型数据用于存放字符串，字符串中可包含字母、数字和其它特殊符号（如#、@、&等等）。SQLServer字符型包含两类：固定长度(char)或可变长度(varchar)

字符数据类型。[例：]createtablem(c1char(20),)insertmvalues('a2#4')select*fromm结果：C1a2#4ٮٮٮٮٮٮٮٮٮٮٮٮٮٮٮٮ\10制作人：王晓燕之数据表第8页nchar[(n)]：nchar[(n)]为包含n个字符固定长度Unicode字符型数据，n

值在1与4,000之间，缺省为1。长度为2n字节。若输入字符串长度不足n，将以空白字符补足。

nvarchar[(n)]：nvarchar[(n)]为最多包含n个字符可变长度Unicode字符型数据，n

值在1与4,000之间，缺省为1。(7)Unicode字符型（unicode字符集）unicode字符集：存放要用两个字节才能存放双字节字符。比如：汉字、韩文、日文等。Unicode是“统一字符编码标准”，用于支持国际上非英语语种字符数据存放和处理。\10制作人：王晓燕之数据表第9页text类型能够表示最大长度为231-1个字符，其数据存放长度为实际字符数个字节。ntext可表示最大长度为230-1个Unicode字符，其数据存放长度是实际字符个数两倍（以字节为单位）。(8)文本型（较长备注、日志信息等）文本型包含text和ntext两类，分别对应ASCII字符和Unicode字符。\10制作人：王晓燕之数据表第10页（1）Binary：其定义形式为Binary（n），数据存放长度是固定，即n+4个字节，当输入二进制数据长度小于n时，余下部分填充0。n取值范围为1到8,000缺省为1。惯用于存放图像等数据。（2）Varbinary：其定义形式为Varbinary（n），数据存放长度是改变，它为实际所输入数据长度加上4字节。其它含义同Binary。(9)二进制型（位数据流）表示：\10制作人：王晓燕之数据表第11页Datetime：用于存放日期和时间结合体，默认格式是MMDDYYYYhh:mmA.M./P.M，这类型数据长度为8字节，日期和时间分别使用4个字节存放。表示时间：公元1753-1-1零时起~9999-12-3123时59分59秒之间。smalldatetime：表示时间：1900-1-1~~2079-6-6注：当插入数据或者在其它地方使用Datetime类型时，需要用单引号把它括起来。标识符是timestamp。若创建表时定义一个列数据类型为时间戳类型，那么每当对该表加入新行或修改已经有行时，都由系统自动将一个计数器值加到该列，即将原来时间戳值加上一个增量。它能够反应数据库中数据修改相对次序。(10)日期时间类型(11)时间戳型\10制作人：王晓燕之数据表第12页自定义数据类型

SQLServer允许用户自定义数据类型，建立在SQLServer系统数据类型基础之上，须指定：该类型名称、系统数据类型、Null/notnull。SQLServer为用户提供了两种方法来创建自定义数据类型：（1）使用SQLServer管理平台创建用户自定义数据类型；（2）利用系统存放过程创建用户自定义数据类型sp_addtype[@typename=]type,[@phystype=]system_data_type[,[@nulltype=]‘null_type’][,[@owner=]‘owner_name’]

\10制作人：王晓燕之数据表第13页5.2创建表空值(NULL)：通常表示未知、不可用或将在以后添加数据。列identity（标识）属性这种类型列包含由系统自动生成能够标识表中每一行数据惟一序列值。比如：学生编号若作为标识列，那么每次向表中插入一条学生统计时，都会自动生成惟一值作为学生编号，防止了人工添加带来问题。\10制作人：王晓燕之数据表第14页【例】以XSCJ数据库中创建xsqk表为例说明经过SQLServer管理平台创建表操作过程。列名数据类型长度是否为空值默认值说明学号定长字符型（char）6×无主键姓名定长字符型（char）8×无

专业名定长字符型（char）10√无

性别位型（bit）1×1男1，女0出生时间日期时间类型（smalldatetime）4×无

总学分整数型（tinyint）1√无

备注文本型（text）16（系统默认值）√无

\10制作人：王晓燕之数据表第15页创建课程表，名称为KC,表结构如表2.5所表示。列

名数据类型长度是否允许为空值默认值说明课程号定长字符型（char）3×无主键课程名定长字符型（char）16×无

开课学期整数型（tinyint）1×1只能为1—8课时整数型（tinyint）1×无

学分整数型（tinyint）1√无

\10制作人：王晓燕之数据表第16页创建成绩表，名称为cj,表结构如表所表示。列名数据类型长度是否允许为空值默认值说明学号定长字符型（char）6×无主键课程号定长字符型（char）3×无主键成绩整数型（tinyint）1√无

学分整数型（tinyint）1√无

\10制作人：王晓燕之数据表第17页[步骤]1、打开SQLServer管理平台2、选定服务器，展开。3、选择[数据库]项，展开。4、选定要操作数据库，展开。5、选择[表]项，用鼠标右键点击，选择新建表一、使用管理平台创建表\10制作人：王晓燕之数据表第18页

\10制作人：王晓燕之数据表第19页定义列以下属性：列名称、数据类型、长度、精度、小数位数、是否允许为空、默认值、标识列、标识列初始值、标识列增量值和是否有行标识（默认值、标识列之类可不填）。单击工具栏保留按钮，则出现输入新建表对话框，如图所表示输入新建表名称后，单击“确定”按钮，即会将新表保留到数据库中。\10制作人：王晓燕之数据表第20页设置主键右击单击其它属性设置\10制作人：王晓燕之数据表第21页使用T-SQL语句创建主键

使用Transact-SQL语句操作法设置主键约束，其语法形式以下：

CONSTRAINTconstraint_name/指定约束名称

PRIMARYKEY[CLUSTERED|NONCLUSTERED]/指定索引类型：聚簇索引（缺省时默认）与非聚簇索引

（column_name[,…n]）/指定主键列名，最多16个\10制作人：王晓燕之数据表第22页例：

例5-5建立一个SC表，定义SNO，CNO共同组成SC主键程序清单以下：CREATETABLESC(SNOCHAR(5)NOTNULL,CNOCHAR(5)NOTNULL,SCORENUMERIC(3),CONSTRAINTSC_PRIMPRIMARYKEY(SNO,CNO))\10制作人：王晓燕之数据表第23页语法格式：CREATETABLE

[database_name.[owner].|owner.]table_name({<column_definition>/*列定义*

|column_nameAScomputed_column_expression/*定义计算列*/|<table_constraint>}/*指定表约束*/)[ON{filegroup|DEFAULT}]/*指定存放表文件组*/[TEXTIMAGE_ON{filegroup|DEFAULT}]/*指定存放text、ntext和image类型数据文件组*/二、使用CREATETABLE创建表\10制作人：王晓燕之数据表第24页<column_definition>::=/*指定列名、类型*/{column_namedatatype|identity|NOTNULL|NULL} [COLLATE<collation_name>] /*指定排序规则[[DEFAULTconstant_expression]/*指定默认值/*指定列为标识列*/|[IDENTITY[seed,increment][NOTFORREPLICATION]]][ROWGUIDCOL] /*指定列为全局标识符列[<column_costraint>][,…n] /*指定列约束列名列数据类型指出该列为标识符列可取空值不可取空值\10制作人：王晓燕之数据表第25页【例】设已经创建了数据库XSCJ，现在该数据库中需创建学生情况表XS。举例说明表建立创建表XST-SQL语句以下：USE

XSCJ/*选择XSCJ为当前数据库*/CREATETABLEXS( 学号char(6)NOTNULLprimarykey,

姓名char(8)NOTNULL,

专业名char(10)NULL,

性别bitNOTNULL,

出生时间smalldatetimeNOTNULL,

总学分tinyintNULL,

备注textNULL)GO\10制作人：王晓燕之数据表第26页5.3修改表结构使用SQLServer管理平台使用ALTERTABLE修改表注意：1、应尽可能在表里没有数据时修改表。2、假如增加一列到含有数据表里，要确保你增加这列允许NULL值。这是因为数据库里已经有行此列中不会包含值。3、假如在表里改变已经含有数据列数据类型，要确保数据类型兼容。比如，假如列中存在数据是字符数据，需要把该列更改成字符数据类型。假如数据类型不兼容，更改将会失败。比如，你不能把图像数据转换成字符数据。\10制作人：王晓燕之数据表第27页使用SQLServer管理平台修改表对一个已存在表能够进行修改操作包含：更改表名增加列删除列修改已经有列属性（列名、数据类型、是否为空值）(1)更改表名SQLServer中允许改变一个表名字，但当表名改变后，与此相关一些对象如视图，以及经过表名与表相关存放过程将无效，提议普通不要更改一个已经有表名，尤其是在其上定义了视图或建立了相关表。【例】将XS表表名改为student。\10制作人：王晓燕之数据表第28页第1步在SQLServer管理平台中展开需更名表，在其上单击鼠标右键，在弹出快捷菜单上选择“重命名”，如图所表示。\10制作人：王晓燕之数据表第29页第2步在表名位置上输入新表名，如图所表示，按下回车键\10制作人：王晓燕之数据表第30页(2)增加列【例】向表XS中添加一个“奖学金等级”列，“奖学金等级”列为微整型，允许为空值。在SQLServer管理平台中展开需进行操作表XS，在其上单击鼠标右键，在弹出快捷菜单上选择“修改”，如图所表示。\10制作人：王晓燕之数据表第31页在SQLServer管理平台表“XS

”窗口中点击第一个空白行，输入列名“奖学金等级”，选择数据类型“tinyint”，如图所表示。增加新列\10制作人：王晓燕之数据表第32页当需向表中添加列均输入完成后，点击关闭表“XS”窗口按钮，此时将弹出如图所表示对话框，单击“是”，保留修改后表。\10制作人：王晓燕之数据表第33页 在SQLServer管理平台中展开需进行操作表XS，在其上单击鼠标右键，在弹出快捷菜单上选择“修改”。(3)删除列\10制作人：王晓燕之数据表第34页

添加列：列允许空值或对列创建DEFAULT约束，则能够将列添加到现有表中。删除列：

（1）用于索引；（2）用于CHECK、FOREIGNKEY、UNIQUE或PRIMARYKEY约束；（3）与DEFAULT定义关联或绑定到某一默认对象；（4）绑定到规则；（5）已注册支持全文；（6）用作表全文键。注意：\10制作人：王晓燕之数据表第35页含有以下特征列不能被修改：含有text、ntext、image或timestamp数据类型列计算列全局标识符列复制列用于索引列（但若用于索引列为varchar、nvarchar或varbinary数据类型时，能够增加列长度）用于由CREATESTATISTICS生成统计列。若需修改这么列，必须先用DROPSTATISTICS语句删除统计用于主键或外键约束列用于CHECK或UNIQUE约束列关联有默认值列这里所罗列特征。当改变列数据类型时，要求：原数据类型必须能够转换为新数据类型；新类型不能为timestamp类型；假如被修改是IDENTITY列，则新数据类型必须是有效IDENTITY数据类型。（3）修改已经有列属性\10制作人：王晓燕之数据表第36页使用ALTERTABLE修改表

语法格式：ALTERTABLEtable{[ALTERCOLUMNcolumn_name /*修改已经有列属性*/

{new_data_type[(precision[,scale])]

[COLLATE<collation_name>]

[NULL|NOTNULL]|{ADD|DROP}ROWGUIDCOL}

]

|ADD /*增加新列*/

{[<column_definition>]|column_nameAScomputed_column_expression

}[,...n]

|[WITHCHECK|WITHNOCHECK]ADD

{<table_constraint>}[,...n]

|DROP /*删除列*/

{[CONSTRAINT]constraint_name|COLUMNcolumn}[,...n]

|{CHECK|NOCHECK}CONSTRAINT/*增加、删除约束

{ALL|constraint_name[,...n]}

|{ENABLE|DISABLE}TRIGGER/*（取消）授权触发器

{ALL|trigger_name[,...n]}

}在删除一个列之前，必须先删除基于该列全部索引和约束\10制作人：王晓燕之数据表第37页ALTERTABLE修改表举例【例】设已经在数据库XSCJ中创建了表XS。先在表XS中增加1个新列——奖学金等级。然后在表XS中删除名为奖学金等级列。USEXSCJALTERTABLEXSADD奖学金等级tinyintNULLGO

用企业管理器显示XSCJ数据库XS表结构。USEXSCJALTERTABLEXSDROPCOLUMN奖学金等级GO\10制作人：王晓燕之数据表第38页【例】修改表XS中已经有列属性：将名为“姓名”列长度由原来8改为10；将名为“出生时间”列数据类型由原来smalldatetime改为datetime。USEXSCJALTERTABLEXSALTERCOLUMN姓名char(10)ALTERCOLUMN出生时间datetimeGO\10制作人：王晓燕之数据表第39页5.4向表中添加统计使用SQLSERVER管理平台使用INSERT语句\10制作人：王晓燕之数据表第40页经过管理平台向表中添加统计右击要操作表名（如左图），选择“打开表”。插入统计将新统计添加在表尾，能够向表中插入多条统计。操作方法是：将光标定位到当前表尾下一行，然后逐列输入列值（如右图）。\10制作人：王晓燕之数据表第41页使用INSERT语句插入表数据语法格式：INSERT[INTO]{table_name

/*表名*/WITH(<table_hint_limited>[…n]) /*指定表提醒，可省略*/|view_name

/*视图名*/|rowset_function_limited/*能够是OPENQUERY或OPENROWSET函数*/}{[(column_list)] /*列表*/{VALUES /*指定列值子句*/({DEFAULT|NULL|expression}[,…n]) /*列值组成形式*/|derived_table /*结果集*/|exectute_statement/*有效EXECTUTE语句*/}}|DEFAULTVALUES /*全部列均取默认值*/VALUES子句中值：(1)DEFAULT：指定为该列默认值。这要求定义表时必须指定该列默认值。(2)NULL：指定该列为空值。(3)expression：能够是一个常量、变量或一个表示式，其值数据类型要与列数据类型一致。注意表示式中不能有SELECT及EXECUTE语句。\10制作人：王晓燕之数据表第42页【例】向XSCJ数据库表XS中插入以下一行：001112罗林琳计算机01/30/19800:0:040能够使用以下T-SQL语句：USEXSCJINSERTINTOXSVALUES(‘001112’,’罗林琳‘,’计算机‘,0,’1/30/19800:0:0‘,40,NULL)GO\10制作人：王晓燕之数据表第43页\10制作人：王晓燕之数据表第44页【例】用以下CREATE语句建立表XS1：

CREATETABLEXS1( numchar(6)NOTNULL, namechar(8)NOTNULL, speialitychar(10)NULL)

用以下INSERT语句向XS1表中插入数据：

INSERTINTOXS1SELECT学号,姓名,专业名

FROMXS WHERE专业名=’计算机’上面这条INSERT语句功效是：将XS表中专业名为‘计算机’各统计学号、姓名和专业名列值插入到XS1表各行中。用以下SELECT语句进行查询看插入结果：SELECT*FROMXS1\10制作人：王晓燕之数据表第45页\10制作人：王晓燕之数据表第46页5.5修改表中统计使用SQLSERVER管理平台使用UPDATA语句\10制作人：王晓燕之数据表第47页经过管理平台修改表统计

在管理平台中修改统计数据方法是，先定位被修改统计字段，然后对该字段值进行修改。\10制作人：王晓燕之数据表第48页使用UPDATE语句修改数据在T-SQL中，UPDATE语句能够用来修改表中数据行。语法格式:UPDATE{table_nameWITH(<table_hint_limited>[...n])/*修改表数据*/|view_name /*修改视图数据*/|rowset_function_limited /*能够是OPENQUERY或OPENROWSET函数*/}SET /*赋予新值*/{column_name={expression|DEFAULT|NULL}/*为列重新指定值*/|@variable=expression /*指定变量新值*/|@variable=column=expression /*指定列和变量新值*/}[,…n]{{[FROM{<table_source>}[,…n]] /*修改table_source数据[WHERE<search_condition>] /*指定条件*/}|[WHERECURRENTOF /*相关游标说明*/{{[GLOBAL]cursor_name}|cursor_variable_name}]}[OPTION(<query_hint>[,…n])] /*使用优化程序*/(1)表：由table_name指定要从其中删除数据表名，关键字WITH指定目标表所允许一个或多个表提醒。(2)视图：由view_name指定要从其中删除数据视图名，要注意该视图必须能够更新，而且正确引用了一个基本表。(3)OPENQUERY和OPENROWSET函数：由rowset_function_limited指定。(4)table_source：将在介绍SELECT语句时详细讨论。\10制作人：王晓燕之数据表第49页【例】将XSCJ数据库XS表中学号为001112学生备注列值改为“三好生”。USEXSCJUPDATEXS SET备注=‘好生’

WHERE学号=001112GO用SELECT语句进行查询，能够发觉表中学号为“001112”行备注字段值已被修改，如图所表示。\10制作人：王晓燕之数据表第50页\10制作人：王晓燕之数据表第51页\10制作人：王晓燕之数据表第52页【例】将XS表中全部学生总学分都增加10。将姓名为“罗林琳”同学专业改为“通信工程”，备注改为“转专业学习”，学号改为001241。UPDATEXS SET总学分=总学分+10GOUPDATEXS SET专业名=‘通信工程’,

备注=‘转专业学习’,

学号=‘001241’ WHERE姓名=‘罗林琳’GO\10制作人：王晓燕之数据表第53页5.6删除表中统计使用SQLSERVER管理平台使用DELETE或TRANCATETABLE语句\10制作人：王晓燕之数据表第54页经过管理平台删除统计当表中一些统计不再需要时，要将其删除。在管理平台中删除统计方法是：第1步在操作表数据窗口中定位需被删除统计行，即将当前光标移到要被删除行，点击鼠标右键，在弹出快捷菜单上选择“删除”功效项，如左图所表示。第2步选择“删除”后，将出现右图所表示确实认对话框，点击“是”按钮将删除所选择统计，点击“否”按钮将不删除该统计。\10制作人：王晓燕之数据表第55页1使用DELETE语句删除数据语法格式：DELETE[FROM]{table_nameWITH(<table_hint_limited>[...n])/*从表中删除数据*/|view_name /*从视图删除数据*/|rowset_function_limited/*能够是OPENQUER