ch5 SQL Server之数据表

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,200810,第五章,SQL Server,表操作,5.1,SQL Server,中的数据类型,5.2 创建表,5.3 修改表的结构,5.4 向表中添加记录,5.5 修改表中的记录,5.6 删除表中的记录,5.7 删除表,账号,wxy,密码,wxy,5.1,SQL Server,中的数据类型,在,SQL Server2005,中，每个列、局部变量、表达式和参数都有其各自的数据类型。指定对象的数据类型相当于定义了该对象的四个特性：,（,1,）对象所含的数据类型，如字符、整数或二进制数。,（,2,）所存储值的长度或它的大小。,（,3,）数字精度（仅用于数字数据类型）。,（,4,）小数位数（仅用于数字数据类型）。,SQL Server,提供系统数据类型集，定义了可与,SQL Server,一起使用的所有数据类型；另外用户还可以使用,Transact-SQL,或,.NET,框架定义自己的数据类型，它是系统提供的数据类型的别名。每个表可以定义至多,250,个字段，除文本和图像数据类型外，每个记录的最大长度限制为,1962,个字节。,SQL Server,支持的数据类型,数据类型,符,号,标,识,整数型,bigint , int , smallint , tinyint,精确数值型,decimal , numeric,位型,Bit,（,0,、,1,）,真假逻辑关系（,on/off,、,yes/no,、,true/false,货币型,money , smallmoney,浮点型,float , real,),字符型,char , varchar,文本型,text,ntext,Unicode,字符型,nchar , nvarchar,日期时间类型,datetime , smalldatetime,二进制型,binary , varbinary,图象型,Image,、,word,文档、,execl,文档、图象数据,时间戳型,Timestamp,其他,cursor ,sql_variant , table , uniqueidentifier,，,XML,系统数据类型,1,、,精确数字类型,；,2,、,近似数字类型,；,3,、,字符数据类型,；,4,、,Unicode,字符数据类型,；,5,、,日期和时间类型,；,6,、,二进制字符数据类型,；,7,、其它数据类型。,(1),整数型,bigint,：,大整数，数范围为,-2,63,2,63,-1,，其精度为,19,，小数位数为,0,，长度为,8,字节。,int,：,整数，数范围为,-2,31,2,31,- 1,，其精度为,10,，小数位数为,0,，长度为,4,字节。,smallint,：,短整数，数范围为,-2,15,2,15,- 1,，其精度为,5,，小数位数为,0,，长度为,2,字节。,tinyint,：,微短整数，数范围为,0,255,，长度为,1,字节，其精度为,3,，小数位数为,0,，长度为,1,字节。,下面分别说明系统数据类型,(2),精确整数型,decimal,和,numeric,可存储从,-10,38,+1,到,10,38,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,。,例：,create table decimal_table,(,c1 decimal(3,2),),insert decimal_table values(4.5678),select * from decimal_table,结果：,c1,4.57,(3),浮点型,(,不要求绝对精确时使用）,real,：,使用,4,字节存储数据，表数范围为,-3.40,38,3.40,38,，,数据精度为,7,位有效数字。,float,：,-1.79,308, 1.79,308,。,表示方法：尾数,E,阶数,Bit,称为位数据类型，其数据有两种取值：,0,和,1,，长度为,1,字节。在输入,0,以外的其他值时，系统均把它们当,1,看待。这种数据类型常作为逻辑变量使用，用来表示真、假或是、否等二值选择。,(4),位型,(5),货币型 （专门用于货币处理）,money,：,数据的数范围为,-2,63,2,63,-1,，其精度为,19,，小数位数为,4,，长度为,8,字节。,smallmoney,：,数范围为,2,31,2,31,- 1,，其精度为,10,，小数位数为,4,，长度为,4,字节。,注：,货币数值之前必须带有货币符号,”,$”,。,例：,create table money_table,(,c1,money,c2,smallmoney,),insert money_table values(,$,123456,$,1234),select *from money_table,结果：,C1 c2,123456.0000 1234.0000,(6),字符型（,ASCII,字符集）,字符型数据用于存储字符串，字符串中可包括字母、数字和其它特殊符号（如,#,、,、,&,等等）。,SQL Server,字符型包括两类：,固定长度,(,char,),或可变长度,(,varchar),字符数据类型。,例：,create table m,(,c1,char,(20),),insert m values(,a2#4,),select *from m,结果：,C1,a2#4, ,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,是“,统一字符编码标准,”，用于支持国际上非英语语种的字符数据的存储和处理。,text,类型可以表示最大长度为,2,31,-1,个字符，其数据的存储长度为实际字符数个字节。,ntext,可表示最大长度为,2,30,- 1,个,Unicode,字符，其数据的存储长度是实际字符个数的两倍（以字节为单位）。,(8),文本型 （较长的备注、日志信息等）,文本型包括,text,和,ntext,两类，分别对应,ASCII,字符和,Unicode,字符。,（,1,）,Binary,：其定义形式为,Binary,（,n,），数据的存储长度是固定的，即,n+4,个字节，当输入的二进制数据长度小于,n,时，余下部分填充,0,。,n,取值范围为,1,到,8,000,缺省为,1,。常用于存储图像等数据。,（,2,）,Varbinary,：其定义形式为,Varbinary,（,n,），数据的存储长度是变化的，它为实际所输入数据的长度加上,4,字节。其他含义同,Binary,。,(9),二进制型 （位数据流）表示：,Datetime,：,用于存储日期和时间的结合体，默认的格式是,MM DD YYYY hh:mm A.M./P.M,，此类型数据长度为,8,字节，日期和时间分别使用,4,个字节存储。,表示时间：公元,1753-1-1,零时起,9999-12-31,23,时,59,分,59,秒之间。,smalldatetime,：,表示时间：,1900-1-12079-6-6,注：,当插入数据或者在其他地方使用,Datetime,类型时，需要用单引号把它括起来。,标识符是,timestamp,。,若创建表时定义一个列的数据类型为时间戳类型，那么每当对该表加入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。,它可以反映数据库中数据修改的相对顺序。,(10),日期时间类型,(11),时间戳型,自定义数据类型,SQL Server,允许用户自定义数据类型，建立在,SQL Server,系统数据类型基础之上，,须指定,：,该类型的名称、系统数据类型、,Null/not null,。,SQL Server,为用户提供了两种方法来创建自定义数据类型：,（,1,）使用,SQL Server,管理平台创建用户自定义数据类型；,（,2,）利用系统存储过程创建用户自定义数据类型,sp_addtype typename= type,phystype= system_data_type, nulltype= null_type, owner= owner_name,5.2,创建表,空值,(NULL),：,通常表示未知、不可用或将在以后添加的数据。,列的,identity,（标识）属性,这种类型的列包含由系统,自动生成,的能够标识表中每一行数据的,惟一序列值,。例如：学生编号若作为标识列，那么每次向表中插入一条学生记录时，都会自动生成惟一的值作为学生编号，避免了人工添加带来的问题。,【,例,】,以,XSCJ,数据库中创建,xsqk,表为例说明通过,SQL Server,的管理平台创建表的操作过程。,创建课程表，名称为,KC,表结构如表,2.5,所示。,创建成绩表，名称为,cj,表结构如表所示。,步骤,1、打开,SQL Server,管理平台,2、选定服务器，展开。,3、选择数据库项，展开。,4、选定要操作的数据库，展开。,5、选择表项，,用鼠标右键点击，,选择新建表,一、使用管理平台创建表,定义列的以下属性：列名称、数据类型、长度、精度、小数位数、是否允许为空、默认值、标识列、标识列的初始值、标识列的增量值和是否有行的标识（默认值、标识列之类的可不填）。,单击工具栏的保存按钮，则出现输入新建表对话框，如图所示,输入新建表的名称后，单击“确定”按钮，即会将新表保存到数据库中。,设置主键,右击,单击,其他属性的设置,使用,T-SQL,语句创建主键,使用,Transact-SQL,语句操作法设置主键约束，其语法形式如下：,CONSTRAINT constraint_name,/,指定约束名称,PRIMARY KEY CLUSTERED|NONCLUSTERED,/,指定索引的类型：聚簇索引（缺省时默认）与非聚簇索引,（,column_name ,n,）,/,指定主键的列名，最多,16,个,例：,例,5-5,建立一个,SC,表，定义,SNO,，,CNO,共同组成,SC,的主键,程序清单如下：,CREATE TABLE SC,(SNO CHAR(5) NOT NULL,CNO CHAR(5) NOT NULL,SCORE NUMERIC(3),CONSTRAINT SC_PRIM PRIMARY KEY(SNO,CNO),语法格式：,CREATE TABLE,database_name.owner.| owner.,table_name,(, /*,列的定义*,| column_name AS computed_column_expression,/*,定义计算列*,/,| /*,指定表的约束*,/,),ON, | DEFAULT /*,指定存储表的文件组*,/,TEXTIMAGE_ON, | DEFAULT,/*,指定存储,text,、,ntext,和,image,类型数据的文件组*,/,二、使用,CREATE TABLE,创建表,:=,/*,指定列名、类型*,/,column_name datatype|identity|NOT NULL|NULL COLLATE /*,指定排序规则,DEFAULT constant_expression /*,指定默认值,/*,指定列为标识列*,/,|IDENTITY seed, incrementNOT FOR REPLICATION,ROWGUIDCOL/*,指定列为全局标识符列,n/*,指定列的约束,列名,列的数据类型,指出该列为标识符列,可取空值,不可取空值,【,例,】,设已经创建了数据库,XSCJ,，现在该数据库中需创建学生情况表,XS,。,举例说明表的建立,创建表,XS,的,T-SQL,语句如下：,USE,XSCJ /*,选择,XSCJ,为当前数据库,*,/,CREATE TABLE,XS,(,学号,char(6) NOT NULL,primary key,姓名,char(8) NOT NULL,专业名,char(10) NULL,性别,bit NOT NULL,出生时间,smalldatetime NOT NULL,总学分,tinyint NULL,备注,text NULL,),GO,5.3,修改表结构,使用,SQL Server,管理平台,使用,ALTER TABLE,修改表,注意,：,1、应尽量在表里没有数据时修改表。,2、如果增加一列到含有数据的表里，要保证你增加的这列允许,NULL,值。这是因为数据库里已有的行的此列中不会包含值。,3、如果在表里改变已经含有数据的列的数据类型，要保证数据类型兼容。例如，如果列中存在的数据是字符数据，需要把该列更改成字符数据类型。如果数据类型不兼容，更改将会失败。例如，你不能把图像数据转换成字符数据。,使用,SQL Server,管理平台修改表,对一个已存在的表可以进行的修改操作包括：,更改表名,增加列,删除列,修改已有列的属性（列名、数据类型、是否为空值）,(1),更改表名,SQL Server,中允许改变一个表的名字，但当表名改变后，与此相关的某些对象如视图，以及通过表名与表相关的存储过程将无效，建议一般不要更改一个已有的表名，特别是在其上定义了视图或建立了相关的表。,【,例,】,将,XS,表的表名改为,student,。,第,1,步,在,SQL Server,管理平台中展开需更名的表，在其上单击鼠标右键，在弹出的快捷菜单上选择“重命名”，如图所示。,第,2,步,在表名位置上输入新的表名，如图所示，按下回车键,(2),增加列,【,例,】,向表,XS,中添加一个“奖学金等级”列，“奖学金等级”列为微整型，允许为空值。,在,SQL Server,管理平台中展开需进行操作的表,XS,，在其上单击鼠标右键，在弹出的快捷菜单上选择“修改”，如图所示。,在,SQL Server,管理平台的表“,XS”,窗口中点击第一个空白行，输入列名“奖学金等级”，选择数据类型“,tinyint”,，如图所示。,增加新列,当需向表中添加的列均输入完毕后，点击关闭表“,XS”,窗口按钮，此时将弹出如图所示的对话框，单击“是”，保存修改后的表。,在,SQL Server,管理平台,中展开需进行操作的表,XS,，在其上单击鼠标右键，在弹出的快捷菜单上选择“修改”。,(3),删除列,添加列：,列允许空值或对列创建,DEFAULT,约束，则可以将列添加到现有表中。,删除列：,（,1,）用于索引；,（,2,）用于,CHECK,、,FOREIGN KEY,、,UNIQUE,或,PRIMARY KEY,约束；,（,3,）与,DEFAULT,定义关联或绑定到某一默认对象；,（,4,）绑定到规则；,（,5,）已注册支持全文；,（,6,）用作表的全文键。,注意：,具有以下特性的列不能被修改：,具有,text,、,ntext,、,image,或,timestamp,数据类型的列,计算列,全局标识符列,复制列,用于索引的列（但若用于索引的列为,varchar,、,nvarchar,或,varbinary,数据类型时，可以增加列的长度）,用于由,CREATE STATISTICS,生成统计的列。若需修改这样的列，必须先用,DROP STATISTICS,语句删除统计,用于主键或外键约束的列,用于,CHECK,或,UNIQUE,约束的列,关联有默认值的列,这里所罗列的特性。当改变列的数据类型时，要求：,原数据类型必须能够转换 为新数据类型；,新类型不能为,timestamp,类型；,如果被修改的是,IDENTITY,列，则新数据类型必须是有效的,IDENTITY,数据类型。,（,3,）修改已有列的属性,使用,ALTER TABLE,修改表,语法格式：,ALTER TABLE table, ,ALTER COLUMN,column_name /*,修改已有列的属性*,/,new_data_type ( precision , scale ) COLLATE NULL | NOT NULL |,ADD | DROP,ROWGUIDCOL, |,ADD,/*,增加新列*,/ | column_name AS computed_column_expression ,.n |, WITH CHECK | WITH NOCHECK ADD, ,.n |,DROP,/*,删除列*,/ CONSTRAINT constraint_name | COLUMN column ,.n | CHECK | NOCHECK ,CONSTRAINT,/*,增加、删除约束, ALL | constraint_name ,.n | ENABLE | DISABLE ,TRIGGER,/*,（取消）授权触发器, ALL | trigger_name ,.n ,在删除一个列之前，必须先删除基于该列的所有索引和约束,ALTER TABLE,修改表举例,【,例,】,设已经在数据库,XSCJ,中创建了表,XS,。先在表,XS,中增加,1,个新列,奖学金等级。然后在表,XS,中删除名为奖学金等级的列。,USE XSCJ,ALTER TABLE XS,ADD,奖学金等级,tinyint NULL,GO,用企业管理器显示,XSCJ,数据库,XS,表的结构。,USE XSCJ,ALTER TABLE XS,DROP COLUMN,奖学金等级,GO,【,例,】,修改表,XS,中已有列的属性：将名为“姓名”的列长度由原来的,8,改为,10,；将名为“出生时间”的列的数据类型由原来的,smalldatetime,改为,datetime,。,USE XSCJ,ALTER TABLE XS,ALTER COLUMN,姓名,char(10),ALTER COLUMN,出生时间,datetime,GO,5.4 向表中添加记录,使用SQL SERVER管理平台,使用INSERT语句,通过管理平台向表中添加记录,右击要操作的表名（如左图），选择“打开表”。插入记录将新记录添加在表尾，可以向表中插入多条记录。操作方法是：将光标定位到当前表尾的下一行，然后逐列输入列的值（如右图） 。,使用,INSERT,语句插入表数据,语法格式：,INSERT,INTO, table_name,/*,表名*,/,WITH (n),/*,指定表提示，可省略*,/,| view_name,/*,视图名*,/,| rowset_function_limited,/*,可以是,OPENQUERY,或,OPENROWSET,函数*,/, (column_list) /*,列表*,/,VALUES,/*,指定列值的子句*,/,(DEFAULT | NULL |expression ,n) /*,列值的构成形式*,/,| derived_table /*,结果集*,/,| exectute_statement /*,有效的,EXECTUTE,语句*,/,| DEFAULT VALUES /*,所有列均取默认值*,/,VALUES,子句中的值：,(1) DEFAULT,：指定为该列的默认值。这要求定义表时必须指定该列的默认值。,(2) NULL,：指定该列为空值。,(3) expression,：可以是一个常量、变量或一个表达式，其值的数据类型要与列的数据类型一致。注意表达式中不能有,SELECT,及,EXECUTE,语句。,【,例,】,向,XSCJ,数据库的表,XS,中插入如下的一行：,001112,罗林琳 计算机,0 1/30/1980 0:0:0 40,可以使用如下的,T-SQL,语句：,USE XSCJ,INSERT INTO XS,VALUES(001112,罗林琳,计算机, 0,1/30/1980 0:0:0, 40,NULL),GO,【,例,】,用如下的,CREATE,语句建立表,XS1,：,CREATE TABLE XS1,( num char(6) NOT NULL,name char(8) NOT NULL,speiality char(10) NULL,),用如下的,INSERT,语句向,XS1,表中插入数据：,INSERT INTO XS1,SELECT,学号,姓名,专业名,FROM XS,WHERE,专业名,=,计算机,上面这条,INSERT,语句的功能是：将,XS,表中专业名为计算机的各记录的学号、姓名和专业名列的值插入到,XS1,表的各行中。用如下的,SELECT,语句进行查询看插入结果：,SELECT *,FROM XS1,5.,5,修改表中记录,使用SQL SERVER管理平台,使用UPDATA语句,通过管理平台修改表记录,在管理平台中修改记录数据的方法是，先定位被修改的记录字段，然后对该字段值进行修改。,使用,UPDATE,语句修改数据,在,T-SQL,中，,UPDATE,语句可以用来修改表中的数据行。,语法格式,:,UPDATE, table_name WITH ( .n ) /*,修改表数据*,/,| view_name /*,修改视图数据*,/,| rowset_function_limited /*,可以是,OPENQUERY,或,OPENROWSET,函数*,/,SET,/*,赋予新值*,/, column_name = expression | DEFAULT | NULL /*,为列重新指定值*,/,| variable = expression /*,指定变量的新值*,/,| variable = column = expression /*,指定列和变量的新值*,/,n, FROM ,n /*,修改,table_source,数据, WHERE /*,指定条件*,/,| WHERE CURRENT OF /*,有关游标的说明*,/, GLOBAL cursor_name | cursor_variable_name,OPTION(,n) /*,使用优化程序*,/,(1),表：由,table_name,指定要从其中删除数据的表名，关键字,WITH,指定目标表所允许的一个或多个表提示。,(2),视图：由,view_name,指定要从其中删除数据的视图名，要注意该视图必须可以更新，并且正确引用了一个基本表。,(3) OPENQUERY,和,OPENROWSET,函数：由,rowset_function_limited,指定。,(4) table_source,：将在介绍,SELECT,语句时详细讨论。,【,例,】,将,XSCJ,数据库的,XS,表中学号为,001112,的学生的备注列值改为“三好生”。,USE XSCJ,UPDATE XS,SET,备注,=,好生,WHERE,学号,=001112,GO,用,SELECT,语句进行查询，可以发现表中学号为“,001112”,的行的备注字段值已被修改，如图所示。,【,例,】,将,XS,表中的所有学生的总学分都增加,10,。将姓名为“罗林琳”的同学的专业改为“通信工程”，备注改为“转专业学习”，学号改为,001241,。,UPDATE XS,SET,总学分,=,总学分,+10,GO,UPDATE XS,SET,专业名,= ,通信工程,备注,= ,转专业学习,学号,= 001241,WHERE,姓名,= ,罗林琳,GO,5.,6,删除表中记录,使用SQL SERVER管理平台,使用DELETE或TRANCATE TABLE语句,通过管理平台删除记录,当表中的某些记录不再需要时，要将其删除。在管理平台中删除记录的方法是：,第,1,步,在操作表数据的窗口中定位需被删除的记录行，即将当前光标移到要被删除的行，点击鼠标右键，在弹出的快捷菜单上选择“删除”功能项，如左图所示。,第,2,步,选择“删除”后，将出现右图所示的确认对话框，点击“是”按钮将删除所选择的记录，点击“否”按钮将不删除该记录。,1,使用,DELETE,语句删除数据,语法格式：,DELETE,FROM, table_name WITH ( .,n, ) /*,从表中删除数据*,/,| view_name /*,从视图删除数据*,/,| rowset_function_limited /*,可以是,OPENQUERY,或,OPENROWSET,函数*,/, FROM ,n /*,从,table_source,删除数据*,/, WHERE /*,指定条件*,/,| CURRENT OF GLOBAL cursor_name | cursor_variable_name,/*,有关游标的说明*,/,OPTION(,n) /*,使用优化程序*,/,使用,DELETE,或,TRANCATE,语句删除数据,说明：,FROM,子句,:,用于说明从何处删除数据。可以从四种类型的对象中删除数据：,(1),表：由,table_name,指定要从其中删除数据的表名，关键字,WITH,指定目标表所允许的一个或多个表提示。,(2),视图：由,view_name,指定要从其中删除数据的视图名，要注意该视图必须可以更新，并且正确引用了一个基本表。,(3) OPENQUERY,和,OPENROWSET,函数：由,rowset_function_limited,指定。,(4) table_source,：将在介绍,SELECT,语句时详细讨论。,例题,【,例,】,将,XSCJ,数据库的,XS,表中总学分小于,39,的行删除，使用如下的,T-SQL,语句：,USE XSCJ,DELETE FROM XS,WHERE,总学分,39,GO,【,例,】,将,XSCJ,数据库的,XS,表中备注为空的行删除,(,实际不做操作,),。,DELETE FROM XS,WHERE,备注,IS NULL,将,XSCJ,数据库的,XS,表中的所有行均删除,(,实际不做操作,),。,DELETE XS,2.,使用,TRUNCATE TABLE,语句删除表数据,使用,TRUNCATE TABLE,语句将删除指定表中的所有数据，因此也称其为清除表数据语句。,语法格式：,TRUNCATE TABLE name,对于由外键约束的表，不能使用,TRUNCATE TABLE,删除数据，,而应使用不带,WHERE,子句的,DELETE,语句，,另外,TRUNCATE TABLE,也不能用于参与了索引视图的表。,使用,DELETE,或,TRANCATE,语句删除数据,5.7,表的删除,使用,SQL Server,管理平台,使用,DROP TABLE,语法格式：,DROP TABLE table_name,其中,table_name,是要删除的表名。,要删除学生表,xs,，使用命令：,DROP TABLE xs,GO,删除一个表时，表的定义、表中的所有数据以及表的索引、触发器、约束等均被删除。设需将,XSCJ,数据库中的表,test,删除，操作过程为：,第,1,步,在“,SQL Server,管理平台”中展开数据库,XSCJ,，再展开表，在表,dbo_test,上点击鼠标右键，在弹出的快捷菜单上选择“删除”，如图所示。,使用,SQL Server,管理平台,第,2,步,系统弹出如图所示的“删除对象”对话框，单击“确定”按”选项，即可删除表。,单击“显示相关性”按钮，则会出现显示相关性对话框，当有对象依赖于该表时，该表就不能删除。,