SQLServer教案第07周-数据库和表的创建

数据库原理与应用——SQLServer2005教案邹竞授课日期年月日第6周授课形式讲课授课时数4章节名称第06章数据库和表的创建教学目的与要求①理解数据库中的数据文件和日志文件的概念和作用②理解SQLServer中系统数据库与用户数据库的概念③掌握数据库的创建方法以及创建数据库的CreateDatabase语句④掌握在数据库中创建表的CreateTable语句和撤销表的DropTable语句⑤掌握为表添加字段、撤销字段的方法和相关语句。⑦理解约束的概念和类型，以及约束的作用⑦掌握为表添加约束、撤销约束的方法和相关语句。⑧掌握用InsertInto语句为数据表插入新记录的语法⑨掌握用Update语句为数据表更新已经数据的语法⑩掌握用DeleteFrom语句为数据表删除记录的语法教学重点教学难点教学方法和手段讲授法结合课堂实例分析讨论教学过程与组织导入新课我们已经学习关系数据库的相关理论。现在，我们以SQLServer2005数据库为例，介绍如何在DBMS中创建数据库和表。讲授新课第07章数据库和表的创建第01节数据库的创建7.1.1SQLServer数据库的结构1．数据库文件和文件组SQLServer2000用文件来存放数据库，数据库文件有三类。主数据库文件（Primary）：存放数据，每个数据库都必须有一个主数据文件。其他数据文件（Secondary）：存放数据，一个数据库可以没有也可以有多个Secondary文件。事务日志文件（TransactionLog）：存放事务日志，每个数据库必须有一个或多个日志文件。一般情况下，一个简单的数据库可以只有一个主数据文件和一个日志文件。如果数据库很大，则可以设置多个Secondary文件和日志文件，并将它们放在不同的磁盘上。默认状态下，数据文件名为“数据库名_Data.MDF”，日志文件名为“数据库名_Log.LDF”。数据库的创建者可以在创建时指定其他的路径和文件名，也可以添加Secondary文件和更多的日志文件。文件组允许多个数据库文件组成一个组，并对它们整体进行管理。比如，可以将三个数据文件（data1.mdf、data2.mdf和data3.mdf）分别创建在三个盘上，这三个文件组成文件组fgroup1，在创建表的时候，就可以指定一个表创建在文件组fgroup1上。SQLServer的数据库文件和文件组必须遵循以下规则：（1）一个文件和文件组只能被一个数据库使用。（2）一个文件只能属于一个文件组。（3）数据和事务日志不能共存于同一文件或文件组上。（4）日志文件不能属于文件组。2．数据库对象SQLServer2000数据库中的数据在逻辑上被组织成一系列对象，当一个用户连接到数据库后，数据库原理与应用——SQLServer2005教案邹竞他所看到的是这些逻辑对象，而不是物理的数据库文件。SQLServer中有以下数据库对象：表（Table）、视图（View）、存储过程（Storedprocedures）、触发器（Triggers）、用户自定义数据类型（User-defineddatatypes）、用户自定义函数（User-definedfunctions）、索引（Indexes）、规则（Constraints）、默认值（Defaults）等。7.1.2SQLServer2005的系统数据库1．master数据库master数据库记录了SQLServer系统级的信息，包括系统中所有的登录账号、系统配置信息、所有数据库的信息、所有用户数据库的主文件地址等。每个数据库都有属于自己的一组系统表，记录了每个数据库各自的系统信息，这些表在创建数据库时自动产生。为了与用户创建的表相区别，这些表被称为系统表，表名都以“sys”开头。master数据库中还有很多系统存储过程和扩展存储过程。系统存储过程是预先编译好的程序，所有的系统存储过程的名字都以sp_开头。2．tempdb数据库tempdb数据库用于存放所有连接到系统的用户临时表和临时存储过程以及SQLServer产生的其他临时性的对象。tempdb是SQLServer中负担最重的数据库，因为几乎所有的查询都可能需要使用它。在SQLServer关闭时，tempdb数据库中的所有对象都被删除，每次启动SQLServer时，tempdb数据库里面总是空的。3．model数据库model数据库是系统所有数据库的模板，这个数据库相当于一个模子，所有在系统中创建的新数据库的内容，在刚创建时都和model数据库完全一样。如果SQLServer专门用作一类应用，而这类应用都需要某个表，甚至在这个表中都要包括同样的数据，那么就可以在model数据库中创建这样的表，并向表中添加那些公共的数据，以后每一个新创建的数据库中都会自动包含这个表和这些数据。当然，也可以向model数据库中增加其他数据库对象，这些对象都能被以后创建的数据库所继承。4．msdb数据库msdb数据库被SQLServer代理（SQLServerAgent）来安排报警、作业，并记录操作员。7.1.3创建数据库1．在ManagerStudio中创建数据库略。2．使用T-SQL创建数据库使用T-SQL语句创建数据库的语法格式如下：CREATEDATABASE数据库名ON(NAME=数据文件逻辑名称,FILENAME='存取路径\数据文件名',SIZE=数据文件初始大小,FILEGROWTH=数据文件增长率%)LOGON(NAME=日志文件逻辑名称,FILENAME='存取路径\日志文件名',SIZE=日志文件初始大小,数据库原理与应用——SQLServer2005教案邹竞FILEGROWTH=日志文件增长率%)注意：在SQLServer2005/2008中，数据文件和日志文件的初始大小至少要为3M。通过执行以下的语句可以创建LWZZ数据库。CREATEDATABASELWZZON(NAME=LWZZ_Data,FILENAME='C:\ProgramFiles\Microsoft\MSSQL\data\LWZZ_Data.MDF',SIZE=3,FILEGROWTH=10%)LOGON(NAME=LWZZ_Log,FILENAME='C:\ProgramFiles\Microsoft\MSSQL\data\LWZZ_Log.LDF',SIZE=3,FILEGROWTH=10%)我们也可以简单地来创建数据库：CREATEDATABASEmytest由于没有指定主文件名，在默认的情况下，命名主文件为mytest.mdf，日志文件名为mytest_log.log。同时由于按复制Model数据库的方式来创建新的数据库，主文件和日志文件的大小都同Model数据库的主文件和日志文件大小一致，并且可以自由增长。7.1.4修改数据库修改数据库包括增减数据文件和日志文件、修改文件属性（包括更改文件名和文件大小）、修改数据库选项等。1．在ManagerStudio中修改数据库略2．使用T-SQL语句修改数据库（1）用ALTERDATABASE语句增减数据文件和日志文件。增加数据文件的语法如下：ALTERDATABASE数据库名ADDFILE(NAME=逻辑数据文件名,FILENAME='存取路径\数据文件名'SIZE=数据文件初始大小,MAXSIZE=文件大小最大值,FILEGROWTH=文件增长率)修改数据文件的大小的语法如下：ALTERDATABASE数据库名MODIFYFILE(数据库原理与应用——SQLServer2005教案邹竞NAME=逻辑数据文件名,SIZE=数据文件初始大小,MAXSIZE=文件大小最大值,FILEGROWTH=文件增长率)例如：ALTERDATABASELWZZADDFILE(NAME=LWZZ_data2,FILENAME='C:\ProgramFiles\Microsoft\MSSQL\data\LWZZ_data2.ndf'SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=1MB)其中ADDFILE是指增加一个数据文件，还可以是ADDLOGFILE、REMOVEFILE、MODIFYFILE等，分别代表增加日志文件、删除数据库文件和修改文件信息。比如，以下的语句将LWZZ数据库的第二个数据文件LWZZ_data2的初始大小修改为20MB。ALTERDATABASELWZZMODIEYFILE(NAME=LWZZ_data2,SIZE=20MB)关于ALTERDATABASE语句的更详细用法可以参考SQLServer2000的在线手册。（2）用ALTERDATABASE语句修改数据库选项，例如ALTERDATABASELWZZSETAUTO_SHRINKON（3）使用系统存储过程sp_dboption修改数据库选项Sp_dboption'LWZZ','autoshrink','true'除了设置选项外，Sp_dboption还有很多其他的作用。如运行不带参数的Sp_dboption，可以得到所有数据库选项列表。如果希望看到某个数据库当前的选项设置情况，可以给Sp_dboption加上一个数据库名作为参数，SQLServer将显示所有在该数据库中设置为True的选项。如果想查看某个特定选项的当前设置值，可以输入数据库名和选项名。7.1.5删除数据库1．在ManagerStudio中删除数据库略2．使用T-SQL语句删除数据库使用SQL语句删除数据库需要使用DROPDATABASE语句。使用DROPDATABASE语句可以一次删除多个数据库。语法格式为：DROPDATABASE数据库名列表例如：DROPDATABASEnorthwind,pubs第02节数据表的创建数据库原理与应用——SQLServer2005教案邹竞7.2.1创建表结构1．在ManagerStudio中创建数据表略2．使用T-SQL语句创建数据表使用SQL语句定义表的语句格式为：CREATETABLE表名(列名数据类型[列级完整性约束定义]{,列名数据类型[列级完整性约束定义]…}[,表级完整性约束定义])SQL语句中有定义这三类完整性的相关语法成份，完整性约束又分为列级约束和表级约束。（1）实体完整性约束，用于定义表中的主码约束。在本列的列名、数据类型之后，写上以下的英文短语，就是将本列定义为主码。PRIMARYKEY（2）参照完整性约束，用于定义表中的外码约束。在本列的列名、数据类型之后，写上以下的英文短语，就是将本列定义为外码。[FOREIGNKEY(列名)]REFERENCES外表名(外表中被引用的列名)（3）惟一性约束。在某一列的列名、数据类型说明之后，写上以下的英文短语，就是将本列定义为惟一性约束(值唯一，无重复)。UNIQUE（5）（非）空值约束。将某列定义为（非）空值约束的方法是，在某一列的列名、数据类型之后，加上以下成份。（NOT）NULL（6）CHECK约束。当需要限制某列的取值范围时，通常使用这一约束。方法是，在某一列的列名、数据类型之后，加上以下成份。CHECK（约束表达式）凡是在某一列的列名、数据类型之后定义的约束，都叫“列级约束”。特别地，默认值约束和（非）空值约束只能定义为列级约束。凡是在一个表的全部列定义之后再定义的约束，都叫“表级约束”。多数情况下，一个表中的主码是惟一的列，在本列将它定义为主码约束即可。（7）默认值严格来说，默认值不是约束。将某列定义为默认值约束的方法是，在某一列的列名、数据类型之后，加上以下成份。DEFAULT常量下面给出创建表的实例：系信息表Department结构如下：列名说明类型和长度约束Deno系编号字符串，6位主键Dname系名称字符串，最长20位非空、唯一Tel联系电话字符串，最长11位唯一数据库原理与应用——SQLServer2005教案邹竞CREATETABLEDepartment(DeNochar(6)CONSTRAINTDepartment_DeNo_PKPrimaryKey,DNamevarchar(20)CON