表的创建与管理-第5章数据库对象的操作.pptx
- 文档编号:15802641
- 上传时间:2023-07-08
- 格式:PPTX
- 页数:143
- 大小:486.60KB
表的创建与管理-第5章数据库对象的操作.pptx
《表的创建与管理-第5章数据库对象的操作.pptx》由会员分享,可在线阅读,更多相关《表的创建与管理-第5章数据库对象的操作.pptx(143页珍藏版)》请在冰点文库上搜索。
第6章表的创建与管理,6.1数据类型6.2表的创建6.3表结构的修改6.4表的重命名与删除6.5向表中添加、更新、删除数据6.6数据完整性的概念与实施方法,上一章,返回目录,1.表的概念:
表是数据库的数据对象,用于存储和操作数据的一种逻辑结构,是一系列列的集合。
2.表的构成:
表由表头和若干行数据构成。
3.表的特性:
表中每一行代表一个惟一的记录,每列代表一个域。
教学过程:
例1:
学生成绩表,表名,表头,数据,行,列,4.对表的操作:
填表:
将数据写入表中。
修改:
改正表中的数据信息。
删除:
删除表中记录信息。
查询:
在表中按某些条件查找记录信息。
5.注意:
在SQLServer2005中,每个数据库最多可存储20亿个表,每个表可以有1024列,每行最多可以存储8060字节。
SQLServer中表分为:
永久表和临时表。
其中永久表在创建后,除非用户删除否则将一直存放在数据库文件中;而临时表则会在用户退出或进行系统修复时被自动删除。
6.数据类型:
(1)相关概念:
概念:
定义每个列所能存放的数据值和存储格式。
精度:
指数值数据中所存储的十进制数据的总位数。
例如:
tinyint类型可以表示范围是0255,其精度为3。
小数位数:
指数值数据中小数点右边可以有的数字位数的最大值。
例:
89.658,其精度为5,小数位数为3。
长度:
存储数据所用的字节数。
例:
tinyint所用长度为1字节。
(2)种类:
系统数据类型和用户自定义数据类型。
SQLServer2005中列的数据类型既可以是系统数据类型也可以是用户自定义数据类型。
6.1数据类型,6.1.1系统数据类型6.1.2自定义数据类型,6.1.1系统数据类型,1.整型数据类型2.浮点数据类型3.字符数据类型4.日期和时间数据类型5.文本和图形数据类型,6.货币数据类型7.位数据类型8.二进制数据类型9.特殊数据类型10.新增数据类型,具体种类:
1.整型数据类型,整型数据类型是最常用的数据类型之一,它主要用来存储数值,可以直接进行数据运算,而不必使用函数转换。
int(integer):
int(或integer)数据类型可以存储从-231(-2,147,483,648)到231-1(2,147,483,647)范围之间的所有正负整数。
Smallint:
可以存储从-215(-32,768)到215-1范围之间的所有正负整数。
Tinyint:
可以存储从0到255范围之间的所有正整数。
2.浮点数据类型,浮点数据类型用于存储十进制小数。
浮点数值的数据在SQLServer中采用只入不舍的方式进行存储。
Real:
可以存储正的或者负的十进制数值,最大可以有7位精确位数。
Float:
可以精确到第15位小数,其范围从-1.79E-308到1.79E+308。
Decimal和numeric:
Decimal数据类型和numeric数据类型完全相同,它们可以提供小数所需要的实际存储空间,但也有一定的限制,可以用2到17个字节来存储从-1038-1到1038-1之间的数值。
3.字符数据类型,字符数据类型可以用来存储各种字母、数字符号和特殊符号。
Char:
其定义形式为char(n),每个字符和符号占用一个字节的存储空间。
Varchar:
其定义形式为varchar(n)。
用char数据类型可以存储长达255个字符的可变长度字符串。
Nchar:
其定义形式为nchar(n)。
Nvarchar:
其定义形式为nvarchar(n)。
4.日期和时间数据类型,Datetime:
用于存储日期和时间的结合体。
它可以存储从公元1753年1月1日零时起到公元9999年12月31日23时59分59秒之间的所有日期和时间。
Smalldatetime:
与datetime数据类型类似,但其日期时间范围较小,它存储从1900年1月1日到2079年6月6日内的日期。
5.文本和图形数据类型,Text:
用于存储大量文本数据,其容量理论上为1到231-1(2,147,483,647)个字节,但实际应用时要根据硬盘的存储空间而定。
Ntext:
与text数据类型类似,存储在其中的数据通常是直接能输出到显示设备上的字符,显示设备可以是显示器、窗口或者打印机。
Image:
用于存储照片、目录图片或者图画,其理论容量为231-1(2,147,483,647)个字节。
6.货币数据类型,Money:
用于存储货币值,存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中,存储范围为-922337213685477.5808到922337213685477.5808,精度为货币单位的万分之一。
Smallmoney:
与money数据类型类似,但其存储的货币值范围比money数据类型小,其存储范围为-214748.3468到214748.3467。
7.位数据类型,Bit:
称为位数据类型,其数据有两种取值:
0和1,长度为1字节。
8.二进制数据类型,Binary:
其定义形式为binary(n),数据的存储长度是固定的,即n+4字节,当输入的二进制数据长度小于n时,余下部分填充0。
Varbinary:
其定义形式为varbinary(n),数据的存储长度是变化的,它为实际所输入数据的长度加上4字节。
其它含义同binary。
9.特殊数据类型,Timestamp:
亦称时间戳数据类型,它提供数据库范围内的唯一值,反应数据库中数据修改的相对顺序,相当于一个单调上升的计数器。
Uniqueidentifier:
用于存储一个16字节长的二进制数据类型,它是SQLServer根据计算机网络适配器地址和CPU时钟产生的唯一号码而生成的全局唯一标识符代码(GloballyUniqueIdentifier,简写为GUID)。
10.新增数据类型,Bigint:
用于存储从-263(-9,223,372,036,854,775,807)到263-1(9,223,372,036,854,775,807)之间的所有正负整数。
sql_variant:
用于存储除文本、图形数据和timestamp类型数据外的其它任何合法的SQLServer数据。
table:
用于存储对表或者视图处理后的结果集。
这种新的数据类型使得变量可以存储一个表,从而使函数或过程返回查询结果更加方便、快捷。
6.1.2自定义数据类型,创建用户定义类型要提供三个参数:
名称、系统数据类型、是否允许为空。
创建方法:
1.使用企业管理器(EnterpriseManager)创建用户自定义数据类型。
删除数据类型:
2.利用系统存储过程创建用户自定义数据类型。
系统存储过程sp_addtype为用户提供了T_SQL语句创建自定义数据类型的途径,其语法形式如下:
sp_addtypetypename=type,phystype=system_data_type,nulltype=null_type,owner=owner_name,使用Tranact-SQL创建用户定义数据类型:
格式:
sp_addtype类型名,系统数据类型,是否为空例:
sp_addtypewx,int,notnull,使用Tranact-SQL删除用户定义数据类型:
格式:
sp_droptype类型名例:
sp_addtypewx,例子6-1:
自定义一个地址数据类型。
execsp_addtypeaddress,varchar(80),notnull其运行结果如下:
(1row(s)affected)typeadded.,例子6-2:
删除自定义的生日数据类型。
execsp_droptypebirthday其运行结果如下:
(1row(s)affected)(0row(s)affected)Typehasbeendropped.,例:
在XK数据库中创建一个名为TEL(电话号码)的用户自定义数据类型,定义为VARCHAR数据类型,长度为3,且不能为NULLUSEXkGOEXECsp_addtypeTel,VARCHAR(30),NOTNULLGO,例:
删除刚建立的用户自定义数据类型USEXkGOEXECsp_droptypeTelGO,6.2表的创建,在SQLServer2005中,每个数据库中最多可以创建200万个表,用户创建数据库表时,最多可以定义1024列,也就是可以定义1024个字段。
SQLServer2005提供了两种方法创建数据库表,第一种方法是利用企业管理器(EnterpriseManager)创建表;另一种方法是利用Transact-SQL语句中的create命令创建表。
1.利用EnterpriseManager创建表,在EnterpriseManager中,展开指定的服务器和数据库,打开想要创建新表的数据库,用右键单击表对象,从弹出的快捷菜单中选择新建表选项,或者在工具栏中选择图标,就会出现新建表对话框,在该对话框中,可以定义列的以下属性:
列名称、数据类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。
然后根据提示进行设置。
2.利用create命令创建表,其语法形式如下:
CREATETABLEdatabase_name.owner.|owner.table_name(|column_nameAScomputed_column_expression|,n)ONfilegroup|DEFAULT,创建表的各参数的说明如下:
database_name:
用于指定在其中创建表的数据库名称。
owner:
用于指定新建表的所有者的用户名。
table_name:
用于指定新建的表的名称。
column_name:
用于指定新建表的列的名称。
computed_column_expression:
用于指定计算列的列值的表达式。
ONfilegroup|DEFAULT:
用于指定存储表的文件组名。
TEXTIMAGE_ON:
用于指定text、ntext和image列的数据存储的文件组。
data_type:
用于指定列的数据类型。
DEFAULT:
用于指定列的缺省值。
constant_expression:
用于指定列的缺省值的常量表达式。
IDENTITY:
用于指定列为标识列。
Seed:
用于指定标识列的初始值。
Increment:
用于指定标识列的增量值。
NOTFORREPLICATION:
用于指定列的IDENTITY属性在把从其它表中复制的数据插入到表中时不发生作用,即不足的生成列值,使得复制的数据行保持原来的列值。
ROWGUIDCOL:
用于指定列为全球唯一鉴别行号列。
COLLATE:
用于指定表使用的校验方式。
column_constraint和table_constraint:
用于指定列约束和表约束。
例子6-3:
创建了一个雇员信息表,其SQL语句的程序清单如下:
CREATETABLEemployee(numberintnotnull,namevarchar(20)NOTNULL,sexchar
(2)NULL,birthdaydatetimenull,hire_datedatetimeNOTNULLDEFAULT(getdate(),professional_titlevarchar(10)null,salarymoneynull,memontextnull),例子6-4,USEXkGO-创建班级信息表ClassCREATETABLEClass(ClassNochar(8)NOTNULL,DepartNochar
(2)NOTNULL,ClassNamechar(20)NOTNULL)GO,-创建学生信息表StudentCREATETABLEStudent(StuNochar(8)NOTNULL,ClassNochar(8)NOTNULL,StuNamechar(8)NOTNULL,Pwdchar(8)NOTNULL)GO,-创建学生选课表StuCouCREATETABLEStuCou(StuNochar(8)NOTNULL,CouNochar(3)NOTNULL,WillOrdersmallintNOTNULL,Statechar(4)NOTNULL,RandomNumvarchar(50)NULL)GO,6.3表结构的修改,6.3.1使用T-SQL的ALTERTABLE语句修改表ALTERTABLEdatabase_name.owner.table_nameWITHNOCHECKADDcolumn_namedatatypecolumn_constraints|,table_constraint,next_col_name|next_table_constraint|DROPCONSTRAINTconstraint_name|COLUMNcolumn,n,其中:
ldatabase_name:
将要修改的表所在的数据库。
owner:
表对象的所有者。
table_name:
将要修改的表的名称。
WITHNOCHECK:
允许check或者foreignkey约束加到一个表,而不验证现有的数据是否违反约束。
若没有指定这个选项,增加的任何约束都要对现有数据进行检查,如果有任何约束违反,ALTERTABLE语句将被终止。
ADD:
在现有表中增加一个新的数据项或一个约束。
column_name:
增加的字段名称。
datatype:
新增字段的数据类型。
DROP:
删除现有表中的一个字段或约束。
CONSTRAINT:
删除数据库中的约束。
COLUMN:
指定删除数据库中的列。
例子6-5:
创建了一个雇员信息表,其SQL语句的程序清单如下:
createtableemployees(idchar(8)primarykeynamechar(20)notnull,departmentchar(20)null,memochar(30)nullageintnull,)altertableemployeesaddsalaryintnulldropcolumnagealtercolumnmemovarchar(200)null,例子6-6为XK数据库中的CLASS表创建基于CLASSNO,列名为PK_CLASS的主键,再将其删除,USEXkGO-增加主键约束ALTERTABLEClassADDCONSTRAINTPK_ClassPRIMARYKEYCLUSTERED(ClassNo)GO-删除主键约束ALTERTABLEClassDROPCONSTRAINTPK_ClassGO,6.3.2使用SQLServer企业管理器修改表,利用企业管理器增加、删除和修改字段。
在企业管理器中,打开指定的服务器中要修改表的数据库,用右键单击要进行修改的表,从弹出的快捷菜单中选择设计表选项,则会出现设计表对话框,在该对话框中,可以利用图形化工具完成增加、删除和修改字段的操作。
例子6-7修改XK数据库中的表STUDENT将STUNO设置为主键,6.4表的重命名与删除,重命名:
1.使用T-SQL语言进行例:
USEXkGoEXECsp_renameDepart,DepartmentGO2.使用企业管理器,删除表,1.利用企业管理器删除表在企业管理器中,展开指定的数据库和表格项,用右键单击要删除的表,从快捷菜单中选择“除去表”选项,则会出现除去对象对话框。
单击“全部删除”按钮,即可删除表。
2.利用DROPTABLE语句删除表DROPTABLE语句可以删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、许可对象。
DROPTABLE语句的语法形式如下:
DROPTABLEtable_name,例子6-8删除数据库XK中的表CLASS,USEXkGoDROPTABLEClassGO,6.5表的数据操作,6.5.1向表中插入数据在企业管理器中向表中插入数据用INSERT语句向表中插入数据INSERTINTOtable_nameWITH(.n)|view_name|rowset_function_limited(column_list)VALUES(DEFAULT|NULL|expression,.n)|derived_table|execute_statement,其中:
lINTO:
一个可选的关键字,可以将它用在INSERT和目标表之间。
ltable_name:
将要接收数据的表或table变量的名称。
lWITH(.n):
指定目标表所允许的一个或多个表提示。
需要有WITH关键字和圆括号。
不允许有READPAST、NOLOCK和READUNCOMMITTED。
lview_name:
视图的名称及可选的别名。
通过view_name来引用的视图必须是可更新的。
由INSERT语句所做的修改不能影响视图的FROM子句中引用的多个基表。
例如,在多表视图中的INSERT必须使用column_list,column_list是指引用来自一个基表的列。
lrowset_function_limited:
是OPENQUERY或OPENROWSET函数。
lcolumn_list:
要在其中插入数据的一列或多列的列表。
必须用圆括号将column_list括起来,并且用逗号进行分隔。
lVALUES:
引入要插入的数据值的列表。
对于column_list(如果已指定)中或者表中的每个列,都必须有一个数据值。
必须用圆括号将值列表括起来。
如果VALUES列表中的值与表中列的顺序不相同,或者未包含表中所有列的值,那么必须使用column_list明确地指定存储每个传入值的列。
lDEFAULT:
强制SQLServer装载为列定义的默认值。
如果对于某列并不存在默认值,并且该列允许NULL,那么就插入NULL。
对于使用timestamp数据类型定义的列,插入下一个时间戳值。
DEFAULT对标识列无效。
lExpression:
一个常量、变量或表达式。
表达式不能包含SELECT或EXECUTE语句。
lderived_table:
任何有效的SELECT语句,它返回将装载到表中的数据行。
lexecute_statement:
任何有效的EXECUTE语句,它使用SELECT或READTEXT语句返回数据。
6.5.2修改表中数据,在企业管理器中修改表数据用UPDATE语句修改表数据UPDATEtable_nameWITH(.n)SETcolumn_name=expression|DEFAULT|NULLWHERE,其中:
ltable_name:
需要更新的表的名称。
如果该表不在当前服务器或数据库中,或不为当前用户所有,这个名称可用链接服务器、数据库和所有者名称来限定。
lWITH(.n):
指定目标表所允许的一个或多个表提示,需要有WITH关键字和圆括号。
lSET:
指定要更新的列或变量名称的列表。
lcolumn_name:
含有要更改数据的列的名称。
column_name必须驻留于UPDATE子句中所指定的表或视图中。
标识列不能进行更新。
lexpression:
变量、字面值、表达式或加上括弧的返回单个值的subSELECT语句。
lDEFAULT:
指定使用对列定义的默认值替换列中的现有值。
如果该列没有默认值并且定义为允许空值,这也可用来将列更改为NULL。
lWHERE:
指定条件来限定所更新的行。
6.5.3删除表中数据,在企业管理器中删除表中数据用DELETE语句删除表中数据DELETEFROMtable_nameWITH(.n)|view_name|rowset_function_limitedFROM,.nWHERE,其中:
lFROM:
是可选的关键字,可用在DELETE关键字与目标table_name、view_name或rowset_function_limited之间。
ltable_name:
是要从其中删除行的表的名称。
在其作用域内的table变量、或是将OPENDATASOURCE函数作为服务器名称的由四部分组成的表名(或视图名称)还可以在DELETE语句中作为表源使用。
lWITH(.n):
指定目标表所允许的一个或多个表提示。
需要有WITH关键字和圆括号。
不允许有READPAST、NOLOCK和READUNCOMMITTED。
lview_name:
是视图名称。
在视图的FROM子句中,view_name引用的视图必须可更新且正确引用一个基表。
lFROM:
指定附加的FROM子句。
这个对DELETE的Transact-SQL扩展使用户得以从指定数据,并从第一个FROM子句内的表中删除相应的行。
lWHERE:
指定用于限制删除行的条件。
如果没有提供WHERE子句,则DELETE删除表中的所有行。
6.6数据完整性的概念与实施方法,数据库应用程序开发中的一个重要步骤就是设计和实施数据的完整性,并确定实施数据完整性的最佳方案。
数据完整性是指数据库中数据的正确性和一致性,是衡量数据库质量的一个重要标准。
在使用INSERT、UPDATE和DELETE命令修改数据库中的数据时,数据的完整性可能被破坏,如插入了一个不存在学生的成绩,或将学生的班号修改为一个不存在的班级等。
通过定义数据完整性规则,SQLServer可以通过自身提供的完整性规则有效地管理数据的输入,而不必使用额外的应用程序来协助管理,这样一方面可以节省系统开销,另一方面将使数据库中的数据独立于应用程序,使创建开放式数据库系统成为可能。
实现数据完整性与业务规则,数据完整性的落实可为数据库中的数据质量提供保障。
在对数据表进行规划时的两个重要步骤就是对数据列的有效数值加以识别,并决定如何落实列内数据的完整性。
数据完整性分为四种类型,并可通过多种方式得到落实。
1.域完整性,域完整性是指给定列的输入有效性。
强制域有效性的方法有:
通过限制数据类型(包括自定义数据类型)、格式(CHECK约束和规则)或可能的取值范围(FOREIGNKEY约束、CHECK约束、DEFAULT定义、NOTNULL定义和规则)来实现。
如:
性别字段只能取“男”或“女”;课程成绩取值范围为0100;姓名字段不能为空;性别字段的默认值为“男”等等。
2.实体完整性,实体完整性是指表中的每一行必须是惟一的。
实体完整性强制表中的所有记录都有一个惟一的标识列,这个惟一标识列可能是一列,也可能有若干列的组合。
强制实体完整性的方法有:
UNIQUE约束、PRIMARYKEY约束或IDENTITY属性。
如:
在学生情况表中,学生的学号是惟一的,它与每个学生一一对应;成绩表中,学号+课程号是惟一的,它与每个学生的每门课相对应。
对于实体完整性,有如下规则:
实体完整性规则针对基本关系。
一个基本关系表通常对应一个实体集,例如,学生关系对应学生集合。
现实世界中的实体是可以区分的,它们具有一种唯一性质的标识。
例如,学生的学号,教师的职工号等。
在关系模型中,主关键字作为唯一的标识,且不能为空。
3.参照完整性(引用完整性),参照完整性确保数据库中数据的一致性。
在SQLServer中,参照完整性基于外键(引用表)与主键(被引用表)之间或外键与惟一键之间的关系(通过FOREIGNKEY和CHECK约束)。
通过外键将引用表和被引用表关联起来。
参照完整性通过已定义的表间关系,确保键值在所有表中都必须一致,因此不能引用不存在的值(即指向不存在的行)。
如果在被引用的表中,某一记录被外部键引用,则该记录就不能删除;若需要更改键值,那么在整个数据库中,对该键值的所有引用都要进行一致的更改,以保证数据的参照完整性。
建立外键约束,用来控制数据
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 创建 管理 数据库 对象 操作
![提示](https://static.bingdoc.com/images/bang_tan.gif)