SQLServer数据库基本对象操作和管理.ppt

第3章SQLServer数据库基本对象操作和管理,3.1数据库,在SQLServer2005中创数据库方法有两种，使用SQLServerManager和T-SQL(CreateDatebase)创建数据库。当新数据库创建之后，系统自动把model数据库中的系统表复制到新数据库中。下面描述在SQLServer2005中用两种不同方法创建数据库的内容。,1.定义数据库,一般来说，为了创建数据库首先应定义数据库，定义数据库的内容包括：(1)数据库名称如命名数据名称为Material_Data。(2)数据库大小如主文件(.mdf)大小为10MB，日志文件大小为3MB。(3)数据库将驻留在哪一个文件中如数据库将驻留在C:ProgramFilesMicrosoftSQLServerMSSQLdatamaterial_Data.MDF中。即，创建Material_Data数据库,主文件(.mdf)大小为10MB，日志文件大小为3MB。数据库将驻留在C:ProgramFilesMicrosoftSQLServerMSSQLdatamaterial_Data.MDF中。,2.ServerManager创建数据库,下面描述用ServerManager创建数据库步骤。第一步，登录SQLServer2005数据库引擎后，图3.1所示SQLServer数据库管理图面。,SQLServer数据库管理界面,在选择数据库菜单中右击鼠标，出现图3.2所示图面，选择新建数据库功能,第二步指定数据库名称Material_Data在进入新建数据库，图3.3所示图面中，在数据库名称输入框输入数据库名称Material_Data,指定数据库名称,图3.3数据库名称输入图面,指定数据库所有者,第三步指定数据库所有者可选择默认所有者。选择数据库所有者右边按钮，进入数据库所有者指定方法，步骤图3.4-3.7所示,图3.6指定数据库所者-3,图3.7指定数据库所者-4,指定主文件与日志文件初始大小,第四步指定主文件与日志文件初始大小，图3.8所示。主文件10MB，日志文件3MB。,图3.8指定主文件与日志文件初始大小,指定主文件与日志文件自助增长方式与最大文件大小,第五步指定主文件与日志文件自助增长方式与最大文件大小，在图3.8中，选择自动增长栏中右边按扭，出现图3.9所示窗口。在3.9所示窗口中可指定文件自动增长方式与最大文件大小。,图3.9指定主文件与日志文件自助增长方式最大文件大小,指定主文件与日志文件目录路径,第六步指定主文件与日志文件目录路径，在图3.8的路径栏中，选择与主文件、日志文件对应的右边按钮，分别进入主文件目录路径与日志文件目录路径指定窗口，图3.10与图3.11所示。,3T-SQL(CreateDatebase)创建数据库,下面描述用T-SQL(CreateDatebase)创建数据库。(1)选择新建查询命令，进入T-SQL命令编辑窗口，图3.12所示。,(2)在图3.12数据库下拉列表框中选择master数据库，图3.13所示。,(3)在图3.11的T-SQL命令编辑窗口，输入如下的T-SQL语句。CREATEDATABASEMaterial_Data1ONPRIMARY(NAME=Material_Data1,FILENAME=C:ProgramFilesMicrosoftSQLServerMSSQL.1MSSQLDatamaterial_Data1.MDF,SIZE=10MB,MAXSIZE=15MB,FILEGROWTH=20%)LOGON(NAME=Material_Log1,FILENAME=C:ProgramFilesMicrosoftSQLServerMSSQL.1MSSQLDatamaterial_Log1.LDF,SIZE=3MB,MAXSIZE=5MB,FILEGROWTH=1MB),其中，PRIMARY指定主文件(扩展名为.dmf)FILENAME指定文件名与路径SIZE指定数据或日志文件大小MAXSIZE指定文件能增长到的最大长度FILEGROWTH指定文件增长量,(4)在图3.13中，选择执行功能菜单，执行结果图3.14所示，数据库创建成功,(5)当新数据库创建之后，系统自动把model数据库中的系统表复制到新数据库中，新建成功的Material_Data出现在数据库列表中，图3.15所示。,3.2表,3.2.1表管理设计与创建假如有如下五个业务表,表3.1Department,表3.2Product,表3.3planning,表3.4Manager,表3.5Orderbill,现对以上五个表的表名、字段名、字段类型、字段长度、主键及外键设计如下，表3.6-3.10所示：,表3.6Department,表3.7Product,表3.8planning,表3.9Manager,表3.10Orderbill,现试图在3.1中建立的数据库Material_Data中，创建以上5个表。在SQLServer2005中,可用下述方法在己建立的数据库中创建及修改表。第一步，进入Material_Data数据库中新建表功能，图3.16所示。,1使用SQLServerManager创建表,填写表中列名、数据类型及是否为空，设置键及表存盘。(1)Departmenta.填写Department表中列名、数据类型，图3.17所示。,第二步,b.设置表Department中主键，图3.18-3.19所示。,c.存盘及表命名，图3.20-图3.22所示。,图3.20输入表命名Table_1,图3.21输入表命名Department,(3)planninga.填写表planning中列名、数据类型，图3.29所示。,3.2表,b.存盘及表命名，图3.30-3.32所示。,图3.30输入表命名Table_1,图3.31输入表命名Planning,c.按图3.33-3.38所示设置deptNo外键,图3.34设置deptNo外键-使用“添加“按钮,图3.35设置deptNo外键-确定“标识“名称,图3.36置deptNo外键-选择主键表与外键表,图3.37deptNo外键-选择主键表与外键表字段,d.设置productNo外键，图3.39-3.43所示。,图3.40设置productNo外键-使用“添加“按钮,图3.41设置productNo外键-确定标“名称”FK_planning_Product,图3.42设置productNo外键-确定主表、外键表等相关表中字段,图3.43设置productNo外键完成返回ManagementStudio窗口,2使用SQLServerManager设置表的属性,在属性框中，可修改表的属性，如可对前面设计的各表的列名，类型等进行修改，如图3.44所示。,3使用SQLServerManager修改表的结构,使用SQLServerManager增加、删除列（1）增加列，图3.45所示。,（2）删除列,4使用T-SQL语言创建表,创建表的SQL语句是CREATETABLE。CREATETABLE命令的语法如下：CREATETABLEtable_name(name_Of_attr_1type_Of_attr_l，nsme_of_attr_2type_Of_attr_2，)；,（1）创建表department。,SQLQuery.sql程序设计如下：CREATETABLEdepartment(deptNochar(10)NOTNULLPRIMARYKEY,deptNamevarchar(50)NOTNULL),（2）创建表product。,SQLQuery2.sql程序设计如下：CREATETABLEproduct(productNochar(10)NOTNULLPRIMARYKEY,productNamevarchar(50)NOTNULL),（3）创建表planning,SQLQuery3.sql程序设计如下：CREATETABLEplanning(deptNochar(10)NOTNULLconstraintFK_plan_deptNoforeignkey(deptNo)referencesdepartment(deptNo),productNochar(10)NOTNULLconstraintFK_plan_productNoforeignkey(productNo)referencesproduct(productNo),planoutputnumeric(10)NULL),（4）创建表Manager,SQLQuery4.sql程序设计如下：CREATETABLEManager(ManagerNochar(10)NOTNULLPRIMARYKEY,ManagerNamevarchar(50)NOTNULL,Sexchar(2)NULL,Wagenumeric(6)NULL),创建表Orderbill,SQLQuery5.sql程序设计如下：CREATETABLEOrderbill(OrderbillNochar(10)NOTNULLPRIMARYKEY,managerNochar(10)NOTNULLconstraintFK_Orderbill_managerNoforeignkey(managerNo)referencesmanager(managerNo),OrderDateDatetimeNULL,OrderMoneynumeric(10,2)NULL),3.2.2向表中插入数据,表创建完成后，可以用命令INSERTINTO向表里填充记录。语法是：INSERTINTOtable_name(name_Of_attr_1，name_Of_attr_2，)VALUES(val_attr_1，val_attr2，)；,1.INSERT语句,实例3.1把表3.11中数据输入数据库Material_Data中Department表中。,表3.11Department,INSERTINTOdepartment(deptNo,deptName)VALUES(0001,生产管理部)INSERTINTOdepartment(deptNO,deptName)VALUES(0002,销售管理部),2使用SELECT子句插入数据,语法形式：INSERTtable_nameSELECTcolumn_listFROMtable_listWHEREserach_conditions注意：参考表可以相同或不同；要插入数据的表必须已存在；要插入数据的表必须和SELECT子句结果集兼容；,建立department临时表department1,并把department中数据输入department1。程序设计如下：CREATETABLEdepartment1(deptNochar(10)NOTNULLPRIMARYKEY,deptNamevarchar(50)NOTNULL)INSERTdepartment1SELECTdeptNO,deptNameFROMdepartment,实例,3.2.3使用UPDATE修改表中数据,Update(更新数据)命令要修改记录中的一个或者多个属性的值，使用UPDATE命令。语法是；UPDATEtablenameSETname_Of_attr_1=value-1，nameof_attr_k=value_kWHEREcondition；,实例,把department表中“销售部”改为“市场部”。程序设计如下：UPDATEdepartmentSETdeptName=市场部WHEREdeptName=销售管理部,删除一条记录,要从一个表中删除一条记录，使用DELETEFROM命令。语法是：DELETEFROMtable_nameWHEREcondition实例删除department表部门名为市场部的部门。DELETEFROMdepartmentWHEREdeptName=市场部,3.2.5使用Transact_SQL修改表结构,修改表结构语句的基本语法格式为：ALTERTABLEtable_nameALTERCOLUMNcolumn_namenew_data_type(precision,scale)NULL|NOTNULL|ADD,n|DROPCONSTRAINTconstraint_name|COLUMNcolumn,n,实例,在表Product中增加一个新列Price(money类型，允许为空)。程序设计如下：USEMaterial_DataALTERTABLEProductADDPricemoneyNULLGO,实例,在表Product中删除列Price。程序设计如下：USEMaterial_DataALTERTABLEProductDROPCOLUMNPriceGO,3.3Index索引,索引：一种物理结构，提供一种以一列或多列的值为基础的迅速查找表中的行的机制索引类型聚簇索引：行的物理顺序与索引顺序一致非聚簇索引：逻辑顺序唯一性索引：创建索引的列上不包括重复数据SQLServer对索引的限制每表一个聚簇索引每表多个非聚簇索(249),3.3.1可视化创建索引,1.选择要创建索引的列，选择“索引/键（I）”，图3.62所示。,图3.62选择要创建索引的列,2.在弹出的“索引/键”对话框中，点击“添加”按钮，图3.63所示。,3.在“标识”栏下的“（名称）”里修改新增索引的名称为：IX_ProductName。图3.64所示。,图3.64修改新增索引的名称,4.在“常规”栏下的“列”里选择新增索引要绑定的列及排序方式（升序或降序）,图3.65选择新增索引要绑定的列及排序方式,图3.66选择新增索引要绑定的列及排序方式,5.点击“确定”完成排序,3.3.2使用Transact_SQL创建索引,CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEXindex_nameONtable|view(columnASC|DESC,n)WITH,nONfilegroup:=PAD_INDEX|FILLFACTOR=fillfactor|IGNORE_DUP_KEY|DROP_EXISTING|STATISTICS_NORECOMPUTE|SORT_IN_TEMPDB,实例,为表Product的productNane字段建立升序索引：USEMaterial_DataIFEXISTS(SELECTNAMEFROMsysindexesWHEREname=IX_ProductName)DROPINDEXProduct.IX_ProductNameGOUSEMaterial_DataCREATEINDEXIX_ProductNameONProduct(productName)GO,实例,为表Product的productNane、productNo两字段建立索引：USEMaterial_DataIFEXISTS(SELECTNAMEFROMsysindexesWHEREname=IX_PName_PNo)DROPINDEXProduct.IX_PName_PNoGOUSEMaterial_DataCREATEINDEXIX_PName_PNoONProduct(productName,productNo)GO,3.4SQLServer数据查询与表现,在SQLServer2005中方法有两种查询数据库数据方法，使用SQLServerManager和T-SQL创建查询。SQL里面最常用的命令是select(查询)语句，用于检索数据。,3.4.1检索表中列技术,1检索全部列检索pub数据库中jobs表中全部信息。SELECT*FROMjobs2重新对列排序结果集中列的顺序由SELECT关键字后面列名的顺序确定。SELECTjob_desc,job_idFROMjobs,3.4.1检索表中列技术,3使用文字串技术可在SELECT关键字后面增加文字说明。SELECTjob_desc,itsidis,job_idFROMjobs4改变列标题技术可在SELECT关键字后面，对每列表头增加描述字说明。SELECTDescrbition=job_desc,IdentifyNumber=job_idFROMjobs将jobs表中的job_desc、job_id两列表头增加描述字说明。这样列表显示的表头将是Descrbition（对应job_desc列）、IdentifyNumber（对应job_id列）。,3.4.2操纵数据技术,使用算术运算符：+,-,*,/。可以用在各种数字列上。对pub数据库titles表中的price列进行如下数据运算：price+2、price2、price*2及price/2。对应这些运算的SELECT语句如下：SELECTtitle,price,price+2FROMtitlesSELECTtitle,price,price2FROMtitlesSELECTtitle,price,(price*2)FROMtitlesSELECTtitle,price,(price/2)FROMtitles,3.4.3选择行技术,语法格式：SELECTslect_listFROMtable_listWHEREsearch_conditions1比较技术search_conditions比较运算符有：等于大于小于=小于或者等于不大于!不小于!,实例,查询pubs数据库employee表中pub_id不等于0877，列名为pub_id,emp_id,fname的行。Selectpub_id,emp_id,fnameFromemployeeWherepub_id0877查询pubs数据库authors表中state等于CA，列名为au_lname,city,state的行。Selectau_lname,city,stateFromauthorsWherestate=CA,2范围技术,BETWEEN指定搜索的范围。语法：test_expressionNOTBETWEENbegin_expressionANDend_expression从pubs数据库titles表中检索年销售额在2000至2200之间的书籍。Selecttitle_id,ytd_salesFromtitlesWhereytd_salesBetween2000and2200,3逻辑运算技术,AND|ORNOT指定搜索的逻辑运算结果。语法：SELECTselect_listFROMtable_listWHERENOTexpressionAND|ORNOTexpression,实例,从pubs数据库authors表中检索au_lname=white或au_lname=green或state=CA的作者。Select*FromauthorsWhereau_lname=whiteORau_lname=greenORstate=CA,4汇总数据技术,1)合计函数AVG数据表达式的平均值COUNT某个表达式中数据值的数量COUNT(*)所选择行的数量MAX表达式中最大值MIN表达式中最小值SUN数据表式的总和,实例,从pubs数据库titles表中检索price平均值。Selectavg(price)Fromtitles从pubs数据库titles表中检索price种类数，语句如下：Selectcount(price)Fromtitles从pubs数据库titles表中检索记录数，语句如下：Selectcount(*)Fromtitles,(2)GROUPBY子句,对查询结果分组后，合计（聚集）函数将分别作用于每个组按指定的一列或多列值分组，值相等的为一组,(2)GROUPBY子句,求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；,视图及其应用,视图综述视图的操作视图定义信息查询案例中的视图应用,一、视图综述,1.视图的基本概念视图是一个虚拟表，从一个或多个表中导出（视图也可以从视图中导出），其内容由查询语句定义生成。从表面上看，视图和真实表一样，具有结构和数据，包含一系列带有名称的列和行数据。实质上，表是视图的基础，数据库中只存储了视图定义，而不存放视图所对应的数据，视图所对应的数据仍存放在视图所引用的基表中。,一、视图综述,2.视图的作用1)视图可以集中数据，满足不同用户对数据的不同要求。2)视图可以简化复杂查询的结构，从而方便用户对数据的操作。3)视图能够对数据提供安全保护。4)便于组织数据导出。,二、视图的操作,创建视图修改视图使用视图删除视图,创建视图时应该注意以下：,创建者必须拥有创建视图的权限，对视图中引用的基表或视图有许可权。只能在当前数据库中创建视图，如果使用分布式查询，视图所引用的基表和视图可以存在于其它数据库或它服务器中。在一个视图中最多引用1024列，视图中记录的行数限制由基表中记录数目决定。视图的名称必须遵循标识符的定义规则，对于每个用户来说也必须唯一，而且视图名称不允许与该用户拥有的表重名。,视图中列的名称一般继承其基表中列的名称，如果视图中某一列是算术表达式、函数、常量或者来自多个表的列名相同，必须要为视图中的列定义名称。可以将视图创建在其它视图上，SQLServer2000中允许32层的视图嵌套。不能将规则、默认绑定在视图上。不能在临时表上创建视图，也不能创建临时视图。,创建视图时应该注意以下:,创建视图,1.使用企业管理器创建视图2.使用CREATEVIEW语句创建视图,例：创建一个包括学生学号、姓名及所选课程的课程名以及成绩的视图（视图名：成绩单）。,1.使用企业管理器创建视图,创建过程中的图解：,属性,图视图设计器,1.使用企业管理器创建视图,创建步骤如下：1）在企业管理器，打开“视图设计器”。2）单击工具栏上的“添加表”按钮，将表添加到视图设计器中。3）根据新建视图的需要，从表中选择视图引用的列。4）在网格窗格中的“准则”栏中设置过滤记录的条件。5）设置视图的其它属性。6）在视图设计器窗口中，检查SQL语法。7）最后，单击“保存”按钮，为视图指定名称。关闭“视图设计器”。,图“添加表”对话框,1.使用企业管理器创建视图,图选择表列,2.使用CREATEVIEW语句创建视图,语法格式如下：CREATEVIEW.view_name(column,.n)ASselect_statementWITHCHECKOPTION,2.使用CREATEVIEW语句创建视图,例：以学生表（S表）为基础建立一个视图，其名称为“学生名单”，其包含列为学生表中的学号和姓名。程序清单如下：,CREATEVIEW学生名单ASSELECTSNO,SNAMEFROMS,从单个表派生的视图:,2.使用CREATEVIEW语句创建视图,例：以选课表(S_C表)为基础建立一个视图，其名称为“数据库成绩”，其包含列为选课表中的所有列，筛选记录条件为“课程编号=001”。程序清单如下：WITHCHECKOPTION,CREATEVIEW数据库成绩ASSELECT*FROMS_CWHERECNO=001,2.使用CREATEVIEW语句创建视图,例：给出有北京仓库订购单的北京供应商的名称。WITHCHECKOPTION,从多个表派生的视图:,CREATEVIEWBJ_订购单AS,SELECT供应商名FROM供应商WHERE地址=北京AND供应商号IN(SELECT供应商号FROM订购单WHERE职工号IN(SELECT职工号FROM职工WHERE仓库号IN(SELECT仓库号FROM仓库WHERE城市=北京),例：列出每个职工经手的具有最高总金额的订购单信息。,订购单1,2.使用CREATEVIEW语句创建视图,2.使用CREATEVIEW语句创建视图,CREATEVIEWV_MAXASSELECT职工号,MAX(总金额)AS最大总金额FROM订购单1GROUPBY职工号,SELECT订购单1.职工号,订购单号,供应商号,订购日期,总金额FROM订购单1,V_MAXWHERE订购单1.职工号=V_MAX.职工号AND订购单1.总金额=V_MAX.最大总金额,视图中的虚列:,或：CREATEVIEWV_SAL1ASSELECT职工号,工资as月工资,工资*12as年工资FROM职工,2.使用CREATEVIEW语句创建视图,CREATEVIEWV_SAL(职工号,月工资,年工资)ASSELECT职工号,工资,工资*12FROM职工,例：定义一个视图，包含职工号、月工资、年工资。,修改视图,1.使用企业管理器2.使用ALTERVIEW语句其语法格式如下：ALTERVIEWview_name(column,.n)ASselect_statement,ALTERVIEW学生名单ASSELECTSNAMEFROMS,修改视图,例：将视图“学生名单”修改为只包含列为学生表中的姓名。程序清单如下：WITHCHECKOPTION,使用视图,1.通过视图查询数据信息例：查询“数据库成绩”视图中全部的记录。,SELECT*FROM数据库成绩,使用视图,2.通过视图修改数据在使用视图修改数据时，要注意下列一些事项：,不能在一个语句中对多个基表使用数据修改语句。如果要修改由两个或两个以上基表得到的视图，必须进行多次修改，每次修改只能影响一个基表。对于基表中需更新而又不允许空值的所有列，它们的值在INSERT语句或DEFAULT定义中指定。这将确保基表中所有需要值的列都可以获取值。,不能修改那些通过计算得到结果的列。在视图定义中使用了WITHCHECKOPTION子句，则所有在视图上执行的数据修改语句都必须符合定义视图的SELECT语句中所设定的条件。在基表的列中修改的数据必须符合对这些列的约束条件，如是否为空、约束、DEFAULT定义等。,使用视图,使用视图,例：向“数据库成绩”视图中插入一条新的选课记录。学号为0008,课程号为001,成绩为90。,INSERTINTO数据库成绩VALUES(0008,001,90)GO,使用视图,例：将“数据库成绩”视图中学生0003的成绩提高10%。,UPDATE数据库成绩SETSCORE=SCORE*1.1WHERESNO=0003,INSERTINTO学生名单VALUES(0011,孟欢),使用视图