SQL完全教程第3章管理数据表.ppt

Sql server 2005程序设计与管理基础1第第第第3 3章章章章 管理数据表管理数据表管理数据表管理数据表 3.1SQLServer表的概念表的概念3.2SQLServer中的数据类型中的数据类型3.3创建创建数据表数据表3.4管理数据管理数据表表23.1SQLServer3.1SQLServer3.1SQLServer表的概念表的概念表的概念表的概念表的概念表的概念1.1.表的概念表的概念 在在SQL ServerSQL Server数据库中，数据库中，表定表定义为义为列的集合，与列的集合，与ExcelExcel电电子表格相似，数据在表中是按行和列的格式子表格相似，数据在表中是按行和列的格式组织组织排列的。每排列的。每行代表唯一的一条行代表唯一的一条记录记录，而每列代表，而每列代表记录记录中的一个域。中的一个域。学号学号姓名姓名性别性别出生日期出生日期系部系部入学时间入学时间000101张三男1980计算机2000000201李伟男1981经济管理2000010101王丽女1982数学2001关系名(表名)属性（列或字段）关系模式学生表学生表学生表学生表属性名记录或元组主键（码）33.1SQLServer3.1SQLServer3.1SQLServer表的概念表的概念表的概念表的概念表的概念表的概念2.表设计的限制表设计的限制在在SQLServer创建表有如下限制：创建表有如下限制：每个数据库里最多有每个数据库里最多有20亿个表亿个表。每每个个表表上上最最多多可可以以创创建建一一个个聚聚集集索索引引和和249个个非非聚聚集集索索引引。每个表最多可以配置每个表最多可以配置1024个字段个字段。每每条条记记录录最最多多可可以以占占8060B，但但不不包包括括text字字段段和和image字段。字段。4第第第第3 3章章章章 管理数据表管理数据表管理数据表管理数据表 3.1SQLServer表的概念表的概念3.2SQLServer中的数据类型中的数据类型3.3创建创建数据表数据表3.4管理数据管理数据表表53.2SQLServer20053.2SQLServer20053.2SQLServer2005数据类型数据类型数据类型数据类型数据类型数据类型1.二进制数据二进制数据2.字符型数据类型字符型数据类型3.Unicode字符数据字符数据4.日期时间型数据日期时间型数据5.整数型数据整数型数据6.精确数值型数据精确数值型数据7.近似数值类型近似数值类型8.货币数据货币数据9.位类型数据位类型数据63.2SQLServer20053.2SQLServer20053.2SQLServer2005数据类型数据类型数据类型数据类型数据类型数据类型1.二进制数据二进制数据功能：常用于存储图像等数据，包括长二进制数据功能：常用于存储图像等数据，包括长二进制数据binary、变长二进制数据变长二进制数据varbinary和和image三种。三种。类型类型存储空间大小存储空间大小(字节字节)最大取值最大取值说明说明Binary(n)N存储空间固定存储空间固定N1,8000默认为默认为1输入不足输入不足,补足后存储补足后存储;超超过则截断后存储过则截断后存储.Varbinary(n)变长存储数据变长存储数据,存储大小为存储大小为实际长度实际长度+2N1,8000默认为默认为1输入不足输入不足n字节字节,按实际长按实际长度度+2个字节存储个字节存储;超过则超过则截断截断.image231-173.2SQLServer20053.2SQLServer20053.2SQLServer2005数据类型数据类型数据类型数据类型数据类型数据类型2.字符型数据类型字符型数据类型功能功能:用于存储汉字、英文字母、数字、标点和各种符号，用于存储汉字、英文字母、数字、标点和各种符号，必必须由英文单引号括起来须由英文单引号括起来。类型类型存储空间大小存储空间大小(字节字节)最大取值最大取值说明说明char(n)固定长度存储字固定长度存储字符串符串N1,8000默认为默认为1输入不足输入不足n个字节个字节,补足后补足后存储存储;超过则截断后存储超过则截断后存储.varchar(n)按变长存储字符按变长存储字符串串N1,8000默认为默认为1输入不足输入不足n字节字节,按实际长按实际长度度+2字节存储字节存储;超过则截断超过则截断.text231-1个字节个字节83.2SQLServer20053.2SQLServer20053.2SQLServer2005数据类型数据类型数据类型数据类型数据类型数据类型3.Unicode字符数据字符数据 功能：存放功能：存放Unicode字符数据字符数据.支持的字符范围更大，存储所需支持的字符范围更大，存储所需 要的空间也更大。要的空间也更大。类型类型存储空间大小存储空间大小(字节字节)最大取值最大取值说明说明nchar(n)固定长度存储固定长度存储字符串字符串N1,4000默认为默认为1输入不足输入不足n个字节个字节,补足后补足后存储存储;超过则截断后存储超过则截断后存储.nvarchar(n)按变长存储字按变长存储字符串符串N1,4000默认为默认为1输入不足输入不足n字节字节,存储大小存储大小是是2n+2个字节个字节;超过则截超过则截断断.ntext230-1个字节个字节93.2SQLServer20053.2SQLServer20053.2SQLServer2005数据类型数据类型数据类型数据类型数据类型数据类型4.日期时间型数据日期时间型数据功能：用于存储日期和时间数据。功能：用于存储日期和时间数据。类型类型存储范围存储范围datetime存储从存储从1753年年1月月1日到日到9999年年12月月31日的日期和时间日的日期和时间数据，精确度为数据，精确度为3%秒秒,用用4个字节的整数存储个字节的整数存储datetime值。值。smalldatetime存储从存储从1900年年1月月1日到日到2079年年6月月6日的日期和时间数日的日期和时间数据，精确度为据，精确度为1分钟分钟,用用2个字节的整数存储时间值。个字节的整数存储时间值。注意注意注意注意:当使用日期格式数据时，在字符串中可以使用当使用日期格式数据时，在字符串中可以使用当使用日期格式数据时，在字符串中可以使用当使用日期格式数据时，在字符串中可以使用 斜杠斜杠斜杠斜杠/,连字符连字符连字符连字符-或句号或句号或句号或句号.作为分隔符。作为分隔符。作为分隔符。作为分隔符。103.2SQLServer20053.2SQLServer20053.2SQLServer2005数据类型数据类型数据类型数据类型数据类型数据类型5.整数型数据整数型数据功能：用于存储整型数据。功能：用于存储整型数据。类型类型存储大小（字节）存储大小（字节）数据范围数据范围bigint8-263263-1int4-231231-1smallint2-215215-1tinyint10255113.2SQLServer20053.2SQLServer20053.2SQLServer2005数据类型数据类型数据类型数据类型数据类型数据类型6.精确数值型数据精确数值型数据功能：用于存储带有小数点且小数点后位数确定的实数。功能：用于存储带有小数点且小数点后位数确定的实数。类型类型说明说明数据范围数据范围decimal(p,s)p指定存储数据的最大位指定存储数据的最大位数，不含小数点数，不含小数点，p1，38；s指定存储的小数的指定存储的小数的最大位数最大位数，s0，p.默认小数位是默认小数位是0。-1038+11038-1numeric(p,s)123.2SQLServer20053.2SQLServer20053.2SQLServer2005数据类型数据类型数据类型数据类型数据类型数据类型7.近似数值类型近似数值类型功能：用于存储浮点数。功能：用于存储浮点数。类型类型说明说明数据范围数据范围float(n)n为精度，为精度，n1，53存放存放-1.79E+3081.79E+308数数值范围内的浮点数值范围内的浮点数real存储大小为存储大小为4个字个字节节从从-3.40E+38到到3.40E+38之之间的浮点数字数据间的浮点数字数据133.2SQLServer20053.2SQLServer20053.2SQLServer2005数据类型数据类型数据类型数据类型数据类型数据类型8.货币数据货币数据类型类型存储大小存储大小数据范围数据范围money8个字节个字节货币数据值介于货币数据值介于-263与与263-1之之间，精确到货币单位的千分之间，精确到货币单位的千分之十十smallmoney4个字节个字节货币数值介于货币数值介于-214,748.3648+214.748,3647之间，精确到货之间，精确到货币单位的千分之十币单位的千分之十143.2SQLServer20053.2SQLServer20053.2SQLServer2005数据类型数据类型数据类型数据类型数据类型数据类型9.位类型数据位类型数据功能：位类型数据用于存储整数，只能取功能：位类型数据用于存储整数，只能取 1、0 或或NULL，常用于逻辑数据的存取。常用于逻辑数据的存取。说明：说明：在位类型的字段中输入在位类型的字段中输入0 0和和1 1之外的任何值，系统都会之外的任何值，系统都会 作为作为1 1来处理。来处理。如果一个表中有如果一个表中有8 8个以下的位类型数据字段，则系统会用一个以下的位类型数据字段，则系统会用一 个字节存储这些字段；如果表中有个字节存储这些字段；如果表中有9 9个以上个以上1616个以下位类型数据个以下位类型数据 字段，则系统会用两个字节来存储这些字段。字段，则系统会用两个字节来存储这些字段。15第第第第3 3章章章章 管理数据表管理数据表管理数据表管理数据表 3.1SQLServer表的概念表的概念3.2SQLServer中的数据类型中的数据类型3.3创建创建数据表数据表3.4管理数据管理数据表表163.33.3创建数据表创建数据表创建数据表创建数据表1.使用使用SSMS创建表创建表创建表的步骤创建表的步骤:1）定定义义表表结结构构：给给表表的的每每一一列列取取字字段段名名，并并确确定定每每一一列列的的数数据类型、数据长度、列数据是否可以为空等。据类型、数据长度、列数据是否可以为空等。2）设设置置约约束束：设设置置约约束束是是为为了了限限制制该该列列输输入入值值的的取取值值范范围围，以保证输入数据的正确性和一致性。以保证输入数据的正确性和一致性。3）添加数据添加数据：表结构建立完成之后，就可以向表中输入数据。：表结构建立完成之后，就可以向表中输入数据。173.33.3创建数据表创建数据表创建数据表创建数据表1.使用使用SSMS创建表创建表例例1:请在请在student数据库中建立数据库中建立“学生基本信息学生基本信息”表的结构。表的结构。注注:主键的字段值不能为空主键的字段值不能为空,且字段值在表中必须唯一且字段值在表中必须唯一字段名字段名字段数据类型字段数据类型长度长度是否为空是否为空约束约束学号学号char8否否主键主键姓名姓名char10否否性别性别char2是是默默 认认 值值男男出生年月出生年月datetime8是是籍贯籍贯varchar20是是家庭住址家庭住址varchar60是是电话电话char15是是所属班级所属班级char8是是183.33.3创建数据表创建数据表创建数据表创建数据表2.使用使用T-SQL语句创建表语句创建表1）语法：语法：CREATETABLEdatabase_name.owner.|owner.table_name(column_definitioncolumn_definition|PRIMARYKEY|UNIQUE,N)ONfilegroup_name|default|primary:=column_namedata_typeDEFAULTconstraint_expression|IDENTITY(seed,increment),n193.33.3创建数据表创建数据表创建数据表创建数据表语法注释：语法注释：database_name：指定创建的表所在的数据库，在当：指定创建的表所在的数据库，在当前数据库创建表时该项可以省略。前数据库创建表时该项可以省略。owner：指定表的拥有者，如果表的拥有者为当前用：指定表的拥有者，如果表的拥有者为当前用户，该项可以省略。户，该项可以省略。table_name：指定新建表的名称。：指定新建表的名称。column_definttion：为表中字段的定义表达式。：为表中字段的定义表达式。column_name：为表中的字段名。：为表中的字段名。data_type：为字段的数据类型。：为字段的数据类型。PRIMARYKEY：为主键约束关键字。：为主键约束关键字。UNIQUE：为唯一约束关键字。：为唯一约束关键字。DEFAULT：为默认约束关键字。：为默认约束关键字。IDENTITY：为自动编号标识。：为自动编号标识。Seed：为自动标识的开始值，默认为：为自动标识的开始值，默认为1。Increment：为自动编号的步长或增量，默认为：为自动编号的步长或增量，默认为1。203.33.3创建数据表创建数据表创建数据表创建数据表 例例2:创建带有参照约束的学生表创建带有参照约束的学生表stud_info，学生表的表结构定义如，学生表的表结构定义如下表所示。下表所示。“学号学号”字段为学生表的主键。字段为学生表的主键。字段名字段名字段数据类型字段数据类型长度长度是否为空是否为空约束约束学号学号char8否否主键主键姓名姓名char10否否性别性别char2是是默默 认认 值值男男出生年月出生年月datetime8是是籍贯籍贯varchar20是是家庭住址家庭住址varchar60是是电话电话char15是是所属班级所属班级char8是是213.33.3创建数据表创建数据表创建数据表创建数据表创建创建“学生学生”表的代码如下：表的代码如下：USEStudentGOCREATETABLEstud_info(学号学号char(8)PRIMARYKEY,姓名姓名char(10)NOTNULL,性别性别char(2)DEFAULT男男,出生年月出生年月datetime,籍贯籍贯varchar(20),家庭住址家庭住址varchar(60),电话电话char(15),所属班级所属班级char(8)）GO223.33.3创建数据表创建数据表创建数据表创建数据表例例3：在学生管理数据库中用：在学生管理数据库中用T-SQL语句创建一个学生成绩表语句创建一个学生成绩表stud_score，表结构如下，其中包含标识列和计算列。，表结构如下，其中包含标识列和计算列。233.33.3创建数据表创建数据表创建数据表创建数据表实现的实现的T-SQL语句语句:CREATE TABLE CREATE TABLE stud_scorestud_score(ScoreidScoreid intint IDENTITY(1,1)PRIMARY KEY,IDENTITY(1,1)PRIMARY KEY,Stud_noStud_no char(8)NOT NULL,char(8)NOT NULL,Math Math intint DEFAULT 0,DEFAULT 0,Chinese Chinese intint DEFAULT 0,DEFAULT 0,Computer Computer intint DEFAULT 0,DEFAULT 0,total_scoretotal_score AS Math+Chinese+computer AS Math+Chinese+computer)GOGO243.33.3创建数据表创建数据表创建数据表创建数据表设置列的标识属性时，应注意设置列的标识属性时，应注意:该列的该列的数据类型数据类型必须是必须是decimal,int,numeric,smallint,bigint,tinyint中的一种，才可以设置标识属性。中的一种，才可以设置标识属性。标识列不允许为空值标识列不允许为空值，也，也不能包含默认不能包含默认属性。属性。每个表中只允许有一个标识列每个表中只允许有一个标识列，并且不可以修改。，并且不可以修改。设置计算列时，应注意设置计算列时，应注意:在在计算列中不能添加计算列中不能添加如如primarykey、unique、foreignkey、default等等约束约束条件。条件。不能对计算列进行赋值不能对计算列进行赋值。25第第第第3 3章章章章 管理数据表管理数据表管理数据表管理数据表 3.1SQLServer表的概念表的概念3.2SQLServer中的数据类型中的数据类型3.3创建创建数据表数据表3.4管理数据管理数据表表263.43.4管理数据表管理数据表管理数据表管理数据表 3.4.1修改表结构修改表结构3.4.2删除数据表删除数据表 3.4.3查看数据表查看数据表273 3.4.1.4.1修改表结构修改表结构修改表结构修改表结构1使用使用SSMS修改表结构修改表结构例例4：修改：修改“student”库中库中“学生基本信息表学生基本信息表”表结构。表结构。283.4.13.4.1修改表结构修改表结构修改表结构修改表结构2使用使用T-SQL语言修改表语言修改表1）语法：语法：ALTERTABLEtable_nameALTERCOLUMNcolumn_namenew_data_type(precision,scale)NULL|NOTNULL|ADD,.n|DROPCONSTRAINTconstraint_name|COLUMNcolumn,.nDEFAULTconstant_expressionWITHVALUES|IDENTITY(seed,increment)293.4.13.4.1修改表结构修改表结构修改表结构修改表结构2）语法注释：语法注释：ALTERCOLUMN：修改表列属性的子句修改表列属性的子句ADD：增加列或约束的子句增加列或约束的子句DROPCOLUMN：删除表列的子句删除表列的子句table_name：需要修改表的表格名称需要修改表的表格名称column_name：希望增加的字段名希望增加的字段名data_type：需要增加的字段的数据类型名需要增加的字段的数据类型名collation_name：排序规则名排序规则名 303.4.13.4.1修改表结构修改表结构修改表结构修改表结构例例5：使用：使用T-SQL代码修改学生基本信息表代码修改学生基本信息表stud_info结构结构,增加字增加字段段“QQ号码号码”，数据类型为，数据类型为varchar，长度为，长度为15，可为空；增加入，可为空；增加入学时间字段，时间学时间字段，时间/日期类型，默认时间为日期类型，默认时间为2006-9-1。USEstudentGOALTERTABLEstud_infoADDQQ号码号码varchar(15)GOALTERTABLEstud_infoADD入学时间入学时间datetimeDEFAULT2006-9-1GO313.4.13.4.1修改表结构修改表结构修改表结构修改表结构例例6：使用：使用T-SQL代码修改学生基本信息表代码修改学生基本信息表stud_info结结构，修改字段构，修改字段“家庭住址家庭住址”的长度为的长度为100。实现的实现的T-SQL语句语句USEstudentGOALTERTABLEstud_infoALTERCOLUMN家庭住址家庭住址varchar(100)GO323.4.13.4.1修改表结构修改表结构修改表结构修改表结构例例7：使用：使用T-SQL语句删除学生基本信息表语句删除学生基本信息表stud_info的的QQ号码字段。号码字段。实现的实现的T-SQL语句语句USEstudentGOALTERTABLEstud_infoDROPCOLUMNQQ号码号码GO 333.43.4管理数据表管理数据表管理数据表管理数据表 3.4.1修改表结构修改表结构3.4.2删除数据表删除数据表 3.4.3查看数据表查看数据表343.4.23.4.2删除数据表删除数据表删除数据表删除数据表1使用使用SSMS删除数据表删除数据表2使用使用T-SQL语句删除数据表语句删除数据表语法格式为：语法格式为：DROPTABLEtable_name例例8：使用：使用T-SQL语句删除学生基本信息表。语句删除学生基本信息表。实现的实现的T-SQL语句语句USEstudentGODROPTABLE学生基本信息表学生基本信息表GO353.43.4管理数据表管理数据表管理数据表管理数据表 3.4.1修改表结构修改表结构3.4.2删除数据表删除数据表 3.4.3查看数据表查看数据表363.43.4.3.3 查查查查看数据表看数据表看数据表看数据表1使用使用SSMS对象资源管理器查看表属性信息对象资源管理器查看表属性信息。在对象资源管理器中，右键单击在对象资源管理器中，右键单击“student”数据库数据库的学生基本信息表的学生基本信息表stud_info，从弹出的快捷菜单中选择，从弹出的快捷菜单中选择“属性属性”命令。命令。373.43.4.3.3 查查查查看数据表看数据表看数据表看数据表2使用使用T-SQL语句查看数据表语句查看数据表Execsp_helptable_name例例9:查看学生基本信息表查看学生基本信息表stud_info属性信息。属性信息。ExecSp_helpstud_info38小小小小小小结结结结结结 掌握掌握掌握掌握 1、使用、使用SSMS创创建、修改、建、修改、删删除数据表除数据表2、使用、使用T-SQL语句语句创创建、修改、建、修改、删删除数据表除数据表 了解了解了解了解 1、了解数据类型及、了解数据类型及SQL中创建表的限制中创建表的限制2、查看数据表信息、查看数据表信息39谢谢！40