项目2-数据库及数据表的创建与维护.ppt

任务2-1：系统数据库管理任务2-2：用户数据库的创建、修改和删除任务2-3：数据表的创建、修改和删除任务2-4：数据表记录的添加、修改和删除任务2-5：数据完整性,在SQLServer2008中，用户如何访问及使用数据库，就需要正确了解数据库中所有对象及其设置。数据库就像一个容器，它里面除了存放着数据的表之外，还有视图、存储过程、触发器、约束等数据库对象。数据库管理的核心任务包括创建、操作和维护数据库。在SQLServer2008中，数据库是表、视图、存储过程、触发器等数据库对象的集合，是数据库管理系统的核心内容。为了更好地学习并理解数据库，首先来了解一个数据库的组成及其数据库中的数据文件、事务日志文件及文件组等基本概念。,SQLServer2008将数据库映射为一组操作系统文件，数据和日志信息分别存储在不同的文件中。,数据文件,数据库文件,事务日志文件,用于存储数据库中的所有对象，如表、视图、存储过程等,用以记录所有事务及每个事务对数据库所做的修改,2-1,数据文件：数据文件用于存储数据库中的所有对象，如表、视图、存储过程等。,包含数据库的启动信息和数据库中其他文件的指针。每个数据库有且仅有一个主要数据文件，主要数据文件的建议文件扩展名为.mdf。,次要数据文件是可选的，由用户定义并存储主要数据文件未存储的其他数据和对象，建议文件扩展名为.ndf。,【注意】次要数据文件不是必须的，如果主要数据文件足够大，能够容纳数据库中的所有数据，则该数据库不需要次要数据文件；但有些数据库可能非常大，超过了单个Windows文件的最大值(4G)，可以使用多个次要数据文件，这样数据库就能继续增长。,【说明】SQLServer2008不强制使用.mdf、.ndf和.ldf文件扩展名，但使用它们有助于标识文件的各种类型和用户。SQLServer2008的每个数据库文件都有一个逻辑文件名和一个物理文件名。逻辑文件名只在Transact-SQL语句中使用，是实际磁盘文件名的代号。物理文件名是操作系统文件的实际名字，包括文件所在的路径。,事务日志文件：用以记录所有事务及每个事务对数据库所做的修改。每个SQLServer2008数据库至少拥有一个事务日志文件，也可以拥有多个事务日志文件。事务日志文件的大小最少是1MB。事务日志是数据库的重要组件，当系统出现故障或数据库遭到破坏时，就需要使用事务日志恢复数据库内容。日志文件的建议文件扩展名为.ldf。,2、数据库文件组多个数据文件集合起来形成的一个整体就是文件组。对文件进行分组的目的是便于进行管理和进行数据的分配。,每个文件组有一个组名。一个数据文件不能存在于两个或两个以上的文件组里，日志文件不属于任何文件组。主文件组：包含了所有的系统表。当建立数据库时，主文件组包括主要数据文件和所有没有被包含在其他文件组里的次要数据文件。用户自定义文件组：用户自定义文件组包含所有在使用CREATEDATABASE或ALTERDATABASE命令时使用FILEGROUP关键字来进行指定文件组的文件。默认文件组：默认文件组包含所有在创建时没有指定文件组的表、索引等数据库对象。在每个数据库中，每次只能有一个文件组是默认文件组。可以在用户自定义文件组中指定一个默认文件组；如果没有指定默认文件组，则主文件组为默认文件组。,3、数据库的物理存储结构,页面和盘区SQLServer2008中数据存储的基本单位是页。为数据库中的数据文件（.mdf或.ndf）分配的磁盘空间可以从逻辑上划分成页。在SQLServer2008中，页的大小是8KB，SQLServer2008数据库每兆字节有128页。由8个连续页面（88KB=64KB）组成的数据结构称为一个盘区，SQLServer2008数据库每兆字节有16个盘区。,简单地说，一个数据库是由文件组成的，文件是由盘区组成，而盘区是由页面组成的。,【注意】数据行存放在数据页中，但数据页只能包含除text、ntext和image数据外的所有数据，text、ntext和image数据存储在单独的页中。行不能跨页存储，而每数据页是8KB，严格地说是8060B，因此页中每一行最多包含的数据量是8060B。日志文件是由一系列日志记录组成，而不是页面。,4、SQLServer2008数据库的分类,3）系统数据库系统数据库是在SQLServer2008的每个实例中都存在的标准数据库，用于存储有关SQLServer的信息，SQLServer使用系统数据库来管理系统。,1）示例数据库AdventureWorks和AdventureWorksDW是SQLServer2008中的示例数据库，是系统为了让用户学习和理解SQLServer2008而设计的。,2）用户数据库用户数据库是用户根据事务管理需求创建的数据库，例如，图书信息管理数据库、sales数据库等。,master数据库：SQLServer2008中的总控数据库，是最重要的系统数据库。系统是根据master数据库中的信息来管理系统和其他数据库。如果master数据库信息被破坏，整个SQLServer系统将受到影响，用户数据库将不能被使用。model数据库：用户建立新数据库提供模板和原型，它包含了将复制到每个新建数据库中的系统表。msdb数据库：支持SQLServer代理。当代理程序调度作业、记录操作时，系统要用到或实时产生很多相关信息，这些信息一般存储在msdb数据库中。tempdb数据库：临时数据库，保存所有的临时表、临时数据以及临时创建的存储过程。resource数据库：只读和隐藏的数据库，包含SQLServer2008所有的系统对象，我们无法使用可以列出所有数据库的一般SQL命令来看到它。,5、数据库对象的结构,架构是一种允许我们对数据库对象进行分组的容器对象，是形成单个命名空间的数据库对象的集合。命名空间是一个集合，其中每个元素的名称都是唯一的。在SQLServer2008中，一个数据库对象通过由4个命名部分组成的结构来引用，即：server_name.database_name.schema_name.object_name,对象所在的服务器名称,对象所在的数据库名称,对象的架构名称,对象名,如果应用程序引用了一个没有限定架构的数据库对象，那么SQLServer2008将尝试在用户的默认架构（通常为dbo）中找出这个对象。例如，引用服务器“HBSI”上的数据库“sales”中的销售员表“Seller”时，完整的引用为“HBSI.sales.dbo.Seller”。在实际引用时，在能够区分对象的前提下，前三个部分是可以根据情况省略的。,1、创建数据库,创建数据库,使用SQLServerManagementStudio创建数据库。,使用Transact-SQL语句创建数据库,优点：简单直观,优点：可以将创建数据库的脚本保存下来，在其他计算机上运行以创建相同的数据库,2-2,1）使用SQLServerManagementStudio创建数据库,CREATEDATABASEdatabase_nameON,.n,.nLOGON,.n:=PRIMARY(NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=growth_increment),.n:=FILEGROUPfilegroup_name,.n,2）使用Transact-SQL语句创建数据库教材P45。,数据库的名称,附录：Transact-SQL语法约定参考说明大写Transact-SQL关键字。粗体数据库名、表名、列名、索引名、存储过程、实用工具、数据类型名以及按所显示的原样必须键入的文本。（方括号）可选语法项。不要键入方括号。（）说明。,.n此项可以重复n次，各项之间以逗号分隔。.n指示前面的项可以重复n次。每一项由空格分隔。:=语法块的名称。此约定用于对可在语句中的多个位置使用的过长语法段或语法单元进行分组和标记。可使用语法块的每个位置由括在尖括号内的标签指示：。（大括号）必选语法项。不要键入大括号。|（竖线）大括号中的语法项。只能使用其中一项。;Transact-SQL语句终止符。虽然在此版本的SQLServer中大部分语句不需要分号，但将来的版本需要分号。斜体用户提供的Transact-SQL语法的参数。下划线指示当语句中省略了包含带下划线的值的子句时应用的默认值。,CREATEDATABASEsampleONPRIMARY(NAME=sample_dat,FILENAME=d:datasample_data.mdf,SIZE=5,MAXSIZE=50,FILEGROWTH=10),该语句需在“查询编辑器”中输入并执行,创建一个数据库sample。主文件为sample_dat。,【说明】由于没有为主要数据文件指定容量单位，系统默认为MB。由于在创建时没有指定日志文件，系统将自动创建一个初始容量为2MB的日志文件并且没有最大容量限制。如果在查询语句编辑区域选定了语句，则只执行选定的语句，否则执行所有语句。其他示例:教材P46P47。,2、查看数据库,查看数据库的属性,使用SQLServerManagementStudio,用Transact-SQL语句,对于已有的数据库,1）使用SQLServerManagementStudio查看数据库属性教材P36P37。,2）使用Transact-SQL语句查看数据库属性系统存储过程sp_helpdb查看数据库的属性。例：查看数据库sales的属性。语句格式：sp_helpdbsales例：查看所有数据库的属性。语句格式：sp_helpdb,3、修改数据库,1）使用SQLServerManagementStudio修改数据库属性。,2）使用Transact-SQL语句修改数据库属性教材P49。,ALTERDATABASEdatabase_nameADDFILE,.nTOFILEGROUPfilegroup_name|ADDLOGFILE,.n|REMOVEFILElogical_file_name|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUPfilegroup_name|MODIFYFILE|MODIFYNAME=new_dbname|MODIFYFILEGROUPfilegroup_namefilegroup_property|NAME=new_filegroup_name|SETAUTO_SHRINKon/off,为数据库sample添加数据文件sample_dat2和日志文件sample_log2。,ALTERDATABASEsampleADDFILE(NAME=sample_dat2,FILENAME=d:datasample_dat2.ndf,SIZE=4,MAXSIZE=10,FILEGROWTH=1)ALTERDATABASEsampleADDLOGFILE(NAME=sample_log2,FILENAME=d:datasample_log2.ldf,SIZE=4,MAXSIZE=10,FILEGROWTH=1),其他示例：教材P50。,4、重命名数据库,使用ALTERDATABASEMODIFYNAME进行重命名数据库。正在使用的数据库是不能进行重命名的。,5、数据库的收缩,6、删除数据库,当一个数据库不再需要时可以将其删除，以释放该数据库所占有的磁盘空间。但是应该注意的是，如果某个数据库正在被使用时，则无法对其进行删除操作。,1）使用SQLServerManagementStudio删除数据库,2）使用Transact-SQL语句删除数据库,语法格式：DROPDATABASEdatabase_name例：删除数据库studentDROPDATABASEstudent例：删除sample和student数据库。DROPDATABASEsample,student,【注意】4个系统数据库master、tempdb、model、msdb不能删除。正在使用的数据库不能删除。数据库被删除之后，文件及其数据都从服务器上的磁盘中被删除。一旦删除数据库，它即被永久删除，所以删除数据库时一定要谨慎。,课堂练习图形方式附加、分离sales数据库。,1、表的概念教材P58.,数据库中包含一个或多个表。表是数据的集合，是用来存储数据和操作数据的逻辑结构。数据在表中是按照行和列的格式来组织排列的，每一行代表一条唯一的记录，每一列代表记录的一个属性。例如，一个包含销售员基本信息的数据表，表中每一行代表一名销售员，每列分别代表该销售员的信息，如编号、姓名、性别等。,2-3,在使用数据库的过程中，接触最多的莫过于表。表是存储数据的地方，是一种结构化的文件，可用来存储一些特定数据类型的数据，是数据库中最重要的部分。,2、系统数据类型教材P6569。,2）使用Transact-SQL语句创建数据表,CREATETABLEdatabase_name.schema_name|schema_name.table_name(column_name1data_typeDEFAULTconstant_expressionIDENTITY(SEED,INCREMENT)NULL|NOTNULL,n)ONfilegroup|DEFAULT,3、表的创建,1）使用SQLServerManagementStudio创建数据表教材P60P62。,例：为sales数据库创建一个销售人员表Seller，它包含销售员编号（SaleID）、姓名（SaleName）、性别（Sex）、出生日期（Birthady）、雇用日期（HireDate）、地址（Address）、电话（Telephone）和备注（Note）字段，其中SaleID、SaleName这两列不允许为空。,CREATETABLESeller(SaleIDchar(3)NOTNULL,SaleNamechar(8)NOTNULL,Sexchar(2),Birthdaydatetime,HireDatedatetime,Addresschar(60),Telephonechar(13),Notechar(200),例：为sales数据库创建订单表Orders，包括OrderID、CustomerID、SaleID和OrderDate字段，其中OrderID为标识列，起始值为10248，增量为1；CustomerID和SaleID字段不允许为空值；OrderDate字段的默认值为当前日期。,CREATETABLEOrders(OrderIDintIDENTITY(10248,1),-教材P70CustomerIDchar(3)NOTNULL,SaleIDchar(3)NOTNULL,OrderDatedatetimeDEFAULTgetdate()-教材P69),SQLServer为自动进行顺序编号而引入了自动编号的IDENTITY属性。当需要对某一列输入的数据进行有规律的顺序编号且保证每个编号惟一时，最好是采用IDENTITY，这样既方便准确，速度又快。具有IDENTITY属性的列称为标识列，其取值称为标识值。命令格式：CREATETABLEtable_name(column_namedata_typeIDENTITY(seed，increment)NOTNULL,.n),4、修改表结构,1）使用SQLServerManagementStudio修改表结构教材P73。,2）使用Transact-SQL语句修改表结构,ALTERTABLEtable_nameADDcolumn_namedate_typeDEFAULTcontant_expressionIDENTITY(SEED,INCREMENT)NULL|NOTNULL,n|DROPCOLUMNcolumn_name,n|ALTERCOLUMNcolumn_namenew_datetypeNULL|NOTNULL,例：sales数据库中的Customer表包含CustomerID、ConpanyName和ConnectName三个字段，现为该表添加地址（Address）、邮政编码（ZipCode）和电话号码（Telephone）字段。ALTERTABLECustomerADDAddresschar(40),ZipCodechar(6),Telephonechar(20)例：将表Seller中的Sex列删除ALTERTABLESellerDROPCOLUMNSex例：将Seller表中的Address字段的长度改为30，且不能为空ALTERTABLESellerALTERCOLUMNAddressvarchar(30)NOTNULL,5、删除数据表,1）使用SQLServerManagementStudio删除数据表,2）使用Transact-SQL语句删除数据表,使用DROPTABLE命令删除表格，其语法格式为：DROPTABLEtable_name1,n例：将Customer表从sales数据库中删除。DROPTABLECustomer,1、使用Transact-SQL语句向表中插入数据,INSERTINTOtable_name(column_name,n)VALUES(expression|NULL|DEFAULT,n)其中：table_name：要插入数据的表名。column_name：要插入数据的列名。expression：与column_name相对应的字段的值，字符型和日期型值插入时要加单引号。,2-4,例：向Category表中添加三行数据。INSERTINTOCategory(CategoryID,CategoryName,Description)VALUES(1,饮料,软饮料、咖啡、茶、啤酒和淡啤酒)INSERTINTOCategory(CategoryID,CategoryName,Description)VALUES(2,调味品,香甜可口的果酱、调料、酱汁和调味品)INSERTINTOCategory(CategoryID,CategoryName,Description)VALUES(3,点心,甜点、糖和面包)例：向Seller表中插入一行数据，其中Sex字段使用默认值为男，HireDate等字段均取空值。INSERTINTOSeller(SaleID,SaleName,Sex,Birthday,HireDate,Address,Telephone,Notes)VALUES(s11,赵宇飞,DEFAULT,1974-07-5,NULL,NULL,NULL,NULL)或INSERTINTOSeller(SaleID,SaleName,Birthday)VALUES(s11,赵宇飞,1974-07-25),例：对表中所有列插入数据，则可省略列名。INSERTINTOCategoryVALUES(1,饮料,软饮料、咖啡、茶、啤酒和淡啤酒)INSERTINTOCategoryVALUES(调味品,2,香甜可口的果酱、调料、酱汁和调味品)INSERTINTOCategoryVALUES(2,点心)【注意】在插入数据时，对允许为空的列可使用NULL插入空值；对具有默认值的列可使用DEFAULT插入默认值。当向表中所有列都插入新数据时，可以省略列名表，但必须保证VALUES后的各数据项位置同表定义时的顺序一致，否则系统会报错。,错误,错误,例：创建数据表Orders，然后再插入一行数据。,CREATETABLEOrders(OrderIDintIDENTITY(10248,1),CustomerIDchar(3)NOTNULL,SaleIDchar(3)NOTNULL,OrderDatedatetimeDEFAULTgetdate()INSERTINTOOrders(CustomerID,SaleID)Values(c01,s11),【注意】具有IDENTITY属性的列，其值由系统给出，用户不必向表中插入数据。,2、使用Transact-SQL语句修改表中数据,UPDATEtable_nameSETcolumn_name=expression,nWHEREsearch_conditions其中：table_name：要更新数据的表名。column_name：要更新数据的列名。expression：更新后的数据值。search_conditions：更新条件，只对表中满足该条件的记录进行更新。,例：将Product表中啤酒的价格改为4元。UPDATEProductSETPrice=4WHEREProductName=啤酒例：将Seller表中SaleID为s11的地址改为东直门外大街108号，电话改为（010）60486658。UPDATESellerSETAddress=东直门外大街108号,Telephone=(010)60486658WHERESaleID=s11例：将Product表中CategoryID为2的所有产品的价格下调10%。UPDATEProductSETPrice=Price*(1-0.1)WHERECategoryID=2,3、使用Transact-SQL语句删除表中数据,DELETEFROMtable_nameWHEREsearch_conditions说明：删除表中符合search_conditions的数据；缺省WHERE子句时，表示删除该表中的所有数据。例：将SaleID为s11的数据从Seller表中删除。DELETEFROMSellerWHERESaleID=s11,4、使用SQLServerManagementStudio插入、修改、删除表中数据,课堂练习,1、根据以下数据表中的内容，使用T-SQL语句分别创建出表结构，并完成数据的插入。（1）表名：Employee（2）表名：Product2、修改“ProductID”为“Y19”的产品，使其“Stocks”为“1000”、“Price”为“200”。3、删除“EmpNo”为“J0015”的职员记录。,数据完整性的含义包括以下内容：（1）数值的完整性，指数据类型与取值的正确性。（2）表内数据不相互矛盾。（3）表间数据不相互矛盾，指数据的关联性不被破坏。,约束定义了必须遵循的用于维护数据一致性和正确性的规则，是强制实现数据完整性的主要途径。约束有5种类型，包括：主键约束、唯一性约束、检查约束、默认约束、外键约束（参照约束）。,2-5,1、主键（PRIMARYKEY）约束,主键用于唯一地标识表中每一条记录。我们可以定义表中的一列或多列为主键，则主键列上没有任何两行具有相同值（即重复值），该列也不能为空值。为了有效实现数据的管理，每张表都应该有自己的主键，且只能有一个主键。,CREATETABLEStudent(sidintPRIMARYKEY,snamechar(20)NOTNULL,sageint,scitychar(10),在SQLServer中，对于基本表的约束分为列约束和表约束。列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，定义表约束时必须指出要约束的列的名称。完整性约束的基本语法格式如下：CONSTRAINTconstraint_name(约束名),使用Transact-SQL语句操作法设置主键约束，其语法形式如下：CONSTRAINTconstraint_namePRIMARYKEY（column_name）,2、唯一性（UNIQUE）约束,用来限制表中任意两行在指定列上都不允许有相同的值。一个表上可以放置多个UNIQUE约束。,唯一性约束和主键约束的区别：唯一性约束允许在该列上存在NULL值，而主键约束限制更为严格，不但不允许有重复，而且也不允许有空值。,CREATETABLEDepartment(dep_idintPRIMARYKEY,dep_namechar(20)NOTNULLUNIQUE,dep_headchar(5)使用Transact-SQL语句完成惟一性约束的操作，其语法形式如下：CONSTRAINTconstraint_nameUNIQUE（column_name）,3、检查（CHECK）约束,CREATETABLEStudent(sidintPRIMARYKEY,snamechar(20)NOTNULL,sageintCHECK(sageBETWEEN18AND30),scitychar(10),用来指定某列的可取值的范围。它通过限制输入到列中的值来强制域的完整性。我们可以在单列上定义多个CHECK约束，以它们定义的顺序来求值。,用Transact-SQL语句创建检查约束。其语法形式如下：CONSTRAINTconstraint_nameCHECK（logical_expression）,4、默认（DEFAULT）约束,CREATETABLEStudent(sidintPRIMARYKEY,snamechar(20)NOTNULL,sageintdefault20,scitychar(10),用于给表中指定列赋予一个常量值（默认值），当向该表插入数据时，如果用户没有明确给出该列的值，SQLServer会自动为该列输入默认值。每列只能有一个DEFAUL