数据库和表1(精)ppt课件

.,在本章中，你将学到：管理数据库管理表,目标,.,SQLServer2005包含如下系统数据库：Master：master数据库记录所有服务器特定的配置信息。Tempdb：tempdb数据库保存所有临时表和存储过程。每次SQLServer启动时，该数据库里面总是空的。Model：model数据库作为新数据库的模板或原型。Msdb：msdb数据库处理任务计划、异常处理等。,在SQLServer2005中识别系统数据库,.,课间思考,model数据库的用处是什么?,答案：model数据库作为一个新数据库的模板或原型。,.,4个系统数据库是如何启动的,4个数据库的启动关系如下启动master数据库；启动model数据库；启动msdb数据库；清除tempdb数据库；启动tempdb数据库。,.,创建用户数据库,在缺省的情况下，创建数据库的权限默认是sysadmin（系统管理员）和dbcreator（数据库创建者）创建数据库的用户成为该数据库的拥有者(dbowner)。如何命名数据库对于非规范的标识符，必须进行分隔。（双引号或者方括号）SELECT*FROMMyTable,.,如何命名用户数据库,在一个SQLServer服务器上，最多可以创建32767个数据库。数据库名称必须遵循SQLServer的标识符命名规则。名称的长度可以从1128。名称的第一个字符必须是一个字母或者“_”、“”和“#”中的任意字符。在中文版SQLServer中，可以直接使用中文名称。名称中不能有空格，不允许使用SQLServer的保留字。,.,产生的操作系统文件：数据文件包含数据库的启动信息，并存储数据主数据文件只有1个，是数据库的起点每个数据库都有一个主数据文件扩展名为.mdf次要数据文件扩展名为.ndf这些文件包含不能放置在主数据文件中的所有数据日志文件发生在数据库中的修改和产生这些修改的事务,是存储恢复事务的所有必要的信息。一个数据库必须有至少一个日志文件最小值为512k大小应该是数据库的2540扩展名为.ldf,.,如果在这个阶段发生故障，A4000，B10000,.,建议在创建数据库时，请根据数据库中预期的最大数据量，创建尽可能大的数据文件。允许数据文件自动增长，但要有一定的限度。为此，需要指定数据文件增长的最大值，以便在硬盘上留出一些可用空间。这样便可以使数据库在添加超过预期的数据时增长，而不会填满磁盘驱动器.,.,文件组,文件组：文件组是SQLServer数据文件的一种逻辑管理单位，若干个分布在不同的硬盘驱动器上的数据文件可以组织成一个文件组。目的：可以更好地分配和管理存储空间，通过控制在特定磁盘驱动器上放置数据和索引来提高数据库的性能。由于操作系统对物理文件的大小进行了限制，所以当某个磁盘上的数据文件超过单个文件允许的最大值时，可以使用文件组中存贮在其他驱动器上的数据文件扩充存储空间,.,两种类型的文件组：主文件组（Primary）：包括主要数据文件和任何没有明确指派给其他文件组的数据文件系统表的页面都分配在主文件组中用户定义文件组：createdatabase或alterdatabase的filegroup指定的文件组事务日志不属于任何文件组,.,两种方法创建数据库：ManagementStudioSQL语句,.,SQL概述,SQL（StructuredQueryLanguage）结构化查询语言，是关系数据库的标准语言SQL是一个通用的、功能极强的关系数据库语言,.,SQL概述（续）,3.1.1SQL的产生与发展3.1.2SQL的特点3.1.3SQL的基本概念,.,1Background-History,IBMSequellanguagedevelopedaspartofSystemRprojectattheIBMSanJoseResearchLaboratoryRenamedStructuredQueryLanguage(SQL)ANSIandISOstandardSQL:SQL-86SQL-89SQL-92SQL:1999SQL:2003Commercialsystemsoffermost,ifnotall,SQL-92features,plusvaryingfeaturesetsfromlaterstandardsandspecialproprietaryfeatures.Notallexamplesheremayworkonyourparticularsystem.,.,Background,有关标准SQL-86“数据库语言SQL”SQL-89“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持SQL-92“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。SQL-3(99年)新的标准，增加对面向对象模型的支持,.,SQL标准的进展过程,标准大致页数发布日期SQL/861986.10SQL/89(FIPS127-1)120页1989年SQL/92622页1992年SQL991700页1999年SQL20032003年,.,3.1SQL概述,3.1.1SQL的产生与发展3.1.2SQL的特点3.1.3SQL的基本概念,.,3.1.2SQL的特点,1.综合统一集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。可以独立完成数据库生命周期中的全部活动：定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新；数据库重构和维护数据库安全性、完整性控制等用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。数据操作符统一,.,2.高度非过程化,非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径SQL只要提出“做什么”，无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。,.,3.面向集合的操作方式,非关系数据模型采用面向记录的操作方式，操作对象是一条记录SQL采用集合操作方式操作对象、查找结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合,.,4.以同一种语法结构提供多种使用方式,SQL是独立的语言能够独立地用于联机交互的使用方式SQL又是嵌入式语言SQL能够嵌入到高级语言（例如C，C+，Java）程序中，供程序员设计程序时使用,.,5.语言简洁，易学易用,SQL功能极强，完成核心功能只用了9个动词。,.,Background,用户1,用户2,用户4,视图V1,视图V1,用户3,基本表B1,基本表B2,基本表B4,基本表B3,存储文件S1,存储文件S2,存储文件S4,存储文件S3,SQL数据库体系结构,SQL用户,VIEW,BaseTable,Storedfile,.,用户定义数据库：语法：CREATEDATABASEdatabase_nameONPRIMARYLOGON,.n:=(NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=growth_increment),.n让我们看看如何sp_helpdb（帮助的使用）spsystemprocedure,创建用户定义数据库,显式地定义存储数据库数据部分的操作系统文件,显式地定义存储数据库事务日志部分的操作系统文件,.,除非指定了FORATTACH，否则每个新数据库都从model数据库继承数据库选项设置。,.,创建指定数据和事务日志文件的数据库,CREATEDATABASEEmployeeON(NAME=Employee_dat,FILENAME=D:ProgramFilesMSSQL.1MSSQLDataEmployee_Data.mdf,SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME=Employee_log,FILENAME=D:ProgramFilesMSSQL.1MSSQLDataEmployee_Log.ldf,SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB),.,创建未指定文件的数据库,CREATEDATABASEmytest;创建名为mytest的数据库，并创建相应的主文件和事务日志文件。因为语句没有项，所以主数据库文件的大小为model数据库主文件的大小。事务日志将设置为下列值中的较大者：512KB或主数据文件大小的25%。因为没有指定MAXSIZE，文件可以增大到填满所有可用的磁盘空间为止。,.,课间思考,哪个语句被用来创建一个数据库?,答案：CREATEDATABASE语句,.,修改数据库,修改数据库ALTERDATABASEdatabaseADDFILE,.nTOFILEGROUPfilegroup_name|ADDLOGFILE,.n|REMOVEFILElogical_file_name|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUPfilegroup_name|MODIFYFILE|MODIFYNAME=new_dbname|MODIFYFILEGROUPfilegroup_namefilegroup_property|NAME=new_filegroup_name|SET,.nWITH|COLLATE,.,重命名用户定义数据库只有管理员和数据库拥有者可以重命名数据库存储过程：sp_renamedb更改数据库的名称。在重命名数据库之前确保没有人使用该数据库数据库设置为单用户模式语法：sp_renamedbold_database_name,new_name返回代码值:0（成功）或非零数字（失败）,.,删除用户定义数据库,删除用户定义数据库Dropdatabasedatabase_name只有sysadmin和数据库的拥有者有删除数据库的许可。不能删除系统数据库不能删除当前正在使用的数据库,.,分离用户数据库,SQLServer服务器由若干个数据库组成，除了master、model和tempdb这3个系统数据库外，其余的数据库都可以从服务器的管理中分离出来，脱离服务器的管理，同时保持数据文件和日志文件的完整性和一致性，这样分离出来的数据库的日志文件和数据文件可以附加到其他SQLServer服务器上构成完整的数据库，附加的数据库和分离时完全一致。,.,附加用户数据库,附加数据库可以很方便地在SQLServer服务器之间利用分离后的数据文件和日志文件组织成新的数据库。在实际工作中，分离数据库作为对数据基本稳定的数据库的一种备份的办法来使用。,.,数据库的故障还原模型（恢复模型）,简单模型使用简单恢复模型可以将数据库恢复到上次备份的即时点。不过，无法将数据库还原到故障点或特定的即时点。若要还原到这些点，请选择完全恢复或大容量日志记录恢复。备份策略包括：数据库备份差异备份（可选）还原最新的完整数据库备份。如果有差异备份，则还原最新的那个备份。上次的数据库备份或差异备份后的更改将丢失。,.,完全模型完全恢复提供将数据库恢复到故障点或特定即时点的能力。为保证这种恢复程度，包括大容量操作（如SELECTINTO、CREATEINDEX和大容量装载数据）在内的所有操作都将完整地记入日志。完全恢复的备份策略包括：数据库备份差异备份（可选）事务日志备份完全恢复模型中为防止事务损失，必须保护事务日志不受损坏,.,大容量日志记录大容量日志记录恢复模型提供对媒体故障的防范，并对某些大规模或大容量复制操作提供最佳性能和最少的日志使用空间。下列操作为最小日志记录操作：SELECTINTO大容量装载操作（bcp和BULKINSERT）CREATEINDEX（包括索引视图）text和image操作（WRITETEXT和UPDATETEXT）。在大容量日志记录恢复模型中，这些大容量复制操作的数据丢失程度要比完全恢复模型严重。在大容量日志记录恢复模型下，只记录这些操作的最小日志，而且无法逐个控制这些操作。在大容量日志记录恢复模型中，数据文件损坏可能导致必须手工重做工作,.,表的分类,按照存储时间分永久表：除非人工删除，否则一直保存临时表：只在数据库运行期间临时保存数据按照表的用途分类系统表：维护服务器和数据库正常工作的数据表，不允许用户进行更改用户表,.,临时表,临时表与永久表相似，但临时表存储在tempdb中，当不再使用时会自动删除。可以创建本地临时表和全局临时表。本地临时表只对于创建者是可见的。当用户与SQLServer实例断开连接后，将删除本地临时表。全局临时表在创建后对任何用户和任何连接都是可见的，当引用该表的所有用户都与SQLServer实例断开连接后，将删除全局临时表。本地临时表的名称前面有一个数字符号(#table_name)，而全局临时表的名称前面有两个数字符号(#table_name)。CREATETABLE#MyTempTable(colaINTPRIMARYKEY),.,创建表,说明：表在数据库中被创建表名和列名要有意义（不允许使用关键字）不允许重名。表名最多128个字符。列名在一个表中必须是唯一的，最多128个字符。,.,创建表,两种创建表的方式：ManagementStudio中创建执行SQL语句说明：创建表只能在当前数据库中进行当前数据库指的是可以操作的数据库Use数据库名操作：如何更改当前数据库默认数据库是系统数据库master,.,数据类型,字符串类型单引号编码普通字符编码多种字符集，统称ANSI字符集统一字符编码Unicode字符集（固定使用两个字节来表示一个字符）非Unicode字符类型：char(n)固定长度，非Unicode字符数据，长度为n个字节。n的取值范围为1至8,000，存储大小是n个字节。varchar(n|max)可变长度，非Unicode字符数据。n的取值范围为1至8,000。text,.,数据类型（续）,Unicode字符nchar(n)n个字符的固定长度的Unicode字符数据。n值必须在1到4,000之间（含）。nvarchar(n|max)可变长度Unicode字符数据。n值在1到4,000之间（含）。前面有一个N标识符如Ndog,.,数据类型,精确数字bigint、decimal、int、numeric、smallint、money、tinyint、smallmoney、bit近似数字floatreal日期和时间Datetime、smalldatetime用单引号括起来20060507,.,避免使用NULL值,在设计各个字段时，应该尽量避免使用NULL值NULL值表示数据未知。NULL不是空白或者0没有两个相等的空值两个NULL值相比或NULL与其他数值相比，返回是未知NOTNULL表示数据列不允许空值。确保数据列包含有意义的数据，确保数据的完整性,.,列属性：Primarykey：主关键字约束。主关键字不能为空或有重复值组合主键的设置Default：默认值约束该列的值可以输入，也可以不输入，但取值为默认值Null：允许为空Check：检查约束该列的取值必须符合检查约束所设置的条件,.,避免使用NULL值,在设计各个字段时，应该尽量避免使用NULL值NULL值表示数据未知。NULL不是空白或者0没有两个相等的空值两个NULL值相比或NULL与其他数值相比，返回是未知NOTNULL表示数据列不允许空值。确保数据列包含有意义的数据，确保数据的完整性,.,IDENTITY（标识）列,IDENTITY（标识）列通过使用IDENTITY属性可以实现标识符列。这使得开发人员可以为表中所插入的第一行指定一个标识号（IdentitySeed属性），并确定要添加到种子上的增量（IdentityIncrement属性）以确定后面的标识号。注意事项：一个表只能有一个使用IDENTITY属性定义的列，且必须通过使用decimal、int、numeric、smallint、bigint或tinyint数据类型来定义该列。可指定种子和增量。二者的默认值均为1。标识符列不能允许为空值，也不能包含DEFAULT定义或对象。不能更新注意：如果在经常进行删除操作的表中存在标识符列，那么标识值之间可能会出现断缺。,.,GUID（全局唯一标识符）列,概念IDENTITY是自动为表产生标识号，但是不同表的IDENTITY值可能会相同GUID数据类型产生整个数据库唯一的标识使用GUID的注意事项一个表只能有一个GUID列，必须定义为uniqueidentifier数据类型数据库引擎不为该列自动生成值。若要插入全局唯一值，应为列创建DEFAULT定义，使用NEWID函数生成全局唯一值,.,查看INDENTITY和GUID的值,INDENTITYselectidentitycolfrom表名GUIDselectrowguidcolfrom表名,.,插入计算列,.,表：创建表以存储数据使用CREATETABLE语句创建表语法：CREATETABLEdatabase_name.schema_name.table_name(|,.n)ONpartition_scheme_name(partition_column_name)|filegroup|defaultTEXTIMAGE_ONfilegroup|default;,创建表,.,练习：创建一个表EmployeeLeave。,创建表（续）,.,模式：一个数据库中数据库对象的逻辑分组，帮助提高数据库中对象的可管理性。创建了一个模式，就建立了一个数据库的命名空间，一个个框架，在这个空间中可以进一步定义该模式包含的数据库对象，例如表，视图，索引等。对计算列使用表达式CREATETABLEmytable(lowint,highint,myavgAS(low+high)/2);,.,修改表：当需要添加新列的时候需要修改表使用ALTERTABLE语句修改表语法：ALTERTABLEdatabase_name.schema_name.|schema_name.table_nameALTERCOLUMNcolumn_nameNULL|NOTNULL|WITHCHECK|NOCHECKADDCOLUMNADDCONSTRAINTconstraint_nameconstraint_type让我们看看如何,修改表,.,修改表,添加新列ALTERTABLEdoc_exaADDcolumn_bVARCHAR(20)NULL;删除列ALTERTABLEdoc_exbDROPCOLUMNcolumn_b;更改列的数据类型ALTERTABLEdoc_exyALTERCOLUMNcolumn_aDECIMAL(5,2);,.,添加约束altertablestu_test2addunique(sname)添加默认约束ALTERTABLEstu_test2ADDDEFAULT江苏FORhometown在现有列中添加一个未经验证的CHECK约束ALTERTABLEdbo.doc_exdWITHNOCHECKADDCONSTRAINTexd_checkCHECK(column_a1),.,重命名表,sp_renameSales.SalesTerritory,SalesTerr;,.,删除表：当表不需要的时间，需要删除表。使用DROPTABLE语句删除表。语法：DROPTABLEdatabase_name.schema_name.table_name让我们看看如何,删除表,.,数据库的完整性：数据的正确性和相容性。消除数据冗余确保数据库中的数据是准确的、一致的和可靠的。学号、性别、年龄、课程、院系.数据的完整性是为了防止数据库中存在不符合语义的数据，即不正确的数据检查和防范的对象是不合语义的、不正确的数据，防止它们进入数据库数据的安全性是保护数据库防止恶意的破坏和非法的存取防范的对象是非法用户的非法操作,数据完整性,.,三类完整性约束,它可以被粗略的分成下面的类型：实体完整性：确保每行都可以被一个称为主键的属性唯一确定主键值不能为空参照完整性：确保外键的值与相应主键的值相匹配外键：如果关系模式R中的某属性集不是R的主键，而是另一个关系R1的主键则该属性集是关系模式R的外键。外键的作用：保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。使两张表形成关联，外键只能引用外表中的列的值！用户定义完整性：指用户指定的一系列规则，它不属于实体、域和参照完整性类型,.,主键,概念表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键(PK)，用于强制表的实体完整性一个表只能有一个PRIMARYKEY约束，并且PRIMARYKEY约束中的列不能接受空值标识列可作为主键如果对多列定义了PRIMARYKEY约束，则一列中的值可能会重复，但来自PRIMARYKEY约束定义中所有列的任何值组合必须唯一。,.,.,关系间的引用,在关系模型中实体及实体间的联系都是用关系来描述的，因此可能存在着关系与关系间的引用。例1学生实体、专业实体以及专业与学生间的一对多联系学生（学号，姓名，性别，专业号，年龄）专业（专业号，专业名）,.,学生（学号，姓名，性别，专业号，年龄）,专业（专业号，专业名）,.,关系间的引用(续),例2学生、课程、学生与课程之间的多对多联系学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）,.,学生,学生选课,课程,.,关系间的引用(续),例3学生实体及其内部的领导联系(一对多)学生（学号，姓名，性别，专业号，年龄，班长）,.,.,你可以使用下面的部分来维护完整性：应用约束(constraint),实现数据完整性（续）,.,约束有以下类型：主键约束（primarykey）唯一约束(unique)外键约束(foreignkey)检查约束(check)默认约束(default)约束可以在可以在创建表的时候创建或者之后被添加。表创建以后添加约束的时候，会检查已有数据，如果有违反，则约束被拒绝。存储约束定义的系统表是syscomments,sysreferences和sysconstraints。为了浏览表上的所有约束，使用sp_helpconstraint后跟表名。,实现数据完整性（续）,.,EntityintegrityisimplementedusingthePRIMARYKEYconstraint.ReferentialintegrityisimplementedusingtheFOREIGNKEYandPRIMARYKEYconstraints.User-definedintegrityisimplementedintheformofbusinessrulesusingCHECKconstraintsortriggers.,.,CREATETABLE（，）；如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。,.,主键约束,在创建或修改表时，您可以通过定义PRIMARYKEY约束来创建主键。例子：为stu_test设置主键；为grade设置主键,.,约束可以使用下面的语句之一来创建：CREATETABLE语句ALTERTABLE语句语法：CREATETABLEtable_name(column_nameCONSTRAINTconstraint_nameconstraint_type,CONSTRAINTconstraint_nameconstraint_type),实现数据完整性（续）,.,主键约束：主键约束是定义在列上或一系列列上，它们的值唯一识别表中的所有行。列中不允许NULL值确保实体完整性。语法：CREATETABLEtable_name(col_nameCONSTRAINTconstraint_namePRIMARYKEYCLUSTERED|NONCLUSTEREDcol_name,col_name,col_name,),实现数据完整性（续）,.,创建主键约束（单一主键、组合主键）添加主键删除主键,.,学生表Student,例建立“学生”表Student。CREATETABLEStudent(SnoCHAR(9)，SnameCHAR(20)，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)；,.,学生表Student,例5建立“学生”表Student，学号是主码，姓名取值唯一。CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，/*列级完整性约束条件*/SnameCHAR(20)UNIQUE，/*Sname取唯一值*/SsexCHAR(2)notnull，SageSMALLINT，SdeptCHAR(20)null)；,主码,.,外键约束：外键约束被定义用来删除两个表间的不一致，当一个表中的数据依赖与另一个表中的数据的时候。外键约束将一个表中的一个或多个列（外键）与另一个表中一个同一系列的列（主键列）相联系。,实现数据完整性（续）,.,语法：CREATETABLEtable_name(col_nameCONSTRAINTconstraint_nameFOREIGNKEY(col_name,col_name,)REFERENCEStable_name(column_name,column_name,)(col_name,col_name,col_name,)col_name,col_name,col_name,)让我们看看如何,实现数据完整性（续）,.,例子：为employeeleave表创建一个外键约束,.,Course表,.,课程表Course,例6建立一个“课程”表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY，CnameCHAR(40)，CpnoCHAR(4)，CcreditSMALLINT，FOREIGNKEY(Cpno)REFERENCESCourse(Cno);,先修课,Cpno是外码被参照表是Course被参照列是Cno,.,SC表,.,学生选课表SC,例7建立一个“学生选课”表SCCREATETABLESC(SnoCHAR(9)，CnoCHAR(4)，GradeSMALLINT，PRIMARYKEY(Sno，Cno)，/*主码由两个属性构成，必须作为表级完整性进行定义*/FOREIGNKEY(Sno)REFERENCESStudent(Sno)，/*表级完整性约束条件，Sno是外码，被参照表是Student*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/);,.,检查约束：检查约束通过限制列中要插入的值增强域完整性。检查约束可以被应用到多个列。检查约束可以通过使用下面的关键字被指定：INLIKEBETWEEN,实现数据完整性（续）,.,创建check约束的规则：在列上被创建列级的CHECK约束可以仅引用列上的值。因此，表级约束可以参考同一表的其他列的值。不能包括子查询如果使用withnocheck选项创建，则不能检查表中已有数据当CHECK约束被应用在列上的时候，当插入或更新发生的时候，它验证数据。表级check约束可以参考同一表上的另一个列。它不能放置在有IDENTITY属性的列上。,.,语法：CREATETABLEtable_name(col_nameCONSTRAINTconstraint_nameCHECK(expression)(col_name,col_name,).)让我们看看如何,实现数据完整性（续）,.,为student中sex字段指定约束employeeleave表的leavetype字段取值限定为sl,pl,cl,.,唯一约束：唯一约束被用来增强非主键列的唯一性。列中允许一个NULL值语法：CREATETABLEtable_name(col_nameCONSTRAINTconstraint_nameUNIQUECLUSTERED|NONCLUSTERED(col_name,col_name,col_name,)col_name,col_name,col_name,),实现数据完整性（续）,.,例子：Stu_test表中添加一列cardno，唯一约束,.,默认约束：默认约束可以用于指定一个常量值到列。语法：CREATETABLEtable_name(col_nameCONSTRAINTconstraint_nameDEFAULT(constant_expression|NULL)(col_name,col_name,).)让我们看看如何,实现数据完整性（续）,.,employeeleave表的leavetype字段取值默认为pl,.,课间思考,哪个关键字被用于指定检查约束?,答案：检查约束可以