《数据库和表的创建》PPT课件.ppt

第5章数据库和表的创建,【本章导读】,本章主要讲解SQLServer2005数据库和表的结构、设计、创建和管理，介绍SQLServer2005提供的数据类型以及数据的插入、修改和删除操作。,【本章要点】,数据库的基本结构数据库的创建和管理表的结构SQLServer2000的系统数据类型表的创建和管理数据的插入、修改和删除,第5章数据库和表的创建,5.1数据库的创建5.2数据表的创建5.3数据更新本章小结习题五,5.1数据库的创建,5.1数据库的创建,5.1.1数据库的结构5.1.2系统数据库5.1.3创建数据库5.1.4查看数据库信息5.1.5修改数据库5.1.6删除数据库,5.1.1数据库的结构,SQLServer2000用文件来存放数据库，即将数据库映射到操作系统文件上。数据库文件有三类：主数据文件。数据库文件将被存放在系统的默认存储路径上。SQLServer2000的默认存储路径是“C:ProgramFilesMicrosoftSQLServerMSSQLData”。,主数据文件,PrimaryDatabaseFile，也称主文件。主数据文件主要用来存储数据库的启动信息、部分或全部数据，是数据库的关键文件。主数据文件是数据库的起点，包含指向数据库中其他文件的指针。每个数据库都有一个主数据文件。主数据文件的推荐文件扩展名是.mdf。,次要数据文件,SecondaryDatabaseFile，也称辅助数据文件。次要数据文件用于存储主数据文件中未存储的剩余数据和数据库对象。一个数据库可以没有，也可以有多个次要数据文件。次要数据文件的推荐文件扩展名是.ndf。,事务日志文件,简称日志文件，存放用来恢复数据库所需的事务日志信息。每个数据库必须有一个或多个日志文件。事务日志的推荐文件扩展名是.ldf。,逻辑文件名和物理文件名,逻辑文件名是在所有T-SQL语句中引用物理文件时所使用的名称。逻辑文件名与物理文件名一一对应，其对应关系由SQLServer系统维护。逻辑文件名必须符合SQLServer的标识符命名规则，而且在数据库中的逻辑文件名必须是惟一的。物理文件名是包括目录路径的物理文件名。它必须符合操作系统文件的命名规则。,文件组,SQLServer2000中提供了两种类型的文件组：主文件组和用户定义文件组。主文件组包含主数据文件和任何没有明确分配给其他文件组的数据文件。,规则：,（1）一个文件和文件组只能被一个数据库使用。（2）一个文件只能属于一个文件组。（3）日志文件不能属于文件组。,数据库对象,表（Table）、视图（View）、存储过程（Storedprocedures）、触发器（Triggers）、用户定义数据类型（User-defineddatatypes）、用户自定义函数（User-definedfunctions）、索引（Indexes）、规则（rules）、默认值（Defaults）等。,在SQLServer2005中创建的每个对象都必须有一个惟一的完全限定对象名，即对象的全名。完全限定对象名由四个标识符组成：服务器名、数据库名、所有者名和对象名，各个部分之间由句点“.”连接。格式如下：服务器名.数据库名.所有者名.对象名server.database.owner.object,使用当前数据库内的对象可以省略完全限定对象名的某部分，省略的部分系统将使用默认值或当前值，如：server.database.object/*省略所有者名称*/server.owner.object/*省略数据库名称*/database.owner.object/*省略服务器名称*/serverobject/*省略数据库及所有者名称*/owner.object/*省略服务器及数据库名称*/object/*省略服务器、数据库及所有者名称*/,5.1.2系统数据库,SQLServer2005的系统数据库分别是：master数据库model数据库msdb数据库tempdb数据库,1master数据库,master数据库记录SQLServer系统的所有系统级信息。如果master数据库不可用，则SQLServer将无法启动。,2model数据库,model数据库是在SQLServer实例上创建的所有数据库的模板。可以向model数据库中增加其他数据库对象，这些对象都能被以后创建的数据库所继承。,3msdb数据库,msdb数据库由SQLServer代理（SQLServerAgent）来计划警报和作业。,4tempdb数据库,tempdb数据库是连接到SQLServer实例的所有用户都可用的全局资源，它保存了所有临时表和临时存储过程。每次启动SQLServer时，都要重新创建tempdb，以便系统启动时，该数据库总是空的。在断开连接时，系统会自动删除临时表和存储过程，并且在系统关闭后没有活动连接。,5.1.3创建数据库,使用企业管理器创建数据库：参见录像；使用T-SQL语句创建数据库。,使用T-SQL语句创建数据库,CREATEDATABASEdatabase_nameON,n,nLOGON,nCOLLATEFORLOAD|FORATTACH,:=PRIMARY(NAME=逻辑文件名,FILENAME=存放数据库的物理路径和文件名,SIZE=数据文件的初始大小,MAXSIZE=指定文件的最大大小,FILEGROWTH=指出文件每次的增量）,项用以定义用户文件组及其文件。格式如下：:=FILEGROUP文件组名,【例5-1】创建一个名为BookSys的数据库。CREATEDATABASEBookSys,命名主数据文件为BookSys.mdf，事务日志文件名为BookSys_log.log,【例5-2】创建一个名为KEJI_DB的数据库。要求有三个文件，其中，主数据文件为10MB，最大大小为50MB，每次增长20%；辅助数据文件属于文件组Fgroup，文件为10MB，大小不受限制，每次增长10%；事务日志文件大小为20MB，最大100MB，每次增长10MB。文件存储在“c:db”路径下。,CREATEDATABASEKEJI_DB/*数据库名*/ONPRIMARY/*主文件组*/(NAME=KEJI_DB_Data1,/*主文件逻辑名称*/FILENAME=c:dbKEJI_DB_Data1.mdf,/*主文件物理名称*/SIZE=10mb,MAXSIZE=50mb,FILEGROWTH=20%),FILEGROUPFgroup/*文件组*/(NAME=KEJI_DB_Data2,/*主文件逻辑名称*/FILENAME=c:dbKEJI_DB_Data2.ndf,/*主文件物理名称*/MAXSIZE=UNLIMITED,/*增长不受限制*/SIZE=10Mb,FILEGROWTH=10mb)LOGON(NAME=KEJI_DB_Log,/*日志文件逻辑名称*/FILENAME=c:dbKEJI_DB_Log.ldf,/*日志文件物理名称*/SIZE=20mb,MAXSIZE=100mb,FILEGROWTH=10mb),5.1.4查看数据库信息,sp_helpdb数据库名使用系统存储过程sp_helpdb查看数据库信息。不指定数据库参数，将显示服务器中所有数据库的信息。指定具体数据库参数，将显示指定数据库的信息。,5.1.4查看数据库信息,sp_databases此命令将显示服务器中所有可以使用的数据库的信息。,5.1.4查看数据库信息,sp_helpfile文件名查看数据库中文件的信息不指定文件名参数，将显示当前数据库中所有文件的信息。指定具体文件名参数，将显示数据库中指定文件的信息。,5.1.4查看数据库信息,sp_helpfilegroup文件组名不指定文件组名参数,将显示数据库中所有文件组的信息。指定具体文件组名参数，将显示数据库中指定文件组的信息。,5.1.5修改数据库,使用SQLServerManagementStudio修改数据库：参见录像文件；使用T-SQL语句修改数据库,使用T-SQL语句修改数据库,ALTERDATABASEdatabase_nameADDFILE,nTOFILEGROUPfilegroup_name/*在文件组中增加数据文件*/|ADDLOGFILE,n/*增加事务日志文件*/|REMOVEFILElogical_file_name/*删除数据文件*/|ADDFILEGROUPfilegroup_name/*增加文件组*/|REMOVEFILEGROUPfilegroup_name/*删除文件组*/|MODIFYFILE/*修改文件属性*/|MODIFYNAME=new_dbname/*更新数据库名称*/,【例5-3】为KEJI_DB数据库增加一个数据文件KEJI_DB_Data3，物理文件名为KEJI_DB_Data3.ndf，初始大小为5MB，最大大小为50MB，每次扩展1MB。ALTERDATABASEKEJI_DBADDFILE(NAME=KEJI_DB_Data3,FILENAME=c:dbKEJI_DB_Data3.ndf,SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=1MB),【例5-4】将KEJI_DB数据库的第二个数据文件KEJI_DB_data2的初始大小修改为40MB。ALTERDATABASEKEJI_DBMODIFYFILE(NAME=KEJI_DB_data2,SIZE=40MB),【例5-5】删除KEJI_DB的数据文件KEJI_DB_Data3。ALTERDATABASEKEJI_DBREMOVEFILEKEJI_DB_Data3,【例5-6】使用ALTERDATABASE语句修改数据库选项，将Study数据库的自动缩减选项设置为TRUE。ALTERDATABASEStudySETAUTO_SHRINKON【例5-7】使用系统存储过程sp_dboption修改数据库选项，将Study数据库的自动缩减选项设置为TRUE。Sp_dboptionStudy,autoshrink,true,5.1.6删除数据库,使用SQLServerManagementStudio删除数据库：参见录像文件；使用T-SQL语句删除数据库DROPDATABASEdatabase,n,删除数据库时，请注意如下情况：（1）如果数据库涉及日志传送操作，请在删除数据库之前取消日志传送操作。（2）若要删除为事务复制发布的数据库，或删除为合并复制发布或订阅的数据库，必须首先从数据库中删除复制。（3）如果数据库已损坏，不能删除复制，则可以首先使用ALTERDATABASE将数据库设置为脱机，然后再删除数据库。（4）不能删除系统数据库。（5）删除数据库后，应备份master数据库。,5.2数据表的创建,5.2数据表的创建,5.2.1数据类型5.2.2创建表结构5.2.3查看表结构5.2.4修改表结构5.2.5删除表结构,5.2.1数据类型,表是用来存储数据和操作数据的逻辑结构。关系数据库中的所有数据都存储在表中，因此表是SQLServer数据库最重要的组成部分。,1.整数数据类型,数字数据类型,decimal/numeric可以指定精度小数点两边的总位数P刻度小数点右面的位数S,例1如指定某列的精度为6，小数位数为3，即decimal(6,3)，则向某记录的该例附值86.436876，那么该列实际存储的是：86.4368。如指定某列的精度为8，小数位数为3，即decimal(8,3)，则向某记录的该例附值12685.564986，那么该列实际存储的是：12685.564。,注意：数值型数据，其小数位数必须小于精度；,浮点数据类型,real/float可以存储范围非常大的数字但是不能保证精度,real型数据范围从-3.40E+381.79E+38，存储时使用4个字节，精度可以达到7位。float型数据范围从-1.79E+381.79E+38。利用float来表明变量和表列时可以指定用来存储按科学记数法记录的数据尾数的bit数。如float(n)，n的范围是153。当n的取值为124时，float型数据可以达到的精度是7位，用4个字节来存储。当n的取值范围时2553时，float型数据可以达到的精度是15位，用8个字节来存储。,2.字符数据类型,SQLServer提供了3类字符数据类型：charvarchartext,8000个英文或者4000个汉字,最大长度可以达到231-1个字符，约2GB,字符数据类型,char定长字符串，应给定最大长度给定字符串短于最大长度，补以空格给定字符串长于最大长度，截掉多余字符,字符数据类型,varchar变长字符串，应给定最大长度给定字符串短于最大长度，不补以空格给定字符串长于最大长度，截掉多余字符,字符数据类型,text存储数量庞大的变长字符数据,3.日期/时间数据类型,datetime1753.1.19999.12.31smalldatetime1900.1.12079.6.6用户可以使用GETDATE()函数来得到系统时间使用SETDATEFORMAT命令设置日期格式,【例5-10】设置日期格式为“月-日-年”。SETDATEFORMATMDY本例设置的日期格式为“月-日-年”。M表示月，D表示日，Y表示年。常用的日期格式如下：年年月日月-日-年月/日/年年-月-日系统默认的日期格式是：年-月-日。,4.货币数据类型,向表中添加数据的时候，可在最高位前放$或其他货币单位的记号，但是没有严格要求。,5.二进制数据类型,一些用十六进制来表示的数据。插入数据时在数据前加0 x标识。Binary:固定长度，最长8K字节。Varbinary：其定义形式为varbinary(n)，数据的存储长度是变化的，它为实际所输入数据的长度加上4字节。其它含义同binary。Image：用于存储照片、目录图片或者图画，其理论容量为231-1（2，147，483，647）个字节。,6.双字节数据类型,Nchar：其定义形式为nchar（n），最大长度为4000。Nvarchar：其定义形式为nvarchar（n），最大长度为4000。Ntext：与text数据类型类似，存储在其中的数据通常是直接能输出到显示设备上的字符，显示设备可以是显示器、窗口或者打印机。,7图像、文本数据的使用,字符用两个字节存储，不论英文还是汉字ncharnvarcharntext,最多4000个字符,在SQLServer7.0以前的版本中，文本和图像数据都是这样与表中的其他数据分开存储的。SQLServer2000提供了将小型的文本和图像数据在行中存储的功能。,为了指定某个表的文本和图像数据在行中存储，需要使用系统存储过程sp_tableoption设置该表的“textinrow”选项为TRUE。当指定“textinrow”选项时，还可以指定一个文本和图像数据大小的上限值，这个上限值应在24字节到7000字节之间。,当同时满足以下两个条件时，文本和图像数据可以直接存储在行中：（1）文本和图像数据的大小不超过指定的上限值。（2）数据行有足够的空间存放这些数据。,现建立表text_example：createtabletext_example(bin_1text,bin_2ntext)sp_tableoptiontext_example,textinrow,truesp_tableoptiontext_example,textinrow,1000sp_tableoptiontext_example,textinrow,false,8用户定义数据类型及使用,sp_addtypetype_name,system_type,NULL|NOTNULL|NONULLsp_droptypetype_name,凡是包含了诸如“（）”或“，”等分隔符的系统数据类型，必须使用单引号括起来。,【例5-13】下面的例子创建了两个用户定义数据类型：usemodelgoexecsp_addtypetelelephone,varchar(24),notnullexecsp_addtypefax,varchar(24),null,5.2.2创建表结构,在创建表及其对象之前，最好先规划并确定表的下列特征：（1）表要包含的数据的类型；（2）表中的列数，每一列中数据的类型和长度（如果必要）；（3）哪些列允许空值。,Student,Course,Score,表的创建也有两种方式,利用SQLServerManagementStudio提供的图形界面创建表：参见录像；使用T-SQL命令创建。,使用T-SQL命令创建,CREATETABLEtable_name（column_namedatatypeNOTNULL|NULL）,说明：（1）table_name是所创建的表的名称，表名在一个数据库内必须惟一。（2）column_name是列名，列名在一个表内必须惟一。（3）datatype是该列的数据类型，可以使用系统数据类型，也可以使用用户定义数据类型。对于需要给定数据最大长度的类型，在定义时要给出长度。（4）NOTNULL|NULL指示该列可否输入空值，默认可以为空。,【例5-14】在Study数据库上创建课程表Course，成绩表Score。useStudygocreatetableCourse/*定义课程表Course*/(cnochar(10)notnull,/*课程号列cno，不能输入空值*/camevarchar(40)notnull,/*课程名列cname，不能输入空值*/creditchar(4),/*学分列Credit*/notesvarchar(200)/*备注列Notes*/gocreatetableScore/*定义成绩表Score*/(snochar(10)notnull,/*学号列sno，不能输入空值*/cnochar(10)notnull,/*课程号列cno，不能输入空值*/degreetinyint)/*成绩列degree*/go,5.2.3查看表结构,sp_help查看表结构，包括表的所有者、类型（系统表还是用户表）、创建时间、表上每一列的名称、数据类型、表上定义的索引以及约束等。【例5-15】查看表Course的基本结构。useStudygoexecsp_helpCourse,5.2.4修改表结构,使用SQLServerManagementStudio修改表使用T-SQL语句实现。,（1）添加列,向表中添加列的语句格式如下：ALTERTABLE表名ADD列名列的描述,（1）添加列,【例5-17】向Student表中添加两个新的列：邮箱Email和电话phone。useStudygoaltertableStudentaddEmailvarchar(20)null,phonechar(20)null,可以一次向表中添加多列，各列之间用逗号分开即可。,（2）删除列,删除一列的语句格式为：ALTERTABLE表名DROPCOLUMN列名,（2）删除列,【例5-18】删除Student表的Email列。useStudygoaltertableStudentdropcolumnemail,（3）修改列定义,修改列定义的语句格式为：ALTERTABLE表名ALTERCOLUMN列名列的描述,（3）修改列定义,【例5-19】将Student表的姓名列sname改为最大长度为20的varchar型数据，且不允许空值。altertableStudentaltercolumnsnamevarchar(20)notnull,列中没有存放是空值的记录；在列上没有创建索引。,5.2.5删除表结构,使用SQLServerManagementStudio删除表DROPTABLE表名需要注意的是DROPTABLE语句不能用来删除系统表。【例5-20】删除Study数据库中的表table1。useStudygodroptabletable1,5.3数据更新,5.3数据更新,5.3.1向表中插入数据5.3.2修改表中数据5.3.3删除表中数据5.3.4使用SQLServerManagementStudio实现数据操作,5.3.1向表中插入数据,INSERT语句，可以实现数据的插入操作。INSERT语句的基本语法为：INSERTINTO表名（列名）VALUES（表达式）,1添加数据到一行中的所有列,当将数据添加到一行中的所有列时，使用VALUES关键字来给出要添加的数据。INSERT语句中无需给出表中的列名，只要在VALUES中给出的数据与用CREATETABLE定义表时给定的列名顺序、类型和数量均相同即可。,1添加数据到一行中的所有列,【例5-21】向Course表中添加一条记录。useStudygoinsertintoCoursevalues(080110H,数据库原理与应用,3,必修),2添加数据到一行中的部分列,要将数据添加到一行中的部分列时，则必须同时给出要使用的列名以及要赋给这些列的数据。【例5-22】向Student表中添加一条记录。useStudygoinsertintoStudent(sno,sname,sage,ssex,sbirthday)values(05091101,李明,19,男,1989-1-10),2添加数据到一行中的部分列,注意：（1）输入数据的顺序和数据类型必须与表中列的顺序和数据类型一致。（2）列名与数据必须一一对应，当每列都有数据输入时，列名可以省略，但输入数据的顺序必须与表中列的定义顺序相一致。（3）可以不给全部列赋值，但没有赋值的列必须是可以为空的列。（4）插入字符型和日期型数据时要用单引号引起来。,3添加多行数据,通过在INSERT语句中嵌套子查询，可以将子查询的结果作为批量数据，一次向表中添加多行数据。查询语句将在第六章中讲解，在此只给出一个简单例子。,【例5-23】添加批量数据。创建一个新的数据表stu_temp：createtablestu_temp(snochar(10)notnull,snamechar(20)notnull,sagetinyint)go假设Student中已有一批数据，可以从Student表中选择部分数据插入到新表stu_temp中，此处将所有女生的信息插入到新表stu_temp中：insertintostu_tempselectsno,sname,sagefromStudentwheressex=女,5.3.2修改表中数据,当数据添加到表中后，会经常需要修改，如客户的地址发生了变化，货品库存量的增减等。使用UPDATE语句可以实现数据的修改，其语法格式为：UPDATE表名SET列名=表达式WHERE条件,【例5-24】将所有课程的备注信息都改为“必修”。useStudygoupdateCoursesetnotes=必修,【例5-25】将课程号为“080110H”的课程改为“必修”。useStudygoupdateCoursesetnotes=必修wherecno=080110H,5.3.3删除表中数据,当数据的添加工作完成以后，随着使用和对数据的修改，表中可能存在着一些无用的数据，这些无用数据不仅会占用空间，还会影响修改和查询数据的速度，所以应及时将它们删除。,1使用DELETE语句删除数据,使用T-SQL中的DELETE语句可以删除数据表中的一个或多个记录。DELETE语句最简单的形式如下：DELETE表名WHERE条件,1使用DELETE语句删除数据,【例5-26】删除学号为“05091101”的学生的基本信息。useStudygodeleteStudentwheresno=05091101,2使用TRUNCATETABLE语句删除数据,TRUNCATETABLE语句提供了一种删除表中所有记录的快速方法，因为TRUNCATETABLE语句不记录日志，只记录整个数据页的释放操作，而DELETE语句对每一行修改都记录日志，所以TRUNCATETABLE语句总比没有指定条件的DELETE语句快。,2使用TRUNCATETABLE语句删除数据,【例5-27】删除所有学生的成绩记录。TRUNCATETABLEScore,5.3.4使用SQLServerManagementStudio实现数据操作,参见录像文件。,本章小结,本章主要讲述了数据库的结构、数据库和表的创建与管理、SQLServer2005提供的数据类型以及表中数据的添加、修改、删除。通过对本章的学习，读者应能建立和管理数据库和表。熟悉对表中数据的操作，包括插入、修改和删除，并向表中添加一定量的数据，为后面的数据查询做准备。,本章小结,SQLServer2005用文件来存放数据库，即将数据库映射到操作系统文件上。数据库文件有3类：主数据文件、次要数据文件和事务日志文件。,本章小结,SQLSever2005数据库中的数据在逻辑上被组织成一系列对象，当一个用户连接到数据库后，他所看到的是这些逻辑对象，而不是物理的数据库文件。SQLServer2005中的对象有：表（Table）、视图（View）、存储过程（StoredProcedures）、触发器（Triggers）、用户定义数据类型（User-definedDataTypes）、用户自定义函数（User-definedFunctions）、索引（Indexes）、规则（Rules）、默认值（Defaults）等。,本章小结,SQLServer2005有master数据库、tempdb数据库、model数据库和msdb数据库4个系统库，在创建任何数据库之前，依次打开SQLServerManagementStudio中“对象资源管理器”的“服务器”/“数据库”/“系统数据库”目录，我们就可以看到这4个系统数据库。,本章小结,我们可以使用SQLServerManagementStudio和T-SQL语句两种方式创建、查看、修改和删除数据库。T-SQL提供了数据库创建语句CREATEDATABASE、数据库查看语句sp_helpdb和sp_databases、文件查看语句sp_helpfile、数据库修改语句ALTERDATABASE、数据库删除语句DROPDATABASE。,本章小结,SQLServer为了实现T-SQL的良好性能，提供了丰富的数据类型，包括数值型、字符型、日期/时间型、货币型、二进制型、双字节型、图像、文本型以及用户自定义型数据。我们同样可以使用SQLServerManagementStudio和T-SQL语句两种方式创建、显示、修改和删除数据表结构。T-SQL提供了数据表创建语句CREATETABLE、数据表显示语句sp_help、数据表修改语句ALTERTABLE、数据表删除语句DROPTABLE。,本章小结,新创建的表中没有任何数据，可以通过数据更新操作向表中插入新数据，修改表中的数据和删除表中的数据。我们可以使用INSERT语句插入数据、UPDATE语句修改数据、DELETE和TRUNCATE语句删除数据。,习题五,一、填空题1SQLServer2005提供的系统数据类型有_、_、_、_、_和货币数据，也可以使用用户定义的数据类型。2SQLServer2005的数据库包含3类文件：_、_和_。包含4个系统数据库：_、_、_和msdb数据库。3可以使用系统存储过程_来查看表的定义，后面加上要查看的_作为参数。,习题五,二、操作题1创建用户定义的数据类型：编号（非空，长度为8的字符型）。2创建图书数据库（BookSys），并在数据库中建立如下表，要求图书编号、读者编号使用用户定义类型：编号。,习题五,图书信息（tsxx）,说明：图书编号、书名不能为空。读者信息（dzxx）,说明：读者编号、姓名不能为空。,习题五,借阅信息（jyxx）,说明：图书编号、读者编号不能为空。,习题五,3完成如下操作。（1）向读者信息表中添加列：联系方式，可以为空。（2）修改列“出版社”的定义，长度修改为200。（3）删除“联系方式”一列。4完成如下数据操作。（1）向各表插入若干数据。（2）修改读者信息表中编号为00001001的读者的级别为2级。（3）删除借阅信息表中读者编号为00001001借阅10010001图书的记录。,