SQL第3章管理数据表8921866

1Sqlserver2005程序设计与管理基础2第3章管理数据表3.1SQLServer表的概念3.2SQLServer中的数据类型3.3创建数据表3.4管理数据表33.1SQLServer表的概念1.表的概念在SQLServer数据库中，表定义为列的集合，与Excel电子表格相似，数据在表中是按行和列的格式组织排列的。每行代表唯一的一条记录，而每列代表记录中的一个域。学号姓名性别出生日期系部入学时间000101张三男1980计算机2000000201李伟男1981经济管理2000010101王丽女1982数学2001关系名(表名)属性（列或字段）关系模式学生表属性名记录或元组主键（码）43.1SQLServer表的概念2.表的设计在SQLServer创建表有如下限制：每个数据库里最多有20亿个表。每个表上最多可以创建一个聚集索引和249个非聚集索引。每个表最多可以配置1024个字段。每条记录最多可以占8060B，但不包括text字段和image字段。5第3章管理数据表3.1SQLServer表的概念3.2SQLServer中的数据类型3.3创建数据表3.4管理数据表63.2SQLServer2005数据类型1.二进制数据2.字符型数据类型3.Unicode字符数据4.日期时间型数据5.整数型数据6.精确数值型数据7.近似数值类型8.货币数据9.位类型数据73.2SQLServer2005数据类型1.二进制数据功能：常用于存储图像等数据，包括长二进制数据binary、变长二进制数据varbinary和image三种。类型存储空间大小(字节)最大取值说明Binary(n)N+4存储空间固定N∈[1,8000]默认为1输入不足n+4字节,补足后存储;超过则截断后存储.Varbinary(n)变长存储数据N∈[1,8000]默认为1输入不足n+4字节,按实际长度存储;超过则截断后存储.image231-183.2SQLServer2005数据类型2.字符型数据类型功能:用于存储汉字、英文字母、数字、标点和各种符号，必须由英文单引号括起来。类型存储空间大小(字节)最大取值说明char(n)固定长度存储字符串N∈[1,8000]默认为1输入不足n个字节,补足后存储;超过则截断后存储.varchar(n)按变长存储字符串N∈[1,8000]默认为1输入不足n字节,按实际长度存储;超过则截断后存储.text231-1个字节93.2SQLServer2005数据类型3.Unicode字符数据功能：存放Unicode字符数据.支持的字符范围更大，存储所需要的空间也更大。类型存储空间大小(字节)最大取值说明nchar(n)固定长度存储字符串N∈[1,4000]默认为1输入不足n个字节,补足后存储;超过则截断后存储.nvarchar(n)按变长存储字符串N∈[1,4000]默认为1输入不足n字节,按实际长度存储;超过则截断后存储.ntext230-1个字节103.2SQLServer2005数据类型4.日期时间型数据功能：用于存储日期和时间数据。类型存储范围datetime存储从1753年1月1日到9999年12月31日的日期和时间数据，精确度为3%秒。smalldatetime存储从1900年1月1日到2079年6月6日的日期和时间数据，精确度为分.注意:当使用日期格式数据时，在字符串中可以使用斜杠’/’,连字符’-’或句号.作为分隔符。113.2SQLServer2005数据类型5.整数型数据功能：用于存储整型数据。类型存储大小（字节）数据范围bigint8-263~263-1int4-231~231-1smallint2-215~215-1tinyint10~255123.2SQLServer2005数据类型6.精确数值型数据功能：用于存储带有小数点且小数点后位数确定的实数。类型说明数据范围decimal(p,[s])p指定存储数据的最大位数，不含小数点，p∈[1，38]；s指定存储的小数的最大位数，s∈[0，p].默认小数位是0。-1038+1~1038-1numeric(p,[s])133.2SQLServer2005数据类型7.近似数值类型功能：用于存储浮点数。类型说明数据范围float(n)n为精度，n∈[1，53]存放-1.79E+308~1.79E+308数值范围内的浮点数real存储大小为4个字节从-3.40E+38到3.40E+38之间的浮点数字数据143.2SQLServer2005数据类型8.货币数据类型存储大小数据范围money8个字节货币数据值介于-263与263-1之间，精确到货币单位的千分之十smallmoney4个字节货币数值介于-214,748.3648~+214.748,3647之间，精确到货币单位的千分之十153.2SQLServer2005数据类型9.位类型数据功能：位类型数据用于存储整数，只能取1、0或NULL，常用于逻辑数据的存取。说明：在位类型的字段中输入0和1之外的任何值，系统都会作为1来处理。如果一个表中有8个以下的位类型数据字段，则系统会用一个字节存储这些字段；如果表中有9个以上16个以下位类型数据字段，则系统会用两个字节来存储这些字段。16第3章管理数据表3.1SQLServer表的概念3.2SQLServer中的数据类型3.3创建数据表3.4管理数据表173.3创建数据表1.使用SSMS创建表创建表的步骤:1）定义表结构：给表的每一列取字段名，并确定每一列的数据类型、数据长度、列数据是否可以为空等。2）设置约束：设置约束是为了限制该列输入值的取值范围，以保证输入数据的正确性和一致性。3）添加数据：表结构建立完成之后，就可以向表中输入数据。183.3创建数据表1.使用SSMS创建表例1:请在student数据库中建立“学生基本信息”表的结构。注:主键的字段值不能为空,且字段值在表中必须唯一字段名字段数据类型长度是否为空约束学号char8否主键姓名char10否性别char2是默认值‘男’出生年月datetime8是籍贯varchar20是家庭住址varchar60是电话char15是所属班级char8是193.3创建数据表2.使用T-SQL语句创建表1）语法：CREATETABLE[database_name.[owner].|owner.]table_name({column_definition}|[{PRIMARYKEY|UNIQUE}[,…N]])column_definttion::={column_namedata_type}[[DEFAULTconstraint_expression]|[IDENTITY[(seed,increment)]]][column_constraint][,…n]203.3创建数据表语法注释：●database_name：指定创建的表所在的数据库，在当前数据库创建表时该项可以省略。●owner：指定表的拥有者，如果表的拥有者为当前用户，该项可以省略。●table_name：指定新建表的名称。●column_definttion：为表中字段的定义表达式。●column_name：为表中的字段名。●data_type：为字段的数据类型。●PRIMARYKEY：为主键约束关键字。●UNIQUE：为唯一约束关键字。●DEFAULT：为默认约束关键字。●IDENTITY：为自动编号标识。●Seed：为自动标识的开始值，默认为1。●Increment：为自动编号的步长或增量，默认为1。213.3创建数据表例2:创建带有参照约束的学生表stud_info，学生表的表结构定义如下表所示。“学号”字段为学生表的主键。字段名字段数据类型长度是否为空约束学号char8否主键姓名char10否性别char2是默认值‘男’出生年月datetime8是籍贯varchar20是家庭住址varchar60是电话char15是所属班级char8是223.3创建数据表创建“学生”表的代码如下：USEStudentGOCREATETABLEstud_info(学号char(8)PRIMARYKEY,姓名char(10)NOTNULL,性别char(2)DEFAULT‘男’,出生年月datetime,籍贯varchar(20),家庭住址varchar(60),电话char(15),所属班级char(8)）GO233.3创建数据表例3：在学生管理数据库中用T-SQL语句创建一个学生成绩表stud_score，表结构如下，其中包含标识列和计算列。243.3创建数据表实现的T-SQL语句:CREATETABLEstud_score(ScoreidintIDENTITY(1,1)PRIMARYKEY,Stud_nochar(8)NOTNULL,MathintDEFAULT0,ChineseintDEFAULT0,ComputerintDEFAULT0,total_scoreASMath+Chinese+computer)GO253.3创建数据表设置列的标识属性时，应注意:–该列的数据类型必须是decimal,int,numeric,smallint,bigint,tinyint中的一种，才可以设置标识属性。–标识列不允许为空值，也不能包含默认属性。–每个表中只允许有一个标识列，并且不可以修改。设置计算列时，应注意:–在计算列中不能添加如primarykey、unique、foreignkey、default等约束条件。–不能对计算列进行赋值。26第3章管理数据表3.1SQLServer表的概念3.2SQLServer中的数据类型3.3创建数据表3.4管理数据表273.4管理数据表3.4.1修改表结构3.4.2删除数据表3.4.3查看数据表283.4.1修改表结构1使用SSMS修改表结构例4：修改“student”库中“学生基本信息表”表结构。293.4.1修改表结构2使用T-SQL语言修改表1）语法：ALTERTABLEtable_name{[ALTERCOLUMNcolumn_name{new_data_type[(precision[,scale])][NULL|NOTNULL]}]|ADD{[column_definition]}[,...n]|DROP{[CONSTRAINT]constraint_name|COLUMNcolumn}[,...n]}[[DEFAULTconstant_expression][WITHVALUES]|[IDENTITY[(seed,increment)]]303.4.1修改表结构2）语法注释：•ALTERCOLUMN：修改表列属性的子句•ADD：增加列或约束的子句•DROPCOLUMN：删除表列的子句•table_name：需要修改表的表格名称•column_name：希望增加的字段名•data_type：需要增加的字段的数据类型名•collation_name：排序规则名313.4.1修改表结构例5：使用T-SQL代码修改学生基本信息表stud_info结构,增加字段“QQ号码”，数据类型为varchar，长度为15，可为空；增加入学时间字段，时间/日期类型，默认时间为‘2006-9-1’。USEstudentGOALTERTABLEstud_infoADDQQ号码varchar(15)GOALTERTABLEstud_infoADD入学时间datetimeDEFAULT‘2006-9-1’GO323.4.1修改表结构例6：使用T-SQL代码修改学生基本信息表stud_info结构，修改字段“家庭住址”的长度为100。实现的T-SQL语句USEstudentGOALTERTABLEstud_infoALTERCOLUMN家庭住址varchar(100)GO333.4.1修改表结构例7：使用T-SQL语句删除学生基本信息表stud_info的QQ号码字段。实现的T-SQL语句USEstudentGOA